一、mac 环境下的 SQL 学习
https://zhuanlan.zhihu.com/p/34432866
1. SQL 的keyword 不能作为表或列的名字
2. 语句结束需要用； 结尾
3. 不区分大小写
4. 空格被忽略， 多行 ，一行都是可以的
二、检索数据
0. 注释
-- # /* ..... */
select prod_name # 这是一条注释 from Products -- 这也是一条注释 limit 5,5 ; /* 这也是 一条注释 */
1. select
从一个或多个表中检索信息
输入： 想选择什么， 从什么地方选择
select prod_name from Products; # from 是子句 # 在Products 表中 ，寻找prod_name # 结果为罗列出prod_name 列的所有值 # 为未排序数据
多列：列名之间用逗号隔开
select prod_id, prod_name, prod_price from Products;
检索所有的列： *为通配符
select * from Products;
选定只显示多少行：
第一行是 0行
select prod_name from Products limit 5 ; # 返回不超过5行的数据
select prod_name from Products limit 5 offset 5 ; # 检索从第5（1）行以后显示5（2）行
select prod_name
from Products
limit 5,5 ; # 同样的结果
select 子句顺序
| 子句 | 说明 | 是否必须使用 |
| SELECT | 要返回的列或表达式 | 是 |
| FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |
| WHERE | 行级过滤 | 否 |
| GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |
| HAVING | 组级过滤 | 否 |
| ORDER BY | 输出排序顺序 | 否 |
2. distinct()
必须跟在列名的前面
减少重复项，重复的会被并为一个
select distinct vend_id from Products;
作用与所有的列，
select distinct vend_id, prod_name from Products;
除非指定的两列完全相同，否则所有的行都会被检索出来
如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)。类似地，DISTINCT必须使用列名，不能用于计算或表达式。
3. order by 排序
是子句
应处于select 语句中最后一条子句， 但limit可以放在这个后面
如果不明确规定排序顺序， 则不应该假定检索出的数据的顺序有任何意义
select prod_name from Products order by prod_name;
按多个顺序来排列
select prod_id, prod_price, prod_name from Products order by prod_price, prod_name;
与上面相同，采用相对位置排序
select prod_id, prod_price, prod_name from Products order by 2,3;
排序方向
DESC:降序（descending)
ASC:升序（ascending) 默认为升序
只能应用到直接位于其前面的列名，
select prod_id, prod_price, prod_name from Products order by prod_price DESC; # 用价格进行降序排列
select prod_id, prod_price, prod_name from Products order by prod_price DESC, prod_name; # 以降序排序产品(最贵的在最前面)，再加上产品名
#产品名默认为升序排列
4. where 过滤数据
select prod_name, prod_price from Products where prod_price = 3.49;
| 操作符 | 说明 |
| = | 等于 |
| <> | 不等于 |
| != | 小于 |
| < | 小于等于 |
| <= | 不小于 |
| ! | 大于 |
| >= | 大于等于 |
| !> | 不大于 |
|
between |
在指定的两个值之间 包括边界值 |
| is null | 为null值 |
SELECT vend_id, prod_name FROM Products WHERE vend_id <> 'DLL01 '; SELECT vend_id, prod_name FROM Products WHERE vend_id <> 'DLL01 '; # 字符串的空格也应该包括在内
SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10;
select cust_name from Customers where cust_email is null;
通过过滤选择不包含指定值的所有行时，你可能希望返回含NULL值的行。但是这做不到。因为未知(unknown)有特殊的含义，数据库不知 道它们是否匹配，所以在进行匹配过滤或非匹配过滤时，不会返回这些结果。
过滤数据时，一定要验证被过滤列中含NULL的行确实出现在返回的数据中。
where 子句：
AND 的运算优先于 OR
AND
select prod_id, prod_price,prod_name from Products where vend_id = 'DLL01 ' and prod_price <= 4;
OR
事实上，许多DBMS在OR WHERE子句的第一个条件得到满足的情况下，
就不再计算第二个条件了(在第一个条件满足时，不管第二个条件是否满足，相应的行都将被检索出来)。
select prod_name,prod_price from Products where vend_id = 'DLL01 ' or vend_id = 'BRS01 ';
()
加括号可以改变优先级
select prod_name,prod_price from Products where (vend_id = 'DLL01 ' or vend_id = 'BRS01 ') and prod_price >= 10;
in
select prod_name,prod_price from Products where vend_id in ('DLL01 ' , 'BRS01 ') # 用括号+逗号 order by prod_name;
在有很多合法选项时，IN操作符的语法更清楚，更直观。
在与其他AND和OR操作符组合使用IN时，求值顺序更容易管理。
IN操作符一般比一组OR操作符执行得更快(在上面这个合法选项很少的例子中，你看不出性能差异)
IN的最大优点是可以包含其他SELECT语句，能够更动态地建立WHERE子句。第11课会对此进行详细介绍
not
否定其后所跟的任何条件
select prod_name,prod_price from Products where not vend_id ='DLL01 ' #列出除DLL01之外的所有供应商制造的产品 order by prod_name;
5. 通配符 like
通配符会使运行时间变长
不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。
把通配符置于开始处，搜索起来是最慢的。 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。
%
表示任何字符出现任意次数。
% 还能够匹配0个字符
子句WHERE prod_name LIKE '%'不会匹配产品名称为NULL的行。
select prod_id,prod_name from Products where prod_name like 'Fish%'; # 找出任何Fish 开头的产品
select prod_id,prod_name from Products where prod_name like '%bean bag%'; # 找出中间是bean bag的
# 找出中间的 'F%y'
_ 下划线
下划线的用途与%一样，但它只匹配单个字符，而不是多个字符。
select prod_id,prod_name from Products where prod_name like '__ inch teddy bear%';
[] 中括号
用来指定一个字符集，它必须匹配指定位置(通配符的位置)的一个字符。
只有微软的Access和SQL Server支持集合
前面+ ^（Microsoft access） or ! 来取反， 依具体软件情况而定
SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[JM]%' ORDER BY cust_contact;
6. 计算字段
(1) concat(str1,str2,...)
拼接字段
用+ 或 | | 但在mysql 中用函数
NULL 和任何字段通过concat 拼接都是NULL
SELECT concat(vend_name,vend_country) FROM Vendors ORDER BY vend_name;
(2)TRIM() RTRIM() LTRIM()
去掉左右两边的空格
RTRIM() 去掉右边的空格
LTRIM() 去掉左边的空格
SELECT concat(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title # 输出名为vend_title 的列 FROM Vendors ORDER BY vend_name;
(3) + - * / 加减乘除运算符
SELECT prod_id, quantity, item_price, quantity * item_price AS expanded_price FROM OrderItems where order_num = 20008;
(4) NOW()
返回现在的年月日时间
7. 基本函数
不可移植
较为通用的函数：
（1）SUBSTRING()
提取字符串： 只在MySQL和SQL Server使用
(2) CONVER()
数据类型转换，只在MariaDB、MySQL和SQL Server使用
(3) CURDATE()
取当前日期 只在MariaDB和MySQL使用
(4) 文本处理函数
| 函数 | 说明 |
| LEFT()(或使用子字符串函数） | 返回字符串左边的字符 |
| LENGTH()(也使用DATALENGTH() OR LEN()) | 返回字符串的长度 |
| LOWER() (access 使用LCASE()) | 将字符串转换为小写 |
| LTRIM() | 去掉字符串左边的空格 |
| RIGHT() ( 使用子字符串函数） | 返回字符串右边的字符 |
| RTRIM() | 去掉字符串右边的空格 |
| SOUNDEX() （对字符串进行发音比较） | 返回字符串的soundex值 |
| UPPER() (Access 使用UCASE()) | 将字符串转换为大写 |
soundex()
SELECT cust_name, cust_contact FROM Customers WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green'); #输出Michelle Green 因为两者发音是相同的
(5)时间函数
返回日期的某一部分
SQL Server ： DATEPART(yy, order_date)
Access: DATEPART('yyyy', order_date)
PostgreSQL ：DATE_PART('year', order_date)
Oracle ：to_number(to_char(order_date, 'YYYY') ：to_char()函数用来提取日期的成分，to_number()用来将提取出的成分转换为数值，以便能与2012进行比较
MySQL和MariaDB ：YEAR(order_date) = 2012
SQLite ：strftime('%Y', order_date) = 2012;
(6) 数据处理函数
| 函数 | 说明 |
| ABS() | 返回一个数的绝对值 |
| COS() | 返回一个角度的余弦 |
| EXP() | 返回一个数的指数值 |
| PI() | 返回圆周率 |
| SIN() | 返回一个角度的正弦 |
| SQRT() | 返回一个数的平方根 |
| TAN() | 返回一个角度的正切 |
8.汇总数据，聚集函数 aggregation function
对所有行执行计算，指定ALL参数或不指定参数(因为ALL是默认行为)。
只包含不同的值，指定DISTINCT参数。
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;
（1) AVG()
返回某列的平均值
AVG()函数忽略列值为NULL的行。
AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。
SELECT AVG(prod_price) AS avg_price FROM Products;
SELECT AVG(prod_price) AS avg_price FROM Products where trim(vend_id) ='DLL01' ; #返回特定供应商所提供产品的平均价格
# 在使用了DISTINCT后，此例子中的avg_price比较高，因为有多个物品具有相同的较低价格。排除它们提升了平均价格 SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products WHERE trim(vend_id) = 'DLL01';
(2) COUNT()
使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值(NULL)还是非空值。
使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
SELECT COUNT(*) AS num_cust1 from Customers; select count(cust_email) AS num_cust2 from Customers; #只对具有电子邮件地址的客户计数
SELECT COUNT(*) AS num_prods FROM Products WHERE trim(vend_id) = 'DLL01';
(3) MAX()
返回最大值
在用于文本数据时，MAX()返回按该列排序后的最后一行。
select max(prod_price) AS max_p from Products;
(4) MIN()
返回最小值
在用于文本数据时，MIN()返回该列排序后最前面的行
select min(prod_price) AS min_p from Products;
(5) SUM()
返回总计值
SUM()函数忽略列值为NULL的行。
# 函数SUM(quantity)返回订单中所有物品数量之和，WHERE子句保证只统计某个物品订单中的物品。 select SUM(quantity) AS item_ordered from OrderItems WHERE order_num = 20005; # 合计计算值,得出总的订单金额 select SUM(item_price*quantity) AS total_price from OrderItems WHERE order_num = 20005;
9.数据分组
(1)group by
- GROUP BY子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。
- 如果在GROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算(所以不 能从个别的列取回数据)。
- GROUP BY子句中列出的每一列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SELECT中使用表达式，则必须在GROUP BY子 句中指定相同的表达式。不能使用别名。
- 大多数SQL实现不允许GROUP BY列带有长度可变的数据类型(如文本或备注型字段)。
- 除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出。 如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
- GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
SELECT vend_id,COUNT(*) AS num_prods FROM Products group by vend_id;
分组后 再排列
SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items, order_num;
(2) HAVING
过滤分组 ，where 过滤行， having 过滤分组， where 可以用having 替代
使用HAVING时应该结 合GROUP BY子句
SELECT vend_id,COUNT(*) AS num_prods FROM Products group by vend_id having count(*) >=3;
# 列出具有两个以上产品且其价格大于等于4的供应商: SELECT vend_id, COUNT(*) AS num_prods FROM Products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*) >= 2;
10.子查询
结果嵌套
# 需要列出订购物品RGAN01的所有顾客 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 trim(prod_id) = 'RGAN01'));
****
# 显示Customers表中每个顾客的订单总数 SELECT cust_name, cust_state, ( SELECT COUNT(*) from Orders WHERE `Orders`.cust_id = `Customers`.cust_id) AS cust_order FROM Customers ORDER BY cust_name;
# Orders.cust_id = Customers.cust_id 也行
11. 联结
联结
DBMS在运行时关联指定的每个表， 以处理联结。这种处理可能非常耗费资源，因此应该注意，不要联结不必要的表。联结的表越多，性能下降越厉害。
需要注意，表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户端
SELECT vend_name, prod_name, prod_price from Vendors, Products WHERE Vendors.vend_id = Products.vend_id;
笛卡儿积(cartesian product)
由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
叉联结(cross join)
有时，返回笛卡儿积的联结，也称叉联结
等值联结(equijoin ), 内联结(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 trim(prod_id) = 'RGAN01'));*/ SELECT cust_name,cust_contact FROM Customers,Orders, OrderItems where Customers.cust_id = Orders.cust_id AND Orders.order_num = OrderItems.order_num AND trim(prod_id) = 'RGAN01'; # 两个输出结果一样
给表名起别名
：方式1：AS
方式2： 空格
# 给表取别名 SELECT cust_name, cust_contact FROM Customers AS C, Orders AS O, OrderItems AS OI WHERE C.cust_id = O.cust_id AND OI.order_num = O.order_num AND trim(prod_id) = 'RGAN01';
自联结(self-join)
/*SELECT cust_id, cust_name, cust_contact FROM Customers WHERE cust_name = (SELECT cust_name FROM Customers WHERE trim(cust_contact) = 'Jim Jones');*/ SELECT c1.cust_id, c1.cust_name, c1.cust_contact FROM Customers AS c1, Customers AS c2 WHERE c1.cust_name = c2.cust_name AND trim(c2.cust_contact) = 'Jim Jones'; # 两者结果相同
自然联结(natural join)
SELECT Customers.cust_id, Orders.order_num FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;
自然联结排除多次出现，使每一列只返回一次。
SELECT C.*, O.order_num, O.order_date, OI.prod_id, OI.quantity, OI.item_price FROM Customers AS C, Orders AS O, OrderItems AS OI WHERE C.cust_id = O.cust_id AND OI.order_num = O.order_num AND trim(prod_id) = 'RGAN01';
外联结(outer join)
联结包含了那些在相关表中没有关联行的行。这种联结称为外联结。
(对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客)
SELECT Customers.cust_id, Orders.order_num FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;
在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表(RIGHT指 出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表)。上面的例子使用LEFT OUTER JOIN从FROM子句左边的表(Customers表) 中选择所有行。
全外联结(full outer join)
它检索两个表中的所有行并关联那些可以关联的行。
Access、MariaDB、MySQL、OpenOfficeBase或SQLite不支持FULL 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;
上者不会输出零值，下者会输出零值
12. 组合查询
主要有两种情况需要使用组合查询:
在一个查询中从不同的表返回结构数据
对一个表执行多个查询，按一个查询返回数据
union
用union将两个结果放在一起
UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔(因此，如果组合四条SELECT语句，将要使用三个UNION关 键字)。
UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过，各个列不需要以相同的次序列出)。
列数据类型必须兼容:类型不必完全相同，但必须是DBMS可以隐含转换的类型(例如，不同的数值类型或不同的日期类型)。
SELECT cust_name, cust_contact, cust_email FROM Customers WHERE trim(cust_state) IN ('IL','IN','MI') UNION # 会消去重复的行 SELECT cust_name, cust_contact, cust_email FROM Customers WHERE trim(cust_name) = 'Fun4All';
SELECT cust_name, cust_contact, cust_email FROM Customers WHERE trim(cust_state) IN ('IL','IN','MI') UNION ALL # 不会消去重复的行 SELECT cust_name, cust_contact, cust_email FROM Customers WHERE trim(cust_name) = 'Fun4All';
在用UNION组合查询时，只能使用一条ORDER BY子句，它必须位于最后一条SELECT语句之后。对于结果
集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条ORDER BY子句 。
13. 插入，更新，删除数据
insert
插入完整的行;
插入行的一部分
插入某些查询的结果
INSERT INTO Customers VALUES('1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL); # 对次序要求更高
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES('1000000007', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL); # 更准确， 但更繁琐， 在这种标明列名的情况下， 也可以只给出一部分的值，只要与上面的列名一一对应即可。，但空的地方需允许被定义为NULL 值， 或是表格在定义的时候已经给出默认值
# 从另一张表select 并插入 填充CustNew时，不应该使用已经在Customers中用过的cust_id值
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) SELECT cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country FROM CustNew;
SELECT INTO ：
从一个表 复制到一个全新的表（运行中创建的表）
CREATE TABLE CustCopy AS SELECT * FROM Customers;
UPDATE
更新表中的特定行
更新表中的所有行
UPDATE Customers SET cust_email = 'kim@thetoystore.com' WHERE cust_id = '1000000005';
更新多个
UPDATE Customers SET cust_contact = 'Sam Roberts', cust_email = 'sam@toyland.com' WHERE cust_id = '1000000006';
要删除 也可以用NULL 去更新那个值 NULL 是没有值， ‘’空字符串被认为是一个值
DELETE
从表中删除特定的行;
从表中删除所有行。
DELETE FROM Customers WHERE cust_id = '1000000008';
TRUNCATE TABLE
删除该表中的所有内容
14. 创建和操纵表
CREATE TABLE
利用CREATE TABLE创建表，必须给出下列信息:
- 新表的名字，在关键字CREATE TABLE之后给出;
- 表列的名字和定义，用逗号分隔;
- 有的DBMS还要求指定表的位置。
CREATE TABLE Product1 # CHAR 长度不可变， VARCHAR 字符串长度可变 ( prod_id CHAR(10) NOT NULL, vend_id CHAR(10) NOT NULL, prod_name CHAR(254) NOT NULL, # 逗号前+ DEFAULT 1 则默认值为1 prod_price DECIMAL(8,2) NOT NULL , prod_desc VARCHAR(1000) NULL ); # 还有INTEGER, DATETIME
NULL值就是没有值或缺值。允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受没有列值的行，换句话
说，在插入或更新行时，该列必须有值。
只有不允许NULL值的列可作为主键，允许NULL值的列不能作为唯一标识
ALTER TABLE
ALTER TABLE Vendors ADD vend_phone CHAR(20); # 增加列
ALTER TABLE Vendors DROP COLUMN vend_phone; # 删除列
DROP TABLE
删除整个表而不是其内容
RENAME
RENAME TABLE CustCopy TO SC;
15. 视图
CREATE VIEW: 创建视图
CREATE VIEW ProductCustomers AS SELECT cust_name, cust_contact, prod_id FROM Customers, Orders, OrderItems WHERE Customers.cust_id = Orders.cust_id AND OrderItems.order_num = Orders.order_num;
SELECT cust_name, cust_contact from productcustomers where trim(prod_id) = 'RGAN01';`Customers`
CREATE VIEW Vendorlocations AS SELECT CONCAT(TRIM(vend_name),'(',TRIM(vend_country),')') AS vend_title FROM Vendors;
16. 使用存储过程
存储过程就是为以后使用而保存的一条或多条SQL语句
执行存储过程
17. 事务处理
start transaction
commit : 用于保存修改
rollback : 用于撤销
DELETE FROM Orders rollback;
创建占位符：
SAVEPOINT
SAVEPOINT delete1;
ROLLBACK TO delete1;
18. 使用游标 declare
19.约束、索引和触发器
索引：
CREATE INDEX prod_name_ind ON PRODUCTS (prod_name);
浙公网安备 33010602011771号