MySql学习总结-基础篇

Mysql基础

MySql概述

几个名词

学习mysql之前，先弄清几个名词的意思。

常见的数据库管理系统：Oracle、Mysql、SQLServer、DB2。

SQL

DDL

数据定义语言，用来定义数据库对象。
数据库对象：数据库，表，字段
数据库操作

-- 查询所有数据库
show databases;

-- 查询当前数据库
select database();

-- 创建
-- create database [if not exists] 数据库名 [default charset 字符集][collate 排序规则]
create database if not exists landzDB charset=utf8mb4 collate=utf8mb4_general_ci;

-- 删除
-- drop database [if exists] 数据库名;
drop database if exists landzDB;

-- 使用
-- use 数据库名;
use landzDB;

/**
collate 类型
utf8_general_ci 不区分大小写，这个你在注册用户名和邮箱的时候就要使用。
utf8_general_cs 区分大小写，如果用户名和邮箱用这个 就会造成不良后果
utf8_bin:字符串每个字符串用二进制数据编译存储。 区分大小写，而且可以存二进制的内容

utf8_general_ci校对速度快，但准确度稍差。
utf8_unicode_ci准确度高，但校对速度稍慢。
*/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

表操作

-- 查询当前数据库所有的表
show tables;

-- 查询表结构
desc 表名;

-- 查询指定的建表语句
show create table 表名;

1
2
3
4
5
6
7
8
9

-- 创建表
create table 表名(
  字段1 字段1类型[comment 字段1注释],
  字段2 字段2类型[comment 字段2注释],
  字段3 字段3类型[comment 字段3注释],
  字段4 字段4类型[comment 字段4注释],
  ...
  字段n 字段n类型[comment 字段n注释]
)[comment 表注释];

-- 创建表 标识无符号有符号
create table tb_user(
  id int unsigned comment '用户id'
)

-- 查询字段注释
select COLUMN_NAME,COLUMN_COMMENT,COLUMN_KEY
from
    INFORMATION_SCHEMA.Columns
where
        table_name = 'tb_emp'
  and table_schema='landzDB'



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

datetime 和 timestamp的区别

https://www.jb51.net/article/223960.htm

思考题：char和varchar的区别？

char和varchar的区别char 长度固定，插入性能好，但是费空间。
varchar 长度不固定，每次都要计算长度，性能差，但是省空间。

-- 修改数据类型
alter table 表名 modify 字段名 新数据类型;
-- 修改字段名和字段类型
alter table 表名	change 旧字段名 新字段名 类型 [comment 注释][约束];
-- 添加字段
alter table 表名 add 字段名 类型 [comment 注释][约束];
-- 删除字段
alter table 表名 drop 字段名;
-- 修改表名
alter table 表名 rename to 新表名;
-- 删除表
drop table [if exists] 表名;
-- 删除并重建
truncate table 表名;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

DML

数据操作语言，用来对数据库中的数据进行增删改。
添加数据

-- 给指定字段添加数据
insert into 表名(字段1,字段2,...) values(值1,值2,...);
-- 给全部字段添加数据
insert into 表名 values(值1,值2,...);
-- 批量添加数据
insert into 表名 (字段1,字段2,...) values(值1,值2,...),(值1,值2,...),(值1,值2,...);
insert into 表名 values(值1,值2,...),(值1,值2,...),(值1,值2,...);


1
2
3
4
5
6
7
8
9

1、插入数据时，指定的字段顺序需要与值得顺序是一一对应的
2、字符串和日期类型需要用’'包裹
3、插入数据的长度要在字段规定的范围内

修改数据

--修改数据
update 表名 set 字段1 = 值1, 字段2 = 值2 [where 条件];
1
2

删除数据

--删除数据
delete from 表名 [where 条件];

/**
要有where 否则删除整个表中的数据
**/

1
2
3
4
5
6
7

要有where 否则删除整个表中的数据

DQL

数据查询语言，用来查询数据库中的记录。

/**
	select 
      	字段列表
	from	
      	表名列表
	where 
      	条件列表
	group by
      	分组字段列表
  having 
      	分组后条件列表
	order by
      	排序字段列表
	limit
      	分页参数
**/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- 条件列表
>,>=,<,<=,=,!=或<>,between 0 and 100,in (1,2,3),like _(单个字符)%(匹配任意字符)
,is null

-- 逻辑运算符
and 或 &&,or 或 ||, not 或 !

