mysql中的多表连接（左连接、右连接、内连接等）&函数

MySQL中的多表连接

现有表R,S如下：

 

笛卡尔积

select * from R,S;

 

结果：

注：不需要任何条件。结果为两张表函数相乘（3x3=9）。

 

 

连接类型

分为三种：交叉连接、内连接、外连接。

 

交叉连接 cross join

没有where子句的交叉连接将产生连接表的笛卡尔积。

 

select * from R cross join S;

 

结果：和笛卡尔积一样，如上图。

 

select * from R cross join S where R.C = S.C;

 

结果：

内连接

内连接分为三种：自然连接、等值连接、非等值连接。

 

自然连接 natural join：在连接条件中使用等于=运算符比较被连接列的列值，但删除连接表中重复列。

 

select * from R natural join S;

 

结果：

 等值连接  ：使用等于=比较连接列的列值，在查询结果中列出接连表中的所有列，包括其中的重复列。

 

select * from R  join S where R.C = S.C;

或

select * from R inner join S where R.C = S.C;

 

非等值连接 ：在连接条件中，可以使用其他比较运算符，比较被连接列的列值，如：<、>、!=等。

 

 

外连接

外连接分为三类：全外连接、左外连接、右外连接。

 

左外连接 left join / left outer join

 

select * from R left join S on R.C = S.C;

 

 

左外连接要遍历左表的所有记录，右表没有的用null表示。

 

右外连接 right join / right outer join

 

select * from R right join S on R.C = S.C;

 

 

右外连接要遍历右表所有的记录，左表没有的用null表示。

 

全外连接 full join / full outer join

 

select * from R full join S on R.C = S.C;

 

函数

函数可以分为：单行函数 和 多行函数。

 

单行函数

所谓的单行函数就是将每条数据进行独立的计算，然后每条数据得到一条结果。

 

1.流程控制函数

case

 

Sql代码 

select name, age,  
 (  
    case sex   
    when 1 then "男"  
    when 0 then "女"  
    else "火星人"  
    end  
)  
from user;

if

Sql代码 

select id, name, course, if(socre<60, "fail", "pass")  
from student;  

2. null处理函数

ifnull

select ifnull(birthday, "is null birthady") from user;

如果birthday为null，就返回后面的字符串。

nullif

select nullif(age, 245) from user;

如果age等于245就返回null，不等于就返回age。

isnull

select isnull(birthday) from user;

判断birthday是否为Null.

select if(isnull(birthday), "birthday is null", "birthday is not null") from user;

coalsece

返回第一个非null的值

select coalesce(null, 1);

3.加密函数

md5(str)

password(str):单向，不可逆。

4.数学函数

abs(x):绝对值

greatest 返回最大值

select greatest(2,3);

least 返回最小值

select least(2,3);

round(x) round(x,d) 四舍五入

select round(1.58); #2

select round(1.298, 1); #1.3

select round(23.298, -1); #20

fromat(x,d)

将数字x的格式写为“#,###,###.###”,以四舍五入有方式保留小数点后d位。

select format(12332.123456,4);   #结果：12,332.1234

5.日期时间函数

获得当前系统日期

select curdate();

获得当前系统时间

select curtime();

增加日期或时间

select date_add('2010-06-21', interval 2 month);

interval是一个关键字，2 month 2个月，2是数值，month是单位

select addDate('2011-2-24', 2);

在前面的日期上加上后面的天数。

select date_add('1999-01-01', interval 1 day);   #增加一天

select date_add('1999-01-01', interval 1 hour);  #增加一小时

格式化日期

date_format(date,fmt)

select date_format(birthday, '%Y-%m-%d %H:%i:%s') from user;

将字符串转换为日期

str_to_date(str,fmt)

select str_to_date('04/31/2004', '%m/%d/%Y');

6.字符串函数

char_length(str) 返回字符串str的长度，单位为字符。

length(str) 返回字符串的长度，单位为字节。

upper(str) 转换为大写字母。

lower(str) 转换为小写字母。

left(str, len) 返回从字符串开始的len最左字符。

right(str, len) 返回从字符串开始的len最右字符。

char() 将整数转换为字符。

concat(str1, str2,...) 连接字符串。

select concat('My','S','QL'); #结果：MySQL

replace(str, from_str, to_str) 替换字符串

select replace('www.mysql.com', 'w', 'Ww'); #结果：WwWwWw.mysql

reverse(str) 反转字符串

instr(str, substr) 返回字符串str中子字符串的第一个出现位置。

select instr('Hello World', 'or'); #结果：8

trim( [{both | leading | trailing}[remstr] from]  str) 去除前缀后缀

select trim('    bar   '); #结果：'bar'

select trim(leading 'x' from 'xxxbarxxx'); 结果：'barxxx'

select trim(both 'x' from 'xxxbarxxx'); 结果：'bar'

select trim(trailing 'xyz' from 'barxxyz'); 结果：'barx'

lpad 左填充

rpad右填充

select rpad('Smith', 10, '*'); #结果：'Smith**********'

substring(str,pos), substring(str,pos,len) 子字符串

select substring('Sakila',2); #结果：'akila'

select substring('Sakila',-3); #结果：'ila'

select substring('Sakila',-3,2); #结果：'il'

7.类型转换函数

cast(val as type)   /         convert(str type)

将传入的参数值转为另一种数据类型

类型：binary[(N)], char[(N)],    date, datetime,     decimal,    signed,time,    unsigned [integer]

select cast('2000-01-01 02:10:30' as date);

select cast('24.36' as signed); #转换为整型

select cast('100' as decimal); #转换为浮点型

convert(str using char_set)

将字符串转换编码

select convert('ab中国' using utf8)

多行函数

就是多条记录同时计算，得到最终一条结果记录。也成为聚集函数、分组函数，主要用于完成一些统计功能。

avg平均值

select avg(age) from user;

select avg(distinct age) from user;

count记录条数统计

select count(*), count(age), count(distinct age) from user;

max最大值

select max(age), max(distinct age) from user;

min最小值

select min(age), min(distinct age) from user;

sum求和

select sum(age), sum(distinct age) from user;

select sum( ifnull(age, 0) ) from user;

group by子句

1.出现在select列表中的字段 或者 出现在order by后面的字段，如果不是包含在分组分数中，那么该字段必须同时在group by子句中出现。

2.包含在group by子句中的字段则不必须出现在select列表中。

3.如果没有group by子句，select列表中不允许出现字段（单行函数）与分组函数混用的情况。

4.不允许在where子句中使用分组函数，having中才能用。

 

这是我转载的下面这位大佬的，但是在现在的开发中可能会因为性能、部署等问题函数是要求尽量不使用的。

至于为什么会影响性能这个我以后会补充一篇关于mysql执行计划的文章，看一下函数、索引、多表连接查询等问题的分析。

至于为什么会和部署有关这个问题具体原因可以自己查一下，或者等下回分解。

 转载自：http://huaxia524151.iteye.com/blog/1423614