MySQL笔记（1）-- 函数

函数的使用：
- 字符串的拼接：concat(str1,str2) as 别名
- "+"号是运算符，进行数值的计算：
  - - 当两个操作数是数值时，做加法计算： select 10+90 ====> 100
    - 当其中一个为字符型时，会将字符型进行转换为数值
      - 如果转换成功，继续做加法计算：select '10'+90 ====> 100
      - 转换失败，将字符型转换为0【可以表示字符型忽略不算】：select 'a'+90 ====> 90
    - 当其中一个为null时，结果为null：select null+90 ====>null
- 判断为空：ifnull(str1,str2)，如果str1为空，输出str2；
- 获取字段值的长度：length(str)
  - 字段类型是varchar的几种字符集下的长度值【适应中文模式和英文模式，即中文标点和英文标点】：
    - ASCII格式：不能存储汉字，单个英文字母长度为1；
    - GBK格式：单个汉字长度是2，单个英文字母长度是1；
    - UTF8格式：单个汉字长度是3，单个英文字母长度是1；
    - UTF16格式：单个汉字和英文字母长度都是2；
    - UTF-32格式：单个汉字和英文字母长度都是4；
  - varchar(M)和char(M)的对比：
    - varchar(M)的M范围为0-65535【2的16次方，2个字节】，存储可变长度字符，存储位置不固定，必须使用引用表来查找内容，查找效率低；
    - char(M)的M范围为0-255【2的8次方，1个字节】，默认为1，存储固定长度的字符（字段存储在寄存器中，因为它的大小是已知的，这使得搜索和索引更快），比如性别【0/1，男/女】；
- 大小写转换：upper(str)转换为大写，lower(str)转换为小写；
- 截取：substr(str,index)，对str进行截取，从index开始，结果不包含index的值，默认索引为1；
- 获取第一个出现值的索引：instr(str1,str2) ====>instr('ababab','a')====> 1
- 位数填充：lpad进行左填充【rpad进行右填充】，当字符串长度不满指定的数值时，缺少多少位，在左边进行填充；当字符串长度超过指定数值时，只输出指定数值的长度字符串：
- ```
select lpad('字符串', 10,'*') as name   =====>> 结果：*******字符串
select lpad('字符串', 1,'*')  as name   =====>> 结果：字
```
  字符替换：replace(str1,str2,str3)若在字符串str1中存在str2，则将str2替换为str3
```
replace('abc','a','d')====>结果：dbc 
```
- 取舍：
  - round进行四舍五入，对负数来说，先对其转换为正数取整，再加负号转换为负数：
    - round(-1.6)先对1.6进行取整为2，再加上符号
    - round(1.23456, 2)表示小数位取2位
  - ceil向上取整（>=)：ceil(1.2)==2
  - floor向下取整(<=)：floor(-9.9)==-10
  - truncate(M,D)截断，D表示只取小数点后几位：truncate(1.23456,3) ===1.234
- 取余：mod 等价% ,mod(a,b)等价a-a/b*b,如果a是正数，结果是正数，如果a是负数，结果是负数，结果的正负跟b无关
- now()获取日期加时间，CURDATE()获取日期，curtime()获取时间，year(str)获取指定日期时间中的年 ====>year(now());
  - 将指定日期格式的字符转换为'yyyy-MM-dd HH:mm:ss'的格式:str_to_date(str1,str)，str1和str2的格式必须对应
```
str_to_date('1/21/29 11','%c/%d/%y %h')=====>结果：2029-01-21 11:00:00
str_to_date('1-21-2020','%c-%d-%Y')=====>结果：2020-01-21　　
```
  - 将指定日期格式'yyyy[英文符号]MM[英文符号]dd HH[英文符号]mm[英文符号]ss'，类似'yyyy-MM-dd HH:mm:ss'或'yyyy!MM!dd HH,mm.ss'转换成指定格式的字符：date_format(str1,str)
```
select date_format('2020!12!12 12,','%Y/%m/%d %H')=====>结果：2020/12/12 12
select date_format('2012-12-2 12','%d日%m月%Y年 %H')=====>结果：02日12月2012年 12
```
    　　
- sum求和，avg平均值，max最大值，min最小值，count总数：
  - sum和avg处理数值类型，max、min、count处理任何类型；以上类型的处理都忽略null值，即包含null的数据不进行处理；推荐使用count(*)
  - MYISAM存储引擎下，count(*)的效率高；
  - innodb存储引擎下，count(*)和count(1)的效率差不多，比count(字段)要高一些；
- if(expr,a,b)，如果表达式expr为真，结果为a，否则为b
<>表示不等于，等价!=，但不能判断null，使用is null和is not null来判断；is null仅可以判断null，等价<=>null,<=>表示等于，可以判断null和数值，但可读性差；
模糊查询like语法：like ‘__a_b%'表示查询包含第三个为a，第五个为b的数据；like '_\_%'表示查询包含第二个为_的数据，\表示转义，也可以使用 like '_$_%' ESCAPE '$'来表示，escape表示$是转义字符，转移字符可以自定义；一个'_‘表示仅替代一个字符，一个’%‘表示替代一个或多个字符；
between范围的使用：between 1 and 2表示范围在[1,2]，between 3 and 2表示[3,...)和(...,2]，即大于等于3和小于等于2；
查看表的结构：desc 表名；
排序：order by 字段 asc(升序，默认)/desc(降序)，支持多个字段的排序；
分页：limit 偏移位置,条目【偏移位置默认开始为0】，limit 0,10等价limit 10，表示查询前10条记录；

case语法：case等价switch,如果字段的值符合表达式exprA ,输出rsultA

 case 字段
 when  exprA then rsultA
 when  exprB then rsultB
 else  rsult
 end as 结果

分组group by：select 分组函数 lie【要求出现在group by的后面】
- 分组前查询，使用where：select MAX(id) from tableName where id>10 group by id;
- 分组后查询，使用having：select count(1) from tableName group by id having count(1)>1;
union联合查询：sql union sql 将两个SQL语句的结果一起输出，应用场景是查询结果来自多表，但表之间没有关联关系，每个表的查询列数必须是一致的，查询的每个列值属性和顺序最好一致【union默认去重，union all 可以不去重】
```
select id,name,sex from tableName where sex='男'
union
select id,name,gender from tableName where gender='male'
```

完整sql结构：

select 列表
form 表
连接类型 join 表2
on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 偏移,条目数

讨论：
- select *from tableName;和select *from tableName where columnNameA like '%%' and columnNameB like '%%' 的区别：
  - 前面一句会把包含null的数据查出来，后面一句会把columnNameA和columnNameB包含有值的，即使是空字符串的查出来，不包含指定列的null值
- 把and替换为or：select *from tableName where columnNameA like '%%' or columnNameB like '%%' 的区别：
  - 会把columnNameA和columnNameB当中一列存在值的查询出来

posted @ 2020-03-09 16:31 码猿手阅读(200) 评论(0) 编辑收藏举报

刷新页面返回顶部

码猿手

MySQL笔记（1）-- 函数

公告