初识MySQL之综合复习篇（干货）
【摘要】 在日常开发和数据查询当中，我们需要等值连接或者左右连接多个表进行相关的字段查询
当然我们在MySQL语法知识点里面我们需要对它的整个框架要有一个比较熟悉的掌握程度，这样才能在比较的熟悉的操作我们的数据
建表
# 根据E-R图创建表
DROP TABLE IF EXISTS wxw_data;
CREATE TABLE wxw_data (
SN CHAR(20)...
在日常开发和数据查询当中，我们需要等值连接或者左右连接多个表进行相关的字段查询
当然我们在MySQL语法知识点里面我们需要对它的整个框架要有一个比较熟悉的掌握程度，这样才能在比较的熟悉的操作我们的数据
建表
# 根据E-R图创建表
DROP TABLE IF EXISTS wxw_data;
CREATE TABLE wxw_data (
SN CHAR(20) PRIMARY KEY,
NAME VARCHAR(50) NOT NULL,
PRICE DOUBLE,
EDITOR varchar(50)
);
DROP TABLE IF EXISTS wxw_CANKAO;
CREATE TABLE wxw_CANKAO (
JIAOCAI_SN CHAR(20),
COURSE_CODE char(8),
type varchar(50),
PRIMARY KEY (JIAOCAI_SN,COURSE_CODE),
CONSTRAINT fk_jiaocai_sn FOREIGN KEY (JIAOCAI_SN) REFERENCES wxw_data(SN),
CONSTRAINT fk_course_code FOREIGN KEY (COURSE_CODE) REFERENCES tb_course(CODE)
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
增
USE XSXK;
INSERT INTO wxw_data VALUES ('978-7-115-48910-4','MySQL数据库技术与应用',42.00,'张素青');
INSERT INTO wxw_data SELECT '978-7-115-48910-5','MySQL数据库技术与应用',42.00,'张素青';
# 插入自己的信息
INSERT INTO tb_student SELECT '2019888888','tanguangyu','男','2010-10-10','15902333748',
ID from tb_major_class where COLLEGE='数理与大数据学院' and grade=2019 and class=1 and
major='大数据';
SELECT * FROM tb_student where ID='2019888888';
INSERT INTO tb_student SELECT '2018888888','tanguangyu','男','2010-10-10','15902333748',
ID from tb_major_class where COLLEGE='数理与大数据学院' and grade=2018 and class=1 and
major='大数据';
SELECT * FROM tb_student where ID='2018888888';
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
删
USE XSXK;
DELETE FROM tb_student where ID='2018888888';
- 1
- 2
删除该条件下的一行数据
改
UPDATE tb_student SET GENDER='女' WHERE ID='2019888888';
--定位到ID为这个的行，然后把性别改为女即可，基本的操作及时
update 表名 set 需要改变的字段='新字段属性' where 条件定位
SELECT * FROM tb_student where ID='2019888888';
- 1
- 2
- 3
- 4
- 5
- 6
查
# 查询每个老师带了哪些课程班
select CODE 班号, TEACHER_ID 教师编号 from tb_course_class;
# 1， 返回的字段有哪些
SELECT t.NAME 教师姓名, cc.CODE 班号
# 2， 要返回的字段从哪些表(临时的表、视图、表连接、子查询结果集)中得到
FROM tb_teacher t, tb_course_class cc
# 3， 判断要返回的字段从哪些行取得
WHERE t.ID = cc.TEACHER_ID;
# 查询每个老师带了多少课程班
# 1， 返回的字段有哪些
SELECT t.NAME 教师姓名, COUNT(cc.CODE) 班级数量
# 2， 要返回的字段从哪些表(临时的表、视图、表连接、子查询结果集)中得到
FROM tb_teacher t, tb_course_class cc
# 3， 判断要返回的字段从哪些行取得
WHERE t.ID = cc.TEACHER_ID
# 4， 按什么条件分组聚合
GROUP BY t.NAME;
# 查询每个老师带了多少课程班,按数量多少排序
# 1， 返回的字段有哪些
SELECT t.NAME 教师姓名, COUNT(cc.CODE) 班级数量
# 2， 要返回的字段从哪些表(临时的表、视图、表连接、子查询结果集)中得到
FROM tb_teacher t, tb_course_class cc
# 3， 判断要返回的字段从哪些行取得
WHERE t.ID = cc.TEACHER_ID
# 4， 按什么条件分组聚合
GROUP BY t.NAME
# 5， 排序默认正序从小到大
ORDER BY 班级数量 DESC;
# 查询每个老师带了多少课程班,按数量多少排序, 筛选大于5个班的老师
# 1， 返回的字段有哪些
SELECT t.NAME 教师姓名, COUNT(cc.CODE) 班级数量
# 2， 要返回的字段从哪些表(临时的表、视图、表连接、子查询结果集)中得到
FROM tb_teacher t, tb_course_class cc
# 3， 判断要返回的字段从哪些行取得
WHERE t.ID = cc.TEACHER_ID
# 4， 按什么条件分组聚合
GROUP BY t.NAME
# 5， 对查询结果集再次筛选
HAVING 班级数量>5;
# 查询每个老师带了多少课程班,按数量多少排序, 筛选大于5个班的老师
# 1， 返回的字段有哪些
SELECT t.NAME 教师姓名, COUNT(cc.CODE) 班级数量
# 2， 要返回的字段从哪些表(临时的表、视图、表连接、子查询结果集)中得到
FROM tb_teacher t, tb_course_class cc
# 3， 判断要返回的字段从哪些行取得
WHERE t.ID = cc.TEACHER_ID
# 4， 按什么条件分组聚合
GROUP BY t.NAME
# 5， 对查询结果集再次筛选
HAVING 班级数量>5
ORDER BY 班级数量
LIMIT 1;
# 子查询
# where子句中的子查询
# 查询某门课程是那个学院开设的
SELECT DISTINCT COLLEGE
FROM tb_major_class
WHERE ID IN
(SELECT MAJOR_CLASS FROM tb_student WHERE ID IN
(SELECT STUDENT_ID FROM tb_electives WHERE COURSE_CLASS_CODE IN (SELECT CODE FROM tb_course_class WHERE COURSE_CODE IN ( SELECT CODE FROM tb_course WHERE NAME='Web程序设计基础' ) )
)
);
# 表连接（内连接）
# 查询某门课程是那个学院开设的
SELECT DISTINCT mc.COLLEGE
FROM tb_major_class mc, tb_student s, tb_electives e, tb_course_class cc, tb_course c
WHERE
mc.ID = s.MAJOR_CLASS and
s.ID = e.STUDENT_ID and
e.COURSE_CLASS_CODE = cc.CODE and
cc.COURSE_CODE = c.CODE and
c.NAME like 'Web%';
# 查询某门课程是那个学院开设的
SELECT DISTINCT c.NAME 课程,mc.COLLEGE 学院
FROM tb_major_class mc, tb_student s, tb_electives e, tb_course_class cc, tb_course c
WHERE
mc.ID = s.MAJOR_CLASS and
s.ID = e.STUDENT_ID and
e.COURSE_CLASS_CODE = cc.CODE and
cc.COURSE_CODE = c.CODE;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
# SELECT 生成一个结果集（二维表）
SELECT 1,2;
SELECT 'abc';
SELECT CURRENT_TIME();
# FROM 扫描一个二维表结构（实体表、临时表、查询结果集）
SELECT tb_course.NAME, CODE
FROM tb_course;
# FROM 表连接（新的临时表）
-- 交叉连接（笛卡尔积） A CROSS JOIN B => 列数=A+B 行数=A*B
SELECT * from tb_course,tb_teacher;
SELECT * FROM tb_course CROSS JOIN tb_teacher;
# 内连接
SELECT * from tb_course as c,tb_course_class as cc WHERE c.CODE=cc.COURSE_CODE;
# 查询课程班级的课程名称
# 查课程班级对应的课程编号
SELECT COURSE_CODE,CODE FROM tb_course_class WHERE CODE='3SL1113A.09';
# 查课程名称
SELECT * FROM tb_course WHERE CODE = (SELECT COURSE_CODE FROM tb_course_class WHERE CODE='3SL1113A.09');
SELECT a.*,b.NAME FROM (SELECT COURSE_CODE,CODE FROM tb_course_class WHERE CODE='3SL1113A.09') as a
, tb_course b
WHERE a.COURSE_CODE=b.CODE;
SELECT a.*,b.NAME FROM (SELECT Teacher_id,CODE FROM tb_course_class WHERE CODE='3SL1113A.09') as a
INNER JOIN
tb_teacher b ON a.teacher_id=b.ID; -- join 条件 SELECT cc.CODE,cc.COURSE_CODE,t.NAME FROM tb_teacher t INNER JOIN tb_course_class cc ON t.ID=cc.teacher_id
WHERE cc.CODE='3SL1113A.09';
SELECT cc.CODE,c.NAME,t.NAME from
tb_course c, tb_teacher t, tb_course_Class cc
WHERE c.CODE=cc.COURSE_CODE and t.ID=cc.Teacher_id;
SELECT cc.CODE 班级编号,c.NAME 课程名称,t.NAME 授课老师 from tb_course_class cc JOIN tb_course c ON cc.COURSE_CODE=c.CODE JOIN tb_teacher t ON cc.teacher_id=t.ID
where cc.CODE='3SL1113A.09'; # 外连接 （左连接，右连接） # WHERE 条件判断 => 是否SELECT
SELECT 'a' FROM tb_course WHERE 1=1;
# 查询2学分的课程信息
SELECT * FROM tb_course WHERE CREDIT=2;
# 查询不及格的学生成绩
SELECT * FROM tb_electives WHERE SCORE < 60;
# LIKE (不是喜欢，是像） % 通配符，匹配任意0个或多个字符
SELECT * FROM tb_course WHERE NAME like '%程序设计';
# 查询和‘我’同姓的学生信息
SELECT * FROM tb_student WHERE NAME like '谭%飞';
# IN ==> OR OR OR
SELECT * FROM tb_student WHERE NAME='谭飞' OR NAME='谭华' OR NAME='谭贝';
SELECT * FROM tb_student WHERE NAME IN ('谭飞','谭华','谭贝');
# 子查询
# 查询3SL1037A.01班分数最高的学生的ID
# 1.先要知道最高分
SELECT MAX(SCORE) FROM tb_electives WHERE COURSE_CLASS_CODE='3SL1037A.01';
# 2.再知道谁是最高分
SELECT * FROM tb_electives WHERE SCORE=100 AND COURSE_CLASS_CODE='3SL1037A.01';
SELECT * FROM tb_electives WHERE COURSE_CLASS_CODE='3SL1037A.02' ORDER BY SCORE ASC LIMIT 1;
UPDATE tb_electives SET SCORE=100 WHERE COURSE_CLASS_CODE='3SL1037A.01' and SCORE=99; SELECT * FROM tb_electives
WHERE SCORE IN (SELECT MIN(SCORE) FROM tb_electives WHERE COURSE_CLASS_CODE like '3SL1037A.0%') AND COURSE_CLASS_CODE like '3SL1037A.0%';
# 查询最低分
SELECT MIN(SCORE) FROM tb_electives ;
# 分班查询最低分
SELECT MIN(SCORE),COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE;
# 分班查询最低分的学生
SELECT MIN(SCORE),COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE;
select t.STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE
from
tb_electives t INNER JOIN
(SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore ; select t.STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE,s.NAME
from
tb_electives t
INNER JOIN
(SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore
LEFT JOIN tb_student s ON t.STUDENT_ID=s.ID;
select t.STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE,s.NAME 学生姓名,c.NAME 课程名
from
tb_electives t
INNER JOIN
(SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore
LEFT JOIN tb_student s ON t.STUDENT_ID=s.ID
LEFT JOIN tb_course c
ON instr(t.COURSE_CLASS_CODE,c.CODE);
select t.STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE,s.NAME 学生姓名,c.NAME 课程名,cc.TEACHER_ID
from
tb_electives t
INNER JOIN
(SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore
LEFT JOIN tb_student s ON t.STUDENT_ID=s.ID
LEFT JOIN tb_course c
ON instr(t.COURSE_CLASS_CODE,c.CODE)
LEFT JOIN tb_course_class cc ON t.COURSE_CLASS_CODE=cc.CODE;
select t.STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE,s.NAME 学生姓名,c.NAME 课程名,te.NAME 教师姓名,te.TITLE 教师职称
from
tb_electives t
INNER JOIN
(SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore
LEFT JOIN tb_student s ON t.STUDENT_ID=s.ID
LEFT JOIN tb_course c
ON instr(t.COURSE_CLASS_CODE,c.CODE)
LEFT JOIN tb_course_class cc ON t.COURSE_CLASS_CODE=cc.CODE
LEFT JOIN tb_teacher te ON cc.TEACHER_ID=te.ID; select mc.COLLEGE 学院,
concat(mc.MAJOR,mc.GRADE,'-',mc.CLASS) as 专业班级,
t.STUDENT_ID 学生学号,
s.NAME 学生姓名,
t.COURSE_CLASS_CODE 课程班编号,
c.NAME 课程名称,
t.SCORE 分数,
te.NAME 教师姓名,
te.TITLE 教师职称
from
tb_electives t
INNER JOIN
(SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore
LEFT JOIN tb_student s ON t.STUDENT_ID=s.ID
LEFT JOIN tb_course c
ON instr(t.COURSE_CLASS_CODE,c.CODE)
LEFT JOIN tb_course_class cc ON t.COURSE_CLASS_CODE=cc.CODE
LEFT JOIN tb_teacher te ON cc.TEACHER_ID=te.ID
LEFT JOIN tb_major_class mc ON mc.ID=s.MAJOR_CLASS; # 查询‘我’所在专业班级的信息
# 专业班在哪儿找： tb_major_class
# 已知条件：我=》 数理学院，大数据专业，2019级， 3班
SELECT *
FROM tb_major_class
WHERE COLLEGE='数理与大数据学院' AND MAJOR='大数据' AND GRADE=2019 AND CLASS=3;
# 我的专业班级编号为43
# 统计我这个班有多少人
# 统计数量COUNT()
# 人-学生人数，学生在哪里找： tb_student
SELECT COUNT(*) AS 学生人数, MAJOR_CLASS AS 班级编号
FROM tb_student
WHERE MAJOR_CLASS=43;
# 分组统计每个班的学生人数
SELECT COUNT(*) AS 学生人数, MAJOR_CLASS AS 班级编号
FROM tb_student
GROUP BY MAJOR_CLASS;
# 分组统计每个班的学生人数, 按学生人数从多到少排序
# major class 专业班人数
SELECT COUNT(*) AS 学生人数, MAJOR_CLASS AS 班级编号
FROM tb_student
GROUP BY MAJOR_CLASS
ORDER BY 学生人数 DESC;
# course class 课程班人数
# 课程班学生在哪里找： tb_electives
SELECT COUNT(*) as 学生人数, COURSE_CLASS_CODE as 课程班编号
FROM tb_electives
GROUP BY COURSE_CLASS_CODE;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
每文一语
学习不仅是借鉴，还要学会转移和利用，最后内化为自己的知识点
文章来源: wxw-123.blog.csdn.net，作者：王小王-123，版权归原作者所有，如需转载，请联系作者。
原文链接：wxw-123.blog.csdn.net/article/details/111676277
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）