MySQL 数据库进阶篇

1、as 与 distinct

as关键字

使用as给字段起别名

select id as 序号, name as 名字, gender as 性别 from student;

通过as给表起别名

select s.name, s.id, s.gender from student as s ;

消除重复 distinct

distinct 可以消除重复的行

select distinct 列1,... from 表名
select gender from student；  # 这样取到的结果是所有学员的性别列
select distinct gender from student;  # 取到的是性别这列的种类

2、where条件

  where 的作用是通过子句对表中的数据筛选，结果为True的行会出现在结果集中。where后面支持多种运算符，进行条件的处理:

比较运算符
逻辑运算符
模糊查询
范围查询
空判断
2.1、比较运算(>, <, =, >=, <=, !=)

select * from student where age >= 20;
select * from student where name != "xiaoming"；
select * from student wheere hight < 170.00；

2.2、逻辑运算符(and, or, not)

# 查询编号大于5的女同学
select * from student where id>5 and gender=0 ；
# 查询年龄小于20的同学，或者身高高于180.00
select * from student where age<20 or hight<180.00；
# 查询电话号码不为空的同学
select * from student where phone is not null;

2.3、模糊查询(like, %~， _~)

# 查询姓刘的学生
select * from student where name like '刘%'；
# 查询姓刘名字为一个字的学生
select * from student where name like '刘_'；
# 查询名为小明的学生
select * from student where name like '_小明'；

2.4、范围查询(in, btween…and…)

# 查询编号为1或3或8的学生
select * from student where ID in (1,3,8);
# 查询编号在2到6的男生
select * from student where (ID between 2 and 6) and gender="男";

2.5、空判断

# 查询没有填写身高的同学
select * from student where hight is null；

2.6、优先级

优先级从高到低是 小括号，not 比较运算符，逻辑运算符；
and 比 or 的优先级高，如果一起出现想要先算or 需要用括号括起来。

3、排序

3.1、排序的语法

select * from 表名 order by 列1 asp|desc[, 列2 asc|desc] 
把数据按列1进行排序，如果某些行的列1值相同时，则按照列2的值排序。
asc从小到大排列，即升序；
desc从大到小排列，即降序；
默认按照列值从小到大排序（即asc关键字）

# 查询未删除男生的信息，并按学号降序
select * from student where gender='男' and is_delete=0 order by id desc;
# 查询未删除学生的信息，按序号升序
select * from student where is_delete=0 order by id (asp);
# 显示所有学生信息，先按年龄从大到小排序，如果年龄相同按照身高从大到下排序
select * from student order by age desc, hight desc;

排序时要用order by 关键字，asc是升序，desc是降序
4、分页

 select * from 表名 limit start=0，count
说明：从start开始，到count条数据，start默认值为0，也就是当需要前n条数据时可以直接写上xxx.limit n;
查看前3行男生的信息
select * from student where gender=1 limit 0， 3;
获取第n页数据的SQL语句
select * from student limit (n-1)*m, m;

5、聚合函数

5.1、总数

count(*)计算总行数，括号中写星或者列名，结果是相同

# 查询学生总数
select count(*) from students;

5.2、最小值min()

# 查询未删除学生的最小编号
select min(id) from student where is_delete=0;

5.3、最大值max()

# 查询女生编号的最大值
select max(id) from student where gender=1;

5.4、求和sum()

# 查询本班所有男生的总身高
select sum(hight) from student where gender=0；
# 查询本班所有男生的平均身高
select sum(hight)/count(*) from student where gender=0;

5.5、平均值avg()

# 查询未删除女生的平均年龄
select avg(age) from student where is_delect=0 and gender=1;

5.6、保留小数

round()不是聚合函数，作用是设置小数位

 格式：round(值，小数位)
# 查询全班年龄的平均值，保留两位小数
select round(avg(age),2) from students;

6、分组

6.1 group by 分组

group by的使用特点：

