Oracle 10g数据库基础之基本DDL和DML语句

Oracle 10g数据库基础之基本DDL和DML语句

--资料参考：张烈 张建中《数据库管理员培训讲义》

表的基本操作

表有名称。

表由行和列组成

表是存放数据的最基本对象

我们将一般的表叫做heap table（堆表），其含义为杂乱无章的存储数据，堆表是数据库的重要组织

形式。它有别于索引组织表和cluster表。

 表的名称规则

标准ASCII码可以描述

字母开头

30个字母内

不能是保留字

可以包含大小写字母，数字，_,$,#

不能和所属用户的其它对象重名。千万不要使用汉语做表和列的名称，因为汉语是ascii码所不能描述

的，ORACLE的核心是ASCII编写的，你使用汉语只是一时痛快，后患无穷。

Select object_name,object_type from user_objects;

user_objects当前用户所拥有的所有对象。不包含你建立的public对象。

select table_name from user_tables;

user_tables你自己的表，你有一切的权利。你所拥有的表。

select * from tab;

tab你所拥有的表和视图，显示的较简洁，列较少。

查询数据

SQL语言中最主要、最核心的部分是它的查询功能即SELECT语句。查询语言用来对已经存在于数据库中的数据按照特定的组合、条件表达式或次序进行检索。SELECT语句的完整语法如下：

SELECT [ALL|DISTINCT]] select_list

[INTO new_table_]

FROM table_source

[WHERE search_condition]

[GROUP BY group_by_expression]

[HAVING search_condition]

[ORDER BY order_expression [ASC|DESC]]

插入数据

INSERT语句可以实现往数据库表中插入记录。有两种方法可以向一张表中插入数据。一种是用VA L U E S选择，直接给各字段赋值；二是通过一条查询语句，把从其他表或视图中选取的数据插入。INSERT语句的完整语法如下：

I N S E RT [INTO ]

{ TABLE_NAME | VIEW_NAME}[（C O L U M N _ L I S T）]

{ VALUES| VALUES_LIST | SELECT_STAT E M E N T }

1．插入所有值

当向表中所有列都插入新数据时，可以省略列名表，但是必须保证VALUES后的各数据项位置同表定义时顺序一致。例如：

INSERT AUTHORS VALUES ( '123-45-6789 ','YANG','ZH','900555_1212')

2．插入部分值

当向表中插入部分数据时，应在列名表处写出各个字段的顺序。例如：

INSERT PUBLISHERS (PUB_ID,PUB_NAME) VALUES('9975','UNBOUND PRESS')

3．插入缺省值选项

方法一：为所有列插入缺省值

语法：INSERT TABLENAME DEFAULT VALUES

这种语法要求所有列必须具有IDENTITY属性、TIMESTAMP类型、允许NULL或赋有DEFAULT值。

方法二：为列插入缺省值

该列必须具有TIMESTAMP类型、允许NULL或赋有DEFAULT值。

INSERT shippers(companyname,phone)

VALUES ('yangzh coffee co.', DEFAULT )

4．用SELECT插入多行

可以用查询语句从其他表或视图中选出要插入的数据，并将它插入到表中。语法如下：

INSERT TABLE_NAME

SELECT COLUMN_LIST

FROM TABLE_LIST

WHERE SEARCH_CONDITIONS

修改数据

UPDATE语句实现更新数据库。其完整语法如下：

UPDATE { TABLE_NAME | VIEW_NAME}

SET [ { TABLE_NAME | VIEW_NAME}]

{COLUMN_LIST | VARIABLE_LIST }=expression

[WHERE CLAUSE]

例如：

update discounts set discount=discount+0.10 where lowqty>=100

删除数据

DELETE实现删除数据库表中的记录，其完整语法如下：

DELETE [FROM] TABLE_NAME WHERE SEARCH_CONDITIONS

例如：

delete sales where datediff(year,ord_date,getdate())>=3

实验1：建立简单的表，并对表进行简单ddl操作

该实验的目的是掌握简单的ddl语法.学习建立表,修改表,验证表,删除表

Create table语句建立表

要指明表的名称

列的名称

列的数据类型

列的宽度

是否有默认值

常见的数据类型

Char(n)定长

Varchar2(n)变长，最大到4000

Number(p,s)

