SQL 查询语法

DISTINCT 连接查询

DISTINCT 单列

-- 按照a字段进行去重
select distinct(a) from tableA;

DISTINCT 多列

select distinct a,b,c from tableA;

注意此时是将a,b,c三列所有不同的组合全部列出来，而不仅仅只是distinct a
相当于以下语句：

 select a,b,c from tableA group by a,b,c

另外一种的 DISTINCT 多列

其实这篇文章的最初需求如下：
想分别查某一张表的几个字段的distinct值

select distinct a from tableA;
select distinct b from tableA;
select distinct c from tableA;

这样是可以达到目的的。但是这样要写三条语句，不爽，想着用一条语句达到目的。
思考了一会，想到用union来解决这个问题

select distinct(a) || ' a' from tableA
union all
select distinct(b) || ' b' from tableA
union all
select distinct(c) || ' c' from tableA

这样就达到了一条语句查询出所有结果的目的。后面拼接的字符串是为了标识这个值属于哪个字段 

---

JOIN 连接查询

我们创建学生表和成绩表用来举例

student表

grades表

现在，查询学生的数学成绩并对应姓名。可以先用不使用连接的SQL语句查询：

SELECT s.name ,g.math
FROM student s ,grades g 
WHERE s.id = g.studentId 

这样的查询结果为：

1、INNER JOIN（内连接）

先看一下用INNER JOIN 的SQL语句查询上面的内容写法：

SELECT s.name ,g.math
FROM student s
INNER JOIN grades g ON g.studentId = s.id

INNER JOIN定义：在查询的几个表中，每个表都存在至少一个匹配时，INNER JOIN 关键字返回行。也就是共有部分

2、LEFT JOIN（左连接）

把上面内连接(INNER JOIN)的关键字改为left join效果如何呢？

SELECT s.name ,g.math
FROM student s
LEFT JOIN grades g ON g.studentId = s.id

下面是查询结果:

LEFT JOIN定义：关键字会从左表 (student) 那里返回所有的行，即使在右表 (grades) 中没有匹配的行。

3、RIGHT JOIN（右连接）

有了LEFT JOIN，肯定会想，有没有RIGHT JOIN呢？答案是，有的。

SELECT s.name ,g.math
FROM student s
RIGHT JOIN grades g ON g.studentId = s.id

下面是查询结果:

 会发现，多了两列没有名字，只有成绩的行。 
RIGHT JOIN定义：关键字会右表 (grades) 那里返回所有的行，即使在左表 (student) 中没有匹配的行。

总结

• INNER JOIN: 如果查询的表中都有至少一个匹配，则返回行
• LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行

---

 WHERE（过滤）

数据库表一般包含大量数据，很少需要检索所有行，一般只会检索需要的数据，这样就需要使用过滤条件

1、操作符过滤

SELECT prod_name,prod_price 
FROM products 
WHERE prod_price BETWEEN 5 AND 10

2、组合过滤——AND、OR、IN、NOT

•  AND操作符（检索满足所有给定条件的行）

SELECT prod_id,prod_name,prod_price 
FROM products 
WHERE vend_id = 1003 AND prod_price < = 10

•  OR操作符（检索匹配任意一条的行）

SELECT prod_name,prod_price 
FROM products 
WHERE vend_id = 1002 OR vend_id = 1003

•  IN操作符（找出每个与条件都可以匹配的行）

SELECT prod_name,prod_price 
FROM products 
WHERE vend_id IN (1002,1003) 

•  NOT操作符（与IN联合使用，找出与条件不匹配的行）

SELECT prod_name,prod_price 
FROM products 
WHERE vend_id NOT IN (1002,1003) 

3、通配符过滤——LIKE、%、_ 

• % 通配符（最常使用的通配符是百分号%。在搜索串中，%表示任何字符出现任意次数）
• 例如检索name字段，以小开头的词

SELECT prod_id,prod_name 
FROM products 
WHERE prod_name LIKE 'jet%'

•  通配符可以在搜索模式中任意位置使用，下面使用两个通配符，它们位于模式的两端
• 请注意%匹配的是0个到多个字符

SELECT prod_id,prod_name 
FROM products 
WHERE prod_name LIKE '%anvil%'

