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当中一列存在值的查询出来

 

 

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