SQL语句拓展（基于MySQL数据库）

在上一篇中写了MySQL数据库中ddl和dml的相应操作，本篇的内容是对SQL 语句一些复杂操作时的拓展。
# ddl和dml #
ddl语句操作表，数据库，非数据操作都是ddl语句；
dml语句：操作数据的命令是dml语句。

1.where条件查询
1.1等值比较=
查询部门号为10的所有员工：
select * from emp where deptno=10；
查询时的大小写问题：当需要判断字符串是否相等时需要将字符串用单引号引起来，所有的关系型数据库系统都是这样做的，但要注意的是，单引号中的字符串内容是要区分大小写的MySQL的区分大小写在where后加BINARY函数。
例如：select * from emp where DEPTNO =20 and binary JOB=’clerk’；

1.2 非等值比较

除了可以使用等值判断以外，我们还可以使用其他条件判断符号，例如：> < >= <= <>

取出薪水值大于2000的所有雇员的名字和薪水：
select ename,sal from emp where sal > 2000;

取出薪水位于800和1500之间的雇员名字和薪水：
select ename,sal from emp where sal > 800 and sal < 1500；
SELECT ename,sal from emp where SAL BETWEEN 800 and 1500；
两种方法的区别：between包含临界值。

取出所有部门号不是10的雇员的名字和薪水：
select ename,sal,DEPTNO from emp where DEPTNO<> 10；

2.in语句

2.1薪水值为800，1250，1500，2000的雇员：
select * from emp where sal IN(800,1250,1500,2000)；

2.2查询名字为jones，king或james的员工：
select * from emp where ENAME IN(‘JONES’,’KING’,’JAMES’)；

3.处理日期
查询所有在82年一月一号以后入职的员工：
SELECT * from emp where HIREDATE > ‘1982-01-01’；

4 .AND,OR,NOT
4.1部门号为10并且薪水>1000的员工：
select * from emp where DEPTNO =10 and sal >1000；

4.2部门号为10或者工作岗位是clerk的员工：
select * from emp where DEPTNO = 10 OR JOB=’CLERK’

4.3薪水值没有位于800，1200，2000之中的员工：
select * from emp where sal not in(200,1200,2000);

5.模糊查询
关键字‘like’被用作模糊查询，在用户只知道字符串的一部分时，可以使用‘like’进行模糊查询。‘like’通常和通配符一起使用，通配符有两个：
‘%’表示零个或多个字符：0-N个任意字符；
‘-’表示一个字符：一个任意字符。

5.1查询名字中含有‘LA’的人员：
select * from emp where ENAME LIKE ‘%LA%’
需要注意的是这里的模糊查找区分大小写。

5.1查询名字中第二个字母中含有‘A’的雇员：
select * from emp where ENAME like ‘_A%’

6.Order by 排序 asc desc
6.1.按照姓名的升序排列：
select * from emp ORDER BY ENAME asc

6.2 多字段排序按照薪水升序部门降序排序：
select * from emp ORDER BY sal ASC , deptno DESC

7.SQL函数
7.1 Lower函数将字符串全部转换为小写：
查询ename字符串并将值改为小写:select LOWER(ENAME) from emp

7.2 upper函数将字符串全部转换为大写;
查询job字段并将值改为大写：select UPPER(JOB) from emp

7.3 sub字符串的截取：
求一个字符串helloworld中的子串并在虚拟表中查询：
SELECT ‘helloword’, SUBSTR(‘helloword’, 3, 5) from DUAL；

7.4 Ascii码
在虚拟表中求一个字符的ASCII码数值
SELECT ASCII(‘A’) from DUAL；

7.5 round四舍五入
在虚拟表中查询34.643的值
select ROUND(34.643) from DUAl –默认取整
select ROUND(34.643,2) from DUAL –小数点后面保留位数，2代表保留2位
select ROUND(12334.643,-2) from DUAL –精度可以为负数

8. to_char-to_date-to_number
8.1在虚拟表中查询系统时间并转换为字符串：
select date_format(sysdate(),’%Y-%m-%d’) from dual;

