MySQL

第一章 概念

1.数据库的定义

数据库是根据结构来组织、存储、管理数据的仓库

2.关系型数据库

• ROBMS:(Relational Database Management System)，关系型数据库的简称
• 关系型数据库采用了 关系模型 （一对一、多对对、一对多…）来组织数据

3.数据库的分类与排名

关系型数据库

• MySQL ：开源，免费，甲骨文公司开发，一般在web项目中广泛使用
• Oracle ： 收费，甲骨文公司，一般在大型项目中使用（银行，电信等项目）

非关系型数据库

• Redis ： 高性能缓存数据库
• SQLite ： 轻量数据库，主要用于移动端

4.MySQL的特点

• 开源，免费
• 支持多平台（windows、linux、macOS）
• 支持SQL语言（SQL：结构化查询语言）
• 时学习数据库的首选

5.关系型数据库的核心元素

• 红色：数据库（整个mysql）
• 浅蓝色：数据仓库
• 绿色：数据库
• 紫色：字段（数据列）
• 深蓝色：数据行（一条条数据）

第二章 安装MySQL

1.安装

在centos中安装

yum -y install mysql-server

2.启动

service mysqld start

3.验证

# 验证是否启动成功，可以查看端口号
netstat -an | grep 3306

4.登录

# 登录格式
mysql -u用户名 -p密码

# 案例
mysql -uroot -p  （无密码）

5.修改密码

-- 切换到mysql数据仓库
use mysql；

-- 修改root用户的密码为123456
update user set password=password('123456') where user='root';

-- 刷新权限
flush privileges

6.远程连接

关闭centos的防火墙

service iptables stop

登录mysql，赋予用户远程登录mysql的权限

-- 赋权
grant all on *.* to root@"%" identified by "123456" with grant option;

-- 刷新权限
flush privileges；

第三章 Navicat

1.新建连接

2. 新建数据仓库

3. 新建表

4. 插入数据

第四章 数据类型与约束

数据类型

类型	描述
整数（int）	有符号范围（-21474836482147483647），无符号范围（04294967295）
小数（decimal）	如：decimal(5, 2)，表示共要存5位小数，小数占2位，整数占3位
字符串（varchar)	范围（0~65533），如：varchar(3)，表示最多存3个字符，中文与字母都是占一个
日期类型（datetime）	范围（1000-01-01 00:00:00 ~ 9999-12-31 23:59:59）, 如：2021-03-18 09:53:01

目的：为了保证数据的准确性，必须要用合适的数据类型去存储数据

约束

约束	释义
主键（primary key）	物理上存储的顺序，用来唯一标识每一行数据
非空（not null）	此字段不允许不填（必填）
唯一（unique）	此字段的值不允许重复
默认值（default）	此字段不填时使用默认值，如若填了则使用填写值
外键（foreign key）	维护两个表之间的关系，一般某表中的外键是另一张表的主键

第五章 SQL

1.用户操作

1.1创建用户

-- 格式
create user 'username'@'ip' identified by 'password';
-- 案例：创建一个yangmi用户
create user 'yangmi'@'192.168.13.64' identified by '123456';

1.2 查看用户信息

-- 注释
-- 查看用户信息
select * from user;

1.3 赋权

-- 格式
grant 权限1,权限2 on *.* to '用户'@'ip';

-- 权限有：create、insert、delete、update、select、drop...、all(所有权限)

-- 第一个星号表示数据仓库名，第二个星号表示数据表名，当用*时，表示通配符，匹配所有

-- 案例：给yangmi2赋予查询的权限
grant select on *.* to 'yangmi2'@'localhost' ;

1.4 收回权限

-- 格式 
revoke 权限1,权限2 on *.* from '用户'@'ip';

-- 收回yangmi用户select（查询）的权限 
revoke select on *.* from 'yangmi'@'%';

1.5 修改用户名

-- 格式 
rename user '旧用户名'@'ip' to '新用户名'@'ip';

-- 将reba用户名改为dilireba 
rename user 'reba'@'192.168.13.21' to 'dilireba'@'192.168.13.21';

1.6 修改密码

-- 格式 
set password for '用户名'@'ip'=password('密码');

-- 将zhangsan用户的密码改为tashi123 
set password for 'zhangsan'@'%'=password('tashi123');

1.7 删除用户

-- 格式 
drop user '用户名'@'ip';

-- 删除用户yangmi2 
drop user 'yangmi2'@'localhost';

2.数据表操作

2.1 创建表

