MySQL实训2_在此数据库下创建如下3表及结构如下。 a. 部门表(department) :部门编号(depid)-CSDN博客

本文链接：https://blog.csdn.net/qq_40891747/article/details/116561952

#cmd中打开mysql
mysql -u root -p

1．以自己的姓名创建一个数据库。

create database yi;

2．在此数据库下创建如下3表，数据类型，宽度，是否为空等，根据实际情况自己定义。
创建表1：
A．雇员表（employee）：
雇员编号（empid），
姓名（name），
性别（gender），
职称（title），
出生日期（birthday），
所在部门编号（depid）；
其中雇员编号为主键；

mysql> create table employee(
    -> empid varchar(10) primary key not null,
    -> name varchar(10),
    -> gender varchar(10),
    -> title varchar(20),
    -> birthday date,
    -> depid varchar(10));

mysql> desc employee;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| empid    | varchar(10) | NO   | PRI | NULL    |       |
| name     | varchar(10) | YES  |     | NULL    |       |
| gender   | varchar(10) | YES  |     | NULL    |       |
| title    | varchar(20) | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| depid    | varchar(10) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

创建表2：
B．部门表（department）：
部门编号（depid），
部门名称（depname）；
其中部门编号为主键。

mysql> create table department(
    -> depid varchar(10) primary key not null,
    -> depname varchar(20));
    
mysql> desc department;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| depid   | varchar(10) | NO   | PRI | NULL    |       |
| depname | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

创建表3：
C．工资表（salary）：
雇员编号（empid），
基本工资（base_salary），
职务工资（title_salary），
扣除（deduction）。
其中雇员编号为主键。

mysql> create table salary(
    -> empid varchar(10) primary key not null,
    -> base_salary decimal(8,2),
    -> title_salary decimal(8,2),
    -> deduction int);

mysql> desc salary;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| empid        | varchar(10)  | NO   | PRI | NULL    |       |
| base_salary  | decimal(8,2) | YES  |     | NULL    |       |
| title_salary | decimal(8,2) | YES  |     | NULL    |       |
| deduction    | int(11)      | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+

3．修改表结构，在部门表中添加一个”部门简介”字段。

分析：

本题主要考查的是添加字段的语句，语句如下：
alter table+表名+add+新字段+新字段属性

mysql> alter table employee add 部门简介 varchar(100);

mysql> desc employee;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empid    | varchar(10)  | NO   | PRI | NULL    |       |
| name     | varchar(10)  | YES  |     | NULL    |       |
| gender   | varchar(10)  | YES  |     | NULL    |       |
| title    | varchar(20)  | YES  |     | NULL    |       |
| birthday | date         | YES  |     | NULL    |       |
| depid    | varchar(10)  | YES  |     | NULL    |       |
| 部门简介 | varchar(100) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

4．在上面的3个表中各输入若干条记录，内容如下。
雇员表：

雇员编号	姓名	性别	职称	出生日期	所在部门编号
1000	张三	男	高级工程师	1975-1-1	111
1002	李四	女	助理工程师	1985-1-1	111
1003	王五	男	工程师	1978-11-11	222
1004	赵六	男	工程师	1979-1-1	222

mysql> insert into employee values
    -> ('1001','张三','男','高级工程师','1980-01-01','1111',null),
    -> ('1002','李四','女','助理工程师','1980-11-01','1111',null),
    -> ('1003','王五','男','工程师','1980-01-21','2222',null),
    -> ('1004','赵六','男','工程师','1980-01-11','2222',null);

mysql> select * from employee;
+-------+------+--------+------------+------------+-------+----------+
| empid | name | gender | title      | birthday   | depid | 部门简介 |
+-------+------+--------+------------+------------+-------+----------+
| 1001  | 张三 | 男     | 高级工程师 | 1980-01-01 | 1111  | NULL     |
| 1002  | 李四 | 女     | 助理工程师 | 1980-11-01 | 1111  | NULL     |
| 1003  | 王五 | 男     | 工程师     | 1980-01-21 | 2222  | NULL     |
| 1004  | 赵六 | 男     | 工程师     | 1980-01-11 | 2222  | NULL     |
+-------+------+--------+------------+------------+-------+----------+

部门表：

部门编号	部门名称	部门简介
111	生产部	Null
222	销售部	Null
333	人事部	Null

mysql> insert into department(depid,depname) values
    -> ('1111','生产部'),
    -> ('2222','销售部'),
    -> ('3333','人事部');

mysql> select * from department;
+-------+---------+
| depid | depname |
+-------+---------+
| 1111  | 生产部  |
| 2222  | 销售部  |
| 3333  | 人事部  |
+-------+---------+

工资表：

雇员编号	基本工资	职务工资	扣除
1001	2200	1100	200
1002	1200	200	100
1003	1900	700	200
1004	1950	700	150

mysql> insert into salary values
    -> ('1001',3200,1200,200),
    -> ('1002',4200,1100,100),
    -> ('1003',5200,2200,200),
    -> ('1004',2000,1500,150);

mysql> select * from salary;
+-------+-------------+--------------+-----------+
| empid | base_salary | title_salary | deduction |
+-------+-------------+--------------+-----------+
| 1001  |     3200.00 |      1200.00 |       200 |
| 1002  |     4200.00 |      1100.00 |       100 |
| 1003  |     5200.00 |      2200.00 |       200 |
| 1004  |     2000.00 |      1500.00 |       150 |
+-------+-------------+--------------+-----------+

5．将李四的职称改为“工程师”，并将她的基本工资改为5700元，职务工资为600。

①题目要求更改李四的职称，故使用update语句进行更新。语句如下：
update 表名 set 列名 1 = 值 1(,列 2 = 值 2) (where 筛选列 = 筛选值)

