SELECT

• 查询表中某个字段（列）

  # 不写表名时会默认伪表
  select 1, 2; 
  # dual 就是伪表
  select 1, 2 from dual; 
  
  select first_name, last_name, salary
  from employees;

• 查询表中所有字段

  select * from employees

• 设置字段的别名

  # as 可以省略，但是不建议。
  select first_name as "f_n", last_name "l_n", salary "sal"
  from employees;
  
  # "" 可以省略
  select first_name as f_n, last_name as l_n, salary as sal
  from employees;
  
  # 支持中文（要设置编码格式为 utf-8）
  select first_name as f_n, last_name as l_n, salary as 薪资
  from employees;
  
  # 别名中有空格时尽量使用 ""，不要使用 ''
  select first_name as f_n, last_name as l_n, salary as "薪 资"
  from employees;

• 去除重复行

  # 将查询员工所属部门字段去重
  select distinct department_id
  from employees;
  
  # 报错，因为只有所属部分进行了去重，薪资和去重后的部门字段行数不一致。
  select salary, distinct department_id
  from employees;
  
  # 不报错，对薪资和部门这两个字段都相同的记录进行了去重。
  select distinct department_id, salary
  from employees;

• 空值参与运算

  # 年工资中会出现 null，因为有的员工奖金率是 null，null 参与运算时值为 null
  select employee_id, salary as "月工资", salary * (1 + commission_pct) * 12 as "年工资"
  from employees;
  
  # 如果是 null，就用 0 来替换
  select employee_id, salary as "月工资", salary * (1 + ifnull(commission_pct, 0)) * 12 as "年工资"
  from employees;

• 着重号

  # 报错，order 是关键字，字段或表明与这些关键字重复时会报错。
  select * from order;
  
  # 使用着重号解决
  select * from `order`;

• 查询常数

  # 给查询出来结果的添加一个 Google 字段，值都为 'Google'
  select 'Google', employee_id, first_name, last_name
  from employees;

• 显示表解构

  # 显示表中字段的详细信息
  describe employees;
  desc employees;

• 过滤数据

  # where 必须写在 from 后面
  
  # 查询指定部门的信息
  select * 
  from employees 
  where department_id = 90;
  
  # 查询指定 last_name 的信息
  # MySQL 中 'King' 可以写成 "King" 或 'king' 或 "king"，因为MySQL没有严格遵守ANSI标准
  # Oracle 中必须写 'King'
  select * 
  from employees 
  where last_name = 'King';