01. Oracle(基础语法)[通俗易懂]

1. 函数

1.1 大小写转换函数

1.2 字符串操作函数

1.3 数字操作函数

1.4 日期函数

• fmt:
  • YEAR: 摄入某年的1月1日,几千半年舍去,后半年作为下一个月
  • MONTH: 摄入到某年的1日,即前月舍去,后半月作为下一个月
  • DDD: 默认,月中的某一天,最靠近的天,前半天舍去,后半天作为第二天
  • DAY: 舍入到最近的周的周日,即上半周舍去,下半周作为下一周周日
  • 注意:
    • YEAR,MONTH,DAY可以为DATE类型匹配
    • HOUR,MINUTE,SECOND必须与TIMESTAMP类型匹配
    • HOUR匹配的结果没有加上时区,因此在中国运行的结果小8小时

1.5 转换函数

• 日期格式化元素:
  • YYYY: 4位数表示的年份
  • YEAR: 英文描述的年份
  • MM: 2位数表示的月份
  • MONTH: 英文描述的月份
  • MON: 三个字母的英文描述月份简称
  • DD: 2位数表示的日期
  • DAY: 英文表示的星期几
  • DY: 三个字母的英文描述的星期几的简称
  • HH24:MI:SS : 时分秒的格式化
  • DDspth: 英文描述的月中第几天
  • fm: 格式化关键字,可选

1.6 其他单行函数

1.7 聚合函数

1.8 条件表达式

• case…when…then…else…end: 相当于 if…else if…else

  -- 写法一: 直接跟字段
  case 字段 when 值1 then 结果1
  	when 值2 then 结果2
  		else 结果三 end
  -- 写法二: 跟判断语句
  case when 条件判断1 then 结果1
  	when 条件判断2 then 结果2
  		else 结果3 end
  

  代码100分

• decode:

  代码100分 decode(字段1,"值1",结果1,"值2",结果2,结果3)
  

2. 视图

• 定义:
  • 当我们多次使用同一个复杂复杂语句进行查询时,我们不想每次都写很复杂的语句,就可以创建视图
• 优点:
  • 提供了另一种级别的表安全性
  • 隐藏了数据的复杂性
  • 简化了SQL命令
  • 隔离基表的改变
• 语法:
  • 创建视图:
    • CREATE ViEW 视图名 AS sql查询语句
  • 删除视图:
    • DORP VIEW 视图名
  • 修改视图:
    • UPDATE VIEW 视图名 WHERE 条件
  • 查询视图:
    • SELECT * FROM 视图名

3. 集合操作

• 语法:

  • select语句 集合操作方法 select语句

4. 连接查询

• (+): oracle特有方式,未被标记的一方会被作为基表

  -- 相当于左外连接
  select * from a,b where a.id = b.id(+)
  -- 相当于右外连接
  select * from a,b where a.id(+) = b.id
  

5. 排序操作

• 将结果集中的null放在最前或最后: nulls first/nulls last
  • null 默认
  • 书写在order by 之后

6. 序列操作

• 相当于MySql中的自动增长序列

  • 完整写法:

    • create sequence 序列名
    • start with 5 —从5开始
    • increment by 2 —每次增长2
    • maxvalue 20 —最大值20
    • cycle —可循环
    • cache 5 —缓存5

  • 创建序列:

    • create sequence 序列名

  • 查询序列:

    • select * from emp
      • 当前值: currval
      • 下一个值: nextval

  • 删除序列:

    • drop sequence 序列名

7. 索引

• 提高检索的速度

  • 大数据才创建索引,为经常用到的列创建索引
  • 索引不要超过四层
  • 主键自带索引

  代码100分-- 创建索引:
     create index 索引名 on 表名(列)
  -- 删除索引:
     drop index 索引名
  

8. 窗口函数

• 可以简单理解为分组后的展示所有数据

  row_number()over( partition by "分区字段" order by "分组字段" asc/desc ) 
  

9. sql 练习

drop table emp;
create table emp(
       eid number,
       ename varchar2(224),
       birthday date,
       salary float,
       did number
)

insert into emp values(1,"jack",to_date("2000-1-30 16:20:31","yyyy-mm-dd hh24:mi:ss"),3000.0,2);
insert into emp values(2,"rose",to_date("1999-5-1 17:00:00","yyyy-mm-dd hh24:mi:ss"),5000.0,3);
insert into emp values(3,"admin",to_date("2000-10-1 00:56:59","yyyy-mm-dd hh24:mi:ss"),4000.0,4);
insert into emp values(4,"zhangsan",to_date("1998-1-1 12:30:00","yyyy-mm-dd hh24:mi:ss"),4500.0,1);
insert into emp values(5,"lisi",to_date("1900-4-1 14:07:20","yyyy-mm-dd hh24:mi:ss"),55000.0,null);

