MS SQL基础
MS SQL基础
创建数据库
1 use master 2 go 3 --判断当前数据库是否存在 4 if exists(select * from sysdatabases where name='StudentManageDB' ) 5 drop database StudentManageDB 6 go 7 --创建数据库 8 create database StudentManageDB 9 on primary 10 ( 11 name='StudentManageDB_data',--数据库文件的逻辑名 12 filename='D:\DB\StudentManageDB_data.mdf',--数据库物理文件名（绝对路径） 13 size=10MB,--数据库文件初始大小 14 filegrowth=5MB --数据文件增长量 15 ), 16 ( 17 name='StudentManageDB_data1', 18 filename='D:\DB\StudentManageDB_data1.ndf', 19 size=10MB, 20 filegrowth=5MB 21 ) 22 --创建日志文件 23 log on 24 ( 25 name='StudentManageDB_log', 26 filename='D:\DB\StudentManageDB_log.ldf', 27 size=5MB, 28 filegrowth=2MB 29 ), 30 ( 31 name='StudentManageDB_log1', 32 filename='D:\DB\StudentManageDB_log1.ldf', 33 size=5MB, 34 filegrowth=2MB 35 ) 36 go 37 --创建数据库
数据库完整性设计
1 --创建数据表的各种约束 2 use StudentManageDB 3 go 4 --创建“主键”约束primary key 5 if exists(select * from sysobjects where name='pk_StudentId') 6 alter table Students drop constraint pk_StudentId 7 alter table Students 8 add constraint pk_StudentId primary key (StudentId) 9 10 --创建检查约束check 11 if exists(select * from sysobjects where name='ck_Age') 12 alter table Students drop constraint ck_Age 13 alter table Students 14 add constraint ck_Age check (Age between 18 and 35) 15 16 --创建唯一约束unique 17 if exists(select * from sysobjects where name='uq_StudentIdNo') 18 alter table Students drop constraint uq_StudentIdNo 19 alter table Students 20 add constraint uq_StudentIdNo unique (StudentIdNo) 21 22 23 --创建身份证的长度检查约束 24 if exists(select * from sysobjects where name='ck_StudentIdNo') 25 alter table Students drop constraint ck_StudentIdNo 26 alter table Students 27 add constraint ck_StudentIdNo check (len(StudentIdNo)=18) 28 29 --创建默认约束 30 if exists(select * from sysobjects where name='df_StudentAddress') 31 alter table Students drop constraint df_StudentAddress 32 alter table Students 33 add constraint df_StudentAddress default ('地址不详' ) for StudentAddress 34 35 if exists(select * from sysobjects where name='df_UpdateTime') 36 alter table ScoreList drop constraint df_UpdateTime 37 alter table ScoreList 38 add constraint df_UpdateTime default (getdate() ) for UpdateTime 39 40 --创建外键约束 41 if exists(select * from sysobjects where name='fk_classId') 42 alter table Students drop constraint fk_classId 43 alter table Students 44 add constraint fk_classId foreign key (ClassId) references StudentClass(ClassId) 45 46 if exists(select * from sysobjects where name='fk_StudentId') 47 alter table ScoreList drop constraint fk_StudentId 48 alter table ScoreList 49 add constraint fk_StudentId foreign key(StudentId) references Students(StudentId)
1 use StudentManageDB 2 go 3 --创建主键约束 4 5 if exists(select * from sysobjects where name='pk_StudentId') 6 alter table Students drop constraint pk_StudentId 7 alter table Students add constraint pk_StudentId primary key(StudentId) 8 9 --创建唯一约束 10 if exists(select * from sysobjects where name='uq_StudentIdNo') 11 alter table Students drop constraint uq_StudentIdNo 12 alter table Students add constraint uq_StudentIdNo unique(StudentIdNo) 13 14 --创建检查约束 15 if exists(select * from sysobjects where name='ck_Age') 16 alter table Students drop constraint ck_Age 17 alter table Students add constraint ck_Age check(Age between 18 and 25) 18 19 if exists(select * from sysobjects where name='ck_PhoneNumber') 20 alter table Students drop constraint ck_PhoneNumber 21 alter table Students add constraint ck_PhoneNumber check(len(PhoneNumber)=11) 22 23 24 update Students set PhoneNumber='13099012876' where StudentId=10000 25 select * from Students 26 27 28 if exists(select * from sysobjects where name='df_StudentAddress') 29 alter table Students drop constraint df_StudentAddress 30 alter table Students add constraint df_StudentAddress default('地址不详') for StudentAddress 31 32 insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo ,PhoneNumber, 33 StudentAddress,ClassId) 34 values('李小璐','女','1989-01-12',24,120229198901121315, '13099012876',default,1) 35 36 insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo ,PhoneNumber, 37 ClassId) 38 values('李小璐','女','1989-01-12',24,120229198901121316, '13099012876',1) 39 40 41 insert into StudentClass (ClassId,ClassName) values(1,'软件1班') 42 if exists(select * from sysobjects where name='fk_ClassId') 43 alter table Students drop constraint fk_ClassId 44 alter table Students add constraint fk_ClassId foreign key (ClassId) references StudentClass(ClassId) 45 46 select * from studentClass
示例：StudentManageDB
1 --指向当前要使用的数据库 2 use master 3 go 4 5 --判断当前数据库是否存在 6 if exists (select * from sysdatabases where name='StudentManageDB') 7 drop database StudentManageDB--删除数据库 8 go 9 --创建数据库 10 create database StudentManageDB 11 on primary 12 ( 13 --数据库文件的逻辑名 14 name='StudentManageDB_data', 15 --数据库物理文件名（绝对路径） 16 filename='D:\DB\StudentManageDB_data.mdf', 17 --数据库文件初始大小 18 size=10MB, 19 --数据文件增长量 20 filegrowth=1MB 21 ) 22 --创建日志文件 23 log on 24 ( 25 name='StudentManageDB_log', 26 filename='D:\DB\StudentManageDB_log.ldf', 27 size=2MB, 28 filegrowth=1MB 29 ) 30 go 31 --创建学员信息数据表 32 use StudentManageDB 33 go 34 if exists (select * from sysobjects where name='Students') 35 drop table Students 36 go 37 create table Students 38 ( 39 StudentId int identity(100000,1) , 40 StudentName varchar(20) not null, 41 Gender char(2) not null, 42 Birthday datetime not null, 43 StudentIdNo numeric(18,0) not null,--身份证号 44 Age int not null, 45 PhoneNumber varchar(50), 46 StudentAddress varchar(500), 47 ClassId int not null --班级外键 48 ) 49 go 50 --创建班级表 51 if exists(select * from sysobjects where name='StudentClass') 52 drop table StudentClass 53 go 54 create table StudentClass 55 ( 56 ClassId int primary key, 57 ClassName varchar(20) not null 58 ) 59 go 60 --创建成绩表 61 if exists(select * from sysobjects where name='ScoreList') 62 drop table ScoreList 63 go 64 create table ScoreList 65 ( 66 Id int identity(1,1) primary key, 67 StudentId int not null, 68 CSharp int null, 69 SQLServerDB int null, 70 UpdateTime datetime 71 ) 72 go 73 --创建管理员用户表 74 if exists(select * from sysobjects where name='Admins') 75 drop table Admins 76 create table Admins 77 ( 78 LoginId int identity(1000,1) primary key, 79 LoginPwd varchar(20) not null, 80 AdminName varchar(20) not null 81 ) 82 go 83 --创建数据表的各种约束 84 use StudentManageDB 85 go 86 --创建“主键”约束primary key 87 if exists(select * from sysobjects where name='pk_StudentId') 88 alter table Students drop constraint pk_StudentId 89 alter table Students 90 add constraint pk_StudentId primary key (StudentId) 91 92 --创建检查约束check 93 if exists(select * from sysobjects where name='ck_Age') 94 alter table Students drop constraint ck_Age 95 alter table Students 96 add constraint ck_Age check (Age between 18 and 35) 97 98 --创建唯一约束unique 99 if exists(select * from sysobjects where name='uq_StudentIdNo') 100 alter table Students drop constraint uq_StudentIdNo 101 alter table Students 102 add constraint uq_StudentIdNo unique (StudentIdNo) 103 104 105 --创建身份证的长度检查约束 106 if exists(select * from sysobjects where name='ck_StudentIdNo') 107 alter table Students drop constraint ck_StudentIdNo 108 alter table Students 109 add constraint ck_StudentIdNo check (len(StudentIdNo)=18) 110 111 --创建默认约束 112 if exists(select * from sysobjects where name='df_StudentAddress') 113 alter table Students drop constraint df_StudentAddress 114 alter table Students 115 add constraint df_StudentAddress default ('地址不详' ) for StudentAddress 116 117 if exists(select * from sysobjects where name='df_UpdateTime') 118 alter table ScoreList drop constraint df_UpdateTime 119 alter table ScoreList 120 add constraint df_UpdateTime default (getdate() ) for UpdateTime 121 122 --创建外键约束 123 if exists(select * from sysobjects where name='fk_classId') 124 alter table Students drop constraint fk_classId 125 alter table Students 126 add constraint fk_classId foreign key (ClassId) references StudentClass(ClassId) 127 128 if exists(select * from sysobjects where name='fk_StudentId') 129 alter table ScoreList drop constraint fk_StudentId 130 alter table ScoreList 131 add constraint fk_StudentId foreign key(StudentId) references Students(StudentId) 132 133 134 -------------------------------------------插入数据-------------------------------------- 135 use StudentManageDB 136 go 137 138 --插入班级数据 139 insert into StudentClass(ClassId,ClassName) values(1,'软件1班') 140 insert into StudentClass(ClassId,ClassName) values(2,'软件2班') 141 insert into StudentClass(ClassId,ClassName) values(3,'计算机1班') 142 insert into StudentClass(ClassId,ClassName) values(4,'计算机2班') 143 --insert into StudentClass(ClassId,ClassName) values(5,'网络1班') 144 --insert into StudentClass(ClassId,ClassName) values(6,'网络2班') 145 146 --插入学员信息 147 insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId) 148 values('王小虎','男','1989-08-07',22,120223198908071111,'022-22222222','天津市南开区红磡公寓5-5-102',1) 149 insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId) 150 values('贺小张','女','1989-05-06',22,120223198905062426,'022-33333333','天津市河北区王串场58号',2) 151 insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId) 152 values('马小李','男','1990-02-07',21,120223199002078915,'022-44444444','天津市红桥区丁字沽曙光路79号',4) 153 insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId) 154 values('冯小强','女','1987-05-12',24,130223198705125167,'022-55555555',default,2) 155 insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId) 156 values('杜小丽','女','1986-05-08',25,130223198605081528,'022-66666666','河北衡水路北道69号',1) 157 insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId) 158 values('王俊桥','男','1987-07-18',24,130223198707182235,'022-77777777',default,1) 159 insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId) 160 values('张永利','男','1988-09-28',24,130223198909282235,'022-88888888','河北保定市风华道12号',3) 161 insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId) 162 values('李铭','男','1987-01-18',24,130223198701182257,'022-99999999','河北邢台市幸福路5号',1) 163 insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId) 164 values('宁俊燕','女','1987-06-15',24,130223198706152211,'022-11111111',default,3) 165 insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId) 166 values('刘玲玲','女','1989-08-19',24,130223198908192235,'022-11111222',default,4) 167 168 169 170 --插入成绩信息 171 insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100000,60,78) 172 insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100001,55,88) 173 insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100002,90,58) 174 insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100003,88,75) 175 176 insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100004,62,88) 177 insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100006,52,80) 178 insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100007,91,66) 179 insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100009,78,35) 180 181 --插入管理员信息 182 insert into Admins (LoginPwd,AdminName) values(123456,'王晓军') 183 insert into Admins (LoginPwd,AdminName) values(123456,'张明丽') 184 185 --删除学员信息 186 --delete from Students 187 188 --truncate table Students --删除全部数据以后，自动标识列重新编号 189 190 --显示学员信息和班级信息 191 select * from Students 192 select * from StudentClass 193 select * from ScoreList 194 select * from Admins
END
