SQL查询语句-Select

Select

查询字段

select 字段1,字段2 from 表名;

查询所有

select * from dept;

起别名

select deptno,dname as deptname from dept;
as 可省略

若别名中有空格，用单括号括起来在所有数据库当中，字符串统一使用单引号括起来，单引号是标准。双引号在 oracle 数据库中用不了。但是在 mysql 中可以使用。

去除重复记录

select distinct job from emp;
select distinct job,deptno from emp; 只能出现在所有字段的最前方，表示两个字段联合去重
select count(distinct job) from emp; 统计工作岗位数量

数学运算

select ename，sal*12 as yearsal from emp;

条件查询

=

• 查询薪资等于800的员工姓名和编号
  select empno, ename from emp where sal = 800;
• 查询SMITH的编号和薪资
  select empno, sal from emp where ename='SMITH';

<>或!=

• 不等于查询薪资不等于800的员工姓名和编号
  select empno,ename from emp where sal!=800;

<

• 查询薪资小于2000的员工姓名和编号
  select empno,ename,sal from emp where sal<2000;

<=

• 查询薪资小于等于3000的员工姓名和编号
  select empno,ename,sal from emp where sal <=3000;

＞

• 查询薪资大于3000的员工姓名和编号
  select empno,ename,sal from emp where sal 3000;

＞=

• 查询薪资大于等于3000的员工姓名和编号
  select empno,ename,sal from emp where sal >=3000;
• between…and… 等同于 >= and <=
• 查询薪资在2450和3000之间的员工信息？包括2450和3000
  select empno,ename,sal from emp where sal>=2450 and sa1<=3000;
select empno,ename,sal from emp where salL between 2450 and 3000; 必须遵循左小右大

is null(is not null)

• 查询哪些员工的津贴/补助为null
  select empno,ename,sal,comm from emp where comm is null;

and or

• 查询工作岗位是ANAGER并且工资大于2500的员工信息
  select empno,ename,job,sal from emp where job='MANAGER' and sal>2500;
• 查询工作岗位是MANAGER和SALESMAN的员工
  select empno,ename,job from emp where job='MANAGER' or job='SALESMAN';
• and优先级比or高
• 查询工资大于2500并且部门编号为10的员工，或者20部门的所有员工
  select * from emp where sal>2500 and deptno=10 or deptno=20;
• 查询工资大于2500，并且部门编号为10或20部门的员工
  select * from emp where sal>2500 and (deptno=10 or deptno=20);

in

• 相当于多个or
• 查询薪资是800和5000的员工信息
  select ename,sal from emp where sal in(800,5000);
select ename,sal from emp where sal not in(800,5000,3000);

like

• 模糊查询 % 任意多个字符 _ 任意一个字符
• 找出名字中含有o的
  select ename from emp where ename like '%o%';
• 找出第三个字母是R的
  select ename from emp where ename like '__R%';
• 找出名字中有_的
  select name from t_student where name like '%\_%'; 要用转移字符

排序

• 查询所有员工薪资，默认升序，asc 升序 desc 降序
  select ename,sal from emp order by sal;
select ename,sal from emp order by sal desc;
• 多个字段排序，只有 sal 相等的时候，才会启用 ename 排序
  select ename,sal from emp order by sal asc,ename asc;
• 按第2列 sal 排序
  select ename,sal from emp order by 2;

单行处理函数

每个输入对应一个输出

• 大小写转换
  select lower(ename) as ename from emp;
select upper(name) as name from t_student;
• 取长度
  select length(ename) as enamelength from emp;
• 取子串：起始下标，截取的长度，起始下标从 1 开始
  select substr(ename,1,1) as ename from emp;
• 模糊查询
  select ename from emp where substr(ename,1,1)='A';
• 字符拼接
  select concat(empno,ename) from emp;
• 去空格：对传送过来的数据处理
  select * from emp where ename=trim(' KING ');
• ifnull (数据，被当做的值)
  select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;

  在所有数据库当中，NULL 参与的数学运算，最终结果都是 NULL
  select 跟某个表的字段名（可以等同看做变量名），也可以跟字面量/字面值（数据）

• 四舍五入
• select round(sal,1) as result from emp;
• 随机数：生成 100 以内的随机数
  select round(rand()*100,0) from emp;
• str_to_date：varchar 转换成 date
• date_format：date 转换成 varchar
• 格式化数字：设置千分位 #了解#
  select ename,format (sal, '$999,999') as sal from emp;
• 当员工的工作岗位是 MANAGER 的时候，工资上调 10，当工作岗位是 SALESMAN 的时候，工资上调 50 号，其它正常。
  select ename,job,sal as oldsal,(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end)as newsal from emp;

分组函数

• 多行处理函数，分组后使用，若未分组，整张表默认为一组。多个输入，一个输出
  count sum avg max min
  count (具体字段)：该字段下所有不为 NULL 的元素的总数
  count ()：表中总行数
