Mysql(二)函数与连接

一、函数

     1、if函数

      if(exp1, exp2, exp3)     判断exp1是否为true（不为0，并且不为nlll），如果为true，返回 exp2的值，否则返回exp3的值。

select if(5 > 3, 100, 200);
select if(5 < 3, 100, 200);
select if(true, 100, 200);

MySQL中，true与1是同义词，false与0是同义词。

select 1 = true, 0 = false;

ifnull(exp1, exp2)  如果exp1不为null，返回exp1的值，否则，返回exp2的值。

select ifnull(100, 200);
select ifnull(null, 200);

select ifnull(name, '无名氏') from student

#nullif(exp1, exp2)
#如果exp1=exp2，则返回null，否则返回exp1的值。

select nullif(5, 5);
select nullif(5, 6);

    2、case函数

#case的第一种形式，类似于Java中的switch-case
select age 年龄,
case age
when 10 then '小孩，10岁'
when 20 then '弱冠之年'
when 30 then '而立之年'
else '其他年龄'
end 年龄说明
from student;

 

#case的第二种形式，类似于Java中的if-else if
select
case
when age <= 10 then '小孩，10岁'
when age <= 20 then '弱冠之年'
when age <= 30 then '而立之年'
else '其他年龄'
end 年龄说明
from student;

#可以使用第二种case代替第一种case。
select
case
when age = 10 then '小孩，10岁'
when age = 20 then '弱冠之年'
when age = 30 then '而立之年'
else '其他年龄'
end 年龄说明
from student;

     3、#abs 返回绝对值

select abs(-2);

     4、ceil / ceiling 返回大于等于参数的最小整数。（向上取整）

select ceil(3.2), ceiling(3.2)

     5、floor 返回小于等于参数的最大整数。（向下取整）

select floor(3.5);

    6、mod 取余数

select mod(5, 2);

    7、pow / power 求指数

select pow(2, 5), power(2, 5);

rand 返回0-1随机小数，包括0，包括1。
select rand();

round 返回最接近的整数值。（四舍五入）
select round(5.5), round(5.2)

#round函数也可以指定一个参数，参数用来指定保留几位小数。
select round(2.222, 1), round(2.888, 2);

一个参数的round可以使用两个参数的round函数来表示（第   二个参数为0）。

select round(2.5), round(2.5, 0);
#round的第二个参数还可以是负值。
select round(1234.5678, -1), round(1256.789, -2);

sqrt 求平方根（开方）
select sqrt(3);

length 返回字符串的长度，以字节为单位
select length('abcdefg');

在utf8编码下，一个中文要占用3个字节。
select length('ab中文');

char_length 返回字符串的长度，以字符为单位。
select char_length('abcd');

select char_length('ab中文');

#concat 进行字符串的连接，返回连接之后的结果。
#concat函数是可变参数。
select concat('ab', 'cd');
select concat('ab', 'cd', 'e', 'fg');
#concat_ws 使用分隔符连连接字符串。第一个参数指定分隔符。
#concat_ws函数是可变参数。
select concat_ws('-', 'a', 'bc', 'def');
select concat_ws('多个字符', 'a', 'bc', 'def');
#insert(str, pos, len, newStr)
#str待插入的字符串 pos开始的位置， len长度 newStr插入的字符串
#返回str字符串从pos位置开始，len个长度的字符，使用newStr进行
#替换后的结果。
#MySQL中，索引从1开始。
select insert('abcdefg', 2, 3, '12345');
#instr(str, substr) 返回substr在str中首次出现的位置。
#如果没有出现，返回0。
select instr('abcdabcd', 'cd');
select instr('abcdabcd', 'ef');
#left(str, len) 返回str最左侧的len个字符
select left('12345678', 5);
#right(str, len) 返回str最右侧的len个字符
select right('12345678', 5);
#lower / lcase 返回字符串的小写形式
select lower('ABCde'), lcase('ABCde');
#upper / ucase 返回字符串的大写形式
select upper('abc'), ucase('abc');
#replace(str, from, to) 返回str中出现的from使用to
#进行替换后的结果。
select replace('abcdabcd', 'ab', 'xy');

