mysql之select(二)

   union

   联合

   作用: 把2次或多次查询结果合并起来。

   要求:两次查询的列数一致。推荐:查询的每一列，相对应得列类型也一样。

   可以来自于多张表。多次sql语句取出的列名可以不一致，此时，以第1个sql的列名为准。

   如果不同的语句中取出的行，有完全相同的（每个列的值都相同），那么相同的行将会合并（去重复）。

   如果不去重复，可以加all来指定→union all

   如果子句中有order by、limit，那么子句须用（）包起来，推荐放到所有子句之后，即对最终合并后的结果来排序。在子句中，order by配合limit使用才有意义，如果order by 不配合limit使用，会被语法分析器优化分析时，去除，将不起作用。

   例：想把>5000元的或者<20元的商品查询出来。

   ①

select goods_id,goods_name,shop_price from goods
where shop_price < 20 or shop_price > 5000;

   ②

select goods_id,goods_name,shop_price from goods
where shop_price > 5000
union
select goods_id,goods_name,shop_price from goods
where shop_price < 20;

   例：想取第4个栏目的商品，价格降序排列，还想取第5个栏目的商品，价格也降序排列，用union完成。

   错误的语句：

(select goods_id,cat_id,goods_name,shop_price from goods
where cat_id = 4
order by shop_price desc)
union(goods_id不可能重复)
(select goods_id,cat_id,goods_name,shop_price from goods
where cat_id = 5
order by shop_price desc);

   正确的语句：

(select goods_id,cat_id,goods_name,shop_price from goods
where cat_id = 4
order by shop_price desc)
union(goods_id不可能重复，所以不用all)
(select goods_id,cat_id,goods_name,shop_price from goods
where cat_id = 5
order by shop_price desc) 
order by shop_price desc;

   例：取第3个栏目价格前3高的商品和第4个栏目价格前2高的商品，用union来实现。

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

   练习：

   表ta如下：

id	num
a	5
b	10
c	15
d	10

   表tb如下：

id	num
b	5
c	10
d	20
e	99

   想得到如图所示的结果：

id	num
a	5
b	15
c	25
d	30
e	99

   解：

create table ta (
    id char(1),
    num int
);

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

create table tb (
    id char(1),
    num int
);

insert into tb
values
('b',5),
('c',10),
('d',20),
('e',99);

   ①union合并

select * from ta
union 
select * from tb;

   ②sum,group by求和

select id,sum(num) from 
(select * from ta
union 
select * from tb) as tmp
group by id;

   那如果用以下语句修改之后，又该怎么做呢？

update tb set num = 15 where id = 'c';

   ↓

   ①

select * from ta
union all
select * from tb;

   ②

select id,sum(num) from 
(select * from ta
union all
select * from tb) as tmp
group by id;

   

   集合(Set)

   集合的特性：无序性、唯一性。

   一张表就是一个集合，一行数据是集合的一个元素。

   理论上讲，不可能存在完全相同的两个行，但是表中可以有完全相同的两行，因为表内部有一个rowid。

   集合相乘，即笛卡尔积，其实就是两个几个的完全组合。

   设集合A有M个元素，M个元素各不相同；设集合B有N个元素，N个元素各不相同。那么A*B得到的积，有M*N个元素，而且每个元素不可能重复。

   所以：表A有9行，表B有10行。两表相乘→9*10→A*B表有90行。

   

   左连接/右连接/内连接

   左连接：left

select 列1,列2,列N from
tableA left join tableB
on tableA 列 = tableB     [此处表连接成一张大表，完全当成普通表看]
where group,having...照常写

   右连接：right

select 列1,列2,列N from
tableA right join tableB
on tableA 列 = tableB     [此处表连接成一张大表，完全当成普通表看]
where group,having...照常写

   内连接：inner

select 列1,列2,列N from
tableA inner join tableB
on tableA 列 = tableB     [此处表连接成一张大表，完全当成普通表看]
where group,having...照常写

   左/右链接：

   以左表为准，去右表找匹配数据，找不到匹配，用null补齐。

   如何记忆：

1. 左右连接可以互相转化。
2. 可以把右连接转化为左连接来使用（并推荐使用左连接代替右连接，兼容性好一些）。

   A站在B的左边   →   B站在A的右边

   A left join B    →   B right join A

   内连接：查询左右表都有的数据，即不要左/右连接中NULL的那一部分，内连接是左/右连接的交集。

   思考：能否查出左右连接的并集呢？

   答：目前不能，目前的mysql不支持外连接：Outer join。但可以用union来达到目的。

   练习1、goods表，category表，brand表，3表连接查询，得出如下字段：

goods_id	cat_id	cat_name	brand_id	brand_name	goods_name

   解：

   1、先取商品表与栏目表的关联信息   

select goods_id,goods.cat_id,cat_name,goods.brand_id,brand_name,goods_name
from
goods left join category
on goods.cat_id = category.id; [此处表连接成一张大表，完全当成普通表看]

   2、将上面的表再与品牌表关联

select goods_id,goods.cat_id,cat_name,goods.brand_id,brand_name,goods_name
from
(goods left join category
on goods.cat_id = category.id)
left join brand
on goods.brand_id = brand.id;

   练习2、见下图：

   解：

create table m (
    mid int primary key auto_increment,
    hid int,
    gid int,
    mres varchar(10),
    matime date
);

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

create table t (
    tid int,
    tname varchar(10)
);

insert into t
values
(1,'国安'),
(2,'申花'),
(3,'传智联队');

   ①先把顺序搞对

select hid,mres,gid,matime from m;

   ②先把主队的队名打印出来.....

