MySQL数据库综合练习一

---

题目

drop table if exists student;
 drop table if exists teacher;
 drop table if exists course;
 drop table if exists sc;


create table student(
sno varchar(10) primary key,
sname varchar(20),
sage int,
ssex varchar(5)
);

create table teacher(
tno varchar(10) primary key,
tname varchar(20)
);

create table course(
cno varchar(10),
cname varchar(20),
tno varchar(20),
constraint pk_course primary key (cno,tno)
);

create table sc(
sno varchar(10),
cno varchar(10),
score decimal(8,2),
constraint pk_sc primary key (sno,cno)
);


/*******初始化学生表的数据******/
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,'女');
 
/******************初始化教师表***********************/
insert into teacher values ('t001', '刘阳');
insert into teacher values ('t002', '谌燕');
insert into teacher values ('t003', '胡明星');
 
/***************初始化课程表****************************/
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');
 
/***************初始化成绩表***********************/
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);
insert into sc values ('s001','c004',78.9);
insert into sc values ('s002','c004',80.9);
insert into sc values ('s003','c005',81.9);
insert into sc values ('s004','c005',60.9);
insert into sc values ('s001','c006',82.9);
insert into sc values ('s002','c006',72.9);
insert into sc values ('s003','c007',81.9);
insert into sc values ('s001','c007',59); 
insert into sc values ('s005','c001',80.9);
insert into sc values ('s006','c001',81.9);
insert into sc values ('s007','c001',36);
insert into sc values ('s004','c002',75);
insert into sc values ('s005','c002',71.2);
insert into sc values ('s006','c002',85.9);
insert into sc values ('s007','c003',59);
insert into sc values ('s004','c004',77.9);
insert into sc values ('s005','c004',84.9);
insert into sc values ('s006','c005',82.9);
insert into sc values ('s007','c005',45);
insert into sc values ('s004','c006',62.9);
insert into sc values ('s006','c006',92.9);
insert into sc values ('s007','c007',35.9);
insert into sc values ('s008','c007',69); 

---

1、查询“c001”课程比“c002”课程成绩高的所有学生的学号

知识点：
1、子查询（查询结果作为数据表）
2、聚合函数的使用。（没有在聚合函数中的字段，必须包含在group by子句中）——分组统计。
聚合函数总共有5个（sum,count,max,min,avg）。作用：统计
3、case… when…else…end（相当与程序里面的if…else…）
(1) Case…when…when…else…end
(2) Case when…when…else…end

SELECT DISTINCT
	sno 
FROM
	sc AS S 
WHERE
	( SELECT score FROM sc WHERE cno = 'c001' AND S.sno = sno )>(
	SELECT
		score 
	FROM
		sc 
	WHERE
	cno = 'c002' 
	AND s.sno = sno)

结果：

2、查询平均成绩大于60 分的同学的学号和平均成绩；

SELECT
	sno,
	avg( score ) as '平均成绩'
FROM
	sc 
GROUP BY
	sno 
HAVING
	avg( score )> 60

结果：

3、查询所有同学的学号、姓名、选课数、总成绩；

SELECT
	student.sno,
	student.sname,
	count( sc.cno ) AS '选课数',
	 sum( sc.score ) AS '总成绩' 
FROM
	student
	INNER JOIN sc ON student.sno = sc.sno 
GROUP BY
	sc.sno

结果：

4、查询姓“刘”的老师的个数；

SELECT
	tname,
	count(*) AS '个数' 
FROM
	teacher 
WHERE
	tname LIKE '刘%'

结果：

5、查询没学过“谌燕”老师课的同学的学号、姓名

SELECT
	sno,
	sname 
FROM
	student stu 
WHERE
	stu.sno NOT IN (
	SELECT
		sc.sno 
	FROM
		sc 
	WHERE
		sc.cno IN (
		SELECT
			course.cno 
		FROM
			course 
	WHERE
	course.tno IN ( SELECT teacher.tno FROM teacher WHERE teacher.tname = '谌燕' )))

结果：

6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名

SELECT
	student.sno,
	student.sname 
FROM
	student
	INNER JOIN sc ON ( student.sno = sc.sno ) 
WHERE
	sc.cno IN ( 'c001', 'c002' ) 
GROUP BY
	sc.sno 
HAVING
	COUNT( cno )>= 2

结果：

7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；

SELECT
	student.sno,
	student.sname 
FROM
	student 
WHERE
	student.sno IN (
	SELECT
		sc.sno 
	FROM
		sc 
	WHERE
		sc.cno IN (
		SELECT
			course.cno 
		FROM
			course 
	WHERE
	course.tno IN ( SELECT teacher.tno FROM teacher WHERE tname = '谌燕' )))

结果：

8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；

SELECT DISTINCT
	student.sno,
	student.sname 
FROM
	student
	INNER JOIN sc ON ( student.sno = sc.sno ) 
WHERE
	( SELECT score FROM sc WHERE sc.cno = 'c001' AND student.sno = sc.sno )> (
	SELECT
		score