1 Star 0 Fork 48

吕宇鹏/SQL Server作业仓库

加入 Gitee
与超过 1200万 开发者一起发现、参与优秀开源项目,私有仓库也完全免费 :)
免费加入
文件
该仓库未声明开源许可证文件(LICENSE),使用请关注具体项目描述及其代码上游依赖。
克隆/下载
SQLQuery8.sql 3.25 KB
一键复制 编辑 原始数据 按行查看 历史
五星好市民.林 提交于 2021-03-08 23:21 . 林程铭第4次作业
create database company1--创建数据库
on primary
(
--主数据文件参数设置
name='company1',
filename='C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\company1.mdf',
size=5MB,
filegrowth=1MB,
maxsize=unlimited
),
(
--次数据文件参数设置
name='company1',
filename='C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\company2.ndf',
size=5MB,
filegrowth=1MB,
maxsize=unlimited
),
filegroup group1
(
--日志文件参数设置
name='company1_log',
filename='C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\company2.ldf',
size=2MB,
filegrowth=10%,
maxsize=unlimited
)
----------------------
--创建数据库表
create table sectioninfo--部门信息表
(
sectionid int identity(1,1),
sectionname varchar(5)
)
create table userinfo
(
userno int identity(1,1),
username varchar(5),
usersex varchar(1),
userage int ,
useraddress varchar(20),
usersection int,
)
create table workinfo
(
workid int identity(1,1),
userid int ,
worktime datetime ,
workdescription varchar(20)
)
------------------
--修改部门信息表设置
alter table sectioninfo
alter column sectionname varchar(10)
--修改员工信息表设置
alter table userinfo
alter column username varchar(10)
alter table userinfo
alter column usersex varchar(2)
alter table userinfo
alter column useraddress varchar(50)
--修改员工考勤表设置
alter table workinfo
alter column workdescription varchar(40)
----------------
--约束部门信息表进行设置
alter table sectioninfo
add constraint PK_sectioninfo_sectionid primary key(sectionid)
alter table sectioninfo
add constraint sectionname check(sectionname is not null)
--约束员工信息表进行设置
alter table userinfo
add constraint PK_userinfo_userno primary key(userno)
alter table userinfo
add constraint userno check(userno is not null)
alter table userinfo
add constraint username check(username is not null)
alter table userinfo
add constraint username unique(username)
alter table userinfo
add constraint usersex check(usersex is not null)
alter table userinfo
add constraint usersex check(usersex='男' or usersex='女')
alter table userinfo
add constraint userage check(userage is not null)
alter table userinfo
add constraint userage check(userage>=0 and userage<=100)
alter table userinfo
add constraint useraddress default(湖北)
alter table userinfo
add constraint FK_userinfo_usersection foreign key(usersection) references userinfo(usersection)
--约束员工考勤表进行设置
alter table workinfo
add constraint PK_workinfo_workid primary key(workid)
alter table workinfo
add constraint workid check(workid is not null)
alter table workinfo
add constraint FK_workinfo_userid check(userid is not null)
alter table workinfo
add constraint worktime check(worktime is not null)
alter table workinfo
add constraint workdescription check(workdescription is not null)
alter table workinfo
add constraint workdescription check(workdescription='迟到' or workdescription='早退' or workdescription='旷工' or workdescription='病假' or workdescription='事假')
马建仓 AI 助手
尝试更多
代码解读
代码找茬
代码优化
1
https://gitee.com/lv-yupeng/sql-server-job-warehouse.git
git@gitee.com:lv-yupeng/sql-server-job-warehouse.git
lv-yupeng
sql-server-job-warehouse
SQL Server作业仓库
master

搜索帮助

0d507c66 1850385 C8b1a773 1850385