实验楼网站mysql_MySQL常用查询

使用用户变量

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;

mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;

使用外键及AUTO_INCREMENT语句

mysql> CREATE TABLE person (

-> id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

-> name CHAR(60) NOT NULL,

-> PRIMARY KEY (id)

-> );

SELECT @last := LAST_INSERT_ID();

mysql> CREATE TABLE shirt (

-> id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

-> style ENUM('t-shirt', 'polo', 'dress') NOT NULL,

-> color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,

-> owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),

-> PRIMARY KEY (id)

-> );

ZEROFILL

mysql> CREATE TABLE shop (

-> article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,

-> dealer CHAR(20) DEFAULT '' NOT NULL,

-> price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,

-> PRIMARY KEY(article, dealer));