mysql学习笔记3：表联结，union，mysql的函数，视图，中文的乱码问题

 

 

表联结

 两个表全相行，行的角度看，就是两个表的每一行的笛卡尔积。而从列的角度看，就是列的拼接。

全相乘的结果集，可以从中取出需要的部分列，这里的公共的列要使用全名：表名.列名 。

ambiguous：模糊不清的。

一个连接的例子：两个表相乘联查效率比较低

select goods_id,minigoods.cat_id,goods_name,category.cat_id,cat_name
from minigoods,category
where minigoods.cat_id=category.cat_id;

 

左连接:  效率较高

一张表在左，一张表在右。两张表通过一定的关系来筛选右表的行。

左表  left join  右表    on 条件；

查询出来的是一个结果集形式，可以把这个结果集当成一张表，可以再对这张虚拟表查询，能使用全部5种查询子句。

取出第4个栏目下的商品和商品分类名
select goods_id,goods_name,goods_num,shop_price,cat_name
from 
goods left join category
on goods.cat_id=category.cat_id

where goods.cat_id=4;

左连接，右连接，内连接三者的区别

左右连接可以呼唤。a left join b  效果，  和 b right join a 一样。没有本质的区别，二者可以互换。

考虑到兼容性，最好使用左连接。

内连接：是左右连接的交集。  只选出有匹配值的。

 表a inner join 表b

练习题

选出所有人，不管有没有匹配值，也就是左右连接的并集。也叫做外连接。可惜在MySQL不支持外连接。

左连接可以在外面嵌套。也就是先左连接，把结果当成表，再与另一个表第二次左连接。

 

union

合并两条或多余两条的语句的结果集。

语法：sql1 union sql2;

1.一张表的合并，

结果集的列对应相同的合并，直接补在下面。

查询价格高于4000或者小于30的商品列表，不用or完成。
selece goods_id,goods_name,shop_price  
from goods
where shop_price <30

union

selece goods_id,goods_name,shop_price  
from goods
where shop_price>4000;

2.两张表的合并

可以通过给列设置别名，达到两个结果集的列名对应一致。

如果没有使用as，列名不一样了，那么最终结果的列名以第一个结果集为准。

3.union使用范围和一些注意

两个结果集的列的数目相等就可以了。

就算列的类型不一样，也可以合并。

1.

使用union之后的最终结果集，  可以使用order by子句进行排序。这个order子句排序的是整个结果集。

内层和外层如果都进行了排序，内层会不起作用，内层的排序语句会被MySQL的代码分析器优化。

 

在内层，如果后面再有一个limit子句，那么内层的order子句不会被优化，会发挥作用。

 

2.如果两个结果集合并前有重复行，重复的行会被合并成一行，就是去重。如果都要保留，不去重，可以使用union all 来合并结果集。

 

4.练习题

表格a                                                                                                                           

id	num
a	5
b	10
c	15
d	10

表格b

id	num
b	5
c	15
d	20
e	99

查询的效果是

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

使用union连接完成

select temp.id,sum(num)
from
(select * from a
union all
select * from b）  as temp
group by temp.id;

MySQL的函数

数学函数

abs() 绝对值

bin() 把一个数写成二进制

floor()   第一个取整函数,[x]  应用场景：商品打88折并且抹去零头，就可以使用这个函数

ceiling() 第二个取整函数，向上取整数。[x]+1

rand() 随机数函数，范围在0和1之间。

字符串函数

函数

concat():可以拼接字符串，默认用逗号分隔

ascii() ：返回给定字符串的ascii码

length();  返回给定字符串的字节个数

 char_length():返回给定字符串的字符个数

bit_length():返回给定字符串的比特长度。

reverse():  反转给定的字符串，倒序排列。

position(‘子串’   in  ‘父串’）：返回子串在父串中的位置

right('大字符串',返回的长度)：从右边开始算返回大字符串的一部分

练习题：

数据表里的邮箱地址有多种，如@qq.com,@163.com,@126.com等，  计算每一类邮箱所占的比例

思路：先查询后缀，使用字符串的函数

select *,right(email,length(email)-position('@'in email))   from test14;

 

日期和时间函数

函数介绍

now();返回现在的日期时间，datetime格式的

curdate(): 返回当前的日期，是date格式的

curtime():  返回当前的时间，是time格式的

 

dayofweek(‘给定日期')： 返回给定的日期是星期几。注意：星期日则返回1，星期一的返回2……

week(给定日期)：返回给定日期是这一年的第几周。

练习题：

有一张加班时间表，按照每星期分别统计加班的总时间，就是每星期的加班总时间

select *,week(dt) as wk, sum(num) from jiaban group by wk;

  加密函数

良好的加密：不可逆，碰撞性低(也就是两个明文对应同一个密码)

md5():

流程控制函数

case 值          when 情况1  then   返回值1            when 情况2    then 返回值2           ……   else  默认值        end

 

if(表达式，条件真的时候的返回值，条件假时的返回值)。相当于java中的三元运算符。

ifnull('列名'，条件真的时候的返回值)

 

系统调试函数

user():   返回用户名和对应的主机

database():  返回当前正在使用的数据库名字

version():返回当前数据库服务器MySQL的版本号

 

注意：

表结构要尽量合理，尽量少用MySQL的函数，因为会影响查询速度。

如果对某列应用了函数，那么这一列的索引就不能用了。从而导致查询速度慢。

 

视图(view)

在查询中，会把结果集当成表来看。视图是一张虚拟的表，是原来的表运算后的投影。这样表的变化可以影响视图。视图是表的投影。

视图的创建

create view   视图名 as  select语句；

把查询的结果集定义为视图。视图就可以当成表来使用了

使用视图的好处

可以简化查询。情景：复杂的而且多次用到的查询，可以把中间结果集定义成视图

权限控制能够更精细。  情景：两网站合作，互相开放用户表的权限，但是不想展示密码字段，可以使用视图。

数据很多，分表时方便。情景：小说网站，有5张表，把5张表的查询union成一个视图，再查询这个视图。

 

表影响视图

表数据改变，视图数据会自动做相应改变。

而视图，有一些视图不能更新。如果视图数据和表的数据之间一一对应，可以修改视图的数据，并且会影响表的数据。

 

algorithm

是建表还是合并查询语句。在创建视图时使用   create algorithm=merge view 视图名   as  select语句

algorithm=merge:表示合并查询语句

algorithm=temptable:表示建立临时表

undefined：由系统来决定是建临时表还是合并查询语句