第1章 MySQL用户及权限介绍

1.1 MySQL用户的定义

MySQL中的用户是用户名+授权主机段，而不是单纯的用户名：

图片[1]|MySQL元数据知识介绍（六）|leon的博客

提示：相同用户名但不同主机名的用户是两个用户。

1.2 用户、权限、角色、权限范围

1.2.1 用户的作用

用户登录
用于管理数据库及数据

1.2.2 权限

对数据库的读、写等操作，如：insert、update、select等。

1.2.3 角色

数据库定义好的一组权限的定义，如：all privileges、replication、slave等

1.2.4 权限范围

全库级别： *.*
单库级别：*
单表级别：t1

第2章字符集

2.1 字符集介绍

字符集（Charset）是一个系统支持的所有抽象字符的集合。字符是各种文字和符号的总称，包括各国家文字、标点符号、图形符号、数字等。

图片[2]|MySQL元数据知识介绍（六）|leon的博客

2.2 MySQL数据库的字符集

MySQL数据库的字符集合包括字符集（CHARACTER）和校对规则（COLLATION）。MySQL中常见的字符集有：UTF8、LATIN1、GBK。常见校对规则有：ci（大小写不敏感）、cs或bin（大小写敏感）。

2.2.1 查看字符集和校对规则

mysql> 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 |
...省略部分输出内容...
40 rows in set (0.00 sec)

mysql> show collation;
+--------------------------+----------+-----+---------+----------+---------+
| Collation                | Charset  | Id  | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci          | big5     |   1 | Yes     | Yes      |       1 |
| big5_bin                 | big5     |  84 |         | Yes      |       1 |
| dec8_swedish_ci          | dec8     |   3 | Yes     | Yes      |       1 |
...省略部分输出内容...
219 rows in set (0.00 sec)

2.3 指定字符集

提示：二进制安装MySQL时，默认字符集是拉丁字符集，要在创建数据库时指定字符集或在配置文件中指定要使用的字符集。

2.3.1 MySQL实例级别

2.3.1.1 编译安装时指定

cmake .
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \

2.3.1.2 在配置文件中指定

[mysqld]
character-set-server=utf8

2.3.2 数据库中的库级别

CREATE DATABASE `leon` /*!40100 DEFAULT CHARACTER SET utf8 */
create database leon DEFAULT CHARACTER SET UTF8 DEFAULT COLLATE = utf8_general_ci;

2.3.3 表级别（含字段级别）

CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8

2.3.4 MySQL客户端级别（连接及返回结果）

2.3.4.1 临时生效单条命令法

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

2.3.4.2 通过修改my.cnf实现修改mysql客户端的字符集

[client]
default-character-set=utf8

2.4 生产环境更改数据库（含数据）字符集的方法

alter database leon CHARACTER SET utf8 collate utf8_general_ci;
alter table t1 CHARACTER SET latin1;

提示：注意：更改字符集时，一定要保证由小往大改，后者必须是前者的严格超集，生产中不要随便更改。

第3章 Informatica_schema获取元数据

3.1 元数据访问方法

INFORMATION_SCHEMA数据库表其中包含 MySQL 数据库服务器所管理的所有对象的相关数据：

使用“SHOW”语句获取数据库和表信息
使用“DESCRIBE（或 DESC）”语句检查表结构和列属性
使用mysqlshow客户端程序进行命令行操作

3.2 INFORMATION_SCHEMA 数据库介绍

INFORMATION_SCHEMA数据库充当数据库元数据的中央系统信息库，其中包括模式和模式对象以及服务器统计信息（状态变量、设置、连接）。INFORMATION_SCHEMA数据库采用表格式实现灵活的访问（可以使用任意select语句）。它本身是“虚拟数据库”，其中的表并非“真实”表（基表），而是“系统视图”，可以根据当前用户的特权动态填充表。

3.2.1 INFORMATION_SCHEMA 表

3.2.1.1 列出 INFORMATION_SCHEMA 数据库中所有的表

mysql> use information_schema;
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
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
...省略部分输出内容...
59 rows in set (0.00 sec)

3.2.1.2 查询INFORMATION_SCHEMA 数据库

指定要检索哪个表和哪些列
通过使用 WHERE 子句，可仅检索特定条件
对结果分组或排序
使用 JOIN、UNION 和子查询
将结果检索到其他表中
基于INFORMATION_SCHEMA表创建视图

【示例】：

mysql> SELECT TABLE_NAME, ENGINE
    -> FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_SCHEMA = 'leon';
+------------+--------+
| TABLE_NAME | ENGINE |
+------------+--------+
| test       | InnoDB |
+------------+--------+
1 row in set (0.00 sec)

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
    -> FROM   INFORMATION_SCHEMA.COLUMNS
    -> WHERE  DATA_TYPE = 'set';
+--------------+--------------+-------------+
| TABLE_SCHEMA | TABLE_NAME   | COLUMN_NAME |
+--------------+--------------+-------------+
| mysql        | columns_priv | Column_priv |
| mysql        | event        | sql_mode    |
| mysql        | proc         | sql_mode    |
| mysql        | procs_priv   | Proc_priv   |
| mysql        | tables_priv  | Table_priv  |
| mysql        | tables_priv  | Column_priv |
+--------------+--------------+-------------+
6 rows in set (0.01 sec)

