MSSQL基础练习

 

/*个人练习,从基础开始;*/

--判断是否存在Test数据库,有就删掉
USE master;
GO
IF EXISTS(SELECT name FROM SYSDATABASES WHERE name = 'Test')
	BEGIN
		DROP DATABASE Test;
	END
GO

--创建Test数据库,有主数据库,次数据库和多日志文件
CREATE DATABASE Test
ON PRIMARY
(
	NAME = 'Test_dat',
	FILENAME = 'E:\Test\Test_dat.mdf',
	SIZE = 3MB,
	MAXSIZE = 5MB,
	filegrowth = 1%
),
(
	NAME = 'Test_dat1',
	FILENAME = 'E:\Test\Test_dat.ndf',
	SIZE = 3MB,
	MAXSIZE = 5MB,
	filegrowth = 1%
)
LOG ON
(
	NAME = 'Test_Log1',
	FILENAME = 'E:\Test\Test_dat1.ldf',
	SIZE = 2MB,
	MAXSIZE = 5MB,
	filegrowth = 1%
),
(
	NAME = 'Test_Log2',
	FILENAME = 'E:\Test\Test_dat2.ldf',
	SIZE = 2MB,
	MAXSIZE = 5MB,
	filegrowth = 1%
);

GO

--分离数据库
EXEC sp_detach_db Test;
GO


--附加数据库
CREATE DATABASE Test
ON
(
	FILENAME = 'E:\Test\Test_dat.mdf'
) FOR ATTACH;

GO
USE Test;
GO

--如果存在Test2表,则删除表Test2
IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(N'Test2'))
	BEGIN
		DROP TABLE Test2;
	END
GO

--创建表Test2
CREATE TABLE Test2
(
    id INT IDENTITY(1,1) PRIMARY KEY,
    tName VARCHAR(20) NOT NULL,
    captainId INT UNIQUE
)
GO

--如果存在Test表,则删除表Test
IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(N'Test'))
	BEGIN
		DROP TABLE Test;
	END
GO

--创建表Test
CREATE TABLE Test
(
	id INT IDENTITY(1,1) PRIMARY KEY,--id自动编号，并设为主键
	[name] VARCHAR(20) NOT NULL,
	sex CHAR(2) NOT NULL CHECK(sex IN ('男','女')), --约束,限制值为男女;
	birthday DATETIME NOT NULL CHECK(birthday BETWEEN '1950-01-01' AND '1988-12-31'), --约束生日,限制出现的时间范围
	phone CHAR(11) NOT NULL CHECK(LEN(phone)=11), --限制手机号码的长度必须为11;
	remark VARCHAR(MAX) DEFAULT '请在这里填写备注', --设置默认值
	tId INT NOT NULL FOREIGN KEY(tId) REFERENCES Test2(id), --添加外键,受Test2中的ID列约束
	age AS DATEDIFF(yyyy,birthday,GETDATE())--计算列。
);


--添加数据
INSERT Test2(captainId,tName) VALUES('1','第一组');
GO
INSERT Test([name],sex,birthday,phone,tId) VALUES('张三','男','1987-12-12','12345678912','1');
GO
SELECT * FROM Test2;
GO
SELECT * FROM Test;