SQL必知必会（第五版）

SQL必知必会

了解SQL

1.数据库

数据库就像是一个容器，存放各种表。需要与DBMS区分

1.1表

一个结构化的清单，存储各种数据。

sql全称是struct query language 结构化查询语言

描述表的信息就是 模式 schema，当然模式也可以描述数据库。

表是由 列 column构成的。表中的一个字段称为列

每列都有对应的数据类型，数字还是字符串等。

数据是由 行 row 构成的。一行称为一条记录

1.2主键

表中每一行都应该能唯一标识自己。这就是 主键 primary key的作用

表中任何列都可以称为主键，至于要满足以下条件

任何两行都没有相同的主键

每一行必须都有一个主键，非null

主键列的值不允许修改或更新

主键值不能重用，比如某一行删除后他的主键不能付给新行

虽然可以用多列联合作为主键，但是主键是唯一的。

检索数据

1.检索列

select prod_name
from Products;

从Products表选取prod_name列

sql语句结尾必须用；号结束

SQL不区分大小写，SELECT select Select三者完全相同

未排序数据返回时可能会与数据库里显示的不一致。但是行数一致即可。

显示时可能时数据添加的顺序，也可能不是。

select prod_id,prod_name,prod_price
from Products;

检索多列只要在列名后加 **，**号即可

除此之外还可以用通配符 比如*号显示全部

需要注意的是最好别用这个 *。这会损失检索性能。

但是该通配符也可以检索出你不知道名字的列。

2.去重

select distinct vend_id 
from Products

显示该列不重复的所有值。

选哟注意的是，distinct对其后的所有列都生效

比如 distinct a,b

则会显示a,b列所有不重复的组合。

3.显示行数控制

select top 5 from xxx
select a from xxx limit 5;

第一行时mysql显示前五行，第二个是hive显示前五条数据。

select a from xxx limit 5 offset 5

该语句意思选则第五条数据后的五条数据。也就是6-10条。

需要注意的是offset时从 0开始计数。

也可以简化，比如 limit 5，1

该语句表示从第五条数据开始数，取一条。与标准写法相比是反着的。

排序数据

单列排序

一般来说显示的数据是数据往数据库插入的数据，但是如果后续修改了数据。

比如喊出或更新，DBMS可能会因为优化空间而修改存储数据。

为了排序我们可以使用Order By子句，但是确保其 处于最后一个子句

select s,name
from xx
order By s;

可以用没select的列进行排序。

多列排序

select a,b,c
from xx
order by a ,b

默认都是升序

先按a升序排，如果a相同，则按b的升序排

也有更简介的写法

order by 2,3

就是先按第二列排，然后排第三列，都是升序

如果排序的列没有select上，不可以使用该用法

ASC是升序，DESC是降序

过滤数据

select a
from x
where a=3

可以通过where子句过滤符合的条件

order by子句应位于where之后

where的操作符

= 等于

<> 不等于

< 不等于

<= 小于等于

!< 不小于

> 大于

>= 大于等于

!> 不大于

BETWEEN 在两值之间，左闭右开，左边值可以取到，右边取不到。

IS NULL 是null值

如果判断值是字符则应该用单引号

between 使用时搭配 and 之前是低端值。之后是高端值

高级过滤

操作符 and or 可以用在where子句

and:两边条件都得满足

or：满足之一即可

or是懒惰运算的，如果第一个条件满足了，就不会执行第二个判断

select xxx
from a
where name='xcw' and age=19
order by age

运算顺序

必要时使用括号括起来相同的条件，并且记住 and是优先执行的

例如想查看由 a。b厂商制造的10元以上的产品

select a
from xx
where (id=a or id=b)and price >10

()括号优先级大于and 或or运算符

IN操作符

select a
from xxx
where id in ("a","b") and price >10

这个和上面的操作一样

in的性能大于or，同时in后可以跟select子句

NOT运算符

否定之后的所有条件

select a
from a
where not id =1

Like操作符

通配符：用来匹配值的一种特殊字符

搜索模式：由字面值和通配符两者构成的搜索条件

% 表示任何字符出现任意次数。简单理解就是代表是随机的字符串。

select prodname
from as
where prodname like 'ID%'

select prodname
from as
where prodname like '%ID%'
‘F%Y’ //F开头。Y结尾


SELECT prod_name,prod_desc
FROM products
WHERE prod_desc like '%toy%' AND prod_desc LIKE '%carrots%'
ORDER BY prod_name DESC;
要同时包含两个like时，得完整写一遍条件

