MySQL单表多表查询练习

HealerCCX

已于 2023-01-09 17:27:18 修改

阅读量977

点赞数 3

文章标签： mysql 数据库 sql

于 2023-01-09 17:21:15 首次发布

本文链接：https://blog.csdn.net/HealerCCX/article/details/128617779

版权

一、单表查询

1、创建数据库，准备表及数据

/*创建数据库*/
create database if not EXISTS students character set utf8 collate utf8_general_ci;
/*创建表*/
use students;
create table if not EXISTS student
(
	stuID  int(5)  not null primary key,
	stuName  varchar(50)  not null,
	stuSex CHAR(10),
	stuAge smallint
);

CREATE TABLE if not EXISTS courses(
		 couID int  not null primary key auto_increment COMMENT '学号',
		 couName varchar(50) not null DEFAULT('大学英语'),
		 couHours  smallint UNSIGNED COMMENT '学时',
		 couCredit  float DEFAULT(2) COMMENT '学分'
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE  utf8mb4_0900_ai_ci;

CREATE TABLE if not EXISTS stu_cou(
		 ID int not null primary key auto_increment,
		 stuID int(5)  not null  COMMENT '学号',
		 couID int  not null  COMMENT '课程编号',
		 time timestamp not null DEFAULT(now())
);

 /*添加外键约束*/
 alter table stu_cou add CONSTRAINT fk_stuid foreign key(stuID) REFERENCES student(stuID) ON UPDATE CASCADE ON DELETE CASCADE ;
 alter table stu_cou add CONSTRAINT fk_couid foreign key(couID) REFERENCES courses(couID) ON UPDATE CASCADE ON DELETE CASCADE ;

  /*插入数据*/
 insert into student(stuID,stuName,stuSex,stuAge) values(1001,'张三','男',19),(1002,'李四','男',18),(1003,'王五','男',18),(1004,'黄丽丽','女',18),(1005,'李晓辉','女',19),(1006,'张敏','女',18); insert into student VALUES(1007,'五条人','男',20),(1008,'胡五伍','女',19);
 insert into courses(couID,couName,couHours,couCredit) values(50,'大学英语',64,2),(60,'计算机基础',78,2.5),(70,'Java程序设计',108,6),(80,'数据库应用',48,2.5);
 insert into stu_cou(stuID,couID) values(1001,50),(1001,60),(1001,70),(1001,80),(1002,50),(1002,60),(1002,70),(1002,80),(1003,50),(1003,60),(1003,70),(1003,80),(1004,50),(1004,60),(1004,70),(1004,80),(1005,50),(1005,60),(1005,70),(1005,80),(1006,50),(1006,60),(1006,70),(1006,80);

2、练习

例1：查询student表中所有数据。

mysql> select * from student;

例2：查询指定列的数据，查询student表中stuID,stuName两列。

mysql> select stuID,stuName from student;

例3：给列指定别名，查询student表中stuID列，指定别名为学号，stuName列，指定别名为姓名。

mysql> select stuID as '学号',stuName as '姓名' from student;

例4：查询student表中stuID为1001的记录。

mysql> select * from student where stuID=1001;

例5：查询student表中性别为男且年龄小于19岁的记录。

mysql> select * from student where stuSex='男' and stuAge<19;

例6：查询student表中性别为女或年龄等于18岁的记录。

mysql> select * from student where stuSex='女' or stuAge=18;

例7：查询student表中学号为1001和1004的记录。

mysql> select * from student where stuID in(1001,1004);

例8：查询student表中学号为1001到1004的记录。

mysql> select * from student where stuID between 1001 and 1004;

例9：查询student表中学号不包含1001到1004的记录。

mysql> select * from student where stuID not between 1001 and 1004;

例10：查询student表中所有姓张的同学的信息。

mysql> select * from student where stuName like '张%';

例11：查询student表中姓名包含“丽”字的同学的信息。

mysql> select * from student where stuName like '%丽%';

例12：分别查询student表中姓名包含“五”字的同学的信息。

mysql> select * from student where stuName like '%五%';

例13：查询student表中的记录，根据年龄进行升序排列，降序排列。

mysql> select * from student order by stuAge asc;

mysql> select * from student order by stuAge desc;

例14：查询student表中的记录，根据年龄进行升序排列，姓名进行降序排列。

mysql> select * from student order by stuAge asc,convert(stuName using gbk) desc;

例15：使用聚合函数查询学生总人数。

mysql> select count(*) as '学生总人数' from student;

例16：使用聚合函数查询课程表中所有课程的学分和。

mysql> select sum(couCredit) as '总学分' from courses;

例17：分别使用聚合函数查询年龄最大的学生和年龄最小的学生的年龄。

mysql> select max(stuAge) as '年龄最大' from student;

mysql> select min(stuAge) as '年龄最小' from student;

例18：使用聚合函数查询课程表中所有课程的平均学时数。

mysql> select avg(couHours) as '平均学时数' from courses;

例19：使用分组查询输出学生性别。

mysql> select stuSex 性别  from student  GROUP  BY stuSex;

例20：使用分组查询输出学生学号和姓名。

mysql> select stuID,stuName from student group by stuID;

例21：分组查询学生表中男女同学的人数。

mysql> select stuSex,count(stuID) as '人数' from student group by stuSex;

例22：分组查询学生表中男女同学的平均年龄，人数，男女同学年龄最大值，年龄最小值。

mysql> select stuSex,avg(stuAge) as '平均年龄',count(stuID) as '人数',max(stuAge) as '最大年龄',min(stuAAge) as '最小年龄' from student group by stuSex;

例23：分组查询学生表中男女同学各年龄段的人数。

mysql> select stuSex,stuAge,COUNT(stuID) as '人数' from student group by stuSex,stuAge;

二、多表查询练习

1、准备数据

#创建表及插入记录
CREATE TABLE class (
  cid int(11) NOT NULL AUTO_INCREMENT,
  caption varchar(32) NOT NULL,
  PRIMARY KEY (cid)
) ENGINE=InnoDB CHARSET=utf8;

INSERT INTO class VALUES
(1, '三年二班'), 
(2, '三年三班'), 
(3, '一年二班'), 
(4, '二年九班');

CREATE TABLE teacher(
  tid int(11) NOT NULL AUTO_INCREMENT,
  tname varchar(32) NOT NULL,
  PRIMARY KEY (tid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO teacher VALUES
(1, '张磊老师'), 
(2, '李平老师'), 
(3, '刘海燕老师'), 
(4, '朱云海老师'), 
(5, '李杰老师');

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),
  CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher (tid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO course VALUES
(1, '生物', 1), 
(2, '物理', 2), 
(3, '体育', 3), 
(4, '美术', 2);

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),
  KEY fk_class (class_id),
  CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES class (cid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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, '刘四');

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),
  CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course (cid),
  CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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);

