SQL学习笔记 | XavierShi's Blog

检索数据

排序检索数据

关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有任何意义。

使用ORDER BY语句进行排序。SELECT prod_name FROM Products ORDER BY prod_name; 注意，order by 语句必须是最后一条字句，不然就会出错。
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;多个列排序的另一种写法，根据前面的 select 语句中的列位置排序
以上两种都是默认升序排序。SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price DESC,prod_name; 使用 DESC 关键字可以进行降序排序，如果多列降序，则每一列名后面都要加 DESC 关键字。与之相对的升序关键字是 ASC，不过一般用不到

普通检索

SELECT prod_name FROM Products;检索出来的数据可能是无序的
SELECT prod_name,prod_id FROM Products; 检索多个列
SELECT * FROM Products; 检索所有列
SELECT DISTINCT vend_id FROM Products; 检索唯一值，去除多余重复的 vend_id 值，只列出少量的唯一值
SELECT prod_name FROM Products LIMIT 5 OFFSET 5; 限制结果返回，返回低 5 行起的 5 条数据。★ 需要注意的是，此种写法每个 DBMS 实现语句并不相同。

```sql
– 这是一条注释
SELECT prod_name FROM Products;
#这是第二条注释某些DBMS可能并不支持
/*
第三种块状注释
*/

- 使用 `AS` 关键字可以将要查询的列转化一个别名显示`SELECT prod_name,prod_price AS '产品名和价格' FROM Products WHERE prod_price BETWEEN 5 AND 10;`
- 使用 `+` 可以做运算
- 使用`CONCAT`关键字可以拼接多个字段。
  ```sql
  SELECT CONCAT(prod_id,prod_name) #prod_id,prod_name
  FROM Products WHERE prod_price > 9;

过滤数据

简单过滤

SELECT prod_name,prod_price FROM Products WHERE prod_price = 3.49;使用 where 字句进行数据检索操作
where 字句操作符，比较特殊的是 BETWEEN（在指定的两个值之间） IS NULL（为 NULL 值）
SELECT prod_name,prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10; 使用 between 取区间值

高级数据过滤

AND 用在 WHERE 字句中的关键字，用来指示检索满足所有给定条件的行
OR WHERE 字句中使用的关键字，用来表示检索匹配任一给定条件的行
AND 语句的优先级比 OR 高，因此可以使用圆括号调整优先级
IN WHERE 字句中用来指定要匹配值的清单的关键字，功能与 OR 相当
NOT WHERE 字句中用来否定其后条件的关键字
LIKE 用于进行模糊查询 SELECT prod_name FROM Products WHERE prod_name LIKE '%i%' ;查询包含 i 字母的 prod_name 值
LIKE 中使用下划线_可以代表空几个字符，几个下划线就是几个字符 SELECT prod_name FROM Products WHERE prod_name LIKE '_i%' ; 查找第二个字母为 i 的 prod_name 数据
使用\_可以对_下划线进行转义
使用 ESCAPE 关键字可以修改转义符号 SELECT prod_name FROM Products WHERE prod_name LIKE '_$_%' ESCAPE '$';

计算字段

有多种形式

SELECT prod_id,quantity,item_price,item_price-1 AS new_price FROM OrderItems; 直接进行运算的计算字段
SELECT prod_id,quantity,item_price,CONCAT(item_price,quantity) AS new_price FROM OrderItems; mysql 中使用 concat 操作符进行字符串计算

SQL 函数

SQL 函数，每一个 DBMS 都有特定的函数，函数名称很可能在不同的 DBMS 里面是不同的

SELECT cust_id,cust_name,UPPER(CONCAT(SUBSTR(cust_contact, 1, 2),SUBSTR(cust_city, 1, 3)) )AS user_login FROM Customers;

文本处理函数

UPPER() 将字符串转为大写字母
LOWER() 将字符串转为大写字母
TRIM() 去掉字符串前后的空格 LTRIM() RTRIM() 分别去掉左右的空格
SUBSTR(str, pos, pos) 字符串截取，第一个参数是字符串，第二个参数是开始位置，第三个参数是截取数量

数值处理函数

ABS()返回一个数的绝对值

日期函数

DATE() 提取日期部分
NOW() 获取完整的年月日时分秒
CURDATE()获取完整的年月日
CURTIME()获取完整的时分秒
DATE_FORMAT() 格式化函数具体内容详见 w3c mysql

聚集函数

AVG()获取一列的平均值
COUNT()返回某列的行数
MAX()返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和

分组数据

使用分组可以将数据分为讴歌逻辑组，对每个组进行聚集计算。

