MYSQL补充SQL语句

MYSQL补充SQL语句

一、order by按关键字排序

​ 对指定单个或多个字段作为条件进行排序

​ 现在我们有一张表，我们可以通过对id和age作为条件进行排序（注意：排序要从左到右进行运行）

mysql> select * from ky20;
+----+------------+----------+---------+------+-----------+
| id | name       | cardid   | address | age  | hobby     |
+----+------------+----------+---------+------+-----------+
|  1 | 张三       |  2233123 | 南京    |   18 | 睡懒觉    |
|  2 | 李四       |  3213133 | 北京    |   90 | 打人      |
|  3 | 王五       | 54353321 | 上海    |   40 | 烫头      |
|  4 | 赵六       |   546654 | 黑河    |   21 | 吃大鹅    |
|  5 | 辉哥       |   676123 | 地球    |   32 | 禅悟      |
|  6 | 牛牛       |   532432 | 火星    |   22 | 钓鱼      |
|  7 | 羊峰       |   867212 | 火星    |    6 | 吃杨      |
|  8 | 测试员1    |  2233123 | 月球    |   16 | 跳海      |
|  9 | 张鹏程     |   532432 | 南通    |   18 | 周妍      |
+----+------------+----------+---------+------+-----------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

此时我们排序，以年龄和id作为条件进行多条件排序

select * from 表名 order by 字段1 升降序 ,字段2 升降序;
1

mysql> select * from ky20 order by age desc ,id desc;
+----+------------+----------+---------+------+-----------+
| id | name       | cardid   | address | age  | hobby     |
+----+------------+----------+---------+------+-----------+
|  2 | 李四       |  3213133 | 北京    |   90 | 打人      |
|  3 | 王五       | 54353321 | 上海    |   40 | 烫头      |
|  5 | 辉哥       |   676123 | 地球    |   32 | 禅悟      |
|  6 | 牛牛       |   532432 | 火星    |   22 | 钓鱼      |
|  4 | 赵六       |   546654 | 黑河    |   21 | 吃大鹅    |
|  9 | 张鹏程     |   532432 | 南通    |   18 | 周妍      |
|  1 | 张三       |  2233123 | 南京    |   18 | 睡懒觉    |
|  8 | 测试员1    |  2233123 | 月球    |   16 | 跳海      |
|  7 | 羊峰       |   867212 | 火星    |    6 | 吃杨      |
+----+------------+----------+---------+------+-----------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

当然我们还可以再追加条件来查询

select * from 表名 where 条件  order by 字段1 升降序 ,字段2 升降序;
1

二、查询不重复记录

​ 指定查找列中不重复的数据

select distinct 去重的列 from 表明;
1

mysql> select distinct cardid from ky20;
+----------+
| cardid   |
+----------+
|  2233123 |
|  3213133 |
| 54353321 |
|   546654 |
|   676123 |
|   532432 |
|   867212 |
+----------+
1
2
3
4
5
6
7
8
9
10
11
12

三、分组以及聚合函数

​ SQL查询出来的结果我们可以通过group by来实现，而分组常常结合聚合函数一起使用常用的聚合函数包括计数（COUNT）、 求和（SUM）、求平均数（AVG）、最大值（MAX）、最小值（MIN），而group可以按照一个或多个字段进行分组

mysql> select * from ky20;
+----+------------+----------+---------+------+-----------+
| id | name       | cardid   | address | age  | hobby     |
+----+------------+----------+---------+------+-----------+
|  1 | 张三       |  2233123 | 南京    |   18 | 睡懒觉    |
|  2 | 李四       |  3213133 | 北京    |   90 | 打人      |
|  3 | 王五       | 54353321 | 上海    |   40 | 烫头      |
|  4 | 赵六       |   546654 | 黑河    |   21 | 吃大鹅    |
|  5 | 辉哥       |   676123 | 地球    |   32 | 禅悟      |
|  6 | 牛牛       |   532432 | 火星    |   22 | 钓鱼      |
|  7 | 羊峰       |   867212 | 火星    |    6 | 吃杨      |
|  8 | 测试员1    |  2233123 | 月球    |   16 | 跳海      |
|  9 | 张鹏程     |   532432 | 南通    |   18 | 周妍      |
+----+------------+----------+---------+------+-----------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

​ （1）计数（COUNT）

select count(*) from ky20
1

统计表内有几条数据

mysql> select count(*) from ky20;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1
2
3
4
5
6

统计20岁以上的人的个数

