MySQL（二）：常用查询语句

查询语句

1. 基本查询：

   SELECT * FROM <表名>

   SELECT是关键字，表示要执行一个查询操作，*表示“所有列”，FROM表示将要从哪个表查询，该SQL将查询出students表的所有数据。SELECT不要求一定要有FROM子句，不带FROM子句的SELECT语句有一个有用的用途，就是用来判断当前到数据库的连接是否有效。许多检测工具会执行一条SELECT 1;来测试数据库连接。

2. 条件查询：

   SELECT * FROM <表名> WHERE <条件表达式>

   SELECT语句可以通过WHERE条件设置查询条件，查询结果是满足查询条件的记录。条件表达式可以用<条件1>AND<条件2>表达满足条件1并且满足条件2，以及<条件1>OR<条件2>表示满足条件1或条件2，也可以用NOT<条件>筛选出不符合条件的记录。SQL语句中“不等于”用“<>”表示。

3. 投影查询

   SELECT 列1，列2，列3 FORM <表名>
   SELECT 列1 别名1，列2，列3 FORM <表名>

   如果我们只希望返回某些列的数据，而不是所有列的数据，我们可以用以上的语句，这种操作称为投影查询；结果集中的列名可以如第二行代码所示自定义。投影查询操作可以接WHERE条件实现更复杂的查询。

4. 排序

   SELECT id,name,score
   FORM students
   WHERE class_id = 1
   ORDER BY score DESC,name;

   使用SELECT语句查询，通常结果是按照主键排序的，如果想根据其他条件排序可以加上ORDER BY子句。上方代码是从students表中查询1班的成绩并按倒叙（从高到低）排列，如果有相同成绩再按name列排序。默认的排序规则是ASC：“升序”，即从小到大。ASC可以忽略，即ORDER BY score ASC和ORDER BY score效果一样。DESC为倒序。

5. 分页查询

   SELECT id,name,gender,score
   FORM students
   ORDER BY score DESC
   LIMIT <pageSize> OFFSET < pageSize*(pageIndex-1) >;

   当查询的结果集很大时，成千上万行的数据放在一个页面显示不如分页显示。上述代码中pageSize是每页需要显示的结果数，pageIndex是当前页的索引（从1开始），例如LIMIT 3 OFFSET 0；意思是对结果集从0号记录开始，最多取3条，注意SQL记录集的索引是从0开始的，如果要查询第4页的结果集则为LIMIT 3 OFFSET 9;当OFFSET超过了查询的最大数量时，查询语句不会报错而是得到一个空的结果集“Empty result set”。

6. 聚合查询

SELECT COUNT(*) boys FORM students WHERE gender = 'M';

 

对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。以上         查询操作是统计students表中男生的数量，并把返回的结果集列名改别名为boys。另外，其实COUNT()函数返回的也是二维表，只不         过只有一列。除了COUNT()函数外，SQL还提供如下聚合函数：

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

注意，MAX()和MIN()函数并不局限于数值类型，如果是字符类型，则返回排序最后和排序最前的字符。如果聚合查询的WHERE条件          没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL。

对于聚合查询，SQL还提供了“分组聚合”的功能，通过GROUP BY子句实现：

SELECT COUNT(*) num FROM students GROUP BY class_id;

 

7.多表查询

SELECT查询可以从多张表中同时查询数据。查询多张表的语法是：

SELECT <列名1> <别名1>,<列名2> <别名2>
FROM <表名1> <别名1>,<表名2> <别名2>
WHERE <条件>;

多表查询查询的结果也是一个二维表，它是两张表的“乘积”，表1的每一行与表2的每一行两两拼在一起返回，结果集的列数是两表列          数之和，行数是两表行数之积。这种多表查询又称笛卡尔查询，使用笛卡尔查询时要非常小心，由于结果集是目标表的行数乘积，对          两个各自有100行记录的表进行笛卡尔查询将返回1万条记录，对两个各自有1万行记录的表进行笛卡尔查询将返回1亿条记录。

值得注意的是查询的两表可能会有列名相同，比如id之类的，可以设置列的别名，表的名字也可以通过设置别名变得简洁：

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;

8.连接查询

连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表   的行有选择性地“连接”在主表结果集上。用最常用的一种内连接举例，假设希望结果集同时包含所在班级的名称，但结果集只有class_id列，缺少对应班级的name列。现在问题来了，存放班级名称的name列存储在classes表中，只有根据students表的class_id，找到classes表对应的行，再取出name列，就可以获得班级名称：

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查询的写法是：

1. 先确定主表，仍然使用FROM <表1>的语法；
2. 再确定需要连接的表，使用INNER JOIN <表2>的语法；
3. 然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
4. 可选：加上WHERE子句、ORDER BY等子句。

      使用别名不是必须的，但可以更好地简化查询语句。

有INNER JOIN，就有RIGHT OUTER JOIN，LEFT OUTER JOIN，以及FULL OUTER JOIN。他们的区别是：

INNER JOIN 只返回同时存在两张表的行数据；RIGHT OUTER  JOIN返回右表都存在的行，如果一行仅在右表存在结果集以NULL填充剩下的字段。LEFT OUTER JOIN则返回左表都存在的行；FULL OUTER JOIN会把两张表的所有记录全部选择出来，并自动把对方不存在的列填充为NULL。