/*

* 名称：人力资源管理系统数据库

*/

/*切换到系统数据库master；*/

USE master;

GO

/*若当前系统中存在HR表，则删除之；*/

IF EXISTS (SELECT 1 FROM sysdatabases WHERE name = 'HR')

BEGIN

DROP DATABASE HR;

END

GO

--创建数据库HR

create database HR

go

/*切换到HR数据库*/

USE HR;

GO

-----------------------------------------------------------------------

--在HR数据库中创建表---------1职位信息表JOBS

-----------------------------------------------------------------------

/*

* 表名：职位信息表

*/

CREATE TABLE JOBS

(

--职位编号

JOB_ID VARCHAR(10) NOT NULL,--职位名称，主键

JOB_TITLE NVARCHAR(20) NOT NULL,--最低薪资,不小于城市最低薪资标准1000

MIN_SALARY MONEY NOT NULL,--最高薪资，不小于最低薪资

MAX_SALARY MONEY NOT NULL,

);

GO

--职位主键约束

ALTER TABLE JOBS

ADD CONSTRAINT JOB_ID_PK PRIMARY KEY (JOB_ID);

--最低薪资检查约束

ALTER TABLE JOBS

ADD CONSTRAINT JOB_MIN_SALARY_CK CHECK(MIN_SALARY >= 1000);

--最高薪资检查约束

ALTER TABLE JOBS

ADD CONSTRAINT JOB_MAX_SALARY_CK CHECK(MAX_SALARY >= MIN_SALARY);

GO

--测试数据，同时添加多个数据（union all）

INSERT INTO JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)

SELECT 'AD_PRES', '行政总裁', 10000, 18000 UNION ALL

select 'IT_MGR', '项目经理', 8000, 12000 UNION ALL

select 'IT_PROG', '程序员', 4000, 10000 UNION ALL

select 'MK_MGR', '市场经理', 9000, 12000 UNION ALL

select 'MK_REP', '市场代表', 4000, 9000;

GO

/*查询职位表信息*/

SELECT JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY

FROM JOBS

go

----------------------------------------------------

--在HR数据库中创建表---------2部门信息表DEPARTMENTS

----------------------------------------------------

/*

* 表名：部门信息表

*/

CREATE TABLE DEPARTMENTS

(

--部门编号 主键 IDENTITY(1,1)以1开始,每次自动增长1----自增

DEPARTMENT_ID INT NOT NULL IDENTITY(1,1),

--部门名称

DEPARTMENT_NAME NVARCHAR(30)NOT NULL,

--部门主管编号

MANAGER_ID INT NULL,

);

GO

--职员主键约束

ALTER TABLE DEPARTMENTS

ADD CONSTRAINT DEPARTMENT_ID_PK PRIMARY KEY (DEPARTMENT_ID);

GO

--测试数据

/*

--第一种方法

INSERT INTO DEPARTMENTS(DEPARTMENT_NAME, MANAGER_ID)

VALUES('总裁办公室', NULL);

INSERT INTO DEPARTMENTS(DEPARTMENT_NAME, MANAGER_ID)

VALUES('市场部', NULL);

INSERT INTO DEPARTMENTS(DEPARTMENT_NAME, MANAGER_ID)

VALUES('软件部', NULL);

INSERT INTO DEPARTMENTS(DEPARTMENT_NAME, MANAGER_ID)

VALUES('财务部', NULL);

GO

*/

--第二种方法

insert into DEPARTMENTS(DEPARTMENT_NAME, MANAGER_ID)

select '总裁办公室', NULL union all

select '市场部', NULL union all

select '软件部', NULL union all

select '财务部', NULL

go

/*查询部门表信息*/

SELECT DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID

FROM DEPARTMENTS;

GO

----------------------------------------------------

--在HR数据库中创建表---------3职员信息表EMPLOYEES

----------------------------------------------------

 

/*

* 表名：职员信息表

* 说明：在更新此表的部门编号或职位编号时，应向JOB_HISTORY表加入数据，同时更新入职时间为当前日期。

*/

CREATE TABLE EMPLOYEES

