进阶知识之表设计和查询

学生信息、成绩、课程数据表，提取码：iqub

评论及回复数据表，提取码：hdr7

一、表设计之数据类型选择

1.1、表设计步骤

1. 第一步，确定大类型：数字、字符串、日期等级等；
2. 第二步，确定具体的类型：如整数类型，tinyint/smallint/mediumit/int/bigint

1.2、数据类型

1.2.1、数字类型（整数/小数）

整数类型

整数类型说明：

• 存储值的范围越大，所需要的存储空间也会越大
• 可以添加AUTO_INCREMENT自增约束条件
• UNSIGNED属性(无符号显示)：默认是SIGNED(有符号显示)，正数的上限提示约1倍
• INT(11)：显示宽度，即能显示的最大数字个数

小数类型

浮点数取值范围：

1.2.2、字符类型

utf8mb3字符集：一个字符=3个字节

utf8mb4字符集：一个字符=4个字节

1.2.3、日期时间类型

1.2.4、其他类型

1.2.5、数据类型的选择

选择的参考规则

• 尽可能选择满足业务需求的最小数据类型
  速度更快，更省资源(磁盘、内存、CPU)
  精度不同，消耗的资源不同
• 尽可能选择使用简单数据类型，如：整形比字符操作代价更低
• 尽可能避免NULL，注意：NULL与空值的区别。避免NULL方法 NOT NULL限制，或者DEFAULT指定默认值。

二、表设计之字段约束条件

2.1、字段约束

2.2、外键约束管理

同创建表时创建外键约束

CREATE TEABLE `account_user`(
	`id` INT NOT NULL AUTO_INCREMENT,
	PRIMARY KEY(`id`)
);

建立外键的时，可以给外键取一个名字，方便后续进行修改和删除
CREATE TABLE `account_user_address`(
	`user_id` INT NOT NULL,
	FOREIGN KEY [key_name](`user_id`) REFERENCES `account_user`(`id`)
);

修改表时增加外键约束

ALTER TABLE `account_user_address` ADD FROEIGN KEY key_name(`user_id`) REFERENCES `account_user(`id`)`;

删除外键约束

ALTER TABLE `account_user_address` DROP FOREIGN KEY key_name;

外键约束使用要点

• 用来约束多个表中数据的关系
• 在【子表】上定义约束内容
• 约束关联字段的类型要保持一致
• 注意添加外键约束后的数据删除问题，只能先删除外键表相关的数据后，才能正常删除主表中的数据

2.3、实战：设计学生成绩表

-- 学生信息表
CREATE TABLE `school_student_info` (
 `id` INT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
 `stu_no` SMALLINT NOT NULL COMMENT '学号',
 `stu_name` VARCHAR(16) NOT NULL COMMENT '姓名',
 `sex` TINYINT DEFAULT 1 COMMENT '性别, 0: 女，1：男',
 `age` TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄',
 `class_name` VARCHAR(10) DEFAULT '' COMMENT '班级',
 `address` VARCHAR(255) DEFAULT '' COMMENT '家庭住址',
 `phone_no` CHAR(11) DEFAULT '' COMMENT '联系电话',
 PRIMARY KEY (`id`)
) COMMENT '学生信息表';


-- 课程信息表
CREATE TABLE `school_course_info` (
 `id` INT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
 `course_name` VARCHAR(64) NOT NULL COMMENT '课程名称',
 `teacher` VARCHAR(16) NOT NULL COMMENT '老师',
 `desc` VARCHAR(512) DEFAULT '' COMMENT '课程描述',
 PRIMARY KEY (`id`)
)COMMENT '课程信息表';

-- 学生成绩表
CREATE TABLE `school_student_grade` (
 `id` INT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
 `course_id` INT NOT NULL COMMENT '课程ID',
 `student_id` INT NOT NULL COMMENT '学生ID',
 `score` FLOAT DEFAULT 0 COMMENT '成绩',
 `created_at` date DEFAULT '0-0-0' COMMENT '考试时间',
 PRIMARY KEY (`id`),
 FOREIGN KEY student_ref(`student_id`) REFERENCES `school_student_info`(`id`),
 FOREIGN KEY course_ref(`course_id`) REFERENCES `school_course_info`(`id`)
) COMMENT '学生成绩表';

