MySQL Basic Syntax

1. Description: Create a default database
Create database name;


2. Description: Create a database with the specified character encoding
Create database name character set character encoding format;


3. Description: Delete Database
drop database name;


4. Description: Create a new table
CREATE TABLE table name (column name 1 column type column constraint [is NULL, whether the primary key is not automatically incremented ...],
Column-Name 2 column-type column constraint [is NULL, whether the primary key is auto-incremented ...],
...
Column name n column type column constraint [is NULL, primary key, whether primary key is automatically incremented ...]
);


5. Description: Delete Table
drop table name;


6. Description: Modify table structure
① Description: Adding columns
ALTER TABLE name add column list type [constraint];
② Description: Modifying columns
ALTER TABLE name change column list new name new column type [constraint];
③ Description: Delete a column
ALTER TABLE name drop column name;
④ Description: Renaming a table
ALTER TABLE name rename new table name;
⑤ Description: Modify the table's storage engine
ALTER TABLE name engine = ' storage engine name ';


7. Description: View all databases under the current database server
show databases;


8. Description: View the encoding format of the current database
Show variables like ' character% ';


9. Description: Set the current client character set
Set character_set_client = character format;


10. Description: Setting the connection character set
Set character_set_connection = character format;


11. Description: Set the resulting character set
Set character_set_results = character format;


12. Description: Use the specified database
Use library name;


13. Description: View all tables under the current database
Show tables;


14. Description: View table structure
DESC table name;/describe table name;


15. Description: syntax for viewing the structure of a previously defined table
Show create table table name;


16. Description: Query all data in the table
Show * from table name;


17. Description: Query the data for the criteria specified in the table
Show * from table name where filter condition;


18. Description: Insert Value
Insert into table name (column 1, column 2 to insert data ...) column n) VALUES (value of the corresponding column 1, value 2 ...) Value n);


19. Description: Modify the data in the table
Update table name Set column = ' new value ' where limit modification condition;


20. Description: Clear the table data (recoverable)
Delete from table name;


21. Description: Clear the Data in the table (not recoverable)
TRUNCATE TABLE name;


22. Description: Delete data that meets the specified criteria
Delete from table name where to specify the condition;


23. Description: Increase the primary key for the specified name
ALTER TABLE name add constraint specify name (PK) primary key (required to set the column name as the primary key);


24. Description: Remove primary key from table
ALTER TABLE name drop PRIMARY key [primary key name];


25. Description: Add a foreign key with the specified name
ALTER TABLE specifies the name (FK) from the table name add constraint foreign key (from the table column name) references the primary table (primary key column);


26. Description: Delete The foreign key of the specified name in the table
ALTER TABLE Name drop FOREIGN key [foreign key name];


27. Description: The value of the specified column in the query table (eliminates duplicate values in the result)
Select DISTINCT column name from table name;


28. Description: Paged Query
Select Column name from table name where conditional limit start position, indicating the number of rows;


29. Description: Select syntax
SELECT [All|distinct]
*| table name. *| table name. column name [as column alias]
From
Table name [as table alias]
[Left|right|inner Join table name 2]--Union query
[Where specify conditions]--specify the conditions that the result needs to be met
[GROUP by Group column name]--specifies which fields are grouped by the result
[Having a secondary condition that specifies the result of a grouping]--the secondary condition that the record of the filter group must meet
[Order by sort column name Sort by]--specifies that the query records are sorted by one or more criteria
[Limit conditions];


30. Description: Scope query
SELECT * FROM table name where column name between small value and large value;


31. Description: Fuzzy query
SELECT * FROM table name where column name like ' Fuzzy query condition ';


32. Description: Query for data with escape characters in fuzzy query conditions
SELECT * FROM table name where column name like ' escape character symbol _% ' escape ' escape character symbol ';


33. Description: Include query
SELECT * FROM table name where column name in (range);


34. Description: Does not contain
SELECT * from table name where not in (range);


35. Description: The data is empty query
SELECT * FROM table name where column name is null;


36. Description: Data is not empty query
SELECT * FROM table name where column name is not NULL;


37. Description: Multi-table Query
SELECT * FROM table 1, table 2,... Table n the relationship condition in the where multiple tables;


38. Description: Group Query
SELECT * from table name [where filter condition] GROUP by group column name [having group result filter];


39. Description: Internal connection Query
① implicit in-connection
Select the column to query from table 1, table 2 ... Table n where multi-table relationship condition;
② Show Inner Connection
Select the column to query from table 1 inner JOIN table 2 on the multi-table relationship condition;


40. Description: External connection Query
① Left Connection
Select the column to query from table 1 left JOIN table 2 on multi-table relationship condition;
② Right Connection
Select the column to query from table 2 right join table 2 on multi-table relationship condition;


41. Description: Variable assignment using
①set @ Variable name = assigned value; or SET @ variable name: = assign value;
②select @ variable Name: = assigned value; or select @ Variable name: = Field name from table name where condition;


