创建数据库:
--创建数据库,需要在master数据库里面注册**
use master
go
--drop语句仅仅适合创建数据库开始期间使用,其他时候不要使用!
if exists( select * from sysdatabases where name='MyMISDB')
drop database MyMISDB
go
create database MyMISDB
on primary --主要文件组
(
--下面创建的是主数据文件**
name='MyMISDB_data', --数据库的逻辑名(这个必须唯一,就是数据库系统用的)
filename='D:\DB\MyMISDB_data.mdf', --数据库的物理文件名和绝对路径
size=20MB, --数据库文件的初始大小,通常是根据实际的需要来确定的
filegrowth=1MB --当数据超过文件大小的时候自动增长量
)
,
(
--下面创建的是次要数据文件**
name='MyMISDB_data2',
filename='D:\DB\MyMISDB_data2.ndf',
size=20MB,
filegrowth=1MB
)
log on
(
--下面创建的是日志文件**
name='MyMISDB_log',
filename='D:\DB\MyMISDB_log.ldf',
size=20MB,
filegrowth=1MB
)
go
use MyMISDB
go
--创建数据表(部门、职位、员工......)
if exists(select * from sysobjects where name='Department')
drop table Department
go
create table Department
(
DepartmentId int identity(10,1) primary key ,--主键,标识列
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) unique not null check(len(IdNo)=18),--unique:唯一约束 + 检查约束
--Age int not null check (age>=30 and age<=40),
WeiXinNumber varchar(20) not null,
PhoneNumber varchar(50) not null,
OtherWork nvarchar(50) null,
EntryDate Datetime not null, --入职时间(字段是一个日期类型)
--DepartmentName varchar(50) not null,--部门名称
--PostName varchar(50) not null --非空
--为什么在这个地方不能直接写部门名称和职位名称,而单独创建表呢?
--有三个原因:
--1. OOP对象职责明确原则,不符合。
--2. 数据的完整性被破坏。比如,我们没有添加员工之前,是不是部门就不存在?
--建立主外键的“引用关系”
PostId int references Post (PostId) not null ,--职位编号(外键约束)
DepartmentId int references Department (DepartmentId) not null --部门编号(外键约束)
)
go
use MyMISDB
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('人事部')
insert into Post(PostName)values('软件工程师'),('测试工程师'),('实施工程师'),('财务经理'),('人事经理')
select * from Department
select * from Post
--插入数据(EmployeeId是标识列,不能显式插入值)
insert into Employee (EmployeeName, Gender, NowAddress, IdNo, WeiXinNumber,
PhoneNumber, OtherWork, EntryDate, PostId, DepartmentId)
values
('Kiter10','男','天津','123226199012111510','thinger01','13600001201','暂无','2020-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)
--下面的数据不行(因为外键约束不对)
-- insert into Employee (EmployeeName, Gender, NowAddress, IdNo, WeiXinNumber,
--PhoneNumber, OtherWork, EntryDate, PostId, DepartmentId) values
-- ('Kiter12','男','天津','123226199012111512','thinger03','13600001205','暂无','2020-01-01',19,10)
select * from Employee
--查询部分字段
select EmployeeId,EmployeeName, Gender, NowAddress, IdNo, WeiXinNumber from Employee
where EmployeeId>1005 and NowAddress='德州'
--查询排序
select EmployeeId,EmployeeName, Gender, NowAddress, IdNo, WeiXinNumber from Employee
where EmployeeId>1002 order by NowAddress DESC --ASC
--内连接查询(将两个有主外键关系的表通过主外键关联查询)
select EmployeeId,EmployeeName,Gender,NowAddress, IdNo,职位编号=Employee.PostId,
职位名称=PostName, DepartmentName as 部门名称 from Employee
inner join Post on Post.PostId=Employee.PostId
inner join Department on Department.DepartmentId=Employee.DepartmentId
where EmployeeId>1005 Order by 部门名称 DESC
--修改(使用Update请务必注意用where条件)
update Employee set EmployeeName='哈哈哈',Gender='女' where EmployeeId=1007
select * from Employee
--删除(请务必注意用where条件)
delete from Employee where EmployeeId=1010
--自动生成列删除后,就被空出来了。
insert into Employee (EmployeeName, Gender, NowAddress, IdNo, WeiXinNumber,
PhoneNumber, OtherWork, EntryDate, PostId, DepartmentId)
values
('Kiter50','男','天津','123226199012111550','thinger01','13600001251','暂无','2020-01-01',10,10)
--聚合函数
select 员工总数=count(*) from Employee
select 编号最大数=max(EmployeeId) from Employee
select 编号最小数=min(EmployeeId) from Employee
select 编号平均值=avg(EmployeeId) from Employee