第三章
处理某字段中不同区间的问题
3.6.4. 拥有某个字段的组间最大值的行
任务:对每项物品,找出最贵价格的物品的经销商。
可以用这样一个子查询解决该问题:
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);
LAST_INSERT_ID();
CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
);
支持大小写的正则查询
SELECT * FROM pet WHERE name REGEXP [BINARY] '^b';
计算年份间隔
SELECT name, birth, CURDATE(),(YEAR(CURDATE())-YEAR(birth))- (RIGHT(CURDATE(),5)<RIGHT(birth,5)) AS age FROM pet ORDER BY age;