SQL学习日记（四）查询语句

基本查询
    SELECT * FROM <表名>

SELECT 关键字，表示执行查询
* 表示所有列
FROM   表示从哪个表开始查询

该语句将查询出所查表的所有数据，以二维表的形式显示
SELECT语句并不要求一定有 FROM子句 ，也可以实现一些简单计算
 SELECT 100+200
会直接计算出结果，sql可以计算，但只是辅助功能
不带FROM的SELECT语句可以用于测试当前到数据库的连接是否有效。

条件查询
SELECT * FROM <表名> WHERE <条件表达式>
因为基本查询是将表中所有数据全部列出，过于繁杂，为了筛选，可以使用WHERE条件设定过滤条件
例如查询80分以上学生
SELECT * FROM students WHERE score >= 80
score：列名
WHERE后的部分：条件
条件表达式中可以用<条件一> AND <条件二>
若某列为字符串将字符串用''括起来

也可以用<条件一> OR <条件二>查询 两者条件中满足任意一条的

还有一种是 NOT<条件>即 查询不满足条件的

如果不加括号优先级按NOT>AND>OR的优先级来排序

常用的条件表达式
条件    表达式举例1    表达式举例2    说明
使用=判断相等    score = 80    name = 'abc'    字符串需要用单引号括起来
使用>判断大于    score > 80    name > 'abc'    字符串比较根据ASCII码，中文字符比较根据数据库设置
使用>=判断大于或相等    score >= 80    name >= 'abc'    
使用<判断小于    score < 80    name <= 'abc'    
使用<=判断小于或相等    score <= 80    name <= 'abc'    
使用<>判断不相等    score <> 80    name <> 'abc'    
使用LIKE判断相似    name LIKE 'ab%'    name LIKE '%bc%'    %表示任意字符，例如'ab%'将匹配'ab'，'abc'，'abcd'

投影查询
由已知，条件查询中返回到的二维表与原表结构是相同的，即结果集的所有列与原表所有列一一对应
但是我们只希望返回某些列的数据，而不是所有列的数据，我们可以用

SELECT 列一, 列二, 列三 FROM...

让结果只包含指定列，该操作称为投影查询

例从students 表中返回id score name

SELECT id, score, name FROM students;

结果只包含了我们指定的列，且结果顺序可以自定义

除此，还可以给返回的列改名

SELECT 列一 别名一, 列二 别名二, 列三 别名三 FROM ...
投影查询也可以接WHERE条件实现更复杂的查询

排序
SELECT查询时查询结果通常是按主键进行排序的，如果想按其他顺序排序可以在查询语句结尾加上
ORDER BY <列名>//默认按从低到高排序(ASC)
如果要反过来是
ORDER BY <列名> DESC
若该列有相同数据，要进一步排序，可以继续添加列名
ORDER BY <1> DESC, <2>表示 先按1从高到低排序，若有相同的再按2排序

如果有WHERE子句，那么ORDER BY 子句要放到 WHERE子句后//排序放在查询语句后

分页查询
如果数据过大，放在一个页面不易观察，可以使用分页语句，在排序后加上
LIMIT<pagesize> OFFSET <pageindex> //<pagesize>表示一页最多几条记录，M表示第几条 从 0 开始
LIMIT=pagesize
OFFSET=pagesize * （pageindex-1）

OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集。

在mysql中，LIMIT XX OFFSET XX 可以简写成 LIMIT XX, XX
但是在pageindex越大时，查询效率会越低

聚合查询
统计查询结果总数，平均数等计算，sql有专门函数表示
例如查询<1>表中一共多少记录

SELECT COUNT(*) FROM <1>;

COUNT(*)表示查询所有列的行数，结果虽然是一个数字，但是其查询的结果本质是一个二维表，只是该二维表为一行一列，名为COUNT(*)
所以一般会设置一个别名

SELECT COUNT(*) num FROM <1>;
将查询结果命名为num

可以使用WHERE语句作为条件判断

函数    说明
SUM    计算某一列的合计值，该列必须为数值类型
AVG    计算某一列的平均值，该列必须为数值类型
MAX    计算某一列的最大值
MIN    计算某一列的最小值

要特别注意：如果聚合查询的WHERE条件没有匹配到任何行，
COUNT()会返回0，
而SUM()、AVG()、MAX()和MIN()会返回NULL

