MySQL 数据库

典型代表 ?MySQL ? MariaDB ? PostgreSQL(pgdb) ?Oracle ? SQL Server ? Db2
信创 ? ? ? ?国产数据库 ?阿里云 RDB ? ? 华为高斯 ? ? 阿里 Oceanbase ? ?腾讯 TDBA ? ? 人大金仓 ? ?达梦 ? ?greatSQL

关系型数据库 SQL ? 操作命令：

sql语句 ? ? ?存储结构：二维表格 ? ? ?存储的数据：结构化数据 ? ? ?使用场景：存储业务数据和账户信息
对象： ?库 -> 表 -> 二维表格形式的结构化数据 ? ? 列（字段）：用来描述对象的一个属性
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 行（记录）：用来描述一个对象的信息

非关系型数据库 NoSQL ? 存储结构：

不同类型的NoSQL使用不同的存储结构，比如键值对、文档、索引、时间序列等
缓存型 ?Redis ? Memcached
文档型 ?MongoDB
索引型 ?Elasticsearch
时序型 ?Prometheus ?InfluxDB
列式型 ?HBase

Mysql基础操作
?

select version();   #查询mysql的版本号
select user();      #查询用户
show databases;        #查询所有数据库    
use 库名;   show tables [from 库名];   #查看表
describe 表名;   desc [库名.]表名;    #查看表结构

SQL语句 DDL ?DML ?DQL ?DCL

DDL：用于管理数据库对象（库、表、索引等）

create database 库名;      #新建库

use 库名;       #使用库

create table 表名 (字段1 数据类型, 字段2 数据类型, ....[, primary key(字段)]);
      #创建表

drop table [库名.]表名;    #删除库下面的表
drop database 库名;        #删除库和库下面的所有表

修改表结构

改表名         alter table 旧表名 rename 新表名
增加字段       alter table 表名 add 新字段 数据类型 [约束属性];
修改字段       alter table 表名 change 旧字段 新字段 数据类型 [约束属性];
删除字段       alter table 表名 drop 字段名;

MySQL的六大约束特性：

primary key     主键约束     字段的值不能重复，不能为Null，一个表只能有一个主键
unique key      唯一性约束   字段的值不能重复，但能为Null，一个表可以有多个唯一键
not null        非空约束     字段的值不能为Null
default         默认值约束   字段的值如果没有设置则使用默认值自动填充
auto_increment  自增约束     字段的如果没有设置默认会从 1 开始每次自动递增 1，要求自增字段必须为主键
foreign key     外键约束     保证外键表与主键表相关联的表数据的完整性和一致性

int(N) zerofill   零填充  N为零填充的个数

克隆表

create table 新表 like 旧表;                 #克隆表结构
insert into 新表 (select * from 旧表);       #克隆表数据        可以实现表结构和表数据与旧表都一致

create table 新表 (select * from 旧表);      #表数据和旧表一致，表结构和旧表不一定一致

清空表

delete from 表名;       #一条一条的删除记录，效率较慢；自增长字段仍然会按照清空表前的最大记录继续自增

truncate table 表名;    #直接重建表，清空表效率更快；自增长字段会重新从 1 开始自增

临时表

create temporary table (....);       

#临时表跟普通表一样可以进行增删改查，但是show tables是查不到的；
#临时表只能在当前会话中有效，退出当前会话或在其它会话中，临时表都会失效

外键约束

主键表： alter table 表名 add primary key (主键字段);
外键表： alter table 表名 add foreign key (外键字段) references 主键表名 (主键字段);
         插入新数据时，需要先在主键表插入数据再在外键表插入相关联的数据；删除数据时，需要先在外键表删除数据再在主键表删除相关联的数据
		 
		 alter table 表名 drop foreign key '外键别名';
		 alter table 表名 drop key '键名';

DML：用于管理表数据

insert into 表名 (字段1, 字段2, ....) values (字段1的值, 字段2的值, ....);
 #新建表数据
insert into 表名 values (所有字段的值);


update 表名 set 字段1=值[, 字段2=值, ....]  where 条件表达式;
  #修改，更新表数据
delete from 表名 where 条件表达式;
 #删除表数据

DQL：用于根据条件查询表数据

select * from 表名 [where 条件表达式];
select  字段1,字段2,....  from 表名 [where 条件表达式];

select * from 表名\G      #纵向查看每行记录的字段的值

