mysql

最新推荐文章于 2024-04-03 15:58:48 发布

honky_tonk_man

最新推荐文章于 2024-04-03 15:58:48 发布

阅读量200

点赞数

分类专栏：运维文章标签： mariadb

本文链接：https://blog.csdn.net/qq_37026934/article/details/82830060

版权

运维专栏收录该内容

17 篇文章 0 订阅

订阅专栏

文章目录

简介
mariadb
sql基本语句
mariadb权限管理
- mariadb权限级别
- mariadb账号创建

简介

数据管理模型

层次模型

层次模型和我们的文件系统一样，有一个根目录，然后一级目录二级目录，但是子目录和子目录之间平行目录很难产生关系，关系都是自上而下的，比如一级子目录a下的a1，和一级子目录b下的b1很难产生关系

网络模型

层次模型进化后就成为网络模型

关系模型

关系模型是在层次模型和网络模型之后的数据模型，他产生于70年代ibm公司，这个模型一直用到现在
关系模型就是一种二维关系
所有的数据都抽象成表格，表格中有行和列
但是表中可以存储什么数据了？可以存储mp3吗？，可以存储图片吗？这样我们按照数据就又可以分成结构化数据，半结构化数据，非结构化数据了

结构化数据

存储的实体的属性就是结构化数据，比如存储一个人的属性信息，存储一张图片的属性，这些属性非常容易结构化

半结构化数据

半结构化数据可以通过灵活的键值调整获取相应信息，且数据的格式不固定，如json，同一键值下存储的信息可能是数值型的，可能是文本型的，也可能是字典或者列表

非结构化数据

就是图片，mp3，视频等等不可以通过键获取其对应关系

索引（index）

索引就是目录一样的，可以通过他快速查找内容，但是当我们索引形成了后还要再插入项，那么索引有可能要打散，再生成，通俗地说就是将表中某些字段抽取出来，单独的将其组织成一个独特的数据结构中，并且排序(b+ tree)，常用的索引类型有
B+ tree（mysql）：balabce tree
hash:
索引有几个概念
innodb是的主键索引将索引和数据放在一起（聚集索引，一次IO），其他的都是分散索引，myisa是分散的索引（二次IO），
mysql索引就是将key或者叫做字段取出来，按照升序或者降序排序，再存入B树或者B+树种，hash索引（memory storage engine）
索引的优势是加速查询的效率，劣势是降低写操作的效率，为什么降低写效率？因为在写的时候mysql不光要保存数据，还要保存索引，维护索引
简单索引
简单索引就是将一个key或者叫字段取出来按照规则排序做成索引叫做简单索引

复杂索引
将多个key或者叫做字段拿出来排列组合成一个索引，叫做复杂索引

对于索引以下场合失效

查询条件不是从左侧开始就失效，例如

index(age,Fname),WHERE Fname = ‘jerry’
因为是复杂索引，一个索引有多个key或者字段组合在一起，并且age字段在前，Fname字段在后，索引查找一半是从第一个字段开始查找，我们直接查找Fname字段，所以无效，除非我们将WHERE 后面的语句改为WHERE age >30 AND Fname = ‘Smith’;即可
不能跳过索引中某列，比如

index(name,age,gender),WHERE age > 30;
在复杂索引中WHERE先查找范围，后查找等值相当于没有使用索引，算法设计上的结果

HASH索引只适用于等值，精确值比较，效率非常高（有的时候做到O1），因为HASH表数据结构的特殊性，他不能排序，所以不能做大于，小于的计算，只能精确比较才能体现HASH索引的优越性

高性能索引的策略

在WHERE中独立使用列，尽量避免参与运算，比如WHERE age+2 > 32;
左前缀索引:构建于字段的最左侧的多少个字符，要通过索引选择性来评估，字符越小越好
多列索引（复杂索引）WHERE的时候按照我们上面说的使用AND连接多个索引
多个索引的时候将选择性最高的放在左侧

我们可以用EXPLAIN来分析索引的有效性，或者说是否用到索引

EXPLAIN [EXPLAIN_TYPE] SELECT select_option

我们举个例子
创建个数据库再创建个表，表中多设定几个key

MariaDB [mydb]> CREATE TABLE students (stuid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(200),age TINYINT UNSIGNED,gender ENUM('F','M'),major VARCHAR(200));
Query OK, 0 rows affected (0.017 sec)

MariaDB [mydb]>
MariaDB [mydb]>
MariaDB [mydb]>
MariaDB [mydb]> DESC students;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| stuid  | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name   | varchar(200)        | YES  |     | NULL    |                |
| age    | tinyint(3) unsigned | YES  |     | NULL    |                |
| gender | enum('F','M')       | YES  |     | NULL    |                |
| major  | varchar(200)        | YES  |     | NULL    |                |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set (0.010 sec)

MariaDB [mydb]>

只对name，age，gender，major插入一些数据，stuid不用，因为其是主键且自动增长

MariaDB [mydb]> INSERT INTO students (name,age,gender,major) VALUES('jia baoyu',17,'M','pixie jianfa'),('Li Chong',37,'M','Xianglong Shiba Zhang'),('Ximen Qing',31,'M','Kuihua Baodian'),('Li Mochou',27,'F','Wuxiang Shenggong');
Query OK, 4 rows affected (0.000 sec)
Records: 4  Duplicates: 0  Warnings: 0

查看一下数据

MariaDB [mydb]> SELECT * FROM students;
+-------+------------+------+--------+-----------------------+
| stuid | name       | age  | gender | major                 |
+-------+------------+------+--------+-----------------------+
|     1 | jia baoyu  |   17 | M      | pixie jianfa          |
|     2 | Li Chong   |   37 | M      | Xianglong Shiba Zhang |
|     3 | Ximen Qing |   31 | M      | Kuihua Baodian        |
|     4 | Li Mochou  |   27 | F      | Wuxiang Shenggong     |
+-------+------------+------+--------+-----------------------+
4 rows in set (0.000 sec)

MariaDB [mydb]>

//其实数据较少不用索引就可以，这个数据不够看太少了，我们可以在bash里面写一个for循环循环个1k条数据

for i in {1..5000} ;  do mysql -uroot -p000000 -e"INSERT INTO mydb.students VALUES ('$i','stu
$i',$(($RANDOM % 100 + 1)),'F','major$1')" ;done
//注意-e后面必须跟sql，且如果前面的-u和-p后面没空格，-e和sql语句中间也没有空格

这样我们插入了5k行数据。。。
然后我们用EXPLAINT语句去查看一个查询的sql语句

MariaDB [(none)]> EXPLAIN SELECT name,age FROM mydb.students WHERE age >=15;
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL | 5000 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.000 sec)

这里select_type是simple说明类型是simple,表示就一个select，如果我们使用UNION去连接2个select表示查询为UNION
完成这个查询我们执行了那个类型操作(type中)all是遍历，index是根据索引顺序进行全表扫描，range表示根据索引在一定的范围进行扫描，ref表示根据索引返回匹配值的所有行，也就是使用index的精确比较，const表示根据索引精确的匹配了一个
key就是索引，我们没有使用就是null，key_len是我们取了多少个索引字节
row是我们为了select取了多少行
创建索引
我们可以先看一下CREATE INDEX的语法

MariaDB [(none)]> HELP CREATE INDEX;
Name: 'CREATE INDEX'
Description:
Syntax:
CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option] ...

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

CREATE INDEX is mapped to an ALTER TABLE statement to create indexes.
See [HELP ALTER TABLE]. CREATE INDEX cannot be used to create a PRIMARY
KEY; use ALTER TABLE instead. For more information about indexes, see
https://mariadb.com/kb/en/optimization-and-indexes/.

URL: https://mariadb.com/kb/en/create-index/


MariaDB [(none)]>

我们直接创建一个最简单的索引

MariaDB [(none)]> CREATE INDEX index_name ON mydb.students(name);
Query OK, 0 rows affected (0.022 sec)
Records: 0  Duplicates: 0  Warnings: 0

这里我们创建了名为index_name的索引，并且索引以name这个key创建，我们再show以下这个索引

MariaDB [(none)]> SHOW INDEX FROM mydb.students;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY    |            1 | stuid       | A         |        5000 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | index_name |            1 | name        | A         |        5000 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.001 sec)
//2个索引，主键自动是一个索引，还有一个是index_name，他是以name而建

我们再查询一下

