MySQL综合练习

练习中所需的数据库：

create database salesmanage;

use salesmanage;

create table 员工表(
 员工号 char(4),
 员工姓名 char(8),
 性别 char(2),
 年龄 int,
 受雇日期 date,
 薪金 decimal(6),
 部门编号 char(4)
 );
 
 create table 部门表(
  部门编号 char(4),
  部门名称 varchar(10),
  工作地点 varchar(20),
  负责人 char(4)
);

create table 销售表(
 员工号 char(4),
 销售量 int,
 销售金额 decimal(8),
 销售日期 date
 );
 
INSERT INTO 员工表 VALUES
 ('8001','韩鹏','男',35,'2014-12-01',4000,'1002')
,('8002','张峰','男',36,'2019-09-10',2500,'1003')
,('8003','欧阳','男',30,'2020-02-14',1500,'1004')
,('8004','王萌','女',28,'2021-05-16',5500,'1004')
,('8005','欧阳宝贝','女',40,'2013-08-19',5000,'1001')
,('8006','郑一轩','男',32,'2017-06-10',3000,'1004')
,('8007','李晓','女',30,'2018-07-28',2800,'1004');

INSERT INTO 部门表 VALUES
('1001','人事部','综合楼三楼301','8005'),
('1002','科研部','实验楼202','8001'),
('1003','生产部','生产大楼一、二楼','8002'),
('1004','销售部','银帆大厦一楼','8004');

INSERT INTO 销售表 VALUES
('8003',150,45000,'2022-05-06'),
('8004',230,69000,'2022-07-18'),
('8006',310,93000,'2022-10-28'),
('8007',280,84000,'2022-08-13'),
('8006',130,39000,'2022-11-09'),
('8004',50,15000,'2022-09-20');

数据库salesmanage包括员工表、部门表、销售表，各表内容如下：

 

1. 在salesmanage数据库中创建表student，该表的结构如下图所示。

create table student(
 id int  not null primary key unique auto_increment,
 name varchar(20) not null unique,
 sex char(2) not null,
 birthday datetime
);

2、① 将student表的sex字段的类型改为VARCHAR(4)；

      ② 将student表的sex字段的名称改为ssex；

      ③ 将student表的address字段删除。

（1）alter table student modify sex varchar(4);
（2）alter table student change sex ssex varchar(4);
（3）alter table student drop ssex;

3、将员工表的员工号设置为主键，部门编号设置为外键，其取值参照部门表的部门编号列。

alter table 员工表 add primary key(员工号);
alter table 部门表 add primary key(部门编号);
alter table 员工表 add foreign key(部门编号) references 部门表(部门编号);

4、在员工表中插入以下两行数据。

8008，何景涛，男，35，2019-5-15，4500，1004

8009，刘文力，男，29，2018-8-20，4000，1004

insert 员工表 values('8008','何景涛','男',35,'2019-05-15',4500,'1004')
,('8009','刘文力','男',29,'2018-8-20',4000,'1004');

5、将题目（4）所插入的员工薪金提高5%。

update 员工表 set 薪金=薪金*1.05 where 员工号='8008' or 员工号='8009';

6、删除姓名为“何景涛”的员工信息。

delete from 员工表  where 员工姓名='何景涛';

7、查找姓名以“郑”开头的员工信息。

select * from 员工表 where 员工姓名 like '郑%';

8、查询人事部和销售部的员工的员工号和员工姓名。

select 员工号,员工姓名 from 员工表 yg,部门表 bm
 where yg.部门编号=bm.部门编号 and (部门名称='人事部' or 部门名称='销售部');

9、查询有销售业绩的员工姓名。

select 员工姓名 from 员工表 
  where 员工号 in(select 员工号 from 销售表);

10、查询“郑”姓员工的姓名，销售金额和工作地点。

select 员工姓名,销售金额,工作地点 from 员工表 yg,部门表 bm,销售表 xs
 where yg.部门编号=bm.部门编号 and yg.员工号=xs.员工号 and 员工姓名 like '郑%';

11、统计“销售部”人数。

select count(*) from 员工表 yg,部门表 bm
 where yg.部门编号=bm.部门编号 and 部门名称='销售部';

12、建立名为view1 的视图，查询2019 年12 月以后入职的员工的姓名、性别和工作地点。

create view view1 as
 select 员工姓名,性别,工作地点 from 员工表 yg,部门表 bm
 where yg.部门编号=bm.部门编号 and 受雇日期>'2019-12-30';

13、建立名为view2 的视图，统计每个员工的销售总金额。

create view view2 as
 select 员工号,sum(销售金额) from 销售表 group by 员工号;