select count(*),age from ky20 where age >20 group by age;
1

mysql> select count(*),age from ky20 where age >20 group by age;
+----------+------+
| count(*) | age  |
+----------+------+
|        1 |   21 |
|        1 |   22 |
|        1 |   32 |
|        1 |   40 |
|        1 |   90 |
+----------+------+
1
2
3
4
5
6
7
8
9
10

​ （2）平均数

统计前五个（包含5）人员的平均数

select avg(age) from ky20 where id <=5;
1

mysql> select avg(age) from ky20 where id <=5;
+----------+
| avg(age) |
+----------+
|  40.2000 |
+----------+
1
2
3
4
5
6

查询年龄大于平均数的人员数据

select * from ky20 where age>(select avg(age) from ky20 );
1

mysql> select * from ky20 where age>(select avg(age) from ky20 );
+----+--------+----------+---------+------+--------+
| id | name   | cardid   | address | age  | hobby  |
+----+--------+----------+---------+------+--------+
|  2 | 李四   |  3213133 | 北京    |   90 | 打人   |
|  3 | 王五   | 54353321 | 上海    |   40 | 烫头   |
|  5 | 辉哥   |   676123 | 地球    |   32 | 禅悟   |
+----+--------+----------+---------+------+--------+	
1
2
3
4
5
6
7
8

四、Limit限制

​ 使用限制显示的行数可以限制指定的行数，也可以显示你想看的几行到几行

​ （1）显示前三行

select * from ky20 limit 3;
1

mysql> select * from ky20 limit 3;
+----+--------+----------+---------+------+-----------+
| id | name   | cardid   | address | age  | hobby     |
+----+--------+----------+---------+------+-----------+
|  1 | 张三   |  2233123 | 南京    |   18 | 睡懒觉    |
|  2 | 李四   |  3213133 | 北京    |   90 | 打人      |
|  3 | 王五   | 54353321 | 上海    |   40 | 烫头      |
+----+--------+----------+---------+------+-----------+

1
2
3
4
5
6
7
8
9

​ （2）显示从4行开始（不包含四行）往后2行

select * from ky20 limit 4,2;
1

mysql> select * from ky20 limit 4,2;
+----+--------+--------+---------+------+--------+
| id | name   | cardid | address | age  | hobby  |
+----+--------+--------+---------+------+--------+
|  5 | 辉哥   | 676123 | 地球    |   32 | 禅悟   |
|  6 | 牛牛   | 532432 | 火星    |   22 | 钓鱼   |
+----+--------+--------+---------+------+--------+
1
2
3
4
5
6
7

五、设置别名（alias as）

​ 在查询长字段或多表查询防止冲突时，可以设置别名来防止冲突，也可以针对显示的字段进行

​ （1）为查询结果添加别名（只对结果修改，无法修改表）

select age as 年龄 from ky20;
1

mysql> select age as 年龄 from ky20;
+--------+
| 年龄   |
+--------+
|     18 |
|     90 |
|     40 |
|     21 |
|     32 |
|     22 |
|      6 |
|     16 |
|     18 |
+--------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

​ （2）记数以别名显示

​ 统计年龄大于20岁的人数

select count(*) as 人数 from ky20 where age >20;
1

mysql> select count(*) as 人数 from ky20 where age >20;
+--------+
| 人数   |
+--------+
|      5 |
+--------+

1
2
3
4
5
6
7

​ （3）利用as将数据导入到另一个表内

​ 将查询到的值赋予前面那个表（sql从左往右的顺序应该是创建一个表，然后赋值，赋的值来源于as后的语句）

 create table ceshi as select id,name,age from ky20;
1

mysql> create table ceshi as select id,name,age from ky20;
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> select * from ceshi;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | 张三       |   18 |
|  2 | 李四       |   90 |
|  3 | 王五       |   40 |
|  4 | 赵六       |   21 |
|  5 | 辉哥       |   32 |
|  6 | 牛牛       |   22 |
|  7 | 羊峰       |    6 |
|  8 | 测试员1    |   16 |
|  9 | 张鹏程     |   18 |
+----+------------+------+
9 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

当然这只是单纯的将值赋予过去了，单价结构没改，并没有复制表结构

mysql> desc ceshi;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(2)      | NO   |     | 0       |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int(10)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1
2
3
4
5
6
7
8

对表设置别名（后面多表查询时用，防止字段相同时会冲突）

select * from ky20 k #as作为关键字可以省略，如果不省略则添加在表名后
1

六、通配符

