MySQL之综合实践【从基础到进阶】 - ic翼

阅读 165
收藏 12
2018-04-28
原文链接:bingyishow.top

前言

本篇文章将前几天所学过的MySQL知识进行了一次综合实践。包括表的建立、插入、查询、视图、函数的运用、触发器等。

创建&使用数据库

  • 建立数据库、建立表。写文章的时候没有截图。所以我尽可能的把代码思路弄清楚点。不好之处还望多多留言指正哈。PS:打表格的时候是真的费时间啊。

1.创建数据库YGGL

create database YGGL;

2.使用数据库YGGL

use YGGL;

创建表

3.在数据库YGGL中创建三个数据表Employees(员工信息表)、Departments(部门信息表)、和Salary(员工薪水情况表)

创建员工信息表

这里需要注意的是:因为Employees、Salary需要创建外键。最好是先创建没有外键的表。

  • Departments表结构
字段名 数据类型 主键 外键 非空 唯一 自增 默认 说明
DepartmentID char(3) 部门编号
DepartmentName varchar(20) 部门名称
create table Departments
(
    DepartmentID char(3) primary key not null unique comment '部门编号',
    DepartmentName varchar(20) not null comment '部门名称'
);

desc Departments;

  • Employees表结构
字段名 数据类型 主键 外键 非空 唯一 自增 默认 说明
EmployeeID char(6) 员工编号
Name varchar(10) 姓名
Birthday date 出生日期
Sex char(2) ‘1’ 性别
Workyear tinyint(1) 工作时间
Address varchar(20) 地址
PhoneNumber char(12) 电话号码
DepartmentID char(3) 员工部门号
create table Employees
(
    EmployeeID char(6) primary key not null unique comment '员工编号',
    Name varchar(10) not null comment '姓名',
    Birthday date not null comment '出生日期',
    Sex char(2) not null default 1 comment '性别',
    Workyear tinyint(1) comment '工作时间',
    Address varchar(20) comment '地址',
    PhoneNumber char(12) comment '电话号码',
    DepartmentID char(3) comment '员工部门号',
    constraint test2 foreign key(DepartmentID) references Departments(DepartmentID)
);

desc Employees;

  • Salary表结构
字段名 数据类型 主键 外键 非空 唯一 自增 默认 说明
EmployeeID char(6) 员工编号
InCome float(8) 0 收入
OutCome float(8) 0 支出
create table Salary
(
    EmployeeID char(6) primary key not null unique  comment '员工编号',    
    InCome float(8)  not null default 0 comment '收入',
    OutCome float(8) not null default 0 comment '支出',
    constraint test1 foreign key(EmployeeID) references Employees(EmployeeID)
);

desc Salary;

插入数据

4.向Employees(员工信息表)、Departments(部门信息表)、和Salary(员工薪水情况表)三张表中分别插入以下数据。

  • 向部门信息表中插入数据
