技術文章 » Mariadb基礎

Mariadb基礎

2020-10-21 17:00:51

Mariadb基礎

關係型資料庫介紹

資料結構模型

  • 資料結構模型主要有：
    • 層次模型
    • 網狀模型
    • 關係模型
      • 二維關係：row（行），column（列）
  • 資料庫管理系統：DBMS（DataBase Management System）
  • 關係型資料庫管理系統：RDBMS（Relational DataBase Management System）

注： 資料庫管理系統是由DateBase服務+DateBase+table+row、column組成，這些東西可以存在多個，而不是隻能存在一個

RDBMS專業名詞

  • 常見的關係型資料庫管理系統

    • MySQL：MySQL，MariaDB，Percona-Server
    • PostgreSQL：簡稱為pgsql
    • Oracle
    • MSSQL：Windows平臺的資料庫

  • SQL：Structure Query Language，結構化查詢語言

  • 約束：constraint，向資料表提供的資料要遵守的限制

    • 主鍵約束：一個或多個欄位的組合，填入的資料必須能在本表中唯一標識本行。且必須提供資料，不能為空（NOT NULL）。
      • 一個表中只能存在一個
    • 惟一鍵約束：一個或多個欄位的組合，填入的資料必須能在本表中唯一標識本行。允許為空（NULL）
      • 一個表可以存在多個
    • 外來鍵約束：一個表中的某欄位可填入資料取決於另一個表的主鍵已有的資料
    • 檢查性約束

  • 索引：將表中的一個或多個欄位中的資料複製一份另存，並且這些資料需要按特定次序排序儲存

關係型資料庫的常見元件

  • 資料庫：database
  • 表：table，由row和列column組成
  • 索引：index
  • 檢視：view
  • 使用者：user
  • 許可權：privilege
  • 儲存過程：procedure
  • 儲存函數：function
  • 觸發器：trigger
  • 事件排程器：event scheduler

SQL語句

  • SQL語句分為三類
    • DDL：Data Defination Language，資料定義語言
    • DML：Data Manipulation Language，資料操縱語言
    • DCL：Data Control Language，資料控制語言
SQL語句型別對應操作
DDLCREATE：建立
DROP：刪除
ALTER：修改
DMLINSERT：向表中插入資料
DELETE：刪除表中資料
UPDATE：更新表中資料
SELECT：查詢表中資料
DCLGRANT：授權
REVOKE：移除授權

MariaDB安裝與設定

MariaDB安裝

  • MariaDB和MySQL操作語法是通用的，但是現在MySQL是收費的，所以這裡我們安裝MariaDB
  • MariaDB安裝的方式有三種：
    • 原始碼：編譯安裝
    • 二進位制格式的程式包：展開至特定路徑，並經過簡單設定後即可使用
    • 程式包管理器管理的程式包
//在Redhat 8中本地源中已經存在mariadb安裝包，所以可以直接使用yum命令安裝
[root@server ~]# yum -y install mariadb mariadb-common mariadb-devel mariadb-server
  • 在Redhat 7 中要安裝MySQL
#設定mysql的yum源
wget -O /usr/src/mysql57-community-release-el7-10.noarch.rpm \
http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
rpm -Uvh /usr/src/mysql57-community-release-el7-10.noarch.rpm

#安裝mysql5.7
yum -y install mysql-community-server mysql-community-client  \
mysql-community-common mysql-community-devel

Mariadb設定

//啟動資料庫
[root@server ~]# systemctl enable --now mariadb

//確保3306埠已經處於監聽狀態
[root@server ~]# ss -antl|grep 3306
LISTEN   0         80                        *:3306                   *:*       

//登入mariadb資料庫
[root@server ~]# mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.17-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)]> 

//修改Mariadb登入密碼
MariaDB [(none)]> set password = password('redhat123+');

//重新使用密碼登，以下兩種方法都可以登入
[root@server ~]# mysql -uroot -p'redhat123+'
[root@server ~]# mysql -uroot -p 
Enter password:

