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 | 周妍 |
+----+------------+----------+---------+------+-----------+
此时我们排序，以年龄和id作为条件进行多条件排序
select * from 表名 order by 字段1 升降序 ,字段2 升降序;
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 | 吃杨 |
+----+------------+----------+---------+------+-----------+
当然我们还可以再追加条件来查询
select * from 表名 where 条件 order by 字段1 升降序 ,字段2 升降序;
二、查询不重复记录
? 指定查找列中不重复的数据
select distinct 去重的列 from 表明;
mysql> select distinct cardid from ky20;
+----------+
| cardid |
+----------+
| 2233123 |
| 3213133 |
| 54353321 |
| 546654 |
| 676123 |
| 532432 |
| 867212 |
+----------+
三、分组以及聚合函数
? 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）计数（COUNT）
select count(*) from ky20
统计表内有几条数据
mysql> select count(*) from ky20;
+----------+
| count(*) |
+----------+
| 9 |
+----------+
统计20岁以上的人的个数
select count(*),age from ky20 where age >20 group by age;
mysql> select count(*),age from ky20 where age >20 group by age;
+----------+------+
| count(*) | age |
+----------+------+
| 1 | 21 |
| 1 | 22 |
| 1 | 32 |
| 1 | 40 |
| 1 | 90 |
+----------+------+
? （2）平均数
统计前五个（包含5）人员的平均数
select avg(age) from ky20 where id <=5;
mysql> select avg(age) from ky20 where id <=5;
+----------+
| avg(age) |
+----------+
| 40.2000 |
+----------+
查询年龄大于平均数的人员数据
select * from ky20 where age>(select avg(age) from ky20 );
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 | 禅悟 |
+----+--------+----------+---------+------+--------+
四、Limit限制
? 使用限制显示的行数可以限制指定的行数，也可以显示你想看的几行到几行
? （1）显示前三行
select * from ky20 limit 3;
mysql> select * from ky20 limit 3;
+----+--------+----------+---------+------+-----------+
| id | name | cardid | address | age | hobby |
+----+--------+----------+---------+------+-----------+
| 1 | 张三 | 2233123 | 南京 | 18 | 睡懒觉 |
| 2 | 李四 | 3213133 | 北京 | 90 | 打人 |
| 3 | 王五 | 54353321 | 上海 | 40 | 烫头 |
+----+--------+----------+---------+------+-----------+
? （2）显示从4行开始（不包含四行）往后2行
select * from ky20 limit 4,2;
mysql> select * from ky20 limit 4,2;
+----+--------+--------+---------+------+--------+
| id | name | cardid | address | age | hobby |
+----+--------+--------+---------+------+--------+
| 5 | 辉哥 | 676123 | 地球 | 32 | 禅悟 |
| 6 | 牛牛 | 532432 | 火星 | 22 | 钓鱼 |
+----+--------+--------+---------+------+--------+
五、设置别名（alias as）
? 在查询长字段或多表查询防止冲突时，可以设置别名来防止冲突，也可以针对显示的字段进行
? （1）为查询结果添加别名（只对结果修改，无法修改表）
select age as 年龄 from ky20;
mysql> select age as 年龄 from ky20;
+--------+
| 年龄 |
+--------+
| 18 |
| 90 |
| 40 |
| 21 |
| 32 |
| 22 |
| 6 |
| 16 |
| 18 |
+--------+
? （2）记数以别名显示
? 统计年龄大于20岁的人数
select count(*) as 人数 from ky20 where age >20;
mysql> select count(*) as 人数 from ky20 where age >20;
+--------+
| 人数 |
+--------+
| 5 |
+--------+
? （3）利用as将数据导入到另一个表内
? 将查询到的值赋予前面那个表（sql从左往右的顺序应该是创建一个表，然后赋值，赋的值来源于as后的语句）
create table ceshi as select id,name,age from ky20;
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)
当然这只是单纯的将值赋予过去了，单价结构没改，并没有复制表结构
mysql> desc ceshi;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(2) | NO | | 0 | |
| name | varchar(10) | YES | | NULL | |
| age | int(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
对表设置别名（后面多表查询时用，防止字段相同时会冲突）
select * from ky20 k #as作为关键字可以省略，如果不省略则添加在表名后
六、通配符
? mysql的通配符有三种% _ escape ,通常搭配模糊查询使用
? _ 类似.匹配单个字符
? % 类似.+匹配一个或多个字符
? 匹配两个字符
select * from ky20 where name like '__';
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的数据
select * from ky20 where cardid like '%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 | 跳海 |
+----+------------+----------+---------+------+-----------+
七、子查询
? 子查询也叫嵌套查询，sql运行的顺序则是先运行子查询后从左往右，子查询可以查询相同的表也可以查询不同的表
? 通过查询测试表id<4的id作为条件来查询ky20表
select * from ky20 where id in (select id from ceshi where id<4);
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 | 烫头 |
+----+--------+----------+---------+------+-----------+
? 多表查询
? 我先在有两张表，一张信息表，一张为成绩表，我要查找及格的人的具体信息
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 |
+----+------------+-------+
select * from ky20 where name in (select name from chenji where score >=60 );
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 | 跳海 |
+----+------------+---------+---------+------+-----------+
当然还可以取反 只要将in 改成not in 就可以了
select * from ky20 where name not in (select name from chenji where score >=60 );
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 | 周妍 |
+----+-----------+----------+---------+------+--------+
使用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 | 周妍 | 不合格 |
+----+------------+----------+---------+------+-----------+-----------+
然后我们再用update修改
update ky20 set acc='合格' where name in (select name from chenji where score >=60)
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 | 周妍 | 不合格 |
+----+------------+----------+---------+------+-----------+-----------+
还可以删除新增都大差不差
八、exists
判断子查询语句是否为空，如果为空则返回flase（前面语句将不运行）如果true（则正常运行）
select count(*) from report where exists(select id from report where score=59);
九、链接语法（join）
两表链接查询
我们现在有两张表，一张具体信息表，一张成绩超过60的表，他们两个有相同的字段name，我们通过name连接进行多表查询
select t1.name,t2.score from ky20 t1 join new t2 on t1.name=t2.name;
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则表示基于什么进行查询
对两个表进行拼接
左链接
select * from ky20 k left join new n on k.name=n.name;
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 |
+----+------------+----------+---------+------+-----------+-----------+--------+-------+
#很明显从左往右，右表只有匹配左表才会显示，
右链接
select * from ky20 k right join new n on k.name=n.name;
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为基础给左表匹配
内链接
匹配两个表相同的
select * from ky20 k inner join new n on k.name=n.name;
十、视图
? 视图是一张虚拟的表，这张虚拟表中不包含真实数据，只是做了真实数据的映射。我们可以创建时候对sql语句进行限制从而对用户所查询的目标进行限制。
? 创建单表视图
create view v_ky20 as select name,age from ky20;
mysql> create view v_ky20
-> as
-> select name,age from ky20;
创建一个视图，显示的数据只有年龄和姓名
mysql> select * from v_ky20;
+------------+------+
| name | age |
+------------+------+
| 张三 | 18 |
| 李四 | 90 |
| 王五 | 40 |
| 赵六 | 21 |
| 辉哥 | 32 |
| 牛牛 | 22 |
| 羊峰 | 6 |
| 测试员1 | 16 |
| 张鹏程 | 18 |
+------------+------+
? 对表视图使用as创建字段别名
mysql> select name '姓名' , age '年龄' from v_ky20;
+------------+--------+
| 姓名 | 年龄 |
+------------+--------+
| 张三 | 18 |
| 李四 | 90 |
| 王五 | 40 |
| 赵六 | 21 |
| 辉哥 | 32 |
| 牛牛 | 22 |
| 羊峰 | 6 |
| 测试员1 | 16 |
| 张鹏程 | 18 |
+------------+--------+
? 设置视图字段，并将我们查询出的数据插入相应的字段中
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 | 张鹏程 |
+------+------------+
|