三、条件查询

运算符

• 比较运算符
• 算术运算符
• 逻辑运算符
• 位运算符

3.1、比较运算符

比大小

多用于数字类型、字符串类型、日期时间类型。比较结果：1：相等，0：不相等

运算符号：=(<=>)、!=(<>)、>、<、>=、<=

需要注意的是：<=>(安全等于运算符)，可以用来判断NULL值

指定范围

BETWEEN a AND b：指定范围查询，必须从结果集中获取

对NULL值的比较

IS NULL / IS NOT NULL

练习一

-- 查询年龄大于12岁的所有学生
SELECT * FROM school_student_info WHERE age > 12;

-- 查询年龄在9~12岁之间(含)的学生信息
SELECT * FROM school_student_info WHERE age  BETWEEN 9 AND 12;

-- 查询未设置/已设置年龄的学生信息
SELECT * FROM school_student_info WHERE age IS NULL;
SELECT * FROM school_student_info WHERE age IS NOT NULL;

在/不在列表内

IN (a, b, c) / NOT IN (a, b, c)

最大值

GREATEST(a, b, c)

最小值

LEAST(a, b, c)

如果是字母通过ascii码比较，如果比较的值中存在NULL，则结果为NULL

练习二

-- 查询年龄是9岁和12岁的学生信息
SELECT * FROM school_student_info WHERE age IN (9, 12);

字符串搜索

可以用单引号，也可以用双引号，只要成对匹配就可以。

1. 以**开头：LIKE ‘A%’
2. 以**结尾：LIKE ‘%B’
3. 包含**内容：LIKE ‘%AB%’
4. 正则匹配：REGEXP ‘[0-9]{4}’

LIKE运算符支持的两种通配符

1. %：任意数量的字符
2. _：一个字符

练习三

-- 查找所有姓“李”的学生信息
SELECT * FROM school_student_info WHERE stu_name LIKE '李%';

-- 查找所有姓“李”，名只有一个字的学生信息
SELECT * FROM school_student_info WHERE stu_name LIKE '李_';

-- 查找姓名中包含“雪”字的学生信息
SELECT * FROM school_student_info WHERE stu_name LIKE '%雪%';

3.2、算术运算符

算法运算符，遵循运算符优先规则。

对于字符串，会做转化(只能处理数字开头，进行截取)，不能转化为数字的字符串，会返回0。


练习示例

-- 将学生的年龄加10岁进行展示
SELECT *, age + 10 FROM school_student_info;

-- 给所有学生的数学成绩加5分
UPDATE school_student_grade SET score = score + 5 WHERE course_id = 2;

3.3、逻辑运算符

1. 与关系/且关系(全部满足)：用AND或者&&表示
2. 非关系：用NOT或者!表示
3. 或关系(部分满足)：用OR或者||表示
4. 异或：XOR

-- 查询所有年龄在9岁到12岁之间的女生
SELECT * FROM school_student_info WHERE age  BETWEEN 9 AND 12 AND sex='女';

-- 查询所有12岁以上的男生和9岁以下的女生
SELECT * FROM school_student_info WHERE (age>12 AND sex='男') OR (age<9 AND sex='女');

-- 查询所有“李”姓的女生中年龄不为空的学生信息
SELECT * FROM school_student_info WHERE stu_name LIKE '李%' AND sex='女' AND age IS NOT NULL;

四、函数的使用

常见函数分类：

• 字符串函数
• 日期时间函数
• 聚合统计函数
• 数学函数
• 其它函数

4.1、字符串函数

常用字符串函数

1. 字符串的长度：LENGTH(s)
   utf8编码时，一个汉字3字节，数字或字母1字节
2. 字符串拼接：CONCAT(s1, s2, …)
   如果参数中有NULL，则返回NULL
3. 大小写转换：LOWER(s)、UPPER(s)
4. 删除两端的空格：TRIM(s)、LTRIM(s)、RTRIM(s)

