MySQL 学习笔记

1.清除重复行

　　使用DISTINCT关键字可从查询结果中清除重复行

　　　　

SELECT DISTINCT department_id FROM employees;

 

　　DISTINCT的作用范围是后面所有字段的组合

　　　　

SELECT DISTINCT department_id , job_id FROM employees;

 

 

2.WHERE中的字符串和日期值

　　字符串和日期要用单引号扩起来

　　字符串是大小写敏感的,日期值是格式敏感的

SELECT last_name, job_id, department_id
        FROM employees
             WHERE last_name = ‘KING';                            

 

3.WHERE中的比较运算符

SELECT last_name, salary, commission_pct
    FROM employees
    WHERE salary<=1500;

 

4.使用BETWEEN运算符显示某一值域范围的记录

SELECT last_name, salary
     FROM employees
     WHERE salary BETWEEN 1000 AND 1500;

 

 5.使用IN运算符获得匹配列表值的记录

SELEC Temployee_id, last_name, salary, manager_id
    FROM employees
    WHERE manager_id IN (7902, 7566, 7788);

 

6.使用LIKE运算符执行模糊查询

　　 查询条件可包含文字字符或数字， (%) 可表示零或多个字符， (_)可表示一个字符。

SELECT last_name
     FROM employees
     WHERE last_name LIKE '_A%';

 

7.逻辑运算符

7.1使用AND运算符

　　AND需要所有条件都是满足T　　

SELECT employee_id, last_name, job_id, salary
    FROM employees
    WHERE salary>=1100–4 AND job_id='CLERK';

7.2使用OR运算符

　　OR只要两个条件满足一个就可以

SELECT employee_id, last_name, job_id, salary
    FROM employees
    WHERE salary>=1100 OR job_id='CLERK';

7.3使用NOT运算符

NOT是取反的意思

SELECT last_name, job_id
    FROM employees
    WHERE job_id  NOT IN ('CLERK','MANAGER','ANALYST');

7.4优先级规则

SELECT dept_id, salary
    FROM employees
    WHERE dept_id=1
        OR dept_id=2
        AND salary>1500;

8.排序查询  order by

参数ASC，表示按照升序进行排序 

参数DESC，表示按照降序进行排序 

默认情况下，按照ASC方式，升序进行排序

select * from employees order by gender,employee_id ASC;  /*升序查询*/
select * from employees order by employee_id DESC;  /*降序查询*/

9.子查询

SELECT
    t.NAME
FROM
    depts  t
WHERE
    t.dept_id = (
        SELECT
            t1.dept_id
        FROM
            (select * from employees) t1
        WHERE
            t1.employee_id = 6
    );