sql必知必会

第一课了解sql

数据库（database）

表（table）储存在表中的数据时同一种类型的数据或清单

列（column）

行（row）

1.1主键 primart key

表中每一行都应该有一列可以唯一标识自己。

主键值能够唯一标识表中的每一行。

注：主键列不允许null，不允许修改或更新，不能重用。

第二课检索数据

2.1SELECT语句

2.1.1检索单列

1 2	select prod from products; #意为从表products中查询prod列

提示：结合sql语句

多条sql语句必须以分号(;)分隔，并且sql语句不区分大小写。

2.1.2检索多列

与单列相同，列名之间必须以逗号分隔，但是在最后一个列名的后面不加，否则会报错。

2.1.3检索所有列

在实际列名的位置使用星号通配符可以做到这点。

2.1.4检索不同值

想要返回不同的值，在列名前直接用distinct，则不会返回重复的数值。

2.1.5top关键字

在SQLserver中使用SELECT时，可以用TOP关键字来限制最多返回多少行。

1 2	SELECT top 5 prod_name FORM prducts; #检索products表中前五个列

2.1.6LIMIT关键字

如果使用mysql，mariaDB，postgresql，则用LIMIT关键字来限制

SELECT top 5 prod_name FORM prducts LIMIT 5;
#只返回五行数据
SELECT top 5 prod_name FORM prducts LIMIT 5 OFFSET 4;
#从第四行开始返回五行数据

第三课排列数据

3.1.1ORDER BY关键字

在SELECT语句最后使用，ORDER BY，对order by后面的列进行排序（列名之间用逗号分开），其后加数字也可以（相对列位置）。

第四课过滤数据

4.1使用WHERE子句

1	SELECT sex FROM member WHERE username= 'allen';

分析这条语句从menber表中检索了 username为allen那一行的sex列。

4.2where子句的操作符

有大于小于以及取非!，还有两个比较特殊的”BETWEEN AND” “IS NULL”。就像是python里的if一样，输出满足条件的那一行。

第五课高级数据过滤

5.1组合WHERE子句

5.1.1AND操作符

要通过不知一个列进行过滤，可以使用AND操作符给WHERE子句附加条件

1	SELECT sex FROM member WHERE username= 'allen' AND address = 'nba 76';

5.1.2OR操作符

就和其他的编程语言一样，or也是或运算。许多dbms在OR WHERE子句的第一个条件得到满足的情况下就不会计算第二个条件了。

5.1.3运算顺序

and的优先级是大于or的

5.2 IN操作符

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

1 2	select username from member where id in (1,3); #这个语句会找到id为1和3的数据，并不是开区间的意思，比起or更加简洁明了，执行起来也更加快捷。

5.3NOT操作符

WHERE子句中的NOT操作符有且仅有一个功能，那就是否定其后所跟的任何条件。

1 2	select username from member where NOT id in (1,3); #检索id不是1和3的数据。

第六课用通配符进行过滤

通配符（wildcard）:用来匹配值的一部分的特殊字符

搜索模式(search pattern):由字面值、通配符或两者组合构成的搜索条件。

6.1 LIKE操作符

6.1.1 百分号%通配符

最常使用的通配符，%表示任何字符出现任意次数。

1 2	select username from member where username LIKE 'L%'; #检索username以l开头的的行

6.1.2下划线_通配符

下划线的作用和百分号一样，但是它只匹配单个字符而不是多个字符。

6.1.3方括号[]通配符

方括号通配符用来指定一个字符集，他必须匹配指定位置(通配符的位置)的一个字符。

注意:并不是所有都支持集合，sqlserver支持，但是mysql，Oracle，db2，sqlite都不支持

1 2	select username from member where username like "[jm]%" #找出所有名字以j或者m开头的成员

第七课创建计算字段

7.1计算字段