-- 格式 
create table 表名( 
字段1 类型 约束, 
字段2 类型 约束...);

-- 案例
-- 创建一张水果表，里面包含水果id、水果名字、水果价格(保留2位小数)、
-- 水果的产地，生产日期 
create table fruits( 
	id int primary key auto_increment, -- auto_increment 表示自动编号
	name varchar(20) not null, 
	price decimal(5, 2), 
	origin varchar(20), 
	date datetime);

-- 案例2
-- 创建学生表，包含学生id（自动编号），学生姓名（不超过10个字符）
-- 学生年龄 
create table students( 
	id int primary key auto_increment, 
	name varchar(10) not null, 
	age int unsigned); -- unsigned表示无符号，不能为负整数

2.2 删除表

-- 格式 
drop table 表名;

-- 案例：删除学生表 
drop table students;

-- 先判断表是否存在，存在再删除 
drop table if exists students;

2.3 备份表

-- 格式 
create table 新表名 as (select * from 旧表);

-- 备份水果表 
create table fruits_bak as (select * from fruits);

2.4 改表名

-- 格式 
rename table 旧表名 to 新表名;

-- 将heros表改名为heros2 
rename table heros to heros2;

3.数据增删改查

3.1 插入数据

全量插入：字段数量与插入值得数量相等

-- 格式 
insert into 表名 values(值1, 值2...);

-- 在水果表中，插入一条苹果的数据，编号为1，价格为3.9，产地为山东，生产日 期为昨天 
insert into fruits values(1, '苹果', 3.90, '山东', '2021-03- 25');

部分插入：对于没有非空约束的字段可以选择性插入

-- 格式 
insert into 表名(字段1, 字段2...) values(值1, 值2);

-- 在水果表中，部分插入一条数据，数据为香蕉
-- 主键有自动编号，名字约束为非空，其余可以不填 
insert into fruits(name) values('香蕉')

批量插入：一次性插入多条数据

-- 全量+批量 
insert into 表名 values(),(),(),();

-- 全量+批量 案例 
insert into fruits values(null,'西瓜', 5.00, '宁夏', '2021-03- 26'), (null, '哈密瓜', 12.00, '新疆', '2021-03-26');

-- 部分+批量 
insert into fruits(name) values('火龙果'), ('梨子');

-- 多个insert插入 
insert into fruits(name) values('橘子'); 
insert into fruits(name) values('芒果'); 
insert into fruits(name) values('菠萝');

3.2 修改数据

-- 格式 
update 表名 set 字段1=值1,字段2=值2... where 条件;

-- 将水果表中的橘子改名成沃柑(如果有多个相同数据，则全部被修改) 
update fruits set name='沃柑' where name='橘子';

3.4 简单查询

-- 查询水果表中的所有数据 
select * from fruits;

4. 基础查询

查询指定的字段

-- 查询水果表中所有的水果名字 
select name from fruits;
-- 查询水果表中所有的水果编号以及水果名字 
select id, name from fruits;

取别名

-- 给字段取别名 
select name as 水果名字 from fruits;

-- 给表取别名 
select name from fruits as f;

5. 去重查询

在 select 后，查询字段前面使用 distinct ，可以去掉重复值

-- 查询所有水果的价格，并且去掉重复值 
select distinct price from fruits;

注意：使用 distinct 对多个字段去重时，只有当多个字段组合值重复时，才会去掉

6. 条件查询

-- 格式 
select 字段 from 表名 where 条件;

-- 查询水果表中价格为10元的水果的信息 
select * from fruits where price=10;

比较运算符

比较运算符返回布尔值，要么是 True ，要么是 False ，条件成立与否

• | 运算符 | 解释 |
  | :----: | :--------: |
  | > | 大于 |
  | < | 小于 |
  | >= | 大于或等于 |
  | <= | 小于或等于 |
  | = | 等于 |
  | != | 不等于 |

案列

-- 查询水果表中价格为10元的水果的信息 
select * from fruits where price=10;

-- 查询水果表中价格大于5元的水果的名字 
select name from fruits where price>5;

-- 查询水果表中价格小于10元的水果的编号和名字 
select id,name from fruits where price<10;

-- 查询山东产的水果的信息 
select * from fruits where origin='山东';

-- 查询非山东产的水果的信息 
select * from fruits where origin!='山东';

-- 查询2021-03-26之前生产的水果的信息 
select * from fruits where date<'2021-03-26';

-- 查询2021-03-26生产的水果的信息 
select * from fruits where date='2021-03-26';

逻辑运算符

