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 colBwhere ...

右连接典型的语法

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

内连接典型的语法

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

1. 左右连接中以左表为准，向右表去匹配数据，如果没有找到匹配的，用null补齐
2. 左右连接可以相互转化，可以把右连接转化为左连接来使用。推荐使用左连接，兼容性好一点

3. 可以如下理解：

   1. A表在B表的左边：tableA left join tableB
   2. B表在A表的右边：tableB right join tableA

4. 内连接：表示左右表中都有的数据，即不要左右表中含有null的那一部分。可以这样子理解：内连接就是左右连接的交集

5. 注意一点：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%';