【MYSQL】基础练习（四）

【MYSQL】基础练习（四）

题目：某公司的mis数据库中有部⻔表（dept）和员工表（employee）表结构及其数据如下：

表一：dept表结构

表2：dept表数据

表3：employee表结构

表4: employee表数据

要求编写程序，完成以下任务：

-- 任务1：编写脚本，创建mis数据库，创建dept表，创建employee表。
-- 任务2：添加约束 :dept表的 dept_name 列的值是唯一的 ;employee表的
dept_id 列引用dept表的 dept_id 列 ,给employee表的gender字段添加默认约
束，默认为'男'
-- 任务3：根据表2和表4，向dept表和employee表中添加测试数据
-- 任务4：查询工资大于2900元的员工信息
-- 任务5：将孙二娘的出生日期更改为1985-6-8
-- 任务6：删除员工扈三娘
-- 任务7：查找工资最低的两个员工
-- 任务8：按照工资降序排序员工信息
-- 任务9：删除测试部门及其员工
-- 任务10: 统计男员工和女员工的人数
-- 任务11： 计算最高工资和最低工资的差额
-- 拓展任务：统计部门平均工资，及部门名称
-- 拓展任务：查询员工信息，显示员工姓名，部门名称，薪水

---

1.按要求依次创建数据库、建表并插入数据：

--1.创建数据库mis
CREATE DATABASE mis;
--2.创建dept表结构
CREATE TABLE dept (
	dept_id INT PRIMARY KEY AUTO_INCREMENT COMMENT "部门编号",
	dept_name VARCHAR(20) NOT NULL COMMENT "部门名称"
);
--3. 插入dept 数据
INSERT INTO dept(dept_id,dept_name) VALUES(1,"开发部门"),(2,"测试部门");
--4.创建employee表结构
CREATE TABLE employee (
	emp_id INT PRIMARY KEY AUTO_INCREMENT COMMENT "员工编号",
	emp_name VARCHAR(20) COMMENT "员工姓名",
	birthday DATE COMMENT "出生日期",
	gender VARCHAR(10) COMMENT "员工性别",
	salary DOUBLE COMMENT "员工工资",
	dept_id INT COMMENT "部门编号"
);
--5.插入employee表数据
INSERT INTO employee (emp_id, emp_name, birthday, gender, salary, dept_id)
VALUES (1, '林冲', '1981-10-10', '男', 2800, 1),
       (2, '宋江', '1992-6-21', '男', 3100, 2),
       (3, '扈三娘', '1984-3-8', '女', 3000, 1),
       (4, '孙二娘', '1985-6-7', '女', 2950, 2);

2.添加约束 :dept表的 dept_name 列的值是唯一的 ;employee表的dept_id 列引用dept表的 dept_id 列 ,给employee表的gender字段添加默认约束，默认为’男’

--dept表添加唯一约束dept_name
ALTER TABLE dept ADD UNIQUE (dept_name);

--employee表的dept_id 列引用dept表的 dept_id 列
ALTER TABLE employee ADD CONSTRAINT fk_dept_id FOREIGN KEY (dept_id) REFERENCES dept(dept_id);
ALTER TABLE 表名 ADD CONSTRAINT 外键约束名称 FOREIGN KEY (本表列名) REFERENCES 被引用表名 (被引用表列名);
--给employee表的gender字段添加默认约束，默认为'男'
ALTER TABLE employee MODIFY gender varchar(10) DEFAULT '男';
ALTER TABLE table_name MODIFY COLUMN 列名 列类型 DEFAULT 默认值；

任务3：根据表2和表4，向dept表和employee表中添加测试数据

 insert into dept(dept_name)values('运维部门'),('安保部门'),('财务部门'),('宣传部门');

INSERT INTO employee (emp_name, birthday, salary, dept_id)
VALUES ('卢俊义', '1982-05-15', 2600, 3),
       ( '武松', '1983-11-20', 3000, 4),
       ('李逵', '1984-09-03', 3200, 3),
       ( '鲁智深', '1985-12-08', 2800, 4),
 	   ('吴用', '1986-07-25', 2900, 1);

任务4：查询工资大于2900元的员工信息

select *
from employee
where salary > 2900;

任务5：将孙二娘的出生日期更改为1985-6-8

update employee 
set birthday='1985-6-8'  
where emp_name='孙二娘'； 

– 任务6：删除员工扈三娘

delete from employee where emp_name='扈三娘';

–任务7：查找工资最低的两个员工

select emp_name,salary from employee order by salary limit 0,2;

– 任务8：按照工资降序排序员工信息

select * from employee order by salary desc;

– 任务9：删除测试部门及其员工

DELETE FROM employee 
WHERE 
dept_id=(SELECT dept_id FROM dept WHERE dept_name='测试部门');

DELETE FROM dept WHERE dept_name='测试部门';

– 任务10: 统计男员工和女员工的人数

select gender,count(*) as count
from employee 
group by gender;

– 任务11： 计算最高工资和最低工资的差额

SELECT MAX(salary)-MIN(salary) AS balance
FROM employee;

– 拓展任务：统计部门平均工资，及部门名称

select dept.dept_name,avg(employee.salary)
from dept,employee
where dept.dept_id = employee.dept_id
group by dept_name ;

– 拓展任务：查询员工信息，显示员工姓名，部门名称，薪水

SELECT employee.emp_name AS '员工姓名',dept.dept_name AS '部门名称', employee.salary AS '薪水'
FROM dept, employee
WHERE dept.dept_id = employee.dept_id;