DQL 数据查询语言
- 简单查询
- 限定查询
- 子查询
- 表连接
- 集合
DCL 数据控制语言
- grant 赋权
grant 权限 to 用户 - revoke 回收权限 dba 岗位
revoke 权限 from 用户；
DDL 数据定义语言
- 创建 create
- 删除 drop
- 修改 alter
- 清空 truncate
DML 数据操作语言
- 插入 insert
- 更新 update
- 删除 delete
- 合并 merge
TCL事务控制语言
- 提交 commit
- 回滚 rollback
创建表
1、复制表的方式创建表
create table 表名 as select 语句;
create table 表名 as select 语句;
--创建一张emp1 ,emp1和emp表的列名列的数据类型等都一致
create table emp1 as select * from emp;
select * from emp1;
--创建表emp3 内容和emp表的 ename,job,hiredate列一样
create table emp3 as select ename,job,hiredate from emp;
select * from emp3;
--创建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_null 表格式同emp一致(注意:表格式)
create table emp_null as select * from emp where 1=2;
select * from emp_null;
2、手动创建表
字段类型:|-数值型 number int
|-字符型 char varchar varchar2
|-日期型 date
语法结构:
create table 名(列1 类型长度 [约束],
列2 类型长度 [约束],
.....)
--创建学生表
create table student(id number(10),
name varchar2(50),
sex varchar2(8),
birth date,
family varchar2(100),
phone number(11));
emp
select * from student;
drop table student;
--创建一张yuangong 字段参考emp表
create table yuangong(员工编号 number(4),
部门编号 number(2),
职位 varchar2(9),
员工姓名 varchar2(10),
薪资 number(7,2),
提成 number(7,2),
入职日期 date,
经理编号 number(4));
select * from yuangong;
带约束创建表
create table 名(列1 类型长度 [约束],
列2 类型长度 [约束],
.....)
表的约束是Oracle数据库中应用在表数据上一系列强制性的规则。
当向已创建约束的表中插入数据或修改表中数据时，必须满足表的完整性约束所规定的条件。
按照约束用途分类：
- primary key：主键约束 唯一且非空
- foreign key：外键约束
- check：检查约束
- unique：唯一约束
- not null：非空约束
- default：默认值
create table student(
id number(8) primary key,
--一个列可以加多个约束,如果有not null约束 必须放最后
name varchar2(1000) unique not null ,
sex varchar2(6) check(sex in('男','女')) ,
class varchar2(20) not null,
birth date default date'2023-9-1');
带约束名创建表：
create table 名(列1 类型长度 constraint 约束名 约束,
列2 类型长度 constraint 约束名 约束,
.....)
drop table student;
create table student(id number(8) constraint pk_id primary key,
name varchar2(1000) constraint unn_name unique not null , --一个列可以加多个约束,如果有not null约束 必须放最后
sex varchar2(6) check(sex in('男','女')) ,
class varchar2(20) constraint nn_class not null,
birth date default date'2023-9-1'); --默认值不可以添加约束名
单独设置约束名
create table 名(列1 类型长度 ,
列2 类型长度 ,
.....
[约束名] 约束(字段),
[约束名] 约束(字段));
drop table student;
create table student(id number(8) ,
name varchar2(1000),
sex varchar2(6),
class varchar2(20),
birth date,
primary key(id,name), --可以设置组合主键 为一个或多个字 段
check(sex in('男','女')),
unique(name),
check(class is not null) --非空约束必须改写为check
--默认值不可以用这种定义方式
);
一张表只能有一个主键，但是一个主键可以由多个字段共同构成。
外键约束：外键约束需要两张表，创建外键约束的表是子表（外键表），参考表是父表（参考表）。
创建外键约束的列叫外键列，参考表的参考列必须是父表的主键列。
外键约束的作用：外键列中的值必须是父表的主键列中存在的值。
语法: [constraint 约束名 ] foreign key(外键列) references 父表(主键列)
drop table yuangong;
create table yuangong(empno number(4),
ename varchar2(10) ,
job varchar2(9) ,
mgr number(4) ,
hiredate date ,
sal number(7,2) ,
comm number(7,2),
deptno number(2),
primary key(empno),
foreign key(deptno) references dept(deptno) )
增加约束
alter table 表名 add [constraint 名] 约束类型(列);
alter table yuangong add unique(mgr);
几种特殊情况 需要特殊语句添加/修改 约束
- 当修改非空约束、默认值约束、列的类型长度时，需要使用modify
- 当修改数据类型长度时，要求字符为空或长度大于原始长度
alter table 表名 modify 列名 [类型长度 ] [default 值 | not null | null];
--修改yuangong 表 sal字段约束可以为空
alter table yuangong modify sal number(7,2); --默认值的类型长度也会影响修改
select * from yuangong;
insert into yuangong(empno) values(1234);
truncate table yuangong;
select * from yuangong;
--修改yuangong表的sal 类型长度为8,2
alter table yuangong modify sal number(8,2);
drop table yuangong;
删除约束
alter table 表名 drop constraint 约束名
alter table yuangong drop constraint SYS_C0011517;
alter修改表
1添加列
alter table 表名 add 列名 数据类型长度 ;
alter table yuangong add phone_number varchar2(20);
select * from yuangong;
2修改列的数据类型
alter table 表名 modify 列名 数据类型;
alter table user_info modify phone_number number(20);
3修改列名
alter table 表名 rename column 原列名 to 新列名;
alter table yuangong rename column phone_number to p_number;
alter table emp100 rename column abc to cba;
4删除列
alter table 表名 drop column 列名;
alter table yuangong drop column p_number;
5修改表名
alter table 原表名 rename to 新表名;
alter table yuangong rename to yuan_gong;
alter table emp100 rename to emp001;
select * from yuan_gong;
删除表
rop table 表名
--物理删除 --无法在回收站找回
drop table 表名 purge;
select * from emp10;
insert into emp10(empno) values(1234);
truncate table emp10; --ddl
--清空表数据
truncate table 表名
--查看回收站
select * from user_recyclebin;
select * from emp3;
--清空 emp3 --删除表数据 表结构还在 但是数据无法找回 慎用
truncate table emp3;
--删除 表emp3 --删除表 可以通过 recyclebin 找回
drop table emp3;
--闪回技术 闪回emp3
flashback table a to before drop;
--物理删除emp3;
drop table emp3 purge;
--清空回收站
purge recyclebin;
总结
ddl --一般操作无法撤回
create |-复制表的方式创建 create table 名 as select ...;
|-手动创建表 create table 名 (列1 数据类型 [约束],
列2 数据类型 [约束]...)
|-数据类型 |-数值型 int number
|-字符型 char varchar varchar2
|-日期型 date
|-约束: 主键pk(唯一且非空) 外键(两张表) fk 唯一 un 检查 ck 非空 nn 默认值 default
alter |-增加列 alter add
|-删除列 alter drop
|-重命名 alter rename
|-删除约束 alter drop
|-修改列的类型长度及约束 alter modify
drop |-删除表 drop table 名 [purge]
|-清空表 truncate table 名 --慎用
|-清空回收站 purge recyclebin
|-闪回技术 flashback
truncate
DML数据操作语言
插入 insert
更新 update
删除 delete
合并 merge
commit;
rollback;
insert 插入数据
批量插入
insert into 表名[(列名)] select 语句
--创建一个表emp1 结构和emp一致
drop table emp1;
create table emp1 as select * from emp where 1=2;
手动插入
insert into 表名[(列名)] values(值1,值2...)
dml操作有提交和回滚
--创建一张emp2 表结构同emp 向emp2插入emp表10部门的数据
create table emp2 as select * from emp where 1=2; --ddl
insert into emp2 select * from emp where deptno=10; --dml
select * from emp2;
--向emp2表插入emp表30部门的员工的员工编号
insert into emp2(empno) select empno from emp where deptno=30; --dml
--清空emp2 向emp2表的sal,deptno列 插入10部门的平均工资和人数
truncate table emp2; --ddl
insert into emp2(sal,deptno) select avg(sal),count(1) from emp where deptno=10;
create table abc(a number(10));
--清空emp2 向emp2表插入一条数据 :
--姓名 QUEEN 员工编号 7890 工资 4800 部门 10 职位 MANAGER 入职日期 今天
insert into emp2(ename,empno,sal,deptno,job,hiredate) values('QUEEN',7890,4800,10,'MANAGER',sysdate);
要求数据类型,字段数量一致
update 更新数据
语法：
update 表名 set 列1=值1[,列2=值2..] where 条件
--把 emp1表中的员工姓名更新为小写,职位更新为首字母大写
select * from emp1;
update emp1 set ename=lower(ename),job=initcap(job),sal=sal+500 where 1=1;
--把 emp1表中的员工姓名更新为大写,职位更新为大写
update emp1 set ename=upper(ename),job=upper(job) where 1=1;
--把emp1表中10部门工资增加500
update emp1 set sal=sal+500 where deptno=10;
--把emp1表中KING 入职日期更新为空
update emp1 set hiredate='' where ename='KING';
--把emp1中10部门员工职位改为小写 工资增加666
update emp1 set job=lower(job),sal=sal+666 where deptno=10;
--创建emp_1 数据同emp
create table emp_1 as select* from emp;
--更新10部门的工资翻番再翻番 名字转小写
select * from emp_1;
update emp_1 set sal=sal*2*2,ename=lower(ename) where deptno=10;
--更新20部门的工资减半再减半,名字转首字母大写
update emp_1 set sal=sal/2/2 ,ename=initcap(ename) where deptno=20;
--更新30部门的工资为原来的百分之一再三次方
update emp_1 set sal=power(sal/100,3) where deptno=30;
--所有人奖金加10000
update emp_1 set comm=nvl(comm,0)+10000 where 1=1;
删除数据 delete
语法:
delete from 表 [where 条件]
select * from emp_1;
--从 emp_1表中删除10部门数据
delete from emp_1 where deptno=20;
--从emp_1表中删除工资小于SCOTT工资的员工信息
delete from emp_1 where sal<(select sal from emp where ename='SCOTT');
--删除emp_1中1981年入职的员工信息
DELETE FROM emp_1 where to_char(hiredate,'yyyy')=1981;
--删除emp_1表中全部数据
delete from emp_1 where 1=1;
delete、drop、truncate区别
| delete | drop | truncate |
|---|---|---|
| DML | DDL | DDL |
| 表中数据 | 表 | 表中全部数据 |
| 可以加where条件 | 不可以 | 不可以 |
| 可以回滚 | 不能回滚 | 不能回滚 |
merge into 合并数据
--有则改之 无则加勉
语法:
merge into 表a --要更新的表
using 表b --参考表
on(条件) --on 和() 没有空格
when matched then
update set a.列=b.列/值....
when not matched then
insert[(a.列...)] values(b.列/值...)
--创建emp1020 和emp10部门 20部门数据一样
drop table emp1020;
create table emp1020 as select * from emp where deptno in(10,20);
--emp2030 和emp20 30部门数据一样
drop table emp2030;
create table emp2030 as select * from emp where deptno in(20,30);
--把 emp2030 表中20部门的员工姓名更新为小写
update emp2030 set ename=lower(ename) where deptno=20;
commit;
select * from emp1020;
select * from emp2030;