EmployeeID Name Brithday Sex Workyear Address PhoneNumber DepartmentID
000001 王林 1996-01-23 1 8 中山路32-1-508 83355668 2
010008 伍容华 1976-03-28 1 3 北京东路100-2 83321321 1
020010 王向荣 1982-12-09 1 2 四牌楼10-0-108 83792361 1
020018 李丽 1960-07-30 0 6 中山东路102-2 83413301 1
102201 刘明 1972-10-18 1 3 虎踞路100-2 83606608 5
102208 朱俊 1965-09-28 1 2 牌楼巷5-3-106 84708817 5
108991 钟敏 1979-08-10 0 4 中山路10-3-105 83346762 3
111006 张石兵 1974-10-01 1 1 解放路34-1-203 84563418 5
210678 林涛 1977-04-22 1 2 中山北路24-35 83467336 3
302556 李玉珉 1968-09-20 1 3 热河路209-3 58765991 4
308759 叶凡 1978-11-18 1 2 北京西路3-7-52 83308901 4
504209 陈林琳 1969-09-03 0 5 汉中路120-4-12 84468158 4
INSERT INTO `yggl`.`employees` (`EmployeeID`, `Name`, `Birthday`, `Sex`, `Workyear`, `Address`, `PhoneNumber`, `DepartmentID`) VALUES ('000001', '王林', '1996-01-23', '1', '8', '中山路32-1-508', '83355668', '2');
INSERT INTO `yggl`.`employees` (`EmployeeID`, `Name`, `Birthday`, `Sex`, `Workyear`, `Address`, `PhoneNumber`, `DepartmentID`) VALUES ('010008', '伍容华', '1976-03-28', '1', '3', '北京东路100-2', '83321321', '1');
INSERT INTO `yggl`.`employees` (`EmployeeID`, `Name`, `Birthday`, `Sex`, `Workyear`, `Address`, `PhoneNumber`, `DepartmentID`) VALUES ('020010', '王向荣', '1982-12-09', '1', '2', '四牌楼10-0-108', '83792361', '1');
INSERT INTO `yggl`.`employees` (`EmployeeID`, `Name`, `Birthday`, `Sex`, `Workyear`, `Address`, `PhoneNumber`, `DepartmentID`) VALUES ('020018', '李丽', '1960-07-30', '0', '6', '中山东路102-2', '83413301', '1');
INSERT INTO `yggl`.`employees` (`EmployeeID`, `Name`, `Birthday`, `Sex`, `Workyear`, `Address`, `PhoneNumber`, `DepartmentID`) VALUES ('102201', '刘明', '1972-10-18', '1', '3', '虎踞路100-2', '83606608', '5');
INSERT INTO `yggl`.`employees` (`EmployeeID`, `Name`, `Birthday`, `Sex`, `Workyear`, `Address`, `PhoneNumber`, `DepartmentID`) VALUES ('102208', '朱俊', '1965-09-28', '1', '2', '牌楼巷5-3-106', '84708817', '5');
INSERT INTO `yggl`.`employees` (`EmployeeID`, `Name`, `Birthday`, `Sex`, `Workyear`, `Address`, `PhoneNumber`, `DepartmentID`) VALUES ('108991', '钟敏', '1979-08-10', '0', '4', '中山路10-3-105', '83346762', '3');
INSERT INTO `yggl`.`employees` (`EmployeeID`, `Name`, `Birthday`, `Sex`, `Workyear`, `Address`, `PhoneNumber`, `DepartmentID`) VALUES ('111006', '张石兵', '1974-10-01', '1', '1', '解放路34-1-203', '84563418', '5');
INSERT INTO `yggl`.`employees` (`EmployeeID`, `Name`, `Birthday`, `Sex`, `Workyear`, `Address`, `PhoneNumber`, `DepartmentID`) VALUES ('210678', '林涛', '1977-04-22', '1', '2', '中山北路24-35', '83467336', '3');
INSERT INTO `yggl`.`employees` (`EmployeeID`, `Name`, `Birthday`, `Sex`, `Workyear`, `Address`, `PhoneNumber`, `DepartmentID`) VALUES ('302556', '李玉珉', '1968-09-20', '1', '3', '热河路209-3', '58765991', '4');
INSERT INTO `yggl`.`employees` (`EmployeeID`, `Name`, `Birthday`, `Sex`, `Workyear`, `Address`, `PhoneNumber`, `DepartmentID`) VALUES ('308759', '叶凡', '1978-11-18', '1', '2', '北京西路3-7-52', '83308901', '4');
INSERT INTO `yggl`.`employees` (`EmployeeID`, `Name`, `Birthday`, `Sex`, `Workyear`, `Address`, `PhoneNumber`, `DepartmentID`) VALUES ('504209', '陈林琳', '1969-09-03', '0', '5', '汉中路120-4-12', '84468158', '4');

  • 向员工信息表中插入数据