符号	解释
and	和
or	或
not	非

案列

-- 查询价格小于5元并且山东产的水果的名字 
select name from fruits where price<5 and origin='山东';

-- 查询山东或者宁夏产的水果的名字 
select name from fruits where origin='山东' or origin='宁夏';

-- 查询非山东产的水果的信息 
select * from fruits where not origin='山东';

-- 查询山东产价格小于5元，或者宁夏产的价格小于10元的水果的信息 
select * from fruits where (origin='山东' and price<5) or (origin='宁夏' and price<10);

模糊查询

• 关键字： like
• % 表示任意一个或者多个字符
• _ 表示任意一个字符

-- 查询产地中包含‘山’的水果的信息 
select * from fruits where origin like '%山%';

-- 查询以“火”开头的水果的名称 
select name from fruits where name like '火%';

-- 查询名称以“瓜”结尾的水果的信息 
select * from fruits where name like '%瓜';

-- 查询名称以“梨”开头的且一共只有2个字的水果的信息 
select * from fruits where name like '梨_';

范围查询

• 连续范围： between…and… ，在…和…之间
• 非连续范围： in ，在…里面

案列

-- 查询价格在5-10之间的所有的水果的信息 
select * from fruits where price between 5 and 10;

-- 查询价格在5-10之间并且西山生产的所有的水果的信息 
select * from fruits where (price between 5 and 10) and origin='西山';

-- 查询在西山或者宁夏生产的水果的信息 
select * from fruits where origin in ('西山', '宁夏');

空判断

• 判断是否为空： is null
• 判断是否不为空： is not null
• 注意：空字符串 ‘’ 与 null 是不一样的

7. 查询排序

• 关键词： order by
• 升序： asc （默认）
• 降序： desc

-- 查询所有的水果信息并且按照价格从小到大排序 
select * from fruits order by price asc; -- asc可以省略

-- 查询所有的水果信息并且按照价格从小到大排序 
select * from fruits order by price desc;

-- 查询所有的水果信息并且价格从小到大排序，如果价格相同时再按照日期从大到 小排序 select * from fruits order by price, date desc;

8. 聚合函数

• 聚合函数一般要放在 select 的后面

max：最大值

-- 查询水果表最高的价格 
select max(price) from fruits;

min：最小值

-- 查询水果表最低的价格 
select min(price) from fruits;

avg：平均值

-- 查询水果的平均价格（结果保留2为小数） 
select round(avg(price), 2) from fruits;

sum：求和

-- 查询山东和宁夏生产的水果的价格之和 
select sum(price) from fruits where origin in ('山东', '宁夏');

count：统计个数

-- 查询水果表中水果的总数 
select count(*) from fruits;

9. 分组查询

• 按照字段分组，字段中相同的数据会被放到一个组中（每次、每种…）
• 分组后的数据可以进行统计，一般搭配聚合函数来使用
• 关键字： group by
• 分组后数据筛选关键字： having

-- 查询每个地区的水果的信息
-- 仅分组的话，只会显示该组内的第一条数据 
select * from fruits group by origin;

-- 查询每个地区的水果的个数 
select origin, count(*) from fruits group by origin;

-- 查询每种价格的水果的个数 
select price, count(*) from fruits group by price;

-- 查询宁夏生产的水果的个数 
select origin, count(*) from fruits group by origin having origin='宁夏';

-- 查询水果表中同名的水果名称及出现的次数 
select name, count(*) from fruits group by name having count(*)>1;

-- 排序要放到分组之后
select price, count(*) from fruits group by price order by price desc;

10.连接查询

10.1 准备数据

10.2 等值连接

注意：在使用「等值连接」时，一定要先找出两张表中 共同 的字段，再去连接查询。

-- 格式 
select * from 表1, 表2 where 表1.字段=表2.字段;

-- 连接水果表与商城表
-- 查询所有的水果与商城的信息
-- 两张表都有的数据才会显示 
select * from fruits f, mall m where f.fid=m.fid;

-- 查询水果的名称，在售平台，库存 
select f.name, m.name, stock
from fruits f, mall m 
where f.fid=m.fid;

-- 查询苹果名称，价格，在售平台，库存 
select f.name, price, m.name, stock
from fruits f, mall m 
where f.fid=m.fid 
and f.name='苹果';

-- 查询所有水果的名称、库存并按库存降序排序 
select f.name, stock
from fruits f, mall m 
where f.fid=m.fid 
order by stock desc;

