mysql 高级查询语句（2)）
mysql 高级查询语句（2)
1 子查询
连接表格，在WHERE 子句或HAVING 子句中插入另一个SQL语句
格式
select 字段名1 from 表格1 where 字段2 [比较运算符] #子查询的外查询部分
(select 字段1 from 表2 where 条件2 )； #子查询的内查询部分
可以是符号的运算符，例如 =、>、<、>=、<=；也可以是文字的运算符，例如 LIKE、IN、BETWEEN
# 以project表name字段分组，project表name字段值要能够在在school.class表中查询name字段中找到，然后查询 project 表中name字段值和score 平均值。
mysql> select name,avg(score) from school.project group by name
-> having name in (select name from school.class);
+-------------+------------+
| name | avg(score) |
+-------------+------------+
| houlu | 90.0000 |
| linrui | 80.0000 |
| wanglei | 66.6667 |
| zhoujiazhen | 66.6667 |
+-------------+------------+
4 rows in set (0.00 sec)
mysql> select name,avg(score) from school.project a group by a.name havingg a.name in (select b.name from school.class b);
+-------------+------------+
| name | avg(score) |
+-------------+------------+
| houlu | 90.0000 |
| linrui | 80.0000 |
| wanglei | 66.6667 |
| zhoujiazhen | 66.6667 |
+-------------+------------+
4 rows in set (0.00 sec)
2 exists 测试查询布尔值是否为真
用来测试内查询有没有产生任何结果，类似布尔值是否为真
如果有的话，系统就会执行外查询的SQL语句，若是没有的话，那整个SQL语句就不会产生任何结果
语法：SELECT “栏位” FROM “表格1” WHERE EXISTS (SELECT * FROM "表格2" WHERE “条件”)；
#如果school.class里有id值为1 的，则查看 school.project表
select * from school.project where exists ( select * from school.class where id=1);
3 表连接查询
(1)格式1
select 字段 from 表1 联结查询关键字 表2 on 表1.表字段=表2.表字段
(2) 格式2
select 字段 from 表1 联结查询关键字 表2 using(表字段)
3.1 inner join（内连接）
只返回两个表中联结字段相等的行
# 查看school.class 表 name 字段 和 school.project 表 name字段相同的行
mysql> select * from school.class a inner join school.project b on a.name=b.name;
或者 select * from school.class a inner join school.project b using(name);
+----+-------------+---------+-------------+-------+
| id | name | project | name | score |
+----+-------------+---------+-------------+-------+
| 7 | houlu | chanese | houlu | 100 |
| 5 | linrui | chanese | linrui | 80 |
| 3 | wanglei | chanese | wanglei | 40 |
| 6 | zhoujiazhen | chanese | zhoujiazhen | 60 |
| 6 | zhoujiazhen | math | zhoujiazhen | 60 |
| 5 | linrui | math | linrui | 60 |
| 3 | wanglei | math | wanglei | 100 |
| 7 | houlu | math | houlu | 90 |
| 7 | houlu | english | houlu | 80 |
| 3 | wanglei | english | wanglei | 60 |
| 6 | zhoujiazhen | english | zhoujiazhen | 80 |
| 5 | linrui | english | linrui | 100 |
+----+-------------+---------+-------------+-------+
12 rows in set (0.00 sec)
mysql> select * from school.class a inner join school.project b using(name);
+-------------+----+---------+-------+
| name | id | project | score |
+-------------+----+---------+-------+
| houlu | 7 | chanese | 100 |
| linrui | 5 | chanese | 80 |
| wanglei | 3 | chanese | 40 |
| zhoujiazhen | 6 | chanese | 60 |
| zhoujiazhen | 6 | math | 60 |
| linrui | 5 | math | 60 |
| wanglei | 3 | math | 100 |
| houlu | 7 | math | 90 |
| houlu | 7 | english | 80 |
| wanglei | 3 | english | 60 |
| zhoujiazhen | 6 | english | 80 |
| linrui | 5 | english | 100 |
+-------------+----+---------+-------+
12 rows in set (0.00 sec)
3.2 left join 左连接
返回包括左边中的所有记录和右表中联结字段相等的记录
select * from school.class a left join school.project b on a.name=b.name;
3.3 right join右连接查询
返回包括右表中的所有记录和左边中联结字段相等的记录
select * from school.project a right join school.class b on a.name=b.name;
4 view 试图
4.1 概念
- 可以被当作是虚拟表或存储查询
- 视图跟表格的不同是，表格中有实际储存资料，而视图是建立在表格之上的一个架构，它本身并不实际储存资料。
- 视图表的内容，是由select 查询语句的结果决定的,所以，查询的结果不能有相同的字段名
- 临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失。
- 视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。比如你要对几个表进行连接查询，而且还要进行统计排序等操作，写SQL语句会很麻烦的，用视图将几个表联结起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便。
语法：
(1）创建视图
create view 视图名 as 'select 语句'；
(2) 查看有那些视图
show table status where comment='view' \G
(3) 删除视图
drop view 视图名；
#创建 视图
mysql> create view V_avg_score as
-> select name,avg(score) from school.project
-> group by name
-> having name in (select name from school.class);
#查看视图表内容
select * from V_avf_score
#查看所有的视图表
show table status where comment='view' \G
#删除视图表 V_avg_score
drop view V_avg_score;
5 union 联集
联集，将连个sql 语句的结果合并起来,两个sql语句所产生的栏位需要是同样的资料种类
语法
(1) union: 生成结果的资料值将没有重复，且按照字段的顺序进行排序
[select 语句1 ] union [select 语句2 ]；
(2) unio all： 将生成结果的资料值都列出来，无论有无重复
[ select 语句1 ] union all [select 语句2];
#使用union 联集有去重的效果
mysql> select name from class union select name from project;
#使用union all 联集只是将前后查询结果联集在一起，并不去重
mysql> select name from class union all select name from project;
#不管是union 还是union all ，前后查询的字段值类型不同，并不影响合并
mysql> select id from class union select name from project;
6 取交集值(去重）
交集值，取两个sql 语句结果的交集
6.1 方法一： inner join 内联与 on/where/using 一起用取交集使用distinct去重
# 内联和on/where/using 一起使用取交集，如果想要去重，则在加上distinct
select class.name from class inner join project where class.name=project.name;
select class.name from class inner join project on class.name=project.name;
select class.name from class inner join project using(name);
#加上distinct 去重
select distinct class.name from class inner join project using(name);
6.2 方法二：使用子查询 和union all 联集 配合group by 配合count函数 取交集并去重
此方法，当一张表里有重复，而另一张里却又没有，就会统计出错，需要先使用 distinct 去重然后再联集
select A.name from ( select name from class
union all select name from project ) A group by A.name having count(*) >1;
6.4 方法三 使用子查询结合内联，然后给group by 分组
select A.name from
( select B.name from class B inner join project C where B.name=C.name) A
group by A.name;
6.4 方法四 使用左联或者右联配合distinct去重
select distinct A.name from class A
left join project B using(name) where B.name is not null;
6.5 方法五：使用distinct 配合 where in过滤和 子查询
select distinct name from class where name in (select name from project);
7 无交集
无交集是现实第一个sql语句的结果，且与第二个sql语句的没有交集的结果，且没有重复
#方法一
mysql> select distinct name from class where name not in (select name from project);
+----------+
| name |
+----------+
| zhangsan |
| lisi |
| dingcong |
+----------+
3 rows in set (0.00 sec)
#方法二：
mysql> select distinct A.name from class A left join project B using(name) where B.name is null;
+----------+
| name |
+----------+
| zhangsan |
| lisi |
| dingcong |
+----------+
3 rows in set (0.00 sec)
#方法三：
mysql> select A.name from
(select distinct name from class union all select distinct name from project) A
group by A.name having count(*)=1;
+----------+
| name |
+----------+
| dingcong |
| lisi |
| zhangsan |
+----------+
3 rows in set (0.00 sec)
8 case
case 是sql 用来做if-then -else 之类逻辑的关键字
语法（1）：
select 字段名 ， case 字段名
when 条件1 then 结果
when 条件2 then 结果
else 结果
end 新字段名
from 表名 ；
条件/结果 可以是一个数值或这公式，else 字句和 新字段名不是必须的。
语法（2:)
select 字段名 ， case
when 条件1 then 结果
when 条件2 then 结果
else 结果
end 新字段名
from 表名;
8.1 简单函数
mysql> select
-> name '名字' ,
-> case name
-> when 'linrui' then
-> '扫地'
-> when 'zhoujiazhen' then
-> '拖地'
-> else
-> '擦窗子'
-> end '工作'
-> from
-> project;
#这里没有将查询结果分组显示，
8.2 搜索函数
mysql> select
-> name '名字' ,
-> avg(score) '平均分' ,
-> case
-> when avg(score) < 60 then
-> '打屁股'
-> when avg(score) <70 then
-> '打手心'
-> when avg(score) <80 then
-> '鼓励鼓励'
-> when avg(score) <90 then
-> '还不错，奖励一根棒棒糖'
-> else
-> '优秀，非常好，奖励三本练习题'
-> end '期末考试奖惩'
-> from
-> project group by name;
+-------------+-----------+--------------------------------------------+
| 名字 | 平均分 | 期末考试奖惩 |
+-------------+-----------+--------------------------------------------+
| houlu | 90.0000 | 优秀，非常好，奖励三本练习题 |
| linrui | 80.0000 | 还不错，奖励一根棒棒糖 |
| wanglei | 66.6667 | 打手心 |
| zhoujiazhen | 66.6667 | 打手心 |
+-------------+-----------+--------------------------------------------+
4 rows in set (0.00 sec)
