MySQL Query Statement Select explain and practice

Select statement execution Process:

START ———— >from------>where (SELECT, appropriate line)--------->group by (grouping)---------->having (filtering the grouping) ———— >order by ( Sort) ———— >select (projection, suitable field) ———— >limit ———— >end Result

Select Single-Table query:

DISTINCT: Data de -duplication: Select DISTINCT gender from students;

Variables:mysql server itself built-in variables example:select VARIABLES like ' query% ';

As: Display with alias example:select Name as Stuname from students;

In: or example: Select Name,age from students where age in (18,19,25);

is null: The value is empty is NOT null: value not null

Like:% arbitrary length any character _ any single character

Rlike: Using regular expressions

Group: Groups query results according to specified criteria for aggregation operations

Built-in functions: AVG (), Max (), Min (), count (), SUM ()

ORDER By: Sort the results of a query based on a specified field

Ascending: ASC (default) Descending: DESC

Limit [[Offset,]row_count]: Limits on the number of output rows for the results of a query

to impose a ' lock ' on the data request in the query result:

For UPDATE: Write lock, exclusive lock, exclusive lock

Lock in SHARE MODE: Read lock, shared lock

Example: Viewing the average age of male and female classmates

Select AVG (age), gender from students group by gender;

Example: See groups of boys and girls with an average age greater than 20

Select AVG (age), gender as ages from students group by gender have age >20;

Example: View name, age in reverse chronological order

Select Name,age from students order by age DESC;

Cases:

Select Name,age from students order by age limit 10,10; see the top 10 10

Exercises :

1. In the students table, the age is greater than 25, and the name and age of the male student

Select Name,age from students where gender= ' m ' and age>25;

2. Based on ClassID, show the average age of each group

Select AVG (age), ClassID from students where ClassID are NOT null GROUP by ClassID;

3. Show the group with average age greater than 30 in the second question and the average age

Select AVG (age), ClassID from students group by ClassID have AVG (age) >30;

4. Show information about the students whose names begin with L

SELECT * from students where name is like ' l% ';

5. Show Teacherid non-empty students of the relevant information

SELECT * from students where teacherid are NOT null;

6. Show information for the top 10 students of the oldest age after sorting

SELECT * FROM students order BY age DESC limit 10;

7. Check the information of students who are older than or equal to 20 years old, less than or equal to 25 years old, using three ways

SELECT * from students where age>=20 and Age<=25;select * from students where age between and 25;select * from Students where age in (20,21,22,23,24,25);

Select multiple Table query:

Cross join: also called the Cartesian product, the result is a multiplication of two table rows (not commonly used). Example: SELECT * from Table1,table2;

Internal connection:

Equivalent connection: let two or more tables establish a connection relationship by "equivalent" (common)

Cases:

SELECT * from Students,teachers where students.teacherid=teachers. TID; Select S.name,c.class from students as s,classes as C where s.classid=c.classid;

No equivalent connection:

Natural connections:

Self-connect: The value of one field in a table equals the value of another field.

Cases:

Select S.name,t.name from students as s,students as T where S.teacherid=t.stuid;

External connection:

Left outer connection: the left table is the same as a field equivalent to establish a connection relationship, such as the left table has the right table has one by one corresponding, such as the left table has the right table has no left table shows all, the right table blank corresponding. (Show the left table all, the right table has the corresponding not to leave blank)

Use method: From tb1 to left JOIN tab2 on Tab1.col1=tab2.col;

Cases:

Select S.name,c.class from students as s left joins classes as C on S.classid=c.classid;

Right outer connection:

How to use: from TB1 right JOIN tab2 on Tab1.col1=tab2.col

Sub-query: query statements nested in query statements (MySQL support is not good, less use)

Query again based on the result of a statement

Subquery used in the WHERE clause:

(1) Subqueries that are used in comparison expressions can only return a single value:

Cases:

Select Name,age from students where age> (select Avg. from students);

(2) subqueries used in in: subqueries should single-key queries and return one or more value-constituent lists

Example: Find teacher age and classmate age equal

Select Name,age from students where-age-in (select-age from Teachers);

(3) for exists

For subqueries in the FROM clause:

Cases:

Select S.aage,s.classid from (select AVG (age) as aage,classid from students where ClassID are NOT null GROUP by ClassID) as s where S.aage30;

Federated Query : Merges the results of two table queries into one.

Cases:

Select Name,age from Students UNION select name,age from teachers;

This article is from the Linux OPS blog, so be sure to keep this source http://arm2012.blog.51cto.com/2418467/1980507

MySQL Query Statement Select explain and practice