MariaDB [(none)]> EXPLAIN SELECT name,age FROM mydb.students WHERE name = 'stu1002';
+------+-------------+----------+------+---------------+------------+---------+-------+------+-----------------------+
| id   | select_type | table    | type | possible_keys | key        | key_len | ref   | rows | Extra                 |
+------+-------------+----------+------+---------------+------------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | students | ref  | index_name    | index_name | 803     | const |    1 | Using index condition |
+------+-------------+----------+------+---------------+------------+---------+-------+------+-----------------------+
1 row in set (0.001 sec)

key代表我们使用了index_name这个索引，并且为了此次查询我只抽出了一个数据，也就是我们想要的，说明索引有用，我们再用通配符模糊匹配一下

MariaDB [(none)]> EXPLAIN SELECT name,age FROM mydb.students WHERE name LIKE 'stu100%';
+------+-------------+----------+-------+---------------+------------+---------+------+------+-----------------------+
| id   | select_type | table    | type  | possible_keys | key        | key_len | ref  | rows | Extra                 |
+------+-------------+----------+-------+---------------+------------+---------+------+------+-----------------------+
|    1 | SIMPLE      | students | range | index_name    | index_name | 803     | NULL |   11 | Using index condition |
+------+-------------+----------+-------+---------------+------------+---------+------+------+-----------------------+
1 row in set (0.009 sec)

MariaDB [(none)]>

以上的数据说明我们取出了11行数据查找，前面我们说，索引是左前最小匹配如果模糊匹配的通配符在前面那么索引就不起作用

MariaDB [(none)]> EXPLAIN SELECT name,age FROM mydb.students WHERE name LIKE '%stu100%';
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL | 5000 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.000 sec)

我们创建一个组合索引

MariaDB [(none)]> CREATE INDEX name_and_age ON mydb.students(name,age);
Query OK, 0 rows affected (0.026 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看一下

MariaDB [(none)]> SHOW INDEX FROM mydb.students;
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY      |            1 | stuid       | A         |        5000 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | index_name   |            1 | name        | A         |        5000 |     NULL | NULL   | YES  | BTREE      |         |               |
| students |          1 | name_and_age |            1 | name        | A         |        5000 |     NULL | NULL   | YES  | BTREE      |         |               |
| students |          1 | name_and_age |            2 | age         | A         |        5000 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.000 sec)

这个时候2个索引name和name_and_age有一个key重合，就是name，这样我们可以删除一个

MariaDB [(none)]> DROP INDEX index_name ON mydb.students;
Query OK, 0 rows affected (0.013 sec)
Records: 0  Duplicates: 0  Warnings: 0

视图（view）

视图就是一个虚表，就是将一个表中的几个列取出来做成一个虚表，他不真正的存在，用户只可以看到这个虚表，

SQL标准

数据库如何去让我们使用？那就是接口，数据库开发商会开发出数据库的接口让我们调用，它提供我们编程功能，等等功能由他去调用接口，但是每一个数据库的开发商有可能使用不同的sql语言，那么ANSI协会出台了sql标准，去规范这些sql语言，

事务（transactions）

组织多个操作为一个整体，要么全部都成功执行，要么失败回滚
数据库事务正确执行的四个基本要素
ACID:
A:原子性 Atomicity

传统意义上的原子性是多个相互关联的函数运行时，必须是有序运行一个函数运行完毕，与他相关联的函数才能开始运行
在mysql中原子性就是多个语句运行的时候如果其中某一个语句出错，那么就回滚，不管前面的语句有没有运行成功

C:一致性Consistency

就是字面意思，换句话说，如果说你在应用层面做一个操作，给转账者扣钱，没给接收者加钱，那么这个不一致跟事务的不一致是没有关系的，需要开发人员自己做业务逻辑一致性的保证，我们的原子性，隔离性，持久性都是为了保证一致性

I：隔离性 Isolation

隔离性指的是同时执行多条sql的时候他们是相互合理，互不干扰的,但是为了保证并发我们的隔离有多种级别

D：持久性 Durability

持久性就是值sql执行后就把io操作写入到硬盘，指的是数据持久化存储，他用redo log实现，在命令敲下去的时候先将sql语句存入redo log buffer中，再存入redo log file中后期如果在执行中崩溃可以根据redo log恢复

大家谈到事务都是说ACID等等，并没有直观的描述什么是事务，这里我可以定义一下，事务是一系列有序的声明，查询，插入等等sql操作，这些操作要支持上述的ACID

事务日志

事务日志就是帮助数据库完成ACID性质的日志

在没有事务日志之前，mysql操作数据都是将磁盘中的数据读取到内存中，再在内存中进行修改，修改完后刷新到磁盘上，这样如果断电就有着在语句没有执行的风险，并且如果我们执行以下语句

UPDATE tb1 SET salary = salary*2 WHERE wage > 5

因为我们定义了wage大于5所以数据可能不在同一个连续的区域中，如果磁盘是机械硬盘，这个操作就是随机读写操作，那么磁盘的磁头要移动到合适的磁道等待磁盘上特定的数据转过来，如果不是连续的可能转一半就停止读，再去移动磁头，所以对于机械硬盘来说这个就是噩梦

在有了事务日志后
我们下发sql语句先存入事务日志，然后再由事务日志刷到磁盘上（redo log，就算断电也可以根据事务日志进行同步，如果要回滚可以用undo log），这个过程由同步线程执行，
我们上面说了我们的sql语句先是在内存中操作，再由内存到硬盘，但是有了事务日志，内存就直接到事务日志了，我们可以在mysql中设置当我们commit(按回车)之后直接刷到硬盘，但是这样IO效率就低了（但是几乎不可能丢失我们commit的数据），我们也可以设置1秒刷新一次，这样我们最多会丢失1秒的commit数据，我们按照以下参数定义事务日志相关的配置

我们理一理innodb中提交sql语言事务日志的操作，首先我们commit后先把文件存入内存一个叫做log buffer中，再调用fsync() syscall将buffer的数据阻塞的写入redo log file 和undo log file，再由redo log中的sql进行执行刷到硬盘中，如果某一个错误需要回滚到执行事务前(mysql中的原子性)就调用undo log file进行回滚

innodb_log_files_in_group
//设计分为多少个innodb事务日志  
innodb_log_group_home_dir
//事务日志放在那里
innodb_log_file_size  
//设置innodb的日志大小
innodb_mirrored_log_groups
//设置镜像日志放在那里
innodb_flush_log_at_trx_commit
//设置按照那种模式从内存中刷新到事务日志中，由0，1，2三个选项
//0代表不主动从内存将commit的sql刷新到redo log中  
//1代表commit后立即将sql语句刷新到redo log中，这里是存入操作系统的缓冲区，需要fsync()进行同步，并且这个函数还是阻塞的
//2代表存入磁盘文件对应的os cache中，1秒一次刷新一次到磁盘中

以上的函数我们可以再配置文件中定义重启服务，也可以在mysql中sql语句更改

MariaDB [(none)]> SHOW VARIABLES LIKE "innodb%log%";
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| innodb_encrypt_log               | OFF       |
| innodb_flush_log_at_timeout      | 1         |
| innodb_flush_log_at_trx_commit   | 1         |
| innodb_locks_unsafe_for_binlog   | OFF       |
| innodb_log_buffer_size           | 16777216  |
| innodb_log_checksums             | ON        |
| innodb_log_compressed_pages      | ON        |
| innodb_log_file_size             | 50331648  |
| innodb_log_files_in_group        | 2         |
| innodb_log_group_home_dir        | ./        |
| innodb_log_optimize_ddl          | ON        |
| innodb_log_write_ahead_size      | 8192      |
| innodb_max_undo_log_size         | 10485760  |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_scrub_log                 | OFF       |
| innodb_scrub_log_speed           | 256       |
| innodb_undo_log_truncate         | OFF       |
| innodb_undo_logs                 | 128       |
+----------------------------------+-----------+
18 rows in set (0.002 sec)
//查询我们可以更改的变量发现有我们需要改的

这里的变量分为global和session2种，分别是对于每个session和全局（由session继承）要查看分别为

MariaDB [(none)]> SHOW GLOBAL VARIABLES;
MariaDB [(none)]> SHOW VARIABLES;
//不加上global自动就是session
//改变global对于当前会话没有效，只对新的线程有效 
//改变session对本线程当场生效 
//不管global还是session更改的如果服务器一重启都没了，要永久存储还是存储到mysql的配置文件种

比如我们更改global的变量autocommit，我们先看这个变量的值

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.001 sec)

MariaDB [(none)]>

我们更改为关闭

MariaDB [(none)]> SET @@global.autocommit=0;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.001 sec)

MariaDB [(none)]>
//@后面表示变量一个@表示用户自定义变量，二个@代表global或者session变量，后面直接跟值，来更改

我们可以开始一个事务

