MySQL数据库——语句

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

---

一、MySQL数据库语句

（一）、按关键字排序

使用select语句可以将需要的数据从 mysql 数据库中查询出来，如果对查询的结果进行排序操作，可以使用 order by 语句完成排序，并且最终将排序后的结果返回给客户。这个语句的排序不光可以针对某一个字段，也可以针对多个字段。

select 字段 from 表名 order by 字段 asc|desc


1
2
3

#asc|desc

#asc 是按照升序进行排名的，是默认的排序方式，即ASC可以省略
#desc 是按照降序的方式进行排序的
order by 也可以通过 where 子句对查询结果进行进一步的过滤
1
2
3
4
5

1、可进行多字段的排序

create table info1 (id int (10) primary key not null auto_increment,name char(10),score decimal(5,2),address varchar(40),hobbyid varchar(10));

insert into info1 values(1,'liuyi',80,'beijing',2);
insert into info1 values(2,'wangwu',90,'shengzheng',2);
insert into info1 values(3,'lisi',60,'shanghai',4);
insert into info1 values(4,'tianqi',99,'hangzhou',5);
insert into info1 values(5,'zhangsan',98,'chengdu',3);
insert into info1 values(6,'hanmeimei',59,'nanjing',3);
insert into info1 values(7,'lilei',70,'nanjing',5);

select * from info1;
1
2
3
4
5
6
7
8
9
10
11

（二）、单字段排序

1、按分数排序，默认不指定是升序排列

select * from info1 order by score; ###（asc默认省略）
1

2、按分数排序，降序排列

select * from info1 order by score desc;   
1

3、结合where进行条件过滤，筛选条件是nanjing的学生按分数把名字和分数降序

select name,score from info1 where address='nanjing' order by score desc;

1
2

（三）、多字段排序

ORDER BY 语句也可以使用多个字段来进行排序，当排序的第一个字段相同的记录有多条的情况下，这些多条的记录再按照第二个字段进行排序，`order by` 后面跟多个字段时，字段之间使用英文逗号隔开，优先级是按先后顺序而定，但order by 之后的第一个参数只有在出现相同值时，第二个字段才有意义。
1

1、查询学生信息先按照hobbidy降序 相同的在按照id降序排列

select * from info1 order by hobbid desc,id desc;

1
2

2、查询学生信息先按照hobbyid升序 相同的在按照id升序排列

select * from info1 order by hobbyid,id;
1

（四）、区间判断及查询不重复记录

1、AND/OR——且/或 的使用

（1）、and——且

select * from info1 where id>2 and id<5;     #2
1

（2）、or——或

select * from info1 where id>5 or id<2;      
#id>5或者id<2
1
2

（3）、嵌套/多条件——先括号里的

`先括号里的`
select * from info1 where id=2 or (id>3 and id <5);  #相当于id=2或者id是4

select * from info1 where score <70 or (score >90 and score <100);   ##分数小于70或者  大于90小于100 
1
2
3
4

（4）、distinct查询不重复记录——去重查询

select  distinct字段  from 表名﹔

`distinct 必须放在最开头`
`distinct 只能使用需要去重的字段进行操作`
`distinct 去重多个字段时，含义是：几个字段同时重复时才能被过滤，会默认按左边第一个字段为依据。`

select address from info1 group by address;
1
2
3
4
5
6
7

（五）、对结果进行分组

通过 SQL 查询出来的结果，还可以对其进行分组，使用` GROUP BY `语句来实现 ，GROUP BY 通常都是结合聚合函数一起使用的，常用的聚合函数包括：`计数（COUNT）`、 `求和（SUM）`、`求平均数（AVG）`、`最大值（MAX）`、`最小值（MIN）`，GROUP BY 分组的时候可以按`一个`或`多个字段`对结果进行分组处理。
1

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator valueGROUP BY column_name;

selet字段，聚合函数from表名，(where字段名（匹配）数值)group by字段名;

