欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

mysql必知必会

程序员文章站 2024-01-12 15:02:40
...

什么是数据库

数据库(Database)就是按照数据结构来组织，存储和管理数据的仓库 专业的数据库是专门对数据进行创建，访问，管理，搜索等操作的软件，比起我们自己用文件读写的方 式对象数据进行管理更加的方便，快速，安全

作用

对数据进行持久化的保存

方便数据的存储和查询，速度快，安全，方便

可以处理并发访问

更加安全的权限管理访问机制

常见的数据库

数据库分两大类，一类是 关系型数据库。另一类叫做 非关系型数据库。
关系型数据库： MySQL，Oracle，PostgreSQL，SQLserver……

非关系型数据库：Redis内存数据库，MongoDB文档数据库……

mysql

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Gm00p5Hk-1597985233477)(C:\Users\chenyibin2\AppData\Roaming\Typora\typora-user-images\image-20200813103155337.png)]

#查看当前mysql中所有的库
show databases;
#选择需要操作的库，打开库 
use mysql;
#查看当前库中的所有数据
show tables;
#创建用户表
create table user(    
    name varchar(20),    
    age int,    
    sex char(1) )engine=innodb default charset=utf8;

数据表操作

  • 数据库管理系统中, 可以有很多库, 每个数据库中可以包括多张数据表
    查看表: show tables;

  • 创建表: create table 表名(字段名1 类型,字段名2 类型)engine=innodb default charset=utf8;

  • 创建表: 如果表不存在,则创建, 如果存在就不执行这条命令

    create table if not exists 表名(字段1 类型,字段2 类型);

  • 删除表: drop table 表名;

  • 查看建表语句：show create table users;

数据操作 增删改查

  • 插入
    insert into 表名(字段1,字段2,字段3) values(值1,值2,值3);
    insert into 表名(字段1,字段2,字段3) values(a值1,a值2,a值3),(b值1,b值2,b值3);
  • 查询
    select * from 表名; select 字段1,字段2,字段3 from 表名; select * from 表名 where 字段=某个值;
  • 修改
    update 表名 set 字段=某个值 where 条件;
    update 表名 set 字段1=值1,字段2=值2 where 条件; update 表名 set 字段=字段+值 where 条件;
  • 删除
    delete from 表名 where 字段=某个值;

数据类型

字符串数据类型

mysql必知必会

数值类型

mysql必知必会

有符号或无符号

所有数值数据类型（除BIT和BOOLEAN外）都可以有符号或无符号
有符号数值列可以存储正或负的数值 无符号数值列只能存储正数。

默认情况为有符号，但如果你知道自己不需要存储负值，可以使用UNSIGNED关键字

日期和时间类型

mysql必知必会

表的字段约束

  • unsigned 无符号(给数值类型使用，表示为正数，不写可以表示正负数都可以)
  • 字段类型后面加括号限制宽度 char(5). varchar(7) 在字符类型后面加限制 表示 字符串的长度
  • int(4) 没有意义，默认无符号的int为int(11)，有符号的int(10)
  • int(4) unsigned zerofill只有当给int类型设置有前导零时，设置int的宽度才有意义。
  • not null 不能为空,在操作数据库时如果输入该字段的数据为NULL ，就会报错
  • default 设置默认值 primary key 主键不能为空,且唯一.一般和自动递增一起配合使用。
  • auto_increment 定义列为自增属性，一般用于主键，数值会自动加1
  • unique 唯一索引(数据不能重复:用户名)可以增加查询速度,但是会降低插入和更新速度

MySQL的运算符

算术运算符： +、 -、 *、 /、 %

比较运算符： =、 >、 <、 >=、 <=、!=

数据库特有的比较： in、not in、is null、is not null、like、between、and

逻辑运算符： and、or、not

like: 支持特殊符号%和_ ;

Mysql数据库表引擎与字符集

1.服务器处理客户端请求

​ 其实不论客户端进程和服务器进程是采用哪种方式进行通信，后实现的效果都是：客户端进程向服务器进程发送 一段文本（MySQL语句），服务器进程处理后再向客户端进程发送一段文本（处理结果）。那服务器进程对客户 端进程发送的请求做了什么处理，才能产生后的处理结果呢？客户端可以向服务器发送增删改查各类请求，我们 这里以比较复杂的查询请求为例来画个图展示一下大致的过程：

