数据库基础(一)

本文档初步介绍了，建表、修改表、向表中插入数据、更新数据、检索数据、删除数据、序列以及索引，适合初学数据库的人员。

本资料主要正对Oracle数据库的SQL，其他数据库可以参考，但在细节上存在差异。

 

一、创建表

1.      建表的简化语法说明：

在数据库中，可以使用createtable 语句创建表。create table 语句的简化语法如下：

CREATE [GLOBALTEMPORARY] TABLE table_name

(

Column_name type [CONSTRAINT constraint_def DEFAULT default_exp]

  [,column_name type [CONSTRAINT constraint_def DEFAULT default_exp]…]

) [ON COMMIT {DELETE | PRESERVE} ROWS]  TABLESPACE tab_space;

 

2.      语法中关键字说明如下：

1)       GLOBAL TEMPORARY说明该表的行都是临时的，这种表就称为临时表。临时表中的行是特定于某个用户会话的，行的有效期由ONCOMMIT子句指定。

2)       table_name指定要分配给该表的名称。

3)       column_name 指定要分配给某个列的名称。

4)       type 指定某个列的类型。

5)       constraint_def 指定对某个列的约束。

6)       default_exp 指定一个表达式，用来为某个列赋予默认值。

7)       ON COMMIT 控制临时表中行的有效期。 DELETE说明这些行在事务的末尾要被删除。

8)       PRESERVE 说明这些行在用户会话的末尾要被删除。如果对临时表没有指定ON COMMIT选项，那么默认值是DELETE。

9)       tab_space 为该表指定表空间。如果没有指定表空间，该表就被存储在该用户的默认表空间中。

 

3.      建立普通表举例

如： create tablebasetab

(

  msisdn varchar2(21)  not null  primary key,

  imsi  varchar2(16) null,

  paytype number null default 0

)

 

4.      建立临时表

所谓临时表就是在一个事务中，要来保存数据的，退出事务，数据将被清空。

如： create globaltemporary table basetab_temp

(

   msisdnvarchar2(14)

) on commit preserve rows;

如果向表basetab_temp中插入一条数据，然后断开连接，再连接后，查询该表会发现该表为空。

 

二、修改表

在我们将表建好后，发现表结构需要修改、后来需要增加新列或需要加约束等，这时我们可以使用ALTERTABLE语句对表进行修改。ALTER TABLE语句可以执行以下任务：

添加、修改或删除列。

添加或删除约束。

启动或禁用约束。

1.      添加列

如：

1)       向basetab中增加一个20个变长字符列name

alter table basetab add name varchar2(20);

2)       向basetab增加number型的low_num和high_num列默认值都为0

alter tablebasetab add low_num number(3)  default 0;

alter tablebasetab add high_num number(3) default 0;

2.      虚拟列

虚拟列在定义好后，不需要对该列插入数据，通过现有的列计算后的值填进去。

如：

在basetab中增加一列mid_num，该列是通过low_num和high_num的平均值得到。

alter table basetab add(mid_num as ((low_num + high_num)/2));

3.      修改列

1)       修改basetab中msisdn的长度为15

alter table basetab modify msisdn varchar2(15);

说明：只有在表中没有任何行或所有列都为空值时才可以减小列的长度。

2)       修改basetab中的数字列low_num的精度为5

alter table basetab modify low_num number(5);

3)       修改basetab中msisdn列的数据类型为字符型定长14

alter table basetab modify msisdn char(14);

4)       修改basetab中high_num列的默认值为100

alter table basetab modify high_num default 100;

5)       删除basetab中high_num列

alter table basetab drop column high_num;

6)       限制basetab中high_num列值必须大于等于0

alter table basetab add constraint basetab_hig_num_ck check(high_num>=0);

7)       限制basetab中msisdn列不为空

alter table basetab modify msisdn constraint basetab_msisdn_nn notnull;

8)       添加foreign key约束

    foreign key是约束两张表，如果在subscribeinfo建立msisdn关联basetab的外键，则subscribeinfo中msisdn必须在basetab中存在。

    在subscribeinfo建立msisdn关联basetab的外键语句如下：

