Oracle数据库学习三
5.DML
DML 语句 语句操作语言
INSERT
UPDATE
DELETE
MERGE
INSERT 方法:
按顺序列插入
按指定列插入
default值插入
子查询(代替values)结果插入
替代变量插入
DML 语句 语句操作语言
INSERT
UPDATE
DELETE
MERGE
INSERT 方法:
按顺序列插入
按指定列插入
default值插入
子查询(代替values)结果插入
替代变量插入
SQL> create table t1(id number,name varchar2(20) default 'dxw');
表已创建。
SQL> select *from t1;
未选定行
SQL> insert into t1(id) values(1);
已创建 1 行。
SQL> select *from t1;
ID NAME
---------- --------------------------------------------------
1 dxw
SQL> insert into t1(id,name) values(1,'daivd');
已创建 1 行。
SQL> select *from t1;
ID NAME
---------- --------------------------------------------------
1 dxw
1 daivd
SQL> insert into t1 values(1,'xyz');
已创建 1 行。
SQL> select *from t1;
ID NAME
---------- --------------------------------------------------
1 dxw
1 daivd
1 xyz
SQL>
SQL> insert into t1 select empno,ename from emp;
已创建14行。
SQL> select *from t1;
ID NAME
---------- --------------------------------------------------
1 dxw
1 daivd
1 xyz
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
已选择17行。
SQL>
SQL> insert into t1(id,name) values(&a,&b);
输入 a 的值: 1000
输入 b 的值: qwer
原值 1: insert into t1(id,name) values(&a,&b)
新值 1: insert into t1(id,name) values(1000,qwer)
insert into t1(id,name) values(1000,qwer)
*
第 1 行出现错误:
ORA-00984: 列在此处不允许
SQL> insert into t1(id,name) values(&a,&b);
输入 a 的值: 1000
输入 b 的值: 'qwer'
原值 1: insert into t1(id,name) values(&a,&b)
新值 1: insert into t1(id,name) values(1000,'qwer')
已创建 1 行。
SQL> select *from t1;
ID NAME
---------- --------------------------------------------------
1 dxw
1 daivd
1 xyz
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
。。。。。。。。
update 更新表中数据
一定要加where子句 否则会应用到所有行
加了where子句时只更新where条件受影响的行
多列同时更新列与列之间用逗号分隔
更新日期列时要避免隐式转换 使用to_date()函数
使用defulat值更新依旧使用default关键字
子查询更新保证等号两边列的数量一致
SQL> create table t1 as select *from emp;
Table created.
SQL> select *from t1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> update t1 set sal=1000;
14 rows updated.
SQL> select *from t1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1000 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1000 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1000 500 30
7566 JONES MANAGER 7839 02-APR-81 1000 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1000 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 1000 30
7782 CLARK MANAGER 7839 09-JUN-81 1000 10
7788 SCOTT ANALYST 7566 19-APR-87 1000 20
7839 KING PRESIDENT 17-NOV-81 1000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1000 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1000 20
7900 JAMES CLERK 7698 03-DEC-81 1000 30
7902 FORD ANALYST 7566 03-DEC-81 1000 20
7934 MILLER CLERK 7782 23-JAN-82 1000 10
14 rows selected.
SQL> update t1 set sal=2000 where deptno=20;
5 rows updated.
SQL>
SQL> select *from t1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 2000 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1000 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1000 500 30
7566 JONES MANAGER 7839 02-APR-81 2000 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1000 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 1000 30
7782 CLARK MANAGER 7839 09-JUN-81 1000 10
7788 SCOTT ANALYST 7566 19-APR-87 2000 20
7839 KING PRESIDENT 17-NOV-81 1000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1000 0 30
7876 ADAMS CLERK 7788 23-MAY-87 2000 20
7900 JAMES CLERK 7698 03-DEC-81 1000 30
7902 FORD ANALYST 7566 03-DEC-81 2000 20
7934 MILLER CLERK 7782 23-JAN-82 1000 10
14 rows selected.
SQL> update t1 set (job,sal)=(select 'worker',3000 from dual) where deptno=30;
6 rows updated.
SQL> select *from t1 order by deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 1000 10
7839 KING PRESIDENT 17-NOV-81 1000 10
7934 MILLER CLERK 7782 23-JAN-82 1000 10
7566 JONES MANAGER 7839 02-APR-81 2000 20
7902 FORD ANALYST 7566 03-DEC-81 2000 20
7876 ADAMS CLERK 7788 23-MAY-87 2000 20
7369 SMITH CLERK 7902 17-DEC-80 2000 20
7788 SCOTT ANALYST 7566 19-APR-87 2000 20
7521 WARD worker 7698 22-FEB-81 3000 500 30
7844 TURNER worker 7698 08-SEP-81 3000 0 30
7499 ALLEN worker 7698 20-FEB-81 3000 300 30
7900 JAMES worker 7698 03-DEC-81 3000 30
7698 BLAKE worker 7839 01-MAY-81 3000 30
7654 MARTIN worker 7698 28-SEP-81 3000 1400 30
14 rows selected.
子查询更新，使用子查询得到的结果对目标表进行更新，如果要更新多个字段，需要把这些字段用括号
括起来，并且用逗号隔开。
SQL> update myemp set (job,sal)=(select job,sal from emp where empno=7788);
已更新14行。
SQL> select *from myemp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH ANALYST 7902 1980-12-17 00:00:00 3500 20
7499 ALLEN ANALYST 7698 1981-02-20 00:00:00 3500 30
7521 WARD ANALYST 7698 1981-02-22 00:00:00 3500 30
7566 JONES ANALYST 7839 1981-04-02 00:00:00 3500 20
7654 MARTIN ANALYST 7698 1981-09-28 00:00:00 3500 30
7698 BLAKE ANALYST 7839 1981-05-01 00:00:00 3500 30
7782 CLARK ANALYST 7839 2016-05-25 00:00:00 3500 1000 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3500 20
7839 KING ANALYST 2016-05-25 00:00:00 3500 1000 10
7844 TURNER ANALYST 7698 1981-09-08 00:00:00 3500 30
7876 ADAMS ANALYST 7788 1987-05-23 00:00:00 3500 20
7900 JAMES ANALYST 7698 1981-12-03 00:00:00 3500 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3500 20
7934 MILLER ANALYST 7782 2016-05-25 00:00:00 3500 1000 10
已选择14行。
关联子查询更新
SQL> update myemp m set (job,sal,comm)=(select job,sal,comm from emp where empno=m.empno);
已更新14行。
SQL> select *from myemp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 900 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 2016-05-25 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3500 20
7839 KING PRESIDENT 2016-05-25 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 2016-05-25 00:00:00 1300 10
已选择14行。
delete 删除表数据
不加where 全部记录删除
加where只删除条件满足行
where条件中也可以使用子查询
delete t1
delete t1 where ....
SQL> delete from myemp where deptno=(select deptno from dept where loc='NEW YORK');
已删除3行。
SQL> SELECT *FROM MYEMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 900 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3500 20
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
已选择11行。
ORACLE就提供了merge来解决这种选择性执行DML语句
语法:
MERGE INTO table_name table_alias
USING (table|view|sub_query) alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
COL1=COL_VAR1,
COL2=COL_VAR2
WHERE expr
DELETE WHERE expr
WEHN NOT MATCHED THEN
INSERT (col1,col2,...)
VALUES (col_var1,col_var2,...);
INTO 被插入或更新的表
USING 参照表 插入或更新的数据从这个表获取
ON 条件
WHEN MATCHED THEN 条件成立判断
UPDATE SET 更新内容(不写表名) 而且建立关系的列不用用于更新
WEHN NOT MATCHED THEN 条件失败判断
INSERT VALUES 插入内容(不写表名)
SQL> create table myemp as select empno,ename,deptno,sal from emp where deptno=10;
表已创建。
SQL> select *from myemp;
EMPNO ENAME DEPTNO SAL
---------- ---------- ---------- ----------
7782 CLARK 10 2450
7839 KING 10 5000
7934 MILLER 10 1300
SQL> update myemp set sal=0;
已更新3行。
SQL> commit;
提交完成。
SQL> select *from myemp;
EMPNO ENAME DEPTNO SAL
---------- ---------- ---------- ----------
7782 CLARK 10 0
7839 KING 10 0
7934 MILLER 10 0
merge into myemp m
using emp e
on(m.empno=e.empno)
when matched then
update set m.sal=e.sal
where m.empno=7839
when not matched then
insert values(e.empno,e.ename,e.deptno,e.sal)
where e.deptno=20
SQL> select *from myemp;
EMPNO ENAME DEPTNO SAL
---------- ---------- ---------- ----------
7782 CLARK 10 0
7839 KING 10 5000
7934 MILLER 10 0
7369 SMITH 20 900
7566 JONES 20 2975
7788 SCOTT 20 3500
7876 ADAMS 20 1100
7902 FORD 20 3000
update后可以使用delete where子句
删除规则: 匹配delete条件并且匹配on条件，并且匹配update的where条件。
6.DCL
DCL 语句 数据控制语言
GRANT 授予
REVOKE 回收
DCL语言会在讲授权限控制时接触到更多权限和角色
权限 grant 权限 to user
revoke 权限 from user
7.TCL
TCL 事务控制 语句
什么是事务:用于确保数据的一致性,由一组相关的DML组成, 该组DML的操作要么全确认,要么全取消
比如银行转账业务 步骤一:从A账户减少300元,步骤二:向B账户增加300元.
为了确保总的金额不变 就要维持数据的一致性 步骤一和步骤二两个操作或者全确认 或者全取消
事务的开始语句:update insert delete select ... for update 都能触发一个事务的开启
事务的结束语句:commit提交 rollback回滚
未完成的事务可以撤销
未完成的事务,其他会话看不到结果,只能看到提交的结果
维护事务需要锁和回退段参与
事务四个特性
事务的原子性：事务中的所有操作作为一个整体，要么全部回退，要么全部提交
事务的隔离性：事务之间互相隔离，相互独立存在
事务的一致性：未提交的事务不被其他会话看到
事务的持久性：事务提交后，数据永久生效
事物控制语句
COMMIT
SAVEPOINT
savepoint t1
rollback to t1
ROLLBACK
事务的提交方法
1.手动提交
执行commit语句
2.自动提交
ddl,dcl会自动提交之前未结束的事务
exit｜disconnect 退出sqlplus会自动提交
3. 设置sqlplus里: set autocommit on|off
事务的撤销方法
1.手动撤销
执行rollback
2.自动撤销
网络或数据库崩溃
强制退出sqlplus 点击X按钮
commit 执行就是提交整个事务
rollback 用savepoint设置定点 可以回退到事务过程中的定点位置
8.数据库其他资源
(1).用户和权限
创建用户
CREATE USER user_name IDENTIFIED BY password;
DROP USER user_name [cascade];
ALTER USER user_name ACCOUNT [UN]LOCK;
什么是权限
权限就是用来控制特定的用户发送特定的SQL语句是否允许正确的执行
在多用户环境,DBA需要维护数据库访问的安全
以确保特定用户拥有特定的操作权限
数据库的权限分为
系统权限
对象权限
系统权限
大部分是针对对象的创建,删除,修改,审计等等的操作
查看系统都具备哪些权限
select DISTINCT PRIVILEGE from dba_sys_privs;
查看当前会话都具备哪些权限
select * from session_privs;
对象权限
授予特定用户对特定对象的权限
特定对象:表、视图、序列、过程、函数、程序包上。
角色
角色就是一组权限的集合
我们也可以自己建立包含自己拟定权限的角色
查看oracle给你定制了哪些角色
SQL> SELECT *FROM DBA_ROLES;
(2).视图
视图 view
命名的sql语句,存储在数据字典中
视图本身不包含数据(物化视图除外)，视图的数据来源于基表或其他视图（创建视图时的查询所关联的表）
视图优缺点
可以限制数据的访问
方便管理
增加了数据库的负担
查看视图的定义
user_views
视图分类
简单视图
只有单表
不包含去重,聚集,分组,函数,伪列,计算表达式
可以在视图上执行DML操作，这叫可更新视图
复杂视图
包含一个或多个表
可以包含函数和分组数据
不能进行DML操作基表
创建语法
create view view_name as select deptno,max(sal) from emp group by deptno;
DEPTNO MAX(SAL)
---------- ----------
30 2850
20 3000
10 5000
删除视图
drop view view_name;
强制选项
SQL> create or replace force view v1 as select empno,ename,sal,deptno from my_emp where deptno=10;
Warning: View created with compilation errors.
(3)序列
sequence 序列
流水号(整数)递增或递减
存在于library cache
通常为主键或唯一约束提供值
减少编写生成序列的程序代码量
比如QQ号的产生
语法
CREATE SEQUENCE seq_name
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE N | NOMAXVALUE}]
[{MINVALUE N | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE N|NOCACHE}];
取值方法
NEXTVAL 返回下一个值
CURRVAL 返回当前值
SQL> create sequence seq1;
Sequence created.
SQL> select *from user_sequences where sequence_name='SEQ1';
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ - - ---------- -----------
SEQ1 1 1.0000E+28 1 N N 20 1
SQL> SELECT SEQ1.NEXTVAL FROM DUAL;
NEXTVAL
----------
1
SQL> /
NEXTVAL
----------
2
SQL> /
NEXTVAL
----------
3
SQL> /
NEXTVAL
----------
4
SQL> SELECT SEQ1.CURRVAL FROM DUAL;
CURRVAL
----------
4
想要修改序列的当前值回溯的过去值 只能删除重新
drop sequence seq2;
create sequence seq2 start with 10;
修改序列的属性
alter sequence seq2 minvalue 1;
（4）约束
用来维护数据结构完整性的一种手段
可以是表级 也可以是列级
通过查询视图 user_constraints user_cons_columns
约束的五种类型
非空 not null
唯一 unique
检测 check 自定义
主键 primary key
外键 foreign key
非空约束
列的值不允许null值
可以在建表时指明
可以建完表后添加
可以指定名字 不指定系统会给定随机名
创建表时在字段类型后添加 not null关键字
SQL> create table t1 (id number not null,name varchar2(20) constraint t1_name_notnull not null);
Table created.
SQL> desc t1
Name Null? Type
-------------------------------------------------- -------- ----------------------------------
ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(20)
查看约束
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name='T1';
TABLE_NAME CONSTRAINT_NAME C STATUS
------------------------------ ------------------------------ - --------
T1 SYS_C005173 C ENABLED
T1 T1_NAME_NOTNULL C ENABLED
SQL>
查看约束作用于t1表哪一列
SQL> col OWNER for a10
SQL> col column_name for a16
SQL> col table_name for a10
SQL> col CONSTRAINT_NAME for a20
SQL> select * from user_cons_columns where table_name='T1';
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
---------- -------------------- ---------- ---------------- ----------
SCOTT T1_NAME_NOTNULL T1 NAME
SCOTT SYS_C005173 T1 ID
SQL> insert into t1 values(1,'SEKER');
1 row created.
对已经存在的表添加
SQL> alter table t1 add (mail varchar2(40));
Table altered.
SQL> desc t1
Name Null? Type
-------------------------------------------- -------- ------------------------------
ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(20)
MAIL VARCHAR2(40)
SQL> alter table t1 modify(mail constraint ct not null);
Table altered.
SQL> desc t1
Name Null? Type
-------------------------------------------- -------- ------------------------------
ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(20)
MAIL NOT NULL VARCHAR2(40)
唯一约束
列的数据只能是唯一的 不可重复
可以是单列,可以是组合列(就叫表级)
所以唯一性约束它可以是列级别,可以是表级别
但unique规定的列只有一列时可以在列级别定义
如果unique规定的列包含多列时只能在表级别定义
创建唯一约束 即自动创建索引
SQL> create table t1(id number unique,
name varchar2(10),
mail varchar2(20),
constraints my_un unique(name,mail))
;
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name='T1';
TABLE_NAME CONSTRAINT_NAME C
---------- -------------------- -
T1 SYS_C005210 U
T1 MY_UN U
SQL> select TABLE_NAME,CONSTRAINT_NAME,COLUMN_NAME from user_cons_columns where TABLE_NAME='T1';
TABLE_NAME CONSTRAINT_NAME COLUMN_NAME
---------- -------------------- ----------------
T1 MY_UN NAME
T1 MY_UN MAIL
T1 SYS_C005210 ID
SQL> select TABLE_NAME,INDEX_NAME,INDEX_TYPE from user_indexes where table_name='T1';
TABLE_NAME INDEX_NAME INDEX_TYPE
---------- ------------------------------ ---------------------------
T1 SYS_C005210 NORMAL
T1 MY_UN NORMAL
SQL> 索引自动创建 和约束同名
删除约束语法
SQL> alter table t1 drop constraint SYS_C0011129;
SQL> alter table t1 disable constraint SYS_C0011129;
表已更改。
主键约束
主键和唯一共同点:
都是唯一的 可以单列 也可以多列
都是自动创建索引
主键和唯一不同点:
unique 可以为空
primary key 不可以为空
一般来说 每个表都应该有自己的主键列 而且最好是单列的，一张表只能具有一个主键，可以是单列也可以是多列
SQL> create table t2 (id number constraint t2_PK_id primary key,name varchar2(10));
Table created.
外键约束
例如 emp.deptno 被 dept.deptno值包含
如果你删除dept中deptno=10
从数据变更角度来讲没问题 但他的业务逻辑出现了错误 没有部门哪来的员工呢??
这就叫数据的完整性
这就是外键的作用 参考主键来维护数据完整性
参考主键的值 外键列中的值被主键中的值所包含(子集)
同一张表可以创建多个外键列，例如：
SQL> create table t2(id number,name number,
foreign key(id) references emp(empno),
foreign key(name) references emp(empno));
同一个列可以创建多个外键约束，但是该列上的只能存在所有参考的主键列值的交集部分
外键约束既可以参考其他表，也可以参考本表的其他字段，例如：
SQL>alter table emp add constraint emp_mgr_fk foreign key(mgr) references emp(empno);
而且参考列一定是唯一或主键
否则会报: ORA-02270: no matching unique or primary key for this column-list
创建外键约束
SQL> create table my_dept as select * from dept;
SQL> alter table my_dept modify(deptno number primary key);
SQL> create table my_emp (
empno number,
ename varchar2(20),
deptno number,
constraint my_emp_fk_dno foreign key(deptno) references my_dept(deptno)
);
