点击查看代码
if exists(select * from sys.objects where name='Department' and type='U')
drop table Department
create table Department
(
--id identity(x,y) 初始x 自增y,primary key 主键
DepartmentId int primary key identity(1,1),
--名称
DepartmentName nvarchar(50) not null,
--描述
DepartmentRemark text
)
--字符串
--char(x) 占用x个字节 ‘ab’=>x个字节
--varchar(x) 最多占用x个字节 ‘ab’=>2个(x>2)
--text 长文本
--char text varchar 前面加n;存储unicode 对中文友好
--例子
--varchar(100) 100个字母或者50个汉字
--nvarchar(100) 100个字母或者100个汉字
--Rank是关键字了,所以加上[]
create table [Rank]
(
--id identity(x,y) 初始x 自增y,primary key 主键
RankId int primary key identity(1,1),
--名称
RankName nvarchar(50) not null,
--描述
RankRemark text
)
if exists(select * from sys.objects where name='People' and type='U')
drop table People
create table People(
--id identity(x,y) 初始x 自增y,primary key 主键
PeopleId int primary key identity(1,1),
--部门 references 引用外键 引用在部门表的id范围之内
DepartmentId int references Department(DepartmentId)not null ,
--职员
RankId int references [Rank](RankId)not null,
--名称
PeopleName nvarchar(50) not null,
--性别 加上约束(check) 默认
PeopleSex nvarchar(1)default('男') check(PeopleSex='男' or PeopleSex='女')not null,
--老版本date 新版本有time 加上samll就是表示现在时间 不能表示未来
PeopleBirth smalldatetime not null,
--小数的使用 float 可能有误差 decimal(x,y) 长度x,小数y位
PeopleSalary decimal(12,2) check(PeopleSalary>=1000 and PeopleSalary
点击查看代码
use Mytest
select *from Department
select * from [Rank]
select * from People
--数据crud
--修改
--update 表名 set 列名='xxx' where 条件
--update 表名 set 列名='xxx', 列名='yyy' where 条件
update Department set DepartmentName='经理部' where DepartmentId=8
update People set PeopleSalary=PeopleSalary+1000
update People set PeopleSalary=15000 where RankId=3 and PeopleSalary'1988-8-7' and PeopleBirth5000
select* from People where (MONTH(PeopleBirth)=11 and DAY(PeopleBirth)