MySQL database (v) Retrieval of table records

First, the basic query statement

Second, single-table query

1. Querying all fields

Mysql> SELECT * from tb_bookinfo;+----------+-----------+--------+-----------+------+-------+------+----------+ ------------+------+----+| Barcode |  bookname |  typeid | author    | ISBN | Price | Page | Bookcase | InTime     | del  | ID |+----------+-----------+--------+-----------+------+-------+------+----------+-------- ----+------+----+| 17120107 | Java King |      3 | Lianjiang |  | 49.80 |  |        1 | 2018-04-17 |    0 |  1 | | 17120108 | Lian      |      1 | Qiaojiang | 50.00 |  351 |        2 | 2018-04-18 |    0 |  2 | | 17120109 | Tian King |      2 | Tianjiang | 117  | 51.10 |  352 |        3 | 2018-04-19 |    0 |  3 |+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+3 rows In Set (0.00 sec)

2. Querying a specified field

Mysql> SELECT Bookname,author from tb_bookinfo;+-----------+-----------+| BookName  | author    |+-----------+-----------+| Java King | Lianjiang | | Lian      | Qiaojiang | | Tian King | Tianjiang |+-----------+-----------+3 rows in Set (0.00 sec)

3. Querying the specified data

Mysql> SELECT * from Tb_bookinfo WHERE bookname= ' Tian King ', +----------+-----------+--------+-----------+------+-- -----+------+----------+------------+------+----+| Barcode |  bookname |  typeid | author    | ISBN | Price | Page | Bookcase | InTime     | del  | ID |+----------+-----------+--------+-----------+------+-------+------+----------+-------- ----+------+----+| 17120109 | Tian King |      2 | Tianjiang | 117  | 51.10 |  352 |        3 | 2018-04-19 |    0 |  3 |+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+1 row In Set (0.00 sec)

4. Query with in keyword

Mysql> SELECT bookname,author,price,page,bookcase from Tb_bookinfo WHERE bookcase in (up); +-----------+----------- +-------+------+----------+| BookName  | author    | price | page | Bookcase |+-----------+-----------+-------+------+----------+| Java King | Lianjiang | 49.80 |  |        1 | | Lian      | Qiaojiang | 50.00 |  351 |        2 |+-----------+-----------+-------+------+----------+2 rows in Set (0.00 sec) mysql> SELECT Bookname,author,price, Page,bookcase from Tb_bookinfo WHERE bookcase in (1,3), +-----------+-----------+-------+------+----------+| BookName  | author    | price | page | Bookcase |+-----------+-----------+-------+------+----------+| Java King | Lianjiang | 49.80 |  |        1 | | Tian King | Tianjiang | 51.10 |  352 |        3 |+-----------+-----------+-------+------+----------+2 rows in Set (0.00 sec)

5. Range query with between and

Mysql> SELECT * from Tb_bookinfo WHERE inTime between ' 2017-04-17 ' and ' 2017-04-19 '; Empty Set (0.00 sec) mysql> SELECT * from Tb_bookinfo WHERE inTime between ' 2018-04-17 ' and ' 2018-04-19 '; +----------+--- --------+--------+-----------+------+-------+------+----------+------------+------+----+| Barcode | BookName | typeID | Author | ISBN | Price | Page | Bookcase | InTime | del | ID |+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+| 17120107 |      Java King | 3 | Lianjiang | 115 |  49.80 |        350 | 1 |    2018-04-17 |  0 | 1 | | 17120108 |      Lian | 1 | Qiaojiang | 116 |  50.00 |        351 | 2 |    2018-04-18 |  0 | 2 | | 17120109 |      Tian King | 2 | Tianjiang | 117 |  51.10 |        352 | 3 |    2018-04-19 |  0 | 3 |+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+3 rows In Set (0.00 sec) mysql> SELECT * from Tb_bookinfo WHERE inTime between ' 2018-04-17 ' and ' 2018-04-18 '; +----------+-----------+--------+-----------+------+-------+------+----------+-------- ----+------+----+| Barcode | BookName | typeID | Author | ISBN | Price | Page | Bookcase | InTime | del | ID |+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+| 17120107 |      Java King | 3 | Lianjiang | 115 |  49.80 |        350 | 1 |    2018-04-17 |  0 | 1 | | 17120108 |      Lian | 1 | Qiaojiang | 116 |  50.00 |        351 | 2 |    2018-04-18 |  0 | 2 |+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+2 rows In Set (0.00 sec) mysql> SELECT * from Tb_bookinfo WHERE inTime not between ' 2018-04-17 ' and ' 2018-04-18 '; +----------+-- ---------+--------+-----------+------+-------+------+----------+------------+------+----+| Barcode | BookName | typeID | Author | ISBN | Price | Page | Bookcase | InTime | del | ID |+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+| 17120109 |      Tian King | 2 | Tianjiang | 117 |  51.10 |        352 | 3 |    2018-04-19 |  0 | 3 |+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+1 row In Set (0.00 sec)

