MySQL-基础

数据库管理系统DBMS

MySQL数据模型

客户端->DBMS->多个数据库->多个二维表

SQL语句

DDL(Data Definition Language)数据定义语言（数据库、表、字段）

DML(Data Manipulation Language)数据操作语言

DQL(Data Query Language)数据查询语言

DCL(Data Control Language)数据控制语言，管理数据库用户，控制数据库的访问权限

DDL-数据定义

数据库操作

大类 语句 作用
查询 show databases 查询所有数据库
seclect database() 查询当前数据库
创建 create database [if not exists] 数据库名 （如果不存在）创建一个数据库
使用 use 数据库名; 使用数据库
删除 drop database [if exists] 数据库名 删除数据库

表操作-查询

语句 作用
show tables; 查询当前数据库所有表
desc 表名; 查询表结构
show create table 表名; 查询指定表的建表语句

表操作-创建

1
2
3
4
5
create table 表名(
	字段1 字段类型 [约束] [comment 字段1注释],
    ……
    字段n 字段类型 [约束] [comment 字段1注释]
)[comment 表注释];

关于约束：

约束 描述 关键字
非空约束 限制字段值不能为null not null
唯一约束 保证字段所有数据唯一 unique
主键约束 主键是一行数据的唯一标识，非空且唯一 primary key
默认约束 字段未指定值则用默认值 default
外键约束 让两张表的数据建立连接 foreign key

字段数据类型

数值

类型 大小（byte） 描述
tinyint 1 小整数值
smallint 2 大整数值
mediumint 3 大整数值
int 4 大整数值
bigint 8 极大整数值
float 4 单精度浮点数
double 8 双精度浮点数
decimal 小数值（精确定点数)

例如double,需使用double(整个数的长度，小数部分长度)

字符串

类型 描述
char 定长字符串
varchar 变长字符串
tinyblob 不超过255个字符的二进制数据
tinytext 短文本字符串
blob 二进制形式的长文本数据
text 长文本数据
midiumblob 二进制形式的中等长度文本数据
midiumtext 中等长度文本数据
longblob 二进制形式的极大文本数据
longtext 极大文本数据

日期时间

大小 范围 格式 描述
date 3 1000-01-01至9999-12-31 YYYY-MM-DD 日期值
time 3 -838:59:59至838:59:59 HH:MM:SS 时间值或持续时间
year 1 1901至2155 YYYY 年份值
datatime 8 1000-01-01 00:00:00至9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
timestamp 4 1970-01-01 00:00:01至2038-01-19 03:14:07 YYYY-MM-DD HH:MM:SS 混合日期和时间值，时间戳

修改、删除表结构

语言可以不掌握，但是要会图形化操作

DML-数据操作

添加数据：

insert into 表名(字段1，字段2，……) values(值1,值2,……);

  • 字符串应该和日期型数据应该包含在引号内
  • 插入的数据大小，应该在字段的规定范围内

修改数据

update 表名 set 字段名1=值1,字段名2=值2,…[where 条件]

删除数据

delete from 表名 [where 条件]

DQL-数据查询

基本查询：

基本查询
select 字段列表 from 表名;
select * from 表名;（不建议在开发中使用这种方式，因为不直观）

起别名
select 字段 as ‘别名’ from 表名;as可省略

去重
select distinct 字段名称 from 表名;

条件查询：

select 字段列表 from 表名 where 条件列表;

比较运算符 功能
> >= < <= = !=或<> 比较
between…and … 在某个范围之内（含最小值、最大值）
in(…) 在in之后的列表中的值，多选一
like 占位符 模糊匹配（_ 匹配单个字符，%匹配任意个字符）
is null 是null
逻辑运算符 功能
and或&& 并且
or或|| 或
not或！ 非

例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
select * from emp where name ='初音未来';

select * from emp where job>1;

select * from emp where image is null;

select * from emp where image is NOT null;

select * from emp where job!=1;

select * from emp where job>=2 && job<=3;
select * from emp where job>=2 and job<=3;

select * from emp where job between 1 and 2;

select * from emp where job in(2,3);

select * from emp where name like '____';