Date

Long

Lob

raw

在现有表的基础上建立表

SQL> Create table t2

  2  as select ename name,sal salary from emp;

Table created

SQL>

当t2诞生时就会有子查询中所查出的数据。

如果想改变列的名称，请用别名。

如果不想要数据，只建立表结构，请加一个假条件。

SQL> Create table t3 (c1,c2,c3) as

  2  Select ename,empno,sal from emp where 9=1;

Table created

SQL>

修改表结构

如果列为null，可以随便修改列的类型和宽度。

如果有数据，修改会受到限制。但不会破坏数据。

如果不改变数据类型，只改变宽度的话加大是可以的。

SQL> alter table t1 modify(name char(4));

Table altered

SQL> alter table t2 modify(name char(8));

Table altered

修改表的名称, 必须是表的owner才可以修改表名称

SQL> rename t1 to t_1;

Table renamed

SQL> desc t_1;

Name     Type         Nullable Default Comments

-------- ------------ -------- ------- --------

NAME     CHAR(4)      Y                        

SALARY   NUMBER(5)    Y        0               

CONTENT  CHAR(4 CHAR) Y                        

HIREDATE DATE         Y                        

SQL>

修改列的名称（10g才可以）

SQL> alter table t3 rename column c1 to name;

Table altered

表注释

SQL> comment on table emp is 'employee table';

Comment added

SQL> select COMMENTS from user_tab_comments where table_name='EMP';

COMMENTS

--------------------------------------------------------------------------------

employee table

SQL>

列注释

SQL> select COMMENTS from user_tab_comments where table_name='EMP';

COMMENTS

--------------------------------------------------------------------------------

employee table

SQL>

SQL>

SQL> COMMENT ON COLUMN EMP.SAL IS '员工工资';

Comment added

SQL> select COMMENTS from user_col_comments

  2  where table_name='EMP' AND column_name='SAL';

COMMENTS

--------------------------------------------------------------------------------

员工工资

SQL>

删除（丢弃）表

SQL> Select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

DEPT                           TABLE  

EMP                            TABLE  

BONUS                          TABLE  

SALGRADE                       TABLE  

T3                             TABLE  

T2                             TABLE  

T_1                            TABLE  

7 rows selected

SQL> Drop table t2;

Table dropped

并没有将表真的删除，只是改了名称。

SQL> Select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

DEPT                           TABLE  

EMP                            TABLE  

BONUS                          TABLE  

SALGRADE                       TABLE  

T3                             TABLE  

BIN$uHlWiyacnb/gQKjAWGURmg==$0 TABLE

T_1                            TABLE  

7 rows selected

SQL>

显示回收站的信息(在oracle服务器上，不要使用客户端)

SQL> show recyclebin;

ORIGINAL NAME  RECYCLEBIN NAME        OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ -------------------

T2          BIN$uHlWiyacnb/gQKjAWGURmg==$0 TABLE            2012-05-18:15:01:42

SQL> SELECT * FROM USER_RECYCLEBIN;

 

OBJECT_NAME                    ORIGINAL_NAME               OPERATION

------------------------------ -------------------------------- ---------

TYPE                    TS_NAME                    CREATETIME

------------------------- ------------------------------ -------------------

DROPTIME              DROPSCN PARTITION_NAME               CAN CAN

------------------- ---------- -------------------------------- --- ---

   RELATED BASE_OBJECT PURGE_OBJECT SPACE

---------- ----------- ------------ ----------

BIN$uHlWiyacnb/gQKjAWGURmg==$0 T2                         DROP

TABLE                 USERS                  2012-05-18:15:01:11

2012-05-18:15:01:42  493680                              YES YES

     52504 52504        52504           8

将回收站的表还原

SQL> FLASHBACK TABLE t2 TO BEFORE DROP;

 

Flashback complete.

还原表的同时修改表的名称。

FLASHBACK TABLE T2 TO BEFORE DROP RENAME TO TT2;

清空回收站内指定的表

PURGE TABLE T2;

清除当前用户的回收站，不会影响其它用户的回收站

PURGE RECYCLEBIN;

绕过回收站，彻底的删除表，在10G前是没有回收站的，就是彻底的删除。回收站内没有的表是不容易恢复的，我只能取备份来恢复了。

