一、外键约束
1、创建外键
--- 每一个班主任会对应多个学生 , 而每个学生只能对应一个班主任
----主表
CREATE TABLEClassCharger(
idTINYINT PRIMARY KEYauto_increment,
nameVARCHAR (20),
ageINT,
is_marriged boolean--show create table ClassCharger: tinyint(1));INSERT INTO ClassCharger (name,age,is_marriged) VALUES ("冰冰",12,0),
("丹丹",14,0),
("歪歪",22,0),
("姗姗",20,0),
("小雨",21,0);----子表
CREATE TABLEStudent(
idINT PRIMARY KEYauto_increment,
nameVARCHAR (20),
charger_idTINYINT, --切记:作为外键一定要和关联主键的数据类型保持一致
--[ADD CONSTRAINT charger_fk_stu]FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)) ENGINE=INNODB;INSERT INTO Student(name,charger_id) VALUES ("alvin1",2),
("alvin2",4),
("alvin3",1),
("alvin4",3),
("alvin5",1),
("alvin6",3),
("alvin7",2);DELETE FROM ClassCharger WHERE name="冰冰";INSERT student (name,charger_id) VALUES ("yuan",1);--删除居然成功,可是 alvin3显示还是有班主任id=1的冰冰的;
-----------增加外键和删除外键---------
ALTER TABLE student ADD CONSTRAINTabcFOREIGN KEY(charger_id)REFERENCESclasscharger(id);ALTER TABLE student DROP FOREIGN KEY abc;
2、 INNODB支持的ON语句
--外键约束对子表的含义: 如果在父表中找不到候选键,则不允许在子表上进行insert/update
--外键约束对父表的含义: 在父表上进行update/delete以更新或删除在子表中有一条或多条对
--应匹配行的候选键时,父表的行为取决于:在定义子表的外键时指定的
--on update/on delete子句
-----------------innodb支持的四种方式---------------------------------------
-----cascade方式 在父表上update/delete记录时,同步update/delete掉子表的匹配记录-----外键的级联删除:如果父表中的记录被删除,则子表中对应的记录自动被删除--------
FOREIGN KEY (charger_id) REFERENCESClassCharger(id)ON DELETE CASCADE
------set null方式 在父表上update/delete记录时,将子表上匹配记录的列设为null
--要注意子表的外键列不能为not null
FOREIGN KEY (charger_id) REFERENCESClassCharger(id)ON DELETE SET NULL
------Restrict方式 :拒绝对父表进行删除更新操作(了解)
------No action方式 在mysql中同Restrict,如果子表中有匹配的记录,则不允许对父表对应候选键
--进行update/delete操作(了解)
二、多表查询
--准备两张表--company.employee--company.department
create tableemployee(
emp_idint auto_increment primary key not null,
emp_namevarchar(50),
ageint,
dept_idint);insert into employee(emp_name,age,dept_id) values('A',19,200),
('B',26,201),
('C',30,201),
('D',24,202),
('E',20,200),
('F',38,204);create tabledepartment(
dept_idint,
dept_namevarchar(100)
);insert into department values(200,'人事部'),
(201,'技术部'),
(202,'销售部'),
(203,'财政部');
mysql> select * fromemployee;+--------+----------+------+---------+
| emp_id | emp_name | age | dept_id |
+--------+----------+------+---------+
| 1 | A | 19 | 200 |
| 2 | B | 26 | 201 |
| 3 | C | 30 | 201 |
| 4 | D | 24 | 202 |
| 5 | E | 20 | 200 |
| 6 | F | 38 | 204 |
+--------+----------+------+---------+
6 rows in set (0.00sec)
mysql> select * fromdepartment;+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 200 | 人事部 |
| 201 | 技术部 |
| 202 | 销售部 |
| 203 | 财政部 |
+---------+-----------+
4 rows in set (0.01 sec)
准备表和数据
1、多表查询之连接查询
1.笛卡尔积查询
mysql> SELECT * FROMemployee,department;--select employee.emp_id,employee.emp_name,employee.age,--department.dept_name from employee,department;
+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
| 1 | A | 19 | 200 | 200 | 人事部 |
| 1 | A | 19 | 200 | 201 | 技术部 |
| 1 | A | 19 | 200 | 202 | 销售部 |
| 1 | A | 19 | 200 | 203 | 财政部 |
| 2 | B | 26 | 201 | 200 | 人事部 |
| 2 | B | 26 | 201 | 201 | 技术部 |
| 2 | B | 26 | 201 | 202 | 销售部 |
| 2 | B | 26 | 201 | 203 | 财政部 |
| 3 | C | 30 | 201 | 200 | 人事部 |
| 3 | C | 30 | 201 | 201 | 技术部 |
| 3 | C | 30 | 201 | 202 | 销售部 |
| 3 | C | 30 | 201 | 203 | 财政部 |
| 4 | D | 24 | 202 | 200 | 人事部 |
| 4 | D | 24 | 202 | 201 | 技术部 |
| 4 | D | 24 | 202 | 202 | 销售部 |
| 4 | D | 24 | 202 | 203 | 财政部 |
| 5 | E | 20 | 200 | 200 | 人事部 |
| 5 | E | 20 | 200 | 201 | 技术部 |
| 5 | E | 20 | 200 | 202 | 销售部 |
| 5 | E | 20 | 200 | 203 | 财政部 |
| 6 | F | 38 | 204 | 200 | 人事部 |
| 6 | F | 38 | 204 | 201 | 技术部 |
| 6 | F | 38 | 204 | 202 | 销售部 |
| 6 | F | 38 | 204 | 203 | 财政部 |
+--------+----------+------+---------+---------+-----------+
2.内连接
--查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。
select * from employee,department where employee.dept_id =department.dept_id;--select * from employee inner join department on employee.dept_id = department.dept_id;
+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
| 1 | A | 19 | 200 | 200 | 人事部 |
| 2 | B | 26 | 201 | 201 | 技术部 |
| 3 | C | 30 | 201 | 201 | 技术部 |
| 4 | D | 24 | 202 | 202 | 销售部 |
| 5 | E | 20 | 200 | 200 | 人事部 |
+--------+----------+------+---------+---------+-----------+
3.外连接
--(1)左外连接:在内连接的基础上增加左边有右边没有的结果
select * from employee left join department on employee.dept_id =department.dept_id;+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
| 1 | A | 19 | 200 | 200 | 人事部 |
| 5 | E | 20 | 200 | 200 | 人事部 |
| 2 | B | 26 | 201 | 201 | 技术部 |
| 3 | C | 30 | 201 | 201 | 技术部 |
| 4 | D | 24 | 202 | 202 | 销售部 |
| 6 | F | 38 | 204 | NULL | NULL |
+--------+----------+------+---------+---------+-----------+
--(2)右外连接:在内连接的基础上增加右边有左边没有的结果
select * from employee RIGHT JOIN department on employee.dept_id =department.dept_id;+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
| 1 | A | 19 | 200 | 200 | 人事部 |
| 2 | B | 26 | 201 | 201 | 技术部 |
| 3 | C | 30 | 201 | 201 | 技术部 |
| 4 | D | 24 | 202 | 202 | 销售部 |
| 5 | E | 20 | 200 | 200 | 人事部 |
| NULL | NULL | NULL | NULL | 203 | 财政部 |
+--------+----------+------+---------+---------+-----------+
--(3)全外连接:在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
--mysql不支持全外连接 full JOIN
--mysql可以使用此种方式间接实现全外连接
select * from employee RIGHT JOIN department on employee.dept_id =department.dept_idUNION
select * from employee LEFT JOIN department on employee.dept_id =department.dept_id;+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
| 1 | A | 19 | 200 | 200 | 人事部 |
| 2 | B | 26 | 201 | 201 | 技术部 |
| 3 | C | 30 | 201 | 201 | 技术部 |
| 4 | D | 24 | 202 | 202 | 销售部 |
| 5 | E | 20 | 200 | 200 | 人事部 |
| NULL | NULL | NULL | NULL | 203 | 财政部 |
| 6 | F | 38 | 204 | NULL | NULL |
+--------+----------+------+---------+---------+-----------+
--注意 union与union all的区别:union会去掉相同的纪录
2、多表查询之复合条件连接查询
--查询员工年龄大于等于25岁的部门
SELECT DISTINCTdepartment.dept_nameFROMemployee,departmentWHERE employee.dept_id =department.dept_idAND age>25;--以内连接的方式查询employee和department表,并且以age字段的升序方式显示
selectemployee.emp_id,employee.emp_name,employee.age,department.dept_namefromemployee,departmentwhere employee.dept_id =department.dept_idorder by age asc;
3、多表查询之子查询
--子查询是将一个查询语句嵌套在另一个查询语句中。--内层查询语句的查询结果,可以为外层查询语句提供查询条件。--子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字--还可以包含比较运算符:= 、 !=、> 、
--1. 带IN关键字的子查询
---查询employee表,但dept_id必须在department表中出现过
select * fromemployeewhere dept_id IN(select dept_id fromdepartment);+--------+----------+------+---------+
| emp_id | emp_name | age | dept_id |
+--------+----------+------+---------+
| 1 | A | 19 | 200 |
| 2 | B | 26 | 201 |
| 3 | C | 30 | 201 |
| 4 | D | 24 | 202 |
| 5 | E | 20 | 200 |
+--------+----------+------+---------+
5 rows in set (0.01sec)--2. 带比较运算符的子查询
--=、!=、>、>=、
--查询员工年龄大于等于25岁的部门
select dept_id,dept_name fromdepartmentwhere dept_id IN(select DISTINCT dept_id from employee where age>=25);--3. 带EXISTS关键字的子查询
--EXISTS关字键字表示存在。在使用EXISTS关键字时,内层查询语句不返回查询的记录。--而是返回一个真假值。Ture或False--当返回Ture时,外层查询语句将进行查询;当返回值为False时,外层查询语句不进行查询
select * fromemployeeWHERE EXISTS(SELECT dept_name from department where dept_id=203);--department表中存在dept_id=203,Ture
select * fromemployeeWHERE EXISTS(SELECT dept_name from department where dept_id=205);--Empty set (0.00 sec)
ps:create table t1(select * from t2);
三、课后练习
练习一:
1、将所有的课程的名称以及对应的任课老师姓名打印出来
SELECT cid,cname,tname FROM course LEFT JOIN teacher on course.teacher_id=teacher.tid;
2、查询学生表中男女生各有多少人
SELECT gender,COUNT(gender) 人数 FROM student GROUP BY gender;
3、查询物理成绩等于100的学生的姓名
SELECT sid,sname FROM student WHERE sid in (SELECT student_id FROM score WHERE course_id=(SELECT cid FROM course WHERE cname='物理') and num=100);
4、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT sname,AVG(num) FROM student,score WHERE student.sid=score.student_id GROUP BY student_id HAVING AVG(num)>80;
5、查询所有学生的学号,姓名,选课数,总成绩
SELECT student_id 学号 ,sname 姓名,COUNT(course_id) 选课数,SUM(num) 总成绩 FROM student,score WHERE student.sid=student_id GROUP BY student_id;
6、查询姓李老师的个数
SELECT COUNT(tname) 姓李老师个数 from teacher WHERE tname like '李%';
7、查询没有报李平老师课的学生姓名
SELECT sid,sname FROM student WHERE sid NOT IN (SELECT distinct student_id from score LEFT JOIN student on student_id=student.sid WHERE course_id in (SELECT cid from course,teacher WHERE teacher_id=tid and tname='李平老师'));
mysql> select sid,sname from student where sid not in (SELECT distinct student_id from score where course_id in (SELECT cid from course,teacher WHERE teacher_id=tid and tname='李平老师'));
8、查询物理课程比生物课程高的学生的学号
SELECT A.student_id FROM
(SELECT * FROM score WHERE course_id=(SELECT cid FROM course WHERE cname='物理'))A
INNER JOIN
(SELECT * FROM score WHERE course_id=(SELECT cid FROM course WHERE cname='生物')) B ON A.student_id = B.student_id WHERE A.num>B.num
9、查询没有同时选修物理课程和体育课程的学生姓名
SELECT sid,sname from student WHERE sid NOT IN (SELECT student_id from score WHERE course_id in (2,3) GROUP BY student_id HAVING COUNT(student_id)=2);
10、查询挂科超过两门(包括两门)的学生姓名和班级
SELECT sname,caption FROM student,class WHERE class_id=cid and sid IN (SELECT student_id FROM score WHERE num<60 GROUP BY student_id HAVING COUNT(student_id)>=2);
11 、查询选修了所有课程的学生姓名
SELECT sname FROM student WHERE sid in (SELECT student_id FROM score GROUP BY student_id HAVING COUNT(course_id)=(SELECT COUNT(cid) FROM course));
12、查询李平老师教的课程的所有成绩记录
SELECT sid,sname,cname,num FROM student RIGHT JOIN (SELECT student_id,cname,num FROM score,course WHERE course_id=cid AND course_id in (SELECT cid from course,teacher WHERE teacher_id=tid and tname='李平老师')) ls on ls.student_id=student.sid;
13、查询选课学生都选修了的课程号和课程名
SELECT cid,cname FROM course WHERE cid =(SELECT course_id from score GROUP BY course_id HAVING COUNT(student_id)=(select count(distinct student_id) from score))
练习二:
14、查询每门课程被选修的次数
SELECT course_id,cname,COUNT(course_id) 被选课次数 FROM score,course WHERE cid=course_id GROUP BY course_id;
15、查询只选修了一门课程的学生姓名和学号
SELECT sid,sname FROM student WHERE sid =(SELECT student_id FROM score GROUP BY student_id HAVING COUNT(course_id)=1);
16、查询所有学生考出的成绩并按从高到低排序(成绩去重)
SELECT DISTINCT num FROM score ORDER BY num DESC;
17、查询平均成绩大于85的学生姓名和平均成绩
SELECT sname,AVG(num) FROM score,student WHERE student_id =student.sid GROUP BY student_id HAVING AVG(num)>85;
18、查询生物成绩不及格的学生姓名和对应生物分数
SELECT sname,num FROM score,student WHERE student_id=student.sid AND course_id=(SELECT cid FROM course WHERE cname='生物') and num<60;
19、查询在所有选修了李平老师课程的学生中,这些课程(李平老师的课程,不是所有课程)平均成绩最高的学生姓名
SELECT student_id,sname,AVG(num) FROM score,student WHERE student_id=student.sid AND course_id in (SELECT cid FROM course WHERE teacher_id =( SELECT tid FROM teacher WHERE tname like '李平%')) GROUP BY student_id ORDER BY AVG(num) DESC LIMIT 1;
20、查询每门课程成绩最好的前两名学生姓名
21、查询不同课程但成绩相同的学号,课程号,成绩
SELECT * FROM score WHERE student_id IN (SELECT student_id FROM score GROUP BY num,student_id HAVING COUNT(student_id)>=2);
22、查询没学过“李平”老师课程的学生姓名以及选修的课程名称;
SELECT * from score WHERE student_id NOT IN (select distinct student_id from score where course_id in (SELECT cid FROM
course WHERE teacher_id =( SELECT tid FROM teacher WHERE tname like '李平%')));
23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名;
SELECT sid,sname FROM student WHERE sid IN (SELECT DISTINCT student_id FROM score WHERE course_id IN (SELECT course_id from score WHERE student_id=1) AND student_id!=1);
24、任课最多的老师中学生单科成绩最高的学生姓名
SELECT sname FROM student WHERE sid IN (SELECT student_id FROM score,(SELECT course_id,MAX(num) 最大值 FROM score WHERE course_id in (SELECT cid FROM course WHERE teacher_id=(SELECT teacher_id FROM course GROUP BY teacher_id ORDER BY COUNT(cid) DESC LIMIT 1)) GROUP BY course_id HAVING max(num)) A WHERE A.最大值=score.num AND score.course_id=A.course_id);