第七章 MySql-DQL命令

非等值连接

• 案例1. 查询员工的工资和工资级别

select
       salary "工资",
       grade_level "等级编号"
from
       employees e join job_grades j
where
       salary between lowest_sal and highest_sal;

非等值连接加条件筛选

• 案例1. 查询员工的工资和工资级别，要求只显示级别为A的

select salary "工资",
       grade_level "级别编号"
from 
       employees e join job_grades j
where 
       salary between lowest_sal and highest_sal and grade_level = 'A';

自连接

• 案例1. 查询员工名和上级的领导名称

select
    e.last_name "员工名称",
    m.last_name "领导名称"
from
    employees e join employees m
where
    e.manager_id = m.employee_id;

练习题

• 1.显示员工表的最大工资，工资平均值

select
       max(salary) "最大工资",
       avg(salary) "工资平均值"
from 
       employees;

• 查询员工表的employee_id,job_id,last_name,按department_id降序，salary升序

select 
      employee_id,
      job_id,
      last_name
from 
      employees
order by 
      department_id desc,
      salary asc ;

• 查询员工表的job_id中包含 a和e的，并且a 在e的前面

select
       job_id
from
       employees
where
      job_id like '%a%e%';

• 显示当前日期，以及去前后空格，截取子符串的函数

//获取当前系统时间
select now();
//去掉前后空格
select trim("   wewew    ");
//去掉指定字符 我的
select TRIM("我的" from "我的爱人")
//截取下标为2到末尾的字符为结果
select substr("我的爱人",2);
//截取下标从1到2的数据为结果
select substr("我的爱人",1,2);

连接查询练习题

• 1.显示所有员工的姓名，部门号和部门名称

select
       e.last_name,
       e.department_id,
       d.department_name
from
     employees e join departments d
where
      e.department_id = d.department_id;

• 2.查询90号部门员工的job_id和90号部门的location_id

select
       job_id,
       location_id,
       d.department_id
from
       employees e join departments d
where
       e.department_id=d.department_id
       and e.department_id = 90;

• 3.选择有奖金的这几个字段

select
       last_name,
       department_name,
       l.location_id,
       city
from
       employees e join departments d join locations l
where
       e.department_id = d.department_id
       and d.location_id = l.location_id
       and e.commission_pct is not null ;

• 4.选择city在Toronto工作的员工

select
       last_name,
       job_id,
       d.department_id,
       department_name
from
     employees e join departments d join locations l
where
      e.department_id = d.department_id
      and d.location_id = l.location_id
      and l.city = 'Toronto';

• 5.查询每个工种，每个部门的部门名，工种名和最低工资,按最低工资升序。

select
       department_name "部门名",
       job_title "工种名",
       min(salary) "最低工资"
from
       employees e join departments d join jobs j
where  e.department_id = d.department_id
       and e.job_id = j.job_id
group by
         department_name, job_title
order by min(salary) asc ;

• 6.查询每个国家下的部门个数大于2的国家编号

select
    country_id "国家编号",
    count(*) "个数"
from
    locations l join departments d
where
    l.location_id = d.location_id
group by
    country_id
having count(*)>2;