第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语句:
[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 | +-----------+

我的微信
如果有技术上的问题可以扫一扫我的微信