SQL Server温故系列(2)：SQL 数据操作 CRUD 之简单查询

• 1、查询语句 select
  • 1.1、查询语句的 select 子句
  • 1.2、查询语句的 from 子句
  • 1.3、查询语句的 where 子句
  • 1.4、查询语句的 order by 子句
• 2、插入子句 into

1、查询语句 select

顾名思义，select 语句的作用就是从表中查询数据。查询语句一次可以从一个或多个表中检索一个或多个字段的一行或多行。select 是 sql 中最常用的一个语句，完整的 select 语法是非常复杂的，本文将先对简单查询做个回顾，更高级的用法将会在本系列的后续博文中陆续介绍。基本语法如下：

select top(top-expressions) column-expressions
from source-table-name
where search-conditions
order by order-by-expressions;

1.1、查询语句的 select 子句

select 子句的作用是指定查询返回的字段，而如果只是查询常量、变量或算术表达式等（非表的）数据，就不需要 from 子句了，示例如下：

select 'a','1','a1';    -- 查询 sql 字面量
select getdate();       -- 调用系统函数，获得当前系统时间
select 5+1,7-2,2*2,9/3; -- 做加减乘除四则运算

通过 from 子句来查询表数据，示例如下：

select t.code as 学号,t.name as 姓名 from t_students t; -- 查询所有学生的学号和姓名（且给表和字段指定了别名）

通过 * 查询 from 子句中的所有表中的全部列，示例如下：

select * from t_students;            -- 查询学生表中所有字段
select * from t_students,t_students; -- 查询学生表和学生表中所有字段（仅为演示，无实际意义）

通过 distinct 参数过滤重复数据，示例如下：

select distinct t.* from t_students t;       -- 查询所有学生的信息
select distinct t.classid from t_students t; -- 查询“有效的”班级 id

注意：distinct 参数的作用在于确保结果集中只包含唯一的行。然而有意思的是，尽管在 sql server 中理论上不存在两个相等的 null，但对 distinct 来说 null 值却是相等的。所以如果表中有多个 null 值，distinct 之后就只会返回一个。另外，与 distinct 对应的还有一个 all，表示可以包含重复行，也就是默认返回，该参数总是可以省略不写。

通过 top 参数限制查询的返回行数，示例如下：

select top(5) t.* from t_students t;          -- 查询随机的前 5 个学生
select top(3+2) t.* from t_students t;        -- 结果集同上（支持运算表达式）
select top(select 5) t.* from t_students t;   -- 结果集同上（支持子查询）
select top(25) percent t.* from t_students t; -- 查询随机的前 25% 的学生

注意：percent 用于指示返回结果集中前 expression% 的行，如果行数的计算结果为小数，则小数部分的值向上舍入到下一个整数值。当指定了 percent 时，top 的表达式会被隐式转换为 float 值；否则，就会被转换为 bigint；转换失败则返回错误。

在实际开发中，top 参数一般要跟 order by 子句结合起来使用才更有意义，示例如下：

select top(5) t.* from t_students t order by t.birthday;         -- 查询年龄最大的前 5 个学生
select top(5) percent t.* from t_students t order by t.birthday; -- 查询年龄最大的前 5% 的学生

假如现在要查询最近一次英语考试排名前三的学生，但实际上第三名和第四名的分数相同，如果只是 top(3) 就会漏掉一个第三名，这时候在 top 的最后面加上 with ties 就会返回所有前三名的学生。示例如下：

select top(3) with ties t1.code,t1.name,t3.name,t2.counts,t2.scores 
from t_students t1,t_examresults t2,t_courses t3 
where t1.id = t2.studentid and t2.courseid = t3.id and t3.name = '英语' and t2.counts = 8 
order by t2.scores desc;

查询结果如下：

code            name            name          counts      scores
--------------- --------------- ------------- ----------- -------------------
s1032007        刘艳            英语            10          100.0
s1032009        宋梅            英语            10          99.0
s1032003        宋阳            英语            10          98.0
s1033005        马鹏飞          英语            10          98.0

再来看一个综合点的案例，按 classid 升序排序，查询前 30% 的学生信息，示例如下：

select top(30) percent with ties t.* from t_students t order by t.classid;

让我们来分析一下：因为学生总数 30 人，所以 30% 就是 30×0.3 等于 9 人；然后按 classid 升序排序，那么前 9 个人就都是 1 班的，然而 1 班有 12 个人，也就是说从第 9 个到第 12 个的 classid 都是 1，所以最终会返回 12 行（整个 1 班的学生信息都会被返回）。

