十、Mysql的DQL语句

十、Mysql的DQL语句

DQL（Data QueryLanguage）语句也就是select查询语句。

select的查询

一、查看系统参数

select @@xxx 查看系统参数，该参数一般为my.cnf中定义的参数
案例：
SELECT @@port;
查看mysql端口号
SELECT @@basedir;
查看mysql的程序目录
SELECT @@datadir;
查看mysql的数据目录
SELECT @@socket;
查看mysql的socket路径
SELECT @@server_id;
查看mysql的server_id号
1
2
3
4
5
6
7
8
9
10
11
12

二、select常用函数

SELECT NOW();
查看时间
SELECT DATABASE();
查看当前所有数据库
SELECT USER();
查案当前是使用那个用户登录数据库
SELECT CONCAT("hello world");
打印hello world,concat函数长用户拼接列信息
SELECT CONCAT(USER,"@",HOST) FROM mysql.user;
在mysql.user表中查看user和host信息，并以user@host的方式显示
SELECT GROUP_CONCAT(USER,"@",HOST) FROM mysql.user;
把查询到的结果横向显示，列转行

https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html?tdsourcetag=s_pcqq_aiomsg
mysql函数地址
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

三、select的单表查询

select命令执行的顺序

select from where “group by” having “order by” limit

实验数据使用的world.sql数据库

[world]>show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
1
2
3
4
5
6
7
8

1、from子句

语句格式

SELECT 列1,列2 FROM 表
SELECT  *  FROM 表   (不建议在生产环境使用)
1
2

例子:

1)查询stu中所有的数据(不要对大表进行操作)

use school;
select * from stu;
1
2

2)查询stu表中,学生姓名和入学时间

use school;
select sname,intime from stu
1
2

2、where子句

语句格式

SELECT col1,col2 FROM TABLE WHERE colN 条件;
1

2.1 where配合等值查询

查询中国(CHN)所有城市信息

SELECT * FROM city WHERE countrycode='CHN';
1

查询北京市的信息

SELECT * FROM city WHERE NAME='peking';
1

查询甘肃省所有城市信息

SELECT * FROM city WHERE district='gansu';
1

2.2where配合比较操作符(> < >= <= <>)

<>表示为不等于!=

查询世界上少于100人的城市

SELECT * FROM city WHERE population<100;
1

2.3where配合逻辑运算符(and or )

中国人口数量大于500w

SELECT * FROM city WHERE countrycode='CHN' AND population>5000000;
1

中国或美国城市信息

SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';
SELECT * FROM city WHERE countrycode IN ('CHN','USA');
1
2

2.4where配合模糊查询

查询省的名字前面带guang开头的

SELECT * FROM city WHERE district LIKE 'guang%';    
注意:%不能放在前面,因为不走索引.
1
2

2.5where配合in语句

中国或美国城市信息

SELECT * FROM city WHERE countrycode IN ('CHN' ,'USA');
1

2.6where配合between and

查询世界上人口数量大于100w小于200w的城市信息

SELECT * FROM city  WHERE population >1000000 AND population <2000000;
SELECT * FROM city  WHERE population BETWEEN 1000000 AND 2000000;
1
2

3、group by + 常用聚合函数

group by:根据 by后面的条件进行分组，方便统计，by后面跟一个列或多个列

常用聚合函数

max()      ：最大值
min()      ：最小值
avg()      ：平均值
sum()      ：总和
count()    ：个数
group_concat() : 列转行
1
2
3
4
5
6

例子1：统计世界上每个国家的总人口数.

USE world;
SELECT countrycode,SUM(population)  FROM  city  GROUP BY countrycode;
1
2

例子2： 统计中国各个省的总人口数量

USE world;
SELECT distric,SUM(population) FROM city WHERE countrycode="CHN" GROUP BY distric;
1
2

例子3：统计世界上每个国家的城市数量

USE world; 
SELECT countrycode,COUNT(district) FROM city GROUP BY countrycode;
SELECT countrycode,COUNT(ID) FROM city GROUP BY countrycode;
1
2
3

4、having

常用命令顺序为 select from where “group by” having

先执行group by之前的语句结果，having以该结果来执行后面的命令

统计中国每个省的总人口数，只打印总人口数小于100w

