数据库Mysql的学习(六)-子查询和多表操作

 

UPDATE readerinfo SET balance = balance-(SELECT price FROM bookinfo WHERE book_id=20150301)*0.05 WHERE card_id ='20121xxxxxx';
//子查询就是一个嵌套先计算子查询

SELECT * FROM borrow WHERE book_id =(SELECT book_id FROM bookinfo book_name ='xxxxxx')

SELECT * FROM bookinfo WHERE price <(SELECT ROUND(AVG(price),2) FROM bookinfo);//显示小于平均图书价格的图书信息

SELECT * FROM bookinfo WHERE book_category_id <> (SELECT category_id FROM boiokcategory WHERE category='数据库');//显示不是数据库的图书信息

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

SELECT * FROM bookinfo WHERE price > ANY (SELECT price FROM bookinfo WHERE book_category_id=4);//any为大于他的最小值

SELECT * FROM bookinfo WHERE price > SOME (SELECT price FROM bookinfo WHERE book_category_id=4);//some为大于他的最大值

SELECT * FROM bookinfo WHERE book_category_id  IN (SELECT category_id FROM bookcategory WHERE parent_id=2)//这时

SELECT * FROM bookinfo WHERE book_category_id = ANY (SELECT category_id FROM bookcategory WHERE parent_id=2)//两句等效内层查询语句返回的是一个数据列，供外层查询语句比较操作

SELECT * FROM table1 WHERE EXISTS(子查询)//exist判断是否存在，存在就执行外查询


CREATE TABLE readerfee(
    book_id INT,
    card_id CHAR(18),
    actul_return_date DATE,
    book_fee DECIMAL(7,3),
    PRIMARY KEY(book_id,card_id)
);
SELECT book_id ,card_id,return_id FROM borrowinfo WHERE DATEDIFF(SYSDATE(),return_date) >0 AND statue='否';

SELECT INTO readerfee(book_id,card_id,return_date) SELECT book_id ,card_id,return_id FROM borrowinfo WHERE DATEDIFF(SYSDATE(),return_date) >0 AND statue='否';
//将一个表中的记录插入到另一个表中


//练习
UPDATE borrowinfo SET STATUS ='是' WHERE book_id =20151101 AND card_id ='20120xxxxx';

UPDATE readerfee SET actual_return_date=SYSDATE(),book_fee=DATEDIFF(SYSDATE(),return_date)*0.2 WHERE book_id =20151101 AND card_id='3213100.0xxxx';

    
    
    
//多表查询
SELECT book_id ,book_name,category FROM bookinfo INNER JOIN bookcategory ON bookinfo.book_category_id=bookcategory.category_id;
//有内链接  外连接和自连接

//内链接
内链接为两个表都满足条件的
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='否'；    


SELECT t1.book_id,book_namet1.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='否'；    //起个别名这样也是可以的，inner可以省略。
//ON 后面为内连接的条件

//外连接 有左连接和右连接
左连接 显示左表的全部记录  右表满足条件的记录，右连接同理

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 bookcategory
RIGHT JOIN bookinfo ON bookcategory.category_id =bookinfo.book_category_id;
WHERE parent_id<>0;

//自连接
SELECT * FROM bookcategory

SELECT s.category_id AS '图书类别编号' ,s.category AS '图书类别名称' ,p.category AS '图书上级分类名称' FROM bookcategory s
LEFT JOIN bookcategory p ON s.parent_id =p.category_id;

//多表更新
首先需要把表连接起来
UPDATE readerfee t1 JOIN readerinfo t2 ON t1.card_id=t2.card_id
SET actual_return_date =SYSDATE() , bookfee=DATEDIFF(SYSDATE(),return_date)*0.2 , balance=balance-book_fee
WHERE t1.book_id =20151101 AND t1.card_id='2002xxxxxxxx';
//表的复制
CREATE TABLE bookcategory_bak
AS
SELECT * FROM bookcategory;
//多表删除
多表删除的话要用到多表连接
DELETE xx,xx,FROM (多表连接的东西)

 

