Oracle---笔记(一)---SQL查询实例

 查询emp表的所有字段的数据
select *  from emp;

--  查询emp表的部分字段：员工号，姓名，职位，工资
select EMPNO,ENAME,JOB,SAL from EMP 

--  查询每个员工的工号，姓名，年薪
select EMPNO,ENAME,SAL*12 年薪   from emp;total salalry

-- 别名中有特殊字符或空格时，需要用双引号包裹起来
select EMPNO,ENAME,SAL*12 "total salalry"   from emp;

 --  输出"xxx的职位是xxx"，用||来连接字符串，oracle中字符串要用单引号包裹
 select ename||'的职位是'||JOB  from emp;

 --  使用distinct去除重复行，即当字段在多个行中的值重复时，就只保留一行
 select  DISTINCT DEPTNO from emp;

--  查询所有员工的职位，去除重复的数据
  select DISTINCT  job from emp;
 
 --  查询所有员工的信息，按照工资降序显示,desc不能省略
 select *  from emp   ORDER BY   sal  DESC

  --  查询所有员工的信息，按照工资升序显示,asc可以省略不写
 select *  from emp   ORDER BY   sal

 --   查询所有员工的信息，先按部门号升序，再按工资降序显示
 select * from emp  ORDER BY   DEPTNO,sal  desc;

 -- 查询姓名为SMITH的员工信息
 select *  from emp  where  ename='SMITH';
 select *  from emp where  HIREDATE='17-12月-80';

 -- 查询不在20号部门的员工的信息
 select *  from emp  where deptno<>20;

 --  查询名字为BLAKE,KING，SMITH三个人的信息
 select *  from emp  where ename in('BLAKE','KING'，'SMITH')
 select *  from emp  where ename ='BLAKE' or   ename='KING' or  ename='SMITH'

 --  查询工资在1600到2975之间的员工信息
 select *  from emp  where sal BETWEEN 1600 and 2975  ORDER BY  sal;
 select *  from emp where sal>=1600 and sal<=2975  ORDER BY  sal;
 
 --  查询名字以S开头的所有员工
 select *  from emp where ename  like  'S%'

 --  查询名字的第2个字母是L的所有员工
 select * from emp where ename like '_L%'

 --  查询名字中包含_的人的信息，对于和特殊字符冲突的字符需要在前面加/,再加 ESCAPE '/'

 --  表示：/后面的字符被当成普通字符来解析
 select *  from  emp   where ename like '%/_%'   ESCAPE '/';
 
  --  查询奖金为空的员工信息
select *  from emp  where COMM is null 

 --  查询奖金不为空的员工信息
select *  from emp  where COMM is not null;
 
 --  查询名字不以S开头的员工信息
 select * from emp where ename not like 'S%'


~~此处是分割线~~ 

 单行函数
 --1.INITCAP(ch)：将字符串的首字母转换为大写
 select INITCAP('hello') from  dual
   
   --  查询所有员工的姓名，工资，职位，并且姓名首字母都是大写
	 select  INITCAP(ename),sal,job  from  emp 
	
-- 2.lower(ch):将字符串全转换为小写 ,upper(ch)：将字符串全转换为大写
	 select lower(ename),sal,job from emp;
 
 --3.rtrim(ch,ch1):从右边删除字符串ch中的一部分子字符串ch1
 select RTRIM('helloWuhan','han') from  dual;

 -- 4.replace(c,ch1,ch2):从原始字符串中找到字符串ch1,用ch2代替ch1
 select REPLACE('helloWuhan', 'Wuhan','changsha') from dual

 -- 5.instr(c,ch1):从原始字符串c中查找子串ch1的位置(从1开始)
 select instr('helloWuhan','Wuhan') from dual;
 
 --6.substr(c,start,[length]):截取原始字符串c，从start位置开始截取，截取length个字符，返回截取后的子串,length参数是可选的，如果没有指定length表示从start开始一直截取到最后
 select substr('helloWuhan',5) from dual;

 -- 7.CONCAT(ch1, ch2):连接2个字符串ch1和ch2，和||的功能相似
 select concat('hello','Wuhan') from dual;

 --  要连接hello,你好，wuhan三个字符串
 select 'hello'||'你好'||'wuhan' from dual;
  select concat( concat('hello','你好'),'wuhan' ) from dual;

 -- 8.floor(n):返回小于等于参数n的最大整数
 select floor(-9.9) from dual;
 
 -- 9.round(m,n):四舍五入，对参数m保留n位小数
 select round(100.256,2) from dual;
 
 -- 10.trunc(m,n):截断函数，对参数m保留n位小数，其余小数不要了
 select trunc(100.2599999,2) from dual;

 -- 11.MONTHS_BETWEEN(date1, date2):返回日期date1和date2之间月份的差值，用date1-date2

 --  查询2018-8-10到今天的月份的差值
 select MONTHS_BETWEEN('10-8月-18', '10-1月-19') from  dual;

 -- 12.ADD_MONTHS(date,n)：在日期date基础上添加n个月，返回添加后的新日期
 select ADD_MONTHS('10-8月-18', -2) from dual;

 -- 13.NEXT_DAY(date, ch):返回指定日期date后的下一个星期几对应的新日期,ch表示"星期x"
 select NEXT_DAY('21-1月-19', '星期二') from dual;
 
 --  14.LAST_DAY(date):返回指定日期date所在月的最后一天
 select LAST_DAY('21-1月-19') from dual;
 

 --  15.EXTRACT(time_unit FROM date):返回指定日期date中的日期字段
 time_unit(MONTH或YEAR,DAY)的值
 
  -- 查询当前日期中的月份的值:用sysdate获取当前日期
	-- select SYSDATE   from dual;
 select EXTRACT(DAY FROM  SYSDATE) from dual;

 --  查询员工表中入职日期为1981年的员工信息
 select * from emp  where EXTRACT(YEAR from HIREDATE)=1981

  --  查询2月份入职的员工
	select *  from emp where EXTRACT(MONTH from HIREDATE)=2
	
