MySQL的高阶语句

前言

数据库是用来存储数据，更新，查询数据的工具，而查询数据是一个数据库最为核心的功能，数据库是用来承载信息，而信息是用来分析和查看的。所以掌握更为精细化的查询方式是很有必要的。本文将围绕数据的高级查询语句展开。 

排序、分组、子查询、视图、多表查询（左连接、右连接、内连接）

查询语法

使用select语句，用order by 来对表进行排序

ASC：升序排列，默认，可以不加

DESC：降序排列，需要添加

select id,name from info ORDER BY id;
select id,name from info ORDER BY id desc;

排序时，尽量用数字列排序，文字的话会将相同的排在一起，其他没什么区别

order by 结合where条件过滤

select name,score from info where address='南京西路' order by score desc;

例题：查id 姓名 成绩，根据性别都是等于女的然后按照id进行降序排列

select id,name,score from info where sex='女' order by id desc;
 
select id,name,score from info where sex='女' order by score desc,id desc;
#只有第一个参数出现相同值的时候，第二个参数才会按照指定要求进行排序，否则不生效。

and/or  且和或

select * from info;
select * from info where score > 70 and score <=90;

例如：找分数大于80，小于90的项

select * from info where score >80 or score <90;

嵌套条件

select * from info where score > 70 and (score >75 and score <90);

select * from info where score > 70 or (score >75 and score <90);

例题：嵌套条件，满足性别是男，在次基础上筛选成绩在80-90之间

select * from info where sex='男' and (score >70 and score <90);

去重查询

select distinct address from info;
select distinct sex from info;

例题：根据地址去重，过滤出成绩等于90且性别是男

select distinct address from info where sex='男' and (score =90);

group by 语句

分组的时候可以按照一个字段，也可以按照多个字段对结果进行分组处理。

语法：

语法：
select count(name),sex from info group by sex;

例题：根据where条件筛选， score >=80

select count(name),score,sex,name from info where score >=80 group by sex;

例题：以地址为分组，对score求和

select sum(score),address from info group by address;

例题：算出男生女生的平均成绩

select avg(score),sex from info group by sex;

例题：分别求出男生组和女生组的成绩最低的人

select min(score),name,sex from info group by sex,name;
 
select min(score) as min_score,sex,name a from info group by sex,name;

group by实现条件过滤

group by 不可用使用where只能使用having语句实现条件过滤

select avg(score),address from info group by address having avg(score) > 60;

例题：

用地址分组，id倒叙排列求成绩平均值>50

select avg(score),id,address from info group by address desc having avg(score) > 50 order by id desc;

例题：统计name的行数，计算出学生的个数，把成绩也查出来，按照统计出来的学生个数，升序排列。按照地址分组，学生的成绩>=70

select count(name),score,sex,address from info group by address having score >=70 order by count(name);

例题：按照性别分组，求出男生女生的最大成绩，最大成绩是否超过75分满足条件的过滤出来

select max(score),sex from info group by sex having max(score) >75;

group by语句

一般是结合聚合函数一起使用

使用聚合函数必须要加group by 分组的条件要进行筛选，要选用多个重复值的列，过滤条件要用having语句过滤条件。

limit 限制输出的结果记录

limit 限制输出的结果记录，查看表中的指定行

语法：

select * from info limit 3;
#只看前3行
 
select * from info limit 1,4;
#查看第2到5行

select * from info limit 5,3;
#查看6到8行

select * from info order by id desc limit 3;
#倒叙查看后3行

通配符

通配符主要用于替换字符串中的部分字符，通过部分字符的匹配将相关的结果查询出来

通配符和like一起使用，使用where语句一起来完成查询。

%:表示0个，1个或者多个

_:表示单个字符

select * from info where address like '山%';

#以山为开头

#以路为结尾

select * from info where address like '%路';
#以路为结尾

#表示内容包含 %c%

select * from info where address like '%路%';

#表示查找单个字符_

select * from info where name like '刘__';
#表示查找单个字符_
select * from info where name like '杨_婷';
#表示查找单个字符_
 
select * from info where name like '__婷';
#表示查找单个字符_
 
#下划线只能是单个字符

例题：以山开头匹配后面2个字符

select * from info where address like '山%__';

别名

设置别名：alias >> as

在MySQL 查询时，表的名字或者字段太长，可以使用别名来进行代替，方便书写，增加可读性

select name as 姓名,score as 成绩 from info;

#as可加可不加

create table test as select * from info;

#使用as语句复制表的时候约束不会被复制过来

select * from test;

desc test;

desc info;

使用as复制表，约束不会被复制过来

可以给表起别名，但是注意别名不能和数据库中的其他表重名，可能会有冲突。