Mariadb的程式組成

  • 使用者端
    • mysql：CLI互動式使用者端程式
    • mysql_secure_installation：安全初始化
    • mysqldump：mysql備份工具
    • mysqladmin：mysql備份工具
  • 伺服器端
    • Mariadb

Mariadb工具使用

  • 語法：mysql [OPTIONS] [database]

  • 常用選項：

    • -uUSERNAME #指定使用者名稱，預設為root
    • -hHOST #指定伺服器主機，預設為localhost，推薦使用ip地址
    • -pPASSWORD #指定使用者的密碼
    [root@server ~]# mysql -uroot -p'redhat123+' -h127.0.0.1 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.3.17-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)]>
    • -P# #指定資料庫監聽的埠，這裡的#需用實際的埠號代替，如-P3307
    • -V #檢視當前使用的mysql版本
    [root@server ~]# mysql -V
mysql  Ver 15.1 Distrib 10.3.17-MariaDB, for Linux (x86_64) using readline 5.1
    • -e #不登入mysql執行sql語句後退出，常用於指令碼
    [root@server ~]# mysql -uroot -p'redhat123+' -e 'SHOW DATABASES;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

伺服器端監聽的兩種socket地址

socket說明
IP socket預設監聽在tcp的3306埠，支援遠端通訊
Unix socket監聽在sock檔案上（/tmp/mysql.sock，/var/lib/mysql/mysql.sock）
僅支援本地通訊
server地址只能是：localhost，127.0.0.1

Mariadb資料庫操作

DDL操作

資料庫操作

  • 建立資料庫
//語法：CREATE DATABASE [IF NOT EXISTS] 'DB_NAME';
MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS CWT;                       #如果CWT資料庫不存在則建立CWT資料庫，這樣做的好處是，如果要建立的資料庫存在也不會報錯
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| CWT                |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
  • 刪除資料庫
//語法：DROP DATABASE [IF EXISTS] 'DB_NAME';
MariaDB [(none)]> DROP DATABASE IF EXISTS CWT;						      #如果CWT資料庫存在則刪除它，這樣做的好處是，如果要刪除的資料庫不存在也不會報錯
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)

表操作

  • 建立表
//語法：CREATE TABLE table_name (col1 datatype 修飾符,col2 datatype 修飾符) ENGINE='儲存引擎型別';
MariaDB [(none)]> CREATE DATABASE XX_DATA;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| XX_DATA            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)

//進入資料庫
MariaDB [(none)]> USE XX_DATA
Database changed
MariaDB [XX_DATA]> SHOW TABLES;

//建立表
MariaDB [XX_DATA]> CREATE TABLE xx(id int not null,name varchar(10),age tinyint);
Query OK, 0 rows affected (0.004 sec)

//檢視表
MariaDB [XX_DATA]> SHOW TABLES;
+-------------------+
| Tables_in_XX_DATA |
+-------------------+
| xx                |
+-------------------+
1 row in set (0.001 sec)
  • 刪除表
MariaDB [XX_DATA]> DROP TABLE xx;
Query OK, 0 rows affected (0.003 sec)

MariaDB [XX_DATA]> SHOW TABLES;
Empty set (0.000 sec)

使用者操作

  • mysql使用者帳號由兩部分組成，如'USERNAME'@'HOST'，表示此USERNAME只能從此HOST上遠端登入，HOST的值可以為：
    • IP地址，如：192.168.86.132
    • 萬用字元
      • %：匹配任意長度的任意字元，常用於設定允許從任何主機登入
      • _：匹配任意單個字元

注： HOST應該為使用者端的IP地址

//建立用於登入資料庫的使用者
語法：CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];
MariaDB [(none)]> CREATE USER 'tom'@'192.168.86.132' IDENTIFIED BY 'redhat456+';
Query OK, 0 rows affected (0.000 sec)

//使用新建立的使用者和密碼在使用者端上登入
[root@client ~]# mysql -utom -p'redhat456+' -h192.168.86.129
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.3.17-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)]> 

//刪除資料庫使用者
語法：DROP USER 'username'@'host'; 
MariaDB [(none)]> DROP USER 'tom'@'192.168.86.132';
Query OK, 0 rows affected (0.000 sec)

