2021-03-30

三天速成——mysql数据库

课程目标

DQL基础(重点)

• 查询所有的数据
• 查询指定的列
• 列别名
• 表别名
• DQL支持的算术运算符
• DQL支持的比较运算符
• DQL支持的逻辑逻辑运算符
• between and 区间查询
• in 集合查询
• like 模糊查询
• distinct 过滤掉重复的行
• 聚合函数(汇总)
• 分组和汇总查询
• 排序
• having
• limit 分页查询

数据完整性(掌握)

• 主键约束
• 外键外键
• 唯一约束
• 非空约束
• 默认约束

索引(了解)

第一章DQL(重点)

Data Query Language 数据查询语言

MySQL客户端(控制台) ，通过一系列的命令发送给MySQL服务器，查询(检索)指定的数据

特征：执行DQL之前和执行DQL之后数据库表中的数据不会发生任何变化。

1.1 查询所有的数据

场景：查询customer表所有的数据

use j1012;
-- *表示匹配所有列
-- 首先执行 from emp 从数据锁定j1012数据库的数据文件,然后从数据文件中选择所有列
select *
from emp;

注意：查询数据库之前一定要指定数据库

1.2 查询指定的列

场景：查询customer表里面的ID和客户名称

select id,cust_name
from customer;

小结：先确定表然后确定表中需要那些列，所以先执行from后执行select

1.3 列别名

别名就是小名

使用别名的目的：让查询的结果更友好

列别名通常写在列名称的后面，列名称和列别名之间有一个空格

select empno 员工编号,ename 员工姓名,sal 基本工资
from emp;

1.4 表别名

使用表别名目的：提高DQL的可读性，为后面学习多表联合查询做铺垫

表别名写在表名称的后面，表名称和表别名之间有一个空格

select empno 员工编号,ename 员工姓名,sal 基本工资
from emp 员工表;

小结：SQL语句不要全部写在一行，使用关键字来换行

1.5 DQL的算数运算符

+ - * /   

MySQL只支持4中算数运算符。

MySQL的算数运算符可以跟列名称一起使用，通常对某一列数数据进行算术运算

场景：把员工表的“基本工资”+100

-- 把员工表的“基本工资”+100
select empno ,ename,sal,sal+100 新的工资
from emp;

1.6 DQL的比较运算符

> >=   <  <=  
!=和<> 都表示不等于
= 表示等于 ，MySQL没有 == 运算符

比较运算符用在where子句中，进行行过滤。过滤掉不满足条件的记录。

场景：查询员工工资大于1500的员工

目的：演示where关键字，演示比较运算符，MySQL中比较运算符通常写在from 表名称后面

-- 查询员工工资大于1500的员工
select empno,ename,job,sal
from emp
where sal > 1500;

执行顺序：from(确定要查询哪张表)—> where(过滤不满足的条件) -----> select(选择结果集中的列)

小结：where必须写在from关键字的后面，作为from关键字的过滤条件

场景：查询职位不是“经理”的所有员工信息

select empno,ename,job,sal
from  emp
where job <> 'MANAGER';

1.7DQL的逻辑运算符

Java:   &&    ||  !
MySQL:  and   or  not

场景：查询所在部门为10并且工资大于2000的所有员工信息,使用逻辑运算符and

select empno,ename,job,sal,deptno
from emp
where deptno = 10 and sal > 2000;

场景：查询所在部门为10或者工资大于2000的所有员工信息，使用逻辑运算符or

select empno,ename,job,sal,deptno
from emp
where deptno = 10 or sal>2000;

场景：查询所在部门为不是10并且工资不大于2000的所有员工信息，使用逻辑运算符not

select empno,ename,job,sal,deptno
from emp
where not (deptno = 10 and sal > 2000);

1.8区间查询

between and 关键字 在…之间

场景：查询工资在1000到2000之间的所有员工工资

select empno,ename,sal,job,hiredate,deptno
from emp
where sal between 1000 and 2000;
-- 等同于下面的条件
-- where sal >= 1000 and sal <= 2000
-- 小结：between后面跟一个“低值”，and后面跟一个“高值”

4.9 in 集合查询

in 关键字 在…里面

场景：查询职位是“经理”、“销售员”，“职员”的所有员工信息

注意：in是MySQL的关键字，通常跟一对小括号一起使用，小括号里面就是一个子集合