(

--职员编号

EMPLOYEE_ID INT NOT NULL IDENTITY(100,1),

--职员名称

FIRST_NAME NVARCHAR(4) NOT NULL,

--职员姓氏

LAST_NAME NVARCHAR(4) NOT NULL,

--职员邮件

EMAIL VARCHAR(25) NOT NULL,

--职员电话

PHONE_NUMBER VARCHAR(20) NOT NULL,

--入职时间

HIRE_DATE DATETIME NOT NULL,

--职员职位编号

JOB_ID VARCHAR(10) NOT NULL,

--职员薪资

SALARY MONEY NULL,

--职员的上级的职员编号

MANAGER_ID INT NULL,

--职员的部门编号

DEPARTMENT_ID INT NULL,

);

GO

--职员的主键约束

ALTER TABLE EMPLOYEES

ADD CONSTRAINT EMPLOYEE_ID_PK PRIMARY KEY (EMPLOYEE_ID);

--职员的入职时间默认值

ALTER TABLE EMPLOYEES

ADD CONSTRAINT EMPLOYEE_HIRE_DATE_DF DEFAULT(getdate()) FOR HIRE_DATE;

--职员薪资的检查约束

ALTER TABLE EMPLOYEES

ADD CONSTRAINT EMPLOYEE_SALARY_CK CHECK(SALARY > 0);

--职员上级的外键约束（职员和职员[管理者]间的多对一关系）

ALTER TABLE EMPLOYEES

ADD CONSTRAINT EMPLOYEE_MANAGER_ID_FK FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID);

--职员职位的外键约束（职员和职位间的多对一关系）

ALTER TABLE EMPLOYEES

ADD CONSTRAINT EMPLOYEE_JOB_ID_FK FOREIGN KEY (JOB_ID) REFERENCES JOBS(JOB_ID);

--部门管理者的外键约束（部门和职员间的一对一关系）

ALTER TABLE DEPARTMENTS

ADD CONSTRAINT DEPARTMENT_EMPLOYEE_MANAGER_ID_FK FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEES (EMPLOYEE_ID);

--员工部门的外键约束（职员和部门间的多对一关系）

ALTER TABLE EMPLOYEES

ADD CONSTRAINT EMPLOYEE_DEPARTMENT_ID_FK FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS (DEPARTMENT_ID);

GO

 

/*新增总裁*/

--新增总裁

insert into EMPLOYEES(FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)

values('云', '马', 'yun.ma@163.com', '023-68887888', 'AD_PRES', 12000, NULL, 1);

--得到标识列；

SELECT @@IDENTITY FROM EMPLOYEES;

--修改部门表的部门主管为总裁；

UPDATE DEPARTMENTS SET MANAGER_ID = 100 WHERE DEPARTMENT_ID = 1;

--新增市场部经理

insert into EMPLOYEES(FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)

values('凡', '郭', 'fan.guo@163.com', '023-68881666', 'MK_MGR', 10000, NULL, 2);

--得到标识列；

SELECT @@IDENTITY FROM EMPLOYEES;

--修改部门表的部门主管为总裁；

UPDATE DEPARTMENTS SET MANAGER_ID = 101 WHERE DEPARTMENT_ID = 2

--新增软件部经理

insert into EMPLOYEES(FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)

values('兹', '盖', 'zi.gai@163.com', '023-68881888', 'IT_MGR', 10000, NULL, 3);

--得到标识列；

SELECT @@IDENTITY FROM EMPLOYEES;

--修改部门表的部门主管为总裁；

UPDATE DEPARTMENTS SET MANAGER_ID = 102 WHERE DEPARTMENT_ID = 3

 

--新增程序员

insert into EMPLOYEES(FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)

values('念', '邓', 'nian.deng@163.com', '023-67771234', 'IT_PROG', 5000, 102, 3);

insert into EMPLOYEES(FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)

values('成强', '张', 'chengqiang.zhang@163.com', '023-67771235', 'IT_PROG', 5500, 102, 3);

insert into EMPLOYEES(FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)

values('隆海', '吴', 'longhai.wu@163.com', '023-67771236', 'IT_PROG', 4500, 102, 3);

/*查询职员表信息*/

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID,

SALARY, MANAGER_ID, DEPARTMENT_ID

FROM EMPLOYEES

GO

 

----------------------------------------------------

