Oracle数据库语言分类

数据库语言：
DQL：数据库的查询语言：简单查询 限定查询 树形查询 子查询...
DCL：数据库控制语言 赋权 grant 权限 to 用户
                    回收权限 revoke 权限  from 用户
DDL:数据库定义语言 创建表 视图 序列 create table,view,sequence
                   删除表 drop 修改 alter...删内容 truncate
DML:数据库操作语言 插入 insert
                   更新 update
                   删除 delete
                   合并 merge
TPL/TCL:事务处理/控制语言 提交 commit
                          回滚 rollback
DCL：数据库控制语言
赋权：grant select on 表名 to 用户;
回收权限：revoke select on 表名 from 用户
--grant dba to scott;最高管理权限
all select delete update
DDL:数据库定义语言
创建表 视图 序列 create table,view,sequence
--表
创建表：复制表
        手动创建表
语法：create table 表名 as select 语句;
--创建EMP1数据用EMP；
create table EMP1 as select * from emp;
select * from user_tables;--查表
--创建EMP10 EMP20 EMP30 数据同emp部门是10 20 30的数据
create table emp10 as select * from emp where deptno =10;
create table emp20 as select * from emp where deptno =20;
create table emp30 as select * from emp where deptno =30;
select * from emp10;
select * from emp20;
select * from emp30;
--创建一张空表 格式同emp
create table emp_null as select * from emp where 1=2;
select * from emp_null;
注：只要表结构时 where后加一个不成立的条件
表名的命名规则：
驼峰式 ZhangSan
下划线 zhang_san_li_si

手动创建表
  create table 表名(列1 类型长度[约束],
                   列2 类型长度[约束]...)
  数据类型
  数值型 number [数1[,数2]] 会进行四舍五入
                 数1表示长度 数2表示小数位数 数2不写默认整数
                 数1最大精度为38
                 数1最长可为126
                 int 整形  只能存储的数据相当于 number(22)
  字符型 char([数]) 定长字符
         最多可以存储2000字节的字符 数不写 默认是1
         位数不足用空格补齐
         varchar/varchar2(数) 不定长字符
         最多存储4000个字节的字符 数不能不写
         位数不足时 直接存储
         二者的区别：
         char 读取速度快 可能会浪费空间
         varchar/varchar2 速度会慢 不会浪费空间
  日期型 date 由一个9字节组成的定宽日期时间类型
         timestamp 时间戳
         比date更加的精确 存储了时区
         select systimestamp from dual;
  大对象 clob 字符大对象     用char存储 例如：xml,txt
         blob 二进制大对象   用二进制存储 保存到图片
--创建一个学生表
create table stu(
stu_id number(10),
stu_name varchar(20),
stu_sex char(2),
stu_bith date,
stu_pic blob);

select * from stu;
约束 constraint 
作用：用来确保数据满足业务需求 保障数据的合理性 合法性
分类：主键：primary key    pk 唯一且非空--一个表内有一个主键
      非空约束：not null   nn 非空
      唯一约束：unique     un 唯一值(可以有空)
      检查约束：check      ck 给他一个条件
      外键约束：foreign key   fy
      默认值：default
--非列模式 不带约束名
create table stu1(
stu_id number(10) primary key,
stu_name varchar(20) not null,
stu_sex char(2) check(stu_sex in('男','女')),
stu_bith date default date'2011-1-1',
stu_pic blob);
select * from stu1;
--非列模式 带约束名
create table stu2(
stu_id number(10) constraint pk_id primary key,
stu_name varchar(20) constraint nn_name not null,
stu_sex char(2) constraint ck_sex check(stu_sex in('男','女')),
stu_bith date default date'2011-1-1',
stu_pic blob);
--列模式 不带约束名
create table stu3(
stu_id number(10),
stu_name varchar(20),
stu_sex char(2),
stu_bith date,
stu_pic blob,
primary key(stu_id),
check(stu_name is not null),
check(stu_sex in('男','女')));
注：默认值不能用列模式 非空列用列模式要换成检查
   外键约束和复合主键  只能用列模式创建
   复合主键：列模式,primary key(id,name)
