MS Server 数据库准备脚本

use master  --表示下面的操作是针对master数据库完成的
go
--判断当前数据库是否在master数据库中已经存在
if exists(select * from sysdatabases where name=MISDB)
drop database MISDB   --删除数据库
go

--创建数据库
create database MISDB
on primary  
(
      name=MISDB_Mdata,  --数据库文件的逻辑名称（数据库服务内部使用的）
  filename=D:\C#study\DB\MISDB_Mdata.mdf,  --数据库物理文件名（绝对路径）
  size=30MB, --数据库文件初始大小
  filegrowth=1MB  --当数据超过文件大小的时候自动增长量
),
(   --次要数据文件（可以有多个，也可以没有）
      name=MISDB_ndata,  
  filename=D:\C#study\DB\MISDB_ndata.ndf, 
  size=20MB,
  filegrowth=1MB 
)
log on
(
      name=MISDB_log1,  
  filename=D:\C#study\DB\MISDB_log1.ldf, 
  size=2MB,
  filegrowth=1MB 
),
(
      name=MISDB_log2,  
  filename=D:\C#study\DB\MISDB_log2.ldf, 
  size=2MB,
  filegrowth=1MB 
)
go
--添加数据表
use MISDB
go
--部门信息表
if exists(select * from sysobjects where name=Department)
drop table Department
go
create table Department
(
    DepartmentId int identity(10,1) primary key,  --主键、标识列（从10开始，每次递增1）
DepartmentName varchar(50) not null,  --部门名称，非空
)
go
--职位信息表
if exists(select * from sysobjects where name=Post)
drop table Post
go
create table Post
(
     PostId int  identity(10,1) primary key,
PostName varchar(50) not null
)
go
--员工信息表
if exists(select * from sysobjects where name=Employee)
drop table Employee
go
create table Employee
(
      EmployeeID int identity(1000,1) primary key,   --主键约束
  EmployeeName varchar(50) not null,    --非空约束
  Gender char(2) not null,
  NowAddress nvarchar(100) default(地址不详),  --默认约束
  IdNo char(18) not null check(len(IdNo)=18),  --检查约束
  WeiXinNumber varchar(20) not null,
  PhoneNumber varchar(50) not null,
  OtherWork nvarchar(50) null,
  EntryDate datetime not null,
  PostId int references Post(PostId) ,--外键约束（职位编号）
  DepartmentId int references Department(DepartmentId)  --外键（部门编号）
)
go
--插入测试数据   （常见操作：insert update delete select ）

--经验：首先要插入没有《外键约束》的主键表的数据。
use MISDB
go
insert into Department(DepartmentName) values(开发部),(测试部),(实施部),(财务部),(人事部)
--insert into Department(DepartmentName) values(测试部)
--insert into Department(DepartmentName) values(实施部)
--insert into Department(DepartmentName) values(财务部)
--insert into Department(DepartmentName) values(人事部)


--select DepartmentId,DepartmentName from Department
insert into Post(PostName) values(软件工程师),(测试工程师),(实施工程师),(财务经理),(人事经理)



insert into Employee(EmployeeName, Gender, NowAddress, IdNo, WeiXinNumber, PhoneNumber, OtherWork, EntryDate, PostId, DepartmentId)
values
(Kiter10,男,天津,123226199012111510,thinger01,13600001201,暂无,2025-01-01,10,10),
(Kiter11,男,北京,123226199012111511,thinger02,13600001202,暂无,2020-01-02,11,11),
(Kiter12,女,上海,123226199012111512,thinger03,13600001203,暂无,2020-01-03,12,13),
(Kiter13,男,广州,123226199012111513,thinger04,13600001204,暂无,2020-01-04,10,10),
(Kiter14,男,苏州,123226199012111514,thinger05,13600001205,暂无,2020-01-05,12,12),
(Kiter15,女,南宁,123226199012111515,thinger06,13600001206,暂无,2020-01-06,10,10),
(Kiter16,男,德州,123226199012111516,thinger07,13600001207,暂无,2020-01-07,11,11),
(Kiter17,男,衢州,123226199012111517,thinger08,13600001208,暂无,2020-01-08,12,12),
(Kiter18,女,西安,123226199012111518,thinger09,13600001209,暂无,2020-01-09,10,10),
(Kiter19,男,咸阳,123226199012111519,thinger10,13600001210,暂无,2020-01-10,11,11),
(Kiter20,女,沈阳,123226199012111520,thinger11,13600001211,暂无,2020-01-11,14,14)


select * from Employee
select * from Department
select * from Post

--select EmployeeName, Gender, NowAddress, IdNo, WeiXinNumber,PostId from Employee
-- where PostId=10 and DepartmentId=10

-- select EmployeeName, Gender, NowAddress, IdNo, WeiXinNumber,PostId from Employee
-- where PostId>10  

--  select EmployeeId,EmployeeName, Gender, NowAddress, IdNo, WeiXinNumber,PostId from Employee
-- where Gender=男

 --更新的话，一定要注意写where
 --update Employee set  EmployeeName=王老师 ,NowAddress=天津 where EmployeeId=1001

 --删除的话，一定要注意写where
 --delete from Employee where EmployeeId=1002

 -- delete from Employee where Gender=男

 -- delete from Employee

 -- 这个无法执行删除（因为主键值，已经被外键表使用）
 -- delete from Department where DepartmentId=11

 -- 如果要强制删除，可以在确保安全的情况下，先删除外键表数据
 -- delete from Employee where DepartmentId=11

  --连接查询和汇总查询
--  select EmployeeName, Gender, NowAddress, IdNo,PostName, DepartmentName,Post.PostId from Employee
--  inner join Post on Employee.PostId=Post.PostId
--  inner join Department on Employee.DepartmentId=Department.DepartmentId

--  --改变列的名称
--    select 员工姓名=EmployeeName, 性别=Gender, NowAddress as 住址, IdNo,PostName, DepartmentName,Post.PostId from Employee
--  inner join Post on Employee.PostId=Post.PostId
--  inner join Department on Employee.DepartmentId=Department.DepartmentId

--  --聚合查询
--select count(*) as 员工总数 from Employee
--select 最大编号=max(EmployeeId)  from Employee
--select 最小编号=min(EmployeeId)  from Employee

--select  员工总数=count(*) ,最大编号=max(EmployeeId),最小编号=min(EmployeeId)  from Employee

--select 平均编号=avg(EmployeeId)  from Employee