--在HR数据库中创建表---------4任职历史记录表JOB_HISTORY

----------------------------------------------------

/*

* 表名：任职历史记录表

* 说明：在更新EMPLOYEES表的部门编号或职位编号时，应向此表加入数据。

*/

CREATE TABLE JOB_HISTORY

(

--任职职员编号

EMPLOYEE_ID INT NOT NULL,

--任职开始时间

START_DATE DATETIME NOT NULL,

--任职结束时间

END_DATE DATETIME NOT NULL,

--任职职位编号

JOB_ID VARCHAR(10) NOT NULL,

--任职部门编号

DEPARTMENT_ID INT NULL,

);

GO

--任职历史记录主键约束（员工编号和开始时间的组合主键）

ALTER TABLE JOB_HISTORY

ADD CONSTRAINT JOB_HISTORY_EMPLOYEE_ID_START_DATE_PK PRIMARY KEY (EMPLOYEE_ID, START_DATE);

--任职结束时间默认约束

ALTER TABLE JOB_HISTORY

ADD CONSTRAINT JOB_HISTORY_END_DATE_DF DEFAULT(GETDATE()) for END_DATE

--任职职员编号的外键约束（任职历史记录和职员间的多对一关系）

ALTER TABLE JOB_HISTORY

ADD CONSTRAINT JOB_HISTORY_EMPLOYEE_ID_FK FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEES (EMPLOYEE_ID);

--任职职位编号的外键约束（任职历史记录和职位间的多对一关系）

ALTER TABLE JOB_HISTORY

ADD CONSTRAINT JOB_HISTORY_JOB_ID_FK FOREIGN KEY (JOB_ID) REFERENCES JOBS (JOB_ID);

--任职部门编号的外键约束（任职历史记录和部门间的多对一关系）

ALTER TABLE JOB_HISTORY

ADD CONSTRAINT JOB_HISTORY_DEPARTMENT_ID_FK FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID);

GO

--向任职记录表中新增历史数据；

INSERT INTO JOB_HISTORY(EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID)

VALUES(103, '2011-05-06', DEFAULT, 'IT_PROG', 3);

GO

/*查询职员表信息*/

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID,

SALARY, MANAGER_ID, DEPARTMENT_ID

FROM EMPLOYEES

GO

/*查询任职历史记录信息*/

SELECT EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID

FROM JOB_HISTORY;

GO

--查询所有的EMPLOYEES表中的信息

select * from EMPLOYEES

go

-----------------------------作业---------------------------------------

-----1职位信息表JOBS

-----2部门信息表DEPARTMENTS

-----3职员信息表EMPLOYEES

-----4任职历史记录表JOB_HISTORY

-- 一、 SELECT子句练习：

--1) 查询职员表的所有信息；

select * from EMPLOYEES

go

--2) 查询职员表中的职员编号、职员姓名、电子邮件、电话号码；

select EMPLOYEE_ID,FIRST_NAME,EMAIL,PHONE_NUMBER from EMPLOYEES

go

--3) 查询员工的职员编号、职员姓名、和职员年薪

select EMPLOYEE_ID,FIRST_NAME,SALARY from EMPLOYEES

go

--4) 查询职员表中的职员编号、职员姓名、入职时间、薪资，并将列名显示为中文；as 名称 （给列指定新的别名）

select EMPLOYEE_ID as 职员编号,FIRST_NAME as 职员姓名,HIRE_DATE as 入职时间,SALARY as 薪资 from EMPLOYEES

go

--5) 从职员表中，查询公司现有哪些部门，给出这些部门的编号；distinct---去掉相同的行 DEPARTMENT_ID<>1除了部门的编号为1的其他部门

select DEPARTMENT_ID,DEPARTMENT_NAME from DEPARTMENTS where DEPARTMENT_ID in(select distinct(DEPARTMENT_ID) from EMPLOYEES where DEPARTMENT_ID<>1)

go

--6) 从任职历史记录表中，查询有部门或职务变动的所有职员的编号；（选做）

--7) 查询职员表中前2位职员的信息，

select top 2 * from EMPLOYEES

go

-- 二、 聚合函数的使用：

----------------------------------------------

-----1职位信息表JOBS

