廖雪峰SQL教程读书笔记（三）：查询数据

# 基本查询，SELECT查询结果是一个二维表
SELECT * FROM students;   # 从students表格中选取所有数据

SELECT 100+200   # 不带FROM可以直接运算得到300，用来检测当前数据库的连接是否有效
----------------------------------------------------------
# 条件查询，WHERE关键字
SELECT * FROM students WHRER score >= 80;   # score是列名

SELECT * FROM students WHERE score >= 80 AND gender = 'M'   # 条件可以有多个

SELECT * FROM students WHERE <条件1> OR <条件2>   # 或条件

SELECT * FROM students WHERE NOT <条件1>   # 非条件

SELECT * FROM students WHERE NOT class_id = 2

SELECT * FROM students WHERE class_id <> 2   # 两者等价

# 条件运算按照NOT > AND > OR的优先级进行

score = 80	name = 'abc'   # 判断相等

name > 'abc'   # 字符串比较根据ASCII码，中文字符比较根据数据库设置

name LIKE '%bc%'   # %表示任意字符，例如'ab%'将匹配'ab'，'abc'，'abcd'；%表示0个到多个，_表示1个
----------------------------------------------------------
# 投影查询
SELECT id, score, name FROM students;   # 选择指定列

SELECT id, score points, name FROM students;   # 选择制定列并改名，score改为points

SELECT id, score points, name FROM students WHERE gender = 'M';   # 组合使用
----------------------------------------------------------
# 排序
# 选择后默认按照主键排序，通过ORDER BY更改排序
SELECT id, name, gender, score FROM students ORDER BY score;   # 结果依据score列排序，从低到高顺序，默认ASC可省略

SELECT id, name, gender, score FROM students ORDER BY score DESC;   # DESC倒序

SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;   # 先按score倒序，再按gender顺序

SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;
# 先WHERE条件后ORDER BY排序
---------------------------------------------------------
# 分页查询，数据量太大，分页显示，本质就是截取数据LIMIT <M> OFFSET <N>
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;   # 三条记录，第一页，从0开始，LIMIT 3条

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;   # 从第三条记录开始，取3条记录，LIMIT总是设定为pageSize；OFFSET计算公式为pageSize * (pageIndex - 1)。

# OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0。
# 在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30 15。
---------------------------------------------------------
# 聚合查询，查询记录数量
SELECT COUNT(*) FROM students;   # 查询students表中有多少条记录，返回结果是一个二维表，列名是默认COUNT(*)。

SELECT COUNT(*) num FROM students;   # 设置返回结果列名为num

SELECT COUNT(*) boys FROM students WHERE gender = 'M';   # 叠加条件

# 更多函数
SUM	  # 计算某一列的合计值，该列必须为数值类型
AVG	  # 计算某一列的平均值，该列必须为数值类型
MAX	  # 计算某一列的最大值
MIN	  # 计算某一列的最小值
# 如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而MAX()、MIN()、MAX()和MIN()会返回NULL。MAX()和MIN()函数并不限于数值类型。如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符。

# 分组，GROUP BY
SELECT COUNT(*) num FROM students GROUP BY class_id;   # 先分组后计算
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;   # 可以多个列分组
------------------------------------------------------------
# 多表查询，又称笛卡尔查询，慎用因为返回记录数量太多
SELECT * FROM students, classes;   # 同时同students、classes表查询数据，返回二维表，是students表和classes表的“乘积”，即students表的每一行与classes表的每一行都两两拼在一起返回。结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;   # 多表查询时，要使用表名.列名这样的方式来引用列和设置别名

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;   # 增加WHERE条件
-------------------------------------------------------------
# 连续查询
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;   # 内连接 INNER JOIN
# 先确定主表，仍然使用FROM <表1>的语法；
# 再确定需要连接的表，使用INNER JOIN <表2>的语法；
# 然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
# 可选：加上WHERE子句、ORDER BY等子句。

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
LEFT OUTER JOIN classes c
ON s.class_id = c.id;   # 外连接

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
FULL OUTER JOIN classes c
ON s.class_id = c.id;   # 全连接

SELECT ... FROM tableA ??? JOIN tableB ON tableA.column1 = tableB.column2;   # 查询语句语法
------------------------------------------------

.
.
.
2019-01-04 18:37:55写于风雪交加夜