MySQL 之 DML-1

数据操纵语言(Data Manipulation Language 或 Data Modification Language,DML)包括SELECT,INSERT,UPDATE,DELETE语句。

一.根据不同查询条件查询：

1.过滤

     过滤：使用where语句，将不满足条件的语句过滤掉.

     ①．在过滤当中常用到的比较运算符

     BETWEEN...AND...：在两个值之间，包括边界

     IN（set）：包含值中的一个

     LIKE：模糊查询

     % 代表零个或多个字符(任意个字符)。

     _ 代表一个字符

     IS NULL : 空值

2.排序：

     ②ORDER BY子句：ORDER BY子句在where句之后 使用 ORDER BY 子句排序。

ASC（ascend）: 升序（默认）

DESC（descend）: 降序

说明：去除重复行：在列的前面加DISTINCT，但是为了提高查询效率，可以使用exists代替distinct.

SELECT DISTINCT d.department_id,department_name FROM departments d,employees e WHERE d.department_id=e.department_id;

SELECT department_id,department_name FROM departments d WHERE EXISTS(SELECT department_id FROM employees e WHERE e.department_id=d.department_id);

3.分组:  MAX   MIN   AVG  SUM  COUNT(group by 分组)

     求出EMPLOYEES 表中各部门的平均工资

select  avg(salary)

from  employees

group  by  department_id;

说明：出现select子句中的非分组函数，必须出现在group by 子句后。

4.过滤(having)

select avg(salary)

from employees

group by department_id

having avg(salary) >= 8000;

说明：where 子句后不能使用分组函数，需要使用 having，一般情况下，having写在group by后面

5.条件表达式

-case - when - then 表达式(mysql,oracle数据库都可以使用)

select employee_id, last_name, salary, department_id, case department_id when 10 then salary * 1.1

                                                  when 20 then salary * 1.2

                                                  when 30 then salary * 1.3

                                                   end   "new_salary"

from employees

where department_id in (10, 20, 30);

-decode 函数(只适用于oracle数据库)

select employee_id, last_name, salary, department_id, decode(department_id, 10, salary * 1.1

                                                         , 20, salary * 1.2

                                                         , 30, salary * 1.3

                                                         ,salary * 1.4)  " new_salary"

from employees;

6.去除重复行

在列的前面加DISTINCT，但是为了提高查询效率，可以使用exists代替distinct,执行效率会更高。

    用distinct实现如下：

SELECT DISTINCT d.department_id,department_name FROM departments d,employees e WHERE d.department_id=e.department_id;

    这样写的sql语句执行过程是：先遍历执行部门表中的每一行，因为每一行对应一个department_id,然后在固定department_id的情况下遍历employees表中的每一行，直到运行到employees表的最后一行。

然后在换下一个department_id进行查找，又要遍历employees表中的每一行，以此类推，直到所有的department_id都比较过。

    用exists实现如下：

SELECT department_id,department_name FROM departments d WHERE EXISTS(SELECT department_id FROM employees e WHERE e.department_id=d.department_id);

    首先说明一下exists的工作原理，exists语句用来判断()内的表达式是否存在返回值，如果存在就返回true，如果不存在就返回false，所以在上面语句中我们使用select null,因为返回什么数据不重要，重要有值返回就行。

另外exists的有点是，它只要括号中的表达式有一个值存在，就立刻返回true，而不用遍历表中所有的数据。

所以在department_id同样多的情况下，用distinct需要每次都遍历employees表进行比对，而使用exists只需要比对employees表的一部分，在employees表数据十分庞大时，这种性能差别就能更好的体现出来。

7.Limit :限制查询结果返回的数量

SELECT * FROM employees ORDER BY employee_id DESC LIMIT 2,2;

说明：如果Limit后面跟两个参数，就第一个参数表示从第几行开始，第二个参数表示从该行的第一个参数开始，查询几条。    

          如果Limit后面跟一个参数，就表示返回前几条记录数。

二.子查询：

单行子查询：Equal to：=

Greater than ：>

Greater than or equal to:>=

Less than :<

Less than or equal to:<=

Not equal to:<>

多行子查询：等于列表中的任意一个：in

和子查询返回的某一个值比较：any

和子查询返回的所有值比较：All

1.使用比较运算符的子查询：=,>,<,>=,<=,<>,<=>

例如：

  查询所有价格大于平均价格的商品，并且按价格降序排序

  SELECT goods_id,goods_name,goods_price FROM tdb_goods

  WHERE goods_price > (SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods)

  ORDER BY goods_price DESC;

   查询价格大于或等于"超级本"价格的商品，并且按价格降序排列

   SELECT goods_id,goods_name,goods_price FROM tdb_goods

   WHERE goods_price = ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本')

   ORDER BY goods_price DESC;

     说明：如果子查询中返回多个值就使用any，some，all，他们的区别是：any和some是满足任意一个就可以，而All是需要满足所有条件。

2.使用in/ not in 的子查询：

= ANY 或 = SOME 等价于 IN

SELECT goods_id,goods_name,goods_price FROM tdb_goods

WHERE goods_price IN (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本')

ORDER BY goods_price DESC;

三.多表连接：

1.等值连接 - 非等值连接

2.内连接 - 外连接

3.自连接 - 非自连接

(1). 等值连接  -  非等值连接

等值连接

select employee_id, department_name

from employees, departments

where employees.department_id = departments.department_id;

非等值连接

SELECT employee_id, last_name, salary, grade_level

FROM employees e, job_grades j

WHERE e.`salary` BETWEEN j.lowest_sal AND j.highest_sal

② 内连接  -  外连接

左外连接：不但将多个表中满足条件的数据查出，还将左表中不满足条件的数据查询出来

SELECT employee_id, department_name

FROM employees e

LEFT OUTER JOIN departments d

ON e.department_id = d.department_id;

右外连接：不但将多个表中满足条件的数据查出，还将右表中不满足条件的数据查询出来

SELECT employee_id, department_name

FROM employees e

RIGHT JOIN departments d

ON e.department_id = d.department_id;

内连接： join ... on ... （重要）

SELECT employee_id, e.department_id, department_name

FROM employees e

INNER JOIN departments d -- inner 可以省略不写

ON e.department_id = d.department_id;

满外连接：oracle 中支持 满外连接但 mysql 不支持。 oracle中的满外链接： full outer join

SELECT employee_id, department_name

FROM employees e

FULL JOIN departments d

ON e.department_id = d.department_id;

③． 自连接  -  非自连接

自连接  Xxx works for xxx

SELECT employee_id, last_name, manager_id

FROM employees;

# xxx works for xxx

SELECT CONCAT(emp.last_name, ' works for ' , mgr.last_name)

FROM employees emp, employees mgr

WHERE emp.`manager_id` = mgr.`employee_id`;

 说明：如果需要以上操作sql的创建语句和数据，请： http://download.csdn.net/download/luomingkui1109/10228754