-----2部门信息表DEPARTMENTS

-----3职员信息表EMPLOYEES

-----4任职历史记录表JOB_HISTORY

--1) 查询职员总人数；----as 职员总人数给列起别名

select COUNT(EMPLOYEE_ID) as 职员总人数 from EMPLOYEES

go

--2) 查询3号部门的总人数；

select COUNT(DEPARTMENT_ID) from EMPLOYEES where DEPARTMENT_ID=3

go

--3) 查询部门表中的总部门数；

--select * from DEPARTMENTS

select COUNT(DEPARTMENT_ID) from DEPARTMENTS where DEPARTMENT_ID<>1

go

--4) 查询部门表中有部门主管的部门数；

select COUNT(MANAGER_ID) 部门数 from DEPARTMENTS where MANAGER_ID is not null

go

--5) 查询任职历史记录表中，有过职位或部门变更的职员总数；

--select * from JOB_HISTORY

select COUNT(EMPLOYEE_ID) from JOB_HISTORY where JOB_ID is not null and DEPARTMENT_ID is not null

go

--6) 查询职员表中的最高以及最低薪资；

--select * from EMPLOYEES

select MAX(SALARY) as 最高薪资,MIN(SALARY) as 最低薪资 from EMPLOYEES

go

--7) 查询职员表中，3号部门的薪资发放总数，及薪资平均值；

select COUNT(SALARY) as 薪资发放总数, AVG(SALARY) as 薪资平均值 from EMPLOYEES where DEPARTMENT_ID=3

-- 三、 WHERE子句的使用：

----------------------------------------------

-----1职位信息表JOBS

-----2部门信息表DEPARTMENTS

-----3职员信息表EMPLOYEES

-----4任职历史记录表JOB_HISTORY

--1) 查询职员编号为101的职员信息；

select * from EMPLOYEES where EMPLOYEE_ID='101'

go

--2) 查询入职时间在2008年或以前的职员信息；

--select * from EMPLOYEES

select * from EMPLOYEES where HIRE_DATE<=2008

go

--3) 查询薪资在10000元或以上的职员信息；

select * from EMPLOYEES where SALARY>=10000

go

--4) 查询工资在2500到7000之间的员工信息；

select * from EMPLOYEES where SALARY between 2500 and 7000

go

--5) 查询1998年入职的员工信息；

select * from EMPLOYEES where HIRE_DATE=1998

go

--6) 查询职位编号是ST_CLERK或者ST_MAN的员工；

select * from EMPLOYEES where JOB_ID='ST_CLERK' and JOB_ID='ST_MAN'

go

--7) 查询姓张或是姓李的职员信息；

select * from EMPLOYEES where LAST_NAME='张' or LAST_NAME='李'

go

--8) 查询部门编号以MK开始的职员信息；

select * from EMPLOYEES where JOB_ID like 'MK%'

--9) 查询职位编号中含有MGR的职位信息；

select * from EMPLOYEES where JOB_ID like '%MGR%'

--10) 查询职员邮件中同时包含有o和u的职员信息；

select * from EMPLOYEES where EMAIL like '%o%u%'

go

--11) 查询职员邮件中包含有o或u的职员信息；

select * from EMPLOYEES where EMAIL like '%o%' or EMAIL like '%u%'

go

--12) 查询职员邮件中，第3个字符是n，第4个字符是g的职员信息；

select * from EMPLOYEES where EMAIL like '__ng%'

go

--13) 查询职位编号含有_M的员工信息；

select * from EMPLOYEES where JOB_ID like '%_M%'

--14) 查询部门表中，哪些部门没有部门主管；

select DEPARTMENT_NAME,MANAGER_ID from DEPARTMENTS where MANAGER_ID is null

--15) 查询职员表中，部门主管的名称和职位编号；JOB_ID

------------------------------------------------------------------------

select DEPARTMENT_NAME as 部门主管的名称 from DEPARTMENTS where DEPARTMENT_ID in(select DEPARTMENT_ID from EMPLOYEES )

select JOB_ID from EMPLOYEES

-------------------------------------------------------------------------

--16) 查询不在2010年入职的员工信息；

select * from EMPLOYEES where HIRE_DATE<>2010

go

