Oracle数据库

--创建一个scott用户，密码为tigger
--授予权限给scott；
--connect：用于可以登录scott，不能创建实体类
--resource：可以创建实体类
create user scott identified by tigger;
grant connect,resource to scott;
--创建一个hlt用户，密码为123456；
--system有权限创建
create user hlt identified by 123456;
grant connect,resource to hlt;
--查询emp表中的所有数据
select employee_id,first_name,last_name,email,phone_number,hire_date,salary,commission_pct,manager_id,department_id from employees;
select * from employees;
--查询单列内容:select 列名1，列名2，列名3 from 表名
select first_name,last_name from employees;
select employee_id,hire_date,salary from employees;
--添加别名
--将英文转化为中文别名，直接在后边写别名即可，as可加可不加
select employee_id as 员工编号 from employees;
--将英文大写转换为英文小写别名,将别名写在双引号中；as可加可不加
select salary "salary" from employees;
--PHONE_NUMBER去掉下划线，变为小写；EMAIL变为中文
select phone_number "phonenumber",email "邮箱" from employees;
--连接符 ||
--连接两个id并且起一个别名为“员工工作编号”
select employee_id||job_id as "员工工作编号" from employees;
--连接工资与字符“工资”，使得每一列都有工资出现
select salary||'工资' from employees;
--连接姓、名列，并且起一个别名为“姓名”
select first_name||last_name as "姓名" from employees;
--oracle算术运算，没有取余%
--  + - * / 
--员工工资加1000
select salary,salary+1000 from employees;
select salary,salary-1000 from employees;
select salary,salary*0.1 from employees;
select salary,salary/1000 from employees;
--求所有员工总工资（工资+奖金比率）
select salary,commission_pct,salary+salary*commission_pct as "总工资" from employees;
--null:空值，没有指定的，无效的
--和null进行算术运算，结果扔为null

--关键字：distinct  删除重复值
select distinct manager_id from employees;
--------------过滤查询------------------------
--关键字  where
--查询员工编号是AD_VAR的员工信息
select * from employees where job_id='AD_VP';
--查询1990年1月3号入职的员工信息
--日期格式默认为‘DD-MON-RR’(日、月、年)1990/3/1   1-3月-1990
select * from employees where Hire_date='3-1月-1990';
--查询部门编号是60的员工信息
select * from employees where department_id=60;
--查询入职日期是1989-9-27的员工信息
select * from employees where hire_date='21-9月-1989';
--查询first_name是steven的员工信息
select * from employees where first_name='Steven';
--查询员工工资>=10000的员工信息
select * from employees where salary>=10000;
--查询员工编号为100的园丁信息
select * from employees where employee_id=100;
--查询员工工资少于5000的姓名
select salary,first_name||last_name as 姓名 from employees where salary<5000;
--查询在1990年以后入职的员工信息
select employee_id,first_name,hire_date from employees where hire_date>'31-12月-1989';
--查询员工编号、姓名、工资、新工资（工资上调25%），要求工资大于10000；
--注意：过滤条件参与表达式运算，别名不可进行使用，一定要用原生列
select employee_id,first_name||last_name as 姓名,salary,salary+salary*0.25 as 新工资 from employees where salary+salary*0.25>10000;
-----------其他比较运算------------------------
--between   and:包含边界 a>=1000,a<=3000;
--查询员工工资在1600-3000的员工信息
select * from employees where salary between 1600 and 3000;
--(int)集：特定的集元素匹配
--查询员工部门为50,60的员工信息
select * from employees where department_id in(50,60);
--查询工资在1000-5000之间的员工姓名、工资、编号
select first_name||last_name as 姓名,salary,employee_id from employees where salary between 1000 and 5000;
--查询员工部门在50,60,90的部门编号，员工姓名
select department_id,first_name||last_name as 姓名 from employees where department_id in(50,60,90);
--查询工资小于5000或者大于1000的员工
select * from employees where salary<5000 or salary>10000;
select * from employees where salary not between 5000 and 10000;
--查询部门不在50、60的员工姓名和部门id
select first_name||last_name as 姓名,department_id from employees where department_id not in(50,60);
------------------空值--------------------
--空值：is null/is not null(空值参与运算结果都为空)
--查询奖金比率为空的员工信息
select * from employees where commission_pct is null;
--查询奖金比率bu为空的员工信息
select * from employees where commission_pct is not null;
------------------模糊查询- LIKE-----------------
--% :代表0个或多个字符
--——（下划线）:代表一个字符
--查询员工信息，其姓名first_name以s开头的所有员工
select * from employees where first_name like 'S%';
--查询first_name中包含teven，并且teven钱只有一个字符的员工信息
select * from employees where first_name like '_teven%';
--查找first_name中包含v的员工信息
select * from employees where first_name like '%v%';
--查询first_name中以y结尾，前面包含0个或多个字符的员工信息
select * from employees where first_name like '%y';
--查询first_name中倒数第三个字母是u的员工能够信息
select * from employees where first_name like '%u__';
--查询first_name中包含n、e的员工信息
select * from employees where first_name like '%n%e%' or first_name like '%e%n%';
--查找first_name中有%的员工信息
--关键字：ESCAPE转义字符
 select * from employees where first_name like '%\%%' escape '\';
