SQL总复习一：基础查询和增删改以及事务

SQL语句分类（根据功能）：

DDL（Data Definition Language，数据定义语言）
用来创建或者删除存储数据用的数据库以及数据库中的表等对象。 DDL 包含以下几种指令。

CREATE： 创建数据库和表等对象
DROP： 删除数据库和表等对象
ALTER： 修改数据库和表等对象的结构

DML（Data Manipulation Language，数据操纵语言）
用来查询或者变更表中的记录。 DML 包含以下几种指令。
SELECT：查询表中的数据
INSERT：向表中插入新数据
UPDATE：更新表中的数据
DELETE：删除表中的数据
DCL（Data Control Language，数据控制语言）
用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对 RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定。 DCL 包含以下几种指令。
COMMIT： 确认对数据库中的数据进行的变更
ROLLBACK： 取消对数据库中的数据进行的变更
GRANT： 赋予用户操作权限
REVOKE： 取消用户的操作权限

实际使用的 SQL 语句当中有 90% 属于 DML

运算符

运算符就是使用其两边的值进行四则运算或者字符串拼接、数值大小比较等运算，并返回结果的符号。

注意点：

• 加法运算符（+）前后如果是数字或者数字类型的列名的话，就会返回加法运算后的结果。如果+两边都是字符串，那会合并字符串。但若是两边一个字符串、一个数字类型（int或decimal），那么会报错。记住+两边要么同为数字类型要么同为字符串类型才可以。
• 四则运算所使用的运算符（+、 -、 *、 /）称为算术运算符
• 判断是否为NULL或者说等于null，需要使用IS NULL或者IS NOT NULL运算符。
  所有包含 NULL 的计算，结果肯定是 NULL。就算 null/0,结果也是null且不会报错
• SQL语句中的不等于是：<>
• 字符串的比较，是按首字母顺序、首数字顺序排序的，越靠后越大
• 注意：select 17/3 ，结果是5，即得到的是实际相除的结果的整数部分。想得到小数得用：17/3.00 这种
• AND 运算符优先于 OR 运算符。所以在 …… where Age=18 and (UserType>2 or UserFees>100)中，在and后面的条件加了括号 ，否则条件将是：…… where (Age=18 and UserType>2) or UserFees>100。很明显，二者结果是大不相同的。

基本查询相关

SQL 中各部分的执行顺序是：FROM → WHERE → GROUP BY → HAVING →SELECT( → ORDER BY)。

select ……from……where……

select …… from …… where ……，这种结构，首先通过 WHERE 子句，基于行，查询出符合指定条件的记录，然后再从这些记录中选取出 SELECT 语句指定的列 。

distinct

SQL中distinct关键字常用于select之后，作用是将重复数据仅取一行。distinct后有多个列时，仅当不同行中，这些列的内容完全一样时才会仅显示一条。
注意：

• 对选取一个字段的语句用distinct，那么如果是两个null，那也会取出一条，该条内容为null。
• DISTINCT 关键字只能用在第一个列名之前，不管后面有几个字段。
• distinct 也可以用在函数里面，如果不存在重复的元素，不管是否加上 DISTINCT 可选项，COUNT 的结果都是相同的。参考以下SQL

-- 选中材料存在重复的生产地
SELECT center
 FROM Materials
 GROUP BY center
HAVING COUNT(material) <> COUNT(DISTINCT material);

**null 与 where **

如果在where中通过数据表中的某列进行查询，如果存在该列的值有null的记录，则有一点需要注意：你要想得到这些该列为null的记录，则条件表达式中必须使用IS NULL运算符，否则累死你也查不出来。比如这列名是age，但有些记录的age值为null，那么你where age<>0得到的结果是不包含这些null值的记录的，where age<>0 or age is null 才会得到包含null值的记录。还有不为null是 IS NOT NULL

聚合函数

函数这个词，与我们在学校数学课上学到的意思是一样的，有输入和输入。SQL 中有很多用于汇总的函数，请大家先记住以下 5 个常用的函数：

