20210809 Oracle 日常

select * from student;

--学生平均年龄
select avg(s.age) 平均年龄 from student s
--学生最大年龄
select max(s.age) 最大年龄 from student s
--学生最小年龄
select min(s.age) 最小年龄 from student s
--学生年龄总和
select sum(s.age) 年龄总和 from student s
--学生总数
select count(*) 学生总数 from student s
--男女学生总人数分别人数
select s.sex 性别,count(*) 总人数 from student s group by s.sex 

select decode(s.sex,1,'男',0, '女') 性别,count(*) 总人数 from student s group by s.sex 

select decode(s.sex,1,'男',0, '女') 性别,count(*) 总人数 ,max(s.age) 最大年龄,
min(s.age) 最小年龄,avg(s.age) 平均年龄,sum(s.age)年龄总和 from student s group by s.sex 

--having 对分完组后从新筛选
--查询不唯一的年龄,having在group by 之后进行条件筛选
select s.age, count(*) from student s group by s.age having count(*)>1 ;

--oder by 根据年龄降序排序  asc升序（默认）,desc降序
select s.* from student s order by s.age desc ;

--根据年龄降序排序，年龄相同，在根据编号降序排序

select s.* from student s order by s.age desc,s.id desc ;
--查询唯一的年龄，并按照年龄降序排序
select s.age, count(*)
  from student s
 where 1 = 1 --查询条件
 group by s.age
having count(*) = 1 --分组条件
 order by s.age desc;--排序条件
 
 
 
select * from hr.departments;
select * from hr.employees;
select * from hr.jobs;
select * from hr.locations;
select * from hr.countries;
select * from hr.regions;
 

create table departments as select * from hr.departments;

select * from departments

create table employees as select * from hr.employees;

create table jobs as select * from hr.jobs;

create table locations as select * from hr.locations;

create table countries as select * from hr.countries;

create table regions as select * from hr.regions;

select * from employees;
select * from departments;

--查询所有员工编号，姓名，入职时间，薪资以及部门名称
select e.*,
       d.department_name 部门名称,
       e.employee_id 编号,
       concat(e.last_name, first_name) 姓名,
       e.hire_date 入职时间,
       e.salary 工资
  from employees e, departments d
 where e.department_id = d.department_id;--两张表关联条件

--查询员工编号为110的姓名，入职时间，薪资，部门名称

select e.*,
       d.department_name 部门名称,
       e.employee_id 编号,
       concat(e.last_name, first_name) 姓名,
       e.hire_date 入职时间,
       e.salary 工资
  from employees e, departments d
 where e.department_id = d.department_id and e.employee_id=110;
 
--查询工龄大于20年的员工
select e.* from employees e where ADD_MONTHS(e.hire_date,15*12)< sysdate;

select e.*
  from employees e
 where MONTHS_BETWEEN(sysdate,e.hire_date) > 12*20;

select e.salary 基本工资,
       e.commission_pct 奖金,
       e.salary + e.salary * nvl(e.commission_pct,0) 实际工资 
       from employees e

--统计各个部门的总人数，平均工资，最大工资，最小工资
select e.department_id 部门,
       count(*) 总人数,
       avg(e.salary + e.salary * nvl(e.commission_pct,0)) 平均工资,
       max(e.salary + e.salary * nvl(e.commission_pct,0)) 最大工资,
       min(e.salary + e.salary * nvl(e.commission_pct,0)) 最小工资
  from employees e, departments d
 where e.department_id = d.department_id
 group by e.department_id
 order by e.department_id;

--查询员工姓名字符个数大于10的员工信息
select concat(e.last_name, first_name) 姓名 from employees e 
where length(concat(e.last_name, first_name))>10;

--查询员工编号，员工姓名，直接上级编号，上级姓名
select e.employee_id , concat(e.last_name, first_name) 姓名, e.manager_id
  from employees e, departments d
 where e.department_id = d.department_id;

select e.employee_id 员工编号,
       concat(e.last_name, e.first_name) 员工姓名,
       b.manager_id 上级编号,
       concat(b.last_name, b.first_name) 上级姓名
  from employees e, employees b
 where e.manager_id = b.employee_id
 order by e.employee_id;
 
 --使用inner join内连接查询员工编号，员工姓名，直接上级编号，上级姓名
 select e.employee_id 员工编号,
       concat(e.last_name, e.first_name) 员工姓名,
       b.manager_id 上级编号,
       concat(b.last_name, b.first_name) 上级姓名
  from employees e join employees b on e.manager_id = b.employee_id
 order by e.employee_id;
 
 --使用left outer、join 左外连接员查询工编号，员工姓名，直接上级编号，上级姓名
  select e.employee_id 员工编号,
       concat(e.last_name, e.first_name) 员工姓名,
       b.manager_id 上级编号,
       concat(b.last_name, b.first_name) 上级姓名
  from employees e left join employees b on e.manager_id = b.employee_id
 order by e.employee_id;
 
 
