SQL基础学习（七）- MySQL的查询语句

功能查询  

1、简单查询

1、查询部门表中的部门编号、部门名称；
SELECT DEPTNOas 部门编号,DNAMEas 部门名称 FROM DEPT;

2、查询工资大于等于1000并且小于3000的员工信息

SELECT * FROM EMPWHERE SAL>=1000AND SAL<3000;

3、查询年龄在23-25之间的学生

SELECT * FROM stu WHERE ageBETWEEN23AND25;

4、查询工作是经理、普通员工、销售员的所有员工信息

SELECT * FROM EMP WHERE JOB IN ('MANAGER','CLERK','SALESMAN');

2、模糊查询

查询员工编号大于7000且姓名中不包含“S”字符的员工信息
SELECT * FROM EMP WHERE EMPNO >7000 AND ENAME NOT LIKE '%S%';

查询名字里姓张的学生。

SELECT * FROM sstud WHERE sname LIKE '张_';
SELECT * FROM sstud WHERE sname LIKE '张__';

3、聚合函数查询

SELECT COUNT(*) FROM stu; //一般要专门给一个别名

SELECT AVG(age) AS averageAge FROM sstud;//取平均值

SELECT ROUND(AVG(age)) AS averageAge2 FROM sstud;//四舍五入

SELECT SUM(age) AS sumAge FROM sstud;//和

SELECT MAX(age) AS maxAge FROM sstud;//最大值

4、去重查询

SELECT DISTINCT sname,age FROM sstud WHERE age = ( SELECT MIN(age) FROM sstud ) ;
SELECT DISTINCT saddress FROM sstud;

5、排序-desc、asc

SELECT * FROM sstud ORDER BY age ASC;//按年龄升序排
SELECT age,sname FROM sstud ORDER BY age DESC;//降序

6、分组-GROUP BY

SELECT saddress,AVG(age)AS 平均年龄 FROM sstud GROUP BY saddress; 
SELECT saddress, SUM(age)AS 年龄总和 FROM sstud GROUP BY saddress; 

固定搭配1: SELECT ... FROM ... WHERE ... ORDER BY ... ----ORDER BY子句要放在最后

固定搭配2: SELECT ... FROM ... GROUP BY ... HAVING .... ----GROUP BY子句中的条件用的是HAVING (不能用WHERE)

SELECT saddress,AVG(age)AS 平均年龄 FROM sstud GROUP BY saddress HAVING AVG(age)>22; 
SELECT saddress,AVG(age) AS a FROM sstud GROUP BY saddress HAVING a>22; //留意一下别名的用法