1、mysql查询
- 关于左右连接 ：结果集小的表驱动大表，减少循环次数，达到优化目的，但是需要结合索引进一步优化
- union all 与 union 的区别：前者不去重，后者自动去重
- show 语句：
show databases; 查询所有库
show tables; 查询所有表
show tables from city; 查询city库中所有表
show processlist; 查询mysql连接情况
show full processlist; 查询mysql连接的详细情况
show charset; 查询mysql支持的字符集
show collaction; 查询mysql的校对规则
show engines; 查询支持的引擎
show privileges; 查询支持的权限
show grants for use; 查询user用户的权限
show create database city; 查询city库的建库语句
show create table city; 查询city表的建表语句
show index from tb1; 查询tb1表的索引
show variables; 查询数据库参数
show variables like ‘%character%’ ; 模糊查询某个参数
show binary logs; 查询所有二进制日志文件信息
show binlog events in；查询二进制日志事件
show master status; 查询二进制日志的位置点信息
show slave status; 查询从库状态信息
show relaylog events in ;查询中继日志事件
2、元数据
2.1、什么是元数据：
关于数据的数据，类似一些表，视图，存放了数据库的信息
2.2、元数据查询：
information_schema库中，提供了访问元数据的方式，
use information_shcme;
show tables;查看该库下的表，常用的tables表；
mysql> show databases;
+-----------------------+
| Database |
+-----------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| t1 |
| test1 |
| world |
+-----------------------+
mysql> use information_schema;
Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| INNODB_LOCKS |
| INNODB_TRX |
| INNODB_SYS_DATAFILES |
| INNODB_FT_CONFIG |
| INNODB_SYS_VIRTUAL |
| INNODB_CMP |
| INNODB_FT_BEING_DELETED |
| INNODB_CMP_RESET |
| INNODB_CMP_PER_INDEX |
| INNODB_CMPMEM_RESET |
| INNODB_FT_DELETED |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_LOCK_WAITS |
| INNODB_TEMP_TABLE_INFO |
| INNODB_SYS_INDEXES |
| INNODB_SYS_TABLES |
| INNODB_SYS_FIELDS |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_BUFFER_PAGE |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_FT_INDEX_TABLE |
| INNODB_FT_INDEX_CACHE |
| INNODB_SYS_TABLESPACES |
| INNODB_METRICS |
| INNODB_SYS_FOREIGN_COLS |
| INNODB_CMPMEM |
| INNODB_BUFFER_POOL_STATS |
| INNODB_SYS_COLUMNS |
| INNODB_SYS_FOREIGN |
| INNODB_SYS_TABLESTATS |
+---------------------------------------+
61 rows in set (0.01 sec)
2.3、information_schema.tables表简介
table_schema 库名
table_name 表名
engine 引擎
table_rows 表的行数
avg_row_length 表中行的平均行（字节）
index_length 索引的占用空间大小（字节）
mysql> desc tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| TABLE_TYPE | varchar(64) | NO | | | |
| ENGINE | varchar(64) | YES | | NULL | |
| VERSION | bigint(21) unsigned | YES | | NULL | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_FREE | bigint(21) unsigned | YES | | NULL | |
| AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(32) | YES | | NULL | |
| CHECKSUM | bigint(21) unsigned | YES | | NULL | |
| CREATE_OPTIONS | varchar(255) | YES | | NULL | |
| TABLE_COMMENT | varchar(2048) | NO | | | |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)
mysql> select table_schema,table_name,engine,table_rows from TABLES where TABLE_SCHEMA='world'\G
*************************** 1. row ***************************
table_schema: world
table_name: city
engine: InnoDB
table_rows: 4188
*************************** 2. row ***************************
table_schema: world
table_name: country
engine: InnoDB
table_rows: 239
*************************** 3. row ***************************
table_schema: world
table_name: countrylanguage
engine: InnoDB
table_rows: 984
*************************** 4. row ***************************
table_schema: world
table_name: t1
engine: InnoDB
table_rows: 0
4 rows in set (0.00 sec)
2.4、information_schema.columns表简介
mysql> desc columns;
+--------------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| COLUMN_NAME | varchar(64) | NO | | | |
| ORDINAL_POSITION | bigint(21) unsigned | NO | | 0 | |
| COLUMN_DEFAULT | longtext | YES | | NULL | |
| IS_NULLABLE | varchar(3) | NO | | | |
| DATA_TYPE | varchar(64) | NO | | | |
| CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES | | NULL | |
| CHARACTER_OCTET_LENGTH | bigint(21) unsigned | YES | | NULL | |
| NUMERIC_PRECISION | bigint(21) unsigned | YES | | NULL | |
| NUMERIC_SCALE | bigint(21) unsigned | YES | | NULL | |
| DATETIME_PRECISION | bigint(21) unsigned | YES | | NULL | |
| CHARACTER_SET_NAME | varchar(32) | YES | | NULL | |
| COLLATION_NAME | varchar(32) | YES | | NULL | |
| COLUMN_TYPE | longtext | NO | | NULL | |
| COLUMN_KEY | varchar(3) | NO | | | |
| EXTRA | varchar(30) | NO | | | |
| PRIVILEGES | varchar(80) | NO | | | |
| COLUMN_COMMENT | varchar(1024) | NO | | | |
| GENERATION_EXPRESSION | longtext | NO | | NULL | |
+--------------------------+---------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)
mysql> select t1.table_schema,t1.table_name,column_name,data_type from tables t1
-> join columns t2 on t1.table_name=t2.table_name and t1.TABLE_SCHEMA='world' and t1.table_name='city';
+--------------+------------+-------------+-----------+
| table_schema | table_name | column_name | data_type |
+--------------+------------+-------------+-----------+
| world | city | city_id | smallint |
| world | city | city | varchar |
| world | city | country_id | smallint |
| world | city | last_update | timestamp |
| world | city | ID | int |
| world | city | Name | char |
| world | city | CountryCode | char |
| world | city | District | char |
| world | city | Population | int |
+--------------+------------+-------------+-----------+
9 rows in set, 1 warning (3.14 sec)
- 练习1
--查询所有库中包含的具体表
SELECT table_schema,GROUP_CONCAT(table_name) FROM TABLES GROUP BY table_schema;
-- 统计world库下city表数据量大小=平均行长度*数据行数+索引长度
SELECT table_schema,table_name ,(AVG_ROW_LENGTH*table_rows+index_length)/1024 cd FROM TABLES
WHERE TABLE_SCHEMA='world' AND table_name='city';
-- 统计world库下数据总量大小
SELECT table_schema,table_name ,SUM((AVG_ROW_LENGTH*table_rows+index_length))/1024 cd FROM TABLES
WHERE TABLE_SCHEMA='world'
- 练习2
--利用cocat函数实现语句拼接，批量生成执行语句
-- mysql 分库分表备份 mysql -uroot -proot 库名 表名 >/bak/库名_表名.sql
SELECT CONCAT('mysql -uroot -proot ',table_schema,' ',table_name,' >/bak/',table_schema,'_',table_name,'.sql') FROM information_schema.tables
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
