[大数据]SQL查询

SQL查询

简单查询

我们可以通过*来查询数据库所有的字段，也可以通过字段名并用,分隔的方式来查询需要的字段。

from后跟我们需要查询的数据表。

在数据库的操作中，我习惯性的把关键字进行大写，把数据库名/表名/列名/字段名进行小写，当然我们也可以用下边的符号,具体可以看例子，但是注意此处不能使用单引号，单引号默认指的是字符串。

--查询指定字段
SELECT product_id,`name`,`quantity_in_stock`,`unit_price`
FROM `products`;
--查询所有结果
SELECT * 
FROM `products`;

数据运算

在查询的时候我们也支持一些简单的数据运算+,-,*,/,%分别对应加减乘除取模运算,当然和数学运行一样我们可以()来提高它的运算等级。

SELECT `name`,unit_price,unit_price+2,unit_price-2,unit_price*2,unit_price/2,unit_price%2
FROM `products`;

得到的结果,我取其中一行放在下边：

4.65 6.65 2.65 9.30 2.325000 0.65

查询结果筛选

DISTINCT

再有计数之后我们会有一个新的需求，比如我们想要知道不同价格物品种类总数时，我们需要把相同价格的物品进行去重，我们就需要用到DISTINCT关键字。

同时注意：DISTINCT关键字只能用于开头。

SELECT DISTINCT unit_price,`name`
FROM `products`;

AS

我们在COUNT时发现它的字段名为COUNT(unit_price)这是很不舒服的，我们需要一个能够代表该行特点的字段名称，我们就需要用到as

有时候我们可能需要更改某个字段的字段名,这个时候我们就需要用到as关键词,当然我们也可以不使用。

SELECT `name` AS `姓名`,unit_price 价格，unit_price '价格'
FROM `products`;

我们有这两种方式来改变字段名，如果不用as记得加空格哦。

同时注意：别名是可以加单引号的。

对以上三个关键字同时使用:

SELECT DISTINCT COUNT(unit_price) AS '不同价格商品的种类数量',`name`
FROM `products`;

这也说明对于同一个字段，可以有多个不同的关键字来进行修饰。

TOP

top并不是所有的数据库都支持事实上常用的MySQL并不支持TOP关键字。该关键字在SQL Server中被支持。

查询数据库中前三条数据：

SELECT TOP 3 * 
FROM products;

查询数据库中前百分之三的数据：

SELECT TOP 3 PERCENT * 
FROM products;

条件关键词

WHERE

条件都放在where关键词后。当然也有特殊情况，后边我们回学到HAVING,如果我们的条件是分组后的必须用HAVING关键词。

比较运算符

一说到条件，那么比较运算应该是最常见的，比如获取价格在某一个区间内的商品。

常见的比较运算符包括>,<,=,>=,<=,!=,<>，分别表示大于，小于，等于，大于等于，小于等于，后两个都表示为不等于。

SELECT `name`,unit_price FROM `products` WHERE unit_price>4;

SELECT `name`,unit_price FROM `products` WHERE unit_price>=4;

SELECT `name`,unit_price FROM `products` WHERE unit_price<4;

SELECT `name`,unit_price FROM `products` WHERE unit_price<=4;

SELECT `name`,unit_price FROM `products` WHERE unit_price=4;

SELECT `name`,unit_price FROM `products` WHERE unit_price!=4;

SELECT `name`,unit_price FROM `products` WHERE unit_price<>4;

AND

我们可能有多个条件同时满足的情况比如价格小于4或者大于3的物品。而这些不同的条件都需要and关键词来连接。

SELECT `name`,unit_price 
FROM `products` 
WHERE unit_price<4
AND unit_price>3;

OR

我们可能有多个条件仅需满足这些条件中的一部分即可，比如价格大于4或者小于3的物品。而这些条件就可以用or关键词来修饰。

SELECT `name`,unit_price 
FROM `products` 
WHERE unit_price>4
OR unit_price<3;

NOT

我们有时候对于满足一些条件的数据字段是不想要查询的就需要通过NOT关键字来过滤这些结果。

SELECT `name`,unit_price 
FROM `products` 
WHERE 
NOT unit_price>4
OR unit_price<3;

当然 NOT的内部也可以写其他的东西比如:

SELECT `name`,unit_price 
FROM `products` 
WHERE 
NOT (unit_price>4
OR unit_price<3);

