mysql 思维导图模板_ProcessOn思维导图、流程图

默认配置

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:\mysql\mysql-5.6.44-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:\mysql\mysql-5.6.44-winx64\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

账户相关

create user 'gcs'@'%' identified by '123456';
# 创建账户并设置密码，%表示所有机器都可以连接

mysql> grant all on *.* to 'gcs'@'%';
# 全部权限
grant 权限类型 on dbname.* for 'user'@'ip'
grant all on *.* to 'eva'@'%';
grant select
grant select, insert
flush privileges; # 刷新使授权立即生效

flush privileges;
# 权限立即生效

show grants for 'gcs'@'%';
# 查看用户数据库权限
+---------------------------------+
| Grants for gcs@% |
+---------------------------------+
| GRANT USAGE ON *.* TO `gcs`@`%` |
+---------------------------------+

mysql> update user set Grant_priv = 'Y', Super_priv = 'Y' where user = 'root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 2 Changed: 1 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

https://dev.mysql.com/doc/refman/8.0/en/grant.html

https://blog.csdn.net/weixin_43180441/article/details/99685405?ops_request_misc=&request_id=&biz_id=102&utm_term=mysql%3E%20create%20database%20day40;%20&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduweb~default-1-99685405.142^v9^pc_search_result_control_group,157^v4^control&spm=1018.2226.3001.4187

存储引擎
# mysql5.6默认存储引擎Innodb

# Innodb存储引擎（mysql5.6之后的默认存储引擎）
# 数据和索引存储在一起 2个文件
    # 数据索引/表结构
    # 数据持久化
    # 支持事物：为了保证数据的完整性，将多个操作变成原子性操作(原子性：不可拆分的)
    # 行级锁：修改的行少的时候使用                         -> 修改数据频繁的操作
    # 表级锁：批量修改多行的时候使用                       -> 对于大量数据的同时修改
    # 支持外键：约束两张表中的关联字段不能随意的添加、删除    -> 能够降低数据增删改的出错率

# 面试题
    # 你了解mysql的存储引擎么？
    # 你的项目用了什么存储引擎，为什么？
        # Innodb
        # 多个用户操作的过程中对同一张表的数据同时做修改
        # Innodb支持行级锁，所以我们使用了这个引擎
        # 为了适应程序未来的扩展性，扩展新功能的时候可能会用到...,涉及到要维护数据的完整性
        # 项目中有一两张xx表，之间的外间关系是什么，一张表的修改或者删除比较频繁，怕出错所以做了外键约束

补充：
    # select database() 查看当前库
    # show engines; # 查看当前版本支持的存储引擎
    # show variables like '%engine%'
    # creat table tbname(fdname type) engine = Innodb; 创建表，并指定存储引擎

sql慢查询优化

# 慢查询优化：
    # 首先从sql的角度优化
        # 把每一句话单独执行，找到效率低的表，优化这句sql
        # 了解业务场景，适当的创建索引，帮助查询
        # 尽量用连表代替子查询
        # 确认命中索引的情况
    # 考虑修改表结构
        # 拆表
        # 把固定的字段往前调整
    # 使用执行计划，观察sql的type通过以上调整是否提高
# mysql的慢日志
    # 在mysql的配置中开启并设置一下
    # 在超过设定时间之后，这条sql总是会被记录下来
    # 这个时候我们可以对这些被记录的sql进行定期优化

如何正确使用mysql数据库

