MySQL_Ch_2

@BCKou

MySQL_Ch_2

1.特殊比较字符

1. 特殊的比较运算符
   – 1) between...and...
   – 格式: 列名between下限and上限

   – 包含区间是含两个端点 等价大于等于 并且 小于等于

   – 查询emp表中,20号部门月薪在1000-2000之间的员工信息

   select * from emp where deptno = 20 and sal >= 1000 and sal <= 2000;
   

   – 不要按照数学公式方式写区间0 <= sal <= 2000;

   select * from emp where deptno = 20 and sal between 1000 and 2000;
   

   – 下限和上限可以使用数值、文字(字符串)、日期

   – 通常使用最多的是数值(表示数值范围)、日期(表示日期范围)

   – 文字(使用很少,例如:英文名字可以按照字母顺序表示)

   – 查询emp表中,在1982年入职的员工信息

   select * from emp where hiredate between '1982-1-1' and '1982-12-31'
   

   – 英文字母顺序

   select * from emp where ename between 'A' and 'B';-- 后者端点不包含
   

   – 上限和下限可以反着写,但是你的语句虽然不报错,但是逻辑上是不行的

   select * from emp where deptno = 20 and sal between 2000 and 1000;
   

2. in

   – 格式:列名 in(值1,值2,...)

   – 等价于多个or的关系组合

   – 查询所有30号部门,其上司的员工编号是7902、7698、7788的员工信息

   select * from emp where (mgr = 7902 or mgr = 7698 or mgr = 7788) and deptno = 30;
   

   select * from emp where mgr in(7902,7698,7788)and deptno = 30;
   

   – 针对单个值,也可以使用in,它的执行效率要低,实际中有时需要写in

   – =等号是无法与null进行匹配

3. like(重要)

   – 格式: 列名like包含统配字符的字符串

   – 模糊查询

   统配字符 (% _)

   – % :0-n个任意字符

   – _ :1个任意的字符

   – 查询所有名字首字母是B的员工信息

   select * from emp where ename like 'B%';
   

   – 查询倒数第二个字符是T的员工信息

   select * from emp where ename like '%T_';
   

   关键字:escape 转义 将通配字符转义为普通的字符含义

   ESCAPE '/' 表示'/'为换码字符，在查询时使用可以方便查询到原本就含有'_'、'%'的值。

   – 如果要查询到名字在四个字符以上、倒数第三个字符为d、倒数第二个字符为_的教师的全部信息：

   select * from prof where Pname like '%d/__' escape '/';
   

   – 查询名字为HaHa%Ha的员工信息

   select * from emp where ename like '%/%%' escape '/';
   

   – escape后面需要指定一个符号

   – like一般针对文字的模糊查询使用较多

4. is null

   – 用于筛选空值的

   – 查询没有奖金的员工信息

   错误写法:select * from emp where comm = 0 or comm = null;-- 错误写法,null不能写成=等号
   

   select * from emp where comm = 0 or comm is null;
   

   – 查询公司的Boss是谁(查询其编号为空值的)

   select * from emp where mgr is null;
   

5. not

   – 逻辑非关系

   – 可以与上述4个特殊比较运算符一起使用

   – not between ... and...

   – not in(...)

   – not like ...

   – is not null (英文语法设置)

6. ALL

   (min, max)

   小于ALL ==> 小于min

   大于ALL ==> 大于max

7. ANY

   (min, max)

   小于ANY ==> 小于max

   大于ANY ==> 大于ANY

   等于ANY ==> 大于等于min && 小于等于max 类似于in

2.排序

