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

1 --建数据库

2 create databasecstn3 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=10mb18 )19 go

20 --调用数据库

21 usecstn22 go

23 --建表

24 create tableStudent25 (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 intostudent36 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 constraintUQ_Student_tSAge46 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'男')fortsgender55 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 * fromstudent72 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

--署名：黄飘龙