【数据库学习笔记】之【练习】

练习

建表：

# 建库
DROP DATABASE IF EXISTS school;
CREATE DATABASE school;
USE school;

# 建表
DROP TABLE IF EXISTS Student;
CREATE TABLE IF NOT EXISTS `Student`(
	`Sno` VARCHAR(20) NOT NULL,
	`Sname` VARCHAR(20) NOT NULL,
	`Ssex` VARCHAR(20) NOT NULL,
	`Sbirthday` DATETIME,
	`SClass` VARCHAR(20),
	PRIMARY KEY (`Sno`)
)CHARSET=utf8;

DROP TABLE IF EXISTS Teacher;
CREATE TABLE IF NOT EXISTS `Teacher`(
	`Tno` VARCHAR(20) NOT NULL,
	`Tname` VARCHAR(20) NOT NULL,
	`Tsex` VARCHAR(20) NOT NULL,
	`Tbirthday` DATETIME,
	`Prof` VARCHAR(20),
	`Depart` VARCHAR(20) NOT NULL,
	PRIMARY KEY (`Tno`)
)CHARSET=utf8;

DROP TABLE IF EXISTS Course;
CREATE TABLE IF NOT EXISTS `Course`(
	`Cno` VARCHAR(20) NOT NULL,
	`Cname` VARCHAR(20) NOT NULL,
	`Tno` VARCHAR(20) NOT NULL,
	PRIMARY KEY (`Cno`),
	CONSTRAINT FK_T FOREIGN KEY(`Tno`) REFERENCES Teacher(`Tno`)
)CHARSET=utf8;

DROP TABLE IF EXISTS Score;
CREATE TABLE IF NOT EXISTS `Score`(
	`Sno` VARCHAR(20) NOT NULL,
	`Cno` VARCHAR(25) NOT NULL,
	`Degree` DECIMAL(4,1),
	PRIMARY KEY (`Cno`,`Sno`),
	CONSTRAINT FK_S FOREIGN KEY(`Sno`) REFERENCES Student(`Sno`),
	CONSTRAINT FK_C FOREIGN KEY(`Cno`) REFERENCES Course(`Cno`)
)CHARSET=utf8;

# 插数据
INSERT INTO `student` VALUES ('101', '李军', '男', '1976-02-20 00:00:00', '95033');
INSERT INTO `student` VALUES ('103', '陆君', '男', '1974-06-03 00:00:00', '95031');
INSERT INTO `student` VALUES ('105', '匡明', '男', '1975-10-02 00:00:00', '95031');
INSERT INTO `student` VALUES ('107', '王丽', '女', '1976-01-23 00:00:00', '95033');
INSERT INTO `student` VALUES ('108', '曾华', '男', '1977-09-01 00:00:00', '95033');
INSERT INTO `student` VALUES ('109', '王芳', '女', '1975-02-10 00:00:00', '95031');

INSERT INTO `teacher` VALUES ('804', '李诚', '男', '1958-12-02 00:00:00', '副教授', '计算机系');
INSERT INTO `teacher` VALUES ('825', '王萍', '女', '1972-05-05 00:00:00', '助教', '计算机系');
INSERT INTO `teacher` VALUES ('831', '刘冰', '女', '1977-08-14 00:00:00', '助教', '电子工程系');
INSERT INTO `teacher` VALUES ('856', '张旭', '男', '1969-03-12 00:00:00', '讲师', '电子工程系');

INSERT INTO `course` VALUES ('3-105', '计算机导论', '825');
INSERT INTO `course` VALUES ('3-245', '操作系统', '804');
INSERT INTO `course` VALUES ('6-166', '数字电路', '856');
INSERT INTO `course` VALUES ('9-888', '高等数学', '831');

INSERT INTO `score` VALUES ('101', '3-105', 64.0);
INSERT INTO `score` VALUES ('109', '3-245', 68.0);
INSERT INTO `score` VALUES ('105', '3-245', 75.0);
INSERT INTO `score` VALUES ('109', '3-105', 76.0);
INSERT INTO `score` VALUES ('108', '3-105', 78.0);
INSERT INTO `score` VALUES ('107', '6-166', 79.0);
INSERT INTO `score` VALUES ('108', '6-166', 81.0);
INSERT INTO `score` VALUES ('101', '6-166', 85.0);
INSERT INTO `score` VALUES ('103', '3-245', 86.0);
INSERT INTO `score` VALUES ('105', '3-105', 88.0);
INSERT INTO `score` VALUES ('107', '3-105', 91.0);
INSERT INTO `score` VALUES ('103', '3-105', 92.0);

