重温mysql

-- 新建学生表
CREATE TABLE student(
sno VARCHAR(20),
sname VARCHAR(10),
ssex VARCHAR(10),
sbirthday DATETIME,
class VARCHAR(20)
)
INSERT INTO student VALUES (108,'曾华','男',19770901,95033);
INSERT INTO student VALUES (105,'匡明','男',19751002,95031);
INSERT INTO student VALUES (107,'王丽','女',19760123,95033);
INSERT INTO student VALUES (101,'李军','男',19760220,95033);
INSERT INTO student VALUES (109,'王芳','女',19750210,95031);
INSERT INTO student VALUES (103,'陆君','男',19740603,95031);

-- 新建课程表
CREATE TABLE course (
    cno VARCHAR(20),
    cname VARCHAR(10),
    tno VARCHAR(20)
)
INSERT INTO course VALUES( '3-205','计算机导论',825);
INSERT INTO course VALUES( '9-245','操作系统',804);
INSERT INTO course VALUES( '6-166','数字电路',856);
INSERT INTO course VALUES( '9-888','高等数学',831);

-- 新建成绩表
CREATE TABLE score(
    sno VARCHAR(20),
    cno VARCHAR(20),
    degree DECIMAL(4,1)
)
INSERT INTO score VALUE (103,'3-245',86);
INSERT INTO score VALUE (105,'3-245',75);
INSERT INTO score VALUE (109,'3-245',68);
INSERT INTO score VALUE (103,'3-105',92);
INSERT INTO score VALUE (105,'3-105',88);
INSERT INTO score VALUE (109,'3-105',76);
INSERT INTO score VALUE (101,'3-105',64);
INSERT INTO score VALUE (107,'3-105',91);
INSERT INTO score VALUE (108,'3-105',78);
INSERT INTO score VALUE (101,'6-166',85);
INSERT INTO score VALUE (107,'6-166',79);
INSERT INTO score VALUE (108,'6-166',81);

-- 新建教师表
CREATE TABLE teacher (
    tno VARCHAR(20),
    tname VARCHAR(10),
    tsex VARCHAR(20),
    tbirthday DATETIME,
    prof VARCHAR(20),
    depart VARCHAR(20)
)
INSERT INTO teacher VALUE (804,'李诚','男',19581202,'副教授','计算机系');
INSERT INTO teacher VALUE (856,'张旭','男',19690312,'讲师','电子工程系');
INSERT INTO teacher VALUE (825,'王萍','女',19770814,'助教','计算机系');
INSERT INTO teacher VALUE (831,'刘冰','女',19770814,'助教','电子工程系');

-- 1查询Student表中的所有记录的Sname、Ssex和Class列
SELECT sname,ssex,class FROM student; 
-- 2查询教师所有的单位即不重复的Depart列
SELECT DISTINCT depart FROM teacher;
-- 3查询Student表的所有记录
SELECT * FROM student;
-- 4查询Score表中成绩在60到80之间的所有记录
SELECT * FROM score WHERE degree>=60 AND degree<=80;
-- 5查询Score表中成绩为85，86或88的记录
SELECT * FROM score WHERE degree=85 OR degree=86 OR degree=88;
-- 6 查询Student表中“95031”班或性别为“女”的同学记录
SELECT * FROM student WHERE class=95031 OR ssex='女';
-- 7以Class降序查询Student表的所有记录
SELECT * FROM student ORDER BY class DESC;
-- 8以Cno升序、Degree降序查询Score表的所有记录
SELECT * FROM score ORDER BY cno ASC, degree DESC;
-- 9查询“95031”班的学生人数
SELECT COUNT(*) FROM student WHERE class=95031;
-- 10查询每门课的平均成绩
SELECT cno,AVG(degree) FROM score GROUP BY cno; 
-- 11、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT AVG(degree) FROM score WHERE cno LIKE '3%' GROUP BY cno HAVING COUNT(cno)>4
SELECT AVG(Degree) FROM score WHERE Cno IN 
(SELECT Cno FROM score GROUP BY Cno HAVING COUNT(*)>5)
 AND Cno LIKE '3%' GROUP BY Cno
