1. 查处最便宜的商品的价格

select min(shop_price) from goods;

1. 查询出发布最早的商品。即goods_id 最小的值.

select min(goods_id) from goods;

1. goods_number 是指库存量，统计一下，本店一共库存多少件商品

select sum(goods_number) from goods;

1. 查看一个店中的所有商品的平均价格.

select avg(shop_price) from goods;

1. 统计一下本店中有多少的商品。(不是多少个)

count 计算 多少条记录.
如果使用字段，来count()计算总共，null并不会计算在其中.

select count(*) from goods; # 查询的就是绝对的行数，哪怕某一行所有字段全为null，也计算在内。
select count(goods_id) from goods; # 字段如果是 null，并不会计算在其中。

用 count(*)，count(1)，谁更好呢?
对于myisam引擎的表，没有区别的。因为，这中引擎内部，有一个计数器在维护着行数。
innodb的表，用count(*)直接读取行数，效率低下，因为innodb真的去数一边。

5个统计函数，单独使用，意义不大，要和分组配合起来使用，威力更大。

计算，第3个栏目下所有商品的库存量之和 sum(goods_num);

select sum(goods_number)  from goods where cat_id = 3;  

计算，第4个栏目下所有商品的库存量之和

select sum(goods_number) from goods where cat_id = 4;

group 子句

计算，一次计算完，每个栏目下的库存量之和
按照规则，分组，然后 组内解决总和。

分组之后再统计，结合起来使用。

select sum(goods_number) from goods group by cat_id;

是否正确：

select goods_id, sum(goods_number) from goods;

这条语句执行了，把 goods_id，第一次出现的值 取出来。
对于SQL标准来说，这个语句是错误的，不能执行的。但是在MySQL中可以这么做。(MySQL特征)
这是MySQL的一个特点，出于可移植性和规范性，不推荐这样写。
严格的讲，select 的a, b 列必须在 group by a, b, c的列出现。

按cat_id分组，计算每个栏目下的商品的平均价格

select goods_id, cat_id, avg(shop_price) from goods group by cat_id;

having 子句

having筛选

having 发挥的时间，是where对硬盘上的表文件进行查询之后，内存形成一张结果的虚拟表(伪列存在于此表中)， having在此时生效。

1. 查询本店价比市场价省的钱，并且要求省钱200元以上的取出来。

select goods_id, market_price, shop_price, (market_price - shop_price) as discount from goods having discount > 200;
# where 没有写出，表示where 1;

1. 查询每种商品所积压的贷款(提示: 库存 +单价)
   goods_number + shop_price

select goods_id, goods_number, shop_price , (goods_number * shop_price) as hk from goods ;

1. 查询该店积压的总货款。

   即，每个商品积压的贷款之和。考虑：sum那个列?
   
   

select sum(shop_price * goods_number) as zhk from goods;

1. 查询每个栏目下，积压的货款。

select cat_id, shop_price, goods_number, sum(shop_price * goods_number) as hk from goods group by cat_id;

1. 查询积压货款超过2w元的栏目，以及该栏目积压的贷款。

select cat_id, shop_price, goods_number, sum(shop_price * goods_number) as hk from goods group by cat_id having hk > 20000;

1. 查询本店价比市场价省的钱，且筛选出省钱200以上的商品. (用where 和 having 分别来实现)

#where
select goods_id, market_price, shop_price from goods where (market_price - shop_price) > 200;

#having
select goods_id, market_price, shop_price from goods having (market_price - shop_price) > 200;
select goods_id, market_price, shop_price, (market_price - shop_price) as discount from goods having discount > 200;

查询出2门及2门以上的不及格者的平均成绩