mysql> update employee set title="工程师" where name="李四";

mysql> select * from employee;
+-------+------+--------+------------+------------+-------+----------+
| empid | name | gender | title      | birthday   | depid | 部门简介 |
+-------+------+--------+------------+------------+-------+----------+
| 1001  | 张三 | 男     | 高级工程师 | 1980-01-01 | 1111  | NULL     |
| 1002  | 李四 | 女     | 工程师     | 1980-11-01 | 1111  | NULL     |
| 1003  | 王五 | 男     | 工程师     | 1980-01-21 | 2222  | NULL     |
| 1004  | 赵六 | 男     | 工程师     | 1980-01-11 | 2222  | NULL     |
+-------+------+--------+------------+------------+-------+----------+

② 题目要求更改李四的基本工资和职务工资，同样需要使用update语句。
③ 由于在salary表中只有empid没有姓名，由于要对李四的工资进行修改，而employee表中有empid和name，故需要对employee表进行查询，得到李四的empid
④ 使用update语句对李四的工资进行更改。


```python
mysql> update salary set base_salary=5700,title_salary=600
    -> where empid=(select empid from employee where name="李四");

mysql> select * from salary;
+-------+-------------+--------------+-----------+
| empid | base_salary | title_salary | deduction |
+-------+-------------+--------------+-----------+
| 1001  |     3200.00 |      1200.00 |       200 |
| 1002  |     5700.00 |       600.00 |       100 |
| 1003  |     5200.00 |      2200.00 |       200 |
| 1004  |     2000.00 |      1500.00 |       150 |
+-------+-------------+--------------+-----------+

6．查询出每个雇员的雇员编号，姓名，职称，所在部门，实发工资和应发工资。

① 由于雇员编号、姓名、职称在表employee，部门名称在表department，实发工资（基本工资+职务工资-扣除）、应发工资（基本工资+职务工资）在表salary，
② 故需要采用多表联合将三个表中的数据输出
注：不能和内连接一样，两个left join 再用and将两个on和在一起

mysql> select employee.empid,name,title,depname,(base_salary+title_salary-deduction) as  实发工资,(base_salary+title_salary) as 应发工资
    -> from employee left join salary on employee.empid=salary.empid
    -> left join department on department.depid=employee.depid;
+-------+------+------------+---------+----------+----------+
| empid | name | title      | depname | 实发工资 | 应发工资 |
+-------+------+------------+---------+----------+----------+
| 1001  | 张三 | 高级工程师 | 生产部  |  4200.00 |  4400.00 |
| 1002  | 李四 | 工程师     | 生产部  |  6200.00 |  6300.00 |
| 1003  | 王五 | 工程师     | 销售部  |  7200.00 |  7400.00 |
| 1004  | 赵六 | 工程师     | 销售部  |  3350.00 |  3500.00 |
+-------+------+------------+---------+----------+----------+

7．查询姓“张”且年龄小于45岁的员工的记录。

① 查询姓“张”的，采用模糊查询，like，注意不能用‘=’
② 查询年龄小于45岁的员工，需要借助year()函数和curdate()函数，curdate()是用来返回当前日期，year() #返回括号里数据的年份

mysql> select * from employee where name like"张%" ;
+-------+------+--------+------------+------------+-------+----------+
| empid | name | gender | title      | birthday   | depid | 部门简介 |
+-------+------+--------+------------+------------+-------+----------+
| 1001  | 张三 | 男     | 高级工程师 | 1980-01-01 | 1111  | NULL     |
+-------+------+--------+------------+------------+-------+----------+

mysql> select * from employee where name like"张%" and (year(curdate())-year(birthday))<40;
Empty set (0.00 sec)

8．查询销售部所有雇员的雇员编号，姓名，职称，部门名称，实发工资。

① 由于雇员编号、姓名、职称在表employee，部门名称在表department，实发工资（基本工资+职务工资-扣除）在表salary，故需要采用多表联合
② 题目要求查询销售部员工的数据，故需要判断depname是否为销售部

mysql> select employee.empid,name,title,depname,(base_salary+title_salary-deduction) as  实发工资
    -> from employee left join salary on employee.empid=salary.empid left join department on department.depid=employee.depid
    ->  where depname="销售部";
+-------+------+--------+---------+----------+
| empid | name | title  | depname | 实发工资 |
+-------+------+--------+---------+----------+
| 1003  | 王五 | 工程师 | 销售部  |  7200.00 |
| 1004  | 赵六 | 工程师 | 销售部  |  3350.00 |
+-------+------+--------+---------+----------+

9．统计各类职称的人数。

查询各类职称，则需要对于职称(title)进行分组，而在分组基础上采用count()函数即可统计各类职称人数

mysql> select title,count(*)as 人数 from employee group by title;
+------------+------+
| title      | 人数 |
+------------+------+
| 工程师     |    3 |
| 高级工程师 |    1 |

10．统计各部门的部门名称，实发工资总和，平均工资。
分析

① 由于部门名称在表department，实发工资（基本工资+职务工资-扣除）在表salary，且统计部门人数需要借助到employee，故需要采用多表联合
② 统计各部门的部门名称需要用到 group by来进行分类
③ 求实发工资总和需要借助sum()函数，求平均工资需要借助avg()函数

mysql> select depname,sum(base_salary+title_salary-deduction)as 实发工资,avg(base_salary+title_salary-deduction) as 平均工资
    -> from employee left join salary on employee.empid=salary.empid left join department on department.depid=employee.depid
    -> group by depname;
+---------+----------+-------------+
| depname | 实发工资 | 平均工资    |
+---------+----------+-------------+
| 生产部  | 10400.00 | 5200.000000 |
| 销售部  | 10550.00 | 5275.000000 |
+---------+----------+-------------+