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 | +----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+ | 17120107 | Java King | 3 | LianJiang | 115 | 49.80 | 350 | 1 | 2018-04-17 | 0 | 1 | +----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+ 1 row in set (0.00 sec)
9.带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 | 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)
10.用DISTINCT关键字去除结果中的重复行
mysql> SELECT DISTINCT del FROM tb_bookinfo ; +------+ | del | +------+ | 0 | +------+ 1 row in set (0.00 sec)
11.用ORDER BY关键字对查询结果排序(ASC表示升序,DESC表示降序)
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 | 116 | 50.00 | 351 | 2 | 2018-04-18 | 0 | 2 | | 17120107 | Java King | 3 | LianJiang | 115 | 49.80 | 350 | 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 | 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 | | 17120107 | Java King | 3 | LianJiang | 115 | 49.80 | 350 | 1 | 2018-04-17 | 0 | 1 | +----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+ 3 rows in set (0.00 sec)
12.用GROUP BY关键字分组查询
(1)使用GROUP BY关键字分组查询
mysql> SELECT del,COUNT(*) FROM tb_bookinfo GROUP BY del; +------+----------+ | del | COUNT(*) | +------+----------+ | 0 | 3 | +------+----------+ 1 row in set (0.00 sec)
(2)GROUP BY关键字与GROUP_CONCAT()函数一起使用
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)按多个字段进行分组,当第一个字段有相同值时,再按第二个字段进行分组
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限制查询结果的数量(显示前两条数据和从第1个编号开始(记录编号是从0开始的),查询两条数据)
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 | 116 | 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 | 116 | 50.00 | 351 | 2 | 2018-04-18 | 0 | 2 | | 17120107 | Java King | 3 | LianJiang | 115 | 49.80 | 350 | 1 | 2018-04-17 | 0 | 1 | +----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+ 2 rows in set (0.00 sec)
三、聚合函数查询
聚合函数的最大特点是它们根据一组数据求出一个值。聚合函数的结果值只根据选定行中非NULL的值进行计算,NULL值被忽略。
1.COUNT()函数用于对除“*”以外的任何参数,返回所选择集合中非NULL值的行的数目;对于参数“*”,返回选择集合中所有行的数目,包含NULL值的行。
mysql> SELECT COUNT(*) FROM tb_bookinfo; +----------+ | COUNT(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec)
2.SUM()函数
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()函数
mysql> SELECT AVG(price) FROM tb_bookinfo; +------------+ | AVG(price) | +------------+ | 50.299999 | +------------+ 1 row in set (0.00 sec)
4.MAX()函数
mysql> SELECT MAX(price) FROM tb_bookinfo; +------------+ | MAX(price) | +------------+ | 51.10 | +------------+ 1 row in set (0.00 sec)
5.MIN()函数
mysql> SELECT MIN(price) FROM tb_bookinfo; +------------+ | MIN(price) | +------------+ | 49.80 | +------------+ 1 row in set (0.00 sec)
四、连接查询
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) 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.外连接查询
与内连接不同,外连接是指使用OUTET JOIN关键字将两个表连接起来。外连接生成的结果集不仅包含符合连接条件的行数据,而且含包括左表、右表或两边连接表中所有的数据行。
(1)左外连接(这里要弄清楚左外连接和内连接的区别)
mysql> SELECT bookid,borrowTime,backTime,ifback,bookname,author,price -> FROM tb_borrow LEFT JOIN tb_bookinfo ON 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)
mysql> SELECT bookid,borrowTime,backTime,ifback,bookname,author,price -> FROM tb_bookinfo LEFT JOIN tb_borrow ON 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 | | NULL | NULL | NULL | NULL | Tian King | TianJiang | 51.10 | +--------+------------+------------+--------+-----------+-----------+-------+ 3 rows in set (0.00 sec)
(2)右外连接
mysql> SELECT bookid,borrowTime,backTime,ifback,bookname,author,price -> FROM tb_bookinfo RIGHT JOIN tb_borrow ON 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)
mysql> SELECT bookid,borrowTime,backTime,ifback,bookname,author,price -> FROM tb_borrow RIGHT JOIN tb_bookinfo ON 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 | | NULL | NULL | NULL | NULL | Tian King | TianJiang | 51.10 | +--------+------------+------------+--------+-----------+-----------+-------+ 3 rows in set (0.00 sec)
3.复合条件连接查询
mysql> UPDATE tb_borrow SET ifback=0 WHERE id=2; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 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 | 0 | +----+----------+--------+------------+------------+----------+--------+ 2 rows in set (0.00 sec) 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 bookid,borrowTime,backTime,ifback,bookname,author,price -> FROM tb_bookinfo,tb_borrow 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 | 0 | Lian | QiaoJiang | 50.00 | +--------+------------+------------+--------+-----------+-----------+-------+ 2 rows in set (0.00 sec)
mysql> SELECT bookid,borrowTime,backTime,ifback,bookname,author,price -> FROM tb_bookinfo,tb_borrow WHERE tb_borrow.bookid=tb_bookinfo.id AND ifback=0; +--------+------------+------------+--------+----------+-----------+-------+ | bookid | borrowTime | backTime | ifback | bookname | author | price | +--------+------------+------------+--------+----------+-----------+-------+ | 2 | 2018-04-16 | 2018-04-21 | 0 | Lian | QiaoJiang | 50.00 | +--------+------------+------------+--------+----------+-----------+-------+ 1 row in set (0.00 sec)
五、子查询
MySQL可以嵌套多个查询,在外面一层的查询中使用里面一层查询产生的结果集。这样就不是执行两个独立地查询,而是执行包含一个(或者多个)子查询的单独查询。
1.带IN关键字的子查询
mysql> SELECT id FROM tb_bookinfo; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.00 sec) mysql> SELECT bookid FROM tb_borrow; +--------+ | bookid | +--------+ | 1 | | 2 | +--------+ 2 rows in set (0.00 sec) mysql> SELECT id,bookname,author,price -> FROM tb_bookinfo WHERE id IN (SELECT bookid FROM tb_borrow); +----+-----------+-----------+-------+ | id | bookname | author | price | +----+-----------+-----------+-------+ | 1 | Java King | LianJiang | 49.80 | | 2 | Lian | QiaoJiang | 50.00 | +----+-----------+-----------+-------+ 2 rows in set (0.01 sec)
mysql> SELECT id,bookname,author,price -> FROM tb_bookinfo WHERE id NOT IN (SELECT bookid FROM tb_borrow); +----+-----------+-----------+-------+ | id | bookname | author | price | +----+-----------+-----------+-------+ | 3 | Tian King | TianJiang | 51.10 | +----+-----------+-----------+-------+ 1 row in set (0.00 sec)
2.带比较运算符的子查询
mysql> 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 | 0 | +----+----------+--------+------------+------------+----------+--------+ 2 rows in set (0.00 sec) 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 ifback FROM tb_borrow WHERE borrowTime='2018-04-17'; +--------+ | ifback | +--------+ | 1 | +--------+ 1 row in set (0.00 sec)
mysql> SELECT * FROM tb_bookinfo -> WHERE typeid > (SELECT ifback FROM tb_borrow WHERE borrowTime='2018-04-17'); +----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+ | 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 | | 17120109 | Tian King | 2 | TianJiang | 117 | 51.10 | 352 | 3 | 2018-04-19 | 0 | 3 | +----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+ 2 rows in set (0.00 sec)
3.带EXISTS关键字的子查询
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 | 0 | +----+----------+--------+------------+------------+----------+--------+ 2 rows in set (0.00 sec) 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 id,bookname,author,price -> FROM tb_bookinfo WHERE EXISTS (SELECT * FROM tb_borrow WHERE tb_borrow.bookid=tb_bookinfo.id); +----+-----------+-----------+-------+ | id | bookname | author | price | +----+-----------+-----------+-------+ | 1 | Java King | LianJiang | 49.80 | | 2 | Lian | QiaoJiang | 50.00 | +----+-----------+-----------+-------+ 2 rows in set (0.01 sec)
mysql> SELECT id,bookname,author,price -> FROM tb_bookinfo WHERE NOT EXISTS (SELECT * FROM tb_borrow WHERE tb_borrow.bookid=tb_bookinfo.id); +----+-----------+-----------+-------+ | id | bookname | author | price | +----+-----------+-----------+-------+ | 3 | Tian King | TianJiang | 51.10 | +----+-----------+-----------+-------+ 1 row in set (0.00 sec)
4.带ANY关键字的子查询
ANY关键字表示满足其中任意一个条件,通常与比较运算符一起使用,只要满足内层查询语句返回的结果中的任意一个,就可以通过该条件来执行外层查询语句。
<表示小于子查询结果集中某一个值;>表示至少大于子查询结果集中的某一个值。
mysql> SELECT * FROM tb_student; +----+------+------+---------+-------+ | id | name | sex | classid | score | +----+------+------+---------+-------+ | 1 | a | 男 | 1 | 199 | | 2 | b | 女 | 3 | 150 | | 3 | c | 女 | 2 | 199 | | 4 | d | 男 | 2 | 188 | | 5 | e | 女 | 5 | 198 | | 6 | f | 女 | 5 | 200 | +----+------+------+---------+-------+ 6 rows in set (0.00 sec)
得到的结果是只要大于188都可以输出。
mysql> SELECT * FROM tb_student WHERE score > ANY (SELECT score FROM tb_student WHERE classid=2); +----+------+------+---------+-------+ | id | name | sex | classid | score | +----+------+------+---------+-------+ | 1 | a | 男 | 1 | 199 | | 3 | c | 女 | 2 | 199 | | 5 | e | 女 | 5 | 198 | | 6 | f | 女 | 5 | 200 | +----+------+------+---------+-------+ 4 rows in set (0.00 sec)
得到的结果是只要比199小都输出。
mysql> SELECT * FROM tb_student WHERE score < ANY (SELECT score FROM tb_student WHERE classid=2); +----+------+------+---------+-------+ | id | name | sex | classid | score | +----+------+------+---------+-------+ | 2 | b | 女 | 3 | 150 | | 4 | d | 男 | 2 | 188 | | 5 | e | 女 | 5 | 198 | +----+------+------+---------+-------+ 3 rows in set (0.00 sec)
5.带ALL关键字的子查询
和ANY相反,<表示小于查询结果中的最小值,>表示大于查询结果中的最大值
mysql> SELECT * FROM tb_student WHERE score > ALL (SELECT score FROM tb_student WHERE classid=2); +----+------+------+---------+-------+ | id | name | sex | classid | score | +----+------+------+---------+-------+ | 6 | f | 女 | 5 | 200 | +----+------+------+---------+-------+ 1 row in set (0.00 sec) mysql> SELECT * FROM tb_student WHERE score < ALL (SELECT score FROM tb_student WHERE classid=2); +----+------+------+---------+-------+ | id | name | sex | classid | score | +----+------+------+---------+-------+ | 2 | b | 女 | 3 | 150 | +----+------+------+---------+-------+ 1 row in set (0.00 sec)
六、合并查询结果
1.使用UNION关键字
mysql> SELECT barcode FROM tb_bookinfo; +----------+ | barcode | +----------+ | 17120107 | | 17120108 | | 17120109 | +----------+ 3 rows in set (0.00 sec) mysql> SELECT barcode FROM tb_bookinfo_copy; +----------+ | barcode | +----------+ | 17120107 | | 17120108 | | 17120106 | +----------+ 3 rows in set (0.00 sec) mysql> SELECT barcode FROM tb_bookinfo UNION SELECT barcode FROM tb_bookinfo_copy; +----------+ | barcode | +----------+ | 17120107 | | 17120108 | | 17120109 | | 17120106 | +----------+ 4 rows in set (0.00 sec)
2.使用UNION ALL关键字
mysql> SELECT barcode FROM tb_bookinfo UNION ALL SELECT barcode FROM tb_bookinfo_copy; +----------+ | barcode | +----------+ | 17120107 | | 17120108 | | 17120109 | | 17120107 | | 17120108 | | 17120106 | +----------+ 6 rows in set (0.00 sec)
七、定义表和字段的别名
1.为表取别名
mysql> SELECT bookname,author,price,page -> FROM tb_bookinfo AS book -> LEFT JOIN tb_borrow AS type ON book.typeid=type.id; +-----------+-----------+-------+------+ | bookname | author | price | page | +-----------+-----------+-------+------+ | Java King | LianJiang | 49.80 | 350 | | Lian | QiaoJiang | 50.00 | 351 | | Tian King | TianJiang | 51.10 | 352 | +-----------+-----------+-------+------+ 3 rows in set (0.00 sec)
2.为字段取别名,统计del的次数并取别名为degree
mysql> SELECT del,COUNT(*) AS degree FROM tb_bookinfo GROUP BY del; +------+--------+ | del | degree | +------+--------+ | 0 | 3 | +------+--------+ 1 row in set (0.00 sec)
八、使用正则表达式查询
1.匹配指定字符集中任意一个(查询出bookname中包括字幕g或v)
mysql> SELECT * FROM tb_bookinfo -> WHERE bookname REGEXP '[gv]'; +----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+ | 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 | | 17120109 | Tian King | 2 | TianJiang | 117 | 51.10 | 352 | 3 | 2018-04-19 | 0 | 3 | +----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+ 2 rows in set (0.01 sec)
2.使用“*”和“+”来匹配多个字符(*表示匹配多个该符号之前的字符,包括0和1个;+表示匹配多个该符号之前的字符,包括一个)
mysql> SELECT email FROM tb_student; +-----------------------+ | email | +-----------------------+ | sjy534948129@sina.com | | sjy.com | | a.com | | a.b.com | | assdad | | dadd.sda | +-----------------------+ 6 rows in set (0.00 sec) mysql> SELECT email FROM tb_student -> WHERE email NOT REGEXP '^[a-zA-Z0-9_-]+@[a-zA-Z0-9_-]+(\.[a-zA-Z0-9_-]+)+$'; +----------+ | email | +----------+ | sjy.com | | a.com | | a.b.com | | assdad | | dadd.sda | +----------+ 5 rows in set (0.00 sec)
正则表达式的模式字符
1. ^ 匹配以特定字符或字符串开头的记录
2. $ 匹配以特定字符或字符串结尾的记录
3. . 匹配字符串的任意一个字符,包括回车和换行
4. [字符集合] 匹配'字符集合'中的任意一个字符
5. [^字符集合] 匹配除'字符集合'中的任意一个字符
6. S1|S2|S3 匹配S1、S2和S3中的任意一个字符串
7. * 匹配多个该符号之前的字符,包括0个和1个
8. + 匹配多个该符号之前的字符,包括1个
9. 字符串{N} 匹配字符串出现N次
10. 字符串{M,N} 匹配字符串出现至少M次,最多N次