Oracle数据库查询语句练习

select userenv('language') from dual;--AMERICAN_AMERICA.ZHS16GBK
select * from V$NLS_PARAMETERS--AMERICAN
--===========解锁scott用户并重新设置密码
alter user scott account unlock;
alter user scott identified by tiger;

--===========基本查询
--1.查询出所有emp中的信息，并用中文进行字段重命名
select empno as "员工编号",ename "员工姓名",job 职位,mgr "领导编号",hiredate "入职日期",sal "工资",comm "奖金",deptno "部门编号" from emp;

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

--3.查询emp表中员工的全年的工资总和(sal总和)
select ename,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')

--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 ename,comm from emp order by comm desc 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(ename) from emp;

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

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

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

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

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

--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,'yyyyfm-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('xxxxxx','1','2') from dual;

--条件表达式
--1.查询员工的job内容并转成中文显示
----decode方式
select ename,decode(job,'CLERK','柜员','SALESMAN','销售','MANAGER','管理','其他') from emp;
----case when then end方式
select ename, case job when 'CLERK'  then  '柜员' 
                       when 'SALESMAN'  then  '销售'
                       when 'MANAGER'  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

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