SQL语句总结

0.表结构

查看表的结构

desc <table name>;

表名称：dept 描述：部门信息表

表名称：dept 描述：部门信息表

英文字段名称	中文描述	类型
DEPTNO	部门编号	INT(2)
DNAME	部门名称	VARCHAR(14)
LOC	位置	VARCHAR(13)

表名称：emp 描述：员工信息表

英文字段名称	中文描述	类型
EMPNO	员工编号	INT (4)
ENAME	员工姓名	VARCHAR(10)
JOB	工作岗位	VARCHAR(9)
MGR	上级领导	INT (4)
HIREDATE	入职日期	DATE
SAL	薪水	DOUBLE(7,2)
COMM	津贴	DOUBLE (7,2)
DEPTNO	部门编号	INT(2)

注：DEPTNO字段是外键，DEPTNO的值来源于dept表的主键，起到了约束的作用

表名称：salgrade 描述：薪水等级信息表

英文字段名称	中文描述	类型
GRADE	等级	INT
LOSAL	最低薪水	INT
HISAL	最高薪水	INT

1.查询指令

一个完整的select语句格式如下

select 字段
from 表名
where …….
group by ……..
having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现)
order by ……..

以上语句的执行顺序

1. 首先执行where语句过滤原始数据
2. 执行group by进行分组
3. 执行having对分组数据进行操作
4. 执行select选出数据
5. 执行order by排序

原则：能在where中过滤的数据，尽量在where中过滤，效率较高。having的过滤是专门对分组之后的数据进行过滤的。

1.1 查询：select

普通查询：

查询多个字段

select empno, ename from emp;

查询所有的字段（Java编程时，不建议写*,最好写出所有字段）

select * from emp;

字段值做一些运算

select empno as ‘员工编号’, ename as ‘员工姓名’, sal*12 as ‘年薪’ from emp;

注意:字符串必须添加单引号 | 双引号

条件查询：

条件查询需要用到where语句，where必须放到from****语句表的后面，支持如下运算符

运算符	说明
=	等于
<>或!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between … and ….	两个值之间,等同于 >= and <=
is null	为null（is not null 不为空）
and	并且
or	或者
in	包含，相当于多个or（not in不在这个范围中）
not	not可以取非，主要用在is 或in中
like	like称为模糊查询，支持%或下划线匹配%匹配任意个字符下划线，一个下划线只匹配一个字符

注意：

• 数值可以直接用，但是字符串需要加单引号或者双引号

select empno, ename, sal from emp where sal between 1600 and 3000; //包含最大值和最小值

select empno, ename from emp where job <> ‘MANAGER’;

• null值比较需要用is,不能用等于

select * from emp where comm=null; //错误

select * from emp where comm is null; //正确

select * from emp where comm is not null; //正确

• 运算符号优先级问题不用记，尽量采用括号

查询薪水大于1800，并且部门代码为20或30的员工

select * from emp where sal > 1800 and (deptno = 20 or deptno = 30);

• in

in表示包含的意思，完全可以采用or来表示，采用in会更简洁一些

select * from emp where job in (‘manager’,‘salesman’);

• not

查询出薪水不包含1600和薪水不包含3000的员工

select * from emp where sal not in (1600, 3000);

• like

Like 中的表达式必须放到单引号中|双引号中

查询姓名中包含O的所有的员工

select * from emp where ename like ‘%O%’;

查询姓名中第二个字符为A的所有员工

select * from emp where ename like ‘_A%’;

1.2 数据排序：order by

order by后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by默认采用升序(asc)，如果存在where子句那么order by必须放到where语句的后面

select * from emp order by sal;

select * from emp where job=‘MANAGER’ order by sal;

select * from emp order by job,sal; //多个字段排序，如果根据第一个字段排序重复了，会根据第二个字段排序

select * from emp order by sal desc; //手动指定从大到小排序

select * from emp order by job desc, sal desc;

1.3 数据处理函数/单行处理函数==

Lower	转换小写	select lower(ename) from emp;
upper	转换大写	select * from emp where job=upper(‘manager’);
substr	取子串（substr(被截取的字符串,起始位置（从1开始）,截取的长度)）	select * from emp where substr(ename, 1, 1)=upper(‘m’);
length	取长度	select length(ename), ename from emp where length(ename)=5;
trim	去首尾空格，中间不去	select * from emp where job=trim(upper('manager '));
str_to_date	将字符串转换成日期	select * from emp where HIREDATE=‘1981-02-20’;
date_format	格式化日期	select empno, ename, date_format(hiredate, ‘%Y-%m-%d %H:%i:%s’) as hiredate from emp;
format(几乎不用)	设置千分位
round	四舍五入	select round(123.56);
rand()	生成随机数	select rand(); select * from emp order by rand() limit 2;//随机抽取记录数
Ifnull	可以将null转换成一个具体值	select ifnull(comm,0) from emp;

