MYSQL学习之路——数据处理之查询

通过本章学习，您将可以：

• 基本的SELECT语句
• 过滤和排序数据
• 分组函数
• 分组查询
• 多表查询
• 分页查询

基本SELECT语句：

SELECT *|{[DISTINCT] column|expression}
FROM table;

SELECT 标识选择哪些列

FROM 表示从哪个表中选择

SELECT *   #*表示选择全部列
FROM departments;

SELECT department_id,location_id    #选择特定的列
FROM departments;

SQL语言大小写不敏感，SQL可以写在一行或者多行，关键字不能被缩写也不能分行，各子句一般要分行写。使用缩进提高语句的可读性。

日期和字符只能在单引号中出现

列的别名：

重命名一个列，能便于计算。

SELECT first_name AS "名字",salary "工资"   #去掉AS也可以 取别名
FROM employees;

使用DESCRIBE命令，显示表结构

DESCRIBE employees

过滤和排序数据

使用WHERE子句，将不满足条件的行过滤，WHERE子句紧跟随FROM子句

SELECT *|{[DISTINCT] column|expression[alias],...}
FROM table
[WHERE condition(s)];

其中还有一个不常用的<=>符号，表示可能等于，虽然可以用，但不推荐，通常使用<=> null

SELECT last_name,salary
FROM empoyees
WHERE salary <=3000;

BETWEEN..AND..的用法

SELECT last_name,salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;

IN的用法

SELECT employee_id,last_name,salary,manager_id
FROM employees
WHERE manager_id IN(100,101,201);

LIKE的用法

SELECT first_name
FROM employees
WHERE first_name LIKE 'S%';

NUL的用法

SELECT last_name,manager_id
FROM employees
WHERE manager_id IS NULL;

AND的语法

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary >=100
AND job_id LIKE '%MAN%';

OR的语法

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary >= 10000
OR job_id LIKE '%MAN%';

NOT的语法

SELECT last_name,job_id
FROM employees
WHERE job_id
NOT IN ('IT_PROG','ST_CLERK','SA_REP');

ORDER BY子句

• ASC（ascend）：升序
• DESC（descend）：降序

SELECT last_name,job_id,department_id,hire_date
FROM employees
ORDER BY hire_date;     #默认是升序，若想要改为降序则为ORDER BY hire_date DESC;

按别名排序

SELECT employee_id,last_name,salary*12 AS annsal
FROM employees
ORDER BY annsal;

多个列排序

SELECT last_name,department_id,salary
FROM employees
ORDER BY department_id,salary DESC;

分组函数

• AVG()求平均
• COUNT()计数
• MAX()最大值
• MIN()最小值
• SUM()求和

SELECT AVG(salary),MAX(salary),MIN(salary),SUM(salary)
FROM employees;

COUNT（计数）语法：

SELECT COUNT(*)     #也可以写作SELECT COUNT(1)
FROM employees
WHERE department_id =50;

SELECT COUNT(commission_pct)
FROM employees
WHERE department_id =80; #查询的依旧是depatment_id=80个数而不是commission_pct

分组数据：GROUP BY子句语法

SELECT
department_id,
AVG( salary ) 
FROM
employees 
GROUP BY
department_id;

在GROUP BY子句中包含多个列

SELECT department_id,job_id,SUM(salary)
FROM employees
GROUP BY department_id,job_id;

不可以再WHERE子句中使用组函数，可以在HAVING子句中使用组函数。

过滤分组：HAVING子句，加在GROUP BY之后

SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) >10000;

笛卡尔集

• 省略连接条件
• 连接条件无效
• 所有表中的所有行互相连接

为了彼岸笛卡尔集，可以再WHERE加入有效的链接条件。

等值链接

SELECT beauty.id,NAME,boyname 
FROM beauty,boys
WHERE beauty.boyfriend_id = boys.id; 

在表中有相同列时，再列名之前加上表明前缀

区分重复的列名

• 使用表名前缀在多个表中区分相同的列
• 在不同表中具有相同列名的列可以用表的别名加以区分
• 如果使用了表别名，则在select与剧中需要使用表别名代替表名
• 表别名最多支持32个字符长度，但建议越少越好。

SELECT b.name,bo.boyName
FROM boys bo,beauty b
WHERE b.boyfriend_id = bo.id;

SQL192语法，仅仅适用于内链接

#查询员工名和对应的工种名

SELECT first_name,job_title
FROM employees e,jobs j
WHERE e.job_id = j.job_id

#查询有奖金的员工名和部门名

SELECT first_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.department_id=d.department_id
AND commission_pct is not null;

#查询每个城市的部门个数

#查询每个城市的部门个数
SELECT count(*),city
from locations l,departments d
where l.location_id=d.location_id
GROUP BY city
ORDER BY count(*);

三表连接

#查询员工名、部门名、所在的城市

SELECT first_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id
AND d.location_id=l.location_id;

非等值连接

#查询员工的工资和工资级别

SELECT salary,grade_level,first_name
FROM employees e,job_grades j
WHERE salary BETWEEN j.lowest_sal AND j.highest_sal;

自连接

相当于等值连接

#查询员工名和上级的名称

SELECT e.first_name AS 员工,m.first_name AS 上级
FROM employees e,employees m
WHERE e.manager_id = m.employee_id;

SQL99：使用ON子句创建连接

内链接:inner join on 等值连接，非等值连接，自连接 （92、99语法都可以）

#查询女神名和对应的男神名(可对比之前的92语法，inner join 相当于","，on 相当于"where")

SELECT name,boyName
FROM beauty b
INNER JOIN boys bo
ON b.boyfriend_id=bo.id;

#查询员工名和对应的部门名

SELECT first_name,department_name
FROM employees e INNER JOIN departments d 
ON e.department_id=d.department_id;

外连接:left join on、right join on （仅仅99语法）

#查询女神名和对应的男神名（beauty表为主表）

SELECT name,boyName
FROM beauty b
LEFT JOIN boys bo 
ON b.boyfriend_id=bo.id;

SELECT name,boyName
FROM boys bo
RIGHT JOIN beauty b
ON b.boyfriend_id=bo.id;

#查询男朋友不在男神表的女神名

SELECT name 
FROM beauty b
LEFT JOIN boys bo
ON b.boyfriend_id =bo.id
WHERE boyName IS NULL;

#查询哪个部门没有员工

SELECT department_name,first_name
FROM departments d 
LEFT JOIN employees e
ON d.department_id = e.department_id
WHERE first_name IS NULL;

交叉连接:cross join （99语法去实现笛卡尔乘积，但要避免...作用不大）

SELECT name,boyName 
FROM beauty
CROSS JOIN boys;