部门号 部门名称
1 财务部
2 人力资源部
3 经理办公室
4 研发部
5 市场部
INSERT INTO `yggl`.`departments` (`DepartmentID`, `DepartmentName`) VALUES ('1', '财务部');
INSERT INTO `yggl`.`departments` (`DepartmentID`, `DepartmentName`) VALUES ('2', '人力资源部');
INSERT INTO `yggl`.`departments` (`DepartmentID`, `DepartmentName`) VALUES ('3', '经理办公室');
INSERT INTO `yggl`.`departments` (`DepartmentID`, `DepartmentName`) VALUES ('4', '研发部');
INSERT INTO `yggl`.`departments` (`DepartmentID`, `DepartmentName`) VALUES ('5', '市场部');

  • 向员工工资表中插入数据
编号 收入 支出
000001 2100.8 123.09
010008 1582.62 88.03
102201 2569.88 185.65
111006 1987.01 79.58
504209 2066.15 108.0
302556 2980.7 210.2
108991 3259.98 285.12
020010 2860.0 198.0
020018 2347.68 180.0
308759 2531.98 199.08
210678 2240.0 121.0
102208 1980.0 100.0
INSERT INTO `yggl`.`salary` (`EmployeeID`, `InCome`, `OutCome`) VALUES ('000001', '2100.8', '123.09');
INSERT INTO `yggl`.`salary` (`EmployeeID`, `InCome`, `OutCome`) VALUES ('010008', '1582.62', '88.03');
INSERT INTO `yggl`.`salary` (`EmployeeID`, `InCome`, `OutCome`) VALUES ('102201', '2569.88', '185.65');
INSERT INTO `yggl`.`salary` (`EmployeeID`, `InCome`, `OutCome`) VALUES ('111006', '1987.01', '79.58');
INSERT INTO `yggl`.`salary` (`EmployeeID`, `InCome`, `OutCome`) VALUES ('504209', '2066.15', '108.0');
INSERT INTO `yggl`.`salary` (`EmployeeID`, `InCome`, `OutCome`) VALUES ('302556', '2980.7', '210.2');
INSERT INTO `yggl`.`salary` (`EmployeeID`, `InCome`, `OutCome`) VALUES ('108991', '3259.98', '285.12');
INSERT INTO `yggl`.`salary` (`EmployeeID`, `InCome`, `OutCome`) VALUES ('020010', '2860.0', '198.0');
INSERT INTO `yggl`.`salary` (`EmployeeID`, `InCome`, `OutCome`) VALUES ('020018', '2347.68', '180.0');
INSERT INTO `yggl`.`salary` (`EmployeeID`, `InCome`, `OutCome`) VALUES ('308759', '2531.98', '199.08');
INSERT INTO `yggl`.`salary` (`EmployeeID`, `InCome`, `OutCome`) VALUES ('210678', '2240.0', '121.0');
INSERT INTO `yggl`.`salary` (`EmployeeID`, `InCome`, `OutCome`) VALUES ('102208', '1980.0', '100.0');

表的基本操作

1.查询Employees表中所有信息

select * from employees;

2.查询Workyear字段,并去掉重复值

SELECT DISTINCT `Workyear` from employees;

3.查询1970-01-01之前和1979-12-31之后出生的员工信息

select * from Employees where birthday<='1970-01-01' or birthday>='1979-12-31';

4.查询在1、3、5部门工作的员工信息

select EmployeeID,Name,Birthday,Sex,Workyear,Address,PhoneNumber,Employees.DepartmentID,DepartmentName from Employees,Departments where  Departments.DepartmentID=Employees.DepartmentID and Departments.DepartmentID in (1,3,5);

5.查询姓名为两个字的员工的信息

select * from Employees where Name like '__';

6.查询在1号部门工作的性别为1的员工信息

###这里有两种方法
select * from Employees,Departments where Departments.DepartmentID=Employees.DepartmentID and Departments.DepartmentID = 1 and Employees.sex = 1;
select * from employees where DepartmentID='1' and sex='1';

7.查询在4号部门和5号部门工作的员工信息