2、练习

1、查询所有的课程的名称以及对应的任课老师姓名

mysql> select course.cname,teacher.tname from course inner join teacher on course.teacher_id = teacher.tid;

2、查询学生表中男女生各有多少人

mysql> select gender 性别,count(1) 人数 from student group by gender;

3、查询物理成绩等于100的学生的姓名

mysql> select student.sname from student where sid in ( select student_id from score inner join course on score.course_id = course.cid where course.cname = '物理' and score.num = 100);

4、查询平均成绩大于八十分的同学的姓名和平均成绩

mysql> select sname,avg(num) from student inner join score on student_id =student.sid group by sname having avg(num)>80;

5、查询所有学生的学号，姓名，选课数，总成绩

mysql> select student.sid,sname,count(course_id),sum(num) from student inner join score on student_id=student.sid group by student.sid ;

6、查询姓李老师的个数

mysql> select tname from teacher WHERE tname like '李%' GROUP BY tname;

7、查询没有报李平老师课的学生姓名

mysql> select student.sname from student where sname not in(select sname from student  inner join score on student.sid =score.student_id WHERE course_id NOT in (select cid from course inner join teacher on teacher_id = tid where tname = '李平老师'));

8、查询物理课程比生物课程高的学生的学号

mysql> select t1.student_id FROM (select student_id,num from score WHERE course_id =(select cid from course where cname='物理'))as t1 inner join (select student_id,num from score WHERE course_id =(select cid from course where cname='生物'))as t2 on t1.student_id=t2.student_id WHERE t1.num>t2.num;