-------------------------逻辑运算---------------------------
--(not:不是，非；and：和  or:或者)
--查询部门编号不属于10,20,90的员工信息
select * from employees where department_id not in(10,20,90);
--查询员工工资大于等于5800，小于等于8300的员工信息
select * from employees where salary>=5800 and salary<=8300;
--查询员工部门编号大于90或者小于60的员工信息
select * from employees where department_id>90 or department_id<60;
-----------------------排序Order by--------------------
--升序：asc（默认排序，可以不写，从小到大）
--降序：desc
--语句格式：select 列名等 from 表名 where 过滤条件 order by 列名 排序方式
--按工资降序排序查询所有员工信息
select * from employees order by salary desc;
--查询员工表中job_id为AD——VP员工，a按工资降序排序
select * from employees where job_id='AD_VP' order by salary desc;
--查询新工资（原工资上浮25%）大于10000的员工编号、姓名、工资、新工资；
select employee_id,first_name||last_name as 姓名,salary,salary*1.25 as new_salary from employees;
--查询新工资（原工资上浮25%）大于10000的员工编号、姓名、工资、新工资，按新工资升序排序
select employee_id,first_name||last_name as 姓名,salary,salary*1.25 as new_salary from employees order by new_salary asc;
--查询员工表中的信息，按照部门id升序排序，按照工资降序排序
select * from employees order by department_id asc,salary desc;
--查询员工编号、姓名、工资、入职日期、部门编号 按照部门升序、工资降序、入职日期升序排序
select employee_id,first_name||last_name as 姓名，salary,hire_date,department_id from employees order by department_id asc,salary desc,hire_date asc;
--查询工资在大于5000，小于10000的部门编号、工资，按照部门编号升序、工资降序排序
select department_id,salary from employees where salary between 5000 and 10000 order by department_id asc,salary desc;
----------------------函数-------------------------
--定义：实现某种功能的方法
--两种实现方式：单行函数、多行函数
--单行函数：对一行数据返回一个结果（一对一）
--多行函数：对多行数据返回一个结果（一对多）
---------------单行函数--------------------------------
--单行函数分类：字符函数、日期函数、数值函数、转换函数、通用函数
--1.1字符函数：大小写控制函数、字符控制函数
--大小写控制函数：lower,upper、initcap；
--lower(参数1)：将大写转化为小写
--upper(参数1)：将小写转换为大写
--转换first_name，将大写转化为小写
select first_name,lower(first_name) from employees;
----转换first_name，将小写转化为大写
select first_name,upper(first_name) from employees;
--initcap(一个参数)：将首字母转换成大写
--把当前first_name列首字母转换成大写
select first_name,initcap(First_name) "首字母" from employees;
--注意：首字母大写，其余字母小写；如果字段是AD_PRES,那么会把两端的首字母都大写
select job_id,initcap(job_id) "转化" from employees;
--查询字段job_id,将job_id字段变为小写字段
select job_id,lower(job_id) from employees;
--查询last_name字段，将last_name字段变为大写
select last_name,upper(last_name) from employees;
--查询email，将email首字母变为大写
select email,initcap(email) from employees;
----------------字符控制函数-------------------------
--concat(参数1，参数2) ：进行连接    ||
--姓名进行连接
select first_name||last_name "姓名" from employees;
select first_name,last_name,concat(first_name,last_name) "姓名" from employees;
--连接两个字符：hello word
--orcale提供了违表dual,别于测试学习
select concat('hello','world') from dual;
--substr(参数1、参数2、参数3)：截取字符串
--参数1：要截取的字符串
--参数2：从哪个索引位置开始截取
--参数三：截取几个字符
--注意，oralce索引都是从1开始的

