文章展示了在MySQL数据库中创建mis数据库，dept和employee表的结构，以及如何添加唯一性约束和外键约束。接着，插入测试数据，执行了查询高薪员工、修改员工信息、删除员工等操作，并统计了员工性别分布和工资差额。最后，进行了部门平均工资和员工详细信息的查询。
某公司的mis数据库中有部⻔表（dept）和员工表（employee）表结构及其数据如下：
表1：dept表结构
mysql> create database mis; Query OK, 1 row affected (0.00 sec) mysql> use mis; Database changed mysql> create table dept( -> dept_id int primary key auto_increment, -> dept_name varchar(20) not null -> ); Query OK, 0 rows affected (0.01 sec) mysql> show create table mis; ERROR 1146 (42S02): Table 'mis.mis' doesn't exist mysql> show create table dept; +-------+------------------------------------------------------------------------- | Table | Create Table +-------+------------------------------------------------------------------------- | dept | CREATE TABLE `dept` ( `dept_id` int NOT NULL AUTO_INCREMENT, `dept_name` varchar(20) NOT NULL, PRIMARY KEY (`dept_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+------------------------------------------------------------------------- 1 row in set (0.00 sec)
表2：dept表数据
mysql> insert into dept(dept_id,dept_name) values(1,'开发部门'),(2,'测试部门'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select *from dept; +---------+-----------+ | dept_id | dept_name | +---------+-----------+ | 1 | 开发部门 | | 2 | 测试部门 | +---------+-----------+ 2 rows in set (0.00 sec)
表3：employee表结构
mysql> create table employee( -> emp_id int primary key auto_increment, -> emp_name varchar(20), -> birthday date, -> gender varchar(10), -> salary double, -> dept_id int -> ); Query OK, 0 rows affected (0.01 sec) mysql> show create table employee; +----------+------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ | Table | Create Table +----------+------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ | employee | CREATE TABLE `employee` ( `emp_id` int NOT NULL AUTO_INCREMENT, `emp_name` varchar(20) DEFAULT NULL, `birthday` date DEFAULT NULL, `gender` varchar(10) DEFAULT NULL, `salary` double DEFAULT NULL, `dept_id` int DEFAULT NULL, PRIMARY KEY (`emp_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +----------+------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ 1 row in set (0.00 sec)
表4: employee表数据
mysql> insert into employee(emp_name,birthday,gender,salary,dept_id) values
-> ('林冲','1981-10-10', '男',2800,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into employee(emp_name,birthday,gender,salary,dept_id) values
-> ('宋江','1992-06-21','男',3100,2),
-> ('扈三娘','1984-03-08','女',3000,1),
-> ('孙二娘','1985-06-07','女',2950,2);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select *from employee;
+--------+----------+------------+--------+--------+---------+
| emp_id | emp_name | birthday | gender | salary | dept_id |
+--------+----------+------------+--------+--------+---------+
| 1 | 林冲 | 1981-10-10 | 男 | 2800 | 1 |
| 2 | 宋江 | 1992-06-21 | 男 | 3100 | 2 |
| 3 | 扈三娘 | 1984-03-08 | 女 | 3000 | 1 |
| 4 | 孙二娘 | 1985-06-07 | 女 | 2950 | 2 |
+--------+----------+------------+--------+--------+---------+
4 rows in set (0.00 sec)
要求编写程序，完成以下任务：
任务1：编写脚本，创建mis数据库，创建dept表，创建employee表。
任务2：添加约束 :dept表的 dept_name 列的值是唯一的 ;employee表的 dept_id 列引用dept表的 dept_id 列 ,给employee表的gender字段添加默认约 束，默认为'男'
dept表的 dept_name 列的值是唯一的 mysql> alter table dept add unique(dept_name); Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table dept;
+-------+--------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
-------------------+
| Table | Create Table
+-------+--------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
-------------------+
| dept | CREATE TABLE `dept` (
`dept_id` int NOT NULL AUTO_INCREMENT,
`dept_name` varchar(20) NOT NULL,
PRIMARY KEY (`dept_id`),
UNIQUE KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
-------------------+
1 row in set (0.00 sec)
employee表的 dept_id 列引用dept表的 dept_id 列 ,
alter table employee add constraint dept_id foreign key(dept_id)
references dept(dept_id);
给employee表的gender字段添加默认约 束，默认为'男'
mysql> alter table employee modify column gender varchar(10) default('男');
任务3：根据表2和表4，向dept表和employee表中添加测试数据
任务4：查询工资大于2900元的员工信息
mysql> select *from employee where salary>2900; +--------+----------+------------+--------+--------+---------+ | emp_id | emp_name | birthday | gender | salary | dept_id | +--------+----------+------------+--------+--------+---------+ | 2 | 宋江 | 1992-06-21 | 男 | 3100 | 2 | | 3 | 扈三娘 | 1984-03-08 | 女 | 3000 | 1 | | 4 | 孙二娘 | 1985-06-07 | 女 | 2950 | 2 | +--------+----------+------------+--------+--------+---------+ 3 rows in set (0.00 sec)
任务5：将孙二娘的出生日期更改为1985-6-8
mysql> update employee set birthday='1985-06-08' where emp_name='孙二娘'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM employee; +--------+----------+------------+--------+--------+---------+ | emp_id | emp_name | birthday | gender | salary | dept_id | +--------+----------+------------+--------+--------+---------+ | 1 | 林冲 | 1981-10-10 | 男 | 2800 | 1 | | 2 | 宋江 | 1992-06-21 | 男 | 3100 | 2 | | 3 | 扈三娘 | 1984-03-08 | 女 | 3000 | 1 | | 4 | 孙二娘 | 1985-06-08 | 女 | 2950 | 2 | +--------+----------+------------+--------+--------+---------+ 4 rows in set (0.00 sec)
任务6：删除员工扈三娘
mysql> delete from employee where emp_id=3; Query OK, 1 row affected (0.00 sec) mysql> select *from employee; +--------+----------+------------+--------+--------+---------+ | emp_id | emp_name | birthday | gender | salary | dept_id | +--------+----------+------------+--------+--------+---------+ | 1 | 林冲 | 1981-10-10 | 男 | 2800 | 1 | | 2 | 宋江 | 1992-06-21 | 男 | 3100 | 2 | | 4 | 孙二娘 | 1985-06-08 | 女 | 2950 | 2 | +--------+----------+------------+--------+--------+---------+ 3 rows in set (0.00 sec) mysql>
任务7：查找工资最低的两个员工
mysql> select emp_name,salary from employee order by salary limit 0,2; +----------+--------+ | emp_name | salary | +----------+--------+ | 林冲 | 2800 | | 孙二娘 | 2950 | +----------+--------+ 2 rows in set (0.00 sec)
任务8：按照工资降序排序员工信息
mysql> select *from employee order by salary desc; +--------+----------+------------+--------+--------+---------+ | emp_id | emp_name | birthday | gender | salary | dept_id | +--------+----------+------------+--------+--------+---------+ | 2 | 宋江 | 1992-06-21 | 男 | 3100 | 2 | | 4 | 孙二娘 | 1985-06-08 | 女 | 2950 | 2 | | 1 | 林冲 | 1981-10-10 | 男 | 2800 | 1 | +--------+----------+------------+--------+--------+---------+ 3 rows in set (0.00 sec)
任务9：删除测试部门及其员工
delete from employee where dept_id=2; Query OK, 2 rows affected (0.00 sec) mysql> select *from dept; +---------+-----------+ | dept_id | dept_name | +---------+-----------+ | 1 | 开发部门 | | 2 | 测试部门 | +---------+-----------+ 2 rows in set (0.00 sec) mysql> select *from employee; +--------+----------+------------+--------+--------+---------+ | emp_id | emp_name | birthday | gender | salary | dept_id | +--------+----------+------------+--------+--------+---------+ | 1 | 林冲 | 1981-10-10 | 男 | 2800 | 1 | +--------+----------+------------+--------+--------+---------+ 1 row in set (0.00 sec)
任务10: 统计男员工和女员工的人数
select count(gender) as 人数 from employee group by gender;
任务11： 计算最高工资和最低工资的差额
select max(salary)-min(salary) as 差额 from employee;
拓展任务：统计部门平均工资，及部门名称
select dept_name,avg(salary) as 平均工资 from dept,employee group by dept_name;
拓展任务：查询员工信息，显示员工姓名，部门名称，薪水
select emp_name,dept_name,salary from dept,employee where dept.dept_id=employee.dept_id;
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
