Beginner MySQL Basic knowledge note--02

Query Section

1> all data in query data: SELECT * FROM table name
2> the data of an item in the query data: Eg:select id,name from students;
3> de-duplicated rows: eg:select distinct gender from students;
(means to delete the duplicate row in gender)
* Note: eg:select distinct id,gender from students;

You need to repeat both lines to not appear, for example, if the IDs are different, then show them all.

Select * FROM table name where condition ;

• Comparison operator: equals =, greater than =, greater than or equal to >=, less than <, less than equals <=, not equal to! = or <>
• Logical operators: And,or,not
• Fuzzy query: like,% represents any number of any character, _ represents any one character
• Range query: In indicates a non-contiguous range of queries, the range is placed in the in () brackets

Eg:between min and Max represents the range between the minimum and maximum

(when there are multiple and in a sentence, then the nearest and the between is with him, the rest is logic and)

• null and ': null is NULL, does not account for memory, and NULL is NULL. "refers to an empty string, to point to space.
• Precedence: High to Low: parentheses, not, comparison operators, logical operators.

And the first operation if it appears at the same time and you want to count or, enclose the parentheses.

Aggregation (5 aggregate functions)

• COUNT (*): Calculates the total number of rows, in parentheses, * and the column name result is the same

Eg:select Count (*) from students;//query number of students

• Max (column): Indicates the maximum value for this column

Eg:select Max (ID) from students;//query number Max

• Min (column): Indicates the minimum value to be calculated for this column

• SUM (column): Sum, requires data type at this time

• AVG (column): Find the average of this column

Get query-specific content with aggregations (subqueries):
Eg:select * from students where id= (the Select min (ID) from students where isdelete=0);

Grouping

Syntax: Select column name from table name Group BY column name
Eg:select Gender,count (*) from students group by gender;
According to the sex group, how many people were there?

data filtering after grouping

　　whereand have: where is the filter for the original set, and the having is to group the result set after filtering.
Syntax: Select column 1, column 2, aggregation ... from table name Group BY column 1, column 2, column 3 ... having column 1, ... Polymerization...
Eg:select Gender,count (*) from students group by gender have gender=0;
Select Gender,count (*) as RS from students group by gender have rs>2; (RS is an alias for Count (*))

Sort

　　Order by column 1 asc|desc column 2 Asc|desc
(the column does not write after the default from small to large arrangement)
1. ASC ascending, small to large
2. Desc descending, from large to small
(There are multiple columns, and if the previous columns are the same size, continue to compare the subsequent columns)

page Out

Syntax:select * FROM table name
　　　　　Limit Start,count
(starting from Start, get count bar data)

Paging: Limit N*m,m

　　Blog Address: http://www.cnblogs.com/yudanqu/

Beginner MySQL Basic knowledge note--02