MySQL query statement -- SELECT

I would like to summarize several other experiments on high-availability clusters. Let's look back at the content summarized by others. It's very fine, and a lot of content has been extended. Sorry, I just skipped it, haha ~~~

Here we will summarize the mysql query statements:

This section describes simple database operations. Next, we will start with the details to introduce mysql query statements;

Import a jiaowu database here to perform the following operations:

Let's take a look at the contents of this database.

1. Mysql> show databases;
2. + -------------------- +
3. | Database |
4. + -------------------- +
5. | Information_schema |
6. | Jiaowu |
7. | Mysql |
8. | Test |
9. + -------------------- +
10. 4 rows in set (0.00 sec)

L> use jiaowu;

Database changed

1. Mysql> show tables;
2. + ------------------ +
3. | Tables_in_jiaowu |
4. + ------------------ +
5. | Courses |
6. | Scores |
7. | Students |
8. | Tutors |
9. + ------------------ +
10. 4 rows in set (0.00 sec)

1. Mysql> select * from students;
2. + ----- + -------------- + ------ + -------- + ------ + --------------------- +
3. | SID | Name | Age | Gender | CID1 | CID2 | TID | CreateTime |
4. + ----- + -------------- + ------ + -------- + ------ + --------------------- +
5. | 1 | GuoJing | 19 | M | 2 | 7 | 3 | 10:00:00 |
6. | 2 | YangGuo | 17 | M | 2 | 3 | 1 | 10:00:00 |
7. | 3 | DingDian | 25 | M | 6 | 1 | 7 | 10:00:00 |
8. | 4 | HuFei | 31 | M | 8 | 10 | 5 | 10:00:00 |
9. | 5 | HuangRong | 16 | F | 5 | 9 | 9 | 10:00:00 |
10. | 6 | YueLingshang | 18 | F | 8 | 4 | NULL | 10:00:00 |
11. | 7 | ZhangWuji | 20 | M | 1 | 7 | NULL | 10:00:00 |
12. | 8 | Xuzhu | 26 | M | 2 | 4 | NULL | 10:00:00 |
13. | 9 | LingHuchong | 22 | M | 11 | NULL | 10:00:00 |
14. | 10 | YiLin | 19 | F | 18 | NULL | 10:00:00 |
15. + ----- + -------------- + ------ + -------- + ------ + --------------------- +
16. 10 rows in set (0.00 sec)

1. Mysql> select * from tutors;
2. + ----- + -------------- + -------- + ------ +
3. | TID | Tname | Gender | Age |
4. + ----- + -------------- + -------- + ------ +
5. | 1 2 | HuangYaoshi | M | 63 |
6. | 3 | Miejueshitai | F | 72 |
7. | 4 | OuYangfeng | M | 76 |
8. | 5 | YiDeng | M | 90 |
9. | 6 | YuCanghai | M | 56 |
10. | 7 | Jinlunfawang | M | 67 |
11. | 8 | HuYidao | M | 42 |
12. | 9 | NingZhongze | F | 49 |
13. + ----- + -------------- + -------- + ------ +
14. 9 rows in set (0.00 sec)

This is the data used in the following example. You can refer to it first;

The first is the mysql query statement:

Query category:

Single Table query: simple query

Multi-Table query: join query

Subquery: complex Query

Joint Query

 

Select statement:

Common functions:

# Field -- indicates a field

Total number of count (*) rows

1. Mysql> select count (*) from tutors;
2. + ---------- +
3. | Count (*) |
4. + ---------- +
5. | 9 |
6. + --------- +
7. 1 row in set (0.00 sec)

Max (field) returns the maximum value.

1. Mysql> select max (age) from tutors;
2. + ---------- +
3. | Max (age) |
4. + ---------- +
5. | 93 |
6. + ---------- +
7. 1 row in set (0.00 sec)

Min (field) returns the minimum value.

Avg (field) Average

1. Mysql> select avg (age) from tutors;
2. + ---------- +
3. | Avg (age) |
4. + ---------- +
5. | 1, 67.5556 |
6. + ---------- +
7. 1 row in set (0.00 sec)

Sum () and