6. A character matching query with like, through which you can implement a fuzzy query, there are two wildcard characters:% (can match one or more characters, can represent any length of string) and _ (match only one character)

Mysql> SELECT * from Tb_bookinfo WHERE barcode like '%71% '; +----------+-----------+--------+-----------+------+---- ---+------+----------+------------+------+----+| Barcode |  bookname |  typeid | author    | ISBN | Price | Page | Bookcase | InTime     | del  | ID |+----------+-----------+--------+-----------+------+-------+------+----------+-------- ----+------+----+| 17120107 | Java King |      3 | Lianjiang |  | 49.80 |  |        1 | 2018-04-17 |    0 |  1 | | 17120108 | Lian      |      1 | Qiaojiang | 50.00 |  351 |        2 | 2018-04-18 |    0 |  2 | | 17120109 | Tian King |      2 | Tianjiang | 117  | 51.10 |  352 |        3 | 2018-04-19 |    0 |  3 |+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+3 rows In Set (0.00 sec)

7. Querying for null values with the IS NULL keyword

Mysql> SELECT * from Tb_bookinfo WHERE bookname is NULL; Empty Set (0.00 sec)

8. Multi-criteria query with and

Mysql> SELECT * from Tb_bookinfo WHERE bookcase=1 and del=0;+----------+-----------+--------+-----------+------+--- ----+------+----------+------------+------+----+| Barcode |  bookname |  typeid | author    | ISBN | Price | Page | Bookcase | InTime     | del  | ID |+----------+-----------+--------+-----------+------+-------+------+----------+-------- ----+------+----+| 17120107 | Java King |      3 | Lianjiang |  | 49.80 |  |        1 | 2018-04-17 |    0 |  1 |+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+1 row In Set (0.00 sec)

9. Multi-condition query with OR

Mysql> SELECT * from Tb_bookinfo WHERE bookcase=1 OR bookcase=2;+----------+-----------+--------+-----------+------ +-------+------+----------+------------+------+----+| Barcode |  bookname |  typeid | author    | ISBN | Price | Page | Bookcase | InTime     | del  | ID |+----------+-----------+--------+-----------+------+-------+------+----------+-------- ----+------+----+| 17120107 | Java King |      3 | Lianjiang |  | 49.80 |  |        1 | 2018-04-17 |    0 |  1 | | 17120108 | Lian      |      1 | Qiaojiang | 50.00 |  351 |        2 | 2018-04-18 |    0 |  2 |+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+2 rows In Set (0.00 sec)

10. Use the DISTINCT keyword to remove duplicate rows from the result

Mysql> SELECT DISTINCT del from Tb_bookinfo; +------+| del  |+------+|    0 |+------+1 row in Set (0.00 sec)

11. Sort the results of the query with the ORDER BY keyword (asc means ascending, desc = descending)

Mysql> SELECT * from Tb_bookinfo ORDER by price desc;+----------+-----------+--------+-----------+------+-------+-- ----+----------+------------+------+----+| Barcode |  bookname |  typeid | author    | ISBN | Price | Page | Bookcase | InTime     | del  | ID |+----------+-----------+--------+-----------+------+-------+------+----------+-------- ----+------+----+| 17120109 | Tian King |      2 | Tianjiang | 117  | 51.10 |  352 |        3 | 2018-04-19 |    0 |  3 | | 17120108 | Lian      |      1 | Qiaojiang | 50.00 |  351 |        2 | 2018-04-18 |    0 |  2 | | 17120107 | Java King |      3 | Lianjiang |  | 49.80 |  |        1 | 2018-04-17 |    0 |  1 |+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+3 rows In Set (0.00 sec)