MariaDB [(none)]> START TRANSACTION;

查看以下有几个表

MariaDB [mydb]> SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| tbl1           |
+----------------+
1 row in set (0.001 sec)

MariaDB [mydb]>

查看以下这个表的内容

MariaDB [mydb]> SELECT * FROM tbl1;
Empty set (0.000 sec)

空的说明没有内容，我们再看一下这个表的结构

MariaDB [mydb]> SHOW COLUMNS FROM tbl1;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | YES  |     | NULL    |       |
| name  | varchar(200)     | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.001 sec)

MariaDB [mydb]>

发现key有一个id一个name，类型分别是int和varchar，所以直接插入

MariaDB [mydb]> INSERT INTO tbl1 VALUES (2,"JERRY"),(3,"TOM");
Query OK, 2 rows affected (0.001 sec)
Records: 2  Duplicates: 0  Warnings: 0
//注意多个key-value用逗号隔开

再查看以下表内容，就有了数据

MariaDB [mydb]> SELECT * FROM tbl1;
+------+-------+
| id   | name  |
+------+-------+
|    2 | JERRY |
|    3 | TOM   |
+------+-------+
2 rows in set (0.000 sec)

MariaDB [mydb]>

因为我们处于一个事务种，所以因为原子原则我们可以回滚

MariaDB [mydb]> ROLLBACK;
Query OK, 0 rows affected (0.011 sec)

MariaDB [mydb]>

事务回滚后再看一下表内容 ,又空了。

MariaDB [mydb]> SELECT * FROM tbl1;
Empty set (0.000 sec)

MariaDB [mydb]>

我们也可以在事务中某个语句结束后加上SAVEPOINT POINT_NAME语句创建一个保存点，就像快照一样ROLLBACK可以回滚到这个保存点上而不是直接回滚到事务创建之初
插入一个数据(因为表目前是空的)

MariaDB [mydb]> INSERT INTO tbl1 VALUES (1,"ZHR"),(2,"JERRY");
Query OK, 2 rows affected (0.011 sec)
Records: 2  Duplicates: 0  Warnings: 0

查看以下这个表

MariaDB [mydb]> SELECT * FROM tbl1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | ZHR   |
|    2 | JERRY |
+------+-------+
2 rows in set (0.000 sec)

SAVEPOINT以下保存当前的状态

MariaDB [mydb]> SAVEPOINT frist;
Query OK, 0 rows affected (0.000 sec)

再删除一行数据，并且查看表中数据

MariaDB [mydb]> DELETE FROM tbl1 WHERE id = 1 ;
Query OK, 1 row affected (0.002 sec)

MariaDB [mydb]> SELECT * FROM tbl1;
+------+-------+
| id   | name  |
+------+-------+
|    2 | JERRY |
+------+-------+
1 row in set (0.000 sec)

MariaDB [mydb]>

再rollback到frist上,失败，为什么？

MariaDB [mydb]> ROLLBACK TO  frist;
ERROR 1305 (42000): SAVEPOINT frist does not exist

因为我们开启了autocommit，因此每执行一次sql语句自动commit因为START TRANSACTION是开始一个事务，结束一个事务是COMMIT，所以我们要关闭autocommit

MariaDB [mydb]> SET @@session.autocommit=OFF;
Query OK, 0 rows affected (0.000 sec)

查看一下状态

MariaDB [mydb]> SHOW VARIABLES LIKE "%autocommit%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| autocommit             | OFF   |
| wsrep_retry_autocommit | 1     |
+------------------------+-------+
2 rows in set (0.001 sec)

已经关闭

隔离级别

这里我们考虑一个问题，就是多个事务操作一个表格，其中一个事务commit提交了，另一个还没有，这个时候数据不对第二个没有commit的事务怎么办？所以这里innodb定义了4个隔离级别
我们可以用SQL语句查看隔离级别

MariaDB [mydb]> SHOW SESSION VARIABLES LIKE "tx_isolation";
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

READ-UNCOMMITTED(读未提交)
这个隔离级别最低，2个transaction只要有一个transaction做了操作（没有commit）另一个transaction立马看到数据被改变（也叫做“脏读”，顾名思义度别人没有提交的数据），如果第一个tranaction又后悔进行了ROLLBACK那么第二个transaction也可以立马看到数据恢复了
设置READ-UNCOMMITTED

MariaDB [mydb]> SET @@global.tx_isolation = "READ-UNCOMMITTED";
Query OK, 0 rows affected (0.008 sec)

MariaDB [mydb]>
//设置global的变量  
MariaDB [mydb]> SET @@session.tx_isolation = "READ-UNCOMMITTED";
Query OK, 0 rows affected (0.000 sec)
//设置session的变量

READ-COMMITTED(读提交)
这个隔离级别又相较于上一个严厉了许多，一个transaction进行的操作只要没有commit，另一个transaction就看不到，相当于一个表2个transaction对他做了个快照，各自在自己的一层改各自的互不干扰，只有commit后另一个transaction才看到

MariaDB [mydb]> SET @@global.tx_isolation = "READ-COMMITTED";
Query OK, 0 rows affected (0.008 sec)

MariaDB [mydb]>
//设置global的变量  
MariaDB [mydb]> SET @@session.tx_isolation = "READ-COMMITTED";
Query OK, 0 rows affected (0.000 sec)
//设置session的变量

REPEATABLE-READ(可重重复读)
这个级别会出现幻读，也就是说一个transaction改了一个表后commit，另一个transaction不会看到更改后的数据，如果另一个transaction什么也不干也commit，SELECT发现表被改了，这个时候就是幻读，明显什么没干，但是数据没了

MariaDB [mydb]> SET @@global.tx_isolation = "REPEATABLE-READ";
Query OK, 0 rows affected (0.008 sec)

MariaDB [mydb]>
//设置global的变量  
MariaDB [mydb]> SET @@session.tx_isolation = "REPEATABLE-READ";
Query OK, 0 rows affected (0.000 sec)
//设置session的变量

SERIALIZABLE(串行化)
隔离级别最高，但是并发几乎不可能

存储引擎

mysql是一个插件式存储引擎，存储引擎是组织表怎么存储在磁盘或者内存上的，存储引擎是表级别的概念，建议同一个库用一个存储引擎
定义方法
CREATE TABLE ... ENGINE STORAGE_ENGINE_NAME
SHOW TABLE STATUS
我们也可以改变表的存储引擎，但是不建议，因为改变存储引擎相当于把所有表导出来再用新的存储引擎建立一个新表，再把数据导入到新表中
例如

MariaDB [(none)]> CREATE DATABASE mydb;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> USE mydb;
Database changed
MariaDB [mydb]> CREATE TABLE tbl1 (id INT UNSIGNED ,name VARCHAR(200));
Query OK, 0 rows affected (0.020 sec)

MariaDB [mydb]> SHOW TABLE STATUS ;
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+------------------+-----------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment | Max_index_length | Temporary |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+------------------+-----------+
| tbl1 | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2021-02-03 22:31:47 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |                0 | N         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+------------------+-----------+
1 row in set (0.002 sec)

MariaDB [mydb]>
//为什么data_length是XXX，但是我们并没有数据，因为innodb的原因，collation是排序规则，比如按照拼音排序，按照英语字母排序
//我们可以在SHOW的时候使用通配符like匹配符，mysql中%代表任意一个或者多个字符，_代表一个字符
MariaDB [mydb]> SHOW TABLE STATUS LIKE "tb%";
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+------------------+-----------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment | Max_index_length | Temporary |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+------------------+-----------+
| tbl1 | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2021-02-03 22:31:47 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |                0 | N         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+------------------+-----------+
1 row in set (0.001 sec)

MariaDB [mydb]>
//我们也可以使用where根据某个key过滤,然后sql语句结尾不加;加上\G横着显示  
MariaDB [mydb]> SHOW TABLE STATUS WHERE ENGINE = "innodb"\G
*************************** 1. row ***************************
            Name: tbl1
          Engine: InnoDB
         Version: 10
      Row_format: Dynamic
            Rows: 0
  Avg_row_length: 0
     Data_length: 16384
 Max_data_length: 0
    Index_length: 0
       Data_free: 0
  Auto_increment: NULL
     Create_time: 2021-02-03 22:31:47
     Update_time: NULL
      Check_time: NULL
       Collation: utf8mb4_general_ci
        Checksum: NULL
  Create_options:
         Comment:
Max_index_length: 0
       Temporary: N
1 row in set (0.001 sec)

MariaDB [mydb]>

