数据处理之查询分为以下几个步骤进行学习:
● 基本的SELECT语句
● 过滤和排序数据
● 分组函数
● 分组查询
● 多表查询
(1)基本的SELECT语句
• SELECT 标识选择哪些列
• FROM 标识从哪个表中选择
选择全部列
SELECT *
FROM transportation;
选择特定列
SELECT name, id
FROM transportation;
注意 |
---|
SQL 语言大小写不敏感 |
SQL 可以写在一行或者多行 |
关键字不能被缩写也不能分行 |
各子句一般要分行写 |
使用缩进提高语句的可读性 |
列的别名
• 重命名一个列
• 便于计算
• 紧跟列名, 也可以在列名和别名之间加入关键字‘AS’ ,别名使用双引号, 以便在别名中包含空格或特殊的字符并区分大小写
用AS重命名:
SELECT name AS N, id AS I
FROM transportation;
用" "重命名:
SELECT name "N", salary*12 "Annual Salary"
FROM transportation;
显示表的结构
DESCRIBE transportation
(2)过滤和排序数据
过滤
• 使用WHERE子句,将不满足条件的行过滤掉
SELECT *
FROM transportation
WHERE salary>8000;
操作符 | 含义 |
---|---|
== | 等于(不是==) |
> | 大于 |
>= | 大于、等于 |
< | 小于 |
<= | 小于、等于 |
<> | 不等于(也可以是!=) |
操作符 | 含义 |
---|---|
BETWEEN…AND… | 在两个值之间 (包含边界) |
IN(set) | 等于值列表中的一个 |
LIKE | 模糊查询 |
IS NULL | 空值 |
BETWEEN
使用 BETWEEN 运算来显示在一个区间内的值(闭区间)
SELECT *
FROM transportation
WHERE salary BETWEEN 8000 AND 9000;
IN
使用 IN运算显示列表中的值
SELECT *
FROM transportation
WHERE id IN (1,2);
LIKE
• 使用 LIKE 运算选择类似的值
• 选择条件可以包含字符或数字:
– % 代表零个或多个字符(任意个字符)
– _ 代表一个字符
SELECT name
FROM transportation
WHERE name LIKE '小%';
SELECT name
FROM transportation
WHERE name LIKE '_陈';
NULL
使用 IS (NOT) NULL 判断空值
SELECT name
FROM transportation
WHERE id IS NOT NULL;
操作符 | 含义 |
---|---|
AND | 逻辑并 |
OR | 逻辑或 |
NOT | 逻辑否 |
AND
AND 要求并的关系为真
SELECT *
FROM transportation
WHERE salary>8000
AND id = 3;
OR
OR 要求或关系为真
SELECT *
FROM transportation
WHERE salary>8000
OR id = 1;
NOT
字面含义理解为:“非”
SELECT *
FROM transportation
WHERE id
NOT IN(1,2);
排序
ORDER BY子句
• 使用 ORDER BY 子句排序
– ASC(ascend) : 升序
– DESC(descend) : 降序
• ORDER BY 子句在SELECT语句的结尾
SELECT *
FROM transportation
WHERE salary BETWEEN 8000 AND 9000
ORDER BY id DESC;
(3)分组函数
分组函数作用于一组数据,并对一组数据返回一个值
类型 | 含义 |
---|---|
AVG() | 求平均数 |
COUNT() | 计数 |
MAX() | 求最大值 |
MIN() | 求最小值 |
SUM() | 求和 |
AVG(平均数)、SUM(合计)
可以对数值型数据使用AVG 和 SUM 函数
SELECT AVG(salary),SUM(salary)
FROM transportation
WHERE id IN(1,2,3);
MAX(最大值)、MIN(最小值)
可以对任意类型数据使用 MIN 和 MAX 函数
SELECT MAX(salary),MIN(salary)
FROM transportation;
COUNT(计数)
COUNT(*) 返回表中记录总数,适用于任意类型数据
SELECT count(*)
FROM transportation
WHERE id = 1;
COUNT(expr) 返回expr不为空的记录总数
SELECT COUNT(name)
FROM transportation
WHERE id = 1;
(4)分组查询
GROUP BY 子句
在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中
SELECT id, AVG(salary)
FROM transportation
GROUP BY id;
包含在 GROUP BY 子句中的列不必包含在SELECT 列表中
SELECT AVG(salary)
FROM transportation
GROUP BY id;
使用多个列分组
在GROUP BY子句中包含多个列
SELECT id, name, SUM(salary)
FROM transportation
GROUP BY id, name;
非法使用组函数
• 不能在 WHERE 子句中使用组函数
• 可以在 HAVING 子句中使用组函数
过滤分组:HAVING 子句
使用 HAVING 过滤分组:
- 行已经被分组
- 使用了组函数
- 满足HAVING 子句中条件的分组将被显示
SELECT id, name, SUM(salary)
FROM transportation
GROUP BY id, name
HAVING SUM(salary)>=9000;
(5)多表查询
笛卡尔集的错误情况:
select count(*) from transportation;
输出4行
select count(*) from car;
输出4行
最终输出结果:4*4=16行
SELECT COUNT(*)
FROM transportation,car;
笛卡尔集会在下面条件下产生:
– 省略连接条件
– 连接条件无效
– 所有表中的所有行互相连接
• 为了避免笛卡尔集, 可以在 WHERE 加入有效的连接条件
• 在 WHERE 子句中写入连接条件
• 在表中有相同列时,在列名之前加上表名前缀
SELECT name,carName FROM transportation,car
WHERE transportation.id = car.id;
表的别名
• 使用别名可以简化查询
• 使用表名前缀可以提高执行效率
SELECT `name`,carName
FROM transportation ts,car c
WHERE ts.id = c.id;
连接多个表
• 连接 n个表,至少需要 n-1个连接条件。 例如:连接三个表,至少需要两个连接条件。
join连接
• 分类:
– 内连接 [inner] join on
– 外连接
左外连接 left [outer] join on
右外连接 right [outer] join on
使用ON 子句创建连接
• 自然连接中是以具有相同名字的列为连接条件的
• 可以使用 ON 子句指定额外的连接条件
• 这个连接条件是与其它条件分开的
• ON 子句使语句具有更高的易读性
使用 ON 子句创建多表连接:
内连接:
SELECT `name`,carName,transportation_salary AS ts_salary,car_salary AS c_salary
FROM transportation ts
INNER JOIN car c
ON ts.id = c.id;
左外连接:
SELECT `name`,carName,transportation_salary AS ts_salary,car_salary AS c_salary
FROM transportation ts
LEFT JOIN car c
ON ts.id = c.id;
右外连接:
SELECT `name`,carName,transportation_salary AS ts_salary,car_salary AS c_salary
FROM transportation ts
RIGHT JOIN car c
ON ts.id = c.id;
join连接总结
(1)左外连接
SELECT <select_list>
FROM A
LEFT JOIN B
ON A.key=B.key
(2)内连接
SELECT <select_list>
FROM A
INNER JOIN B
ON A.key=B.key
(3)右外连接
SELECT <select_list>
FROM A
RIGHT JOIN B
ON A.key=B.key