• _   通配符（下划线_通配符用途与%一样，但是它只匹配一个字符而不是多个）

SELECT prod_id,prod_name 
FROM products
WHERE prod_name LIKE '_ ton anvil'

总结：

1. 通配符很有用，但是这种功能是有代价的，通配符搜素效率要比其他搜索花的时间更长
2. 注意通配符的位置，如果放错地方可能不会返回想要的数据
3. 尾空格可能会干扰通配符匹配，例如在fly后面有一个或多个空格，则%fly将不会匹配它们，解决这个问题可以用%fly%去匹配，或者使用函数去掉首尾空格
4. NULL也不能和通配符进行匹配

---

GROUP BY（数据分组）、HAVING（过滤分组）

 SQL聚集函数可以用来汇总数据，这使我们能获得平均值、最大最小值，而不用检索所有数据。到目前所有的计算都是在表的所有数据匹配特定的WHERE子句的数据上进行的。

 1、GROUP BY——创建分组

分组是在SELECT语句的GROUP BY子句中建立的。

我们如果想得到每个供货商vend提供产品的总数，该怎么办？ 

这就要用到分组了，分组允许把数据分为多个逻辑组，以便对每个组进行聚集计算

计算每个供应商提供商品的总数，这个时候就要以供货商vend_id进行分组，然后使用聚集函数COUNT()求得每个供应商的货物总数

SELECT vend_id,COUNT(*) AS num_price 
FROM products 
GROUP BY vend_id

注：

• GROUP BY子句会指示MySQL分组数据，然后对每个组进行聚集
• 如果分组列中具有NULL值，则它也会被分为一组
• 除聚集计算除外，SELECT语句中的每个列都必须在GROUP BY子句中给出
• GROUP BY子句必须在WHERE子句之后，ORDER BY子句之前

 2、HAVING ——过滤分组

除了能使用分组，MySQL还允许过滤分组，规定包括哪些分组，排除哪些分组。HAVING非常类似于WHERE，唯一差别就是WHERE过滤行，HAVING过滤分组。

查询具有2个订单以上的顾客信息

SELECT cust_id,COUNT(*) AS orders 
FROM orders 
GROUP BY cust_id 
HAVING COUNT(*) >= 2

WHERE是在数据分组前进行过滤的，HAVING是在数据分组后进行过滤的

3、分组排序 

我们经常发现GROUP BY分组的数据是以分组顺序输出的，但有时候也要按照用户要求来排序，以保证数据正确的排序。

SELECT vend_id,COUNT(*) AS num_price 
FROM products 
GROUP BY vend_id 
ORDER BY num_price DESC

---

ORDER BY (排序)  LIMIT(限制查询结果)

使用limit关键字可以限制查询结果

• 指定检索的开始行和行数，如果没有指定开始行则默认为0行，如果没有足够的行则返回它能返回的那么多

SELECT * 
FROM student s 
LIMIT 5

SELECT prod_name 
FROM products 
LIMIT 5,2

ORDER BY 排序（它的位置位于FROM之后，LIMIT之前）

SQL语句检索的结果一般没有顺序，如果想对其进行排序则需要用到ORDER BY关键字

• 按单个列排序  数据排序默认为升序（从A到Z)

SELECT prod_name 
FROM products 
ORDER BY prod_name

• 按多个列排序（检索products表，将结果按两个列进行排序，先按照prod_price排序，在对具有相同prod_price值的行按照prod_name排序，如果所有prod_price值都一样，则不会按照prod_name排序）

SELECT prod_id,prod_price,prod_name 
FROM products 
ORDER BY prod_price,prod_name

• 指定排序方向（数据排序默认为升序（从A到Z, 不区分大小写)，还可以使用降序，但必须指定关键字DESC）

SELECT prod_id,prod_price,prod_name 
FROM products 
ORDER BY prod_price DESC

• 指定排序方向，并按多个列排序（先按prod_price降序，然后在对prod_price相同的行按prod_name排序）

SELECT prod_id,prod_price,prod_name 
FROM products 
ORDER BY prod_price DESC,prod_name

• 找出一列中最高或最低的值

SELECT prod_price 
FROM products 
ORDER BY prod_price 
LIMIT 1

SELECT prod_price 
FROM products 
ORDER BY prod_price DESC 
LIMIT 1