#mid / substr / substring
#substr(str, pos) 截取子字符串，从str的pos开始，一直到字符串结束。
select mid('abcdefg', 3), substr('abcdefg', 3), substring('abcdefg', 3);
#substr(str, pos, len)
#第二个参数指定开始点，第三个参数指定截取的长度。
select substr('abcdefg', 3, 3);
#mid / substr / substring 另外一种表示方式
select substr('abcdefg', 3);
select substr('abcdefg' from 3);
select substr('abcdefg', 3, 3);
select substr('abcdefg' from 3 for 3);
#pos（开始点也可以去负值，表示从倒数的位置开始截取。
select substr('abcdefg', -3);
#ltrim 删除字符串左侧的空格
select ltrim(' abc');
#rtrim 删除字符串右侧的空格
select rtrim(' abc '), length(rtrim('abc '));
#trim 删除掉字符串两端的空格
select trim(' abc ');
#trim 可以指定删除掉字符串两点指定的字符
select trim('X' from 'XXXabcXXX')
#删除前端指定的字符
select trim(leading 'X' from 'XXXabcXXX');
#删除后端指定的字符
select trim(trailing 'X' from 'XXXabcXXX');
#删除两端指定的字符
select trim(both 'X' from 'XXXabcXXX');

     8、聚合函数

#avg 求平均值
select avg(age) from student
#count(字段) 返回该字段值非null的记录条数
select count(age) from student;
select age from student
#count(*) 返回记录条数
select count(*) from student;
#max最大值
select max(age) from student;
#min最小值
select min(age) from student;
#sum求和
select sum(age) from student;

#分组统计 group by
#当使用group by进行分组统计时，我们查询的字段要么使用聚合函数，
#要么出现在group by的分组统计中。
select sex, max(age), min(age) from student group by sex;

#错误
#select name, max(age) from student group by sex;

　　

#having
#错误，where是对记录进行过滤，不能对组进行过滤。
#因此，在where中不能使用聚合函数。
#如果需要对组进行过滤，使用having，having中可以使用聚合函数。

select sex, max(age), min(age) from student
group by sex having min(age) > 12;

#排序 order by
#asc 升序排列， desc降序排列，默认为升序排列。
select * from student order by age asc
select * from student order by age desc
#排序可以指定多个字段，当第一个字段相同时，会依次根据
#后续的字段进行排序。
select * from student order by age asc, id desc

#当where，group by， order by，limit同时出现时，
#顺序必须为：where -> group by -> order by -> limit

#错误
#select sex, max(age) from student order by id group by sex

　　

二、连接

     

create table stay (
	id int primary key,
    room varchar(10),
    stay_time date
)
insert into stay(id, room, stay_time)
values (1, '1001', '2016-03-05');
insert into stay(id, room, stay_time)
values (2, '1002', '2016-04-11');
insert into stay(id, room, stay_time)
values (3, '1003', '2016-05-02');

select stu.id id1, stu.name, sy.id id2, sy.room, sy.stay_time
from student stu, stay sy where stu.id = sy.id;

1、内连接

交叉连接([cross] join ) 笛卡尔积连接，可以使用on指定连接 条件。
内部连接([inner] join) MySQL中等同于交叉连接。
自然连接(natural join) 以表中的同名字段作为连接条件。不 能使用on。
说明：自然连接会以表中所有的同名字段作为连接条件。
如果想把指定的同名字段作为连接条件，可以在连接中使用 using子句。 

#MySQL中cross join与inner join是等价的。
#使用on来指定连接条件。
#交叉连接 cross可以省略

 

select stu.id id1, stu.name, sy.id id2, sy.room, sy.stay_time
from student stu cross join stay sy on stu.id = sy.id
#内部连接 inner可以省略
select stu.id id1, stu.name, sy.id id2, sy.room, sy.stay_time
from student stu inner join stay sy on stu.id = sy.id

 

自然连接 natural join
#自然连接是使用两张表中所有的同名字段进行等值连接。
select stu.id id1, stu.name, sy.id id2, sy.room, sy.stay_time
from student stu natural join stay sy
#using 指定等值连接的字段
select stu.id id1, stu.name, sy.id id2, sy.room, sy.stay_time
from student stu join stay sy using(id)

 

select stu.id id1, stu.name, sy.id id2, sy.room, sy.stay_time
from student stu inner join stay sy on stu.id = sy.id
where stu.age > 10

2、外链接

左外连接(left [outer] join)
右外连接(right [outer] join)
全外连接(full [outer] join)
说明：外连接同样使用on作为连接条件，与内连接不同的是， 外连接即使不满足on指定的连接条件，也会保留相应的结果集。
说明： MySQL不支持全外连接。

外连接 [outer可以省略]
select stu.id, stu.name, sy.room, sy.stay_time
from student stu left outer join stay sy on stu.id = sy.id

内连接与外连接
对于内连接，不满足连接条件的记录一律不会在结果集中显示。
对于外连接，不满足连接条件的记录也可能会在结果集中显示。
以左外连接为例，左表的记录一定会在结果集中显示，如果右表
有符合连接条件的记录，则正常显示相关字段值，如果右表没有
符合连接条件的记录，则相关字段显示为null。

MySQL中不支持全外连接。