mysql必知必会
​

​ 虽然查询缓存有时可以提升系统性能，但也不得不因维护这块缓存而造成一些开销，比如每次都要去查询缓 存中检索，查询请求处理完需要更新查询缓存，维护该查询缓存对应的内存区域。从MySQL 5.7.20开始，不 推荐使用查询缓存，并在MySQL 8.0中删除。

2.存储引擎

​ MySQL 服务器把数据的存储和提取操作都封装到了一个叫 存储引擎 的模块里。我们知道 表 是由一行一行的记录 组成的，但这只是一个逻辑上的概念，物理上如何表示记录，怎么从表中读取数据，怎么把数据写入具体的物理存 储器上，这都是 存储引擎 负责的事情。为了实现不同的功能， MySQL 提供了各式各样的 存储引擎 ，不同 存储引 擎 管理的表具体的存储结构可能不同，采用的存取算法也可能不同。
​ 存储引擎以前叫做 表处理器 ，它的功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操 作。
​ 为了管理方便，人们把 连接管理 、 查询缓存 、 语法解析 、 查询优化 这些并不涉及真实数据存储的功能划分为 MySQL server 的功能，把真实存取数据的功能划分为 存储引擎 的功能。各种不同的存储引擎向上边的 MySQL server 层提供统一的调用接口（也就是存储引擎API），包含了几十个底层函数，像"读取索引第一条内容"、“读取 索引下一条内容”、"插入记录"等等。 所以在 MySQL server 完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返 回给客户端就好了。
MySQL 支持非常多种存储引擎：

存储引擎 描述
ARCHIVE 用于数据存档（行被插入后不能再修改）
BLACKHOLE 丢弃写操作，读操作会返回空内容
CSV 在存储数据时，以逗号分隔各个数据项
FEDERATED 用来访问远程表
InnoDB 具备外键支持功能的事务存储引擎
MEMORY 置于内存的表
MERGE 置于内存的表
MyISAM 主要的非事务处理存储引擎
NDB MySQL 集群专用存储引擎

3,MyISAM和InnoDB表引擎的区别

  1. 事务支持
    MyISAM不支持事务，而InnoDB支持。
    事物：访问并更新数据库中数据的执行单元。事物操作中，要么都执行要么都不执行

  2. 存储结构
    MyISAM：每个MyISAM在磁盘上存储成三个文件。
    .frm文件存储表结构。 .MYD文件存储数据。
    .MYI文件存储索引。 InnoDB：主要分为两种文件进行存储
    .frm 存储表结构 .ibd 存储数据和索引 （也可能是多个.ibd文件，或者是独立的表空间文件）

  3. 表锁差异
    MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如 果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。 InnoDB：支持事务和行级锁，是 innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有 效的，非主键的WHERE都会锁全表的。

  4. 表主键
    MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。 InnoDB：如果没有设定主键或者非空唯 一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。 InnoDB的主键范围更大，大是MyISAM的2倍。

  5. 表的具体行数
    MyISAM：保存有表的总行数，如果select count() from table;会直接取出出该值。 InnoDB：没有保存表的总行数 (只能遍历)，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后， myisam和innodb处理的方式都一样。

  6. CURD操作
    MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。 InnoDB：如果你的数据执行大量的INSERT或 UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table 时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，好使用 truncate table这个命令。

  7. 外键
    MyISAM：不支持 InnoDB：支持

  8. 查询效率
    MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。 推荐考虑使用InnoDB来替代MyISAM引擎，原因是InnoDB自身很多良好的特点，比如事务支持、存储 过程、视 图、行级锁定等等，在并发很多的情况下，相信InnoDB的表现肯定要比MyISAM强很多。
    另外，任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类型，才能大的发挥MySQL的性能优 势。如果不是很复杂的Web应用，非关键应用，还是可以继续考虑MyISAM的，这个具体情况可以自己斟酌。