4.2、日期时间函数

插入数据时，可以用日期时间函数，创建表时，可以使用系统自带的日期函数变量，比如CURRENT_DATE、CURDATE。

1. 获取当前的日期：CURRENT_DATE()、CURDATE()

2. 获取当前的日期时间：CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()

3. 获取UTC日期时间：UTC_DATE()、UTC_TIMESTAMP()、UTC_TIME()

4. 日期时间格式化：DATE_FORMAT(date, format)

   日期格式化的方式
   时间格式化方式

   

5. 日期增加：DATE_ADD()、ADDDATE()
   DATE_ADD(date, INTERVAL expr unit)

6. 日期减少：DATE_SUB()、SUBDATE()
   DATE_SUB(date, INTERVAL expr unit)

7. 时间加减：ADDTIME()、SUBTIME()
   ADDTIME(expr1, expr2)

加减单位选项

示例

-- 增加本地时间一个小时
SELECT CURRENT_TIMESTAMP,DATE_ADD(CURRENT_TIMESTAMP,INTERVAL 1 HOUR);

-- 增加本地时间3小时5分钟
SELECT CURRENT_TIME, ADDTIME(CURRENT_TIME, '03:05:00');
SELECT CURRENT_TIMESTAMP, ADDTIME(CURRENT_TIMESTAMP, '03:05:00');

-- 将考试时间增加1年
SELECT *, ADDDATE(created_at,INTERVAL 1 YEAR) FROM school_student_grade;

4.3、聚合统计函数

1. 统计总行数：COUNT()
2. 指定列的最大/最小值：MAX()、MIN()
3. 指定列的总和：SUM()
4. 指定列的平均数：AVG()

-- 统计本班的学生人数
SELECT class_name , COUNT(*) from school_student_info GROUP BY class_name;

-- 统计本班语文成绩的最高分/最低分
SELECT MAX(score), MIN(score) from school_student_grade WHERE course_id=1;

-- 统计本班语文成绩的平均分
SELECT AVG(score) from school_student_grade WHERE course_id=1;

-- 统计学生张三的各科总分
SELECT SUM(score) FROM school_student_grade WHERE student_id=5;

4.4、数学函数和其它常用函数

数学函数：

1. 求绝对值：ABS(x)
2. 求平方根：SQRT(x)
3. 取一个0~1之间的随机数：RAND()
4. 向下取整：FLOOR(x) 向上取整：CEIL(x)
5. 三角函数：SIN(x)、COS(x)、TAN(x)等
6. 对数函数：LOG(x)
   
   其它常用函数
7. IF条件判断：IF(expr1, expr2, expr3)
   满足条件expr1返回expr2，否则返回expr3
8. 获取数据库的版本号：VERSION()
9. 加密函数：MD5(s)、SHA(s)、SHA2(s, l)

SELECT stu_name, sex, IF(sex='男','帅哥','美女') FROM school_student_info;

SELECT VERSION(), MD5('123456'), SHA('123456');

-- 返回在0~50之间(包括0和50)
SELECT CEIL(RAND()* 100 % 50);

五、复杂SQL查询

5.1、分组与去重

思考题：现在要为学生寄送期末成绩单，如果发现地址相同的则统一寄送，现需要统计学生的地址信息

-- 方式一：使用DISTINCT关键字去重
SELECT DISTINCT address FROM school_student_info;

-- 方式二：使用GROUP BY 分组去重
SELECT address FROM school_student_info GROUP BY address;

分组查询：GROUP BY

• 语法参考：SELECT col_name FROM table_name GROUP BY col_name
• 支持多个分组条件
• SELECT 中的字段【只能】是【GROUP BY】中的字段或聚合函数
• GROUP BY 子句必须出现在【WHERE 子句之后】，【ORDER BY 子句之前】

-- 统计每个班的人数
SELECT class_name, COUNT(*) FROM school_student_info GROUP BY class_name;

-- 统计每个班男生、女生的总人数
SELECT class_name, sex, COUNT(sex) FROM school_student_info GROUP BY class_name, sex;

HAVING子句

