MS 建库、建表、建约束

use master
go

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'StudyDB')
 DROP DATABASE StudyDB  --如果存在则删除
GO
EXEC xp_cmdshell 'mkdir D:\SQL' --调用DOS命令创建文件夹
--建库
CREATE DATABASE StudyDB
ON PRIMARY( --PRIMARY 可选指定主文件组中的文件
 
 NAME = 'StudyDB1_data', --主数据文件的逻辑名
 FILENAME = 'D:\SQL\StudyDB1_data.mdf' , --主数据文件的物理名
 SIZE = 3MB,      --主数据文件初始大小
 MAXSIZE = 5MB,    --主数据文件最大大小
 FILEGROWTH = 20%   --主数据文件的增长率
), --注意","号分隔
(
 
 NAME = 'StudyDB2_data', --主数据文件的逻辑名
 FILENAME = 'D:\SQL\StudyDB2_data.ndf' , --主数据文件的物理名
 SIZE = 512 KB,      --主数据文件初始大小
 MAXSIZE = 5MB,    --主数据文件最大大小
 FILEGROWTH = 20% 
)
LOG ON
(
 
 NAME = 'StudyDB1_log',
 FILENAME = 'D:\SQL\StudyDB_log1.ldf' ,
 SIZE = 512KB,
 MAXSIZE = 3MB,
 FILEGROWTH = 20%
),  --注意","号分隔
(

 NAME = 'StudyDB2_log',
 FILENAME = 'D:\SQL\StudyDB_log2.ldf' ,
 SIZE = 512KB,
 MAXSIZE = 3MB,
 FILEGROWTH = 20%
)
go
--建表
use StudyDB  --必须使用StudyDB这个数据库,不然你建立的表在master数据库里面
go
IF EXISTS(SELECT * FROM sys.objects WHERE name='stuInfo')
  DROP TABLE stuInfo
--创建主表stuInfo--
CREATE TABLE stuInfo
(
 stuName    NVARCHAR(20)    NOT NULL,
 stuNo      NCHAR(6)        NOT NULL,
 stuSex     NCHAR(4)        NOT NULL,
 stuAge     SMALLINT        NOT NULL,
 stuSeat    SMALLINT        IDENTITY(1,1),
 stuAddress NTEXT
) 
GO
--为主表stuInfo创建约束[在外添加约束]--
ALTER TABLE stuInfo
  ADD CONSTRAINT PK_stuNo      PRIMARY KEY(stuNo),--主键约束
   CONSTRAINT UQ_stuNo      UNIQUE (stuNo),--唯一约束
      CONSTRAINT CK_stuNo      CHECK(stuNo LIKE 'S253[0-9][0-9]'),--检查约束
      CONSTRAINT CK_stuSex     CHECK(stuSex='男' OR stuSex='女'),
      CONSTRAINT CK_stuAge     CHECK(stuAge BETWEEN 15 AND 40),
      CONSTRAINT CK_stuSeat    CHECK(stuSeat<=30),
      CONSTRAINT DF_stuAddress DEFAULT ('地址不详') FOR stuAddress --默认值设置
 
GO
--如果在已有的数据中添加约束
ALTER TABLE stuInfo 
	WITH NOCHECK --不检查已有数据
	ADD CONSTRAINT my_cus 
	CHECK(stuSex IN (1,2))
	
ALTER TABLE stuInfo
	NOCHECK  | CHECK 
	CONSTRAINT my_cus --使用定义的my_cus约束失效,生效


--创建从表stuMarks--
CREATE TABLE stuMarks
(
 ExamNo CHAR(7) NOT NULL,
 stuNo NCHAR(6) NOT NULL,
 writtenExam SMALLINT NOT NULL,
 LabExam SMALLINT NOT NULL
)
GO
--为从表stuMarks创建约束--
ALTER TABLE stuMarks
   ADD CONSTRAINT PK_ExamNo         PRIMARY KEY(ExamNo),
       CONSTRAINT CK_ExamNo         CHECK(ExamNo LIKE 'S2718[0-9][0-9]'),
       CONSTRAINT FK_stuNo          FOREIGN KEY(stuNo) REFERENCES stuInfo(stuNo),--外键约束
       CONSTRAINT CK_writtenExam    CHECK(writtenExam BETWEEN 0 AND 100),
       CONSTRAINT DF_writtenExam    DEFAULT 0 FOR writtenExam,
       CONSTRAINT CK_LabExam        CHECK(LabExam BETWEEN 0 AND 100),
       CONSTRAINT DF_LabExam        DEFAULT 0 FOR LabExam
GO

 

CREATE TABLE TBL_TEST1(
 TEST1_ID INT PRIMARY KEY IDENTITY(1,1) --主键,自增
 , TEST_NAME VARCHAR(20) NOT NULL
)
CREATE TABLE TBL_TEST2(
 TEST2_ID INT PRIMARY KEY IDENTITY(1,1) 
 , TEST1_ID INT REFERENCES TBL_TEST1(TEST1_ID)--外键约束
 , TEST2_NAME VARCHAR(20) UNIQUE
 , TEST2_SEX CHAR(2) CHECK(TEST2_SEX='男' OR TEST2_SEX='女') --或者:CHECK TEST2_SEX IN('男','女')
 , TEST2_ADDRESS TEXT DEFAULT('地址不详')
)

insert into TBL_TEST1 values('1')
select * from TBL_TEST1
delete TBL_TEST1
insert TBL_TEST2 values(1,'1','男','')
select * from TBL_TEST2
delete TBL_TEST2

 

Technorati 标签: T-Sql