BETWEEN

我们在定义一个区间范围内的条件时使用AND/OR+比较运算符的方式略微有些麻烦。

BETWEEN AND可以帮我们减少麻烦。

注意:BETWEEN AND区间两侧都是闭区间。

SELECT `name`,unit_price 
FROM `products` 
WHERE 
unit_price BETWEEN 1 AND 4;

IN

我们在遇到一个符合OR关键词的场景时，字符串或者数字的操作往往有些许麻烦：

SELECT `name`,unit_price 
FROM `products` 
WHERE unit_price = 1 OR unit_price = 3 OR unit_price = 4;

此时IN关键词应该可以帮你减轻一些负担，并且使代码变得优雅起来。

SELECT `name`,unit_price 
FROM `products` 
WHERE 
unit_price IN (1,3,4);

当然IN关键词还支持字符的操作。只是字符需要加上单引号。

LIKE

我们有时候需要查询复合一个特定条件的字符串比如：姓张的用户名单或是名字中含有糖的食物又或者姓张并且只有两个字的名字。

LIKE关键字使我们可以非常容易的做到。与LIKE关键词一起使用的有%,_两个符号%代表多个字符,_代表一个字符可以同时使用。

查询带有字符o的名字：

SELECT `name`,unit_price 
FROM `products` 
WHERE 
`name` LIKE '%o%';

查询开头为o或者结尾为O的名字：

SELECT `name`,unit_price 
FROM `products` 
WHERE 
`name` LIKE '%o'
OR `name` LIKE 'o%';

查询符合倒数第二个字符为p的字段:

SELECT `name`,unit_price 
FROM `products` 
WHERE 
`name` LIKE '%p_';

REGEXP

正则表达式在处理字符串的过程中可能更加合适。

查询符合名字中带有字符o的名字：

SELECT `name`,unit_price 
FROM `products` 
WHERE 
`name` REGEXP 'o';

查询符合名字开头是字符o的名字：

SELECT `name`,unit_price 
FROM `products` 
WHERE 
`name` REGEXP '^o';

查询符合名字结尾是字符o的名字：

SELECT `name`,unit_price 
FROM `products` 
WHERE 
`name` REGEXP 'o$';

查询符合名字中带有字符o或者字符p的名字：

SELECT `name`,unit_price 
FROM `products` 
WHERE 
`name` REGEXP 'o|p';

查询符合名字中以字符p开头或者字符e结尾的名字：

SELECT `name`,unit_price 
FROM `products` 
WHERE 
`name` REGEXP '^P|e$';

查询符合名字中带有字符o并且该字符前边是P/F两个字符中的一个的名字：

SELECT `name`,unit_price 
FROM `products` 
WHERE 
`name` REGEXP '[PF]o';

查询符合名字中带有字符o并且该字符前边是A-G字符中的一个的名字：

SELECT `name`,unit_price 
FROM `products` 
WHERE 
`name` REGEXP '[A-Z]o';

IS NULL

有时候我们需要查询没有标记价格(该字段为空)的物品,此时用到IS NULL关键词

SELECT `name`
FROM `products` 
WHERE 
unit_price IS NULL;

ORDER BY

有时候我们需要对查询到的数据进行排序,此时需要用到ORDER BY关键词。

ASC：表示升序排列；DESC：表示降序排列；

将查询到的数据按价格降序排列：

SELECT `name`,unit_price
FROM `products` 
ORDER BY unit_price DESC;

同时，我们可以通过多个字段进行排序：

先按照价格进行排序，后根据名字进行排序(相同价格的物品按名字排序)

SELECT `name`,unit_price
FROM `products` 
ORDER BY unit_price,`name`;

我们还可以简化写法：

SELECT `name`,unit_price
FROM `products` 
ORDER BY 1,2;

此处的1代表我们查询的第一个字段，2代表我们查询的第二个字段。

需要注意：MySQL区别于其他数据库的地方在于，我们可以根据任何一个该数据表的字段进行排序。

比如我们虽然没有查询购买日期，但是我们可以根据他排序后进行查找。

SELECT `name`,unit_price
FROM `products` 
ORDER BY buy_date DESC;

LIMIT

有时候我们需要查询前几条数据，就需要用到LIMIT关键字：

查询前五条数据：

