MySQL复杂查询

一、实验目的

1.了解多表连接和子查询的基本运算。

2.掌握多表连接方法。

3.掌握子查询的方法。

4.掌握正则表达式在查询中的应用。

二、实验内容

1、创建学生成绩（xscj）数据库，在数据库中创建student、course、score、teacher四张数据表。

（1）具体结构如下  ：

表1 student表

字段名	数据类型	主键	非空	唯一	自增	说明
s_no	bigint(11)	是	是	是	是	学生学号
s_name	varchar(50)	否	是	否	否	学生姓名
s_sex	char(2)	否	否	否	否	性别
s_bir	date	否	否	否	否	出生日期
phone	varchar(13)	否	否	是	否	电话
email	varchar(50)	否	否	是	否	电子邮件

表2 score表

字段名	数据类型	主键	非空	唯一	自增	说明
s_no	bigint(11)	是	是	否	否	学生学号
c_no	char(13)	是	是	否	否	课程编号
daily	float	否	否	否	否	平时成绩
final	float	否	否	否	否	结业成绩

表3 course表

字段名	数据类型	主键	非空	唯一	自增	说明
c_no	char(13)	是	是	否	否	课程编号
c_name	varchar(50)	否	否	否	否	课程名称
t_no	char(10)	是	是	否	否	教师编号
hour	float	否	否	否	否	学分
week	int(2)	否	否	否	否	教学周
semester	int(1)	否	否	否	否	开课学期

表4 teacher表

字段名	数据类型	主键	非空	唯一	自增	说明
t_no	char(10)	是	是	是	否	教师号
t_name	varchar(10)	否	是	否	否	教师姓名
major	char(10)	否	否	否	否	专业
prof	char(6)	否	否	否	否	职称
department	char(10)	否	否	否	否	院系部门

（2）在对应的表中，插入如下数据：

表1  student 表记录

s_no	s_name	s_sex	s_bir	phone	email
18122221320	张凯	男	2001/5/6	13245678547	36451@qq.com
18122221321	赵峰	男	1999/5/4	13945678548	ABTHE@SOHU.COM
18122221322	王伟	男	2003/8/9	13245678549	45SSEEE@163.COM
18122221323	李成	男	2001/9/12	13245678550	78962@qq.com
18122221324	赵晓月	女	2002/12/4	13545678551	3232459@qq.com
18122221325	王俊鑫	男	2003/4/7	13845678552	8989456@qq.com
18137221506	王枫	男	2001/10/5	13645678553	WSWS4875@SOHU.COM
18137221507	李浩	男	2000/11/23	13888128558	46SSEEE@163.COM
18137221508	孙琳	女	2002/5/1	13522078555	124578@qq.com
19112100070	陈晨	男	2003/4/8	13243271256	457822SW@SOHU.COM
19112100071	吴司	男	1999/12/4	13244673357	JEUD@163.COM
19112100072	李珊	女	1998/12/25	13955089958	78451@qq.com
19112100073	钱峰	男	2000/4/1	13945322359	BBBTIH@SOHU.COM
19112100074	张雯	女	2000/12/3	13545612360	SSSMOU@163.COM

表2  score表记录

s_no	c_no	daily	final
18122221320	c08123	85	95
18122221320	a01564	88	93
18122221320	C06108	92	90
18122221321	c08123	85	94
18122221321	C06108	70	82
18122221322	c08123	60	64
18122221322	a01327	53	50
18122221322	a01564	64	50
18122221323	a01564	88	84
18122221323	C05103	85	86
18122221324	c08123	77	81
18122221324	C05103	95	86
18122221325	C05103	88	90

表3   course 表记录

c_no	c_name	t_no	hour	week	semester
c08123	数据库技术	t07019	4	16	4
c08123	数据库技术	t03117	4	16	4
C06108	数据结构	t07019	4	16	3
C06108	数据结构	t01247	4	16	3
C05103	计算机原理	t01247	4	16	2
C05103	计算机原理	t03117	4	16	2
a01327	高等数学	t00458	4	16	2
a01327	高等数学	t00578	4	16	2
a01564	马克思原理	t04410	2	12	1
a01564	马克思原理	t04115	2	12	1

表4  teacher 表记录

t_no	t_name	major	prof	department
t01247	程瑞	软件工程	副教授	计算机学院
t07019	刘泽	软件工程	讲师	软件学院
t04213	汪凌灵	网络技术	副教授	计算机学院
t04115	刘珊	哲学	讲师	人文学院
t00458	李泽峰	数学	助教	基础课程学院
t00578	张伦	数学	讲师	基础课程学院
t02145	王乐	英语	助教	基础课程学院
t04410	王伟	哲学	副教授	人文学院
t03117	孙艳	软件工程	讲师	软件学院

2.复杂查询统计设计。根据下面的需求，利用选择应用多表连接、子查询和正则表达式相关理论进行复杂查询统计设计。 

1.查询选修课程号为c06108的学生的学号、姓名和结业成绩。

源代码：

mysql> select student.S_no,student.S_name,score.final from student inner join score

    -> on score.S_no=student.S_no

-> where C_no='c06108';

结果截图：

2.利用左外连接方式查询学生的学号、姓名、平时成绩和结业成绩。

源代码：

mysql> select student.S_no,S_name,daily,final

    -> from student left join score

    -> on student.S_no=score.S_no;

结果截图

 

 

3.利用右外连接方式查询教师的排课情况，要求显示课程编号、课程名称、学分、教学周、开课学期、教师编号和教师姓名。

源代码：

mysql> select C_no,C_name,hour,week,teacher.T_no,T_name

    -> from teacher right join course

    -> on teacher.T_no=course.T_no;

结果截图

 

4.查询19级学生的学号、姓名、课程名、结业成绩及学分。

源代码：

mysql> select student.S_no,S_name,C_name,final,hour

    ->  from score join student

    -> on student.S_no=score.S_no

    -> join course on score.C_no=course.C_no

    -> where left(student.S_no,2)='19';

 

结果截图

 

5.查询结业成绩高于90分、总评成绩高于85分的学生的学号、课程号和总评成绩。总评成绩=final*0.8+daily*0.2。

源代码：

mysql> select TT.S_no,TT.C_no,TT.final*0.8+daily*0.2

    -> from (select* from score where final>90)as TT

    -> where TT.final*0.8+TT.daily*0.2>85;

结果截图

 

6.查询结业成绩比选修该课程平均结业成绩低的学生的学号、课程号和结业成绩。

源代码：

mysql> select S_no,C_no,final

    -> from score as a

    -> where final<(select avg(final)

    -> from score as b

    -> where a.C_no=b.C_no

    -> group by C_no);

结果截图

 

7.获取结业成绩中含有高于90分的学生的学号、姓名、电话和e-mail。

源代码：

mysql> select S_no,S_name,phone,email

    -> from student

    -> where S_no in(select S_no

    -> from score

    -> where final>90);

结果截图
 

 

8．查找score表中所有比c05103课程结业成绩都高的学号、姓名、电话和期末成绩。

源代码：

mysql> select student.S_no,S_name,phone,final

    -> from score inner join student

    -> on score.S_no=student.S_no

    -> where final >all

    -> (select final from score where C_no='c05103');

结果截图

 

9.将student表中1999年以后出生的学生纪录添加到stud表中。

源代码：

mysql> insert into stud

    -> (select* from student where S_bir>'1999-12-31');

结果截图

 

 

10.查询student表中学生电话号码尾数为8的学生的学号、姓名、电话。000

源代码：

mysql> select S_no,S_name,phone,email

    -> from student

    -> where phone regexp'8$';

结果截图