MySQL SELECT statement

Description: The first line of the MySQL offset is 0

A position refers to the field that appears in the SELECT statement, such as 1, that represents the first occurrence of a field to be grouped.

SELECT statement:

Select Select_expr1 ", Select_expr2 ..."

【

From table name

"WHERE where_condition"

"GROUP by {column name | position}" ASC | DESC ",....."//Group query results such as: SELECT Sex,id from TB1 Group by sex;

"Having where_condition"// grouping conditions, such as: SELECT sex from TB1 GROUP by sex have count (ID) >2; Group of sex columns with a value greater than 2 for the ID column in the TB1 table /c2>

"ORDER by {column name |expr| location}" "ASC | DESC ",....."// sorts the results of the query, such as: SELECT * from TB1 ORDER by ID Desc,sex ASC; line by ID in descending order

items that do not satisfy the bar are then sorted in ascending order of sex.

"Limit {" offset, "line number | number of rows offset offset}"//Limit the number of returned query results, such as: SELECT * from TB1 limit 2; return the first two rows

SELECT * from TB1 LIMIT 2, 2, starting from the second row, returning 2 rows

】

The order of the select_expr will affect the order of the results, such as: select Id,username from Tb1; and select Username,id from TB1; the results are different.

Select_expr can use "as" alias_name to give it an alias, the alias of the field will affect the result set, alias can be used for group By,ordre by or having, such as: SELECT ID as userid,username as uname from TB1;

* represents all columns, tbl_name.* can represent all columns under the data table, such as: Tb1.id represents the ID column under TB1 of the data table.

MySQL-supported functions or operators can be used in where_condition.

to display the contents of multiple tables through a connection:

syntax structure of the connection:

Table 1 {"INNER |  Cross JOIN | {left | Right} "OUTER" JOIN} table 2 on conditional_expr (connection condition)

Connection type:

INNER join equals join equals cross join equivalent to INNER join called INNER JOIN

The left "OUTER" join is referred to as an outer join

The right "OUTER" join is referred to as the left outer join

SELECT ... INNER join ... "INNER join ..." Displays the left and right table records that meet the join criteria

SELECT ... "Left join ..." shows all the records of the right table and the records that match the link criteria.

SELECT ... Right join ... "Join ..." displays all records of the table and the records of the left table that match the link criteria.

By create ... Select to create the data table and write the records to the select query at the same time:

>CREATE TABLE tdb_goods_brands (

->brand_id SMALLINT UNSIGNED PRIMARY KEY auto_increment,

->brand_name VARCHAR (+) not NULL

- ) SELECT brand_name from Tdb_goods GROUP by Brand_Name;

Self-Connection:

--Find all categories and their parent classes
SELECT S.type_id,s.type_name,p.type_name from Tdb_goods_types as S left JOIN tdb_goods_types as P on s.parent_id = P.type _id;
--Find all classes and their subclasses
SELECT p.type_id,p.type_name,s.type_name from Tdb_goods_types as P left joins Tdb_goods_types as s on s.parent_id = P.type _id;

MySQL SELECT statement