建立一个示例表：CREATE TABLE shop(
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000',
dealer CHAR(20) DEFAULT '' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
PRIMARY KEY(article,dealer)
);
寻找列的最大值
- 使用
MAX()寻找列的最大值。e.g.:SELECT MAX(article) AS article FROM shop;
- 查询最大值所在的行：
SELECT article,dealer,price FROM shop WHERE price=(SELECT MAX(price) FROM shop);或者SELECT article,dealer,price FROM shop ORDER BY price DESC LIMIT 1;
- 按组显示列的最大值：e.g.:
SELECT price MAX(price) AS price FROM shop GROUP BY article;
使用用户变量
e.g.:SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
使用姿势：SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
使用外键
可以使用REFERENCES <表名(列名)>为某一列添加外键约束
e.g.:新建一张person表：CREATE TABLE person(
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARTY KEY(id)
);
将此person表中的id作为下面建立的shirt表的owner的外键:CREATE TABLE shirt(
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY(id)
);
计算每月的访问量
e.g.:建立一张示例表t1，用来记录用户访问的时间：CREATE TABLE t1(year YEAR(4),month INT(2) UNSIGNED ZEROFILL,day INT(2) UNSIGNED ZEROFILL);
使用t1，计算用户每月的访问量：SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1 GROUP BY year,month;
使用AUTO_INCREMENT约束
使用AUTO_INCREMENT约束可使被添加约束的列被唯一标识（这对于主键尤为重要）