MySQL常用查询

建立一个示例表：
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)

);

---

寻找列的最大值

1. 使用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;

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约束可使被添加约束的列被唯一标识（这对于主键尤为重要）