课堂示范.sql

主要介绍了sql的基础教程

--以下是NULL/NOT NULL约束的例子
--====================================================================================================
--创建一个Customer表，包含三个列CustomerID、Pfirstname和Plastname三个列,且设CustomerID列不允许出现空值。
Create table Customer
(
 CustomerID varchar(15) not null,
 Pfirstname varchar(20),
 Plastname varchar(20)
)
--将Customer表中的Pfirstname和Plastname都修改为不允许为空
alter table Customer alter column Pfirstname varchar(20) not null
alter table Customer alter column Plastname varchar(20) not null
--=============================================================================================================
--主键约束
--=============================================================================================================
--创建part_sample表，包含part_number（PK）、part_name、part_weight、part_color字段，不指明主键约束的名称
create table part_sample
(
 part_number int primary key,
 part_name varchar(30),
 part_weight decimal(6,2),
 part_color varchar(15)
)
--创建part_sample表时添加主键约束，指明约束名称
create table part_sample
(
 part_number int constraint pk_partnumber primary key,
 part_name varchar(30),
 part_weight decimal(6,2),
 part_color varchar(15)
)
--创建订单表book_order，表中包含orderID（PK）、bookID（PK）、clientID、quantity、purchase_date字段
create table book_order
(
 orderID varchar(10),
 bookID varchar(5),
 clientID varchar(10),
 quantity int,
 purchase_date datetime,
 constraint pk_orderkey primary key (orderID,bookID) 
)
--修改表时添加主键约束，不指明约束名称
--对于一个已经存在主键的表，如果想修改主键只能先删除原有主键，再重新建立一个新主键
--查看表part_sample中的主键信息
exec sp_pkeys part_sample
--删除表part_sample中的已有主键
alter table part_sample drop constraint pk_partnumber
--修改表part_sample，为其添加主键,不指明约束名称
alter table part_sample add primary key (part_number)
--修改表part_sample，为其添加主键,不指明约束名称
alter table part_sample add constraint partkey primary key (part_number)
--对于已经创建的表添加主键，将要设置为主键的字段并未指明是非空字段的处理方法
--创建student表，表中含有stu_id、name、sex字段，随后再修改此表，为其添加主键
create table student
(
 stu_id varchar(10),
 name varchar(20),
 sex char(2)
)
--直接添加主键信息会怎么样?应该如何处理？
alter table student add constraint pk_stuid primary key (stu_id)
--处理方式为：先修改要定义为主键的列为非空列，再将其定义为主键
alter table student alter column stu_id int not null
alter table student add constraint pk_stuid primary key (stu_id)
--===================================================================================================
--以下是唯一约束的例子
--=======================================================================================================
--创建表authors，表中含有au_id（PK）、au_name、pseudonym字段，其中pseudonym字段带有唯一约束，不指明约束名称
create table authors
(
 au_id varchar(20) primary key,
 au_name varchar(20),
 pseudonym varchar(20) unique
)
--创建表时添加唯一约束，指明约束名称
create table authors
(
 au_id varchar(20) primary key,
 au_name varchar(20),
 pseudonym varchar(20) constraint UQ_pseudonym unique
)
--修改表时添加唯一约束，不指明约束名称
create table authors
(
 au_id varchar(20) primary key,
 au_name varchar(20),
 pseudonym varchar(20) 
)
alter table authors add unique(pseudonym) 
--修改表时添加唯一约束，指明约束名称
create table authors
(
 au_id varchar(20) primary key,
 au_name varchar(20),
 pseudonym varchar(20) 
)
alter table authors add constraint UQ_pseudonym unique(pseudonym) 
--=========================================================================================
--以下是外键约束的例子
--=========================================================================================
--创建表Order_part，含有order_number、part_number、qty_orderde字段，建立一个外键引用前面定义的表part_sample中的part_number。
create table order_part
(
 order_number int,
 part_number int foreign key references part_sample (part_number) on delete no action,
 qty_orderde int
)
--写法二：创建表Order_part，建立一个外键引用前面定义的表part_sample中的part_number。
create table order_part
(
 order_number int,
 part_number int,
 qty_orderde int,
  foreign key (part_number)references part_sample (part_number) on delete no action
)
--创建表Order_part，不指明外键约束，在修改表的时候将外键约束加上
create table order_part
(
 order_number int,
 part_number int,
 qty_orderde int
)
alter table order_part add constraint fk_partnum foreign key (part_number) references part_sample (part_number) on update cascade
--===================================================================================
--以下是检查约束的例子
--===================================================================================
--创建表cust_sample,包含字段cust_id（PK,且字段的值在0～10000之间）、cust_name、cust_address、cust_credit_limit
create table cust_sample
(
 cust_id int primary key,
 cust_name varchar(50),
 cust_address varchar(100),
 cust_credit_limit money,
 constraint chk_custid check (cust_id between 0 and 10000) 
)
--创建表teachar，包含字段tea_id(PK)、name、sex、age,创建完成后再为age字段添加检查约束，限制年龄在0～150之间
create table teachar
(
 tea_id int primary key,
 name varchar(20),
 sex char(2),
 age int
)
alter table teachar with nocheck add constraint chk_teaid check (tea_id>0 and tea_id<150)
--==========================================================================================================
--以下是缺省约束的例子
--==========================================================================================================
--创建表book_lend，包含字段：book_id（PK）、book_name、lend_time，其中lend_time的缺省值为当前系统时间
create table book_lend
(
 book_id int primary key,
 book_name varchar(50),
 lend_time datetime constraint df_lendtime default getdate()
)
--创建表book_lend，不添加缺省约束，再修改该表然后再添加缺省约束
create table book_lend
(
 book_id int primary key,
 book_name varchar(50),
 lend_time datetime
)
alter table book_lend add constraint df_lendtime default getdate() for lend_time
--===================================================================================
--查看表中的约束
exec sp_helpconstraint book_lend
--===================================================================================
--以下是综合使用各种约束的例子
--创建表authors，包含字段：au_id(PK，且字段的值为三个0～9之间的整数+"-"+两个0～9之间的整数+"-"+四个0～9之间的整数)、au_lname（不允许为空）、au_fname（不允许为空）、phone（不允许为空，缺省值为“UNKNOWN”）、address、city、state、zip（五个0～9之间的数字组成）
--===================================================================================
CREATE TABLE authors
(
	au_id varchar(15) NOT NULL  primary key constraint ck_auid check(au_id like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'),
	au_lname varchar(40) NOT NULL,
	au_fname varchar(20) NOT NULL,
	phone varchar(12) NOT NULL CONSTRAINT DF_authphone DEFAULT 'UNKNOWN',
	address varchar(100) NULL,
	city varchar(20),
	state char(2),
	zip char(5) constraint ck_zip check (zip like '[0-9][0-9][0-9][0-9][0-9]')
) 
--===================================================================================
--创建表book，字段有：book_id（PK）、book_name
create table book
(
 book_id varchar(20) primary key,
 book_name varchar(50)
)
--创建规则，使得字段的值为两个字符后面跟一个连字符"-"再跟任意多个字符，并以0到9之间的整数结尾
create rule bookid_rule as @value like '__-%[0-9]'
exec sp_help  
--绑定规则bookid_rule到book表中的book_id字段
exec sp_bindrule bookid_rule,'book.book_id' 
--取消绑定的规则
exec sp_unbindrule 'book.book_id',futureonly
--创建默认，使得字段的默认值为“unknown”
create default nameflt as 'unknown'
--绑定默认nameflt到表book的book_name字段
exec sp_bindefault nameflt,'book.book_name'
--取消绑定的默认
exec sp_unbindefault 'book.book_name',futureonly