# 正确的使用mysql数据库
    # 从库的角度
        # 搭建集群
        # 读写分离
        # 分库
    # 从表的角度
        # 合理安排哦表于表之间的关系：该拆的拆，该合的合
        # 把固定长度的字段放在前面
        # 计量使用char而不是varchar
    # 从操作数据角度
        # 尽量在where字段就约束数值到一个比较小的范围: 分页
        # where a between value1 and value2 （必须建立索引才能快，否则还是全表查）
        # 尽量使用连表查询代替子查询
        # 删除数据和修改数据的时候尽量使用主键
        # 合理的创建和使用索引：
            # 创建索引
                # 1.选择区分度比较大的列
                # 2.尽量选择短的字段创建索引
                # 3.不要创建没必要的索引，及时删除不用的索引(索引多了b+树的索引位置会多次重新计算，拖慢写入的数据)
            # 使用索引
                # 1.查询的字段不是索引字段
                # 2.在条件中使用范围，结果的范围越大速度越慢，范围越小就快
                # 3.like 'a%' 命中索引， like'%a' 不命中索引
                # 4.条件列不能参与计算、不能使用函数
                # 5.and/or
                    # and条件相连，有一列有索引都会命中
                    # or条件相连，所有列都有索引才能命中
                # 6.联合索引（重要）
                    # create index mix_ind on 表(id,name,email)
                    # 遵循最左前缀原则，且从出现范围开始索引失效
                    # select * form tbname wehre id = '123'; 命中索引
                    # select * form tbname wehre id > '123'; 不命中索引，出现范围就不会命中
                    # select * from tbname where id = '123' and name = 'alex'; 命中索引
                    # select * from tbname where id > '123' and name = 'alex'; 不命中索引，出现范围就不会命中
                    # select * from tbname where id = '123' and name = 'alex' and email = 'alex@oldboy'; 命中索引
                    # select * from tbname where email = 'alex@oldboy'; 不命中索引，因为条件中没有id
                    # select * from tbname where name = 'alex' and email = 'alex@oldboy'; 不命中索引，因为条件中没有id

                # 7.条件中的数据类型和实际字段的类型必须一致
                # 8.select字段中应该包含order by的字段
                    # select age from tbname order by age; 快
                    # select name from tbname order by age; 慢,因为select 是在order by 之前查询到的，索引查的快

# 覆盖索引：查询过程中不需要回表
# select id from tbname where id > 10000;
# select max(id) from tbname where id > 10000; # 使用函数
#
# 索引合并：分别创建的两个索引某次查询中临时合并成一条索引: a = 1 or b = 2，将a,b都设置成索引列，在使用or的时候就是索引合并
# 执行计划： explain select 语句，能够查看sql语句有没有按照预期执行，可以查看索引的使用情况，type等级

mysql数据库的备份与恢复

备份和恢复表

# 表和数据的备份
    # 备份数据在终端命令行直接执行
    # mysqldump -uroot -p123456 -h172.16.238.4 homework > /home/zew/tem.sql

    # 恢复数据，在mysql中执行命令
    # 切换到一个要备份的数据库中
    # sourse /home/zew/tem.sql

备份和恢复库

# 备份库
    # 备份
    # mysqldump -uroot -p123456 --databases homework > /home/zew/tem2.sql
    # 恢复
    # source /home/zew/tem2.sql

聚集索引与非聚集索引解析
# 查询时尽量使用主键作为条件

事物

## 多线程或并发情况下，添加锁
# begin; # 开启事务
# select * from emp where id = 1 for update; # 查询id值，for update添加行锁；
# update emp set salary=10000 where id = 1; # 完成更新
# commit; # 提交事务

字符集

mysql> show variables like '%character%';

查看默认配置

select @@basedir as basePath from dual;
# 查看mysql的安装目录
# /usr/

select @@datadir as basePath from dual;
# 查看mysql的数据目录
# /var/lib/mysql/

select@@port as port from dual;
# 查看端口

mysql服务

service mysql status
# 查看服务状态
# Active: active (running)
# Active: inactive (dead)

service mysql start
# 启动服务

service mysql stop
# 停止服务

# service mysql restart
# 重启服务

建库建表相关

库

mysql> show databases;
# 查看所有库信息

mysql> use dbname
# 切换库

mysql> create database dbname;
# 创建库

mysql> show tables;
# 查看库下的所有表

mysql> drop database day39;
# 删除库

表

mysql> desc t1;
# 部分属性
mysql> show create table t1;
# 查看建表时的全部属性

修改表结构

# 修改表名
alter table tbname rename 新名字;

# 添加字段
alter table tbname add 字段名类型(长度) 约束 after 某字段

# 删除某个字段
alter table tbname drop 字段名;

# 修改某个字段的类型、约束
alter table tbname modify 字段名类型(长度) 约束

# 修改某个字段的名字、类型、约束
alter table tbname change 旧字段名新名字类型(长度) 约束

单表的增删改查

# 增
insert into tbname (field1, field2, ...) values(field1, field2,...);
insert into tbname values(field1, field2...);
insert into tbname select * from tbname2 where 条件;

# 删
delete from tbname where 条件;
# 清空表
delete from tbname; # 不会清空主键的自增值
truncate table tbname; # 会清空表和自增字段的偏移量

# 改
update tbname set field = value where 条件;
update tbname set field1 = value1, field2 = value2 where 条件; # 修改多个值