1
2
3
4

1、对hobbyid进行分组查询，并显示最大的id

select hobbyid,max(id) from info1 group by hobbyid;
1

2、按hobbyid相同的分组，计算相同的个数

select count(name),hobbyid from info1 group by hobbyid;

1
2

3、结合order by 把统计的id数量进行降序排列

select count(id),hobbyid from info1 group by hobbyid order by count(id) desc;

1
2

4、结合where语句，筛选分数大于等于80，以hobbyid字段的分组，计算学生个数按降序排列

select count(name),score from info1 where score >= 80 group by hobbyid order by count(name) desc;
1

5、限制结果条目

在使用 MySQL SELECT 语句进行查询时，结果集返回的是所有匹配的记录（行）。有时候仅 需要返回第一行或者前几行，这时候就需要用到 LIMIT 子句

select 字段,聚合函数 from 表名 (where 字段名(匹配) 数值) group by 字段名;
SELECT column1, column2, ... FROM table_name LIMIT [offset,] number


select 字段 from 表名 limit [offset,] number

limit 的第一个参数是位置偏移量（可选参数），是设置 mysql 从哪一行开始
如果不设定第一个参数，将会从表中的第一条记录开始显示。
第一条偏移量是0，第二条为1
offset 为索引下标
number 为索引下标后的几位
1
2
3
4
5
6
7
8
9
10
11
12

（1）、查询所有信息显示前4行记录

select * from info1 limit 4;
1

（2）、从第四行开始，往后显示3行内容

select * from info1 limit 3,3;
1

（3）、结合order by 语句，按id的大小升序排列显示前五行（降序就用desc）

select * from info1 order by id limit 5;
1

（六）、设置别名（alias —— as）

在 mysql 查询时，当表的名字比较长或者表内某些字段比较长时，为了方便书写或者多次使用相同的表，可以给字段列或表设置别名， 方便操作，增强可读性。

列的别名  select 字段 as 字段别名 表名


表的别名 select 别名.字段 from 表名 as 别名


as 可以省略
1
2
3
4
5
6
7

#使用场景：

#对复杂的表进行查询的时候，别名可以缩短查询语句的长度
#多表相连查询的时候（通俗易懂、减短sql语句）
1
2
3
4

1、列别名设置示例

select name as 姓名,score as 成绩 from info1;
1

如果表的长度比较长，可以使用AS给表设置别名，在查询的过程中直接使用别名

2、给info表设置别名为i

select i.name as 姓名,i.score as 成绩 from info1 as i;
1

3、查询info1表的记录数量，以“*”显示

select count(*) as 记录行 from info1;
1

3.1、不写as也可以

3.2、as还可以作为连接语句的操作符

利用as，将查询的数据导入到另外一个表内

create table info2 as select * from info1;
或
create table info2 (select * from info1);
1
2
3

3.3、也可以加入where语句判断

create table info3 as select * from info1 where score >=60;
1

3.4、此处AS起到的作用：

创建了一个新表t1 并定义表结构，插入表数据（与info1表相同）

但是`约束没有被完全复制过来` 但是如果原表设置了`主键`，那么附表的：`default字段会默认设置一个0`
1
2
3

（七）、通配符

• 通配符主要用于替换字符串中的部分字符，通过部分字符的匹配将相关结果查询出来。
• 通常通配符都是跟like一起使用，并协同where子句共同来完成查询任务。

常用的通配符有两个：

1、%:代表零个、一个或多个字符
2、_：下划线代表单个字符

1
2
3
4
5

1、查询名称是t开头的 id 名字 成绩字段

查询名称是t开头的 id 名字 成绩字段

select id,name,score from info1 where name like 't%';
1
2
3

2、查询名字中间有一个忘记的

select id,name,score from info1 where name like 'l_uy_';
1

3、查询名字中间有y的

select id,name,score from info1 where name like '%y%';
1

（八）、子查询