SELECT `name`,unit_price
FROM `products` 
LIMIT 5;

我们在做数据分页的时候希望有一个偏移量：

跳过前三行，查询两行。

SELECT product_id,`name`,unit_price
FROM `products` 
LIMIT 3,2;

^开头 |不同分割 $结尾 []任意匹配 [a-f]for

子查询

我们有时候需要进行，3号客户最大发票数额更大的发票，可以进行多个的嵌套查询。

ALL

SELECT *
FROM invoices i
WHERE invoice_total>(
  SELECT MAX(i.invoice_total)
  FROM invoices i
WHERE i.client_id=3);

正常的子查询如上所述，我们也可以采用下面的方法，ALL关键词表示该字段数据必须满足ALL后边括号内的所有条件比如invoice_total必须大于查询到的所有的值，也就是大于最大值了。

SELECT *
FROM invoices i
WHERE invoice_total>ALL(
  SELECT i.invoice_total
  FROM invoices i
WHERE i.client_id=3);

ANY/SOME

表示满足其中一个条件即可。

比如下面表示我们找到与三号客户有过一样发票值的人即可。

SELECT *
FROM invoices i
WHERE invoice_total=ANY(
  SELECT i.invoice_total
  FROM invoices i
WHERE i.client_id=3);

连接查询

内连接

内连接：我们在查询某个表数据的时候，往往还需要查询与该表相关的表的数据。比如：我们查询订单系统，订单系统中包含了物品的id，购买数量。

他的作用是: 我们FROM关键词后的数据表的每一行在JOIN关键词的表中的每一行进行条件匹配，两张表都匹配的才会显示。

此时如果我们需要查询总价就需要从商品表获取价格以及商品名称，看下面例子：

SELECT oi.order_id,p.name,oi.quantity,p.unit_price
FROM order_items oi
INNER JOIN products p
ON oi.product_id=p.product_id;

此时是对order_items的每一行在products中进行条件匹配。

需要注意：内连接的默认排序是按照 连接进来的表排序的，比如上述例子按照products的查询顺序排序。

INNER JOIN关键字在做内连接时可以简写为JOIN。

USING

我们在做连接的时候，看上一个例子ON关键词后边跟了条件，这样看上去其实很麻烦，我们可以用USING关键词来简化写法。但是要保证连接的两个表中都有该字段。

SELECT o.customer_id,c.customer_id
FROM orders o
JOIN customers c
  USING(customer_id);

跨数据库表连接

我们在实际开发过程中，不可能仅仅对一个数据库进行操作，多个数据库之间的操作才是正常的。

SELECT oi.order_id,p.name,oi.quantity,p.unit_price
FROM order_items oi
INNER JOIN sql_inventory.products p
ON oi.product_id=p.product_id;

注意上述例子默认使用的数据库为order_items，连接的数据库是sql_inventory。

自连接

我们有时候在员工表中，需要得到他们的上下级从属关系，自连接可以很容易做到这一点。

需要注意自连接需要对每一个表起别名，同时通过别名来获取表数据。

SELECT e.first_name,m.first_name
FROM employees e
INNER JOIN employees m
ON e.reports_to=m.employee_id;

多表内连接

我们需要有多个表进行查询工作，就可以采用多个JOIN关键词串联的方式来解决。

SELECT o.order_id,o.order_date,c.first_name,c.last_name,os.name AS STATUS
FROM orders o
JOIN customers c
  ON o.customer_id=c.customer_id
JOIN order_statuses os
  ON o.status = os.order_status_id;

复合条件内连接

我们有时候不仅仅需要符合一个条件，此时就需要用到AND关键词来复合我们的条件。

SELECT *
FROM order_items oi
JOIN order_item_notes oin
  ON oi.order_id = oin.order_Id
  AND oi.product_id=oin.product_id;

隐式内链接

不建议使用，因为一旦没有加入WHERE关键词和条件，会导致交叉连接比如第一个表有5条数据，第二个表有10条数据，就会查出来5*10=50条数据。

SELECT oi.order_id,p.name,oi.quantity,p.unit_price
FROM order_items oi,products p
WHERE oi.product_id=p.product_id;

左外连接

我们有时候需要外连接比如在获取订单的时候，我们也希望把那些没有下单的人也查出来。

左外连接表示：左表数据全有，右表中不符合条件的字段为NULL值。

