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()