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_idFROM Products；  --只返回不同名称

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

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

DECLARE CustCursor CURSORFOR SELECT * FROM CustomersWHERE 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_nameFROM ProductsWHERE vend_id = 'DLL01' AND prod_price <= 4;SELECT prod_name,prod_priceFROM ProductsWHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

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

---

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

SELECT prod_name,prod_priceFROM ProductsWHERE vend_id IN ('DLL01','BRS01')ORDER BY prod_name;

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

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

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

SELECT prod_nameFROM ProductsWHERE NOT vend_id = 'DLL01'ORDER BY prod_name;

---

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

SELECT prod_id,prod_nameFROM ProductsWHERE prod_name LIKE 'Fish%';SELECT prod_id,prod_nameFROM ProductsWHERE prod_name LIKE '%bean bag%';#%表示任意次数的任意字符#_只匹配单个字符SELECT prod_id,prod_nameFROM ProductsWHERE prod_name LIKE '__ inch teddy bear';

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

---

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

SELECT vend_name || ' (' || vend_country || ')'FROM VendorsORDER BY vend_name;#SQLite、Oracle#下面MySQL&&MariaDBSELECT Concat（vend_name,' (',vend_country,')')FROM VendorsORDER BY vend_name;#使用别名SELECT Concat（vend_name,' (',vend_country,')')       AS vend_titleFROM VendorsORDER BY vend_name;

执行算数计算

SELECT prod_id,quantity,item_priceFROM OrderItemsWHERE order_num = 20008;汇总总价SELECT prod_id,       quantity,       item_price,       quantity * item_price AS expanded_priceFROM OrderItemsWHERE 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_avgFROM Products;

---

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

SELECT COUNT(*) AS num_prodsFROM ProductsWHERE vend_id = 'DL001';SELECT vend_id,COUNT(*) AS num_prodsFROM ProductsGROUP BY vend_id;

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

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

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

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

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

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

SELECT cust_idFROM OrdersWHERE 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 ordersFROM CustomersORDER BY cust_name;

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

---

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

SELECT vend_name,prod_name,prod_priceFROM Vendors,ProductsWHERE Vendors.vend_id = Products.vend_id;#等值联结，基于两个表之间的相等测试，也称为内联结（inner join）SELECT vend_name,prod_name,prod_priceFROM Vendors INNER JOIN Products   ON Vendors.vend_id = Products.vend_id;#联结多个表SELECT prod_name,vend_name,prod_price,quantityFROM OrderItems,Products,VendorsWHERE Products.vend_id = Vendors.vend_id  AND OrderItems.prod_id = Products.prod_id  AND order_num = 20007;#看下面的两个同结果SELECT cust_name,cust_contactFROM CustomersWHERE 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_contactFROM Customers,Orders,OrderItemsWHERE 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_contactFROM CustomersWHERE cust_name = (SELECT cust_name                   FROM Customers                   WHERE cust_contact = 'Jim Jones');SELECT c1.cust_id,c1.cust_name,c1.cust_contaceFROM Customers AS c1,Customers AS c2WHERE c1.cust_name = c2.cust_name  AND c2.cust_contace = 'Jim Jones';实际使用时可以测试哪一种性能好，然后选用。

外联结：

#内联结，检索所有顾客及其订单SELECT Customers.cust_id,Orders.order_numFROM Customers INNER JOIN Orders  ON Customers.cust_id = Orders.cust_id;#外联结。检索包括没有订单顾客在内的所有顾客SELECT Customers.cust_id,Orders.order_numFROM 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_ordFROM Customers INNER JOIN Orders  ON Customers.cust_id = Orders.cust_idGROUP BY Customers.cust_id;#检索出了没有订单的顾客SELECT Customers.cust_id       COUNT(Orders.order_num) AS num_ordFROM Customers LEFT OUTER JOIN Orders  ON Customers.cust_id = Orders.cust_idGROUP BY Customers.cust_id;

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

---

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

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