目录
一、数据类型DML、DDL、DCL、DQL、TCL
DML: 数据操纵语言 Data Manipulation Language
- insert：增
- update：改
- delete：删
- merge：能够在一个 SQL 语句中对一个表同时执行 inserts 和 updates 操作
DDL:数据定义语言 Data Definition Language
- create：创建表、视图、索引、同义词和其他对象
- alter：修改表对象结构、属性、名称
- drop：删除数据库对象
- truncate：截断表
DCL: 数据控制语言 Data Control Language
- grant：授予用户权限
- revoke：收回用户权限
DQL:数据查询语言 Data Query Language
- select
TCL: 事务控制语言 Transaction Control Languag
- commit：提交事务
- rollback：回滚（撤销事务）
- savepoint：在会话中设置一个保存点，将来通过 rollback 语句可以回滚到该位置
二、DML
INSERT
inset into 表名（column，column，....） values（value1，value2，.....）
插入一行
*数值类型数据可以不用引号
*列名和值一一对应
insert into dept(deptno,dname,loc) values(50,'DEVELOPMENT','CQ');
如果不声明那些列要插入数据，则values中必须体现所有列：
insert into dept values(50,'DEVELOPMENT','CQ');
空值的插入：
1、 省略插入
insert into dept(deptno,dname) values(50,'DEVELOPMENT');
2、指定NULL
insert into dept(deptno,dname,loc) values(50,'DEVELOPMENT',NULL);
常见错误：
- 对not null列使用null
- 违反唯一约束
- 数据类型不匹配
- 值不在列约束范围内
从一张表中拷贝行：
- 不能使用values
- 子查询中的列要与insert子句中的列相匹配
例
create table dept1 as select * from dept where 1=2;
Table created.
select * from dept1;
no rows selected
insert into dept1 select * from dept where deptno=10;
1 row created.
select * from dept1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK插入日期：
- 可以使用to_date
例
insert into emp values (2296,'AROMANO','SALESMAN',7782, TO_DATE('2022-11-22','yyyy-mm-dd'),1300,null,10);
1 row created.
select * from emp where empno=2296;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
2296 AROMANO SALESMAN 7782 22-NOV-22 1300 10同时向多张表插入多条数据：
insert all
into 表1 values（）
into 表2 values（）
into 表3 values（）
.......
select ... from 表 where ...
例:向10部门和20号部门分别插入相关员工
insert all
when deptno = 10 then into emp10 values (empno,ename,deptno)
when deptno = 20 then into emp20 values (empno,ename,deptno)
select empno,ename,deptno from emp;
8 rows created.
SCOTT@prod>select * from emp10;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7782 CLARK 10
7839 KING 10
7934 MILLER 10UPDATE
update from 表名 set column1='value1',column2='value2',......columnn='valuen' where condition
update dept set loc='aab' where deptno=40;
多列子查询可以这样：
update emp set (job,deptno) = (select job,deptno from emp where empno=7499) where empno = 7698;
基于另一张表进行修改：
例：
update emp set deptno=(select deptno from emp where empno=7369) where empno=7196;改为空值:
1、set 属性=NULL
2、set 属性=''
where 后不能使用=NULL 、='' 、<>'' 、<>null，可以使用 is NULL 或者is NOT NULL
关联子查询更新：
例：
创建测试表：
create table emp1 as select empno,ename,deptno from emp;
alter table emp1 add(loc varchar2(10));
select * from emp1;
EMPNO ENAME DEPTNO LOC
---------- ---------- ---------- ----------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
update emp1 e set loc=(select loc from dept d where e.deptno=d.deptno);
select * from emp1;
EMPNO ENAME DEPTNO LOC
---------- ---------- ---------- ----------
7369 SMITH 20 DALLAS
7499 ALLEN 30 CHICAGO
7521 WARD 30 CHICAGODELETE
delete from 表名 where 条件
注：不加where则删除表中所有数据
补充：MERGE
使用原始表更新目标表数据
如果存在就更新（update），不存在就插入（insert）
例：
create table test1 as select * from dept where 1=2;
create table test2 as select * from dept;
insert into test1 values (10,'DBA','BEIJING');
insert into test1 values(20,'ORACLE','SHANGHAI');
commit;
select * from test1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 DBA BEIJING
20 ORACLE SHANGHAI
select * from test2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
merge into test1 t1 using test2 t2 on (t1.deptno = t2.deptno)
2 when matched then
3 update set t1.dname=t2.dname,t1.loc=t2.loc
4 when not matched then
5 insert (deptno,dname,loc) values (t2.deptno,t2.dname,t2.loc);
select * from test1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
40 OPERATIONS BOSTON
30 SALES CHICAGO上述如果使用子查询，需要分别执行update和insert
update test1 set (dname,loc)=(select dname,loc from test2 where test1.deptno=test2.deptno);
insert into test1 select * from test2 where deptno not in(select deptno from test1);
三、TCL
数据库事务：
以第一个DML语句作为开始
以如下行为作为结束：
- COMMIT或者ROLLBACK语句
- DDL或者DCL语句（自动提交）
- SQL developer或SQL Plus用户退出
- 系统崩溃
sqlplus异常中止时自动进行隐式回滚
事务ACID属性
任何关系型数据库都必须通过ACID测试
COMMIT
commit 保存从会话中 最后一次提交 之后对数据库所做的修改。commit 对数据库作出 永久 的变动，一旦提交之后，这些变动就 无法通过 rollback 语句撤销。显示提交：commit语句隐式提交：DDL或DCL语句、SQLPlus正常退出
执行commit或rollback前的数据库状态
- 改变前的数据状态是可恢复的
- 执行DML语句的用户可以用select查询之前的修改
- 其他用户不能看到当前用户做的改变，直到用户结束事务
- DML涉及到的行被锁定，其他用户不能修改
例：
update dept1 set loc='CQ' where deptno=10;
1 row updated.
select * from dept1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING CQ
新开session：
select * from dept1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
数据并没有改变
update dept1 set loc='SC' where deptno=10;
此时数据开启事务锁，处于一直等待中，当第一个session事务结束后，第二个事务DML开始运行commit提交之后的数据状态：
- 数据的改变保存在数据库中
- 以前的数据被覆盖
- 所以用户可以查看结果
- 受影响行锁定被解除，其他用户可进行操作
- 所有保存点（savepoint）被删除
ROLLBACK
rollback 撤销了执行该命令的用户在指定的会话中对数据库所做的修改。rollback 不会撤销已经提交过的修改。显示回滚：rollback语句隐式回滚：异常退出（如直接关闭session）、系统崩溃提交rollback后的数据库状态
- 数据更改被撤销
- 数据恢复到以前状态
- 数据锁被释放
SAVEPOINT
例
create table dept1 as select * from dept;
select * from dept1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
insert into dept1 values (50,'a',null);
savepoint stp1;
select * from dept1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 a
insert into dept1 values (60,'b',null);
savepoint stp2;
select * from dept1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 a
60 b
rollback to stp1;
select * from dept1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 a
rollback;
select * from dept1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON锁
DML操作时用到两种锁：
排它锁
只有一份，我加锁后，其他人不能加锁，只能等待；行级别，保护这行的数据，防止其 他会话修改这些行；只锁定修改的行。如：session1修改一行数据，session2修改此行会等待，但可以修改其他行。
共享锁
可以加多份；
表级别，保护表的结构，防止其他会话使用 DDL 语句修改表的定义。
如：session1 做修改时，session2 不能把表、字段删了。
select for update
例：
session1
select * from emp where empno=7788 for update;
session2
delete from emp where empno=7788;----会一直等待
select * from emp where empno = 7788 for update;-----无法添加
session1 ROLLBACK或commit后session2可操作死锁
例：当session1在等待session2结束，session2在等待session1结束就会造成死锁
例：
session1：
update emp set sal=9999 where empno=7788;
session2：
update emp set sal=8888 where empno=7566;
update emp set sal=7777 where empno=7788;
session1：
update emp set sal=5555 where empno=7566;
此时，session1第一条语句执行后事务开始未结束，
session2第二条语句执行时会等待session1结束，
此时session1再执行第二条语句时会等待session2的事务结束，
双方一直等待，死锁形成
扫一扫
140
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
