基础T-SQL语句



-----------------创建数据库,数据库位置D:\DataBase----------------------
-----主要数据文件CompanyDB,初始大小20MB,限制大小200MB,按照10MB增长-----
-----次要数据文件CompanyDB_S1,初始大小20MB,限制大小200MB,按照10MB增长-----
-----次要数据文件CompanyDB_S2,初始大小20MB,限制大小200MB,按照10MB增长-----
-----事务日志文件CompanyDB_Log1,初始大小20MB,限制大小200MB,按照10%增长-----
-----次要数据文件CompanyDB_Log2,初始大小20MB,限制大小200MB,按照10%增长-----
create database CompanyDB
on
(
name='CompanyDB',
filename='D:\DataBase\CompanyDB.mdf',
size=20MB,
maxsize=200MB,
filegrowth=10MB
),
-----将两个次要文件CompanyDB_S1，CompanyDB_S2-------
-----分别放入文件夹组ExtFileGROUP1，ExtFileGROUP2---
filegroup ExtFileGROUP1  
(
name='CompanyDB_S1',
filename='D:\DataBase\CompanyDB_S1.ndf',
size=20MB,
maxsize=200MB,
filegrowth=10MB
),
filegroup ExtFileGROUP2
(
name='CompanyDB_S2',
filename='D:\DataBase\CompanyDB_S2.ndf',
size=20MB,
maxsize=200MB,
filegrowth=10MB
)
log on
(
name='CompanyDB_Log1',
filename='D:\DataBase\CompanyDB_Log1.ldf',
size=10MB,
maxsize=100MB,
filegrowth=10%
),
(
name='CompanyDB_Log2',
filename='D:\DataBase\CompanyDB_Log2.ldf',
size=10MB,
maxsize=100MB,
filegrowth=10%
)

------设置主键约束------
create table Department
(
deptID int identity(1,1)
constraint pk_Department primary key ,
deptName nvarchar(20) not null,
deptSponsor nvarchar(20)
)

----另外一种主键约束的设置-----
create table Employee
(
employeeID int identity(1,1),
employeeName nvarchar(20) not null,
employeePhone varchar(20)
constraint pk_Employee primary key(employeeID ASC)
)

------添加新的列------
alter table Employee
add  employeeBirthday datetime

---------------为Employee添加Check约束--------------
alter table Employee
add constraint ck_employeeBirthday check
(
datediff(yyyy,employeeBirthday,getdate())>18
)

--------------判断如果系统存在该表，删除数据库表-----------
if exists (select * from sysobjects where name='Department')
    drop table Department
if exists (select * from sysobjects where name='Employee')
    drop table Employee