• 子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句。子查询语句是先于主查询进行下一步的查询过滤。

• 在子查询中可以与主语句查询相同的表，也可以是不同的表

• 子语句可以与主语句所查询的表相同，也可以是不同表

1、相同表查询

select 字段1，字段2 from 表名1 where 字段 in (select 字段 from 表名 where 条件)；

#将info1表中分数大于80的id的结果集给前面的查询语句进行查询
select id,name from info where id in (select id from info1 where score >80);
1
2
3
4

2、多表查询

select name,score from info1 where id in (select id from info3 where score >80);

1
2
3

3、注意

子查询不仅可以在select语句中使用，还可以在inert update delete 也可以使用。在嵌套的时候，子查询内部还可以再次嵌套新的子查询，也就是说可以多层嵌套

（1）、inset

子查询还可以用在insert语句中，子查询的结果集可以通过insert语句插入到其它表中

select * from infor3;
delete from infor3;
insert into infor3 select * from info1 where id in (select id from info1);

1
2
3
4

（2）、update

update info1 set score=100 where id in (select id from info3 where id >=7);  
#将表id中小大于等于7的id给前面的主查询
将后面给的结果集满足条件的id的成绩改为100
1
2
3

（3）、delete

 delete from info1 where id in (select info3 where id>=7);

 将表info3中id大于等于7的id给主查询
 将满足的id数据删除

1
2
3
4
5

（4）、exists

exists这个关键字在子查询时，主要用于判断子查询的结果集是否为空，如果不为空，则返回true，反之则返回false
ps：在使用exists时，当子查询有结果时，不关心子查询的内容，执行主查询操作；当子查询没有结果时，则不执行主查询操作。

查询如果存在分数等于 80 的记录则计算 info1 的字段数
select count(*) from info1 where exists(select id from info1 where score=80);

1
2
3

4、别名as

将结果集作为一张表进行查询的时候，我们也需要用到别名

select a.id,a.name from (select id,name,score from info where score>80) a;
#将子查询的结果做别名a，在使用as别名时as可以省略




select id from (select id,name from info1);
ERROR 1248 (42000): Every derived table must have its own alias
 
##报错原因
select * from 表名 此为标准格式，而以上的查询语句，"表名" 的位置其实是一个结果集，
mysql 并不能直接识别，而此时给与结果集设置一个别名，以 "select a.id from a" 的方式查询将此结果集是为一张 "表"，就可以正常查询数据了，如下：
1
2
3
4
5
6
7
8
9
10
11
12

（九）、视图

视图是一张虚拟的表，这张虚拟表中不包含真实数据，只是做了真实数据的映射

1、功能：

• 使操作简单化，可以对经常使用的查询定义一个视图，使用户不必为同样的查询操作指定条件
• 增加数据的安全性，通过视图，用户只能查询和修改指定的数据
• 提高表的逻辑独立性，视图可以屏蔽原有表结构变化带来的影响

本质而言，视图是一种select（结果集的呈现）

注：视图适合于多表浏览时使用，不适合增、删、改

2、小结：

使用视图的大部分情况是为了保障数据安全性，提高查询效率，通过视图定义展示的条件。
1

3、视图和表的区别：

1. 视图是已经编译好的sql语句。而表不是；
2. 视图没有实际的物理记录。而表有；
3. 表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改；
4. 视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构；
5. 表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表；
6. 视图的建立和删除只影响视图本身，不影响对应的基本表。（但是更新视图数据，是会影响到基本表的）

4、联系

视图(view)是在基本表之上建立的表，它的结构(即所定义的列)和内容(即所有数据行)都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系

5、创建视图

#创建视图
create view 视图表名 as select * from 表名 where 条件;
#查看视图
select * from 视图表名
#查看表状态
show table status\G
#查看视图结构
desc 视图表名
1
2
3
4
5
6
7
8

create view v_score as select * from info1 where score >80;


1
2
3

（1）、查看视图与源表结构