1
2
3
4
5
6
7

-- 聚合函数
count,max,min,avg,sum
-- eg.
select sum(age) from tb_person;

1
2
3
4
5

--分组查询
select 字段列表 from 表名 where 条件 group by 分组字段 having 分组后的过滤条件

/**
	执行顺序:where> 聚合函数> having
	分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
**/
1
2
3
4
5
6
7

执行顺序:where> 聚合函数> having
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

练习题：表emp，字段有 workaddress（工作地点）,gender（性别）,age（年龄）,name（姓名）。
1、统计男性员工和女性员工的平均年龄
2、查询年龄小于45且员工数量大于等于3的工作地址

--1、
select gender,avg(age) from emp group by gender;
--2、
select workaddress,count(*) address_count  from emp  where age <45 group by workaddress having address_count >=3;
1
2
3
4

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

-- asc 升序（默认）
-- desc 降序
1
2
3
4

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

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

1、索引从0开始，起始索引 = （页码-1)*每页显示记录数
2、分页是数据库的方言，不同的数据库有不同的实现，mysql中使用limit
3、如果查询是第一页的数据，起始索引可以省略，如limit 10;

执行顺序

-- 编写顺序
select..from..where..group by.. having.. order by.. limit..

-- 执行顺序
from.. where.. group by...having..select..order by.. limit.. 
1
2
3
4
5

DCL

数据控制语言，用来创建数据库用户、控制数据库的访问权限。
1、用户管理

-- 查询用户
use mysql;
select * from user;
-- 创建用户
create user ’用户名’@‘主机名‘ identified by '密码';
-- 修改用户密码
alter user ’用户名’@‘主机名‘ identified with mysql_native_password by '密码';
-- 删除用户
create user ’用户名’@‘主机名‘;

1
2
3
4
5
6
7
8
9
10

2、权限控制

grant 权限列表 on 数据库名.表名 to ’用户名’@‘主机名‘;
revoke 权限列表 on 数据库名.表名 to ’用户名’@‘主机名‘;
1
2

函数

字符串函数

-- emp 表中，企业工号 workno ，统一5位数，不足5位，前面补0。例如：1号员工，工号应为00001。
update emp set workno = lpad(workno,5,'0');
1
2

数值函数

日期函数

流程函数

练习题：查询emp表中的员工姓名name和工作地址address分级（北京/上海–>一线城市，其他–>二线城市）

select name,(case address when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end)as '工作地址'
from emp;
1
2

练习题：查询score表中学生姓名和数学math，英语english，语文chinese的成绩等级（85及以上优秀，60包含~85 及格，60以下不及格）

select name,
(case when math>=85 then '优秀' when math>=60 then '及格' else '不及格' end)'数学',
(case when english>=85 then '优秀' when english>=60 then '及格' else '不及格' end)'英语',
(case when chinese>=85 then '优秀' when chinese>=60 then '及格' else '不及格' end)'语文'
from score;
1
2
3
4
5

约束

概述

1、概念：约束是作用于表中字段的规则，用于限制存储在表中的数据。
2、目的：保证数据库中数据的正确性，有效性，完整性

约束分类

-- 约束列子
create table tb_user(
  id int auto_increment primary key comment 'id 唯一标示',
  name varchar(10) not null unique comment '姓名',
  age int check(age>=0&&age<=120) comment '年龄',
  status char(1) default 1 comment '状态',
  gender char(1) comment '性别',
  org_id int comment '组织id',
  constraint fk_user_org_id foreign key(org_id) references tb_dept(id)
);

-- 建表后，添加外键
alter table tb_user add constraint fk_user_org_id foreign key(org_id) references tb_dept(id);

-- 删除外键
alter table tb_user drop foreign key fk_user_org_id;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

外键删除更新行为

alter table tb_emp add constraint fk_emp_org_id foreign key(org_id) references tb_org(id) on update set null;

-- 
alter table tb_emp
    add constraint fk_emp_org_id foreign key (org_id) references tb_org (id) on update set null on delete set null;

1
2
3
4
5
6

多表

多表关系

一对多（多对一）

eg. 部门与员工
关系：一个部门多个员工，一个员工一个部门
实现：在多的一方建立外键，指向一的一方的主键
本例中：
员工表建立外键，指向部门表的主键。

多对多