8.2在虚拟表中查询字符串并转换成日期‘2018-08-30’：
select STR_TO_DATE(‘2018/08/30 ‘,’%Y/%m/%d ‘) from DUAL；

8.3 在虚拟表中查询数字123转换为字符串‘123’：
select CAST(123 AS CHAR(3)) from DUAL；

8.4在虚拟表中查询字符串‘123’转换成数字123：
select cast（‘123’ as signed integer）from dual；

9查询comm字段，如果字段为空，则替换成0
select COMM, IFNULL(COMM,0) from emp

10.组函数
10.1 求薪水的总和，平均值，最大值和最小值：
select SUM(sal),AVG(sal),MAX(sal),MIN(sal) from emp

10.2查询总记录数：
SELECT COUNT(*) from emp
count不记录数值为空的字段

11.Group by分组：
有的时候我们需要将表中的数据进行分组，比如说我们需要计算每个部门的平均工资，这个时候必须首先将现有的数据按照部门进行分组，然后再计算每个组员工的平均薪水。Group by 正是为了解决这样的需求而设立。
11.1将部门编号相同的数据放在一组，查询平均工资：
SELECT DEPTNO, AVG( sal) from emp GROUP BY DEPTNO

11.2将部门编号相同且职位相同的数据放在一组，查询出每组的平均工资：
SELECT deptno,job,avg(sal) from emp GROUP BY DEPTNO,JOB

11.3员工薪水大于1200的雇员，按照部门进行分组，而且这些人分组后组内平均薪水必须大于2000：（having对分组进行限制）
SELECT deptno,avg(sal) FROM emp WHERE SAL>2000 GROUP BY DEPTNO HAVING AVG(SAL)>2000；

12.两表关联查询
select 字段 from 表一,表二；

条件关联查询：
SELECT * from emp ,dept where emp.DEPTNO = dept.DEPTNO；

查询员工表所对应的部门名称：
SELECT e.*,d.DNAME from emp e , dept d WHERE e.DEPTNO=d.DEPTNO；

13. 多表关联查询：
在关联查询的时候，左面表放在内存中用关联字段比对右面表，所以左面放小表，右面放大表，节省内存空间。

工作职位是’PRESIDENT’的雇员姓名，部门名称和薪水等级（有emp，dept，salgrade三张表）：
SELECT e.JOB, e.ENAME,d.DNAME,s.grade from emp e,dept d,salgrade s where e.JOB= ‘PRESIDENT’ and e.DEPTNO=d.DEPTNO AND e.SAL>=s.losal and e.SAL<=s.hisal；

14.自关联
员工的姓名，及其上级经理的姓名：
SELECT * from emp e1,emp e2 WHERE e1.MGR = e2.EMPNO；

15.join关联查询
15.1 笛卡尔积关联查询员工和部门表：
select * from dept d join emp;

15.2 deptno关联查询员工和部门表：
select * from dept d join emp using(deptno); using 只能做同名字段关联

select * from dept d join emp e on e.deptno=d.deptno; on 条件可以关联不同字段的名字。

15.3雇员的薪水等级：
select * from salgrade s join emp e on e.sal>=s.losal and e.sal<=s.hisal;

15.4 查询员工表，部门表，薪水等级表，多表关联 ：
select * from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal>=s.losal and e.sal <= s.hisal;

15.5左外关联和右外关连;
普通关联方式关联主键出现null值的时候忽略该条数据
员工表有两条数据的部门编号为null ，当普通关联的时候主键为null的数据将被忽略
需求：查询出所有的员工，如果员工的部门编号不为空，则把部门信息查出，如果部门编号为空，也要把员工信息查出来，部门信息可以为Null;
select * from emp e left join dept d on e.deptno =d.deptno;

16.子查询
子查询就是查询套查询
16.1查询哪些员工是领导：
select * from emp e where e.deptno in(select mgr from emp) order by e.mgr;

16.2 薪水在整个雇员的平均薪水之上的数据：
select * from emp where sal>(select avg(sal) from emp);