#查看视图结构
desc 视图表名
1
2

（2）、多表创建视图

create view v_infoc1_test1 (id,name,score,age) as select info1.id,info1.name,info1.score,test01.age from info1,test01 where info1.name=test01.name;


1
2
3

（3）、修改原表数据，视图数据会发生改变

（4）、更新视图的数据，原表也会变化

update v_info1_test1 set score=0 where id=2;


1
2
3

（十）、NULL值

在 SQL 语句使用过程中，经常会碰到 NULL 这几个字符。通常使用 NULL 来表示缺失的值，也就是在表中该字段是没有值的。如果在创建表时，限制某些字段不为空，则可以使用 NOT NULL 关键字，不使用则默认可以为空。在向表内插入记录或者更新记录时，如果该字段没有 NOT NULL 并且没有值，这时候新记录的该字段将被保存为 NULL。需要注意 的是，NULL 值与数字 0 或者空白（spaces）的字段是不同的，值为 NULL 的字段是没有 值的。在 SQL 语句中，使用 IS NULL 可以判断表内的某个字段是不是 NULL 值，相反的用 IS NOT NULL 可以判断不是NULL值
1

1、NULL值与空值区别——NULL是占用内存空间的，而空值则不占用内存空间

空值长度为0，不占空间，NULL值的长度为null，占用空间
is null无法判断空值
空值使用"=“或者”<>"来处理（！=）
count（）计算时，NULL会忽略，空值会加入计算
1
2
3
4

新添加一个字段
统计结果为0 说明null不计入统计
1
2

空值计入计算
1

（十一）、运算符

MySQL 的运算符用于对记录中的字段值进行运算。MySQL 的运算符共有四种，分别是：算术运算符、比较运算符、逻辑运算符和位运算符。

1、算术运算符

2、比较运算符

3、等号（=）

是用来判断数字、字符串和表达式是否相等的，如果相等则返回 1(true)，如果不相等则返回 0（flase)。如果比较的两者有一个值是 NULL，则比较的结果就是 NULL。其中字符的比较是根据 ASCII 码来判断的，如果 ASCII 码相等，则表示两个字符相同；如果 ASCII 码不相等，则表示两个字符不相同；
字符串（字母）比较：（‘a’>‘b’）其实比较的就是底层的 ASCII 码；
需要关注的是 ASCII 码有：a、A、0（97、65、48）；
与 linux 返回值表达相反，linux 中运行正常返回值是 0，运行异常返回值是非 0。

如果两个都是整数，则按照整数值进行比较
如果一个整数一个字符串，则会自动将字符串转换为数字，在进行比较。（在程序中，一般是不会把这两者进行想比较的）
如果两者都是字符串，则按照字符串进行比较
如果两者中至少有一个值是NULL，则比较的结果是NULL。
1
2
3
4

4、不等于（<>,!=）

不等于号有两种写法，分别是<>或者!=，用于针对数字，字符串和表达式不相等的比较。如果不相等则返回1，如果相等则返回0，这点正好跟等于的返回值相反。需要注意的是不等于运算符不能用于判断NULL

大于（>）运算符用来判断左侧的操作数是否大于右侧的操作数，若大于返回1，否则返回0，同样不能用于判断NULL。
小于（<）运算符用来判断左侧的操作数是否小于右侧的操作数，若小于返回1，否则返回0，同样不能用于判断NULL。
大于等于（>=）判断左侧的操作数是否大于等于右侧的操作数，若大于等于返回1，否则返回0，不能用于判断NULL。
小于等于（<=）判断左侧的操作数是否小于等于右侧的操作数，若小于等于返回1，否则返回0，不能用于判断NULL。

1
2
3
4
5

5、Beyween and

BETWEEN AND 比较运算通常用于判断一个值是否落在某两个值之间。
例如，判断某数字是否在另外两个数字之间，也可以判断某英文字母是否在另外两个字母之间

