Oracle学习（一）

select userenv('language') from dual;--AMERICAN_AMERICA.ZHS16GBK--查看Oracle当前编码
select * from V$NLS_PARAMETERS--AMERICAN

--===========解锁scott用户并重新设置密码

alter user scott account unlock;
alter user scott identified by tiger;

--===========基本查询

--1.查询出所有emp中的信息，并用中文进行字段重命名
select empno as "员工编号",ename as "姓名",job as "工作",mgr as "上级编号",hiredate as "受雇日期",sal as "工资",comm as "奖金",deptno as "部门编号" from emp;

--2.查询emp表中员工的job信息，并去除重复信息
select distinct(job) from emp;

--3.查询emp表中员工的全年的工资总和(sal总和)
select 12*sal from emp;

--4.查询emp表中员工的全年收入总和(sal+comm的总和)
select ename,12*sal+nvl(comm,0) from emp;

--5.查询emp表中员工编号，姓名
--输出格式如下：编号:xxx，姓名：xxx
----Concat拼接方式
select concat(concat('编号：',empno),concat('，姓名：',ename)) from emp;
----Oracle的||方式
select '编号'||empno||'，姓名'||ename from emp;

--=============================================条件查询

--1.查询工资大于1500的员工
select * from emp where sal>=1500;

--2.查询工资大于1500并且有奖金的雇员
select * from emp where sal>=1500 and comm is not null;

--3.查询工资大于1500或者有奖金的雇员
select * from emp where sal>=1500 or comm is not null;

--4.查询工资大于1500并且没有奖金的雇员
select * from emp where sal>=1500 and comm is null;

--5.查询员工姓名为smith的员工
select * from emp where ename='SMITH';

--=============================================范围查询

--1.查询工资大于1500但小于3000的全部雇员
---->=,<=方式
select * from emp where sal>=1500 and sal<=3000;

----between and方式
select * from emp where sal between 1500 and 3000;

--2.查询1981-1-1到1981-12-31号入职的雇员（between and）
select * from emp where hiredate between to_date('1981-1-1','yyyy-MM-dd') and to_date('1981-12-31','yyyy-MM-dd');
select * from emp where to_char(hiredate,'yyyy-mm-dd') between '1981-1-1' and '1981-12-31';--这样写是错的，因为这样写的话是把hiredate改成了字符串，按理说应该是把时间改为date

--3.查询员工编号是7369,7654,7566的员工
----OR方式
select * from emp where empno=7369 or empno=7654 or empno=7566;

----IN方式
select * from emp where empno in (7369,7654,7566)

--4.查询雇员姓名是'SMITH','ALLEN','WARD'的雇员信息
----IN方式
select * from emp where ename in ('SMITH','ALLEN','WARD');

--=============================================模糊查询like

--1.查询所有雇员姓名中第二个字符有‘M’的雇员
select * from emp where ename like '_M%';

--2.查询名字中带有‘M’的雇员
select * from emp where ename like '%M%';

--3.查询雇员编号不是7369的雇员信息
----<>方式
select * from emp where empno<>7369;

----!=方式
select * from emp where empno!=7369;

--=============================================排序 order by

--1.查询雇员的工资进行降序排序
select ename,sal from emp order by sal desc;

--2.查询雇员的奖金并做降序排序（关于nulls first/nulls last）
select comm from emp order by comm desc nulls last;--nulls first 把有数据的显示在最下面，nulls last 把有数据的显示在最前面

--3.查询雇员的工资做降序排列并且其中奖金部分是升序排序
select ename,sal,comm from emp order by sal desc,comm asc; 

--===========单行函数

/*
伪表，虚表：dual  没有任何的实际意义，只是为了补全Oracle查询语法
*/
--字符函数
--1.将'smith'转换成大写--关键字:upper
select upper('smith') from dual;

--2.将'SMITH'转换成小写--关键字:lower
select lower('SMITH') from dual;

--3.将'smith'首字母大写--关键字:initcap
select initcap('smith') from dual;