Drop table t2 PURGE;

SQL> show recyclebin;

SQL> drop table t3;

Table dropped.

SQL> show recyclebin;

ORIGINAL NAME  RECYCLEBIN NAME        OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ -------------------

T3          BIN$wEsyWUQxfzngQKjAWGUZqg==$0 TABLE         2012-05-18:15:06:51

SQL> PURGE RECYCLEBIN;

Recyclebin purged.

SQL> show recyclebin;

SQL>

知识点

建立表

修改表

注释表

改名称

丢弃表

恢复丢弃的表

初步认识数据字典

数据操作语言dml

Data Manipulation Language（dml)

从无到有 insert into

数据变化 update set

删除数据 delete where

表的融合 merge into

实验2：dml语句，插入删除和修改表的数据

该实验的目的是掌握DML语法.插入删除和修改表中的数据.

insert into语句

insert into语句

Insert into t1(c1,c2,c3) values(v1,v2,v3);

要点关键字写全，列的个数和数据类型要匹配。

这种语法每次只能插入一行。

可以使用函数

Insert into t1(c2) values(sysdate);

将当前的日期插入。

隐式插入null

在插入中没有列出的列，就会被插入NULL，如果该列有DEFAULT值，那么就插入默认值。

Insert into dept(deptno) values(50);

其中dname,loc没有说明，都为null.

显式插入null

明确的写出该列的值为NULL

Insert into dept values(60,null,null);

Insert into dept(loc,dname,deptno) values(null,null,70);

日期和字符串

日期格式敏感

当插入的列为日期的时候，最好强制转化为日期类型，默认的转换在环境变化的时候会报错。

