select 条件查询 与 排序

#基础查询
/*
语法：
select 查询列表 from 表名

查询列表可以是：表中的字段 常量值 表达式 函数
查询的结果是一个虚拟的表格
*/
#F12格式化

USE myemployees;

#字段
SELECT 
  * 
FROM
  employees ;

# 着重号 以和关键字区分
# select `name` from student;

#常量值
SELECT 100;

SELECT 'join';

#表达式
SELECT 100*9;

#函数
SELECT VERSION();


#别名
SELECT 100*89 AS result;  //as 
SELECT 12*12 result;         //空格
SELECT salary AS `out put` FROM employees;

#去重
#案例：查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM employees;

# +号
/*
 只有运算符的功能
 select 100+90;
 select '123'+90; 其中一方为字符型，视图将字符型数值转换成数值型
                  如果转换成功，则继续做加法运算
 select 'join'+90                如果转换失败，则将字符型数值转换成0
                 
 select null+10   只要其中一方为null，则结果为肯定为null
*/
# 查询员工名和姓连接成一个字段，并且显示为姓名
SELECT  CONCAT(last_name,' ',first_name) AS `name` FROM employees;

#条件查询
/*
  select 
    查询列表
  from
    表名
  where
    筛选条件
    
分类：
   1；按条件表达式筛选
   条件运算符： > < = <> >= <=
   
   2.按逻辑表达式
   逻辑运算符: && || !
               and or not (推荐)
  not: 如果连接的条件本身为false，结果为true
   
   3.  模糊查询
   like
    一般和通配符搭配使用
    %：表示任意个或多个字符。可匹配任意类型和长度的字符。
    _：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句：（可以代表一个中文字符）
    escape: 如果我就真的要查%或者_.使用escape，转义字符后面的%或_就不作为通配符了，
        注意前面没有转义字符的%和_仍然起通配符作用
    
   between and 
   in 
   is null
   
*/

#案例：查询工资>12000员工信息
SELECT * FROM employees
WHERE salary>12000;

#案例：部门编号不等于90的员工名和部门编号
SELECT 
  last_name,
  department_id 
FROM
  employees 
 WHERE 
   department_id!=90;
   
SELECT 
  last_name,
  department_id 
FROM
  employees 
WHERE department_id <> 90 ;

#案例 查询工资再10000 到 20000之间的员工名 工资及奖金
SELECT 
  last_name,
  salary,
  commission_pct 
FROM
  employees 
WHERE salary >= 10000 
  AND salary <= 20000 ;

#案例查询部门编号不在90-100之间或者工资高于15000的员工信息
SELECT 
  * 
FROM
  employees 
WHERE NOT (
    department_id >= 90 
    AND department_id <= 110
  ) 
  OR salary > 15000 ;
  
SELECT 
  * 
FROM
  employees 
WHERE 
    department_id < 90 
    OR department_id > 110 
  OR salary > 15000 ;

#like
/*
一般和通配符搭配使用
    %：表示任意个或多个字符。可匹配任意类型和长度的字符。
    _：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句：
              （可以代表一个中文字符）
    escape: 如果我就真的要查%或者_.使用escape，转义字符后面的%或_就不作为通配符了，
        注意前面没有转义字符的%和_仍然起通配符作用
      like也可用筛选数值型
*/
#案例 员工名称包含字符a的员工信息
SELECT
    * 
FROM
    employees
WHERE
    last_name LIKE '%a%';
#查看员工名中第三个字符为e,第5个字符为a的员工名和工资
SELECT 
  last_name,
  salary 
FROM
  employees 
WHERE last_name LIKE '__e_a%' ;

SELECT 
  last_name,
  salary 
FROM
  employees 
WHERE last_name LIKE '_a%' ;


#案例 查询员工名中第二个字符为_员工名

SELECT
    last_name
FROM 
    employees
WHERE
    last_name LIKE '_\_%';

SELECT
    last_name
FROM 
    employees
WHERE
    last_name LIKE '_$_%' ESCAPE '$';

#
SELECT
    * 
FROM
    employees
WHERE
    department_id LIKE '1__';

# BETWEEN  AND
/*
1. 可提高语句的简洁度
2. 包含临界值
3. 两个临界值不可调换顺序
*/

# 查询员工编号在100-120之间的员工信息
SELECT
    *
FROM
    employees
WHERE
    department_id >= 100 AND department_id<=120;
    
SELECT
    *
FROM
    employees
WHERE
    department_id BETWEEN 100 AND 120;


