use mybook;
-- 升序降序 ASC DESC
select * FROM BOOK ORDER BY publish_date ASC,store_number DESC;
-- 连接函数
SELECT CONCAT(author,title,remark) FROM BOOK WHERE id=1;
-- 判断字段是否为空 IS NULL 和IS NOT NULL
SELECT * FROM BOOK WHERE remark is NULL;
-- 无电子邮件的客户姓名和手机信息
SELECT cname,mobile FROM WHERE email IS NULL OR email="";
-- 出版日期在5天前的图书
SELECT * FROM BOOK WHERE DATE_SUB(publish_date,INTERVAL 5 DAY)>NOW();
-- 出版日期在2年前的图书
SELECT * FROM BOOK WHERE DATE_ADD(publish_date,INTERVAL 2 YEAR)<NOW();
-- 生成邮件格式规则如下:Email地址 (规则如下: XS+当前日期+4位随机数+@test.com)
SELECT CONCAT("XS",DATE(NOW()),ROUND(RAND()*10000),"@test.com");
-- 日期函数
SELECT YEAR("2018-09-09 12:11:11");
SELECT DATE("2018-09-09 12:11:11");
SELECT TIMESTAMP(NOW());
SELECT NOW();
-- 随机函数和四余五入函数
SELECT ROUND(RAND()*10000);
--替换函数
SELECT REPLACE(remark,'O','0') FROM BOOK;
SELECT REPLACE(remark,'O','0') FROM BOOK;
SELECT REPLACE(remark,'i','1') FROM BOOK;
SELECT REPLACE(remark,'I','1') FROM BOOK;
UPDATE BOOK SET remark=REPLACE(remark,'O','0');
UPDATE BOOK SET remark=REPLACE(remark,'o','0');
UPDATE BOOK SET remark=REPLACE(remark,'i','1');
UPDATE BOOK SET remark=REPLACE(remark,'I','1');
UPDATE BOOK SET remark= Replace(REPLACE(remark,'O','0'),'i','1');
-- 取子串函数
SELECT SUBSTR(remark FROM 1 FOR 2) FROM BOOK;
SELECT SUBSTR(remark FROM 4 FOR 2) FROM BOOK;
-- 字符串转数字函数 CAST(expr AS type)
SELECT CAST(SUBSTR(remark FROM 1 FOR 2) AS SIGNED) FROM BOOK;
SELECT CAST(SUBSTR(remark FROM 4 FOR 2) AS SIGNED) FROM BOOK;
-- 拆分后进行排序结果
SELECT * FROM BOOK ORDER BY CAST(SUBSTR(remark FROM 1 FOR 2) AS SIGNED),CAST(SUBSTR(remark FROM 4 FOR 2) AS SIGNED);