MySQL学习笔记之select查询语句

我的学习资料是燕十八老师的MySQL****，有兴趣的朋友可以在老师的社区或微博里查看，在此不作过多介绍。通过对视频中select查询语句的学习，掌握了一些新的知识，并不是select查询语句的语法，而是各个查询子句内部的联系。本篇博客适合于学习了select查询语句基本语法之后的朋友，整理出来希望可以帮助到其他朋友。本文的编程环境是CentOS6.5，mysql5.6。主要内容包含以下三部分：

1. select查询模型

2. 5个查询子句之间的联系

3. 练习案例

查询模型

所谓查询模型就是你如何去理解一个表和查询语句，查询语句是怎么把满足要求的数据查询出来的。这对于新手来说可能还不太理解，但是理解了之后，对读懂SQL语句有很大帮助。主要是以下的查询模型：

把列看成变量，所以列之间可以进行运算；把where后面的条件看成表达式，只有满足表达式的行才会被筛选出来。

比如一个商品表goods中有店铺价shop_price和市场价market_price，要求在这个店里面买东西省钱200元以上的商品和省的钱。就可以用以下的select查询语句：

select goods_name,(market_price-shop_price) as discount from goods where (market_price - shop_price)>200;

市场价这一列和店铺价这一列进行了相减运算，得到了折扣价格，然后把折扣价大于200元的商品筛选了出来。

查询语句相互关系

5个子查询语句分别是：

• where 条件查询
• group by 分组
• having 筛选
• order by 排序
• limit 限制结果条数

重点讲一下这5个子查询之间的关系，如下图：

对图的简要分析如下：

原表经过where条件查询，从原表中筛选出满足条件的行，列不变依旧是原表中的列，得到结果集1。

select * from 表名 where 条件;

结果集1经过计算，产生新的列，得到结果集2（注：这一步也可能会用到group by分组统计）；例如：

select goods_name,(market_price – shop_price) as discount from goods;#市场价和店铺价相减运算产生了 折扣（discount）这一原表中没有的列

结果集2通过having的筛选，得到结果集3。这里有必要说明一下where和having的区别，看下面两个select语句，都是筛选出折扣价大于200的商品：

select goods_name,(market_price-shop_price) as discount from goods where (market_price-shop_price)>200;
select goods_name,(market_price-shop_price) as discount from goods having discount>200; 

通过上面两个语句发现他们的区别了吗？他们之间是条件表达式不同，where是针对表中的列，market_price和shop_price都是原表中的列，列是可以运算的；having是针对结果集中的列，discount 是结果集中其中一列的名称。

结果集3通过order by的排序，得到有序的结果集4，注意：order by 也是针对的结果集中的列。

如果排序之后有很多条记录，我只想看前3名，就需要用到limit来限制结果集4中显示的条目，得到最终的结果集5。

例如：我想看一下折扣价大于200的商品中折扣最多的前三名：

select goods_name,(market_price-shop_price) as discount from goods having discount>200 order by discount desc limit 3;

练习材料

看过之后动手练习一下学习效果会更好，我认为学习一门技能就应该多实践，在实践的过程中发现自己的问题，从而帮助了自己更深刻地理解理论知识。准备了如下的练习材料（来源于老师的教学资料）：

1. 主键为32的商品
2. 本店价格高于3000元的商品
3. 查询每个栏目下面最贵商品价格，最低商品价格，商品平均价格
4. 查询每个商品所积压的货款(提示:库存*单价)
5. 查询该店积压的总货款
6. 查询该店每个栏目下面积压的货款.
7. 查询比市场价省钱200元以上的商品及该商品所省的钱(where和having分别实现)
8. 查询积压货款超过2W元的栏目,以及该栏目积压的货款
9. 按栏目由低到高排序,栏目内部按价格由高到低排序
10. 取出价格最高的前三名商品
    

新建表和插入数据（goods.sql）：

create table goods (
  goods_id mediumint(8) unsigned primary key auto_increment,
  goods_name varchar(120) not null default '',
  cat_id smallint(5) unsigned not null default '0',
  brand_id smallint(5) unsigned not null default '0',
  goods_sn char(15) not null default '',
  goods_number smallint(5) unsigned not null default '0',
  shop_price decimal(10,2) unsigned not null default '0.00',
  market_price decimal(10,2) unsigned not null default '0.00',
  click_count int(10) unsigned not null default '0'
) engine=myisam default charset=utf8;

