初识MySQL之综合复习篇（干货）

在日常开发和数据查询当中，我们需要等值连接或者左右连接多个表进行相关的字段查询

当然我们在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_CODEchar(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) )
;
<>增
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';
<>删
USE XSXK; DELETE FROM tb_student where ID='2018888888';
删除该条件下的一行数据

<>改
UPDATE tb_student SET GENDER='女' WHERE ID='2019888888';
--定位到ID为这个的行，然后把性别改为女即可，基本的操作及时 update 表名 set 需要改变的字段='新字段属性' where 条件定位 SELECT
* FROM tb_student where ID='2019888888';
<>查
# 查询每个老师带了哪些课程班 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
IDIN (SELECT MAJOR_CLASS FROM tb_student WHERE ID IN (SELECT STUDENT_ID FROM
tb_electivesWHERE COURSE_CLASS_CODE IN (SELECT CODE FROM tb_course_class WHERE
COURSE_CODEIN ( 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_CLASSand 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_IDand e.COURSE_CLASS_CODE = cc.CODE and cc.COURSE_CODE = c.CODE; #
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_courseas 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 bWHERE 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
bON a.teacher_id=b.ID; -- join 条件 SELECT cc.CODE,cc.COURSE_CODE,t.NAME FROM
tb_teacher tINNER 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 ccWHERE 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_courseWHERE 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_studentWHERE 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
SCOREIN (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_electivesGROUP BY COURSE_CLASS_CODE; # 分班查询最低分的学生 SELECT MIN(SCORE),
COURSE_CLASS_CODEFROM 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 tINNER JOIN (SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM
tb_electivesGROUP BY COURSE_CLASS_CODE) ms ON t.COURSE_CLASS_CODE=ms.
COURSE_CLASS_CODEand 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_CODEand 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 tINNER JOIN (SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM
tb_electivesGROUP BY COURSE_CLASS_CODE) ms ON t.COURSE_CLASS_CODE=ms.
COURSE_CLASS_CODEand 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 ccON 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 tINNER JOIN (SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM
tb_electivesGROUP BY COURSE_CLASS_CODE) ms ON t.COURSE_CLASS_CODE=ms.
COURSE_CLASS_CODEand 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 ccON 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_CODEas 课程班编号 FROM tb_electives GROUP BY COURSE_CLASS_CODE;
每文一语

学习不仅是借鉴，还要学会转移和利用，最后内化为自己的知识点

• « 上一篇：浅谈扫雷源代码
• » 下一篇：手把手教你使用R语言做LASSO 回归