select * from emp where name like '%三';

聚合函数：

select 聚合函数(字段列表) from 表名;

1
2
3
4
5
6
7
8
9
10
11
12
-- 聚合函数
-- 统计数量
select count(*) from emp;
select count(image) from emp;-- null值不参与运算
-- 统计平均值
select avg(age) from emp;
-- 统计最大值
select max(age) from emp;
-- 统计最小值
select min(age) from emp;
-- 统计和
select sum(age) from emp;

分组查询：

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

where与having区别

  • 执行时间不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤；
  • 判断条件不同：where不能对聚合函数进行判断，而having可以。

执行顺序：where > 聚合函数 > having

分组之后，查询的字段一般为聚合函数和分组字段，查询其他的字段毫无意义

1
2
3
4
5
6
-- 根据性别分组，统计男性员工和女性员工的数量
select gender,count(*) from emp group by gender;
-- 根据性别分组，统计男性员工和女性员工的平均年龄
select gender,avg(age) from emp group by gender;
-- 查询年龄小于15的员工，并根据性别进行分组，获取员工数量大于等于2的性别(对聚合函数判断，要用having)
select gender '性别',count(*) 'gendersum' from emp where age < 15 group by gender having gendersum>=3;

排序查询：

select 字段列表 from 表名 order by 字段1 排序方式,字段2 排序方式;

排序方式：

ASC：升序（默认值）

DESC：降序

如果多字段排序，当第一个字段值相同的时候，才会根据第二个字段进行排序

分页查询：

select 字段列表 from 表名 limit 起始索引,查询记录数;

  • 起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数；
  • 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL是limit；
  • 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。

顺序

  • 编写顺序：select>from>where>group by>order by>limit
  • 执行顺序：from>where>group by>select>order by>limit

DCL-数据控制

管理用户：

1
2
3
4
5
6
7
8
9
10
-- 查询用户
use mysql;
select * from user;
-- 创建用户
create user 'itcast'@'localhost' identified by '123456';
create user 'miku'@'%' identified by '123456';-- 可以在任意主机访问该数据库
-- 修改用户密码
alter user 'itcast'@'localhost' identified with mysql_native_password by'1234';
-- 删除用户
drop user 'itcast'@'localhost';

权限控制：

1
2
3
4
5
6
-- 查询权限
show grants for 'miku'@'%';
-- 授予权限
grant all on db01.* to 'miku'@'%';
-- 撤销权限
revoke all on db01.* from 'miku'@'%';
  • 多个权限之间，用逗号分隔；
  • 授权时，数据库名和表名可以使用*进行通配，代表所有。

函数

字符串函数

concat(s1,s2,s3,…,sn)字符串拼接

lower(str)转大写

upper(str)转小写

lpad(str,n,pad)用pad左填充

rpad(str,n,pad)用pad右填充

trim(str)去除头部和尾部的空格

substring(str,start,len)截取字符串，索引从1开始

数值函数

ceil向上取整

floor向下取整

mod(x,y)求x/y的模

rand0~1之间的随机数

round(x,y)对x四舍五入后保留y位小数

案例：

1
2
-- 产生一个6位数的随机验证码
select lpad(round(rand()*1000000,0),6,'0');

日期函数

curdate()返回当前的日期

curtime()返回当前的时间

now()返回当前的日期和时间

year(date)返回日期的年份

month(date)返回日期的月份

day(date)返回日期的天数

date_add(date,interval expr type)返回一个推移指定时间长度后的日期

1
select date_add(now(),interval 70 day);

datediff(date1,date2)求取两个日期之间的天数，前边减后边的

流程函数

if(value,t,f)如果value为true，返回t，为false则返回f

ifnull()如果第一个值为null，则返回第二个值；如果第一个值非null，则返回第一个值

case when [value1] then [res1]… else [default] end如果value1为true，返回res1,…否则返回default

case [expr] when [val1] then [res1]… else [default] end如果expr的值为val1则返回res1,…否则返回default

约束

约束-概述