42. Description: If condition statement
Select if (expression 1, expression 2, expression 3) from table name [where filter condition ...]; The expression 1 is determined, the return expression 2 result is returned, otherwise the expression 3 result is returned.


43. Description: Case Condition statement
①select case When condition 1 then result 1 when condition 2 then result 2 ... else other result end [alias] from table name [where filter condition ...];
②select case the field or expression to judge when constant 1 then result 1 ... else other result end [alias] from table name [where filter condition ...];


44. Description: Query Session Variables
Show session variables;


45. Description: Query Global variables
Show global variables;


46. Description: Set the system variable new value
Set @@ 变量 type. Variable name = new value;


47. Description: The addition of the primary key index
① add when creating table: Create table table name (column name type primary key);
Or: Create table table name (column name type ..., primary key (indexed column));
② add after creating the table: Alter TABLE name add primary key (indexed column);


48. Description: Add a unique index
① add when creating table: Create table table name (column name type unique);
Or: Create Talbe table name (column name Type ..., unique key [index name] (indexed column));
② After creating the table: ALTER TABLE name add unique key [index name] (index column);


49. Description: Addition of regular indexes
① add when creating table: Create table table name (column name Type ..., Index/key [index name] (index column 1, index column 2,..));
② add after creating the table: ALTER TABLE name add index [index name] (index column 1, index column 2 ...);


50. Description: Addition of full-text indexes (only for MYISAM types of data tables) (for Char,varchar,text data column types)
① add when creating table: Create table table name (column name Type ..., fulltext (indexed column)) engine = MyISAM;
② add after creating the table: ALTER TABLE name add fulltext [index name] (index column);


51. Description: Delete Index
①drop index name on table name;
②alter table Name drop index name;
③alter table name drop PRIMARY key;


52. Description: View Index
Show index[or keys] from table name;


53. Description: Analyze execution performance of SQL statements
explain table name; <==> desc Table Name
Explain SELECT statement;


54. Description: Create a view
Create VIEW name as <select statement >;


55. Description: Delete view
Drop view name;


56. Description: View View
SELECT * from view name;


57. Description: View all views
Use INFORMATION_SCHEMA;
select * from views;


58. Description: Create a custom function
Create function name (parameter list)
Returns return value type
"Begin" Function body "end";


59. Description: Call function
The name of the Select function;


60. Description: Delete function
The name of the drop function function;


61. Description: Variables in the Custom function
Declare variable name ", Variable name 2 ..." Variable data type "default Defaults";


62. Description: Assigning values to variables
Select to assign a value into the variable name to assign;
<==> Select the variable name to assign: = the value to be assigned;
<==> set the variable name to assign value = value to be assigned;


63. Description: Process Control Statement syntax:
①if Condition Judging structure
If condition Judgment Statement 1 then satisfies the result of condition 1 output;
ElseIf condition Judgment Statement 2 then satisfies the result of condition 2 output;
...
Else neither satisfies the result of the above condition output;
End If;
②case Branching structure
Ⅰ: Equivalent judgment
Case Judgment Field/column/variable
When the value 1 then equals the result of the value 1 output;
When the value 2 then equals the result of the value 2 output;
...
Else neither satisfies the result of the above condition output;
End case;
Ⅱ: Interval judgment
Case
When judging condition 1 then satisfies the result of condition 1 output;
When judging condition 2 then satisfies the result of condition 2 output;
...
Else neither satisfies the result of the above condition output;
End case;


64. Description: Loop loop (dead loop, usually with leave, iterate)
[Begin_label:] Loop
Looping statements
End Loop [End_label];


65. Description: Leave jump out of the loop control
Leave label;


66. Description: Iterate jump out of this cycle
Iterate label;


67. Description: Repeat cycle (meet the conditions jump out of the loop)
[Begin_label:] Repeat
Loop statement;
Until end loop condition
End repeat [End_label];


68. Description: While loop (conditions are executed in the loop when the condition is met)
[Begin_label:] While participating in loop execution conditions do
Loop statement;
End While [End_label];


69. Description: Create a stored procedure
Create procedure stored procedure name ([Stored procedure parameter in\out\inout] parameter name parameter type)
Begin
The body of the stored procedure;
End


70. Description: Stored procedure Call
Call stored procedure name (parameter);


71. Description: Delete stored procedures
drop procedure stored procedure name;


72. Description: View all stored procedures in the library
Show procedure status;


73. Description: Defining exceptions
Declare exception name condition for exception type, (sqlstate_value example: SQLState ' 42000 ' or Mysql_error_code example: 1148)


74. Description: Exception capture
Declare error handling mode handler for exception name [,...] Error code (handled by: Continue,exit,undo)


75. Description: Turn off autocommit mode
Set autocommit = 0;


76. Description: Turn on autocommit mode
Set autocommit = 1;


77. Description: Start a transaction
Start transaction;


78. Description: Commit the transaction
Commit


79. Description: Data rollback
Rollback

MySQL Basic Syntax