​ mysql的通配符有三种% _ escape ,通常搭配模糊查询使用

​ _ 类似.匹配单个字符

​ % 类似.+匹配一个或多个字符

​ 匹配两个字符

select * from ky20 where name like '__'; 
1

mysql> select * from ky20 where name like '__';
+----+--------+----------+---------+------+-----------+
| id | name   | cardid   | address | age  | hobby     |
+----+--------+----------+---------+------+-----------+
|  1 | 张三   |  2233123 | 南京    |   18 | 睡懒觉    |
|  2 | 李四   |  3213133 | 北京    |   90 | 打人      |
|  3 | 王五   | 54353321 | 上海    |   40 | 烫头      |
|  4 | 赵六   |   546654 | 黑河    |   21 | 吃大鹅    |
|  5 | 辉哥   |   676123 | 地球    |   32 | 禅悟      |
|  6 | 牛牛   |   532432 | 火星    |   22 | 钓鱼      |
|  7 | 羊峰   |   867212 | 火星    |    6 | 吃杨      |
+----+--------+----------+---------+------+-----------+
1
2
3
4
5
6
7
8
9
10
11
12

​ 匹配中间有1的数据

select * from ky20 where cardid like '%1%';
1

mysql> select * from ky20 where cardid like '%1%'; #以任意开头，以任意结尾，中间匹配字符1
+----+------------+----------+---------+------+-----------+
| id | name       | cardid   | address | age  | hobby     |
+----+------------+----------+---------+------+-----------+
|  1 | 张三       |  2233123 | 南京    |   18 | 睡懒觉    |
|  2 | 李四       |  3213133 | 北京    |   90 | 打人      |
|  3 | 王五       | 54353321 | 上海    |   40 | 烫头      |
|  5 | 辉哥       |   676123 | 地球    |   32 | 禅悟      |
|  7 | 羊峰       |   867212 | 火星    |    6 | 吃杨      |
|  8 | 测试员1    |  2233123 | 月球    |   16 | 跳海      |
+----+------------+----------+---------+------+-----------+

1
2
3
4
5
6
7
8
9
10
11
12

七、子查询

​ 子查询也叫嵌套查询，sql运行的顺序则是先运行子查询后从左往右，子查询可以查询相同的表也可以查询不同的表

​ 通过查询测试表id<4的id作为条件来查询ky20表

select * from ky20 where id in (select id from ceshi where id<4);
1

mysql> select * from ky20 where id in (select id from ceshi where id<4);
+----+--------+----------+---------+------+-----------+
| id | name   | cardid   | address | age  | hobby     |
+----+--------+----------+---------+------+-----------+
|  1 | 张三   |  2233123 | 南京    |   18 | 睡懒觉    |
|  2 | 李四   |  3213133 | 北京    |   90 | 打人      |
|  3 | 王五   | 54353321 | 上海    |   40 | 烫头      |
+----+--------+----------+---------+------+-----------+
1
2
3
4
5
6
7
8

​ 多表查询

​ 我先在有两张表，一张信息表，一张为成绩表，我要查找及格的人的具体信息

mysql> select * from chenji;
+----+------------+-------+
| id | name       | score |
+----+------------+-------+
|  1 | 张鹏程     |    40 |
|  2 | 测试员1    |    60 |
|  3 | 羊峰       |    70 |
|  4 | 牛牛       |    10 |
|  5 | 辉哥       |   100 |
|  6 | 赵六       |    80 |
|  7 | 王五       |    20 |
|  8 | 李四       |    55 |
|  9 | 张三       |    66 |
+----+------------+-------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

 select *  from ky20  where name in (select name from chenji where score >=60 );
1

mysql> select *  from ky20  where name in (select name from chenji where score >=60 );
+----+------------+---------+---------+------+-----------+
| id | name       | cardid  | address | age  | hobby     |
+----+------------+---------+---------+------+-----------+
|  1 | 张三       | 2233123 | 南京    |   18 | 睡懒觉    |
|  4 | 赵六       |  546654 | 黑河    |   21 | 吃大鹅    |
|  5 | 辉哥       |  676123 | 地球    |   32 | 禅悟      |
|  7 | 羊峰       |  867212 | 火星    |    6 | 吃杨      |
|  8 | 测试员1    | 2233123 | 月球    |   16 | 跳海      |
+----+------------+---------+---------+------+-----------+
1
2
3
4
5
6
7
8
9
10

当然还可以取反 只要将in 改成not in 就可以了

select *  from ky20  where name not in (select name from chenji where score >=60 );
1