mysql> SELECT CHARACTER_SET_NAME, COLLATION_NAME
    -> FROM   INFORMATION_SCHEMA.COLLATIONS
    -> WHERE  IS_DEFAULT = 'Yes';
+--------------------+---------------------+
| CHARACTER_SET_NAME | COLLATION_NAME      |
+--------------------+---------------------+
| big5               | big5_chinese_ci     |
| dec8               | dec8_swedish_ci     |
| cp850              | cp850_general_ci    |
| hp8                | hp8_english_ci      |
| koi8r              | koi8r_general_ci    |
| latin1             | latin1_swedish_ci   |
| latin2             | latin2_general_ci   |
| swe7               | swe7_swedish_ci     |
| ascii              | ascii_general_ci    |
| ujis               | ujis_japanese_ci    |
| sjis               | sjis_japanese_ci    |
| hebrew             | hebrew_general_ci   |
| tis620             | tis620_thai_ci      |
| euckr              | euckr_korean_ci     |
| koi8u              | koi8u_general_ci    |
| gb2312             | gb2312_chinese_ci   |
| greek              | greek_general_ci    |
| cp1250             | cp1250_general_ci   |
| gbk                | gbk_chinese_ci      |
| latin5             | latin5_turkish_ci   |
| armscii8           | armscii8_general_ci |
| utf8               | utf8_general_ci     |
| ucs2               | ucs2_general_ci     |
| cp866              | cp866_general_ci    |
| keybcs2            | keybcs2_general_ci  |
| macce              | macce_general_ci    |
| macroman           | macroman_general_ci |
| cp852              | cp852_general_ci    |
| latin7             | latin7_general_ci   |
| utf8mb4            | utf8mb4_general_ci  |
| cp1251             | cp1251_general_ci   |
| utf16              | utf16_general_ci    |
| utf16le            | utf16le_general_ci  |
| cp1256             | cp1256_general_ci   |
| cp1257             | cp1257_general_ci   |
| utf32              | utf32_general_ci    |
| binary             | binary              |
| geostd8            | geostd8_general_ci  |
| cp932              | cp932_japanese_ci   |
| eucjpms            | eucjpms_japanese_ci |
+--------------------+---------------------+
40 rows in set (1.42 sec)

mysql> SELECT TABLE_SCHEMA, COUNT(*)
    -> FROM   INFORMATION_SCHEMA.TABLES
    -> GROUP BY TABLE_SCHEMA;
+--------------------+----------+
| TABLE_SCHEMA       | COUNT(*) |
+--------------------+----------+
| information_schema |       59 |
| leon               |        1 |
| mysql              |       28 |
| performance_schema |       52 |
| world              |        3 |
+--------------------+----------+
5 rows in set (0.01 sec)

删除时报错

mysql> DELETE FROM INFORMATION_SCHEMA.VIEWS;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

3.2.2 使用INFORMATION_SCHEMA表创建Shell命令

3.2.2.1 使用 INFORMATION_SCHEMA 表获取有关创建 shell 命令的信息

将 SELECT 和 CONCAT 一起使用以创建 mysqldump 脚本：

mysql> SELECT CONCAT("mysqldump -uroot -p ",
    -> TABLE_SCHEMA," ", TABLE_NAME, " >> ",
    -> TABLE_SCHEMA,".bak.sql")
    -> FROM TABLES WHERE TABLE_NAME LIKE 'Country%';
+-----------------------------------------------------------------------------------------------+
| CONCAT("mysqldump -uroot -p ",
TABLE_SCHEMA," ", TABLE_NAME, " >> ",
TABLE_SCHEMA,".bak.sql") |
+-----------------------------------------------------------------------------------------------+
| mysqldump -uroot -p world country >> world.bak.sql                                            |
| mysqldump -uroot -p world countrylanguage >> world.bak.sql                                    |
+-----------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

3.2.2.2 使用 mysql 命令创建 SQL 语句

使用-e选项输入SELECT/CONCAT语句：

[root@db02 ~]# mysql -uroot -p --silent --skip-column-names -e "SELECT CONCAT('CREATE TABLE ', TABLE_SCHEMA, '.',TABLE_NAME, '_backup LIKE ', TABLE_SCHEMA, '.',TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'world';"
Enter password:
CREATE TABLE world.city_backup LIKE world.city;
CREATE TABLE world.country_backup LIKE world.country;
CREATE TABLE world.countrylanguage_backup LIKE world.countrylanguage;

3.3 MySQL中的show语句

3.3.1 SHOW语句单独使用

3.3.1.1 SOHW databases：列出所有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| leon               |
| mysql              |
| performance_schema |
| world              |
+--------------------+
5 rows in set (0.00 sec)

3.3.1.2 SHOW TABLES：列出默认数据库中的表

mysql> use leon;
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
mysql> show tables;
+----------------+
| Tables_in_leon |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

3.3.1.3 SHOW TABLES FROM <database_name>：列出指定数据库中的表