储存在数据库表中的数据一般不是应用程序所需要的的格式，这就是计算字段可以派上用场的地方了。与前几课介绍的列不同，计算字段并不实际存在于数据库中。计算字段是运行时在SELECT语句内创建的。

7.2拼接字段

在不同的DBMS中会使用不同的拼接符号，大部分是+ 有些是||

例子：拼接由两列组成的标题。

1	SELECT age + username FROM Member ORDER BY uername

7.2.1补充去除空格的函数

1
2
3

rtrim(列名)去除右边的空格
ltrim(列名)去除左边的空格
trim(列名)去除两边的空格

7.3使用别名

select语句可以很好的拼接两个地址字段。但是这个新计算列的并没有名字，他只是一个值，我们可以用AS关键字给他赋予别名。

1	SELECT rtrim(age + username) AS totl FROM Member ORDER BY uername

7.4执行算数计算

计算字段的另一常见用途是对检索出的数据进行算术计算。

1 2	SELECT quantity,price,quantity*price AS final_price FROM order; #计算总的价格之后输出

8.使用函数处理数据

8.1使用函数

大多数sql实现支持一下函数：

用于处理文本字符串的文本函数，用于在数值计算上的算数操作函数，用于处理日期和时间值并从这些值中提取特定数值的日期和时间函数，用于生产美观好动的输出内容的格式化函数，返回dbms特殊信息的函数。

8.1.1文本处理函数

UPPER()输出转化为大写

1	select upper(username) from users;

下面介绍常用的文本处理函数

rtrim(列名)去除右边的空格
ltrim(列名)去除左边的空格
trim(列名)去除两边的空格
left()返回字符串左边的字符
length()返回字符串的长度
lower()将字符串转换为小写
right()返回字符串右边的字符
substr或者substring()提取字符串的组成部分
soundex()返回字符串的soundex值

其中，soundex是一个将任何文本串转换为描述其语音表示的字母数字模式的算法

8.1.2日期和时间处理函数

每种dbms都有自己的特殊形式的日期和时间。

例子：在sqlsever中检索2020年所有的订单，可如下进行：

1
2
3

select order_num
from Orders
where DATAPART(yy,order_date) = 2020;

8.1.3数值处理函数

这些函数，一般主要用于，代数，三角或集合运算，因此不想字符串和时间那样使用的平凡。

ABS()返回一个数的绝对值
COS()返回一个角度的余弦
EXP()返回一个数的指数值
PI()返回圆周率π的值
SIN()返回一个角度的正弦
SQRT()返回一个数的平方根
TAN()返回一个角度的正弦

第九课汇总数据

9.1聚集函数

有时候需要汇总数据但是不用把他们实际检索出来、这种类型的检索例子有：

确定表中的行数

获得表中某些行的和

找出表列的最大值、最小值、平均值。

sql聚集函数
AVG()返回某列的平均值
COUNT()返回某列的行数
MAX()返回某列的最大值
MIN()返回某列的最小值
SUM()返回某列值之和

9.1.1AVG()函数

可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。

1	select avg(id) from member;

返回id的平均值。注意，avg()函数忽略列值为NULL的行。

9.1.2COUNT()函数

可利用count 确定表中行的数目或符合特定条件行的数目。

count()函数有两种使用方法

使用count(*)对表中行的数目进行计数，不管表列中包含的是空值还是非空值。

使用count(colums)对特定列中具有值的进行行计数，忽略null。

select count(*) as num_cust from member;
计算有多少行
select count(cust_email) as num_cust from member;
计算有邮箱的用户有多少个

9.1.3max()函数

返回指定列中的最大值，要求指定列名。

1	select max(id) as max_id from member;

9.1.4min()函数

返回指定列中的最小值，要求指定列名。

1	select min(id) as min_id from member;

9.1.5sum()函数

用返回指定列的和

1	select sum(id) as sum_id from member;

9.2聚集不同值

以上五个聚集函数都可以如下使用：

对所有行执行计算，指定all参数或不指定参数（因为all是默认行为）。

