sql 常用语句脚本代码
1 -------------------------一,数据库操作-----------------------------
2 ------------1,创建数据库------------------
3 IF DB_ID(‘TestDB‘) IS NOT NULL DROP DATABASE TestDB;
4
5 CREATE DATABASE TestDB ON
6 (
7 NAME=‘TestDB‘,
8 FILENAME=‘d:\mcgrady\db\TestDB.mdf‘,
9 SIZE=4,
10 MAXSIZE=10,
11 FILEGROWTH=1
12 );
13
14 ------------2,备份数据库------------------
15 BACKUP DATABASE TestDB TO DISK=‘d:\mcgrady\db\bak\TestDB.bak‘;
16
17 ------------4,删除数据库------------------
18 --IF DB_ID(‘TestDB‘) IS NOT NULL DROP DATABASE TestDB;
19
20 ------------5,删除数据库日志文件------------------
21 --DBCC ERRORLOG
22 --GO 6
23
24 -------------------------二,数据表操作-----------------------------
25 USE TestDB;
26 GO
27 ------------1,创建数据表------------------
28 IF OBJECT_ID(‘dbo.SM_User‘) IS NOT NULL DROP TABLE dbo.SM_User;
29
30 CREATE TABLE SM_User
31 (
32 ID INT NOT NULL IDENTITY,
33 UserId NVARCHAR(20) NOT NULL,
34 PASSWORD NVARCHAR(20) NOT NULL,
35 NAME NVARCHAR(20) NOT NULL,
36 Gender TINYINT NULL
37 CONSTRAINT DFT_User_Gender DEFAULT(0),
38 CreateTime DATETIME NULL
39 CONSTRAINT DFT_User_CreateTime DEFAULT(GETDATE()),
40 DESCRIPTION NVARCHAR(max) NULL,
41 CONSTRAINT PK_SM_User PRIMARY KEY(ID)
42 );
43
44 ------------2,删除数据表------------------
45 --IF OBJECT_ID(‘dbo.SM_User‘) IS NOT NULL DROP TABLE dbo.SM_User; -- 也可以使用TRUNCATE TABLE
46
47 -------------------------三,字段操作-----------------------------
48 ------------1,添加字段------------------
49 ALTER TABLE dbo.SM_User ADD Item1 NVARCHAR(MAX) NULL;
50
51 ------------2,修改字段------------------
52 ALTER TABLE dbo.SM_User ALTER COLUMN Item1 NVARCHAR(50);
53
54 ------------3,删除字段------------------
55 ALTER TABLE dbo.SM_User DROP COLUMN Item1;
56
57 ------------4,添加约束------------------
58 ALTER TABLE dbo.SM_User ADD CONSTRAINT chk_user_createtime CHECK(CreateTime<=GETDATE());
59
60 ------------5,删除约束------------------
61 ALTER TABLE dbo.SM_User DROP CONSTRAINT chk_user_createtime;
62
63 ------------6,添加默认值----------------
64 ALTER TABLE dbo.SM_User ADD CONSTRAINT DFT_User_Password DEFAULT ‘123‘ FOR PASSWORD;
65
66