SELECT district,SUM(Population)
FROM city
WHERE countrycode='chn'
GROUP BY district   
HAVING SUM(Population) < 1000000 ;
#HAVING之前的语句统计每个省的总人口数，HAVING后的语句筛选中国小于100w人口数量的省份
1
2
3
4
5
6

5、order by + limit

排序和限制

order by 从小到大的排序
order by desc 从大到小的排序
limit 限制显示查询的数量
LIMIT N ,M --->跳过N,显示一共M行
LIMIT 5,5 显示6到10行
LIMIT 3   显示前3行 === LIMIT 0,3
1
2
3
4
5
6

查看中国所有的城市，并按人口数进行排序(从大到小)

SELECT district,Population FROM city WHERE countrycode="CHN" ORDER BY Population DESC;
1

统计中国各个省的总人口数量，按照总人口从大到小排序

SELECT district,SUM(population) 
FROM city 
WHERE countrycode="CHN" 
GROUP BY district 
ORDER BY SUM(population) DESC;
1
2
3
4
5

统计中国,每个省的总人口,找出总人口大于500w的,并按总人口从大到小排序,只显示前三名

SELECT district,SUM(population) 
FROM city 
WHERE countrycode="CHN" 
GROUP BY district 
ORDER BY SUM(population) DESC
limit 3;
1
2
3
4
5
6

6、distinct：去重复

SELECT countrycode FROM city ;
SELECT DISTINCT(countrycode) FROM city  ;
1
2

四、联合查询–union all

中国或美国城市信息
 SELECT * FROM city WHERE countrycode in ("CHN","USA");

SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA'

说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能
UNION     去重复
UNION ALL 不去重复
1
2
3
4
5
6
7
8
9
10

五、别名

列别名,表别名
SELECT 
a.Name AS an ,  #定义列别名
b.name AS bn ,  #定义列别名
b.SurfaceArea AS bs,  #定义列别名
a.Population AS bp    #定义列别名
FROM city AS a  JOIN country AS b   #定义表别名
ON a.CountryCode=b.Code     #定义表别名
WHERE a.name ='shenyang';
#注意：列别名只能在结果中显示（以别名来替代原列的内容），不能在命令中调用；表别名可以在命令中调用，用于简化表名太长。
1
2
3
4
5
6
7
8
9
10

六、join 多表连接查询

按需求创建一下表结构,实验环境:

use school
stu ：学生表
sno：    学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别

teacher ：教师表
tno：     教师编号
tname：教师名字

course ：课程表
cno：  课程编号
cname：课程名字
tno：  教师编号

score  ：成绩表
sno：  学号
cno：  课程编号
score：成绩

-- 项目构建
drop database school;
CREATE DATABASE school CHARSET utf8;
USE school