--使用left outer查询员工编号为110的姓名，入职时间，薪资，部门名称

select e.*,
       d.department_name 部门名称,
       e.employee_id 编号,
       concat(e.last_name, first_name) 姓名,
       e.hire_date 入职时间,
       e.salary 工资
  from employees e left join departments d on
  e.department_id = d.department_id and e.employee_id=110;
  
  
  ----------------------------------------------------------------------------------------
--去除重复的行
select distinct employee_id 部门id from employees;
--插入日期类型的值
insert into student(create_time) values('o001', '12-5月-05', 'V002', 'c', '25-5月-05');

--时间点d再加上n个月
select ADD_MONTHS(sysdate,5) from student;
--用于计算x和y之间有几个月
select months_between(sysdate,sysdate) from student;

--返回某个时间值的当月最后一天
select last_day(sysdate) from student;

--返回某个时间值年月日(四舍五入)
select round(2011/8/31) from student;

--用于计算x时间后第一个星期y的时间

select NEXT_DAY(sysdate,5) from student;

--返回某个时间值年月日(直接截取)
select TRUNC(sysdate) from student;
--用于从一个d或者interval类型中截取到特定的部分 

select TRUNC(sysdate) from student;

select extract(year from sysdate) year,
       extract(month from sysdate) month,
       extract(day from sysdate) day
  from dual;

select to_char(sysdate,'yyyy-MM-dd') from dual;

select to_char(sysdate,'Q') from dual;

select to_char(sysdate,'MM') from dual;  

 select to_char(sysdate,'RM') from dual;
 
 --ww 当年第几周
select to_char(sysdate,'ww') from dual;

  --w 本月第几周
select to_char(sysdate,'w') from dual; 

--DDD 当年第几天,一月一日为001 ,二月一日032 

select to_char(sysdate,'DDD') from dual;

select to_char(sysdate,'DD') from dual; --DD 当月第几天 

--首字母大写
select initcap('hello') from dual;

--字母变小写

select lower('FUN') from dual;

--字母变大写
select upper('sum') from dual;

--左删除
select ltrim('xvzadams','xyz') from dual;

--右删除
select rtrim('xvzadams','xyz')from dual;

--删除两头空格
select trim('  xyza  ') from dual;

--翻译字符集别

select translate('jack','ic','12') from dual;

--替换
select replace ('jack and jue','j','bl')from dual

--查找字符位置
select instr('worldwide','d')from dual;
-- 获取子字符串
select substr('abcdefg',3,2) from dual;
--子字符串连接
select concat('Hello','world') from dual;

SELECT LENGTH('frances') FROM dual;

select length('asdfaskfuh asf ') from dual;

select chr(59) from dual;
--去除要移除的字符串
select trim(6 from 9999876789999)from dual;
--lpad函数从左边对字符串使用指定的字符进行填充
select lpad('function',15,'1') from dual;

-- 绝对值
select abs(-15) from dual;
--取大于他最大的整数
select ceil(44.778)from dual;

--取小于他的最大整数
select floor(100.22)from dual;

--计算m的n次方
select power(4,2)from dual;
--求余
select mod(10,3)from dual;
--四舍五入,保留n为小数
select round(1100.446666,2)from dual;
--直接截取，保留n为小数
select trunc(1100.446666,2)from dual;
--开放跟
select sqrt(4) from dual;
--将值从一种数据类型转换为另一种数据类型
SELECT TO_DATE('2005-12-06','yyyy-mm-dd')  FROM dual;
--char
SELECT TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24"时"MI"分"SS"秒"') FROM dual;

SELECT TO_CHAR(sysdate) FROM dual; --char
--将字符串转化为数字
SELECT TO_NUMBER('100') FROM dual; --number
--char
SELECT TO_CHAR('988889999.89','C999999999.00000') FROM dual; 　

---NULLIF (expr1, expr2) ->相等返NULL(空),不等返回expr1; expr1和 expr2类型必须一致