您所在位置：主题：[原创]Sql server基础知识--建立对象
junewu1983
[专家分：30] 发布于 2006-10-13 11:10:00
六、建立对象
1、 表
a) 基本概念
 数据库会分配一定的空间从而定义表的大小，所以表的大小不用指定。
 表可容纳最多1000列。
 表可在用户使用数据库的同时创建，而且表的结构可在表联机时修改。
 RENAME语句的语法 : RENAME old name TO new name; 注意要是表名中包含空格，符号或数字，必须使用双引号
 在CREATE TABLE子句中使用子查询，新表中的列数必须与子查询返回的列数相等，列位置对应。另外，除NOT NULL约束外，新列不会继承源列的所有约束。
 你不能对一列重命名。在表中添加一列时，你不能指定该列的位置，它会被默认放置在最后。你可以为一列增加长度。 通常当一列包含数据时，你不能修改该列的数据类型，不能减少一列的长度，但当列中包含空值或你不改变列的大小时，你可以把CHAR 转换为VARCHAR2数据类型。
 在DROP TABLE table命令后加上CASCADE CONSTRAINTS回把表中的相关约束一并删除。如命令："DROP TABLE employee CASCADE CONSTRAINTS;"会把employee表中的数据，结构，和相关约束一并删除。
 当你创建一个数据类型为VARCHAR2的列时，必须指定长度。
 为表加注释:comment on table tablename is ‘………’;
 COMMENT ON TABLE命令用于在数据字典里添加关于表，视图或快照的注释。
 但你对一表执行了DML语句的INSERT操作时，但没有提交，别人可以同时访问该表，但看不到你所作的修改，由于对象被加锁，所以别人不能作同样的修改。
b) 建表原则
以字母开头
不可以超过30个字
只能由数字,字母,_,$,#组成
用子查询建立表
create table tablename as select …..
c) 删除表
drop table tablename
删除所有的数据
删除所有相关的index
所有待解决的事务会自动提交
不可以回滚
不释放空间
truncate table tablename
删除所有的数据
释放该表所占用的空间
不可以回滚
并存储容量参数重置为定义值
要成功执行TRUNCATE TABLE 命令，你必须拥有该表或拥有DELETE TABLE的系统权限。
2、约束
 约束类别
column level:
NOT NULL
table level:
UNIQUE Key
PRIMARY Key
FOREIGN Key
CHECK
CHECK can be defined either on column level or on table level. Plus, you can say something like:
create table abc
(
column1 number,
column2 number,
constraint less_ck CHECK (column2 < column1)
);
This constraint will make sure column2 is less than column1 when you enter data into the table.
 建立约束
Table constraint level
column,...
[CONSTRAINT constraint_name] constraint_type (column, ...),
EG:
CREATE TABLE emp(
empno NUMBER(4),
ename VARCHAR2(10),
deptno NUMBER(7,2) NOT NULL,
CONSTRAINT emp_empno_pk PRIMARY KEY (EMPNO))
CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)
REFERENCES dept (deptno)
CONSTRAINT emp_deptno_ck CHECK (DEPTNO BETWEEN 10 AND 99));
 修改约束
添加：
ALTER TABLE 命令可为一个现有的表添加一个约束，所有约束都是用ADD来添加，但是为一个列添加NOT NULL 约束，只能使用MODIFY子句，但需注意的是，添加NOT NULL约束时，必须符合以下两个条件中的一个：1.表中没有数据 2.添加约束的目标行没有空值。
ALTER TABLE emp ADD CONSTRAINT emp_mgr_fk
FOREIGN KEY(mgr) REFERENCES emp(empno);
删除：
SQL> ALTER TABLE emp
2 DROP CONSTRAINT emp_mgr_fk;
SQL> ALTER TABLE dept
2 DROP PRIMARY KEY CASCADE;
删除primary key ，而添加CASCADE则表示相关的完整性约束也一并删除。
禁用/启用
SQL> ALTER TABLE emp
2 DISABLE/enable CONSTRAINT emp_empno_pk CASCADE;
 数据字典
