Mysql基础回顾(中篇)

2016-07-26 15:26:48 Mysql 1088 0

更新时间2016年7月26日23:26:48

阶段三

union

>把2次或者多次查询结果合成一张表,要求其列数要一致

可以来源于多张表,可以进行多次sql语句查询,取出的列名可以不一致,此时以第一个sql的列名为准

 

1)如果不同的语句中取出的行,有完全相同(每个列的值都相同),

那么相同的行将会合并(去重复)


SELECTS * FROM ta

UNION

SELECT * FROM tb;

2)如果不去掉重复项,可以通过加all来搞定

SELECTS * FROM ta

UNION ALL

SELECT * FROM tb;

3)如果语句中有oder by,limit(在子句中order by配合limit才有意义,否则语法分析器,会在分析语法的时候,把order by去掉)

则需要将每个查询子句都包起来(但平日多用在总的结果后 排序)

//取第三个栏目前三高、第四个栏目前两高的商品,用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);


join



1) 左连接【这里以tb为基准 [意指它每项得应完全对齐] :交集部分先拿出来。另外,若自己有没对齐的行,则把这些行的数据也拿出来。因为没对齐,所以这几行只有自身的数据,其他数据为NULL】

SELECT tb.level,tb.assignment,ta.name

FROM tb

LEFT JOIN ta

ON ta.level=tb.level

2) 右连接【这里以ta为基准,逻辑过程与左联接同理】

SELECT tb.level,tb.assignment,ta.name

FROM tb

RIGHT JOIN ta

ON ta.level=tb.level


3)内连接【查询左右表都有的数据,即左右链接的交集】

SELECT tb.level,tb.assignment,ta.name

FROM tb

INNER JOIN ta

ON ta.level=tb.level

//查出左右连接的并集,可以用UNION 


//查出下面2016-08-08之后的所有比赛,并以形如下面的方式输出:

                重庆力帆  0:0 上海申花 2016-08-09


SELECT a.matchResult,a.matchTime ,b.teamName as hName,c.teamName as gName

FROM `match` as a

LEFT JOIN `team` as b

On b.teamID=a.hostTeamID

LEFT JOIN `team` as c

On c.teamID=a.guestTeamID

WHERE a.matchTime>'2016-08-08'


更新时间2016年7月28日 10:27:44

view

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


视图的用处

>简化查询

>把表的权限封闭,但开放相应的视图权限,视图里只开放部分数据

>大数据分表可以用到.


视图的创建

create view 视图名 as select语句

视图的删除

drop view 视图名

视图的修改

Alter view as select xxx;

视图与表的关系

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

视图改变了呢?

1:视图增删改也会影响表的吗

    >视图的数据与表的数据 一一对应时,可以修改

2:视图总能增删改吗

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



大数据分表查询

当表超过200万行的时候,查询速度就会变慢,这时候可以通过分表查询的方法

    [示例] 现在我把表通过 模4 的方法,把表分成4张视图

CREATE view g1 as select * from goods where goods_id % 4=0;

CREATE view g2 as select * from goods where goods_id % 4=1;

CREATE view g3 as select * from goods where goods_id % 4=2;

CREATE view g4 as select * from goods where goods_id % 4=3


PHP查询为例

$tableFlag=$_GET['id']%4;

$tablename="g".$tableFlag;

$result=$pdo->("select * from $tablename");

此外,我们可以把多张表通过union合成一张新的视图

CREATE newTable as select * from t1 unionselect  * from t2 ....;


视图的Algorithm

algorithm = merge / Temptable / undefined

Merge当引用视图的时候,视图语句与定义视图的语句合并只是为了形成一条select语句

Temptable 当引用视图时,根据视图的创建语句建立一个临时表多用于做子查询的结果,出现非常频繁的时候

Undefined未定义,让系统帮你选

而temptable是根据创建语句瞬间创建一张临时表,

然后查询视图的语句从该临时表查数据

create algorithm=Temptable view g2 as 

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

查询语句为

select * from g2 group by cat_id; //最后执行的这个查询语句

然后取出结果,并放在临时表上

字符集与校对集

字符集

>数据库默认字符集
>表默认字符集
>列字符集
>如果某一个级别没有指定字符集,则继承上一级
>最上一级是服务器,它是一定有字符集定义的(因为要负责页面的输出),

1.告诉服务器,我给你发送的数据是什么编码 Character_set_client

         [示例]set character_set_client=utf8;

2.告诉转换器,转换成什么编码?Character_set_connection

         [示例]set character_set_connection=utf8;

3.查询的结果是什么编码?Character_set_results

         [示例]set character_set_results=utf8;

若三者统一编码,则可写为set names 字符集名

         [示例]set names utf8;

校对集

>值字符集的排序规则[一个字符集可以有多种排序规则,如图所示]

    >常用字符集为utf8_general_ci

声明校对规则

         [示例]create table(xxxxx) Charset utf8 collate utf8_general_ci;


注:若无特殊说明,文章均为云天河原创,请尊重作者劳动成果,转载前请一定要注明出处