“这是我参与8月更文挑战的第5天，活动详情查看：8月更文挑战”
1. DQL 介绍
- select
- show
2. select 语句应用
2.1 select 单独使用的情况
我们可以使用 @@ 来查询一些参数：
mysql> select @@basedir;
+----------------------+
| @@basedir |
+----------------------+
| /home/service/mysql/ |
+----------------------+
1 row in set (0.00 sec)
mysql> select @@port;
+--------+
| @@port |
+--------+
| 3306 |
+--------+
1 row in set (0.00 sec)
mysql> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
| 1 |
+----------------------------------+
1 row in set (0.00 sec)
当然，有些参数可能太长，我们记不住，我们可以使用 show variables like 命令来查看，比如：
mysql> show variables like "innodb%";
我们也可以使用一些系统的函数，比如查看当前所在数据库：
mysql> select database();
+------------+
| database() |
+------------+
| account |
+------------+
1 row in set (0.00 sec)
2.2 select 通用语法（单表）
select 列
from 表
where 条件
group by 条件
having 条件
order by 条件
limit
2.3 select 配合 from 子句使用
语法：
selct 列,列,列 from 表
- 查询表中所有的信息：
-- 查询部分字段
mysql> select id, account_id, mobile from account;
-- 查询所有字段
mysql> select * from account;
2.4 select 配合 where 子句使用
语法：
select 列,列,列 from 表 where 过滤条件
- where 等值条件查询
mysql> select * from account where account_id = 214771086200696950;
- where 配合不等值查询（<，>，>=，<=）
mysql> select * from account where id < 10;
- where 配合逻辑连接符（and or）
mysql> select * from account where id < 10 and account_id = 214771086200696950;
2.5 where 配合 like 子句，实现模糊查询
mysql> select * from account where name like '王%';
注意：前面不要出现 %，因为不走索引，性能差。
2.6 where 配合 in 语句
mysql> select * from account where account_id in (214771086200696950,214771086200696951) ;
2.7 select 配合 group by + 聚合函数应用
2.7.1 常用聚合函数介绍
- max()
- min()
- avg()
- count()
- sum()
2.7.2 group by
将某列中有共同条件的数据行，分成一组，然后再进行聚合函数操作。
mysql> select account_id,count(*) from t_xz_invite group by account_id;
+--------------------+----------+
| account_id | count(*) |
+--------------------+----------+
| 185452362213624980 | 1 |
| 214771086200696950 | 3 |
| 310318659243455997 | 2 |
| 310976854965882963 | 11 |
| 312999049271991931 | 2 |
| 313581774509548961 | 2 |
| 313583123168697963 | 1 |
+--------------------+----------+
7 rows in set (0.00 sec)
2.8 selct 配合 having 使用
mysql> select account_id,sum(payable_amount) from order group by account_id;
+--------------------+---------------------+
| account_id | sum(payable_amount) |
+--------------------+---------------------+
| 185928004138280964 | 322.80 |
| 214771086200696950 | 73086.00 |
| 309721251417437971 | 20.20 |
| 320940817924552903 | 173811.00 |
+--------------------+---------------------+
mysql> select account_id,sum(payable_amount) from order group by account_id having sum(payable_amount) > 10000;
+--------------------+---------------------+
| account_id | sum(payable_amount) |
+--------------------+---------------------+
| 214771086200696950 | 73086.00 |
| 320940817924552903 | 173811.00 |
+--------------------+---------------------+
2.9 select 配合 order by 子句
mysql> select account_id,order_id,payable_amount from order where payable_amount > 10000 order by payable_amount desc;
2.10 select 配合 limit 使用
- limit m,n：跳过 M 行，一共显示 N 行；
- limit y offset x：跳过 x 行，一共显示 y 行；
-- 显示前 3 行
mysql> select account_id,order_id,payable_amount from order where payable_amount > 10000 order by payable_amount desc limit 3;
-- 显示 4，5 行
mysql> select account_id,order_id,payable_amount from order where payable_amount > 10000 order by payable_amount desc limit 3,2;
-- 显示 3，4，5 行
mysql> select account_id,order_id,payable_amount from order where payable_amount > 10000 order by payable_amount desc limit 3 offset 2;
2.11 union 和 union all
- 作用：多个结果集合并查询
- 区别：
union all对结果集不做去重。
3. 多表连接查询（内连接）
3.1 作用
- 单表数据不能满足查询需求时
3.2 多表连接基本语法
- 最核心的是，知道多张表之间的关联条件列；
- 列书写时，必须是 表名.列；
- 所有涉及到的查询列，都放到 select 后面；
- 将所有的过滤、分组、排序等条件按顺序放在 on 后面；
3.3 别名使用
- 表别名
- 全局调用；
- 列别名
- 被 having 和 order by 调用；
4. tables 视图应用
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> 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)
4.1 显示所有的库、表信息
mysql> select table_schema,table_name from information_schema.tables;
4.1 将同一 table_schema 下的表放在一起
mysql> select table_schema,group_concat(table_name) from information_schema.tables group by table_schema;
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table_schema | group_concat(table_name) |
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| account | student |
| information_schema | CHARACTER_SETS,INNODB_CMP,STATISTICS,KEY_COLUMN_USAGE,INNODB_CMP_PER_INDEX_RESET,INNODB_SYS_VIRTUAL,INNODB_SYS_TABLESTATS,SESSION_VARIABLES,INNODB_SYS_FIELDS,GLOBAL_VARIABLES,INNODB_FT_CONFIG,INNODB_SYS_FOREIGN,SESSION_STATUS,INNODB_SYS_TABLES,GLOBAL_STATUS,INNODB_SYS_DATAFILES,INNODB_SYS_COLUMNS,SCHEMA_PRIVILEGES,INNODB_SYS_INDEXES,FILES,INNODB_TRX,SCHEMATA,INNODB_SYS_FOREIGN_COLS,INNODB_BUFFER_POOL_STATS,INNODB_TEMP_TABLE_INFO,EVENTS,INNODB_LOCKS,ROUTINES,INNODB_METRICS,INNODB_CMPMEM,ENGINES,INNODB_FT_DELETED,INNODB_LOCK_WAITS,VIEWS,REFERENTIAL_CONSTRAINTS,INNODB_SYS_TABLESPACES,COLUMN_PRIVILEGES,INNODB_CMPMEM_RESET,INNODB_BUFFER_PAGE_LRU,TABLE_PRIVILEGES,USER_PRIVILEGES,INNODB_FT_INDEX_CACHE,PROFILING,COLUMNS,INNODB_CMP_PER_INDEX,TABLE_CONSTRAINTS,TRIGGERS,INNODB_FT_INDEX_TABLE,PARTITIONS,PROCESSLIST,INNODB_CMP_RESET,COLLATION_CHARACTER_SET_APPLICABILITY,TABLESPACES,PARAMETERS,PLUGINS,INNODB_FT_DEFAULT_STOPWORD,INNODB_FT_BEING_DELETED,COLLATIONS,TABLES,OPTIMIZER_TRACE,INNODB_BUFFER_PAGE |
| mysql | innodb_table_stats,tables_priv,innodb_index_stats,slow_log,slave_relay_log_info,help_topic,slave_master_info,slave_worker_info,help_category,help_relation,servers,gtid_executed,help_keyword,server_cost,general_log,user,proxies_priv,func,time_zone_transition_type,procs_priv,event,time_zone_transition,proc,engine_cost,time_zone_name,plugin,db,time_zone_leap_second,ndb_binlog_index,columns_priv,time_zone |
| performance_schema | replication_applier_status,events_statements_summary_by_thread_by_event_name,events_statements_summary_global_by_event_name,events_stages_summary_by_host_by_event_name,host_cache,events_stages_history_long,table_io_waits_summary_by_index_usage,events_waits_history,setup_actors,replication_applier_configuration,events_statements_summary_by_program,global_variables,events_stages_history,table_handles,events_stages_summary_by_account_by_event_name,events_waits_current,session_variables,events_statements_summary_by_host_by_event_name,prepared_statements_instances,events_stages_current,status_by_user,global_status,events_transactions_summary_global_by_event_name,session_status,events_statements_summary_by_digest,performance_timers,cond_instances,status_by_thread,file_summary_by_instance,session_connect_attrs,events_transactions_summary_by_user_by_event_name,events_statements_summary_by_account_by_event_name,objects_summary_global_by_type,accounts,status_by_host,file_summary_by_event_name,socket_summary_by_instance |
| sys | user_summary_by_stages,x$schema_table_statistics,innodb_buffer_stats_by_schema,user_summary_by_file_io,schema_table_statistics_with_buffer,schema_unused_indexes,x$latest_file_io,x$user_summary_by_file_io_type,memory_global_by_current_bytes,x$host_summary_by_file_io,x$schema_table_lock_waits,host_summary_by_statement_type,user_summary,schema_tables_with_full_table_scans,x$io_global_by_wait_by_latency,schema_table_statistics,x$user_summary_by_file_io,memory_by_user_by_current_bytes,x$host_summary,memory_by_host_by_current_bytes,x$schema_index_statistics,host_summary_by_statement_latency,sys_config,x$io_global_by_wait_by_bytes,schema_table_lock_waits,memory_by_thread_by_current_bytes,waits_global_by_latency,latest_file_io,x$user_summary,host_summary_by_stages,statements_with_temp_tables,host_summary_by_file_io,x$schema_flattened_keys,x$io_global_by_file_by_latency,schema_redundant_indexes,waits_by_user_by_latency,io_global_by_wait_by_latency,x$statements_with_temp_tables,host_summary_by_file_io_type,statements_w |
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set, 2 warnings (0.01 sec)
4.3 查询所有有 innoDB 引擎的表
mysql> select table_schema,table_name,ENGINE from information_schema.tables where ENGINE='innoDB';
4.4 统计 某个库 下面 table 占用空间大小
- 计算公式：平均行长度 * 函数 + 索引长度（AVG_ROW_LENGTH * TABLE_ROWS + INDEX_LENGTH，为
desc tables命令查出的字段）
mysql> select table_name,(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024 from information_schema.tables where table_schema='account' and table_name='student';
+------------+-----------------------------------------------+
| table_name | (AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024 |
+------------+-----------------------------------------------+
| student | 16.0000 |
+------------+-----------------------------------------------+
1 row in set (0.00 sec)
4.5 统计 某个库 下面所有表大小
mysql> select table_name,sum((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 from information_schema.tables where table_schema='account';
+------------+----------------------------------------------------+
| table_name | sum((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 |
+------------+----------------------------------------------------+
| student | 16.0000 |
+------------+----------------------------------------------------+
1 row in set (0.00 sec)
4.6 统计每个数据库数据量大小，并按数据量大小降序排列
mysql> select table_schema,sum((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 as total_kb from information_schema.tables group by table_schema order by total_kb desc;
+--------------------+-----------+
| table_schema | total_kb |
+--------------------+-----------+
| mysql | 2307.4404 |
| account | 16.0000 |
| sys | 15.9961 |
| performance_schema | 0.0000 |
| information_schema | NULL |
+--------------------+-----------+
5 rows in set (0.08 sec)
4.7 配合 concat() 函数进行语句命令拼接
示例：
模拟数据库拼接：mysqldump -uroot account student >/home/work/bak/account_student.sql
mysql> select concat("mysqldump -uroot ",table_schema," ",table_name," >/home/work/bak","table_schema","_",table_name,".sql") from information_schema.tables;
5. show 介绍
| 命令 | 介绍 |
|---|---|
show databases; | 查看数据库名 |
show tables; | 查看表名 |
show create database xx; | 查看建库语句 |
show create table xx; | 查看建表语句 |
show processlist; | 查看所有用户连接情况 |
show charset; | 查看支持的字符集 |
show collation; | 查看所有支持的校对规则 |
show grant for xx; | 查看用户的权限信息 |
show variables like '%xx%'; | 查看参数信息 |
show engines; | 查看所有支持的存储引擎类型 |
show index from xxx; | 查看表的索引信息 |
show engine innodb status \G | 查看 InnoDB 引擎详细状态信息 |
show binary logs; | 查看二进制日志的列表信息 |
show binary enents in ''; | 查看二进制日志的事件信息 |
show master status; | 查看 mysql 当前使用二进制日志信息 |
show slave status\G | 查看从库状态信息 |
show relaylog enents in ''; | 查看中继日志的时间信息 |
show status like ''; | 查看数据库整体状态信息 |
