Shulou(Shulou.com)06/01 Report--

Select statement execution process:

START- > 1.FROM

-> 2.WHERE (select, appropriate line)

-> 3.GROUP BY (grouping)

-> 4.HAVING (filter packets)

-> 5.ORDER BY (sort)

-> 6.SELECT (projection, appropriate field)

-> 7.LIMIT-> end result

Select single table query:

Keywords:

DISTINCT # data deduplication example: select DISTINCT gender from students;VARIABLES # mysql server built-in variables example: select variables like 'query%';AS # display using aliases example: select name as stuname from students;IN example: select name,age from students where age in (18meme 19mem25) IS NULL # value is empty, IS NOT NULL: value is not empty like #% any length any character _ any single character RLIKE # use regular expression GROUP # to group query results according to specified conditions for aggregate operation built-in functions: avg (), max (), min (), count () Sum () order by # sorts the query results according to the specified field in ascending order: ASC (default) descending order: DESCLIMIT [[offset,] row_count] # limits the number of output rows on the query results and imposes' locks': FOR UPDATE: write locks on data requests in the query results Exclusive lock, exclusive lock LOCK IN SHARE MODE: read lock, shared lock

Ex.: check the average age of male and female students

Select avg (age), gender from students group by gender

Example: check the gender with an average age of more than 20

Select avg (age), gender as' age 'from students group by gender having age > 20

Example: check the name, and the age is sorted in reverse order.

Select name,age from students order by age desc

Ex.: from childhood to age, check the names of the students ranked 11 to 20

Select name,age from students order by age limit 10,10

Exercise:

1. In the students table, the names and ages of male students over 25

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

two。 Show the average age of each group according to classID

Select avg (age), classID from students where classID is not null group by classID

3. Show the grouping and average age of the average age greater than 30 in the second question.

Select avg (age), classID from students group by classID having avg (age) > 30

4. Display information about students whose names begin with L.

Select * from students where name like'L%'

5. Display information about students whose teacherID is not empty

Select * from students where teacherID is not null

6. Sort by age to display the information of the top 10 oldest students.

Select * from students order by age DESC limit 10

7. There are three ways to query the information of students who are 20 years old or 25 years old or older.

Select * from students where age > = 20 and age (select avg (age) from students)

(2) subqueries used in IN: subqueries should be queried with a single button and return one or more values to form a list.

Ex.: find the age of the teacher and the age of the classmate

Select name,age from students where age in (select age from teachers)

(3) for EXISTS

For subqueries in the from clause:

For example, look for classes with an average age of 30:

Select s.aageds.classID from (select avg (age) as aage,classID from students where classID is not null group by classID) ass where s.aage=30

Union query: merges the results of two table queries into one. The fields of the previous table shall prevail, and the following table will be filled with content.

Example:

Select name,age from students UNION select name,age from teachers

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.