• 作用：在分组后进行条件筛选（过滤分组）。
• 注意：WHERE过滤行，而HAVING过滤分组
• 语法参考：SELECT col_name FROM table_name GROUP BY col_name HAVING COUNT(1)>2

-- 查找三门成绩都及格(>=60)的学生
SELECT student_id, COUNT(*) FROM school_student_grade WHERE score >= 60 GROUP BY student_id HAVING COUNT(*)=3;

5.2、子查询

为输出列指定别名

• 语法参考：SELECT age [AS] 'my_age' FROM table_name
• 作用：改变输出的名称，【不会改变其输出的值】

-- 指定列的别名，为了方便阅读，尽量加上AS关键字
SELECT class_name as '班级', COUNT(*) AS '总人数' FROM school_student_info GROUP BY class_name;

为表指定别名

• 语法参考：SELECT t.age FROM table_name [AS] t
• 作用：方便后面的表关联查询

-- 指定表的别名
SELECT s.stu_no, s.stu_name FROM school_student_info AS s;

子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询。

子查询的使用场景：

• SELECT 子查询

  -- 查询每个学生的学号、姓名、语文成绩
  SELECT t1.stu_no AS '学号', t1.stu_name AS '姓名',
  (SELECT t2.score FROM school_student_grade t2 
  WHERE t2.course_id=1 AND t2.student_id=t1.id) AS '语文成绩'
  FROM school_student_info t1;
  

• FROM 子查询：经常被当作临时表

  SELECT t.*
  FROM (SELECT s1.stu_no, s1.stu_name, s1.age * 100 AS age
  FROM school_student_info s1) t 
  WHERE t.age > 1200;
  

• WHERE 子查询

  -- 查询本班语文成绩大于平均成绩的学生ID及分数
  SELECT t1.student_id, t1.score FROM school_student_grade t1 
  WHERE t1.course_id=1 AND t1.score > (
  SELECT AVG(t2.score) from school_student_grade t2 WHERE t2.course_id=1);
  	
  -- 查询本班学生中，没有语文成绩的学生信息
  -- 使用IN关键字
  SELECT t1.* FROM school_student_info t1  WHERE t1.id NOT IN (
  SELECT t2.student_id FROM school_student_grade t2 WHERE t2.course_id=1);
  	
  -- 使用EXISTS关键字
  SELECT t1.* FROM school_student_info t1  WHERE NOT EXISTS (
  SELECT t2.* FROM school_student_grade t2 WHERE t2.course_id=1 AND t2.student_id=t1.id);
  

5.3、多表连接

内连接

• 只要符合连接条件的记录
• 语法参考
  1. SELECT * FROM table1 JOIN table2 ON join_condition
  2. SELECT * FROM table1 JOIN table2 WHERE join_condition
  3. SELECT * FROM table1, table2 WHERE join_condition

-- 查询每个学生的学号、姓名、年龄、课程、老师、语文成绩
-- 方式一：使用 JOIN ON
SELECT s1.stu_no, s1.stu_name, s1.age, s3.course_name, s3.teacher, s2.score
FROM school_student_info s1 
JOIN school_student_grade s2 ON s1.id = s2.student_id
JOIN school_course_info s3 ON s2.course_id = s3.id
WHERE s2.course_id = 1;

-- 方式二：使用 JOIN WHERE
SELECT s1.stu_no, s1.stu_name, s1.age, s3.course_name, s3.teacher, s2.score
FROM school_student_info s1 
JOIN school_student_grade s2
JOIN school_course_info s3
WHERE  s1.id = s2.student_id AND  s2.course_id = s3.id AND s2.course_id = 1;

-- 方式三：使用 , WHERE
SELECT s1.stu_no, s1.stu_name, s1.age, s3.course_name, s3.teacher, s2.score
FROM school_student_info s1, school_student_grade s2, school_course_info s3
WHERE  s1.id = s2.student_id AND  s2.course_id = s3.id AND s2.course_id = 1;

自连接

• 使用场景：在SELECT语句中多次引用相同的表

