select b.bookname,b.author,round(datediff(curdate(),b.publishdate)/365.0)
from bookinfo b
select b.*,round(datediff(curdate(),b.publishdate)/365.0)
from bookinfo b
where b.isbn like '75%' and round(datediff(curdate(),b.publishdate)/365.0)>=2
select *
from bookinfo b
where locate('7',substring(b.isbn,-4))>0
update bookinfo_copy bp set bp.booknum=insert(bp.booknum,1,4,'book') where 1=1
update bookinfo_copy bp set bp.bookname = concat(bp.bookname,'-',bp.author)
where bp.author like '孙%'
select * from bookinfo_copy
delete from bookinfo_copy where publishno ='P0649'
alter table bookinfo_copy add column discount int default null;
update bookinfo_copy bp1
set bp1.discount = case when remain>600 then 1
else 0 end
where 1=1
select * from bookinfo_copy
alter table bookinfo ADD check(remain>=0 AND remain<=1000)
create trigger check_ramin
after insert
on bookinfo
for each row
begin
if(new.remain<0 or new.remain>1000) then
delete from bookinfo where remain =new.remain;
end if;
end
UPDATE NEWPRODUCT SET FACTORY ='ASUS' WHERE FACTORY ='华硕';
ALTER TABLE NEWPRODUCT ADD CHECK (PRICE>=0 AND PRICE <=5000);
ALTER TABLE NEWPRODUCT RENAME COLUMN price TO productprice;
DELETE FROM NEWPRODUCT p WHERE p.productprice<100;
ALTER TABLE NEWPRODUCT ADD PRIMARY key(pdtno);
truncate TABLE NEWPRODUCT;
DROP TABLE NEWPRODUCT;