Sql Server学习笔记1: 创建数据库、创建表、简单查询语句

--创建数据库，需要在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