-- 设计一个评论及评论回复表，查询某条评论下的直接回复记录
SELECT t1.content AS '评论', t2.content AS '评论的回复'
FROM bbs_comment t1, bbs_comment t2 
WHERE t2.parent_id=t1.id;

外连接

• 要全部的记录（不符合条件的也要）
• 外连接分为三种
  1. 左外连接：LEFT [OUTER] JOIN
  2. 右外连接：RIGHT [OUTER] JOIN
  3. 全连接：FULL JOIN （mysql/mariadb不支持）

-- 缺考的学生信息
SELECT t1.id, t1.stu_no, t1.stu_name, t2.score
FROM school_student_info t1
LEFT JOIN school_student_grade t2 ON t1.id=t2.student_id
WHERE t2.score IS NULL;

-- 缺考了哪一门
SELECT t1.id, t1.stu_no, t1.stu_name, t2.course_id, t2.score
FROM school_student_info t1
LEFT JOIN school_student_grade t2 ON t1.id=t2.student_id
WHERE t1.stu_no=1037;

-- 没有录入的学生信息
SELECT t2.student_id,t2.course_id,t2.score,t1.stu_no, t1.stu_name
FROM school_student_info t1
RIGHT JOIN school_student_grade t2 ON t1.id=t2.student_id
WHERE t1.stu_no IS NULL;

结果集连接

• UNION关键字连接多个查询结果
• 要求：每个查询【必须】包含【相同】的列、表达式或聚集函数
• 使用UNION ALL可以返回所有匹配的行（不去重）
• 参考语法

  SELECT * FROM table1
  UNION [ALL]
  SELECT * FROM table2
  

-- 使用UNION,自动去重
SELECT * FROM school_student_info WHERE id <= 10
UNION
SELECT * FROM school_student_info WHERE id <= 15;

-- 使用UNION ALL，不去重，返回所有行
SELECT * FROM school_student_info WHERE id <= 10
UNION ALL
SELECT * FROM school_student_info WHERE id <= 15;

5.4、结果排序

排序的实现

• 语法参考：ORDER BY col_name [ASC | DESC]
• 关键字解析
  1. ASC：升序排列，默认的方式
  2. DESC：降序排列
• 支持按多个字段排序，按照字段的顺序，依次排列

排序规则

• 数字类型（整数，浮点数）：按数字大小排序
• 日期时间：按先后/早晚/大小顺序排序
• 字符类型：按照字母的ASCII码顺序

-- 将三年级所有学生的【语文成绩】根据班级按照从高到低进行排序
SELECT s1.class_name,s1.stu_no, s1.stu_name, s3.course_name, s2.score
FROM school_student_info s1, school_student_grade s2, school_course_info s3
WHERE s1.id=s2.student_id AND s2.course_id=s3.id AND s2.course_id = 1
ORDER BY s1.class_name, s2.score DESC;


-- 将三年级所有学生的期末考试总成绩按照从高到低进行排序
SELECT s1.stu_no, s1.stu_name, SUM(s2.score) AS score
FROM school_student_info s1, school_student_grade s2
WHERE s1.id=s2.student_id
GROUP BY s2.student_id
ORDER BY score DESC;

5.5、分页处理

分页的好处

• 提升用户体验，减少用户等待时间
• 节省系统资源（CPU、内存、网络）

分页的实现

• 语法格式：LIMIT [起始位置,] 偏移量，偏移量：每一页数据的大小
• 处于SQL最后面的位置：SELECT * FROM table_name LIMIT 10;

-- 三年级所有学生中语文成绩最好的前10名
SELECT s1.class_name,s1.stu_no, s1.stu_name, s3.course_name, s2.score
FROM school_student_info s1, school_student_grade s2, school_course_info s3
WHERE s1.id=s2.student_id AND s2.course_id=s3.id AND s2.course_id = 1
ORDER BY s2.score DESC
LIMIT 10;

-- 三年级所有学生中期末考试总成绩的第11~第20名
SELECT s1.stu_no, s1.stu_name, SUM(s2.score) AS score
FROM school_student_info s1, school_student_grade s2
WHERE s1.id=s2.student_id
GROUP BY s2.student_id
ORDER BY score DESC
LIMIT 10, 10;