mssql数据库基本语句总结（1）

 1 --建数据库
 2 create database cstn 
 3 on primary  
 4 (
 5     size=10mb,
 6     maxsize=100mb,
 7     filename='d:\db\cstn.mdf',
 8     name='cstn',
 9     filegrowth=100%
10 )
11 log on 
12 (
13     size=10mb,
14     maxsize=100mb,
15     filename='d:\db\cstn.ldf',
16     name='cstn_log',
17     filegrowth=10mb
18 )
19 go
20 --调用数据库
21 use cstn
22 go
23 --建表
24 create table Student
25 (
26     tId int identity(1,1) primary key,
27     tSId int not null,
28     tSName nvarchar(10),
29 )
30 go
31 --增加一列column
32 alter table Student add tSAge int 
33 go
34 --给表插入一些数据
35 insert into student 
36 select 01,N'关羽',24 union all
37 select 02,N'赵云',23 union all
38 select 03,N'张飞',22 union all 
39 select 04,N'刘备',25
40 go
41 --增加唯一性约束
42 alter table Student add constraint UQ_Student_tSAge unique(tsage)
43 go
44 --删除唯一性约束
45 alter table student drop constraint UQ_Student_tSAge
46 go
47 --增加唯一性约束
48 alter table student add constraint UQ_Student_tSAge unique(tsname) 
49 go
50 --增加一列
51 alter table student add tSGender nchar(2)
52 go
53 --增加默认约束
54 alter table student add constraint DF_Student_tSGender default(N'男')for tsgender
55 go
56 --给表中一列数据统一修改值
57 update student set tsgender=N'男' 
58 go 
59 --给表插入数据（一条一条插入）
60 insert into student values(06,N'小乔',18,N'女')
61 insert into student(tSId,tsname,tsage) values(07,N'周瑜',23)
62 insert into student(tSId,tsname,tsage) values(05,N'孔明',24)
63 insert into student values(08,N'大乔',19,N'女')
64 insert into student values(09,N'貂蝉',18,N'女')
65 insert into student values(10,N'吕布',28,'男')
66 go
67 --修改表中一列数据中的其中一个数据的值
68 update student set tsgender=N'男'  where tsid=10 
69 go
70 --查询表中数据
71 select * from student 
72 go
73 
74 --署名：黄飘龙

--建数据库
create database cstn
on primary
(
size=10mb,
maxsize=100mb,
filename='d:\db\cstn.mdf',
name='cstn',
filegrowth=100%
)
log on
(
size=10mb,
maxsize=100mb,
filename='d:\db\cstn.ldf',
name='cstn_log',
filegrowth=10mb
)
go
--调用数据库
use cstn
go
--建表
create table Student
(
tId int identity(1,1) primary key,
tSId int not null,
tSName nvarchar(10),
)
go
--增加一列column
alter table Student add tSAge int
go
--给表插入一些数据
insert into student
select 01,N'关羽',24 union all
select 02,N'赵云',23 union all
select 03,N'张飞',22 union all
select 04,N'刘备',25
go
--增加唯一性约束
alter table Student add constraint UQ_Student_tSAge unique(tsage)
go
--删除唯一性约束
alter table student drop constraint UQ_Student_tSAge
go
--增加唯一性约束
alter table student add constraint UQ_Student_tSAge unique(tsname)
go
--增加一列
alter table student add tSGender nchar(2)
go
--增加默认约束
alter table student add constraint DF_Student_tSGender default(N'男')for tsgender
go
--给表中一列数据统一修改值
update student set tsgender=N'男'
go
--给表插入数据（一条一条插入）
insert into student values(06,N'小乔',18,N'女')
insert into student(tSId,tsname,tsage) values(07,N'周瑜',23)
insert into student(tSId,tsname,tsage) values(05,N'孔明',24)
insert into student values(08,N'大乔',19,N'女')
insert into student values(09,N'貂蝉',18,N'女')
insert into student values(10,N'吕布',28,'男')
go
--修改表中一列数据中的其中一个数据的值
update student set tsgender=N'男' where tsid=10
go
--查询表中数据
select * from student
go

--署名：黄飘龙