USE school;
SELECT Sname,Ssex,SClass FROM Student;
# 1、查询Student表中的所有记录的Sname、Ssex和SClass列。

SELECT DISTINCT Depart FROM teacher;
# 2、查询教师所有的单位即不重复的Depart列。

SELECT * FROM student;
# 3、查询Student表的所有记录。

SELECT * FROM Score WHERE Degree BETWEEN 60 AND 80;
# 4、查询Score表中成绩在60到80之间的所有记录。

SELECT * FROM Score WHERE Degree IN (85,86,88);
# 5、查询Score表中成绩为85，86或88的记录。

SELECT * FROM student WHERE SClass=95031 OR Ssex='女';
# 6、查询Student表中“95031”班或性别为“女”的同学记录。

SELECT * FROM student ORDER BY SClass DESC;
# 7、以Class降序查询Student表的所有记录。

SELECT * FROM score ORDER BY Cno ASC,Degree DESC;
# 8、以Cno升序、Degree降序查询Score表的所有记录。

SELECT COUNT(*) FROM student WHERE SClass=95031;
# 9、查询“95031”班的学生人数。

SELECT Sno,Cno FROM Score ORDER BY Degree LIMIT 1;
# 10、查询Score表中的最高分的学生学号和课程号。（子查询或者排序）


SELECT AVG(Degree),Cno FROM Score GROUP BY Cno; 
# 11、查询每门课的平均成绩。

SELECT COUNT(*),Cno FROM Score GROUP BY Cno HAVING COUNT(*) > 5 AND Cno LIKE '3%';
# 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

SELECT Sno FROM Score WHERE Degree BETWEEN 70 AND 90;
# 13、查询分数大于70，小于90的Sno列。

SELECT Sname,Cno,Degree FROM student,score WHERE Student.Sno=score.Sno;
# 14、查询所有学生的Sname、Cno和Degree列。

SELECT Sno,Cname,Degree FROM course,score WHERE course.Cno=score.Cno;
# 15、查询所有学生的Sno、Cname和Degree列。

SELECT Sname,Cname,Degree FROM student stu,course cou,score sco WHERE stu.Sno=sco.Sno AND cou.Cno=sco.Cno;
# 16、查询所有学生的Sname、Cname和Degree列。


SELECT
SClass,AVG(Degree)
FROM
student stu 
LEFT OUTER JOIN score sco ON stu.Sno=sco.Sno
GROUP BY SClass
HAVING SClass=95033;
# 17、查询“95033”班学生的平均分。

DROP TABLE IF EXISTS grade;
create table grade(low INT(3),upp INT(3),rank char(1));
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');

SELECT
sco.Sno,sco.Cno,gra.rank
FROM score sco
RIGHT OUTER JOIN grade gra
ON sco.degree>=gra.low AND sco.degree<=gra.upp
# 18、假设使用如上命令建立了一个grade表，现查询所有同学的Sno、Cno和rank列。


SELECT * 
FROM score 
WHERE cno = '3-105'
AND degree > 
(SELECT degree FROM score WHERE sno = '109' AND cno = '3-105')
# 19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

SELECT * FROM score WHERE Degree<(SELECT MAX(Degree) FROM score) GROUP BY Sno HAVING COUNT(Sno)>1 ORDER BY Degree;
# 20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。 

SELECT sco_a.* 
FROM score sco_a  
WHERE sco_a.Cno='3-105' AND sco_a.Degree>
ALL(SELECT Degree FROM score sco_b WHERE sco_b.Sno='109' AND sco_b.Sno='3-105');
# 21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

SELECT Sno,Sname,Sbirthday FROM student
WHERE YEAR(Sbirthday)=
(SELECT YEAR(Sbirthday)FROM student WHERE Sno=108);
# 22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

SELECT sco.sno,sco.Degree 
FROM score sco JOIN (teacher tea,course cou)
ON sco.cno=cou.cno AND tea.tno=cou.tno
WHERE tea.Tname='张旭';
# 23、查询“张旭“教师任课的学生成绩。

SELECT tea.Tname
FROM teacher tea
JOIN (course cou,score sco)
on (tea.Tno=cou.Tno AND cou.Cno=sco.Cno)
GROUP BY sco.Cno
HAVING COUNT(sco.Cno)>5;
# 24、查询选修某课程的同学人数多于5人的教师姓名。