--外键约束;
需要两张表 创建外键约束的表是子表 参考表是父表
创建外键约束的列叫外键列 参考表的参考列必须是父表的主键列
作用：外键列中的值必须是父表主键列中存在的
语法：constraint 约束名 foreign key (列) references 父表(主键列)
                [or delete cascade|on delete set null]
emp子表  dept父表
insert into emp(empno,deptno) values(1234,40);
select * from emp;
delete from emp where empno=1234;--删除行
--创建emp2在他的deptno列增加外键约束 参考是dept的deptno
create table emp2(
empno number,
deptno number,
constraint fk_1 foreign key (deptno) references dept(deptno)
on delete set null);
级联删除 cascade
级联删除为空 set null
不指定级联删除 no action--默认
--删除父表的数据时
  如果没有指定级联删除 必须先删除子表的相关项 在删除父表的对应项
  如果指定了级联删除 子表和父表对应行会一起删除
  如果指定了级联删除为空 父表对应行被删除时 子表的对应项存在
                         外键列对应的那个值变成了null
delete from dept where deptno=10;
插入/更新数据到子表外键列的值 必须是父表中主键列里存在的
插入/更新数据到父表主键列的时候 没有限制(符合主键列的约束)
--增加约束
alter table 表名 add [constraint 约束名] 约束类型(列);--注意add
alter table stu add primary key(stu_id);
--约束重命名
alter table 表名 rename constraint 旧名字 to 新名字;
alter table stu2 rename constraint ck_sex to ck_111;
--修改非空 默认值 列的类型长度
alter table 表名 modify 列名 类型长度 [default 值] [not null|null];
alter table stu1 modify stu_id number(20) default 5 not null;--id改可为空,默认5
修改列的类型长度 列必须是空的
列改为可为空 该列必须现在是非空
varchar2 的长度可以加长 如果要剪短
         则修改的值必须不小于列中最长值的长度
number 要修改长度 必须此列为空
--删除约束
alter table 表名 drop constraint 约束名;
alter table stu2 drop constraint ck_111;
insert into stu1(stu_name) values('c');--插入名为c的学生，id默认5
select * from stu1;
insert into stu1(stu_id,stu_name) values('3','d');--id是唯一主键列
--增加列
alter table add 表 (列 类型长度...)
--列的重命名
alter table 表 rename column 旧名 to 新名;
--删除列
alter table 表 drop (列1,列2...) 可以删除多个列
alter table 表 drop column 列    删除单个列
--表的重命名
rename 旧名 to 新名;
--清空表的数据
select * from emp1;
truncate table 表名；
truncate table emp1;
--删除表
drop table 表名 [purge];
加上 purge 表示物理删除 直接就没有了
不加 purge 表示逻辑删除 放进回收站
--查看回收站
select * from user_recyclebin;
--从回收站闪回删除的表
flashback table 表名 to before drop;
--清空回收站
purge recyclebin;
--视图 view
定义：是一张虚表 内容由查询定义
创建：create [or replace] view 视图名 as select 语句 [with read only]
      如果加上 or replace 如果现有的视图名存在 会覆盖之前的视图
          不加 or replace 如果视图存在 会报错
      如果加上 with read only 表示是只读视图 无法修改
          不加 with read only 表示创建普通视图 可以修改
--创建视图v_10 表示emp中10 部门的数据
create view v_10 as select * from emp where deptno=10;
select * from v_10;
注：视图一般以v_开头
    修改视图相当于修改表
    工作中一般不允许通过视图修改表
视图的优缺点：
优点：不占空间 安全
缺点：性能差 修改限制
--视图删除
drop view 视图名；
drop view v_10;
--序列 sequence
数据库中存放等差数列的表--主要用来提供主键值
创建序列的语法：
create sequence 序列名
  [start with 数]--从几开始 不写默认是1
  [maxvalue 数]--最大到几 不写默认9999999(28个9)
  [minvalue 数]--最小是几  默认是1
  [increment by 数]间隔 等差 不写默认是1
  [cache 数] 缓冲值 默认20
  [cycle] 是否循环 默认不循环