-- 查询水果的名称，在售平台以及其折扣 
select f.name, m.name, discount
from fruits f, mall m, discount d 
where f.fid=m.fid and m.did=d.did;

-- 查询火龙果的名称、在售平台名称、折扣 
select f.name, m.name, discount
from fruits f, mall m, discount d 
where f.fid=m.fid and m.did=d.did 
and f.name='火龙果';

10.3 内连接

内连接把多张表用相同的字段连接起来，只保留相同的数据，过滤掉多余的数据（取交集）

-- 格式 
select *
from 表1 
inner join 表2 on 表1.字段=表2.字段;

-- 查询水果的名称，在售平台，库存 
select f.name, m.name, stock
from fruits f 
inner join mall m 
on f.fid=m.fid;

-- 查询火龙果的名称、在售平台名称、折扣 
select f.name, m.name, discount
from fruits f 
inner join mall m on f.fid=m.fid 
inner join discount d on m.did=d.did 
and f.name='火龙果';

10.4 左连接

左连接是以左边的表为主表，主表的内容会全部显示，左表有右表没有则显示为 null

-- 格式 
select *
from 表1 
left join 表2 on 表1.字段=表2.字段;

-- 查询所有水果的信息以及其对应的库存信息
-- 因为要查询所有的水果信息，所以要以水果表为主表 
select f.fid, f.name, origin, date, stock
from fruits f 
left join mall m on f.fid=m.fid;

-- 查询所有的库存信息以及其对应的水果名称 
select *
from mall m 
left join fruits f on m.fid=f.fid;

注意：关键词左边的为左表，关键词右边的为右表

11. 子查询

定义：在一个 select 中嵌入另外一个 select ，这就是子查询

• 一般情况下，第一个 select 是 主查询
• 子查询一般充当数据源
• 子查询是可以独立存在，是一条完整的查询语句

-- 查询大于平均价格的水果的信息
-- 1. 查询出平均价格 
select avg(price) from fruits;
-- 2. 查询大于平均价格的水果的信息 
select * from fruits where price>6.725;
-- 组合成子查询，一步到位 
select * from fruits where price>(select avg(price) from fruits);

-- 查询西瓜的库存信息（子查询）
-- 1. 从水果表中查询西瓜的id
-- 2. 根据查出的西瓜id从商城表中查询库存 
select stock
from mall 
where fid=( 
    select fid
    from fruits 
    where name='西瓜');
    
-- 查询苹果的折扣
-- 1. 从水果表查出苹果的fid
-- 2. 根据fid从商城表中查询did
-- 3. 根据did从折扣表中查询折扣 
select discount
from discount 
where did in ( 
    select did
	from mall 
    where fid=( 
        select fid
		from fruits 
        where name='苹果'));

-- 哈密瓜卖的太火了，将库存减少200 
update mall 
set stock=stock-200 
where fid in (
    select fid
	from fruits 
    where name='哈密瓜');

12. limit

• 查询时，用于返回前几条或中间几条数据
• 接受两个参数：第一个是「起始值」，第二是「返回条数值」。注意起始值是从0开始的！

-- 查询水果的最高价格 
select max(price) from fruits; 

select * from fruits 
order by price desc 
limit 0, 1; -- limit 0, 1 表示从第一条开始取，取1条，那就是第一条

13. 日期查询

-- 查询系统时间 
select now(); 

2021-03-29 15:17:12
-- year month day hour minute second

-- 模糊查询，截取某部分的时间
-- 查询2021-03-26 00:00:00生产的水果的信息 
select *
from fruits 
where date='2021-03-26 00:00:00';

-- 查询2021年生产的水果的信息第六章 高级操作 
select *
from fruits 
where year(date)='2021';

-- 查询4月份生产的水果的信息 
select *
from fruits 
where month(date)='04';

-- 查询1号生产的水果的信息 
select *
from fruits 
where day(date)='01';

-- 组合查询 
date_format(日期字段, '%Y-%m-%d %H:%i:%s')

-- 查询2021年4月份生产的水果的信息 
select *
from fruits 
where date_format(date, '%Y-%m')='2021-04';

-- 查询2021年4月1号份生产的水果的信息 
select *
from fruits 
where date_format(date, '%Y-%m-%d')='2021-04-01';

-- 查询2021-03-26 00:00:00生产的水果的信息 
select *
from fruits 
where date_format(date, '%Y-%m-%d %H:%i:%s')='2021-03-26 00:00:00';

第六章 高级操作

1. 命令行使用