檢視命令SHOW

  • 檢視支援的所有字元集（支援的語言）
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 |
...
  • 檢視當前資料庫支援的所有儲存引擎
MariaDB [(none)]> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                        | NO           | NO   | NO         |
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                   ...          

//上面那種方法輸出的內容比較難以閱讀，所以推薦使用以下這種方式
MariaDB [(none)]> SHOW ENGINES\G
*************************** 1. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MyISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
...
  • 檢視資料庫資訊
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| XX_DATA            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.116 sec)

//不進入某資料庫而列出其包含的所有表
MariaDB [(none)]> SHOW TABLES FROM XX_DATA;
Empty set (0.000 sec)
  • 檢視表結構
//語法：DESC [db_name.]table_name;
MariaDB [(none)]> DESC XX_DATA.xx;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(15) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

//如果已經進入的資料庫，則只需要DESC xx;就能檢視表結構了
  • 檢視某表的建立命令
//語法：CREATE TABLE table_name;
MariaDB [XX_DATA]> SHOW CREATE TABLE xx;
...
| Table | Create Table                                                                                                                                           |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| xx    | CREATE TABLE `xx` (
  `id` int(11) NOT NULL,
  `name` varchar(15) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
...
  • 檢視某表的狀態
//語法：SHOW TABLE STATUS LIKE 'table_name'\G
MariaDB [XX_DATA]> SHOW TABLE STATUS LIKE 'xx'\G			#支援萬用字元，如：SHOW TABLE STATUS LIKE 'xx%'\G
*************************** 1. row ***************************
            Name: xx
          Engine: InnoDB
         Version: 10
      Row_format: Dynamic
            Rows: 0
  Avg_row_length: 0
     Data_length: 16384
 Max_data_length: 0
...
  • 新增欄位
MariaDB [XX_DATA]> DESC xx;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(15) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.000 sec)

MariaDB [XX_DATA]> ALTER TABLE xx ADD score float;
Query OK, 0 rows affected (0.002 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [XX_DATA]> DESC xx;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(15) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
| score | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.000 sec)
  • 刪除欄位
MariaDB [XX_DATA]> DESC xx;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(15) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
| score | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.000 sec)

MariaDB [XX_DATA]> ALTER TABLE xx DROP score;
Query OK, 0 rows affected (0.134 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [XX_DATA]> DESC xx;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(15) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)
  • 修改欄位
MariaDB [XX_DATA]> DESC xx;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(15) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

MariaDB [XX_DATA]> ALTER TABLE xx MODIFY age tinyint not null;
Query OK, 0 rows affected (0.007 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [XX_DATA]> DESC xx;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(15) | YES  |     | NULL    |       |
| age   | tinyint(4)  | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.000 sec)

獲取幫助

  • 獲取命令使用幫助
//語法：HELP keyword;
MariaDB [XX_DATA]> HELP CREATE TABLE
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]
...

DML操作

  • DML操作包括增(INSERT)、刪(DELETE)、改(UPDATE)、查(SELECT)，均屬針對表的操作。

INSERT語句

//語法：INSERT [INTO] table_name [(column_name,...)] {VALUES | VALUE} (value1,...),(...),...

MariaDB [XX_DATA]> DESC xx;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(15) | YES  |     | NULL    |       |
| age   | tinyint(4)  | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

//插入一條資料
MariaDB [XX_DATA]> INSERT INTO xx VALUE(1,'cwt',18);
Query OK, 1 row affected (0.001 sec)

//插入多條資料
MariaDB [XX_DATA]> INSERT INTO xx VALUES (2,'tom',20),(3,'xx',25),(4,'lisi',20);
Query OK, 3 rows affected (0.001 sec)
Records: 3  Duplicates: 0  Warnings: 0

SELECT語句

MariaDB [XX_DATA]> SELECT * FROM xx;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | cwt  |  18 |
|  2 | tom  |  20 |
|  3 | xx   |  25 |
|  4 | lisi |  20 |
+----+------+-----+

UPDATE語句