9）MyISAM和InnoDB两者的应用场景：
MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那 么MyISAM是更好的选择。 InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要 执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。现在默认使用 InnoDB。

4.MySQL中的utf8和utf8mb4

utf8 字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示 了。而在 MySQL 中字符集表示一个字符所用大字节长度在某些方面会影响系统的存储和性能，所以设计 MySQL 的大叔偷偷的定义了两个概念：

  • utf8mb3 ：阉割过的 utf8 字符集，只使用1～3个字节表示字符。

  • utf8mb4 ：正宗的 utf8 字符集，使用1～4个字节表示字符。
    有一点需要大家十分的注意，在 MySQL 中 utf8 是 utf8mb3 的别名，所以之后在 MySQL 中提到 utf8 就意味着使用 1~3个字节来表示一个字符，如果大家有使用4字节编码一个字符的情况，比如存储一些emoji表情啥的，那请使 用 utf8mb4 。

    字符集的查看

    MySQL 支持好多好多种字符集，查看当前 MySQL 中支持的字符集可以用下边这个语句：

    show charset;

Mysql数据库导入导出和授权

数据导出

1.数据库数据导出

导出一个库中所有数据，会形成一个建表和添加语句组成的sql文件 之后可以用这个sql文件到别的库，或着本机中创建或回复这些数据

不要进入mysql，然后输入以下命令 导出某个库中的数据

mysqldump -u root -p tlxy > ~/Desktop/code/tlxy.sql

2.将数据库中的表导出

不要进入mysql，然后输入以下命令 导出某个库中指定的表的数据

mysqldump -u root -p tlxy tts >  ~/Desktop/code/tlxy-tts.sql

数据导入

把导出的sql文件数据导入到mysql数据库中

​ 在新的数据库中 导入备份的数据，导入导出的sql文件 mysql -u root -p ops < ./tlxy.sql

把导出的表sql 导入数据库 mysql -u root -p ops < ./tlxy-tts.sql

权限管理

mysql中的root用户是数据库中权限最高的用户，千万不要用在项目中。 可以给不同的用户，或者项目，创建不同的mysql用户，并适当的授权，完成数据库的相关操作
这样就一定程度上保证了数据库的安全。
创建用户的语法格式：
grant 授权的操作 on 授权的库.授权的表 to 账户@登录地址 identified by ‘密码’;
示例：

#在mysql中 创建一个 zhangsan 用户，授权可以对tlxy这个库中的所有表 进行 添加和查询 的权限 

grant select,insert on tlxy.* to aaa@qq.com'%' identified by '123456';

#用户 lisi。密码 123456 可以对tlxy库中的所有表有 所有操作权限 
grant all on tlxy.* to aaa@qq.com'%' identified by '123456';

#删除用户 
drop user 'lisi'@'%';

sql中 创建一个 zhangsan 用户，授权可以对tlxy这个库中的所有表 进行 添加和查询 的权限

grant select,insert on tlxy.* to aaa@qq.com’%’ identified by ‘123456’;

#用户 lisi。密码 123456 可以对tlxy库中的所有表有 所有操作权限
grant all on tlxy.* to aaa@qq.com’%’ identified by ‘123456’;

#删除用户
drop user ‘lisi’@’%’;

表关系

​ 表与表之间的关系

外键：

​ 在一个表中，定义一个字段，这个字段中存储的数据是另外一张表中的主键
​ 就是在一个表中的字段，代表着这个数据属于谁

了解：
    外键实现的方式，有两种：物理外键、逻辑外键
    物理外键：
        就是在创建表时，就指定这个表中的字段是一个外键，并且强关联某个表中的某个字段
        需要在定义字段时，使用sql语句来实现
    逻辑外键：
        就是在表中创建一个普通的字段，没有强关联关系，需要通过程序逻辑来实现

一对一

​ 就是在一个表中的数据，对应着另外一张表中的一个数据，只能有一个

一对多

​ 在一个表中的一条数据对应着另外一个表中的多条数据
​ 在一个表中的多条数据，对应着另外一张表中一个数据

多对多

​ 举例：例如一本书，有多个标签，同时每一个标签下又对应多本书

