mysql/mariadb 进阶知识之表设计和查询

2021/7/18 19:38:25

本文主要是介绍mysql/mariadb 进阶知识之表设计和查询,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

进阶知识之表设计和查询

学生信息、成绩、课程数据表,提取码: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;


这篇关于mysql/mariadb 进阶知识之表设计和查询的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程