mysql数据库关联查询与视图

mysql数据库关联查询与视图

1、union：合并查询结果

要求：两次查询的列数一致；推荐：查询的每一列，对应的列的类型一致
认识：

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;

注意：

1. 多次sql语句取出的列名可能不一致，此时，以第1个sql的列名为准

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

3. 重复

   select id, sum(num) from (select *from ta union select *from tb) as tmp group by id;//去重复：
   select id, sum(num) from (select *from ta union all select *from tb) as tmp group by id;//不去重复,加all：

4. 如果子句中有order by、limit，需加小括号，推荐放到子句之后，即对最终合并的最后结果进行排序；

5. order by和limit组合使用后才有意义，如果order by不配合limit使用，会被语法分析器优化分析时去除，即单独使用order by无效

2、集合相乘，为笛卡尔积，其实就是两集合的完全组合

select from ta,tb;即tatb的结果
连接查询：左连接、右连接、内连接
条件联合查询
1：连接表
2：连接条件 ；

左连接:left

select col1 col2,…coln from

tableA left join tableB	//连接哪几个表
on tableA 列=tableB 列	//连接条件

//此时形成大表，完全当成普通表来看，后可跟
where，group，having…一样

右连接:right

select col1 col2,…coln from

tableA right join tableB	//连接哪几个表

on tableA 列=tableB 列 //连接条件
//此时形成大表，完全当成普通表来看，后可跟
where，group，having…一样

内连接：inner

select col1 col2,…coln from

tableA inner join tableB	//连接哪几个表

on tableA 列=tableB 列 //连接条件
//此时形成大表，完全当成普通表来看，后可跟
where，group，having…一样

左右连接：A left join B;和B right join A;一样
左右连接可以相互转化（推荐使用左连接代替右连接，兼容性好一点）

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

左右连接的并集，目前不支持mysql不支持外链接，但可以用Union达到目的

3、列增加

alter table 表名 add 列声明；增加的列默认在最后一列
可以用after来声明新增的列在哪一些后面
alter table 表名 add 列声明 after 列名；
新增放在第一列：alter table 表名 add 列声明 first;

4、修改列

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

5、删除列

alter table 表名 drop 列名;

6、视图：view

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

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

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

视图定义：是由查询结果行程的一张虚拟表。
视图创建：create view 视图名 as select语句;
视图删除：drop view 试图名;
为什么要视图？

1. 可以简化查询
2. 可以进行一些权限控制，把表的权限封闭，但开放相应的视图权限，视图里只开放部分数据
3. 大数据分表时可以用到 比如，标的行数超过200万行时，就会变慢，可以吧一张表的数据拆成4张表来存放，即分散4张表。 分散方法：最常用的可以用id取模来计算 例：$_GET[‘id’]=17, 17%4+1=2,
   $tablename=‘news’.‘2’ select *from news2 where if=17;
   还可以用视图，把4张表形成一张视图 create view news as select from n1 union select
   from n2 union …

视图修改： alter view as select …

7、视图与表的关系：

视图是表的查询结果，则表的数据改变了，影响视图的结果，即视图也跟着变；
视图的数据改变了？
0：视图增删改也会影响表
1：但是，视图并不是总是能增删改的
2：视图的数据与表的数据一一对应时，可以修改（对于视图insert还应注意：视图必须包含表中没有默认值的列，即：没有默认值的列必须包含进视图）

8、视图的algorithm

algorithm=merge/temptable/undefined
merge:当引用视图是，引用视图的语句与定义视图的语句合并
temptable：当引用视图时，根据视图的创建语句建立一个临时表
undefined：未定义，自动，让系统帮你选

merge，意味着视图只是一个规则，语句规则，当查询视图时，把查询视图的语句（比如where那些）与创建时的语句where子句等合并分析，形成一条select语句，执行的是融合的两个语句，查询的原表（原表）；而temptable是根据创建语句瞬间创建一张临时表，然后查询视图的语句从该临时表查数据，最终执行2句话，取数据并放在临时表，然后再去查询临时表（临时表）