CREATE TABLE `reslute` (
  `name` varchar(20) DEFAULT NULL,
  `subject` varchar(20) DEFAULT NULL,
  `score` tinyint(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8

insert into reslute values ('zhangsan', 'math', 90), ('zhangsan', 'ch', 50), ('zhangsan', 'dili', 40), ('lisi', 'ch', 55), ('lisi', 'zhengzhi', 45), ('wangwu', 'zhengzhi', 30);

错误

select name, avg(score), count(score < 60) as sc from reslute group by name having sc >= 2;

正确查询

# 1: 查询所有的平均分
select name, avg(score) from reslute group by name;

# 2: 想办法计算出每个人挂科的情况
select name, subject, score, score<60 as g from reslute;

# 3: 挂科数目，就是g 列的 sum() 结果.
# 总结下
select name, avg(score), sum(score < 60) as sc from reslute group by name having sc >= 2;

order by 与 limit

order by
语法: order by 列名 desc/asc
. (列名，结果集中的列名)
例如: order by add_time asc
 . 按发布时间升序排列.

1. 取出第4个栏目下的商品，并按价格由高到低排序。

select goods_id, goods_name, shop_price from goods where cat_id = 4 order by shop_price desc;

1. 按栏目升序排列，同一个栏目下的商品，再按商品的价格降序排列

select goods_id,cat_id, goods_name, shop_price from goods order by cat_id asc, shop_price desc;
# 多个字段排序，通过`,` 逗号隔开。

limit
限制条目
limit 数量
 限制条数
limit [offset] N

offset 偏移量 (可选参数),跳过几行。 N 取出条数。
offset 如果不写则相当于 limit 0 N
； offset 是跳过的个数，N是实际取的个数。

1. 本店商品价格最高的商品。

select goods_id, goods_name, shop_price from goods order by shop_price limit 3;

1. 本店最高的第三名到第五名 商品。

select goods_id, goods_name, shop_price from goods order by shop_price desc limit 2,3;
# 取第3到第5，即意味着跳过， 第1，第2, 因此偏移量offset是2.
# 取第3,4,5条。即取3条，因此N=3；

1. 取出价格最高的那条商品

select goods_id, goods_name, shop_price from goods order by shop_price desc limit 1;

1. 查询出每个栏目下id号最大(最新)的一条商品.

# 错误语句分析
第一种错误：group by cat_id;
group by cat_id 从语义上来分析，select 的列，只能是cat_id, max/min/avg/sum/count .
这个 goods_id, goods_name 从语义上分析就不对，也不符合sql标准。
只是MySQL允许这种语法。
取出 栏目中 第一次 出现的记录。

第二种错误：name 匹配第一次出现的
select max(goods_id) as max_cat_id, goods_id, goods_name from goods group by cat_id;

第三种错误：先 order by 再 group by. 语法错误。
select goods_id, cat_id, goods_name from goods order by cat_id asc, goods_id desc;
在此基础上，再分组。
select goods_id, cat_id, goods_name from goods order by cat_id asc, goods_id desc group by cat_id;

需要使用到 子查询.

子查询

where 型子查询

把内层查询的结果，作为外层查询的比较条件
典型的：查询最大商品，最贵商品。

如果 where 列 = (内层 sql)。 则内层sql返回的必然是单行单列.
如果 where 列 in (内层 sql)。 则内层sql返回单列， 可以多行。

1. 查询出本店最新的(goods_id最大) 的一条商品。

# 思路，按照goods_id desc 排序，再去 第一行.
select goods_id, goods_name from goods order by goods_id desc limit 1;

1. 查询出本店最新的(goods_id最大) 的一条商品。要求不要使用排序.

# 思路，求出最大的id， 然后在where判断
select goods_id, goods_name from goods where goods_id = (select max(goods_id) from goods);

查询出每个栏目下id号最大(最新)的一条商品.

# 第一步：查询出每个栏目下goods_id 最大的。
select max(goods_id) from goods group by cat_id;
# 只需要再把goods_id = 第一步值.
select goods_name, goods_id from goods where goods_id in (select max(goods_id) from goods group by cat_id);

from 型子查询

内层sql的查询结果，当成一张临时表，供外层sql再次查询.

查询模型：查询结果集 ---> 在结构上可以当成表看.

查询出每个栏目下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;

exists型子查询

把外层sql的结果，拿到内层sql去测试。
如果内层的sql成立，则该行取出。

查询有商品的栏目.取 栏目标，且只取下面有商品的栏目
思考：什么样的表，叫做 下面栏目有商品?
设某栏目cat_id 为N,则 select * from goods where cat_id = N;
能取出数据，则说明该栏目有商品

select cat_id, cat_name from category where exists (select * from goods where goods.cat_id = category.cat_id);
# 从外层SQL取出内层SQL需要的字段值，然后再通过内层SQL来判断.

奇怪的NULL

建表时，列后面 not null defalut ''/default 0; 这是什么意思
就是让这个列值不为NULL， 如果某个列确实没填，也有默认值，值不为NULL

为什么列的值不为NULL。

create table test5 ( sname varchar(20) ) engine myisam charset utf8;

insert into test5 values ('tianjilaoren'), ('lixunhuan'), ('afei'), NULL;

# 查询出用户名不为null的行

select * from test5 where sname != null;

# tianjilaoren lixunhuan afei 没有查询出来?

#查询 sname 为null 的行

select * from test5 whre sname = null;

#也是空

select 'afei' = null; # 查询结果： NULL

# NULL为家， list=null 是假

select null = null; #查询结果： NULL

# null = null , 还是 null， 还是假

select null != null; #查询结果：NULL

# null是空

# null 的比较需要用特殊的运算符 is null 和 is not null

select * from test5 where sname is not null;

# 取出值为null

select * from test5 where sname is null;

null 是一种类型，比较时，只能用专门的is null 和 is not null 来比较 碰到运算符， 一律返回 null.
效率不高，不利于提高索引效率。

因此，往往在建表的时候声明 not null default ''/defalut 0

新手1+N模式

新手1+N模式查询

$conn = mysql_connect('localhost', 'root', '');

$sql = 'use zf';

mysql_query($sql, $conn);

$sql = 'set names utf8';

mysql_query($sql, $conn);

$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);
    
    $row['cat_name'] = $cat['cat_name'];
    
    $list[] = $row;
    
}

