“This is the fifth day of my participation in the August More Text Challenge. For details, see: August More Text Challenge.”

1. DQL is introduced

• select
• show

2. Apply the SELECT statement

2.1 Select is used alone

We can use @@ to query some parameters:

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)
Copy the code

Of course, some arguments may be too long to remember, we can use the show variables like command to see, such as:

mysql> show variables like "innodb%";
Copy the code

We can also use some system functions, such as viewing the current database:

mysql> select database(a); +------------+
| database() |
+------------+
| account    |
+------------+
1 row in set (0.00 sec)
Copy the code

2.2 Select General Syntax (single table)

Select * from table where condition group by condition order by condition limitCopy the code

2.3 Select with FROM clause

Grammar:

Selct column, column, column from tableCopy the code

1. Query all information in the table:

-- Query some fields
mysql> select id, account_id, mobile from account;
-- Query all fields
mysql> select * from account;
Copy the code

2.4 Select is used with the WHERE clause

Grammar:

Select * from (select * from (select * from (select * from (select * from (select * from (select * from))Copy the code

1. Where equivalents are queried

mysql> select *  from account where account_id = 214771086200696950;
Copy the code

2. Where with non-equivalent query (<, >, >=, <=)

mysql> select *  from account where  id < 10;
Copy the code

3. Where with logical connector (and or)

mysql> select *  from account where  id < 10 and account_id = 214771086200696950;
Copy the code

2.5 Where with the like clause to achieve fuzzy query

mysql> select * from account where name like 'the king %';
Copy the code

Note: Do not appear before%Because the index is not gone, the performance is poor.

2.6 Where with in statement

mysql> select *  from account where account_id in (214771086200696950.214771086200696951);Copy the code

2.7 Select is applied with group by + aggregation function

2.7.1 Overview of common aggregation functions

• max()
• min()
• avg()
• count()
• sum()

2.7.2 group by

Grouping of data rows with common conditions in a column and then performing aggregate function operations.

mysql> select account_id,count(*) from t_xz_invite group by account_id;
+--------------------+----------+
| account_id         | count(*) |
+--------------------+----------+214771086200696950 | | 185452362213624980 | 1 | | 3 | | 310318659243455997 | 2 | | 310976854965882963 | | | 312999049271991931 | 2 | 2 | | 313581774509548961 | | 313583123168697963 | | + 1--------------------+----------+
7 rows in set (0.00 sec)
Copy the code

2.8 Use SELCT with HAVING

mysql> select account_id,sum(payable_amount) from order group by account_id;
+--------------------+---------------------+
| account_id         | sum(payable_amount) |
+--------------------+---------------------+73086.00 322.80 | | 185928004138280964 | | 214771086200696950 | | 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) |
+--------------------+---------------------+73086.00 | | 214771086200696950 | | 320940817924552903 | | + 173811.00--------------------+---------------------+
Copy the code

2.9 Select with order by clause

mysql> select account_id,order_id,payable_amount from order where payable_amount > 10000 order by payable_amount desc;
Copy the code

2.10 Select combined with LIMIT

• Limit m,n: skip m lines;
• Limit y offset x: y offset x

-- Displays the first 3 lines
mysql> select account_id,order_id,payable_amount from order where payable_amount > 10000 order by payable_amount desc limit 3;

-- display 4,5 lines
mysql> select account_id,order_id,payable_amount from order where payable_amount > 10000 order by payable_amount desc limit 3.2;

-- Displays 3, 4, and 5 lines
mysql> select account_id,order_id,payable_amount from order where payable_amount > 10000 order by payable_amount desc limit 3 offset 2;
Copy the code

2.11 Union and Union all

• Function: Query multiple result sets
• The difference between:union allYou don’t redo the result set.

SQL > select * from table_name where table_name = table_name;

3.1 role

• The data in a single table cannot meet the query requirements

3.2 Basic syntax for multiple table joins

1. The core is to know the correlation condition columns between multiple tables;
2. When the column is written, it must be a table name. Columns;
3. All query columns involved are placed after select;
4. Put all filter, group, sort and other conditions in order after on;

3.3 Using Aliases

• Table alias
  • Global call;
• Column alias
  • Called by having and order by;

4. Apply the tables view

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)
Copy the code

4.1 Display information about all libraries and tables

mysql> select table_schema,table_name from information_schema.tables;
Copy the code

4.1 Grouping tables in the same 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_TABLESTAT S,SESSION_VARIABLES,INNODB_SYS_FIELDS,GLOBAL_VARIABLES,INNODB_FT_CONFIG,INNODB_SYS_FOREIGN,SESSION_STATUS,INNODB_SYS_TAB LES,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,INN ODB_CMPMEM,ENGINES,INNODB_FT_DELETED,INNODB_LOCK_WAITS,VIEWS,REFERENTIAL_CONSTRAINTS,INNODB_SYS_TABLESPACES,COLUMN_PRIVI LEGES,INNODB_CMPMEM_RESET,INNODB_BUFFER_PAGE_LRU,TABLE_PRIVILEGES,USER_PRIVILEGES,INNODB_FT_INDEX_CACHE,PROFILING,COLUMN S,INNODB_CMP_PER_INDEX,TABLE_CONSTRAINTS,TRIGGERS,INNODB_FT_INDEX_TABLE,PARTITIONS,PROCESSLIST,INNODB_CMP_RESET,COLLATIO N_CHARACTER_SET_APPLICABILITY,TABLESPACES,PARAMETERS,PLUGINS,INNODB_FT_DEFAULT_STOPWORD,INNODB_FT_BEING_DELETED,COLLATIO NS,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_worke r_info,help_category,help_relation,servers,gtid_executed,help_keyword,server_cost,general_log,user,proxies_priv,func,tim e_zone_transition_type,procs_priv,event,time_zone_transition,proc,engine_cost,time_zone_name,plugin,db,time_zone_leap_se cond,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_n ame,events_stages_summary_by_host_by_event_name,host_cache,events_stages_history_long,table_io_waits_summary_by_index_us age,events_waits_history,setup_actors,replication_applier_configuration,events_statements_summary_by_program,global_vari ables,events_stages_history,table_handles,events_stages_summary_by_account_by_event_name,events_waits_current,session_va riables,events_statements_summary_by_host_by_event_name,prepared_statements_instances,events_stages_current,status_by_us er,global_status,events_transactions_summary_global_by_event_name,session_status,events_statements_summary_by_digest,per formance_timers,cond_instances,status_by_thread,file_summary_by_instance,session_connect_attrs,events_transactions_summa ry_by_user_by_event_name,events_statements_summary_by_account_by_event_name,objects_summary_global_by_type,accounts,stat us_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_stat istics_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_b ytes,x$host_summary,memory_by_host_by_current_bytes,x$schema_index_statistics,host_summary_by_statement_latency,sys_conf ig,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_ke ys,x$io_global_by_file_by_latency,schema_redundant_indexes,waits_by_user_by_latency,io_global_by_wait_by_latency,x$state ments_with_temp_tables,host_summary_by_file_io_type,statements_w | +--------------------+--------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------+
5 rows in set.2 warnings (0.01 sec)
Copy the code

4.3 Querying all tables with innoDB engine

mysql> select table_schema,table_name,ENGINE from information_schema.tables where ENGINE='innoDB';
Copy the code

4.4 Calculating the Space occupied by tables in a Library

• Calculation formula: average line length * function + index length (AVG_ROW_LENGTH * TABLE_ROWS + INDEX_LENGTH, isdesc tablesFields found by the command)

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)
Copy the code

4.5 Calculating the size of all tables in a library

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)
Copy the code

4.6 Collecting statistics about the data volume of each database in descending order

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  |
+--------------------+-----------+16.0000 | | mysql 2307.4404 | | | account | | sys 15.9961 | | | performance_schema 0.0000 | | | information_schema | NULL | +--------------------+-----------+
5 rows in set (0.08 sec)
Copy the code

4.7 Concat () function to concatenate statements and commands

Example:

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;
Copy the code

5. The show is introduced