SELECT vend_id,COUNT(*) AS num_prods FROM Products GROUP BY vend_id; 直接 count(*)那么返回的就是全部的行数(9),利用 group by 可以根据 vend_id 分组，查询每个 vend_id 分组下的行数(3,4,2)。需要注意，不加 group by,前面的 vend_id 会报错
使用HAVING操作符过滤分组。事实上 HAVING支持WHERE的所有操作，区别是 having 支持过滤分组，where 在数据分组前进行过滤，having 在数据分组后进行过滤，这是一个重要的区别

子查询

11 课挑战题：

SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE item_price > 10);
SELECT * FROM Orders WHERE order_num in (SELECT order_num FROM OrderItems WHERE prod_id = 'BR01') ORDER BY order_date;
SELECT cust_email FROM Customers WHERE cust_id in (SELECT cust_id FROM Orders WHERE order_num in (SELECT order_num FROM OrderItems WHERE prod_id = 'BR01'));
SELECT cust_id,(SELECT SUM(item_price * quantity) FROM OrderItems WHERE Orders.order_num = OrderItems.order_num) AS total_ordered FROM Orders ORDER BY total_ordered DESC;
SELECT prod_name,(SELECT SUM(quantity) FROM OrderItems WHERE Products.prod_id = OrderItems.prod_id) AS quant_sold FROM Products;

联结

可伸缩，能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序被称为可伸缩性好。

笛卡尔积，由没有联结条件的表关系返回的结果为笛卡尔积，检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。也称叉联结

简单格式的等值联结（内联结）: SELECT vend_name,prod_name,prod_price FROM Vendors,Products WHERE Vendors.vend_id = Products.vend_id;

标准格式的等值联结：SELECT vend_name,prod_name,prod_price FROM Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id;

上面子查询的联结查询语句版： 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';

挑战题：

SELECT cust_name,order_num FROM Customers,Orders WHERE Customers.cust_id = Orders.cust_id ORDER BY cust_name, order_num; SELECT cust_name,order_num FROM Customers INNER JOIN Orders on Customers.cust_id = Orders.cust_id ORDER BY cust_name, order_num;

```sql
SELECT cust_name,Orders.order_num,SUM(item_price*quantity) AS OrderTotal
FROM Customers,Orders,OrderItems
WHERE Customers.cust_id = Orders.cust_id AND Orders.order_num = OrderItems.order_num
GROUP BY cust_name,Orders.order_num
ORDER BY cust_name,order_num;


````
3. `SELECT * FROM Orders, OrderItems WHERE Orders.order_num = OrderItems.order_num AND OrderItems.prod_id = 'BR01' ORDER BY order_date;`
4.  多个`INNER JOIN ON`的联结查询竟然是这么写的，from是单独的 where是单独的，联结查询分行的
```sql
SELECT cust_email
FROM Customers
  INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
  INNER JOIN OrderItems ON Orders.order_num = OrderItems.order_num
WHERE prod_id='BR01';
````

5. 一是 group by 前面不应该加 and，再一个就是 不应该以未出现的字段 使用 group by

```sql
SELECT cust_name,
    SUM(item_price*quantity) AS OrderTotal
FROM Customers,Orders,OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND
Orders.order_num = OrderItems.order_num
GROUP BY cust_name HAVING OrderTotal>= 1000
ORDER BY cust_name;

SELECT cust_name,SUM(item_price*quantity) AS OrderTotal
FROM Customers
  INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
  INNER JOIN OrderItems ON Orders.order_num = OrderItems.order_num
GROUP BY cust_name HAVING OrderTotal >= 1000
ORDER BY cust_name;

不同类型的联结

给表起别名也是使用 AS 关键字，使用.语法能筛选查询前的表的字段。

外联结

1
2
3

SELECT Customers.cust_id, Orders.order_num
FROM Customers
LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

调整 left 或者 right 可以更改左外联结还是右外联结

Inner 和 outer 貌似可以省略的

挑战题

SELECT cust_name,order_num FROM Customers INNER JOIN Orders ON Customers.cust_id = Orders.cust_id;
SELECT cust_name,order_num FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;
SELECT prod_name,order_num FROM Products LEFT OUTER JOIN OrderItems ON Products.prod_id = OrderItems.prod_id ORDER BY prod_name;
SELECT prod_name,COUNT(order_num) FROM Products LEFT OUTER JOIN OrderItems ON Products.prod_id = OrderItems.prod_id GROUP BY prod_name ORDER BY prod_name;

```sql
SELECT Vendors.vend_id, COUNT(prod_id)
FROM Vendors
LEFT OUTER JOIN Products ON Vendors.vend_id = Products.vend_id
GROUP BY Vendors.vend_id;