USER_CONSTRAINTS
SQL> SELECT constraint_name, constraint_type,
2 search_condition
3 FROM user_constraints
4 WHERE table_name = 'EMP';
USER_CONS_COLUMNS(查看被约束的column)
SQL> SELECT constraint_name, column_name
2 FROM user_cons_columns
3 WHERE table_name = 'EMP';
 知识点：如果A表中的a列在B表中拥有一个外键约束，在你删除B表记录之前，你必须先删除A表中的所有子记录。如果你尝试在A表中插入一个记录，而B表中不存在它的副记录的会得到一个违反约束的错误。
3、序列
CREATE（ALTER） SEQUENCE name
[INCREMENT BY n] ；指定序列以n递增，如果没指定，默认值1会使用
[START WITH n] ；由n开始计数
[(MAXVALUE n | NOMAXVALUE)] ；设置最大值n或不设置最大值
[(MINVALUE n | NOMINVALUE)] ；设置最小值n或不设置最小值
[(CYCLE | NOCYCLE)] ；当达到最大值时循环或不循环
[(CACHE n | NOCACHE)] ；预分配缓存大小，默认为20
删除序列
drop sequence name
CURRVAL伪列用于在当前序列中检索连续序列号，它能用在UPDATE语句的SET子句中和INSERT语句的VALUES子句中，还可以用在INSERT语句的子查询的SELECT列表中。但不能用于视图的SELECT语句和带HAVING子句的SELECT语句中。
4、视图
a) 建立
修改一个视图最简单的方法是使用带OR REPLACE的 CREATE VIEW语句，这允许视图的旧版本被代替，避免了删除视图和重建视图的必要，要是删除了视图，你必须在新视图上重新给对象授权。
create [or replace][force/noforce] view viewname
as subquery //子查询不可以用order by
[with check option] //则指明只有允许被视图访问的行才能被插入或更新。
[constraint constraintname]
[with read only] //不可以通过view进行表的修改
b) 对view的DML操作的限制
可以在simple view执行DML操作
不可以删除一行如果view 包含
group funtion
a group by clause
the distinct command
不可以修改数据如果view包含
以上说的条件
由表达式定义的列
the rownum pseudocolumn
不可以添加数据，如果view包含：
以上所有的条件
存在非空的字段没在包含在view中
c) 数据字典
USER_VIEWS数据字典显示用户所拥有的视图的描述
ALL_VIEWS数据字典显示用户有权访问的视图的描述
ALL_OBJECTS数据字典显示用户有权访问的对象（包括视图）的信息USER_OBJECTS数据字典显示用户所拥有的对象的描述
5、序列
CREATE SEQUENCE sequence [INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]; //序列放在内存中。使读取更快
eg: SQL> CREATE SEQUENCE dept_deptno
2 INCREMENT BY 1
3 START WITH 91
4 MAXVALUE 100
5 NOCACHE
6 NOCYCLE;
可以在USER_SEQUENCES查看所建立的表
SQL> SELECT sequence_name, min_value, max_value,
2 increment_by, last_number
3 FROM user_sequences;
•The LAST_NUMBER ：显示nextval的值
•Gaps in sequence values can occur when:
–发生rollback
–系统崩溃
–另一个表在用此序列
你必须是the sequence.的拥有者和 对此the sequence.有ALTER privilege 的权限
如果想改开始值的话只有重建序列
6、索引
a) 建立
 自动：当指定表的列为primary key or union时。系统自动为此表建立一个index
 手动：可以为表的非唯一值的列手动建立index
 create index indexname on table(column)
 数据字典
用户建立的索引在USER_INDEXES 中(详细的信息)
USER_IND_COLUMNS (列的一些简单信息)
SELECT ic.index_name, ic.column_name,ic.column_position col_pos,ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ic.table_name = ' ANNOUNCEMENT';
 建立索引的方针
在where 和join中经常用到的列
列的值范围很大
此列包含很多空值
表很大，但只要其中的2-4％的数据
很多索引不一定能提高速度。
 不建索引的方针
