5.8

#b把列看成变量，把where后面看成php中if（exp）里的exp表达式
哪些行，被取出来？--哪一行能让exp为真，哪一行就能取出来
判断下一行取出什么
select goods_id,goods_name from goods where 1>2;
把列看成变量
既然是变量，变量之间就可以运算 。
取出商品id，商品名，本店价格比市场价省的钱。
select goods_id,goods_name,market_price-shop_price from goods where 1;
表里面原来没有“market_price-shop_price”的列
这一列其实是一个运算结果。属于较“广义投影”
你就把列看成变量，来运算即可。
列的运算结果。可以当成列看 。还可以起个列别名。
select goods_id,goods_name,(market_price-shop_price) as discount
from goods
where cat_id!=3;
帮我查出本店价比市场价省的钱，而且省钱200以上的商品。
select goods_id,goods_name,(market_price-shop_price) as discount
from goods
where (market_price-shop_price)>200;
where是对表中的数据发挥的作用，查询出数据来。
where发挥作用时。表上并没有discount列
发挥完作用，形成的结果才能discount
对于结果中的列。如果再筛选，须用having
查询模型（important）
列就是变量，在每一行上，列的值都在变化
where条件是表达式，在哪一行上表达式为真，
哪一行就取出来。
一道面试题
有如下表和数组
把num值处于[20,29]之间，改为20
num值处于[30,39]之间的，改为30
num
3
12
15
25
23
29
34
37
32
45
48
52
(3),(12),(15),(25),(23),(29),(34),(37),(32),(45),(48),(52);
把num当成变量看，因此num/10取整，再乘以10
update mian set num = floor(num/10)*10
where num >=20 and num<=39;
联系。把goods表中商品名为nokiaxxx的商品，改为htcxxx
substring concat()
select goods_id,good_name,concat('HTC',substring(goods_name,4)) from goods
where goods_name like '诺基亚%';
查出最贵的商品的价格
select max(shop_price) from goods;
查出最便宜商品的价格
select min(shop_price) from goods;
查出最早的商品。即good_id最小的值
select min(goods_id) from goods;
goods_number是指库存量
想统计，本店一共库存多少商品
因此，把所有字段的全部加起来
select sum(goods_number) from goods;
查看所有商品的品均价格
select avg(shop_price) from goods;
统计，一共有多少种商品
select count(*) from goods;
select count(*) from 表名，查询的就是绝对的行数
哪怕某一行所有字段权威null，也计算在内。
而select count(列名) from 表名
查询的是该列不为null的所有行的行数。
用count(*)，count(1)谁好呢？
其实对于myisam引擎的表，没有区别
这种引擎内部有一计数器在维护者行数
Inodb的表，用count(*)直接读行数。效率很低，因为Innodb要去输一遍
计算。第三个栏目下所有商品的库存量之和sum(goods_num)
select sum(goods_number) from goods
where cat_id = 3;
计算第四个栏目下所有商品的库存量之和
select sum(goods_number) from goods
where cat_id = 4;
一次计算完，每个栏目下的库存量之和
group分组查询
select sum(goods_number) from goods
group by cat_id;
select cat_id,sum(goods_number) from goods
group by cat_id;
select goods_id,sum(goods_number) from goods;
按cat_id分组，计算每个栏目下的商品的平均价格
select cat_id,avg(shop_price) from goods
group by cat_id;
严格的讲。select的ａ，ｂ列必须在group by a,b,c里选择
语义上才没有矛盾

5.10