--创建一个序列s1
create sequence s1
start with 6
maxvalue 100
minvalue 2
increment by 2
cache 10
cycle;
--使用序列
序列名.nextval  --先执行
序列名.currval  --后执行
select s1.nextval,s1.currval from dual;
--创建一个不循环的序列s2
create sequence s2
start with 6
maxvalue 100
minvalue 2
increment by 10
cache 10;--缓冲值不能大于本轮循环产生的数量
select s2.nextval,s2.currval,s2.currval from dual;--第一先执行，后面列都是后执行
--序列的删除
drop sequence 序列名;
drop sequence s2;

DML数据库操作语言 insert 插入 delete 删除 update 更新 merge 合并
--插入数据
批量插入
手动插入
批量插入 语法：insert into 表名[列...] select 语句
--创建一张空表emp_1格式同emp
create table emp_1 as select * from emp where 1=2;
--批量插入10部门的数据
insert into emp_1 select * from emp where deptno=10;
--插入20部门的员工姓名 员工编号 工资 部门编号
insert into emp_1(ename,empno,sal,deptno) 
       select ename,empno,sal,deptno from emp where deptno=20;
--把部门表中的dname loc deptno插入emp_1的ename job deptno
insert into emp_1(ename,job,deptno) 
       select dname,loc,deptno from dept;
select * from emp_100;--看看表啥样
--insert first|insert all

--把一个结果集的数据插入到多张表中
insert first|insert all
when 条件1 then into 表(列...) values (值...)
when 条件2 then into 表(列...) values (值...)
  ...
select 语句
--创建三张空表emp_100 emp_200 emp_300 格式同emp
create table emp_100 as select * from emp where 1=2;
create table emp_200 as select * from emp where 1=2;
create table emp_300 as select * from emp where 1=2;
--emp表工资大于1000的插入emp_100中
--emp表工资大于2000的插入emp_200中
--emp表工资大于3000的插入emp_300中
insert all
  when sal > 1000 then
  into emp_100(empno,ename,sal) values(empno,ename,sal)
  when sal > 2000 then
  into emp_200(empno,ename,sal) values(empno,ename,sal)
  when sal > 3000 then
  into emp_300(empno,ename,sal) values(empno,ename,sal)
select * from emp;
--先删除表中内容再插
truncate table emp_100--删除内容
select * from emp_100;
select * from emp_200;
select * from emp_300;
insert first 表示值只会插入一次 前面的表插入后 后面的表就不在插入
insert all 表示插入后 后面的表还会继续插入
--手动插入
insert into 表名(列1,列2...) values (值...);
select * from emp_100;
--创建一张表
create table abc(name varchar(20) primary key,id char(5));
insert into abc values('abc'||s1.nextval,'11'||s1.currval);
select * from abc;
埋数 insert into 表名 values()
="insert into 表 values('"&选择对应表格&"','"&选&"','"&选&"','"&选&"','"&选&"');"
DML 数据库操作语言
--delete from ...where
不加where 表示对全表的数据进行删除
select * from emp_1;
delete from emp_1;
--删除emp_1中empno=7782的
delete from emp_1 where empno=7782;

