sql必知必会

大家好，欢迎来到IT知识分享网。

1 数据库概念

1.1 数据库的基本概念

数据库是数据的汇集，它是以某种有组织的方式存储的数据集合。例如通过文件柜存放文件，那么这个文件柜就是数据库。可见，数据库是保存数据的容器，不管数据是什么、也不管数据是如何组织的。

PS: 人们通常所说的数据库，是指使用的数据库软件，所以容易与上述阐述矛盾、混淆。而数据库软件不叫数据库，而是叫做数据库管理系统，DBMS。

数据库的发展阶段：

• 萌芽阶段：文件系统。使用磁盘文件来存储数据。文件管理系统紧紧存储数据，编写应用程序分析数据、处理数据不方便；难以按用户需求表示数据；无安全控制等。
• 初级阶段：第一代数据库。出现了网状模型、层次模型的数据库。对应的数据库管理系统DBMS
• 中级阶段：第二代数据库。关系型数据库和结构化查询语言
• 高级阶段：新一代数据库。“关系-对象型“数据库。

DBMS: 通过管理数据库(例如mysql)的系统软件–数据库管理系统DBMS，来创建、操纵数据库，实现数据库系统的各种功能，DBMS是数据库系统的核心。数据库管理系统DBMS的基本功能：

• 数据定义：指在数据库定义创建的数据(包括数据名称、数据类型)。
• 数据处理：增加、修改、删除、查询。
• 数据安全：设置用户及权限。
• 数据备份：重要数据备份，避免数据丢失。

PS：数据库管理系统DBMS的优点：相对文件管理系统，DBMS具有并发访问，保证数据安全，可设置用户、设置访问权限等优点。

数据库系统的架构：

• 单机架构：在一台机器上操作。单机架构无法满足多人同时办公。例如，一个单机架构的财务系统，多个财务人员无法共用操作。
• 大型主机/终端架构：一个服务器通过网络与多个终端连接(显示器、键盘)，并发支持多个终端。
• 主从式架构C/S：专门的服务器、专门的客户端，服务器负责数据的管理，客户端负责完成与用户的交互任务。C/S架构适用于小型局域网。很多大型财务系统采用C/S架构。
• 分布式架构：一个服务器承载不了大量用户的并发请求，采用多个服务器提供数据库服务。数据分散到多个服务器。

数据库数据模型

• 层次型数据库：层次数据模型是用树状<层次>结构来组织数据的数据模型。
• 网状型数据库：用有向图表示实体和实体之间的联系的数据结构模型称为网状数据模型。
• 关系型数据库： 使用表格表示实体和实体之间关系的数据模型称之为关系数据模型。

三种模型详细总结blog

1.2 关系型数据库

关系型数据库(RDBMS)的基本概念：
关系：关系就是二维表table。而二维表是某种特定类型数据的结构化清单。存储在表中的数据是同一种类型的数据或清单，不应当将不相干的数据放到同一张表中。一个数据库中表名是唯一的。
二维表table有如下概念：

列column：表中的每一列。表由列组成，列是属性字段，每一列就是一个属性。例如班级学员信息表，每个学员信息由姓名、性别、年龄、籍贯等属性组成，每一条属性占据一列。数据库中每一列都有相应的数据类型(datatype)。
域domain：属性的取值范围，如性别只能是“男”、“女”。
行row：表中的每一行，是一条记录。行和列的顺序不重要。
主键Primary key：用于唯一确定一个记录的字段。主键由一列或几列组成，其值能够唯一标识表中的每一行。一张表中主键列不允许为NULL，主键值不能重复，且不能修改。
在使用多列作为主键时，所有列值的组合必须是唯一的（但单个列的值可以不唯一）。
二维表中，一行就是一条记录，例如班级学员信息，每一个学员信息记录就是一行，这存在一个问题，某个学员有可能被记录多次，即表中完全相同的信息被记录了多次，浪费了空间。主键就是解决这个问题的，不同学员的姓名、籍贯等信息可以相同，但学号是唯一的，显然学号就可以作为主键，保证二维表中不会出现重复记录。
唯一建unique： ：一个或多个属性字段的组合，填入的数据必须能在本表中唯一标识本行。例如身份证号、电话号码，身份证号、电话号码不可能被多个人使用，所以是唯一的。可以不填，为NULL

