数据查询语言（Data Query Language，DQL）全部语法举例（一）单表查询

只用经典4张表，可以练习到所有查询语句。

1、创建表

-- 创建部门表
-- 字段：部门编号、部门名、地点
create table DEPT(  
	DEPTNO int(2) not null,
	DNAME varchar(14), 
	LOC varchar(13) 
);
alter table DEPT add constraint PK_DEPT primary key (DEPTNO);

 -- 创建员工表
 -- 字段：员工编号（主键）、姓名、工作、上级编号（与同表的empno自关联）、入职日期、工资、津贴、部门编号 
create table EMP( 
	EMPNO int(4) primary key, 
	ENAME varchar(10), 
	JOB varchar(9),
	MGR int(4), 
	HIREDATE DATE, 
	SAL double(7,2), 
	COMM double(7,2), 
	DEPTNO int(2)        
);
 -- 外键约束：EMP表的DEPTNO与DEPT表的主键DEPTNO关联  
alter table EMP add constraint FK_DEPTNO foreign key (DEPTNO) references DEPT (DEPTNO);   

-- 创建工资等级表
-- 字段：工资等级（主键）、最低工资、最高工资
create table SALGRADE(  
	GRADE int primary key, 
	LOSAL double(7,2), 
	HISAL double(7,2) 
);

-- 创建奖金表
-- 字段：姓名、工作、工资、津贴
create table BONUS( 
	ENAME varchar(10), 
	JOB varchar(9), 
	SAL double(7,2), 
	COMM double(7,2) 
);

2、插入数据

insert into DEPT (DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
insert into DEPT (DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');
insert into DEPT (DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');
insert into DEPT (DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');

insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7566,'JONES','MANAGER',7839,'1981-04-02',2975,null,20);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,null,30);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,null,10);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,null,20);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7839,'KING','PRESIDENT',null,'1981-11-17',5000,null,10);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7876,'ADAMS','CLERK',7788,'1987-05-23',1100,null,20);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7900,'JAMES','CLERK',7698,'1981-12-03',950,null,30);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7902,'FORD','ANALYST',7566,'1981-12-03',3000,null,20);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7934,'MILLER','CLERK',7782,'1982-01-23',1300,null,10);

insert into SALGRADE (GRADE,LOSAL,HISAL)values(1,700,1200);
insert into SALGRADE (GRADE,LOSAL,HISAL)values(2,1201,1400);
insert into SALGRADE (GRADE,LOSAL,HISAL)values(3,1401,2000);
insert into SALGRADE (GRADE,LOSAL,HISAL)values(4,2001,3000);
insert into SALGRADE (GRADE,LOSAL,HISAL)values(5,3001,9999);

3、单表查询

1）查询emp表（显示整个表）

select * from emp;

2）显示部分列

select empno,ename,sal from emp;

3）显示部分行

使用where子句

select * from emp where sal>2000;

4）显示部分行、部分列

select empno,ename,job,mgr from emp where sal>2000;

5）起别名

select empno as '员工编号',ename as '姓名' from emp; 
-- as、‘’ 可省略
select empno 员工编号,ename 姓名 from emp; 
-- 别名里有特殊符号如空格 ''不可以省略
select empno as '员工 编号',ename as '姓名' from emp;

6）加入算数运算符

select empno,ename,sal,sal+1000 涨薪后,deptno from emp where sal<2500; 
select empno,ename,sal,comm,sal+comm from emp; 
-- 如果有null 加出来为空 但有0加出来是正确的 解决方法见下方 10.2)单行函数-(4)流程函数-if相关-第三条sql语句

7）去重操作

select distinct job from emp;
select distinct job,deptno from emp; 
-- distinct对后面所有列的组合 去重 而不是单独对某一列去重 
-- 比如10的manager 20的manager

8）排序

-- 默认按照升序排列  asc升序 decs降序
select * from emp order by sal; 
select * from emp order by sal desc; 
-- 可以2个排序组合：在工资升序的情况下，工资相同时，deptno按照降序排列 
select * from emp order by sal asc, deptno desc;

9）where子句

将过滤条件放在where子句后面，过滤出符合条件的数据, 可以按数值、字符串、日期类型筛选。

9.1) where子句 + 关系运算符(= > >= < <= <> !=)

从emp表中查询deptno为10（数值）的数据

select * from emp where deptno=10;

从emp表中查询job为CLERK（字符串）的数据,字符串不区分大小写

select * from emp where job = 'CLERK'; 
select * from emp where job = 'clerk';

从emp表中查询job为CLERK（字符串）的数据, 字符串区分大小写

select * from emp where binary job='CLERK';

从emp表中查询hiredate早于1981-12-25(日期)的数据

select * from emp where hiredate < '1981-12-25';

9.2) where子句 + 逻辑运算符（与、或）

从emp表中查询sal在1500和3000之间（不包含1500、不包含3000）的数据

select * from emp where sal>1500 and sal<3000;  
select * from emp where sal>1500 && sal<3000;

从emp表中查询sal在1500和3000之间（包含1500、包含3000）的数据

select * from emp where sal between 1500 and 3000;

从emp表中查询deptno为10或20的数据

select * from emp where deptno=10 or deptno=20;
select * from emp where deptno=10 || deptno=20;
select * from emp where deptno in (10,20);

