Summary of mysql query statements

1. Basic composition

(1)The table to be queried (single table, multiple tables)

(2)The information to be queried (field information, filtering processing)

(3) Query conditions (field association, field value range, record interception settings, sorting method, grouping method, deduplication, or, and)

2. Example display (taking the user table as an example)

2.1 Query a single table (user)

(1) Query all fields of a single table

select * from user;(select is followed by the field name, from is followed by the table name , * represents all fields, where followed by conditions)

(2) Query specific fields of a single table

select user_id,user_name from user; (field names are separated by ",")

(3) The usage of count(), sum(), max(), and min() for querying the total number of records in a single table is the same. The last three function parameters cannot be *.

select count(*) from user;

(4) Query a single table, group by user_id to count the total number of records in each group, and sort by user_id in reverse order

select count(* ) from user group by user_id desc;

Note: When there is only one grouping field, you can directly add desc at the end for reverse order. The default is forward order. You can also add asc

(5) Query Single table, group by user_id, user_name to count the total number of records in each group, and in reverse order by user_id

select count(*) from user group by user_id,user_name order by user_id desc;

Note: group By and order by are used at the same time. The sorted field user_id must appear in the grouping field (user_id, user_name)

(6) Query a single table, the condition is a certain field value range

user_id> =1 and<=2: select * from user where user_id>=1 and user_id<=2;

user_id is between 1 and 2: select * from user where user_id between 1 and 2;

user_id is contained in (1, 2): select * from user where user_id in (1, 2);

user_id is 1 or 2: select * from user where user_id=1 or user_id=2 ;

(7) Query a single table and intercept data limit index, length

Intercept item 1: select * from user limit 1; or select * from user limit 0,1;

Interception of Article 2: select * from user limit 1,1;

(8) Query a single table and remove duplicate distinct

select distinct user_name from user;

(9) having keyword, can be used with the total function;

select count(*) from user group by user_id desc having max(user_weight)<100;

2.2 Query Multiple tables (user, order)

(1) inner join (only return matching values)

select * from user inner join order on user.user_id=order.user_id;

(2) left join (return matching value and remaining value of left table)

select * from user u left join order o on u.user_id=o.user_id;

Note: u and o are aliases, use

(3) right join (return matching value and remaining value of the right table)

select * from user right join order on user.user_id=order.user_id;

(4) full join (return all values)

select * from user full join order on user.user_id=order.user_id;

The above is the detailed content of Summary of mysql query statements. For more information, please follow other related articles on the PHP Chinese website!