142 MySQL多表练习
一、练习
1.1 表与数据准备
company.employee
员工id id int
姓名 emp_name varchar
性别 sex enum
年龄 age int
入职日期 hire_date date
岗位 post varchar
职位描述 post_comment varchar
薪水 salary double
办公室 office int
部门编号 depart_id varchar
# 创建员工信息表
create table employee(id int primary key auto_increment,
emp_name varchar(64) not null,
sex enum('male','female')not null default 'male',
age int unsigned not null default 25,
hire_data date not null,
post varchar(64),
post_comment varchar(100),
salary decimal(7,2),
office int,
depart_id varchar(64) default '销售部'
);
# 查看表结构
mysql> desc employee;
+--------------+-----------------------+------+-----+-----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+-----------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| emp_name | varchar(64) | NO | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(10) unsigned | NO | | 25 | |
| hire_data | date | NO | | NULL | |
| post | varchar(64) | YES | | NULL | |
| post_comment | varchar(100) | YES | | NULL | |
| salary | decimal(7,2) | YES | | NULL | |
| office | int(11) | YES | | NULL | |
| depart_id | varchar(64) | YES | | 销售部 | |
+--------------+-----------------------+------+-----+-----------+----------------+
# 插入记录
# 三个部门:教学部，销售部，运营部
insert into employee(emp_name,sex,age,hire_data,post,post_comment,salary,office,depart_id)values
('张三','male','25','20170301','teacher','教学',7843.2,401,'教学部'),#以下全是教学部
('李四','male','26','20150411','teacher','教学',6543.1,401,'教学部'),
('王二','female','24','20170621','teacher','教学',6631.5,401,'教学部'),
('刘五','male','36','20150411','teacher','教学',6200.2,401,'教学部'),
('陈雨','female','18','20191119','teacher','教学',8564.5,401,'教学部'),
('刘强','male','42','20140506','teacher','教学',9543,401,'教学部'),
('陈二','male','28','20150301','sale','销售',6843.2,402,'销售部'),#以下全是销售部
('汪雨','male','20','20140411','sale','销售',5543.1,402,'销售部'),
('刘高','female','24','20150621','sale','销售',5631.5,402,'销售部'),
('刘七','male','18','20180411','sale','销售',4200.2,402,'销售部'),
('张雨','female','18','20131119','sale','销售',9564.5,402,'销售部'),
('许嵩','male','26','20120506','sale','销售',9543,402,'销售部'),
('张天','male','48','20150401','operation','运营维护',8843.2,403,'运营部'),#以下全是运营部
('梁田','male','38','20140811','operation','运营维护',7543.1,403,'运营部'),
('陈晓','female','24','20160521','operation','运营维护',5631.5,403,'运营部'),
('黄磊','male','18','20150411','operation','运营维护',4200.2,403,'运营部'),
('禾苗','female','18','20130129','operation','运营维护',9564.5,403,'运营部'),
('杨宇','female','42','20120506','operation','运营维护',9543,403,'运营部');
2.2 题目
1.查询出每个部门每次最新入职的那位员工
# 注解，其实就是需要连表去查询，首先需要先按部门分组查询每个组入职日期，再去和真实的表进行内连接，也就浙江一个sql语句的结果作为临时表去和真实表进行内连接，把两个表有关联的数据拿出来
select
*
from
employee
JOIN(
select
post,
max(hire_data) max_hire_data
from
employee
group by
post
)as t2 on t1.post = t2.post
where
employee.hire_data = t2.max_hire_data;
二、综合练习
2.1 数据准备
# 先创建一个班级表，并导入数据
CREATE TABLE class (
cid int(11) NOT NULL AUTO_INCREMENT,
caption varchar(32) NOT NULL,
PRIMARY KEY (cid)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
BEGIN;
INSERT INTO class VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;
# 创建一个学生表并导入数据，和班级的cid创建外键
CREATE TABLE student (
sid int(11) NOT NULL AUTO_INCREMENT,
gender char(1) NOT NULL,
class_id int(11) NOT NULL,
sname varchar(32) NOT NULL,
PRIMARY KEY (sid),
fk_class (class_id),
FOREIGN KEY (class_id) REFERENCES class (cid)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
BEGIN;
INSERT INTO student VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;
# 创建一个教师表，并插入数据
CREATE TABLE teacher (
tid int(11) NOT NULL AUTO_INCREMENT,
tname varchar(32) NOT NULL,
PRIMARY KEY (tid)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
BEGIN;
INSERT INTO teacher VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
# 创建一个课程表，和教师表的tid建立外键，对应的教师教授哪一门课程
CREATE TABLE course (
cid int(11) NOT NULL AUTO_INCREMENT,
cname varchar(32) NOT NULL,
teacher_id int(11) NOT NULL,
PRIMARY KEY (cid),
KEY fk_course_teacher (teacher_id),
FOREIGN KEY (teacher_id) REFERENCES teacher (tid)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
BEGIN;
INSERT INTO course VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;
# 创建学生成绩表，和课程信息的课程id添加外键，，和学生的sid添加外键，每个学生的每门课程的成绩，并插入数据
CREATE TABLE score (
sid int(11) NOT NULL AUTO_INCREMENT,
student_id int(11) NOT NULL,
course_id int(11) NOT NULL,
num int(11) NOT NULL,
PRIMARY KEY (sid),
KEY fk_score_student (student_id`),
KEY fk_score_course (course_id`),
FOREIGN KEY (course_id) REFERENCES course (cid),
FOREIGN KEY (student_id) REFERENCES student (sid)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
BEGIN;
INSERT INTO score VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;
2.2 表的数据
# 班级表数据
+-----+--------------+
| cid | caption |
+-----+--------------+
| 1 | 三年二班 |
| 2 | 三年三班 |
| 3 | 一年二班 |
| 4 | 二年九班 |
+-----+--------------+
# 学生表数据
+-----+--------+----------+--------+
| sid | gender | class_id | sname |
+-----+--------+----------+--------+
| 1 | 男 | 1 | 理解 |
| 2 | 女 | 1 | 钢蛋 |
| 3 | 男 | 1 | 张三 |
| 4 | 男 | 1 | 张一 |
| 5 | 女 | 1 | 张二 |
| 6 | 男 | 1 | 张四 |
| 7 | 女 | 2 | 铁锤 |
| 8 | 男 | 2 | 李三 |
| 9 | 男 | 2 | 李一 |
| 10 | 女 | 2 | 李二 |
| 11 | 男 | 2 | 李四 |
| 12 | 女 | 3 | 如花 |
| 13 | 男 | 3 | 刘三 |
| 14 | 男 | 3 | 刘一 |
| 15 | 女 | 3 | 刘二 |
| 16 | 男 | 3 | 刘四 |
+-----+--------+----------+--------+
# 教师表数据
+-----+-----------------+
| tid | tname |
+-----+-----------------+
| 1 | 张磊老师 |
| 2 | 李平老师 |
| 3 | 刘海燕老师 |
| 4 | 朱云海老师 |
| 5 | 李杰老师 |
+-----+-----------------+
# 课程表数据
+-----+--------+------------+
| cid | cname | teacher_id |
+-----+--------+------------+
| 1 | 生物 | 1 |
| 2 | 物理 | 2 |
| 3 | 体育 | 3 |
| 4 | 美术 | 2 |
+-----+--------+------------+
# 成绩表信息由于过多，此处就不展示了
2.3 练习题目
-
查询男生、女生的人数；
# 按照年龄分组 select gender,count(sid) from studnet group by gender; -
查询姓“张”的学生名单；
# 用相似符合关键字like来从学生表里查询学生的姓名，条件是姓名第一个字是张like '张%' select sname from student where sname like '张%'; -
课程平均分从高到低显示
# 将成绩表score中将课程号进行分组，然后利用聚合函数对每组的分数进行avg(),之后order by select course_id 课程号,avg(num) 平均分 from score group by course_id order by 平均分 desc; -
查询有课程成绩小于60分的同学的学号、姓名；
# 先查询成绩表的学生成绩小于60的学生，然后对其学生学号分组 # 将这个sql查询语句的结果作为一个临时表，和学生表进行内连接，将学生的学号和姓名提取 select sid, sname from student join (select student_id,GROUP_CONCAT(num) from score where num<60 GROUP by student_id) as t1 on t1.student_id=student.sid; -
查询至少有一门课与学号为1的同学所学课程相同的同学的学号和姓名；
# 先对成绩表中的学号为1的学生的课程号拿出：select course_id from score where student_id=1 # 对成绩表中学号大于1的学生课程号进行条件匹配，比较是否在上一个系查询的结果中，把上一个子查询作为条件 # 拿出学号大于1且课程号在学号1的选择的课程中的学生学号，之后和学生表进行内连接，取出这些学生的学号姓名 select sid,sname from student join (select student_id,GROUP_CONCAT(course_id) from score where course_id in (select course_id from score where student_id=1) AND student_id>1 GROUP BY student_id) as t1 on student.sid=t1.student_id; -
查询出只选修了一门课程的全部学生的学号和姓名；
# 先对成表中学生的学号进行排序，用count()聚合函数将统计学生的选择的课程数量，用having对聚合函数后的结果进行筛选 # 拿到只选择1门课程的学生学号，和学生表student进行内连接 select sid,sname from student join (select student_id,count(course_id) from score group by student_id having count(course_id)=1) as t1 on student.sid=t1.student_id; -
查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
select course_id 课程ID,max(num) 最高分,min(num)最低分 from score GROUP BY course_id; -
查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
# 先查询成绩表里都选择课程1的所有学生--子查询----临时表t1 select t1.student_id from (select student_id,course_id,num from score where course_id=1 # 再查询成绩表里都选择课程2的所有学生--子查询----临时表t2 select student_id,course_id,num from score where course_id=2 # 通过t1和t2表进行内连接，得到有关联的数据学生的学号 from student where sid in (select t1.student_id from (select student_id,course_id,num from score where course_id=1)as t1 left join (select student_id,course_id,num from score where course_id=2)as t2 on t1.student_id=t2.student_id # 拿到符合条件的学号的查询结果，作为去学生表student里查询学生学号和姓名的条件 select sid,sname from student where sid in (select t1.student_id from (select student_id,course_id,num from score where course_id=1)as t1 left join (select student_id,course_id,num from score where course_id=2)as t2 on t1.student_id=t2.student_id where t1.num>t2.num) -
查询“生物”课程比“物理”课程成绩高的所有学生的学号；
select t1.sid from( (select * from score where course_id in (select cid from course where cname='生 物')) as t1 left join (select * from score where course_id in (select cid from course where cname='物 理')) as t2 on t1.student_id=t2.student_id) where t1.num>t2.num; -
查询平均成绩大于60分的同学的学号和平均成绩;
# 先查询学生的平均成绩 select student_id,avg(num) 平均成绩 from score group by student_id # 筛选平均成绩大于60的学生学号 -
查询所有同学的学号、姓名、选课数、总成绩；
# 先查询所有学生的学号，选课数，总成绩 select student_id,count(course_id),sum(num) from score group by student_id # 再与学生表student进行内连接，查询学生的名字 select student.sid,student.sname,选课数,总分 from student join (select student_id,count(course_id) 选课数,sum(num) 总分 from score group by student_id)as t1 where student.sid=t1.student_id; -
查询姓“李”的老师的个数；
select count(tid) from teacher where tname like '李%' -
查询没学过“张磊老师”课的同学的学号、姓名；
# 先查询张磊老师老师的编号：(select tid from teacher where tname='张磊老师') # 然后作为查询张磊老师所带的课程号的条件：select cid from course where teacher_id in (select tid from teacher where tname='张磊老师')) #从成绩表里查询所有学生的课程id=张磊老师所带课程的课程号的学生学号 select student_id from score where course_id=(select cid from course where teacher_id in (select tid from teacher where tname='张磊老师'))) # 将上面的一个结果作为整体的一个执行结果相当于是一个表，在去学生表里查询学生学号在这个范围内的姓名和学号 select sid sname from student where sid not in (select student_id from score where course_id=(select cid from course where teacher_id in (select tid from teacher where tname='张磊老师'))) -
查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
# 先从成绩表里查询所欲选择课程1的学生 (select t1.student_id from (select student_id from score where course_id=1)as t1 # 再从成绩表里选择所欲选择课程2的学生 (select student_id from score where course_id=1)as t2 # 把这两个表进行内连接取出有用的学生学号 select t1.student_id from (select student_id from score where course_id=1)as t1 join (select student_id from score where course_id=1)as t2 on t1.student_id=t2.student_id) # 将所有学号取出之后，再从学生表里进行条件匹配 select sid,sname from student where sid in (select t1.student_id from (select student_id from score where course_id=1)as t1 join (select student_id from score where course_id=1)as t2 on t1.student_id=t2.student_id) -
查询学过“李平老师”所教的所有课的同学的学号、姓名；
select sid,sname from student where sid in (select student_id from score where course_id=(select tid from teacher where tname='李平老师'))