查询本店价比市场价所省的钱。并且要求省钱200元以上
select goods_id,market_price,shop_price,(market_price-shop_price) as discount
from goods
where 1
having discount > 200;
查询每种商品积压的货款。
商品*价格
select goods_id,shop_price,goods_number,shop_price*goods_number
as huokuan from goods;
查询该店积压的总贷款
每个商品积压的货款之和
select sum(goods_number*shop_price) from goods;
查询每个栏目下积压的货款
select cat_id, sum(goods_number*shop_price) from goods group by cat_id;
查询出栏目积压货款金额。且要求查处金额大于两万的。
用到了group 与having
select cat_id,sum(goods_number*shop_price) as zhk from goods group by cat_id having zhk>20000;
查询本店价比市场价省的钱而且筛选出省钱200以上的商品。
用where having分别实现。
where实现
select goods_id,market_price,shop_price,(market_price-shop_price) as discount
from goods
where (market_price-shop_price) >200;
having实现
select goods_id,market_price,shop_price,(market_price-shop_price) as discount
from goods
having discount >200;
mysql> create table result (
-> name varchar(20),
-> subject varchar(20),
-> score tinyint
-> )engine myisam charset utf8;
insert into result values
('张三','数学',90),
('张三','语文',50),
('张三','地理',40),
('李四','语文',55),
('李四','政治',45),
('王五','政治',30);
查询出两门及两门以上不及格者的平均成绩。
第一步：查询多有的平均分
select name,avg(score) from result group by name;
下一步，算出每个人挂科的情况
select name,subject,score,score<60 as g feom result
综合上面两个语句
select name,avg(score),sum(score<60) as gks
from result group by name having gks>=2;
order by
当最终结果集出来，可以进行排序。
取出四个栏目下的商品，并且按价格由高到低排序。
select goods_id,goods_name,shop_price
from goods
where cat_id = 4
order by shop_price desc;
降序排列。
排序的语法：
order by 结果集中的列名desc/asc
按发布的时间升序排列即发布早的时间戳小的，靠前
select goods_id,goods_name,shop_price
from goods
where cat_id = 4
order by add_time asc;
按栏目排序
select goods_id,cat_id,goods_name,shop_price
from goods
order by cat_id asc;
我们按栏目升序排列，同一个栏目下的商品，再按商品的价格降序排列
select goods_id,cat_id,goods_name,shop_price
from goods
order by cat_id asc,shop_price desc;
多字段排序
order by 列 desc/asc，列2 desc/asc，列3 desc/acs
限制条目
select goods_id,cat_id,goods_name,shop_price
from goods
where cat_id=3
order by shop_price asc
limit 10;
查询出本店价格最高的前三名
Select goods_id,goods_name,shop_price
from goods
order by shop_price
desc
limit 0,3;
查询出本店最高的第三名和第五名
取第三到第五，即意味着跳过第一第二。因此偏移量offset是2
取第3 4 5条，即取三条，即N=3
select goods_id,goods_name,shop_price
from goods
order by shop_price desc
limit 2,3;
取出价格最高的一条商品
select goods_id,goods_name,shop_price
from goods
order by shop_price desc
limit 0,1;

5.11