1. 排序

   – order by 排序

   – group by 分组

   – 升序asc(默认) 和 降序desc

   – 数值、日期、文字(字母日期)

   – 查询所有10号部门的员工信息,按照月薪升序排序

   select * from emp where deptno = 10 order by sal asc;-- 实际写法
   

   – 按照多列排序

   – 查询员工姓名,年收入,特定是20号部门,按照年收入的升序排序,按照入职日期的降序排序

   – 第二次排序是要建立在第一次排序的基础上

   select ename,(sal + ifnull(comm,0))*12 as 年收入
   from emp 
   where deptno = 20 
   order by 年收入 asc,hiredate desc; -- 使用别名可以进行排序
   

   – 查询年收入高于15000的所有员工信息

   # 错误语法,这里别名不能使用
   select ename,(sal + ifnull(comm,0))*12 as 年收入
   from emp where 年收入 > 15000;
   -- Unknown column '年收入' in 'where clause'
   

   – SQL语句执行顺序

   – 书写顺序: select ... from... where ... order by

   – 执行顺序: from ... where ... select ...order by

   – 因此别名在这里where不能使用进行判断

   select ename,(sal + ifnull(comm,0))*12 as 年收入
   from emp where (sal + ifnull(comm,0))*12 > 15000;
   

3.练习

1. 练习

   1. 查询员工信息,按照部门编号升序排列,按照月薪降序排列

      select * from emp order by empno asc ,sal desc;
      

   2. 查询在1982期间入职的,其上司的员工编号是7782,7788的员工信息

      select * from emp where (hiredate between '1982-1-1'and '1982-12-31') and mgr in(7782,7788);
      

   3. 查询名字中包含至少一个o,至少一个T的员工信息

      select * from emp where ename like '%o%T%'or ename like'%T%o%';
      

   4. 查询员工姓名,月薪,奖金数,按照年收入升序排列

      select ename as 姓名,sal as 月薪,comm as 奖金数 from emp order by (sal + ifnull(comm,0))*12;
      

   5. 查询在10号部门工作,月薪在2000-3000之间的员工信息

      select * from emp where deptno = 10 and sal between 2000 and 3000;
      

4.MySQL的函数

– 函数:官方软件中已经给我们封装了一些固定的功能

– 我们只需要了解对应的函数名称,知道如何调用即可

– 分为两大类

– 1) 单行函数

– 2) 多行函数(分组函数、聚合函数)

1.单行函数

– 只对一行数据进行变换,每一行都会返回一个结果

• 字符函数

  1. upper()小写转大写,针对英文字母

  2. lower()大写转小写,针对英文字母 – 中文不会转换

     查询一个结果,将一个字符串从小写转为大写;使用upper()函数

     select upper('hGhE') from dual;
     

     – dual在MySQL中属于关键字;在oracle中属于虚表;

     # 在oracle中可以使用:	select * from dual;
     

  3. 字符截取函数

     1. substring(str,n,len)获取一个子字符串

        str:被截取的字符串

        n:开始截取的位置,mysql中索引位是以1开始

        len: 截取字符个数

        select substring('0123456',3,3) from dual;
        

        # 查询emp表中,员工姓名前三个字母
        select substring(ename,1,3) from emp;
        

  4. 字符查找函数

     – instr(str1,str2)

     – 在str1中寻找(不是遍历,及只找第一次出现的位置)str2字符串,返回一个位置,str2在str1中的位置

     – 如果没有查到,返回0

     select instr('I Like GiRL','K') from dual; #5 空格也是一个字符;不区分大小写
     

  5. 字符串拼接函数

     –concat(str1,str2)

     – 多个字符串拼接

     select concat('haha','hehe','xixi') from dual;
     

  6. 字符串替换函数

     –replace(str1,str2,str3)

     – 在str1字符串中,使用str3来替换全部的str2

     select replace('ABCDEF','CD','HAHA') from dual;
     

  7. 字符串的长度函数

     – length(str)

     – 返回的是一个字符串的长度

     select length('abcde'),length('哈哈') from dual; #5 6 mysql中一个汉字占3个Unicode编码长度
     

  8. 字符串补齐函数

     – lpad(str1,n,str2)左补齐函数

     – 将字符串str1使用str2在其左侧补充n个长度

     – rpad(str1,n,str2)右补齐函数

     – 将字符串str1使用str2在其右侧补充n个长度

     select lpad('abc',6,'*'),lpad('abc',2,'*')from dual;	#	***abc	ab
     

  9. 去除函数trim()

     – 去除前后字符函数,函数能去掉字符串前后的空格,或者去掉字符串首尾特定的字符.

     select trim('   hell  oworld') from dual;
     select trim('h' from 'helloworldhh') from dual; # elloworld
     select length('   hell  oworld'),length(trim('   hell  oworld'))from dual;
     

