MYSQL基础上机练习题（二）对数据指定列查询、条件查询、查询结果排序、聚集函数查询、分组统计查询

实验目标：

1．掌握指定列或全部列查询

2．掌握按条件查询

3．掌握对查询结果排序

4．掌握使用聚集函数的查询

5．掌握分组统计查询

一、请完成书中实验7.1，并完成以下问题。

1．查询所有学生的姓名及其出生年份回答以下问题：

SQL语句请截图

①　观察查询的数据，若年龄不为空是否能求出出生年份，若年龄为空则出生年份显示什么？

能，若年龄为空，出生年份也显示空

②　使用了哪些函数求出出生年份，AS的作用是什么？

使用GETDATE()得到当前年份，再减去年龄得到出生年份。AS作用给列取别名

2．完成按条件查询SC表考试成绩不及格的学生的学号回答以下问题：

①　插入三行记录，（s2,c7,45）,(s3,c2,59),(s4,c3,50)(重复不能插进去),描述相应的SQL。

INSERT INTO SC(SNO,CNO,SCORE)

VALUES

('S2','C7',45),

('S3','C2',59),

('S4','C3',50)

②　查询考试成绩不及格的学生学号,会出现以下查询结果吗？如果没出现，是什么原因？如果出现了，如何解决出现重复的学号。

解决方法：在SELECT SNO FROM SC 语句的 SNO 前加上 DISTINCT。

3．查询年龄在20-23岁之间的学生的姓名、系名、年龄，试一试，再把between... And...换成in完成查询，描述相应的SQL语句。

4．完成查询姓李的学生的姓名、学号、性别，试一试再插入两行数据(‘10’,‘李思’,‘女’)、(‘11’,‘李王睿安’,‘男’)，能否使用like分别查询李姓两字、李姓四字学生的姓名、学号、性别，描述相应的SQL语句。

查询李姓两字学生的姓名、学号、性别

查询李姓李姓四字学生的姓名、学号、性别

5．完成对查询结果进行排序，回答排序是用什么子句完成，其中升序、降序的关键字分别是什么？可以按多个字段进行排序吗？

SELECT 字段名 AS 排序名,…n  FROM 表名

WHERE (条件)

ORDER BY 字段名（升序），字段名 DESC （降序）

升序：OREDER BY接升序的字段；

降序：OREDER BY接降序的字段DESC

可以按多个字段进行排序

6．对T表查询教师的职称及相应的人数，描述相应的SQL语句。

7．在T表中查询老师的姓名、职称，工资，并按教师的SAL字段进行降序排序。

8.在S表、SC表中查询选课学生、及其选课的总成绩，并根据总成绩进行降序排列，查询结果可参考如下。

9.查询有多少个学生参加选课？

二、执行下面的SQL脚本完成表的创建及数据的插入，并完成后面的问题

1. 数据表（4个表）

l 学生表（学号、姓名、年龄、性别）

create table student(

sno varchar(10) primary key,

sname varchar(20),

sage int,

ssex varchar(5)

)

l 教师表（教师工号、姓名）

create table teacher(

tno varchar(10) primary key,

tname varchar(20)

)

l 课程表（课程号、课程名、教师工号）

create table course(

cno varchar(10),

cname varchar(20),

tno varchar(10),

constraint pk_course primary key (cno,tno)

)

l 成绩表（学号、课程号、分数）

create table sc(

sno varchar(10),

cno varchar(10),

score real,

constraint pk_sc primary key (sno,cno)

)

2. 初始化数据

l 学生表

insert into student values ('s001','张三',23,'男');

insert into student values ('s002','李四',23,'男');

insert into student values ('s003','吴鹏',25,'男');

insert into student values ('s004','琴沁',20,'女');

insert into student values ('s005','王丽',20,'女');

insert into student values ('s006','李波',21,'男');

insert into student values ('s007','刘玉',21,'男');

insert into student values ('s008','萧蓉',21,'女');

insert into student values ('s009','陈萧晓',23,'女');

insert into student values ('s010','陈美',22,'女');

l 教师表

insert into teacher values ('t001', '刘阳');

insert into teacher values ('t002', '谌燕');

insert into teacher values ('t003', '胡明星');

l 课程表

insert into course values ('c001','J2SE','t002');

insert into course values ('c002','Java Web','t002');

insert into course values ('c003','SSH','t001');

insert into course values ('c004','Oracle','t001');

insert into course values ('c005','SQL SERVER 2005','t003');

insert into course values ('c006','C#','t003');

insert into course values ('c007','JavaScript','t002');

insert into course values ('c008','DIV+CSS','t001');

insert into course values ('c009','PHP','t003');

insert into course values ('c010','EJB3.0','t002');

l 成绩表

insert into sc values ('s001','c001',78.9);

insert into sc values ('s002','c001',80.9);

insert into sc values ('s003','c001',81.9);

insert into sc values ('s004','c001',60.9);

insert into sc values ('s001','c002',82.9);

insert into sc values ('s002','c002',72.9);

insert into sc values ('s003','c002',81.9);

insert into sc values ('s001','c003','59');

3. 完成下面的题目

1.　求选了课程的学生人数。

2.查询姓“刘”的老师的个数

3.　查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分。

4.查询平均成绩大于60 分的同学的学号和平均成绩（思路按学号分组计算学生的平均成绩，用having子句筛选出平均成绩大于60的学生）。

5.　按各科平均成绩从低到高和及格率的百分数从高到低顺序，效果如下。（思路：可以通过case when..then..else..end统计人数））

 

6.查询每门课程被选修的学生数。

7.查询男生、女生人数

8.　查询姓“张”的学生名单。

9.　查询1981 年出生的学生名单。

10.　查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列。

11.　查询各个课程及相应的选修人数。

12.　统计每门课程的学生选修人数（超过2人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。

13.　检索至少选修两门课程的学生学号。

14.检索“c004”课程分数小于60，按分数降序排列的同学学号。

15.　删除“s002”同学的“c001”课程的成绩。