DQL - 数据库查询操作

#查询所有学生信息
SELECT * FROM student

#查询学生表中的学号与姓名
SELECT sno,sname FROM student

#查询学生表中的学号与姓名，并且给一个字段别名
SELECT sno AS studentNO,sname AS '姓名' FROM student

#查询学生表中的姓名信息，并过滤掉相同姓名信息
SELECT DISTINCT sname FROM student

#查询学生个数，年龄总和，平均年龄，最大年龄，最小年龄，并给他们一个别名
SELECT COUNT(*) AS '学生个数' ，SUM(age) AS sumage FROM student

#查询所有男生的信息
SELECT * FROM student WHERE sex = '男'

#查询所有21岁男生的信息
SELECT * FROM student WHERE sex = '男' AND age = 21

#查询姓‘陈’的同学
SELECT * FROM student WHERE sname LIKE '陈%'

#查询名字中出现‘陈’的同学
SELECT * FROM student WHERE sname LIKE '%陈%'

#查询姓陈的二个字姓名的同学
SELECT * FROM student WHERE sname LIKE '陈_'

#查询名字结尾是‘樱’的三个字姓名的同学
SELECT * FROM student WHERE sname LIKE '__樱'

#根据学生年龄从大到小进行排序学生信息
SELECT * FROM student ORDER BY age DESC

#根据学生年龄从小到大进行排序'男'同学信息
SELECT * FROM student WHERE sex = '男' ORDER BY age

#第一排序根据学生年龄升序进行排序，第二排序根据‘学号’降序排序的同学信息
SELECT * FROM student ORDER BY age,sno DESC

#分组一般和聚合函数一起使用
#根据性别进行分组，并分别统计各组的人数
SELECT sex, COUNT(*) AS 人数 FROM student GROUP BY sex

#根据性别进行分组，并分别统计各组的同学的平均年龄
SELECT sex, AVG(age) AS 平均年龄 FROM student GROUP BY sex

#HAVING子句 一般是配合GROUP BY使用
#根据性别进行分组，并统计各组的人数大于3人的分组信息
SELECT sex, COUNT(*) AS sexGroup FROM student GROUP BY sex HAVING sexGroup > 3

#MYSQL中 HAVING子句可以单独使用 相当于where
SELECT * FROM student HAVING age = 21

#MYSQL中 HAVING子句也可以和where使用，但是要放在最后
SELECT * FROM student WHERE sex = '男' HAVING age = 21

#显示学生表信息的前3条
SELECT * FROM student LIMIT 3

#显示学生表信息的2-4条
SELECT * FROM student LIMIT 1,3

#显示年龄第2大和第3大的"男"学生
SELECT * FROM student LIMIT WHERE sex = '男' ORDER BY age DESC LIMIT 1,2

#查询年龄大于20岁小于23岁的男生
SELECT * FROM student WHERE age > 20 AND age < 23 AND sex = '男'

#区间的另一种写法 BETWEEN 大于等于20岁小于等于23岁
SELECT * FROM student WHERE age BETWEEN 20 AND 23

#查询性别是男，或者年龄大于等于21岁的学生
SELECT * FROM student WHERE sex = '男' OR age >= 21

#查询地址为''的学生信息
SELECT * FROM student WHERE address = ''

#查询地址为null的学生信息
SELECT * FROM student WHERE address IS NULL

#查询年龄不是21岁的学生
SELECT * FROM student WHERE age != 21
SELECT * FROM student WHERE age <> 21

#查询地址不为null的学生信息
SELECT * FROM student WHERE address IS NOT NULL

#显示拥有系别学生学号，姓名，及所在系名称-[内连接方式]
SELECT s.sno, s.sname, d.dname FROM student s INNER JOIN dept d ON s.did = d.did
SELECT s.sno, s.sname, d.dname FROM student s JOIN dept d ON s.did = d.did

#显示所有学生信息，及所在系情况-[左连接/左外连接]
#左连接已左边的表为主
SELECT s.*, d.dname FROM student s LEFT JOIN dept d ON s.did = d.did
SELECT s.*, d.dname FROM student s LEFT OUTER JOIN dept d ON s.did = d.did

#右连接/右外连接
SELECT d.*, s.* FROM student s RIGHT JOIN dept d ON s.did = d.did

#查询已选课学生姓名，课程名称，课程成绩
SELECT s.sname, c.cname, sc.degree FROM sc 
INNER JOIN student s ON sc.sno = s.sno
INNER JOIN course c  ON sc.cno = c.cno

#查询至少选修一门课的女同学姓名，除去重复姓名项
SELECT DISTINCT s.sname FROM sc INNER JOIN student s ON sc.sno = s.sno
WHERE s.sex = '女'

#查询和'陈樱'同龄的学生信息
SELECT * FROM student WHERE age = (
SELECT age FROM student WHERE sname = '陈樱'
)

#查询课程成绩不及格的选修课课程信息
SELECT * FROM course WHERE cno IN (
SELECT DISTINCT cno FROM sc WHERE degree < 60
)

#查询课程成绩及格的选修课课程信息
SELECT * FROM course WHERE cno NOT IN (
SELECT DISTINCT cno FROM sc WHERE degree < 60
)

#in或者not in一般来说查询效率低，采用多表连接
SELECT c.*, FROM sc JOIN course c ON sc.cno = c.cno
WHERE sc.degree < 60

#ALL表示必须满足子查询结果的所有记录
#查询sc表里成绩最高的记录
SELECT * FROM sc WHERE degree >= ALL (SELECT degree FROM sc)

#查询sc表里成绩最低的记录
SELECT * FROM sc WHERE degree <= ALL (SELECT degree FROM sc)

#any表示满足子查询结果的任意一条记录即可，和some一样
#查询选择’C01‘课程的成绩高于’C02‘的成绩的学生的学号
SELECT * FROM sc WHERE cno = 'C01' AND degree > ANY (
SELECT degree FROM sc WHERE cno = 'C02'
)

SELECT * FROM sc WHERE cno = 'C01' AND degree > SOME (
SELECT degree FROM sc WHERE cno = 'C02'
)

#EXISTS子查询返回结果类型bool
#EXISTS运算符的含义为"存在"，
#使用 EXISTS 关键字引入一个子查询时，就相当于进行一次存在测试。
#外部查询的 WHERE 子句测试子查询返回的行是否存在。
#子查询实际上不产生任何数据；它只返回 TRUE 或 FALSE 值

#显示已经选修了课程的学生信息
SELECT * FROM student s WHERE EXISTS (SELECT * FROM sc WHERE s.sno = sc.sno)


#查询选修了C03课程的学生信息
SELECT * FROM student s WHERE EXISTS (SELECT * FROM sc WHERE s.sno = sc.sno AND sc.cno = 'C03')