MySQL高级查询 #

单表查询 #

select * from 表名;# 查询表中所有的数据

select 字段名称 from 表名; # 查询表中指定字段数据

select [* || 字段] from 表名 [where 条件]; # 查询表中满足条件的数据

什么是结果集 #

通过查询语句查询出来的结果我们就称之为结果集， 结果集以表的形式将查询结果返回给我们

如何给结果集的字段起别名 #

select 字段名称 as 新的别名 from 表名 [where 条件];

什么是字段表达式 #

查询数据的时候，除了可以查询指定字段的数据以外，我们还可以查询表达式的结果

例如：SELECT 6+6;

什么是伪表 #

• 字段表达式虽然能够查询出表达式的结果，但是不符合MySQL的规范
• 所以我们可以通过伪表(dua1)的方式让字段表达式符合MySQL的规范

select 6+6 from dua1;

模糊查询 #

select 字段名称 from 表名 where 字段 like '条件';

• 通配符_： 表示任意一个字符

• 通配符%： 表示任意的0~n个字符

排序 #

select * from 表名称 [where 条件] order by 字段名称 [asc|desc];

# 复合排序 如果第一个字段名称相同，就按照第二个字段名进行排序，以此类推
select * from 表名称 [where 条件] order by 字段名称 [asc|desc], 字段名称 [asc|desc];

• 默认按照升序排序ASC

• 降序排序DESC

聚合函数 #

• count() ：统计

• sum() : 求和

• avg() : 求平均值

• max() : 获取最大值

• min() : 获取最小值

select count(字段名名称) from 表名 [where 条件];

select sum(字段名名称) from 表名 [where 条件];

select avg(字段名名称) from 表名 [where 条件];

select max(字段名名称) from 表名 [where 条件];

select min(字段名名称) from 表名 [where 条件];

数值类 #

• rand() : 生成随机数

例子：

# 随机排序

select * from where 表名称 order by rand(); 

• round() : 四舍五入

• ceil() : 向上取整

  • 3.1 -> 4

• floor() : 向下取整

  • 3.1 -> 3

• truncate() : 截取小数位

  • truncate(要截取的小数,要截取的位数);

字符串类 #

• ucase() ：转换为大写

• lcase() ：转小写

• left() : 从左边开始截取到指定的位置

• right() : 从右边开始截取到指定的位置

• substring() : 从左边的指定位置开始截取到指定位置结束

  • substring('1234566',2,6)

数据分组 #

select 分组字段 || 聚合函数 from 表名称 group by 分组字段;

select city from stu group by city;# 可以查询到全部的城市

select name from stu group by city;# name数据可能会缺失

条件查询 having: #

• having和where很像都是用来做条件查询的

• 但是where是去数据库中查询符合条件的数据，而having是在结果集中查询符合条件的数据

select * from stu where city = '北京';

select * from stu having city = '北京';

select name,age from stu where city = '北京'; # 运行不会报错

select name,age from stu having city = '北京';# 会报错找不到city列

需求：查询某个城市的平均分是否大于60

解析步骤：

# 1. 先按照城市进行分组

select city from stu group by city;

# 2. 取出分组中每个城市的平均分

select city,avg(score) as average from stu group by city;

# 3. 取出大于60的分的城市和成绩

select city ,avg(score) as average from stu group by city having average >= 60;

数据分页 #

select * from 表名称 [where 条件] limit 索引,个数;
# 从第几个开始 给几条数据；

查询选项 #

select [查询选项] 字段名称 from 表名;

• all: 显示查询的所有数据（默认）

• distinct: 去除重复的数据后在进行显示

select distinct name ,score from stu;

完整的查询语句 #

select [查询选项] 字段名称 [from 表名] [where 条件] [order by 排序] [group by 分组] [having 条件] [limit 索引,个数];

多表查询 #

多表查询只需要在单表查询的基础上增加一张表即可

select * from 表名称1,表名称2;

union作用 #

在纵向上将多张表的结果结合起来，返回给我们

select * from 表名称1 union select * from 表名称2;

select id,name from stu union select id,score,uid from person; #报错字段个数不一致

表的连接查询 #

• 将多张表中"关联的字段"连接到一起查询，我们称之为'表的连接查询'
• 不使用连接： select * from stu,grade where stu.id = grade.stuId;

内连接 inner join #

select * from 表名1 inner join 表名2 on 条件;

select * from stu inner join grade on stu.id = grade.stuId;

外连接 #

• 左外连接 left join

  • 在左外连接中，左边表是不看条件的，无论条件是否满足，都会返回左边表中的数据
  • 只有右边的表会看条件，对于右边的表而言，只有满足条件才会返回对应的数据

• 右外连接 right join

  • 在右外连接中，左边表是不看条件的，无论条件是否满足，都会返回右边表中的数据
  • 只有左边的表会看条件，对于左边的表而言，只有满足条件才会返回对应的数据

交叉连接 cross join #

如果没有指定条件，那么返回笛卡尔积，如果指定了条件，返回就等价于内连接

自然连接(natural) #

自然连接是用来简化"内连接和外连接"的

如果多张表需要判断的条件字段名称一致，那么不用编写条件，自然连接就会自动判断

• 自然内连接

select * from 表名1 natural join 表名2;

• 自然外连接

1. 自然左外连接 : natural left join

2. 自然右外连接 : natural right join

using 关键字 #

如果多张表需要判断的条件字段名称是一致的，呢么除了可以使用自然连接来简化以外，还可以使用using关键字来简化。

select * from stu [inner | left | right] join grade on stu.stuId = grade.stuId

等同于

select * from stu [inner | left | right] join grade using(stuId)

子查询 #

• 将一个查询语句的结果作为另一个查询语句的条件来使用

1. 标准子查询（返回的结果只有一个）

select stuId from grade where score = 100;

select name from stu where stuId = 3;

select name from stu where stuId = (select stuId from grade where score = 100);

2. 非标准子查询（返回结果有多个值）

select name from stu where stuId in(select stuId from grade where score >= 60);

• 将一个查询语句的结果作为另一个查询语句的表达式来使用

select name,city,score from person where score >=60;

select name,city,score from (select name,city,score from person where score >=60) as p;