MySQL的SQL基础（五）

1. 单表查询

1.1 having 语句

作用：与where子句类似，having属于后过滤
场景：需要在group by + 聚合函数后，再做判断过滤时使用

-- 例子15: 统计中国，每个省的总人口,只显示总人口数大于500w信息
SELECT district,SUM(population)
FROM world.`city`
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000;

1.2 order by 应用

-- 例子16: 统计中国，每个省的总人口，只显示总人口数大于500w信息，并且按照总人口从大到小排序输出
SELECT district,SUM(population)
FROM world.`city`
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC ;

小提示：
（1）limit一般配合order by一起使用的，不做order by的limit没有太大的意义

1.3 limit 应用

作用： 分页显示结果集

-- 例子17: 统计中国，每个省的总人口，只显示总人口数大于500w信息，并且按照总人口从大到小排序输出，只显示前五名
-- limit 5 等同于 limit 5 offset 0
SELECT district,SUM(population)
FROM world.`city`
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC 
LIMIT 5;

-- 例子18: 统计中国，每个省的总人口，只显示总人口数大于500w信息，并且按照总人口从大到小排序输出，只显示前6-10名
SELECT district,SUM(population)
FROM world.`city`
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC 
LIMIT 5,5;

-- 上下两种写法结果都一样
-- limit 5 offset 5 跳过前5行，显示5行（6~10行）
SELECT district,SUM(population)
FROM world.`city`
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC 
LIMIT 5 OFFSET 5;

3~5名：
limit 2,3
limit 3 offset 2

2. select 多表连接查询

先创建一个school数据库并创建student、teacher、course、score表，并且插入数据，方便后续实验操作

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

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

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

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

-- 项目构建
-- 建库建表并插入数据
CREATE DATABASE school CHARSET utf8mb4;
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=utf8mb4;

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

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

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

INSERT INTO student(sno,sname,sage,ssex)
VALUES (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');

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

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

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;

2.1 多表连接查询作用

为什么要使用多表连接查询？
我们的查询需求，需要的数据，来自于多张表，单张表无法满足。

2.2 多表连接查询类型

2.2.1 笛卡尔乘积

笛卡尔积没有什么意义，但是有助于我们理解工作过程

2.2.2 内连接

2.2.3 外连接

3. show 语句介绍

4. information_schema 元数据获取