mysql> select *  from ky20  where name not in (select name from chenji where score >=60 );
+----+-----------+----------+---------+------+--------+
| id | name      | cardid   | address | age  | hobby  |
+----+-----------+----------+---------+------+--------+
|  2 | 李四      |  3213133 | 北京    |   90 | 打人   |
|  3 | 王五      | 54353321 | 上海    |   40 | 烫头   |
|  6 | 牛牛      |   532432 | 火星    |   22 | 钓鱼   |
|  9 | 张鹏程    |   532432 | 南通    |   18 | 周妍   |
+----+-----------+----------+---------+------+--------+
1
2
3
4
5
6
7
8
9

使用in作为条件update更新

我们通过查询成绩出大于等于60分的人赋给信息表给予及格

 #先添加一个通过字段默认为不合格
mysql> alter table ky20 add acc varchar(20) not null default '不合格';
mysql> select * from ky20;
+----+------------+----------+---------+------+-----------+-----------+
| id | name       | cardid   | address | age  | hobby     | acc       |
+----+------------+----------+---------+------+-----------+-----------+
|  1 | 张三       |  2233123 | 南京    |   18 | 睡懒觉    | 不合格    |
|  2 | 李四       |  3213133 | 北京    |   90 | 打人      | 不合格    |
|  3 | 王五       | 54353321 | 上海    |   40 | 烫头      | 不合格    |
|  4 | 赵六       |   546654 | 黑河    |   21 | 吃大鹅    | 不合格    |
|  5 | 辉哥       |   676123 | 地球    |   32 | 禅悟      | 不合格    |
|  6 | 牛牛       |   532432 | 火星    |   22 | 钓鱼      | 不合格    |
|  7 | 羊峰       |   867212 | 火星    |    6 | 吃杨      | 不合格    |
|  8 | 测试员1    |  2233123 | 月球    |   16 | 跳海      | 不合格    |
|  9 | 张鹏程     |   532432 | 南通    |   18 | 周妍      | 不合格    |
+----+------------+----------+---------+------+-----------+-----------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

然后我们再用update修改

update ky20 set acc='合格' where name in (select name from chenji where score >=60)
1

mysql> update ky20 set acc='合格' where name in (select name from chenji where score >=60);
mysql> select * from ky20;
+----+------------+----------+---------+------+-----------+-----------+
| id | name       | cardid   | address | age  | hobby     | acc       |
+----+------------+----------+---------+------+-----------+-----------+
|  1 | 张三       |  2233123 | 南京    |   18 | 睡懒觉    | 合格      |
|  2 | 李四       |  3213133 | 北京    |   90 | 打人      | 不合格    |
|  3 | 王五       | 54353321 | 上海    |   40 | 烫头      | 不合格    |
|  4 | 赵六       |   546654 | 黑河    |   21 | 吃大鹅    | 合格      |
|  5 | 辉哥       |   676123 | 地球    |   32 | 禅悟      | 合格      |
|  6 | 牛牛       |   532432 | 火星    |   22 | 钓鱼      | 不合格    |
|  7 | 羊峰       |   867212 | 火星    |    6 | 吃杨      | 合格      |
|  8 | 测试员1    |  2233123 | 月球    |   16 | 跳海      | 合格      |
|  9 | 张鹏程     |   532432 | 南通    |   18 | 周妍      | 不合格    |
+----+------------+----------+---------+------+-----------+-----------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

还可以删除新增都大差不差

八、exists

判断子查询语句是否为空，如果为空则返回flase（前面语句将不运行）如果true（则正常运行）

select count(*) from report where exists(select id from report where score=59);
1

九、链接语法（join）

两表链接查询

我们现在有两张表，一张具体信息表，一张成绩超过60的表，他们两个有相同的字段name，我们通过name连接进行多表查询

select t1.name,t2.score from ky20 t1 join new t2 on t1.name=t2.name;
1

mysql> select t1.name,t2.score from ky20 t1 join new t2 on t1.name=t2.name;
+--------+-------+
| name   | score |
+--------+-------+
| 张三   |    66 |
| 赵六   |    80 |
| 辉哥   |   100 |
| 羊峰   |    70 |
+--------+-------+
我们可以看见t1是ky20表，t2是new表，join为链接的意思on则表示基于什么进行查询
1
2
3
4
5
6
7
8
9
10

对两个表进行拼接

左链接

select * from ky20 k left join new n on k.name=n.name;
1