Beyween and 覆盖的范围是 >= 和 <= 关系

6、least 和 greatest（取最小值、取最大值）

LEAST：当有两个或多个参数时，返回其中的最大值，如果其中一个值为NULL，则返回结果就为NULL。
GREATEST：当有两个或多个参数时，返回其中的最大值，如果其中一个值为NULL，则返回结果就为NULL。
若要判断一组数字或字母中哪个最小、哪个最大，可以通过使用 LEAST 和 GREATEST 来实现
1
2
3

7、in & not in（判断一个值是否在，如果是返回1，否则返回0）

IN 判断一个值是否在对应的列表中，如果是返回1，否则返回0。
NOT IN 判断一个值是否不在对应的列表中，如果不是返回1，否则返回0
1
2

8、like & not like （匹配字符串，成功则返回1，反之返回0）

*LIKE 用来匹配字符串，如果匹配成功则返回1，反之返回0
LIKE支持两种通配符:'%'用于匹配任意数目的字符（匹配的是前面一个字符），而’_'只能匹配一个字符。
NOT LIKE 正好跟LIKE相反，如果没有匹配成功则返回1，反之返回0。
1
2
3

9、逻辑运算符（布尔值）

逻辑运算符又被称为布尔运算符，通常用来判断表达式的真假，如果为真返回1，否则返回0，真和假也可以用 TRUE 和 FALSE 表示。MySQL中支持使用的逻辑运算符有四种。

（1）、逻辑非

逻辑运算符中最简单的运算符就是逻辑非，逻辑非使用NOT 或 ! 表示。逻辑非将跟在它后面的逻辑测试取反，把真变为假，把假变为真。如果NOT 后面的操作数为0时，所得值为1；如果操作数为非0时，所得值为0；如果操作数为NULL时，所得值为NULL。

（2）、逻辑与（and）

当所有的操作都为非0值且不为null时，返回值为1，否则为0
（null与0比较特殊）
逻辑与使用 AND 或者 && 表示
1
2
3

由结果可以看出
and 和 && 的作用相同
1 and -1 没有0 或 null，所以返回值为1
1 and 0 中有0，所以返回值为0
1 and null 有null，所以 返回值为null
null and 0 返回值为0

1
2
3
4
5
6
7

（3）、逻辑或（or）

逻辑或通常使用 or
逻辑或表示包含的操作数，任意一个为非零值且不是 NULL 值时，返回1，否则返回0。
当有一个操作数为null时，如果另一个操作数为非0值，则返回值为1，否则为null
如两个操作数均为null，则返回值为null

使用或运算符or进行逻辑判断
1
2
3
4
5
6

（4）、逻辑异或（xor）

两个非NULL 值的操作数，如果两者都是0或者都是非0，则返回0；
如果一个为0，另一个为非0，则返回结果为1；
当任意一个值为NULL时，返回值为NULL
1
2
3

10、位运算符

位运算符实际上是对二进制数进行计算的运算符。MySQL内运算会先将操作数变为二进制格式（10101111），然后进行运算，最后在将计算结果
1

从二进制变回到十进制格式，方便用户查看。MySQL支持6 种位运算符，具体如表所示。

10转换位二进制数是 1010，15转换位二进制是1111

按位与运算（&），是对应的二进制位都是1的，它们的运算结果位1，否则为0，所以 10 & 15 结果位10。
按位或运算（|），是对应的二进制位有一个或两个位1的，运算结果位1，否则为0，所以10 | 15 的结果为 15。
按位异或运算（^），是对应的二进制位不相同时，运算结果1，否则为0，所以10 ^ 15 的结果为5
按位取反（~），是对应的二进制数逐位反转，即1取反后变为0，0取反变为1。数字1的二进制是0001，取反后变成1110，数字5的二进制是0101，将1110和0101进行求与操作，其结果是二进制的0100，转换为十进制就是4
1
2
3
4
5
6