CREATE TABLE stu(
sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(20) NOT NULL COMMENT '姓名',
sage TINYINT UNSIGNED  NOT NULL COMMENT '年龄',
ssex  ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别'
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE course(
cno INT NOT NULL PRIMARY KEY COMMENT '课程编号',
cname VARCHAR(20) NOT NULL COMMENT '课程名字',
tno INT NOT NULL  COMMENT '教师编号'
)ENGINE=INNODB CHARSET utf8;

CREATE TABLE sc (
sno INT NOT NULL COMMENT '学号',
cno INT NOT NULL COMMENT '课程编号',
score INT  NOT NULL DEFAULT 0 COMMENT '成绩'
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE teacher(
tno INT NOT NULL PRIMARY KEY COMMENT '教师编号',
tname VARCHAR(20) NOT NULL COMMENT '教师名字'
)ENGINE=INNODB CHARSET utf8;

INSERT INTO student(sno,sname,sage,ssex)
VALUES (1,'zhang3',18,'m');

INSERT INTO stu(sno,sname,sage,ssex)
VALUES
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f');

INSERT INTO stu
VALUES
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f');

INSERT INTO stu(sname,sage,ssex)
VALUES
('oldboy',20,'m'),
('oldgirl',20,'f'),
('oldp',25,'m');


INSERT INTO teacher(tno,tname) VALUES
(101,'oldboy'),
(102,'hesw'),
(103,'oldguo');

DESC course;
INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103);

DESC sc;
INSERT INTO sc(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);

SELECT * FROM stu;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM sc;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109

SELECT * FROM stu;

[school]>select * from stu;
+-----+---------+------+------+
| sno | sname   | sage | ssex |
+-----+---------+------+------+
|   1 | zhang3  |   18 | m    |
|   2 | zhang4  |   18 | m    |
|   3 | li4     |   18 | m    |
|   4 | wang5   |   19 | f    |
|   5 | zh4     |   18 | m    |
|   6 | zhao4   |   18 | m    |
|   7 | ma6     |   19 | f    |
|   8 | oldboy  |   20 | m    |
|   9 | oldgirl |   20 | f    |
|  10 | oldp    |   25 | m    |
+-----+---------+------+------+
10 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SELECT * FROM teacher;

[school]>[school]>select * from teacher;
+-----+--------+
| tno | tname  |
+-----+--------+
| 101 | oldboy |
| 102 | hesw   |
| 103 | oldguo |
+-----+--------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

SELECT * FROM course;

[school]>select * from course;
+------+--------+-----+
| cno  | cname  | tno |
+------+--------+-----+
| 1001 | linux  | 101 |
| 1002 | python | 102 |
| 1003 | mysql  | 103 |
+------+--------+-----+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

SELECT * FROM sc;

[school]>select * from sc;
+-----+------+-------+
| sno | cno  | score |
+-----+------+-------+
|   1 | 1001 |    80 |
|   1 | 1002 |    59 |
|   2 | 1002 |    90 |
|   2 | 1003 |   100 |
|   3 | 1001 |    99 |
|   3 | 1003 |    40 |
|   4 | 1001 |    79 |
|   4 | 1002 |    61 |
|   4 | 1003 |    99 |
|   5 | 1003 |    40 |
|   6 | 1001 |    89 |
|   6 | 1003 |    77 |
|   7 | 1001 |    67 |
|   7 | 1003 |    82 |
|   8 | 1001 |    70 |
|   9 | 1003 |    80 |
|  10 | 1003 |    96 |
+-----+------+-------+
17 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

多表查询语法

查询张三的家庭住址

SELECT A.name,B.address FROM
A JOIN  B
ON A.id=B.id
WHERE A.name='zhangsan'
1
2
3
4

查询一下世界上人口数量小于100人的城市名和国家名

SELECT b.name ,a.name ,a.population
FROM  city  AS a
JOIN  country AS b
ON    b.code=a.countrycode
WHERE  a.Population<100
1
2
3
4
5

查询城市shenyang，城市人口，所在国家名（name）及国土面积（SurfaceArea）

SELECT a.name,a.population,b.name ,b.SurfaceArea
FROM city  AS a JOIN country AS b
ON a.countrycode=b.code
WHERE a.name='shenyang';
1
2
3
4

统计zhang3,学习了几门课

SELECT st.sname , COUNT(sc.cno)
FROM student AS st
JOIN
sc
ON st.sno=sc.sno
WHERE st.sname='zhang3'
1
2
3
4
5
6

查询zhang3,学习的课程名称有哪些?

SELECT st.sname , GROUP_CONCAT(co.cname)
FROM student AS st
JOIN sc
ON st.sno=sc.sno
JOIN course AS co
ON sc.cno=co.cno
WHERE st.sname='zhang3'
1
2
3
4
5
6
7

查询oldguo老师教的学生名.

SELECT te.tname ,GROUP_CONCAT(st.sname)
FROM student AS st
JOIN sc
ON st.sno=sc.sno
JOIN course AS co
ON sc.cno=co.cno
JOIN teacher AS te
ON co.tno=te.tno
WHERE te.tname='oldguo';
1
2
3
4
5
6
7
8
9

查询oldguo所教课程的平均分数

SELECT te.tname,AVG(sc.score)
FROM teacher AS te
JOIN course AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
WHERE te.tname='oldguo'
1
2
3
4
5
6
7

每位老师所教课程的平均分,并按平均分排序

SELECT te.tname,AVG(sc.score)
FROM teacher AS te
JOIN course AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
GROUP BY te.tname
ORDER BY AVG(sc.score) DESC ;
1
2
3
4
5
6
7
8

查询oldguo所教的不及格的学生姓名

SELECT te.tname,st.sname,sc.score
FROM teacher AS te
JOIN course  AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
JOIN student AS st
ON sc.sno=st.sno
WHERE te.tname='oldguo' AND sc.score<60;
1
2
3
4
5
6
7
8
9

查询所有老师所教学生不及格的信息

SELECT te.tname,st.sname,sc.score
FROM teacher AS te
JOIN course  AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
JOIN student AS st
ON sc.sno=st.sno
WHERE sc.score<60;
1
2
3
4
5
6
7
8
9

综合练习

查询平均成绩大于60分的同学的学号和平均成绩；

SELECT stu.sno,AVG(sc.score)
FROM stu
JOIN sc
ON stu.sno=sc.sno
HAVING AVG(sc.score)>60;
1
2
3
4
5

查询所有同学的学号、姓名、选课数、总成绩；

SELECT stu.sno,stu.sname,COUNT(sc.cno),SUM(sc.score)
FROM stu
JOIN sc
ON stu.sno=sc.sno
GROUP BY stu.sno;
1
2
3
4
5

查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SELECT sc.cno AS "课程ID",MAX(sc.score) AS "最高分", MIN(sc.score) AS "最低分"
FROM sc
JOIN course
ON sc.cno=course.cno
GROUP BY sc.cno;
1
2
3
4
5

统计各位老师,所教课程的及格率

select teacher.tname,course.cname,
count(case when sc.score>60 then 1 end)/count(sc.score) 
from teacher 
join course on teacher.tno=course.tno 
join sc on course.cno=sc.cno 
group by teacher.tno,course.cno;
1
2
3
4
5
6

查询每门课程被选修的学生数

SELECT course.cname, COUNT(stu.sno)
FROM course
JOIN sc
ON course.cno=sc.cno
JOIN stu
ON sc.sno=stu.sno
GROUP BY course.cname;
1
2
3
4
5
6
7

查询出只选修了一门课程的全部学生的学号和姓名

SELECT stu.sno,stu.sname,COUNT(sc.cno)
FROM stu
JOIN sc
ON stu.sno=sc.sno
GROUP BY stu.sno
HAVING COUNT(sc.cno)=1;
1
2
3
4
5
6

查询选修课程门数超过1门的学生信息

SELECT stu.sno,stu.sname,stu.sage,stu.ssex,COUNT(sc.cno)
FROM stu
JOIN sc
ON stu.sno=sc.sno
GROUP BY stu.sno
HAVING COUNT(sc.cno)>1;
1
2
3
4
5
6

统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表

SELECT course.cname,
GROUP_CONCAT(CASE WHEN sc.score>85 THEN stu.sname END  ) AS 优秀,
GROUP_CONCAT(CASE WHEN sc.score>70 AND sc.score<=85 THEN stu.sname END) AS 良好,
GROUP_CONCAT(CASE WHEN sc.score>=60 AND sc.score<=70 THEN stu.sname END) AS 一般, 
GROUP_CONCAT(CASE WHEN sc.score<60 THEN stu.sname END) AS 不及格
FROM course
JOIN sc ON course.cno=sc.cno
JOIN stu ON sc.sno=stu.sno
GROUP BY course.cname;
1
2
3
4
5
6
7
8
9

查询平均成绩大于85的所有学生的学号、姓名和平均成绩

SELECT stu.sno,stu.sname,AVG(sc.score)
FROM stu
JOIN sc
ON stu.sno=sc.sno
GROUP BY stu.sno 
HAVING AVG(sc.score)>85;
1
2
3
4
5
6

stu.sno,stu.sname,stu.sage,stu.ssex,COUNT(sc.cno)
FROM stu
JOIN sc
ON stu.sno=sc.sno
GROUP BY stu.sno
HAVING COUNT(sc.cno)>1;

统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表



1
2
3
4
5

SELECT course.cname,
GROUP_CONCAT(CASE WHEN sc.score>85 THEN stu.sname END ) AS 优秀,
GROUP_CONCAT(CASE WHEN sc.score>70 AND sc.score<=85 THEN stu.sname END) AS 良好,
GROUP_CONCAT(CASE WHEN sc.score>=60 AND sc.score<=70 THEN stu.sname END) AS 一般,
GROUP_CONCAT(CASE WHEN sc.score<60 THEN stu.sname END) AS 不及格
FROM course
JOIN sc ON course.cno=sc.cno
JOIN stu ON sc.sno=stu.sno
GROUP BY course.cname;

查询平均成绩大于85的所有学生的学号、姓名和平均成绩

1
2
3
4
5

SELECT stu.sno,stu.sname,AVG(sc.score)
FROM stu
JOIN sc
ON stu.sno=sc.sno
GROUP BY stu.sno
HAVING AVG(sc.score)>85;

 
1
2