# 查
# as 别名
# distinct 去重
# concat(field1, field2) as n # 返回拼接后的字符串 field1+field2
# concat_ws(分隔符, field1, field2) # 返回指定分隔符后的字段：1|alex|3714|85
# select 支持四则运算，例如根据月薪求年薪
# 判断逻辑 case when ... then ... end 相当于if判断语句
# 聚合函数 min max avg count sum
# 比较运算符 > < >= <= != <>
# 模糊匹配 like %通配符 _ 占位符
# 逻辑运算优先级： not > and > or
# group by 分组
# having 分组后的过滤，having中可以使用聚合函数,where中不可以
# ourder by 排序, asc 升序， desc 降序，默认asc
# limit 取前几项，limit m, n # 从m+1项开始取，取n项； limit m # 从0开始，取m项

多表查询

定义：通过关联关系创建一张大表，保存两张表的笛卡尔积，再根据条件筛选数据

# 内连接
inner join tbname on 关联条件

# 外连接
# 左外连接
left join tbname on 关联条件; # 左外连接以左表为主，右表没有的赋值null
# 右外连接
right join tbname on 关联条件; # 右外连接以右表为主，左表没有的赋值null
# 全外连接
# oracle: full outer join tbname on 关联条件
# mysql 不支持这种方式, 利用union合并左外和右外
# select * from department d left join employee e on d.id = e.dep_id
# union
# select * from department d right join employee e on d.id = e.dep_id;

子查询

其中一个查询结果作为另一个select查询的条件，
或者其中一个查询结果作为另一个select查询关联的外表

exisits关键字
# 返回bool值True/False,
# 返回True时,外层语句执行，返回False时，外层语句不执行

mysql> select * from class where exists (select cid where caption = '一年二班');
    +-----+--------------+
    | cid | caption      |
    +-----+--------------+
    |   3 | 一年二班     |
    +-----+--------------+
    1 row in set (0.00 sec)

    mysql> select * from class where exists (select cid where caption = '一年一班');
    Empty set (0.00 sec)

sql的执行顺序

# from 找表
# where 条件, 筛选符合条件的行
# group by 分组
# having 分组之后的过滤
# select 字段
# order by 排序
# limit m, n 取从m+1开始的前n条记录

数据类型(常用)

整型

int 不约束长度，最多表示10位

tinyint unsigned
# 0~255, 表示年龄使用，现在不常用，直接int即可

unsigned 无符号约束，例如年龄不能为负数

浮点型

float(5,3)
# 表示，总共5位，小数位3位四舍五入，整数位2位

double(5,3)
# 表示，总共5位，小数位3位四舍五入，整数位2位
# 精度比float高，一般情况下float足够了

时间类型

date
# 年月日

time
# 时分秒，常用，比如计时类软件

datetime
# 年月日时分秒，常用

year
# 年，不常用

timestamp
# 时间戳，截止到2038年，已经不用了

# mysql> create table t5(
#     -> id int,
#     -> dt datetime NOT NULL                        # 不能为空
#                   DEFAULT CURRENT_TIMESTAMP        # 默认是当前时间
#                   ON UPDATE CURRENT_TIMESTAMP);    # 在更新的时候使用当前时间更新字段
# 给字段设置默认时间

字符串转换成时间类型
str_to_date()
# select str_to_date(sysdate(), '%Y-%m-%d %H:%i:%S') as time from dual;

+---------------------+
| time |
+---------------------+
| 2022-04-28 16:08:53 |
+---------------------+
1 row in set (0.00 sec)

将时间类型字段按照format格式格式化
date_format(date, format)
# select date_format(sysdate(), '%Y-%m-%d') as date from dual;

+------------+
| date |
+------------+
| 2022-04-28 |
+------------+
1 row in set (0.00 sec)

返回两个日期之间相隔多少天
DATEDIFF(datepart,startdate,enddate)
# select datediff(sysdate(), '1990-11-08')/365 as time from dual;

+---------+
| time |
+---------+
| 31.4904 |
+---------+
1 row in set (0.00 sec)

字符型

char(n)
# 最多只能表示255个字符
# 定长存储，节省时间，浪费空间
# 不足的长度，右侧补空白
# 需要频繁更改的固定长度字段，建议使用char

# 适合使用char
    # 身份证号
    # 手机号
    # qq号
    # username 12 - 18
    # password 32
    # 银行卡号

varchar(n)
# 最多表示65535个字符
# 变长存储，节省空间，存取速度慢
# 对于不经常查看或更改的字段，使用varchar