左链接&右链接

两表全连接查询

集合知识

集合的特点： 无序性 ， 唯一性
集合的运算：求交集，求并集，笛卡尔积(相乘)

笛卡尔积，即集合的元素，做两两的组合。

表与集合的关系

一张表就是一个集合
每一行就是一个元素.

集合不能重复，但可能有两行数据完全一样。
MySQL内部每一行，还有一个rowid.

场景：

create table test7 ( id int, name varchar(20) ) engine myisam charset utf8;
create table test8 ( cat_id int, cat_name varchar(20) ) engine myisam charset utf8;

insert into test7 values (1, '桃子'), (2, '苹果'), (3, '梨');
insert into test8 values (95, '月亮'), (96, '星星');

在数据库的操作上，如何操作表，完成集合的笛卡尔积的效果。
直接使用","隔开表名，查询即可。

select * from test7,test8;

两表做全相乘
从行的角度来看：就是2表每一行，两两组合
从列的角度来看：结果集中的列，是两表的列名的相加

做全相乘

create table minigoods like select * from goods;

select goods_id, minigoods minigoods.cat_id, goods_name, category.cat_id, cat_name from minigoods,category;

# 全相乘
# 加了条件之后
select goods_id, minigoods minigoods.cat_id, goods_name, category.cat_id, cat_name from minigoods,category where minigoods.cat_id = category.cat_id;

左连接语法及应用

Mysql 优化：索引优化, 字段优化, 存储引擎优化 (索引是发生在查询过程中)

假设A表在左，B表在A表的右边滑动。
A表与B表通过一个关系来筛选B表的行

语法： A left join B on 条件

条件为true，则B表对应的行，取出.

A left join B on 条件
 这一块形成的也是一个结果集,也可以看成一张表。 可以对 A left join B on 条件
 as C 。可以对C表做查询， 自然where, group, having, order by, limit 照常使用。 还可以继续 left join. A left join B on 条件 left join D on 条件

C 表的可以查询的列有哪些列 ?
A,B 的列都可以查询。

select goods_name, goods_number, shop_price, cat_name from goods left join category on goods.cat_id = category.cat_id;

左右内连接的区别

没有另一张表对应的行，使用NULL补齐。
多行对应，该如何解决。

create table boy (
bname varchar(20),
other char(1) 
) engine myisam charset utf8;

insert into boy 
values 
('lin','A'),
('李寻欢','B'),
('阿飞','C'),
('呆鹅','D'),
('Weibo','E'),
('贾宝玉','F');

create table girl (
gname varchar(20),
other char(1)
) engine myisam charset utf8;

insert into girl
values 
('陈志芳','A'),
('飞儿','C'),
('飞飞','C'),
('石头','D');

select boy.*, girl.* from boy left join girl on boy.other = girl.other;

结果：

是以boy表, 为基准。

select boy.*, girl.* from girl left join on boy.other = girl.other;

结果:

是以 girl表 为基准

注意：a left join b, 并不是说a表的就一定是在左边，只是说在查询数据时，以a表为准。
NULL,是补齐，而不是查询出来的。

左右连接是可以互换的.
A left join B, 就等价于 B right join A;

select boy.*, girl.* from boy right join girl on boy.other = girl.other;

注意：左右连接 可以互换， 尽量使用 左链接， 出于移植时兼容性方面考虑。

内连接的特点

select boy.*, girl.* from boy inner join girl on boy.other = girl.other;

两个表中符合条件且都有值，拿出来。

结果：

从集合的角度看：
A inner join B 和 left join/right join 的关系：
内连接是左右连接的交集

外连接

左右连接的并集, 外连接。
但是，在mysql中不支持外连接。

题目：

create table m (
mid int,
hid int,
gid int,
mres varchar(10),
matime date
) engine myisam charset utf8;


create table t (
tid int,
tname varchar(20)    
) engine myisam charset utf8;

insert into m 
values 
(1,1,2,'2:0','2006-05-21'),
(2,2,3,'1:2','2006-06-21'),
(3,3,1,'2:5','2006-06-25'),
(4,2,3,'3:2','2006-07-21');