drop table dept;
create table dept(
       did number,
       dname varchar2(225)
)
insert into dept values(1,"管理");
insert into dept values(2,"运维");
insert into dept values(3,"人事");
insert into dept values(4,"开发");

-- 字符串处理函数
--  转换大写 upper
select upper("aaa") from emp;

--  转换小写 lower
select lower("AAA") from emp;

--  首字母大写 initcap
select initcap("hello word!!!") from emp;

--  字符串截取 substr
select substr("helloword!!!",0,5) from emp;

--  字符串替换 replace
select replace("helloword!!!","!","?") from emp;

--  字符串长度 length
select length("helloword!!!") from emp;

--   指定字符出现的位置 instr
select instr("hellowword!!!","h") from emp;


--  数值函数
--  四舍五入 round
select round(15.66,-2) from emp;    -- 0
select round(15.66,-1) from emp;    -- 20
select round(15.66,0) from emp;     -- 16
select round(15.66,1) from emp;     -- 15.7
select round(15.66,2) from emp;     -- 15.66

-- 截取 trunc
select trunc(15.66,-2) from emp;    -- 0
select trunc(15.66,-1) from emp;    -- 10
select trunc(15.66,0) from emp;     -- 15
select trunc(15.66,1) from emp;     -- 15.6
select trunc(15.66,2) from emp;     -- 15.66

--  取余数 mod
select mod(20,3) from emp;


--  日期函数
--  查询系统时间 sysdate
select sysdate from emp;

--  查询今年多少岁
select ename,(sysdate - birthday)/365 from emp;

--  查询过了多少月  months_between
select ename,months_between(sysdate,birthday) from emp;

--  查询三个月后的日期 add_months
select ename,birthday,add_months(birthday,3) from emp;

--  转换函数 
--  把当前时间转换成指定格式的字符串  to_char  fm/去零
select to_char(sysdate,"yyyyfm/mm/dd hh24:mi:ss") from emp;
--  将数字转换成字符串
select to_char(99) from emp;

-- 显示成年月日
select to_char(sysdate,"yyyy") || "年" || to_char(sysdate,"mm") || "月" || to_char(sysdate,"dd") || "日" from emp;

--  把字符串转换成日期类型 to_date
select to_date("1999-10-1 15:24:31","yyyy/mm/dd hh24:mi:ss") from emp;

--  将字符串转换成数字 to_number
select to_number("99") from emp;

--  通用函数
--  空值处理函数 nvl /如果为null,返回指定字符
select nvl(ename,"无") from emp;

--  空值处理函数 nv2 /如果buweinull,返回指定字符1/为null,返回指定字符2
select nvl2(ename,"有","无") from emp;


--  条件表达式
--  将指定内容转换成"xxx" decode
select decode(ename,"admin","管理员","其他") from emp;
--  其他方式 case when then end
select case ename when "admin" then "管理员" else "其他" end from emp;

--  查询工资最高的三个人
--  rownum: 给结果集加上一个序列号
select rownum,e.* from (select * from emp order by salary desc) e where rownum <= 3;
select * from (select rownum r,e1.* from (select * from emp order by salary desc) e1 ) e2 
where e2.r > 3 and e2.r <= 6;
/*
分页公式:
pageNum = 1
pageSize = 2
select * from (select rownum r,e1.* from (select * from 表 order by 排序字段 desc) e1 ) e2 
where r > (pageNum - 1) * pageSize and r <= pageNum * pageSize;
*/


--  集合函数
--  交集 intersect/相当于and
select * from emp where ename = "admin"
intersect
select * from emp where salary = 4000.0;

--  并集 union/相当于or
select * from emp where ename = "admin"
union
select * from emp where salary = 5000;

--  差集 minus/第一个结果集减去第二个结果集
select * from emp where ename = "admin"
minus
select * from emp where salary = 4000;


--  exists/如果存在返回true/反之false
--  找出哪一个没有部门信息
select * from emp where exists(select * from dept where emp.did = dept.did)
select * from emp where not exists(select * from dept where emp.did = dept.did)


--  序列  相当于mysql中的自动增长序列,操作失败也会增长
/*
完整写法:
create sequence 序列名
start with 5  -- 丛5开始
increment by 2 -- 每次增长2
maxvalue 20 -- 最大值20
cycle -- 可循环
cache 5 -- 缓存5
*/
-- 创建序列
create sequence emp_seq;

--  查询序列
select emp_seq.nextval from emp;

--  删除序列
drop sequence emp_seq;


-- 索引
/*
目的是为了提高检索速度
语法: create index 索引名 on 表名(列名)
原则: 大数据才创建索引
      为经常用到的列创建索引
      索引不要超过4层
      主键自带索引
*/
--  创建索引
create index index_ename on emp(ename)

--  删除索引
drop index index_ename;