1，数据库的操作语法
if exists(select * from sys.databases where name='DBTEST')--判断数据库是否存在
--删除数据库
alter database [DBTEST] set offline with rollback immediate--删除现有数据库连接
drop database [DBTEST]
--创建数据库
CREATE DATABASE DBTEST
on --数据文件
(
name='DBTEST_Data',--逻辑名称
filename='C:\Users\Administrator\Desktop\VS\DBTEST_Data.mdf',--物理路径和名称
size=5MB,--文件的初始大小
filegrowth=2MB--文件增长方式可以写大小，也可以写百分比
)
Log on --日志文件
(
name='DBTEST_Log',--逻辑名称
filename='C:\Users\Administrator\Desktop\VS\DBTEST_Log.ldf',--物理路径和名称
size=5MB,--文件的初始大小
filegrowth=2MB--文件增长方式可以写大小，也可以写百分比
)
2，创建表及约束
use [DBTEST]
if exists(select * from sys.objects where name='Department' and type='U')
--删除表
drop table Department
--创建表
create table Department
(
--创建部门编号，int primary key：唯一标识 主键 identity:增长方式 参数1，初始值 参数2，增长方式
DepartmentId int primary key identity(1,1),
DepartmentName nvarchar(50) not null,
DepartmentRemark text
)
create table [Rank]
(
--创建职级编号
RankId int primary key identity(1,1),
RankName nvarchar(50) not null,
RankRemark text
)
create table People
(
--创建员工编号
PeopleId int primary key identity(1,1),
DepartmentId int references Department(DepartmentId) not null,--部门(引用外键)
RankId int references [Rank](RankId) not null,--职级(引用外键)
PeopleName nvarchar(50) not null,
PeopleSex nvarchar(1) default('男') check(peoplesex='男'or peoplesex='女'),--增加约束
PeopleBirth datetime not null,
PeopleSalary decimal(12,2),
PeoplePhone varchar(20) unique not null,--unique 约束该字段的唯一性
PeopleAddress varchar(200),
PeopleAddTime datetime default(getdate())
)
--添加列
Alter table People add PeopleMail varchar(200)
--删除列
Alter table People drop column PeopleMail
--修改列数据类型
Alter table People alter column PeopleMail varchar(300)
--删除约束
Alter table People drop constraint CK__People__PeopleSe__403A8C7D
--添加约束(check约束)
Alter table People add constraint CK__People1 check()
--添加约束(主键约束)
Alter table People add constraint CK__People1 primary key(列名)
--添加约束(唯一约束)
Alter table People add constraint CK__People1 unique(列名)
--添加约束(默认约束)
Alter table People add constraint CK__People1 default 默认值 for 列名
--添加约束(外键)
Alter table People add constraint CK__People1 foreign key(列名) references 关联表名(列名(主键))
3，数据的增删改
(1)插入数据
--插入数据
insert into Department(DepartmentName,DepartmentRemark)
values(N'市场部','......')
insert into Department(DepartmentName,DepartmentRemark)
values(N'软件部','......')
insert into Department(DepartmentName,DepartmentRemark)
values(N'企划部','......')
insert into Department(DepartmentName,DepartmentRemark)
values(N'财务部','......')
insert into Department(DepartmentName,DepartmentRemark)
values(N'研发部','......')
--一次性插入多条记录
insert into Department(DepartmentName,DepartmentRemark)
select N'硬件部','......' union
select N'实施部','......' union
select N'产品部','......'
--解决删除表 主键排序重置问题
delete from Department
dbcc checkident('Department',reseed,0)
(2)更新与删除
--更新语句
update 表名 set 字段1=值1,字段2=值2,...where 条件
--删除语句
delete from 表名 where 条件
--删除所有语句
delete from 表名
--关于删除的区别
drop--删除表对象
delete--删除对象的数据，可以有条件，同时自动编号不会复位
truncate--清空对象数据，不能有条件判定，同时自动编号会复位