Insert into t3(hiredate) values(to_date('080599 ' ,'mmddrr');

字符串大小写敏感

Insert into t2(c1) values('BEIJING ');

Insert into t2(c1) values('beijing ');

子查询插入

不加values关键字，一次可以插入多行

列的类型和位置要匹配

Insert into d1 select * from dept;

Insert into emp2 select * from emp where deptno=30;

Update 语句

修改表中的数据

Update emp set sal=sal+1;

Update emp set sal=2000 where empno=7900;

Update emp set comm=null where deptno=30;

用子查询来更新

SQL> create table emp2 as select * from emp;

 

Table created

建立EMP2表，和EMP表的结构，数据都相同

SQL> alter table emp2 add(dname varchar2(10));

 

Table altered

将emp2表结构修改，增加一列。该列的值为null

SQL> update emp2 set dname=(select dname from dept where dept.deptno=emp2.deptno);

 

14 rows updated

使用相互关联子查询来更新emp2表的dname列。

使用default值进行表的修改

SQL> create table t1(c1 number(2) default 10,c2 char(10) default 'bj');

Table created

SQL> insert into t1(c1)values(20);

1 row inserted

SQL> select * from t1;

 C1 C2

--- ----------

 20 bj

SQL> update t1 set c1=default;

1 row updated

SQL> select * from t1;

 C1 C2

--- ----------

 10 bj

SQL>

如果没有指定default的值，那么为null值。

Delete删除行

Delete t1;--所有的行都删除。

Delete emp2 where sal>2000;

将符合条件的行删除

事务的概念和事务的控制

该实验的目的是了解事务的概念,提交回退和控制事务.

Transaction事务的概念

开始：第一个dml语句

结束：commit或者rollback

未完成的事务可以撤消

未完成的事务，其它会话看不到结果，只能看到已经提交的结果。

维护事务需要锁和回退段的参与

提交事务commit

1. 手工直接提交commit

2.自动提交

ddl,dcl语句

exit退出sqlplus

3.提交后

事务结束

释放锁和回退

其它用户可以看到结果，修改过的结果

撤消事务rollback

1. 手工直接撤消rollback

2.自动撤消

网络或数据库崩溃

强制退出sqlplus

3.撤消后

事务结束

修改前的数据恢复了

释放锁和回退

其它用户可以看到结果，未修改的结果

实验3：在表上建立不同类型的约束

约束就是指对插入数据的各种限制，例如：人员的姓名不能为空，人的年龄只能在0~150岁之间。约束可以对数据库中的数据进行保护。约束可以在建表的时候直接声明，也可以为已建好的表添加约束。

约束(constraint)

保证数据的有效

保证数据的安全

可以自己书写代码

使用触发器

使用oracle提供的五类约束

该实验的目的是掌握oracle提供的五种约束.

 Not null

定义在表的列上，表明该列必须要有值，不能为null

可以在建立表的时候说明

也可以在表建立后修改为not null

可以给约束指定名称。

如果不指定名称，数据库会给一个系统自动指定名称，SYS_C#######

User_constraints,user_cons_columns可以查看到约束的信息

建立表的时候指定not null约束

SQL> CREATE TABLE person

  2  (

  3  pid NUMBER ,

  4  name VARCHAR(30) NOT NULL

  5  ) ;

Table created

SQL> -- 插入数据

SQL> INSERT INTO person(pid,name) VALUES (11,'张三');

1 row inserted

SQL> INSERT INTO person(pid) VALUES (12);

INSERT INTO person(pid) VALUES (12)

ORA-01400: cannot insert NULL into ("SCOTT"."PERSON"."NAME")

SQL> -- 错误的数据，会受到约束限制，无法插入

SQL> create table t1 (name char(9) not null,

  2  telenum char(8) constraint t1_tele_nl not null);

Table created

SQL> desc t1;

Name    Type    Nullable Default Comments

------- ------- -------- ------- --------

NAME    CHAR(9)                          

TELENUM CHAR(8)                           

建立表的时候指明非空约束，一个是系统命名，一个是我们命名。

SQL> select CONSTRAINT_NAME,CONSTRAINT_type,TABLE_NAME,

column_name from user_constraints natural join user_cons_columns;

我们通过数据字典来验证我们的实验结果。

建立表后指定not null约束。要使用modify语法。你不指定名称，数据库自己命名。

SQL> drop table t1;

Table dropped

SQL> create table t1 as select * from dept;

Table created

SQL> alter table t1 modify(dname not null);

Table altered

SQL> desc t1;

Name   Type         Nullable Default Comments

------ ------------ -------- ------- --------

DEPTNO NUMBER(2)    Y                         

DNAME  VARCHAR2(14)                          

LOC    VARCHAR2(13) Y

唯一约束UNIQUE

列的值不能重复

可以为NULL

是用索引来维护唯一的

索引的名称和约束的名称相同

建立表的时候指定UNIQUE约束

SQL> DROP TABLE person ;

Table dropped

SQL> CREATE TABLE person

  2  (

  3  pid NUMBER PRIMARY KEY NOT NULL ,

  4  name VARCHAR(30) NOT NULL ,

  5  tel VARCHAR(50) UNIQUE

  6  ) ;

Table created

SQL>

SQL> -- 插入数据

SQL> INSERT INTO person(pid,name,tel) VALUES (11,'张三','1234567');

1 row inserted

SQL> -- 电话重复了

SQL> INSERT INTO person(pid,name,tel) VALUES (12,'李四','1234567');

INSERT INTO person(pid,name,tel) VALUES (12,'李四','1234567')

ORA-00001: unique constraint (SCOTT.SYS_C005400) violated

SQL>

建立表之后指定UNIQUE约束

SQL> create table t1 as select * from dept;

Table created

SQL> alter table t1 add constraint u_dname unique (dname);

Table altered

Check检测约束

条件约束，插入的数据必须满足某些条件

例如：人员有年龄，年龄的取值只能是0~150岁之间

SQL> DROP TABLE person ;

Table dropped

SQL> CREATE TABLE person

  2  (

  3  pid NUMBER PRIMARY KEY NOT NULL ,

  4 

  4  name VARCHAR(30) NOT NULL ,

  5  tel VARCHAR(50) NOT NULL UNIQUE ,

  6  age NUMBER CHECK(age BETWEEN 0 AND 150)

  7  ) ;

Table created

SQL> -- 插入数据

SQL> INSERT INTO person(pid,name,tel,age) VALUES (11,'张三','1234567',30);

1 row inserted

SQL> -- 年龄的输入错误

SQL> INSERT INTO person(pid,name,tel,age) VALUES (12,'李四','2345678',-100);

INSERT INTO person(pid,name,tel,age) VALUES (12,'李四','2345678',-100)

ORA-02290: check constraint (SCOTT.SYS_C005405) violated

SQL>

Primary key 主健约束

一个表只能有一个主健

主健要求唯一并且非空

可以是联合主健，联合主健每列都要求非空

主健能唯一定位一行,所以主键也叫逻辑rowid

主健不是必需的，可以没有

主健是通过索引实现的

索引的名称和主健名称相同

SQL> DROP TABLE person ;

Table dropped

SQL> CREATE TABLE person

  2  (

  3  pid NUMBER PRIMARY KEY ,

  4  name VARCHAR(30) NOT NULL

  5  ) ;

Table created

SQL> -- 插入数据

SQL> INSERT INTO person(pid,name) VALUES (11,'张三');

1 row inserted

SQL> -- 主键重复了

SQL> INSERT INTO person(pid,name) VALUES (11,'李四');

INSERT INTO person(pid,name) VALUES (11,'李四')

ORA-00001: unique constraint (SCOTT.SYS_C005409) violated

SQL>

SQL> --表建立后指定自命名的主健

SQL> drop table t1;

Table dropped

SQL>

SQL> create table t1(mail char(8) ,name char(8));

Table created

SQL> alter table t1 add constraint pk_t1_mail primary key (mail) ;

Table altered

SQL> -- 插入数据

SQL> INSERT INTO t1 VALUES (11,'张三');

1 row inserted

SQL> -- 主键重复了

SQL> INSERT INTO t1 VALUES (11,'里德');

INSERT INTO t1 VALUES (11,'里德')

ORA-00001: unique constraint (SCOTT.PK_T1_MAIL) violated

foreign key外健

指定在表的列上

引用本表其它列，或其它表的其它列

被引用的列得有唯一约束或者主健约束，因为引用的是索引的键值，而不是真正的表。

目的是维护数据的完整性

核心是一列是另外一列的子集，null除外

建立主健，建立一个外健来引用主健

SQL> --建立主健，建立一个外健来引用主健

SQL>

SQL> create table d as select * from dept;

Table created

SQL> create table e as select * from emp;

Table created

SQL>

SQL> alter table d add constraint pk_d primary key (deptNO) ;

Table altered

SQL> alter table e add constraint fk_e foreign key (deptno) references d(deptno) ;

Table altered

SQL> --违反约束

SQL> delete d where deptno=10;

delete d where deptno=10

ORA-02292: integrity constraint (SCOTT.FK_E) violated - child record found

ORA-02292: 违反完整约束条件 (SCOTT.FK_E) - 已找到子记录

SQL> update e set deptno=50;

update e set deptno=50

ORA-02291: integrity constraint (SCOTT.FK_E) violated - parent key not found

ORA-02291: 违反完整约束条件 (SCOTT.FK_E) - 未找到父项关键字

SQL>

SQL> --建立被级连的外健

SQL>

SQL> alter table e drop constraint FK_E;

Table altered

SQL> alter table e add constraint fk_e foreign key (deptno)

  2  references d(deptno) on delete set null;

Table altered

SQL> --父表的值被删除，子表的相关列自动被赋予NULL值。

SQL>

SQL> alter table e drop constraint FK_E;

Table altered

SQL> alter table e add constraint fk_e foreign key (deptno)

  2  references d(deptno) on delete cascade;

Table altered

SQL> --父表的值被删除，子表的相关行自动被删除。

SQL>

删除约束

任何约束都可以用约束名称来删除

Alter table ### drop constraint ****;

因为主健只能有一个，所以删除主健约束的时候也可以

Alter table ### drop primary key;

如果主健和唯一性约束被删除，自动建立的索引也会同时被清除。

Not Null约束也可以用alter table modify来删除。

删除有外健引用的主健或唯一约束的时候，外健也要被级连删除。

Alter table ### drop primary key cascade;

如果不加cascade，你删不了，报有外键在使用，不能删除。

按约束的名称来删除约束（可以删除各种约束）

Alter table t1 drop constraint sys_c03033;

非空约束的第二种删除方式

alter table t1 modify (name not null);

alter table t1 modify (name null);

启用约束

ALTER TABLE book enable CONSTRAINT person_book_pid_fk ;

禁用约束

ALTER TABLE book disable CONSTRAINT person_book_pid_fk ;

《完》

--xjzhujunjie

--2012/05/20