课堂练习02

-- 1.条件查询 
-- is null 和 is not null
-- 查询时,如果查询的值为null 需要使用is null 
-- 不能使用=null,不为null时同理,
-- 查询没有上级领导的员工信息
select * from emp where mgr is null;
-- 查询有上级领导的员姓名和领导编号
select ename,mgr from emp where mgr is not null

-- 2.distinct 去重
-- 查询员表中出现了几种不同的工作.
select distinct job from emp;
 
--  查询原员工表中出现的部门编号(去掉重复)
select distinct deptno from emp;
--3. 比较运算符 > < >= <= =    != <> 他俩都是不等于
-- 查询工资小于3000的员工
select ename,sal from emp where sal<3000;
-- 查询1号部门的员姓名工作
select distinct ename,job from emp where deptno=1;
--  查询不是程序员的员姓名,工资和 工作的(两种方法)
select ename,sal,job from emp where job!='程序员';
-- 查询有奖金的员工姓名和奖金
select ename,comm from emp where comm>0;
-- 4.and和or 
-- 查询数据时多个条件同时满足使用and,只需要满足一个时使用or ,
-- 查询1号部门工资小于3000的员工信息 
select * from emp where deptno=1 and sal<3000;

-- 查询所有领导的员中工资小于2000的员工姓名 ,工资和领导编号 
select ename,sal,mgr from emp where sal<2000 and mgr is not null;

-- 查询2号部门的员 和工作是程序员的员工信息
select * from emp where DEPTNO=2 or JOB='程序员';

-- 查询工资为800和3000的员工信息
select * from emp where sal=800 or sal=3000;

-- 5.in 关键字
-- 当查询某个字段的值为多个值时使用
-- 查询 工资为 800 3000 1500 的员信息,
select * from emp where  sal in(800,3000,1500);
-- 查询工作是销售，人事和程序员的员工信息
select * from emp where  job in('销售','人事','程序员');

-- 6.between x and y 两者之间,包含x y
-- 查询工资在1000到2000之间 
select * from emp where sal>=1000 and sal<=2000;
select * from emp where  sal between 1000 and 2000;
-- 1. 查询3号部门中有上级领导的员工信息
select * from emp where deptno=3 and mgr is not null;
-- 2. 查询2号部门工资在2000-3000之间的员工姓名,工资和部门编号
select ename,sal,deptno from emp where deptno=2 and sal>=2000 and sal<=3000;
-- 3. 查询1号部门工资为800和1600的员工信息
select * from emp where deptno=1 and sal in(800,1600);
-- 4. 查询1号和2号部门中工资高于2000的员工信息
select * from emp where deptno in(1,2) and sal>2000; 

-- 7.ifnull(列名,值)
-- 判断指定的列是否包含null值,如果有null值 用第二个值替换null值;
-- 查询所有员工的姓名和工资+奖金的的和
select ename,sal+ifnull(comm,0) from emp; 

-- oracle中使用nvl() / nvl2() 
-- nvl(列名 值) 如果指定的列值为null,使用第二个值替换
-- nvl2(列名 值1,值2) 如果指定的列值为null则返回1,如果不为null 则返回2 
select nvl(mgr,'空'),nvl2(comm,0,comm) from emp;

-- 8. like 模糊查询 
-- _代表1个未知字符,%代表0或多个未知字符
-- 以x开头 x% 以x结尾 : %x; 包含x: %x% 
-- 以x开头y结尾 x%y
-- 以第二个字符是x: _x%;
-- 第二个是x倒数第三个是y:_x%y__
-- 查询姓孙的员工姓名
select ename from emp where ename like '孙%';

-- 查询工作中第二个字是售的员工信息
select * from emp where job like '_售%';
-- 查询名字 中以精结尾的
select ename from emp where ename like '%精';
-- 查询名字包含僧并且工资高于2000的员信息 
select * from emp where ename like '%僧%' and sal>2000;

-- 9.order by 排序
-- 格式 :order by 字段/数字 asc/desc
-- asc 表示升序 desc表示降序,默认是asc
-- 当order by后接字段名时表示按照指定字段进行排序
-- 接数字时order by后接字段名时表示按照指定字段进行排序
-- 接数字时（order by n，表示select后的第n个字段进行排序。
-- 查询每个员工的姓名和工资，按照工资升序排序
select ename,sal from emp order by sal asc; 
select ename,sal from emp order by sal ; 
select ename,sal from emp order by 2 ; 
-- 查询3号部门的员信息 按照工资降序排序
select * from emp where deptno=3 order by sal desc;
-- 查询每个员工的姓名，工资 编号 按照部门编号升序 
-- 如果部门标号一致 则按照工资降序排序 
select ename,sal,deptno from emp order by deptno,sal desc; 