SELECT AVG(Degree) FROM score WHERE Cno LIKE '3%' 
GROUP BY Cno HAVING COUNT(*)>5
-- 12、查询分数大于70，小于90的Sno列。
SELECT sno AS '分数' FROM score WHERE degree BETWEEN 70 AND 90
-- 13、查询所有学生的Sname、Cno和Degree列。
SELECT sname,cno,degree FROM  student JOIN score ON 
student.Sno=score.Sno
-- 15、查询所有学生的Sname、Cname和Degree列。
SELECT * FROM student JOIN score ON
student.`sno`=score.`sno` JOIN course ON 
score.`cno` = course.`cno`
-- 16、查询“95033”班学生的平均分。
SELECT  AVG(degree) AS '平均分'FROM score 
WHERE sno IN
(SELECT sno FROM student WHERE class='95033')
-- 17、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
SELECT sno AS '编号',sname AS '姓名',sbirthday AS '出生日期' FROM student 
WHERE YEAR(student.`sbirthday`)=(SELECT YEAR(sbirthday) 
FROM student WHERE sno='108')
-- 18、查询“张旭“教师任课的学生成绩（姓名）。
SELECT degree AS '分数'FROM score,teacher,course 
WHERE teacher.tname='张旭' AND teacher.`tno`=course.`tno` 
AND course.`cno`=score.`cno`
SELECT tno FROM teacher WHERE tname='张旭'
SELECT cno FROM course WHERE tno=856
SELECT sno,degree FROM score JOIN student
ON score.`sno` WHERE cno='6-116'
-- 19、查询考计算机导论的学生成绩
SELECT sno,cno,degree FROM score WHERE cno IN (SELECT cno FROM course WHERE tno IN (SELECT tno FROM teacher WHERE depart='计算机系'))
-- 20、查询李诚老师教的课程名称

-- 21、教高等数学的老师是哪个系的


-- 22、查询选修某课程的同学人数多于5人的教师姓名。
SELECT tname FROM teacher WHERE tno IN (selecet tno FROM course WHERE cno IN(SELECT cno FROM score GROUP BY cno HAVING COUNT(*)>5))

-- 23、查询95033班和95031班全体学生的记录。
SELECT * FROM Student WHERE Class IN('95031','95033')

-- 24、查询存在有85分以上成绩的课程Cno.
SELECT DISTINCT Cno FROM Score WHERE Degree > 85
-- 25、查询出“计算机系“教师所教课程的成绩表。

SELECT * FROM Score WHERE Cno IN(SELECT Cno FROM Course 
WHERE Tno IN(SELECT Tno FROM Teacher WHERE depart = '计算机系'))

-- 26、 查询所有教师和同学的name、sex和birthday.
SELECT Sname,Ssex,Sbirthday FROM Student UNION
SELECT Tname,Tsex,Tbirthday FROM Teacher


-- 27、查询所有“女”教师和“女”同学的name、sex和birthday.

SELECT Sname,Ssex,Sbirthday FROM Student WHERE Ssex='女' UNION
SELECT Tname,Tsex,Tbirthday FROM Teacher WHERE Tsex='女'


-- 28、 查询所有任课教师的Tname和Depart.
SELECT tname,depart FROM teacher WHERE tname  IN (SELECT DISTINCT tname FROM teacher,course,score 
WHERE teacher.`tno`=course.`tno` AND course.cno=score.`cno`)
-- 29、查询所有未讲课的教师的Tname和Depart. 

SELECT Tname,depart FROM Teacher WHERE Tno IN(SELECT Tno FROM Course 
WHERE Cno NOT IN(SELECT Cno FROM Score))
-- 30、查询至少有2名男生的班号。
SELECT class AS '班级'班号 FROM student WHERE ssex='男' GROUP BY class HAVING COUNT(*)>1

-- 31、查询Student表中不姓“王”的同学记录。

SELECT * FROM student WHERE sname NOT LIKE (‘王%’)

-- 32、查询Student表中每个学生的姓名和年龄。
SELECT Sname,YEAR(NOW())-YEAR(Sbirthday) FROM Student
-- 33、查询Student表中最大和最小的Sbirthday日期值。
SELECT MAX(Sbirthday) FROM Student UNION SELECT MIN(Sbirthday) FROM Student
-- 34、以班号和年龄从大到小的顺序查询Student表中的全部记录。
SELECT * FROM Student ORDER BY Class DESC,Sbirthday
-- 35、查询“男”教师及其所上的课程。
SELECT * FROM Teacher JOIN Course ON Teacher.Tno = Course.Tno 
WHERE Teacher.Tsex='男'
-- 36、查询最高分同学的Sno、Cno和Degree列。
SELECT * FROM Score WHERE Degree = (SELECT MAX(Degree) FROM Score )
-- 37、查询和“李军”同性别的所有同学的Sname.
SELECT Sname FROM Student WHERE Ssex = (SELECT Ssex FROM Student WHERE Sname='李军')
-- 38、查询和“李军”同性别并同班的同学Sname.
SELECT Sname FROM Student WHERE Ssex = (SELECT Ssex FROM Student WHERE Sname='李军') 
AND Class=(SELECT Class FROM Student WHERE Sname = '李军')
-- 39、查询所有选修“计算机导论”课程的“男”同学的成绩表。
SELECT * FROM Score WHERE Sno IN(SELECT Sno FROM Student WHERE Ssex='男')
AND Cno IN(SELECT Cno FROM Course WHERE Cname='计算机导论')