--截取hello的前三个字符
select substr('hello',1,3) from dual;
--查询first_name,first_name前三个字符
select first_name,substr(first_name,1,3)from employees;
--length（参数1）:获取字符串长度
--查询first_name字符长度
select first_name,length(first_name) from employees;
--获取phone_number长度
select phone_number,length(phone_number) from employees;
--把员工编号和部门编号进行连接，并获取连接后的长度
select employee_id,department_id,employee_id||department_id as new_id,length(employee_id||department_id) from employees;
--------instr(参数1，参数2)：返回参数2，在参数1中的位置--------------
--参数1：要操作的字符串
--参数2：给定的字符串
----helloword中r的位置
--找不到的话返回值为0
select instr('helloword','r') from dual;
select instr('helloword','ral') from dual;
-------------------填充------------------
--lpad（参数1，参数2，参数3）:左填充
--参数1
--参数2
--参数3
select salary,lpad(salary,10,'*') from employees;
--rpad（参数1，参数2，参数3）:左填充
--参数1
--参数2
--参数3
select salary,rpad(salary,10,'*') from employees;
--如果填充长度小于本身长度，自动截取
select salary,rpad(salary,3,'*') from employees;
-------------trim（字符串1 from 字符串2）-----------
--把字符串1从字符串2中去除
--把hello中的h去除
select trim('h' from 'hello') from dual;
--trim(参数1)：去除字符串两边的字符
select ('       hello     '),trim('          hello          ') from employees;
--ltrim(参数1)：去除字符串两边的字符
select ('       hello     '),ltrim('      hello     ') from employees;
--rtrim(参数1)：去除字符串两边的字符
select ('       hello     '),rtrim('      hello     ') from employees;
--replace(参数1，参数2，参数3)：代替
select replace('abcde','a','%') from dual;
--删除
select replace('abcde','a') from dual;
--手机号中的小数点删除
select phone_number,replace(phone_number,'.') from employees;
-------------数字函数-------------------
--round(参数1，参数2)：四舍五入
--参数1；要操作的数
--参数2：保留几位小数
--12.345
select round(12.3445,3) from dual;
--trunc(参数1，参数2)：截取
--参数1：要操作的数
--参数2：在什么位置进行截取
--注意：如果是截取小数，小数位消失，如果截取整数，整数为变为0
select trunc(122.362,2),trunc(122.3,1) from dual;
--mod(参数1，参数2)；取余
--结果：参数1%参数2
select mod(1600,300) from dual;
----------------日期函数-------------------
--sysdate：无括号
--返回当前系统时间  格式：年月日、时分秒   返回的是本机系统时间
select sysdate from dual;
--返回前一天系统时间
select sysdate-1 from dual;
--返回后一天时间
select sysdate+1 from dual;
-------------------练习----------------------
--查询员工表中截止到今天，每位员工工作了多少天
select employee_id,sysdate-hire_date as "工作天数" from employees;
--查询员工表中截止到今天，每位员工工作了多少周
select employee_id,（sysdate-hire_date）/7 as "工作周数" from employees;
--所有员工名字前加上Dear，并且名字首字母大写(两种方式)
select last_name,'Dear'||initcap(last_name) from employees;
select last_name,concat('Dear',initcap(last_name)) from employees;
--找出名字为5个字母的员工
select last_name,length(last_name)  from employees where length(last_name)=5;
--找出名字中不带R这个字母的员工
select last_name from employees where last_name not like '%R%';
--显示所有员工姓名的第一个字
select first_name||last_name,substr(first_name||last_name,1,1) from employees;
--显示所有员工，按名字降序排序，若相同，则按工资升序排序
select * from employees order by last_name desc,salary asc;
--假设一个月为30天，找出所有员工的日薪，不计小数
select salary,round(salary/30,0) as 日薪 from employees;
--练习9：查询所有有部门的员工
select * from employees where department_id is not null;
--练习10：查询first_name中包含，n和e的员工的信息
select first_name from employees where first_name like '%n%e' or first_name like '%e%n';
--查询工作名称是‘AD_VP’的员工信息
select job_id from employees where job_id='AD_VP';
----------------------------------------------------------------------------------------------------
----months_between(参数1，参数2)：两个日期相差的月数：
--查询员工表中截止到今天为止，每位员工工作了多少月：
select hire_date,trunc(months_between(sysdate,hire_date)) from employees;
--查询2020-8-3到1987-6-30，差了多少年
select trunc((months_between('3-8月-2020','30-6月-1987'))/12) 年 from dual;
---add_months(参数1，参数2)：向参数1中添加参数2个月份   参数1：时间  参数2：加的月份
--查询姓名、入职日期、入职日期前五个月、入职日期后五个月
select last_name,hire_date,add_months(hire_date,-5) "前五个月"，add_months(hire_date,+5) "后五个月" from employees;
--查询当前时间的下一个月
select add_months(sysdate,+1) from dual;
--next_day(参数1，参数2):指定日期的参数2的星期是哪一天
--指定当前日期的下一个星期五是哪一天
select next_day(sysdate,'星期五') from dual;
--指定2020-9-4下一个星期日是哪一天
select next_day('4-9月-2020','星期日') from dual;
select next_day(sysdate+1,'星期日') from dual;
--从今天开始，下一个星期三的下一个星期日是哪一天
select next_day(next_day(sysdate,'星期三'),'星期日'） from dual；
---last_day(参数1)：返回给定日期月份的最后一天
--查询员工入职日期的当月最后一天
select hire_date,last_day(hire_date) from employees;
--查询2020-8-2日的下一个月的最后一天
select last_day(add_months('2-8月-2020',+1)) from dual;
--查询上个月的最后一天是几号
select last_day(add_months(sysdate,-1)) from dual;
--找出早于25年前入职的员工iiiiiii
select first_name,hire_date  from employees where (months_between(sysdate,hire_date)/12)>25;
select first_name,hire_date from employees where months_between(sysdate,hire_date)>25*12;
--查询员工入职日期是员工当月入职的最后一天的员工
select first_name,hire_date,last_day(hire_date) from employees where hire_date=last_day(hire_date);
--查询入职时间超过20年的员工（两种方式）
select first_name from employees where (trunc(months_between(sysdate,hire_date)/12))>20;

-----------------------转换函数--------------------
--转换函数：一种数据类型转换成另一种数据类型
--三种：日期型、字符型、数值型   （要转换成神类型，就to这个类型）
--to_char(date(日期),'格式');  日期--字符
--把当前日期转换成字符形式之 年yyyy月mm日dd
select to_char(sysdate,'yyyy/mm/dd') from dual;
select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'yyyy"年"/mm"月"/dd"日" day') from dual;
--把当前日期转换成字符形式之 时HH分MI秒SS
select to_char(sysdate,'HH24:MI:SS') from dual;--二十四小时制
select to_char(sysdate,'HH:MI:SS') from dual;--十二小时制
select to_char(sysdate,'HH24"时":MI"分":SS"秒"') from dual;
--显示当前系统时间年-月-日 今天是星期 时-分-秒
select to_char(sysdate,'yyyy-mm-dd "今天是"day HH24:MI:SS') FROM DUAL;