从emp表中查询job为MANAGER或CLERK或ANALYZE的数据

select * from emp where job in ('MANAGER','CLERK','ANALYZE');

优先级：and优先于or，为了增加可读性，使用小括号() 。

从emp表中查询(job为salesman或者job为clerk)并且工资大于等于1500的数据

select * from emp where (job = 'SALESMAN' or job='CLERK' )and sal>=1500;

从emp表中查询job为salesman或者(job为CLERK并且工资大于等于1500)的数据

select * from emp where job = 'SALESMAN' or (job='CLERK' and sal>=1500);

9.3) 模糊查询

从emp表中查询名字中带A的员工

select * from emp where ename like '%A%'; 
-- %代表任意多个字符

从emp表中查询名字中第二个字符为A的员工

select * from emp where ename like '_A%'; 
--  _代表任意一个字符

从emp表中查询名字中第三个字符为A的员工

select * from emp where ename like '__A%';

9.4) 是否为null

从emp表中查询comm为null的数据

select * from emp where comm is null;

从emp表中查询comm不为null的数据

select * from emp where comm is not null;

10）常用函数

10.1）多行函数（max,min,count,sum,avg）

多行函数是指对一组数据进行运算，针对这一组数据（多行记录）只返回一个结果，也称为分组函数。多行函数自动忽略null值。最大值max()、最小值min()、计数count()针对所有类型；求和sum()、平均值avg() 只针对数值型类型有效。多行函数只有这5个。

select max(sal), min(sal), count(sal), sum(sal), avg(sal) from emp; 
select max(comm), min(comm), count(comm), sum(comm), avg(comm) from emp; 
select count(*) from emp;

10.2）单行函数

单行函数是指对每一条记录输入值进行计算，并得到相应的计算结果，然后返回给用户，也就是说，每条记录作为一个输入参数，经过函数计算得到每条记录的计算结果。

(1) 字符串函数

select ename,length(ename),substring(ename,2,3) from emp;
-- substring字符串截取，从字符下标为2开始，截取长度3（下标从1开始）

(2) 数值函数

-- 未从表中查询：末尾可以加上 from dual，代表一个伪表，也可以省略。
select abs(-5) 绝对值,ceil(5.3) 向上取整,floor(5.9)向下取整,round(3.14)四舍五入;
select ceil(sal) from emp; 
select 10/3,10%3,mod(10,3); 
-- 除以 取模 取模

(3) 日期与时间函数

select curdate(),curtime(); 
-- curdate()年月日 curtime()时分秒
select now(),sysdate();
-- now(),sysdate() 年月日时分秒
select now(),sysdate(),sleep(3),now(),sysdate();
-- now()也可以用于插入数据 例如insert int emp values(9999,'ss','SALESMAN',7698,now(),1000,null,30);
-- now()插入数据的时候还要参照表的结构

(4) 流程函数

if相关：

select empno, ename, sal, if(sal>=2500,'高薪','一般') from emp; 
select empno, ename, sal, if(sal>=2500,'高薪','一般') as '薪资等级'from emp; 
select empno, ename, sal, comm, sal+ifnull(comm,0) from emp;  
select nullif(1,1),nullif(1,2); 
-- 如果value1等于value2，则返回null，否则返回value1

case相关：

case等值判断

select empno, ename, job, 
case job
    when 'CLERK' then '店员'
    when 'SALESMAN' then '销售员'
    when 'MANAGER' then '经理'
    else '其他' 
end as '岗位' ,
sal from emp;

case区间判断

select empno, ename, sal,
case 
    when sal<= 1000 then 'C'
    when sal<= 2000 then 'B'
    when sal<= 3000 then 'A'
    else 'H'
end as '工资等级'
from emp;

(5) 其他函数

select database(),user(),version();

11）group by

统计各部门的平均工资

select deptno, avg(sal) from emp group by deptno;
select deptno, avg(sal) from emp group by deptno order by deptno desc;

统计各岗位的平均工资

select job, avg(sal) from emp group by job; 
select job, lower(job),avg(sal) from emp group by job;

12）having

统计各个部门的平均工资，只显示平均工资2000以上的

select deptno, avg(sal) as 平均工资 from emp group by deptno having avg(sal)>2000; 
-- 分组以后进行二次筛选 having

统计各个岗位的平均工资，除了MANAGER

-- 方法1
select job, avg(sal) from emp where job != 'MANAGER' group by job;
-- 方法2：
select job, avg(sal) from emp group by job having job != 'MANAGER';

where：分组前过滤；having：分组后过滤

13）总结

select语句书写顺序：（顺序固定，不可改变）

select column, group_function(column)
from table
[where condition]
[group by group_by_expression]
[having group_condition]
[order by column];

select语句的执行顺序：

from-where-group by-select -having-order by

14）练习

列出工资最小值小于2000的职位

select job, min(sal) from emp group by job having min(sal)<2000 ;

列出平均工资大于1200的部门和工作搭配组合

select deptno, avg(sal) from emp group by deptno, job having avg(sal)>1200;

统计人数小于4的部门的平均工资

select deptno, count(*), avg(sal) from emp group by deptno having count(*)<4;

统计各部门的最高工资，排除最高工资小于3000的部门

select deptno, max(sal) from emp group by deptno having max(sal)>=3000;