select empno,ename,job,deptno,hiredate
from emp
where job in('CLERK','MANAGER','SALESMAN');

场景：查询职位不是“经理”、“销售员”，“职员”的所有员工信息

select empno,ename,sal,job,hiredate,deptno
from emp
where  job not in('CLERK','MANAGER','SALESMAN');

1.10 like 模糊查询

like 关键字 像…一样

语法：where 字段名称 like ‘要查找的字符串%’

​ %表示模糊匹配多个字符

​ 要查找的字符串必须写在一对半角单引号里面

1.10.1 前面精确后面模糊

场景：查询所有以A开头的员工姓名

select empno,ename,job,sal,hiredate,deptno
from emp
where ename like 'A%';

小结：like关键字通常用在where后面 like查询的数据类型通常是字符串类型

1.10.2 前面模糊后面精确

语法：where 字段名称 like ‘%要查找的字符串’

场景：查询员工名称以S结束的所有员工信息

select empno,ename,deptno,job,sal,hiredate
from emp
where ename like '%S';

1.10.3 前后模糊中间精确

语法：where 字段名称 like ‘%要查找的字符串%’

场景：查询员工名称包含A的所有员工信息

select empno,ename,sal,job,hiredate,deptno
from emp
where ename like '%A%';

模糊查询小结：第二种和第三种，它们查询效率不高工作中慎用。

1.11 distinct 过滤掉重复的行

distinct 关键字

场景：查询员工表存在的职位信息，要求职位不能重复

select distinct job
from emp;

小结：distinct关键字表示过滤掉重复的行，后面跟列名称

​ MySQL的DQL查询默认都是大写，我们如果用小写定义DQL，那么在执行的时候统一转换为大写

1.12 聚合函数

MySQL支持5个聚合函数，用于对查询结果集做汇总

特征：每个聚合函数查询结果只能是"单行单列"

​ 列名称作为聚合函数的参数，参数类型最好使用数值类型

1.12.1 count

通常获取结果集总行数

场景：查询emp表有多少行数据

步骤：使用聚合函数count统计员工表的总行数

-- 使用列别名让查询更友好
select count(empno) 员工总数
from emp;

1.12.2 sum

计算某一列的和

场景：查询所有员工工资总和

步骤：使用聚合函数sum，对sal列的所有工资进行累加求和

select sum(sal) 工资总和
from emp;

1.12.3 max

计算某一列的最大值

场景：查询员工表的最高工资

步骤：使用聚合函数max，返回sal列的最高工资

select max(sal) 最高工资
from emp;

1.12.4 min

计算某一列的最小值

场景：查询员工表的最低工资

步骤：使用聚合函数min，返回sal列的最低工资

select min(sal) 最低工资
from emp;

1.12.5 avg

计算某一列的平均工资

场景：查询员工表的平均工资

步骤：使用聚合函数avg，返回sal列的平均工资

select avg(sal) 平均工资
from emp;

1.12.5 多个聚合函数一起使用

查询：员工表的总行数、工资总和、最高工资、最低工资、平均工资

select count(*)总行数,sum(sal)工资总和,max(sal)最高工资,min(sal)最低工资,avg(sal)平均工资
from emp;

小结：先查询结果，然后使用聚合函数做汇总。聚合函数通常用在整数类型的列和小数类型的列。

​ 聚合函数计算结果要么是整数，要么是小数

1.13 group by分组和汇总查询

​ 刚才学习了聚合函数对员工表的工资做了汇总，但是无法对每个职位的工资做汇总。如果我们想计算每个职位的总工资，就需要对职位进行分组，然后再对工资进行汇总。

​ 分组关键字: group by

​ group by 将表中的数据分成若干组

​ 语法：

select 列名称,聚合函数
from 表
group by 列名称[列别名];

场景：计算每个职位的工资总和

步骤：1 使用from关键字指定表名称

​ 2 使用group by 关键字对job列进行分组

​ 3 定义select语句对职位进行分组，使用聚合函数sum对sal列进行汇总求和

select job,sum(sal)
from emp
group by job ;
-- 列别名
select job,sum(sal)职位工资总和
from emp
group by job ;
-- group by 使用列别名
select job 职位,sum(sal)职位工资总和
from emp
group by 职位;

注意：如果一个select查询语句有where和group by，那么group by必须写在where的后面。

场景：计算每个职位的工资总和，但是不包括员工编号为7788的员工