除了单个查询，sql还提供了分组聚合的功能
SELECT COUNT(*) num FROM <1> GROUP BY <2>;
该语句表明在查询结果时会按<2>来分组，再分别计算

可以在查询时添加多个列放入结果集中便于区分
但是如果前面用于分类的列中有一个其中值是不同的，例如分属1班的每个人的名字都不同的情况，sql引擎是不能将多个name的值放入一行记录中，所以添加列只能添加分组的列

多表查询
SELECT 查询不单单只可以从一张表查询，还可以从多张表同时查询

SELECT * FROM <1> <2>;

查询结果是两张表按行两两拼接而成的二维表，类似矩阵相乘
其结果列数是两表之和，行数是两表之积

但是两表间可能有重名部分，为了区别可以起别名

SELECT
    <1表>.<列> <别名>,
    <2表>.<列> <别名>,
FROM <1>, <2>;

除此之外我们可以更简化一点，给表取别名
SELECT
    <1表别>.<列> <别名>,
    <2表别>.<列> <别名>,
FROM <1> <表别>, <2> <表别>;
也可以添加WHERE语句

连接查询
先选择一个表作为主表，视作结果集，再将其他表有选择性的连接在主集上
例如，我们想要选出students表的所有学生信息，可以用一条简单的SELECT语句完成：

SELECT s.id, s.name, s.class_id, s.gender, s.score FROM students s;

但是，假设我们希望结果集同时包含所在班级的名称，上面的结果集只有class_id列，缺少对应班级的name列。
此时可以采用最常用的一种内连接  INNER JOIN

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 <条件>
可以在其后加上WHERE,ORDER BY 等语句

对于连接有以下几种
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
区别如下：
INNER JOIN 只返回同时存在的(交集)
RIGHT OUTER 返回右边都存在的，如果某行仅在右边有值，那么左边行的值会由NULL填充
LEFT JOIN 返回左边都存在的
FULL将所有都返回，没有的用NULL替代
具体理解请结合韦恩图进行理解

WHERE与 INNER JOIN的显著区别在于INNER JOIN可以优化查询速率

联合查询

应用场景要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时
将多个SELECT语句查询结果联合到一起，使用UNION 和 UNION ALL 关键字进行合并
1.查询同一张表，但是需求不同
2.多表查询：多张表的结构完全一样，保存的数据（结构）也是一样的

SELECT <1> FROM <表>
UNION //UNION选项有两个可选：ALL 表示重复也输出; DISTINCT 去重输出；默认去重
SELECT <2>

在联合查询中：order by只能最后使用一个，需要对查询语句用括号才行。即要么在所有语句后加上排序，要么将每个子句用括号包裹加上排序

---（正确但不符合所需）
SELECT * FROM student WHERE sex="man" 
union
SELECT * FROM student WHERE sex="woman" ORDER BY score;
这种情况是正确的，但是合并又没有意义，他会把之前的sex分好的情况给打乱

---（正确）
(SELECT * FROM student WHERE sex="man" ORDER BY score;
LIMIT 10)
union
(SELECT * FROM student WHERE sex="woman" ORDER BY score;
LIMIT 10);
在子语句中使用ORDER BY，由于优先级的问题，需要将整个子句用()括起来，且必须和LIMIT结合使用，否则不会生效。

子查询
在SQL查询中嵌套查询称为子查询。但是子查询会对表每一条数据进行查询，所以单表数据过大时不建议使用。
可以分为四类

标量子查询：返回单一值的标量，最简单的形式；
列子查询：返回的结果集是 N行一列；
行子查询：返回的结果集是一行N 列；
表子查询：返回的结果集是N 行 N列。

1.带 IN 关键字的子查询：将原表中特定列的值与子查询返回结果中的值比较
SELECT * FROM <> WHERE <> IN (子语句)
2.带比较运算符的子查询
SELECT * FROM <> WHERE <> 运算符/*用于表范围比较*/ (子语句)
3.带EXISTS的子查询
EXISTS:用来判断某些条件是否满足（跨表），用在 WHERE 之后，且返回结果只有0和1 即如果存在就列出表，如果不存在就列为NULL
4.带 ANY 关键字的子查询
表示是如果满足子语句的任意一个条件，就执行母语句
5.带 ALL 关键字的子查询
ALL 表示满足所有结果才执行外边语句