select hid,t1.tname as hname,mres,gid,t2.tname as gname,matime 
from 
m left join t as t1
on m.hid = t1.tid
left join t as t2
on m.gid = t2.tid
where matime between '2006-06-01' and '2006-07-01';

   

   列的增删改：

   回顾建表语句：

create table 表名 (
    列名称 列类型 [列属性][默认值],   →列声明
    列名称 列类型 [列属性][默认值],
    列名称 列类型 [列属性][默认值]
) charset = utf8/gbk……

   增加列：

alter table 表名 add 列声明

   增加的列默认是在表的最后的一列。

   可以用after来声明新增的列再哪一些后面

alter table 表名 add 列声明 after 列名.

   如果新增列放在最前面，怎么办？

alter table 表名 add 列声明 first

   修改列：

alter table 表名 change 被改变的列名 列声明

   删除列：

alter table 表名 drop 列名;

   视图(view)

   以下例引出视图：

   例，查询每个栏目最贵的商品

select goods_id,goods_name,shop_price from goods order by cat_id asc,shop_price desc;

   查询结果，当成一张表看。

   如果某个查询结果出现的非常频繁，也就是，拿这个结果当做进行子查询时出现的非常频繁，那么

create table g2 like goods;
insert into g2 select…………

   上面两句，是想保存一个查询结果到表里面，供其他查询用。

   1、视图定义：

   视图是由查询结果形成的一张虚拟表。

   2、视图的创建语法：

create view 视图名 as select 语句

   3、视图的删除语法：

drop view 视图名

   4、视图的修改：

alter view as select xxxxxxx

   为什么要使用视图？

   1、可以简化查询。

   例，平均价格前3高的栏目。

create view lmj as 
select cat_id,avg(shop_price) as pj from goods
group by cat_id;

select * from lmj order by pj desc limit 3;

   2、可以进行权限控制。把表的权限封闭，但是开发相应的视图权限，视图里只开放部分数据。

   3、大数据分表时可以用到。

   比如表的行数超过200万行时，就会变慢。可以把一张表的数据拆成4张表来存放。

   例如：新闻表→news表→拆分成news1,news2,news3,news4表。

   把一张表的数据分散到4张表里，分散的方法很多，最常用可以用id，取模来计算。id%4+1 = [1,2,3,4]。比如 $_GET[‘id’]=17,17%4+1 = 2,$tableName = ‘news’.’2’。

select * from news2 where id = 17;

   还可以用视图，把4张表形成一张视图。

create view news as select from n1 union select from n2 union……

   视图与表的关系：

   视图是表的查询结果，自然表的数据改变了，就会影响视图的结果。

   视图改变了呢？

1. 视图增删改也会影响表。

2. 但是，视图并不总是能增删改的

   视图的数据与表的数据一一对应时，才可以修改。

   对于视图insert还应注意，视图必须包含表中没有默认值的列。

   视图的algorithm

   algorihm = merge/temptable/undefined

   merge:当引用视图时，引用视图的语句与定义视图的语句合并。

   temptable:当引用视图时，根据视图的创建语句建立一个临时表。

   undefined:未定义，自动，让系统帮你选。

   merge(合并)，意味着视图只是一个规则，语句规则，当查询视图时，把查询视图的语句（比如where那些）与创建时的语句where子句等合并、分析，形成一条select语句。

   例1，创建视图语句：

create view gui as select goods_id,cat_id,goods_name,shop_price 
from goods where shop_price > 1000;

   查询视图的语句：

select * from gui where shop_price < 3000

   分析：

show create view gui;
#创建视图时where shop_price > 1000;
#查询视图时where shop_price < 3000;
#那么查此视图时，真正发生的是where(select where) and (view where)
#分析出最终语句还是去查goods表，where shop_price < 3000 and shop_price > 1000;

   例2，查询每个栏目下最贵的商品。

   创建视图语句：

create view g2 as 
select goods_id,cat_id,goods_name,shop_price from goods
order by cat_id asc,shop_price desc;

   查询视图的语句：

select * from g2 group by cat_id;

   merge(合并)在一块，查询的仍然是源表，最终执行语句：

select goods_id,cat_id,goods_name,shop_price from goods
group by cat_id
order by cat_id asc,shop_price desc;

   而temptable是根据创建语句瞬间创建一张临时表，然后查询视图的语句从该临时表查询数据。

   例，创建视图语句：

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

   查询视图的语句：

select * from g2 group by cat_id;

   最终执行的是2句话，先从源表中取数据并放在临时表，然后去查临时表。

   字符集与校对集

create table 表名（
列声明
）charset utf8;

   字符集:

   mysql的字符集设置非常灵活，可以设置服务器默认字符集，数据库默认字符集，表默认字符集，列字符集。如果某一个级别没有指定字符集，则继承上一级。

   以表声明utf8为例，存储的数据在表中，最终是utf8。

   图示原理：

  

1. 告诉服务器，我给你发送的数据是什么编码的？ set character_set_client=gbk/utf8
2. 告诉转换器，转换成什么编码？ set character_set_connection=gbk/utf8
3. 查询的结果用什么编码？ set character_set_results=gbk/utf8 

   如果以上3者都为字符集N，则可以简写为set names N；

   推论：什么时将会乱码?

1. client声明与事实不符(???)

2. results与客户端页面不符的时候

   什么时间将丢失数据？

   connection(转换器)和服务器的字符集比client(客户端)小时。

   

   校对集

   指字符集的排序规则。

   一种字符集可以有一个或多个排序规则。

   以utf8为例，我们默认使用的utf8_general_ci规则，也可以按二进制来排，utf8_bin。

   怎样声明校对集？

create table()……charset utf8 collate utf8_general_ci;

   注意:声明校对集必须是字符集合法的校对集。

   查看校对集

show collation;

   查看字符集

show character set;

   查看utf8有哪些校对集?

show collation like ’utf8%’;