表很小
列不常用来查询
查询结果超过表的2-4％
表常被更新
7、同义词
建立：create synonym 别名 for 原名
CREATE SYNONYM d_sum FOR dept_sum_vu;
删除： DROP SYNONYM d_sum;
在创建同义词时，语句中出现PRIVATE 和OR REPLACE 是无效的
1、 表
a) 基本概念
 数据库会分配一定的空间从而定义表的大小，所以表的大小不用指定。
 表可容纳最多1000列。
 表可在用户使用数据库的同时创建，而且表的结构可在表联机时修改。
 RENAME语句的语法 : RENAME old name TO new name; 注意要是表名中包含空格，符号或数字，必须使用双引号
 在CREATE TABLE子句中使用子查询，新表中的列数必须与子查询返回的列数相等，列位置对应。另外，除NOT NULL约束外，新列不会继承源列的所有约束。
 你不能对一列重命名。在表中添加一列时，你不能指定该列的位置，它会被默认放置在最后。你可以为一列增加长度。 通常当一列包含数据时，你不能修改该列的数据类型，不能减少一列的长度，但当列中包含空值或你不改变列的大小时，你可以把CHAR 转换为VARCHAR2数据类型。
 在DROP TABLE table命令后加上CASCADE CONSTRAINTS回把表中的相关约束一并删除。如命令："DROP TABLE employee CASCADE CONSTRAINTS;"会把employee表中的数据，结构，和相关约束一并删除。
 当你创建一个数据类型为VARCHAR2的列时，必须指定长度。
 为表加注释:comment on table tablename is ‘………’;
 COMMENT ON TABLE命令用于在数据字典里添加关于表，视图或快照的注释。
 但你对一表执行了DML语句的INSERT操作时，但没有提交，别人可以同时访问该表，但看不到你所作的修改，由于对象被加锁，所以别人不能作同样的修改。
b) 建表原则
以字母开头
不可以超过30个字
只能由数字,字母,_,$,#组成
用子查询建立表
create table tablename as select …..
c) 删除表
drop table tablename
删除所有的数据
删除所有相关的index
所有待解决的事务会自动提交
不可以回滚
不释放空间
truncate table tablename
删除所有的数据
释放该表所占用的空间
不可以回滚
并存储容量参数重置为定义值
要成功执行TRUNCATE TABLE 命令，你必须拥有该表或拥有DELETE TABLE的系统权限。
2、约束
 约束类别
column level:
NOT NULL
table level:
UNIQUE Key
PRIMARY Key
FOREIGN Key
CHECK
CHECK can be defined either on column level or on table level. Plus, you can say something like:
create table abc
(
column1 number,
column2 number,
constraint less_ck CHECK (column2 < column1)
);
This constraint will make sure column2 is less than column1 when you enter data into the table.
 建立约束
Table constraint level
column,...
[CONSTRAINT constraint_name] constraint_type (column, ...),
EG:
CREATE TABLE emp(
empno NUMBER(4),
ename VARCHAR2(10),
deptno NUMBER(7,2) NOT NULL,
CONSTRAINT emp_empno_pk PRIMARY KEY (EMPNO))
CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)
REFERENCES dept (deptno)
CONSTRAINT emp_deptno_ck CHECK (DEPTNO BETWEEN 10 AND 99));
 修改约束
添加：
ALTER TABLE 命令可为一个现有的表添加一个约束，所有约束都是用ADD来添加，但是为一个列添加NOT NULL 约束，只能使用MODIFY子句，但需注意的是，添加NOT NULL约束时，必须符合以下两个条件中的一个：1.表中没有数据 2.添加约束的目标行没有空值。
ALTER TABLE emp ADD CONSTRAINT emp_mgr_fk
FOREIGN KEY(mgr) REFERENCES emp(empno);
删除：
SQL> ALTER TABLE emp
2 DROP CONSTRAINT emp_mgr_fk;
SQL> ALTER TABLE dept
2 DROP PRIMARY KEY CASCADE;
删除primary key ，而添加CASCADE则表示相关的完整性约束也一并删除。
禁用/启用
SQL> ALTER TABLE emp
2 DISABLE/enable CONSTRAINT emp_empno_pk CASCADE;
 数据字典
