MySQL自学笔记5--表联合、表连接、视图、字符集、校对集

最新推荐文章于 2023-03-27 06:00:00 发布

justHT

最新推荐文章于 2023-03-27 06:00:00 发布

阅读量259

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/qhxjtu/article/details/54726781

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

MySQL自学笔记

使用MySQL 5.5以及MySQL自带命令客户端

表联合

union：联合，其作用是将2次或者多次查询结果合并起来
* 要求：两次的查询的列数一致。推荐是查询的每一列相对应的列类型也是一样的
* 查询可以来自多张表，但是如果多次SQL语句取出来的列名如果不一样的话，此时以第一个SQL的列名为准
* 不同SQL语句中取出来的行如果完全相同的话（即该行的每一个列的值都一样），此时将相同的行合并（即去重复）。如果不想要合并的话，可以使用关键字all来指定，即使用union all
* 如果句子中有用到order by、limit，那就应该将不同SQL语句放在括号( )里面。推荐将order by、limit放到左右子句之后，这样子对最终的合并结果进行排序。
*在子句中 order by配合limit使用才有意义！

集合乘积

集合的乘积是笛卡尔积，就是两个集合的完全组合。
例1：A集合有M个不同的元素，B集合有N个不同的元素。那么A集合与B集合的乘积，即A*B，有M*N个元素。
例2：表A有3行，表B有6行，则表相乘有 3*6 = 18 行

左连接、右连接、内连接

连接 join

左连接典型的语法：

select col1, col2, col3,... from 
table1 left join table2 on table1 colA = table2 colB
where ...

右连接典型的语法

select col1, col2, col3,... from 
table1 right join table2 on table1 colA = table2 colB
where ...

内连接典型的语法

select col1, col2, col3,... from 
table1 inner join table2 on table1 colA = table2 colB
where ...

左右连接中以左表为准，向右表去匹配数据，如果没有找到匹配的，用null补齐
左右连接可以相互转化，可以把右连接转化为左连接来使用。推荐使用左连接，兼容性好一点
可以如下理解：
1. A表在B表的左边：tableA left join tableB
2. B表在A表的右边：tableB right join tableA
内连接：表示左右表中都有的数据，即不要左右表中含有null的那一部分。可以这样子理解：内连接就是左右连接的交集
注意一点：MySQL目前没有外连接这一说法，也不能直接求得左右连接的并集。可以使用union来实现

视图

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

为什么要定义视图

当一个查询结果出现非常频繁时，并且拿该查询结果进行子查询出现的非常频繁，此时可以将一个查询结果保存为一张虚拟的表，来供其他查询用。
1. 可以简化查询
2. 可以进行权限的控制。比如说将表的权限封闭，但是开发相应的视图权限，视图中只开放部分的数据
3. 大数据分表。比如当一张表的行数超过100万行，就会变慢，此时一种有效的方法就是将一张表拆开为多张表来保存。例如分为t1、t2、t3、t4，然后
MySQL create view tableNew as select from t1 union select from t2 union select from t3 union select from t4;
* 分表的操作比较多，通常可以使用取模来进行！

视图的一些基础操作

创建：create view viewName as select语句
删除：drop view
修改：alter view as select语句

视图和表的关系

视图是表的查询结果，当表的数据变化时，会影响视图的结果
视图改变也会影响表的数据，但是视图不总是能够修改的。
- 视图中数据和表中数据一一对应时，而且还有注意如果是将数据插入（insert操作）视图中，该数据必须包含表中没有默认值的列的数据，这样子才可以

algorithm

视图有三种algorithm：merge、temptable、undefined
* merge：当引用视图时，引用视图的语句与定义视图的语句合并。即意味着视图在这里只是一个语句规则，当查询视图时，把查询视图的语句和创建时的语句等合并、分析，形成一个SQL语句。
* 比如：
MySQL create altorethm = merge view g1 as select goods_id, cat_id, goods_name, shop_price from goods order by cat_id asc, shop_price desc;
MySQL select * from g1 group by cat_id;
* 最终执行的语句：
MySQL select goods_id, cat_id, goods_name, shop_price from goods group by cat_id order by cat_id asc, shop_price desc;
* temptable：根据创建语句瞬间创建一张临时表，然后查询视图的语句从该临时表中进行查询
* 如上面merge中的例子，,将altorethm = merge改为altorethm = temptable最终执行时就是瞬间创建一张名为g1的临时表，然后从中进行查询

undefined：表示未定义，altorethm = undefined，表示自动让系统选择一种视图的algorithm

字符集

MySQL字符集设置灵活，可以设置：
1. 服务器默认字符集
2. 数据库默认字符集
3. 表默认字符集
4. 列默认字符集
* 如果某一级没有指定字符集，则继承上一级的字符集

例如声明表字符集为utf8：

create table tableName(
列声明
)charset utf8;

上述例子中，存储在表中的数据最终采用的字符集为utf8;

客户端、转换器、服务器三种间的编码情况：
- 告诉服务器，发送给服务器的数据编码：set character_set_client = gbk/utf8;
- 告诉转换器，转换为什么类型的编码：set character_set_connection = gbk/utf8;
- 查询结果采用的编码：set character_set_result = gbk/utf8;
- 如果三种都为一样的字符集N，则可以写为set names N;，set names gbk;
- 出现乱码情况：client声明与实事不符、results与客户端页面不相符
- 出现数据丢失情况：connection使用的字符集比服务器使用的字符集小时，会出现数据丢失

校对集

校对集指的是字符集的排序规律。一种字符集可以有一个或者多个排序规则。
以utf8为例，默认是使用utf8_general_ci规则进行，但是也可以声明为安装其他规则，比如：utf8_bin
* 例如声明校对集为utf8_bin：
MySQL create table tableName( 列声明 ) charset utf8 collate utf8_bin; # 注意，这里面声明的校对集必须是合法的校对集
* 查看校对集：show collection;
* 查看字符集：show character set;
* 查看utf8下有那些校对集：show collection like 'utf8%';