步骤：1 使用from 关键字查询emp表

​ 2 使用where关键字过滤掉7788的员工信息

​ 3 使用group by 关键字对职位进行分组

​ 4 定义select关键字，使用聚合函数计算每个职位的工资总和

select job 职位名称, sum(sal) 职位工资总和
from emp
where empno <> 7788
group by 职位名称;

1.14 having

having跟where关键字一样都用于行过滤，但是where不支持聚合函数，如果对聚合函数进行过滤此时使用having关键字。

场景：在上一个场景的基础上只统计大于5000的职位工资总和

步骤：1 使用from 关键字查询emp表

​ 2 使用where关键字过滤掉7788的员工信息

​ 3 使用group by 关键字对职位进行分组

​ 4 使用having对聚合函数，过滤掉职位工资总和小于5000的数据

​ 5 定义select关键字，使用聚合函数计算每个职位的工资总和

select job 职位名称,sum(sal) 职位工资总和
from emp
where empno <> 7788
group by 职位名称
having 职位工资总和>5000;

1.15 order by 排序

排序关键字： order by

语法 ： order by 列名称 或者 列别名 asc 或者 desc

asc 升序排序

desc 降序排序

默认asc

注意：order by 关键字 必须定义在group by 的后面

场景：在上一个场景基础上对职位工资总和进行升序排序

步骤：1 使用from 关键字查询emp表

​ 2 使用where关键字过滤掉7788的员工信息

​ 3 使用group by 关键字对职位进行分组

​ 4 使用having对聚合函数，过滤掉职位工资总和小于5000的数据

​ 5 定义select关键字，使用聚合函数计算每个职位的工资总和

​ 6 使用order by 关键字对职位工资总和进行升序排序

select job 职位名称, sum(sal)职位工资总和
from emp
where empno <> 7788
group by 职位名称
having 职位工资总和> 5000
order by 职位工资总和 ;

小结：在结果集查询完毕的情况下进行排序，select执行完毕才会执行order by

1.16 limit 返回最大限制的行数

关键字 limit

limit限制最多返回的行数，工作中通常用户分页查询

limit n; 此时n表示最多返回n表

limit m , n ; 此时m表示下标偏移量(offset)，n表示最多返回n条

场景：查询第三行后面五行的员工信息

使用关键字： limit m,n

步骤：

​ 1 使用from关键字查询emp表

​ 2 使用select关键字指定列名称

​ 3 使用limit关键字返回第三行后面五行的员工信息

select empno,ename,sal,job,deptno
from emp
limit 3,3;

1.17 多表联合查询

将多个表的查询数据合并成一张表(内存表)，简称：化零为整。常用的多表联合查询：内连接、左外连接、右外连接。

为什么使用多表联合查询？下面的SQL语句只能部门编号，无法查询部门名称

select empno,ename,deptno
from emp;

我们想查询下面的结果，必须将多个表的数据联合组装查询

员工编号	员工名称	部门名称
7369	SMITH	RESEARCH
7934	MILLER	ACCOUNTING

1.17.1 内连接

A∩B：A表与B表的交集

关键字 inner join on

inner join查询多张表条件都满足的数据，

on 条件过滤，过滤掉不满足条件的语句

场景：查询员工编号、员工姓名、部门名称

步骤：

​ 1 定义from关键字和inner join关键字列出需要哪些表

​ 2 定义on关键字对条件进行过滤

​ 3 定义select关键字指定要查询的列

select emp.empno,emp.ename,dept.dname
from emp inner join dept 
on emp.deptno = dept.deptno;

上面的场景使用on关键字过滤掉除此之外还可以在where上指定条件

select emp.empno,emp.ename,dept.dname
from emp,dept
where  emp.deptno = dept.deptno;

上面的场景，使用表名称.列名称指定查询哪个表的列，除此之外还可以使用表别名.列名称，这样做的好处让DQL更加简洁

select e.empno,e.ename,d.dname
from emp e,dept d
where  e.deptno = d.deptno;

小结：多表联合查询在指定列时必须使用"表名称".列名称，或者"表别名".列名称。因为必须告诉MySQL服务器你的列属于那张表。

1.17.2 左外连接

AUA∩B 查询两张表都满足的数据，以及左边表独有的数据(以左边表为主查询表)。

关键字：left join on

场景：使用左外连接将dept表作为左表，观察内连接和左外连接有啥区别？

