MySQL study notes the next day

Insert Table Data:

INSERT INTO table_name set COLUMNS_1=VALUE1,COLUMNS_2=VALUE2, the default value will be taken for columns that do not have an assignment, and this method can only insert one data at a time

Inserting data using text:

Load data local infile ' into table table_name;

General form of Select:

Select columns from table or tables where conditions;from generally can not be less, but when the query when there is no form of participation so do not from:select;
Select columns_1,columns_2 from table, the order of 1 and 2 appear different, then they are in a different position in the table.

Operations: div divisible (Result preserving integer part)

　　

Delete data in MySQL table:

(1) Delete from table_name [where search]
(2) TRUNCATE TABLE table_name PS: This situation can only delete all the data in the table
Delete Does not add where, and truncate is the same meaning, delete will return the number of bars, but truncate will not.

Use in () to find one or more of several cases more convenient than or, eg: "... where column_search=search1 or column_search=search2" = = "... where Columns_dearch in ( SEARCH1,SEARCH2) "

MySQL's dedicated comparator <=> (equals) can compare columns to NULL, eg:select name from table where Column_search<=>null.

How to sort the results of a query:

ASC Ascending, desc descending; After the sentence can be at the same time several ASC or DESC, their sort order is the first sort after the end of the same second sort, and so on, for the data is null, when the order of ASC, the null value is at the front, the opposite Desc, Null in the Last Face

Limit the number of bars to use:
"Can use Order by RAND () and limit n" to randomly extract record N records

Use as to give aliases:

When using as to name aliases, enclose them in single quotation marks when there are spaces in the alias:

;
You can omit the keyword as when you use the AS name

Time Type:
Year (): Extract years; month () extract months; DayOfMonth (): Extract day; Curday (): Current time; Timestampdiff () specify two date types to return the result of subtracting in some form. Eg:timestampdiff (Year,birth,death): Returns the year of difference;
To_days () Function: Returns the number of days
Date_add () and date_sub () time plus or decrease:

Wildcard characters like:
"_" one character, "%" indeterminate character

How to set up and use SQL variables
@ variable name: =column_name;
　　

How to generate statistical information:
DISTINCT column_name: Eliminating rows
Use count () to calculate different data columns: Select Column_name,count (*) from table_name GROUP by COLUMN_NAME; (the preceding column_name can be omitted, If not omitted then the front and rear column_name must be the same,)
　　
　　　　　　
The order by cannot be a function or something, and if you want to use it, use its alias

Fill in: Use the type of display table:
Use describe (desc); show [Full]columns from Table_name[where conditions];show field from Table_name;explain table_name, where "Describe table_name" = "Show columns from tables" = "show field from table_name"