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
  去除字符串前后空格：trim(str)====>trim(' a ')
  • 去除前后指定字符：trim(str1 from str2) ====>trim('a' from 'aba'),str2没有去除前后空格，若str2前后有空格，会发现匹配不了而无法去除指定字符
  • 去除前后指定字符改进：trim(str1 from trim(str2)) ====>trim('a' from trim(' aba '))
  • 位数填充：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当中一列存在值的查询出来