只包含不同的值，指定distinct参数。

1	select avg(distinct id) from member;

9.3组合聚集参数

实际上，selct语句可以根据需要包含多个聚集函数。中间用逗号隔开

1	select avg(distinct id),min(id) from member;

10.分组数据

10.1数据分组

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

10.2创建分组

分组是使用SELECT语句的GROUP BY子句建立的。

1	select username,count(*) as sum from member group by username;

由于使用了group by语句，count函数会对每一个组进行计算。这个语句的意思就是，检索每一个用户有多少条数据。

注意，group by子句必须出现在where子句之后，order by 子句之前。

10.3过滤分组

虽然where可以进行数据的过滤，但是因为where过滤指定的是行而不是分组，实际上，where子句并没有分组的概念。

这时候我们可以用having子句来代替，他们的差距就是where过滤行，having过滤分组。

1 2	select username,count() as sum from member group by username having count()>=2; 这个语句的意思就是，检索有两行以上上数据的用户名字。

注意：having要放在group by后面。

10.4select子句顺序

下面回顾一下select语句中子句的顺序。

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

第十一课使用子查询

11.1子查询

sql支持创建子查询，即创建在其他查询中的查询。

下面举几个例子

11.2利用子查询进行过滤

select cust_id
from Orders
where order_num IN (select order_num
                   from OrderItems
                   where prod-id = 'RGAN01')

这里就限制了order_num的范围，在select语句中，子查询总是从内向外处理。而且对于子查询的嵌套没有限制。

第十二课联结表

12.1联结

sql最强大的功能就是在数据查询的执行中联结(join)表。联结是利用sql的select能执行的最重要的操作。

12.1.1创建联结

创建联结非常简单，指定要连接的所有表以及关联他们的方式即可。

下面看一个例子

select vend_name,prod_name,prod_price
from Vendors,Products
WHERE Vendors.vend_id = Products.ven_id;
#where子句指示dbms将Vendors表中的vend_id与Products表中的vend_id匹配起来。

12.1.2内连接

目前为止使用的联结称为等值联结，他基于两个表之间的相等测试，这种联结也称为内连接。

第十三课创建高级联结（未完）

13.1使用表别名

同样是在表的后面使用as关键字即可，

第十四课组合查询

14.1组合查询

sql也允许执行多个查询，(多条sql语句)，并将结果作为一个查询结果集返回，这些组合查询通常称为并(union)或复合查询(xompound query)

14.2创建组合查询

14.2.1使用UNION

给出每条SELECT语句，在每条语句之间放上关键字union。

1	select username from users where id = 1 union select username from member where id = 2;

虽然sql没有对union数量进行限制，但是最好参考一下dbms的文档。

14.2.2union规则

1.union必须有两条或两条以上的select语句组成，语句中间用union分隔。

2.union中的每个查询必须包含相同的列、表达式或聚集函数。

3.列数据类型必须兼容，类型不必完全相同，但是必须是dbms可以隐含转换的类型。

14.2.3包含或取消重复的行

union会自动将重复的行删除，如果想要返回重复的行，使用union all即可

14.2.4对组合查询结果排序

在使用union时，只能使用一条ORDER BY语句，必须位于最后一条select语句的后面，不存在用一种方式排列一部分。

第十五课插入数据

15.1数据插入

除了select语句，sql同样还有其他三个常用的语句。数据插入就是INSERT

顾名思义，insert用来将行插入到数据库中，插入有几种方式；

插入完整的行；

插入行的一部分；

插入写查询的结果；

15.1.1插入完整的行

1 2	INSERT INTO member VALUES('8','test','asdasdasd','boy','1111111111','chain','aaa@pikachu.com');

注意：必须给每一列都提供一个值，如果没有值，那也必须输入null。

虽然上述方法比较简单，但是并不安全，因为他依赖于列的顺序，很有可能就会打乱我们的数据库，下面介绍一种更加安全的方法。