COUNT：计算表中的记录数（行数）

SUM：计算表中数值列中数据的合计值

AVG：计算表中数值列中数据的平均值

MAX：求出表中任意列中数据的最大值

MIN：求出表中任意列中数据的最小值

如上所示，用于汇总的函数称为聚合函数或者聚集函数。所谓聚合，就是将多行汇总为一行即最多只能得到一条记录。只有SELECT子句和HAVING子句（以及ORDER BY子句）中能够使用聚合函数。

注意：

1、聚合函数会将NULL排除在外。但COUNT(*)例外，并不会排除NULL。使用COUNT(*)可以查出包含NULL在内的全部数据的行数，而COUNT(<列名>)会得到NULL之外的数据行数，实际上所有的聚合函数，如果以列名为参数，那么在计算之前就已经把 NULL 排除在外了。

另外，在聚合函数的参数中使用DISTINCT，可以删除重复数据，如：

SELECT SUM(sale_price) as sum1, SUM(DISTINCT sale_price) as sum2 FROM Product;

这句代码中，sum2会先得到表中的所有非null数据，再distinct,再进行求和。所以当表中的sale_price值有相同的记录时，sum2会比sum1小。

2、在使用 COUNT 这样的聚合函数时，SELECT 子句中的元素有严格的限制。实际上，使用聚合函数时，SELECT 子句中只能存在以下三种元素：【常数、聚合函数、GROUP BY子句中指定的列名（也就是聚合键）】

GROUP BY 、HAVING、ORDER BY

使用GROUP BY子句可以像切蛋糕那样将表分割。

不使用 GROUP BY 子句时，是将表中的所有数据作为一组来对待的。而使用 GROUP BY 子句时，会将表中的数据分为多个组进行处理。这个分组，就好像把一个表分成了几个表，再对这每个表执行select。

GROUP BY 和 WHERE 并用时 SELECT 语句的执行顺序：FROM → WHERE → GROUP BY → SELECT

在GROUP BY子句中不能使用SELECT子句中定义的别名。

HAVING

where中不可以对聚合函数进行筛选，但having字句中却可以，它可以对分组的结果进行筛选**，比如选出“报名人数在10人以上的体育项目”；

注意：HAVING 子句必须写在 GROUP BY 子句之后。

having字句只能存在以下三种元素：【常数、聚合函数、GROUP BY子句中指定的列名（也就是聚合键）】

在思考 HAVING 子句的使用方法时，把一次汇总后的结果（group by之后的结果）作为 HAVING 子句起始点的话更容易理解。

注意：在 WHERE 子句和 HAVING 子句中都可以使用的条件，最好写在 WHERE 子句中，理由是执行速度更快。另外，having后面不仅可以跟针对集合的聚合函数的结果作为条件，还可以跟其他针对行判断的条件。

ORDER BY

1、使用ORDER BY子句对查询结果进行排序：

• 在ORDER BY子句中列名的后面使用关键字ASC可以进行升序排序，使用DESC关键字可以进行降序排序。
• ORDER BY子句中可以指定多个排序键，英文逗号隔开。
• 排序健中包含NULL时，会在开头或末尾进行汇总。
• ORDER BY子句中可以使用SELECT子句中定义的列的别名。
• ORDER BY子句中可以使用SELECT子句中未出现的列或者聚合函数。

2、排序升三降四（asc从小到大即升序，desc从大到小即降序，默认asc）。在 ORDER BY 子句中同时指定多个排序键了。规则是优先使用左侧的键，如果该列存在相同值的话，再接着参考右侧的键，往右依次类推。

3、使用 HAVING 子句时 SELECT 语句的顺序

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

这只是一个粗略的总结，虽然具体的执行顺序根据 DBMS 的不同而不同，但是大家有这样一个大致的印象就可以了。一定要记住 SELECT 子句的执行顺序在 GROUP BY 子句之后，ORDER BY 子句之前。因此，在执行 GROUP BY 子句时，SELECT 语句中定义的别名无法被识别 。对于在 SELECT 子句之后执行的 ORDER BY 子句来说，就没有这样的问题了。

