ORACLE DML语句

DML语句

标签（空格分隔）： 数据操纵语言

insert

INSERT INTO表（列1，列2……）VALUES（表达式1，表达式2……）；

INSERT INTO dept（deptno, dname, loc）VALUES（50，'NETWORK'，'BEIJING'）；

INSERT INTO dept VALUES（50，'NETWORK'，'BEIJING'）；

INSERT INTO emp（empno, ename, deptno, sal, hiredate）
VALUES（9999，'Hello'，30，1000，sysdate）；

INSERT INTO表1（列1，列2……）
SELECT列1，列2……FROM表2 WHERE条件表达式

INSERT INTO emp1（empno, ename, deptno, sal, hiredate）
SELECT empno, ename, deptno, sal, hiredate FROM emp
WHERE deptno=10 or deptno=20；

##delete

DELETE FROM表名 WHERE条件

DELETE FROM emp；

DELETE FROM emp WHERE deptno=30 AND sal＜1000；

DELETE FROM dept
WHERE deptno NOT in（SELECT distinct deptno FROM emp）；

##update

UPDATE表名 SET列1=表达式1，列2=表达式2……WHERE条件

SQL＞UPDATE emp
SET sal=sal*1.1，comm=nvl（comm，0）+100；

UPDATE emp
SET sal=sal*1.1，comm=nvl（comm，0）+100；
WHERE deptno=10 OR deptno=20 AND sal＞2000；

SQL＞UPDATE emp
SET sal=sal*1.1，comm=nvl（comm，0）+100
WHERE deptno=（SELECT deptno FROM emp WHERE ename='BLAKE'）；

##事务控制语句
事务具有四个属性

• 原子性（Atomicity）：事务要么全部执行，要么全部不执行，不允许部分执行。
• 一致性（Consistency）：事务把数据库从一个一致状态带入另一个一致状态。
• 独立性（Isolation）：一个事务的执行不受其他事务的影响。
• 持续性（Durability）：一旦事务提交，就永久有效，不受关机等情况的影响。
  ###隐式控制：
  事务在遇到一条DDL命令，如CREATE，或者遇到一条DCL命令，如GRANT，或者从SQLPlus正常退出，即使没有发出COMMIT或ROLLBACK命令，这个事务将被自动提交。如果从SQLPlus非正常退出或发生系统崩溃，那么系统将自动回滚事务。
  ###显式控制
  在事务的最后就要通过COMMIT命令提交事务，或者通过一条ROLLBACK命令回滚事务。

SQL＞INSERT INTO dept VALUES（60，'HHHHH'，'HHHHHH'）；
已创建1行。
SQL＞COMMIT；
提交完成。

SQL＞DELETE FROM dept WHERE deptno=60；
已删除1行。
SQL＞ROLLBACK；
回退已完成。

SQL＞DELETE FROM dept WHERE deptno=60；
已删除1行。
SQL＞SAVEPOINT a；
保存点已创建。
SQL＞DELETE FROM dept WHERE deptno=40；
已删除1行。
SQL＞ROLLBACK TO a；
回退已完成。
SQL＞COMMIT；

##DDL
DDL是“数据定义语言”（Data Defination Language）的缩写，它包含CREATE、ALTER、DROP、RENAME、TRUNCATE等命令，用来对数据库对象进行创建、修改、删除、重命名等操作。其中CREATE、ALTER和DROP命令的功能十分强大，几乎可以对所有的数据库对象进行管理，例如表、视图、索引、存储程序等。
###表的创建

CREATE TABLE表名（
列1数据类型，
列2数据类型，
……）；

CREATE TABLE student（
sno number（8），
sname char（8），
birthday date，
school varchar（40））；

CREATE TABLE表名
AS SELECT语句

CREATE TABLE emp_1
AS SELECT empno, deptno, sal
FROM emp
WHERE deptno=30；

SQL＞CREATE TABLE emp_2（empno_2，deptno_2，sal_2）
AS SELECT empno, deptno, sal
FROM emp
WHERE 1＜0；--只创建表结构而不复制表数据