insert into t 
values 
(1,'guoan'),
(2,'shenhua'),
(3,'gongyiliandong');

查询 :

# 取出出主客队的ID， 并不做特殊处理.
select hid, mres, gid, matime from m;

# 根据hid, 左联t表 ，查出主队的队伍名称
select hid,tname, mres, gid, matime from m left join t on m.hid = t.tid;

# 根据gid， 查出客队的队伍名称.

select hid, tname, mres, tname, gid, tname, matime from 
(m left join t on m.hid = t.tid) left join t on m.gid = t.tid;
# 错误的原因 是 m t t 相连， 名字冲突，起个别名，就可以解决.
# select 列名 as 别名。也可以 .


select hid, t1.tname, mres, gid, t2.tname, matime from 
(m left join t as t1 on m.hid = t1.tid) 
left join t as t2 on m.gid = t2.tid;

# 最后拼接结果：
select hid, t1.tname, mres, gid, t2.tname, matime from
(m left join t as t1 on m.hid = t1.tid)
left join t as t2 on m.hid = t2.tid
where matime > '2006-06-01' and matime < '2016-07-01';

#三表联查 + where.

# 第二种方法：使用 inner join 
select hid, t1.tname, mres, gid, t2.tname, matime from m inner join t as
t1 on m.hid = t1.tid inner join t as t2 on m.gid = t2.tid where matime > '2006-0
6-01' and matime < '2016-07-01';

查询完成ecshop留言板

PHP 逻辑来完成留言板查询

header('Content-type: text/html; charset=utf-8');

/**
 * 商城留言板
 * 
 * 一般情况下，做留言板的显示很容易。
 * 直接select 查询，再显示出来。
 * 
 * 但是ecshop中的留言板难点在于
 * 留言条数来自于2张表。
 * feedback 留言表
 * comment 评论表
 * 
 * 需要把两张表中的数据都取出来，显示结果。
 */
 
/**
 * 思路：从业务逻辑层，用PHP来解决问题
 * 1. 先取出feedback 表，循环取出数据，放入一个数组
 * 2. 再取出comment 表，循环取出数据，放入一个数组
 * 3. 取出的两个数组合并
 * 4. 循环合并后的数组
 */ 

$counn = mysql_connect('localhost', 'root', '');

$sql = 'use ecshop';
mysql_query($sql, $counn);    
 
$sql = 'set names utf8';
mysql_query($sql, $counn);

// 取出feedback表中数据
$sql = 'select user_name, msg_content, msg_time from ecs_feedback where msg_status = 1';

$feeds = array();
$rs =    mysql_query($sql, $counn);

while ( $row = mysql_fetch_assoc($rs) ) {
    
    $feeds[] = $row; 
    
}

// 取出comment表中数据
$sql = 'select user_name, content as msg_content, add_time as msg_time from ecs_comment where status = 1';
$rs = mysql_query($sql, $counn);

$comm = array();
while ( $row = mysql_fetch_assoc($rs) ) {
    
    $comm[] = $row;
            
}

$all = array_merge($feeds, $comm);

union用法深入讲解

union : 合并 2 条 或多条语句的结果.

语法：sql1 union sql2;

1. 查询出价格低于100元 和 价格高于4000元的商品.(不能使用 or)

# 先查询<100的商品
select goods_id, goods_name, shop_price from goods where shop_price < 100;

# 查询 > 4000 的商品
select goods_id, goods_name, shop_price from goods where shop_price > 4000;

# 使用，union  联合查询 、

select goods_id, goods_name, shop_price from goods where shop_price < 100 union select goods_id, goods_name, shop_price from goods where shop_price > 4000;

• 能否从2张表查询在union呢?

1. 合并的是 '结果集'，不区分来自于那一张表。

取自于2张表，通过别名让2个结果集的列一致。
那么，如果取出的结果集，列名字不一样，还能否union。
可以使用 union，而且取出的最终列名，以第一条SQL为准.

• union 满足什么条件就可以使用 ?
  结果集中的字段数量一致

列的类型不一致，不一样，没关系，也是可以合并的。合并的意义不大。

• union 后的结果集，可否再排序呢?
  可以再排序。 sql1 union sql2 order by 字段;

select goods_id, goods_name, shop_price from goods where shop_price < 100 union select goods_id, goods_name, shop_price from goods where shop_price > 4000 order by shop_price asc;

• 用union,取出第4个栏目的商品和第5个栏目的商品，并按价格升序排列.

select goods_id, goods_name, shop_price from goods where where cat_id = 4 union select goods_id, goods_name, shop_price from goods where cat_id = 5 order by shop_price asc;    

• 使用 order by 的注意事项

sql 语句中的 desc 没有发挥作用.