注：delete 和 truncate 的区别：
delete 是DML 可以回滚 truncate 是DDL 不能回滚
delete 后可加条件 truncate 后不能加条件
delete 不能降低高水位线 truncate 可以降低高水位线
delete 慢 truncate 快
--rowid 伪列
--rownum 分页查询
--如何去除重复的数据
create table emp_1 as select * from emp;
insert into emp_1 select * from emp where deptno=20;
select * from emp_1 order by deptno; 
--去重 emp_1 表的数据
创建一个原表的备份表再进行操作，防止没有回旋余地
--1.先创建一张备份表emp_111数据同emp_1去重后的数据
create table emp_111 as select distinct * from emp;
或--删除最大rowid得到最小的rowid 最小指的是时间最早的
create table emp_111 as select * from emp_1
where rowid in (select min(rowid) from emp_1 group by empno);
--2.清空原来emp_1表的数据
truncate table emp_1;
--3.原备份表中去重的数据插入到原表中
insert into emp_1 select * from emp_111;
--4.删除备份表
drop table emp_111;
--看看表
select * from emp_1;
--update 更新
update 表名 set 列=更新,  where 条件
select * from emp_1;
--把emp_1表中10部门的员工姓名小写 工资加500
update emp_1 set ename=lower(ename),sal=sal+500 where deptno=10;
--更新 20部门 工资减半 在减半 名字首字母大写
update emp_1 set ename=initcap(ename),sal=sal/2/2 where deptno=20;
--更新30部门 工资变为原来的1/5 再开1/3次方 名字左边加@
update emp_1 set sal=power(sal/5,1/3),ename='@'||ename where deptno=30;
--更新全表奖金+10000
update emp_1 set comm=nvl(comm,0)+10000;
--如果emp_1的工资小于原表的工资把他的job更新为out
update emp_1 set job='out' where sal<(select sal from emp
                                    where emp_1.empno=emp.empno);
--合并数据
语法：
merge into 表A --进行插入 更新的表
using 表b   --参考表
on 条件
when matched then--匹配成功
  update set a.列=b.列|值...
when not matched then--没有匹配成功
  insert(a.列) values(b.列|值...)
--创建 a b
a 中插入emp的empno ename deptno
b 中插入emp的empno lower(ename) deptno 部门10改为40
create table a as select empno,ename,deptno from emp;
create table b as select empno,lower(ename) ename,
                         decode(deptno,10,40,deptno) deptno from emp;
select * from a;
select * from b;
参考b对a进行插入更新
merge into a
using b
on (a.empno=b.empno and a.deptno=b.deptno)
when matched then
  update set a.ename=b.ename
when not matched then
  insert values(b.empno,b.ename,b.deptno)
进阶语法：
merge into 表A --进行插入 更新的表
using 表b   --参考表
on 条件
when matched then--匹配成功
  update set a.列=b.列|值...
  [where] 更新的条件
  [delete where] 删除的条件
when not matched then--没有匹配成功
  insert(a.列) values(b.列|值...)
  [where] 插入的条件
--建表 aa bb
create table aa as select * from emp;
create table bb as select * from emp;
--把bb表里名字小写 10部门换成40部门
update bb set ename=lower(ename);
update bb set deptno=40 where deptno=10;
select * from aa;
select * from bb;
--参考bb对aa进行插入更新 插入和更新的条件都是工资大于2000把工作是ANALYST的删除
merge into aa
using bb
on (aa.empno=bb.empno and aa.deptno=bb.deptno)--完全没变+变了一点
when matched then
  update set aa.ename=bb.ename
  where aa.sal>2000
  delete where aa.job='ANALYST'
when not matched then
  insert (aa.empno,aa.ename,aa.sal,aa.deptno)--其他没插入的列显示为空
  values(bb.empno,bb.ename,bb.sal,bb.deptno)
  where bb.sal>2000;
--事务
侠义：从当前的tbl到下一个tpl之间的所有dml
(如果执行ddl ddl之前的dml会自动提交)
广义：任何一次的操作 要么成功 要么失败
事务的四个特性：
原子性：事务包含的操作要么全部成功 要么全部失败回滚 成功必须完全应用到数据库
        失败不会对数据库造成影响
一致性：事务执行前和执行后的状态必须是一致的
隔离性：当多个事务并发访问数据库时 数据库为每个用户开启的事务
        不受其他事务操作影响 多个并发事务之间是相互隔离的
持久性：一个事务提交了之后 对数据库的改变是永久的 即使故障也不会改变