--练习2：查询员工表中1987年入职的员工  to_char
select first_name，hire_date from employees where to_char(hire_date,'yyyy') ='1987';
--查询入职日期在一月份的员工
select first_name,hire_date from employees where to_char(hire_date,'mm')='01';
--查询入职日期在本月最后一天的员工
select first_name,hire_date from employees where to_char(hire_date,'dd') = to_char(last_day('hire_date'),'dd');
--------to_char(数字，'数字的格式')； 数字-字符
--数字格式：数字：9 字符  美元符   本地货币   
--将员工表中工资转换为美元符形式
select first_name,salary,to_char(salary,'$99,99,999') from employees;
--将员工表中工资转换为本地货币格式
select first_name,salary,to_char(salary,'L99,999,99') from employees;
--将新工资转换为美元符与本地货币
select salary*1.25,to_char(salary*1.25,'$999,9,99'),to_char(salary*1.25,'L999,9,99') from employees;
--to_number('有效的数字类型','格式'):将字符转换成数字
--查询工资大于5500的员工
select first_name,salary from employees where salary>5500;

select first_name,salary from employees where salary>to_number('5500');
--to_date('日期'，'格式')将字符转换成日期
--查询1996-1-1之前入职的员工信息
select first_name,hire_date from employees where hire_date<'1-1月-2020';
select first_name,hire_date from employees where hire_date<to_date('2020/01/01','yyyy-mm-dd');
select first_name,hire_date from employees where hire_date<to_date('1-1月-2020','dd-MON-rr');
--有一个字符串helloword，获取world并返回其长度
select replace('helloword','hello'),length(replace('helloword','hello')) from dual;