# 适合使用varchar
    # 评论
    # 朋友圈
    # 微博

单选enum和多选set

field enum(opt1,opt2)
# 增改field值时，只能选择固定选项

field set(opt1,opt2,opt3...)
# 插入值时，选项之间不能有空格
# insert into t9 values(1, 'tbjx', '抽烟,喝酒,汤头');

约束 constraints

int unsigned
# 整型无符号约束

not null
# 非空约束

unique
# 唯一约束

unique(field1,field2)
# 联合唯一约束

default
# 默认值约束

male enum('0','1') not null default '1'

auto_increment
# 自增约束
# 至少是unique约束的列才能加自增约束，
# 主要用于主键自增
# 在自增约束的列，插入值时，可传null值，入库时会自增值

primary key
# 主键约束，非空+唯一
# 一般搭配自增约束
# 一张表只能有一个主键，默认第一个非空+唯一约束的字段为主键
# 联合主键约束
# 在建表语句最后 primary key(field1, field2..),一般不用

foreign key(本表字段) references 外表(外表的字段)
# 外键约束

on update cascade
# 外键级联更新，即：外表更新时，关联表同步更新

on delete cascade
# 外键级联删除，即，外表删除是，关联表同步删除(不安全，不用)

foreign key(class_id) references class(id) on update cascade on delete cascade

表与表之间的关系

一对多/多对一
# foreign key

多对多（建两个外键）
# 找出关联关系，建立第三张表，
# 第三张存储那两张表的外键
# 那两张表分别关联第三张表存储的列

create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);

一对一
# 一张表唯一约束，另一张表建立唯一约束加外键约束
# 客户学生
# unique foreign key + unique

如何确认表关系

分析步骤：
#1、先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）

#2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）

#3、总结：
#多对一：
如果只有步骤1成立，则是左表多对一右表
如果只有步骤2成立，则是右表多对一左表

#多对多
如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

#一对一:
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可

索引

B+树

# b+树
    # b是balance 平衡
        # 为了保证每一个数据查找经历的IO次数都相同
    # 只在叶子节点存储数据
        # 为了降低树的高度
    # 叶子节点之间加入双向连接
        # 为了查找范围的时候比较快

聚簇索引和非聚簇索引

# 聚集索引(聚簇索引)
# 全表的数据都存储到叶子节点上 -- Innodb存储引擎中的主键
# 非聚集索引(非聚簇索引)/辅助索引
# 叶子节点不存放具体的整行数据，而是存储主键的值

创建和删除索引

# 索引的创建和删除
    # create index ind_name on 表名(字段名);
    # create index ind_name on 表名(字段名1, 字段名2); # 联合索引
    # drop index 索引名 on 表名; # 删除索引

合理的创建和使用索引

# 数据库使用的时候有什么注意事项
    # 从搭建数据库的角度上来描述问题
    # 建表的角度上
        # 1.合理安排表关系
        # 2.尽量把固定长度的字段放在前面
        # 3.尽量使用char代替varchar
        # 4.分表: 水平分,垂直分
    # 使用sql语句的时候
        # 1.尽量用where来约束数据范围到一个比较小的程度,比如说分页的时候
        # 2.尽量使用连表查询而不是子查询
        # 3.删除数据或者修改数据的时候尽量要用主键作为条件
        # 4.合理的创建和使用索引
            # 1.查询的条件字段不是索引字段
                # 对哪一个字段创建了索引,就用这个字段做条件查询
            # 2.在创建索引的时候应该对区分度比较大的列进行创建
                # 1/10以下的重复率比较适合创建索引
            # 3.范围
                # 范围越大越慢
                # 范围越小越快
                # like 'a%'  快
                # like '%a'  慢
            # 4.条件列参与计算/使用函数
            # 5.and和or
                # id name
                # select * from s1 where id = 1800000 and name = 'eva';
                # select count(*) from s1 where id = 1800000 or name = 'eva';
                # 多个条件的组合,如果使用and连接
                    # 其中一列含有索引,都可以加快查找速度
                # 如果使用or连接
                    # 必须所有的列都含有索引,才能加快查找速度
            # 6.联合索引 : 最左前缀原则(必须带着最左边的列做条件,从出现范围开始整条索引失效)
                # (id,name,email)
                # select * from s1 where id = 1800000 and name = 'eva' and email = 'eva1800000@oldboy';
                # select * from s1 where id = 1800000 and name = 'eva';
                # select * from s1 where id = 1800000 and email = 'eva1800000@oldboy';
                # select * from s1 where id = 1800000;
                # select * from s1 where name = 'eva' and email = 'eva1800000@oldboy';
                # (email,id,name)
                # select * from s1 where id >10000 and email = 'eva1800000@oldboy';
            # 7.条件中写出来的数据类型必须和定义的数据类型一致
                # select * from biao where name = 666   # 不一致
            # 8.select的字段应该包含order by的字段
                # select name,age from 表 order by age;  # 比较好
                # select name from 表 order by age;  # 比较差