约束 描述 关键字
非空约束 限制字段值不能为null not null
唯一约束 保证字段所有数据唯一 unique
主键约束 主键是一行数据的唯一标识，非空且唯一 primary key
默认约束 字段未指定值则用默认值 default
检查约束(8.0.16版本之后) 保证字段值满足某一个条件 check
外键约束 让两张表的数据建立连接 foreign key

约束-外键约束

外键约束是让两张表之间的数据建立连接，从而保证数据的一致性。

语法

1
2
3
4
5
6
7
8
9
10
-- 方法一
create table 表名(
	字段名 数据类型,
    ...
    [constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名)
);
-- 方法二
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);
-- 删除外键
alter table 表名 drop foreign key 外键名称;

示例：

1
2
3
4
-- 添加外键
alter table emp add constraint fk_dept_id foreign key(dept_id) references dept(id);
-- 删除外键
alter table emp drop foreign key fk_dept_id;

外键删除更新行为

行为 说明
no action 当在父表中删除/更新对应记录时，首先检查该记录中是否有对应外键，如果有则不允许更改
restrict 当在父表中删除/更新对应记录时，首先检查该记录中是否有对应外键，如果有则不允许更改
cascade 当在父表中删除/更新对应记录时，首先检查该记录中是否有对应外键，如果有，则也删除/更新外键在子表中的记录
set null 当在父表中删除对应记录时，首先检查该记录中是否有对应外键，如果有则设置为null（前提是允许为null）
set default 父表有变更时，子表将外键设置成一个默认的值（Innodb不支持） 
1
alter table 表名 add constraint 外键名称 foreign key(外键字段) references 主表(主表列名) on update cascade on delete cascade;

多表查询

多表关系

一对多

通常在多的一方添加外键，关联到一的一方的主键

多对多

建立第三方中间表，中间表至少包含两个外键，分别关联两方主键

一对一

通常用于单表查询，或者将其拆分，并在任意一方加入外键来关联。注意在外键中添加unique约束以保证一对一

连接查询

内连接

隐式内连接：select 字段列表 from 表1,表2 where 条件…；
显示内连接：select 字段列表 from 表1 [inner] join 表2 on 连接条件…；

1
2
3
4
5
6
-- 隐式内连接
select emp.name,dept.name from emp,dept where emp.dept_id = dept.id;
-- 如果为表起了别名，那么就不能通过表名限制字段
select e.name,d.name from emp e,dept d where e.dept_id = d.id;
-- 显式内连接
select emp.name,dept.name from emp inner join dept on emp.dept_id = dept.id;
  • 内连接查询的是两张表交集的部分

外连接

左外连接

select 字段列表 from 表1 left [outer] join 表2 on 条件…;

相当于查询表1（左表）的所有数据包括交集部分的数据

右外连接

select 字段列表 from 表1 right [outer] join 表2 on 条件…;

相当于查询表2（右表）的所有数据包括交集部分的数据

1
2
3
4
5
6
-- 左外连接
select emp.*,dept.name from emp left join dept on emp.dept_id = dept.id;
-- 右外连接
select dept.*,emp.* from emp right join dept on emp.dept_id = dept.id;
-- 使用左外连接实现右外连接
select dept.*,emp.* from dept left join emp on emp.dept_id = dept.id;

常用左外连接实现

自连接

自连接查询使用内连接查询和外连接查询均可，重点是要给一张表起两个不同的别名

内连接查询（例）

select 字段列表 from 表A 别名A join 表 A 别名B on 条件…;

外连接查询（例）

select 字段列表 from 表A 别名A left join 表 A 别名B on 条件…;

联合查询

对于联合查询实际上就是将多次查询的结果合并起来，形成一个新的查询结果集，要使用到关键字union，union all。

语法：

1
2
3
select 字段列表 from 表A…
union [all]
select 字段列表 from 表B…；
  • 对于联合查询的多张表的列数和字段类型需要保持一致
  • union all会将全部的数据直接贴合在一起，union会对合并之后的数据去重

子查询

标量子查询

子查询的结果是单个的值，就称为标量子查询

例如如下的需求，查询某某部门的所有员工信息