视图和子查询

视图

1、认识视图

从SQL的角度来看，视图和表是相同的，两者的区别在于表中保存的是实际的数据，而视图中保存的是SELECT语句（视图本身并不存储数据）。在SQL语句中，from 后面常常跟的是表名，但也可以跟某视图名，就好像这个视图名是数据库中已存在的表一样。

使用视图就像使用数据库中的某个表一样，不在多哔哔了。有一点，有些时候可以通过更新视图来更新表数据，具体的反正参与的项目中没遇到过，一般视图只是用来辅助查询用的，即只和select结合使用。

2、视图的优点

视图的优点大体有两点。第一点是由于视图无需保存数据，因此可以节省存储设备的容量。第二个优点就是可以将频繁使用的 SELECT 语句保存成视图，这样就不用每次都重新书写了。创建好视图之后，只需在 SELECT 语句中进行调用，就可以方便地得到想要的结果了。特别是在进行汇总以及复杂的查询条件导致 SELECT 语句非常庞大时，使用视图可以大大提高效率。

3、创建视图的语法

CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……)
AS
<SELECT语句>

另外，删除视图：DROP VIEW 视图名称(<视图列名1>, <视图列名2>, ……)

注意：一般不建议在创建视图的select语句中使用视图，因为影响性能。

子查询

• 子查询就是将用来定义视图的 SELECT 语句直接用在 FROM 子句当中，子查询在SELECT语句执行完毕之后就会消失（联想下变量出了作用域后就好像没出现过一样）。
• 子查询需要命名，就像定义变量需要变量名一样，使用时要根据处理内容来指定恰当的名称。
• 子查询作为内层查询会首先执行。

标量子查询就是只能返回一行一列的子查询，另外，标量就是单一的意思，由于返回的是单一的值，因此标量子查询的返回值可以用在 = 或者 <> 这样需要单一值的比较运算符之中。这也正是标量子查询的优势所在。标量子查询的书写位置并不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。也就是说，能够使用常数或者列名的地 方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用。

数据的增删改和事务。

重要提示：

对数据库表结构和数据进行更新前，一定记得，有条件的话，先备份数据库，另外更新语句一定要记得加上where条件。

插入数据

多行INSERT （Oracle以外）

INSERT INTO ProductIns VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11')
,('0003', '运动T恤', '衣服', 4000, 2800, NULL)
,('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');

INSERT……SELECT……，这种多行插入数据的方式，即可以实现从另一个表取出数据放到当前表，十分方便常用。

删除数据：truncate、drop、delete

TRUNCATE 可以删除表中的全部数据，保留表的结构，但不能通过 WHERE 子句指定条件来删除部分数据。也正是因为它不能具体地控制删除对象，所以其处理速度比 DELETE 要快得多。它常用来清空表数据，清空后，表的主键如果是自增的，那么，再新加入纪录，id是从1开始的。
语法：TRUNCATE <表名>;

如果想将整个表全部删除，可以使用DROP TABLE语句，这样就把这个表的数据和表结构都删了。
语法：DROP TABLE 表名；

如果只想删除表中部分数据，需使用DELETE语句，只需在WHERE子句中书写对象数据的条件即可，一定要使用where加上删除条件 。
DELETE FROM 表名 【WHERE ……】;

修改数据

就是常用的update来进行修改，需要注意的就一点：执行修改或者删除数据的时候，千万千万要记得加上where条件，要是出现你只是想修改或者删除某条记录，结果却搞砸了整张表，结果你可以考虑下。有时候你可能当时就不知道你因为这个粗心造成了损失，直到客户发现数据不对了，你可能才会慢慢反应过来，结果，很有可能就得跑路了，开个玩笑哈。

事务（transaction）