eg.学生和课程
关系：一个学生可以选多门课程，一个课程可以被多个学生选择。
实现：通过创建中间表，中间表中包含两个外键，来关联两个主键。
本例中：创建选课表（中间表）,包含学生id外键和课程id外键，分别指向学生表的主键和课程表的主键。

一对一

eg.商品和详情
关系：一个商品只能有一个详情
实现：在任意一方加入外键，指向另一方的主键，并且外键设置唯一约束。

多表查询

内连接

查询两个表交集的部分

-- 隐式内连接
select 字段列表 from 表1,表2 where 条件;

-- 显示外连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件;
1
2
3
4
5

外连接

查询的某一个表和两表交集的部分

-- 左外连接，取左表和两表相交的数据
select 字段列表 from 表1 left join 表2 on 连接条件;

-- 右外连接，取右表和两表相交的数据
select 字段列表 from 表1 right join 表2 on 连接条件;
1
2
3
4
5

自连接

自己连自己

-- 自连接
select 字段列表 from 表1 别名1 join 表1 别名2 on 条件;
1
2

练习题：查询emp中，员工和所属领导的名字
表结构：id，name，managerid。

select a.name '员工',b.name '领导' from emp a join emp b on a.managerid = b.id;
1

联合查询

把两次查询的结果合并，形成一个新的结果集

select 字段列表 from 表1 

union[all]

select 字段列表 from 表2;

1
2
3
4
5
6

两个字段列表列数必须一致，否则无法使用union。
union all 会把数据全部合并到一起，union 会去重。

练习：将研发部的员工和年龄大于32岁的员工全部查询出来

select * from tb_emp where org_id=3
union all
select * from tb_emp where age>32;
1
2
3

子查询

sql 语句嵌套 select 语句就叫做嵌套查询，又叫子查询。

标量子查询

-- 查询员工表中比 李明 大的员工，假设姓名不重复
select * from emp where age > (select age from emp where name = '李明');
1
2

列子查询

-- 查询员工表中比 李明小红都大 的员工，假设姓名不重复
select * from emp where age > all(select age from emp where name in ('李明','小红'));
1
2

行子查询

-- 查询员工emp表中年龄和直属领导和李明相同的员工
select * from emp where (age,manager_id) = (select age,manager_id from emp where name ='李明');
1
2

表子查询

-- 查询员工emp表中年龄和直属领导和李明、李红相同的员工
select * from emp where (age,manager_id) in (select age,manager_id from emp where name in('李明','李红'));
1
2

事务

简介

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有操作作为一个整体一起向系统提交或撤销操作的请求，即这些操作要么同时成功，要么同时失败。

-- 查看事务是否开启，1  开启 0 关闭 mysql默认是开启事务的。
select @@autocommit;
-- 事务提交操作

-- 方式1
-- 1、关闭自动事务
set @@autocommit = 0;
-- 提交
commit;
-- 回滚
rollback;

-- 方式2

-- 1、开启事务
start[begin] transaction;
-- 提交
commit;
-- 回滚
rollback;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

ACID

并发事务问题

事务的隔离级别

-- 查看隔离级别
select @@transaction_isolation;

-- 设置隔离级别 

set [session|global] transaction isolation level [read uncommitted|read committed|repeatable read|serializable]
1
2
3
4
5
6

存储引擎

mysql体系结构

连接层
最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层
第二层架构主要完成大多数的核心服务功能，如sql接口，并完成缓存的查询，sql的分析和优化，部分内置函数的执行。所有跨存储引擎功能也在这一层实现，如过程、函数等。
引擎层
存储引擎真正的负责了mysql中数据的存储和提取，服务器通过api和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。
存储层
主要是将数据存储在文件系统至上，并完成与存储引擎的交互。

存储引擎简介

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被成为表类型。

-- 1、创建表的时候，指定存储引擎
create table 表名(
	字段1...,
  字段2...,
  字段3...
)engine = innodb

-- 2、查看当前数据库支持的存储引擎
show engines;
1
2
3
4
5
6
7
8
9

存储引擎特点

InnoDB

介绍
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在mysql 5.5之后，InnoDB是默认的mysql存储引擎。
特点
DML操作遵循ACID模型，支持事务；
行级锁，提高并发访问的性能；
支持外键FOREIGN KEY 约束，保证数据的完整性和正确性；
文件
xxx.idb：xxx代表表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm,sdi）、数据和索引。
逻辑存储结构

