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 (多表连接的东西)