第35次文章：数据库简单查询

/*
java中的+号：
（1）运算符，两个操作数都为数值型
（2）连接符，只要有一个操作数为字符型
mysql中的+号：
仅仅只有一个功能：运算符
select 100+90；两个操作数都为数值型，则做加法运算
SELECT '123'+90;        只要其中一方为字符型，试图将字符型数值转换为数值型
                        如果转化成功，则继续做加法运算
SELECT 'ans'+90;        如果转换失败，则将字符型数值转换为0
SELECT null+10;          只要其中一方为null，则其结果肯定为null

*/ 
#案例，查询员工名和姓连接成一个字段，并显示为 姓名,实现字符串的连接使用concat函数
SELECT CONCAT(empname, last_name) 全名 FROM emp;

/*
LIKE
特点：
1.一般和通配符搭配使用
      通配符：
      % 任意多个字符,包含0个字符
      _ 任意单个字符
*/

#案例1：查询员工名中带有字母a的员工
SELECT * FROM emp WHERE empname like '%a%';

#案例2：查询员工名中第三个字符为i，第五个字符为e的员工名和工资
SELECT empname,salary from emp WHERE empname LIKE '__i_e%';

#案例3：查询员工名中第二个字符为_的员工名，包含有特殊字符
SELECT empname FROM emp WHERE empname LIKE '_$_' ESCAPE '$';#可以使用任意字符与使用escape关键字一起使用，将此字符改变为转义字符；或者使用斜杠转义：empname LIKE '_\_'

#2.between and
/*
1.使用between and 可以提高语句的简洁度
2.包含临界值
3.两个临界值不要调换顺序
*/

#案例1.查询员工薪资在2000到5000之间的员工信息
select * FROM emp where  salary>=2000 AND salary<=5000;
#-------------------
select * FROM emp WHERE salary between 2000 and 5000;

/*
含义：判断某字段的值是否属于in列表中的某一项
特点：
      （1）使用in提高语句的简洁度
      （2）in列表的值类型必须一致或兼容
        '123' 123
      （3）列表中不支持通配符的使用
*/
#查询：查询员工的姓名为tom1、tom、ceimeng的员工的名字和部门编号
SELECT empname,deptId FROM emp where empname = 'tom1' OR empname = 'tom' OR empname = 'ceimeng';
#------------------------------------------
SELECT empname,deptId FROM emp where empname IN('tom1','tom','ceimeng');

/*
=或<>不能用于判断null值
is null 或者 is not null 可以判断null值

#is null pk <=>
is null   :仅仅可以判断null值，可读性较高，建议使用
<=>      :既可以判断null值，又可以判断普通的数值，可读性较低
*/

#案列1：查询没有奖金的员工名和奖金
SELECT empname,bonus FROM emp WHERE bonus is NULL;
#----------------
#安全等于     <=>
SELECT empname,bonus FROM emp WHERE bonus <=> NULL;

#进阶3：排序查询
/*
引入：
      select *  FROM emp;
语法：
      select 查询列表
      FROM 表
      【where 筛选条件】
      ORDER BY 排序列表 【asc|DESC】
特点：
      1、asc代表的是升序，desc代表的是降序
      如果不写，默认的是升序
      2、order by字句中可以支持单个字段、多个字段、表达式、函数、别名
      3、order by字句一般是放在查询语句的最后面，limit字句除外
*/
#案列1：查询薪资>=2000的员工信息，按生日进行排序【添加筛选调价】
SELECT * from emp where salary >= 2000 ORDER BY birthday DESC;

#案列2：按年薪的高低显示员工的信息和 年薪【按表达式排序】
SELECT *,salary+IFNULL(bonus,0) '年薪' FROM emp ORDER BY salary+IFNULL(bonus,0) DESC;

#案例3：查询员工信息，要求先按照薪资升序，再按照员工编号降序【按多个字段进行排列】
SELECT * FROM emp ORDER BY salary ASC,id DESC;

/*
取余函数的底层运算
mod(a,b)   : a-a/b*b
MOD(-10,-3) :-10-(-10)/(-3)*(-3)=-10-3*(-3)=-1
*/
select MOD(-10,-3);
SELECT -10%(-3);

#1.if函数：if else 的效果
SELECT empname,bonus, IF(bonus is NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注 FROM emp;

#2.case函数的使用一：switch case 的效果
/*
java 中
switch(变量或表达式){
          case 常量1：语句1;break;
          .....
          DEFAULT:语句n;break;
}
mysql 中
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
ELSE 要显示的值n或语句n；
END
*/

/*案例：查询员工的工资，要求

部门号=1，显示的工资为1.1倍
部门号=2，显示的工资为1.2倍
部门号=3，显示的工资为1.3倍
部门号=4，显示的工资为1.4倍
*/

SELECT empname,salary 原始工资,deptId,
CASE deptId
WHEN 1  THEN salary*1.1 
WHEN 2  THEN salary*1.2 
WHEN 3  THEN salary*1.3 
ELSE salary*1.4 
END 新工资
FROM emp;

#3.CASE 函数的使用二：类似于  多重if
/*
java中：
if(条件1){
    语句1;
}else if(条件2){
    语句2;
}
...
ELSE{
    语句n;
}

mysql 中:
CASE 
WHEN 条件1 THEN 要显示的值1或语句1;
WHEN 条件2 THEN 要显示的值2或语句2;
.....
ELSE 要显示的值n或语句n;
end
*/

#案例：查询员工的工资情况
/*
如果工资>10000，显示的A级别
如果工资>5000，显示的B级别
如果工资>1000，显示的C级别
否则，显示的D级别
*/

SELECT empname,salary,
CASE
WHEN salary>10000 THEN 'A'
WHEN salary>5000 THEN 'B'
WHEN salary>1000 THEN 'C'
ELSE 'D'
END 工资等级
FROM emp;

/*
特点：
1、sum avg 一般用于处理数值型
    max min count 可以处理任何类型
2、以上分组函数都忽略null值
3、可以和关键字distinct搭配使用，实现去重的运算
4、count函数的单独介绍
一般使用count(*)用作统计行数
5、和分组函数一同查询的字段要求是group by 后的字段
*/
#1、和distinct搭配
SELECT COUNT(DISTINCT deptId) from emp;
#2、count函数的详细介绍
SELECT COUNT(*) from emp;#统计整张表中的所有行数，也可以通过在添加常量的方法来统计：SELECT COUNT(1) from emp;
#效率：
#MYISAM 存储引擎下 ， count(*)的效率高
#INNODB 存储引擎下 ， count(*)和COUNT(1)的效率差不多，比count(字段)要高一些  

#进阶5：分组查询
/*
特点：
1、分组查询中的筛选条件分为两类
            数据源              位置              关键字
分组前筛选  原始表           GROUP BY子句的前面     WHERE
分组后筛选  分组后的结果集    GROUP BY子句的后面     HAVING

（1）分组函数做条件肯定是放在having子句中
（2）能用分组前筛选的，就优先考虑使用分组前筛选

2、group by子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开，没有顺序要求），表达式（使用的较少）
3、也可以添加排序（排序放在整个分组查询的最后）
*/

#案例1：查询每个部门的平均工资
SELECT round(avg(salary),2),deptId FROM emp GROUP BY deptId;

#添加分组后复杂的筛选条件
#案例2：查询部门编号>1的每个部门中，最低工资大于1000的部门编号是哪个，以及其部门的最低工资
SELECT deptId,MIN(salary) 部门最低工资 FROM emp WHERE deptId>1 GROUP BY deptId HAVING 部门最低工资>5000;