MyISAM

介绍
myISAM是mysql早期的默认存储引擎。
特点
不支持事务，不支持外键；
支持表锁，不支持行锁；
访问速度快；
文件
xxx.sdi:存储表结构信息
xxx.MYD:存储数据
xxx.MYI：存储索引

Memory

介绍
Memory引擎的表数据是存储在内存中的，由于硬件问题、或断点问题的影响，只能将这些表作为临时表或缓存使用。
特点
内存存放
hash索引
文件
xxx.sdi：存储表结构的信息

存储引擎选择

索引

索引概述

索引是帮助mysql高效获取数据的数据结构。
下图中如果查询年龄为18的数据，在没有索引的情况下需要进行全表扫描进行查找需要对比查找5次才能找到目标数据，效率比较低。

如果给年龄age建立索引，如果使用平衡二叉树这种数据结构建立索引，我们可以得到一个这样的二叉树。

这样找到age为18的数据，需要对比3次即可找到目标数据，大大提高了效率，但是mysql不是用平衡二叉树作为索引结构的。

索引结构

mysql的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含一下几种：

mysql中大部分引擎都支持B+树索引。为什么是B+树，不是二叉查找树、avl树、红黑树呢？
首先二叉查找树在顺序情况下会严重失去平衡，从而降低查找的效率。

avl树和红黑树虽然是自平衡的二叉查找树，但是归根结底是二叉树，在数据量大的时候，树的高度会非常高（层级较深），影响检索效率。

B-Tree（多路平衡查找树）

B+Tree

mysql索引数据结构对经典的B+Tree进行了优化。在原来B+Tree的基础上，增加了一个指向相邻叶子节点的链表指针，就形成了带有有顺序指针的B+Tree，提高区间访问的性能。

B+Tree的索引存在页上，一页16k，所以可以存储更多的索引，降低层级，效率高。

Hash索引

特点：
1、hash索引只能用于对等比较（=,in），不支持范围查询（between，>，<）
2、无法完成排序操作
3、查询效率高，通常只需要一次检索就可以
mysql中，支持hash索引的是memory引擎，而innodb中具有自适应hash功能，hash索引是存储引擎根据b+tree索引在指定条件下自动构建的。

思考：为什么InnoDB存储引擎选择使用B+Tree索引结构，而不是红黑数和B树

1、红黑树是二叉树，在大数据量的时候，深度较深，搜索效率低，B+树层级更少，搜索效率高。 
2、B树所有节点都会存放行数据，在数据量大的时候，一页存放的key减少，导致层级加深影响搜索效率
1
2

索引分类

在InnoDB中，根据索引的形式，可以分为以下两种

聚集索引的选取规则
1、如果存在主键，主键索引就是聚集索引。
2、如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
3、 如果没有主键、或者没有唯一索引，则Inno会自动生成一个rowid作为隐藏的聚集索引。

-- name 建立了二级索引
select * from user where name = 'Arm';
1
2

回表查询

思考：以下哪个SQL语句执行效率高？为什么？(id 为主键，name字段有索引)

select * from user where id = 10;
select * from user where name = 'Arm';
1
2

索引语法

-- 创建索引
create [UNIQUE|FULLTEXT] index index_name on table_name(index_col_name,...);

-- 查看索引
show index from table_name;

-- 删除索引
drop index index_name on table_name;

1
2
3
4
5
6
7
8
9

练习

-- 给name创建索引
create index idx_tb_emp_name on tb_temp(name);
-- gender 唯一，为该字段创建唯一索引
create unique index idx_tb_emp_gender on tb_emp(gender);
-- 为 城市和 manger_id 创建联合索引。
create index idx_tb_emp_wa_mangerid on tb_emp(workaddress,manager_id)
1
2
3
4
5
6

sql性能分析工具

-- sql执行频率

show [session|global] status;

show global status like 'Com_______'; 

-- 查看慢查询日志

show variables like 'slow_query_log';

-- profile 详情，查看sql的时间耗费到哪里了

select @@have_profiling; -- 是否支持
select @@profiling; -- 是否开启
set profiling = 1; -- 开启

show profiles; -- 查看所有sql语句的基本耗时情况

show profile for query query_id; -- 查看指定query_id的sql语句的耗时

show profile cpu for query query_id; -- 查看指定query_id的sql语句对cpu的占用

-- 查看执行计划
explain select 语句


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26