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;