数据库练习

create database WorkManagement
go
use WorkManagement
go
--创建外键能为空吗？
create table WorkerMoney
(
WorkerId int not null,
WorkerMoney int not null,
WorkerBonus int null
)
create table WorkerInfo
(
WorkerId int not null,
WorkerName char(20) not null,
WorkerSex char(2) not null,
WorkerOrder int not null,
WorkerCallPhone text null,
WorkerAddress text null
)
--创建约束
alter table WorkerInfo add constraint PK_WorkerId primary key (WorkerId)
alter table WorkerMoney add constraint FK_WorkerId foreign key (WorkerId) references WorkerInfo (WorkerId)
alter table WorkerMoney add constraint DF_WorkerMoney default (0) for WorkerMoney
alter table WorkerMoney add constraint DF_WorkerBonus default (0) for WorkerBonus
alter table WorkerInfo add constraint CH_WorkerSex check (WorkerSex='男' or WorkerSex='女')
alter table WorkerInfo add constraint DF_WorkerSex default ('男') for WorkerSex
alter table WorkerInfo add constraint DF_WorkerOrder default (0) for WorkerOrder
alter table WorkerInfo add constraint DF_WorkerCallPhone default ('没有') for WorkerCallPhone
alter table WorkerInfo add constraint DF_WorkerAddress default ('未知') for WorkerAddress
--WorkerInfo插入数据
insert into WorkerInfo (WorkerName,WorkerId,WorkerSex,WorkerOrder,WorkerCallPhone,WorkerAddress)
select '张三',1,'男',0,'4512545','无厘头' union
select '李四',2,'男',0,'54231747','未知' union
select '小明',3,'男',0,'4512545423','大冥界' union
select '张明',4,'男',0,'45125452423','未知' union
select '周晓',5,'女',0,'2421445243','未知' union
select '周迅',6,'女',0,'4562454','未知' union
select '晓晓',7,'女',0,'45125454578','未知'
--WorkerInfo创建备份
select * into WorkerInfoStock from WorkerInfo
--查询WorkInfo备份表
select WorkerId as 工号, WorkerName 工人姓名,WorkerOrder 工人等级 ,WorkerCallPhone as 工人电话 ,WorkerAddress 工人地址 from WorkerInfoStock
--WorkerMoney插入数据
insert into WorkerMoney(WorkerId,WorkerMoney,WorkerBonus)
select 1,4246 ,0 union
select 1,456,0union
select 2,8975,45 union
select 2,8956,345 union
select 3,589,3 union
select 4,2426,56 union
select 5,353 ,0 union
select 6,3451,100 union
select 7,1000,80
--备份WorkMoney
select * into WorkerMoneyStock from WorkerMoney
--查询WorkMoney
select WorkerId as 工号,WorkerMoney 工资,WorkerBonus 奖金 from WorkerMoney
--
use master
go
drop database WorkManagement