SQL SELECT

mysql 与 mariadb的关系

---

SELECT 列名1，列名2… FROM 表名 （LIMIT 5 OFFSET 5）/(LIMIT 5，5);

从表某中选择列1,2…（从第五行开始五行）

SELECT语句排序数据使用 ORDER BY

SQL语句由子句（clause）组成，有些子句是必须的，有些事可选的。一个clause通常由一个关键字加上所提供的数据组成。

SELECT prod_id,prod_price,prod_name
FROM Products
ORDER BY prod_price DESC;

由prod_price排序，DESC为降序，并且DESC只应用到直接位于前面的列名。

如果想在多个列上降序，需要对每一列指定DESC关键字。

SELECT vend_id 
FROM Products；  --会返回重复名称

SELECT DISTINCT vend_id
FROM Products；  --只返回不同名称

DISTINCE应用于所有的列，比如SELECT DISTINCT prod_price，vend_id 除非两列都相同，否则将返回两列

结果集（result set） ：sql查询所检索出的结果。
cursor是一个存储在DBMS服务器上的数据库查询结果集。在存储了cursor后，应用程序可以根据需要滚动或浏览其中的信息。
cursor主要适用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或作出更改。
DECLARE语句创建cursor并定义响应的SELECT语句，根据需要带WHERE和其他子句。

DECLARE CustCursor CURSOR
FOR 
SELECT * 
FROM Customers
WHERE cust_email IS NULL;

创建了CustCursor，定义了游标，才可以打开

mysql和mariadb中游标只能在存储过程中使用，包括创建，打开和关闭，都必须在存储过程中

ORDER BY 子句要位于WHERE子句之后，否则会报错。

WHERE子句操作符：

操作符	说明
=	等于
<>	不等于
!=	不等于
<
<=
>
=
!<	不小于
!>
BETWEEN	在两个值之间(BETWEEN 5 AND 10)
IS NULL	为NULL值

提示：何时使用引号

单引号用来限定字符串。如果将值与字符串类型的列进行比较，就需要限定引号。

SQL允许使用多个WHERE子句，有两种使用方式，OR或者AND,例如下：

SELECT prod_id,prod_price,prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

SELECT prod_name,prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

这个子句的组合也有求值顺序的问题。所以使用AND OR连接子句，应该使用圆括号明确地分组操作符。

---

IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN取一组由逗号分隔、括在圆括号中的合法值。例子：

SELECT prod_name,prod_price
FROM Products
WHERE vend_id IN ('DLL01','BRS01')
ORDER BY prod_name;

本例中IN完成了OR相同的功能，那为什么要使用IN：
1.有很多合法选项时，IN更加清楚
2.IN一般比OR执行更快

3.IN最大优点是可以包含其他SELECT语句，能够更加动态地建立WHERE子句。

NOT操作符有且只有一个功能，就是否定其后所跟的任何条件。从不单独使用。例：

SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;

---

利用通配符(wildcard)查询
例如搜索产品名中包含bean bag的所有产品

SELECT prod_id,prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';

SELECT prod_id,prod_name
FROM Products
WHERE prod_name LIKE '%bean bag%';
#%表示任意次数的任意字符

#_只匹配单个字符
SELECT prod_id,prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';

正则表达式的使用要参阅具体的DBMS文档，但是在此有以下几点要注意：
1. 不用过度使用通配符，能使用其他语句完成的就不要用；
2. 不要把wildcard放在开始处，开始处最慢；

---

创建计算字段
有时存储在表中的数据不是应用程序所需要的，我们需要直接从数据库中检索出转换、计算或是格式化的数据，而不是先检索出数据，然后在应用程序中重新格式化，就用到了计算字段。
下面举例说明：Vendors表中存储有供应商名和地址，将其拼接输出
拼接（concatenate）+或||或特殊函数（MySQL&&MariaDB）

SELECT vend_name || ' (' || vend_country || ')'
FROM Vendors
ORDER BY vend_name;
#SQLite、Oracle

#下面MySQL&&MariaDB
SELECT Concat（vend_name,' (',vend_country,')')
FROM Vendors
ORDER BY vend_name;

#使用别名
SELECT Concat（vend_name,' (',vend_country,')')
       AS vend_title
FROM Vendors
ORDER BY vend_name;

执行算数计算

SELECT prod_id,quantity,item_price
FROM OrderItems
WHERE order_num = 20008;

汇总总价
SELECT prod_id,
       quantity,
       item_price,
       quantity * item_price AS expanded_price
FROM OrderItems
WHERE order_num = 10008;

---

使用函数汇总数据

函数	说明
AVG()	都是针对某列
COUNT()
MAX()
MIN()
SUM()