USER_CONSTRAINTS
SQL> SELECT constraint_name, constraint_type,
2 search_condition
3 FROM user_constraints
4 WHERE table_name = 'EMP';
USER_CONS_COLUMNS(查看被约束的column)
SQL> SELECT constraint_name, column_name
2 FROM user_cons_columns
3 WHERE table_name = 'EMP';
 知识点：如果A表中的a列在B表中拥有一个外键约束，在你删除B表记录之前，你必须先删除A表中的所有子记录。如果你尝试在A表中插入一个记录，而B表中不存在它的副记录的会得到一个违反约束的错误。
3、序列
CREATE（ALTER） SEQUENCE name
[INCREMENT BY n] ；指定序列以n递增，如果没指定，默认值1会使用
[START WITH n] ；由n开始计数
[(MAXVALUE n | NOMAXVALUE)] ；设置最大值n或不设置最大值
[(MINVALUE n | NOMINVALUE)] ；设置最小值n或不设置最小值
[(CYCLE | NOCYCLE)] ；当达到最大值时循环或不循环
[(CACHE n | NOCACHE)] ；预分配缓存大小，默认为20
删除序列
drop sequence name
CURRVAL伪列用于在当前序列中检索连续序列号，它能用在UPDATE语句的SET子句中和INSERT语句的VALUES子句中，还可以用在INSERT语句的子查询的SELECT列表中。但不能用于视图的SELECT语句和带HAVING子句的SELECT语句中。
4、视图
a) 建立
修改一个视图最简单的方法是使用带OR REPLACE的 CREATE VIEW语句，这允许视图的旧版本被代替，避免了删除视图和重建视图的必要，要是删除了视图，你必须在新视图上重新给对象授权。
create [or replace][force/noforce] view viewname
as subquery //子查询不可以用order by
[with check option] //则指明只有允许被视图访问的行才能被插入或更新。
[constraint constraintname]
[with read only] //不可以通过view进行表的修改
b) 对view的DML操作的限制
可以在simple view执行DML操作
不可以删除一行如果view 包含
group funtion
a group by clause
the distinct command
不可以修改数据如果view包含
以上说的条件
由表达式定义的列
the rownum pseudocolumn
不可以添加数据，如果view包含：
以上所有的条件
存在非空的字段没在包含在view中
c) 数据字典
USER_VIEWS数据字典显示用户所拥有的视图的描述
ALL_VIEWS数据字典显示用户有权访问的视图的描述
ALL_OBJECTS数据字典显示用户有权访问的对象（包括视图）的信息USER_OBJECTS数据字典显示用户所拥有的对象的描述
5、序列
CREATE SEQUENCE sequence [INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]; //序列放在内存中。使读取更快
eg: SQL> CREATE SEQUENCE dept_deptno
2 INCREMENT BY 1
3 START WITH 91
4 MAXVALUE 100
5 NOCACHE
6 NOCYCLE;
可以在USER_SEQUENCES查看所建立的表
SQL> SELECT sequence_name, min_value, max_value,
2 increment_by, last_number
3 FROM user_sequences;
•The LAST_NUMBER ：显示nextval的值
•Gaps in sequence values can occur when:
–发生rollback
–系统崩溃
–另一个表在用此序列
你必须是the sequence.的拥有者和 对此the sequence.有ALTER privilege 的权限
如果想改开始值的话只有重建序列
6、索引
a) 建立
 自动：当指定表的列为primary key or union时。系统自动为此表建立一个index
 手动：可以为表的非唯一值的列手动建立index
 create index indexname on table(column)
 数据字典
用户建立的索引在USER_INDEXES 中(详细的信息)
USER_IND_COLUMNS (列的一些简单信息)
SELECT ic.index_name, ic.column_name,ic.column_position col_pos,ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ic.table_name = ' ANNOUNCEMENT';
 建立索引的方针
在where 和join中经常用到的列
列的值范围很大
此列包含很多空值
表很大，但只要其中的2-4％的数据
很多索引不一定能提高速度。
 不建索引的方针
表很小
列不常用来查询
查询结果超过表的2-4％
表常被更新
7、同义词
建立：create synonym 别名 for 原名
CREATE SYNONYM d_sum FOR dept_sum_vu;
删除： DROP SYNONYM d_sum;
在创建同义词时，语句中出现PRIVATE 和OR REPLACE 是无效的