以上不管哪种运算符，在使用过程中都会有优先级问题，运算符的优先级决定了不同的运算符在计算过程中的先后顺序。级别高的运算符会先进行计算，如果运算符的级别相同，MySQL会按照顺序从左到右依次进行计算，优先级如下表所示

（十二）、连接查询

MySQL 的连接查询，通常都是将来自两个或多个表的记录行结合起来，基于这些表之间的 共同字段，进行数据的拼接。首先，要确定一个主表作为结果集，然后将其他表的行有选择 性的连接到选定的主表结果集上。使用较多的

连接查询包括：内连接、左连接和右连接

1、内连接

MySQL 中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。
通常在 FROM 子句中使用关键字 INNER JOIN 来连接多张表，并使用 ON 子句设置连接条件，内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只使用 关键字 JOIN。
同时有多个表时，也可以连续使用 INNER JOIN 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表
1
2
3

内连查询：通过inner join的方式将俩张表指定的相同字段的记录行输出出来

select 表名1.字段1,表名1.字段2 from 表名1 inner join 表名2 on 表名1.字段 = 表名2.字段;
1

select info1.id,info1.name from info1 inner join test01 on info1.name=test01.name;


1
2
3

2、左连接

左连接也可以被称为左外连接，在FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 关键字来表示。左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行

左连接中左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录，右表记录不足的地方为NULL.
1
2
3

select * from a left join b on a.id=b.id;

#左边的表内容全部显示，右面显示满足条件的
没有数据会以null显示


1
2
3
4
5
6

3、右连接

右连接也被称为右外连接，在FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 关键字来表示。右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配
1

select * from a right join b on a.id=b.id;

1
2

（十三）、补充一些查询命令

1、查看通用查询日志是否开启

show variables like 'general%';
1

2、查看二进制日志是否开启

show variables like 'log_bin%';


1
2
3

3、查看慢查询日志功能是否开启

show variables like 'slow%';

set global slow_query_log=NO;  #在数据库中设置开启慢查询的方法
1
2
3

（十四）、函数

select avg(score) from info1;     #平均值
select count(score) from info1;   #个数
select min(score) from info1;     #最小值
select max(score) from info1;     #最大值
select sum(score) from info1;    #和
1
2
3
4
5

（十五）、存储过程

1、概述

在实际的数据库应用中，有些数据库操作可能会很复杂，会用到多条SQL语句一起去处理才能完成，这时候就可以使用存储过程，更加轻松高效的完成需求，类似shell脚本中的函数。

存储过程是一组为了完成特定功能的SQL语句集合
1
2
3

#存储过程在使用过程中是将常用的或者复杂的工作预先使用SQL语句写好，并用一个指定的名称储存起来，经过编译和优化后存在数据库服务器中。需要时，只要调用即可。比传统SQL速度更快，执行效率更高
1

2、存储过程的优点

1、执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
2、SQL语句加上控制语句的集合，灵活性高
3、在服务器端存储，客户端调用时，降低网络负载
4、可多次重复被调用，可以随时修改，不影响客户端调用
5、可完成所有的数据库操作，可以控制数据库的信息访问权限

1
2
3
4
5
6

3、创建存储过程

delimiter $$               #将语句的结束符临时改为$$（可自行定义）
CREATE PROCEDURE proc()   #创建存储过程，名为proc，不带参数
begin                      #以关键字BEGIN开始
select * from info1;        #过程体语句
end $$                     #以关键字END结束
delimiter ;                #将语句的结束符改回;


CALL proc()       #调用存储过程
1
2
3
4
5
6
7
8
9

4、查看存储过程

show create procedure [数据库.]存储过程名
show create procedure proc\G
1
2

5、存储过程的参数

DELIMITER @@       #将语句的结束符临时改为@@（可自行定义）
CREATE PROCEDURE proc2(in inname varchar(40))
begin
select * from info1 where name=inname;
end @@
delimiter ;   
1
2
3
4
5
6