SELECT COUNT(*) AS num_items,
       MIN(prod_price) AS price_min,
       MAX(prod_price) AS price_max,
       AVG(prod_price) AS price_avg
FROM Products;

---

分组数据
汇总表内容的子集，GROUP BY / HAVING

SELECT COUNT(*) AS num_prods
FROM Products
WHERE vend_id = 'DL001';


SELECT vend_id,COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;

GROUP BY出现在WHERE之后，ORDER BY 之前。
除了能用GROUP BY 分组数据外，SQL还允许过滤分组，规定包括哪些分组，排除哪些分组。
举例如下：要列出至少有两个订单的所有顾客，为此，必须基于完整的分组而不是个别的列进行过滤。而WHERE过滤基于行不是分组，所以要是使用HAVING.（HAVING支持所有WHERE操作符，只是基于的条件不一样）

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

#另一种理解，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。
SELECT vend_id,COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;
#WHERE子句过滤所有prod_price至少为4的行，然后按vend_id分组数据，HAVING子句过滤计数为2或2以上的分组。

至此，可以排出SELECT子句的顺序：

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算要聚集时使用
HAVING	组级过滤	否

|ORDER BY|输出排序顺序|否

子查询
1. 利用子查询进行过滤

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
                    FROM OrderItems
                    WHERE prod_id = 'RGAN01');

1. 作为计算字段使用子查询

SELECT cust_name,
       cust_state,
       (SELECT COUNT(*)
        FROM Orders
        WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;

子查询常用于WHERE子句的IN操作符中，以及用来填充计算列。

---

联结表
没有连接条件的表关系返回的结果是笛卡尔积，也称叉联结。

SELECT vend_name,prod_name,prod_price
FROM Vendors,Products
WHERE Vendors.vend_id = Products.vend_id;
#等值联结，基于两个表之间的相等测试，也称为内联结（inner join）

SELECT vend_name,prod_name,prod_price
FROM Vendors INNER JOIN Products
   ON Vendors.vend_id = Products.vend_id;

#联结多个表
SELECT prod_name,vend_name,prod_price,quantity
FROM OrderItems,Products,Vendors
WHERE Products.vend_id = Vendors.vend_id
  AND OrderItems.prod_id = Products.prod_id
  AND order_num = 20007;

#看下面的两个同结果
SELECT cust_name,cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
                  FROM Orders
                  WHERE order_num IN (SELECT order_num
                                      FROM OrderItems
                                      WHERE prod_id = 'RGAN01'));

SELECT cust_name,cust_contact
FROM Customers,Orders,OrderItems
WHERE Customers.cust_id = Orders.cust_id
   AND OrderItems.order_num = Orders.order_num
   AND prod_id = 'RGAN01';

其他的联结：自连接（self-join），自然联结（natural join），（外联结outer join）

#子查询与自联结
SELECT cust_id,cust_name,cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
                   FROM Customers
                   WHERE cust_contact = 'Jim Jones');

SELECT c1.cust_id,c1.cust_name,c1.cust_contace
FROM Customers AS c1,Customers AS c2
WHERE c1.cust_name = c2.cust_name
  AND c2.cust_contace = 'Jim Jones';

实际使用时可以测试哪一种性能好，然后选用。

外联结：

#内联结，检索所有顾客及其订单
SELECT Customers.cust_id,Orders.order_num
FROM Customers INNER JOIN Orders
  ON Customers.cust_id = Orders.cust_id;
#外联结。检索包括没有订单顾客在内的所有顾客
SELECT Customers.cust_id,Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
  ON Customers.cust_id = Orders.cust_id;
#LEFT RIGHT 用来指定OUTER JOIN 左边还是右边的表选择所有行

带聚集函数的联结

#检索所有顾客及每个顾客下的订单数
SELECT Customers.cust_id
       COUNT(Orders.order_num) AS num_ord
FROM Customers INNER JOIN Orders
  ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;
#检索出了没有订单的顾客
SELECT Customers.cust_id
       COUNT(Orders.order_num) AS num_ord
FROM Customers LEFT OUTER JOIN Orders
  ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

使用联结和联结条件：
1. 关于确切的语法，要关注相应的DBMS文档。
2. 应该总是使用联结条件，否则会得出笛卡尔积。

---

组合查询
在此之前使用的都是一条SELECT语句，从一个或是多个表执行查询，然后返回一个结果集，SQL允许使用多条SELECT语句然后将结果作为一个查询结果集返回，这样组合查询通常称为并（union）或是复合（compound）查询（query）。
UNION在两个或是多个SELECT之间分隔；
UNION中每个查询必须包含相同的列、表达式或是聚集函数（列顺序可以不同）；
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含转换的类型。

最后一句后面接ORDER BY ，排序。