简单查询语句

建表：

1 mysql> create table shop(id int(4) not null auto_increment,
2     -> name varchar(30),price double(15,2),sort varchar(20),
3     -> count int(5) default 0 not null,
4     -> primary key (id,name));
5 Query OK, 0 rows affected (0.15 sec)

修改表结构：在指定列增加字段。

 1 mysql> alter table shop
 2     -> add supplier_name varchar(50) not null default 'tmall'
 3     -> after price;
输出：
 4 Query OK, 5 rows affected (0.35 sec)
 5 Records: 5  Duplicates: 0  Warnings: 0
查看表变化：
 7 mysql> select * from shop;
 8 +----+-------+--------+---------------+------+-------+
 9 | id | name  | price  | supplier_name | sort | count |
10 +----+-------+--------+---------------+------+-------+
11 |  1 | apple |   2.50 | tmall         | A    |   500 |
12 |  2 | BOOK  |   2.40 | tmall         | B    |   500 |
13 |  3 | rule  |   1.20 | tmall         | B    |   200 |
14 |  4 | grape |   8.50 | tmall         | A    |   500 |
15 |  5 | pen   | 108.50 | tmall         | B    |    80 |
16 +----+-------+--------+---------------+------+-------+
17 5 rows in set (0.00 sec)

 

增加记录：

1 mysql> insert into shop values
2     -> (1,'apple',2.50,'A',500),
3     -> (2,'BOOK',2.40,'B',500),
4     -> (3,'rule',1.20,'B',200);
输出：
5 Query OK, 3 rows affected (0.08 sec)
6 Records: 3  Duplicates: 0  Warnings: 0

简单查询语句

 一、查询记录中某个字段的最大值，查询多个字段，结果不是同一字段：

查询结果不是最同一条记录！！
1 mysql> select max(price) as price,max(id) as id from shop;
输出：不可以按记录输出...
2 +-------+------+
3 | price | id   |
4 +-------+------+
5 |  2.50 |    3 |
6 +-------+------+
7 1 row in set (0.00 sec)

 

二、输出某列最大值的记录：

方法1：

1 mysql> select * from shop where price =(select max(price) from shop);
输出：
2 +----+-------+-------+------+-------+
3 | id | name  | price | sort | count |
4 +----+-------+-------+------+-------+
5 |  1 | apple |  2.50 | A    |   500 |
6 +----+-------+-------+------+-------+
7 1 row in set (0.02 sec)

方法2：

1 mysql> select * from shop order by price desc limit 1;
输出：
2 +----+-------+-------+------+-------+
3 | id | name  | price | sort | count |
4 +----+-------+-------+------+-------+
5 |  1 | apple |  2.50 | A    |   500 |
6 +----+-------+-------+------+-------+
7 1 row in set (0.00 sec)

三、查询组的最大值的记录（若只查最大值，可以用group by 字段1，字段2...）：查询同类同产品的商品，哪一个商家卖的最高。

 1 mysql> select * from shop;
 2 +----+-------+-------+---------------+------+-------+
 3 | id | name  | price | supplier_name | sort | count |
 4 +----+-------+-------+---------------+------+-------+
 5 |  1 | apple |  2.50 | dangdang      | A    |   500 |
 6 |  2 | book  |  8.50 | tmall         | B    |   500 |
 7 |  3 | book  |  1.20 | jd            | B    |   200 |
 8 |  4 | apple |  1.50 | tmall         | A    |   500 |
 9 |  5 | pen   |  8.50 | jd            | B    |    80 |
10 +----+-------+-------+---------------+------+-------+
11 5 rows in set (0.00 sec)
12 
13 mysql> select * from shop s1
14     -> where price in
15     -> (select max(price) from shop s2 where s1.sort=s2.sort and s1.name=s2.name
16 );
17 +----+-------+-------+---------------+------+-------+
18 | id | name  | price | supplier_name | sort | count |
19 +----+-------+-------+---------------+------+-------+
20 |  1 | apple |  2.50 | dangdang      | A    |   500 |
21 |  2 | book  |  8.50 | tmall         | B    |   500 |
22 |  5 | pen   |  8.50 | jd            | B    |    80 |
23 +----+-------+-------+---------------+------+-------+
24 3 rows in set (0.00 sec)

 

 四、两个表查询

 1 mysql> select s.name,s.price,s.supplier_name,s.count from shop s,test t
 2     -> where s.supplier_name=t.name;
输出：
 3 +-------+-------+---------------+-------+
 4 | name  | price | supplier_name | count |
 5 +-------+-------+---------------+-------+
 6 | book  |  8.50 | tmall         |   500 |
 7 | book  |  1.20 | jd            |   200 |
 8 | apple |  1.50 | tmall         |   500 |
 9 | pen   |  8.50 | jd            |    80 |
10 +-------+-------+---------------+-------+
11 4 rows in set (0.00 sec)

 五、

posted @ 2015-09-17 16:36  sunshine-habit  阅读(146)  评论(0)    收藏  举报