约束和排序数据

  二.约束和排序数据
     SELECT *|{[DISTINCT] column|expression[alias],...}
     FROM table [WHERE condition(s) ];

     1.SELECT employee_id, last_name, job_id, department_id
       FROM employees WHERE department_id = 90 ;
     2.SELECT last_name, job_id, department_id
       FROM employees WHERE last_name = 'Whalen';
 
     比较条件:
            > , >= , < , <= , <> (不等于), =
            BETWEEN...AND...  含义在两个值之间(包含)
            IN(set)           匹配一个任意值列表
            LIKE              匹配一个字符模板
            IS NULL           是一个空值
            IS NOT NULL       不是一个空值
     1.SELECT last_name, salary  FROM employeesWHERE salary <= 3000;
     2.SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500;
     3.SELECT employee_id, last_name, salary, manager_id
       FROM employees WHERE manager_id IN (100, 101, 201);
     4.% 表示零个或多个字符
       _ 表示一个字符
      SELECT  first_name  FROM employees   WHERE  first_name LIKE 'S%';
      SELECT last_name    FROM employees   WHERE last_name LIKE '_o%';
      SELECT last_name, manager_id   FROM employees  WHERE manager_id IS NULL;
    
     逻辑条件: AND  OR NOT
     1.SELECT employee_id, last_name, job_id, salary
        FROM employees
        WHERE salary >=10000  AND job_id LIKE '%MAN%';
     2.SELECT employee_id, last_name, job_id, salary
        FROM employees
        WHERE salary >= 10000  OR job_id LIKE '%MAN%';
     3.SELECT last_name, job_id
        FROM employees
        WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');
    
     4.SELECT * FROM detail
       WHERE ( detail.a,detail.b ) NOT IN ( SELECT order.a ,order.b FROM order )  //需要括号

     ORDER BY 子句:
        ASC: 升序排序,默认;
        DESC: 降序排序;
        ORDER BY子句在SELECT语句的最后
     1.SELECT last_name, job_id, department_id, hire_date
        FROM employees
        ORDER BY hire_date ;
     2.SELECT last_name, department_id, salary
        FROM employees
        ORDER BY department_id, salary DESC;
       
        总结:
        SELECT *|{[DISTINCT] column|expression[alias],...}
        FROM table
        [WHERE condition(s)]
        [ORDER BY {column, expr, alias} [ASC|DESC]];