where子查询
查出最新（goods_id最大）的一条商品
按goods_id desc排序，再取第一行。
select goods_id,goods_name from goods order by goods_id desc limit 0,1;
查出最新（goods_id最大）的一条商品 不用排序。
select goods_id,goods_name from goods where goods_id=33;
select goods_id,goods_name from goods
where goods_id = (select max(goods_id) from goods);
用where型子查询。查询每个栏目下goods_id最大的商品
先查出每个栏目下最大的goods_id
select max(goods_id) from goods
group by cat_id;
然后把goods_id = 16 32 33 28这几条取出来
select goods_id,goods_name from goods
where goods_id in (select max(goods_id) from goods group by cat_id);
如果where 列 = （内层sql），则内层sql返回的必是单行单列，单个值
如果where 列 in（内层sql），则内层sql只返回单列，可以多行
from 型子查询
即，内层sql的查询结果，当成一张临时表，供外层sql再次查询
查询每个栏目下goods_id最大的商品
select goods_id,goods_name from goods
order by cat_id asc,goods_id desc;
如果存在一张表，只需要对其group 即可得到每个栏目goods_id最大的商品
假设存在一张tmp表
select goods_id,cat_id,goods_name from goods
order by cat_id asc,goods_id desc;
select * from tmp group by cat_id;
select * from (select goods_id,cat_id,goods_name from goods
order by cat_id asc,goods_id desc) as tmp group by cat_id;
exist 型子查询
把外层sql的结果，拿到内层sql去测试，如果内层的sql成立，则该行取出。
要求：查出所有商品的栏目
再建一张栏目表
create table category(
cat_id int auto_increment primary key,
cat_name varchar(20) not null default ''
)engine myisam charset utf8;
insert into test.category
select cat_id,cat_name from gyshop.category;
观察商品表与栏目的关系
select count(*) ,cat_id from goods group by cat_id;
+----------+--------+
| count(*) | cat_id |
+----------+--------+
| 1 | 2 |
| 15 | 3 |
| 4 | 4 |
| 1 | 5 |
| 3 | 8 |
| 2 | 11 |
| 2 | 13 |
| 2 | 14 |
| 2 | 15 |
+----------+--------+
不把14个栏目都取出来，只把下面有商品的栏目取出来
取栏目表 ，且只取下面有商品的栏目
select cat_id,cat_name from category
where exists (select * from goods where goods.cat_id=category.cat_id);

5.13加油！

<?php
header('Content-type:text/html; charset=utf-8');
/*
思路:
连接数据库,
查询商品表,得到商品名称/库存/价格
得到数组后
循环打印tr
*/
$conn = mysql_connect('localhost','root','xiaosatufu');
$sql = 'use test';
mysql_query($sql,$conn);
$sql = 'set names utf8';
mysql_query($sql,$conn);
// 这是第1条sql,设返回N条数据
$sql = 'select goods_name,cat_id,goods_number,shop_price from goods';
$rs = mysql_query($sql,$conn);
$list = array();
while($row = mysql_fetch_assoc($rs)) {
/**
根据$row中的cat_id,再查category表
每循环一次,又要查一次另外一张表
因此,共查询:1+N次.
**/
$sql = 'select cat_name from category where cat_id=' . $row['cat_id'];
$rs2 = mysql_query($sql,$conn);
$cat = mysql_fetch_assoc($rs2); // array('cat_name'=>'xxxx');
$row['cat_name'] = $cat['cat_name'];
$list[] = $row;
}
//print_r($list);exit;
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="zh-CN">
<head>
<title>新建网页</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="description" content="" />
<meta name="keywords" content="" />
<script type="text/javascript">
</script>
<style type="text/css">
</style>
</head>
<body>
<h1>我也会做报价单</h1>
<table border="1">
<tr>
<td>商品名称</td>
<td>栏目名称</td>
<td>商品库存</td>
<td>商品价格</td>
</tr>
<?php foreach ($list as $v) { ?>
<tr>
<td><?php echo $v['goods_name']; ?></td>
<td><?php echo $v['cat_name']; ?></td>
<td><?php echo $v['goods_number']; ?></td>
<td><?php echo $v['shop_price']; ?></td>
</tr>
<?php } ?>
</table>
</body>
</html>

