数据库管理的相关操作语句

1.插入( insert )

<1>一次只能插入一行数据

插入部分字段的用法：insert into table_name(column1,column2,column3) values(value1,value2,value3);

插入空值字段的用法：insert into table_name(column1,column2,column3) values(value1,value2,null);

插入全部字段的用法：insert into table_name values(value1,value2,value3);

<2>一次可以插入多行数据

Insert into table_name1 select语句;

2.修改( update )

修改字段内容的用法：update table_name set column1=new value,column2=new value where字句;

根据其他表修改数据：update table_name set(column1,column2)=(select语句) where字句;

3.删除( delete )

Delete from table_name where字句; 该命令把被删除的数据标记为unused 并不释放内存。

4.数据库事务( database transaction )

Commit 数据库事务提交，将变化写入数据库.

Rollback 数据库事务回退，撤销对数据的修改.

Savepoint 创建保存点，用于事务的阶段撤销.

5.锁( lock )

<1>隐式锁

对同一行数据，第一个会话修改未提交，则第二个会话不能做修改（相当于给这行加了隐式锁）.

<2>显式锁

①锁定行

Select语句 for update;

②锁定表

共享锁：lock table table_name in share mode;

独占锁：lock table table_name in exclusive mode;

<3>解锁

Commit;

Rollback;

6.表( table )

<1>创建( create )

①Create table table_name(column1数据类型(宽度) 列级约束, column2 数据类型(宽度) 列级约束, column3 数据类型(宽度) 列级约束,表级约束);

②Create table table_name(column1,column2.column3) as select语句;

<2>重命名( rename )

Rename old_table_name to new_table_name;

<3>注释

①为表添加注释：comment on table table_name is ‘字符串’;

清除注释：commenton table table_name is ‘’;

②为列添加注释：comment on column table_name.column_name is ‘字符串’;

清除注释：commenton column table_name.column_name is ‘’;

<4>修改表结构

①增加列

Alter table table_name add new_column_name 数据类型(宽度) 约束条件;

②修改列

Alter table table_name modify column_name 数据类型(宽度) 约束条件;

③删除列

Alter table table_name drop columncolumn_name;

Alter table table_name drop columncolumn_name cascade constraints;

<5>删除表

Drop table table_name;

Drop table table_name cascade constraints;

<6>清空表

Truncate table table_name; 该命令用来删除表中的全部数据并释放内存，但不删除表，表依然存在。

7.约束条件

<1>类别

①主键( primary key )

列级：constraint constraint_name primary key

表级：constraint constraint_name primary key(column_name1, column_name2, column_name3)

②非空( not null )

Constraint constraint_name not null

③唯一( unique )

列级：constraint constraint_name unique

表级：constraint constraint_name unique(column_name1, column_name2, column_name3)

④检查( check )

列级：constraint constraint_name check(条件)

表级：constraint constraint_name check(条件1,条件2)

⑤外键( foreign key )

子记录存在，不允许删除主记录：constraint constraint_name foreign key(column_name1,column_name2) references table_name(column_name1,column_name2)

子记录存在,删主则删子：constraint constraint_name foreign key(column_name1,column_name2) references table_name(column_name1,column_name2) on delete cascade

子记录存在,删主则空子：constraint constraint_name foreign key(column_name1,column_name2) references table_name(column_name1,column_name2)on delete set null

<2>使约束条件生效和失效

生效：alter table table_name enable constraint constraint_name;

失效：alter table table_name disable constraint constraint_name;

<3>增加约束条件

Alter table table_name add constraint constraint_name 表级约束条件;

<4>删除约束条件

Alter table table_name drop constraint constraint_name;

Alter table table_name drop constraint constraint_name cascade;

8.视图( view )

<1>创建( create )

有基表：create or replace view view_name(column_name1,column_name2) as select语句;

无基表：create or replace force view view_name(column_name1,column_name2) as select语句; 会报错！

<2>条件

只读视图：create or replace view view_name(column_name1,column_name2) as select语句 with read only;

限制视图：create or replace view view_name(column_name1,column_name2) as select语句 with check option;

<3>删除

Drop view view_name;

9.索引( index )

唯一索引(默认情况为非唯一)：create unique index index_name ontable_name(column_name1,column_name2);

位图索引(默认情况为B*树索引)：create bitmap index index_name ontable_name(column_name1,column_name2);

删除索引：drop index index_name;

10.序列( sequence )

<1>用法

Create sequence sequence_name increment n start with n maxvalue n nocycle nocache;

Create sequence sequence_name increment n start with n nomaxvalue nocycle nocache;

<2>函数

下一个：sequence_name.nextval

当前：sequence_name.currval

<3>删除

Drop sequence sequence_name;

11.同义词( synonym )

创建：create synonym synonym_name for table_name;

删除：drop synonym synonym_name;

12.数据字典

<1>查看同义词

Select object_name from user_objects whereobject_type=’SYNONYM’;

<2>查看序列

Select sequence_name,min_value,max_value,increment_by,last_number from user_sequences;

<3>查看索引

Select index_name,index_type from user_indexs where table_name=’EMP’;

<4>查看视图

Select text from user_views where view_name=’ 视图名‘;

<5>查看表

Select object_name from user_objects where object_type=’TABLE’;