事务transaction：事务是一个完整的过程(完整的业务逻辑单元)，这个过程可能包含多个操作，数据库操作中，必须保证这些操作同时成功或同时失败。
和事务相关的语句只有：DML语句(delete insert update),这三个语句都是和数据库表当中的“数据”相关。事务具有ACID特性：

• A，原子性：事务是最小的工作单元，不可再分。

例如去ATM机取钱，包含多个步骤组成，但是取钱、退卡、后台扣除取款额，这些操作要么全部完成，要么都不做。又如一项程序操作事务，如果操作过程中出故障了没做完，系统恢复后，发现这不是一项完整的事务，则前面已完成的操作将会被rollback。

• C，一致性：事务必须保证多条DML语句同时成功或者同时失败。并发访问涉及的问题。
• I，隔离性：两个事务之间同时操作一个数据(一个修改、一个删除)，可能产生冲突。隔离性就是解决这个问题的。
• D，持久性：持久性是指最终数据必须持久化到硬盘文件中，事务才算成功的结束。

表与表之间的联系类型
一对一：一张表的一条记录对应另一张表的一条记录
一对多：一张表的一条记录对应另外一张表的多条记录
多对多：通常用第三张表来解决多对多的关系。

例如课程表，学员表，一个学员会选择多门课程，难道每选一门课程就在学员表中增加一条记录吗？这样岂不是学员表一个学员被重复记录了多次，不能这么设计。这个时候就可以利用第三张表学员-课程表解决这种多对多的关系。
学员表与学员-课程表是一对多的关系，这个时候也叫主键-外键关系，因为表中学员-课程表的学员属性ID来自于学员表中的学员属性ID；课程表与学员-课程表也是一对多的关系。
外键：一个表中的某字段可填入的数据取决于另一个表的主键或唯一键已有的数据

多对多关系

RDMBS设计范式基础概念
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同范式，各种范式呈递次规范，越高的范式数据库冗余越小。
目前关系数据库有六种范式：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴德斯科范式(BCNF)、第四范式(4NF)和第五范式(5NF，又称完美范式)。满足最低要求的范式是第一范式(1NF)。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF)，其余范式以次类推。
一般说来，数据库只需满足第一范式(1NF)、第二范式(2NF)、第三范式(3NF)即可。

• 第一范式，1NF：无重复的列，每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。除去同类型的字段，就是无重复的列。说明：第一范式(1NF)是对关系模式的基本要求，不满足第一范式(1NF)的数据库就不是关系数据库

例如：一个学员可以选择多门课程，如果在学员信息表中列出课程属性，每一门课程占用一列，这些列都是课程属性，这就违反了第一范式。应当把课程属性拆除了单独做一张表。

• 第二范式，2NF：属性完全依赖于主键。第二范式必须先满足第一范式，要求表中的每个行必须可以被唯一地区分。通常为表加上一个列，以存储各个实例的唯一标识PK，非PK的字段需要与整个PK有直接相关性

举个栗子：复合主键表中，由多个属性组成主键。第二范式要求主键以外的属性与主键完全关联。比如一张学员信息表，包含姓名、所在城市、城市区号，这张表设计的主键为姓名+城市的复合主键，然而属性城市区号仅与复合主键中的城市有依赖关系，而与姓名毫无关系，这就违反了第二范式。这个时候应该把区号拆出去单独做一张表。

• 第三范式，3NF：属性不依赖于其它非主属性，满足第三范式必须先满足第二范式。第三范式要求一个数据库表中不包含已在其它表中已包含的非主关键字信息，非PK的字段间不能有从属关系。

第三范式，指非主键以外的属性，属性之间无依赖关系。还是以学员信息表为例，包含姓名、身份证号、城市、城市区号，这次以身份证号作为主键，显然表中城市和城市区号存在依赖关系，违反了第三范式，应当把城市区号拆出去另起一张表。

PS: 所谓的范式并不是必须要遵守的，某些特定场景有可能违反第二、第三范式，

比如在业务中高性能是第一要素，上述栗子中为了遵守第二、第三范式，把城市区号拆除前单独做了一张表，这个时候就需要跨表查询区号，必然性能下降。为了满足性能要求，就需要故意违反第二、第三范式，把城市区号也放在一张表中。

2 SQL