MyISAM：不支持事务，表级锁，崩溃后不保证安全恢复，mysql时代默认的存储引擎
InnoDB：mariadb使用的默认引擎就是innoDB开发公司的一个开源引擎XtraDB：支持事务，行级锁，外键，热备
查看mysql支持的存储引擎

MariaDB [(none)]> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                            | NO           | NO   | NO         |
| CSV                | YES     | Stores tables as CSV files                                                       | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                        | NO           | NO   | NO         |
| MyISAM             | YES     | Non-transactional engine with good performance and small data footprint          | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                           | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                               | NO           | NO   | NO         |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                   | YES          | NO   | YES        |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.001 sec)
//transactions是事务的意思
//XA是"分布式事务"

innodb

支持事务，支持行级锁，支持外键
innodb的所有表存储在表空间中(文件系统之上的一个逻辑层)
表空间

innodb的所有表存储在表空间中实际存储在/var/lib/mysql/ibdata1中，如果idata1存储不够用在同一个目录会有第二个idata2 ，既然所有的数据都在ibdata1中，那么table专用的表空间只存储表格式文件(.frm结尾的文件)，如果我们在/etc/mysql/my.cnf中的[mysqld]下加上innodb_file_per_table = ON那么我们每一个表的数据都会放到各自的表空间中不会都放到idata1里面

innodb只适用于较少的事务
mvcc
间隙锁
行级锁
聚集索引
我们知道索引都是单独存放先找到索引在通过索引直接找到磁盘的某个位置，但是聚集索引是索引和数据放在一起，找到索引后面就是数据，一个表可以有多个索引，聚集索引必须是主键也就是说，在多级索引中必须是最里面的一个索引

myisam

支持全文索引
我们知道索引是从某个键抽取出来按照规则排序，每一个key只抽取固定的bit，并不是全文都抽出排序，所以全文抽出来排序是全文索引
不支持事务
表级锁
文件类型
每个表三个文件

TABLE_NAME.frm 表格式
TABLE_NAME.MYD 数据文件
TABLE_NAME.MYI 索引文件

主键

一个或者多个字段，填入主键的数据必须是一个唯一的数据而且不能为空，这个主键中数据可以表示这个数据所在的一行

ID	NAME	sexual
1	BOB	male
2	hank	female

下面ID可当成一个主键因为他是唯一的而name如果每个人的名字不一样也可以当成一个主键来代表这一行

外键

姓名	性别	年龄	班级编号
小陈	女	16	1
小王	男	17	2

班级编号	班级
1	一班
2	二班

像我们的第一张表中班级编号就是一个外键，因为外键能否填进去，取决于第二张表中班级编号有没有，如果没有就填不进去，

唯一键

唯一键和主键一样但是他可以为空，而且一个表可以存在多个，但是一个表只能由一个主键

关系运算

选择 :

挑选出符合条件的行

投影 :

挑选出符合条件的列

连接 :

将多张表关联起来

数据抽象

物理层：

决定数据的存储格式，即如何将数据组织成为物理文件

逻辑层：

描述DB存储什么数据，以及数据间存在什么样的关系

视图层：

描述DB中的部分数据

锁

读锁

读锁是共享锁的一中，一旦设定读锁后其他线程只能读不能写

写锁

写锁是独占锁的一种，一旦设定写锁后其他的线程不能读，不能写，因为读之前需要写

行级锁

相较于表级锁，粒度小，线程只锁定表中的某个行，其他的线程可以操作同一个表其他的行，并发比表级锁好

//我们可以使用LOCK命令设定写锁(独占锁)，读锁(共享锁)
MariaDB [mydb]> LOCK TABLES tbl1 READ ;
Query OK, 0 rows affected (0.000 sec)
//我们可以UNLOCK TABLES释放当前线程锁持有的所有锁
MariaDB [mydb]> UNLOCK TABLES ;
Query OK, 0 rows affected (0.000 sec)

//SELECT可以在查询后添加锁  
//FLUSH可以在刷新到磁盘后添加锁。这里不常用

mariadb

安装

rpm包安装

由OS的发行商提供

程序官方提供

在mariadb官方网站上有指向官方rpm的方法

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

然后yum install mariadb mariadb-server -y 即可

源码包

通用二进制格式的程序包

mariadb的程序组成

Client

client通过mysql protocol协议连接server端
mysql有三种套接字地址
1，ipv4/ipv6：3306/tcp

客户端进程在进程空间，将数据传送到内核空间附上套接字首部，ip首部，帧首部然后通过网卡发出去，再经过交换机，或路由器发送到另外一台有mysql-server端的服务器，首先通过网卡到server端的内核空间拆包拆到套接字看到是3306就传到对应的进程空间，也就是相应的服务上

2，unix sock：/var/lib/mysql/mysql.sock 或者 /tmp/mysql.sock

我们直到unix sock是套接字组中用来同一主机不同进程通讯的，首先如果像ipv4那样客户端进程在进程空间，将数据传送到内核空间附上套接字首部，ip首部，帧首部然后内核看到是自己再拆封装再把数据发到进程空间中，这样非常的麻烦，所以我们就直接一个进程将数据放到一个共享内存中，然后另一个进程去共享内存中取数据即可不用封装，拆封装。而且他的ip是localhost或者127.0.0.1

3
mysql：CLI交互式客户端程序
mysqldump：备份工具
mysqladmin：管理工具
mysqlbinlog

server

配置文件

mariadb的配置文件是一个ini风格的配置文件，用一个文件为多个程序提供配置：
比如 mariadb的配置文件/etc/my.cnf
[mysql] 这一项里面是用来配置mariadb客户端的
[mysqld]这一项里面是用来配置mariadb服务端的
[mysqld_safe]
[server] 对于所有server端文件
[client] 对于所有客户端文件
mariadb启动不止读取一个配置文件，而是按照顺序读取，且最后读取的最后生效有一条命令可以查看他们的运行顺序

[root@docker3 ~]#my_print_defaults     
...
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf 
...

可以看出mariadb先读取/etc/mysql/my.cnf然后是/etc/my.cnf然后是~/my.cnf （ ~ 是/etc/my.cnf.d/ ）

mariadb架构

在这里插入图片描述
client先连接到connection pool验证账号密码，等操作，线程重用机制，每一个线程使用完不后先不exec收集退出，而是如果有新的需求可以复用
连接建立后sql语句就发过来了（通过sql interface），sql语句一来就发送给parser分析器，分析那个是参数，那个是语句，然后再此法分析都没问题，再检查有没有权限执行这些都是再parser运行
等都通过parser将sql发送给optimizer优化器进行优化
最后给存储引擎

mariadb客户端命令mysql

mysql
mysql [OPTIONS] [database]
常用选项
-u，指定用户默认为root
-h，指定远程主机(即mariadb服务器地址)，默认为localhost，客户端连接服务器，服务器会反向解析客户的ip为主机名，mysql的用户账号是USER@HOST格式的如果你连接的时候只写了主机名没写用户，他一反解你后面就连接不上了所以我们要关闭主机名反解功能（默认开启）在配置文件上加上skip_name_resolve=ON即可
-p：username的密码，默认为空

注意mariadb的用户账号由2部分组成： ‘USERNAME’@‘HOST’，其中HOST用于限制此用户可以通过那些远程主机连接当前的mysql服务
%：匹配任意长度任意字符
- :匹配任意单个字符

-P：mysql服务器监听的端口，默认为3306
-S：知名sock文件路径 /PATH/TO/mysql.sock
-D：连接数据库后设定登陆数据库的默认库如果不选择为(none)
-e: 后加上sql语句可以不进入数据库执行sql语句并且显示出来结果例如 -e ‘SHOW DATABASES;’
当我们进入mysql数据库后，也有客户端命令，服务端命令（服务端命令就是sql语句），其中客户端命令不用分号结尾，服务端命令需要以分号结尾
\h查看帮助

MariaDB [(none)]> \h  
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement. //取消命令的执行比如CREATE DATABASE\c 这个句子写到一半在后面加上\c再
按回车这条命令就不执行了就像ctrl + c一样
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.  //更改语句结束符默认是; 例如\d #后面我们的sql语句都必须以#结尾不再是分号
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically. //纵向显示，因为有的命令很长我们屏幕显示不过来可以加上 
\G变成纵向显示（每一行变成一个ROW） 例如 SELECT * FROM user \G
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument. //这个名令很有用， ./是执行shell脚本的而\.是执行sql脚本
的而且是在，mysql里面，例如我们/tmp下有一个sql脚本我们这样  MariaDB [mysql]>\. /tmp/my.sql直接执行my.sql这个脚本，但是注
意运行mysql进程的是mysql用户，我们的脚本存放目录必须要让我们mysql用户可以访问可以执行。
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command. //执行shell命令的例如 \! ls /etc
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