<?php
header('Content-type:text/html; charset=utf-8');
/*
思路:
连接数据库,
查询商品表,得到商品名称/库存/价格
得到数组后
循环打印tr
*/
$conn = mysql_connect('localhost','root','xiaosatufu');
$sql = 'use test';
mysql_query($sql,$conn);
$sql = 'set names utf8';
mysql_query($sql,$conn);
/*
第二个改进版,用两表全相乘,来查询.
这次用一条sql就查出来,技术经理没说话.
技术总监又看到了.
说: 小王,你这两张表全相乘,
比如 A表10000行,B表10000行,都不算大.
但是全相乘,在内存中生成一个非常大的数据. 10000*10000行.
另外:索引没利用
这不行,继续改进
*/
$sql = 'select goods_id,goods_name,goods_number,shop_price,cat_name from goods,category where goods.cat_id=category.cat_id';
$rs = mysql_query($sql,$conn);
$list = array();
while($row = mysql_fetch_assoc($rs)) {
$list[] = $row;
}
//print_r($list);exit;
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="zh-CN">
<head>
<title>新建网页</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="description" content="" />
<meta name="keywords" content="" />
<script type="text/javascript">
</script>
<style type="text/css">
</style>
</head>
<body>
<h1>我也会做报价单</h1>
<table border="1">
<tr>
<td>商品名称</td>
<td>栏目名称</td>
<td>商品库存</td>
<td>商品价格</td>
</tr>
<?php foreach ($list as $v) { ?>
<tr>
<td><?php echo $v['goods_name']; ?></td>
<td><?php echo $v['cat_name']; ?></td>
<td><?php echo $v['goods_number']; ?></td>
<td><?php echo $v['shop_price']; ?></td>
</tr>
<?php } ?>
</table>
</body>
</html>

<?php
header('Content-type:text/html; charset=utf-8');
/*
思路:
连接数据库,
查询商品表,得到商品名称/库存/价格
得到数组后
循环打印tr
*/
$conn = mysql_connect('localhost','root','xiaosatufu');
$sql = 'use test';
mysql_query($sql,$conn);
$sql = 'set names utf8';
mysql_query($sql,$conn);
/*
第三个改进版,用左连接来查询.
[goods left join category on goods.cat_id = category.cat_id] 当成C表看
select goods_id,goods_name,goods_number,shop_price,cat_name from C;
select goods_id,goods_name,goods_number,shop_price,cat_name from goods left join category on goods.cat_id = category.cat_id;
这不行,继续改进
*/
$sql = 'select goods_id,goods_name,goods_number,shop_price,cat_name from goods left join category on goods.cat_id = category.cat_id;';
$rs = mysql_query($sql,$conn);
$list = array();
while($row = mysql_fetch_assoc($rs)) {
$list[] = $row;
}
//print_r($list);exit;
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="zh-CN">
<head>
<title>新建网页</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="description" content="" />
<meta name="keywords" content="" />
<script type="text/javascript">
</script>
<style type="text/css">
</style>
</head>
<body>
<h1>我也会做报价单</h1>
<table border="1">
<tr>
<td>商品名称</td>
<td>栏目名称</td>
<td>商品库存</td>
<td>商品价格</td>
</tr>
<?php foreach ($list as $v) { ?>
<tr>
<td><?php echo $v['goods_name']; ?></td>
<td><?php echo $v['cat_name']; ?></td>
<td><?php echo $v['goods_number']; ?></td>
<td><?php echo $v['shop_price']; ?></td>
</tr>
<?php } ?>
</table>
</body>
</html>

mysql> #test10看成集合有三个元素，test11看成集合有2个元素
mysql> #如果把test10，test11相乘，得到留个元素。
mysql> #在数据的操作上如何操作表完成集合的操作的效果，
mysql> #直接用逗号隔开表明，查询即可。
mysql> #量表做全相乘
mysql> #从行的角度看，是两表每一行，两两组合。
mysql> #从列的角度看，结果集中的列，是两表的列名的相加
mysql> select goods_id,goods_name, cat_id,cat_name from goods,category;
ERROR 1052 (23000): Column 'cat_id' in field list is ambiguous
mysql> #ambiguous 意思是模糊的。
mysql> #cat_id,在2张表都有，到底是指那张表的cat_id字段？
mysql> #如果在多表联查时，在某一列名前，在2张或者2张以上都有。
mysql> #则需要在列明前，指定表名，即 表名.列名
mysql> select goods_id,minigoods.cat_id,goods_name,category.cat_id,cat_name
-> from minigoods,category
-> where minigoods.cat_id=category.cat_id;