//語法：UPDATE table_name SET column1 = new_value1[,column2 = new_value2,...] [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];

MariaDB [XX_DATA]> SELECT * FROM xx;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | cwt  |  18 |
|  2 | tom  |  20 |
|  3 | xx   |  25 |
|  4 | lisi |  20 |
+----+------+-----+
4 rows in set (0.000 sec)

MariaDB [XX_DATA]> UPDATE xx SET age = 21 where name = 'cwt';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [XX_DATA]> SELECT * FROM xx;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | cwt  |  21 |
|  2 | tom  |  20 |
|  3 | xx   |  25 |
|  4 | lisi |  20 |
+----+------+-----+
4 rows in set (0.000 sec)

DELETE語句

//語法：DELETE FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];

MariaDB [XX_DATA]> SELECT * FROM xx;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | cwt  |  21 |
|  2 | tom  |  20 |
|  3 | xx   |  25 |
|  4 | lisi |  20 |
+----+------+-----+
4 rows in set (0.000 sec)

//刪除表中的某一條記錄
MariaDB [XX_DATA]> DELETE FROM xx where name = 'xx';
Query OK, 1 row affected (0.002 sec)

MariaDB [XX_DATA]> SELECT * FROM xx;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | cwt  |  21 |
|  2 | tom  |  20 |
|  4 | lisi |  20 |
+----+------+-----+
3 rows in set (0.000 sec)

//刪除整張表的內容，但會保留表本身
MariaDB [XX_DATA]> DELETE FROM xx;
Query OK, 3 rows affected (0.001 sec)

MariaDB [XX_DATA]> SELECT * FROM xx;
Empty set (0.000 sec)

MariaDB [XX_DATA]> SHOW TABLES;
+-------------------+
| Tables_in_XX_DATA |
+-------------------+
| xx                |
+-------------------+
1 row in set (0.000 sec)

DCL操作

建立授權GRANT

  • 許可權型別（priv_type）
許可權型別作用
ALL所有許可權
SELECT讀取內容的許可權
INSERT插入內容的許可權
UPDATE更新內容的許可權
DELETE刪除內容的許可權
  • 指定要操作的物件db_name.table_name
表示方式意義
*.*所有庫的所有表
db_name指定庫的所有表
db_name.table_name指定庫的指定表

注： WITH GRANT OPTION：被授權的使用者可將自己的許可權副本轉贈給其他使用者（複製自己的許可權給另外一個使用者），不建議使用。

//語法：GRANT priv_type,... ON [object_type] db_name.table_name TO 'username'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| XX_DATA            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)


//授權tom使用者在192.168.86.132上遠端登入資料庫，且對XX_DATA資料庫有SELECT許可權
MariaDB [(none)]> GRANT SELECT ON XX_DATA.* TO 'tom'@'192.168.86.132' IDENTIFIED BY 'redhat456+';
Query OK, 0 rows affected (0.000 sec)

//重讀授權表
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.000 sec)

//遠端登入資料庫，並測試許可權是否正確設定
[root@client ~]# mysql -utom -p'redhat456+' -h192.168.86.129
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 10.3.17-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)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| XX_DATA            |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> USE XX_DATA
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [XX_DATA]> CREATE TABLE cwt(id int not null);
ERROR 1142 (42000): CREATE command denied to user 'tom'@'192.168.86.132' for table 'cwt'

檢視授權

  • 檢視當前登入使用者的授權資訊
MariaDB [(none)]> SHOW GRANTS;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*AACA4ED26B7AFC0A081A787B8C607B4262252DE5' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                           
...
  • 檢視指定使用者的授權資訊
MariaDB [(none)]> SHOW GRANTS FOR 'tom'@'192.168.86.132';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for tom@192.168.86.132                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'192.168.86.132' IDENTIFIED BY PASSWORD '*0AF6C9DF2CBB59D18182D5C4AFD2E069F93F7DA3' |
| GRANT SELECT ON `XX_DATA`.* TO 'tom'@'192.168.86.132'                                   
...

取消授權REVOKE

//語法：REVOKE priv_type,... ON db_name.table_name FROM 'username'@'host';