###这里有两种方法
select * from Employees,Departments where Departments.DepartmentID=Employees.DepartmentID and Departments.DepartmentID in (4,5);
select * from employees where DepartmentID='4' or DepartmentID='5';

8.查询所有员工的支出情况,并按支出进行降序排序

select Name,OutCome from Employees,Salary where Employees.EmployeeID=Salary.EmployeeID order by OutCome desc;

9.统计每个部门的职工人数

select DepartmentName,count(Employees.DepartmentID) from Employees,Departments where Departments.DepartmentID=Employees.DepartmentID group by Employees.DepartmentID;

10.统计每个部门的职工人数,并找出职工人数大于等于3人的部门号

select DepartmentName,count(Employees.DepartmentID) from Employees,Departments where Departments.DepartmentID=Employees.DepartmentID and Employees.DepartmentID >=3 group by Employees.DepartmentID;

11.求支出最多的员工的编号

### 这里有两种方法
select EmployeeID,max(OutCome) as 支出 from Departments,Salary;
select employeeid,outcome 
from salary
where OutCome=(select max(OutCome) from salary);

12.求所有员工支出的平均值

select avg(OutCome) as 平均值 from Salary;

13.查询员工的姓名和所在部门名称

select Name,DepartmentName from Employees,Departments where Departments.DepartmentID=Employees.DepartmentID;

14.查询王向荣所在部门的部门名称

select Departments.DepartmentName from Employees,Departments where Name regexp '王向荣' and Departments.DepartmentID=Employees.DepartmentID;

15.查询财务部的员工姓名

select Employees.Name from Employees,Departments where DepartmentName regexp '财务部' and Departments.DepartmentID=Employees.DepartmentID;

16.查询比王林、刘明、李玉珉中某一个人收入高的员工信息

select DISTINCT Employees.* from Employees,Salary where Salary.InCome >= 2100.8 and Salary.InCome >= 2569.88 and Salary.InCome >= 2980.7 and Salary.EmployeeID=Employees.EmployeeID;

17.查询是否存在部门5,如果存在,则查询Employees表的所有记录

select * from employees
where exists
(select DepartmentID from departments where DepartmentID='5');

18.查询是否存在部门号为6的部门,如果不存在,则查询Employees表的所有记录

select * from employees 
where not exists
(select DepartmentID from departments where DepartmentID='6');

19.将Employees表中名称为“叶凡”的PhoneNumber修改为“83308902”

UPDATE `yggl`.`employees` SET `PhoneNumber`='83308902' WHERE `EmployeeID`='308759';

20.在Salary表中的InCome字段上添加普通索引

alter table Salary add index testSalary (InCome);

21.在Employees表中的Name字段上添加唯一索引

alter table Employees add unique index unEmployees (Name);

22.(1)查看Employees表中全部信息的存储过程(2)调用该存储过程

###
delimiter //
create procedure a()
begin 
    select * from employees;
end //
delimiter ;


call a();

23.(1)创建存储过程:查看Employees表中DepartmentID为5的员工人数,并将人数输出到某个变量中。(2)调用该存储过程

### 
delimiter //
create procedure b(out num int)
begin
    select count(*) into num from employees where DepartmentID='5';
end //
delimiter ;

call b(@sum);
select @sum;

24.在Employees表和Salary表上创建视图view_2,通过该视图可以查看员工姓名、支出及收入情况

###
create view view_2(name,income,outcome)
as select name,income,outcome
from employees,salary
where employees.EmployeeID=salary.EmployeeID;

25.查看视图view_2的所有记录

select * from view_2;

26.删除视图view_2

drop view view_2;

27.创建触发器:向Employees表中插入一条新纪录之后向Salary表中插入一条新的纪录(new.EmployeeID,0,0)

create trigger t1 after insert on employees
for each row insert into salary values (new.employeeID,'0','0');

28.删除27中所创建的触发器

drop trigger t1;
评论
说说你的看法