Mysql> SELECT * from Tb_bookinfo ORDER by typeid asc;+----------+-----------+--------+-----------+------+-------+-- ----+----------+------------+------+----+| Barcode |  bookname |  typeid | author    | ISBN | Price | Page | Bookcase | InTime     | del  | ID |+----------+-----------+--------+-----------+------+-------+------+----------+-------- ----+------+----+| 17120108 | Lian      |      1 | Qiaojiang | 50.00 |  351 |        2 | 2018-04-18 |    0 |  2 | | 17120109 | Tian King |      2 | Tianjiang | 117  | 51.10 |  352 |        3 | 2018-04-19 |    0 |  3 | | 17120107 | Java King |      3 | Lianjiang |  | 49.80 |  |        1 | 2018-04-17 |    0 |  1 |+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+3 rows In Set (0.00 sec)

12. GROUP BY keyword Query

(1) Use GROUP BY keyword to group queries

Mysql> SELECT Del,count (*) from Tb_bookinfo GROUP by del;+------+----------+| Del  | COUNT (*) |+------+----------+|    0 |        3 |+------+----------+1 row in Set (0.00 sec)

(2) The GROUP by keyword is used with the Group_concat () function

Mysql> SELECT Del,group_concat (barcode) from Tb_bookinfo GROUP by del;+------+----------------------------+| Del  | Group_concat (barcode)      |+------+----------------------------+|    0 | 17120107,17120108,17120109 |+------+----------------------------+1 row in Set (0.00 sec)

(3) Grouping by multiple fields, and then grouping by the second field when the first field has the same value

Mysql> SELECT Del,barcode from Tb_bookinfo GROUP by del,barcode;+------+----------+| Del  | barcode  |+------+----------+|    0 | 17120107 | |    0 | 17120108 | |    0 | 17120109 |+------+----------+3 rows in Set (0.00 sec)

13. Limit the number of query results with limit (display the first two data and start with the 1th number (record number is 0), query two data)

Mysql> SELECT * from Tb_bookinfo ORDER by Price DESC LIMIT 2;+----------+-----------+--------+-----------+------+---- ---+------+----------+------------+------+----+| Barcode |  bookname |  typeid | author    | ISBN | Price | Page | Bookcase | InTime     | del  | ID |+----------+-----------+--------+-----------+------+-------+------+----------+-------- ----+------+----+| 17120109 | Tian King |      2 | Tianjiang | 117  | 51.10 |  352 |        3 | 2018-04-19 |    0 |  3 | | 17120108 | Lian      |      1 | Qiaojiang | 50.00 |  351 |        2 | 2018-04-18 |    0 |  2 |+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+2 rows In Set (0.00 sec)

Mysql> SELECT * from Tb_bookinfo ORDER by Price DESC LIMIT 1,2;+----------+-----------+--------+-----------+------+-- -----+------+----------+------------+------+----+| Barcode |  bookname |  typeid | author    | ISBN | Price | Page | Bookcase | InTime     | del  | ID |+----------+-----------+--------+-----------+------+-------+------+----------+-------- ----+------+----+| 17120108 | Lian      |      1 | Qiaojiang | 50.00 |  351 |        2 | 2018-04-18 |    0 |  2 | | 17120107 | Java King |      3 | Lianjiang |  | 49.80 |  |        1 | 2018-04-17 |    0 |  1 |+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+2 rows In Set (0.00 sec)

Third, aggregate function query

The most important feature of aggregate functions is that they find a value based on a set of data. The result value of the aggregate function is calculated only on the non-null value of the selected row, and the null value is ignored.

The 1.COUNT () function is used to return the number of rows that are not null values in the selected collection for any parameter other than "*", and for the parameter "*", returns the number of all rows in the selected collection, including the rows with null values.

Mysql> SELECT COUNT (*) from tb_bookinfo;+----------+| COUNT (*) |+----------+|        3 |+----------+1 row in Set (0.00 sec)