表联结

​ 就是一种查询的机制，用来在一个select语句中关联多个表进行查询，称为联结

内部联结

where， inner join(join)

自联结

自联结:当前这个表与自己这个表 做联结（join）

外部联结

left join

是以 left join 左侧表为基准，去关联右侧的表进行联结，如果有未关联的数据，那么结果为null

right join

是以 right join 右侧表为基准，去关联左侧的表进行联结，如果有未关联的数据，那么结果为null

事务

​ 是由一系列对系统中数据进行访问与更新的操作所组成的一个程序执行逻辑单元

mysql必知必会

事务四大特征(ACID)

  • 原子性：事务中的操作要么全部成功，要么全部失败
  • 一致性：一致性是指事务执行结束后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态
  • 隔离性：每个读写事务的对象对其他事务的操作对象能相互分离，即该事务提交前对其他事务都不可见
  • 持久性：事务一旦提交，它对数据库的改变应该是永久的

隔离性(isolation)

  1. 事物A和事物B之间具有一定的隔离性
  2. 隔离性有隔离级别(4个)
    • 读未提交：read uncommitted
    • 读已提交：read committed
    • 可重复读：repeatable read
    • 串行化：serializable

1、 read uncommitted

- 事物A和事物B，事物A未提交的数据，事物B可以读取到
- 这里读取到的数据叫做“脏数据”
- 这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别
123

2、read committed

- 事物A和事物B，事物A提交的数据，事物B才能读取到
- 这种隔离级别高于读未提交
- 换句话说，对方事物提交之后的数据，我当前事物才能读取到
- 这种级别可以避免“脏数据”
- 这种隔离级别会导致“不可重复读取”
- Oracle默认隔离级别
123456

3、repeatable read

- 事务A和事务B，事务A提交之后的数据，事务B读取不到
- 事务B是可重复读取数据
- 这种隔离级别高于读已提交
- 换句话说，对方提交之后的数据，我还是读取不到
- 这种隔离级别可以避免“不可重复读取”，达到可重复读取
- 比如1点和2点读到数据是同一个
- MySQL默认级别
- 虽然可以达到可重复读取，但是会导致“幻像读”
12345678

4、serializable

- 事务A和事务B，事务A在操作数据库时，事务B只能排队等待
- 这种隔离级别很少使用，吞吐量太低，用户体验差
- 这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发

mysql必知必会

事务并发问题

（1）脏读

有俩事务T1,T2。如果T1读了一条数据，这条数据是T2更新的但是还没提交，突然T2觉得不合适进行事务回滚了，也就是不提交了。此时T1读的数据就是无效的数据。

（2）不可重复读

有俩事务T1,T2。如果T1读了一条数据，之后T2更新了这条数据，T1再次读取就发现值变了。

（3）幻读

有俩事务T1,T2。如果T1读了一条数据，之后T2插入了一些新的数据，T1再次读取就会多出现一些数据。

索引

实现原理

mysql必知必会

mysql必知必会

mysql必知必会

mysql必知必会

mysql必知必会

分析慢查询日志

https://blog.csdn.net/qq_35571554/article/details/82800463

​ 直接分析mysql慢查询日志 ,利用explain关键字可以模拟优化器执行SQL查询语句，来分析sql慢查询语句

例如：执行EXPLAIN SELECT * FROM res_user ORDER BYmodifiedtime LIMIT 0,1000

​ 得到如下结果： 显示结果分析：

​ table | type | possible_keys | key |key_len | ref | rows | Extra EXPLAIN列的解释：

​ table 显示这一行的数据是关于哪张表的

​ type 这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

​ rows 显示需要扫描行数

​ key 使用的索引

索引没起作用的情况

  1. 使用LIKE关键字的查询语句

​ 在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置索引才会起作用。

  1. 使用多列索引的查询语句

​ MySQL可以为多个字段创建索引。一个索引最多可以包括16个字段。对于多列索引，只有查询条件使用了这些字段中的第一个字段时，索引才会被使用。

相关标签： mysql java

上一篇： 实现微信墙功能得原理

下一篇： PHP下include包含文件有关问题

推荐阅读