CREATE DATABASE Lianxi

use Lianxi

-- 数值型函数
/*	绝对值	abs
	  向上取整	ceil和ceiling
		向下取整	floor
		随机数	rand
		四舍五入	round
*/


SELECT abs(-999) as 绝对值
SELECT ceil(3.14) as 向上取整
SELECT floor(3.14) as 向下取整
SELECT rand() as 随机数
SELECT round(3.14 , 1) as 四舍五入


-------- 字符串函数

SELECT LENGTH('asd123') as 字节长度
SELECT LENGTH('闽西') as 字节长度
SELECT CHAR_LENGTH('asd123') as 字符串长度
SELECT CHAR_LENGTH('闽西') as 字符串长度

SELECT REPLACE ('123456789','123456789','abcdefghi') as 替换
SELECT INSERT ('123456789',1,9,'abcdefghi') as 替换

SELECT LEFT ('123456789',5) as 从左所选数字截取
SELECT RIGHT ('123456789',5) as 从右所选数字截取
SELECT substr('123456789',3,5) as 从所选位置到指定位置截取

select CONCAT('123','abc') as 字符串拼接

SELECT LOWER('ABCDEF') as 转换为小写
SELECT UPPER('abcdef') as 转换为大写

SELECT REVERSE('123456789') as 逆序

SELECT TRIM('  123   ') as 去除左右两边的空格
SELECT LTRIM('  123   ') as 去除左边的空格
SELECT RTRIM('  123   ') as 去除右边的空格

SELECT now() as 当前系统时间
SELECT year(now()) as 当前系统年份
SELECT time(now()) as 当前系统时间

SELECT FROM_UNIXTIME(1234567890)
SELECT UNIX_TIMESTAMP(now())

SELECT *,
if (
		CASE xx -- 列名
		WHEN 1 THEN
		ELSE 2
	END
)
from xxx