````

#### 组合查询
```sql
SELECT cust_name,cust_contact,cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION
SELECT cust_name,cust_contact,cust_email
FROM Customers
WHERE cust_name = 'Fun4All';
````

使用 UNION 关键字结合 SELECT 语句实现组合查询，查询结果会被过滤，唯一，必须由两个以上 SELECT 语句才能用。两个表的查询列、表达式、聚集函数要相同或者是可以转化。

如果想包含或取消重复的行，那么可以使用`UNION ALL`关键字来进行组合。

若想排序，则必须在最后一个 SELECT 语句之后使用 ORDER BY。

1. ```sql
   SELECT prod_id,quantity
   FROM OrderItems
   WHERE quantity > 100
   UNION
   SELECT prod_id,quantity
   FROM OrderItems
   WHERE prod_id = 'BNBG*'
   ORDER BY prod_id;

2. ```sql
SELECT prod_id,quantity
FROM OrderItems
WHERE quantity > 100 AND prod_id LIKE 'BNBG%';

```sql
SELECT prod_name
FROM Products
UNION
SELECT cust_name
FROM Customers;


````

#### 插入数据
插入数据，表列表和数据列名最好全部列出并一一对应。
可以从select查询到的数据直接插入到表中，按照列的次序进行对应。
```sql
INSERT INTO Customers(cust_id)
SELECT cust_id
FROM Customers;
````

如果主键重复，则插入失败。
从一个表复制到另一个表

```sql
CREATE TABLE CustCopy AS SELECT * FROM Customers;

更新和删除内容

要更新的表、列名和它们的新值、确定要更新哪些行的过滤条件。

1
2
3

UPDATE Customers
SET cust_email = '123'
WHERE cust_id = 1000000005;

1 2	DELETE FROM Customers WHERE cust_id = 1000000006;

创建和操纵表

创建表

CREATE TABLE Customers
(
  cust_id      char(10)  NOT NULL ,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL
);

更新表添加列

1 2	ALTER TABLE Vendors ADD vend_phone CHAR(20);

更新表删除列

1 2	ALTER TABLE Vendors DROP COLUMN vend_phone;

复杂的表结构更改一般需要手动删除过程，涉及到以下步骤：

用新的列布局创建一个新表
使用 INSERT SELECT 语句，从旧表复制数据到新表，有必要的话，可以使用转换函数和计算字段
检验包含所需数据的新表
重命名旧表（如果确定，可以删除它）
用旧表原来的名字重命名新表
根据需要，重新创建触发器、存储过程、索引和外键

删除表：

1	DROP TABLE CustCopy;

视图

为什么要用视图：

重用 SQL 语句
简化复杂的 SQL 操作
使用表的一部分而不是整个表
保护数据
更改数据格式和表示

视图更新或者覆盖，必须先删除然后再创建

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;

1
2
3

SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

CREATE VIEW CustomersWithOrders AS
SELECT Customers.cust_id,
       Customers.cust_name,
       Customers.cust_address,
       Customers.cust_city,
       Customers.cust_state,
       Customers.cust_zip,
       Customers.cust_country,
       Customers.cust_contact,
       Customers.cust_email
FROM Customers
JOIN Orders ON Customers.cust_id = Orders.cust_id;
SELECT * FROM CustomersWithOrders;

存储过程

存储过程是一个相当重要的主题，详情看文档。

管理事务处理

事务处理用来管理 INSERT、UPDATE、DELETE 语句，不能回退 SELECT、CREATE、DROP 语句。
使用 ROLLBACK 命令可以用来回撤 SQL 语句。

索引

1 2	CREATE INDEX prod_name_ind ON Products (prod_name);

存储过程、事务、触发器各个 DBMS 实现不尽相同，说明这一块非常重要。

SQL 之 case when then 用法

--简单case函数
case sex
  when '1' then '男'
  when '2' then '女’
  else '其他' end;

--case搜索函数
case when sex = '1' then '男'
     when sex = '2' then '女'
     else '其他' end

case 函数只返回第一个符合条件的值，剩下的 case 部分将会自动忽略

--比如说，下面这段sql，你永远无法得到“第二类”这个结果
case when col_1 in ('a','b') then '第一类'
     when col_1 in ('a') then '第二类'
     else '其他' end

资料来源

本篇笔记资料来源于

《SQL 必知必会（第五版）》
尚硅谷 SQL 基础课程