mysql的函数笔记
1. 甲骨文--oracle 和mysql( 免费,开源) 2. sqlserver 3. IBM--DB2 关系型数据库
database 数据库 , 数据表table , 数据: 记录-- 增删改查
结构化查询语言
非关系型数据库: redis, mongodb
数据库优化:
数据类型:
创建表的语法:
create table 表名(
列名1 数据类型 [约束] [默认值],
列名2 数据类型 [约束] [默认值],
列名3 数据类型 [约束] [默认值],
。。。
列名n 数据类型 [约束] [默认值]
);
数据类型:
1.整型---整数
int ,tinyint ,smallint ,bigint
int(5) int
2. 浮点型 ---实数---小数
float ,dounle , decimal
必须加上()
float(m,n) m表示占用的符号长度,n表示小数位的个数
3. 字符串类型 ‘abc’ “ghjj”
varchar(20): 可变长度 "abc" -- 3字节
char( 30): 不可变长度 "abc" --30字节
text : 文本型
4. 日期类型
date ,time,datetime
5. 其他 : 集合类型 set
枚举型: enum
二进制:blob
x的y次方法--幂 2^3= 2*2*2=8
对数: log2 8
一、 数学函数
1. 幂
2. 对数
3.绝对值
4. 三角函数
5. 上限,下限,四舍五入
1. ceil(x)和ceiling(x)意义相同,返回不小于x的最小整数数值 : 向上取整
2. floor函数返回最大整数: 向下取整3 3.5 4
3.四舍五入函数-round(x[,y])
round(x): 取最接近x的整数
select round(-1.14),round(-1.67), round(1.14),round(1.66);
+--------------+--------------+-------------+-------------+
| round(-1.14) | round(-1.67) | round(1.14) | round(1.66) |
+--------------+--------------+-------------+-------------+
| -1 | -2 | 1 | 2 |
+--------------+--------------+-------------+-------------+
1 row in set (0.00 sec)
round(x,y):
y是大于0的整数,表示保留小数点的位数, y是一个负数,表示整数位的后y位,归零,含有四舍五入现象
select round(3741.5926,-3);
+---------------------+
| round(3741.5926,-3) |
+---------------------+
| 4000 |
+---------------------+
4. truncate()截取数字
TRUNCATE(x,y)函数对操作数进行操作,结果保留小数点后面指定y位
mysql> select truncate(3.5678,2) ,round(3.5678,2);
+--------------------+-----------------+
| truncate(3.5678,2) | round(3.5678,2) |
+--------------------+-----------------+
| 3.56 | 3.57 |
+--------------------+-----------------+
5. power(x,y)求一个数的y次幂
6. sign(x)-返回数字的符号,正数为1,负数为-1,零为0
mysql> select sign(-9.2),sign(89.8),sign(0.0);
+------------+------------+-----------+
| sign(-9.2) | sign(89.8) | sign(0.0) |
+------------+------------+-----------+
| -1 | 1 | 0 |
+------------+------------+-----------+
二、字符串函数
1. 求字符串长度
char_length(), length() 区别
mysql> select char_length("a12你好哈"),length("a12你好哈");
+--------------------------+---------------------+
| char_length("a12你好哈") | length("a12你好哈") |
+--------------------------+---------------------+
| 6 | 9 |
+--------------------------+---------------------+
char_length("a12你好哈"): 统计里面有多少个字符 (不区分汉字还是英文)
length("a12你好哈"): 统计的是占用的字节数()
汉字字符集: gbk: 一个汉字占用内存2字节 ,utf8一个汉字占用内存3字节
2. (1)使用CONCAT函数连接字符串
SELECT CONCAT('My SQL', '5.5'),CONCAT('My',NULL, 'SQL');
+-------------------------+--------------------------+
| CONCAT('My SQL', '5.5') | CONCAT('My',NULL, 'SQL') |
+-------------------------+--------------------------+
| My SQL5.5 | NULL |
+-------------------------+--------------------------+
CONCAT 可以一次拼接2个以上的字符串,当其中一个是null值时,结果是NULL
(2)CONCAT_WS函数连接带分隔符的字符串concat_ws(x,s1,s2) x代表分割符,分割符为null返回null
mysql> select concat_ws("-","x","y","z"), concat_ws("-","x",null,"z"),concat_ws(null,"x","y","z");
+----------------------------+-----------------------------+-----------------------------+
| concat_ws("-","x","y","z") | concat_ws("-","x",null,"z") | concat_ws(null,"x","y","z") |
+----------------------------+-----------------------------+-----------------------------+
| x-y-z | x-z | NULL |
+----------------------------+-----------------------------+-----------------------------+
3. lower函数或者lcase函数将字符串中所有字母字符转换为小写
mysql> select LOWER("vfrvGRHG"),lcase("feGRHT");
+-------------------+-----------------+
| LOWER("vfrvGRHG") | lcase("feGRHT") |
+-------------------+-----------------+
| vfrvgrhg | fegrht |
+-------------------+-----------------+
1 row in set (0.00 sec)
upper函数或者ucase函数将字符串中所有字母字符转换为大写
4. 字节的截取:
(1) left(str,n): 截取str 左边n个字符
(2)right(str,n): 截取str 右边n个字符
(3) substring(str,start,length);
mysql> select left("abcdefg",3),right("abcdefg",3) ,substring("abcdefg",3,4);
+-------------------+--------------------+--------------------------+
| left("abcdefg",3) | right("abcdefg",3) | substring("abcdefg",3,4) |
+-------------------+--------------------+--------------------------+
| abc | efg | cdef |
+-------------------+--------------------+--------------------------+
5. 删除空格的函数
ltrim左边去空格, rtrim 右边去空格,trim两端去空格
SELECT '* book *',CONCAT('***',LTRIM(' book '),'***') l1,CONCAT('***',RTRIM(' book '),'***') l2 ,CONCAT('***',TRIM(' book '),'***') l3;
+-----------+------------------+------------------+------------+
| * book * | l1 | l2 | l3 |
+-----------+------------------+------------------+------------+
| * book * | ***book *** | *** book*** | ***book*** |
+-----------+------------------+------------------+------------+
6. 使用REVERSE函数反转字符串
7. 字符串替换replace(str,s1,s2)
mysql> select replace("abcdefgef","ef","***");
+---------------------------------+
| replace("abcdefgef","ef","***") |
+---------------------------------+
| abcd***g*** |
+---------------------------------+
任意的电话号码中间四位被替换为****
select replace("13718820432",substring("13718820432",3,4),"****");
mysql> select * from person1;
+------+-------------+
| id | phone |
+------+-------------+
| 1 | 13245677777 |
| 1 | 13269879012 |
| 3 | 13287901256 |
| 3 | 13789001256 |
mysql> select replace(phone,substring(phone,4,4),"****") from person1;
+--------------------------------------------+
| replace(phone,substring(phone,4,4),"****") |
+--------------------------------------------+
| 132****7777 |
| 132****9012 |
| 132****1256 |
| 137****1256 |
+--------------------------------------------+
三 、日期函数
select current_timestamp(),localtime(),now(),sysdate();
四、系统信息函数
五、条件判断函数
1. if(expr,v1,v2) 函数
mysql> select if(5>41,1,0);
+--------------+
| if(5>41,1,0) |
+--------------+
| 0 |
+--------------+
mysql> select if(comm is null,0,comm)+sal 总钱数 from emp;
+--------+
| 总钱数 |
+--------+
| 880 |
| 2060 |
| 1875 |
| 3273 |
| 2775 |
| 3135 |
| 2695 |
| 3300 |
| 5500 |
| 1650 |
| 1210 |
| 1045 |
| 3300 |
| 1430 |
| 3556 |
+--------+
15 rows in set (0.00 sec)
mysql> select if(comm is null,sal,comm+sal) 总钱数 from emp;
+--------+
| 总钱数 |
+--------+
| 880 |
| 2060 |
| 1875 |
| 3273 |
| 2775 |
| 3135 |
| 2695 |
| 3300 |
| 5500 |
| 1650 |
| 1210 |
| 1045 |
| 3300 |
| 1430 |
| 3556 |
+--------+
15 rows in set (0.00 sec)
2. case函数
case 条件 when 值1 then 结果1 [when 值2 then 结果2][else 结果n] end
mysql> select case 31*5%4 when 0 then "整除" when 1 then "余数为1" when 2 then "余数为2" else "余数为3" end;
+--------------------------------------------------------------------------------------------------------+
| case 31*5%4 when 0 then "整除" when 1 then "余数为1" when 2 then "余数为2" else "余数为3" end |
+--------------------------------------------------------------------------------------------------------+
| 余数为3 |
+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)