SELECT c.customer_id,o.customer_id
FROM customers c
LEFT OUTER JOIN orders o
  ON c.customer_id=o.customer_id;

右外连接

当然我们也可以用右连接的方式来获取它。

右外连接表示：右表数据全有，左表中不符合条件的字段为NULL值。

SELECT o.customer_id,c.customer_id
FROM orders o
RIGHT OUTER JOIN customers c
  ON c.customer_id=o.customer_id;

**注意：**与INNER关键词一样OUTER关键词是可以省略的。

自外连接

SELECT e.first_name,m.first_name
FROM employees e
LEFT JOIN employees m
     ON e.reports_to=m.employee_id;

多表外连接

与多表内连接类似：

SELECT o.customer_id,c.customer_id,s.name
FROM orders o
LEFT JOIN customers c
  ON c.customer_id=o.customer_id
LEFT JOIN shippers s
  ON o.shipper_id=s.shipper_id;

自然连接

数据引擎自己猜测该如何连接，不建议使用(随机性),(会对字段相同的列进行连接)

SELECT o.customer_id,c.first_name
FROM orders o
NATURAL JOIN customers c;

类似于

SELECT o.customer_id,c.first_name
FROM orders o
JOIN customers c
  USING(customer_id);

交叉连接

连接的两个表的每一行都会与另一个表中的每一行进行连接。

他的用法我个人觉得是在型号表中可能会有所使用：

比如我们有一个颜色表，一个大小表我们可以自由组合出所有的结果：

显示的写法：

SELECT *
FROM sizes s
CROSS JOIN colors c;

其实我们在将隐式内连接的时候就有所描述，隐式的交叉连接：

SELECT *
FROM sizes s,colors c;

联合查询

我们在进行订单查询的时候，我们想要把21年以后的订单定为活跃订单，21年以前的订单为存档订单，并把它联合到一个结果集UNION关键词可以帮助我们解决这个问题：

SELECT o.customer_id,o.order_date,'active' AS statue
FROM orders o
WHERE o.order_date >= '2021-01-01'
UNION
SELECT o.customer_id,o.order_date,'archive' AS statue
FROM orders o
WHERE o.order_date < '2021-01-01';

注意需要合并的字段数量必须一致。

注意如果字段数量一样但是字段名不一样，但是数据类型一样也是会查询出来的比如：

SELECT o.customer_id,o.order_date,'active' AS statue
FROM orders o
WHERE o.order_date >= '2019-01-01'
UNION
SELECT o.customer_id,o.order_date,o.comments
FROM orders o
WHERE o.order_date < '2019-01-01';

当然字段数量一样，字段不一样且数据类型不一样是查不出来的。

聚合函数查询

COUNT

有的时候我们需要知道合乎我们查询条件的字段有多少个，此时我们就需要COUNT去计数。

当然COUNT默认返回所有的非空字段数量;

可以使用COUNT(*)获取该表的总列数;

可以使用DISTINCT对结果集去重。

SELECT 
COUNT(unit_price),
COUNT(DISTINCT unit_price),
COUNT(*)
FROM products;

MAX/MIN/AVG/SUM

以上三个分别表示最大值，最小值，平均值和总和。

SELECT 
MAX(unit_price),
MIN(unit_price),
AVG(unit_price),
SUM(unit_price)
FROM products;

GROUP BY

我们有时也需要获取每一个用户花了多少钱，这个时候我们可以用GROUP BY来进行分组操作。

我们甚至还可以给他进行排序。

SELECT 
NAME,
SUM(unit_price) AS sum_price
FROM products
GROUP BY NAME
ORDER BY sum_price DESC;

GROUP BY必须放在ORDER BY的前边，不能放在ORDER BY的后边。

我们也可以根据多个字段进行分组：

SELECT 
NAME,
SUM(unit_price)AS sum_price
FROM products
GROUP BY NAME,unit_price;

HAVING

有时候需要在分组后对数据进行筛选，HAVING能够很好的帮助我们。

HAVING关键词也支持AND/OR等关键词来进行复合查询。

SELECT 
NAME,
SUM(unit_price)AS sum_price
FROM products
GROUP BY NAME
HAVING sum_price>3;

WITH ROLLUP

作用是把值全部加起来，来获取汇总值（我们可以很容易得到各省的产值与总产量）。

