2021-05-24

MySQL的进阶查询

#进阶3排序查询


/*
引入
     select*from employees;
     
语法：
     select 查询列表
     from 表
     where 查询条件
     order by 排序列表 asc（升序可省略），desc（降序）      
     
     执行顺序：1.from 2.where 3. select 4.order by 5. limit
     */
     
     
#案例1：查询员工信息，要求工资从高到低排序

SELECT*FROM employees ORDER BY salary DESC;     

#案例2：查询部门编号>=90的员工信息，按入职时间的先后进行排序

SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate ASC;

#案例3：按年薪的高低显示员工的信息和年薪（按表达式排序） (order by 后支持别名)
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

#案例3：按姓名的长度，显示员工的姓名和工资（按函数排序）
SELECT LENGTH (last_name)字节长度 ,last_name, salary
FROM employees
ORDER BY LENGTH (last_name) DESC;


#案例4：查询员工信息，先按工资排序再按员工编号排序（按多个字段排序）
SELECT *FROM employees 
ORDER BY salary ASC,employee_id DESC;

#案例5：查询工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT last_name,salary FROM employees
WHERE salary  NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;


#案例6：查询邮箱中包含e的员工信息，并先按邮箱的字节降序，再按部门号升序
SELECT *FROM employees WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id;

#进阶4：常见函数
/*
概念：类似java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1隐藏实现细节   2.提高代码的重用性
调用： select 函数名（实参列表）【from 表】；
特点：1叫什么（函数名）
      2.干什么（函数功能）
分类  1.单行函数
        如:concat，length，ifnull等
      2.分组函数
      功能：做统计使用，又称为统计函数，聚合函数，组函数
      
常见函数：
        字符函数：
        length
        concat
        substr
        instr
        trim
        upper
        lower
        lpad
        rpad
        replace
        
        数学函数
        round
        ceil
        floor
        truncate            
        mod
        
        日期函数
        now
        curdate
        curtime
        year
        month
        monthname
        day
        hour
        minute
        second
        str_to_date
        date_formate
        
        其他函数
        version
        database
        user
        
        控制函数
        if
        case
*/

#一.字符函数

#1.length 获取参数值的字节个数(utf8一个汉字占3个字节长度，jdk一个汉字占2个字节长度)
SELECT LENGTH('john');
SELECT LENGTH('卢本伟牛逼');

SHOW VARIABLES LIKE '%char' 

#2.concat拼接字符串
SELECT CONCAT(last_name,'_',first_name)姓名 FROM employees;

#3. upper、lower
SELECT UPPER('John');
SELECT LOWER('John');

#案例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) FROM employees;

#4.substr、substring（注意：索引从1开始）
SELECT SUBSTR('耶斯莫拉',4)郭语;

#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('耶斯莫拉',1,3)郭语;

#案例：姓名中首字符大写，其他字符小写然后拼接显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))姓
FROM employees;


#5.instr 返回字串第一次出现的索引，如果找不到返回0
SELECT INSTR('卢本伟牛逼耶斯莫拉','耶斯莫拉')AS out_put;

#6.trim
SELECT LENGTH(TRIM('卢本伟')) AS put_out;
SELECT TRIM('a'FROM 'aaaaaa卢aaaaaaaa本aaaaaaaaa伟')AS out_put;

#7.lpad用指定的字符实现左填充
SELECT LPAD('卢本伟',10,'*')AS out_put

#8rpad用指定的字符实现右填充
SELECT RPAD('卢本伟',10,'*')AS out_put

#9replace 替换
SELECT REPLACE('17张牌你能秒我','我','卢本伟')AS out_put;


#二.数学函数
#round 四舍五入
SELECT ROUND(1.55)
SELECT ROUND(1.555,2)

#ceil 向上取整，返回>=该数的最小整数
SELECT CEIL(-0.99);

#floor 向下取整，返回<=概述的最大整数
SELECT FLOOR(-0.99);

#truncate 截断
SELECT TRUNCATE(1.69999,1)

#mod取余 a-a/b*b 得到值的正负取决于被除数
SELECT MOD(-10,-3);
SELECT 10%3;

#三.日期函数
#now 返回当前日期+时间
SELECT NOW();

#curdate 返回当前系统日期，不包含时间
SELECT CURDATE();

#curtime 返回当前时间，不包含日期
SELECT CURTIME();

#可以获取指定部分，年.月.日.小时.分钟.秒
SELECT YEAR(NOW())年；
SELECT YEAR('1999-1-1')年；
SELECT YEAR(hiredate) 年 FROM employees;

SELECT MONTH(NOW())月;
SELECT MONTHNAME(NOW())月;

#str_to_date 将字符通过指定的格式转换成日期

SELECT STR_TO_DATE('1999-5-20','%Y-%c-%d')AS put_out;

#查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate='1992-4-3';	

#date_format将日期转成字符串
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日')AS put_out;

#查询有奖金的员工名和入职日期（×月×日 ×年）

SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
 FROM employees WHERE commission_pct IS NOT NULL; 
 
 
#四.其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();

#五.流程控制函数
#1.if 函数： if else 的效果

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

SELECT last_name, commission_pct ,IF(commission_pct IS NULL,'没奖金','有奖金') 备注
FROM employees;

#2.case 函数的使用一： switch case的效果
/*
java中
switch（变量或表达式）{
     case 常量1：语句1；break；
     ...
     default：语句n；break；
}

mysql中
case 要判断的或表达式
when 常量1 then 要显示的值或语句1；
...
else 要显示的值n或语句n；
*/
 
 /*案例：查询员工的工资，要求：
    部门号=30，显示的工资为1.1倍；
    部门号=40，显示的工资为1.2倍；
    部门号=50，显示的工资为1.3倍；
*/
SELECT salary,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资 FROM employees;

#3.case 函数的使用二：类似于 多重if
/*
java中
if（条件）{
    语句1；
}else if（条件2）{
    语句2；
}
   ...
    else{
    语句n；
}

mysql中：
case
when 条件1 then 要显示的值1或语句1
whne 条件2 then 要显示的值2或语句2
...
else 要显示的语句n或语句n；
end
*/ 

#案例：查询员工的工资情况 如果工资大于20000显示A级别，如果工资大于15000显示B级别，如果工资大于10000显示C级别，否则显示D级别
SELECT salary,
CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别 FROM employees;