select substr('helloword',6),length(substr('helloword',6)) from dual; 
--通用函数
--nvl（表达式1，表达式2）；如果表达式1为空，显示表达式2；如果表达式1不为空，显示表达式1
--查询表中员工姓名、经理编号，如果经理编号为空，用o代替
select first_name,nvl(manager_id,0) from employees;
--查询员工姓名、工资、总工资(工资+奖金)，总工资不能为空
select first_name,salary,nvl(salary+salary*commission_pct,0) from employees;
--nvl2(表达式1，表达式2，表达式3)  如果表达式1为空，执行表达式3，不为空，执行表达式2
select nvl2(null,1,2) from dual;
--把salary和commission_pct进行组合，commission_pct为空，现在工资，不为空，总工资
select salary,commission_pct,salary+(salary*commission_pct) as 总工资,nvl2(commission_pct,(salary+salary*commission_pct),salary) from employees;
--nullif(表达式1，表达式2)，如果表达式相等返回空，不相等返回表达式1
select nullif(2,3) from dual;
--查询姓、名的长度是否相等，相等返回null，不相等返回名的长度
select first_name,last_name,nullif(length(first_name),length(last_name)) from employees;
--coalesce(表达式1，表达式2，表达式3.....表达式n)：如果表达式1为空，执行表达式2，表达式2也为空，执行表达式3.。。。。
--------------------------------------
---case表达式
--查询姓名、经理编号、工资
--要求，经理工资上涨，经理编号100，工资+100；经理编号124，工资+200 工资+150
select first_name,manager_id,salary,
case manager_id when 100 then salary+100
                when 124 then salary+200
                else salary+150
end new_salary
from employees;
--查询 姓名、入职日期、奖金比率
--要求：如果在1987年入职，奖金比率+0.5，在1989年入职，奖金比率+0.4，其余加0.1
select first_name,hire_date,commission_pct,
case to_char(hire_date,'yyyy') when '1987' then nvl(commission_pct,0)+0.5
                               when '1989' then nvl(commission_pct,0)+0.4
                               else nvl(commission_pct,0)+0.1
end new_commission
from employees;
--------decode函数-----
--查询姓名、经理编号、工资
--要求，经理工资上涨，经理编号100，工资+100；经理编号124，工资+200 工资+150
select first_name,manager_id,salary,
decode(manager_id,100,salary+100,124,salary+200,salary+150) new_salary from employees;
--查询 姓名、入职日期、奖金比率
--要求：如果在1987年入职，奖金比率+0.5，在1989年入职，奖金比率+0.4，其余加0.1
select first_name,hire_date,commission_pct,
decode(to_char(hire_date,'yyyy'),'1987',nvl(commission_pct,0)+0.5,'1989',nvl(commission_pct,0)+0.4,nvl(commission_pct,0)+0.1) new_commission_pct from employees;