mysql对yggl数据的建立索引_MySQL之综合实践【从基础到进阶】

前言

本篇文章将前几天所学过的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)

否

是

否

收入

OutCome

float(8)

否

是

否

支出

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

中山路32-1-508

83355668

010008

伍容华

1976-03-28

北京东路100-2

83321321

020010

王向荣

1982-12-09

四牌楼10-0-108

83792361

020018

李丽

1960-07-30

中山东路102-2

83413301

102201

刘明

1972-10-18

虎踞路100-2

83606608

102208

朱俊

1965-09-28

牌楼巷5-3-106

84708817

108991

钟敏

1979-08-10

中山路10-3-105

83346762

111006

张石兵

1974-10-01

解放路34-1-203

84563418

210678

林涛

1977-04-22

中山北路24-35

83467336

302556

李玉珉

1968-09-20

热河路209-3

58765991

308759

叶凡

1978-11-18

北京西路3-7-52

83308901

504209

陈林琳

1969-09-03

汉中路120-4-12

84468158

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’);

向员工信息表中插入数据

部门号

部门名称

财务部

人力资源部

经理办公室

研发部

市场部

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;

原文链接：https://blog.csdn.net/weixin_35991223/article/details/113950100

你可能也喜欢