SQL语句学习笔记

SELECT sno as '学号',sn as '姓名',age as '年龄' FROM s WHERE age>20;
SELECT tno as '教师号' ,tn as '教师名',YEAR(NOW())-age as '出生年份' FROM t LIMIT 3;
SELECT DISTINCT sn 不是计算机专业和信息专业的学生 FROM s WHERE maj NOT in ('计算机','信息安全','信息与计算科学');
SELECT cn as '学时在1-50的课程',cno,ct FROM c WHERE ct BETWEEN 1 AND 50;
SELECT DISTINCT dept as'去除重复' FROM s;
SELECT sn as'优秀学生' FROM s WHERE sno IN(SELECT sno FROM sc WHERE score>=90);
SELECT sn as'姓名长度至少是3个汉字且倒数第三个汉字是“张”的学生' FROM s WHERE sn LIKE '%张__';
SELECT sno,cno FROM sc WHERE score IS NULL;
SELECT cno as '查询学号“s1”学生的选课记录，并且按照成绩降序排列。' FROM sc WHERE sno='s1' ORDER BY score DESC;
SELECT COUNT(*) FROM s WHERE maj='信息安全';
SELECT AVG(age) as '平均年龄',MAX(age),MIN(age),SUM(age)FROM s;
SELECT COUNT(*) as 's4选课门数' FROM sc WHERE sno='s4';


--分组查询，与聚合函数同用
SELECT sno,COUNT(*) FROM sc GROUP BY sno;
SELECT sex, COUNT(sno) AS '男女生人数' FROM s GROUP BY sex;
SELECT COUNT(sno) AS '各学院男生人数' FROM s WHERE sex='男' GROUP BY dept;
SELECT cno, COUNT(*)选课人数, MIN(score) 最低成绩 FROM sc GROUP BY cno;
SELECT dept,sex,COUNT(sno) AS '各学院的男女生人数' FROM s GROUP BY dept,sex;
SELECT dept,sex,COUNT(sno) AS '信息学院的男女生人数' FROM s WHERE dept='信息学院' GROUP BY dept,sex;


--HAVING跟着group BY(WHERE筛选元组[行]{其后不能出现聚合函数},HAVING筛选分组)
SELECT cno, COUNT(*)选课人数 FROM sc GROUP BY cno HAVING COUNT(*)>=2;
SELECT COUNT(cno) AS '按课时统计课程门数，只保留两门以上的组' FROM c GROUP BY ct HAVING COUNT(*)>2;



--为什么报错？选最少选课人数
SELECT cno, COUNT(*)选课人数 FROM sc GROUP BY cno HAVING MIN(COUNT(*));
在 SQL 中，HAVING 子句通常用于对 GROUP BY 的结果集进行过滤，但它不能直接使用聚合函数（如 MAX）直接作用于另一个聚合函数的结果（如 COUNT(sno)）。HAVING 子句中的 MAX 函数需要应用于一个已经确定的列或聚合表达式的结果，而不是另一个聚合函数。

SELECT cno, COUNT(sno) AS '选课人数最多的课' FROM sc GROUP BY cno HAVING COUNT(sno) = (
 SELECT MAX(cnt) FROM (
  SELECT cno, COUNT(sno) AS cnt FROM sc GROUP BY cno  
 ) AS subquery  
);

多关系查询
表的链接靠外码
内连接
SELECT * FROM s,c;      笛卡尔积：m+n列 k1*k2行
等值连接
SELECT * FROM s,sc WHERE s.sno=sc.sno;
SELECT * FROM s,c,sc WHERE s.sno=sc.sno AND sc.cno=c.cno;
SELECT c.cn,COUNT(sno) as '数据库课程选修人数',AVG(score) as '平均成绩' FROM c JOIN sc ON c.cno=sc.cno GROUP BY sc.cno HAVING c.cn='数据库系统';

自然连接 ansi
SELECT s.sno,cno FROM s INNER JOIN sc on s.sno=sc.sno;
SELECT c.cno,cn,score FROM s JOIN sc on s.sno=sc.sno JOIN c ON c.cno=sc.cno WHERE sn='张三';

自连接，给表起别名as
SELECT * FROM c AS c1,c AS c2 WHERE c1.cn='程序设计基础' AND c1.ct>c2.ct;

外连接,不匹配的信息为null
SELECT * FROM s LEFT JOIN sc on s.sno=sc.sno; 保留左边不满足
SELECT * FROM sc RIGHT JOIN s on s.sno=sc.sno; 保留右边
SELECT * FROM s LEFT JOIN sc on s.sno=sc.sno LEFT JOIN c ON c.cno=sc.cno;

交叉连接cross join

子查询
SELECT * FROM s JOIN sc ON s.sno=sc.sno JOIN c ON c.cno=sc.cno and age=(SELECT MAX(age) FROM s WHERE dept='信息学院');
SELECT * FROM s JOIN sc ON s.sno=sc.sno JOIN c ON c.cno=sc.cno WHERE age in (SELECT MAX(age) FROM s WHERE dept='信息学院');


(= ANY) = IN = =
>ANY = > MIN(expr)
<ANY = < MAX(expr)
>ALL = > MAX(expr)
<ALL = < MIN(expr)
<>ALL = not in

s表中全部列：s.*

相关子查询
引用父查询表属性值
SELECT sno as '选修了c1的学生' FROM s WHERE 'c1' in (SELECT cno FROM sc WHERE sno=s.sno);

存在量词 EXISTS 返回 真假
SELECT sno as '选修了c1的学生' FROM s WHERE EXISTS (SELECT * FROM sc WHERE sno=s.sno AND cno = 'c1');


全部学生选的课=没有一个学生不选的课程!!!!!!!!!!
SELECT * FROM c WHERE not EXISTS(SELECT * FROM s WHERE NOT EXISTS (SELECT * FROM sc WHERE c.cno=sc.cno and s.sno=sc.sno));


练习：
SELECT * FROM s as s1 WHERE s1.maj!='计算机' AND EXISTS(SELECT dept FROM s as s2 WHERE maj='计算机' AND s1.dept=s2.dept);
SELECT * FROM s as s1 WHERE s1.maj<>'计算机' AND EXISTS(SELECT dept FROM s as s2 WHERE maj='计算机' AND s1.dept=s2.dept);


#没有一个课不选的学生
SELECT * FROM s WHERE not EXISTS(SELECT * FROM c WHERE NOT EXISTS (SELECT * FROM sc WHERE c.cno=sc.cno and s.sno=sc.sno));


#错
SELECT sno FROM sc WHERE cno<>'c1';
#对
SELECT sno FROM s WHERE sno NOT IN (SELECT sno FROM sc WHERE s.sno=sc.sno AND cno='c1' );
#解释：对于选课记录来说，一个学生可能选了c1，但是也选了别的课，所以对于“查找没有选c1课的学生”应用 NOT IN



SELECT * FROM s WHERE sno NOT IN(SELECT sno FROM sc,c WHERE sc.cno=c.cno AND c.cn NOT LIKE '%数据库%');
SELECT * FROM s WHERE not EXISTS(SELECT * FROM tc WHERE tc.tno='t1' AND NOT EXISTS (SELECT * FROM sc WHERE sc.cno=tc.cno and s.sno=sc.sno));