pymysql模块

创建连接

conn = pymysql.connect(user='root', # mysql 用户名
        password='123456', # mysql 密码
        host='172.16.238.4', # mysql 所在的服务器ip
        database='homework',) # mysql使用的库
cur = conn.cursor() # 获取游标，默认返回元组数据, 参数cursor=pymysql.cursors.DictCursor，返回字典

执行sql语句

cur.execute(sql语句) # 执行sql

查询

ret = cur.fetchone() # 获取一条结果
ret = cur.fetchmany(10) # 获取n条结果
ret = cur.fetchall() # 获取全部结果
# 查询时，通过 try 捕获异常

# cur = conn.cursor(cursor=pymysql.cursors.DictCursor)   # 查询返回字典
# cur = conn.cursor()   # course 游标 # 默认返回元组
# try:
#     cur.execute('select * from students;')
#
#     ret = cur.fetchone()        # 获取一条结果
#     print(ret)
#     ret2 = cur.fetchmany(10)    # 获取多条结果
#     print(ret2)
#     ret3 = cur.fetchall()       # 获取全部结果
#     print(ret3)
# except pymysql.err.ProgrammingError as e: # 做异常处理时，控制台打印e,或者打到log里
#     print(e)
#
# conn.close() # 关闭连接
# cur.close()  # 关闭游标

增删改

cur.execute(insert语句)
cur.execute(update语句)
cur.execute(delete语句)
cur.commit() # 提交事物

# 执行增删改操作，需提交事物，
# 如果出现异常，需要在异常处理中回滚事物
cur.commit() 提交事物
cur.roolback() # 回滚事物

## 增加，删除，修改
# conn = pymysql.connect(user='root',  # The first four arguments is based on DB-API 2.0 recommendation.
#         password='123456',
#         host='172.16.238.4',
#         database='homework',)
#
# cur = conn.cursor()
# try:
#     # cur.execute("insert into student values(null, '男', 3, '大壮')")  # 增
#     # cur.execute("update student set gender = '女' where sid = 18")   # 删
#     # cur.execute("delete from student where sid in(20, 21)")          # 改
#     conn.commit() # 提交
# except Exception as e: # 异常处理要打印日志或者写log
#     print(e)
#     conn.rollback() # 如果出问题，回滚事物
#
# cur.close()
# conn.close()

cur.rowcount # 获取查询结果的行数
cur.rownumber # 获取当前结果的行号

利用rowcount 搭配fetchone循环遍历查询结果，
获取使用 yeld/yeld from 得到一个生成器函数

# conn = pymysql.connect(user='root',  # The first four arguments is based on DB-API 2.0 recommendation.
#         password='123456',
#         host='172.16.238.4',
#         database='homework',)
#
#
# cur = conn.cursor()   # course 游标 # 默认返回元组
# try:
#     cur.execute('select * from student;')
#     print(cur.rowcount) # 获取查出使用多少行，便于使用fetchone取所有结果
#     for i in range(cur.rowcount):
#         ret = cur.fetchone()
#         print(cur.rownumber, ret)
# except pymysql.err.ProgrammingError as e: # 做异常处理时，控制台打印e,或者打到log里
#     print(e)
#
# conn.close() # 关闭连接
# cur.close()  # 关闭游标

利用execute()传元组参数，
避免sql拼接引起的sql注入

sql = "select * from userinfo where user = %s and password = %s"
cur.execute(sql, (user, pwd)) # 参数必须是字符串类型

user = input('username:')
pwd = input('password:')
conn = pymysql.connect(user='root',
        password='123456',
        host='172.16.238.4',
        database='day42',)

cur = conn.cursor()
sql = "select * from userinfo where user = %s and password = %s"
cur.execute(sql, (user, pwd)) # sql用execute参数拼接，避免sql注入
print(cur.fetchone())

cur.close()
conn.close()