|
4.数据定义DATA DEFINE LANGUAGE
4.1表Table
4.2列Column
4.3序列Indentity
4.4约束Constraints
4.5索引Index
4.6视图view
4.7权限Privilege
/**********************************************************/
4.1表Table
4.1.1.建立表
语法:
create table <表名>(
<列名> <数据类型> [长度] <,>
<列名...>
)
代码:
//建立公司部门表
create table tb_basic_dept(
id int not null,
name varchar(20) ,
chair varchar(20)
)
4.1.2.删除表
语法:
drop table <表名>
代码:
//删除部门表
drop table tb_basic_dept
-----------------------------------------------------------
4.2列Column
4.2.1.列添加
语法:
alter table <表名> add
<列名> <数据类型> [长度] <,>
<列名...>
代码:
alter table tb_basic_dept add
remark varchar(50)
4.2.2.列删除
语法:alter table <表名> drop column <列名>
代码:
alter table tb_basic_dept drop column remark
4.2.3.列修改
语法:alter table <表名> alter column
<列名> <数据类型> [长度] [null | not null]
代码:
//修改工资列为dec(8,2)
alter table tb_hr_gz alter column gz dec(8,2) null
-----------------------------------------------------------
4.3序列Identity
//特别要求
IDENTITY字段数据类型只能是(int, bigint, smallint, tinyint, decimal, or numeric(x,0))
IDENTITY字段必须是not null约束
4.3.1Identity
语法:
IDENTITY(<data_type> [, <seed>, <increment>]) AS column_name,
代码:
//使用Identity
CREATE TABLE MyTable (
key_col int NOT NULL IDENTITY (1,1),
abc char(1) NOT NULL
)
INSERT INTO MyTable VALUES ('a')
INSERT INTO MyTable VALUES ('b')
INSERT INTO MyTable VALUES ('c')
-----------------------------------------------------------
4.4约束Constraints
4.4.1缺省约束(default)
4.4.2非空约束(not null)
4.4.3规则约束(rule)
4.4.4检查约束(check)
4.4.5唯一约束(unique)
4.4.6主键约束(primary key)
4.4.7外键约束(foreign key)
4.4.8商业规则(business rule)
以下面两个表为例进行演示
create table tb_hr_bm(
bm varchar(20) not null ,
remark varchar(100) default ''
)
create table tb_hr_gz(
id int not null,
name varchar(30) not null,
hrid char(18) null,
workage int null ,
bm varchar(20) null,
gz real null,
remark varchar(100) null
)
hrid=身份证号码
workage=工作年数
gz=工资金额
-----------------------------------------------------------
4.4.1缺省约束(default)
语法:CREATE DEFAULT default_name AS expression
代码:CREATE DEFAULT zip_default AS 94710
-----------------------------------------------------------
4.4.2非空约束(not null)
//表的主键和其它必填字段必须为not null.
语法:create table (column-name datatype not null... )
代码:create table tb_hr_gz(id int not null,...)
-----------------------------------------------------------
4.4.3规则约束(rule)
语法:CREATE RULE rulename AS condition
代码:
//邮编号码6位100000-999999
//建立一个自定义zip类型
CREATE TYPE zip FROM CHAR(6) NOT NULL
//建立一个规则约束
CREATE RULE zip_rule AS @number >100000 and @number < 999999
//绑定规则约束到zip类型
EXEC sp_bindrule zip_rule, 'zip'
//应用自定义zip类型
2> CREATE TABLE address(
city CHAR(25) NOT NULL,
zip_code ZIP,
street CHAR(30) NULL
)
-----------------------------------------------------------
4.4.4检查约束(建立/删除)
//检查约束建立
语法:
alter table name
add constraint <检查约束名> check<取值范围表达式>
代码:
//工资添加取值范围0 ~ 1000000
方法1:
create table tb_hr_gz(
gz real default 0.0 check(gz >=0 and gz <=1000000),
...
)
方法2:
alter table tb_hr_gz
add constraint tb_hr_gz_ck check(gz >=0 and gz <=1000000)
//检查约束删除
语法:
alter table name drop constraint <检查约束名>
代码:
//删除工资的检查约束
alter table tb_hr_gz drop constraint tb_hr_gz_ck
-----------------------------------------------------------
4.4.5唯一约束
4.4.5.1.唯一约束添加
语法:
alter table name add constraint <唯一约束名> unique<列名>
代码:
//列如身份证号码是唯一的!
alter table tb_hr_gz Add constraint tb_hr_gz_uk unique(hrid)
4.4.5.2.唯一约束删除
语法:
alter table name drop constraint <唯一约束名>
代码:
alter table tb_hr_gz drop constraint tb_hr_gz_uk
-----------------------------------------------------------
4.4.6主键约束
4.4.6.1主键约束添加
语法:
alter table table_name
add constraint <主键名称> Primary Key <列名>
代码:
create table tb_hr_bm(
bm varchar(20) not null ,
remark varchar(100) default ''
)
alter table tb_hr_bm
add constraint tb_hr_bm_pk Primary Key (bm)
4.4.6.2主键约束删除
语法:
alter table table_name
drop constraint <主键名称>
代码:
alter table table_name
drop constraint tb_hr_bm_pk
-----------------------------------------------------------
4.4.7外键约束
4.4.7.1外键约束添加
语法:
alter table <表名>
add constraint <外键名>
foreign key(列名)
references <参考表名><列名>
<ON UPDATE|ON DELETE(RESTRICT|CASCADE|SET NULL|SET DEFAULT)>
//补充说明
常用选项是下面3项:
ON UPDATE SET NULL //级联更新
ON DELETE CASCADE //级联删除
ON DELETE SET NULL //级联置空
ON UPDATE(RESTRICT|CASCADE|SET NULL|SET DEFAULT) 表示父表更新后,子表的行为
ON DELETE(RESTRICT|CASCADE|SET NULL|SET DEFAULT) 表示父表删除后,子表的行为
RESTRICT 限制功能:父表一行记录不能更新/删除，当子表有一条记录以上时
CASCADE 级联功能:父表一行记录记录更新/删除删除,子表对应所有的记录自动更新/删除
SET NULL 置空功能:父表一行记录记录更新/删除删除,子表对应所有的记录自动为空
SET DEFAULT 默认值功能:父表一行记录记录更新/删除删除,子表对应所有的记录自动写入默认值
代码:
建立外键的主要代码
alter table tb_hr_personl_info
add constraint tb_hr_personl_info__bm_fk
foreign key(bm)
references tb_hr_bm (bm)
on update cascade
on delete cascade
//建立参考表部门
create table tb_hr_bm
(
bm varchar(20) not null ,
remark varchar(100) default ''
)
alter table tb_hr_bm
add constraint tb_hr_bm_pk Primary Key (bm)
//建立个人信息表
use hr
create table tb_hr_personl_info
(
userid int not null ,
username varchar(20) null,
bm varchar(20) null
)
/*为此表添加主键约束*/
alter table tb_hr_personl_info
add constraint tb_hr_personl_info_pk Primary Key (userid)
/*为个人信息表添加外键约束*/
alter table tb_hr_personl_info
add constraint tb_hr_personl_info__bm_fk
foreign key(bm)
references tb_hr_bm (bm)
on update cascade
on delete cascade
|
