开发环境：SQLyog ULTimate64
//数据库简单建立和基本查询
CREATE DATABASE databasepianrj
CREATE TABLE Student
(
Sno VARCHAR(50) NOT NULL PRIMARY KEY,
Sname VARCHAR(50) NOT NULL,
Ssex VARCHAR(20) NOT NULL,
Sbirthday DATETIME,
Class VARCHAR(20)
)
DROP TABLE Student
USE Student
INSERT INTO Student(Sno,Sname,Ssex,Sbirthday,Class)
VALUES
('108','曾华','男','1977-09-01','95033'),
('105','匡明','男','1975-10-02','95031'),
('107','王丽','女','1976-01-23','95033'),
('101','李军','男','1976-02-20','95033'),
('109','王芳','女','1975-02-10','95031'),
('103','陆军','男','1974-06-03','95031')
CREATE TABLE teacher
(
Tno VARCHAR(20) NOT NULL PRIMARY KEY,
Tname VARCHAR(20) NOT NULL,
Tsex VARCHAR(20) NOT NULL,
Tbirthday DATETIME,
Prof VARCHAR(20),
Depart VARCHAR(20)
)
USE teacher
INSERT INTO teacher(Tno,Tname,Tsex,Tbirthday,Prof,Depart)
VALUES
('804','李成','男','1958-12-02','副教授','计算机系'),
('856','张旭','男','1969-03-12','讲师','电子工程系'),
('825','王萍','女','1972-05-05','助教','计算机系'),
('831','刘冰','女','1977-08-14','助教','电子工程系')
CREATE TABLE Course
(
Cno VARCHAR(20) NOT NULL PRIMARY KEY,
Cname VARCHAR(20) NOT NULL,
Tno VARCHAR(20) NOT NULL ,
FOREIGN KEY (Tno)REFERENCES teacher (Tno)
)
INSERT INTO Course
VALUES
('3-105','计算机导论','825'),
('3-245','操作系统','804'),
('6-166','数字电路','856'),
('9-888','高等数学','831')
CREATE TABLE Score
(
Sno VARCHAR(20) NOT NULL ,
Cno VARCHAR(20) NOT NULL ,
Degree DECIMAL(4,1),
FOREIGN KEY (Cno) REFERENCES Course (Cno),
FOREIGN KEY (Sno) REFERENCES Student (Sno)
)
INSERT INTO Score
VALUES
('103','3-245','86'),
('105','3-245','75'),
('109','3-245','68'),
('103','3-105','92'),
('105','3-105','88'),
('109','3-105','76'),
('101','3-105','64'),
('107','3-105','91'),
('108','3-105','78'),
('101','6-166','85'),
('107','6-166','79'),
('108','6-166','81')
SELECT Sname,Ssex,Class FROM Student
SELECT DISTINCT Depart FROM teacher
SELECT * FROM Student
SELECT *
FROM Score
WHERE Degree BETWEEN 60 AND 80
SELECT *
FROM Score
WHERE Degree in (85,86,88);
SELECT *
FROM Student
WHERE class="95031" OR Ssex = '女'
SELECT *
FROM Student
ORDER BY Class DESC
SELECT *
FROM Score
ORDER BY Cno,Degree DESC
SELECT *
FROM Student
WHERE Class = 95031
SELECT Cno,Sno
FROM Score
where Degree=(select max(Degree)from Score)
SELECT AVG(Degree)
FROM Score
GROUP BY Cno
SELECT Cno,AVG(Degree)
FROM Score
GROUP BY Cno HAVING COUNT(Sno)>5
AND Cno LIKE '3%'
SELECT Score.`Sno`,Student.`Sname`
FROM Score,Student
WHERE Score.`Degree`>70 AND Score.`Degree`<90 and Score.`Sno` = Student.`Sno`
//数据库查询操作
USE databasepianrj
-- 1. 查询所有学生的Sno、Cname和Degree列。
SELECT Student.`Sno`,Course.`Cname`,Score.`Degree`
FROM Student JOIN Score
ON Student.`Sno` = Score.`Sno`
JOIN Course ON Score.`Cno` = Course.`Cno`
-- 2. 查询所有学生的Sname、Cname和Degree列。
SELECT Student.`Sno`,Student.`Sname`,Course.`Cname`,Score.`Degree`
FROM Student JOIN Score
ON Student.`Sno` = Score.`Sno`
JOIN Course ON Score.`Cno` = Course.`Cno`
-- 3. 查询“95033”班学生的平均分。
SELECT Student.`Class`,AVG(Degree)
FROM Student JOIN Score
ON Student.`Sno`=Score.`Sno` GROUP BY Student.`Class`
-- 4. 现查询所有同学的Sno、Cno和rank列。
CREATE TABLE Rank
(
low INT(3),
heigh INT(3),
rank CHAR(1)
)
INSERT INTO Rank
VALUES
(90,100,'A'),
(80,89,'B'),
(70,79,'C'),
(60,69,'D'),
(0,59,'E')
SELECT Student.`Sname`,Course.`Cname`,Score.`Cno`,Rank.`rank`
FROM Score JOIN Rank
ON Score.Degree > Rank.`low` AND Score.`Degree` < Rank.`heigh`
JOIN Student ON Score.`Sno` = Student.`Sno`
JOIN Course ON Course.`Cno` = Score.`Cno`
-- 5. 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECT *
FROM Student JOIN Score
ON Score.`Cno` = '3-105'
AND Score.`Degree`> (SELECT Score.`Degree` FROM Score WHERE Score.`Sno` = '109' AND Score.`Cno` = '3-105')
AND Score.`Sno` = Student.`Sno`
-- 6. 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
SELECT *
FROM Student JOIN Score
ON Score.`Degree` > (SELECT Score.`Degree` FROM Score WHERE Score.`Sno` = '109' AND Score.`Cno` = '3-105')
AND Score.`Sno` = Student.`Sno`
-- 7. 查询和学号为108、101的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
SELECT Sno,Sname,Sbirthday
FROM Student
WHERE YEAR(Sbirthday) = (SELECT YEAR(Sbirthday) FROM Student WHERE Sno = '108') OR YEAR(Sbirthday) = (SELECT YEAR(Sbirthday) FROM Student WHERE Sno = '101')
-- 8. 查询“张旭“教师任课的学生成绩。
SELECT Student.`Sname`,Student.`Sno`,Teacher.`Tname`,Course.`Cname`, Score.`Degree`
FROM Course JOIN Teacher
ON Course.`Tno` = Teacher.`Tno`AND Teacher.`Tname`='张旭'
JOIN Score ON Score.`Cno` = Course.`Cno`
JOIN Student ON Score.`Sno` = Student.`Sno`
-- 9. 查询选修某课程的同学人数多于5人的教师姓名。
SELECT Teacher.`Tname`, Course.`Cname`,Score.`Sno`
FROM Course
JOIN Teacher ON Course.`Tno` = Teacher.`Tno`
JOIN Score
ON Score.`Cno` = Course.`Cno`
HAVING COUNT(Score.`Cno`)>5
-- 10. 查询95033班和95031班全体学生的记录。
SELECT *
FROM Student
NATURAL JOIN Score
NATURAL JOIN Course
NATURAL JOIN Teacher
ORDER BY class
-- 11. 查询存在有85分以上成绩的课程Cno.
SELECT DISTINCT Course.`Cno`
FROM Course
JOIN Score ON Score.`Degree`>85 AND Score.`Cno` = Course.`Cno`
-- 12. 查询出“计算机系“教师所教课程的成绩表。
SELECT Course.`Cname`,Score.`Sno`,Score.`Degree`,Teacher.`Tname`,Teacher.`Depart`
FROM Teacher
JOIN Course ON Course.`Tno` = Teacher.`Tno`
JOIN Score ON Score.`Cno` = Course.`Cno`
WHERE Teacher.`Depart` = '计算机系'
-- 13. 查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
SELECT a.`Prof` AS '计算机',b.`Prof` AS '电子工程系'
FROM Teacher AS a
JOIN Teacher AS b ON a.`Depart` = '计算机系' AND b.`Depart` = '电子工程系' AND a.`Prof` != ALL (SELECT Prof FROM Teacher WHERE Depart = '电子工程系')AND
b.`Prof` != ALL (SELECT Prof FROM Teacher WHERE Depart = '计算机系')
-- 14. 查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
SELECT Course.`Cno`,Student.`Sno`,Score.`Degree`
FROM Course
JOIN Score ON Score.`Cno` = '3-105' AND Score.`Degree` > ANY (SELECT Degree FROM Score WHERE Score.`Cno` = '3-245') AND Course.`Cno` = Score.`Cno`
JOIN Student ON Score.`Sno` = Student.`Sno`
ORDER BY Score.`Degree` DESC
-- 15. 查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
SELECT Course.`Cno`,Student.`Sno`,Score.`Degree`
FROM Course
JOIN Score ON Score.`Cno` = '3-105' AND Score.`Degree` > ALL (SELECT Degree FROM Score WHERE Score.`Cno` = '3-245') AND Course.`Cno` = Score.`Cno`
JOIN Student ON Score.`Sno` = Student.`Sno`
ORDER BY Score.`Degree` DESC
-- 16. 查询所有教师和同学的name、sex和birthday.
SELECT Teacher.`Tname`,Teacher.`Tsex`,Teacher.`Tbirthday`
FROM Teacher
UNION
SELECT Student.`Sname`,Student.`Ssex`,Student.`Sbirthday`
FROM Student
-- 17. 查询所有“女”教师和“女”同学的name、sex和birthday.
SELECT Teacher.`Tname`,Teacher.`Tsex`,Teacher.`Tbirthday`
FROM Teacher
WHERE Tsex = '女'
UNION
SELECT Student.`Sname`,Student.`Ssex`,Student.`Sbirthday`
FROM Student
WHERE Ssex = '女'
-- 18. 查询成绩比该课程平均成绩低的同学的成绩表。//
SELECT *
FROM Score AS a
WHERE a.`Degree` < (SELECT AVG(Degree) FROM Score AS b WHERE a.`Cno` = b.`Cno`)
SELECT AVG(Degree),Cno
FROM Score
GROUP BY(Cno)
-- 19. 查询所有任课教师的Tname和Depart.
SELECT Teacher.`Tname`,Teacher.`Depart`
FROM Teacher
JOIN Course
ON teacher.`Tno` = Course.`Tno`
-- 20. 查询至少有2名男生的班号。//
SELECT Class
FROM Student
WHERE Ssex='男'
GROUP BY Class
HAVING COUNT('Sno')>=2
-- 21. 查询Student表中不姓“王”的同学记录。
SELECT *
FROM Student
WHERE Student.`Sname` NOT LIKE '王%'
-- 22. 查询Student表中每个学生的姓名和年龄。
SELECT Sname,2017-YEAR(Sbirthday) AS age
FROM Student
-- 23. 查询Student表中最大和最小的Sbirthday日期值。//
SELECT DATE(Sbirthday),MIN(DATE(Sbirthday))
FROM Student
-- 25. 查询“男”教师及其所上的课程。
SELECT Course.`Cname`,Teacher.`Tname`,Teacher.`Tno`
FROM Course
JOIN Teacher ON Course.`Tno` = Teacher.`Tno` AND Teacher.`Tsex` = '男'
-- 26. 查询最高分同学的Sno、Cno和Degree列。//?
SELECT *
FROM Score AS a
JOIN Student ON Student.`Sno` = a.`Sno`
WHERE a.`Degree` = (SELECT MAX(Degree) FROM Score AS b WHERE a.`Cno` = b.`Cno`)
-- 27. 查询和“李军”同性别的所有同学的Sname.
SELECT b.Sname
FROM Student AS a
JOIN Student AS b
ON a.`Sname`='李军' AND b.`Ssex` = a.`Ssex`
-- 28. 查询和“李军”同性别并同班的同学Sname.
SELECT b.Sname
FROM Student AS a
JOIN Student AS b
ON a.`Sname`='李军' AND b.`Ssex` = a.`Ssex` AND a.`Class` = b.`Class`
-- 29. 查询所有选修“计算机导论”课程的“男”同学的成绩表。
SELECT Student.`Sname`,Student.`Sno`,Score.`Degree`,Course.`Cname`
FROM Score
JOIN Course ON Course.`Cname`='计算机导论' AND Score.`Cno` = Course.`Cno`
JOIN Student ON Student.`Ssex` = '男' AND Student.`Sno` = Score.`Sno`
