数据库MySQL——查询

SQL：结构化查询语言，用于查询和操作数据库系统
一、查询数据
1.基本查询：
SELECT * FROM <表名>
例：SELECT * FROM students;
select 是关键字，标识要执行一个查询 ， *号表示“所有列”，FROM 便是将要从哪个表查询。本例中是students 表
该SQL将查询出students 表的所有数据。注意：查询结果也是一个二维表，它表示列名和每一行的数据
select语句并不一定要有from 子句，例：select 100 +200 输出：300 （select 直接计算出表达式的结果）
select可以用作计算，但并不是SQL的强项，但是不带from的select 语句有一个有用的用途，就是用来判断当前到数据库的连接是否有效，许多检测工具会执行一条select 1；来测试数据库连接。
2，条件查询：
条件查询的语法：select *from <表名> where <条件表达式>
可以有多个条件，用 and or not 表示，组合三个以上的条件，需要用小括号（）表示进行条件运算
如果不加括号，条件运算按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级。
select语句可以通过where条件来设定查询条件，查询结果是满足查询条件的记录。
例如，要指定条件“分数在80分或以上的学生”，select *from students where score >=80
其中，where 关键字后面的score >=80 就是条件，score是列名。score >=80 就筛选出了指定条件的记录
where 搜索条件在进行分组操作之前应用，having搜索条件在进行分组操作之后应用
having经常与group by和聚合函数（COUNT, SUM, AVG, MIN, or MAX）一起使用，用来过滤由GROUP BY语句返回的记录集。

3，投影查询
返回指定列： 如果我们只希望返回某些列的数据，而不是所有列的数据，我们可以用select 列1，列2，列3， from … ，让结果集仅包含指定列，
例如，从students表中返回id、score和name这三列：
使用投影查询：SELECT id, score, name FROM students;

使用投影查询，并将列名重命名：SELECT id, score points, name FROM students; （将score 重命名为points）
投影查询+WHERE条件：SELECT id, score points, name FROM students WHERE gender = ‘M’;

4 排序（order by）
采用select查询时，查询结果集通常是按照id排序的，即根据主键排序。
– 按score从低到高：SELECT id, name, gender, score FROM students ORDER BY score;（默认升序）
如果要反过来，按照成绩从高到底排序，我们可以加上DESC表示“倒序”：
– 按score从高到低：SELECT id, name, gender, score FROM students ORDER BY score DESC;
如果score列有相同的数据，要进一步排序，可以继续添加列名。如，使用ORDER BY score DESC, gender表示先按score列倒序，如果有相同分数的，再按gender列排序：
– 按score, gender排序:SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;
如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面。例如，查询一班的学生成绩，并按照倒序排序：
– 带WHERE条件的ORDER BY:
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;
5.分页查询：
分页查询：从结果集中“截取”出第M-N条记录，通过 LIMIT OFFSET 子句 实现
将学生按照成绩从高到低进行排序：截取前三条记录，结果集从0号开始记录，选取3条
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;
OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集。
在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。
使用LIMIT OFFSET 分页时，随着N越来越大，查询效率也会越来越低。
6.聚合查询：统计一张表的数量，查询表一共有多少记录，输出的是行数。
– 使用聚合查询: SELECT COUNT() FROM students; COUNT()表示查询所有列的行数 ，意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT()。
– 使用聚合查询并设置结果集的列名为num: SELECT COUNT() num FROM students;
– 使用聚合查询并设置WHERE条件: SELECT COUNT() boys FROM students WHERE gender = ‘M’;（统计students 表中有多少男生）

MAX（）和MIN（）函数并不限于数值类型，如果是字符类型，MAX（）和MIN（）会返回排序最后和排序最前的字符。
– 使用聚合查询计算男生平均成绩: SELECT AVG(score) average FROM students WHERE gender = ‘M’;
如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL：
– WHERE条件gender = 'X’匹配不到任何行: SELECT AVG(score) average FROM students WHERE gender = ‘X’; 返回NULL
分组聚合：GROUP BY
– 按class_id分组: SELECT COUNT() num FROM students GROUP BY class_id;
按class_id分组，把name放入结果集： SELECT name, class_id, COUNT() num FROM students GROUP BY class_id;（不出意外，执行这条查询我们会得到一个语法错误，因为在任意一个分组中，只有class_id都相同，name是不同的，SQL引擎不能把多个name的值放入一行记录中。因此，聚合查询的列中，只能放入分组的列。）
使用多个列进行分组。例如，我们想统计各班的男生和女生人数：
– 按class_id, gender分组:
SELECT class_id, gender, COUNT() num FROM students GROUP BY class_id, gender;
7，多表查询
查询多张表：SELECT * FROM <表1> <表2>
– FROM students, classes: SELECT * FROM students, classes; （查询的结果也是一个二维表，它是students表和classes表的“乘积”，即students表的每一行与classes表的每一行都两两拼在一起返回。结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。）
利用投影查询的“设置列的别名”来给两个表各自的id和name列起别名：
SELECT
students.id sid,
students.name,
students.gender,
students.score,
classes.id cid,
classes.name cname
FROM students, classes;
注意，多表查询时，要使用表名.列名这样的方式来引用列和设置别名，这样就避免了结果集的列名重复问题。
、SQL还允许给表设置一个别名-- set table alias:：
SELECT
s.id sid,
s.name,
s.gender,
s.score,
c.id cid,
c.name cname
FROM students s, classes c; （注意到FROM子句给表设置别名的语法是FROM <表名1> <别名1>, <表名2> <别名2>。这样我们用别名s和c分别表示students表和classes表。）
多表查询添加WHERE条件：
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;
8，连接查询：连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后把其他表的行有选择地连接在主表结果集上，
例如，我们想要选出students表的所有学生信息，可以用一条简单的SELECT语句完成：
– 选出students表的所有学生信息：SELECT s.id, s.name, s.class_id, s.gender, s.score FROM students s;
选出所有学生，同时返回班级名称：
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等子句。

使用别名不是必须的，但可以更好地简化查询语句。
– 使用OUTER JOIN:
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;
INNER JOIN只返回同时存在于两张表的行数据，由于students表的class_id包含1，2，3，classes表的id包含1，2，3，4，所以，INNER JOIN根据条件s.class_id = c.id返回的结果集仅包含1，2，3。
有RIGHT OUTER JOIN，就有LEFT OUTER JOIN，以及FULL OUTER JOIN。它们的区别是：
INNER JOIN只返回同时存在于两张表的行数据，由于students表的class_id包含1，2，3，classes表的id包含1，2，3，4，所以，INNER JOIN根据条件s.class_id = c.id返回的结果集仅包含1，2，3。
RIGHT OUTER JOIN返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。
LEFT OUTER JOIN则返回左表都存在的行。如果我们给students表增加一行，并添加class_id=5，由于classes表并不存在id=5的行，所以，LEFT OUTER JOIN的结果会增加一行，对应的class_name是NULL：
– 使用FULL OUTER JOIN:
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;