实验楼网站mysql_MySQL常用查询

1，创建一个示例表

2，插入数据

3，查看表的内容

4，寻找列的最大值

5，查询某列最大值所在的行

6，按组显示列的最大值

7，使用用户变量

8，使用外键

9，使用两个关键字进行搜索

10，计算每月的访问量

11、使用AUTO_INCREMENT语句

sudo service mysql start

mysql -u root

create database test；

use test；

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;

CREATE TABLE person (

id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

name CHAR(60) NOT NULL,

PRIMARY KEY (id)

);