mysql 两个整数连接_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中不支持全外连接。