Oracle基本语法学习记录（Day02）

Oracle基本语法学习记录（Day02）

记录
数据库链接

select ABS(-14) as 绝对值 from dual --dual是一张虚拟表
--LOWER 将字符串转换为小写
select LOWER('HELLOWORLD') as 转小写 from dual
----UPPER	将字符串变为大写 
select UPPER('ABCDEFG') as 转大写 from dual
--INITCAP	将字符串的第一个字母变为大写 
select INITCAP('superman') as 首大 from dual
--CONCAT 拼接两个字符串，与 || 相同 
select ID||ID,NAME from  S_DEPT ;
select CONCAT(ID,ID),NAME from  S_DEPT ;
--SUBSTR	取字符串的子串 
--orcale语法有点不一样，它是从下标为1的开始的，没有下标0
select SUBSTR('HELLO',3) from dual
select SUBSTR('HELLOWORLD',3,5) from dual
--LENGTH	以字符给出字符串的长度
select LENGTH('HELLOWORLD') as 长度 from dual
--NVL	以一个值来替换空值
select SALARY*nvl(COMMISSION_PCT，0) as 提成 from s_emp
-- 查询last_name值为patel，不区分大小写 用upper
select upper(LAST_NAME) from s_emp where upper(LAST_NAME)='BIRI'
--ROUND精确度
select ROUND(452.7454) from dual
select ROUND(452.7454,2) from dual
select ROUND(452.7454,-1) from dual
--TRUNC
select TRUNC(452.7454) from dual
select TRUNC(452.7454,2) from dual
select TRUNC(452.7454,-1) from dual
--TO_DATE
select MONTHS_BETWEEN(
       TO_DATE('01-03-2000','MM-DD-YYYY'),
       TO_DATE('03-05-1900','MM-DD-YYYY')
       ) as MONTHS
from dual
--得到入职多少年
select ROUND(MONTHS_BETWEEN(sysdate,START_DATE)/12) as 入职年份
from s_emp
--月份增加
select ADD_MONTHS(sysdate,1) from dual
--NEXT_DAY
select NEXT_DAY(sysdate,'星期一') from dual
--LAST_DAY
select LAST_DAY(ADD_MONTHS(sysdate,1)) from dual
--TO_DATE
select TO_DATE('16-08-2019','dd-MM-yy') from dual
select ROUND(TO_DATE('16-08-2019','dd-MM-yyyy'),'MONTH') from dual

--to_char(1210.73, '9999.9') 返回 '1210.7' 
--to_char(1210.73, '9,999.99') 返回 '1,210.73' 
--to_char(1210.73, '$9,999.00') 返回 '$1,210.73' 
--to_char(21, '000099') 返回 '000021' 
--to_char(852,'xxxx') 返回' 354'
select to_char(123456.123456,'99,999,999.999') from dual
--日期转字符串
select to_char(sysdate,'yyyy-mm-dd hh24:MI:SS') from dual
--查询91年入职的员工
select LAST_NAME,to_char(START_DATE,'fmDdspth " of "MONTH YYYY fmHH:MI:SS AM') HIREDATE
    from s_emp where to_char(START_DATE) like '%91'
--查询员工表中入职日期在5月份的员工信息
select * from s_emp where to_number(to_char(START_DATE,'MM'))=5
--'2019-08-15 19:35:44'
select to_date('2019-08-15 19:35:44','yyyy-mm-dd hh24:MI:SS') from dual
--查询员工表中manager_id为空的员工查询出来，并将空列的值置为“No Manager”
select nvl(to_char(MANAGER_ID),'No Manager') LAST_NAME,id from s_emp
where MANAGER_ID is null

--查询员工表中last_name为’Biri’的员工的last_name与部门名称查询出来
----------内连接
--select 
--表名1.列名，表名2.列名，表名1.列名，表名2.列名 。。。
--from 
--表名1,表名2  
--where 表名1.列=表名2.列名
select s_emp.LAST_NAME,s_dept.name
from s_emp,s_dept
where s_emp.DEPT_ID=s_dept.id
      and s_emp.LAST_NAME='Biri'
--select 
--别名1.列名，别名1.列名，别名2.列名，别名2.列名 。。。
--from 
--表名1 as 别名1,表名2  as 别名2
--where 别名1.列=别名2.列名
select emp.LAST_NAME,dept.name
from s_emp  emp,s_dept  dept
where emp.DEPT_ID=dept.id
      and emp.LAST_NAME='Biri'
----内连接方式2
--select 表1.列名，表2.列名
--from 表1
--inner join
--表2
--on 表2.列 =表1.列
select s_emp.LAST_NAME,s_dept.name
from  s_emp inner join s_dept
on s_emp.DEPT_ID=s_dept.id
      where s_emp.LAST_NAME='Biri'
----------自连接
--查询员工表员工的last_name及其部门经理名称
select emp.LAST_NAME|| '是'||emp2.LAST_NAME ||'经理'
from s_emp emp inner join s_emp emp2
on emp.id = emp2.MANAGER_ID

-------------外连接
--左外连接方式一
select * from s_emp
left outer join s_dept
on s_emp.DEPT_ID=s_dept.id
where s_dept.id is null

--左连接方式二
select * from s_emp,s_dept
where s_emp.DEPT_ID=s_dept.id(+)
and  s_dept.id is null

--右连接方式
select * from s_dept
right outer join s_emp
on s_emp.DEPT_ID=s_dept.id
where s_dept.id is null


---------------三表连接,显示地区、last_name,部门名
select * from s_emp,s_dept,s_region
where s_emp.DEPT_ID=s_dept.id
and s_dept.REGION_ID=s_region.ID
---------
select * from 
  s_emp inner join 
  s_dept 
on s_emp.DEPT_ID=s_dept.id
inner join s_region
on s_dept.REGION_ID=s_region.ID
------------
select * from 
  s_emp inner join 
  s_dept inner join s_region
  on s_dept.REGION_ID=s_region.ID
  on s_emp.DEPT_ID=s_dept.id