sql server基本操作 sql server操作语句

转载

mob64ca1411a6fc 2023-09-01 14:40:07

文章标签 sql server基本操作 sql 表名子查询元组 文章分类 SQL Server 数据库

SQL Server 数据操作语句

注意，SQL语言是不区分大小写的，本人所有语句统一用小写表示，且讲解语句格式时中括号表示可省略。

数据查询

查询 select 语句基本结构

select <目标列名序列>
  from <数据源>
  [where <检索条件表达式>]
  [group by <分组依据列>]
  [having <组提取条件>]
  [order by <排序依据列>]

单表查询

选择列

select 选择列的语句一般为：

select 列名（多个列名用逗号隔开）from 表名

当我们需要查询一个表中的所有列时，可以用星号代替一堆列名。

select * from 表名

此外，我们可以用含列名的表达式来代替列名，将计算结果作为新的列，并用 as 语句或赋值语句指定新的列的列名，此外这个表达式也可以用常量来生成常量列，例如：

select name, '分数差' as constC, newC = scoreB-scoreA
  from Student

distinct 关键字

在 select 语句中，在目标列名前加入 distinct 关键字可以去掉查询结果中的重复行数据。

select distinct 列名 from 表名

where 语句查询满足条件的元组（选择行）

基本格式为

where 含列名的条件语句

可筛选条件语句返回值为真的元组。

查询条件	语句（或运算符）
比较	=，>，>=，<，<=，<>（或 != ）
确定范围	between and，not between and
确定集合	in，not in
字符匹配	like，not like
空值	is null，is not null
多重条件（谓词逻辑）	and，or

下面介绍一些语句的具体格式

between and

where 表达式 [not] between 下限值 and 上限值

-- 等价于
where 表达式 >= 下限值 and 表达式 <= 上限值
where 表达式 < 下限值 or 表达式 > 上限值

where 列名 [not] in (常量1, 常量2, ..., 常量n )

-- 等价于
where 列名 = 常量1 or 列名 = 常量2 or ... or 列名 = 常量n
where 列名 <> 常量1 and 列名 <> 常量2 or ... or 列名 <> 常量n

-- 基本格式为
where 列名 [not] like <匹配串>

其中 <匹配串> 是一个字符串，用于表示匹配的字符串的格式，有以下四个通配符

通配符	含义
%	匹配任意0个或多个字符
_	匹配任意一个字符
[ ]	匹配字符列（中括号）中的任何单一字符
[^ ]	匹配不在字符列（中括号）中的任何单一字符

注意，系统在匹配字符串时，会认为空格也属于字符，因此当匹配末尾可能含有空格的字符串时，要先对字符串调用 rtrim 函数。

where rtrim(name) like '___'

and 和 or
注意 SQL Server 里 and 优先级高于 or 。因此当我们混合使用逻辑谓词时，如果要先运算 or ，需要加小括号来改变运算顺序。

order by 语句对查询结果进行排序

基本格式为

order by <列名1> [asc|desc] [, <列名2> [asc|desc] , ... , <列名n> [asc|desc]]

按从左到右的列名顺序排序（优先保证左侧的排序，针对排序列数值相同的元组，再进行按照右边列名的排序）
其中 asc 表示升序排序，desc 表示降序排序，默认为升序。

使用聚合函数（统计函数）汇总数据

显示汇总数据的格式为

select 聚合函数表达式 [as <列别名>] from <表名>

有如下常用聚合函数：

函数	作用
count (*)	统计表中的元组个数
count ([distinct] <列名>)	统计本列非空列值个数，distinct 表示不包括列的重复值
sum (<列名>)	计算本列非空列值总和（必须是数值类型）
avg (<列名>)	计算本列非空列值平均值（必须是数值类型）
max (<列名>)	求本列非空列值最大值
min (<列名>)	求本列非空列值最小值