• 数值函数

  1. round(n1,n2) 四舍五入

     – 将n1值四舍五入,精确到小数点后n2位

     – n2表示负数时,将小数点看成0,小数点左边就是1,2…;小数点左边依次为-1,-2,-3…

     select round(3.1415,3),round(3.14,0),round(16.14,-1),round(16.14,-2)from dual;	# 3.142	3	20	0
     

  2. trunca(n1,n2)舍去函数

     – 将n1数舍去,精确到小数点后n2位

     select truncate(3.1415,3),truncate(3.14,0),truncate(6.14,-1)from dual;
     

  3. mod(n1,n2)取余函数

     – 求n1除以n2后得到的余数

     select mod(5,3),mod(5,-3),mod(-5,3),mod(-5,-3) from dual;	# 2 2 -2 -2
     

     – 判断依据:余数是正还是负,取决于被除数

• 日期函数

  1. now()

     – 获取数据库所在服务器的当前系统时间

     select now() from dual;	#MySQL特有的
     select sysdate() from dual;	-- system date 系统时间	通用写法
     select curdate()from dual;  #2020-11-11
     # cur_开头的函数都与时间有关
     

  2. 时间差值函数

     – timediff(date1,date2):返回的是两个日期的差值,是一个数值

     – datediff(date1,date2):返回两个日期相差的天数

     – 假设emp表中所有的员工至今都未离职,计算他们的司龄

     select datediff(now(),hiredate)/365 from emp;	#只能算大概
     select timediff(now(),hiredate) from emp;	#格式对应才能准确,小时分钟秒要有都有
     select timediff(curdate(),hiredate) from emp;#大概
     select year(curdate())-year(hiredate) from emp;	-- MySQL中准确的
     

  3. 追加/追减函数

     – 追加 date_add(date,interval expr type)

     – 追减 date_sub(date,interval expr type)

     – 设置公司的试用期是6个月,计算emp表中所有的员工的转正日期

     select date_add(hiredate,interval 6 month) from emp;
     

     – 查询工龄大于或者等于35年的员工信息

     select * from emp where (year(curdate())-year(hiredate)) >= 35;
     select * from emp where date_sub(curdate(),interval 35 year) >= hiredate;
     

     4. last_day(date)查询所在月份的最后一天的日期

     select ename,hiredate,last_day(hiredate) from emp;
     

     5. round()、trunc()、extract()函数

        – round

        – 在MySQL中,round()函数是将日期变为字符串

        select round(now()) from dual;
        

        – 在oracle中,round()函数用法:月份中15号含15号之前的舍去,16含16之后的进位

        – 查询1981年入职的员工姓名,入职日期

        select ename,hiredate,round(hiredate,'month') from emp where substr(round(hiredate,'month'),3,2) = 81;
        

        – trunc 舍去

        – extract 抽取日期

        select ename,hiredate,extract(month from hiredate) from emp;
        

     6. 日期的格式化函数

        – 日期的格式化:date_format(date,format)

        – 时间的格式化:time_format(time,format)

        – format 指定的日期格式或者是指定时间格式

        select date_format(now(),'%Y/%m/%d %H:%i:%s') from dual;
        select date_format(now(),'%Y年%m月%d日 %H:%i:%s') from dual;
        select now() from dual;		#2020-11-10 16:43:03
        

• 转换函数

  – MySQL中转换函数:日期格式转换、大小写转换

  – oracle中转换函数:to_date、to_char、to_number

