课堂小练-----mysql

SELECT * FROM stu;
SELECT sname,age,gender FROM stu;
SELECT FROM WHERE GROUP BY  HAVING ORDER BY LIMIT TO
BETWEEN AND  IN IS NULL比较运算符
逻辑运算符
AND
OR
NOT
算术运算符
+-*/%
SELECT * FROM stu WHERE gender="female" && age<=50;
SELECT * FROM stu WHERE sid='s_1001' || sname='lisi';
SELECT * FROM stu WHERE sid IN('s_1001','s_1002','s_1003');
SELECT * FROM stu WHERE sid NOT IN('s_1001','s_1002','s_1003');
SELECT * FROM stu WHERE age IS NULL;
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;
SELECT * FROM stu WHERE gender<>'male';
SELECT * FROM stu WHERE gender!='male';
SELECT * FROM stu WHERE sname IS NOT NULL;
SELECT * FROM stu WHERE sname LIKE'___';
SELECT * FROM stu WHERE sname LIKE'____i';
SELECT * FROM stu WHERE sname LIKE 'z%';
SELECT * FROM stu WHERE sname LIKE '_i%';
SELECT * FROM stu WHERE sname LIKE '%a%';
去除重复记录
SELECT DISTINCT  sal ,comm FROM emp ;
SELECT sal,comm,sal+IFNULL(comm,0)AS `sum` FROM emp;
SELECT ename,job ,CONCAT(ename,"的工作是",job) AS `濑瘊子`,'哈弗大学'AS `学校` FROM emp;
SELECT * FROM stu ORDER BY age ASC;
SELECT * FROM stu ORDER BY age DESC;
SELECT * FROM emp ORDER BY sal DESC ,empno ASC;
聚合函数
1.count (计算列的时候计算非空不包含空值)
SELECT COUNT(*)AS `总数` FROM emp ;
SELECT COUNT(comm) FROM emp;
SELECT COUNT(*) FROM emp WHERE sal>=2500;
SELECT COUNT(*) FROM emp WHERE (sal+ IFNULL(comm,0))>=2500;
SELECT COUNT(comm),COUNT(mgr)FROM emp;
SUM 和AVG
SELECT SUM(sal),SUM(comm) FROM emp;
SELECT SUM(sal+IFNULL(comm,0))FROM emp;
SELECT AVG(sal) FROM emp;
SELECT MAX(sal),MIN(sal) FROM emp;
SELECT deptno as`部门编号`,COUNT(deptno)as`部门人数`,SUM(sal)AS `部门支出` FROM emp GROUP BY deptno;
非分组之外的字段不能直接写出
否则只会是第一个元素的值
SELECT deptno,COUNT(*) FROM emp  WHERE sal>=1500 GROUP BY deptno;
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000;
HAVING 可以使用函数 而WHERE不可以使用
HAVING是分组之后过滤，而WHERE 是分组之前过滤的。
LIMIT查询
SELECT* FROM emp LIMIT 0,5;0从0开始查询5个。
SELECT * FROM emp LIMIT 5;
总结
WHERE对原始数据做筛选
GROUP BY 把数据进行分组；
HAVING 分组后进行筛选
ORDER BY 排序 ASC 升序 DESC 降序
LIMIT 限制 一般用于分页
WHERE-groupby- HAVING -orderby -SELECT - LIMIT;

1、 查询Student表中的所有记录的Sname、Ssex和Class列。
2、 查询教师所有的单位即不重复的Depart列。
3、 查询Student表的所有记录。
4、 查询Score表中成绩在60到80之间的所有记录。
5、 查询Score表中成绩为85，86或88的记录。
6、 查询Student表中“95031”班或性别为“女”的同学记录。
7、 以Class降序查询Student表的所有记录。
8、 以Cno升序、Degree降序查询Score表的所有记录。
9、 查询“95031”班的学生人数。
10、查询Score表中的最高分的学生学号和课程号。
11、查询‘3-105’号课程的平均分。
12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
13、查询最低分大于70，最高分小于90的Sno列。
14、查询所有学生的Sname、Cno和Degree列。
15、查询所有学生的Sno、Cname和Degree列。
SELECT SNAME,SSEX,CLASS FROM student ;
SELECT DEPART FROM teacher GROUP BY DEPART;
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' AND SSEX='女';
SELECT * FROM student ORDER BY CLASS DESC;
SELECT * FROM score ORDER BY CNO ASC,DEGREE DESC;
SELECT COUNT(*) FROM student WHERE CLASS='95031';
SELECT SNO,CNO FROM score WHERE DEGREE = (SELECT MAX(DEGREE) FROM score);
SELECT AVG(DEGREE) FROM score WHERE CNO='3-105';
SELECT AVG(DEGREE)FROM score WHERE CNO LIKE '3%' GROUP BY CNO HAVING COUNT(*)>=5;
SELECT SNO FROM score WHERE DEGREE BETWEEN 70 AND 90;
SELECT  s1.SNAME AS NAME,s1.SNO AS sno ,s2.DEGREE degree  FROM student s1 LEFT JOIN score s2 ON s1.SNO=s2.SNO;
nue ;