• 分组函数自动忽略NULL
• 分组函数不能在 where子句中使用

分组查询

• 每个工作岗位的工资和
  select job,sum(sal) from emp group by job;
• select中，如果有group by的话，select后面只能跟：参加分组的字段，以及分组函数
• 每个部门的最高薪资
  select deptno,max(sal) from emp group by deptno;
• 每个部门，不同工作岗位的最高薪资
  select deptno,job,max(sal) from emp group by deptno,job; 两个字段联合分组
  
  
• 每个部门最高薪资，要求显示最高薪资大于3000的
  select deptno,max(sal) from emp group by deptno having max(sal)>3000;
• having对分组数据进一步过滤
  select deptno,max(sal) from emp where sal>3000 group by deptno;
• where和having，优先选择where，here完成不了，再选择having
• 找出每个部门平均薪资，要求显示平均薪资高于2500的
  select deptno,avg(sal) from emp group by deptno having avg(sal)>2500;

连接查询

内连接

1. 等值连接

• 查询每个员工所在部门名称，显示员工名和部门名

  select 
  	e.ename,d.dname
  from
  	emp e
  inner join // inner可以省略
  	dept d
  on
  	e.deptno = d.deptno; 
  

2. 非等值连接

• 查询每个员工的薪资等级，显示员工名、薪资、薪资等级

  select 
  	e.ename, e.sal, s.grade
  from
  	emp e
  join
  	salgrade s
  on
  	e.sal between s.losal and s.hisal; 
  

3. 自连接

• 查询员工的上级领导，要求显示员工名和对应的领导名

  select 
  	a.ename as '员工名', b.ename as '领导名'
  from
  	emp a
  join
  	emp b
  on
  	a.mgr = b.empno; 
  

外连接

1. 左外连接

• 将 join 关键字左边的这张表看成主表，主表数据会全部查询出来，产生了主次关系。

  select 
  	e.ename,d.dname
  from
  	dept d 
  left outer join // outer可省略
  	emp e
  on
  	e.deptno = d.deptno;
  

2. 右外连接

   select 
   	e.ename,d.dname
   from
   	emp e 
   right join 
   	dept d
   on
   	e.deptno = d.deptno;
   

3. 查询每个员工的上级领导，要求显示所有员工的名字和领导名

   select 
   	a.ename as '员工名', b.ename as '领导名'
   from
   	emp a
   left join
   	emp b
   on
   	a.mgr = b.empno; 
   

多表连接

• 找出每个员工的部门名称以及工资等级，还有上级领导，要求显示员工名、领导名、部门名、薪资、薪资等级

  select 
  	e.ename,e.sal,d.dname,s.grade,l.ename
  from
  	emp e
  join
  	dept d
  on 
  	e.deptno = d.deptno
  join
  	salgrade s
  on
  	e.sal between s.losal and s.hisal
  left join
  	emp l
  on
  	e.mgr = l.empno;
  

子查询

where

• 找出比最低工资高的员工姓名和工资

  select 
  	ename,sal 
  from 
  	emp 
  where 
  	sal > (select min(sal) from emp);
  

from

• 找出每个岗位的平均工资的薪资等级

  select 
  	t.*, s.grade
  from
  	(select job,avg(sal) as avgsal from emp group by job) t
  join
  	salgrade s
  on
  	t.avgsal between s.losal and s.hisal;
  

union 合并查询结果集

• 查询工作岗位是 MANAGER 和 SALESMAN 的员工

  select ename,job from emp where job = 'MANAGER'
  union
  select ename,sal from emp where job = 'SALESMAN';
  

• union 相比表连接，效率要高

• union 在进行结果集合并的时候，要求两个结果集的列数相同。%% MYSQL 可以，oracle 语法严格，不可以，报错。要求：结果集合并时列和列的数据类型也要一致。 %%

limit 分页

• limit startIndex,ength

• startIndex 是起始下标，length 是长度，起始下标从 0 开始。

• 缺省用法：limit 5; 取前 5.

  select 
  	ename,sal
  from
  	emp
  order by 
  	sal desc
  limit 5; 
  

• 取出工资排名是第3-5名的员工

  select 
  	ename,sal
  from
  	emp
  order by
  	sal desc
  limit
  	2, 3;
  

• 分页 limit (pageNo - 1) * pageSize, pageSize

执行顺序

• from where group by having select order by limit

  select
  	...
  from
  	...
  where
  	...
  group by
  	...
  having
  	...
  order by
  	...
  limit
      ...
  

• 每个岗位的平均薪资，要求显示平均薪资大于 1500 的，除 MANAGER 岗位之外，要求按照平均薪资降序排

  select 
  	job,avg(sal) as avgsal 
  from 
  	emp 
  where 
  	job <> 'manager' 
  group by 
  	job 
  having 
  	avg(sal)>1500 
  order by 
  	avgsal desc;