--创建数据库,创建表,修改表结构,维护约束,插入数据,删除数据
--创建数据库(简易版)
create database StudySQL
go
--切换数据库
use StudySQL
go

--创建表基本语法
--create table 表名
--(	字段1 数据类型,
--  字段2 数据类型
--)

--建表(部门,职级,员工)
if exists(select * from sys.objects where name='Department' and type='U')
drop table Department
--部门表
create table Department
(
	--部门编号
	--primary key 主键
	--identity(初始值,增长步长)
	DepartmentId int primary key identity(1,1),
	--部门名称
	DepartmentName nvarchar(50) not null,
	--部门描述
	DepartmentRemark text
)
--char	    定长,char(10),里面可存储10个字节,除去数据占用的字节,其他字节为空格.
--varchar	变长,varchar(10),最多占用10个字节.
--text		长文本
--char(10),存储'ab',占用10个字节
--varchar(10),存储'ab',占用2个字节

--char ,varchar, text 前面加n:	存储unicode字符,对中文友好.
--varchar(100)	存储100个字母或者50个汉字.
--nvarchar(100) 存储100个字母或者1000个汉字.

if exists(select * from sys.objects where name='Rank' and type='U')
drop table Rank
--职级表
create table [Rank]
(
	--职级编号
	--primary key 主键
	--identity(初始值,增长步长)
	RankId int primary key identity(1,1),
	--部门名称
	RankName nvarchar(50) not null,
	--部门描述
	RankRemark text
)


if exists(select * from sys.objects where name='People' and type='U')
drop table People
create table People
(
	--员工编号
	PeopleId int primary key identity(1,1),
	--部门(引用外键)
	DepartmentId int references Department(DepartmentId) not null,
	--职级(引用外键)
	RankId int references [Rank](RankId) not null,
	--姓名
	PeopleName nvarchar(50) not null,
	--性别
	--default设置默认值
	PeopleSex nvarchar(1) default('男') check(PeopleSex='男' or PeopleSex='女') not null,
	--生日
	--smalldatetime
	--date可存储年月日
	--datetime可存储年月日时分秒
	PeopleBirth   datetime not null,
	--工资
	--decimal(12,2)总长度12,小数点后2位
	PeopleSalary decimal(12,2) check(PeopleSalary>=1000 and PeopleSalary<=100000) not null,
	--电话
	--unique唯一约束
	PeoplePhone nvarchar(20)  unique not null,
	--地址
	PeopleAddress nvarchar(100),
	--添加时间
	PeopleAddTime smalldatetime default(getdate())
	--getdate() 获得当前时间
)


--修改表结构--------------------------------
--(1)添加列
--alter table 表名 add 新列名 数据类型
--给员工表添加一列邮箱
alter table People add PeopleeMail nvarchar(200)

--(2)添加列
--alter table 表名 drop  column 列名 
--删除邮箱列
alter table People drop column PeopleeMail

--(3)修改列
--alter table 表名 alter column 列名 数据类型
--修改地址varchar(300)为varchar(200)
alter table People alter column PeopleAddress varchar(200)
--若表中已有大量数据,修改列可能会报错


--维护约束(删除,添加)------------------------
----删除约束
--alter table 表名 drop constraint 约束名
--删除约束
--alter table People drop constraint CK__People__PeopleSa__46E78A0C

----添加约束
----添加 check约束
--alter table 表名 add constraint 约束名 check(表达式)
----添加 主键约束
--alter table 表名 add constraint 约束名 primary key(列名)
----添加 唯一约束
--alter table 表名 add constraint 约束名 unique(列名)
----添加 默认值约束
--alter table 表名 add constraint 约束名 default 默认值 for 列名
----添加 外键约束
--alter table 表名 add constraint 约束名 foreign key(列名) references 关联表名(列名)

--插入数据----------------------------------------
--向部门表插入数据
insert into Department(DepartmentName,DepartmentRemark)
values('市场部','......')
insert into Department(DepartmentName,DepartmentRemark)
values('软件部','......')
insert into Department(DepartmentName,DepartmentRemark)
values('企划部','......')
    
--向职级表插入数据---------------------------------------
insert into [Rank](RankName,RankRemark)
values('初级','辅助其他人完成任务')
insert into [Rank](RankName,RankRemark)
select '中级','具备上单中单能力' union
select '高级','可以带动全场节奏'







