mysql脚本

基础：装了linux环境的mysql数据库 ，而且建了menagerie的数据库

运行命令: mysql  < /var/lib/mysql/menagerie/file_cmd  -t -vvv

file_cmd文件中的内容

use menagerie

#drop the shop table if it exists, then recreate it
DROP TABLE IF EXISTS shop;

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));

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);

select * from shop;

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;

SELECT article, MAX(price) AS price
FROM   shop
GROUP BY article;

SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
SELECT * FROM shop WHERE price=@min_price OR price=@max_price;