三天速成——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某送水应得得工资
-- 建立表 找名词
-- 为每张表建立字段(列)
-- 在表之间建立关联