CREATE DATABASE db_job
USE db_job
CREATE TABLE student -- 学生表
(
Sno CHAR(3) NOT NULL PRIMARY KEY , -- 学号 ，设为主键，不允许空值
Sname CHAR(8) NOT NULL, -- 学生姓名
Ssex CHAR(2)NOT NULL, -- 学生性别
Sbirthday DATETIME, -- 学生出生年月
Class CHAR(5) -- 学生所在班级
);
CREATE TABLE teacher -- 教师表
(
Tno CHAR(3)NOT NULL PRIMARY KEY, -- 教工编号设为主键
Tname CHAR(4)NOT NULL, -- 教工姓名
Tsex CHAR(2)NOT NULL, -- 教工性别
Tbirthday DATETIME, -- 教工出生年月
Prof CHAR(6), -- 职称
Depart VARCHAR(10)NOT NULL -- 教工所在部门
)
CREATE TABLE course -- 课程表
(
Cno CHAR(5) NOT NULL PRIMARY KEY , -- 课程号设为主键
Cname VARCHAR(10) NOT NULL, -- 课程名称
Tno CHAR(3) NOT NULL REFERENCES Teacher(Tno) -- 教工编号设为外键
)
CREATE TABLE score -- 成绩表
(
Sno CHAR(3) NOT NULL REFERENCES Student(Sno), -- 学号设为外码
Cno CHAR(5) NOT NULL REFERENCES Course(Cno), -- 课程号设为外码
Degree DECIMAL(4,1), -- 成绩
PRIMARY KEY(Sno,Cno) -- 学号和课程号设为联合主键
)
SHOW FULL COLUMNS FROM student
ALTER TABLE teacher CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci
INSERT INTO student VALUES(108,'曾华','男','1977-09-01','95033');
INSERT INTO student VALUES(105,'匡明','男','1975-10-02','95031');
INSERT INTO student VALUES(107,'王丽','女','1976-01-23','95033');
INSERT INTO student VALUES(101,'李军','男','1976-02-20','95033');
INSERT INTO student VALUES(109,'王芳','女','1975-02-10','95031');
INSERT INTO student VALUES(103,'陆君','男','1974-06-03','95031');
INSERT INTO teacher VALUES(804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teacher VALUES(856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teacher VALUES(825,'王萍','女','1972-05-05','助教','计算机系') ;
INSERT INTO teacher VALUES(831,'刘冰','女','1977-08-14','助教','电子工程系');
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(103,'3-245',86);
INSERT INTO score VALUES(105,'3-245',75);
INSERT INTO score VALUES(109,'3-245',68);
INSERT INTO score VALUES(103,'3-105',92);
INSERT INTO score VALUES(105,'3-105',88);
INSERT INTO score VALUES(109,'3-105',76);
INSERT INTO score VALUES(101,'3-105',64);
INSERT INTO score VALUES(107,'3-105',91);
INSERT INTO score VALUES(108,'3-105',78);
INSERT INTO score VALUES(101,'6-166',85);
INSERT INTO score VALUES(107,'6-166',79);
INSERT INTO score VALUES(108,'6-166',81);
-- 执行顺序 Where, Group By, Having, Order by
首先where将最原始记录中不满足条件的记录删除(所以应该在where语句中尽量的将不符合条件的记录筛选掉，这样可以减少分组的次数)
然后通过Group By关键字对视图进行分组
接着根据Having关键字后面指定的筛选条件，将分组后不满足条件的记录筛选掉
最后按照Order By语句对视图进行排序，这样最终的结果就产生了。
having子句可以让我们筛选成组后的各种数据，where子句在聚合前先筛选记录，也就是说作用在group by和having子句前。
而having子句在聚合后对组记录进行筛选。
having可以用聚合函数，如having SUM(money) > 1000
SELECT user_type,is_admin ,SUM(id) FROM saut_m_user
WHERE id > 100
GROUP BY user_type,is_admin
HAVING SUM(STATUS) < 500
ORDER BY is_admin ASC
-- 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 IN(85,86,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、 查询Score表中的最高分的学生学号和课程号。（子查询或者排序）
SELECT Sno 学号,Cno 课程号 FROM score WHERE Degree=(SELECT MAX(Degree) FROM score)
-- 10.1查询Score表中除了每门课程最高分的学生学号和课程号。（子查询或者排序）
SELECT Sno 学号,Cno 课程号 FROM score WHERE Degree NOT IN(SELECT MAX(Degree)FROM score GROUP BY Cno)
-- 11、 查询每门课的平均成绩。
SELECT AVG(Degree) FROM score GROUP BY Cno
-- 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT AVG(Degree) FROM score WHERE Cno LIKE'3%' GROUP BY Cno HAVING COUNT(*)>4
-- 13、查询分数大于70，小于90的Sno列。
SELECT Sno,Degree FROM score WHERE Degree BETWEEN 70 AND 90
-- 14、查询所有学生的Sname、Cno和Degree列。
SELECT Sname,Cno,Degree FROM student,score WHERE student.`Sno`=score.`Sno`
-- 15、查询所有学生的Sno、Cname和Degree列。
SELECT Sno,Cname,Degree FROM score,course WHERE score.`Cno`=course.`Cno`
-- 16、查询所有学生的Sname、Cname和Degree列。
SELECT Sname,Cname,Degree FROM student,score,course WHERE student.`Sno`=score.`Sno` AND score.`Cno`=course.`Cno`
-- 17、 查询“95033”班学生的平均分。
SELECT AVG(Degree) FROM student,score WHERE student.`Sno`=score.`Sno` AND student.`Class`=95033
-- 18、现查询所有同学的Sno、Cno和rank列。
SELECT Sno,Cno,rank FROM grade,score WHERE Degree BETWEEN low AND upp
-- 19、 查询选修“3-105”课程的,并且成绩高于“109”号同学成绩的所有同学的记录。
SELECT * FROM score WHERE Cno='3-105' AND Degree>(SELECT Degree FROM score WHERE Sno=109 AND Cno='3-105')
-- 21、 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
SELECT * FROM score WHERE Degree>(SELECT Degree FROM score WHERE Sno='109' AND Cno='3-105')
-- 22、查询和学号为107的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
SELECT Sno,Sname,Sbirthday FROM student WHERE Sno!=107 AND YEAR(Sbirthday) IN(SELECT YEAR(Sbirthday) FROM student WHERE Sno ='107')
-- 23、查询“张旭“教师任课的学生成绩。
SELECT Degree,Sname FROM teacher,student,score,course WHERE Tname='张旭' AND teacher.`Tno`=course.`Tno`AND course.`Cno`=score.`Cno` AND score.`Sno`=student.`Sno`
-- 24、查询选修某课程的同学人数多于5人的教师姓名。
SELECT Tname FROM teacher,course,score WHERE teacher.`Tno`=course.`Tno` AND course.`Cno`=score.`Cno` GROUP BY score.Cno HAVING COUNT(*)>5
-- 25、查询95033班和95031班全体学生的记录。
SELECT * FROM student WHERE Class IN('95033','95031')
-- 26、 查询存在有85分以上成绩的课程Cno.
SELECT Cno FROM score WHERE Degree>85 GROUP BY Cno
-- 27、查询出“计算机系“教师所教课程的成绩表。
SELECT Sname,Cname,Degree FROM teacher,student,course,score WHERE teacher.`Depart`='电子工程系' AND
teacher.`Tno`=course.`Tno` AND course.`Cno`=score.`Cno` AND score.`Sno`=student.`Sno`
-- 29、查询选修编号为“3-105“课程且成绩至少高于一个选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
SELECT Cno,Sno,Degree FROM score WHERE Cno='3-105' AND Degree>(SELECT Degree FROM score WHERE Cno='3-245' ORDER BY Degree ASC LIMIT 1 )
-- 30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
SELECT Cno,Sno,Degree FROM score WHERE Cno='3-105' AND Degree>(SELECT Degree FROM score WHERE Cno='3-245' ORDER BY Degree DESC LIMIT 1)
-- 31、 查询所有教师和同学的name、sex和birthday.
SELECT Tname,Tsex,Tbirthday FROM teacher
UNION ALL
SELECT Sname,Ssex,Sbirthday FROM student
-- 32、查询所有“女”教师和“女”同学的name、sex和birthday.
SELECT Tname,Tsex,Tbirthday FROM teacher WHERE Tsex='女'
UNION ALL
SELECT Sname,Ssex,Sbirthday FROM student WHERE Ssex='女'
-- 33、 查询成绩比该课程平均成绩低的同学的成绩表。
SELECT Sname,Cname,Degree FROM student,course,score s1 WHERE s1.`Cno`=course.`Cno` AND student.`Sno`=s1.`Sno`
AND Degree <(SELECT AVG(Degree) FROM score s2 GROUP BY Cno HAVING s1.Cno=s2.Cno)
-- 34、 查询所有任课教师的Tname和Depart
SELECT DISTINCT Tname,Depart FROM teacher,course,score WHERE teacher.`Tno`=course.`Tno` AND course.`Cno`=score.`Cno`
-- 36、查询至少有2名男生的班号。
SELECT class FROM student WHERE Ssex='男' GROUP BY Class HAVING COUNT(*)>1
-- 37、查询Student表中不姓“王”的同学记录。
SELECT * FROM student WHERE Sname NOT LIKE '王%'
-- 38、查询Student表中每个学生的姓名和年龄。
SELECT Sname,YEAR(NOW())-YEAR(Sbirthday) AS 年龄 FROM student
-- 39、查询Student表中最大和最小的Sbirthday日期值。
SELECT MAX(Sbirthday)最大,MIN(Sbirthday)最小 FROM student
-- 40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
SELECT * FROM student ORDER BY Class DESC,YEAR(NOW())-YEAR(Sbirthday) DESC
-- 41、查询“男”教师及其所上的课程。
SELECT Tname,Cname FROM teacher,course WHERE Tsex='男' AND teacher.`Tno`=course.`Tno`
-- 42、查询最高分同学的Sno、Cno和Degree列。
SELECT Sno,Cno,Degree FROM score WHERE Degree=(SELECT MAX(Degree) FROM score)
-- 43、查询和“李军”同性别的所有同学的Sname.
SELECT Sname FROM student WHERE Ssex=(SELECT Ssex FROM student WHERE Sname='李军') AND Sname!='李军'
-- 44、查询和“李军”同性别并同班的同学Sname.
SELECT Sname FROM student WHERE Ssex=(SELECT Ssex FROM student WHERE Sname='李军') AND
Class=(SELECT Class FROM student WHERE Sname='李军') AND Sname!='李军'
-- 45、查询所有选修“计算机导论”课程的“男”同学的成绩表.
SELECT score.`Degree`,student.`Sname` FROM score,student WHERE Ssex='男' AND Cno=(SELECT Cno FROM course WHERE Cname='计算机导论')
AND student.`Sno`=score.`Sno`
扫一扫
447
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