1
2
3
4
5
-- 分步查询
select id from dept where name = '研发部';-- 查询某部门（假设为研发部）的对应id
select * from emp where dept_id = 1;-- 由得到的id查询员工信息
-- 子查询
select * from emp where dept_id = (select id from dept where name = '研发部');-- 直接将第一个结果替换id进行查询

列子查询

查询的结果为一列多行，就称为列子查询

常用的操作符

操作符 描述
in 在指定的集合范围之内，多选一
not in 不在指定的集合范围之内
any 子查询返回的列表中，有一个满足即可
some 等同于any
all 子查询返回的列表的所有值必须满足

行子查询

查询的结果为一行多列，就称为列子查询

1
2
-- 查询与“张无忌”的薪资及直属领导相同的员工信息
select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '张无忌');

表子查询

查询的结果为多行多列，就称为表子查询。通常将表子查询的结果作为一个新的表进行多表查询。

1
2
3
4
-- 查询与“鹿杖客”，“宋远桥”的职位和薪资相同的员工信息
select * from emp where (job,salary)in (select job,salary from emp where name = "鹿杖客" or name ="宋远桥");
-- 查询入职日期是"2006-01-01"之后的员工信息，及其部门信息
select e.*,d.* from (select * from emp where entrydate>'2006-01-01') e left join dept d on e.dept_id=d.id

事务

事务是一组操作的集合，这组操作，要么全部执行成功，要么全部执行失败

事务操作

第一种方式

  • 查看/设置事务提交方式
    select @@autocommit;
    set @@autocommit=1;
  • 提交事务
    commit;
  • 回归事务
    rollback;

第二种方式

  • 开启事务
    start transaction或begin;
  • 提交事务
    commit;
  • 回归事务
    rollback;

事务的四大特性ACID

原子性、一致性、隔离性、持久性

并发事务问题

问题 描述
脏读 一个事务读到另一个事物还没有提交的数据
不可重复读 一个事务先后读取同一条记录，但两次读取的数据不同
幻读 一个事务按条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在

事务隔离级别

事务的隔离级别就是来解决并发事务问题的，下表表示解决情况。√表示未解决，×表示解决

隔离级别 脏读 不可重复读 幻读
Read uncommitted √ √ √
Read committed × √ √
Repeatable Read(默认) × × √
Serializable × × ×

查看事务的隔离级别

select @@transaction_isolation;

设置事务的隔离级别

set [global/session] transaction isolation level [隔离级别];

MySQL-进阶

数据引擎

（说到引擎，我个人的理解，就是类似于驱动的意思，是使一个器件、物体、工具能够具有他最基础的功能，比如车辆能够启动、屏幕能够显示……是不包括后续一些精调定制化的部分的。）

不过这并不是很重要，只要知道存储引擎的特点及如何选择就够了

InnoDB：是MySQL的默认存储引擎，支持事务、外键。如果应用对事物的完整性又比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。

MyISAM：如果应用是以读取和插入为主，很少有更新、删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎比较合适。

MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保证数据的安全性。

说是这么说，但是实际上主要用的就是InnoDB了，在MyISAM的使用场景基本被nosql系列的数据库取代了，而MEMORY的场景被Redis取代了。

索引

索引（index）本质上一种数据结构，是为了帮助数据库高效获取数据。

索引大大提高了查询效率，同时却也降低了insert、update、delete的效率吗，不过本身查询就是占大头的，所以瑕不掩瑜。

MySQL常用（默认）的结构就是B+Tree（多路平衡搜索树）

  • 每一个节点，可以存储多个key
  • 所有的数据存储在叶子节点，非叶子节点仅用于索引数据
  • 叶子节点形成了一颗双向链表，便于数据的排序及区间范围查询

这种结构实际上就像一个矮胖的树，避免了二叉树容易层级过深的问题。

语法

  • 创建索引

create [unique] index 索引名 on 表名(字段名,…);

  • 查看索引

show index from 表名

  • 删除索引

drop index 索引名 on 表名;

  • 建表时如果添加了主键约束，那么就会自动创建主键索引，是性能最高的
  • 建表时添加唯一约束，本质就是添加了唯一索引