--4.将'helloworld'截取字符串成'hello'--关键字substr
select substr('helloword',1,5) from dual;

--5.获取'hello'的字符串长度--关键字length
select length('hello') from dual;

--6.将'hello'中的l用x进行替换--关键字replace
select replace('hello','l','x') from emp;

--数值函数
--1.将15.66进行四舍五入（从-2到2）--关键字round
select round(15.66,-2) from dual;
select round(15.66,-1) from dual;
select round(15.66,-0) from dual;
select round(15.66,1) from dual;
select round(15.66,2) from dual;

--2.将15.66进行截断（从-2到2）--关键字trunc
select trunc(15.66,-2) from dual;--0
select trunc(15.66,-1) from dual;--10
select trunc(15.66,-0) from dual;--15
select trunc(15.66,1) from dual;--15.6
select trunc(15.66,2) from dual;--15.66

--3.对15/3进行求余数--关键字mod
select mod(15,3) from dual;

--日期函数
--1.查询系统时间--关键字sysdate
select sysdate from dual;

--2.查询雇员进入公司的周数
select ename,(sysdate-hiredate)/7 from emp;

--3.查询雇员进入公司的月数--关键字months_between
select ename,months_between(sysdate,hiredate) from emp;

--4.求出三个月后的日期--关键字add_months
select ename,hiredate,add_months(hiredate,3) from emp;

--转换函数
--1.将系统日期显示为yyyy-mm-dd hh:mi:ss(去掉补零和24小时显示时间)--关键字to_char
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

----显示成年月日
select to_char(sysdate,'yyyy')||'年'||to_char(sysdate,'mm')||'月'||to_char(sysdate,'dd')||'日' from dual;

--2.将字符串'1981-1-1'转换成日期类型--关键字to_date
select to_date('1981-1-1','yyyy-mm-dd') from dual;

select to_number('99') from dual;
select to_char('99') from dual;
--通用函数
--1.空值的处理函数
select nvl(comm,0) from emp;
--2.nvl2(判断值,空返回值,非空返回值) 
select nvl2(null,'1','2') from dual;--第一个值为空的时候返回第三个值2
select nvl2('test','1','2') from dual;--第一个值为空的时候返回第三个值1

--条件表达式
--1.查询员工的job内容并转成中文显示
----decode方式
select ename,decode(job,'CLERK','柜员','SALESMAN','销售','其他') from emp;--如果不写其他的话，会出现空值

----case when then end方式
select ename,case job when 'CLERK' then '柜员'
when 'SALESMAN' then '销售'
     else '其他'
       end
from emp;

--===========多行函数

--1.查询所有员工记录数--关键字count
select count(*) from emp;

--2.查询佣金的总数--（如何查询某个字段的总数量）
select sum(comm) from emp;

--3.查询最低工资--关键字min
select min(sal) from emp;

--4.查询最高工资--关键字max
select max(sal) from emp;

--5.查询平均工资--关键字avg
select avg(sal) from emp;

--6.查询20号部门的员工工资总和
select sum(sal) from emp where deptno=20;

--======================================分组函数

--1.查询部门编号及人数--分组查询关键字group by
select deptno,count(*) from emp group by deptno;

--2.查询每个部门编号及平均工资
select deptno,avg(sal) from emp group by deptno;

--3.查询部门名称，部门编号，平均工资
select dname,emp.deptno,avg(sal) from dept,emp where dept.deptno=emp.deptno group by emp.deptno,dname

--4.查询出部门人数大于5人的部门
select deptno,count(*) from emp group by deptno having count(*)>5;--必须用having才行，如果用where不行【HAVING子句是SELECT语句的可选子句。它用于过滤由GROUP BY子句返回的行分组。 这就是为什么HAVING子句通常与GROUP BY子句一起使用的原因。】

--5.查询部门编号，部门名称，平均工资且平均工资大于2000
select emp.deptno,dept.dname,avg(sal) from emp,dept where emp.deptno=dept.deptno group by emp.deptno,dname having avg(sal)>2000;