--  Oracle能自动进行数据类型转换，如果是+连接：将字符串转换为数字 ||连接：将数字转换为字符串
	select '12.5'||30 from dual;

-- to_char(num,format):使用指定格式format将数字转换为字符串	
	select TO_CHAR(14.560,'0099.990') from dual;
		select TO_CHAR(14.560) from dual;
	-- to_char(date,format):使用指定格式format将日期转换为字符串
	
--  将当前日期转换为字符串
	select TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')  from dual
select TO_CHAR(TO_DATE('20-1月-19'),'YYYY-MM-DD') from  dual

--   查询员工的姓名，入职日期，入职日期都转换为YYYY/MM/DD格式
select ename, to_char(HIREDATE,'YYYY/MM/DD')  from emp;

--  向emp表中插入一条数据,使用to_date()将字符串转换为日期插入到HIREDAT字段上
--  使用to_date()函数时，如果省略第2个参数，则字符串一定要写成默认的日期格式，例如：
--  TO_DATE('12-10月-17')，如果指定了第2个参数，则可以按照格式去写字符串,
  --  如：TO_DATE('2008-10-12', 'YYYY-MM-DD')
insert into  emp(empno,ename,job,HIREDATE) values(8810,'Andy','CLERK',        
TO_DATE('2008-10-12', 'YYYY-MM-DD'));
insert into  emp(empno,ename,job,HIREDATE) values(8811,'Andy','CLERK',        
TO_DATE('12-10月-17'));
select *  from emp;

--  nvl(e1,e2):如果参数e1为null，则返回e2的值，否则就返回e1

--  查询员工的工号，姓名，总收入(工资+奖金)
select  empno ,ename, sal+nvl(comm,0) 总收入  from emp ;

--  查询员工的工号，姓名，全年年薪
select  empno ,ename, (sal+nvl(comm,0))*12 全年年薪  from emp ;
--  nvl2(e1,e2,e3):如果e1不为空，就返回e2,否则返回e3
select  empno ,ename, sal+nvl2(comm,comm,0) 总收入  from emp ;

 --  求20号部门的员工工资的总和
 select  SUM(sal) from emp where deptno=20

 --  求20号部门的员工工资的平均值，只保留2位数字
 select  round(AVG(sal),2) from emp where deptno=20

 --   求20号部门的员工工资的最大值
 select max(sal) from emp where deptno=20
 
  --   求20号部门的员工工资的最小值
 select MIN(sal) from emp where deptno=20
  
  --  求员工姓名的最大值，用max()求字符串的最大值，其实是得到字母顺序最大的那个值
	select max(ename)  from emp;
		select min(ename)  from emp;
		select *  from emp;
		
--  计算员工奖金的总和,平均值，最大值，最小值,此时会跳过comm为空的行(空行忽略),
		--  sum(),avg().max(),min()在计算时，都会跳过字段为null的行，只有count(*)不会跳过空行
		select  sum(comm),avg(comm),max(comm),min(comm)  from emp;
		select count(*) from  emp;
		
-- 统计领取了奖金的员工的数量
		select count(comm) from emp;
		
--  查询员工的不同职位且职位不重复的总数量	
	select count(distinct job)  from emp;