VI. MySQL query data 02/14 Update SLTechnology News&Howtos

VI. MySQL query data

2025-02-14 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

6.1. Basic query statement

The basic statement for MySQL to query data from a data table is the SELECT statement, which has the following basic format:

SELECT {* |} [FROM,... [WHERE expression [GROUP BY] [HAVING [{}...]] [ORDER BY] [LIMIT [,] SELECT [field 1, field 2, field n] FROM [table or view] WHERE [query criteria]

The meaning of each clause:

{* |}: contains a list of asterisk wildcard selection fields, indicating the queried field whose field column contains at least one field name.

FROM,...: table 1, Table 2 shows the source of the query data

WHERE expression: optional, defining the query conditions that must be met by the query

GROUP BY: tell MySQL how to display the queried data and group according to the specified fields

ORDER BY: tells MySQL in what order to display the data of the query, and ASC,DESC that can be sorted

LIMIT [,]: tells MySQL to display the number of data items queried each time

To easily demonstrate the use of the SELECT statement, first create a data table and insert data into the table. Mysql > CREATE TABLE fruits-> (- > f_id char (10) NOT NULL,-> s_id INT NOT NULL,-> f_name char (255th) NOT NULL,-> f_price decimal (8L2) NOT NULL,-> PRIMARY KEY (f_id)->) Query OK, 0 rows affected (0.05 sec) mysql > INSERT INTO fruits (f_id, s_id, f_name, f_price)-> VALUES -> ('t1century recorder 102 minute bananas, 10.3),-> (' t2jewelry recalcitrals 102 minus grapestones, 5.3),-> ('o2jewelry recalcitrals 103 minute coconuts, 9.2),-> (' c0parallel being101reparcherryweights, 3.2),-> ('a2jewelry 103,' apricot',2.2),-> ('l2jewelry 104). 'lemon', 6. 4),-> (' b2 jewelry, 104 minutes, 7. 6),-> (m _ 1 jewelry, 10 ~ 6), 5. 6),-> (m _ 2, 4, 5, 10, 5, 6, 6),-> (6, 6),-> (m, 3, 4, 10, 7, 10, 6, 6),-> (m _ 3, 4, 10, 10, 5, 10, 6, 10, 6, 10, 6, 10, 6, 10, 6, 10, 6, 10, 10, 6, 10, 6, 10, 6, 6). -> ('b5 girls and 107 girls, 3.6) Query OK, 16 rows affected (0.09 sec) Records: 16 Duplicates: 0 Warnings: 0

6.2. Single table query

Query all fields

Syntax: SELECT * FROM table name mysql > SELECT * FROM fruits +-+ | f_id | s_id | f_name | f_price | +-+ | A1 | 101 | apple | 5.20 | | a2 | 103 | apricot | 2.20 | | B1 | 101 | blackberry | 10.20 | | b2 | 104 | berry | 7.60 | | b5 | 107 | xxxx | 3.60 | bs1 | 102 | orange | 11.20 | bs2 | melon | 8.20 | c0 | 101 | cherry | 3.20 | 12 | 104 | lemon | 6.40 | M1 | 106 | mango | 15.60 | m2 | 105 | xbabay | 2 | .60 | | m3 | 105 | xxtt | 11.60 | | O2 | 103 | coconut | 9.20 | T1 | 102 | banana | 10.30 | | T2 | 102 | grape | 5.30 | | T4 | xbababa | 3.60 | +-+ 16 rows in set (0.00 sec)

Query the specified field

Query the specified record

Syntax: SELECT field name 1, field name 2. FROM table name WHERE query condition

WHERE conditional discriminator

Operator description = equal

,! = does not equal

< 小于大于>

< 10;+---------+---------+| f_name | f_price |+---------+---------+| apple | 5.20 || apricot | 2.20 || berry | 7.60 || xxxx | 3.60 || melon | 8.20 || cherry | 3.20 || lemon | 6.40 || xbabay | 2.60 || coconut | 9.20 || grape | 5.30 || xbababa | 3.60 |+---------+---------+11 rows in set (0.00 sec) 带IN关键字的查询 IN操作符用来查询满足指定范围内的条件记录，使用IN操作符，将所有检索条件用括号括起来，检索条件之间用逗号分隔开，只要满足条件范围内的一个值即可。 mysql>

SELECT swatches, f_price-> FROM fruits-> WHERE s_id IN (101102)-> ORDER BY f_name +-+ | s_id | f_name | f_price | +-+ | 101 | apple | 5.20 | 102 | banana | 10.30 | | 101 | blackberry | 10.20 | | 101 | cherry | 3.20 | 102 | grape | | 5.30 | | 102 | orange | 11.20 | +-+ 6 rows in set (0.00 sec) mysql > SELECT s_id | F_name, f_price-> FROM fruits-> WHERE s_id NOT IN (101102)-> ORDER BY f_name +-+ | s_id | f_name | f_price | +-+ | 103 | apricot | 2.20 | 104 | berry | 7.60 | 103 | coconut | 9.20 | 104 | lemon | 6.40 | mango | 15.60 | 105 | melon | | 8.20 | 107 | xbababa | 3.60 | | 105 | xbabay | 2.60 | 105 | xxtt | 11.60 | | 107 | xxxx | 3.60 | +-+ 10 rows in set (0.00 sec)

Range query with BETWEEN AND

BETWEEN AND is used to query values within a range. The operator requires two parameters, the start and end of the range, and these records are returned if the fields meet the specified range query criteria.

Mysql > SELECT f_name, f_price-> FROM fruits-> WHERE f_price BETWEEN 2.00 AND 10.20 +-+ | f_name | f_price | +-+-+ | apple | 5.20 | apricot | 2.20 | blackberry | 10.20 | berry | 7.60 | xxxx | 3.60 | melon | 8.20 | cherry | 3.20 | | lemon | | 6.40 | | xbabay | 2.60 | | coconut | 9.20 | grape | 5.30 | xbababa | 3.60 | +-+-+ 12 rows in set (0.00 sec) mysql > SELECT f_name | F_price-> FROM fruits-> WHERE f_price NOT BETWEEN 2.00 AND 10.20 +-+-+ | f_name | f_price | +-+-+ | orange | 11.20 | mango | 15.60 | xxtt | 11.60 | banana | 10.30 | +-+ + 4 rows in set (0.00 sec)

Character matching query with LIKE

% wildcard character that matches any character of any length

Mysql > SELECT f_id, f_name-> FROM fruits-> WHERE f_name LIKE'b%' +-+-+ | f_id | f_name | +-+-+ | B1 | blackberry | | b2 | berry | | T1 | banana | +-+-+ 3 rows in set (0.00 sec) mysql > SELECT f_id, f_name-> FROM fruits-> WHERE f_name LIKE'% g%' +-+-+ | f_id | f_name | +-+-+ | bs1 | orange | | M1 | mango | | T2 | grape | +-+-+ 3 rows in set (0.00 sec)

_ wildcards: matching a single character

Mysql > SELECT f_id, f_name FROM fruits WHERE f_name LIKE'_ eBay; +-+-+ | f_id | f_name | +-+-+ | A1 | apple | | T2 | grape | +-+-+ 2 rows in set (0.00 sec)

Query null value

When you create a datasheet, you can specify that a column can contain a null value (NULL), which is different from 0 or an empty string. A null value generally indicates that the data is unknown, not applicable, or that the data will be added later. Use the IS NULL clause to query records for which the content of a field is empty.

Mysql > CREATE TABLE customers-> (- > c_id int NOT NULL AUTO_INCREMENT,-> c_name char (50) NOT NULL,-> c_address char (50) NULL,-> c_city char (50) NULL,-> c_zip char (10) NULL,-> c_contact char (50) NULL,-> c_email char (25) NULL -> PRIMARY KEY (c_id)->) Query OK, 0 rows affected (0.02 sec) mysql > INSERT INTO customers (c_id, c_name, c_address, c_city,-> c_zip, c_contact, c_email)-> VALUES (10001, 'RedHook',' 200 Street', 'Tianjin',->' 300000 Fromage Lane', 'LiMing',' LMing@163.com'),-> (10002, 'Stars',' 333 Fromage Lane',-> 'Dalian' '116000,' Zhangbo','Jerry@hotmail.com'),-> (10003, 'Netbhood',' 1 Sunny Place', 'Qingdao',' 266000,-> 'LuoCong', NULL),-> (10004,' JOTO', '829 Riverside Drive',' Haikou',-> '570000,' YangShan', 'sam@hotmail.com') Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0mysql > SELECT c_id +-+ | c_id | c_name | c_email | +-+ | 10003 | Netbhood | NULL | +-+ 1 row in set (sec) mysql > SELECT c_id, c_name C_email FROM customers WHERE c_email IS NOT NULL +-+ | c_id | c_name | c_email | +-+ | 10001 | RedHook | LMing@163.com | | 10002 | Stars | Jerry@hotmail.com | | 10004 | JOTO | | sam@hotmail.com | +-+ 3 rows in set (0.00 sec) |

Multi-conditional query with AND

Mysql > SELECT f_id, f_price, f_name FROM fruits WHERE s_id = '101' AND f_price > = 5 +-+ | f_id | f_price | f_name | +-+ | A1 | 5.20 | apple | | b1 | 10.20 | blackberry | +- -+ 2 rows in set (0.00 sec) mysql > SELECT f_id F_price, f_name FROM fruits-> WHERE s_id IN ('101,' 102') AND f_price > = 5 AND f_name = 'apple' +-+ | f_id | f_price | f_name | +-+ | A1 | 5.20 | apple | +-+ 1 row in set (0.00 sec)

Multi-conditional query with OR

Mysql > SELECT OR s_id name, f_price-> FROM fruits-> WHERE s_id = 101FF = 102mm +-+ | s_id | f_name | f_price | +-+ | 101 | apple | 5.20 | 101 | blackberry | 10.20 | 102 | orange | 11.20 | | 101 | cherry | 3.20 | 102 | banana | | 10.30 | 102 | grape | 5.30 | +-+ 6 rows in set (0.00 sec) mysql > SELECT s_id | F_name, f_price-> FROM fruits-> WHERE s_id IN (101102) +-+ | s_id | f_name | f_price | +-+ | 101 | apple | 5.20 | 101 | blackberry | 10.20 | 102 | orange | 11.20 | | 101 | cherry | 3.20 | 102 | banana | | 10.30 | | 102 | grape | 5.30 | +-+ 6 rows in set (0.00 sec) |

The OR operator is the same as the IN operator, and the same functionality can be achieved. But using the IN operator makes the retrieval statement more concise, and IN executes faster than OR.

The query results do not repeat.

Use the DISTINCT keyword in MySQL to remove duplicated records, and the syntax is:

SELECT DISTINCT field name: FROM table name: mysql > SELECT s_id FROM fruits; +-+ | s_id | +-+ | 10103The table name is mysql > SELECT s_id FROM fruits; +-+ | s_id | +-+ 16 sec mysql > SELECT DISTINCT s_id FROM fruits. +-+ | s_id | +-+ | 101 | 103 | 104 | 102 | 102 | 105 | 106 | +-+ 7 rows in set (0.00 sec)

Sort the results of a query

Single column sort

Multi-column sorting

Mysql > SELECT f_name, f_price FROM fruits ORDER BY f_name, f_price +-+-+ | f_name | f_price | +-+-+ | apple | 5.20 | apricot | 2.20 | banana | 10.30 | berry | berry | blackberry | 10.20 | cherry | 3.20 | coconut | 9.20 | | grape | 5.30 | | lemon | 6.40 | | mango | 15.60 | melon | 8.20 | orange | xbababa | 3.60 | xbabay | 2.60 | xxtt | 11.60 | xxxx | 3.60 | +-+ 16 rows in set (0.00 sec)

Sort in a specified direction

Mysql > SELECT f_name, f_price FROM fruits ORDER BY f_price DESC +-+-+ | f_name | f_price | +-+-+ | mango | 15.60 | xxtt | 11.60 | orange | 11.20 | banana | 10.30 | blackberry | blackberry | coconut | 9.20 | melon | 8.20 | | berry | | 7.60 | | lemon | 6.40 | grape | 5.30 | apple | 5.20 | xxxx | 3.60 | xbababa | 3.60 | cherry | 3.20 | xbabay | 2.60 | apricot | 2.20 | +-+ 16 rows in set (0.00 sec) mysql > SELECT f_price | F_name FROM fruits ORDER BY f_price DESC, f_name +-+-+ | f_price | f_name | +-+-+ | 15.60 | mango | 11.60 | xxtt | 11.20 | orange | | 10.30 | banana | | 10.20 | blackberry | | 9.20 | coconut | | 8.20 | melon | | 7 .60 | berry | | 6.40 | lemon | | 5.30 | grape | | 5.20 | apple | xbababa | | 3.60 | xxxx | | 3.20 | cherry | | 2.60 | xbabay | 2.20 | apricot | +-+-+ 16 rows in set (0.00 sec)

Grouping query

A grouping query groups data according to one or more fields. In MySQL, the data is grouped using the GROUP BY keyword. The basic syntax is as follows:

[GROUP BY field] [HAVING]

Create grouping

The GROUP BY keyword is usually used with collection functions, such as MAX (), MIN (), COUNT (), SUM (), AVG ().

Mysql > SELECT s_id, COUNT (*) AS Total FROM fruits GROUP BY s_id +-+-+ | s_id | Total | +-+-+ | 101 | 3 | 102 | 3 | 103 | 2 | 104 | 2 | 105 | 3 | 106 | 1 | 2 | 2 | +-+ 7 rows in set (0.00 sec) mysql > SELECT s_id, GROUP_CONCAT (f_name) AS Names FROM fruits GROUP BY s_id +-+-+ | s_id | Names | +-+-+ | 101 | apple,blackberry,cherry | 102 | grape,banana,orange | | 103 | apricot,coconut | 104 | lemon,berry | | 105 | xbabay,xxtt Melon | | 106 | mango | | 107 | xxxx,xbababa | +-+-+ 7 rows in set (0.00 sec)

Use HAVING to filter packets

Mysql > SELECT s_id, GROUP_CONCAT (f_name) AS Names-> FROM fruits-> GROUP BY s_id HAVING COUNT (f_name) > 1 +-+-+ | s_id | Names | +-+-+ | 101 | apple,blackberry,cherry | 102 | grape,banana,orange | | 103 | apricot,coconut | 104 | lemon,berry | | 105 | xbabay,xxtt Melon | | 107 | xxxx,xbababa | +-+-+ 6 rows in set (0.00 sec)

Use WITH ROLLUP in the GROUP BY clause to display the sum of all the records queried

Mysql > SELECT s_id, COUNT (*) AS Total-> FROM fruits-> GROUP BY s_id WITH ROLLUP +-+-+ | s_id | Total | +-+-+ | 101 | 3 | 102 | 3 | 103 | 2 | 104 | 2 | 105 | 3 | 106 | 1 | 107 | 2 | NULL | 16 | +-+-+ 8 rows in set (0.00 sec)

Multi-field grouping

Mysql > SELECT * FROM fruits group by sprints favored name +-+ | f_id | s_id | f_name | f_price | +-+ | A1 | 101 | apple | 5.20 | | b1 | 101 | blackberry | 10.20 | | c0 | | 101 | cherry | 3.20 | | T1 | banana | 10.30 | | T2 | 102 | grape | 5.30 | bs1 | 102 | orange | 11.20 | a2 | 103 | apricot | 2.20 | | O2 | 103 | coconut | 9.20 | b2 | 104 | berry | 7.60 | L2 | 104 | lemon | 6.40 | bs2 | 105 | melon | 8.20 | | | m2 | 105 | xbabay | 2.60 | | m3 | 105 | xxtt | 11.60 | | M1 | 106 | mango | 15.60 | T4 | 107 | xbababa | 3.60 | b5 | 107 | xxxx | 3.60 | +-+ 16 rows in set (0.00 sec) |

GROUP BY is used with ORDER BY

Mysql > CREATE TABLE orderitems-> (- > o_num int NOT NULL,-> o_item int NOT NULL,-> f_id char (10) NOT NULL,-> quantity int NOT NULL,-> item_price decimal (8 ~ 2) NOT NULL,-> PRIMARY KEY Query OK, 0 rows affected (0.09 sec) mysql > INSERT INTO orderitems (o_num, o_item, f_id, quantity, item_price)-> VALUES (30001, 1,'a _ 1, 10,5.2),-> (30001, 2,'b _ 2, 3,7.6),-> (30001, 3, 'bs1', 5,11.2),-> (30001, 4,' bs2', 15,9.2),-> (30002, 1) 'b3, 2,20.0),-> (30003, 1,' c0, 100,10),-> (30004, 1,'O2, 50, 2.50),-> (30005, 1,'c0, 5, 10),-> (30005, 2,'b1, 10,8.99),-> (30005, 3,'a2, 10, 2),-> (30005, 4,'M1, 5) 14.99) Query OK, 11 rows affected (0.08 sec) Records: 11 Duplicates: 0 Warnings: 0mysql > SELECT o_num, SUM (quantity*item_price) AS orderTotal-> FROM orderitems-> GROUP BY o_num-> HAVING SUM (quantity*item_price) > +-+-+ | o_num | orderTotal | +-+-+ | 30001 | 268.80 | | 30003 | 1000.00 | 30004 | 125.00 | | 30005 | 236.85 | +-+ + 4 rows in set (sec) mysql > SELECT o_num SUM (quantity*item_price) AS orderTotal-> FROM orderitems-> GROUP BY o_num-> HAVING SUM (quantity*item_price) > = 100-> ORDER BY orderTotal +-+-+ | o_num | orderTotal | +-+-+ | 30004 | 125.00 | | 30005 | 236.85 | 30001 | 268.80 | | 30003 | 1000.00 | +-+-+ 4 rows in set (sec)

Use LIMIT to limit the number of query results

Using the LIMIT keyword, you can limit the number of query results in the syntax format:

LIMIT [position offset,] number of rows mysql > SELECT * From fruits LIMIT 4 +-+ | f_id | s_id | f_name | f_price | +-+ | A1 | 101 | apple | 5.20 | | a2 | 103 | apricot | 2.20 | | B1 | 101 | blackberry | 10.20 | | B2 | 104 | berry | 7.60 | +-+ 4 rows in set (0.00 sec) mysql > SELECT * From fruits LIMIT 4 3 +-+ | f_id | s_id | f_name | f_price | +-+ | b5 | 107 | xxxx | 3.60 | bs1 | 102 | orange | 11.20 | | bs2 | 105 | melon | 8.20 | +- -+ 3 rows in set (0.00 sec)

6.3. Query using aggregate function

Sometimes you don't need to return the data in the actual table, but just summarize the data. MySQL provides some query functions to obtain data for analysis and reporting.

The function AVG () returns the average value of a column COUNT () returns the number of rows of a column MAX ()

Returns the maximum value of a column MIN ()

Returns the minimum value of a column SUM ()

Returns the sum of a column of values

COUNT () function

Mysql > SELECT COUNT (*) AS cust_num-> FROM customers;+-+ | cust_num | +-+ | 4 | +-+ 1 row in set (0.00 sec) mysql > SELECT COUNT (c_email) AS email_num-> FROM customers +-+ | email_num | +-+ | 3 | +-+ 1 row in set (0.00 sec) mysql > SELECT o_num, COUNT (f_id)-> FROM orderitems-> GROUP BY o_num +-+-+ | o_num | COUNT (f_id) | +-+-+ | 30001 | 4 | 30002 | 1 | 30003 | 1 | 30004 | 1 | | 30005 | 4 | +-+-+ 5 rows in set (0.00 sec)

SUM () function

Mysql > SELECT SUM (quantity) AS items_total-> FROM orderitems-> WHERE o_num = 30005 mysql + | items_total | +-+ | 30 | +-+ 1 row in set (0.00 sec) mysql > SELECT o_num, SUM (quantity) AS items_total-> FROM orderitems-> GROUP BY o_num +-+-+ | o_num | items_total | +-+-+ | 30001 | 33 | | 30002 | 2 | 30003 | 30004 | 50 | | 30005 | 30 | +-+-+ 5 rows in set (0.00 sec)

AVG () function

Mysql > SELECT AVG (f_price) AS avg_price-> FROM fruits-> WHERE s_id = 103 row in set + | avg_price | +-+ | 5.700000 | +-+ 1 row in set (0.00 sec) AVG > SELECT f_price AS avg_price-> FROM fruits-> GROUP BY s_id +-+-+ | s_id | avg_price | +-+-+ | 6.200000 | 6.200000 | 8.933333 | 5.700000 | 5.700000 | 7.000000 | 7.466667 | 7.466667 | 15.600000 | 3.600000 | +-+-+ 7 rows in set (0.00 sec)

MAX () function

Mysql > SELECT MAX (f_price) AS max_price FROM fruits;+-+ | max_price | +-+ | 15.60 | +-+ 1 row in set (0.00 sec) mysql > SELECT s_id, MAX (f_price) AS max_price-> FROM fruits-> GROUP BY s_id +-+-+ | s_id | max_price | +-+-+ | 101 | 10.20 | 102 | 11.20 | 103 | 9.20 | 104 | 7.60 | | 105 | 11.60 | 106 | 15.60 | 107 | 3.60 | +-+- -+ 7 rows in set (0.00 sec) mysql > SELECT MAX (f_name) FROM fruits +-+ | MAX (f_name) | +-+ | xxxx | +-+ 1 row in set (0.00 sec)

MIN () function

Mysql > SELECT MIN (f_price) AS min_price FROM fruits;+-+ | min_price | +-+ | 2.20 | +-+ 1 row in set (0.00 sec) mysql > SELECT s_id, MIN (f_price) AS min_price-> FROM fruits-> GROUP BY s_id +-+-+ | s_id | min_price | +-+-+ | 101 | 3.20 | 102 | 5.30 | 103 | 2.20 | 104 | 6.40 | | 105 | 2.60 | 106 | 15.60 | 107 | 3.60 | +-+-+ 7 rows in set (0.00 sec)

6.4. Join query

Connection is the main feature of relational database model. Join query is the most important query in relational database. It mainly includes internal connection, external connection and so on. Multiple table queries can be implemented by concatenating meta operators. In the relational database management system, the relationship between the various data is uncertain when the table is established, and all the information of an entity is often stored in a table. When querying, the join operation is used to query the information of different entities that exist in multiple tables.

Internal join query

The inner join uses the comparison operator to compare the data of certain columns between tables and lists the data rows in these tables that match the join conditions to form a new record.

First create the table and insert the data:

Mysql > CREATE TABLE suppliers-> (- > s_id int NOT NULL AUTO_INCREMENT,-> s_name char (50) NOT NULL,-> s_city char (50) NULL,-> s_zip char (10) NULL,-> s_call CHAR (50) NOT NULL,-> PRIMARY KEY (s_id)->) Query OK, 0 rows affected (0.03 sec) mysql > INSERT INTO suppliers (s_id, s_zip, s_call)-> VALUES (101 Inc.','Zhongshan' FastFruit Inc.','Tianjin','300000','48075'),-> (102 Supplies','Chongqing','400000','44333'),-> (103 Inc.','Zhongshan') "528437" Eat Ours','Beijing','010', 11111'),-> (105 "good Inc.','Zhengzhou','450000'," 22222')-> (106 "just Inc.','Zhengzhou','450000'," 45678 "),-> (107"DK Inc.','Zhengzhou','450000'," 33332 ") Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0

Use inner join queries between fruits and suppliers tables

If in a join query, two tables are designed to be the same table, this query is called a word join query. For example: inquire about other fruit types provided by fruit suppliers who supply fanciid = 'a1'.

Mysql > SELECT f1.f_id, f1.f_name-> FROM fruits AS F1, fruits AS f2-> WHERE f1.s_id = f2.s_id AND f2.f_id = 'a1' +-+-+ | f_id | f_name | +-+-+ | A1 | apple | | b1 | blackberry | | c0 | cherry | +-+-+ 3 rows in set (0.00 sec)

External indirect query

The outer join query queries the associated rows in multiple tables, which are divided into left outer join and right outer join

Left outer join: returns records that include all records in the left table and equal join fields in the right table

First create the table and data:

Mysql > CREATE TABLE orders-> (- > o_num int NOT NULL AUTO_INCREMENT,-> o_date datetime NOT NULL,-> c_id int NOT NULL,-> PRIMARY KEY (o_num)->) Query OK, 0 rows affected mysql > INSERT INTO orders (o_num, o_date, c_id)-> VALUES (30001, '2008-09-01, 10001),-> (30002,' 2008-09-12, 10003),-> (30003, '2008-09-30, 10004),-> (30004,2008-10-03, 10005),-> (30005,2008-10-08, 10001) Query OK, 5 rows affected (0.02 sec) Records: 5 Duplicates: 0 Warnings: query all customers in the customers table and orders table, including customers without orders mysql > SELECT customers.c_id, orders.o_num-> FROM customers LEFT OUTER JOIN orders-> ON customers.c_id = orders.c_id +-+-+ | c_id | o_num | +-+-+ | 10001 | 30001 | 10001 | 30005 | 10002 | NULL | 10003 | 30002 | 10004 | 30003 | +-+-+ 5 rows in set (0.00 sec)

Right outer join: returns records that include all records in the right table and equal join fields in the left table

# query all orders in customers table and orders table, including orders without customers mysql > SELECT customers.c_id, orders.o_num-> FROM customers RIGHT OUTER JOIN orders-> ON customers.c_id = orders.c_id +-+-+ | c_id | o_num | +-+-+ | 10001 | 30001 | 10003 | 30002 | 10004 | 30003 | NULL | 30004 | | 10001 | 30005 | +-+-+ 5 rows in set (0.00 sec)

Compound conditional join query

In the process of compound conditional join query, filter conditions are added to limit the results of the query so as to make the results of the query more accurate.

In customers table and orders table, use INNER JOIN syntax to query the order information of customers with ID 10001 in customers table mysql > SELECT customers.c_id, orders.o_num-> FROM customers INNER JOIN orders-> ON customers.c_id = orders.c_id AND customers.c_id = 10001 +-+-+ | c_id | o_num | +-+-+ | 10001 | 30001 | | 10001 | 30005 | +-+-+ 2 rows in set (sec) use INNER JOIN syntax to query internal joins between fruits and suppliers tables And sort the query results in the order of mysql > SELECT suppliers.s_id, soundname _ FROM fruits INNER JOIN suppliers _ fanciname, f_price-> FROM fruits INNER JOIN suppliers-> ON fruits.s_id = suppliers.s_id-> ORDER BY fruits.s_id. +-+ | s_id | s_name | f_name | f_price | +-+ | 101 | FastFruit | Inc. | apple | 5.20 | | FastFruit Inc. | blackberry | 10.20 | | FastFruit Inc. | cherry | 3.20 | LT Supplies | grape | 5.30 | LT Supplies | banana | 10.30 | LT Supplies | orange | 11.20 | ACME | apricot | 2.20 | ACME | coconut | 9.20 | | 104 | | | FNK Inc. | lemon | 6.40 | | FNK Inc. | berry | 7.60 | Good Set | xbabay | 2.60 | | Good Set | xxtt | 11.60 | Good Set | melon | 8.20 | Just Eat Ours | mango | 15.60 | DK Inc. | xxxx | 3. 60 | | 107 | DK Inc. | xbababa | 3.60 | +-+ 16 rows in set (0.00 sec)

6.5. Subquery

A subquery is a query in which one query statement is nested within another query statement.

Subquery with ANY and SOME keywords

The ANY and SOME keywords are synonyms that either of these conditions are met, and they allow you to create an expression to compare the list of return values of a subquery and return a result as a condition for an outer query as long as any comparison condition in the inner subquery is met.

First create two tables and insert data:

Mysql > CREATE table tbl1 (num1 INT NOT NULL); Query OK, 0 rows affected (0.02 sec) mysql > CREATE table tbl2 (num2 INT NOT NULL); Query OK, 0 rows affected (0.03 sec) mysql > INSERT INTO tbl1 values (1), (5), (13), (27); Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0mysql > INSERT INTO tbl2 values (6), (14), (11), (20) Query OK, 4 rows affected (0.05sec) Records: 4 Duplicates: 0 Warnings: 0 returns all the num2 columns of the tbl2 table, and then compares the value of num1 in tbl1 with it. As long as it is greater than any 1 value of num2, it is the result that meets the query condition. Mysql > SELECT num1 FROM tbl1 WHERE num1 > ANY (SELECT num2 FROM tbl2); +-+ | num1 | +-+ | 13 | | 27 | +-+ 2 rows in set (0.00 sec)

Subquery with ALL keyword

The ALL keyword is different from ANY in that all inner query conditions need to be met at the same time when using ALL.

Returns the values in the tbl1 table that are greater than all the values in the num2 column of the tbl2 table. The SQL statement is as follows: mysql > SELECT num1 FROM tbl1 WHERE num1 > ALL (SELECT num2 FROM tbl2); +-+ | num1 | +-+ | 27 | +-+ 1 row in set (0.00 sec)

Subquery with EXISTS keyword

The parameter after the EXISTS keyword is an arbitrary subquery, and the system operates on the subquery to determine whether it returns a row. If at least one row is returned, then the result of EXISTS is TRUE. At this time, the outer query statement will query.

Query whether there is a vendor of s_id=107 in the suppliers table, and if so, query the record mysql > SELECT * FROM fruits-> WHERE EXISTS-> (SELECT s_name FROM suppliers WHERE s_id=107) in the fruits table. +-+ | f_id | s_id | f_name | f_price | +-+ | A1 | 101 | apple | 5.20 | | a2 | 103 | apricot | 2.20 | | B1 | 101 | blackberry | 10.20 | | b2 | 104 | berry | 7.60 | | b5 | 107 | xxxx | 3.60 | bs1 | 102 | orange | 11.20 | bs2 | melon | 8.20 | c0 | 101 | cherry | 3.20 | 12 | 104 | lemon | 6.40 | M1 | 106 | mango | 15.60 | m2 | 105 | xbabay | 2 | .60 | | m3 | 105 | xxtt | 11.60 | | O2 | 103 | coconut | 9.20 | T1 | 102 | banana | 10.30 | | T2 | 102 | grape | 5.30 | | T4 | xbababa | 3.60 | +-+ 16 rows in set (0.00 sec) Query whether there is a supplier of s_id=107 in the suppliers table If it exists, query the records in the fruits table with f_price greater than 10.20 mysql > SELECT * FROM fruits-> WHERE f_price > 10.20 AND EXISTS-> (SELECT s_name FROM suppliers WHERE s_id = 107) +-+ | f_id | s_id | f_name | f_price | +-+ | bs1 | 102 | orange | 11.20 | | M1 | 106 | mango | 15.60 | | m3 | 105 | xxtt | 11.60 | | T1 | | | 102 | banana | 10.30 | +-+ 4 rows in set (0.00 sec) query whether there is a supplier of s_id=107 in the suppliers table | If it does not exist, query the records in the fruits table mysql > SELECT * FROM fruits-> WHERE NOT EXISTS-> (SELECT s_name FROM suppliers WHERE s_id = 107) Empty set (0.00 sec)

Subquery with IN keyword

When the IN keyword runs a subquery, the inner query statement returns only one data column, and the values in this data column are provided to the outer query statement for comparison.

Query the order number with f_id c0 in the orderitems table, and query the customers with order number c_idmysql > SELECT c_id FROM orders WHERE o_num IN-> (SELECT o_num FROM orderitems WHERE f_id = 'c0') according to the order number; +-+ | c_id | +-+ | 10004 | 10001 | +-+ 2 rows in set (10004 sec)

Subquery with comparison operator

6.6. Merge query results

Using the UNION keyword, you can give multiple SELECT statements and combine their results into a single result set. When merging, the corresponding columns of the two tables must have the same number of columns and data types. Use the ALL keyword without deleting duplicate rows or sorting the results. Its basic syntax is:

SELECT column,... FROM table1UNION [ALL] SELECT column,... FROM table2 queries the information of all fruits whose prices are less than 9, and queries the information of all fruits whose s_id is equal to 101,103. Use the UNION link to query the results mysql > SELECT s_id, f_name, f_price-> FROM fruits-> WHERE f_price

< 9.0 ->

UNION ALL-> SELECT s_id, f_name, f_price-> FROM fruits-> WHERE s_id IN (101103) +-+ | s_id | f_name | f_price | +-+ | 101 | apple | 5.20 | 103 | apricot | 2.20 | 104 | berry | 7.60 | 107 | xxxx | 3.60 | | 105 | | | melon | 8.20 | cherry | 3.20 | lemon | 6.40 | xbabay | 2.60 | grape | 5.30 | xbababa | 3.60 | apple | 5.20 | apricot | 2.20 | 101 | blackberry | 10.20 | cherry | 3.20 | coconut | 9.20 | +-| -+ 15 rows in set (0.00 sec) query information on all fruits whose price is less than 9 Query the information of all fruits whose s_id equals 101and 103.Use the UNION ALL link to query the results mysql > SELECT s_id, f_name, f_price-> FROM fruits-> WHERE f_price

< 9.0 ->

6.7. Aliases tables and fields

Alias the table

When the table name is long or some special queries are executed, you can specify an alias for the table for convenience, with the syntax format as follows:

Table name [AS] Table alias is orders alias o, query the order date of the 30001 order mysql > SELECT * FROM orders AS o-> WHERE o.o_num = 30001 +-+ | o_num | o_date | c_id | +-+ | 30001 | 2008-09-01 00:00:00 | 10001 | +-- -+-+ 1 row in set (0.00 sec) aliases the customers and orders tables, respectively And query mysql > SELECT c.c_id, o.o_num-> FROM customers AS c LEFT OUTER JOIN orders AS o-> ON c.c_id = o.c_id +-+-+ | c_id | o_num | +-+-+ | 10001 | 30001 | 10001 | 30005 | 10002 | NULL | 10003 | 30002 | 10004 | 30003 | +-+-+ 5 rows in set (0.00 sec)

Alias the field

The syntax format for aliasing a field is:

Column name [AS] column alias query fruits table, alias f_name fruit_name,f_price alias fruit_price, alias fruits table F1, query f_price in the table

< 8的水果的名称mysql>

SELECT f1.f_name AS fruit_name, f1.f_price AS fruit_price-> FROM fruits AS F1-> WHERE f1.f_price

< 8;+------------+-------------+| fruit_name | fruit_price |+------------+-------------+| apple | 5.20 || apricot | 2.20 || berry | 7.60 || xxxx | 3.60 || cherry | 3.20 || lemon | 6.40 || xbabay | 2.60 || grape | 5.30 || xbababa | 3.60 |+------------+-------------+9 rows in set (0.00 sec)查询suppliers表中字段s_name和s_city，使用CONCAT函数连接这两个字段值，并取列别名为suppliers_titlemysql>

6.8. Query using regular expressions

Regular expressions are usually used to retrieve or replace text content that matches a pattern, and matches special strings in the text that meet the requirements according to the specified matching pattern.

The option describes the example matching value example ^ the start string of the matching text, the'^ baggage bookmark, the end string of the matching text, the end string 'st$'test,persist. Matches any single character' b. T match bit.batt butbut*

Match zero or more of the preceding characters: 'fancinprecinct', 'frectoring', ffnrecting, fffn.'

Match the preceding character one or more times' ba+'baa,baaaa

Match contains the specified string text 'fa'fan,afa,faad [character set]

Matches any character in the character set'[xz] 'dizzy,zebra,x-sd [^]

Match any character'[^ abc] 'qwe,ret,ryrty string {n,} that is not in parentheses

Match the preceding character at least n times the'b {2,} 'bbb,bbbb,bbbbbbb string {n ·m}

Match the previous string at least n times, m times at most'b {2jue 4} 'bb,bbb,bbbb in the fruits table, query the record mysql > SELECT * FROM fruits WHERE f_name REGEXP' ^ b 'that begins with the letter' b'in the f_name field. +-+ | f_id | s_id | f_name | f_price | +-+ | B1 | 101 | blackberry | 10.20 | b2 | 104 | berry | 7.60 | T1 | | | 102 | banana | 10.30 | +-+ 3 rows in set (0.00 sec) in the fruits table | Query mysql > SELECT * FROM fruits WHERE f_name REGEXP'^ be' for records whose f_name field begins with "be" +-+ | f_id | s_id | f_name | f_price | +-+ | B2 | 104 | berry | 7.60 | +-- -+ 1 row in set (0.00 sec) in the fruits table Query the record mysql > SELECT * FROM fruits WHERE f_name REGEXP 'ytrees' that ends with the letter'y'in the f_name field +-+ | f_id | s_id | f_name | f_price | +-+ | B1 | 101 | blackberry | 10.20 | b2 | 104 | berry | 7.60 | | c0 | | 101 | cherry | 3.20 | | m2 | 105 | xbabay | 2.60 | +-+ 4 rows in set (0.00 sec) in the fruits table | Query the record mysql > SELECT * FROM fruits WHERE f_name REGEXP 'rry$' whose f_name field ends with the string "rry" +-+ | f_id | s_id | f_name | f_price | +-+ | B1 | 101 | blackberry | 10.20 | b2 | 104 | berry | 7.60 | | c0 | | 101 | cherry | 3.20 | +-+ 3 rows in set (0.00 sec) in the fruits table | Query records with the letters'a 'and' g 'and only one letter between them in the f_name field mysql > SELECT * FROM fruits WHERE f_name REGEXP' a.g' +-+ | f_id | s_id | f_name | f_price | +-+ | bs1 | 102 | orange | 11.20 | M1 | 106 | mango | 15.60 | +-+-+ -+-+ 2 rows in set (0.00 sec) in the fruits table Query the record mysql > SELECT * FROM fruits WHERE f_name REGEXP'^ ba*' that the value of the f_name field begins with the letter'b 'and followed by the letter' a'. +-+ | f_id | s_id | f_name | f_price | +-+ | B1 | 101 | blackberry | 10.20 | b2 | 104 | berry | 7.60 | T1 | | | 102 | banana | 10.30 | +-+ 3 rows in set (0.00 sec) in the fruits table | Query records mysql > SELECT * FROM fruits WHERE f_name REGEXP'^ ba+' for f_name field values that begin with the letter'b 'and followed by the letter' a'at least once +-+ | f_id | s_id | f_name | f_price | +-+ | T1 | 102 | banana | 10.30 | +-- -+ 1 row in set (0.00 sec) in the fruits table Query records with the f_name field value containing the string "on" mysql > SELECT * FROM fruits WHERE f_name REGEXP 'on' +-+ | f_id | s_id | f_name | f_price | +-+ | bs2 | 105 | melon | 8.20 | | L2 | 104 | lemon | 6.40 | | O2 | 103 | coconut | 9. 20 | +-+ 3 rows in set (0.00 sec) in fruits table Query records whose f_name field value contains the string "on" or "ap" mysql > SELECT * FROM fruits WHERE f_name REGEXP'on | ap' +-+ | f_id | s_id | f_name | f_price | +-+ | A1 | 101 | apple | 5.20 | | a2 | 103 | apricot | 2.20 | | bs2 | 105 | melon | 8. 20 | | L2 | 104 | lemon | 6.40 | | O2 | 103 | coconut | 9.20 | T2 | 102 | grape | 5.30 | +-+ 6 rows in set (0.00 sec) in the fruits table Use the LIKE operator to query records with a f_name field value of "on" mysql > SELECT * FROM fruits WHERE f_name LIKE 'on' Empty set (0.00 sec) in the fruits table, find the record mysql > SELECT * FROM fruits WHERE f_name REGEXP'[ot] 'that contains the letters' o' or't'in the f_name field. +-+ | f_id | s_id | f_name | f_price | +-+ | a2 | 103 | apricot | 2.20 | | bs1 | 102 | orange | 11.20 | | bs2 | 105 | melon | 8.20 | | | L2 | 104 | lemon | 6.40 | | M1 | 106 | mango | 15.60 | m3 | 105 | xxtt | 11.60 | | O2 | 103 | coconut | 9.20 | +-+ 7 rows in set (0.00 sec) in the fruits table Query records mysql > SELECT * FROM fruits WHERE s_id REGEXP'[456] 'that contain 4, 5 or 6 values in the s_id field +-+ | f_id | s_id | f_name | f_price | +-+ | B2 | 104 | berry | 7.60 | bs2 | 105 | melon | 8.20 | | L2 | 104 | lemon | 6.40 | | M1 | | 106 | mango | 15.60 | | m2 | 105 | xbabay | 2.60 | m3 | 105 | xxtt | 11.60 | +-+ 6 rows in set (0.00 sec) in the fruits table | Query records mysql > SELECT * FROM fruits WHERE f_id REGEXP'[^ a-e1-2] 'that contain characters other than the letter asige and the number 1x2 in the f_id field +-+ | f_id | s_id | f_name | f_price | +-+ | b5 | 107 | xxxx | 3.60 | bs1 | 102 | orange | 11.20 | bs2 | 105 | melon | 8.20 | | | c0 | 101 | cherry | 3.20 | | 12 | 104 | lemon | 6.40 | | M1 | 106 | mango | 15.60 | m2 | 105 | xbabay | 2.60 | m3 | xxtt | 11.60 | O2 | 103 | coconut | 9.20 | T1 | 102 | banana | 10.30 | T2 | 102 | grape | 5.30 | T2 | 107 | xbababa | 3.60 | +-+ | -+ 12 rows in set (0.00 sec) in the fruits table Query records mysql > SELECT * FROM fruits WHERE f_name REGEXP'x {2,} 'for which the letter' x 'appears at least twice in the f_name field +-+ | f_id | s_id | f_name | f_price | +-+ | b5 | 107 | xxxx | 3.60 | m3 | 105 | xxtt | 11.60 | + -- + 2 rows in set (0.00 sec) in the fruits table Query the f_name field value with the string "ba" at least once, and record mysql > SELECT * FROM fruits WHERE f_name REGEXP'ba {1Jue 3}'at most. +-+ | f_id | s_id | f_name | f_price | +-+ | m2 | 105 | xbabay | 2.60 | | T1 | 102 | banana | 10.30 | T4 | 107 | xbababa | 3.60 | | | +-+ 3 rows in set (0.00 sec) |

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.