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

  1. 云栖社区>
  2. 博客>
  3. 正文

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

科技小能手 2017-11-13 15:29:00 浏览1131
展开阅读全文

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 )

4SELECT插入多行

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

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 Languagedml)

从无到有 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 51CTO博客,原文链接:http://blog.51cto.com/xjzhujunjie/869886

网友评论

登录后评论
0/500
评论
科技小能手
+ 关注