• 通用函数

  – 控制语句 实际中一般不使用，描述程序的执行流程

  – 基本上大多数的控制在Java语言中进行描述

  select case when 1 > 2 then 'true' else 'false' end; #false
  

  – if(expr1,expr2,expr3)

  – 如果expr1是true,则if语句返回的值是expr2,否则返回的是expr3

  select if(1 > 2, 2, 3); # mysql规则不是很规范
  select if(1 > 2, 2, 3) from dual;
  

多行函数(分组函数)

• 有多条记录生成一组统计出的数据结果
• 数据库标准叫法:行(记录) 列(属性)

1. 统计型的函数

   1. 求和 sum()函数

      select sum(sal) from emp;
      

   2. 求平均值(使用最多) avg()函数

      select avg(sal) from emp;
      

   3. 最大值max()函数

      select max(sal) from emp;
      

   4. 最小值min()函数

      select min(sal) from emp;
      

   5. 统计个数(计数)(使用最多) count()函数

      查询emp中,有多少个员工

      select count(ename) from emp;
      

      查询emp中,有多少个部门

      select count(distinct deptno)from emp;
      

   6. 分组 group by 依据…分组

      查询各个部门的工资平均值

      select deptno,avg(sal) from emp group by deptno;
      

      select empno,deptno,avg(sal) from emp group by deptno;#错误写法
      

      – 注意:

      – 只有使用了分组函数(sum() avg() max() min() count()等）,才能使用group by

      – 只有在group by 中出现的列,才能写在select中

      – 在MySQL中如果select 中的列,没有出现group by中,可以执行,但是逻辑是不对的.

      • group by 多次分组

        – 第二次分组是建立在第一次分组的基础上

        分组先后是有顺序区别顺序的,结果相同,列的输出顺序不同

        – 查询每个部门不同岗位的平均工资

        select deptno,job,avg(sal) from emp group by deptno, job; # 每个部门不同岗位
        select deptno,job,avg(sal) from emp group by job, deptno; #顺序前后有区别,这个与上面那个结果是相同的
        

        – 查询10号部门和20部门的平均工资

        select deptno avg(sal) from emp where deptno in(10,20) group by deptno;
        

   7. having 专门用于分组函数的筛选,代替了where的查询

      – 只有在where 中出现分组函数时,才用having替换.

      – group by ... having ...

      – 查询平均工资高于2500的部门id和平均工资

      select deptno,avg(sal)
      from emp where avg(sal) > 2500 -- 数据库是无法区分到底以什么分组
      group by detpno  #错误写法
      

      select deptno, avg(sal) from emp group by deptno having avg(sal) > 2500;
      

SQL语句书写顺序和执行顺序

  -- 只有在`where` 中出现分组函数时,才用`having`替换.
  
  -- 书写顺序: `select ... from ... where ... group by ... having ... order by...`
  
  -- 执行顺序: `from ... where ... group by ...having ... select ... order by ...`

​

练习（以employees表）

1. 查询Nancy Greenberg 的员工信息

   select * from employees where first_name = 'Nancy' and last_name = 'Greenberg';
   

2. 查询职位是AD_VP,并且在1990年前入职的员工信息

   select * from employees where job_id = 'AD_VP' and hire_date < '1990-1-1';
   

   select * from employees where job_id = 'AD_VP' and substr(round(hire_date,'month'),1,4) < 1990;
   

3. 查询月薪在10000-20000之间的且部门编号是90的员工信息

   select * from employees where (salary between 10000 and 20000) and department_id = 90;
   

4. 查询司龄高于20年的员工信息,按照月薪升序排列

   select * from employees where (year(curdate())-year(hire_date)) > 20 order by salary asc;
   ##
   select * from employees where date_sub(curdate(),interval 20 year) > hire_date order by salary asc;
   # oracle 特有函数:months_between()函数可以计算两个量之间的相差月数
   

5. 查询年收入高于25万的员工信息

   select * from employees where ((1 + ifnull(commission_pct,0)) * salary*12) > 250000; 
   
   select * from employees where ((salary + ifnull(commission_pct,0) * salary)*12) > 250000; 
   

6. 查询职位以SA开头的所有员工信息

   select * from employees where job_id like('SA%');
   
   select * from employees where substr(job_id,1,2) = 'SA';
   

7. 查询上司是145、146、147、149的所有员工信息

   select * from employees where manager_id in(145,146,147,149);
   

8. 查询公司的老板信息

   select * from employees where manager_id is null;
   

9. 查询不属于任何部门的员工信息

   select * from employees where department_id is null;
   

   • 分组练习题

10. 查询每个岗位的平均工资,最高工资和最低工资

    select job_id,avg(salary), max(salary),min(salary) from employees group by job_id;
    

11. 查询月薪高于8000的员工,按照岗位查询平均工资

    
    select avg(salary) from employees where salary > 8000 group by job_id;
    

12. 查询各部门,各个经理负责的员工的平均工资

    select  distinct department_id, manager_id ,avg(salary) from employees group by department_id,manager_id;
    ##
    select avg(salary) from employees group by department_id,manager_id;
    

13. 查询平均工资高于8000的岗位id和平均工资

    select job_id, avg(salary) from employees group by job_id having avg(salary) > 8000;
    

14. 查询司龄高于20年的员工的平均工资

    select avg(salary) from employees where (year(curdate()) - year(hire_date)) > 20;
    

15. 查询1995年之前入职员工的最高工资和最低工资

    select max(salary), min(salary) from employees where hire_date < '1995-1-1';
    

16. 查询各个部门的部门id和部门人数

    select department_id, count(*) from employees where department_id is not null group by department_id;
    

17. 查询各个部门部门id和月薪的总和

    select department_id, sum(salary) from employees where department_id is not null group by department_id;
    

18. 查询在1992-1993年间入职员工平均工资

    select avg(salary) from employees where hire_date < '1993-1-1' and hire_date >= '1992-1-1'; 
    ##
    select avg(salary) from employees where hire_date between '1992-1-1' and  '1993-12-31'; 
    

19. 查询各部门平均工资在8000元以上的部门id及平均工资。

    select department_id,avg(salary) from employees group by department_id having avg(salary) > 8000;
    

20. 查询工作编号中不含有“SA_”字符串及平均工资在8000元以上的工作编号及平均工资，并按平均工资 降序排序。

    select job_id,avg(salary) 
    from employees 
    where job_id not like 'SA/_%' escape '/' 
    group by job_id
    having avg(salary) > 8000
    order by avg(salary) desc;
    ####
    select job_id,avg(salary)
    from employees
    where instr(job_id,'SA_') = 0 ---可变的
    group by job_id
    having avg(salary) > 8000
    order by avg(salary) desc;
    

21. 查询工作不为AD_PRES，工资的和大于等于25000的工作编号和每种工作工资的和。

    select job_id,sum(salary)
    from employees
    where job_id not like 'AD/_PRES' escape '/'
    group by job_id
    having sum(salary) > 25000;
    ####
    select job_id,sum(salary)
    from employees
    where job_id <> 'AD_PRES'
    group by job_id
    having sum(salary) > 25000;
    

22. 显示经理号码，这个经理所管理员工的最低工资，不包括经理号为空的，不包括最低工资小于3000的，按最低工资由高到低排序。

    select manager_id,min(salary)
    from employees
    where manager_id is not null
    group by manager_id
    having min(salary) >=3000
    order by min(salary) desc;
    

23. 显示公司员工总工资及平均工资。

    select sum(salary),avg(salary) from employees;
    

24. 查询未分配部门的雇员信息。

    select * from employees where department_id is null;
    

25. 查找职位是FI_ACCOUNT或者工资超过16000的职位是AD_VP的员工信息。

    select * from employees where job_id = 'FI_ACCOUNT' or (job_id = 'AD_VP'and salary >16000);
    

26. 相同职位且经理相同的员工平均工资。

    select distinct job_id,manager_id ,avg(salary)
    from employees
    group by job_id, manager_id;