• 登录MySQL： mysql -u用户名 -p密码
• 查看所有的数据仓库： show databases;
• 使用某个数据仓库： use 仓库名;
• 查看当前正在使用的数据仓库： select database();
• 创建数据仓库： create database tashi_bak charset=utf8;
• 删除数据仓库： drop database 仓库名;
• 清空表数据： `truncate 表名; ``

2. 数据库的备份与恢复

什么时候用到备份与恢复？

• 搭建新的测试环境时，可以快速导入数据
• 版本更新时，快速导入新数据

备份

mysqldump -u用户名 -p密码 需要备份的仓库名>备份文件存放路径 
# 案例 
# 将tashi仓库备份到当前路径 
mysqldump -uroot -p123456 tashi>./tashi_bak.sql

注意：该命令需在在Linux/Windows命令行中执行！

恢复

mysql -u用户名 -p密码 数据库名<sql文件路径

恢复步骤：

1. 创建一个新的数据仓库，专门用来恢复数据
2. 执行恢复的命令即可

3. 存储过程

「定义」：存储过程也叫存储程序，它是一条或者多条SQL的集合

-- 创建存储过程 
delimiter // 
create procedure gc1() -- gc1是存储过程的名字 
begin 
select avg(price) from phone; 
select sum(price) from phone; 
end 
//

-- 调用存储过程 
call gc1();

总结

• 存储过程其实就是一个可重复执行的SQL语句的集合
• 存储过程只需编译一次，然后被缓存起来，下次直接运行
• 存储过程可以减少网络交互，减少网络访问量

4. 视图

「定义」：本质上是对查询的封装，对于经常需要在多个地方「重复」使用的查询语句，可以将其定义成「视图」，方便使用。

-- 创建视图
-- 格式 
create view 视图名称 as 查询语句;

-- 案例：将 查询手机表所有的价格信息并且按照价格降序排序 封装成视图 
create view phone_view as select price from phone order by price desc;

-- 查看视图 
show tables;

-- 使用视图 
select * from phone_view;

-- 删除视图 
drop view phone_view;

5. 事务

「定义」：事务是一个操作序列，这些操作要么「都执行」，要么「都不执行」

比如一个转账：小明给小方转账500块钱，分解步骤：

• 小明 -500
• 小方 +500

如果小明与小方任意一方失败，则整个转账流程就失败，需要全部回滚。

事务的主要应用场景比如：银行系统，订单系统…

-- 查看自动提交是否开启 OFF是关闭 ON 打开 
show variables like 'autocommit';
-- 如果是ON，那么就要执行关闭的
sql set autocommit=0;

-- 查看表引擎 
show create table 表名;

-- 注意：MyISAM不支持事务功能
-- 如果是上面不支持的引擎，需要修改表引擎 
alter table table_name ENGINE=InnoDB; 

begin; --开始事务 
update phone set price=1999 where pid=3; -- 将手机表中pid为3的价 格修改为1999 
commit; -- 必须要提交事务以后，修改才会生效 

begin; -- 开始事务 
update phone set price=999 where pid=3; -- 将手机表中pid为3的价格 修改为999 
rollback; -- 回滚，如果发现sql执行错了，则可以回滚，相当于sql没有执行过 
commit; -- 提交事务，上面已经回滚，此事务相当于没有执行过

6. 索引

• 在数据库中建立「索引」，可以大大提高数据查询的速度
• 如果表中有主键，则主键就是索引

-- 查看索引
-- 如果表中有主键，则主键就是索引 
show index from phone;

-- 创建一张表，无索引（无主键则无索引） 
create table tb( 
    id int, 
    username varchar(10));

-- 查看tb的索引 
show index from tb;

-- 创建索引
-- create index 索引名称 on 表名(字段); 
create index id_index on tb(id);

-- 再次查看tb的索引 
show index from tb;

-- 删除索引
-- drop index 索引名称 on 表名; 
drop index id_index on tb;

-- 准备10W数据 

set profiling=1; -- 打开SQL运行时间检测 
show profiles; -- 查看运行时间

-- 无索引查询 
select username from tb where id=99999;

-- 记录查询时间 
show profiles;

-- 创建索引
-- 有索引查询 
select username from tb where id=99998;

-- 记录查询时间 
show profiles;

总结

• 有索引相对于无索引来说，查询速度可以提高十倍甚至更快
• 对于一些需要大量增删改的项目来说，不推荐创建索引值，因为每次修改都需要重建索引，会降低修改速度
• 对于数据量大、并且经常以查询为主的项目，则推荐创建索引值