mysql> select * from ky20 k left join new n on k.name=n.name;
+----+------------+----------+---------+------+-----------+-----------+--------+-------+
| id | name       | cardid   | address | age  | hobby     | acc       | name   | score |
+----+------------+----------+---------+------+-----------+-----------+--------+-------+
|  7 | 羊峰       |   867212 | 火星    |    6 | 吃杨      | 合格      | 羊峰   |    70 |
|  5 | 辉哥       |   676123 | 地球    |   32 | 禅悟      | 合格      | 辉哥   |   100 |
|  4 | 赵六       |   546654 | 黑河    |   21 | 吃大鹅    | 合格      | 赵六   |    80 |
|  1 | 张三       |  2233123 | 南京    |   18 | 睡懒觉    | 合格      | 张三   |    66 |
|  2 | 李四       |  3213133 | 北京    |   90 | 打人      | 不合格    | NULL   |  NULL |
|  3 | 王五       | 54353321 | 上海    |   40 | 烫头      | 不合格    | NULL   |  NULL |
|  6 | 牛牛       |   532432 | 火星    |   22 | 钓鱼      | 不合格    | NULL   |  NULL |
|  8 | 测试员1    |  2233123 | 月球    |   16 | 跳海      | 合格      | NULL   |  NULL |
|  9 | 张鹏程     |   532432 | 南通    |   18 | 周妍      | 不合格    | NULL   |  NULL |
+----+------------+----------+---------+------+-----------+-----------+--------+-------+

#很明显从左往右，右表只有匹配左表才会显示，
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

右链接

select * from ky20 k right join new n on k.name=n.name;
1

mysql> select * from ky20 k right join new n on k.name=n.name;
+------+--------+---------+---------+------+-----------+--------+--------+-------+
| id   | name   | cardid  | address | age  | hobby     | acc    | name   | score |
+------+--------+---------+---------+------+-----------+--------+--------+-------+
|    1 | 张三   | 2233123 | 南京    |   18 | 睡懒觉    | 合格   | 张三   |    66 |
|    4 | 赵六   |  546654 | 黑河    |   21 | 吃大鹅    | 合格   | 赵六   |    80 |
|    5 | 辉哥   |  676123 | 地球    |   32 | 禅悟      | 合格   | 辉哥   |   100 |
|    7 | 羊峰   |  867212 | 火星    |    6 | 吃杨      | 合格   | 羊峰   |    70 |
+------+--------+---------+---------+------+-----------+--------+--------+-------+
#右查询以右表 new为基础给左表匹配

1
2
3
4
5
6
7
8
9
10
11

内链接

匹配两个表相同的

select * from ky20 k inner join new n on k.name=n.name;
1

十、视图

​ 视图是一张虚拟的表，这张虚拟表中不包含真实数据，只是做了真实数据的映射。我们可以创建时候对sql语句进行限制从而对用户所查询的目标进行限制。

​ 创建单表视图

create view v_ky20 as select name,age from ky20;
1

mysql> create view v_ky20
    -> as
    -> select name,age from ky20;
 创建一个视图，显示的数据只有年龄和姓名
1
2
3
4

mysql> select * from v_ky20;
+------------+------+
| name       | age  |
+------------+------+
| 张三       |   18 |
| 李四       |   90 |
| 王五       |   40 |
| 赵六       |   21 |
| 辉哥       |   32 |
| 牛牛       |   22 |
| 羊峰       |    6 |
| 测试员1    |   16 |
| 张鹏程     |   18 |
+------------+------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

​ 对表视图使用as创建字段别名

mysql> select name '姓名' , age '年龄' from v_ky20;
+------------+--------+
| 姓名       | 年龄   |
+------------+--------+
| 张三       |     18 |
| 李四       |     90 |
| 王五       |     40 |
| 赵六       |     21 |
| 辉哥       |     32 |
| 牛牛       |     22 |
| 羊峰       |      6 |
| 测试员1    |     16 |
| 张鹏程     |     18 |
+------------+--------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

​ 设置视图字段，并将我们查询出的数据插入相应的字段中

mysql> create view v_ky202 (v_id,v_name) as select id ,name from ky20;
mysql> select * from v_ky202;
+------+------------+
| v_id | v_name     |
+------+------------+
|    1 | 张三       |
|    2 | 李四       |
|    3 | 王五       |
|    4 | 赵六       |
|    5 | 辉哥       |
|    6 | 牛牛       |
|    7 | 羊峰       |
|    8 | 测试员1    |
|    9 | 张鹏程     |
+------+------------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16