mysql表记录的检索实验报告_Mysql数据库（五）表记录的检索

一、基本查询语句

二、单表查询

1.查询所有字段

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)

2.查询指定字段

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.查询指定数据

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.带IN关键字的查询

mysql> SELECT bookname,author,price,page,bookcase FROM tb_bookinfo WHERE bookcase IN(1,2);

+-----------+-----------+-------+------+----------+

| bookname | author | price | page | bookcase |

+-----------+-----------+-------+------+----------+

| Java King | LianJiang | 49.80 | 350 | 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 | 350 | 1 |

| Tian King | TianJiang | 51.10 | 352 | 3 |

+-----------+-----------+-------+------+----------+

2 rows in set (0.00 sec)

5.带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.带LIKE的字符匹配查询，通过它可以实现模糊查询，有两种通配符：%(可以匹配一个或多个字符，可以代表任意长度的字符串)和_(只匹配一个字符)

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 | 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)

7.用IS NULL关键字查询空值

mysql> SELECT * FROM tb_bookinfo WHERE bookname IS NULL;

Empty set (0.00 sec)

8.带AND的多条件查询

mysql> SELECT * FROM tb_bookinfo WHERE bookcase=1 AND del=0;

+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+

| barcode | bookname | typeid | author | ISBN | price | page | bookcase | inTime | del | id |

+----------+-----------+--------+-----------+-