• 模式（Schema）
  模式指的是一个用户所拥有的所有数据库对象的逻辑集合。在创建一个新用户时，同时创建了一个同名的模式，这个用户创建的所有数据库对象都位于这个模式中。用户在自己的模式中创建表，需要具有CREATE TABLE系统权限，如果需要在别人的模式中创建表，则需要具有CREATE ANY TABLE权限。在访问其他用户的数据库对象时，要指定对方的模式名称，例如，通过SCOTT.EMP引用SCOTT用户的emp表。
• 数据字典（Data dictionary）
  数据字典是一些视图，从这些视图中可以查看一些重要的系统系统数据，如数据库中的表、索引、权限、表空间等信息。这些视图是在创建数据库时自动创建的，它们的内容也是由数据库服务器自动维护的。用户可以查看以下几种形式的数据字典视图：
  • 以USER_开始的视图
  • 以ALL_开始的视图
  • 以DBA_开始的视图
    ##修改表结构
    ALTER命令可以使用若干个子句，通过这些子句可以完成修改表结构的操作。可以使用的子句包括ADD、DROP、MODIFY和RENAME等。

ALTER TABLE表名
ADD（列1，数据类型，
列2，数据类型，
……）；

ALTER TABLE student --
ADD（gender char（2）DEFAULT'男'，
address varchar2（50））；

如果一个表中已经有数据，这时增加一个列时，不能将该列约束为“非空（NOT NULL）”，因为我们不能一方面要求该列必须有数据，而另一方面又无法在增加列的同时向该列插入数据。例如，要向dept表中增加一个非空的列，语句的执行将出错：

ALTER TABLE dept
ADD（alias char（20）NOT NULL）
ERROR位于第1行：
ORA-01758：要添加法定（NOT NULL）列，则表必须为空

ALTER TABLE dept
ADD（alias char（20）default'NOT KNOWN'NOT NULL）；

ALTER TABLE表名DROP COLUMN列名

ALTER TABLE student DROP COLUMN gender；

ALTER TABLE表名
MODIFY（列1，新数据类型非空属性，
列2，新数据类型非空属性，
……）；

SQL＞ALTER TABLE student
MODIFY（gender char（4）NOT NULL，
address char（40））；

ALTER TABLE表名RENAME TO新表名

ALTER TABLE表名RENAME TO新表名

###其他DDL语句

DROP TABLE表名

DROP TABLE stu；

RENAME表名TO新表名；

RENAME stu TO stu_1；

TRUNCATE TABLE表名；

TRUNCATE命令的作用是删除表中的数据。与DELETE语句不同的是，TRUNCATE命令将删除表中的所有数据，不需要指定任何条件，而且数据被删除后无法再恢复。

TRUNCATE TABLE student；

等同于

DELETE FROM表名；
COMMIT；

##约束
###约束类型

• NOT NULL　非空约束：一个列上的值不能为空
• UNIQUE　唯一性约束：一个列上的数据必须唯一
• PRIMARY KEY　主键约束：主键列上的数据不能重复，并且不能为空
• FOREIFN KEY　外键约束：该列中的数据必须是另一个与之关联的表中的主键列中的数据
• CHECK　检查约束：一个列必须满足的条件
  在一个表上只能创建一个主键。当创建主键时，在主键列上将自动建立一个唯一性索引，索引的名字与约束的名字相同。
  ###创建约束

CREATE TABLE表名（
列1　数据类型　CONSTRAINT　约束名1　约束类型，
列2　数据类型　CONSTRAINT　约束名2　约束类型，
……）；

SQL＞CREATE TABLE student（
sno number（8）PRIMARY KEY，
sname char（8）NOT NULL，
gender char（2）CHECK（gender in（'男'，'女'）），
birthday date，
school varchar（40））；

