MySQL learning database search statement DQL Xiaobai chapter

1. Simple retrieval of data

The student table in the blog content is:

1.1. Retrieving a Single Column

select + column name + from + table name

1.2. Retrieving multiple columns

select + column name 1, column name 2, column name 3...column n + from + table name

1.3. Retrieve all columns

select + * (wildcard) +from + table name

1.4. Retrieving distinct rows

use distinct keyword
select + distinct + column name + from + table name

For example, if we want to check how many genders there are in a class, we don't need to display the gender of every student, we only need to display the different genders, that is, distinct returns a unique value.

There is a point to note here, the distinct keyword is applied to all columns, not just the column preceding it. If two columns are given, all rows will be detected unless the two specified columns are different.

1.5. Limiting search results

use limit keyword
selet + column name + from + table name + limit + number

We usually select statements return the number of matching rows. We can use limit to limit the number of rows returned. For example, the select result can return 10 rows, and we only need the first 5 rows, so we can use the parameter after limit as 5. If the select returns 4 rows, and the limit limit is 5 rows, then 4 rows will be returned. Of course, there can be two parameters after limit, and there are two ways to write them:

1.selet + column name + from + table name + limit + number 1, number 2
2.selet + column name + from + table name + limit + number 2 + offset + number 1

It means starting from the row number represented by number 1, retrieve the row number 2. The second way of writing is supported after MySQL 5.

Note that the first row retrieved is row 0, not 1

1.6. Use fully qualified table name to search

select + table name.column name + from + database name.table name

2. Sort and retrieve data

2.1. Basic syntax

use order by keyword
select + column name (can be multiple columns) + from + table name + order by + column name

The two column names can be different

We can sort the salaries of employees on a daily basis, but some employees have the same salary. In this way, we can sort the salaries by age, etc. This is sorting by multiple columns, namely:

select + column name (can be multiple columns) + from + table name + order by + column 1, column 2

This means that if column 1 is consistent, it will be sorted according to column 2. If column 1 is inconsistent, it will not be sorted according to column 2.

2.2. Specify sort direction

The sorting we talked about above is actually the default sorting, which is in ascending order by default (letters from 'A' to 'Z', numbers from small to large)
We can also sort in descending order

use desc Keyword
select + column name (can be multiple columns) + from + table name + order by + column name desc

So what should we do with multiple sorting rules? That is, if we sort the students in a class in descending order according to their total scores, then those with the same total scores are also sorted in descending order according to their Chinese scores. Here we should pay attention to

The desc keyword applies only to the column that directly precedes it.

So when we need to sort by multiple fields, we only need to write the corresponding sorting rules after the corresponding fields. For example, if we need both fields to be in descending order:

select + column name (can be multiple columns) + from + table name + order by + column name 1 desc, column name 2 desc

In fact, we don't need to write ascending order, because the default is ascending order, the keyword is asc

According to what we have learned above, let’s make an example:
List the name, gender, and math scores of the oldest student:

select sname,ssex,smath from student ORDER BY sage desc LIMIT 1

Note the order of order by and limit

3. Retrieve Filter Data

3.1 Using a simple where clause

select + column name + from + table name where + judgment condition

where is a special operator:

1. <> : not equal to, actually the same as !=
2. between : used with and to indicate between two specified values, for example: select age from student where age between 18 and 20

Where to perform a null value check:

select + column name + from + table name + where + column name + is null

Note that is is used here instead of =

3.2 Combining where clauses

Function: and must satisfy the conditions on both sides of and at the same time; or only satisfies the condition on one side of or. in is used to specify the range of conditions; not is used to negate the content of the condition.

Take the student table as an example:
1.and: We find all the information of girls who scored more than 90 points in mathematics

SELECT * FROM student where ssex="女" and smath>90

2.or: We look for the names of students who are older than 19, or whose English scores are greater than 90.

SELECT sname FROM student where sage>19 or senglish>90

3.in: We find the names of students aged 18 and 19

select sname from student where sage in(18,19)

4.not: We look for names of students other than those aged 18 and 19.

select sname from student where sage not in(18,19)

5. We find all the information of students whose age is not 20 and sort them in descending order of math scores, just the second row:

select * from student where sage not in(20) GROUP BY smath desc LIMIT 1,1

We pay attention to the order of their keywords and the meaning of the parameters after limit.

3.3 Using Wildcards

Wildcards: Special characters used to match part of a value.
like: compare based on wildcard matching instead of direct equality matching

Take the student table as an example:
1.%: We search for all the information of students whose names contain flowers

select * from student where sname like "%花%"

There will be two results, Cuihua and Ruhuasiyu. % will only match one side (regardless of string length). If it is just "%hua", Ruhuasiyu will not be found. Of course, if we want to find "ruhuasiyu", we can also do this:

