Database (query Special)

1. Basic Query Statement (SELECT)

SELECT *| field List from table 1, table 2, table n WHERE expression GROUP by has the LIMIT;

SELECT Field 1, Field 2, field n from table or view WHERE query condition;

Description: The SELECT statement determines the value to query, and the FROM statement determines the source of the data.

2. Single-table query: Refers to the data required to query from a single table.

Directory

1. Querying all fields

2. Querying a specified field

3. Querying a specified record

4. Query with in keyword

5. Query scope with between and

6. A character matching query with like

7. Querying for null values

8. Multi-criteria query with and

9. With or multi-conditional query

10. Query results are not duplicated

11. Sort the results of the query

12. Group queries

13. Limit the number of query results by using limit

1. Query all fields (SELECT * from table name;) Note: Low efficiency

2. Query the specified field (SELECT field name 1, field name 2, field name n from table name;)

3. Query the specified record (SELECT field name from table name WHERE query condition;)

3.1WHERE Conditional character

equals =

Not equal to <>,!=

Less than <

Less than or equal to <=

Greater than or equal to >=

Greater than >

Located between two values between and

4. Query with in keyword (SELECT field name from table name WHERE in| Not in ORDER by;)

5. Query scope with between and (SELECT field name from table name WHERE to query field name between| Not between value 1 and value 2;)

6. A wildcard query with a like character matching query (like "%", "_"). Fuzzy Lookup.

1.% percent semicolon wildcard character. Matches any length of characters, including 0 characters.

Example: 1 begins with B b% 2 ends with B%b 3 only B is matched%b% 3 B begins with Y end b%y

2._ the underscore wildcard, which can only match any one character at a time.

Example: _ _ _ _y matches the four-letter word in front of the end of Y.

7. Query null value (SELECT field name from table name WHERE is null|is not NULL;)

8. Multi-condition query with and (WHERE conditional clause 1 and Conditional clause 2;) precedence higher than or

9. With or multi-conditional query (WHERE conditional clause 1 or conditional clause 2;) in faster, can also nest. Complete and or the same function. priority is lower than and

10. Query results are not duplicated (SELECT DISTINCT field name from table name;)

11. Sort the query results (SELECT field name 1, field name 2 from table name order BY field name 1 to sort, field name 2 to sort)

Order BY field name DESC descending order

ORDER by field name ASC Ascending order ( default )

12. Packet query (group BY field name having conditional expression;)

12.1 GROUP by

GROUP by is usually used in conjunction with a set of functions.

SELECT Number set function as field alias from table name GROUP by

12.2 having filter

SELECT field name from table name GROUP by field name having condition;

Having been used for grouping

Where is used before grouping

GROUP by field name with ROLLUP

13. Restrict the number of query results using limit (SELECT *from table name limit start Row number parameter (default 0), row number parameter;)

3. Querying using Aggregate functions

SELECT aggregate function (field name) as the newly generated virtual field name (equals rename)

AVG () return average

COUNT (*) returns the total number of rows in the table, and the Count (field name) returns the specified number of fields

Max () returns the maximum value

MIN () returns the minimum value

SUM () returns and

4. Internal connection query (INNER join)

SELECT the field name to connect from table name 1 INNER JOIN table name 2 on field 1 = Field 2;

SELECT the field name to concatenate from table name 1, table name 2 WHERE Field 1 = field 2;

5. External connection query (left join| Right JOIN)

SELECT the field name to connect from table name 1 left| Right JOIN table name 2 on field 1 = Field 2;

6. Compound condition query () add filter conditions during connection query to make the results more accurate

SELECT the field name to connect from table name 1 INNER JOIN table name 2 on field 1 = field 2 condition;

7. Sub-query

One query nested inside another query mysql4.1 started to introduce

The 7.1.any,some keyword means that you can return the result as an outer query condition if any one of the conditions is met

SELECT field name from table name WHERE condition >any| SOME (conditions);

7.2.ALL keyword requires that all inner query conditions are met before a value is returned

SELECT field name from table name WHERE condition >all (condition);

7.3.exists| Not EXISTS keyword to see if the row is returned if it is returned, if not | vice versa

7.4.IN keyword, the inner query statement returns only a single data column, and the value in this data column will be provided to the outer query statement comparison operation.

7.5 comparison operator Query.

7.6 Merging query Results (union| UNION All)

7.7 Alias for table (table name 1 as table name 1 alias)

7.8-bit column alias (column name 1 as column name 1 name )

8. Using regular expression queries (REGEXP)

SELECT * FROM table name WHERE table name REGEXP "Regular expression"

Database (query Special)