定义：事务就是需要在同一个处理单元中执行的一系列更新处理的集合。
事务的特点：记住一句话，提交时要么所有更新都执行，要么都不执行，只要有一句执行失败，所有的都不执行。还有：一旦开启了事务，在提交或回滚之前，所有的数据变更都不会保存，这就好比你开始事务后就复制了一个数据库，你之后的操作都是在这个复制的数据库上进行操作的，你提交后，才会在本来的数据库上进行更新，所以说，事务提交前，你当前看到是更新数据了，但其它人并看不到，即你用另一个查询窗口查看数据，还是你更新之前的样子。

为什么需要事务：对表进行更新需要使用 INSERT、DELETE 或者 UPDATE 三种语句。但通常情况下，更新处理并不是执行一次就结束了，而是需要执行一系列连续的操作。这时，事务就能体现出它的价值了。比如用户网购了一条裤子，那么肯定有订单信息、收货地址信息和商品库存的变动吧，那数据变动肯定是要有订单信息的插入、订单收货地址的插入、裤子的库存的更新吧，那如果你的程序只是生成了订单，没有保存订单和收货地址的联系没有变库存，肯定不行吧，你不生成订单即用户点提交订单后没反应也比这事情做了一半要强吧。而有了事务，你把这三个操作作为一个整体，要么全部正确执行保存数据变动，要么全不执行，数据原封不动，多好，多放心。

事务在SQL server中的使用方式：

开始事务用 begin transaction ，结束事务有两种，commit 用来提交事务（commit transaction ）即保存一系列的更新，相当于Ctrl+S，rollback 用来取消事务（rollback transaction ），即所做的一系列更改都不保存，相当于Ctrl+Z。开始事务和结束事务两句之间放一系列的更新语句

注意：通常执行多条更新语句时，可以通过SQL server上的Try catch监测SQL 有没有执行出错，如果出错，再进行rollback。

事务的ACID特性

ACID是数据库事务的四个基本特性，它们保证了数据库事务的一致性、可靠性和持久性。

• A代表原子性(Atomicity)：表示一个事务是一个不可分割的最小操作单元，要么全部执行成功，要么全部失败回滚。如果事务执行成功，则对数据库所做的修改将永久保存；如果事务执行失败，则对数据库所做的修改将被撤销，恢复到事务开始之前的状态。
• C代表一致性(Consistency)：表示在任何给定时间点，数据库都必须保持一致状态。这意味着如果一个事务修改了数据库，其他事务也必须看到相同的结果。
• I代表隔离性(Isolation)：表示多个事务之间相互独立，不会相互干扰。每个事务都在自己的私有环境中运行，不会与其他事务共享资源。
• D代表持久性(Durability)：表示一旦事务提交成功，它对数据库所做的修改将永久保存。即使系统发生故障或崩溃，已经提交的事务也不会丢失。
  这些特性确保了关系型数据库可以安全地执行复杂的操作，例如多用户同时访问同一个数据集。

事务隔离级别

事务我们知道，就是让事务里面的多个增删改操作，要么都成功，要么有一个失败就回滚即都不成功。很好，很有用。

那要是对SQL Server 同时进行两个事务，事务中都对同一条记录修改，能保证两个修改都有效吗？比如对同一商品的库存扣减，原有100，事务A中要扣10，事务B中要扣5，那两个事务完成后，该商品库存是剩余85吗？即事务里，对行访问更新时，会先锁定该行吗？

这就要说一个概念：事务隔离级别。

事务隔离级别是数据库管理系统(DBMS)中的一种概念，用于定义多个事务同时访问共享资源时如何协调它们的操作。

在 SQL 标准中，定义了四个标准的事务隔离级别，分别为：

1. 读未提交（Read Uncommitted）：允许一个事务读取另一个事务已修改但未提交的数据。

2. 读已提交（Read Committed）：要求一个事务只能读取另一个事务已经提交的数据。

3. 可重复读（Repeatable Read）：保证同一事务内多次读取同样记录的结果是一致的。

4. 序列化（Serializable）：最高级别的隔离级别，完全符合 ACID 性质。所有的事务串行执行，避免了幻读的情况。

