mysql进阶

一、as 与 distinct

as关键字web

使用as给字段起别名
数据库

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

经过as给表起别名svg

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

消除重复 distinct函数

distinct 能够消除重复的行
ssr

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

二、where条件

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

• 比较运算符
• 逻辑运算符
• 模糊查询
• 范围查询
• 空判断

2.一、比较运算(>, <, =, >=, <=, !=)
xml

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

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.三、模糊查询(like, %~， _~)
排序

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

2.四、范围查询(in, btween…and…)
it

# 查询编号为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.五、空判断

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

2.六、优先级

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

三、排序

3.一、排序的语法

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是降序

四、分页

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.一、总数

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

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

5.二、最小值min()

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

5.三、最大值max()

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

5.四、求和sum()

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

5.五、平均值avg()

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

5.六、保留小数

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

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

六、分组

6.1 group by 分组

group by的使用特色：

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

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

注意： MYSQL中默认状况下，select后面的字段只能出如今如下两种状况：

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

6.二、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.三、group by + 聚合函数

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

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

6.四、group by + having

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

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

6.五、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;

七、链接

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是等价的其余数据库中不同。

八、自查询

  两个表中其余内容都基本类似时，只有不多几列的值不相同时，咱们采用自链接。这样作的好处是存储的是结构基本类似的数据，没有必要多作一个表分别来存储，通常状况是作两个表，而后同过内链接找到想要的内容。而如今用自查询，把全部信息都放进一个表中，经过给这个表气不一样的名字，把他当作内容相同的另外一个表进行链接。

九、子查询

9.一、子查询的概念

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

子查询和主查询的关系：

• 子查询是嵌入到主查询之中的
• 子查询是辅助主查询的，要么充当条件，要么充当数据源
• 子查询是能够独立存在的语句，是一条完整的select语句。

9.二、子查询的分类

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

9.三、标量子查询

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

9.四、列子查询

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

9.五、行子查询

# 查找班级年龄最大,身高最高的学生
select