select emp.empno,emp.ename,dept.dname
from dept left join emp 
on emp.deptno = dept.deptno;

1.17.3 右外连接

**BUA∩B **查询两张表都满足的数据，以及右边表独有的数据(以右边表为主查询表)。

关键字：right join on

​ 可以自学

第二章数据完整性

确保实体之间数据的有效性、一致性避免脏数据

脏数据：两个省份有两个“江苏省”就是脏数据。

MySQL通过5种约束实现数据完整性

2.1 主键约束

概念：主要的关键字，用来确保每一行在表中的唯一性

身份证编号能够确保每一行在表中的唯一性，所以将它作为身份证表的主键，此时身份证表的编号不能为空。

主键关键字：primary key

主键约束特征：非空并且唯一

身份证编号	地址	姓名	日期
420105XXXXX	湖北省XXXXX	张三	1990-10-10
420106XXXXX	湖北省XXXXX	李四	2000-01-01
420107XXXXX	湖北省XXXXX	王五	2001-03-08

创建主键语法：

primary key(主键列)

2.2 非空约束

概念：确保列的值不能为空

非空约束关键字： not null

特征：通常在定义列的时候设置非空约束

场景：定义一个省份表，将id设置为主键，省份名称不能为空

create table tb_provinces(
 id int(11) auto_increment,
 province_name varchar(50) not null,
 province_abbreviation varchar(10) not null,
 primary key (id)
);

注意：主键约束通常在表的内部创建

2.3 外键约束

概念：外部关键字，在多个表之间建立约束，建立外键的目的确保数据一致性、有效性。外检约束相当于Java中的关联关系(has a)。

特征：在某张表定义一个外部关键字去关联另外一张表的主键，外键和另一张表的主键列数据必须一致，否则就违背了数据完整性。

外键关键字 : foreign key references

语法： foreign key(外键列名称)  references  关联表(关联表的主键); 

2.4 唯一约束

概念：确保该列的每一条数据都不能重复

跟主键约束的区别：主键约束不能为空，唯一约束可以为空

唯一约束关键字：unique

2.5 默认约束

概念：定义一个列为其赋默认值

默认约束关键字：default

例如：为城市表的“城市描述”列定义默认约束，默认值：‘ABCDEFG’

场景：创建城市表，将id列定义为主键并且自动增长，为“城市简称”列定义唯一约束，为“省份编号”定义外键，去关联省份表的主键

create table tb_citys(
 id int(11) auto_increment,
 city_name varchar(50) not null,
 city_arrea varchar(10) unique,  -- 唯一约束  城市区号 例如：武汉(027)
 city_desc varchar(200) default 'ABCDEFG', -- 默认约束 城市描述   
 province_id int(11) ,
 -- 为tb_citys 的id列建立主键
 primary key (id),
 -- 为tb_citys的province_id列建立外键，去关联tb_provinces表的主键
 foreign key(province_id) references tb_provinces(id)   
);      

小结：没有外键的表叫做主表，有外键的表叫做从表。

2.6插入数据

创建表顺序：先建立主表，后建立从表，外键表是从表承载了软件公司的业务

插入数据：先向主表插入数据，后向从表插入数据。省份表是主表，城市表是从表，先有省份后有城市。

insert into tb_provinces(province_name,province_abbreviation)values('湖北省','鄂');
insert into tb_citys(city_name,city_arrea,city_desc,province_id) value('武汉','027','英雄的城市，武汉一定能够过关的',1);

删除数据：先删除从表的数据，后删除主表的数据。

 -- 下面DML语句会失败，因为删除主表数据的时候MySQL服务器会校验，主表相关的所有从表数据存不存在，如果从表相关的数据存在，不让删除主表数据
 delete from tb_provinces;

小结：先插入主表数据后插入从表数据，先删除从表数据后删除主表数据

2.7课后作业

参照如下的说明为送水公司设计出数据库表结构
1你现在要为送水公司设计管理系统
2公司里有多少个送水工，他们的工资是这样计算的：底薪+(送水总数*每桶的提层比例)
3每个送水工的每桶提成比例不一样
4客户只有事先定了水票才能要求送水工送水
5送水工送了多少桶水就会从客户那里得到多少张水票
6要求能查出
A某客户手里还有几张水票
B某送水工送了多少桶水
C某送水应得得工资
-- 建立表  找名词  
-- 为每张表建立字段(列)
-- 在表之间建立关联