Mysql语句运用基本函数

SELECT VERSION();

SELECT `department_id` FROM employees;

#concat  拼接
SELECT CONCAT(last_name,first_name) FROM employees;

#distint 去重
SELECT DISTINCT job_id FROM employees;

#列出字段类型
DESC employees;

#ifnull  为空时候则为0
SELECT IFNULL(commission_pct,0) AS ss,commission_pct FROM employees

# 拼接查询  caoncat ifnull('name',0)
SELECT CONCAT(`first_name`,',',`last_name`,',',IFNULL(`commission_pct`,100)) FROM employees

#where  
#条件表达式  < > = != <= >=
#逻辑表达式  && ||  and  or not  
#模糊查询    between and   like  in  is null 
#escape  转义  like "__$_%";

#is null  使用  判断null 值

SELECT last_name,IFNULL(commission_pct,0) AS a FROM employees WHERE commission_pct IS NULL 

SELECT last_name,IFNULL(commission_pct,0) AS a FROM employees


#排序查询  order by 字段 desc--asc limit 1 / 0,13放最后
SELECT * FROM employees WHERE department_id >= 90 ORDER BY employee_id DESC

# 长度 length()

SELECT userid,IFNULL(`name`,0) AS a FROM `user` WHERE `name` IS NULL

SELECT userid,IFNULL(`name`,'111') a,IFNULL(`tel`,'666') b FROM `user`

# upper  大写   lower 小写

SELECT CONCAT(UPPER(last_name),'--',LOWER(first_name)) AS 姓名 FROM employees

# substr  截取  索引从 1 开始 指定索引处所有字符 

SELECT SUBSTR('你是谁呀jsdjkcbkcbkjsa！',7) out_put

# substring  指定字符串长度字符
SELECT SUBSTR('你是谁呀jsdjkcbkcbkjsa！',1,3) out_put

# instr  返回字符串 第一次出现索引，如果找不到返回为O 

SELECT INSTR('张无忌爱上赵敏和周芷若','赵敏1') AS bb

# trim ltrim rtrim  去除首尾 / 左-右字符

#lpad  左填充  

SELECT LPAD('世界',5,'1') AS abc

#rpad  右填充
SELECT RPAD('你是谁',11,'5') AS abc

#replace  替换

SELECT REPLACE('杨过爱上小龙女','小龙女','郭襄') AS 爱情


#数学函数  round ceil  floor  

#truncate  截断函数
SELECT TRUNCATE(1.6666666,2);

# mod 取余 

SELECT MOD(10,3);

#if else  效果

SELECT IF(10>5,'大','小') AS 大小;


#case 函数
/*

switch ()
   case  常量: 语句 brack;
   default： 语句 brack;
   
 mysql 
    
    case  判断字段
    when  常量1 then 要显示的值
    。。。。
    else  要显示的值
    end  
*/

SELECT Salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
ELSE salary
END AS 新工资
FROM employees;

# case 函数2  多重if
/*
  if elseif else
  case 
  when 条件一  then 要显示的值 1
  。。。
  else 要显示的值
  end   
*/
SELECT Salary 原始工资,department_id,
CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'd'
END  AS 级别表
,CONCAT(UPPER(first_name),'--',LOWER(last_name)) AS a
FROM employees;


#分组函数
/*
统计 计算函数
sum  avg  max  max min count
*/

# datediff(max('日期'),min('日期'))  相差天数


/*
select 分组函数 

from 表

group by 分组字段

order by 字句

*/

SELECT AVG(salary),IFNULL(department_id,0) AS 部门
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id


#分组后 选择  having ()
SELECT MAX(Salary),manager_id AS a FROM employees WHERE commission_pct IS NOT NULL
GROUP BY a HAVING a <=146;