注：聚合函数可以和 where 子句结合实现对指定元组的计算。
注：聚合函数不能出现在 where 子句中。（如果需要实现对应功能，见后面子查询内容中的子查询用于比较测试部分）

group by，having 语句对查询结果进行分组统计

group by 语句可以实现将原本的大表，根据其中部分列（可以是多个列）中相同的数值分为不同的部分分别进行各种查询操作。

group by 列名1[, 列名2, ..., 列名n] 
[having <组筛选条件>]

注意：分组依据列不能是 text、ntext、image 类型。
注意：分组依据列必须是表中存在的列名，不能是查询语句中指派的列别名。
注意：带有 group by 子句的 select 语句的查询列表中只能出现分组依据列或统计函数，不能直接出现非分组依据列或其一般表达式。

我们可以将聚合函数和 group by 语句结合，实现分组计算。

having 语句在功能上类似于 where 语句，但存在以下两个不同点：

where 语句在 group by 语句执行前运行，having 语句在 group by 语句运行分组后，针对每一组运行。
where 语句中不允许含有统计函数，having 语句中可以使用统计函数。

多表连接查询

若一个查询涉及两个或两个以上的表，则称之为多表连接查询。下面主要介绍内连接和外连接，语句格式采用 ANSI 标准，即连接是在 join 语句中执行。其中 join 语句，紧接在 from 语句后面。

内连接

对于两张表，我们可以利用其中语义相同的列将这两张表连接起来，语法格式为：

select * from 表1 [inner] join 表2 on <连接条件> 		--join和on都可以换行写

其中，连接条件的一般格式为：

[<表名1>.]<列名> <比较运算符> [<表名2>.]<列名>

注意，如果两个表中用于连接的列名有重复，则 [<表名>.] 部分必须加上，且用于作为连接条件的列必须是可比的、语义相同的列。大多数情况下，比较运算符会用 =，称为等值连接。
SQL 的连接过程为：遍历表1中所有的元组，将表2中每个满足连接条件的元组直接拼接至表1元组后方。
当然，直接用 select * 无法避免连接后的表中会出现冗余列或不需要的列，我们可以将需要的列用逗号隔开（有重复列名的列需要用 <表名>.<列名> 的格式）替代星号。
此外我们可以用 as 语句给 from 或 join 后的表名另指定表别名（当为表指定了表别名时，所有使用表名的地方要使用表别名）

select [<表名>.]<列名>（多个列名用逗号隔开） from 表1 [[as] 表别名1]
  [inner] join 表2 [[as] 表别名2]
  on [<表名1>|<表别名1>.]<列名> <比较运算符> [<表名2>|<表别名2>.]<列名>

当我们需要将更多的表连接时，只需要使用多个 join 语句即可。

自连接

当相互连接的表为同一张表时，我们称之为自连接，自连接可以实现表内不同元组间的比较运算，并将结果筛选出来。
使用自连接时，必须将所有表名另指定表别名。

from 表名 [as] 表别名1 join 表名 [as] 表别名2
on <表别名1>.<列名> <比较运算符> <表别名2>.<列名>

外连接

在内连接中，连接后的表不会显示不满足连接条件的元组信息（例如表1中某些元组在表2中没有可以连接在后面的元组，连接后的表会直接舍弃表1中的这些元组），然而有些时候，我们需要连接后的表能告诉我们，哪些元组是匹配不到满足连接条件的元组的，这时候就需要使用外连接。
外连接是在内连接的基础上，若另一张表中没有满足连接条件的元组，则仍会给前一张表的这个元组连接一个全为 NULL 的元组。
外连接分为左外连接和右外连接，语法格式分别为：

from 表1 left [outer] join 表2 on <连接条件> 			--左外连接
from 表1 right [outer] join 表2 on <连接条件> 		--右外连接

左外连接，会保留表1中的所有元组，对无法连接的元组，为其连接一个全为 NULL 的元组。其连接过程与内连接相似。
右外连接，会保留表2中的所有元组，对无法连接的元组，为其连接一个全为 NULL 的元组。
外连接的其他语法格式（select 语句后跟随的星号或列名）和内连接相同。