select * from 表名 limit N;      #查看表的前N行的记录
select * from 表名 limit N,M;    #查看表的前N行之后的连续M行的记录（不包含第N行）

DCL：用于管理用户和权限

用户管理：

create user '用户名'@'源地址'  identified by '密码';
                     源地址可以是localhost/IP/网段/主机名/%
					 
rename user '用户名'@'源地址' to '新用户名'@'源地址';

drop user '用户名'@'源地址';

select user,host,authentication_string from mysql.user;

select user();

set password [for '用户名'@'源地址'] = password('密码');
alter user '用户名'@'源地址' identified by '密码';

如何找回 root 密码？

1）修改MySQL配置文件，在 [mysqld] 配置项下面添加 skip-grant-tables 配置参数
2）重启mysqld服务，使用 mysql 密码直接登录数据库
3）执行命令 update mysql.user set authentication_string=password('密码') where user='root'; 修改 root 用户密码
4）还原MySQL配置，重启mysqld服务，使用 mysql -u用户名 -p密码 [-h mysql地址 -P mysql端口] 命令登录数据库验证

权限管理：

grant 权限1,权限2,....  on 库名.表名 to '用户名'@'源地址' [identified by '密码'];   #5.7或之前版本支持创建新用户和授予权限，8.0版本只能用于授予权限
       all                   *.*

show grant for '用户名'@'源地址';   #查看权限

revoke 权限1,权限2,....  on 库名.表名 from  '用户名'@'源地址';
       all

索引

定义:

索引是一个排序的列表，包含索引字段的值和其对应的行记录的数据所在的物理地址

作用：

加快表的查询速度，还可以对字段排序

副作用：

会额外占用磁盘空间；更新包含索引的表会花费更多的时间，效率会更慢

工作方式：

没有索引的情况下，要根据条件查询某行数据时，需要先扫描全表，再定位某行数据的位置。

有了索引后，会先通过查询条件的字段值找到其索引对应的行记录的数据的物理地址，然后根据物

理地址直接访问相应的行记录的数据，就像根据书目录的页码快速找到所需的内容。

创建索引的依据：

1.表中的记录行数较多时，一般超过300行的表建议要有索引

2.建议在表中的主键字段、外键字段、多表连接使用的公共关键字段、唯一性较好的字段、不经

常更新的字段、经常出现在 where,group by,order by 子语句的字段、短小的字段上面创建索引

3.不建议在唯一性较差的字段、更新太频繁的字段、大文本字段上面创建索引

索引的类型

普通索引   create index 索引名 on 表名 (字段);                          alter table 表名 add index 索引名 (字段);
唯一索引   create unique index 索引名 on 表名 (字段);                   alter table 表名 add unique 索引名 (字段);
主键索引                                                                alter table 表名 add primary key (字段);
组合索引   create index 索引名 on 表名 (字段1, 字段2, ....);            alter table 表名 add index 索引名 (字段1, 字段2, ....);
           create unique index 索引名 on 表名 (字段1, 字段2, ....);     alter table 表名 add unique 索引名 (字段1, 字段2, ....);
		  select .... from 表名 where 字段1=XX and 字段2=XX ... ;   #用 and 做逻辑运算符多字段查询时，要创建组合索引且要满足最左原则
		                                                            #用 or 做逻辑运算符多字段查询时，所有字段都要单独创建单列索引
全文索引   create fulltext index 索引名 on 表名 (字段);                 alter table 表名 add fulltext 索引名 (字段);
        支持模糊查询   select .... from 表名 where match(字段) against('单词');

查看索引

show create table 表名;
show index from 表名;
show keys from 表名;

删除索引

drop index 索引名 on 表名;
alter table 表名 drop index 索引名;
alter table 表名 drop primary key;

问题

遇到 select 语句查询速度慢改怎么办？

1.先使用 explain 分析 select 语句，看 key 字段，确定 select 语句使用使用了索引或索引使用是否正确
2.再根据 select 语句中 where 子语句使用的条件字段创建相应的单列索引或组合索引，组合索引要满足最左原则
?

事务介绍

定义

事务就是一组数据库操作序列（包含一个或者多个SQL操作命令），事务会把所有操作看作是一个不可分割的整体向数据库系统提交或撤消操作，所有操作要么都执行，要么都不执行。