1.2、查询语句的 from 子句

from 子句的作用是在增删改查中指定要被影响或查询的目标表。可以给查询语句的 from 子句中的表取别名，别名可带来使用上的方便，也可用于区分自连接或子查询中的表。如果出现重复的表名或别名，sql server 则会返回错误。如果连接中的多个表中存在同名的字段，则需要使用表名或别名来限定字段名。如果定义了别名，就不能再使用表名限定了。示例如下（查询学生和班级信息）：

select t2.name,t1.code,t1.name,t1.gender,t1.birthday 
from t_students t1
join t_classes t2 on t1.classid = t2.id;

如果表在同一 sql server 实例的另一个数据库中，则需要按照 database.schema.table-name 的形式来访问。示例如下：

select t.* from anchoredu.dbo.t_learncard t; -- 查询 anchoredu 库中 t_learncard 表的信息

如果表不在同一 sql server 的实例中，则需要按照 linked-server.catalog.schema.object 的形式来访问。示例如下：

select t.* from [192.168.0.16].shopdb.dbo.t_user t; -- 查询 shopdb 库中 t_user 表的信息

在设计数据库的时候，我们一般会根据数据库范式，将现实中的数据拆分为多个实体后分别存储到不同的表中，以避免过度的数据冗余。随之而来的问题是为了满足实际业务需要，我们往往需要同时获取多个表中的数据，这时候连结查询就派上用场了。

简单来讲，联合多个表来获取数据的查询就是连结查询。连结查询也是关系型数据库中最重要的查询，主要分为内连接、外链接和交叉连接。连结查询中通过 join 来指定哪些表参与连接，而 on 则用来指定连接所基于的匹配条件。示例如下：

select t1.* from t_students t1 join t_classes t2 on t1.classid=t2.id; -- 查询班级有效的学生信息

-- 查询考过 100 分的学生信息
select distinct t1.* from t_students t1 
join t_examresults t2 on t1.id = t2.studentid and t2.scores = 100;

注意：在 on 中做字段运算时，字段不必具有相同的数据类型。当数据类型不相同时，运算符两边的字段类型要么相互兼容，要么是 sql server 能够隐式转换的类型。如果不能隐式转换，就必须使用类型转换函数显式的转换数据类型才行。

1.2.1、内连接查询 inner join

内连接查询会返回所有匹配上的行，放弃不匹配的行。如果未指定任何连接类型，则默认未内连接。示例如下：

select t2.name,t1.code,t1.name,t4.name,t3.counts,t3.scores 
from t_students t1 
inner join t_classes t2 on t1.classid=t2.id 
inner join t_examresults t3 on t1.id=t3.studentid 
inner join t_courses t4 on t3.courseid=t4.id
order by t2.id,t1.id,t4.id,t3.counts;

1.2.2、外连接查询 outer join

外连接又分为左外连接 left outer、右外连接 right outer 和 全外连接 full outer，其中 outer 关键字是可以省略的。左外连接会根据左表数据，在右表中查出符合条件的记录与之匹配，并将未找到匹配行的右表输出列设置为 null。右外连接的查询步骤正好与左外连接相反。而全外连接相当于是把左外连接的操作和右外连接的操作都做一遍，然后取并集。示例如下：

select t2.name,t1.code,t1.name,t4.name,t3.counts,t3.scores 
from t_students t1 
left outer join t_classes t2 on t1.classid=t2.id 
right outer join t_examresults t3 on t1.id=t3.studentid 
full outer join t_courses t4 on t3.courseid=t4.id
order by t2.id,t1.id,t4.id,t3.counts;

1.2.3、交叉连接查询 cross join

交叉连接查询会返回被连接表的笛卡尔积，返回行数等于两个表的行数乘积。交叉连接不需要 on 条件，可以同时省略 cross join。示例如下：

select * from t_classes cross join t_courses;

注意：on 中的条件一般也可以用在 where 子句中，虽然这种条件位置的不同对 inner 连接查询结果不会有影响，但对 outer 连接来说就可能会导致出现不同的结果。这是因为 on 子句中的条件在应用于连接之前先应用于表，而 where 子句是在语义上应用于联接结果。

1.3、查询语句的 where 子句

where 子句的作用是定义要返回的行应满足的条件。示例如下：