select * from student where sname like "%花%玉"

2._: We search for the outstanding student Ruhuasiyu:

select * from student where sname like "_花_玉"

% can match 0 characters and a string of unlimited length, while _ can only and must match 1 character

4. Use regular expressions to retrieve data

Regular expressions are special strings used to match text. They compare a pattern (regular expression) with a text string.

Common symbols in regular expressions:

Keywords: regexp
select + 列名(可以多個列) + from + 表名+ where + 列名+ regexp + 正則表達式

Take the student table for example:
1. Let's first talk about the difference between regexp and like

Like matches the entire string while regexp matches a substring

For example:
We use like to match the name of the student whose type is 1.

select sname from student where othername like "1"
result:
Sister Feng

We replace like with regexp:

select sname from student where othername regexp "1"
result:
Cuihua Fengjie Qiuxiang

Now you have a clear understanding of the difference. 2 . is to match any character. For example, we find the names of students whose Chinese scores are 8:

select sname from student where schinese regexp “.8”
result:
Fengjie Qiuxiang

3. |
For example, let's find the names of students whose aliases contain 1 ton or 2

select sname from student where othername regexp “1 ton|2”
Search results:
Cuihua brings wealth like flowers and jade

4. [....]
For example, we search for the names of students whose aliases are 1 ton or 2 ton.

select sname from student where othername regexp “[1|2] ton”
Running results:
Cuihua Wangcai

In fact, the above is equivalent to select sname from student where othername regexp “1 ton|2 ton”
Also equivalent to select sname from student where othername regexp “[12] ton” 5. -
For example, find the names of students whose aliases contain lowercase English letters

select sname from student where othername regexp “[az]”
Running results:
Cuihua Wangcai Qiuxiang is as beautiful as a flower and jade

6. \\
For example, let's find the names of students whose aliases contain the character .

select sname from student where othername regexp “\\.”
Find results;
none

The special symbols we encounter, such as \, [,], etc. can be escaped with \\

7. Instead of using the student table as an example, ? ’s take the example from the book:

Pay attention to this sentence:? Matches 0 or 1 occurrence of any preceding character

8.{n} Take another example from the book (the same goes for the other braces):

Here is a matching character class:

9. ^ Still using the example from the book:

Of course, ^ can not only refer to the beginning of a string, but also indicate negation in a set (that is, in []).

Here is an example to express negation:
[^az]: means matching characters that are not az. Let's take the student table as an example: find the names of students whose aliases do not start with a and 1.

select sname from student where othername regexp “^[^a1]”

The first ^ is the beginning of the restriction, and the second ^ is a negation.

10. One more thing: case-insensitive search

Keywords: binary

The strings we usually query will ignore the case, so we can use binary to ignore the case.

select + column name (can be multiple columns) + from + table name + where + column name + regexp + binary + regular expression

The rest are not that important, try it yourself

5. Retrieve calculated fields

5.1 Using splicing fields

Use concat(str1,str2,....) function

For example, if a math teacher has a request to print out all the students' math scores in the format of "Math Score (Name)", then

select concat(smath,"(",sname,")") from student

Running results:

5.2 Using Aliases

Keywords: AS

Using the example of 5.1

SELECT CONCAT(LTRIM(smath),"(",sname,")") AS "数学成绩" from student

result:

AS can be omitted, and the above example can also be written as

SELECT CONCAT(LTRIM(smath),"(",sname,")") “Mathematics scores” from student. Aliases are supported after group by, having, and order by

5.3 Performing Arithmetic Calculations

For example, we can find the total score and name of each student:

SELECT sname as “姓名”,smath+schinese+senglish as “总成绩” from student

Running results:

6. Use functions to retrieve data

6.1 Common text processing functions:

Take upper as an example. We use the upper function to query the alias of our classmates:

SELECT UPPER(othername) as “alias” from student

Running results:

6.2 Date and time processing functions:

Date format:

Let's take a few important functions as examples:

1.now(): Returns the current system date and time

select now()

Running results:

2.curdate(): Returns the current system date, excluding time

select curdate()

Running results:

3.curtime(): Returns the current time, excluding the date

select curtime()

Running results:

4. YEAR(), Hour(), etc.: Get the specified part

select year(now())

Running results:

5. For the date_format() function, convert the current time into a string:

select DATE_FORMAT(now(),"%Y-%c-%d")

Running results:

There is a corresponding function for converting a string to a date:
example:

select STR_TO_DATE("2001-09-10","%Y-%c-%d")

Running results:

6.3 Numerical processing functions:

6.4 Aggregate Functions:

1. AVG() function example: Let's take the student table as an example. We want to find the average math score of the students.

select avg(smath) as "Average score in mathematics" from student

