数据操纵语言(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`;