select t.* from t_students t where t.birthday >= '2000-01-01'; -- 查询 00 后学生
select t.* from t_students t where t.id in(1,3,5,7,9);         -- 查询 id 为 1,3,5,7,9 的学生
select t.* from t_students t where t.id not in(1,3,5,7,9);     -- 查询 id 不为 1,3,5,7,9 的学生
select t.* from t_students t where t.id between 11 and 20;     -- 查询 id 在 11~20 之间的学生
select t.* from t_students t where t.id not between 11 and 20; -- 查询 id 不在 11~20 之间的学生

逻辑函数 and 和 or 的作用都是在 where 子语句中把两个或多个条件结合起来。不同的是，and 是当两边的条件都为 true 时才返回 true，否则就返回 false；而 or 是当两边有一个为 true 时就返回 true，否则就返回 false。而且 and 的优先级比 or 的优先级要高，不过可以通过使用括号来改变求值顺序。示例如下：

select t.* from t_students t where t.classid = 1 and t.gender = 0;               -- 同时满足两个条件
select t.* from t_students t where t.classid = 1 or t.classid = 3;               -- 满足两个条件之一
select t.* from t_students t where t.id >= 1 and t.classid = 1 and t.gender = 0; -- 同时满足多个条件
select t.* from t_students t where t.id >= 1 or t.classid = 1 or t.classid = 1;  -- 满足多个条件之一

注意：and 和 or 其实都还有返回 unknown 的情况，只是本人没遇到过，也不知道要怎么模拟，有兴趣的读者可以看官方文档自行了解。

1.4、查询语句的 order by 子句

order by 子句的作用对查询返回的数据进行排序。order by 指定的字段必须是选择列表中定义的字段或 from 子句中指定的表中定义的字段。指定字段时可以用字段名，也可以用字段的别名，还可以用表示字段在选择列表中所处位置的非负整数。示例如下：

select t.* from t_students t order by t.birthday;                 -- 按出生日期升序排序
select t.* from t_students t order by t.birthday desc;            -- 按选择列表中的字段降序排序
select t.code,t.name from t_students t order by t.birthday desc;  -- 按 from 中表的字段降序排序
select t.code as 学号,t.name 姓名 from t_students t order by 学号; -- 按字段别名升序排序

注意：desc 按从最大值到最小值的顺序进行排序，即降序。asc 表示按从最小值到最大值的顺序进行排序，即升序。asc 是 order by 子句的默认参数，可不写。在 sql server 的排序中，null 值总是被视为最小的值。

1.4.1、指定多个排序字段时，将先按第 1 个字段对结果集进行排序，然后按第 2 个字段对结果集进行排序，依此类推。示例如下：

select t.* from t_students t order by t.classid asc,t.gender,t.birthday desc;

1.4.2、指定表达式为排序条件（大数据量时性能会很差）示例如下：

select t.* from t_students t order by datepart(month, t.birthday); -- 按出生月份升序排序
select t.* from t_students t order by substring(t.code,5,3) desc;  -- 按学号后 3 位降序排序

1.4.3、有条件的指定排序条件示例如下：

select t.* from t_students t 
order by case t.gender when 1 then t.birthday end desc -- 男生按年龄降序排列
        ,case t.gender when 0 then t.birthday end asc; -- 女生按年龄升序排列

select t.* from t_students t 
order by case when t.classid = 1 then t.name -- 1 班的学生按姓名降序排序
         else t.code end desc;               -- 其它班的学生按学号降序排序

1.4.4、限制查询返回的行数从 sql server 2012 开始，支持在 order by 子句中使用 offset 和 fetch 来限制查询返回的行数，其中，offset 用于指定将要跳过的行数（即偏移量），而 fetch 用于指定最终要返回的行数（即提取数）。示例如下：

select * from t_students order by id offset 10 rows; -- 返回第 10 条以后的全部数据
select * from t_students order by id offset 10 rows fetch next 5 rows only; -- 返回第 11~15 条数据

注意：offset 和 fetch 必须在 order by 子句中才能使用，且不能与 top 在同一个查询*存。fetch 必须结合 offset 才能使用，但 offset 却可以单独使用。

2、插入子句 into

2.1、查询语句的 into 子句