Running results:

2. COUNT function The general COUNT() function has two uses

• 1 Use count(*) to count, regardless of whether the column is null value
• 2. Use count(column) to count the values ​​in a specific column, ignoring null values

Examples of usage:

select count(*) from student

Running results:

3. Specify different values

Keywords distinct

If we find out how many different ages there are in the student table;

select DISTINCT sage as "age category" from student

Running results:

4. Note

avg(), min(), max(), sum() functions will ignore null values

7. Group retrieval data

7.1. Simple grouping

Keywords group by

Let’s take an example:
Count the number of boys and girls in the student table

select ssex,COUNT(*) from student GROUP BY ssex

Running results:

Note:

1. If there is a null value in the group, then null is returned as a group. If there are multiple null values, then they are grouped together.
2. Group by must be used after the where clause

7.2 Filtering Groups:

It is to filter the data after simple grouping by keywords having

For example, we count the number of boys in the student table whose math scores are above 80

select ssex,COUNT(*) from student where smath>80 GROUP BY ssex HAVING ssex="男"

Running results:

The difference between where and having:

where filters before grouping, having filters after grouping

8. Use subqueries to retrieve data

8.1 Introduction to Subqueries

1. Subquery: A statement that appears in other statements is called a subquery or inner query. A query that embeds other select statements is called an outer query or main query.

2.(1) Subquery classification:
By where the subquery occurs
Select the back + ( Only supports scalar subqueries)
from behind + ( Supports table subqueries)
where or having followed by + (Supports scalar subquery, column subquery, row subquery)
exists after +( Table subquery)

2. (2) According to the number of rows and columns in the result set:
Scalar subquery: The result set has only one row and one column)
Example subquery: Result set one column multiple rows)
Row subquery: ( The result set usually has multiple columns in one row)
Table subquery: The result set is usually multiple rows and columns)

8.2 Examples of various subqueries:

1. Subqueries after where and having:
Example 1: We query the student table for all the information of students whose math scores are higher than Cuihua's ( Scalar subquery):

select * from student where smath>(select smath from student where sname="Cuihua")

Running results:

Example 2: If the scores of the boy Ruhuasiyu in Chinese, Mathematics and English are all greater than 80, then display the information of all classmates ( Row subquery):

select * from student where (80,80,80)<(select schinese,smath,senglish from student where sname="如花似玉")

Running results:

Example 3: Use where to query the names of female classmates ( Example subquery) (rather simple example)

SELECT a.sname FROM student a where a.smath in (select smath from student where ssex="女")

Running results:

2. Select the following subquery ( Scalar subquery)
We find the number of awards won by each student:
Here we introduce a prize table:

select sname,(select count(*) from prize where student.sname=prize.sname ) as “Number of awards” from student

Running results:

3. From the table subquery behind:
Example: We look for the names of girls who scored above 80 points in math:

select s.sname from (select * from student where ssex="女") as s

Running results:

4. Table subquery after exists

The function of exists is to judge whether it is empty

Example: We query the names of students whose math scores are 100

SELECT a.sname FROM student a where EXISTS(select a.smath from student where a.smath=100 )

Running results:

Note that you cannot write:

SELECT a.sname FROM student a where EXISTS(select smath from student where smath=100 )

There is no alias. This way of writing will first run the select in exists. As long as there is one true in exists, the names of all the students will be queried. Otherwise, the names of all the students will not be found. So if you write it as above, since there is one student whose math is equal to 100, and the others are not 100, then if there is one true, the names of all the students will be found. However, after using the alias, each student will be judged once, and a name will be output for each successful judgment.

8.3 Correlated Subqueries

Correlated subquery: A subquery that involves an outer query

We query the average math scores of boys and girls in the student table:

select ssex as sssex ,(select avg(smath) FROM student where sssex=student.ssex) as “Average score” from student GROUP BY ssex

Running results:

We can see that I gave the first ssex an alias and the second one a reference. If we use ssex=ssex directly, we will get an error. Correlated subqueries avoid ambiguity.

9. Connection query

We add an information table based on the student table to represent student information (including height, weight and student number)

9.1 Connection Query

Classification;

Connection query is divided into inner connection and outer connection cross connection
1. Inner join: equijoin, non-equijoin, self-join
2. Outer join: left outer join, right outer join, full outer join
3. Cross-connect

1. Equivalent and non-equivalent inner joins ( sql92 version)
Take an example of an equijoin: combine the student table and the information table to query the student's name, gender, age, height, and weight.

select student.sname,ssex,sage,slength,sweigth from student,information where student.sname=information.sname

Running results:

Inner join of equal value, non-equivalued join ( sql99 version)
grammar:

select + query list + from table 1 (alias) + (inner) join table 2 (alias) + on join condition + (where) + (group by) + (having) + (order by)