1
2
3

INSERT INTO member('id','username','pw','sex','phonenum','address','email'
)
VALUES('8','test','asdasdasd','boy','1111111111','chain','aaa@pikachu.com');

虽然这种方法与上面功能一样而且更加繁琐但是不会打乱我们的数据库，因为明确给出了每一列的列名，就像python中的基于索引和基于名字的参数。

15.1.2插入部分行

正如所述，使用INSERT的推荐方法是明确给出表的列名。用这种语法还可以省略列，这表示可以只给某些列提供值，给其他列不提供值。

1
2
3

INSERT INTO member('pw','sex','phonenum','address','email'
)
VALUES('asdasdasd','boy','1111111111','chain','aaa@pikachu.com');

这里就没有输入id 和username这两行，这表示insert语句省略了这两列，如果省略了，有默认值就是默认值没有就是null。

15.1.3插入检索出的数据

可以使用INSERT SELECT来实现插入检索出的数据

INSERT INTO member('id','username','pw','sex','phonenum','address','email'
)
SELECT id,username,pw,sex,phonenum,address,xxx
FROM xxx;

15.2从一个表复制到另一个表

有一种数据插入不使用insert语句，要将一个表的内容复制到一个全新表(运行中创建的表)。

使用CREATE SELECT

1	CREATE TABLE custcopy AS SELECT * FROM customers;

第十六课更新和删除数据

这一课介绍如何利用UPDATE 和 DELETE语句进一步操作表数据。

16.1更新数据

更新(修改)数据，可以使用UPDATE语句，有两种使用的方法：

更新中的特定行

更新表中所有行

基本的UPDATE语句由三部分组成，分别是：要更新的表，列名和他们的新值，确定要更新哪些行的过滤条件。

1
2
3

UPDATE copytable 
SET username = 'xiugai'
WHERE id = 1;

上面语句就是修改id为1的用户的名字。

在更新多个列时，只需要使用一条set命令，每个“列=值”对之间用逗号分隔。

16.2删除数据

从个表中删除数据，使用DELETE语句，有两种使用方式：

从表中删除特定的行

从表中删除所有行

1 2	DELETE FROM copytable WHERE id = 1;

删除了表copytable中id为1的用户。如果省略WHERE语句，他就会删除整个表。update也一样。

第十七课创建和操纵表

17.1创建表

一般有两种创建表的方法：

多数dbms都具有交互式创建和管理数据库表的工具

表可以直接用sql语句操纵

17.1.1表创建基础

利用CREATE TABLE创建表，必须给出下列信息：

新表的名字，在create table关键字之后给出

列表的名字和定义，用逗号分隔

有的dbms还要求指定表的位置

CREATE TABLE new_table
(id CHAR(10) NOT NULL,
 passwd CHAR(10) NOT NULL
);

这里创建了一个名叫new_table 的表，表中有两列，一列是id 一列是passwd，后面跟了数据类型。

17.1.2使用null值

每个表列要么是NULL列，要么是NOT NULL列，有null的时候那个列的默认值就是null。

17.1.3指定默认列

默认值在CREATE TABLE语句的列定义中用关键字DEFAULT指定

CREATE TABLE new_table
(id CHAR(10) NOT NULL,
 passwd CHAR(10) NOT NULL DEFAULT 1
);

17.2更新表

更新表定义，可以使用ALTER TABLE语句，虽然所有的dbms都支持这个语句，但是他们的允许更新的内容差别很大。

所以在这里给出都支持的操作。

1 2	ALTER TABLE new_table ADD name CHAR(20)

在new_table 表中创建了添加了一个新的列，名为name。

17.3删除表

使用DROP TABLE语句即可

1	DROP TABLE new_table;

17.4重命名表

每个dbms对表重命名的支持有所不同，对于这个操作，不存在严格的标准。

mysql用户使用rename语句，其他的大伙自行查阅即可。