含义：将查询的结果按照一个或者多个字段进行分组，字段值相同的为一组。
可用于单个字段分组，也可用于多个字段分组

 # 查看全班性别的分组
select gender from student group by gender;  # --->分为4组,（男，女，中性，保密）

注意： MYSQL中默认情况下，select后面的字段只能出现在以下两种情况：

group by 后出现
聚合函数中出现

6.2、group by + group_concat()

group_concat(字段名)根据分组结果，使用group_concat来放置每一个分组中某个字段

# 按性别对全班同学分组，并且列出各组中学生的姓名
select gender ,group_concat(name) from student group by gender;
# 按照班级分组，并列出每个班的学生编号
select cls_id ,group_concat(id) from student group by cls_id;

6.3、group by + 聚合函数

与group by一起使用时，聚合函数统计的对象是分组

# 按性别对全班同学分组，并且统计每组的平均年龄
select gender ,avg(age) from student group by gender;
# 按照班级分组，并列出每个班有多少人
select cls_id ,count(*) from student group by cls_id;

6.4、group by + having

having 条件表达式：用来过滤分组结果
having作用类似where，但是having只能用于group by 后面，而where是用来过滤表数据的

 # 列出性别分组中，人数超过2人的分组，并且显示组内人数
select gender, count(*) from student group by gender having count(*)>2

6.5、group by + with rollup

with rollup作用：在最后添加一行来记录当前表中对该字段的操作结果，一般是统计总数。

# 列出性别分组，并且显示组内人数，统计汇总结果
select gender, count(*) from student group by gender with rollup;
# 列出班级分组，显示班级内学员ID，统计汇总结果
select cls_id，con_cat(id) from student group by cls_id with rollup;

7、连接
MySQL支持3种类型的连接查询：内连接查询：inner 求交集
左连接查询：left 左边集合和左右两边的交集
右连接查询：right 右边集合和左右两边的交集
 连接查询的语法

select * from 表1 inner/right/left join 表2 on 表1.列1 运算符 表2.列2

# 使用内连接查询班级表与学生表
select * from students as s inner join classes as c on s.cls_id=c.id;
# 使用左连接查询班级表与学生表
select * from students as s left join classrs as c on s.cls_id=c.id;
# 使用右连接查询班级表与学生表
select * from students as s right join classes a sc on s.cls_id=c.id;

  笛卡尔运算，将左表和右表中每一行都分别进行拼接组合 — 交叉连接cross join
把学生表添加到班级表中。

  内连接在笛卡尔连接的基础之上，使用on 进行指定过滤笛卡尔积的数据条件。
注意： 在MySQL中join 和 inner join是等价的其他数据库中不一样。

8、自查询

  两个表中其他内容都基本相似时，只有很少几列的值不相同时，我们采用自连接。这样做的好处是存储的是结构基本相似的数据，没有必要多做一个表分别来存储，一般情况是做两个表，然后同过内连接找到想要的内容。而现在用自查询，把所有信息都放进一个表中，通过给这个表气不同的名字，把他当做内容相同的另一个表进行连接。

9、子查询

9.1、子查询的概念

  在一个select语句中，嵌入另一个select语句，那么被嵌入的select语句被称为子查询语句，外部的那个select 被称为主查询语句。

子查询和主查询的关系：

子查询是嵌入到主查询之中的
子查询是辅助主查询的，要么充当条件，要么充当数据源
子查询是可以独立存在的语句，是一条完整的select语句。
9.2、子查询的分类

标量子查询：返回的结果是一个数据（一行一列）
列子查询：返回的结果是一列（一列多行）
行子查询：返回的结果是一行（一行多列）
9.3、标量子查询

# 查询大于班级平均年龄的学生
select * from students where age>(select avg(age) from students);

9.4、列子查询

 # 查询还有学生在班里的所有的班级的名称
select name from classes where id in (select cls_id from students);