SQL-server 学习笔记（三）：实战建库建表，插入数据

建立三张表：部门（Department）、职级（[Rank]）、员工(People)

部门：部门标号作为主键

 

 

create database Department
use Department
create table Department
(
    DepartmenID int primary key not null identity(1,1),
    DepartmenName varchar(20) not null,
    DeartmenRemark text
)

职级：职级编号作为主键

 

 

 

use Department
create table [Rank]
(
    RankID int primary key not null identity(1,1),
    RankName varchar(20) not null,
    RankRemark text
)

员工：员工编号作为主键，部门和职级作为外键。为什么将部门和职级作为外键？必须保证部门和职级的信息在部门表和职级表里有，是合法的才可以。

 

 

 

use Department
create table People
(
    --PeopleID作为主键
    PeopleID int primary key not null identity(1,1),
    --RankID和DepartmentID作为外键
    RankID int not null references [Rank](RankID),
    DepartmentID int not null references Department(DepartmenID),
    --姓名
    PeopleName varchar(20) not null,
    --性别默认男，而且只能填男或女
    PeopleSex varchar(10) default('男') check(PeopleSex='男' or PeopleSex='女'),
    --生日数据是时间类型，smalldatetime比datetime精度小，范围小，占用空间小
    BirthData smalldatetime not null,
    --薪水用decimal这种专门储存精确数字的数据类型，(12,2)12代表数最多12位，2表示小数点后边2位。这样比float要精确。
    Salary decimal(12,2) not null check(Salary>=1000 and Salary<=1000000),
    --因为是电话号码，按照管理要求11位
    Callphone varchar(20) check(len(Callphone)=11),
    Address text,
    AddTime smalldatetime default(getdate())
)

use Department
insert into Department(DepartmenName,DeartmenRemark)
--一次性插入多行数据
values('市场部','负责开拓市场'),
('软件部','负责软件开发'),
('后勤部','负责后勤管理'),
('测试部','负责产品测试')
use Department
insert into [Rank](RankName,RankRemark)
values('初级','0-3年工作年龄是初级'),
('中级','4-10年工作年龄是中级'),
('高级','11年以上工作年龄是高级')

use Department
insert into people(DepartmentID,RankID,PeopleName,PeopleSex,BirthData,Salary,Callphone,Address,AddTime)
values(1,3,'刘备','男','1984-7-9',20000,'13554785452','成都',getdate()),
(1,2,'孙尚香','女','1987-7-9',15000,'13256854578','荆州',getdate()),
(1,1,'关羽','男','1988-8-8',12000,'13985745871','荆州',getdate()),
(2,1,'张飞','男','1990-8-8',8000,'13535987412','宜昌',getdate()),
(2,3,'赵云','男','1989-4-8',9000,'13845789568','宜昌',getdate()),
(3,3,'马超','男','1995-4-8',9500,'13878562568','香港',getdate()),
(3,2,'黄盖','男','1989-4-20',8500,'13335457412','武汉',getdate()),
(3,1,'貂蝉','女','1989-4-20',6500,'13437100050','武汉',getdate()),
(2,2,'曹操','男','1987-12-20',25000,'13889562354','北京',getdate()),
(2,3,'许褚','男','1981-11-11',9000,'13385299632','北京',getdate()),
(2,1,'典韦','男','1978-1-13',8000,'13478545263','上海',getdate()),
(1,1,'曹仁','男','1998-12-12',7500,'13878523695','深圳',getdate()),
(1,3,'孙坚','男','1968-11-22',9000,'13698545841','广州',getdate()),
(3,3,'孙策','男','1988-1-22',11000,'13558745874','深圳',getdate()),
(3,2,'孙权','男','1990-2-21',12000,'13698745214','深圳',getdate()),
(3,2,'大乔','女','1995-2-21',13000,'13985478512','上海',getdate()),
(2,1,'小乔','女','1996-2-21',13500,'13778787874','北京',getdate()),
(1,2,'周瑜','男','1992-10-11',8000,'13987455214','武汉',getdate()),
(2,3,'鲁肃','男','1984-9-10',5500,'13254785965','成都',getdate()),
(3,3,'吕蒙','男','1987-5-19',8500,'13352197364','成都',getdate()),
(1,1,'陆逊','男','1996-5-19',7500,'13025457392','南京',getdate()),
(1,2,'太史慈','男','1983-6-1',7500,'13077778888','上海',getdate()),
(1,2,'aaaa','男','1983-6-1',7500,'13077778888','上海',getdate())

这样一个建库、建表、插入数据就完成了，下一步是表中的数据修改。