第十八课使用视图

18.1视图

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

当我们需要查询多个表的数据时，可能会运用表的联结，但是如果把多个表包装成名为xxx的虚拟表，则可以让我们的检索代码更加简洁。

18.1.1视图的一些规则和限制

与表一样，视图必须唯一命名。

对于可以创建的视图数目没有限制。

视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造视图。

许多dbms禁止在视图中使用order by

18.2创建视图

用CREATE VIEW 语句来创建，并且只能用于创建不存在的视图。

create view xxx as
select xxx,xxx
from xxx,xx
where xxx

第十九课使用存储过程

19.1存储过程

简单的说，存储过程就是为以后使用而保存的一条或多条sql语句，可将其视为批文件，虽然他们的作用不仅限于批处理。#可以理解为python中创建一个函数

19.2执行储存过程

存储过程的的执行远比编写繁琐得多，所以我们先介绍存储过程的执行，

EXECUTE AddNewProduct('JTSO1',
                      'Stuffed Eiffel Tower',
                      6,49,
                      'Plush stuffed toy withxxxx');

这里执行了一个名为AddNewProduct的存储过程，佳宁一个新产品添加到Product表中，里面有四个参数，供应商，产品名，价格和描述，

19.3创建存储过程

下面的例子是SqlServer版本?为什么作者说执行的过程比创建的过程更加繁琐？？？？

CREATE PROCEDURE Malilinglistcount 
AS 
DECLARE @cnt INTEGER
SELECT @cnt = COUNT(*)
FROM Customers
WHERE NOT cust_email IS NULL
RETURN @cnt;

分析：申明了一个局部变量@cnt，然后在select语句中使用这个变量让他包含count()函数返回的的值，最后用return输出。

第二十课管理事务处理

20.1事务处理

使用事务处理，通过确保成批的sql操作要么完全知晓，要么不完全执行，来维护数据库的完整性。

下面是事务处理的几个关键词

事物 transaction 指一组sql语句
回退 rollback 指撤销指定sql的过程
提交 commit 指将为存储的sql语句结果写入数据库表
保留点 saepoint 值事务处理中设置的临时占位符，可以对他发布退回

20.2控制事务处理

不同的dbms有不同的语法。

下面举sql server

1
2
3

BEGIN TRANSACTION

COMMIT TRANSACTION

20.2.1使用ROLLBACK

sqk的rollback用来回撤sql语句，

1 2	DELETE FROM Orders ROLLBACK

20.2.2使用commit

一般的sql语句都是针对数据库表直接执行和编写的。这就是所谓的隐式提交(implicit commit)，即提交操作是自动进行的。

在事物处理块中，他并不会隐式提交，有的dbms又是。

下面是sql server 的例子

1
2
3

BEGIN TRANSACTION
DELETE Order
COMMIT TRANSACTION

第二十一课使用游标

20.1 游标

在使用游标前，必须声明他。

一旦声明，就必须打开游标以供使用。

对于填有数据的游标，根据需要取出(检索)各行。

在结束游标使用时，必须关闭，

20.1.1创建游标

使用DECLARE语句创建游标，这条语句在不同的dbms中有所不同。

DECLARE命名游标，并定义相应的select语句。

下面是mysql和sqlserver的例子

1
2
3

DECLARE CustCUrosr CURSOR
FOR 
SELECT * FROM Customers

20.1.2使用游标

使用OPEN CURSOR 语句打开游标，这条语句很简单，在大多数dbms中的语法相同：

1	OPEN SURSOR CustoCurso

现在可以用FETCH语句访问游标数据了，指出要检索哪些行，从何处检索他们以及将他们放于何处。

DECLARE TYPE CustCursor IS REF CURSOR 
    RETURN Customers%ROWTUPE;
BEGIN
    OPEN CustCursor;
    FETCH CustCursor INTO CustRecord;
    CLOSE CustCursor;
END;