--1--
insert into Department(DepartmentName,DepartmentRemark)
values('软件部','......')
insert into Department values('硬件部','.....')
insert into Department(DepartmentName,DepartmentRemark)
values('市场部','......')
--2--
insert into [Rank](RankName,RankRemark)
values('初级','辅助其他人完成任务')
insert into [Rank](RankName,RankRemark)
select '中级','具备上单中单能力' union
select '高级','可以带动全场节奏'

--3 --
--向员工表插入数据-----------------------------------------
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress)
values(1,3,'刘备','男','1984-7-9',20000,'13551785452','成都')
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress)
values(1,2,'孙尚香','女','1987-7-9',15000,'13256854578','荆州')
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress)
values(1,1,'关羽','男','1988-8-8',12000,'13985745871','荆州')
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress)
values(2,1,'张飞','男','1990-8-8',8000,'13535987412','宜昌')
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress)
values(2,3,'赵云','男','1989-4-8',9000,'13845789568','宜昌')
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress)
values(3,3,'马超','男','1995-4-8',9500,'13878562568','香港')
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress)
values(3,2,'黄盖','男','1989-4-20',8500,'13335457412','武汉')
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress)
values(3,1,'貂蝉','女','1989-4-20',6500,'13437100050','武汉')
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress)
values(2,2,'曹操','男','1987-12-20',25000,'13889562354','北京')
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress)
values(2,3,'许褚','男','1981-11-11',9000,'13385299632','北京')
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress)
values(2,1,'典韦','男','1978-1-13',8000,'13478545263','上海')
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress)
values(1,1,'曹仁','男','1988-12-12',7500,'13878523695','深圳')
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress)
values(1,3,'孙坚','男','1968-11-22',9000,'13698545841','广州')
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress)
values(3,3,'孙策','男','1988-1-22',11000,'13558745874','深圳')
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress)
values(3,2,'孙权','男','1990-2-21',12000,'13698745214','深圳')
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress)
values(3,2,'大乔','女','1995-2-21',13000,'13985478512','上海 ')
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress)
values(2,1,'小乔','女','1996-2-21',13500,'13778787874','北京')
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress)
values(1,2,'周瑜','男','1992-10-11',8000,'13987455214','武汉')
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress)
values(2,3,'鲁肃','男','1984-9-10',5500,'13254785965','成都')
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress)
values(3,3,'吕蒙','男','1987-5-19',8500,'13352197364','成都')
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress)
values(1,1,'陆逊','男','1996-5-19',7500,'13025457392','南京')
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress)
values(1,2,'太史慈','男','1983-6-1',7500,'13077778888','上海')
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress)
values(1,2,'aaaa','男','1983-6-1',7500,'13077778888','上海')
--------------------------------------------------------------------
insert into Department values('软件研发部','公司核心部门')

-----
insert into Department(DepartmentName,DepartmentRemark)
select '市场部','什么都不懂,只知道吹牛' union
select '产品部','......' union
select '总经办','都是领导'


-----------------------------------------------------------------------
select * from Department
select * from [Rank]
select * from People

--修改表
--语法:
--update 表名 set 字段1=值1,字段2=值2 where 条件
--工资调整,每个人加薪1000元
update People set PeopleSalary=PeopleSalary+1000
--将员工编号为7的加薪500
update People set PeopleSalary=PeopleSalary+500 
where PeopleId=7
--将软件部(部门编号1)人员工资低于10000的调整为10000
update People set PeopleSalary=10000
where DepartmentId = 1 and Peoplesalary < 10000
--修改刘备的工资是以前的两倍,并且把刘备的地址修改成北京
update People set PeopleSalary=PeopleSalary*2,PeopleAddress='北京'
where PeopleName='刘备'

--
--删除数据--------------------------------------
--语法:
--delete from 表名 where 条件
--删除员工表的全部记录
delete from People
--删除市场部(部门编号3)中工资大于1万的人
delete from People where DepartmentId = 3 and PeopleSalary > 10000


--关于删除(drop,truncate,delete)
drop table People--删除整个表,表也没有了
truncate table People--删除数据(清空数据),表结构依然存在
delete from People --删除所有数据,表结构存在
--truncate 和 delete区别
--truncate 清空所有数据,不能有条件;
-----------删除数据后,再添加数据编号仍为1,2,3
--  delete 可以删除所有数据,也删除符合条件的数据
-----------删除数据后,此编号不存在,为4,5