1. Mysql> select sum (1 + 2 );
2. + ---------- +
3. | Sum (1 + 2) |
4. + ---------- +
5. | 3 |
6. + ---------- +
7. 1 row in set (0.01 sec)

Select selects columns, where selects rows, and combines them to display the attributes of an object as a whole.

The condition is specified after where:

The following conditions can be specified:

Arithmetic comparison:

>,<, =,!,> =, <=, <=> (The result is null and no error occurs)

1. Mysql> select name, age from students where age> = 20;
2. + ------------- + ------ +
3. | Name | age |
4. + ------------- + ------ +
5. | DingDian | 25 |
6. | HuFei | 31 |
7. | ZhangWuji | 20 |
8. | Xuzhu | 26 |
9. | LingHuchong | 22 |
10. + ------------- + ------ +
11. 5 rows in set (0.00 sec)

Logical comparison of combinations:

And

Or

Not (!)

1. Mysql> select name, age from students where! (Age <= 25 );
2. + ------- + ------ +
3. | Name | age |
4. + ------- + ------ +
5. | HuFei | 31 |
6. | Xuzhu | 26 |
7. + ------- + ------ +
8. 2 rows in set (0.00 sec)

Comparison of other conditions:

Beween ...... And ......

1. Mysql> select name, age from students where age between 24 and 30
2. + ---------- + ------ +
3. | Name | age |
4. + ---------- + ------ +
5. | DingDian | 25 |
6. | Xuzhu | 26 |
7. + ---------- + ------ +
8. 2 rows in set (0.00 sec)

The in query field is in the specified list.

1. Mysql> select name, age from students where age in (18, 20, 25 );
2. + -------------- + ------ +
3. | Name | age |
4. + -------------- + ------ +
5. | DingDian | 25 |
6. | YueLingshang | 18 |
7. | ZhangWuji | 20 |
8. + -------------- + ------ +
9. 3 rows in set (0.01 sec)

Is null: the query is null.

1. Mysql> select name from students where cid2 is null;
2. + ------------- +
3. | Name |
4. + ------------- +
5. | LingHuchong |
6. | YiLin |
7. + ------------- +
8. 2 rows in set (0.00 sec)

Is not null

Like: wildcard matching

%: Match any character of any length

_: Match a single character

Regexp | rlike: Regular Expression matching

Order by: sort. The default value is asc in ascending order.

Desc: Descending Order

1. Mysql> select name, age from students where age in (22, 18, 25) order by age desc;
2. + -------------- + ------ +
3. | Name | age |
4. + -------------- + ------ +
5. | DingDian | 25 |
6. | LingHuchong | 22 |
7. | YueLingshang | 18 |
8. + -------------- + ------ +
9. 3 rows in set (0.00 sec)

Distinct: the uniqueness of the display result, which is attached to the select statement (as described in the following example, only the same cid1 is displayed once)

1. Mysql> select distinct cid1 from students order by cid1 desc;
2. + ------ +
3. | Cid1 |
4. + ------ +
5. | 18 |
6. | 11 |
7. | 8 |
8. | 6 |
9. | 5 |
10. | 2 |
11. | 1 |
12. + ------ +
13. 7 rows in set (0.00 sec)

Group by: groups the obtained results. Generally, the group results are used for aggregation operations.

Having: filter the results of a group based on conditions.

1. Mysql> select avg (age), cid1 from students group by cid1;
2. + ---------- + ------ +
3. | Avg (age) | cid1 |
4. + ---------- + ------ +
5. | 1 | 20.0000 |
6. | 20.6667 | 2 |
7. | 16.0000 | 5 |
8. | 25.0000 | 6 |
9. | 1, 24.5000 | 8 |
10. | 22.0000 | 11 |
11. | 1, 19.0000 | 18 |
12. + ---------- + ------ +
13. 7 rows in set (0.00 sec)

Limit: limit the number of lines displayed

Eg: limit 1, 2; indicates skipping the first line and then displaying the second line

1. Mysql> select avg (age), cid1 from students group by cid1 limit 1, 2;
2. + ---------- + ------ +
3. | Avg (age) | cid1 |
4. + ---------- + ------ +
5. | 20.6667 | 2 |
6. | 16.0000 | 5 |
7. + ---------- + ------ +
8. 2 rows in set (0.00 sec)

1 2 Next Page