MySQL: SQL statement: User, permission, information, status, setting, replication, session, prepare SQL, etc.

{Describe | DESC} table_name[Col_name -- supports wildcard % _]: <=> Show columns from; this statement is provided to be compatible with Oracle.

Explain table_name: <=> Describe table_name | show columns from table_name.

Explain[Extended | Partions]Select clause; -- Get the statement execution plan

Use db_name; Change the current database
,

Prepare prepared_statement_name from {'text constant '| text variable };

• Text can be a lot of single SQL statements, not just select, insert, delete, update.
• The text cannot be multiple statements!
• The text can contain "? "Indicates the replacement parameter. Note that even if the replacement parameter may be of the text type, you do not need to add" '"to the text to include"? ". Processing
• The replacement parameter can only be data_value, but not something else.

Execute prepared_statement_name[Using @ val1 [, @ var2] [,...];

{Deallocate | drop} prepare prepared_statement_name;

Create {user user_name[Identified by {'plain _ password' | password 'hash _ password'}]} [,...]

• In essence, a new user inserts a record into the mysql. User table.
• New users do not have any Permissions
• The user's full format is: 'user _ name' @ 'host'

Drop user user1[, User2] [,...]

Grant {permission type [column _ list]} _ list
On[Object Type -- {table, procedure, function}]Object range -- {* | *. * | db_name. * | db_name.table_name | table_name | db_name.routine_name}
To {user [new password can be specified]} _ list
[With {various options -- {GRANT option | max _ {queries | updates | connections} _ per_hour count | max_user_connections count} _ list]

• Grant can replace create user, and grant permissions when creating a user! When Grant specifies a user that does not exist, it determines whether to create a new user when no identified by clause or the password provided by the clause is specified based on the setting of the no_auto_create_user system variable.
• There is a large set of permission types. Refer to the manual. Special: All [privileges], super, usage, shutdown. MySQL's references permission settings have not been implemented
• Permission type: Table object permission, other object permission, system permission, and auxiliary utility statement permission;
• Whether the permission type implies other permissions: Implicit class permissions, explicit Permissions
• Permission types can be divided into: Global permissions *. *, database permissions dB. *, table-level permissions dB. TB. *, column-level permissions, storageProgramPermission
• Before MySQL version 5.1.6, you need the super permission to manage the trigger. After that, you only need the trigger permission!

• User_name @ host_name: "user_name" and "hostname" are not included unless special characters exist. Otherwise, {'| "} must be used.
• Grant specifies user_name @ host_name: host_name. You can use the [%] wildcard to specify the host range.
• When Grant specifies db_name: You can use "\ _" to specify the "_" wildcard, or you can specify the "%" wildcard.

Rename user old_user_name to new_user_name[,...]

Revoke {permission type [Table column _ list]} _ list on[Object Type]Object range: from user[,...]
Revoke all privileges, grant option from user[,...]

Set Password[For user]= {'Hash _ password' | password ('plain _ password') | old password ('plain _ password ')}

Set has different functions in different scenarios
Set Character Set
Set names
Set Password
SET transaction isolation level
Set {local | session | @ local. | @ session. |} system_var | {Global | @ Global .} system_var} | user_var }={ newvalue | default}[,...]

• Pay attention to the value assignment of set variables. You can use the ": =" operator to facilitate compatibility with other systems such as PostgreSQL and Oracle.
• When you use set to set system variables, only new connections and current sessions should be triggered.
• Many MySQL System variables can be set to the default value during compilation using set Var = default, but this is not always the case!
• When using system variables in an expression, the global or session cannot be specified. MySQL automatically obtains the variables and takes precedence over the session variables.
• Show variables always displays system variables, but select @ VaR is not necessarily!
• Of course, not all system variables can be set at runtime.

Show has different functions in different occasions!

2. -- Display basic object classes
4. Show Character Set[] -- Display character set
6. Show collation[] -- Display sorting rules
8. Show {databases | schemas}[]
10. Show tables[From db_name] []-- Simplified form of "show table status [from db_name ]"
12. Show triggers[From db_name] []
14. Show profiles
16. Show privileges-- Display all system permission information
18. Show[Storage]Engines-- Display storage engine
20. Show plugins
22. Show[Full]Events
24. Show[Full]Columns from table_name[From db_nmae] []
26. Show {index | indexes | keys} from table_name[From db_name]
28. -- Display definition class
30. Show create database db_name
32. Show create event event_name
34. Show create function fun_name
36. Show create procedure proc_name
38. Show create table table_name
40. Show create view view_name
42. Show create trigger trigger_name
44. Show grants for user-- Display User permission Definitions
46. Show Function Code fun_name
48. Show Procedure Code proc_name
50. -- Display object status class
52. Show engine engine_name {status | mutex}
54. Show InnoDB status
56. Show procedure status[]
58. Show function status[]
60. Show Table Status[From db_name] []
62. Show scheduler status-- Display the status of all scheduled jobs
64. Show Profile[Types] [for query N] [offset N] [limit N]
66. -- Display other information
68. Show open tables[From db_name] []
70. Show[Full]Processlist-- Display the connection process of the server
72. Show[Global | session]Status[]
74. Show[Global | session]Variales[]
76. Show errors[Limit [offset,] row_count] -- Display Error
78. Show warnings[Limit [offset,] row_count] -- Display Error

• From in show can be replaced by in.
• [] In Show is generally like 'containing wildcard string' or 'where' string'

Flush[No_write_to_binlog | Local]Flush_options[,...]

• Flush mainly indicates that MySQL has a cache.
• Flush options include logs, privileges, hosts, query cache, status, {table | tables} [table_name] [,...], user_resources ,...

Reset query Cache-- Clear all result Cache

Kill {connection (default) | query} thread_id-- Kill the specified connection; cancel the query operation of the specified connection