sql基本语句

sql语句就是我们服务端的命令，可以通过help contents；查看帮助(help contents;查看有多少类型的命令什么DDL，DML等等)，然后我们再help 某一个类型的语言查看这个类型语言的具体命令例如

MariaDB [(none)]> help Contents;
Account Management
   Administration
   Compound Statements
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Procedures
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility  
   
//我们再查看数据定义语言  
MariaDB [(none)]> help Data Definition    

//我们再查看数据定义语言中的CREATE DATABASES命令的帮助   
MariaDB [(none)]> help CREATE DATABASE
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.

URL: https://mariadb.com/kb/en/create-database/

数据类型

在我们创建表的时候可以进一步确定其数据类型
字符集：

SHOW CHARACTER SET; //查看我们mariadb字符集也就是编码方式   
MariaDB [(none)]> SHOW CHARACTER SET ;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                 | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
| macce    | Mac Central European        | macce_general_ci    |      1 |
| macroman | Mac West European           | macroman_general_ci |      1 |
| cp852    | DOS Central European        | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode            | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
+----------+-----------------------------+---------------------+--------+
40 rows in set (0.001 sec)    
//其中Charset为编码方法  
//Description为简介  
//Default collation 默认的排序方法，简体中文的有可能用拼音，有可能用笔画
//Maxlen为一个字最大的长度单位为字节  
MariaDB [(none)]> SHOW COLLATION; 为查看编码支持的排序方法  
MariaDB [(none)]> SHOW COLLATION LIKE 'gbk%';
+----------------------+---------+------+---------+----------+---------+
| Collation            | Charset | Id   | Default | Compiled | Sortlen |
+----------------------+---------+------+---------+----------+---------+
| gbk_chinese_ci       | gbk     |   28 | Yes     | Yes      |       1 |
| gbk_bin              | gbk     |   87 |         | Yes      |       1 |
| gbk_chinese_nopad_ci | gbk     | 1052 |         | Yes      |       1 |
| gbk_nopad_bin        | gbk     | 1111 |         | Yes      |       1 |
+----------------------+---------+------+---------+----------+---------+
4 rows in set (0.001 sec)  
//gbk编码默认为gbk_chinese_ci，也就是按照普通的字母顺序排序，而且不分大小写

