mysql子查询sysdate_数据库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 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 TABLEreaderfee(

book_idINT,

card_idCHAR(18),

actul_return_date DATE,

book_feeDECIMAL(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 FROMborrowinfoINNER JOIN bookinfo ON borrowinfo.book_id =bookinfo.book_idINNER JOIN readerinfo ON borrowinfo.card_id=readerinfo.card_idWHERE borrowinfo.status='否'；SELECT t1.book_id,book_namet1.card_id ,NAME,tel,return_date,STATUS FROMborrowinfo t1JOIN bookinfo t2 ON t1.book_id =t2.book_idJOIN readerinfo t3 ON t1.card_id=t3.card_idWHERE t1.status='否'； //起个别名这样也是可以的，inner可以省略。//ON后面为内连接的条件//外连接 有左连接和右连接

左连接 显示左表的全部记录 右表满足条件的记录，右连接同理SELECT book_id ,book_name,category FROMbookcategoryLEFT JOIN bookinfo ON bookcategory.category_id =bookinfo.book_category_id;WHERE parent_id<>0;SELECT book_id ,book_name,category FROMbookcategoryRIGHT JOIN bookinfo ON bookcategory.category_id =bookinfo.book_category_id;WHERE parent_id<>0;//自连接SELECT * FROMbookcategorySELECT s.category_id AS '图书类别编号' ,s.category AS '图书类别名称' ,p.category AS '图书上级分类名称' FROMbookcategory sLEFT JOIN bookcategory p ON s.parent_id =p.category_id;//多表更新

首先需要把表连接起来UPDATE readerfee t1 JOIN readerinfo t2 ON t1.card_id=t2.card_idSET actual_return_date =SYSDATE() , bookfee=DATEDIFF(SYSDATE(),return_date)*0.2 , balance=balance-book_feeWHERE t1.book_id =20151101 AND t1.card_id='2002xxxxxxxx';//表的复制CREATE TABLEbookcategory_bakAS

SELECT * FROMbookcategory;//多表删除

多表删除的话要用到多表连接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 * 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 (多表连接的东西)