mysql> show tables from leon;
+----------------+
| Tables_in_leon |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

3.3.1.4 SHOW COLUMNS FROM <table_name>：显示表的列结构

mysql> show columns from test;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | int(4)     | NO   | PRI | NULL    | auto_increment |
| name  | char(20)   | NO   |     | NULL    |                |
| state | tinyint(2) | NO   |     | 1       |                |
+-------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

3.3.1.5 SHOW INDEX FROM <table_name>：显示表中有关索引和索引列的信息

mysql> show index from test\G;
*************************** 1. row ***************************
        Table: test
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

3.3.1.6 SHOW CHARACTER SET：显示可用的字符集及其默认整理

mysql> 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 |
...省略部分输出内容...
40 rows in set (0.00 sec)

3.3.1.7 SHOW COLLATION：显示每个字符集的整理

mysql> show collation;
+--------------------------+----------+-----+---------+----------+---------+
| Collation                | Charset  | Id  | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci          | big5     |   1 | Yes     | Yes      |       1 |
| big5_bin                 | big5     |  84 |         | Yes      |       1 |
| dec8_swedish_ci          | dec8     |   3 | Yes     | Yes      |       1 |
...省略部分输出内容...
219 rows in set (0.00 sec)

3.3.1.8 SHOW STATUS：列出当前数据库状态

mysql> show status;
+-----------------------------------------------+-------------+
| Variable_name                                 | Value       |
+-----------------------------------------------+-------------+
| Aborted_clients                               | 0           |
| Aborted_connects                              | 0           |
| Binlog_cache_disk_use                         | 0           |
...省略部分输出内容...
341 rows in set (0.00 sec)

常用用法：

show (global) stutus  like '%Innodb%';     # 查看当前数据库的整体状态

3.3.1.9 SHOW VARIABLES：列出数据库中的参数定义值

mysql> show variables;
+--------------------------------------------+--------------------------------+
| Variable_name                              | Value                          |
+--------------------------------------------+--------------------------------+
| auto_increment_increment                   | 1                              |
| auto_increment_offset                      | 1                              |
| autocommit                                 | ON                             |
...省略部分输出内容...                                                  
453 rows in set (0.00 sec)

常用用法：

show (global) variables like '%log%';      # 查看当前数据库的参数配置

3.3.2 SHOW语句与子句一起使用

3.3.2.1 Show语句与from一起使用：

mysql> SHOW TABLES FROM mysql;
mysql> SHOW TABLES FROM INFORMATION_SCHEMA;
mysql> SHOW COLUMNS FROM CountryLanguage;
mysql> SHOW FULL COLUMNS FROM CountryLanguage\G

3.3.2.2 SHOW 与 LIKE 和 WHERE 一起使用：

mysql> SHOW DATABASES LIKE 'm%';
mysql> SHOW COLUMNS FROM Country
-> WHERE `Default` IS NULL;

3.4 DESCRIBE 语句

DESCRIBE（DESC）语句等效于SHOW COLUMNS。

【语法】：

DESCRIBE <table_name>;

【示例】：

mysql> desc information_schema.character_sets;
+----------------------+-------------+------+-----+---------+-------+
| Field                | Type        | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+-------+
| CHARACTER_SET_NAME   | varchar(32) | NO   |     |         |       |
| DEFAULT_COLLATE_NAME | varchar(32) | NO   |     |         |       |
| DESCRIPTION          | varchar(60) | NO   |     |         |       |
| MAXLEN               | bigint(3)   | NO   |     | 0       |       |
+----------------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

3.5 mysqlshow客户端程序

可以使用mysqlshow在命令行查询有关数据库和表的结构的信息，用法与 SHOW 语句相似：

mysqlshow [options] [db_name [table_name [column_name]]]

3.5.1 查询mysqlshow帮助

[root@db02 ~]# mysqlshow --help

3.5.2 显示所有数据库或特定数据库、表和/或列的相关信息

[root@db02 ~]# mysqlshow -uroot -p12345678
Warning: Using a password on the command line interface can be insecure.
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| leon               |
| mysql              |
| performance_schema |
| world              |
+--------------------+
[root@db02 ~]# mysqlshow -uroot -p12345678 world
Warning: Using a password on the command line interface can be insecure.
Database: world
+-----------------+
|     Tables      |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
[root@db02 ~]# mysqlshow -uroot -p12345678 world city
[root@db02 ~]# mysqlshow -uroot -p12345678 world city CountryCode
[root@db02 ~]# mysqlshow -uroot -p12345678 "w%"
Warning: Using a password on the command line interface can be insecure.
Wildcard: w%
+-----------+
| Databases |
+-----------+
| world     |
+-----------+

温馨提示：本文最后更新于2022-12-20 20:57:52，已超过490天没有更新。某些文章具有时效性，若文章内容或图片资源有错误或已失效，请联系站长。谢谢！
转载请注明本文链接：https://blog.leonshadow.cn/763482/654.html

1. 本网站名称：Leon的博客
2. 本站永久网址：https://blog.leonshadow.cn
3. 本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长QQ632113590进行删除处理。
4. 本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5. 本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报
6. 本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END