事务的 ACID 特性：原子性、一致性、隔离性、持久性

原子性：

事务管理的基础。把事务中的所有操作看作是一个不可分割的工作单元，要么都执行，要

么都不执行。

一致性：

事务管理的目的。保证事务开始前和事务结束后数据的完整和一致

隔离性：

事务管理的手段。使多个事务并发操作同一个表数据时，每个事务都有各自独立的数据空

间，事务的执行不会受到其它事务的干扰。可通过设置隔离级别解决不同的一致性问题。

持久性：

事务管理的结果。当事务被提交以后，事务中的命令操作修改的结果会被持久保存，且不

会被回滚。

隔离级别：

未提交读 ?read uncommitted ? 允许脏读不可重复读 ?幻读

提交读 ? ?read committed ? ? 不允许脏读，允许不可重复读 ?幻读

可重复度 ?repeatable read ? ?不允许脏读不可重复读，有条件的允许幻读（InnoDB存储引擎可以不允许）

串行读 ? ?Serializable ? ? ? 都不允许，相当于表级锁定，但是会影响数据库的读写效率和性能

设置隔离级别：

set global transaction isolation level 隔离级别名称; ? ? #全局级别的设置，可在所有会话有效，需要重新登录才可生效

set session transaction isolation level 隔离级别名称; ? ?#会话级别的设置，在当前会话会话中立即生效

查看隔离级别：

show global variables like '%isolation%';
show session variables like '%isolation%';

事务管理操作:

begin;             #开启一个事务
....         insert into    update XXX set   delete from      #事务性操作
savepoint XX;      #在事务中创建回滚点
rollback to XX;    #在事务中回滚操作到指定的回滚点位置
commit; 或 rollback;    #提交或回滚结束事务

自动提交事务

set [global/session] ?autocommit = 0/1; ? ? ? ?#0关闭自动提交，1开启自动提交
show ?[global/session] ?variables like 'autocommit';

存储引擎

定义：

存储引擎是MySQL数据库中的组件，负责执行实际的数据I/O操作（数据的存储和提取）。工作在文件系统之上，数据库的数据会先传到存储引擎，再按照存储引擎的存储格式保存到文件系统。

常用的存储引擎：InnoDB ? MyISAM
?

MyISAM 与 InnoDB 的区别？

MyISAM：不支持事务、外键约束，只支持表级锁定，适合单独的查询和插入的操作，读写会相互阻塞，支持全文索引，硬件资源占用较小，数据文件和索引文件是分开存储的（存储成三个文件：表结构文件.frm、数据文件.MYD、索引文件.MYI）
使用场景：适用于不需要事务支持，单独的查询或插入数据的业务场景

InnoDB：支持事务、外键约束，支持行级锁定（在全表扫描时仍然会表级锁定），读写并发能力较好，支持全文索引（5.5版本之后），缓存能力较好可以减少磁盘IO的压力，数据文件也是索引文件（存储成两个文件：表结构文件.frm、数据文件.ibd）
使用场景：适用于需要事务的支持，一致性要求较高，数据会频繁更新，读写并发高的业务场景

查看表的存储引擎：

show create table 表名;
show table status [from 库名] where name='表名'\G

show engines;

更改表的储存引擎

alter table 表名 engine=InnoDB/MyISAM;             #针对已存在的表修改存储引擎

create table 表名 (....) engine=InnoDB/MyISAM;     #新建表时指定存储引擎

vim /etc/my.cnf    
[mysqld]
default-storage-engine=InnoDB/MyISAM          #设置新建表的默认存储引擎

死锁

现象：死锁是指两个或多个事务在同一个资源上相互占用，并请求对方的锁定资源，从而导致恶性循环的现象。

如何避免死锁?

1.设置事务的锁等待超时时间 innodb_lock_wait_timeout

2.设置开启死锁检测 innodb_deadlock_detect

3.为表添加合理的索引，减少表锁发生的概率

4.如果业务允许，可以降低隔离级别，比如采用提交读隔离级别

5.建议开发人员尽量使用更合理的业务逻辑，多表操作时以固定顺序访问表，尽量避免同时锁定

多个资源

6.建议开发人员尽量保持事务简短，减少对资源的占用时间和占用范围

7.建议开发人员在读多写少的场景下适用乐观锁机制

文章来源:https://blog.csdn.net/X2683933654/article/details/135202497
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！