MySQL知识整理，开发必备。

最新推荐文章于 2024-09-20 10:07:22 发布

JsonTu

最新推荐文章于 2024-09-20 10:07:22 发布

阅读量361

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/jiamingTu/article/details/106345087

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL知识整理

关于MySQL知识必备

关于MySQL知识必备

MySQL概述：

在这里插入图片描述

为什么使用MySQL:

在这里插入图片描述
MySQL优点：

MySQL性能卓越，服务稳定，很少出现异常宕机。
MySQL开发源代码且无版权制约，自主性及使用成本低。
MySQL历史悠久，社区及维护稳定，可以有效找到问题解决办法。

MySQL版本

Mysql商业版的发布介绍：
Mysql社区版的发布介绍：
Mysql社区版和商业版的区别：
生产环境如何选择Mysql版本：

MySQL安装和配置：

Windows环境：

1.MySQL官网下载Windows版本安装包：https://dev.mysql.com/downloads/mysql/
在这里插入图片描述

Linux环境安装MySQL：

服务器环境是centos 7.6 这篇教程是通过yum方式安装的。
参考博文：https://www.cnblogs.com/baoshu/p/centos-mysql.html

还可以通过从web官网直接下载Linux版本：
下载 MySQL for Linux Generic 二进制版本安装包（.tar.gz）
在这里插入图片描述
参考博文：https://www.cnblogs.com/fanshudada/p/9781794.html

MySQL 推荐使用 RPM 包进行 Linux 平台下的安装（因为 RPM 包的安装和卸载都很方便，通过简单的命令就可以实现。）
在这里插入图片描述
参考链接：http://c.biancheng.net/view/7616.html

MySQL语法和函数：

MySQL数据库定义语句 DDL

对数据库和表操作，关键字有：CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME

MySQL数据操纵语句 DML

对数据的操作语句，关键字有：SELECT,INSERT,UPDATE,DELETE,MERGE,CALL,EXPLAIN PLAN,LOCK TABLE

数据控制语句–授权/取消授权

关键字有：GRANT,REVOKE
grant 普通数据用户，查询、插入、更新、删除数据库中所有表数据的权利。
grant select, insert, update, delete on testdb.* to common_user@’%’

grant 数据库开发人员，创建表、索引、视图、存储过程、函数。。。等权限。
grant 创建、修改、删除 MySQL 数据表结构权限。
grant create on testdb.* to developer@’192.168.0.%’;
grant alter on testdb.* to developer@’192.168.0.%’;
grant drop on testdb.* to developer@’192.168.0.%’;

grant 操作 MySQL 外键权限。
grant references on testdb.* to developer@’192.168.0.%’;
grant 操作 MySQL 临时表权限。
grant create temporary tables on testdb.* to developer@’192.168.0.%’;
grant 操作 MySQL 索引权限。
grant index on testdb.* to developer@’192.168.0.%’;

grant 操作 MySQL 视图、查看视图源代码权限。
grant create view on testdb.* to developer@’192.168.0.%’;
grant show view on testdb.* to developer@’192.168.0.%’;

grant 操作 MySQL 存储过程、函数权限。
grant create routine on testdb.* to developer@’192.168.0.%’; – now, can show procedure status
grant alter routine on testdb.* to developer@’192.168.0.%’; – now, you can drop a procedure
grant execute on testdb.* to developer@’192.168.0.%’;

grant 作用在整个 MySQL 服务器上：
grant select on . to dba@localhost; – dba 可以查询 MySQL 中所有数据库中的表。
grant all on . to dba@localhost; – dba 可以管理 MySQL 中的所有数据库

grant 作用在单个数据库上：
grant select on testdb.* to dba@localhost; – dba 可以查询 testdb 中的表。

grant 作用在单个数据表上：
grant select, insert, update, delete on testdb.orders to dba@localhost;

grant 作用在表中的列上：
grant select(id, se, rank) on testdb.apache_log to dba@localhost;

grant 作用在存储过程、函数上：
grant execute on procedure testdb.pr_add to ’dba’@’localhost’
grant execute on function testdb.fn_add to ’dba’@’localhost’
注意：修改完权限以后一定要刷新服务，或者重启服务，刷新服务用：FLUSH PRIVILEGES。
解除权限 revoke
//解除 test_zhang 所有权限及grant
REVOKE ALL PRIVILEGES,GRANT OPTION FROM test_zhang
//解除 test_zhang 用户的更新、删除权限
REVOKE UPDATE, DELETE ON classicmodels.* FROM test_zhang;

授予用户权限
grant 权限 on 数据对象 to 用户;

解除用户权限
revoke 权限 on 数据对象 from 用户;

事务控制语句TCL:

关键字有：COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION。

MySQL聚合函数：

函数定义	函数说明
avg():	计算一组值或表达式的平均值
group_concat()：	将字符串从分组中连接成具有各种选项(如DISTINCT，ORDER BY和SEPARATOR)的字符串。
count():	计算表中的行数。
instr():	返回子字符串在字符串中第一次出现的位置
min():	在一组值中找到最小值。
sum():	计算一组值或表达式的总和
max():	在一组值中找到最大值。
avg():	计算一组值或表达式的平均值

MySQL字符串函数

函数定义	函数说明
left():	获取指定长度的字符串的左边部分
replace()：	搜索并替换字符串中的子字符串。
concat():	将两个或多个字符串组合成一个字符串。
substring():	从具有特定长度的位置开始提取一个子字符串。
trim():	从字符串中删除不需要的字符。
format():	格式化具有特定区域设置的数字，舍入到小数位数。

MySQL日期和时间函数

函数定义	函数说明
curdate():	返回当前日期
now()：	返回当前时间和日期
month():	返回一个指定日期的月份整数
yeart():	返回日期值的年份部分
week():	返回一个日期的星期数值
sysdate():	返回当前日期
day():	获取指定日期月份的天数

MySQL执行计划explan：

explain命令是查看查询优化器如何决定执行查询的主要方法。
执行计划的生成方法：
explain select …………….
生成的方法很简单在相应的select前面加explain即可
执行计划的查看:
Id：包含一组数字，表示查询中执行select子句或操作表的顺序；
执行顺序从大到小执行；
当id值一样的时候，执行顺序由上往下；
Select_type:表示查询中每个select子句的类型
在这里插入图片描述
Type：表示MySQL在表中找到所需行的方式，又称“访问类型”，常见有以下几种

MySQL执行计划的局限：

MySQL中的锁（表锁、行锁）：

锁是计算机协调多个进程或纯线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
MySQL大致可归纳为以下3种锁：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

MySQL表级锁（MyISAM引擎）：
MySQL表级锁有两种模式：表共享锁（Table Read Lock）和表独占写锁（Table Write Lock）。
对MyISAM的读操作，不会阻塞其他用户对同一表请求，但会阻塞对同一表的写请求；
对MyISAM的写操作，则会阻塞其他用户对同一表的读和写操作；
MyISAM表的读操作和写操作之间，以及写操作之间是串行的。
当一个线程获得对一个表的写锁后，只有持有锁线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。
在这里插入图片描述
InnoDB引擎锁（表锁，行锁）：
InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。
InnoDB实现了以下两种类型的行锁。
共享锁（s）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（Ｘ）：允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁
另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。
意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
在这里插入图片描述
InnoDB行锁实现方式：
InnoDB行锁是通过索引上的索引项来实现的，这一点ＭySQL与Oracle不同，后者是通过在数据中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味者：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁！
什么时候使用表锁：

第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。

MySQL功能:

索引：

索引（Index）是帮助MySQL高效获取数据的数据结构。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
-目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构