--创建一个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;