SQL(Structured Query Language：结构化查询语言)，是一种专门用来与数据库沟通的语言。SQL的优点：

• SQL不是摸个特定数据库厂商的专有语言。绝大多数重要的DBMS支持SQL，所以学习了SQL就能够与几乎所有数据库打交道；
• 简单易学。SQL语句全都是由很强描述性的英语单词组成；
• 功能强大，通过简单的语句组合，可以进行非常复杂和高级的数据库操作。

PS：作为python开发程序员来说，真正做开发往往不关注后台使用的数据库是什么，而是怎么去存放、访问数据。管理数据属于运维人员。

2.1 基本概念

关键字(keyword)：SQL语句是由简单的英语单词构成的。这些单词称为关键字，每个SQL语句都是由一个或多个关键字构成的。作为SQL组成部分的保留字。关键字不能用作表或列的名字。

• 非交互执行命令，命令要用引号括起来，windows要使用双引号：mysql-uroot-pyourpasswd -e "show datebases;"
• 查看表状态：show table status like 'table_name' \G；\G的作用：是将查到的结构旋转90度变成纵向
• 修改密码：set password for userinfo=password('yourpassword');

索引：索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

关系运算：

• 选择：挑选出符合条件的行
• 投影：挑选出需要的字段
• 连接：表间字段的关联

2.2 检索数据

选出指定的列：select columns from table;从指定的表中选出指定的一列或多列，多列以逗号，分隔；使用通配符*选出所有列。多条SQL语句必须以分号(;)分隔。sql语句不区分大小写。sql语句忽略空格，且一条语句可以分在多行，分号;结束。

使用通配符*会降低查询性能，所以分必要不用*。

过滤相同的值distinct：select distinct columns from table; 使用distinct可以过滤掉一列中相同的值，同一个值只保留一个。一般只适用于过滤一列，多列的请求除非两列完全相同。

限制结果输出：

select columns from table fetch first 5 rows only;提取前5行。
select columns from table limit 5;提取前5行。
select columns from table limit start, rows; start为起始行位置，rows为需要提取的行数。注意，最小行索引为0．

注释：三种方法