字符类型：
1. 定常字符//（给客户分配一个固定的长度比如20个，如果填一个字符就给你20个字节，如果多了超过20就截断）
	CHAR(#) //不区分大小写 #是打算保存多少个字节最好不要超过255个
	BINARY(#) //区分字符大小写(保存为2进制)   
2. 变长字符  //（存一个给一个，但是对性能有影响）  
	VARCHAR(#) //不区分大小写  他会在最后加上结束符，如果内容255个字节以内就一个字节结束符，如果是大于255小于65535个字符就是2个字节的结束符
	VARBINARY(#) //区分字符大小写
3. 对象类型 //(用来存大量字符的，我们把它当成对象存储)  
	TEXT //一个格子可以存一篇非常大的文章，当然他不是真正的存在格子里，他是存在另外一个空间，而格子里存储的是他的标识符而且text不区分大小写 TINYTEXT(可以存储255个字符，如果一个字符是一个字节的话)，SMALLTEXT(可以存储65535个字符) ,MEDIUMTEXT（可以存储2^24) ,TEXT(可以存储2^32次方)，LONGTEXT(可以存储2^40次方)。
	BLOB  //他是二进制，他区分大小写 ，他的大小和上面一样分为TINYBLOB,SMALLBLOB,MEDIUMBLOB,LONGBLOB,大小也一样
4. 内置类型  
	SET (集合类型) //给定一个集合里面有abc，我们可以随意组合但是不能填入除abc之外的字母
	ENUM(枚举类型)   //设定这个格子只能填给好的数据比如 性别 只能填Male和Female，当然格子里面只是Male和Female的标识符，这样排序不好排
	   
数值类型

5. 精确数值类型
	INT //(TINYINT-8位表示数据范围，SMALLINT-16位表示数据范围，MEDIUMINT-24位表示数据范围，INT-32位表示数据范围，BIGINT-40位表示数据范围)注意INT是SIGNED的如果我们要使用UNSIGNED就要在前面加上这个修饰符
     
6. 近似数值类型  
	FLOAT 
	DOUBLE  

日期时间型  

7. 日期型  
	DATE
8. 时间型   
	TIME 
9. 日期时间型
	 DATETIME
10. 时间戳  
	TIMESTAMP    
11.年份  
	YEAR(2),YEAR(4)  
字段数据修饰符  //修饰某一个字段设置其属性
12.非空  
	NOT NULL
13.空  
	NULL
14.默认值 
	DEFAULT value  
15.主键 
	PRIMARY KEY  
16.唯一键  
	 UNIQUE KEY

以上的数据类型可HELP DATA TYPES查看

MariaDB [(none)]> HELP DATA TYPES

Nothing found
Please try to run 'help contents' for a list of all accessible topics

MariaDB [(none)]> HELP DATA TYPES;
You asked for help about help category: "Data Types"
For more information, type 'help <item>', where <item> is one of the following
topics:
   AUTO_INCREMENT
   BIGINT
   BINARY
   BIT
   BLOB
   BLOB DATA TYPE
   BOOLEAN
   CHAR
   CHAR BYTE
   DATE
   DATETIME
   DEC
   DECIMAL
   DOUBLE
   DOUBLE PRECISION
   ENUM
   FLOAT
   INT
   INTEGER
   LONGBLOB
   LONGTEXT
   MEDIUMBLOB
   MEDIUMINT
   MEDIUMTEXT
   SET DATA TYPE
   SMALLINT
   TEXT
   TIME
   TIMESTAMP
   TINYBLOB
   TINYINT
   TINYTEXT
   VARBINARY
   VARCHAR
   YEAR DATA TYPE

字段修饰符，和数据类型不一样的是字段修饰符定义主键，定义非空定义默认值

NOT NULL:非空  
NULL	为空  
DEFAULT value： 默认值  
UNIQUE KEY 唯一键  
AUTO_INCREMENT:自动增长，如果定义了自动增长则一定要定义他为主键

DDL（data defined language）

定义一张表，删除一张表，查看表就是数据定义语言例如
CREATE , ALTER ,DROP,SHOW ETC ，

注意DDL会对数据的触发器，索引，数据等进行定义，所有DDL语言里面的DROP删除
表，会删除数据，索引，等等东西，换句话说会真正的去定义数据，抹掉数据

delete是DML语言是数据操作语言，他删除后的表可以找回所以如果用delete删除一个表磁
盘大小不会变表可以回滚，而drop是真正的对数据结构进行定义所以drop后磁盘立马变
小，并且找不回数据。。。

创建

创建数据库

使用CREATE命令创建格式为

CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name;  
[DEFAULT]CHARACTER SET = charset_name
[DEFAULT]COLLATE=collation_name  
#设置字符集和排序规则如果不设置就使用默认SHOW CHARACTER SET查看字符集，SHOW COLLATION查看排序方式

我们来创建数据库

MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS zhr CHARACTER SET=utf8  ;  
//上面的语句我们创建了一个数据库如果存在就不创建并且不报错只是warning一下我
们可以show warning查看上一次报错，然后CHARACTER SET=utf8设置默认字符集为
utf8

其实我们创建数据库就是在/var/lib/mysql/下创建了一个目录，

创建表

我们创建表可以为表中每一项单独定义他们的数据类型或者字段类型，其具体格式如下

MariaDB [zhr]> CREATE TABLE tb1 ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE KEY ,name VARCHAR(60) NOT NULL );  
#我们定义了2项一个是id一个是name，在括号中定义项，每一项用逗号隔开，其中在
定义id这一项的时候我们用的是smallint数据类型而且是无大小写，字段类型是不能为
空，自动增长主键，name项的数据类型是VARCHAR（60）边长字节60，字段类型不
能为空

创建完表后我们可以用DESCRIBE来查看表的字段属性

MariaDB [zhr]> DESCRIBE tb1;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(60)          | NO   |     | NULL    |                |
+-------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

我们也可以不在某一项中定义唯一键，可以另起一个

MariaDB [zhr]> CREATE TABLE tb2 (id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,name VARCHAR(60) NOT NULL,UNIQUE KEY(id));
Query OK, 0 rows affected (0.10 sec)  
我们在后面定义了UNIQUE KEY并且指定主键  
PRIMARY KEY也可以单独定义

我们可可以定义表选项，比如为表单独的定义某个引擎，某个字符类型，某个排序类型等，他就写在()外面即可，因为()是专门用来定义某些项的，

常见的table_option  
  | ENGINE [=] engine_name	#设置引擎=号可以省略
  | AUTO_INCREMENT [=] value	#设置从那个数开始自动增长=号可以省略
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}	#校验和
  | [DEFAULT] COLLATE [=] collation_name	#排序规则
  | COMMENT [=] 'string'	#简述这个表

我们的表创建完成后也可以使用SHOW TABLES STATUS\G;查看此表的属性\G竖着看，前面DESC TABLE是查看表每一项的属性

MariaDB [zhr]> SHOW TABLE STATUS\G;
*************************** 1. row ***************************
           Name: tb1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1
    Create_time: 2019-01-02 22:24:46
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 2. row ***************************
           Name: tb2
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1
    Create_time: 2019-01-03 21:51:29
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 3. row ***************************
           Name: tb3
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1
    Create_time: 2019-01-03 21:59:29
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
3 rows in set (0.00 sec)  


#只看tb1  
MariaDB [zhr]> SHOW TABLE STATUS LIKE 'tb1'\G;
*************************** 1. row ***************************
           Name: tb1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1
    Create_time: 2019-01-02 22:24:46
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

如果我们想用别的表的项和格式再创建一个新的表我们能可以这样写

MariaDB [mysql]> CREATE TABLE zhr.tb5 LIKE user;
Query OK, 0 rows affected (0.01 sec)
#我们仿照mysql数据库的user表创建了zhr数据库的tb5表
MariaDB [zhr]> SHOW TABLE STATUS LIKE 'tb5';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+-----------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation | Checksum | Create_options | Comment                     |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+-----------------------------+
| tb5  | MyISAM |      10 | Dynamic    |    0 |              0 |           0 | 281474976710655 |         2048 |         0 |           NULL | 2019-01-03 22:55:53 | 2019-01-03 22:55:53 | NULL       | utf8_bin  |     NULL |                | Users and global privileges |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+-----------------------------+
1 row in set (0.00 sec)
#结构也一模一样，但是他的内容没有复制

我们也可以根据mysql数据库的user表的前3项创建一个新表

MariaDB [zhr]> CREATE TABLE tb6 SELECT Host,User,Password FROM  mysql.user;  
#他会把数据也给放进去，但是我们如果不指定每一项的结构默认不会继承SELECT表中项的类型，必须自己写

我们也可以创建索引

MariaDB [zhr]> CREATE INDEX user ON tb5(User);
#前一个user是索引的名字，后一个User是tb5的某一项

修改属性（ALTER）

我们可以修改表属性使用alter修改表属性无非就是表的字符集，表的排序方式等，添加（ADD）表的某一项和删除表的某一项

MariaDB [(none)]> ALTER DATABASE zhr CHARACTER SET 'gbk' COLLATE 'gbk_chinese_ci';  
#以上修改了数据库的字符集为gbk，排序方式为gbk_chinese_ci,

修改表属性

#为表添加gender一项   
MariaDB [zhr]> ALTER TABLE tb1 ADD gender ENUM('F','M') NOT NULL AFTER id;
#此时我们添加了tb1这一项，指定他的类型为枚举，只有F和M选项，并且不能为空，
而且这一项在id这一项后面。

我们可以再用chang改变某一项的名字和他的属性

MariaDB [zhr]> ALTER TABLE tb1 CHANGE id stuid SMALLINT  UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0
#我们把tb1这一项名字改成了stuid，并且使用SMALLINT和UNSIGEND数据类型，
NOT NULL PRIMARY KEY AUTO_INCREMENT字段类型，记住数据类型和字段类型
不能混写

modify也可以改，和chang一样，他们的区别是change必须要连名字一起改，而modify不用

我们也可以用alter中drop掉某个字段

MariaDB [zhr]> ALTER TABLE tb1 DROP gender;

我们可以通过ALTER去创建索引 (创建索引也是用add)

MariaDB [zhr]> ALTER TABLE tb1 ADD INDEX(name); 

#我们可以用SHOW INDEX 去查看索引  
#我们也可以删除索引 ，索引只能创建和删除
MariaDB [zhr]> ALTER TABLE tb1 DROP INDEX name; 
#删除主键创建主键

删除（DROP）

MariaDB [(none)]> DROP DATABASE zhr; 
#注意在生产环境中最好不要删除数据库，最好的方法是移走

查看（SHOW）

like；后使用mariadb通配符

MariaDB [(none)]> SHOW DATABASES LIKE '%ma' ;
+--------------------+
| Database (%ma)     |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
#show是查看数据库的命令使用LIKE就代表后面可以用通配符，在mysql中%代表以任
意字符出现任意次数和bash shell的通配符*有点像，所以上面就是以ma结尾的数据库

DML（data Manipulation language）

用来操作表中数据的（增删改查。。。）例如
INSERT,DELETE,UPDATE,SELECT ETC

插入和替换（insert&&replace）

我们先创建一个表

MariaDB [zhr]> CREATE TABLE tb7(stuid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,gender ENUM('F','M') NOT NULL,name VARCHAR(60) NOT NULL,brithdate DATE,classid SMALLINT);

然后我们去插入对应的数据

MariaDB [zhr]> INSERT INTO tb7 VALUE(1,'F','guo jing','1997-6-28',3);

我们也可以选择的插入多行数据

MariaDB [zhr]> INSERT INTO tb7(name,gender) VALUE('yang guo','F'),('yang kang','F');  
#此处先定义在name和gender项中插入，并且插入了2行

然后我们再查看表格

MariaDB [zhr]> SELECT * FROM tb7;
+-------+--------+-----------+------------+---------+
| stuid | gender | name      | brithdate  | classid |
+-------+--------+-----------+------------+---------+
|     1 | F      | guo jing  | 1997-06-28 |       3 |
|     2 | F      | yang guo  | NULL       |    NULL |
|     3 | F      | yang kang | NULL       |    NULL |
+-------+--------+-----------+------------+---------+
3 rows in set (0.00 sec)

这时插入我们还有替换（REPLACE），当我们这一行存在就替换，如果不存在就插入

MariaDB [zhr]> REPLACE INTO tb7 VALUE (3,'M','zhang wuji','1996-11-25',5);   
#再查看表格  
MariaDB [zhr]> SELECT * FROM tb7;
+-------+--------+------------+------------+---------+
| stuid | gender | name       | brithdate  | classid |
+-------+--------+------------+------------+---------+
|     1 | F      | guo jing   | 1997-06-28 |       3 |
|     2 | F      | yang guo   | NULL       |    NULL |
|     3 | M      | zhang wuji | 1996-11-25 |       5 |
+-------+--------+------------+------------+---------+  
#我们发现replace的语法和insert一样

查找（select）

select有很多子语句
select * from tb1_name,tb1_name; #*代表多项他可以列出多个表，但是要慎用因为他是表一的行乘以表二的行

#我们可以只查看几项  
MariaDB [zhr]> SELECT name,gender FROM tb7;
+------------+--------+
| name       | gender |
+------------+--------+
| guo jing   | F      |
| yang guo   | F      |
| zhang wuji | M      |
+------------+--------+
3 rows in set (0.00 sec)  

#如果我们觉得name这一项，name这个名字不好我们可以使用AS起一个别名，这个别名并不会去改变原表  
MariaDB [zhr]> SELECT name AS student_name,gender FROM tb7; 
+--------------+--------+
| student_name | gender |
+--------------+--------+
| guo jing     | F      |
| yang guo     | F      |
| zhang wuji   | M      |
+--------------+--------+
3 rows in set (0.00 sec)

这个是挑选列的，当我们挑选完列后他就可以直接输出整行，我们可以使用where挑选出指定的行，怎么去挑选行了？主要是做比较，其中再maradb中比较可以支持一下几种算术类型

我们打印stuid和name还有gender项并且我们使用WHERE过滤大stuid大于1的行  
MariaDB [zhr]> SELECT stuid,name,gender FROM tb7 WHERE stuid > 1;
+-------+------------+--------+
| stuid | name       | gender |
+-------+------------+--------+
|     2 | yang guo   | F      |
|     3 | zhang wuji | M      |
+-------+------------+--------+

打印stuid和name和gender项并且过滤出gender等于F的行
MariaDB [zhr]> SELECT stuid,name,gender FROM tb7 WHERE gender='F';
+-------+----------+--------+
| stuid | name     | gender |
+-------+----------+--------+
|     1 | guo jing | F      |
|     2 | yang guo | F      |
+-------+----------+--------+
2 rows in set (0.00 sec)  

打印出classid等于NULL的几行 （记住NULL必须要用is用=号是不行的）
MariaDB [zhr]> SELECT * FROM tb7 WHERE classid IS NULL;
+-------+--------+----------+-----------+---------+
| stuid | gender | name     | brithdate | classid |
+-------+--------+----------+-----------+---------+
|     2 | F      | yang guo | NULL      |    NULL |
+-------+--------+----------+-----------+---------+
同理打印出classid不等于id为空的几行  
MariaDB [zhr]> SELECT * FROM tb7 WHERE classid IS NOT NULL;
+-------+--------+------------+------------+---------+
| stuid | gender | name       | brithdate  | classid |
+-------+--------+------------+------------+---------+
|     1 | F      | guo jing   | 1997-06-28 |       3 |
|     3 | M      | zhang wuji | 1996-11-25 |       5 |
+-------+--------+------------+------------+---------+
2 rows in set (0.00 sec)  

我们也可以做列表比较，也就是说匹配列表里面的字符或者数字 ，我们的需求是打印出stuid为2，3的同学的名字  
MariaDB [zhr]> SELECT name FROM tb7  WHERE stuid  IN (2,3);        
+------------+
| name       |
+------------+
| yang guo   |
| zhang wuji |
+------------+


我们也可以用通配符或者正则表达式去匹配行 ，比如我们匹配以名字以y开头的行 （注意使用通配符要用LIKE而且mariadb中通配符%代表所有和*一样） 
MariaDB [zhr]> SELECT * FROM tb7 WHERE name LIKE 'y%';
+-------+--------+----------+-----------+---------+
| stuid | gender | name     | brithdate | classid |
+-------+--------+----------+-----------+---------+
|     2 | F      | yang guo | NULL      |    NULL |
+-------+--------+----------+-----------+---------+ 
我们也可以使用正则表达式去匹配，但是使用正则表达式我们就要用RLIKE  
MariaDB [zhr]> SELECT * FROM tb7 WHERE name RLIKE '^y.*$'; #name以y开头，以.*任意字符结尾
+-------+--------+----------+-----------+---------+
| stuid | gender | name     | brithdate | classid |
+-------+--------+----------+-----------+---------+
|     2 | F      | yang guo | NULL      |    NULL |
+-------+--------+----------+-----------+---------+

我们也可以根据某一项进行行排序使用ORDER BY，比如我们以名字进行排序  
MariaDB [zhr]> SELECT * FROM tb7 ORDER BY name;	#因为是升序而且本来就是这个顺序，
+-------+--------+------------+------------+---------+
| stuid | gender | name       | brithdate  | classid |
+-------+--------+------------+------------+---------+
|     1 | F      | guo jing   | 1997-06-28 |       3 |
|     2 | F      | yang guo   | NULL       |    NULL |
|     3 | M      | zhang wuji | 1996-11-25 |       5 |
+-------+--------+------------+------------+---------+  
我们来降序  
MariaDB [zhr]> SELECT * FROM tb7 ORDER BY name DESC;
+-------+--------+------------+------------+---------+
| stuid | gender | name       | brithdate  | classid |
+-------+--------+------------+------------+---------+
|     3 | M      | zhang wuji | 1996-11-25 |       5 |
|     2 | F      | yang guo   | NULL       |    NULL |
|     1 | F      | guo jing   | 1997-06-28 |       3 |
+-------+--------+------------+------------+---------+

我们也可以根据某一项中的数据进行分组(GROUP BY)，其中GROUP BY就是为了聚合，GROUP BY后加上某一项，并且将这一项根据数据重合分组比如我们的gender有3个其中2个F一个M我们就分成了2组，然后再由聚合函数进行计算，我们下面的聚合函数用的是count就是计数的，我们因为group by根据gender分成了2组我们再用count(gender)对gender的2组进行技术
  MariaDB [zhr]> SELECT count(gender),gender FROM tb7 GROUP BY gender;  #，count（gender是对gender进行计数，因为我们开始因为group by将gender分成了2组所以我们就计算这2组的数目）count输出的时候也会输出count我们可以用AS去起一个别名让他好看一些  
+----------+--------+
| count(gender) | gender |
+----------+--------+
|        2 | F      |
|        1 | M      |
+----------+--------+

其中我们的聚合有count()做计数，sum()求和,avg()做平均，max()取最大，min()取最小 
MariaDB [zhr]> SELECT sum(classid) FROM tb7 ; 	取出所有人的classid的和
+--------------+
| sum(classid) |
+--------------+
| 8            |
+--------------+ 

MariaDB [zhr]> SELECT max(stuid),name,gender FROM tb7 GROUP BY gender; #这个sql语句的意思是我们根据gender进行分组并且将他根据数据的重叠分成2组，然后我们再用聚合函数算出这2组中最大的stuid
+------------+------------+--------+
| max(stuid) | name       | gender |
+------------+------------+--------+
|          2 | guo jing   | F      |
|          3 | zhang wuji | M      |
+------------+------------+--------+

聚合完成后我们可以用HAVING做过滤

我们也可以加上条件判断 AND 后者 OR还可以取反NOT具体如下   
MariaDB [test1]>  SELECT * FROM tb7 WHERE stuid > 1 AND gender = 'M';	//此语句的意思为查找表tb7中的所有项，并且只打印出stuid大于1，还有gender等于M的项，   
+-------+--------+------+-----------+---------+
| stuid | gender | name | brithdate | classid |
+-------+--------+------+-----------+---------+
|     3 | M      | xxx  | NULL      |    NULL |
+-------+--------+------+-----------+---------+
1 row in set (0.00 sec)

MariaDB [test1]> SELECT * FROM tb7 WHERE stuid > 1 OR gender = 'M';   //此语句的意思为查找表tb7中的所有项，并且只打印出stuid大于1，或者gender等于M的项，   
+-------+--------+------+-----------+---------+
| stuid | gender | name | brithdate | classid |
+-------+--------+------+-----------+---------+
|     2 | F      | ZZZ  | NULL      |    NULL |
|     3 | M      | xxx  | NULL      |    NULL |
+-------+--------+------+-----------+---------+
2 rows in set (0.00 sec)

MariaDB [test1]> SELECT * FROM tb7 WHERE NOT gender = 'F';        //not为取反就是打印出gender不等于F的行  
+-------+--------+------+-----------+---------+
| stuid | gender | name | brithdate | classid |
+-------+--------+------+-----------+---------+
|     3 | M      | xxx  | NULL      |    NULL |
+-------+--------+------+-----------+---------+
1 row in set (0.00 sec)

DELECT（删除）

他只能删除行不能删除字段，注意必须要where或者limit（limit是将操作限制在某一个范围内），不然就是删除整表

MariaDB [test1]> DELETE  FROM tb7 WHERE name LIKE 'P%'; 	#删除以P开头的那一行
Query OK, 1 row affected (0.05 sec)

UPDATE(更新)

UPDATE后直接指定表名，如果不加where则代表改全部，set代表更改哪一项

MariaDB [test1]> UPDATE  tb7 SET classid=999 WHERE stuid=8; //更改表tb7的classid为999，并且此操作在stuid等于8的哪一行上执行  
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0


MariaDB [test1]> SELECT * FROM tb7;	//更改完成
+-------+--------+------+------------+---------+
| stuid | gender | name | brithdate  | classid |
+-------+--------+------+------------+---------+
|     1 | F      | YHX  | 1996-01-01 |       3 |
|     2 | F      | ZZZ  | NULL       |    NULL |
|     3 | M      | xxx  | NULL       |    NULL |
|     4 | F      | ddd  | NULL       |       2 |
|     5 | M      | SSS  | NULL       |       2 |
|     6 | F      | RRR  | NULL       |       5 |
|     7 | F      | QQQ  | NULL       |     999 |
+-------+--------+------+------------+---------+
7 rows in set (0.00 sec)

mariadb权限管理

mariadb主要对2个权限进行审查，一个是user，一个是host，user权限审查通过后，还要看这个user有没有权限在这个host上登陆
我们mariadb在配置权限的时候会写上’tom’@'localhost’或者’tom‘@’172.16.%。%‘
前者tom表示mariadb内置的用户，后面的localhost或者ip代表我们的这个用户可以在那台主机上登陆我们的mariadb服务器，%代表匹配所有

注意我们开始说过mariadb连接时候会默认反解析域名，比如我们tom@172.16.%.%,我们的tom在172.16.0.1上登陆mariadb服务器，但是mariadb服务器给172.16.0.1反向解析成 zhr.noob.com（假如），此时我们的访问就成了 tom@zhr.noob.com，他是不匹配tom@172.16.%.%,所以建议一开始就去掉反向解析

mysql数据库的权限存储于各个表种，以user为中心的授权放在user表中，以db为中心的授权放在db表中，以host为中心的授权放在host表中
tables_priv:表级别的权限
column_priv:行级别的权限
procs_priv:程序级别的权限
proxies_priv:代理级别的权限

mariadb权限级别

库级别

表级别

字段管理级别

管理类权限

程序类权限

mariadb账号创建

MariaDB [test1]> CREATE USER 'zhr'@'192.168.2.%' IDENTIFIED BY '000000';
Query OK, 0 rows affected (0.01 sec)
//创建一个账号zhr，只允许他在192.168.2.%主机上登陆mariadb 服务器，然后使用的密码为000000

我们用另一台主机192.168.2.89（mariadb server ip 为192.168.2.88）

[root@owncloud ~]# mysql -uzhr -p000000  -h 192.168.2.88	//h指定主机名
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.2.14-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

使用CURRENT_USER函数查看当前的用户

MariaDB [(none)]> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| zhr@localhost  |
+----------------+
1 row in set (0.000 sec)

MariaDB [(none)]>

我们也可以使用grant命令去创建用户，因为GRANT授权一个用户发现用户不存在则自动创建用户

我们可以rename一个user

MariaDB [(none)]> RENAME USER 'zhr'@'localhost' to 'zhrr'@'localhost';
Query OK, 0 rows affected (0.009 sec)

我们也可以DROP删除一个用户（delete是删除一行数据）

MariaDB [(none)]> DROP USER 'zhrr'@'localhost';
Query OK, 0 rows affected (0.009 sec)

MariaDB [(none)]>

再查看mysql数据库的user表发现没有相应的用户了

MariaDB [mysql]> SELECT * FROM user WHERE User = 'zhrr';
Empty set (0.011 sec)

如果我们直接改的授权表需要输入

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.000 sec)

update则不用
我们可以修改用户的密码,先创建一个用户

MariaDB [(none)]> CREATE USER 'zhr'@'123456';
Query OK, 0 rows affected (0.009 sec)

MariaDB [(none)]>

修改密码有3种方式
第一种
直接sql语言进行更改

MariaDB [(none)]> SET PASSWORD FOR 'zhr'@'localhost' = PASSWORD('000000');
Query OK, 0 rows affected (0.010 sec)
//PASSWORD是mysql一个内置的hash函数专门用于设置密码，如果我们直接设置设置的hash后的值

第二种
直接更改mysql.user这个表的项，我们先看一下这个表的结构

MariaDB [(none)]> DESC mysql.user;
+------------------------+-----------------------------------+------+-----+----------+-------+
| Field                  | Type                              | Null | Key | Default  | Extra |
+------------------------+-----------------------------------+------+-----+----------+-------+
| Host                   | char(60)                          | NO   | PRI |          |       |
| User                   | char(80)                          | NO   | PRI |          |       |
| Password               | char(41)                          | NO   |     |          |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N        |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N        |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N        |       |
| File_priv              | enum('N','Y')                     | NO   |     | N        |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N        |       |
| References_priv        | enum('N','Y')                     | NO   |     | N        |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N        |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N        |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N        |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N        |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N        |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N        |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N        |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N        |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N        |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N        |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N        |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N        |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N        |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N        |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N        |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N        |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N        |       |
| Delete_history_priv    | enum('N','Y')                     | NO   |     | N        |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |          |       |
| ssl_cipher             | blob                              | NO   |     | NULL     |       |
| x509_issuer            | blob                              | NO   |     | NULL     |       |
| x509_subject           | blob                              | NO   |     | NULL     |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0        |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0        |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0        |       |
| max_user_connections   | int(11)                           | NO   |     | 0        |       |
| plugin                 | char(64)                          | NO   |     |          |       |
| authentication_string  | text                              | NO   |     | NULL     |       |
| password_expired       | enum('N','Y')                     | NO   |     | N        |       |
| is_role                | enum('N','Y')                     | NO   |     | N        |       |
| default_role           | char(80)                          | NO   |     |          |       |
| max_statement_time     | decimal(12,6)                     | NO   |     | 0.000000 |       |
+------------------------+-----------------------------------+------+-----+----------+-------+
47 rows in set (0.010 sec)

key还是非常多的，我们看第二项key就是password里面就是密码，我们直接update更改

MariaDB [(none)]> UPDATE mysql.user SET Password = PASSWORD("000000") WHERE User = 'zhr';
Query OK, 0 rows affected (0.010 sec)
Rows matched: 1  Changed: 0  Warnings: 0

因为我们是直接改的表(而不是命令修改)他不会立即生效，我们要flush以下

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.000 sec)