2.SUM () function

Mysql> SELECT Price from tb_bookinfo;+-------+| Price |+-------+| 49.80 | | 50.00 | | 51.10 |+-------+3 rows in Set (0.00 sec) mysql> SELECT SUM (price) from tb_bookinfo;+------------+| SUM (Price) |+------------+|     150.90 |+------------+1 row in Set (0.00 sec)

3.AVG () function

Mysql> SELECT AVG (price) from tb_bookinfo;+------------+| AVG (Price) |+------------+|  50.299999 |+------------+1 row in Set (0.00 sec)

4.MAX () function

Mysql> SELECT MAX (price) from tb_bookinfo;+------------+| MAX (Price) |+------------+|      51.10 |+------------+1 row in Set (0.00 sec)

5.MIN () function

Mysql> SELECT MIN (price) from tb_bookinfo;+------------+| MIN (Price) |+------------+|      49.80 |+------------+1 row in Set (0.00 sec)

Four, connection query

1. Internal connection Query

Connectivity is the most common way to connect records of different tables together. Inner joins are the most common type of connection and are the most symmetrical because they require a common column match for each table that constitutes a connection, and the mismatched rows are excluded. The inner joins include equal joins and natural joins, the most common example being an equal connection, that is, using the equals operator to match rows of two tables based on the values of the columns that are common to each table. In this case, the final result set contains only the rows that match the specified field in the joined table.

Mysql> SELECT * from tb_bookinfo;+----------+-----------+--------+-----------+------+-------+------+----------+ ------------+------+----+| Barcode | BookName | typeID | Author | ISBN | Price | Page | Bookcase | InTime | del | ID |+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+| 17120107 |      Java King | 3 | Lianjiang | 115 |  49.80 |        350 | 1 |    2018-04-17 |  0 | 1 | | 17120108 |      Lian | 1 | Qiaojiang | 116 |  50.00 |        351 | 2 |    2018-04-18 |  0 | 2 | | 17120109 |      Tian King | 2 | Tianjiang | 117 |  51.10 |        352 | 3 |    2018-04-19 |  0 | 3 |+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+3 rows In Set (0.00 sec) mysql> SELECT * from tb_borrow;+----+----------+--------+------------+------------+----------+--- -----+| ID | Readerid | BookID | Borrowtime | Backtime | operator | Ifback |+----+----------+--------+------------+------------+----------+--------+|        1 |      1 | 1 | 2018-04-17 | 2018-04-20 |      Mr |  1 | |        2 |      1 | 2 | 2018-04-16 | 2018-04-21 |      Mr | 1 |+----+----------+--------+------------+------------+----------+--------+2 rows in Set (0.00 sec) Mysql> SELECT Bookid,borrowtime,backtime,ifback,bookname,author,price, from Tb_borrow,tb_bookinfo WHERE tb_borrow.bookid=tb_ bookinfo.id;+--------+------------+------------+--------+-----------+-----------+-------+| BookID | Borrowtime | Backtime | Ifback | BookName | Author |      Price |+--------+------------+------------+--------+-----------+-----------+-------+| 1 | 2018-04-17 |      2018-04-20 | 1 | Java King | Lianjiang |      49.80 | | 2 | 2018-04-16 |      2018-04-21 | 1 | Lian | Qiaojiang | 50.00 |+--------+------------+------------+--------+-----------+-----------+-------+2 rows in Set (0.00 sec)

2. External connection Query

Unlike an inner connection, an outer join refers to connecting two tables using the Outet join keyword. The result set generated by an outer join contains not only row data that meets the join criteria, but also all data rows in the left table, right table, or both sides of the join table.

3. Compound Conditional Connection Query

Five, sub-query

1. Sub-query with in keyword

2. Subqueries with comparison operators

3. Sub-query with exists keyword

4. Subqueries with the ANY keyword

5. Subquery with the ALL keyword

Vi. Merging Query Results

1. Using the Union keyword

2. Using the UNION ALL keyword

Vii. defining aliases for tables and fields

1. Alias the table

2. Alias a field

Viii. using Regular expression queries

1. Match any one of the specified character sets

2. Use "*" and "+" to match multiple characters

MySQL database (v) Retrieval of table records