使用 top 限制结果集

当我们只需要列出结果集中的前几行数据时，可以使用 top 谓词。top 谓词一般位于 select 后面（如果有 distinct 则位于 distinct 后面），查询列表的前面。格式为：

top n [percent] [with ties]

其中 n 为非负整数。

具体含义如下：

语法	格式或含义
top n	表示取查询结果的前n行数据
top n percent	表示取查询结果的前n%行数据
with ties	表示包括并列的结果（必须含有 order by 子句才能使用）

top 谓词一般和 order by 子句一起使用，实现提取按照排名的前部分数据。

子查询

在 SQL 中，select-from-where 语句称为一个查询块。
如果一个 select 语句嵌套在一个 select 语句（或之后的 insert、update、delete 语句）中，则称之为子查询或内层查询。子查询语句一般需放在小括号中
子查询可以出现在能够使用表达式的任何地方，但一般用于外层查询的 where 子句或 having 子句中。
子查询主要有以下三个用法。

子查询用于基于集合的测试

当子查询的返回结果为一个含有多个元组的结果集，且这个结果集的列的个数、数据类型、语义与表达式中涉及的表的列的个数、数据类型、语义相同时，我们可以结合 [not] in 语句，实现多个表或表内的特殊包含关系筛选。（例如从另一个表中，提取满足条件的一列，在当前表中筛选某一列的值在这一列中的元组）

where 表达式 [not] in (子查询)

SQL 会先执行子查询，再执行外层查询。（因此，一般情况下，这种方式的子查询不会用到 <表名>.<列名> 的格式）
或者，我们也可以用多表连接查询来实现这个功能。

子查询用于比较测试

当子查询只返回一个值时，我们可以结合比较运算符，将一个表达式的值与子查询返回的值进行比较。之前提到聚合函数不能直接出现在 where 子句中，但我们可以将其放在子查询中，并将子查询放在 where 子句中来实现我们需要实现的功能。

where 表达式 比较运算符 (子查询)

SQL 会先执行子查询，再执行外层查询。（因此，一般情况下，这种方式的子查询不会用到 <表名>.<列名> 的格式）

子查询用于存在性测试（exists 谓词）

exists 谓词的基本格式为：

where [not] exists (子查询)

带 exists 谓词的子查询不返回查询的结果，只返回真值或假值。
exists 谓词的含义为：当子查询中有满足条件的数据时，exists 返回真值，否则返回假值。
带 exists 谓词的查询，会先无条件执行外层查询，取外层查询的每一行结果，分别带入子查询中，如果子查询能匹配到满足条件的数据，则 exists 返回真值。（带入子查询的数据要用 <外层表名>.<列名> 来表示）
因为带 exists 谓词的子查询只返回真值或假值，所以不需要特意指定查询列名，用星号即可。

where [not] exists (
  select * from <表名>
    where 含有 <外层表名>.<列名> 的条件语句)

注意：使用 exists 谓词前，必须仔细思考其查询的过程。

数据更改

数据修改语句修改数据库中的数据，且不返回结果集。

插入数据

insert 语句用于插入单行数据，有以下两种基本格式：

insert [into] <表名> values (值列表)

这种情况下，值列表必须按照表的对应列名顺序填入，若对应列允许填入 NULL 值，则可以填入 NULL。

insert [into] <表名>(列名1, 列名2, ...) values(值列表)

这种情况下，值列表按照前面列名顺序填入，并将其余列设为 NULL 值（只有可为空值的列才能不填入值）。

注意：列名列表和值列表都写在小括号里，用逗号隔开。

更新数据

update 语句用于更新全部行或指定部分行的某一列的数据。

无条件更新

无条件更新用于更新表中所有行的数据。

update <表名> set <列名> = 表达式 [, <列名2> = 表达式2, ...]