insert into `goods` values (1,'kd876',4,8,'ecs000000',1,1388.00,1665.60,9),
(4,'诺基亚n85原装充电器',8,1,'ecs000004',17,58.00,69.60,0),
(3,'诺基亚原装5800耳机',8,1,'ecs000002',24,68.00,81.60,3),
(5,'索爱原装m2卡读卡器',11,7,'ecs000005',8,20.00,24.00,3),
(6,'胜创kingmax内存卡',11,0,'ecs000006',15,42.00,50.40,0),
(7,'诺基亚n85原装立体声耳机hs-82',8,1,'ecs000007',20,100.00,120.00,0),
(8,'飞利浦[e-mail]',3,4,'ecs000008',1,399.00,478.79,10),
(9,'诺基亚e66',3,1,'ecs000009',4,2298.00,2757.60,20),
(10,'索爱c702c',3,7,'ecs000010',7,1328.00,1593.60,11),
(11,'索爱c702c',3,7,'ecs000011',1,1300.00,0.00,0),
(12,'摩托罗拉a810',3,2,'ecs000012',8,983.00,1179.60,13),
(13,'诺基亚5320 xpressmusic',3,1,'ecs000013',8,1311.00,1573.20,13),
(14,'诺基亚5800xm',4,1,'ecs000014',1,2625.00,3150.00,6),
(15,'摩托罗拉a810',3,2,'ecs000015',3,788.00,945.60,8),
(16,'恒基伟业g101',2,11,'ecs000016',0,823.33,988.00,3),
(17,'夏新n7',3,5,'ecs000017',1,2300.00,2760.00,2),
(18,'夏新t5',4,5,'ecs000018',1,2878.00,3453.60,0),
(19,'三星sgh-f258',3,6,'ecs000019',12,858.00,1029.60,7),
(20,'三星bc01',3,6,'ecs000020',12,280.00,336.00,14),
(21,'金立 a30',3,10,'ecs000021',40,2000.00,2400.00,4),
(22,'多普达touch hd',3,3,'ecs000022',1,5999.00,7198.80,16),
(23,'诺基亚n96',5,1,'ecs000023',8,3700.00,4440.00,17),
(24,'p806',3,9,'ecs000024',100,2000.00,2400.00,35),
(25,'小灵通/固话50元充值卡',13,0,'ecs000025',2,48.00,57.59,0),
(26,'小灵通/固话20元充值卡',13,0,'ecs000026',2,19.00,22.80,0),
(27,'联通100元充值卡',15,0,'ecs000027',2,95.00,100.00,0),
(28,'联通50元充值卡',15,0,'ecs000028',0,45.00,50.00,0),
(29,'移动100元充值卡',14,0,'ecs000029',0,90.00,0.00,0),
(30,'移动20元充值卡',14,0,'ecs000030',9,18.00,21.00,1),
(31,'摩托罗拉e8 ',3,2,'ecs000031',1,1337.00,1604.39,5),
(32,'诺基亚n85',3,1,'ecs000032',4,3010.00,3612.00,9);

select语句如下：

mysql> select goods_name from goods where goods_id=32;
mysql> select goods_id,goods_name,shop_price from goods where shop_price>3000;
mysql> select cat_id,max(shop_price),min(shop_price),avg(shop_price) from goods group by cat_id;
mysql> select goods_name,(goods_number*shop_price) as sumprice from goods;
mysql> select sum(goods_number*shop_price) from goods;
mysql> select cat_id,sum(goods_number*shop_price) from goods group by cat_id;
mysql> select goods_id,goods_name,(market_price-shop_price) as discount from goods where (market_price-shop_price)>200;
mysql> select goods_id,goods_name,(market_price-shop_price) as discount from goods having discount>200;
mysql> select cat_id,sum(goods_number*shop_price) as goods_money 
    -> from goods
    -> group by cat_id
    -> having goods_money>20000;
mysql> select goods_id,goods_name,cat_id,shop_price
    -> from goods
    -> order by cat_id asc,shop_price desc;
mysql> select goods_id,goods_name,shop_price
    -> from goods
    -> order by shop_price desc
    -> limit 3;

相关文件：建表的goods.sql和实验过程记录文件百度网盘，如需自取。
链接：https://pan.baidu.com/s/1vX6Ve1qLBw-T-aoNU92TqQ 密码：ifin