另外：case … when … then ……else …end用法

如果job为MANAGERG薪水上涨10%,如果job为SALESMAN工资上涨50%

select empno, ename, job, sal, case job when ‘MANAGER’ then sal1.1 when ‘SALESMAN’ then sal1.5 end as newsal from emp;

其他的工资不动，需要添加else

select e.,sal ,case job when ‘salesman’ then sal1.1 when ‘clerk’ then sal*1.2 else sal end as new_sal from emp e;

1.4 分组函数/聚合函数/多行处理函数

count	取得记录数	select count(comm) from emp; //津贴不为null的员工数 select count(distinct job ) from emp; //distinct，去除重复
sum	求和	select sum(comm) from emp; select sum(sal+IFNULL(comm, 0)) from emp;
avg	取平均	select avg(sal) from emp;
max	取最大的数	select max(sal) from emp; select max(str_to_date (hiredate, ‘%Y-%m-%d’)) from emp; //取得最晚入职得员工
min	取最小的数	select min(sal) from emp; select min(str_to_date(hiredate, ‘%Y-%m-%d’)) from emp; //取得最早入职得员工（可以不使用str_to_date转换）

组合多个聚合函数一起使用：

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

注意：分组函数自动忽略空值==，不需要手动的加where条件排除空值。

select count(*) from emp where xxx; 符合条件的所有记录总数。

select count(comm) from emp; comm这个字段中不为空的元素总数。

注意：分组函数不能直接使用在where关键字后面。

mysql> select ename,sal from emp where sal > avg(sal);

ERROR 1111 (HY000): Invalid use of group function

1.5 分组查询

分组查询主要涉及到两个子句，分别是：group by和having

分组函数的执行顺序：

根据条件查询数据

分组

采用having过滤，取得正确的数据

group by:

在SQL语句中若有group by 语句，那么在select语句后面只能跟分组函数+参与分组的字段。（！！！！！）

单个分组：

取得每个工作岗位的工资合计:select job, sum(sal) from emp group by job;

如果使用了order by，order by必须放到group by后面

多个分组：

elect job,deptno,sum(sal) from emp group by job,deptno; //正确

select empno,deptno,avg(sal) from emp group by deptno; //错误

having:

如果想对分组数据再进行过滤需要使用having子句

取得每个岗位的平均工资大于2000的job

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

1.6 连接查询/跨表查询

内连接：（只查询连接条件相等的数据）

• 显示薪水大于2000的员工信息，并显示所属的部门名称

select e.ename, e.sal, d.dname from emp e join dept d on e.deptno=d.deptno where e.sal>2000;

外连接：

分为左连接和右连接，左连接以左面的表为准和右边的表比较，和左表相等的不相等都会显示出来，右表符合条件的显示,不符合条件的不显示，右连接恰恰相反。

select e.ename, e.sal, d.dname from dept d left join emp e on e.deptno=d.deptno; //左连接

select e.ename, e.sal, d.dname from emp e right join dept d on e.deptno=d.deptno; //右连接

1.7 子查询

子查询就是嵌套的select语句，可以理解为子查询是一张表

• 在where语句中使用子查询，也就是在where语句中加入select语句

查询哪些人是管理者，要求显示出其员工编号和员工姓名

select empno, ename from emp where empno in(select distinct mgr from emp where mgr is not null); //先找出领导名

查询哪些人的薪水高于员工的平均薪水，需要显示员工编号，员工姓名，薪水

select empno, ename, sal from emp where sal > (select avg(sal) from emp);

• 在from语句中使用子查询，可以将该子查询看做一张表

查询哪些人是管理者，要求显示出其员工编号和员工姓名（同上）

select e.empno, e.ename from emp e join (select distinct mgr from emp where mgr is not null) m on e.empno=m.mgr; //采用连接查询

查询各个部门的平均薪水所属等级，需要显示部门编号，平均薪水，等级编号

思路：首先取得各个部门的平均薪水，然后将部门的平均薪水作为一张表与薪水等级表建立连接，取得等级

select a.deptno,a.avg_sal,g.grade from (select deptno,avg(sal) avg_sal from emp group by deptno ) a join salgrade g on a.avg_sal between g.losal and hisal;

1.8 limit语句

select * from emp limit 5; //取前5条

select * from emp limit 1,2; //从第二条开始取两条数据