第三种
使用mysql自己的远程客户端执行命令

mysqladmin -uroot -p000000 -hlocalhost flush-privileges

我们也可以使用mysql命令直接执行sql语句

mysql -uroot -p000000 -hlocalhost -e 'SQL'
-e后面就直接加上sql语句即可

万一我们的mysql密码忘记了怎么办？不是我们主动改密码

先将mysql服务下线
启动mysql进程的时候（在mariadb.server的启动文件种找到execstart一项这个专门用来添加启动参数的）加上--skip-grant-tables（跳过授权表，一旦跳过mysql对你无限制所言）--skip-networking（禁止远程网络登陆，因为我们已经跳过了授权表任何人都有root权限，一旦别人网络登录，mysql将非常危险），然后我们读取这个文件systemctl daemon-reload
再按照上面的方法修改密码即可（UPDATE不能SET因为没有加载授权表）
再按正常重启服务

GRANT
grant的用法就是将自身的某个权限授予某个用户,大体格式如下

GRANT 权限 ON 数据库对象 TO 用户

注意这里数据库对象默认是表，如果我们的对象是那个库的那个函数需要在数据库对象前面加上FUNCTION,如果对象是进程那么前面加上PROCEDURE
权限指的是DROP，SELECT等等
我们查看某个用户的权限

