mysql 高级查询语句（2)）

转载

mob604756f0266e 2021-09-07 22:02:00

文章标签 字段 mysql 子查询字段名 sql语句 文章分类 代码人生

mysql 高级查询语句（2)

1 子查询
2 exists 测试查询布尔值是否为真
3 表连接查询
4 view 试图
- 4.1 概念
5 union 联集
6 取交集值(去重）
7 无交集
8 case
- 8.1 简单函数
- 8.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;

mysql 高级查询语句（2)）_字段名

3.3 right join右连接查询

返回包括右表中的所有记录和左边中联结字段相等的记录

 select * from school.project  a  right  join school.class  b on a.name=b.name;

mysql 高级查询语句（2)）_子查询_02

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;

mysql 高级查询语句（2)）_字段_03

mysql 高级查询语句（2)）_sql语句_04

mysql 高级查询语句（2)）_sql语句_05

mysql 高级查询语句（2)）_sql语句_06

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;

mysql 高级查询语句（2)）_mysql_07

mysql 高级查询语句（2)）_sql语句_08

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);

mysql 高级查询语句（2)）_sql语句_09

mysql 高级查询语句（2)）_字段名_10

mysql 高级查询语句（2)）_字段名_11

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;

mysql 高级查询语句（2)）_mysql_12

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;

mysql 高级查询语句（2)）_子查询_13

6.4 方法四使用左联或者右联配合distinct去重

select distinct  A.name from class A 
  left join project B using(name) where B.name is not null;

mysql 高级查询语句（2)）_mysql_14

mysql 高级查询语句（2)）_字段_15

6.5 方法五：使用distinct 配合 where in过滤和子查询

select distinct  name from class where name in (select name from project);

mysql 高级查询语句（2)）_字段名_16

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;
 
 #这里没有将查询结果分组显示，

mysql 高级查询语句（2)）_字段_17

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)

mysql 高级查询语句（2)）_子查询_18