通配符可以出现在任意位置，这表示文本中包含ID的字符值

_用来匹配一个字符

[]匹配指定字符集

通配符虽然使用起来容易，但是会造成性能损失。有以下tips

不哟啊过度使用通配符，如果能使用其他通配符，优先其他

就算使用，也尽量别把通配符放在最开始的地方，搜索起来最慢

计算字段

字段 field 与列 coulum意思相同

计算字段是再select时创建的

拼接字符

select name + '('+contry+')'

用+号拼接字符和列值

trim（）可以去除空格 retrim去除右侧空格，ltrim去掉左边空格，trim去掉左右两边空格

AS 可以为sql列指定别名。

select name + '('+contry+')' as 'new_column'

虽然as后别名可以为字符串，但是再客户端中字符串别名会带了各种问题，一般都是命名为一个字母

常用算数符

±*/

函数处理数据

函数还可以再where中使用或其他sql语句中使用，也可以对别名使用

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gFsrhqld-1670463155106)(SQL必知必会第五版.assets/image-20221124151501262.png)]

日期处理函数

DATEPART函数

日期中包含的

select xxx
from s
where DATEPART(yy,order_year) =2020
订单日期是2022年的数据返回

数值处理函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hhFjG90a-1670463155107)(SQL必知必会第五版.assets/image-20221124153004650.png)]

聚合函数

我们长须汇总数据而不是一条一条展示出来，这就会用到聚合函数，也叫聚簇函数

聚合函数的作用是根据计算返回一个值

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AmUYqSwd-1670463155107)(SQL必知必会第五版.assets/image-20221124160244527.png)]

count（*）是计算所有列个数

分组数据

分组可以把数据分组后再使用聚合函数

select id ,count (*)
from a
group by id

先根据id分组，然后聚簇函数计算每种id有几条数据

group注意事项

groupby子句可以包含任意数目列，因此可以对分组嵌套

如果建立了分组，groupby会把分组当一整个数据来计算

group by中 不能使用聚合函数，

大多数sql不支持varchar使用在groupby

除了聚集函数外，select的每一列都得再group的条件里 不能使用别名

所有的null值是一个分组

group位于where之后order之前

过滤分组

where只能根据某一条件过滤行，没有分组的概念

比如，想要获得分组内大于两条数据的用户，这种必须基于组来操作

这时要使用 having语句 其也支持所有的通配符和操作符

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

子句顺序

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vzKEYBSe-1670463155107)(SQL必知必会第五版.assets/image-20221125132627733.png)]

子查询

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

子查询总是由内向外处理

子查询只能返回单列

计算字段的子查询

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

举例来说，在查数据时。比如到了某一条数据。子查询使用了cust_id字段。该字段默认是该条数据的字段，这样就完成了绑定的效果

完全限定列名 table.colume

联结表

连接是指 join

把数据分为多个表有利于数据结构，所以要分表

普通的多表联结

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id

该表使用where正确的联结， 必须使用完全列名

执行时是A表的每一个数据去匹配B表，同时where过滤

如果有多个where条件可以用and 链接，每一个and的条件都要完整的写出来

如果没有条件就会产生 笛卡尔积，a表每一条数据去匹配b表每条数据

笛卡尔积也称 叉联结 cross join

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

需要注意的是，以上这种多表联结非常损耗性能，不要联结不必要的表。表联结的越多，性能下降越严重。

虽然sql语法本身不限制联结表个数，但是具体的dbms会限制

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

innerjoin的语法

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'

多个innerjoin的用法，作用是链接多个表。

SELECT cust_name,SUM(orderitems.item_price*orderitems.quantity) as total
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 SUM(orderitems.item_price*orderitems.quantity) >=1000 
ORDER BY cust_name

Having那里不能用as取别名，所以需要在select那里重新计算一下

同时having也可以接筛选的工作，直接大于等于即可

高级联结

表别名

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
 AS vend_title
FROM Vendors
ORDER BY vend_name

sql除了可以给列名和计算字段赋别名之外，还可以给 表赋别名

表别名不会和列别名一样返回客户端

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 prod_id = 'RGAN01'

oracle中没有 AS.需要使用的话可以使用Customers C来起别名

自联结（self-join） 自然联结（natural join）外联结 （outer join）

自联结

假如要给与 Jim Jones 同一公司的所有顾客发送一封信件。这个查询要求 首先找出 Jim Jones 工作的公司，然后找出在该公司工作的顾客。下面是 解决此问题的一种方法：

SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
 FROM Customers
 WHERE cust_contact = 'Jim Jones');

cust_id cust_name cust_contact

1000000003 Fun4All Jim Jones 1

000000004 Fun4All Denise L. Stephens

如果使用自联结，同时搭配表别名。sql如下

select  c1.cust_id, c1.cust_name, c1.cust_contact
from customers as c1,customer as c2
where c1.cust_name=c2.cust_name and c2.cust_contact ='jim jones';

自然联结

默认情况下 自联结速度是比子查询要快的

默认情况下join的表至少有一个相同列（被join那个列）

所以一个表join出来本来会有多个相同的列，

所以显示哪些列就必须由你决定select

这就叫 自然联结

外联结

inner join只能联结有关联行的数据，比如都有cust_id等。

如果某一个表没有关联数据则不会被选中

比如左表是顾客表，右侧是订单表。左侧有没有消费过的顾客。

这样在join时是根据顾客id 来join。没有消费过的顾客在inner join时就不会选中

使用outer join 除了被联结的数据（类似inner join）还会有没有被选中的数据

据此，outer join使用时必须指定那一侧是全量数据，使用 Right或者Left

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

优化之一就是小表放左侧。

还有一种用法是全外联结 （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

注意事项

确保联结类型正确

每个dbms联结不同，请查看具体dbms的手册

提供联结条件，否则会产生 笛卡尔积

多表联结时，可以把一个一个join来排查错误

组合查询

常规情况返回的结果是一个sql的结果

可以通过 UNION（并）来将多条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'

ust_name cust_contact cust_email
----------- ----------- ----------------
Fun4All Denise L. Stephens dstephens@fun4all.com
Fun4All Jim Jones jjones@fun4all.com
Village Toys John Smith sales@villagetoys.com
The Toy Store Kim Howard NULL

通过union可以拼合两个sql的结果

union本身没什么问题，可以简化， 但是上例完全可以把所有where子句条件放到一个where里。

所以union大多数都是用于复杂的条件或者非常多个表联结是使用

union本身语法没有联结数量限制，但是具体请查看dbms文档

理论上来说多条where性能强于union，因为where会先晒出后分析，而union相当于操作两次或多次。

但是这只是理论上，具体 请实验

以下是注意事项

• union必须组合两个或多个sql。同时用union联结
• union的结果必须包含相同的列，聚合函数，或表达式。 但是显示顺序可以不一样

说明：UNION 的列名 如果结合 UNION 使用的 SELECT 语句遇到不同的列名，那么会返回什 么名字呢？比如说，如果一条语句是 SELECT prod_name，而另一条 语句是 SELECT productname，那么查询结果返回的是什么名字呢？ 答案是它会返回第一个名字，举的这个例子就会返回 prod_name，而 不管第二个不同的名字。这也意味着你可以对第一个名字使用别名， 因而返回一个你想要的名字。 这种行为带来一个有意思的副作用。由于只使用第一个名字，那么想 要排序也只能用这个名字。拿我们的例子来说，可以用 ORDER BY prod_name 对结果排序，如果写成 ORDER BY productname 就会出错， 因为查询结果里没有叫作 productname 的列。

消除重复的行

上例可以看到第一条sql返回3行。第二条返回2行。但是union后只有四行这是因为第一个sql结果和第二个sql结果中重复的行会被dbms自动抹去

如果想要返回两条sql所有的结果需要使用 union all

这意味着。如果你想返回所有的数据，只能用union all而不是where，因为where不会选中重复数据

对union结果排序

可以使用order by语句。其应处于最后一条sql后。会排序所有的结果。

且只可有一个order by子句不可以对单个语句排序

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'
ORDER BY cust_name, cust_contact;

插入数据

可以插入行 ，行的一部分，子查询

插入前请确保权限够

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(1000000006,
 'Toy Land',
 '123 Any Street',
 'New York',
 'NY',
 '11111',
 'USA',
 NULL,
 NULL)

提示：总是使用列的列表 不要使用没有明确给出列的 INSERT 语句。给出列能使 SQL 代码继续 发挥作用，即使表结构发生了变化

注意：小心使用 VALUES 不管使用哪种 INSERT 语法，VALUES 的数目都必须正确。

如果不提供列 名，则必须给每个表列提供一个值，如果为空的字段，可以使用NULL；

如果提供列名，则必须给列出的每 个列一个值。否则，就会产生一条错误消息，相应的行不能成功插入。