Write the above sql92 version example using sql99 statement

select student.sname,ssex,sage,slength,sweigth from student join information on student.sname=information.sname

The difference from sql2 syntax is that the two tables are separated by join, and the difference from sql92 is that sql92 syntax puts the join condition and other filter conditions in where, while sql99 puts the join condition after on and other filter conditions after where.

2. Inner join self join ( sql92 version):
Here’s a verbal example:
For example: In an employee table, there are employee numbers and corresponding numbers of leaders. If you want to find the name of an employee and the name of his leader, you will have

select a.name,b.name from employee table a,employee table b where a.number = b.leader number

Here, one table is considered two tables, but these two tables must be given aliases, otherwise there will be ambiguity. They must be written in this format

3. Inner join of multiple tables equal join:

select + query list + from table 1 (alias) + (inner) join table 2 (alias) + on join condition + (inner) join table 3 (alias) + on+join condition + (inner) join table 4 (alias) + on join condition + ..... + (where) + (group by) + (having) + (order by)

4. Left outer join of outer join:

Outer joins are used when one table has records that the other table does not have.

Left outer join basic syntax:
SELECT + query linked table + FROM + main table (alias) + LEFT (OUTER) JOIN + slave table (alias) + ON connection condition

Right outer join basic syntax:
SELECT + query linked list + FROM + slave table (alias) + right (OUTER) JOIN + master table (alias) + ON connection condition

Outer join characteristics (left outer right outer):

1. The outer join will query all records in the main table
2. If the main table has it but the secondary table does not, then it will display null

In general, outer join = inner join + data in the main table that is not in the slave table

We introduce a new table abord (the number of times students in the student table have studied abroad)

example:
We query the number of times each student has studied abroad (we use a left outer join):

select a.sname,b.scount from student a left JOIN abord b on a.sname=b.sname

Running results:

5. Full outer join characteristics

The outer join will query all records in the primary table and the secondary table

10.Joint query

Keyword union

Application scenario: When the information we query comes from multiple tables and there is no direct connection between the multiple tables, but the queried field information is consistent (for example, both tables query name and gender, so that they can be combined into one table, and the query fields must be aligned. For example, if both tables query name and gender, the order of the first select field must be consistent with the second select, that is, name first and gender second, or gender first and name second)

For example:
Find the name and gender of students aged 18 and 19 in the student table:

SELECT sname,ssex from student where sage=18 UNION SELECT sname,ssex from student where sage=19

Running results:

Let’s see the effect of deduplication: We query the name and gender of the classmate who is 18 years old twice

SELECT sname,ssex from student where sage=18 UNION SELECT sname,ssex from student where sage=18

Running results:

But if we use union all

SELECT sname,ssex from student where sage=18 UNION all SELECT sname,ssex from student where sage=18

Running results:

11. Subqueries

11.1 Single-row subquery

Single-row subquery, the query result can only be one row of data, and the subquery cannot contain order by clause

Example (querying employee information that is neither the highest nor the lowest salary in emp):

select empno,ename,sal,from emp where sal>(select min(sal) from emp) and sal<(select max(sal) from emp);

11.2 Multi-row Subqueries

Subquery returns multiple rows of data using IN , ANY or ALL

Example: Query the emp table for employee information of other departments whose salary is greater than the salary of any employee in department number 10.

select * from emp where sal > any(select sal from emp where deptno=10 ) and deptno<>10

11.3 Correlated Subqueries

In single-row subqueries and multi-row subqueries, the inner and outer queries are executed separately. Some inner queries are executed with the help of outer queries, and the execution of outer queries is inseparable from the inner query. In other words, the inner and outer queries are related. Such subqueries are called correlated subqueries.

example:
Use a correlated subquery to retrieve information about employees whose salary is greater than the average salary of the same position

select empno,ename,sal from emp f where sal>(select avg(sal) from emp where job=f.job) order by job

In the above example, the subquery needs to use the job title in the outer query table, and the outer query needs to use the job information from the subquery.

12. Supplement

12.1 Natural Query

When a natural query retrieves multiple tables, it will automatically connect the tables with the same columns in the two tables (natural connections require the same column names)
Keywords: natural join

for example:

select empno,ename,job,dname from emp natural join dept where sal>2000

Our employee table and dept both have empno columns, so at this time, the system will automatically connect when querying

12.2 Cross-connect

A cross join is a join that does not require any join conditions (the two tables do not need to have any relationship), and the result of the execution is a Cartesian cross join keyword: cross join

select column 1, column 2 from table 1 cross join table 2

The above is the detailed content of the MySQL database search statement DQL for beginners. For more information about MySQL search statement DQL, please pay attention to other related articles on 123WORDPRESS.COM!