一、基础的SELECT语句
- 最基本的SELECT语句:
- SELECT 字段1,字段2,... FROM 表名;
- *:表中的所有的字段(或列)
- 列的别名:
- SELECT 字段名 [as] "别名" from 表名;
- 列的别名,尽量使用双引号(""),而且不建议省略as
- 去除重复行:DISTINCT
- SELECT DISTINCT 字段名 FROM 表名;
- 空值参与运算:结果一定为null
- 空值:null,不等同于0,'','null'
- 如何用0来代替null:SELECT IFNULL(字段名,0) FROM 表名;
- 着重号:`
- 如果自定义的字段名、表名与关键字、保留字、数据库系统或常用方法冲突,请在SQL语句中使用`(着重号)引起来
- 查询常数
- SELECT 常数,字段名 FROM 表名;
- 显示表的结构:DESCRIBE或DESC
- DESCRIBE 表名;
- DESC 表名;
- 显示表中字段的详细信息
- 过滤数据:WHERE
- SELECT 字段名 FROM 表名 WHERE 过滤条件;
- WHERE子句声明在FROM的后面
二、排序与分页
2.1、排序
- 如果没有使用排序操作,默认情况下查询返回扽数据是按照添加数据的顺序显示的
- 使用 ORDER BY 子句排序:
- SELECT 字段名 FROM 表名 ORDER BY 排序字段1 ASC/DESC,排序字段2 ASC/DESC,...;
- ASC:升序
- DESC:降序
- 如果在 ORDER BY 后没有显示的指明排序的方式,则默认按升序排列
- 可以使用不在SELECT列表中的列排序。
- 在对多列进行排序的时候,首先排序的第一列必须有相同的列值,才会对第二列进行排序。如果第一列数据中所有值都是唯一的,将不再对第二列进行排序。
2.2、分页
- MySQL 使用 LIMIT 分页
- SELECT 字段名 FROM 表名 LIMIT 偏移量,条目数;
- 当偏移量等于0时,偏移量可以省略
- 分页显式公式:
- (当前页数 - 1) * 每页条数, 条目数
- MySQL 8.0 新特性:
- LIMIT 条目数 OFFSET 偏移量
三、多表查询
3.1 笛卡尔积(或交叉连接)
笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y,那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合,也就是第一个对象来自于 X,第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。SQL 92中,笛卡尔积也称为交叉连接,英文是 CROSS JOIN。在 SQL 99 中也是使用 CROSS JOIN表示交叉连接。它的作用就是可以把任意表进行连接,即使这两张表不相关。
笛卡尔积的错误会在下面条件下产生
- 省略多个表的连接条件(或关联条件)
- 连接条件(或关联条件)无效
- 所有表中的所有行互相连接
3.2、关联查询
- 前提条件:
- 这些一起查询的表之间是有联系的(一对一、一对多),它们之间一定是有关联字段,这个关联字段可能建立了外键,也可能没有建立外键。
- 等值连接 VS 非等值连接
- 等值连接:
- SELECT 字段名 FROM 表名1,表名2 WHERE 表名1.字段名1 = 表名2.字段名1;
- 非等值连接:
- SELECT 字段名 FROM 表名1,表名2 WHERE 非等值连接条件;
- 等值连接:
- 自连接 VS 非自连接
- 自连接:
- SELECT 字段名 FROM 表名1 别名1,表名1 别名2 WHERE 连接条件;
- 非自连接:
- SELECT 字段名 FROM 表名1,表名2 WHERE 连接条件;
- 自连接:
- 内连接 VS 外连接
- 内连接:合并具有同一列的两个以上的表的行,结果集中不包含一个表与另一个表不匹配的行
- SELECT 字段名 FROM 表名1,表名2 WHERE 连接条件; -- SQL92
- SELECT 字段名 FROM 表名1 [INNER] JOIN 表名2 ON 连接条件; -- SQL99
- 外连接:两个表在连接过程中除了返回满足连接条件的行以外还返回左(右)表中不满足条件的行,这种方式称为左(右)外连接,没有匹配的行时,结果表中相应的列为空(NULL)
- 左外连接:连接条件中左边的表称为主表,右边的表称为从表
- SELECT 字段名 FROM 表名1,表名2 WHERE 表名1.字段名1 = 表名2.字段名1(+); -- SQL92,MySQL不支持
- SELECT 字段名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 连接条件; -- SQL99
- 右外连接:连接条件中右边的表称为主表,左边的表称为从表
- SELECT 字段名 FROM 表名1,表名2 WHERE 表名1.字段名1(+) = 表名2.字段名1; -- SQL92,MySQL不支持
- SELECT 字段名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 连接条件; -- SQL99
- 满外连接:结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据
- SELECT 字段名 FROM 表名1 FULL [OUTER] JOIN 表名2 ON 连接条件; -- SQL99,MySQL不支持FULL
- 左外连接:连接条件中左边的表称为主表,右边的表称为从表
- 内连接:合并具有同一列的两个以上的表的行,结果集中不包含一个表与另一个表不匹配的行
- 在表中有相同列时,在列名之前加上表名前缀。
- 如果给表起了别名后,一旦在SELECT或WHERE中使用别名的话,则必须使用表的别名,而不能使用表的原名
- 如果有n个表实现多表的查询,则至少需要n-1个连接条件
3.3、UNINO的使用
UNION关键字,可以给出多条SELECT语句,并将它们的结果组合成单个结果集。合并时,两个表对应的列数和数据类型必须相同,并且相会对应。各个SELECT语句之间使用UNION或UNION ALL关键字分分隔。UNION操作符返回两个查询的结果集的并集,去除重复记录。UNION ALL操作符返回两个查询的结果集的并集,对于两个结果集的重复部分,不去重。
语法格式:
SELECT 字段名1,字段名2,...
FROM 表名1
UNION
SELECT 字段名1,字段名2,...
FROM 表名2;
SELECT 字段名1,字段名2,...
FROM 表名1
UNION ALL
SELECT 字段名1,字段名2,...
FROM 表名2;
注意:执行UNION ALL语句时所需要的资源比UNINO语句。如果明确知道合并数据后的结果不存在重复数据,或者不需要去除重复的数据,则尽量使用UNINO ALL语句,以提高数据查询的效率。
3.4 7种SQL JOIN的实现
-- 中图:内连接
SELECT 字段名
FROM 表名1 [INNER] JOIN 表名2
ON 表名1.字段名1 = 表名2.字段名1;
-- 左上图:左外连接
SELECT 字段名
FROM 表名1 LEFT [OUTER] JOIN 表名2
ON 表名1.字段名1 = 表名2.字段名1;
-- 右上图:右外连接
SELECT 字段名
FROM 表名1 RIGHT [OUTER] JOIN 表名2
ON 表名1.字段名1 = 表名2.字段名1;
-- 左中图
SELECT 字段名
FROM 表名1 LEFT [OUTER] JOIN 表名2
ON 表名1.字段名1 = 表名2.字段名1
WHERE 表名1.字段名1 IS NULL;
-- 右中图
SELECT 字段名
FROM 表名1 RIGHT [OUTER] JOIN 表名2
ON 表名1.字段名1 = 表名2.字段名1
WHERE 表名2.字段名1 IS NULL;
-- 左下图:满外连接
-- 方式一:左上图 UNION ALL 右中图
SELECT 字段名1,字段名2,...
FROM 表名1 LEFT [OUTER] JOIN 表名2
ON 表名1.字段名3 = 表名2.字段名3;
UNION ALL
SELECT 字段名1,字段名2,...
FROM 表名1 RIGHT [OUTER] JOIN 表名2
ON 表名1.字段名3 = 表名2.字段名3
WHERE 表名2.字段名3 IS NULL;
-- 方式二:左中图 UNION ALL右上图
SELECT 字段名1,字段名2,...
FROM 表名1 LEFT [OUTER] JOIN 表名2
ON 表名1.字段名3 = 表名2.字段名3;
WHERE 表名1.字段名3 IS NULL;
UNION ALL
SELECT 字段名1,字段名2,...
FROM 表名1 RIGHT [OUTER] JOIN 表名2
ON 表名1.字段名3 = 表名2.字段名3
-- 右下图:左中图 UNION ALL 右中图
SELECT 字段名1,字段名2,...
FROM 表名1 LEFT [OUTER] JOIN 表名2
ON 表名1.字段名3 = 表名2.字段名3;
WHERE 表名1.字段名3 IS NULL;
UNION ALL
SELECT 字段名1,字段名2,...
FROM 表名1 RIGHT [OUTER] JOIN 表名2
ON 表名1.字段名3 = 表名2.字段名3
WHERE 表名2.字段名3 IS NULL;
3.5、SQL99语法的新特性
3.5.1、自然连接
NATURAL JOIN 用来表示自然连接,它会自动帮你查询两张连接表中的 所有相同的字段 ,然会进行 等值连接。
语法:
SELECT 字段名
FROM 表名1 NATURAL JOIN 表名2;
3.5.2、USING连接
SQL99还支持使用 USING 指定数据表里的同名字段
进行等值连接,但是只能配合JOIN一起使用。USING 指定了具体的相同的字段名称,你需要在 USING 的括号 () 中填入要指定的同名字段。
语法:
SELECT 字段名
FROM 表名1 JOIN 表名2
USING (共同的字段名);