CREATE TABLE student（--列级约束）
sno number（8）CONSTRAINT pk_sno PRIMARY KEY，
sname char（8）CONSTRAINT nn_sname NOT NULL，
gender char（2）CONSTRAINT gen_check CHECK（gender in（'男'，'女'）），
birthday date，
school varchar（40））；

CREATE TABLE　表名（
列1　数据类型，
列2　数据类型，
……
CONSTRAINT　约束名1　约束类型（列名），
CONSTRAINT　约束名2　约束类型（列名），
……）；

CREATE TABLE student（
sno number（8），
sname char（8）CONSTRAINT nn_sname NOT NULL，
gender char（2），
birthday date，
school varchar（40），
CONSTRAINT pk_sno PRIMARY KEY（sno），--表级约束
CONSTRAINT gen_check CHECK（gender in（'男'，'女'））
）；

如果在列级定义外键约束，定义的格式为：

CONSTRAINT约束名REFERENCES表名（列名）

CONSTRAINT fk_deptno REFERENCES dept（deptno）```

CONSTRAINT约束名FOREIGN KEY（外键列）REFERENCES表名（列名）

约束作为一种附加在表上的数据库对象，它的信息也被记录在数据字典中。与约束有关的数据字典有两个，一个是user_constraints；另一个是user_cons_columns。其中在数据字典user_constraints中记录当前用户所拥有的约束的信息，如约束名、约束类型、约束所在的表、约束的状态等。如果是外键，还记录了与之关联的主键名称

SELECT constraint_name AS名称，constraint_type AS约束类型，status AS状态
FROM user_constraints
WHERE table_name='STUDENT'；--查询表上的约束信息

SQL＞SELECT constraint_name, table_name, column_name
FROM user_cons_columns WHERE table_name='STUDENT'；--查询约束施加在哪个列上

###创建表之后指定约束

ALTER TABLE表名ADD（CONSTRAINT约束名约束类型（列名））

ALTER TABLE student
ADD（CONSTRAINT pk_sno PRIMARY KEY（sno），
CONSTRAINT gen_check CHECK（gender in（'男'，'女'）））；

ALTER TABLE表名MODIFY（列名CONSTRAINT约束名NOT NULL）；--NOT NULL只能通过ADD添加

ALTER TABLE student MODIFY（sname CONSTRAINT nn_sname NOT NULL）；

###约束维护
如果希望去掉表上的某个约束，可以将其删除，也可以使其无效。约束是不能被修改的，如果在表上已经建立了一个约束，现在希望把它改为另一类型的约束，或者希望把它施加在另一个列上，只能先将这个约束删除，然后重新创建。

ALTER TABLE表名DROP CONSTRAINT约束名；

ALTER TABLE student DROP CONSTRAINT gen_check；

如果要删除一个主键约束，首先要考虑这个主键列是否已经被另一个表的外键列关联，如果没有关联，那么这个主键约束可以被直接删除，否则不能直接删除。要删除主键约束，必须使用CASCADE关键字，连同与之关联的外键约束一起删除。删除主键的ALTER命令语法格式为：

ALTER TABLE表名DROP CONSTRAINT主键约束名CASCADE；

ALTER TABLE dept DROP CONSTRAINT pk_dept CASCADE；

在表中建立主键约束或UNIQUE约束时，在相关的列上将自动建立唯一性索引。当从表中删除主键约束或UNIQUE约束时，与它们相关的索引也被一起删除。
如果一个表被删除了，那么依附于它的约束也就没有意义了，这个表上的约束也将被一起删除。
如果希望一个约束暂时不起作用，可以使其无效。使约束无效的操作是通过ALTER命令的DISABLE子句实现的。

ALTER TABLE表名DISABLE CONSTRAINT约束名；

ALTER TABLE student DISABLE CONSTRAINT nn_sname；

SELECT constraint_name AS约束名，constraint_type AS约束类型，status AS状态
FROM user_constraints
WHERE table_name='STUDENT'；--查询约束状态

ALTER TABLE表名ENABLE CONSTRAINT约束名；--使约束生效

ALTER TABLE student ENABLE CONSTRAINT nn_sname；