--17) 查询部门编号为3，工资在5000或以上的职员信息；

select * from EMPLOYEES where DEPARTMENT_ID=3 or SALARY>=5000

go

--18) 查询2010年之后入职，或是薪资在5000以下的职员信息；

select * from EMPLOYEES where HIRE_DATE>2010 and SALARY<5000

go

-- 四、 ORDER BY子句的使用：排序ORDER BY asc desc

----------------------------------------------

-----1职位信息表JOBS

-----2部门信息表DEPARTMENTS

-----3职员信息表EMPLOYEES

-----4任职历史记录表JOB_HISTORY

--1) 查询职员编号、职员姓名、职员薪资，并按薪资高低降序排序；

select EMPLOYEE_ID,LAST_NAME+FIRST_NAME,SALARY from EMPLOYEES ORDER BY SALARY desc

go

--2) 查询职员编号、职员姓名、职员入职时间，并按入职时间升序排序；

select EMPLOYEE_ID,LAST_NAME+FIRST_NAME,HIRE_DATE from EMPLOYEES order by HIRE_DATE asc

go

--3) 查询职员编号、职员姓名、职员入职时间、部门编号，先按部门编号升序，再按入职时间升序排列；

select EMPLOYEE_ID,LAST_NAME+FIRST_NAME,HIRE_DATE ,DEPARTMENT_ID from EMPLOYEES order by DEPARTMENT_ID,HIRE_DATE asc

go

--4) 查询薪资排名在前2位的职员信息；

select top 2 * from EMPLOYEES order by SALARY desc

go

--5) 随机从职员表里抽取两位职员，列出其职员编号、职员姓名； newid()随机函数

select top 2 EMPLOYEE_ID as 职员编号,LAST_NAME+FIRST_NAME as 职员姓名 from EMPLOYEES order by NEWID()

go

 

-- 五、 GROUP BY子句的使用：

----------------------------------------------

-----1职位信息表JOBS

-----2部门信息表DEPARTMENTS

-----3职员信息表EMPLOYEES

-----4任职历史记录表JOB_HISTORY

--1) 查询职员表中，按部门分类的组别；

select DEPARTMENT_ID,DEPARTMENT_NAME from DEPARTMENTS where DEPARTMENT_ID in(select DEPARTMENT_ID from EMPLOYEES group by DEPARTMENT_ID )

select DEPARTMENT_ID from EMPLOYEES group by DEPARTMENT_ID

--2) 查询职员表中，按部门分类，每个部门的平均薪资；

select AVG(SALARY) as 平均薪资 , DEPARTMENT_ID from EMPLOYEES group by DEPARTMENT_ID

--3) 查询职员表中，按职位分类，每个职位的最高薪资；

select MAX(SALARY) 最高薪资 from EMPLOYEES group by JOB_ID

--4) 查询职员表中，按部门分类，薪资在12000以上的职员人数；

---select * from EMPLOYEES

select COUNT(SALARY)职员人数 from EMPLOYEES where SALARY>=12000 group by DEPARTMENT_ID

--6、 HAVING子句的使用：

--1) 查询职员表中，平均薪资在7500或以上的部门编号及平均薪资；

select DEPARTMENT_ID 部门编号 ,avg(SALARY) 平均薪资 from EMPLOYEES group by DEPARTMENT_ID having AVG(SALARY)>=7500

--2) 查询职员表中，最低工资在8000以上的部门编号及最低薪资；

select DEPARTMENT_ID 部门编号,MIN(SALARY)最低薪资 from EMPLOYEES group by DEPARTMENT_ID having MIN(SALARY)>8000

go

-- 六、 INTO子句的使用：

----------------------------------------------

-----1职位信息表JOBS

-----2部门信息表DEPARTMENTS

-----3职员信息表EMPLOYEES

-----4任职历史记录表JOB_HISTORY

--1) 将EMPLOYEES表的数据备份至EMP_TEMP；

select * into EMP_TEMP from EMPLOYEES

--select * from EMP_TEMP

--2) 随机从职员表里抽取两位职员，列出其职员编号、职员姓名；

select top 2 EMPLOYEE_ID as 职员编号,LAST_NAME+FIRST_NAME as 职员姓名 from EMPLOYEES order by NEWID()