SELECT * FROM student WHERE SClass=95033 || SClass=95031;
# 25、查询95033班和95031班全体学生的记录。

SELECT DISTINCT Cno FROM score WHERE Degree>85;
# 26、查询存在有85分以上成绩的课程Cno.

SELECT A.*
FROM score A
JOIN (teacher B,course C)
ON A.Cno=C.Cno AND B.Tno=C.Tno
WHERE B.Depart='计算机系';
# 27、查询出“计算机系“教师所教课程的成绩表。

SELECT Tname,Prof FROM teacher
WHERE Depart='计算机系' AND Prof NOT IN
(SELECT Prof FROM teacher WHERE Depart='电子工程系');
# 28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

SELECT * FROM score WHERE Degree > ANY
(SELECT Degree FROM score WHERE Cno='3-245')
ORDER BY Degree DESC;
# 29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的一名同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
 
SELECT * FROM score
WHERE Degree>
ALL(SELECT Degree FROM score WHERE Cno='3-245')
ORDER BY Degree DESC;
# 30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
 
SELECT Sname AS name,Ssex AS sex,Sbirthday AS birthday 
FROM student
UNION
SELECT Tname AS name,Tsex AS sex,Tbirthday AS birthday
FROM teacher;
# 31、查询所有教师和同学的name、sex和birthday.

SELECT Sname AS name,Ssex AS sex,Sbirthday AS birthday 
FROM student
WHERE Ssex='女'
UNION
SELECT Tname AS name,Tsex AS sex,Tbirthday AS birthday
FROM teacher
WHERE Tsex='女';
# 32、查询所有“女”教师和“女”同学的name、sex和birthday.

SELECT A.* FROM
score A
WHERE Degree<
(SELECT AVG(Degree) FROM score B WHERE A.Cno=B.Cno);
# 33、查询成绩比该课程平均成绩低的同学的成绩表。

SELECT A.Tname,A.Depart FROM
teacher A
JOIN course B
ON a.Tno=B.Tno;
# 34、查询所有任课教师的Tname和Depart.

SELECT Tname,Depart FROM
teacher A
WHERE NOT EXISTS
(SELECT * FROM course B WHERE a.Tno=B.Tno)
# 35、查询所有未讲课的教师的Tname和Depart.

SELECT SClass 
FROM student A
WHERE Ssex='男'
GROUP BY SClass
HAVING COUNT(Ssex)>1;
# 36、查询至少有2名男生的班号。
 
SELECT * FROM student A
WHERE Sname NOT LIKE '王%';
# 37、查询Student表中不姓“王”的同学记录。

SELECT Sname,(YEAR(NOW())-YEAR(Sbirthday)) AS AGE
FROM student;
# 38、查询Student表中每个学生的姓名和年龄。

SELECT Sname,Sbirthday
FROM student
WHERE Sbirthday=(SELECT MIN(Sbirthday) FROM student)
UNION
SELECT Sname,Sbirthday
FROM student
WHERE Sbirthday=(SELECT MAX(Sbirthday) FROM student);
# 39、查询Student表中最大和最小的Sbirthday日期值。
 
SELECT SClass,(YEAR(NOW())-YEAR(Sbirthday)) AS AGE
FROM student
ORDER BY SClass DESC,AGE;
# 40、以班号和年龄从大到小的顺序查询Student表中的全部记录。

SELECT A.Tname,B.Cname
FROM teacher A
JOIN course B
USING(TNO)
WHERE A.Tsex='男';
# 41、查询“男”教师及其所上的课程。

SELECT A.* FROM
score A
WHERE Degree=
(SELECT MAX(Degree) FROM score B);
# 42、查询最高分同学的Sno、Cno和Degree列。

SELECT Sname 
FROM student
WHERE Ssex=
(SELECT Ssex FROM student WHERE Sname='李军');
# 43、查询和“李军”同性别的所有同学的Sname.

SELECT Sname 
FROM student
WHERE Ssex=
(SELECT Ssex FROM student WHERE Sname='李军')
AND SClass=
(SELECT SClass FROM student WHERE Sname='李军');
# 44、查询和“李军”同性别并同班的同学Sname.

SELECT A.*
FROM
score A JOIN
(student B,course C) USING (Sno,Cno)
WHERE b.Ssex='男'
AND c.Cname='计算机导论';
# 45、查询所有选修“计算机导论”课程的“男”同学的成绩表。