mysql/mariadb 进阶知识之表设计和查询
2021/7/18 19:38:25
本文主要是介绍mysql/mariadb 进阶知识之表设计和查询,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
进阶知识之表设计和查询
学生信息、成绩、课程数据表,提取码:iqub
评论及回复数据表,提取码:hdr7
一、表设计之数据类型选择
1.1、表设计步骤
- 第一步,确定大类型:数字、字符串、日期等级等;
- 第二步,确定具体的类型:如整数类型,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);
字符串搜索
可以用单引号,也可以用双引号,只要成对匹配就可以。
- 以**开头:LIKE ‘A%’
- 以**结尾:LIKE ‘%B’
- 包含**内容:LIKE ‘%AB%’
- 正则匹配:REGEXP ‘[0-9]{4}’
LIKE运算符支持的两种通配符
- %:任意数量的字符
- _:一个字符
练习三
-- 查找所有姓“李”的学生信息 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、逻辑运算符
- 与关系/且关系(全部满足):用AND或者&&表示
- 非关系:用NOT或者!表示
- 或关系(部分满足):用OR或者||表示
- 异或: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、字符串函数
常用字符串函数
- 字符串的长度:LENGTH(s)
utf8编码时,一个汉字3字节,数字或字母1字节 - 字符串拼接:CONCAT(s1, s2, …)
如果参数中有NULL,则返回NULL - 大小写转换:LOWER(s)、UPPER(s)
- 删除两端的空格:TRIM(s)、LTRIM(s)、RTRIM(s)
4.2、日期时间函数
插入数据时,可以用日期时间函数,创建表时,可以使用系统自带的日期函数变量,比如CURRENT_DATE、CURDATE。
-
获取当前的日期:CURRENT_DATE()、CURDATE()
-
获取当前的日期时间:CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()
-
获取UTC日期时间:UTC_DATE()、UTC_TIMESTAMP()、UTC_TIME()
-
日期时间格式化:DATE_FORMAT(date, format)
日期格式化的方式
时间格式化方式 -
日期增加:DATE_ADD()、ADDDATE()
DATE_ADD(date, INTERVAL expr unit) -
日期减少:DATE_SUB()、SUBDATE()
DATE_SUB(date, INTERVAL expr unit) -
时间加减: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、聚合统计函数
- 统计总行数:COUNT()
- 指定列的最大/最小值:MAX()、MIN()
- 指定列的总和:SUM()
- 指定列的平均数: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、数学函数和其它常用函数
数学函数:
- 求绝对值:ABS(x)
- 求平方根:SQRT(x)
- 取一个0~1之间的随机数:RAND()
- 向下取整:FLOOR(x) 向上取整:CEIL(x)
- 三角函数:SIN(x)、COS(x)、TAN(x)等
- 对数函数:LOG(x)
其它常用函数 - IF条件判断:IF(expr1, expr2, expr3)
满足条件expr1返回expr2,否则返回expr3 - 获取数据库的版本号:VERSION()
- 加密函数: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、多表连接
内连接
- 只要符合连接条件的记录
- 语法参考
SELECT * FROM table1 JOIN table2 ON join_condition
SELECT * FROM table1 JOIN table2 WHERE join_condition
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 进阶知识之表设计和查询的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-11-04部署MySQL集群项目实战:新手入门教程
- 2024-11-04如何部署MySQL集群资料:新手入门指南
- 2024-11-02MySQL集群项目实战:新手入门指南
- 2024-11-02初学者指南:部署MySQL集群资料
- 2024-11-01部署MySQL集群教程:新手入门指南
- 2024-11-01如何部署MySQL集群:新手入门教程
- 2024-11-01部署MySQL集群学习:新手入门教程
- 2024-11-01部署MySQL集群入门:新手必读指南
- 2024-10-23BinLog入门:新手必读的MySQL二进制日志指南
- 2024-10-23Binlog入门:MySQL数据库的日志管理指南