SQL 语句结构，select语句及练习

select:查询，从表中查询符合条件的数据 select 列

顺序，格式

from 表

where 条件

group by 列

having

order by 列

查询表中所有的列，用*

select * from emp 查询表中具体的列，需要写具体的列名，多列之间逗号分割，最后一列没有逗号，如果列名不对，则会报标识符无效，通过右键表名--描述来查看表的具体的字段名 select empno,ename,sal,comm,DEPTNO from emp

对列进行算术运算，+-*/ select empno,ename,sal,comm,DEPTNO,sal*12 as sal_year from emp 对列起别名，列名或表达式 [as] 别名 对表中的数据进行过滤，通过where子句 where后是一个逻辑表达式，返回Ture或False，最终将返回True的数据显示出来，列 运算符 值 where后如果有多个条件，用and或or将其连接起来

单值运算符：=、>、>=、<、<=、!=、<> 多值运算符：in 例子：comm in(‘a‘,‘b‘) //in 里面只能是具体的值不能是表达式 模糊匹配：like 通常与通配符一起使用：_任意一个字符，%任意个字符 列名 like ‘通配符‘ comm like‘&a&‘

between and : 列 between a and b 等价于 列>=a and 列<=b null：列 is null 将该列为null的记录显示出来，列 is not null 将该列不为null的值显示出来 null值是一个未知的值，与任何一个值的比较运算结果都为false，对null进行比较运算只能用is null和is not null；null与任何一个数的算术运算结果都为null

sql语句不区分大小写，但是表中的数据区分大小写

case when表达式相当与if语句

• case when 条件 then 表达式 end

• case when 条件 then 表达式 else 表达式 end distinct对查询结果集去重，重复数据只显示一条 select distinct job,deptno from emp //对Job去重

聚集函数：

聚集函数通常与group by子句一起使用，先分组（同一组的多条数据合并一条）按照指定的算法进行合并

group by

select语句如果有group 子句，select后最多只能出现group by后面的列或者聚集函数或者常量 如果select后由列和聚集函数一起出现，则select后的列必须出现在group by的后面

• count() 统计对应的列的行数

• sum() 对应列求和

• avg() 对应列求平均

• max() 对应列求最大值

• min() 对应列求最小值 例子 select count(*),count(1),count(empno),count(comm) from emp count(*)与count(列)的区别：

• count(*)返回查询结果集的记录数，不忽略null值。

• count(列)返回该列不为null的记录数 avg(列)=sum(列)/count(列) ,如果该列有null值，avg的结果会偏高，因此需要优先处理null值

查询部门10的工资小于1000或大于2000和部门20的员工（职位为CLERK），并验证结果 select * from emp where ( deptno = 10 and (sal < 1000 or sal > 2000) ) or (deptno = 20 and job = ‘CLERK‘)

题目

--1、选择在部门30中员工的所有信息 select * from emp where deptno=30;

--2、列出职位为（MANAGER）的员工的编号，姓名? select empno,ename from emp where job=‘MANAGER‘;

--3、找出奖金高于工资的员工 select * from emp where comm>sal;

--4、找出每个员工奖金和工资的总和 select ename,sal+case when comm is null then 0 else comm end 奖金工资总和 from emp;

--5、找出部门10中的经理(MANAGER)和部门20中的普通员工(CLERK) select * from emp where (deptno=10 and job=‘MANAGER‘) or (deptno=20 and job=‘CLERK‘); select * from emp where deptno in(‘10‘) and job in(‘MANAGER‘);

--6、找出部门10中既不是经理也不是普通员工，而且工资大于等于2000的员工 select * from emp where deptno=10 and job not in(‘MANAGER‘,‘CLERK‘) and sal>=2000;

--7、找出有奖金的员工的不同工作 select distinct(job) from emp T where comm is not null;

--8、找出没有奖金或者奖金低于500的员工 select * from emp where case when comm is null then 0 else comm end<=500;

--9、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面 select ename,hiredate from emp order by hiredate asc;

--10、查询出雇员表中部门号为10到30的所有员工姓名，工作。雇员信息，要求按照雇用日期的降序进行排序 select ename,job,hiredate,deptno from emp where deptno>=10 and deptno<=30 order by hiredate desc;

--11、查询出姓名中第三个字母为“A”的所有员工的姓名 select * from emp where ename like‘__A%‘;

--12、查出佣金比工资多10%的员工的姓名 select * from emp where comm>=sal*1.1;

--18、算出部门30中得到最多奖金的员工奖金 select max(comm) from emp where deptno=30;

--20、算出每个职位的员工数和最低工资 select job,count(*),min(sal) from emp group by job;