插入子查询

INSERT INTO Customers(cust_id,
 cust_contact,
 cust_email,
 cust_name,
 cust_address,
 cust_city,
cust_state,
 cust_zip,
 cust_country)
SELECT cust_id,
 cust_contact,
 cust_email,
 cust_name,
 cust_address,
 cust_city,
 cust_state,
 cust_zip,
 cust_country
FROM CustNew;

可以使用CTAS语法快速建表

create table xxx as select * from

更新和删除数据

更新数据

可以选择 更新选中的行，或者更新所有行

更新时一定要注意，一不小心就会更新所有的行。

update子句是where结束的，如果没有where，他就会更新所有的行

确定表，列名和新的值，确定过滤条件

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

需要注意的是，update不支持子查询更新多行。

删除数据

delete时要注意where子句，如果没有会删除所有行。

DELETE FROM Customers
WHERE cust_id = 1000000006

delete 语句删除表中所有的行，但是delete从来不删除表本身。

如果想更快的删除

TRUNCATE TABLE 语句，它完成相同的工作，而速度更快（因为不记录数据的变动）

更新和删除的前提

• 一定要，确保带where子句
• 保证每个表都有主键
• 使用前先用select先测试数据
• 使用完整性的数据库

创建表和操作表

创建表

CREATE TABLE Products
( 
 prod_id CHAR(10) NOT NULL,
 vend_id CHAR(10) NOT NULL,
 prod_name CHAR(254) NOT NULL,
 prod_price DECIMAL(8,2) NOT NULL,
 prod_desc VARCHAR(1000) NULL
)

更新表

ALTER TABLE Vendors
ADD vend_phone CHAR(20)

ALTER TABLE Vendors
DROP COLUMN vend_phone

更新表一般操作

(1) 用新的列布局创建一个新表；

(2) 使用 INSERT SELECT 语句（关于这条语句的详细介绍，请参阅第 15 课）从旧表复制数据到新表。有必要的话，可以使用转换函数和计算 字段；

(3) 检验包含所需数据的新表；

(4) 重命名旧表（如果确定，可以删除它）；

(5) 用旧表原来的名字重命名新表；

(6) 根据需要，重新创建触发器、存储过程、索引和外键

删除表

DROP TABLE CustCopy

该操作会删除表结构和表中数据，而不像delete仅删除表数据而不动表结构

删除表没有确认步骤， 也不能撤销，执行这条语句将永久删除该表

视图

视图时虚拟的表，视图本身不存储数据，甚至可以理解为一段存储的sql。只有使用时才执行。

非常类似于spark的transforming操作。

使用视图的原因

• 重用SQL语句
• 简化复杂SQL，编写查询时可以不知道内部复杂的细节
• 使用表的一部分而不是整个表
• 保护数据，可以给用户授予表的部分而不是全部权限
• 更改数据格式和显示，视图可以返回与表格式不同的数据

视图每次执行都会处理查询所需要的所有检索，所以对性能有要求

如果用多个联结，或者复杂过滤，非常复杂的sql会导致数据库性能下降。

所以使用视图时请进行测试。

使用视图的规则和限制

• 视图命名必须唯一
• 视图数目没有限制
• 视图可以嵌套，但是会导致严重的性能下降
• 有些DBMS会禁止orderby
• 有些DBMS会让视图只读

每个DBMS环境都不一致，具体请看DBMS手册

创建视图

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

删除视图使用drop view tablename

存储过程

什么是存储过程

实际开发中，一个业务流程会与多个业务耦合。

比如用户下单。涉及到订单记录，库存变更，用户记录。

所以一个业务流程实际是多个业务sql耦合起来。

而各个sql需要低耦合，这样可以分别维护。

存储过程就是保存一条或多条sql语句。将其分为批文件。

通过存储过程可以把复杂的处理过程封装为一个过程。

执行存储过程

存储过程的执行远比编写要频繁。

使用 execute即可执行

EXECUTE AddNewProduct('JTS01', 
 'Stuffed Eiffel Tower', 
6.49,
'Plush stuffed toy with 
➥the text La Tour Eiffel in red white and blue')

创建存储过程

CREATE PROCEDURE MailingListCount (
 ListCount OUT INTEGER
) 
IS
v_rows INTEGER;
BEGIN
 SELECT COUNT(*) INTO v_rows
 FROM Customers
 WHERE NOT cust_email IS NULL;
 ListCount := v_rows;
END;