mysql(多表)
1外键
定义:外键是指引用另一个表中的一列或多列,被引用的列应该具有主键约束或唯一性约束,外键用于建立和加强两个表数据之间的连接。
1.1 为表添加外键约束
语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段名) REFERENCES 外表表名(主键字段名);
举例:为student表添加外键约束
命令:ALTER TABLE student ADD CONSTRAINT FK_ID FOREIGN KEY (gid) REFERENCES grade(id);
也可以在创建表的时候为其添加外键
1 2 3 4 5 6 7 8 9 | CREATE TABLE score ( id INT( 4 ) NOT NULL AUTO_INCREMENT, stu_number INT( 4 ) DEFAULT NULL, cou_number INT( 4 ) DEFAULT NULL, s_score DOUBLE DEFAULT NULL, PRIMARY KEY (id), CONSISTENT score_stu_number_studentnumber FOREIGN KEY(stu_number) REFERENCES student(stu_number) // score_stu_number_studentnumber 外键名<br> ) ENGINE=INNODB AUTO_INCREMENT= 20 DEFAULT CHARSET=utf8 |
操作关联表
2.1 关联关系
(1)多对一
数据表中最常见的一种关系,比如学生与班级的关系,一个班级可以有多个学生,但是一个学生不能属于多个班级。在多对一的关系中,应该将外键建在多的一方。
课程和老师表 (假定一门课程多个老师 ,一个老师一门课程)
(2)多对多(多对多应该提供第三张表存放这两者的关系)
比如学生与课程的关系,一个学生可以选择多门课程,一门课程也供多个学生选择
第三张表 用于存放学生和课程之间的关系
(3)一对一(外键可以在任意一方)
比如一个人只有一张身份证,而一张身份证也只对应一个人。
3、连接查询
(1)内链接
内连接(Inner Join)又称简单连接或自然连接,是一种常见的连接查询。内连接使用比较运算符对两个表中的数据进行比较,并列出与连接条件匹配的数据行,组成新的记录。
语法:SELECT 查询字段 FROM 表1 [ INNER ] JOIN 表2 ON 表1.关系字段=表2.关系字段
其中 INNER JOIN 用于连接两个表,ON 来指定连接条件 inner可以省略
1 2 3 4 5 6 7 8 9 10 11 12 | /*1 查询"01"课程比"02"课程成绩高的学生的信息及课程分数*/ SELECT s.s_number, s.s_name NAME,s.s_age age, s.s_gender gender, sc.s_score AS '01成绩' , sc2.s_score AS '02成绩' FROM student s JOIN score sc ON s.s_number=sc.stu_number AND sc.cou_number= '01' JOIN score sc2 ON s.s_number=sc2.stu_number AND sc2.cou_number= '02' WHERE sc.s_score >sc2.s_score ; |
(2) 自连接
如果在一个连接查询中涉及的两个表其实是同一个表,这种查询称为自连接查询,例如要查询王红所在的部门有多少个人,就可以用自连接查询。
命令:SELECT p1.* FROM employee AS p1 JOIN employee AS p2 ON p1.did=p2.did WHERE p2.name='王红';
(3)外连接分为左连接和右连接,当返回的查询结果不仅需要包含符合条件的数据,还需要包含其中一个表或者两个表的所有数据的时候,需要用到外连接查询。(左外连接 左边表全部显示,右外连接则右边表全部显示 ,左右外连接可以通过互换位置和相应的left /outer 达到相同的结果)
语法:SELECT 所查字段 FROM 表1 LEFT | RIGHT [ OUTER ] JOIN 表2
ON 表1.关系字段=表2.关系字段
WHERE 条件
4、子查询
子查询是指一个查询语句嵌套在另一个查询语句内部的查询。在执行时,首先执行子查询中的语句,然后将返回的结果作为外层查询的过滤条件。
4.1 带 IN 关键字的子查询(in 先执行里面的查询 SELECT did FROM employee WHERE age=20)
举例1:查询年龄为20岁的员工的部门
命令:SELECT * FROM department WHERE id IN (SELECT did FROM employee WHERE age=20);
4.2 带 EXISTS 关键字的子查询
EXISTS 关键字后面的参数可以是任意一个子查询,这个子查询不产生任何数据,只返回 TRUE 或 FALSE,当返回 TRUE 时,外层查询才会执行。
举例:查询employee表中是否存在年龄大于21岁的员工,若存在则查询department表中所有记录。
命令:SELECT * FROM department WHERE EXISTS (SELECT did FROM employee WHERE age >21 );
4.3 带 ANY 关键字的子查询()
ANY 关键字表示只要满足内层子查询中的任意一个条件,就返回一个结果作为外层查询条件。
举例:使用带ANY 关键字的查询,查询满足条件的部门。
命令:SELECT * FROM department WHERE did >ANY (SELECT did FROM employee);
在此命令中,子查询会先将employee表中所有did查询出来,分别是1,1,2,4,然后将 department 中的 did 的值与之比较,只要大于employee.did中的任意一个值,就是符合查询条件的结果。由于employee.did的最小值为1,所以department中只要大于1的did都满足条件,即2,3,5。
4.4 带 ALL 关键字的子查询
ALL关键字类似于ANY ,只是ALL关键字的子查询返回的结果需要同时满足所有内查询条件。
举例:使用带 ALL 关键字的子查询,查询满足条件的部门。
命令:SELECT * FROM department WHERE did > ALL (SELECT did FROM employee);
5.5 带 比较运算符的子查询
举例:使用带比较运算符的子查询,查询赵四属于哪个部门
命令:SELECT did,name FROM department WHERE did = (SELECT did FROM employee WHERE name='赵四');
CASE命令有两种语句格式:
语法形式1:CASE expression
WHEN expression_11 THEN expression_12
…
WHEN expression_n1 THEN expression_n2
[ELSE expression_m]
END
该语句的执行过程是:将CASE后面表达式的值与各WHEN子句中的表达式的值进行比较,如果两者相等,则返回THEN后的表达式的值,然后跳出CASE语句,否则返回ELSE子句中的表达式的值。ELSE子句是可选项。当CASE语句中不包含ELSE子句时,如果所有比较失败时,CASE语句将返回NULL。
【例】从数据表stud_info中,选取stud_id、grade,如果grade为“男”则输出“M”,如果为“女”输出“F”。
SELECT stud_id, sex=CASE gender
WHEN ’男’ THEN ’M’
WHEN ’女’ THEN ’F’
END
FROM stud_info
语法形式2:CASE WHEN condition_expression_1 THEN expression_1
…
WHEN condition_expression_n THEN expression_n
[ELSE expression_m]
END
该语句的执行过程是:首先测试WHEN后的条件表达式的值,如果其值为真,则返回THEN后面的表达式的值,否则测试下一个WHEN子句中的表达式的值,如果所有WHEN子句后的表达式的值都为假,则返回ELSE后的表达式的值,如果在CASE语句中没有ELSE子句,则CASE表达式返回NULL。
注意:CASE命令可以嵌套到SQL语句中。
【例】从stud_grade表中查询所有同学考试成绩情况,凡成绩为空者输出“未考”、小于60分输出“不及格”、60分至70分输出“及格”、70分至90分输出“良好”、大于或等于90分时输出“优秀”。
SELECT stud_id,name,score=CASE WHEN grade IS NULL THEN ’未考’
WHEN grade<60 THEN ’不及格’
WHEN grade>=60 AND grade<70 THEN ’及格’
WHEN grade>=70 AND grade<90 THEN ’良好’
WHEN grade>=90 THEN ’优秀’
END
FROM stud_grade
练习:
--1.学生表
Student -学生编号,s_name 学生姓名,s_age 出生年月,s_gender 学生性别
--2.课程表
course - --课程编号,c_name 课程名称,c_teachername 教师编号
--3.教师表
teacher t_number教师编号,t_name 教师姓名
--4.成绩表
score -- id ,stu_number学生编号,cou_number 课程编号,score 分数
*/
创建表:
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 | DROP TABLE IF EXISTS student; CREATE TABLE student ( s_number INT( 4 ) NOT NULL AUTO_INCREMENT, s_name VARCHAR( 20 ) DEFAULT NULL, s_age DATE DEFAULT NULL, s_gender CHAR( 2 ) DEFAULT NULL, PRIMARY KEY (s_number) ) ENGINE=INNODB DEFAULT CHARSET=utf8; CREATE TABLE teacher ( t_number INT( 4 ) NOT NULL AUTO_INCREMENT, t_name VARCHAR( 20 ) DEFAULT NULL, PRIMARY KEY (`t_number`) ) ENGINE=INNODB AUTO_INCREMENT= 4 DEFAULT CHARSET=utf8 CREATE TABLE score ( id INT( 4 ) NOT NULL AUTO_INCREMENT, stu_number INT( 4 ) DEFAULT NULL, cou_number INT( 4 ) DEFAULT NULL, s_score DOUBLE DEFAULT NULL, PRIMARY KEY (id), CONSISTENT score_stu_number_studentnumber FOREIGN KEY(stu_number) REFERENCES student(stu_number) ) ENGINE=INNODB AUTO_INCREMENT= 20 DEFAULT CHARSET=utf8 CREATE TABLE course ( c_number INT( 4 ) NOT NULL AUTO_INCREMENT, c_name VARCHAR( 20 ) DEFAULT NULL, c_teachernumber INT( 4 ) NOT NULL, PRIMARY KEY (c_number) ) ENGINE=INNODB AUTO_INCREMENT= 4 DEFAULT CHARSET=utf8 |
数据:
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 | INSERT INTO student VALUES( '01' , '赵雷' , '1990-01-01' , '男' ); INSERT INTO student VALUES( '02' , '钱电' , '1990-12-21' , '男' ); INSERT INTO student VALUES( '03' , '孙风' , '1990-05-20' , '男' ); INSERT INTO student VALUES( '04' , '李云' , '1990-08-06' , '男' ); INSERT INTO student VALUES( '05' , '周梅' , '1991-12-01' , '女' ); INSERT INTO student VALUES( '06' , '吴兰' , '1992-03-01' , '女' ); INSERT INTO student VALUES( '07' , '郑竹' , '1989-07-01' , '女' ); INSERT INTO student VALUES( '08' , '王菊' , '1990-01-20' , '女' ); INSERT INTO course VALUES( '01' , '语文' , '02' ); INSERT INTO course VALUES( '02' , '数学' , '01' ); INSERT INTO course VALUES( '03' , '英语' , '03' ); INSERT INTO teacher VALUES( '01' , '张三' ); INSERT INTO teacher VALUES( '02' , '李四' ); INSERT INTO teacher VALUES( '03' , '王五' ); INSERT INTO score VALUES(NULL, '01' , '01' , 80 ); INSERT INTO score VALUES(NULL, '01' , '02' , 90 ); INSERT INTO score VALUES(NULL, '01' , '03' , 99 ); INSERT INTO score VALUES(NULL, '02' , '01' , 70 ); INSERT INTO score VALUES(NULL, '02' , '02' , 60 ); INSERT INTO score VALUES(NULL, '02' , '03' , 80 ); INSERT INTO score VALUES(NULL, '03' , '01' , 80 ); INSERT INTO score VALUES(NULL, '03' , '02' , 80 ); INSERT INTO score VALUES(NULL, '03' , '03' , 80 ); INSERT INTO score VALUES(NULL, '04' , '01' , 50 ); INSERT INTO score VALUES(NULL, '04' , '02' , 30 ); INSERT INTO score VALUES(NULL, '04' , '03' , 20 ); INSERT INTO score VALUES(NULL, '05' , '01' , 76 ); INSERT INTO score VALUES(NULL, '05' , '02' , 87 ); INSERT INTO score VALUES(NULL, '06' , '01' , 31 ); INSERT INTO score VALUES(NULL, '06' , '03' , 34 ); INSERT INTO score VALUES(NULL, '07' , '02' , 89 ); INSERT INTO score VALUES(NULL, '07' , '03' , 98 ); |
操作:
1 2 3 4 5 6 7 8 9 10 11 12 | /*1 查询"01"课程比"02"课程成绩高的学生的信息及课程分数*/ SELECT s.s_number, s.s_name NAME,s.s_age age, s.s_gender gender, sc.s_score AS '01成绩' , sc2.s_score AS '02成绩' FROM student s JOIN score sc ON s.s_number=sc.stu_number AND sc.cou_number= '01' JOIN score sc2 ON s.s_number=sc2.stu_number AND sc2.cou_number= '02' WHERE sc.s_score >sc2.s_score ; |
过程 (1)先执行
1 2 3 4 5 6 | SELECT s.s_number, s.s_name NAME,s.s_age age, s.s_gender gender, sc.s_score AS '01成绩' FROM student s JOIN score sc ON s.s_number=sc.stu_number AND sc.cou_number= '01' |
得到:
(2 )将得到的结果表再join score sc2 加上条件
sc2
得到最终结果:
1 2 3 4 5 6 7 8 9 10 | /*2 查询"01"课程比"02"课程成绩低的学生的信息及课程分数*/ SELECT s.* ,sc.s_score AS '01课程' ,sc2.s_score AS '02课程' FROM student s JOIN score sc ON sc.stu_number=s.s_number AND sc.cou_number= '01' JOIN score sc2 ON sc2.stu_number=s.s_number AND sc2.cou_number= '02' WHERE sc2.s_score>sc.s_score; |
1 2 3 4 5 6 7 | /*3 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩*/ /*(1)求所有同学的平均成绩*/ SELECT s.*, AVG(s_score) AS AVG FROM score s GROUP BY stu_number<br><br><br><br><br> |

(2)
1 2 3 4 5 6 | SELECT s.s_number,s.s_name,t.AVG FROM student s JOIN (SELECT s.*, AVG(s_score) AS AVG FROM score s GROUP BY stu_number) t ON t.stu_number=s.s_number AND t.AVG>= 60 |
1 2 3 4 5 6 7 8 9 | /*4 查询平均成绩小于80分的同学的学生编号和学生姓名和平均成绩*/ SELECT student.s_name,student.s_number,t.平均成绩 FROM student JOIN (SELECT sc.stu_number,AVG(sc.s_score) AS '平均成绩' FROM score sc GROUP BY stu_number) t ON t.stu_number=student.s_number WHERE t.平均成绩< 80 |
1 | /*5 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩*/ <br> a 选课总数:<br> |
SELECT stu_number,COUNT(*)
FROM score
GROUP BY stu_number

1 2 3 4 5 6 7 8 | SELECT s.s_number,s.s_name,t.xkzs,f.zcj /*总成绩*/ FROM student s JOIN (SELECT stu_number,COUNT(*) AS 'xkzs' /*选课总数*/ FROM score GROUP BY stu_number) t ON t.stu_number=s.s_number JOIN (SELECT stu_number,SUM(s_score) AS 'zcj' FROM score GROUP BY stu_number) f ON f.stu_number=s.s_number |
/*6、查询"李"姓老师的数量 */ SELECT * FROM teacher; SELECT COUNT(*) FROM teacher WHERE t_name LIKE '李%';
1 2 3 4 5 6 | /*7、查询学过"张三"老师授课的同学的信息 */ <br> ( 1 ) 找到张三老师对应的课程编号(因为在score表中没有教师编号 只有课程编号 所以先找到对应的课程编号) SELECT c.c_teacherNumber AS t_num,c.c_number ,t.t_name FROM course c JOIN teacher t ON t.t_number=c.c_teacherNumber AND t.t_name= '张三' |

(2)将刚查出来的表join score 找到对应的student的编号
1 2 3 4 5 6 7 8 9 | SELECT stu_number ,f.t_name,f.t_num FROM score sc JOIN (SELECT c.c_teacherNumber AS t_num,c.c_number ,t.t_name FROM course c JOIN teacher t ON t.t_number=c.c_teacherNumber AND t.t_name= '张三' ) f ON f.c_number=sc.cou_number |
(3) 根据匹配的student的number 找到student的信息
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT s.*, l.t_name,l.t_num FROM student s JOIN (SELECT stu_number ,f.t_name,f.t_num FROM score sc JOIN (SELECT c.c_teacherNumber AS t_num,c.c_number ,t.t_name FROM course c JOIN teacher t ON t.t_number=c.c_teacherNumber AND t.t_name= '张三' ) f ON f.c_number=sc.cou_number) l ON l.stu_number =s.s_number |
在下面的表中你可以看到只有id=6的学生没有选课程编号为2的课程 即没有选‘张三老师的课’
1 | /*8、查询没学过"张三"老师授课的同学的信息 */ |
(1)先找到学过张三老师课程的学生的stu_number
1 2 3 4 5 6 7 8 | SELECT stu_number ,f.t_name,f.t_num FROM score sc JOIN (SELECT c.c_teacherNumber AS t_num,c.c_number ,t.t_name FROM course c JOIN teacher t ON t.t_number=c.c_teacherNumber AND t.t_name= '张三' ) f ON f.c_number=sc.cou_number |
(2)用not in()
1 2 3 4 5 6 7 8 | SELECT * FROM student WHERE s_number NOT IN(SELECT stu_number FROM score sc JOIN (SELECT c.c_teacherNumber AS t_num,c.c_number ,t.t_name FROM course c JOIN teacher t ON t.t_number=c.c_teacherNumber AND t.t_name= '张三' ) f ON f.c_number=sc.cou_number) |
8号没有选课 所以没有他的选课记录(因此在not in中显示出来)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | /*9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息*/ 《 1 》SELECT ss.stu_number FROM score ss JOIN (SELECT stu_number ,cou_number FROM score WHERE cou_number= '02' ) t ON t.stu_number=ss.stu_number AND ss.cou_number= '01' 《 2 》SELECT s.stu_number FROM score s JOIN score ss ON s.stu_number=ss.stu_number AND s.cou_number= '01' AND ss.cou_number= '02' ; |
两种写法结果都一样 都是现实两种编号课程都学习了得学生的编号
在将(学生编号在这个表中的数据)展示出来。(自连接)
1 2 3 4 5 | SELECT * FROM student WHERE s_number IN( SELECT s.stu_number FROM score s JOIN score ss ON s.stu_number=ss.stu_number AND s.cou_number= '01' AND ss.cou_number= '02' ); |
1 2 3 4 5 6 7 8 9 10 11 | /*10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息*/ SELECT * FROM student a LEFT JOIN score b ON a.s_number=b.stu_number AND b.cou_number= '01' LEFT JOIN score c ON a.s_number=c.stu_number AND c.cou_number= '02' |
将 c表中的不是null的项去除
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT a.* FROM student a LEFT JOIN score b ON a.s_number=b.stu_number AND b.cou_number= '01' LEFT JOIN score c ON a.s_number=c.stu_number AND c.cou_number= '02' WHERE b.cou_number= '01' AND c.cou_number IS NULL |
/*-11、查询学全所有课程的同学的信息 */
SELECT * FROM student WHERE s_number IN( SELECT stu_number FROM score GROUP BY stu_number HAVING COUNT(*) = (SELECT COUNT(*) FROM course));
【推荐】FlashTable:表单开发界的极速跑车,让你的开发效率一路狂飙
【推荐】Flutter适配HarmonyOS 5知识地图,实战解析+高频避坑指南
【推荐】博客园的心动:当一群程序员决定开源共建一个真诚相亲平台
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步