select 语句中的 into 子句能够在默认文件组中创建一个新表，并将来自查询的结果行插入到新表中，且新表中字段的名称、数据类型、是否为 null 性和值都与查询选择列表中的相应字段或表达式的相同。换句话说，select into 的作用相当于是把一个表的表结构和表数据拷贝到一个新表中，但源表中的约束和索引是不会拷贝到新表中的。

select into 语句常用于创建表的备份或用于对记录进行存档。如要创建一个女学生表并填充相应信息，示例如下：

select t.* into t_girlstudents from t_students t where t.gender = 0;

2.1.1、通过多个源表来创建一个新表 如要创建女生历次考试数学成绩表，并填充相应信息，示例如下：

select t1.code,t1.name,t2.counts,t2.scores 
into t_girlexamresults 
from t_students t1 
join t_examresults t2 on t1.id = t2.studentid 
join t_courses t3 on t2.courseid = t3.id 
where t1.gender = 0 and t3.name = '语文'
order by t1.code,t2.counts;

2.1.2、以最小的日志记录创建一个新表 这样比先 create 表再 insert 数据的效率要高，如要创建最近一次考试的成绩表，示例如下：

alter database microedu set recovery bulk_logged; -- 修改恢复模式为大容量日志模式

select t.id,t.studentid,t.courseid,t.scores 
into t_lastexamresults 
from t_examresults t 
where t.counts = (select max(counts) from t_examresults);

alter database microedu set recovery full; -- 修改恢复模式为完整日志模式

2.1.3、在同一实例上的另一个数据库中创建表 如要再创建一个数据库 miniedu，然后把学生表拷贝过去，示例如下：

create database microedu2;
go
select t.* into microedu2.dbo.t_students from microedu.dbo.t_students t;

2.1.4、使用 identity 函数创建标识列 identity 函数可在有 into 子句的 select 语句中将标识列插入到新表中，语法如下：

identity(data-type [,seed, increment]) as column-name

其中 data-type 是标识列的数据类型，也是函数最终的返回类型，可以是任何整数类型，也可以是 decimal 数据类型。seed 是要分配给表中第一行的整数值，也就是标识种子。如果既没有指定 seed，也没有指定 increment，那么它们都默认为 1。increment 是要加到表中后续行的 seed 值上的整数值，也就是标识增量。column-name 是将要插入到新表中的字段名称。示例如下：

select identity(int,100,1) id,t.name into t_partstudents from t_students t where t.classid = 1;

注意：上例中由的 identity 函数指定的标识列与源表没有任何关系。

2.2、insert into select 与 select into

通俗来讲，insert into select 和 select into 两个语句的作用都是复制表，因为都是从一个表中查询出数据然后插入到另一个表中，被查询的表可以是派生表。二者的区别在于：insert into select 只复制表数据，不复制表结构，也不创建新表；而 select into 不仅复制表数据，还复制表结构，而且会创建新表。

从另一个角度来说，insert into select 要插入的表必须是已经存在的，因此需要考虑查询表与插入表的数据兼容性，而 select into 则会把数据插入到尚不存在的新表中。

还有一个角度就是，insert into select 要求必须有目标（插入）表，但可以没有（数据）源表，临时组装的数据即可；而 select into 恰恰相反，可以（也必须）没有目标表，但必须要有源表。

oracle(pl/sql) 中也有复制表的语句，相较于 sql server(t-sql) 的语法略有不同，其中 insert into select 二者是相同的，而 select into 在 oracle 中对应的语法是 create table select。

对开发者而言，复制表的功能并不常用，但也可能会遇到急需要用的时候。一般我们都会上网去查一下，这本身很正常，但遗憾的是网上很多帖子的作者可能也没搞清楚，只是模糊的写了个 sql。例如我当初急找 oracle 复制表时就多次找到了 select into，因此浪费了不少时间。但愿本文的读者看到这里的对比和总结后，不再走我当初的弯路了吧！

3、本文小结

本文主要讲述了 t-sql 语句中的 select 语句及其子句的简单用法，查询功能也是关系型数据库中最重要、最常用的一个功能。

本文参考链接：

• 1、sql server 2016 select
• 2、sql server 2016 from
• 3、sql server 2016 where
• 4、sql server 2016 like
• 5、sql server 2016 into

本文链接：
版权声明：本文为博客园博主 原创，作者保留署名权！欢迎通过转载、演绎或其它传播方式来使用本文，但必须在明显位置给出作者署名和本文链接！个人博客，能力有限，若有不当之处，敬请批评指正，谢谢！