9、查询没有同时选修物理课程和体育课程的学生姓名

mysql> select sname from student WHERE sid not in ( select t1.student_id from (select student_id from score where course_id =(select cid from course where cname='物理'))as t1 inner join (select student_id from score where course_id =(select cid from course where cname='体育'))as t2 on t1.student_id=t2.student_id );

10、查询挂科超过两门(包括两门)的学生姓名和班级

mysql> select sname,caption from (select sname,class_id from student where student.sid in (select student_id from score where num<60 having (count(num<60)>=2))) as b left join class on class.cid = b.class_id;

11、查询选修了所有课程的学生姓名

mysql> select sname from student inner join score on student_id=student.sid group by student_id having count(class_id)=(select count(cid) from course);

12、查询李平老师教的课程的所有成绩记录

mysql> select num from score where course_id in (select cid from course where teacher_id=(select tid from teacher where tname='李平老师'));

13、查询全部学生都选修了的课程号和课程名

mysql> select cname from course where cid=(select course_id from student inner join score on student.sid = student_id group by course_id having count(course_id)=(select count(sid) from student));

14、查询每门课程被选修的次数

mysql> select course_id,count(course_id) from student inner join score on student.sid = student_id group by course_id ;

15、查询之选修了一门课程的学生姓名和学号

mysql> select sname,sid from student where sid in (select student_id from score group by student_id having count(course_id)=1);

16、查询所有学生考出的成绩并按从高到低排序（成绩去重）

mysql> select distinct num from score order by num desc;

17、查询平均成绩大于85的学生姓名和平均成绩

mysql> select sname,avg(num) from score inner join student on student.sid=student_id group by sname having avg(num)>85;

18、查询生物成绩不及格的学生姓名和对应生物分数

mysql> select sname,b.num from student inner join ((select student_id,num from score where course_id=(select cid from course where cname='生物') having num<60))as b on student.sid=b.student_id ;

19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

mysql> select sname from student where sid =(select student_id from score where course_id in (select cid from course where teacher_id=(select tid from teacher where tname='李平老师')) group by student_id order by  avg(num) desc limit 1);

进阶练习一

1、创建数据

CREATE TABLE `t_dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `t_emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(20) DEFAULT NULL,
  `age` INT(3) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
empno int  not null,
 PRIMARY KEY (`id`),
 KEY `idx_dept_id` (`deptId`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');

INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,null,100010);

ALTER TABLE `t_dept` 
add  CEO  INT(11)  ;
update t_dept set CEO=2 where id=1;
update t_dept set CEO=4 where id=2;
update t_dept set CEO=6 where id=3;
update t_dept set CEO=8 where id=4;
update t_dept set CEO=9 where id=5;

2、练习

1 所有有门派的人员信息

mysql> select * from t_emp,t_dept where  t_emp.deptId = t_dept.id;

2 列出所有用户，并显示其门派信息

mysql> select t_emp.name,IFNULL(t_dept.deptName,'没有门派') as '门派' from  t_emp LEFT JOIN t_dept on t_emp.deptId = t_dept.id;

3 列出不入派的人员：

mysql> select name from t_emp where  deptId is null ;

4 所有没人入的门派：

mysql> select * from t_dept b left join t_emp a on a.deptId= b.id where a.deptId is null;

5 列出所有人员和门派的对照关系

mysql> select * from t_emp,t_dept where t_emp.deptId = t_dept.id;

6 列出所有没入派的人员和没人入的门派

mysql> select * from t_emp left join t_dept on t_emp.deptId = t_dept.id where t_dept.id is null union select * from t_dept left join t_emp  on t_emp.deptId = t_dept.id where t_emp.deptId is null;

7 求各个门派对应的掌门人名称:

mysql> select a.name from t_dept b left join t_emp a on b.CEO=a.id;

8 求所有当上掌门人的平均年龄:

mysql> select avg(age) from t_dept left join t_emp on t_emp.id = t_dept.CEO ;

9 求所有人物对应的掌门名称:

mysql> select t_emp.name,c.name from t_emp left join (select t_emp.name,t_emp.deptId as id from t_dept left join t_emp on t_emp.id = t_dept.CEO )c on c.id = t_emp.deptId;