select * from table; --这是注释； 行内注释：--，在一行内有效
#这是注释；整行注释：在一行的开始处使用#，这一整行都将作为注释
/*这是注释*/；多行注释：/*表示注释开始位置，*/`表示注释结束位置。

2.3 排序检索数据

关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有任何意义。用SELECT 语句检索出的数据，可使用ORDER BY 子句进行排序，指示DBMS软件按指定列以字典顺序排序。

例如：select * from table oder by colunm_name。
order by 子句必须紧邻select子句，例如：select * from products order by prod_name fetch first 5 rows only;
在同时使用order by 和where 子句时，应该让order by 位于where之后
order by 子句后的列名不一定是select子句中选择的列名，只要在table内即可。

按多列排序：

• 经常需要按不止一个列进行数据排序。例如，如果要显示雇员名单，可能希望按姓和名排序(首先按姓排序，然后在每个姓中再按名排序)。如果多个雇员有相同的姓，这样做很有用。 要按多个列排序，简单指定列名，列名之间用逗号分开即可

例如: select * from products order by prod_id, prod_price, prod_name;。当存在prod_id相同的行时，才会在prod_id相同的行中按prod_price排序，以此类推。

• 按select子句中选择的列序号(从1开始)进行排序

例如：select prod_id, prod_price, prod_name from products order by 1, 2;。1表示prod_id，2表示prod_price。可以指定多个，排序原则同上。而且可以混合使用select子句选择的列序号和实际的列名。

• 降序排列。order by默认是升序排列。使用desc或descending，实现降序排列。

例如： select * from products order by prod_name, prod_id desc;。
注意：desc只作用于其前面的紧邻的列名，如果每一列都要降序排列，则每一列都要单独声明desc。
排序也不区分大小写，a与A相同。

2.4 过滤数据

在SELECT语句中，使用where子句指定搜索条件进行数据过滤。WHERE 子句在表名(FROM 子句)之后给出。在同时使用order by 和where 子句时，应该让order by 位于where之后。

例如：select * from orderitems where item_price>=9.99 order by item_price;。
又如：select prod_name, prod_price from products where prod_id!='FC'。注意：如果将值与字符串类型的列进行比较，就需要限定引号。用来与数值列进行比较的值不用引号。

范围检查：使用 where...between A and B子句。注意 between是闭区间[A,B]。

例如：select * from products where prod_price between 5 and 10;

NULL检查： null，无值(no value)，它与字段包含0、空字符串或仅仅包含空格不同。

例如：select * from customers where cust_email is NULL;
注意：确定值是否为NULL，不能简单地检查是否= NULL，而应当用is null子句。大小写不敏感。

where子句操作符：

where子句操作符

过滤数据的意义在于，分析数据通常只会根据特定操作或报告的需要提取表数据的子集，而不是所有数据。客户端请求数据，只关心需要的数据，应在服务端进行过滤，而不需要冗余的数据浪费网络带宽。

2.5 高级数据过滤

2.4节中，where子句数据过滤的条件都只有一个，可以使用and、or、in、not操作符，组合多个过滤条件，进行数据过滤。

使用and操作符: select * from products where prod_price between 4 and 10 and prod_id != 'anv01' order by prod_price;
使用or操作符：select * from products where vend_id=1001 or vend_id=1002 order by prod_price;
使用in操作符：select * from products where vend_id in(1002, 1001, 1003) order by prod_price;。实际上，in操作符等价于or操作符，in操作符相对or操作符表达更简洁清晰，效率更高；

混合使用and操作符和or操作符：select * from products where (vend_id=1001 or vend_id=1002) and prod_price between 4 and 8 order by prod_price;
注意：混合使用and操作符和or操作符时，and操作符优先级更高，为解决优先级问题，多个or操作符应道使用圆括号括起来，或者使用in操作符替换。

not操作符：select * from products where not vend_id in (1001,1002) and prod_price between 4 and 8 order by prod_price;。
注意，where子句中的not操作符用来否定其后的条件，不能单独存在。 存在多个条件时，只对not紧跟的一个条件有效。

2.6 用通配符(wildcard)进行过滤

通配符实际上是SQL的where子句中有特殊含义的字符，SQL支持几种通配符。使用通配符进行模糊匹配，可以过滤出包含特定字符的数据。注意：通配符搜索只能用于文本字段(字符串)。

百分号%通配符：%表示任何字符出现0次、1次或多次。例如：

搜索s开头，e结尾的产品prod_name：select * from products where prod_name like 's%e';。
注意：DBMS存储字符串时，通常用空格来填补字段的内容。例如规定产品名这一列的长度为50个字符，不足50用空格填充，这样的话，使用%通配符，搜索特定字符开头和结尾，结果不准确。可以使用trim函数去掉空格：ltrim删除字符串左边的空格；rtrim删除字符串右边的空格；trim，删除左右两边的空格。
NULL是一个值，不能用%匹配。

下划线_通配符：_匹配任意字符1次，例如：搜索第二个字符为a的产品名：select * from products where prod_name like '_a%';

使用方括号通配符：内指定一个字符集，用于匹配字符集内任意一个字符1次。注意：只有Access 和SQL Server支持集合[]，mariadb不支持。例如匹配以N或A开头的城市：select * from vendors where vend_city like '[NA]%';

脱字号^在方括号开始位置，表示不匹配后面的字符：select * from vendors where vend_city like［^abc］%；也可以使用操作符not，脱字号相对not在多重where子句中语法更简洁。

小结： 通配符搜索效率较低，把通配符置于开始处，搜索起来是最慢的。

2.7创建计算字段

字段(field)。从数据库中检索出的数据，经转换、计算或格式化后的数据列就是计算字段；计算字段是运行时在select语句内创建。注意，列(column)是存在于数据库表格中的原始数据，而计算字段(field)是运行时对数据处理后产生的。

• 拼接(concatenate)字段：Access 和SQLServer 使用+号拼接，oracle等使用||拼接，mysql、mariadb使用concat函数拼接

例如，使用||拼接：select vend_name '(' || vend_country || ')' from vendors order by vend_name;

mysql、mariadb使用concat函数拼接厂商名称及其所在地区：select concat(vend_name,'(',vend_country,')') from vendors order by vend_name;
注意：很多DBMS默认输出指定长度的字符串，不足部分用空格填充。可使用sql的trim函数删除空格。例如：
select concat(trim(vend_name), '(',trim(vend_country), ')') as vend_info from vendors order by vend_name;

使用别名alias：如上例所示，select语句中，使用as关键字，可对检索的column或field起别名，在输出的列名，将以别名显示。客户端请求的数据，服务端如起了别名，客户端感知的列名就是别名。

• 在很多DBMS中，AS关键字是可选的，不过最好使用它，这被视为一条最佳实践。 别名也称导出列(derived column)

执行算数运算：计算字段的另一常见用途是对检索出的数据进行算术计算。sql支持的计算算法操作符有＋,-,*,/，配合使用圆括号区分优先顺序。

2.8 使用函数处理数据

不同的DBMS函数名称和语法可能及其不同，因此，与SQL语句不一样，SQL函数不是可移植的。编写的sql代码一定要注释基于何种DBMS编写。为了提高可移植性，尽量少用函数

文本处理函数：常用的文本处理函数：trim、rtrim、ltrim、soundex、upper、lower。

在select语句中使用upper函数：select vend_city，upper(vend_city) as vend_city_case from vendors order by vend_city;
soundex函数：根据发音，模糊匹配发音相同的单词，例如在where子句中使用soundex过滤：select cust_name, cust_contact from customers where soundex(cust_contact)=soundex('Michaelle Green');

日期和时间处理函数：注意不同DBMS时间和日期处理函数差异很大。

Mysql和mariadb使用year()函数，提取年份：select * from orders where year(order_date)='2002';

数值处理函数：不同DBMS数值处理函数基本相同：abs、cos、exp(返回一个指数值)、pi、sin、sqrt、tan等。

例如，求和：select sum(item_price) from orderitems;

2.9汇总数据

客户端只关心表中某一列的最大值、最小值、平均值，或者表的行数，而不关心表的详细信息，在服务端则可使用聚集函数进行数据处理，并将处理后的数据返回给客户端，而不是将所有数据扔给客户端，让客户去处理。

聚集函数(aggregate function).对某些行运行的函数，计算并返回一个值。SQL支持的聚集函数有：avg()，count()，max()，min()，sum()。不同DBMS的聚集函数名和功能都相同，不用担心。

使用avg函数，求指定厂商的商品平均价格：select avg(prod_price) as avg_price from products where vend_id=1003; 注意*：avg函数自动忽略null行

使用count函数，统计指定列的行数：select count(vend_state) from vendors;。
注意：count(*)统计表的总行数，不管表的一行的值是否为null；使用count(column)对特定列中具有值的行进行计数，忽略null值。

使用sum函数，计算购物车物品数量列的指定订单的物品总数：select sum(order_item) from orderitems where order_num=20005; 注意： sum函数忽略null行。

使用max函数，计算指定厂商最贵的产品：select max(prod_price) from products where vend_id=1003;
注意：max函数和min函数在用于文本数据时，max()返回按该列排序后的最后一行，min()返回最前面一行，忽略值为null的行。

聚集不同的值：distinct：对所有行进行计算时，如果只关心每一行中不同的值时，使用distinct参数。默认为all参数，省略

例如：使用AVG()函数返回特定供应商提供的产品的平均价格(相同的价格只统计一次)：select avg(distinct prod_price) from products where vend_id=1003;
对比不使用distinct：select avg(prod_price) from products where vend_id=1003;'
注意：distinct不能作用于count(*)，而可以作用于count(column)。原因从二者定义就很清晰。

其他聚集函数：例如，limit、fetch、top等。

例如，提取价格前五的物品：select * from products limit 5;

*组合聚集函数：上述聚集函数例子都只涉及单个函数。实际上SELECT 语句可根据需要包含多个聚集函数。

2.10 分组数据

前述数据处理，都是基于所有数据或满足where子句条件的数据上进行的。很多场景，用户需要根据一定条件进行分组分析，此时使用group by子句。

• GROUP BY子句可以包含任意数目的列，意思就是说：可以按多列的值进行层层分组，例如group byvend_id，prod_id，则先按厂商ID分组，然后再按产品名分组。
• 除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出
• GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

过滤分组：即分组后，再对分组进行过滤，检索出满足条件的分组。使用having子句。

having与where的差异：where在数据分组前进行过滤，having在数据分组后进行过滤。

小结：select子句及其顺序

2.11 使用子查询subquery

关系型数据库中的表都是关系表，表与表之间有关联关系。例如customers表存储的是顾客信息，orders表中存储的订单信息，orderitems表汇总存储的是订单物品信息。客户需要分析订购ANV01商品的所有顾客。怎么处理：

1、先从orderitems表中查询商品对应的订单号；select order_num from orderitems where prod_id='ANV01';
2、再从orders表中查寻订单号对应的顾客ID；select cust_id from orders where order_num in (20005);
3、最后从customers中查询具体的顾客信息；select * from customers where cust_id in (10001);
利用子查询，将上述三个查询操作组合成一个sql语句：select * from customers where cust_id in (select cust_id from orders where order_num in (select order_num from orderitems where prod_id='ANV01'));

注意：作为子查询的SELECT 语句只能查询单个列。企图检索多个列将返回错误。

使用子查询作为计算字段：显示customers表中，每个顾客的订单总数。

注意： 子查询中的WHERE 子句与前面使用的WHERE 子句稍有不同，因为它使用了完全限定列名， 而不只是列名。用一个句点分隔表名和列名，在有可能混淆列名时必须使用这种语法。

2.12 联结表

SQL 最强大的功能之一就是能在数据查询的执行中联结（join）表。联结是利用SQL 的SELECT 能执行的最重要的操作。

现在有同一供应商生产的多种产品，一种产品又有不同的供应商生产，如何存储供应商信息和产品信息呢？将产品信息和供应商信息分开存储，两张表通过某些共同的值互相关联，如供应商ID。理由是：

• 同一供应商生产的每个产品，其供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间；
• 如果供应商信息发生变化，例如供应商迁址或电话号码变动，只需修改一次即可，只修改供应商表格，产品信息表格仅存储供应商ID，供应商ID永恒不变；
• 如果有重复数据（即每种产品都存储供应商信息），则很难保证每次输入该数据的方式都相同。不一致的数据在报表中就很难利用。

这样做的好处是：能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序，称为可伸缩性好（scale well）。

什么是联结：如果上所述，产品信息和供应商信息存储在多个表中，怎样用一条SELECT 语句就检索出数据呢？答案是使用联结。联结是一种机制，用来在一条SELECT 语句中关联表，因此称为联结。

创建联结：用where子句创建联结。例如：

检索所有产品对应的供应商名称：select p.*, v.vend_name from products as p, vendors as v where p.vend_id=v.vend_id;。
从示例可看出：别名可以应用于from子句，给表起别名，简化代码。
where子句，通过vend_id作为将两张表匹配起来，这里的限定条件必须使用完全限定名。

在一条SELECT 语句中联结几个表时，相应的关系是在运行中构造的。在联结两个表时，实际要做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE 子句作为过滤条件，只包含那些匹配给定条件（这里是联结条件）的行。

笛卡尔联结： 没有WHERE子句，第一个表中的每一行将与第二个表中的每一行配对，即笛卡尔联结：检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

例如：select p.*, v.vend_name from products as p, vendors as v;

内联接（inner join）： 上述使用where子句限定的连接为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内联结。等价语法inner join ... on ...：

select p.*, v.vend_name from products as p inner join vendors as v on p.vend_id=v.vend_id;
更推荐使用inner join ... on ...。

联结多个表：
上一节中，联结多个表，使用了子表达式，也可以通过联结的方式，例如：select p.*, v.vend_name,o.quantity from products as p inner join vendors as v inner join orderitems as o on p.vend_id=v.vend_id and o.prod_id=p.prod_id order by o.quantity;

可以根据性能表现，选择使用子表达式还是联结。

2.13 创建高级联结

除了上节所述的内联接，还有：自联结（self-join）、自然联结（natural join）和外联结（outer join）。

自联结：例如，要查询顾客表中，找出与Jim Jones在同一个公司的所有顾客，并给他们发邮件：

select cust_email from customers where cust_state=(select cust_state from customer where cust_name='jim');;
使用自联结：select a.cust_email from customers as a, customers as b where a.cust_state=b.state and b.cust_name='jim');

• 表别名的一个主要原因是能在一条SELECT 语句中不止一次引用相同的表
• 自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句

自然联结： 使用联结时，联结多个表，完全相同的列可能出现多次，自然联结排除多次出现，使每一列只返回一次。*自然联结要求你只能选择那些唯一的列，一般通过对一个表使用通配符(SELECT )，而对其他表的列使用明确的子集来完成。例如：

select p.*, v.vend_name from products as p, vendors as v;

外联结：内联结把不满足where过滤条件的行过滤掉，不检索出来。而使用外连接，则不满足的条件的行也会被检索出来。
语法结构：left/right outer join... on.。在使用outer join语法时，必须使用right或left关键字指定包括其所有行的表（right指出的是outer join右边的表，而left指出的是outer join左边的表）。

例如：select customers.cust_id, orders.order_num from customers left outer join orders on customers.cust_id=orders.cust_id;

联结中，带聚集函数：检索所有顾客及每个顾客所下的订单数。

select c.cust_id,count(o.order_num) as order_nums from customers as c inner join orders as o on c.cust_id=o.cust_id group by o.order_num order by c.cust_id desc;
综合运用了分组、联结、聚集、别名、排序。

2.14 组合查询

SQL也允许执行多个查询（多条select语句），并将结果作为一个查询结果集返回。这些组合查询通常称为并(union)或复合查询(compound query)。 主要有两种情况需要使用组合查询：

• 在一个查询中从不同的表返回结构数据；
• 对一个表执行多个查询，按一个查询返回数据。

多数情况下，组合相同表的两个查询所完成的工作与具有多个WHERE子句条件的一个查询所完成的工作相同。换句话说，任何具有多个WHERE 子句的SELECT 语句都可以作为一个组合查询。例外情况：
两个查询返回中有完全相同的行，where子句和union会自动去重，但是使用union all关键字，可以不去重，完成了where子句不能完成的工作。

创建组合查询： 使用union很简单，所要做的只是给出每条select语句，在各条语句之间放上关键字union。

注意： 使用union可能比使用where子句更为复杂。但对于较复杂的过滤条件，或者从多个表（而不是一个表）中检索数据的情形，使用union可能会使处理更简单。

• union必须由两条或两条以上的SELECT 语句组成，语句之间用关键字UNION 分隔。
• union中的每个查询必须包含相同的列、表达式或聚集函数（不过,各个列不需要以相同的次序列出）。
• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS 可以隐含转换的类型（例如，不同的数值类型或不同的日期类型）。
• union与where子句一样，都会自动去重；但是可以使用union all取消去重，返回值中可以包含完全相同的行。

对组合查询结果排序：

• SELECT 语句的输出用ORDER BY 子句排序。在用UNION 组合查询时，只能使用一条ORDER BY 子句，它必须位于最后一条SELECT 语句之后。order by是对所有select语句的查询结果进行排序。

例如：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='e fudd' order by cust_name,cust_contact;

如果组合union使用的select语句遇到不同的列名，比如第一条语句是select prod_name,第二条语句是select product_name，那么它会返回第一个名字，而不管第二个不同的名字。

这就意味着，可以对第一个名字使用别名，返回一个想要的名字。
另外：在需要组合多个表的数据时，即使有不匹配的列名的表，可以将union与别名组合，检索出一个结果集。

2.15 插入数据

insert用来将行插入（或添加）到数据库表。使用插入语句，需要确认DBMS是否设置正确的权限，否则插入将失败。插入有几种方式：

• 插入完整的行；
• 插入行的一部分；
• 插入某些查询的结果。

插入完整的行：

给顾客表中插入一行完整信息：insert into customers values(10007, 'Mr wang', '123 street', 'New York', 'NY', '11111', 'USA', NULL,NULL);
into关键字在有的DBMS可省略，如mysql。但是尽量不要省略，这样可以保证可移植性。
存储到表中每一列的数据在VALUES子句中必须给出每一列提供一个值。如果某列没有值，如上面的cust_contact 和cust_email列，则应该使用NULL值（前提是该列允许使用null）。
注意：这种语法很简单，但并不安全，应该尽量避免使用。上面的SQL语句高度依赖于表中列的定义次序，还依赖于其容易获得的次序信息，一旦表结构发生变化，sql语句将不可用，移植性差。
更安全的做法，给出每一列的名字，列名与vaulues后的值一一对应，这种插入方法，不依赖表汇总列名的顺序，即使表结构发生变化，也不会失败：insert into customers(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email) values(10008, 'Mr Tang', '578 street', 'New York', 'NY', '11111', 'USA', NULL,'')

部分插入：使用上述的安全插入方法，可以只插入部分列，其他未给出的列，DBMS自动填充默认值或填充null。所以，部分插入必须满足两个条件之一：

• 该列定义为允许NULL值（无值或空值）。
• 在表定义中给出默认值。这表示如果不给出值，将使用默认值。

插入检出的数据 insert... select...：利用SELECT 语句的结果插入表中，由一条INSERT 语句和一条SELECT语句组成的。利用insert... select...，可以从其他表检出的数据插入到指定的表中。

• 插入检出的数据可以一次插入多行，而上述的部分插入或完整插入一次只能插入一行。

例如，创建一张新表，new_customers:create table new_customers (cust_id tinyint unsigned, cust_name char(30), cust_address char(30), cust_city char(30), cust_state char(30), cust_zip tinyint unsigned, cust_country char(30), cust_contact char(30), cust_email char(30));
插入一条记录：insert into new_customers(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email) values(109, 'Mr Tang', '508 street', 'New York', 'NY', '21', 'USA', NULL,'')
利用insert... select...，插入新表中的数据到customers表中:insert customers select * from new_customers where cust_id=109;
注意：insert... select...语句，对插入和检查的两张表的列名没用要求必须一致，DBMS只是把检出的每一列按顺序一一填充到插入表中对应的列。insert和select也可以指定列名

从一张表复制到另外一张表：使用select into或create... as... select可以copy出一张新表，create select是导出数据到新表；而insert select是导出数据再插入已有的表。例如：

mysql、mariadb、oracle、PostgreSQL 和SQLite 使语法：create table copy_customers as select * from customers;
其他语法：select * into copy_customers from customers;
这条SELECT 语句创建一个名为copy_customers的新表，并把Customers表的整个内容复制到新表中。因为这里使用的是SELECT *，所以将在CustCopy 表中创建（并填充）与Customers 表的每一列相同的列。要想只复制部分的列，可以明确给出列名，而不是使用*通配符。

注意，使用SELECT INTO时：

• 任何SELECT 选项和子句都可以使用，包括WHERE 和GROUP BY；所以，复制新表，也可以设置过滤条件
• 可利用联结从多个表插入数据；
• 不管从多少个表中检索数据，数据都只能插入到一个表中。

2.16 更新和删除数据

利用update和delete语句可以更新（修改）和删除数据库中表的内容。都是高危操作，需要配置足够的安全权限。
1 更新（修改）数据：update table＿name set...。注意不要省略where子句。update语句以WHERE子句结束，它告诉DBMS更新哪一行。没有WHERE子句，DBMS将会用这个电子邮件地址更新Customers表中的所有行。

• 更新表中的特定行

例如，更新顾客表中的指定顾客的email：update newcustomers set cust_email='' where cust_id=10010;。 当需要更新多个列值时，set后使用逗号隔开即可。

• 更新表中的所有行。没有where子句将更新所有行
• 删除某个值，可以设置为null。这与保存空字符串很不同（空字符串用’’表示，是一个值），而NULL表示没有值。update newcustomers set cust_email=null where cust_id=10010;
• 在update语句中使用子查询： update newcustomers set cust_email=(select cust_email from customers where cust_id=10001) where cust_id=10010;

2 删除数据： delete from table_name [where...]。有些DBMS可以省略from，但是为了保证sql语句的可移植性，最好保留from关键字。
–从表中删除特定的行：delete from要求指定从中删除数据的表名，where子句过滤要删除的行

例如：删除特定的顾客信息：delete from newcustomers where cust_id=10001;
注意：对于联结表，当删除的某一行数据与另一张表存在关联关系时，将会破坏联结表的完整性；通过使用外键确保引用完整性，DBMS通常可以防止删除某个关系需要用到的行。

• 从表中删除所有的行：没有where子句将删除所有行。可以通过日志恢复数据

注意：DELETE 语句从表中删除行，甚至是删除表中所有行。但是，DELETE不删除表本身。
使用truncate table＿name可以更快的删除表中的所有行，因为truncate table＿name不记录数据的变动，无法通过日志恢复数据

3 更新和删除的指导原则

• 除非确实打算更新和删除每一行，否则绝对不要使用不带where子句的update或delete语句。
• 保证每个表都有主键，尽可能像where子句那样使用
• 使用强制实施引用完整性的数据库，这样DBMS将不允许删除其数据与其他表相关联的行。