SQL基础应用-DQL(数据的查询语言)应用2

• 基于Centos7.6

1.常用聚合函数

执行顺序：select 列 from 表 where 条件 group by 条件 having 条件 order by 条件 limit 条件;
分析：
select 需要显示的列 from 表名 where 条件 group by 将where后条件进行分组的条件 having 对having之前的结果按条件做过滤 order by 对以上处理完的结构再进行条件过滤 limit 显示顺序;

1>group by + 常用聚合函数（有统计类需求一般使用group by）
group by：将某列中有共同条件的数据行，分成一组，然后再进行聚合函数操作。group by 还有去重复的功能，将共同点合并，只显示一次。
group by执行顺序：先排序，再去重。

根据 by后面的条件进行分组，方便统计，by后面跟一个列或多个列
**max()**      ：最大值
**min()**      ：最小值
**avg()**      ：平均值
**sum()**      ：总和
**count()**    ：计数

group_concat() ：列转行
例子：
mysql> grant all on *.* to test@'192.168.3.%' identified by 'test';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select user,host from mysql.user;
+---------------+-------------+
| user          | host        |
+---------------+-------------+
| test          | %           |
| test          | 192.168.3.% |
| mysql.session | localhost   |
| mysql.sys     | localhost   |
| root          | localhost   |
+---------------+-------------+
5 rows in set (0.00 sec)

mysql> select user,group_concat(host) from mysql.user group by user;
+---------------+--------------------+
| user          | group_concat(host) |
+---------------+--------------------+
| mysql.session | localhost          |
| mysql.sys     | localhost          |
| root          | localhost          |
| test          | %,192.168.3.%      |
+---------------+--------------------+
4 rows in set (0.00 sec)
#group_concat实现一对多显示

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

USE world
SELECT countrycode ,SUM(population)    FROM  city  GROUP BY countrycode;
例子2： 统计中国各个省的总人口数量(练习)

SELECT district,SUM(Population) FROM city  WHERE countrycode='chn' GROUP BY district;
例子3：统计世界上每个国家的城市数量(练习)

SELECT countrycode,COUNT(id)  FROM city GROUP BY countrycode;

2>SELECT + having 应用
语句顺序：where|group|having
having属于后过滤
例子4：统计中国每个省的总人口数，只打印总人口数小于100

SELECT district,SUM(Population)
FROM city
WHERE countrycode='chn'
GROUP BY district
HAVING SUM(Population) < 1000000 ;

concat拼接
例：
mysql> select concat(user,"@",host) from mysql.user;
+------------------------+
| concat(user,"",host)   |
+------------------------+
| test%                  |
| test192.168.3.%        |
| mysql.sessionlocalhost |
| mysql.syslocalhost     |
| rootlocalhost          |
+------------------------+
5 rows in set (0.00 sec)

3>order by + limit
作用
实现先排序，by后添加条件列
 
district 去重复，只显示一次。

应用案例
查看中国所有的城市，并按人口数进行排序(从大到小)
SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;
统计中国各个省的总人口数量，按照总人口从大到小排序
SELECT district AS 省 ,SUM(Population) AS 总人口
FROM city
WHERE countrycode='chn'
GROUP BY district
ORDER BY 总人口 DESC ;
统计中国,每个省的总人口,找出总人口大于500w的,并按总人口从大到小排序,只显示前三名
SELECT  district, SUM(population)  FROM  city 
WHERE countrycode='CHN'
GROUP BY district 
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 3 ;

LIMIT N ,M --->跳过N,显示一共M行
LIMIT 5,5

LIMIT N OFFSET M--->跳过M,显示一共N行

SELECT  district, SUM(population)  FROM  city 
WHERE countrycode='CHN'
GROUP BY district 
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 5,5;

4>distinct：去重复

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

2.联合查询

1>UNION和UNION ALL
UNION     去重复
UNION ALL 不去重复
作用：UNION和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,来提高性能

2>多表连接查询(内连接)
作用：单表数据不能满足查询需求时使用。
要求：(1)找到多表之间的关联列
     (2)列书写时必须是：表名.列
     (3)所有涉及到的查询列，都放在select后
     (4)将所有的过滤，分组，排序等条件按顺序写在ON的后面
     注意：多表连接中，驱动表选择数据行少的表。后续所有表的关联列尽量是主键或唯一键，至少建立一个索引。
     
双表格式：select 列 from 表1 join 表2 on 条件 where 条件 group by 条件 having 条件 order by 条件 limit 条件;
多表格式：select 列 from 表1 join 表2 on 1、2表的条件 join表3 on 2、3表的条件…… where 条件 group by 条件 having 条件 order by 条件 limit 条件;

SELECT A.name,B.address FROM
A JOIN  B
ON A.id=B.id
WHERE A.name='zhangsan'
例子：
查询一下世界上人口数量小于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
查询城市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';

3>别名(AS)
分类：列别名,表别名
表别名是全局调用
列别名是被having和order调用

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';

3.多表SQL练习题

1>例子：按需求创建一下表结构:
-----------------------------------------------
--项目构建:
drop database school;
CREATE DATABASE school CHARSET utf8;
USE school

CREATE TABLE student(
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 student(sno,sname,sage,ssex)
VALUES
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f');

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

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


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

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 student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM sc;

该库逻辑结构：
use school
student ：学生表
sno：    学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别

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

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

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

2>多表SQL练习题

统计zhang3,学习了几门课
SELECT st.sname , COUNT(sc.cno)
FROM student AS st
JOIN
sc
ON st.sno=sc.sno
WHERE st.sname='zhang3';

查询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';

查询user老师教的学生名.
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='user';

查询user所教课程的平均分数
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='user';

每位老师所教课程的平均分,并按平均分排序
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 ;

查询user所教的不及格的学生姓名
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='user' AND sc.score<60;

查询所有老师所教学生不及格的信息
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;