alter table subscribeinfo add constraint sub_fk msisdn referencesbasetab(msisdn);

带foreign key 约束的 on delete cascade 意思就是当删除主表中的数据是，关联表中的数据一起被删除。如：

alter table subscribeinfo add constraint sub_fk msisdn referencesbasetab(msisdn) on delete cascade;

带foreign key 约束的 on delete null意思是删除主表中的数据时，关联表的外键列将为空，如：

alter table subscribeinfo add constraint sub_fk msisdn referencesbasetab(msisdn) on delete null;

9)       将basetab中msisdn设置为唯一值

alter table basetab add constraint base_uq unique(msisdn);

10)    删除basetab中的唯一约束

alter table basetab drop constraint base_uq;

11)    禁用basetab中对hig_num大于等于0的约束

alter table basetab disable constraint basetab_hig_num_ck;

12)    启用basetab中对hig_num大于等于0的约束

alter table basetab enable constraint basetab_hig_num_ck;

1，  将表basetab_exp重命名为basetab_nn表

rename basetab_exp to basetab_nn;

2，  截断表

截断表即将表中数据清空，在截断过程中不会undo日志，所有截断不会被会滚。如：

truncate table basetab;

3，  删除basetab_nn表

drop table basetab_nn;

 

三、插入数据

我们在将表建立好后，需要将我们的数据插入到表中，可以使用insert语句。Insert可以往表中插入单行数据，也可以通过子查询将一张表的多行数据插入到另一张表中，还可以进行多表插入。下面就对单行插入、多行插入和多表插入进行说明。

 

1.      单行插入数据

1)       具体语法：

INSERT INTO TABLE [(column[,column,…])] VALUES(value[,value,…]);

TABLE用于指定表名；column用于指定列名，如果要指定多个列，那么列之间要用逗号分开；value用于提供列数据。

说明：如果不提供列，则每个列都要有数据。

2)       向basetab中中插入一条数据

  insert into basetab(msisdn,paytype) values(‘8613815427202’,0);

2.      多行插入数据

当我们想一次插入多行数据时可以使用子查询插入数据。当使用子查询插入数据时，可以将一张表的数据复制到另外一张表中。当处理行迁移、复制表数据或者装载外部表数据到数据时，可以使用子查询插入数据。

1)       具体语法：

INSERT INTO TABLE[(column[,column,…])] subQuery

TABLE用于指定表名；column用于指定列名，如果要指定多个列，那么列之间要用逗号分开；subQuery用于指定为目标表提供数据的子查询。需要插入的列和子查询的列的数据类型和个数要完全匹配。

2)       使用子查询插入数据

insert into subscribeinfo(msisdn,serviceid) select msisdn,serviceidfrom subscribeinfo;

3)       使用子查询执行直接装载

insert /*+APPEND*/ intosubscribeinfo(msisdn,serviceid) select msisdn,serviceid from subscribeinfo;

注意，尽管以上两条语句的执行结果一样，但第二条语句使用/*+APPEND*/来表示直接装载方式。当要装载大批量数据时，采用第二种方法装载数据的速度要远远优于第一种方法。Append在数据库oracle常用操作培训.ppt中有说明。

3.      多表插入数据

所谓多表插入即在通过一些限制条件，将子查询中的数据分别插入到不同的表中。其中通过两个操作符ALL和FIRST来实现的。

1)       ALL操作符

如：insert all when type=0 then into waptab

when type=1 then into javatab

when type=2 then into smstab

when orderflag =8 then into ordertab

else into othertab

select * from serviceinfo;

2)       FIRST操作符

当使用FIRST操作符执行多表插入时，如果数据已经满足了先前条件，并且已经被插入到某表中，那么该行数据在后续插入中不会再次使用

如：insert first when type=0 then into waptab

when type=1then into javatab

when type=2then into smstab

when orderflag=8 then into ordertab

else intoothertab

select * fromserviceinfo;