MariaDB [(none)]> REVOKE SELECT ON XX_DATA.* FROM 'tom'@'192.168.86.132';
Query OK, 0 rows affected (0.000 sec)


//重讀授權表
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.000 sec)


MariaDB [(none)]> SHOW GRANTS FOR tom@192.168.86.132;
+-----------------------------------------------------------------------------------------------------------------+
| Grants for tom@192.168.86.132                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'192.168.86.132' IDENTIFIED BY PASSWORD '*0AF6C9DF2CBB59D18182D5C4AFD2E069F93F7DA3' |
  • mysql服務程序啟動時會讀取mysql庫中的所有授權表至記憶體中：
    • GRANT或REVOKE等執行許可權操作會儲存於表中，mysql的服務程序會自動重讀授權表，並更新至記憶體中
    • 對於不能夠或不能及時重讀授權表的命令，可手動讓mysql的服務程序重讀授權表
//重讀授權表
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.000 sec)

範例

  • 建立一個資料庫，並建立一張表student，該表包含三個欄位（id，name，age），表結構如下：
mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
  • 解析
MariaDB [(none)]> CREATE DATABASE cwt;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| cwt                |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)


MariaDB [(none)]> USE cwt;
Database changed

MariaDB [cwt]> CREATE TABLE student(id int PRIMARY KEY auto_increment NOT NULL,name VARCHAR(100) NOT NULL,age tinyint);
Query OK, 0 rows affected (0.326 sec)

MariaDB [cwt]> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.003 sec)
  • 檢視下該新建的表有無內容（用select語句）
MariaDB [cwt]> SELECT * FROM student;
Empty set (0.000 sec)
  • 在新建的student表中插入資料（用insert語句），結果應如下所示：
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        | NULL |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
  • 解析
MariaDB [cwt]> INSERT INTO student VALUES (1,'tom',20),(2,'jerry',23),(3,'wangqing',25),(4,'sean',28),(5,'zhangshan',26),(6,'zhangshan',20),(7,'lisi',NULL),(8,'chenshou',10),(9,'wangwu',3),(10,'qiuyi',15),(11,'qiuxiaotian',20);
Query OK, 11 rows affected (0.001 sec)
Records: 11  Duplicates: 0  Warnings: 0

MariaDB [cwt]> SELECT * FROM student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        | NULL |
|  8 | chenshou    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.000 sec)
  • 修改lisi的年齡為50
MariaDB [cwt]> UPDATE student set age = 50 where name = 'lisi';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [cwt]> SELECT * FROM student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        |   50 |
|  8 | chenshou    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.000 sec)
  • 查詢student表中名字叫zhangshan的記錄
MariaDB [cwt]> SELECT * FROM student WHERE name = 'zhangshan';
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  5 | zhangshan |   26 |
|  6 | zhangshan |   20 |
+----+-----------+------+
2 rows in set (0.000 sec)
  • 查詢student表中名字叫zhangshan且年齡大於20歲的記錄
MariaDB [cwt]> SELECT * FROM student WHERE name = 'zhangshan' AND age > 20;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  5 | zhangshan |   26 |
+----+-----------+------+
1 row in set (0.000 sec)
  • 查詢student表中年齡在23到30之間的記錄
MariaDB [cwt]> SELECT * FROM student WHERE age BETWEEN 23 and 30;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  2 | jerry     |   23 |
|  3 | wangqing  |   25 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
+----+-----------+------+
4 rows in set (0.000 sec)
  • 修改wangwu的年齡為100
MariaDB [cwt]> UPDATE student set age = 100 where name = 'wangwu';
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [cwt]> SELECT * FROM student WHERE name = 'wangwu';
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  9 | wangwu |  100 |
+----+--------+------+
1 row in set (0.000 sec)
  • 刪除student中名字叫zhangshan且年齡小於等於20的記錄
MariaDB [cwt]> DELETE FROM student WHERE name = 'zhangshan' AND age <= 20;
Query OK, 1 row affected (0.001 sec)

MariaDB [cwt]> SELECT * FROM student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshou    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
10 rows in set (0.000 sec)