--21、列出员工表中每个部门的员工数，和部门 select deptno,count(*) from emp group by deptno;

--22、算出每个部门的平均奖金（包含没有奖金的） select deptno,avg(case when comm is null then 0 else comm end) from emp group by deptno;

-- 工资大于ALLEN的员工 select * from emp where sal>(select sal from emp where ename=‘ALLEN‘); select deptno, count(), avg(sal) from emp where sal > (select sal from emp where ename = ‘ALLEN‘) group by deptno order by count() desc; select deptno, count(*) from emp where sal > (select avg(sal) from emp) group by deptno;

--求出平均工资大于2000的部门号和平均工资 select deptno,avg(sal) from emp group by deptno--1.先求出每个部门的平均工资相对于一个虚拟的表 select * from (select deptno, avg(sal) avg_sal from emp group by deptno) where avg_sal > 2000 --2.这里的avg(sal)需要取别名

-- 查询职位与SCOTT和ALLEN一样的员工信息，包含员工号，姓名，职位，工资 select job from emp where ename in(‘SCOTT‘,‘ALLEN‘); select empno, ename, job, sal from emp where job = (select job from emp where ename = ‘SCOTT‘) or job = (select job from emp where ename = ‘ALLEN‘); -- 方法2 select empno, ename, job, sal from emp where job in（(select job from emp where ename = ‘SCOTT‘)，(select job from emp where ename = ‘ALLEN‘)）;

--两表关联emp表和dept表 select * from emp t1,dept t2 where t1.列1=t2.列1 // 列1 在2个表中都存在

select t1.ename,t1.job,t2.dname from emp t1,dept t2 where t1.deptno=t2.deptno

--13、查找出职位和‘MARTIN‘?或者‘SMITH‘一样的员工的平均工资 select avg(sal) from emp where job in(select job from emp where ename in(‘MARTIN‘,‘SMITH‘));

--14、找出工资比jones多的员工 select ename from emp where sal>(select sal from emp where ename=‘JONES‘)

--15、查找出不在部门20，且比部门20中任何一个人工资都高的员工姓名、部门名称 SELECT ENAME,DEPTNO FROM EMP WHERE DEPTNO!=20 AND SAL>(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=20 )

--16、分组统计各部门下工资>500的员工的平均工资 SELECT DEPTNO,AVG(SAL) FROM EMP WHERE SAL>500 GROUP BY DEPTNO

--17、统计各部门下平均工资大于500的部门 select deptno from (select deptno,avg(sal) avg_sal from emp group by deptno) where avg_sal>500;

--方法2 having是一个过滤声明，是在查询返回结果集后，在对结果进行过滤 select deptno,avg(sal) from emp group by deptno having avg(sal)>500

--20、算出每个职位的员工数和最低工资 select job, count(*), min(sal) from emp group by job;

--23、分组统计每个部门下，每种职位的平均奖金（也要算没奖金的人）和总工资(包括奖金) select deptno, job, avg(case when comm is null then 0 else comm end) 平均奖金, sum(sal + case when comm is null then 0 else comm end) from emp group by deptno, job

order by deptno;

select comm,deptno,job from emp order by deptno desc;

--24、列出员工表中每个部门的员工数，和部门 select deptno,count(*) from emp group by deptno

--25、列出员工表中每个部门的员工数（员工数必须大于3），和部门名称 select * from (select t1.deptno, t2.dname, count() c from emp t1, dept t2 where t1.deptno = t2.deptno group by t1.deptno, t2.dname) where c > 3 -- having 方法 select t1.deptno,t2.dname,count() from emp t1,dept t2 where t1.deptno=t2.deptno group by t1.deptno,t2.dname having count(*)>3

--26、得到平均工资大于2000的工作职种 select job from (select job,avg(sal) avg_sal from emp group by job) where avg_sal>2000

--27、分部门得到工资大于2000的所有员工的平均工资，并且平均工资还要大于2500 select deptno, avg(sal) from (select * from emp where sal > 2000 order by deptno asc) group by deptno having avg(sal)>2000

--28、得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置 select t1.deptno,t2.dname,t2.loc from (select deptno,sum(sal) sum_sal from emp group by deptno) t1,dept t2 where t1.deptno=t2.deptno and sum_sal=(select min(sum(sal)) from emp group by deptno)

--29、查询出king所在部门的部门号\部门名称\部门人数 select t1.deptno,t2.dname,count(*) from emp t1,dept t2 where t1.deptno=t2.deptno and t1.deptno=(select deptno from emp where ename=‘KING‘) group by t1.deptno,t2.dname

SQL 语句结构，select语句及练习

标签：dep   最小   amp   sof   人工   als   标识符   比较   manager