UPDATE readerinfo SET balance = balance-(SELECT price FROM bookinfo WHERE book_id=20150301)*0.05 WHERE card_id ='20121xxxxxx';
//子查询就是一个嵌套先计算子查询

SELECT * FROM borrow WHERE book_id =(SELECT book_id FROM bookinfo book_name ='xxxxxx')

SELECT * FROM bookinfo WHERE price <(SELECT ROUND(AVG(price),2) FROM bookinfo);//显示小于平均图书价格的图书信息

SELECT * FROM bookinfo WHERE book_category_id <> (SELECT category_id FROM boiokcategory WHERE category='数据库');//显示不是数据库的图书信息

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

SELECT * FROM bookinfo WHERE price > ANY (SELECT price FROM bookinfo WHERE book_category_id=4);//any为大于他的最小值

SELECT * FROM bookinfo WHERE price > SOME (SELECT price FROM bookinfo WHERE book_category_id=4);//some为大于他的最大值

SELECT * FROM bookinfo WHERE book_category_id IN (SELECT category_id FROM bookcategory WHERE parent_id=2)//这时

SELECT * FROM bookinfo WHERE book_category_id = ANY (SELECT category_id FROM bookcategory WHERE parent_id=2)//两句等效内层查询语句返回的是一个数据列，供外层查询语句比较操作

SELECT * FROM table1 WHERE EXISTS(子查询)//exist判断是否存在，存在就执行外查询

CREATE TABLE readerfee(
book_id INT,
card_id CHAR(18),
actul_return_date DATE,
book_fee DECIMAL(7,3),
PRIMARY KEY(book_id,card_id)
);
SELECT book_id ,card_id,return_id FROM borrowinfo WHERE DATEDIFF(SYSDATE(),return_date) >0 AND statue='否';

SELECT INTO readerfee(book_id,card_id,return_date) SELECT book_id ,card_id,return_id FROM borrowinfo WHERE DATEDIFF(SYSDATE(),return_date) >0 AND statue='否';
//将一个表中的记录插入到另一个表中

//练习
UPDATE borrowinfo SET STATUS ='是' WHERE book_id =20151101 AND card_id ='20120xxxxx';

UPDATE readerfee SET actual_return_date=SYSDATE(),book_fee=DATEDIFF(SYSDATE(),return_date)*0.2 WHERE book_id =20151101 AND card_id='3213100.0xxxx';

//多表查询
SELECT book_id ,book_name,category FROM bookinfo INNER JOIN bookcategory ON bookinfo.book_category_id=bookcategory.category_id;
//有内链接 外连接和自连接

//内链接
内链接为两个表都满足条件的
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='否'；

SELECT t1.book_id,book_namet1.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='否'； //起个别名这样也是可以的，inner可以省略。
//ON 后面为内连接的条件

//外连接 有左连接和右连接
左连接 显示左表的全部记录 右表满足条件的记录，右连接同理

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 bookcategory
RIGHT JOIN bookinfo ON bookcategory.category_id =bookinfo.book_category_id;
WHERE parent_id<>0;

//自连接
SELECT * FROM bookcategory

SELECT s.category_id AS '图书类别编号' ,s.category AS '图书类别名称' ,p.category AS '图书上级分类名称' FROM bookcategory s
LEFT JOIN bookcategory p ON s.parent_id =p.category_id;

//多表更新
首先需要把表连接起来
UPDATE readerfee t1 JOIN readerinfo t2 ON t1.card_id=t2.card_id
SET actual_return_date =SYSDATE() , bookfee=DATEDIFF(SYSDATE(),return_date)*0.2 , balance=balance-book_fee
WHERE t1.book_id =20151101 AND t1.card_id='2002xxxxxxxx';
//表的复制
CREATE TABLE bookcategory_bak
AS
SELECT * FROM bookcategory;
//多表删除
多表删除的话要用到多表连接
DELETE xx,xx,FROM (多表连接的东西)