MySql多表查询命令

一、子查询

例子：更新借阅book_id为20190701图书的用户130429199007025628所剩下的余额

UPDATE readerinfo SET balance=balance-(SELECT price FROM bookinfo WHERE book_id=20190701)*0.05 WHERE card_id='130429199007025628'

1、使用比较运算符的子查询

（1）查询图书名称为软件测试的借阅信息

SELECT book_id FROM bookinfo WHERE book_name="软件测试";
SELECT * FROM borrowinfo WHERE book_id=(SELECT book_id FROM bookinfo WHERE book_name="软件测试")

（2）查询图书价格小于图书平均价格的图书信息

SELECT AVG(price) FROM bookinfo
SELECT * FROM bookinfo WHERE price<(SELECT ROUND(AVG(price),2) FROM bookinfo);

（3）查询图书类别不等于计算机的图书信息

SELECT category_id FROM bookcategory WHERE category!='计算机';
SELECT * FROM bookinfo WHERE book_category_id !=(SELECT category_id FROM bookcategory WHERE category='计算机')

（4）关键字any=some  all

查询图书类别属于计算机的图书信息

SELECT * FROM bookinfo WHERE book_category_id = ANY(SELECT category_id FROM bookcategory WHERE parent_id=1)

some和any是大于子查询的最小值

SELECT * FROM bookinfo WHERE price > ANY(SELECT price FROM bookinfo WHERE book_category_id=1)

all是大于子查询的最大值
SELECT * FROM bookinfo WHERE price > ALL(SELECT price FROM bookinfo WHERE book_category_id=1)

2、使用【not】in和exist的子查询

in=any

SELECT * FROM bookinfo WHERE book_category_id IN(SELECT category_id FROM bookcategory WHERE parent_id=1)

SELECT * FROM bookinfo WHERE book_category_id =ANY(SELECT category_id FROM bookcategory WHERE parent_id=1)

SELECT * FROM bookinfo WHERE book_category_id NOT IN(SELECT category_id FROM bookcategory WHERE parent_id=1)

子查询存在再执行外查询，不存在则外查询返回空

SELECT * FROM bookinfo WHERE EXISTS(SELECT category_id FROM bookcategory WHERE category='计算机')

SELECT * FROM bookinfo WHERE EXISTS(SELECT category_id FROM bookcategory WHERE category='不存在')

3、插入记录时使用子查询

INSERT INTO readerfee (book_id, card_id, return_date)
SELECT
book_id,
card_id,
return_date
FROM
borrowinfo
WHERE DATEDIFF(SYSDATE(), return_date) > 0
AND STATUS = "否" ;   插入查询的内容

二、多表连接查询

内连接，分析互相关联的三张表，只有借阅表与其他两个表内连接

SELECT borrowinfo.book_id,book_name,borrowinfo.card_id,NAME,tel,return_date,STATUS FROM borrowinfo
INNER JOIN bookinfo ON borrowinfo.book_id=bookinfo.book_id
INNER JOIN readerinfo ON borrowinfo.card_id=readerinfo.card_id
WHERE borrowinfo.STATUS="否";

可以简化为：表别名，inner省略

SELECT t1.book_id,book_name,t1.card_id,NAME,tel,return_date,STATUS FROM borrowinfo t1
JOIN bookinfo t2 ON t1.book_id=t2.book_id
JOIN readerinfo t3 ON t1.card_id=t3.card_id
WHERE t1.status="否";

2、外连接

SELECT book_id,book_name,category FROM bookcategory
LEFT JOIN bookinfo ON bookcategory.category_id = bookinfo.book_category_id
WHERE parent_id!=0;      左连接

SELECT book_id,book_name,category FROM bookinfo
RIGHT JOIN bookcategory ON bookcategory.category_id = bookinfo.book_category_id;    右连接

3、自连接      两张表为同一张表

SELECT a.category_id,a.category,s.category FROM bookcategory a   
LEFT JOIN bookcategory s
ON a.parent_id=s.category_id;    左和右连接是合并，会出现空的情况

SELECT a.category_id,a.category,s.category FROM bookcategory a   
INNER JOIN bookcategory s
ON a.parent_id=s.category_id;      内连接数据一一对应

4、多表更新

UPDATE readerfee t1 JOIN readerinfo t2 ON t1.card_id=t2.card_id
SET actual_date=SYSDATE(),book_fee=DATEDIFF(SYSDATE(),return_date)*0.2,balance=balance-book_fee
WHERE t1.book_id=20090101 AND t1.card_id="130429199007023333" 

5、多表删除

单表

SELECT book_id,book_name,category FROM bookcategory
LEFT JOIN bookinfo ON bookcategory.category_id=bookinfo.book_category_id
WHERE parent_id!=0;   先查询

DELETE bookcategory FROM bookcategory
LEFT JOIN bookinfo ON bookcategory.category_id=bookinfo.book_category_id
WHERE parent_id!=0 AND book_id IS NULL;   再删除

多表：

SELECT book_id,book_name,category_id,category FROM bookcategory
INNER JOIN bookinfo ON bookcategory.category_id=bookinfo.book_category_id
WHERE category_id=3

DELETE bookcategory,bookinfo FROM bookcategory
INNER JOIN bookinfo ON bookcategory.category_id=bookinfo.book_category_id
WHERE category_id=3