# in
#判断某字段的值是否属于in列表中的某一项
/*
1. 可提高语句简洁度
2. in列表的值类型必须统一或兼容
3. 不支持通配符
*/

#案例： 查询员工的工种编号是IT_PROG AD_PRES AD_VP中的员工名和工种编号
SELECT
    last_name,
    job_id
FROM employees
WHERE
    job_id = 'IT_PROG' OR job_id = 'AD_VP' OR job_id = 'AD_PRES';

SELECT
    last_name,
    job_id
FROM employees
WHERE
    job_id IN ('IT_PROG','AD_PRES','AD_VP');


#is null
/*
    = 或<>不能用于判断null值
    is null 或 is not null可以判断null值
*/
#查询没有奖金的员工名和奖金率
SELECT
    last_name,
    commission_pct
FROM
    employees
WHERE
    commission_pct IS NULL;
    
SELECT
    last_name,
    commission_pct
FROM
    employees
WHERE
    commission_pct IS NOT NULL;
    
    
# 安全等于 <=>
/*
is null: 仅仅可以判断null值， 可读性较高，建议使用
<=>： 既可以判断null值，也可以判断普通数值，可读性低
*/
SELECT
    last_name,
    commission_pct
FROM
    employees
WHERE
    commission_pct <=> NULL;

#查询工资为12000员工信息
SELECT
    last_name,
    salary
FROM
    employees
WHERE
    salary <=> 12000;


#查询没有奖金且工资小于18000的salary last_name
SELECT
    last_name,
    salary
FROM employees
WHERE
    commission_pct IS NULL AND salary<18000;
    
# 查询employees表中，jod_id不为it 或工资为12000的员工信息
SELECT 
  * 
FROM
  employees 
WHERE job_id <> 'IT' 
  OR salary = 12000 ;

SELECT
    *
FROM
    employees
WHERE
    NOT(job_id = 'IT') OR salary=12000;

#查询departments表中涉及到了那些位置编号
SELECT
    DISTINCT location_id
FROM 
    departments;

#有null值不在结果中
SELECT
    *
FROM 
    employees
WHERE
    commission_pct LIKE '%%' AND last_name LIKE '%%';


#查询员工号为176的员工姓名 部门号 年薪
SELECT 
    e.`last_name`,e.`department_id`,e.`salary`*12*(1+ IFNULL(e.`commission_pct`,0)) AS money
FROM
  employees AS e 
WHERE 
    e.employee_id=176;


# 查询没有奖金且工资小于18000
SELECT 
    e.`salary`,e.`last_name`
FROM
  employees AS e
WHERE
  e.`salary`<18000 AND e.`commission_pct` IS NULL;
 
# 查询job_id不为it,工资为12000的员工信息 
SELECT * 
FROM
  employees AS e 
WHERE e.`job_id` <> 'IT' 
  AND e.`salary` = 12000 ;

SELECT 
  e.`commission_pct`
FROM
  employees AS e 
WHERE
  e.`commission_pct` LIKE '%%'

#3. 排序查询
/*
SELECT 查询列表
FROM 表
[where 筛选条件]
order by 排序列表[asc desc]

默认升序
一般放在查询语句最后面，limit子句除外
*/

#查询员工信息，要求工资从高到底排序
SELECT
    *
FROM
    employees
ORDER BY salary ASC;

# 查询部门编号>=90的员工信息，按入职时间的先后进行
SELECT 
    *
FROM
    employees
WHERE
    department_id>=90
ORDER BY hiredate ASC;

#按表达式排序 ：按年薪的高低显示员工的信息和年薪
SELECT
    *,
    salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM
    employees
ORDER BY 年薪 DESC;

#按函数排序：按姓名的长度显示员工的姓名和工资
SELECT
    last_name,
    salary
FROM 
    employees
ORDER BY LENGTH(last_name) DESC;

#按多个字段排序： 查询员工信息，要求先按工资排序 升序，再按员工编号排序 降序
SELECT
    *
FROM
    employees
ORDER BY salary ASC, employee_id DESC;

#查询员工的姓名 部门号 年薪，按年薪降序， 按姓名升序
SELECT
    last_name,
    department_id,
    salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM 
    employees
ORDER BY 
    年薪 DESC, last_name ASC;

#选择工资不在8000 到 17000的员工姓名和工资，按工资降序
SELECT
    last_name,
    salary
FROM 
    employees
WHERE
    salary NOT BETWEEN 8000 AND 17000
ORDER BY
    salary DESC;


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