MySQL的简单查询

一、实验目的

1.掌握select语句的格式和各子句的功能。

2.掌握 where子句中like、in、 between、and、is等逻辑运算符的使用。

3.掌握 group by 语句和聚合函数的使用。

二、实验内容

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

（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	孙艳	软件工程	讲师	软件学院

1. select...from...where基本子句的使用。

①在student表中查询出生日期在2003年以前的学生的学号、姓名、电话和年龄；

源代码：

select S_no, S_name,phone,S_bir FROM student WHERE S_bir>2003-01-01;

结果截图：

 

②在score表中查询结业成绩高于90分的学生的学号和课程号,并按照学号排序；

源代码：

select S_no,C_no FROM score WHERE final>90 order by S_no;

结果截图：

 

③查询学号分别为18122221324、18137221508和19112100072学生的课程号、平时成绩和结业成绩；

源代码：

Select C_no,daily,final FROM score WHERE S_no IN('18122221324','18137221508','19112100072');

结果截图：

 

④查询选修课程号为c08123的学生的学号和结业成绩，并且要求平时成绩在85~100分；

源代码：

select  S_no,final from score where C_no='c08123'and daily between 85 and 100;

结果截图：

 

⑤在 student表中显示所有姓赵的学生的姓名、生日和电子邮件；

源代码：

select S_name,S_bir,email from student where S_name like'赵%';

结果截图：

 

⑥在 score表中显示结业成绩高于90分的学生的学号、课程号和结业成绩；

源代码：

select S_no,C_no,final from score where final>90;

结果截图：

 

⑦查询计算机学院专业为“软件工程”或“网络技术”的教师的教师号、姓名和职称。

源代码：

select t_no,T_name,prof from teacher where major in('软件工程','网络技术');

结果截图：

 

2. 排序、分组和限定记录的查询

①在student表中输出在“2002-12-4”年前出生的的学生的学号、姓名、出生日期和电话,并按照“出生日期”的降序排列。

源代码：

select S_no,S_name,S_bir,phone from student where S_bir>2002-12-04 order by S_bir;

结果截图：

 

②在score表中查询总评成绩大于85分的学生的学号、课程号和总评成绩,并先按照课程号的升序、再按照总评成绩的降序排列。总评成绩计算公式如下：总评成绩=daily*0.2+final*0.8;

源代码：

mysql>  select S_no,C_no,daily*0.2+ final*0.8 as'总评'from score where daily*0.2+ final*0.8>85

    -> order by C_no, daily*0.2+ final*0.8 desc;

结果截图：

 

③利用 group by子句对 score表数据分组,显示每个学生的学号和平均总评成绩。总评成绩计算公式如下:总评成绩=daily*0.3+final*0.7;

源代码：

select S_no,avg(daily*0.3+final*0.7) from score group by S_no;

结果截图：

 

④使用group by关键字对score表中的c_no字段进行分组查询。可以查看每门课程平时成绩的平均分和总分。

源代码：

select avg(daily),daily+final from score group by C_no;

结果截图：

 

⑤查询选课在2门以上且各门课程结业成绩均高于85分的学生的学号及其总成绩,查询结果按总成绩降序列出；总成绩=sum( daily*0.3+final*0.7)。

源代码：

mysql> select S_no,sum(daily*0.3+final*0.7) from score where final>=85

    -> group by S_no

    -> having count(*)>=2

    -> order by sum(daily*0.3+final*0.7)desc;

结果截图：

 

⑥查询 score表中结业成绩 final高于90分的成绩,按照平时成绩 daily进行升序排列,从編号1开始,查询3条记录。

源代码：

select * from score where final>90 order by daily asc limit 1,3;

结果截图：

 

3.聚合函数的应用

①查询score表中学生的总结业成绩大于275分的学生学号、总结业成绩及平均结业成绩;

源代码：

mysql> select S_no,sum(final),avg(final) from score group by S_no

    -> having sum(final)>275

    -> order by S_no;

结果截图：

 

②查询选修课程号为c05103的学生的结业成绩最高分、最低分及之间相差的分数;

源代码：

select max(final),min(final),max(final)-min(final) from score where C_no='c05103';

结果截图：

 

③查询 score表中每个学生的平时成绩和结业成绩的平均分、总分。

源代码：

select S_no,avg(daily+final),daily+final from score group by S_no with rollup;

结果截图：