MySQL数据库简单实践操作(一)
一,创建数据库test,表Students,表SC,表Courses
二,插入相关数据
三,交互式SQL--简单查询
#① 查询计算机系学生的学号、姓名、性别和出生日期。
SELECT Sno, Sname, Ssex, Sbirthday
FROM students
WHERE Sdept = '计算机';
#② 查询姓“李”的学生的学号和姓名。
SELECT Sno, Sname
FROM students
WHERE Sname like '李%';
#③ 查询课程表中先行课为空的课程名。
SELECT Cname
FROM courses
WHERE PreCno IS NULL;
#④ 查询考试成绩有不及格的学生的学号。
SELECT Sno
FROM sc
WHERE Grade < '60';
#⑤ 求选修了C1 课程或C2 课程的学生的学号及成绩。
SELECT Sno, Grade
FROM sc
WHERE Cno IN ('C1','C2');
#⑥ 查询全体计算机系学生的姓名及其年龄。
SELECT Sname ,FLOOR (DATEDIFF (now(),Sbirthday)/365.25)
FROM students
WHERE Sdept = '计算机'
#⑦ 查询计算机系在1986-1987 年之间出生的学生的姓名。
SELECT Sname
FROM students
WHERE Sdept = '计算机'
AND Sbirthday BETWEEN '1986-01-01' AND '1987-12-31'
#⑧ 查询姓“李”且姓名为3个汉字的学生学号和姓名。
SELECT Sno, Sname
FROM students
WHERE Sname like '李__';
#⑨ 查询选修了两门以上课程的学生学号与课程数。
#第一种做法
CREATE VIEW Courses1 AS
SELECT Sno,Count(Cno) AS sum_courses
FROM sc
WHERE Sno IN (
SELECT Sno
FROM sc)
GROUP BY sc.Sno
SELECT Sno, sum_courses
FROM Courses1
WHERE sum_courses >2
#第二种做法
SELECT Sno AS 学号,Count(Cno) AS 选课门数
FROM sc
WHERE Sno IN (SELECT Sno
From sc)
GROUP BY sc.Sno
HAVING Count(Cno) > 2
#⑩ 查询选修课程数大于等于2 的学生的学号、平均成绩和选课门数,并按平均成绩降序排列。
SELECT Sno AS 学号, Avg(Grade) AS 平均分,Count(Cno) AS 选课门数
FROM sc
WHERE Sno IN (SELECT Sno
From sc)
GROUP BY sc.Sno
HAVING Count(Cno) >= 2
ORDER BY Avg(Grade) DESC