第1章 MySQL用户及权限介绍
1.1 MySQL用户的定义
MySQL中的用户是用户名+授权主机段，而不是单纯的用户名：
提示：相同用户名但不同主机名的用户是两个用户。
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.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语句：
[[email protected] ~]# 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帮助
[[email protected] ~]# mysqlshow --help
3.5.2 显示所有数据库或特定数据库、表和/或列的相关信息
[[email protected] ~]# mysqlshow -uroot -p12345678 Warning: Using a password on the command line interface can be insecure. +--------------------+ | Databases | +--------------------+ | information_schema | | leon | | mysql | | performance_schema | | world | +--------------------+ [[email protected] ~]# mysqlshow -uroot -p12345678 world Warning: Using a password on the command line interface can be insecure. Database: world +-----------------+ | Tables | +-----------------+ | city | | country | | countrylanguage | +-----------------+ [[email protected] ~]# mysqlshow -uroot -p12345678 world city [[email protected] ~]# mysqlshow -uroot -p12345678 world city CountryCode [[email protected] ~]# mysqlshow -uroot -p12345678 "w%" Warning: Using a password on the command line interface can be insecure. Wildcard: w% +-----------+ | Databases | +-----------+ | world | +-----------+
我的微信
如果有技术上的问题可以扫一扫我的微信