MariaDB [(none)]> SHOW GRANTS FOR 'zhr'@'localhost';
+------------------------------------------------------------------------------------------------------------+
| Grants for zhr@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `zhr`@`localhost` IDENTIFIED BY PASSWORD '*032197AE5731D4664921A6CCAC7CFCE6A0698693' |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.009 sec)

这个用户只能看1个库，还不能创建如下

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.000 sec)


MariaDB [(none)]> CREATE DATABASE text;
ERROR 1044 (42000): Access denied for user 'zhr'@'localhost' to database 'text'
MariaDB [(none)]>

我们登陆root给zhr这个用户一点权限，使其可以对mytest创建(虽然其不存在)

MariaDB [(none)]> GRANT CREATE ON mytest.* TO 'zhr'@'localhost';
Query OK, 0 rows affected (0.002 sec)

登陆zhr,创建数据库和表（表设置组件，自动增长）

MariaDB [(none)]> CREATE DATABASE mytest;
Query OK, 1 row affected (0.009 sec)

MariaDB [(none)]>
MariaDB [mytest]> CREATE TABLE tbl1  (id INT UNSIGNED AUTO_INCREMENT ,name VARCHAR(20),PRIMARY KEY(id));
Query OK, 0 rows affected (0.017 sec)

但是我们select就报错（因为只有CREATE没有SELECT权限）

MariaDB [mytest]> CREATE TABLE tbl1  (id INT UNSIGNED AUTO_INCREMENT ,name VARCHAR(20),PRIMARY KEY(id));
Query OK, 0 rows affected (0.017 sec)

回到root给他上select权限

MariaDB [(none)]> GRANT SELECT ON mytest.* TO 'zhr'@'localhost';
Query OK, 0 rows affected (0.000 sec)

再SELECT,就没有报错

MariaDB [(none)]> SELECT * FROM mytest.tbl1;
Empty set (0.010 sec)

MariaDB [(none)]>

我么不可以对某个key上权限，以下规定zhr用户只能对mytest的tbl1的int和name字段有insert权限

MariaDB [(none)]> GRANT INSERT(int,name) ON mytest.tbl1 TO 'zhr'@'localhost';
Query OK, 0 rows affected (0.000 sec)

honky_tonk_man

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql

文章目录简介数据模型结构化数据半结构化数据非结构化数据关系模型简介数据模型结构化数据存储的实体的属性就是结构化数据半结构化数据半结构化数据可以通过灵活的键值调整获取相应信息，且数据的格式不固定，如json，同一键值下存储的信息可能是数值型的，可能是文本型的，也可能是字典或者列表非结构化数据就是图片，mp3，视频等等不可以通过键获取其对应关系关系模型关系型数据库存储的...
复制链接

扫一扫