標簽：一個 from enc insert 外鍵 rem medium enum value
（1）你可以使用以下語句創建示例表：
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));
mysql> INSERT INTO shop VALUES
-> (1,‘A‘,3.45),(1,‘B‘,3.99),(2,‘A‘,10.99),(3,‘B‘,1.45),
-> (3,‘C‘,1.69),(3,‘D‘,1.25),(4,‘D‘,19.95);
（2）執行語句后，表應包含以下內容： SELECT * FROM shop;
（3）列的最大值
選取價格最貴：select max(price) as article from shop;
（4）擁有某個列的最大值的行
任務：找出最貴物品的編號、銷售商和價格。
方法一：
mysql> select article,dealer,price
-> from shop
-> where price=(select max(price) from shop);
方法二：按價格降序排序所有行并用MySQL特定LIMIT子句只得到第一行，這里用到desc前面講過，是降序的意思。
mysql> select article ,dealer,price
-> from shop
-> order by price desc
-> limit 1;
注:如果有多項最貴的物品( 例如每個的價格為19.95)，LIMIT解決方案僅僅顯示其中一個！
（5）列的最大值：按組
任務：每項物品的的最高價格是多少？
mysql> SELECT article, MAX(price) AS price
-> FROM shop
-> GROUP BY article
-> ;
（6）擁有某個字段的組間最大值的行
任務：對每項物品，找出最貴價格的物品的經銷商。
SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article);
（7）使用用戶變量
任務：要找出價格最高或最低的物品的
SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop; SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
（8）使用外鍵
CREATE TABLE person ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, name CHAR(60) NOT NULL, PRIMARY KEY (id) ); 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) ); INSERT INTO person VALUES (NULL, ‘Antonio Paz‘); SELECT @last := LAST_INSERT_ID(); INSERT INTO shirt VALUES (NULL, ‘polo‘, ‘blue‘, @last), (NULL, ‘dress‘, ‘white‘, @last), (NULL, ‘t-shirt‘, ‘blue‘, @last); INSERT INTO person VALUES (NULL, ‘Lilliana Angelovska‘); SELECT @last := LAST_INSERT_ID(); INSERT INTO shirt VALUES (NULL, ‘dress‘, ‘orange‘, @last), (NULL, ‘polo‘, ‘red‘, @last), (NULL, ‘dress‘, ‘blue‘, @last), (NULL, ‘t-shirt‘, ‘white‘, @last); SELECT * FROM person; SELECT * FROM shirt; SELECT s.* FROM person p, shirt s WHERE p.name LIKE ‘Lilliana%‘ AND s.owner = p.id AND s.color <> ‘white‘;
（8）根據天計算訪問量
CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL, day INT(2) UNSIGNED ZEROFILL); INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2), (2000,2,23),(2000,2,23);
SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1 GROUP BY year,month;
（9）使用AUTO_INCREMENT
CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) );
INSERT INTO animals (name) VALUES (‘dog‘),(‘cat‘),(‘penguin‘), (‘lax‘),(‘whale‘),(‘ostrich‘);
SELECT * FROM animals;
標簽：一個 from enc insert 外鍵 rem medium enum value
原文地址：https://www.cnblogs.com/aisisi/p/15058584.html