外层的语句还要对最终结果，再次排序。因此，内层的语句的排序，就没有意义。
因此: 内层的order by 语句单独使用， 不会影响结果集，仅排序。在执行期间，就被MySQL的代码分析器优化。
内层的order by 必须能够影响结果集时，才有意义。 比如 配合 limit 使用。
根据是否影响结果集，选择性优化。

• 第3个栏目下，价格前3高的商品 和 第4个栏目下， 价格前2 高的商品。(使用union完成)

(select goods_id, cat_id, goods_name, shop_price from goods where cat_id = 3 order by shop_price asc limit 3)
union
(select goods_id, cat_id, goods_name, shop_price from goods where cat_id = 4 order by shop_price asc limit 2);

内层的order by 发挥了作用， 因为有 limit。order by 会实际影响结果集，有意义。

• 如果 union后的结果有重复(即某2行，或N行， 所有的列，值都一样),如何变化。

这种情况下，是比较常见的，默认会去重复。

如果不想去重复。
可以使用 union all;

union 面试题
懒，能写一行绝不写二行。

CREATE TABLE `num1` (
  `id` varchar(10) DEFAULT NULL,
  `num` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;



CREATE TABLE `num2` (
  `id` varchar(10) DEFAULT NULL,
  `num` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


insert into num1 values ('a', 5), ('b', 10), ('c',15), ('d', 10);

insert into num2 values ('b', 5), ('c', 15), ('d', 20), ('e', 99);

# 查询
select id, sum(num) from (select * from num1 union all select * from num2) as tmp order by id;

数学函数与字符串函数

MySQL函数

数学函数
sqrt(); 计算指定数字的平方根
rand(); 生成随机数

聚合函数

avg(col); 返回指定列的平均值
count(col); 返回指定列中非null值的个数
min(col); 返回指定列的最小值
max(col); 返回指定列的最大值
sum(col); 返回指定列的所有值之和
group_concat(col); 返回由属于一组的列值连接组合而成的结果

字符串函数

ascii(); 计算字符的ascii码
length(col); 计算字符串的字节长度
char_length(col); 计算字符数
reverse(col); 字符串反转
position('@' in 'abc@sina.com') 返回字符串位置
right('abc@sina.com', 8); 返回 后边的 字符串

create table test14 (uname varchar(20), email varchar(30))engine myisam charset utf8;
inesrt into test14 values ('lily', 'lily@163.com'), ('zhifang', 'zhifang@qq.com');
select email, right( email, length(email) - position('@' in email) ) from test14;

# 对于email，并非全部的存储一个字段，而是分开存储了，变得更加高效。
# 把 email拆成 @ 前后2部分，放在2个列。

时间函数与流程控制函数

时间函数

now(); 返回 年月日时分秒
curdate(); 返回 年月日
curtime(); 返回 时分秒
dayofweek('2016-09-18'); 查看某一天，是那一周的第几天 . (西方的周日第一天).
week( curdate() ); 查看某一年的第几周. 38

create table jiaban (
num int,
dt date    
)engine myisam charset utf8;

insert into jiaban 
values
(5, '2012-09-01'),
(6, '2012-09-02'),
(7, '2012-09-03'),
(8, '2012-09-04'),
(9, '2012-09-05'),
(10, '2012-09-06'),
(11, '2012-09-07'),
(12, '2012-09-08'),
(13, '2012-09-09'),
(14, '2012-09-10'),
(15, '2012-09-11'),
(16, '2012-09-12');

# 按周统计加班时间

select sum(num), week(dt) as wk from jiaban group by wk;

加密函数

md5(); md5加密，不可逆，单向加密，不可逆。

良好的加密：
1， 不可逆性

1. 碰撞性低 （重复的概率低）

控制流程函数

case 值 when 某种可能 then 返回值 when 另一种可能值 then 返回值 else 默认值 end

select sname, case gender when 1 then '男' when 0 then '女' end as xingbie from test15;

if ( 条件, '', '' ); 三元运算符

select sanme, if (gender=0, '优先', '等待') as vip from test15;

ifnull(expr1, epxr2); 判断第一个表达是否为null，如为null， 返回第2个表达式的值; 如不为null，返回自身，即表达式1.

ifnull(null, 0);
ifnull('',0);

系统调试函数

user(); 返回用户即所在主机 , 判断自己的身份
database(); 当前所在的库
version(); 服务器版本 , 服务器比较古老，某些兼容性问题，需要判断。

MySQL函数使用注意事项

PHP中和MySQL中都有相同的函数，那么优先使用哪种?

1. MySQL的函数肯定是要影响查询速度.
   应该在建表的时候，通过合理的表结构减少函数的使用。 比如：emial, 按 @ 前后拆分。
2. 如果确实需要使用函数。
   比如时间的格式化， 在 mysql里用date_format, 在php里用date可以实现。

优先放在业务逻辑层，即PHP层处理。

1. 在查询时使用了函数，最大的一个坏处。
   以 date_format(A); 则A列的索引将无法使用。

如果针对某些查询，而此列，用上了函数来判断。
此列将不再使用索引。

例如：select name, email from table where right('@', length(email) - position('@' in email) );
email 列使用索引，可以加快查询速度。
但因为使用的并不是email列，而是函数处理后的email的返回值。
因此，email列的查询就 非常缓慢。

总结：在where条件中，对某列的使用了函数，由此列的索引不发挥作用。

视图 VIEW

在查询中，经常把查询结果 当成临时表来看。
View是什么? View 可以看一张虚拟表。是表通过某种运算得到的一个投影(映射)。
View 没有实实在在的数据，只是通过表，经过一系列的运算，运算出来的一个结果。

表的变化，会影响到视图。

查询每个栏目下，商品的平均价格,并取平均价前3高的栏目

select avg(shop_price) as pj, cat_id from goods group by cat_id order by pj desc limit 3;

group by cat_id,查询出每个栏目下的平均价，设为结果集A。无论是查询前3高，还是前3低，都要用到结果集A。结果集A频繁使用，因此可以把结果保存一张表，下次来查这张表。但是，如果goods表又添加了商品，A结果集就与保存的临时表不一样了，不会单项更新数据，这时，可以用视图解决。

创建视图
建视图的，要指定视图的列名与列类型么?
不需要，它是个影子，继承了上面的字段。 只是一种关系。

既然视图只是表的某种查询的投影，所以主要的步骤在于查询表上，查询的结果命名为视图就可以了。

创建视图的语法
create view 视图名 as select 语句;

视图的作用：

• 可以简化查询.

比如:复杂的统计时，先用视图审查一个中间结果，在查询视图。

• 更精细的权限控制。

比如:某张用户表为例。 2个网站搞合作，可以查询对方网站的用户.
需要向对方开发用户表的权限，但是呢，又不想开放用户表中的密码字段。创建一个视图，除去密码字段。

create view vuser
as
select user_id, username, emial from user;

开放这个视图的权限给对方。

• 数据多，分表时可以用到。

比如：小说站 novel 表， 1000多万篇。(一张表放200万数据，大概)
可以分成 novel1, novel2, novel3 ... 直到放完。每张表放200万。
查询小说时，不知在哪张表。

create view novel as 
select title from novel1 union select title from novel2 ... union title from 最后一张表;

视图是表的一个影子。
表与视图，数据变化时的相互影响问题

表的数据变化，要影响到视图的变化。

视图如果变化了，表如何变?
视图某种情况下，也是可以修改的
要求：视图的数据和表的数据 一一对应。 就像函数的映射才行。
表--> 退出视图的对应的数据
视图-->推出表对应的数据

视图往往是用来查询。

视图的定义，是一直存在的，视图并没有占用空间。

.frm
 表结构 定义文件
.MYD
 表的数据
.MYI
 表的索引

删除视图

drop view gui;

一一对应是指：根据select关系，从表中取出的行，只能计算出视图中确定的一行。反之，视图中任意抽一行，能够反推出表中的确定一行。

视图algorithm

cretae view v1
as
select * from goods where shop_price>300;

下次查询价格>300 且小于500

select * from v1 where shop_price<500;


select * from goods where shop_price > 300 and shop_price < 500;

把建视图时的条件 和 查视图的条件 叠加起来， 直接去查表。

对于一些简单视图，在发挥作用的过程中，并没有建立临时表，而只是 把条件存起来，下次来查询，把条件一合并，直接去查表。

条件叠加相比于建临时表，那个快。
建表：查询->形成临时表->查询临时表

叠加：合并条件->查询表

需要建立临时表么，还是合并语句。

algorigthm作用就是是否需要合并条件查询语句

algorigthm=merge (合并查询语句)
temptable 临时表
undefined 未定义，由系统判断

create algorithm = merge view v2
as 
select * from goods where shop_price > 300;

select goods_id, goods_name, shop_price from v2 from where shop_price < 500;

v2视图，并没有建立临时表
有时候，在复杂查询下，必须建立临时表。
比如，每个栏目的平均价格。

create view v3 as select *  from goods order by cat_id asc, shop_price desc;

下次再查，每个栏目最高的商品价格

select * from v3 group by cat_id;

思考：如何合并这两个语句。

合并后，语句 出错了.
此时，语句不能合并，只能先建立临时表。

create algorithm=temptable view v3 as
select goods_id, cat_id, goods_name, shop_price from goods order by cat_id asc, shop_price desc;

这张表，明确指定了生成临时表。
如果拿不准用什么，或者不愿意考虑。alogrithm=undefind .让系统做决定.

对于简单查询使用 alogrihtm=merge

GB2312与UTF8编码

字符集

计算机中，只有 01010101
而人的世界中，有文字，有图片，有声音。

01 ----> 文字对应起来

认为的约定
65-->A
66-->B
...

二进制编码 到 字符的映射，就是字符集

ASSIC

1个字节8个位，就足够。实际上ASSIC ，使用7个位表示就足够。
0-127来表示
0xxx xxxx 来表示。最高位始终是0

gb2312 --> gbk
汉字2个字节
碰到>128的，就再往后找一字节，2个字节理解成中文。
继续找，找到>128,就带一个字节。<127 一个字节表示。

解决了多字节之后，又引来一个问题--世界各国的字符集，兼容问题。

Uincode
Unicode是一个世界通用的码表

unicode 与 utf-8 的关系?
unicode 是 4个字节存储。
unicode 只负责分配编号，而不负责在网络上传输，需要经过一定的规则进行简化。
转换格式：UTF。
压缩方式：UTF-8。

就像原文件 --> 压缩文件的关系

给定unicode字符 --> uft-8 的二进制值.
utf-8的二进制值 --> unicode字符。解码与编码问题.

utf8占用几个字节呢?
不可能定长，压缩规则才有效果。 1-6个字节.

GBK中文经常在java中，被转为utf-8，如何转?
GBK和 unicode有对应关系

GBK->unicode->utf-8

乱码如何形成的?

1. 解码时，与实际编码不一致，可修复
   。
2. 传输过程中，编码不一致，导致字节丢失不可修复
   。

MySQL字符集参数

set names作用

客户端的字符集，设为A GBK
数据库的字符集设为B UTF8(建表的时候设置的编码)

客户端收集到是什么编码 和 服务器想存成什么编码.

连接器的特性：链接客户端与服务器
客户端的字符先发给连接器，连接器选择一种编码将其转换，临时存储。
再次转换成，服务器需要的编码

clientGBK --> 转connUTF8, connUTF8-->不转换，服务器UTF8
clientGBK --->GBK不转给连接器， connGBK ， 转connUTF8 --> 服务器UTF8

设置阶段编码

要想不乱码，需要指定客户端的编码，让连接器不理解错误。这样就不会存入错误数据。往回去的时候，还要告诉连接器，如果从服务器返回数据，应该转成什么格式。

一共3个参数:

• 客户端发送的编码
• 连接器使用的编码
• 获取的返回数据的编码

需要明确告知服务器，客户端编码:

set character_set_client = gbk;

告诉连接器使用utf8

set character_set_connection = utf8;

告诉服务器返回结果使用GBK

set character_set_reslutes=gbk;

如果服务器设置严格，不允许插入。不严格，插入会造成数据丢失。

收到客户端编码 和 往服务器送， 两个过程.

gb2312 < gbk < utf8

sever >= conntion >= client

如果3者都是 GBK，可以简写。简写成 set names gbk

牵涉到数据库，不乱码

1. 正确指定客户端的编码
2. 合理选择连接器的编码
3. 正确指定返回内容编码
4. 网页文件本身编码
5. mate 信息编码.

utf8的BOM问题

一个中文在UTF8下是三个字节

记事本在打开的时候，分辨不出来要使用什么编码解析。
分析编码的特点，推测出来使用什么编码。

如果字节比较少，容易推测错误。

在utf8的BOM，是3个字节一个中文。

utf8文件的时候，前面多了三个字节：EF BB BF。
这3个字节不用来显示，是用来辨识utf8。是BOM信息。

在session cookie 使用前，不能有任何输出，空行空格都不行。
在utf8有BOM头的信息下，多个三个字节。会在session 或 cookie 会出现错误。

utf8和utf-8什么区别?
utf-8是一种编码，世界上utf-8只有一个。

表达这个编码方式. UTF-8 utf-8 utf8 UTF8。都是指选用utf-8这种方式。名称不一样，别名。推荐UTF 大写。

中文截取无乱码

<?php

/**
 * utf8截取无乱码
 * 
 * 思路：
 * 看到如下字节
 * 42 DC 34 af aa
 * 
 * 想截取无乱码，那就说明，从 42开始截几个字符，作为一个字符。
 * 
 * 从第一个字节开始，需要知道截取几个字符，比如截取1个字节，截取取出42
 * 
 * 再从DC截取，需要知道，从DC往后是几个字节组成一个字符。
 * ...
 * 
 * 类推，这样，截取出来的字节才能保证，正是是一个个的字符。
 * 关键如何判断utf8的字符的字节数。
 * 有几个 1
 * 
 * 最高字节 
 * 0xxx xxxx 1个字节
 * 110x    xxxx 2个字节
 * 1110 xxxx 3个字节
 * 1111 0xxx 4个字节
 * ... 
 * 111111100       
 */
 
 $str = '中文英E文中Z国M,M美国';
 
/**
 * $str 是带截取的字符串
 * $len 是截取的字符数
 */ 
function utf8sub ( $str, $len ) {
    
    // 处理空字符
    if ( $len <= 0 ) {
        return '';
    }

    $length = strlen($str); // 待截取的字符串字节数    
    
    // 先字符串的第一个字节, substr(); 是按照字节来截取  ord();转为 ansiic码
    
    $offset = 0; // 截取高位字符的偏移量
    $chars = 0; // 截取到的字符数
    $reslut = ''; // 截取到的字符串
    
    while ( $chars < $str && $offset < $length ) {
        
        $high = decbin(ord(substr($str, $offsetc, 1))); // 11100100 判断高位字节
        
        if ( strlen($high) < 8 ) {
            
            // 截取一个字节
            $count = 1;
            
        } else if ( substr($high, 0, 3) == '110' ) {
            
            // 截取两个字节
            $count = 2;
            
        } else if ( substr($high, 0, 4) == '1110' ) {
            
            // 截取三个字节
            $count = 3;
            
        } else if ( substr($high, 0, 5) == '11110' ) {
            
            // 截取四个字节
            $count = 4;
            
        } else if ( substr($high, 0, 6) == '111110' ) {
            
            // 截取五个字节
            $count = 5;
            
        } else if ( substr($high, 0, 7) == '1111110' ) {
        
            // 截取六个字节
            $count = 6;    
                    
        }
        
        $reslut .= substr($str, $offset, $count);
            
        $chars += 1;
        $offset += $count;
        
    }
    
    return $res;
    
} 

echo utf8sub($str, 5);

// 使用字符串来判断，效果不高.

?>

存储引擎与事务简单介绍

mysql引擎

例子：
一份歌词，保存方式。
可以怎么保存。

1. 记载脑子里，
2. 用铅笔写纸上
3. 用钢笔写
4. 刻石头上

美国科学家，收集了人类的音乐，符号。(二泉映月)

不变的是数据，变化的是"存储的格式"

如果说有：
sanme,age
三 22
四 23

这个信息，无论用什么engine来存储，都一样。
但是，不同engine，都有存储信息功能，则必须不一样的地方。

总结：engine 引擎，就是mysql存储数据的不同方式。

数据库对同样的数据，有着不同的存储方式和管理方式，在mysql中，称为存储引擎。

事务

# 建立2张一样结构的表，但是引擎不一样
create table a1 ( uname varchar(20), money int ) engine myisam charset utf8;
create table a2 ( uname varchar(29), money int ) engine innodb charset utf8;

insert into a1 values ('zhangsan', 3000), ('lisi', 2000);
insert into a2 values ('zhangsan', 3000), ('lisi', 2000);

# 在存储数据上是一样的。

银行转账

张三给李四转500元

张三 -500
李四 +500

这2步，必须都完成了，转账才完成。

想这种，2步或n步，从逻辑上来讲，是一个“原子操作”
即，要么成功，要么都不成功。

如何保障这种特性。
使用事务. 在这种环境下诞生的一个概念，操作。

事务特性：原子性，一致性，隔离性，持久性。

原子性就是：2步或N步操作，逻辑上不可分割。
通俗说，要么成功，要么都不成功。

# 如何使用事务?
# strat transaction; 开启事务。
# ... 中间事务操作 
# commit; 提交事务

# 原子性
strat transaction;

update a1 set money = money + 1000 where uname = 'zhangsan';
update a2 set moneys = money - 1000 where uname = 'lisi'; # 失败, 故意表名打错，模拟网络故障等失败场景。

# 整体的转账操作，从逻辑上讲，应该失败，即zhangsan的钱，不能多500
# 部分失败，则之前的成功操作，如何处理。
# 回滚 
rollback;

# 一致性
# 是指操作前后，值的变化，逻辑上成立。

# 隔离性
事务结束前，每一步的操作带来的影响，别的会话看不见。

# 持久性
事务一旦完成，无法撤销

电子商城，在线支付.

支付成功，生成一条支付流水。
同时，把订单改为已支付。(事务，一致性)

数据库的使用者：
增删改查，左右连接，子查询。

数据库的设计者：
建库/建表的数据库设计。

引擎优化，索引优化，表的范式设计，主从服务器

数据库的管理者
DBA
权限管理，数据备份，运行监控，性能检测。