注：这里的表达式可以包含列名，可以实现数据自加1等操作。

有条件更新

update <表名> set <列名> = 表达式 [, <列名2> = 表达式2, ...]
  where 更新条件

为 update 语句添加 where 子句可以让只满足更新条件的部分数据进行更新。

update <表名> set <列名> = 表达式 [, <列名2> = 表达式2, ...]
  from <表名> join <其他表名> on <连接条件>
    where 更新条件

在此基础上，还可以再添加 join 子句实现多表连接，再根据更新条件更新数据。

删除数据

delete 语句用于删除表内全部数据或指定的部分数据。

无条件删除

无条件删除会删除表中的所有数据，但保留表的结构。

delete [from] <表名>

有条件删除

同更新语句，可以增加 where 子句，join 子句。

delete [from] <表名>
  [from <表名> join <其他表名> on <连接条件>]
    where 删除条件

注意：更新和删除语句中的 where 语句，也可以在其中使用子查询。

数据查询拓展

将查询结果保存到新表

为 select 语句添加 into 子句可将查询结果保存至新表。

select 查询列表序列 into <新表名>

注意：若查询列表中含有表达式，必须要为其取别名才能使用 into 子句。

case 多分支表达式

case 语句可以根据传入表达式的值，返回多个可能结果中的一个。（可以起到替换数据的作用）
case 语句常用于 select 查询语句中的查询列表中，后常接 as 取别名子句，作为一个表达式来替换查询结果；或 update 更新语句 set 后的表达式中用于视条件更新不同的值。
注意：所有 case 语句都是以 case 作为开头，以 end 作为结尾。
注意：case 语句是一个表达式。

简单 case 表达式

简单 case 语句可以实现将一个表达式和多个测试值进行比较，每个值对应一个返回结果，并且可以再加一个 else 子句作为无匹配结果的返回值（无 else 子句，else 值默认为 null ）。

case 输入表达式
  when 测试表达式1 then 结果表达式1
  when 测试表达式2 then 结果表达式2
  ...
  else 结果表达式n+1
end

当 输入表达式 = 测试表达式i 时，会返回 结果表达式i 。

搜索 case 表达式

简单 case 表达式如果不传入输入表达式，并将测试表达式替换成返回值为 bool 值的真假值判断表达式（和 where 语句后面跟的条件语句要求一样）。
搜索 case 表达式会按照从上往下顺序，返回第一个判断表达式为 True 对应的结果表达式（同样也可以加 else 子句，无 else 子句，else 值默认为 null ）。

case
  when 判断表达式1 then 结果表达式1
  when 判断表达式2 then 结果表达式2
  ...
  else 结果表达式n+1
end

查询结果的并、交、差运算

查询结果的并、交、差运算语句写在两个（或多个的每两个之间）select 查询语句之间，可以将多个查询结果进行并、交、差运算使其合并为一个结果。有如下要求和注意点：

各 select 语句中的查询列的个数相同，对应列的语义相同。
各 select 语句中对应列的数据类型必须是互相可兼容的（可进行隐式转换，例如 char(20) 和 varchar(40) ）。
运算后的结果集采用第一个（最上面的）表的列名作为列名。
如果要对最后的查询结果进行排序，order by 子句要写在最后一个查询语句中，排序的依据列应该是第一个查询语句中出现的列名。
如果要用 into 子句保存结果集，into 子句要写在第一个查询语句中。
这三个运算的优先级是 intersect > except = union，同等优先级按从上往下顺序执行。（此结论已经过本人在 SQL Sever 2012 上验证）

select 语句1
运算语句
select 语句2
运算语句
...
运算语句
select 语句n

运算语句有以下四种：

运算语句	作用
union	并运算（自动删除重复记录）
union all	并运算（保留重复记录）
intersect	交运算
except	差运算

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：javac编译lib包文件 javac 带包编译

下一篇：hive list数据类型建表 hive in list

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