仅仅在MySQL中被支持。仅仅用于聚合值的列。

SELECT 
product_id,
NAME,
SUM(unit_price) AS sum_price
FROM products
GROUP BY NAME WITH ROLLUP;

上述例子中只有sum_price的列被全部求和了，也就是说我们除了获取了分组以后的和还获得了总和。

查询练习

比生菜更贵的产品

SELECT *
FROM products
WHERE unit_price> (
  SELECT unit_price 
  FROM products
  WHERE NAME = 'Lettuce - Romaine, Heart'
);

比平均工资高的雇员

SELECT *
FROM employees
WHERE salary >(
SELECT AVG(salary)
FROM employees);

没有被下单的商品

SELECT *
FROM products
WHERE product_id NOT IN(
 SELECT DISTINCT product_id
 FROM order_items
 );

没有发票的客户

SELECT *
FROM clients
WHERE client_id NOT IN(
SELECT DISTINCT client_id
FROM  invoices);

如果我们不用子查询的话，我们应该怎么做呢？

连接查询你值得拥有！！！

SELECT *
FROM clients c
LEFT JOIN invoices i
ON c.client_id=i.client_id
WHERE i.invoice_id IS NULL;

查询购买了生菜的客户编号与姓名

SELECT c.customer_id,c.first_name,c.last_name
FROM customers c
WHERE c.customer_id IN (
  SELECT o.customer_id
  FROM orders o
  WHERE o.order_id IN(
    SELECT order_id 
    FROM order_items oi
    WHERE oi.product_id = (
      SELECT p.product_id
      FROM products p
      WHERE NAME = 'Lettuce - Romaine, Heart'
    )
  )
);

此处再看连接查询：

SELECT DISTINCT c.customer_id,c.first_name,c.last_name
FROM customers c
LEFT JOIN orders o USING(customer_id)
LEFT JOIN order_items oi USING(order_id)
LEFT JOIN products p USING(product_id)
WHERE p.name='Lettuce - Romaine, Heart';

连接查询的写作思想在于时刻记得你要查什么，什么表可以得到它，然后去连接吧！

比自己部门平均工资高的员工

SELECT *
FROM employees e
WHERE salary >(
	SELECT AVG(salary)
	FROM employees
	WHERE e.office_id=office_id
);

看上表，在查询过程中，进入employees表然后对表中的数据进行平均值的求取，随后每一行都会进行一次平均值的求取这种方式叫做相关子查询，这种查询方式慢并且比较占内存，但是使用却很广泛。

每个客户高于自己发票平均值的发票

SELECT client_id
FROM invoices i
WHERE invoice_total>(
  SELECT AVG(invoice_total)
  FROM invoices
  WHERE i.client_id=client_id
);

得到所有有发票的客户信息

SELECT *
FROM clients
WHERE client_id IN(
  SELECT DISTINCT client_id
  FROM invoices
);

同时我们也可以通过连接来获取。

SELECT DISTINCT client_id
FROM clients c
INNER JOIN invoices USING(client_id);

让我们再来一个方法：

SELECT DISTINCT client_id
FROM clients c
WHERE EXISTS(
  SELECT client_id
  FROM invoices
  WHERE client_id=c.client_id
);

EXISTS的意义在于当我们的表数据有很多的情况下，IN关键词的效率自然是不如EXISTS。

从来没有下过单的商品

SELECT *
FROM products p
WHERE NOT EXISTS(
  SELECT *
  FROM order_items
  WHERE p.product_id=product_id
)

发票数额以及每一列都有平均额度

AVG关键词只能查询到一行也就是该行的平均额度，此处就需要在SELECT关键词后边加入我们的子查询结果。

并且如果我们在一条查询语句中写过了查询那么我们可以直接通过SELECT获取他。

SELECT invoice_id,invoice_total,(
	SELECT AVG(invoice_total) FROM invoices) AS invoice_total_avg,
	invoice_total-(SELECT invoice_total_avg) AS invoice_total_cz
FROM invoices;

FROM中的查询

注意必须要起一个别名。

SELECT invoice_total_avg,invoice_total_cz
FROM 
(SELECT invoice_id,invoice_total,(
	SELECT AVG(invoice_total) FROM invoices) AS invoice_total_avg,
	invoice_total-(SELECT invoice_total_avg) AS invoice_total_cz
FROM invoices) AS invoice;