列的别名在结果中可以显示，但是表的别名在结果中没有显示，只能用于查询。

MySQL--子查询

子查询：连接表格，在WHERE 子句或HAVING 子句中插入另一个SQL语句。 

简而言之，就是在原本的select语句中，嵌套一个select

格式：select.........(select....)

执行方式

括号里面的先被执行，（先于主查询）然后再把子查询的结果作为条件返回给主查询条件过滤。

select name,score from info where id in ( select * from info where score > 80 );

//子查询的语句查询的结果只能是一列（不能是*），多列就会报错

因此写成：

select name,score from info where id in ( select id from info where score > 80 )；

加粗项得一一对应

先筛选出表里成绩大于80的人，然后再将他们的name，score反馈出来

用not in 取反

select id,name,score from info where id not in (select id from info where score > 20);

例题：修改info表score=100，not in 子查询的条件是 id>1

update info set score=90 where id not in (select id from test where id > 1);
 
test表中过滤出id=1，然后id=1到info表更新成绩

例题--结合delete使用

delete from info where id in (select id where score > 80);

含义：通过id过滤出成绩大于80的项，然后在info表中删除

exists

• 用来测试内查询有没有产生任何结果，类似布尔值是否为真。
• 如果内查询有结果的话，系统就会执行外查询中的SQL语句。若是没有结果的话，那整个SQL语句就不会产生任何结果。

格式：

 SELECT "字段1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "条件");

要求：根据info 表查询出大于80分的同学，统计有多少个。

按正常逻辑，应该如上述代码一样，但是运行结果只有4个，不符合实际总数。

此时有错误，第一个符合之后，接下来的行不管符不符合都算进去了

select count(*) from info a where exists (select id from info where score > 80 and info.id=a.id);

将info复制给a，内查询的判断条件是info的id号要与查询的info的id号相等才能有值。

子查询结果要和外查询的id 相同，才不会出错

视图

MySQL当中的视图view，（是一个虚拟的表）基于查询结果得出的一个虚拟表。

在工作当中，我们查询的表未必是真表。有可能是基于真表查询的结果的一个结果的一个虚拟表，可以简化复杂的查询语句。还能隐藏表的细节，提供安全的数据访问。

创建视图表可以是一张表的结果集，也可以是多个表共同查询的结果集

格式：

create view test1 as select * from info where score >= 80;

视图表和真表的区别

1.存储方式不一样，真表存储实际数据，真正写在磁盘当中。视图不存储任何数据，仅仅是一个基于查询结果集的虚拟表

2.从数据更新的情况来说，表可以 增 删 改 查。但是视图一般情况只能用于查，展示数据。

3.占用空间，真表真实占用空间，视图不占用数据库空间。

如何查看视图

show full tables in 库名 where table_type like 'view';

如何删除视图

drop view v_score;

练习:

 
 
#练习题：infohe test01 根据info的id，name，score，加上给你test01的age
 
create view v_info(id,name,score,age) as select a.id,a.name,a.score,b.age from info a,test01 b where a.name=b.name;

基于查询的结果集，原表的数据变化，视图表的数据也会发生变化

修改了视图表的数据，原表的数据也会发生变化。一般情况下不对视图表进行改的操作。视图表一般就一个作用就是查

真表占了80%，视图适用于安全新要求比较高的场景，尤其是对外访问基本上都是视图

视图也能更新。原表数据变化，视图表的数据也会跟着一起变化

update info set score=70 where name = 'a';

更新试图表，真表也会改变。一般情况下，是不对视图表进行改的操作

update v_info set age=5000 where name = 'b';
 
select * from test01;

null值和空值的区别：

Null就是真空，空值有值，值为space

如何在表中鉴别空值和null

null值不会被统计，空值会被统计。

连接查询

1.内连接

把两张表或者多张表（不超过3张），同时符合特点条件的数据记录的组合。一个或者多个列的相同值才会有查询的结果。

select a.id,a.name from test01 a inner join info b on a.name=b.name;
 
test01里面的a  和  info 里面的b进行匹配查找判断
 
 a.id,a.name from test01：连接条件
 
a.name=b.name：判断条件

示意图：

2.左连接

左外连接，在语句中使用 left join关键字来连接。。在左连接当中，左侧表是基础表，接受左表的所有行，然后和右表（匹配表）记录进行匹配。

匹配左表当中的所有行以及右表中符合条件的行

select * from test01 a left join info b on a.name=b.name

两张表做对比，有相同的展示

右表没有的通过null值记录。

3.右连接

和左连接相反，以右侧表为寄出，接受右侧表所有记录，匹配的记录，不匹配的记录null值

select a.name,a.id,b.name from test01 a right join info b on a.name=b.name;

左表和右表靠物理位置分别