Shulou(Shulou.com)06/01 Report--

Foreword:

We may often use some SQL in our daily work or study. I suggest you organize and record more of these commonly used SQL, so that it will be much more convenient to use later. The author in the work and study process also sorted out the next personal commonly used SQL, now share with you! There may be some SQL you don't use yet, but I still hope it can help you. Maybe you can use it if you need it one day.

Note: the SQL shared below applies to MySQL version 5.7, and the lower version may be slightly different. Some SQL execution may require higher permissions.

1.show related statement # View instance parameters for example: show variables like'% innodb%';show global variables like'% innodb%';# to view instance status, for example: show status like 'uptime%';show global status like' connection%';# View database link: show processlist;show full processlist;# query the structure of a table: show create table tb_name;# query the detailed field information of a table: show full columns from tb_name # query all index information of a table: show index from tb_name;# queries a table that starts with cd: show tables like 'cd%';# queries all views in a database: show table status where comment='view';# queries the permissions of a user: show grants for' test_user'@'%';2. Check the account-related information # here first introduce the CONCAT function: in MySQL, the CONCAT () function is used to concatenate multiple strings into a string. Using this function, we can concatenate the sql that cannot be obtained in one step, and the later statements are useful to this function. # use the\ escape character # to view all user names when''appears in the stitching string: SELECT DISTINCT CONCAT (' User:\', user,'\'@\', host,'\';) AS QUERYFROM mysql.user # View user details: SELECT user, host, authentication_string, password_expired, password_lifetime, password_last_changed, account_locked FROM mysql.user;3.KILL database link # the following list of SQL is just a statement that splices out the kill link. If you want to execute, you can copy the result directly. # kill links with idle time greater than 2000s: SELECT concat ('KILL', id,';') FROM information_ schema.`PROCESSLIST` WHERE Command = 'Sleep' AND TIME > 2000 * kill links in a certain state: SELECT concat (' KILL', id,';') FROM information_ schema.`PROCESSLIST` WHERE STATE LIKE 'Creating sort index';# kill a user's link: SELECT concat (' KILL', id,' ') FROM information_ schema.`PROCESSLIST` WHERE where user='root';4. Stitching create database or user statement # stitching create database statement (excluding system libraries): SELECT CONCAT ('create database','`', SCHEMA_NAME,'`, 'DEFAULT CHARACTER SET', DEFAULT_CHARACTER_SET_NAME,' ') AS CreateDatabaseQueryFROM information_schema.SCHEMATAWHERE SCHEMA_NAME NOT IN (' information_schema', 'performance_schema',' mysql', 'sys') # stitching create user statements (excluding system users): SELECT CONCAT ('create user\', user,'\'@\', Host,'\ 'IDENTIFIED BY PASSWORD\', authentication_string,'\';) AS CreateUserQueryFROM mysql.`user`userWere `User`NOT IN ('root',' mysql.session', 'mysql.sys') # A password string can be executed in other instances to directly create users with the same password as this instance. 5. View the size of the library or table # to view the space occupied by the entire instance: SELECT concat (round (sum (data_length / 1024 / 1024), 2), 'MB') AS data_length_MB, concat (round (index_length / 1024 / 1024), 2),' MB') AS index_length_MB FROM information_ schema.`TABLES` # check the size of each library: SELECT TABLE_SCHEMA, concat (TRUNCATE (sum (data_length) / 1024 / 1024, 2), 'MB') AS data_size, concat (TRUNCATE (sum (index_length) / 1024 / 1024, 2),' MB') AS index_size FROM information_ schema.`TABLES`GROUP BY TABLE_SCHEMA # View the space occupied by a single library: SELECT concat (round (sum (data_length / 1024 / 1024), 2), 'MB') AS data_length_MB, concat (round (sum (index_length / 1024 / 1024), 2),' MB') AS index_length_MB FROM information_ schema.`TABLES`where table_schema = 'test_db' # View the space occupied by a single table: SELECT concat (round (sum (data_length / 1024 / 1024), 2), 'MB') AS data_length_MB, concat (round (sum (index_length / 1024 / 1024), 2),' MB') AS index_length_MB FROM information_ schema.`TABLES`WERE table_schema = 'test_db' AND table_name =' tbname';6. View table fragmentation and shrinking statements # View the fragmentation of all tables under a library: SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.TABLE_ROWS, concat (round (t.DATA_LENGTH / 1024 / 1024, 2),'M') AS size, t.INDEX_LENGTH, concat (t.DATA_FREE / 1024 / 1024, 2) 'M') AS datafree FROM information_ schema.`TABLES`t WHERE t.TABLE_SCHEMA = 'test_db' ORDER BY datafree DESC # shrink the table and reduce fragments: alter table tb_name engine = innodb;optimize table tb_name;7. Find the key-less table # find a library without key table: SELECTtable_schema,table_nameFROM information_ schema.`TABLES`where table_schema = 'test_db'AND TABLE_NAME NOT IN (SELECTtable_ nameFROM information_schema.table_constraints t JOIN information_schema.key_column_usage k USING (constraint_name, table_schema) Table_name) WHERE t.constraint_type = 'PRIMARY KEY' AND t.table_schema =' test_db') # find no key table except the system library: SELECT t1.table_schema, t1.table_nameFROM information_ schema.`TABLES`TABLES`t1LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS T2 ON t1.table_schema = t2.TABLE_SCHEMAAND t1.table_name = t2.TABLE_NAMEAND t2.CONSTRAINT_NAME IN ('PRIMARY') WHERE t2.table_name IS NULLAND t1.TABLE_SCHEMA NOT IN (' information_schema', 'performance_schema',' mysql', 'sys')

Summary:

I hope these SQL sentences can help you, you can collect it, maybe you will use it some time! Original is not easy, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.