-- 10.limit 分页查询 
-- 格式 :limit 跳过的条数,显示的条数
--  计算公式 : limit (请求页数-1)*条数,线束的条数当跳过条数为0时，可以省略不写.
-- 查询第一页的10条数据 limit 0,10,(LIMIT 10)
-- 查询第3页的8条数据 limit 16,8
-- 查询第2页的10条数据 limit 10,10
-- 查询第8页的5条数据 limit 35,5
-- 查询员工表中工资升序排序后第一页的5条数据
select * from emp order by sal limit 5;
-- 查询员工表中工资降序后第二页的3条数据
select * from emp order by sal desc limit 3,3; 
-- 查询工资高于1000的员信息,工资升序,查询前三条数据,
select * from emp where sal>1000 order by sal limit 3;

-- 1. 查询有领导并且工资在1000到3000之间的员工信息
select * from emp where mgr is not null and sal between 1000 and 3000;

-- 2. 查询1号部门有几种不同的工作
select distinct job from emp where  deptno=1;

-- 3. 查询名字中包含精或者工作中包含序的员工姓名和工作
select ename,job from emp where ename like'%精%' or job like '%序%';
-- 4. 查询3号部门工资最高的员工姓名和工资
select ename,sal from emp where depyno=3  order by sal desc limit 0,1;
-- 5. 查询上级领导编号是8工资最高的3个人的信息
select * from emp where mgr=8 order by sal desc limit 3;  
-- 6. 查询1号部门和2号部门的员工信息工资升序，查询第3页的2条数据
select * from emp where deptno in(1,2)
order by sal limit 4,2;

-- 11.别名
select ename from emp;
select ename as '姓名' from emp;
select ename '姓名' from emp;
select ename 姓名 from emp;

-- 12.数值计算 + - * / %
-- 对查询的某个字段的数据可以直接进行计算
-- 查询某个字段的数据可以直接进行计算
-- 查询每个员工的姓名,工资和年终奖,(年终奖=5个月工资)
select ename,sal*5 年终奖 from emp;
--给1号部门的员工每人涨50块
update emp set sal=sal+50 where deptno=1;
select * from emp;

-- 13 聚合函数
-- SQL基本函数,可以对一组的值执行计算,并返回单个值,
-- 使用聚合函数可以对查询的多条数进行统计查询
-- 统计方式,1.平均值 2.最大值 3.最小值 4.求和 5.计数
-- (1) avg(字段名)平均值
-- 查询每个员工的平均工资 
select avg(sal) from emp;
-- 查询2号部门的平均工资 select avg(sal) from emp where deptno=2;
-- (2)max(字段名) 最大值
-- 查询1号部门的最高工资
select max(sal) from emp where deptno=1;
-- 查询员工表中的最高奖金
select max(comm) from emp;
-- (3)min(字段名) 最小值 
-- 查询1号部门的最低工资
select min(sal) from emp where deptno=1;
-- 查询1号部门最早入职日期
select min(HIREdate) from emp where deptno=1;
-- (4)sum(字段名) 求和
-- 查询2号部门的工资总和
select sum(sal) from emp where deptno=2;
-- (5)count(*)计数
-- 查询二号部门的人数 
select count(*) from emp where deptno=2;
-- 查询程序员的人数
select count(*) from emp where job='程序员';
-- 查询1号部门的平均工资,最高工资,最低工资,工资总和,人数并且起别名
select avg(sal) 平均工资,max(sal) 最高工资,min(sal) 最低工资,sum(sal) 工资总和,  count(*) 人数 from emp where deptno=1;
--  count(*) count(数字) count(字段名) 
-- 相同点 都是用来统计表的数据条数的
-- 不同点,count(*)和count(数字)统计出的结果是一样的,都不会忽略null值
-- 但是count(*)的效率没有count(数字)高,
-- count (字段名)在统计的时候,如果没有字段值为null,则不会入总数.

-- 1. 查询员工表中工资高于2000的员工姓名和工资,按照工资升序排序,查询第2页的两条数据
select ename,sal from emp where sal>2000 order by sal limit 2,2;

-- 2. 查询和销售相关工作的工资总和
select sum(sal) from emp where job='销售'; 

-- 3. 查询1号部门工资高于2000的员工人数
select count(*) from emp where deptno=1 and sal>2000; 
-- 4. 查询3号部门的人数和工资总和并起别名
select count(*) 人数,sum(sal) 工资总和 from emp where deptno=3;
-- 5. 查询1号部门中名字包含僧的员工的人数和平均工资 起别名
select count(*) 人数,avg(sal) 工资总和 from emp where ename like'%僧%' and deptno=1;