Mysql系列基础篇-SQL分类（单表的DQL）

单表的DQL操作

基础查询

多个字段的查询

select name ,age from student;
select * from student;

去除重复的结果集

select distinct address from student;
查询出来的结果集完全一样才可以进行去重

计算列

select name,math,english,math + ifnull(english,0) from student;
如果有null参与的计算，均按照0来进行计算

起别名

select name,math as 数学,english 英语,math + ifnull(english,0) as 总分 from student;
as可以省略，字段名和别名之间使用空格

条件查询

• where字句后跟条件
• 运算符

> < <= >= = <> 
between ... and 
in(集合)
like
is null
and 或 && 
or 或 || 
not 或 ！

查询年龄大于等于20岁的信息
select * from student where age >= 20;
查询年龄等于20岁的信息
select * from student where age = 20;
查询年龄不等于20岁的信息
select * from student where age != 20;
select * from student where age <> 20;
查询年龄大于等于20岁的，小于等于30岁的信息
select * from student where age >= 20 && age <= 30;
select * from student where age >= 20 and  age <= 30;
select * from student where age between 20 and 30;
查询年龄为18岁的，23岁的，27岁的信息
select * from student where age = 18 || age = 23 || age = 27;
select * from student where age = 18 or age = 23 or age = 27;
select * from student where age in (18,23,27);
查询英语成绩为null的信息
select * from student where english is null;
查询英语成绩不为null的信息
select * from student where english is not null;

模糊查询

like:模糊查询
占位符：
_:单个任意字符
%多个任意字符

查询姓小的student的信息
select * from student where name like '小%';
查询姓名中第二字是红的student的信息
select * from student where name like '_红%';
查询姓名中有三个字的student的信息
select * from student where name like '___';
查询姓名中包含丽的student的信息
select * from student where name like '%丽%';

排序查询

语法：order by 排序字段 排序方式
asc：升序（默认）
desc：降序

按照数学成绩排名（降序）
select * from student order by math desc;
按照数据成绩排名，如果数据成绩一样，则按照英语成绩排名（降序）
select * from student order by math desc ,english desc;

聚合函数

将一列数据作为一个整体， 进行纵向的计算
count：计算个数（默认会排除为null值的字段）

• 一般选择非空的列：主键
• count（*）：所有列只要有一个不为null，就会算一条记录

max：计算最大值
min：计算最小值
sum：计算和
avg：计算平均值

select count(ifnull(english,0)) from student;
select count(*) from student;
select count(id) from student;
select max(math) from student;
select min(math) from student;
select avg(math) from student;
select sum(math) from student;

分组查询

语法：gruop by 字段

分组之后查询的字段必须为分组字段、聚合函数

where和having的区别？

where在分组之前进行限定，如果不满足条件，则不分组，having在分组之后进行限定，如果不满足条件，则不会查询出来
where之后不可以跟聚合函数，having可以进行聚合函数的判断

按照性别分组，分别查询男、女同学的数学平均分，人数
要求：
分数低于70分的人，不参与分组
分组之后人数要大于4人
select sex,avg(math),count(id) from student where math > 70 group by sex having(count(id) > 4);

分页查询

语法：

limit 开始的索引，每页查询的条数
公式：开始的索引 = （当前的页码 - 1） *  每页显示的条数
limit是mysql的一个方言

select * from student limit 0,3; 第一页
select * from student limit 3,3; 第二页
select * from student limit 6,3; 第三页

case when 条件表达式函数

语法：

case when 条件  then result 
[ when 条件  then result ]
else result  
end  

result是一个返回布尔类型的表达式，如果表达式返回true，则整个函数返回相应result的值，如果表达式皆为false，则返回ElSE后result的值，如果省略了ELSE子句，则返回NULL。

mysql> select * from student;
+----+-----------+------+------+---------+------+---------+
| id | names     | age  | sex  | address | math | english |
+----+-----------+------+------+---------+------+---------+
|  1 | 小明      |   55 | 男   | 杭州    |   66 |      78 |
|  2 | 小张      |   50 | 男   | 杭州    |   78 |      98 |
|  3 | 小红      |   28 | 女   | 广州    |   87 |      88 |
|  4 | 小丽      |   32 | 女   | 广州    |   69 |      91 |
|  5 | 小李      |   28 | 男   | 广州    |   91 |      77 |
|  6 | 小赵      |   41 | 男   | 广州    |   85 |      95 |
|  7 | 小敏      |   27 | 女   | 深圳    |   90 |      88 |
|  8 | 小娟      |   23 | 女   | 香港    |   69 |      98 |
|  9 | 张三      |   32 | 男   | 深圳    |   75 |      91 |
| 10 | 李四      |   31 | 男   | 香港    |   89 |      95 |
| 11 | 翠花      |   18 | 女   | 北京    |   92 |      81 |
| 12 | 萌萌      |   19 | 女   | 北京    |   88 |    NULL |
| 13 | 小月月    |   26 | 男   | 北京    |   38 |      59 |
+----+-----------+------+------+---------+------+---------+

english成绩<60的为不及格，60=<成绩< 80的为不及格，成绩>=80的为优秀

mysql> select 
names ,
(case when english < 60 then '不及格' when english >=60 and english < 80 then '及格' when english >= 80 then '优秀' else '缺考' end ) as remark 
from student;
+-----------+-----------+
| names     | remark    |
+-----------+-----------+
| 小明      | 及格      |
| 小张      | 优秀      |
| 小红      | 优秀      |
| 小丽      | 优秀      |
| 小李      | 及格      |
| 小赵      | 优秀      |
| 小敏      | 优秀      |
| 小娟      | 优秀      |
| 张三      | 优秀      |
| 李四      | 优秀      |
| 翠花      | 优秀      |
| 萌萌      | 缺考      |
| 小月月    | 不及格    |
+-----------+-----------+

输出班级同学的考试状态是正常还是缺考

mysql> select 
names,
(case when english is null then '缺考' else '正常' end) as status from student;
+-----------+--------+
| names     | status |
+-----------+--------+
| 小明      | 正常   |
| 小张      | 正常   |
| 小红      | 正常   |
| 小丽      | 正常   |
| 小李      | 正常   |
| 小赵      | 正常   |
| 小敏      | 正常   |
| 小娟      | 正常   |
| 张三      | 正常   |
| 李四      | 正常   |
| 翠花      | 正常   |
| 萌萌      | 缺考   |
| 小月月    | 正常   |
+-----------+--------+

统计班中男女同学的人数并输出男女同学的及格人数

mysql> select 
sum(case when sex='男' then 1 else 0 end) sum_male,
sum(case when sex = '男' and math>=60 then 1 else 0 end) male_pass, 
sum(case when sex='女' then 1 else 0 end) sum_female,
sum(case when sex = '女' and math>=60 then 1 else 0 end) female_pass  from student;
+----------+-----------+------------+-------------+
| sum_male | male_pass | sum_female | female_pass |
+----------+-----------+------------+-------------+
|        7 |         6 |          6 |           6 |
+----------+-----------+------------+-------------+