除了标准隔离级别外，还有一些扩展的隔离级别，如 SQL Server 中的 SNAPSHOT 隔离级别和 Oracle 中的 SERIALIZABLE 隔离级别等。这些 DBMS 还可能支持其他隔离级别或混合使用不同的隔离级别。不同的隔离级别会对并发性、数据一致性和性能产生不同的影响。所以选择适当的事务隔离级别对于确保数据库的一致性和可靠性非常重要，因此开发人员应该仔细考虑并了解他们使用的 DBMS 支持哪些隔离级别。

SQL Server 中指定事务隔离级别（默认 Read Uncommitted）：

declare @effect_row int=0
--以下示例为会话设置了 TRANSACTION ISOLATION LEVEL。 对于每个后续 Transact-SQL 语句，SQL Server 将所有共享锁一直保持到事务结束为止。即，隔离级别的设置只对当前事务有效，不会影响其他事务。
set transaction isolation level serializable
begin tran
	update Base_User set UserOnLine=1 where RealName='Test'
	set @effect_row=@@ROWCOUNT  --@@ROWCOUNT 函数返回一个整数值，表示最近一次 DML 操作（如更新、删除、插入）所影响的行数。如果没有任何 DML 操作或者该操作不会影响任何行，则返回值为 0。
	update Base_User set UserOnLine=2 where RealName='Test2'
	set @effect_row+=@@ROWCOUNT
if(@effect_row>0)
	commit tran
else
	rollback tran

在数据库事务中，当任何一个操作步骤出现错误或异常情况时，可以将整个事务标记为失败状态。具体来说，事务的失败通常指以下情况之一：

1. 事务中的任何一个 SQL 语句执行失败，例如违反了某个约束条件、试图插入重复的键、访问不存在的表等；
2. 在事务执行期间出现了系统级别的故障或错误，例如数据库服务器崩溃、硬件故障、网络连接中断等；
3. 在执行事务期间，使用 ROLLBACK 命令显式地回滚事务。

无论哪种情况，都会导致当前事务的所有修改被撤销，并恢复到开始事务前的状态，这就是事务的回滚（Rollback）。如果没有发生异常情况，则可以提交事务（Commit），使得所有修改永久生效，同时结束当前事务。

参考：SET TRANSACTION ISOLATION LEVEL (Transact-SQL) - SQL Server | Microsoft Learn

其他小细节方面

注释的写法：

--这是单行注释
select * from UserInfo
/*这里面可以放多行注释内容
这是又一行
*/
select * from UserInfo

varchar 和 nvarchar

char(n)或varchar(n)，n表示存的字节数，一个英文、数字或符号是一个字节，一个中文是两个字节，故，此时如果是中文，那只能存n/2个中文，所以此类型不适合作为含中文字符串的数据存储类型。

而nvarhcar(n)，这种类型中的n表示的是字符个数，就是对英文数字或中文这些字符都无区别对待，n是几就能最多存几个字符。
比如varchar(6）和nvarchar(6)来存“like张大仙”这个字符串，前者存的是“like张”，后者存的是“like张大”。

如果是字符串，且含有中文，就用 nvarchar这个类型来存储

拷贝数据库相关对象

数据库中的数据表和存储过程、自定义函数这些都是可以拷贝的，主要是通过：

右击数据库名->任务->生成脚本->选择对象（再选中”选择具体的数据库对象“）->勾选你想复制的数据表或存储过程之类的->下一步（后面保存到新建查询窗口）。这样就得到了创建你想要的东西的脚本即代码语句，Ctrl+C这些东西，如果你在另一个数据库中Ctrl+N新建一个查询窗口，再粘贴这些代码，再执行，就完成表的拷贝啦！

智能提示

SQL server数据库管理工具里，写SQL没有智能提示了，按Ctrl + J就有了。

---

推荐一本SQL入门教程书：图灵社区的《SQL基础教程》

---

更新于：2023-5-17