T-SQL复习05--基础查询

• SELECT语句语法
• 查询执行顺序
• 使用SELECT…WHERE查询数据
  • 使用通配符*查询所有列
  • 查询指定的列
  • 使用as给列或表指定别名
  • where使用
• 模糊查询
• OR/AND逻辑运算
• BETWEEN…AND
• IN
• IS NULL
• ORDER BY
• 排名函数RANK
• 排名函数ROW_NUMBER
• 过滤重复数据
• 判断对象(表/存储过程/视图等)是否存在
  • 判断表是否存在方式1
  • 判断表是否存在方式2
• SELECT…INTO
• INSERT…SELECT
• MERGE..INTO
• TOP
  • 使用TOP限制删除的行数
  • 使用TOP限制更新的行数
  • 使用TOP限制插入行数

SELECT语句语法

基本语法：

select 选择列表(查询所显示的内容)
from 表名(查询内容所在的表)
where 查询条件(查询内容的条件)
group by 条件(分组汇总的条件)
having 条件(筛选条件)
order by 列名(排序基准)

解析：

1. 选择列表：可以包括几个列名或者表达式，用逗号隔开，用于指示应该返回哪些数据
2. from子句包含提供数据的表或视图名称
3. where子句用于给出查询条件
4. 聚合函数对数据进行汇总计算，使用having进行筛选
5. order by子句决定结果集的排列顺序

查询执行顺序

1. FROM：查询首先回根据select语句的from子句组装初始数据集
2. WHERE：筛选阶段实际上是根据where子句进行筛选
3. 聚合：SQL语句对数据集执行聚合运算
4. ORDER BY：根据from子句和where子句筛选出满足条件的行后，按照order by子句指定方式排序
5. 谓词：可以只显示前几行或只返回指定行

使用SELECT…WHERE查询数据

使用通配符*查询所有列

示例：

SELECT * FROM dbo.STUDENT

查询指定的列

示例：

SELECT NAME,AGE FROM dbo.STUDENT

使用as给列或表指定别名

示例：

SELECT NAME AS 姓名,AGE AS 性别 FROM dbo.STUDENT AS 学生表

其中AS可以直接省略，例如：

SELECT NAME 姓名,AGE 性别 FROM dbo.STUDENT 学生表

where使用

示例：

SELECT * FROM dbo.STUDENT WHERE NAME='李筱思'
SELECT * FROM dbo.STUDENT WHERE AGE>16

注：字符类型和时间类型的数据需要加引号标识

模糊查询

示例：

-- 查找学生姓名以'李'开头的学生信息
SELECT * FROM STUDENT WHERE NAME LIKE '李%'

-- 查找学生姓名以'李'或以'赵'开头的学生信息
SELECT * FROM STUDENT WHERE NAME LIKE '[李赵]%'

-- 查找学生姓名不以'李'开头的学生信息
SELECT * FROM STUDENT WHERE NAME NOT LIKE '李%'

-- 查找学生姓名不以'李'或以'赵'开头的学生信息
SELECT * FROM STUDENT WHERE NAME LIKE '[^李赵]%'

-- 查找学生姓名以'李'开头，名字只有一个字符的学生信息
SELECT * FROM STUDENT WHERE NAME LIKE '[李]_'

OR/AND逻辑运算

示例：

-- 查询姓名以'李'开头或年龄是13岁的学生信息
SELECT * FROM STUDENT WHERE NAME LIKE '李%' OR AGE=13

-- 查询姓名以'李'开头，或年龄是13岁性别为女的学生信息
SELECT * FROM STUDENT WHERE NAME LIKE '李%' OR (AGE=13 AND SEX='女')

-- 查询姓名以'李'开头且性别为女,或年龄13岁且性别为女的学生信息
SELECT * FROM STUDENT WHERE (NAME LIKE '李%' AND SEX='女') OR (AGE=13 AND SEX='女')

BETWEEN…AND

示例：

-- 查询年龄在11到13范围内的学生
SELECT * FROM STUDENT WHERE AGE BETWEEN 11 AND 13

-- 查询年龄不在11到13范围内的学生
SELECT * FROM STUDENT WHERE AGE NOT BETWEEN 11 AND 13

注：BETWEEN…AND是包括边界的

IN

示例：

-- 查询年龄为10岁或15岁的学生
SELECT * FROM STUDENT WHERE AGE IN (10,15)
-- 其实这里跟使用OR是一个效果

-- 查询年龄不为10岁或15岁的学生
SELECT * FROM STUDENT WHERE AGE NOT IN (10,15)

IS NULL

示例：

-- 查询地址为空的学生信息
SELECT * FROM STUDENT WHERE ADDRESS IS NULL

-- 查询地址不为空的学生信息
SELECT * FROM STUDENT WHERE ADDRESS IS NOT NULL

ORDER BY

ORDER BY ... DESC/ASC：降序排序/升序排序，默认是ASC
示例：

-- 查询所有学生，按年龄降序排序
SELECT * FROM STUDENT ORDER BY AGE DESC

-- 查询所有学生，按年龄升序排序
SELECT * FROM STUDENT ORDER BY AGE ASC

同时对多个字段排序：若以多个字段排序时，以第一个排序的字段为主，当第一个字段出现相等值时对第二个字段排序

示例：

-- 同时对学生性别、年龄排序
SELECT * FROM STUDENT ORDER BY SEX DESC,AGE DESC

排名函数RANK

具体语法：

RANK() OVER(PARTITION BY 表达式 ORDER BY 表达式)

注：针对分区内的每行数据排名，若排序字符按值相等，则排名相同，PARTITION BY子句可选
示例：

-- 按照学生年龄降序排名
SELECT *,RANK() OVER(ORDER BY AGE DESC) AS 排名 FROM dbo.STUDENT

-- 按照性别分组，在分组内按照年龄降序排名
SELECT *,RANK() OVER(PARTITION BY SEX ORDER BY AGE DESC) 排名 FROM dbo.STUDENT

-- 按照性别分组，在分组内按照年龄降序排名，对结果集按照年龄升序排序
SELECT *,RANK() OVER(PARTITION BY SEX ORDER BY AGE DESC) 排名 FROM dbo.STUDENT ORDER BY AGE ASC

排名函数ROW_NUMBER

具体语法：

ROW_NUMBER() OVER(PARTITION BY 表达式 ORDER BY 表达式)

注：对分区内的每行数据排名，若排序字段值相等，则排名累计，PARTITION BY子句可选
示例：

-- 按照学生年龄降序排名
SELECT *,ROW_NUMBER() OVER(ORDER BY AGE DESC) AS 排名 FROM dbo.STUDENT

-- 按照性别分组，在分组内按照年龄降序排名
SELECT *,ROW_NUMBER() OVER(PARTITION BY SEX ORDER BY AGE DESC) 排名 FROM dbo.STUDENT

-- 按照性别分组，在分组内按照年龄降序排名，对结果集按照年龄升序排序
SELECT *,ROW_NUMBER() OVER(PARTITION BY SEX ORDER BY AGE DESC) 排名 FROM dbo.STUDENT ORDER BY AGE ASC

过滤重复数据

DISTINCT消除重复项

DISTINCT关键字可以从SELECT语句的结果中消除重复的行，如果没有指定DISTINCT，将返回所有行，包括重复的行

示例：

SELECT DISTINCT ADDRESS FROM dbo.STUDENT

判断对象(表/存储过程/视图等)是否存在

判断表是否存在方式1

使用SYS.OBJECTS测试，在数据库中创建的每个用户定义的架构作用域内的对象在该表中对应一行

示例：

IF EXISTS(SELECT * FROM sys.objects WHERE name='STUDENT')
DROP TABLE/PROC dbo.STUDENT

判断表是否存在方式2

使用OBJECT_ID测试，OBJECT_ID(OBJECT_NAME.[OBJECT_TYPE])返回架构范围内数据库对象的唯一标识。

OBJECT_TYPE：U表示用户自定义表，P表示SQL存储过程，V表示视图

示例：

IF(OBJECT_ID('STUDENT','U/P/V') IS NOT NULL)
DROP TABLE/PROC dbo.STUDENT

SELECT…INTO

使用SELECT...INTO创建表并复制表中数据，源表中定义的索引、约束和触发器不会转移到新表中，也不能在SELECT...INTO语句中指定它们

如果定义表的SELECT语句包含一个联接，而该联接将导致IDENTITY属性不能转移到新表

示例：

-- 创建表并复制表数据
-- 先判断表是否存在，存在则删除，然后再用SELECT...INTO
IF(OBJECT_ID('STUDENT_CP','U') IS NOT NULL)
DROP TABLE dbo.STUDENT_CP
GO
SELECT * INTO STUDENT_CP FROM dbo.STUDENT

INSERT…SELECT

使用INSERT...SELECT将数据从一个表插入另一个表

基本语法：

INSERT INTO 目标表...INSERT INTO 源表

注：目标表必须存在
示例：

-- 先判断表是否存在，存在则删除，然后再创建一张表，再往表里写源表查询出来的数据
IF OBJECT_ID('STUDENT_INSERT','U') IS NOT NULL
DROP TABLE STUDENT_INSERT
GO
CREATE TABLE STUDENT_INSERT
(
姓名 VARCHAR(10),
性别 CHAR(2),
年龄 INT
)
GO
INSERT INTO STUDENT_INSERT SELECT NAME,SEX,AGE FROM dbo.STUDENT

MERGE..INTO

根据与源表的联接的结果，对目标表执行插入、更新或删除操作。例如：根据在另一个表中找到的差异在一个表中插入、更新或删除行，可以对两个表进行同步

示例：

-- 合并两张学生表的学生信息，当学生姓名相同时更新目标表的学生信息，当目标表没有该学生时插入学生信息
-- 目标表=STUDENT  源表=STUDENT_INSERT
-- 以下操作描述：
    -- 以STUDENT_INSERT为准，如果STUDENT里的哪条数据跟STUDENT_INSERT里哪条数据的NAME相同，则将STUDENT里这条数据的年龄更新成STUDENT_INSERT里这条数据的年龄
    -- 如果STUDENT_INSERT里有数据的姓名是STUDENT里没有的，则向STUDENT里插入这条数据
MERGE INTO dbo.STUDENT
USING dbo.STUDENT_INSERT
ON dbo.STUDENT.NAME=dbo.STUDENT_INSERT.姓名
WHEN MATCHED THEN UPDATE SET AGE=dbo.STUDENT_INSERT.年龄 --匹配上则更新年龄
WHEN NOT MATCHED THEN INSERT (NAME,SEX,AGE) VALUES(dbo.STUDENT_INSERT.姓名,dbo.STUDENT_INSERT.性别,dbo.STUDENT_INSERT.年龄); --没匹配上则插入
GO

TOP

TOP限制返回结果集的行数，当将TOP与ORDER BY子句结合使用时，结果集限制为前N个已排序行，否则，它将以未定义的顺序返回前N个行

示例：

-- 查询学生表中前3行数据
SELECT TOP 3 * FROM dbo.STUDENT

-- 对学生表按年龄排序返回前3行数据
SELECT TOP 3 * FROM dbo.STUDENT ORDER BY AGE

-- TOP N WITH TIES 返回结果集前N条记录，以及排序字段值与第N条记录相等的记录，TOP N WITH TIES必须与ORDER BY一同使用
SELECT TOP 3 WITH TIES * FROM dbo.STUDENT ORDER BY AGE

使用TOP限制删除的行数

示例：

-- 删除年龄小于15岁的任意一个学生数据
DELETE TOP(1) FROM dbo.STUDENT WHERE AGE<15

-- 删除年龄小于15岁年龄最小的一个学生
DELETE FROM dbo.STUDENT WHERE AGE IN (SELECT TOP 1 AGE FROM dbo.STUDENT WHERE AGE<15 ORDER BY AGE ASC)

使用TOP限制更新的行数

示例：

-- 更新年龄小于15岁的任意一个学生的年龄
UPDATE TOP(1) dbo.STUDENT SET AGE=AGE+10 WHERE AGE<15

-- 更新年龄小于15岁年龄最小的一位学生的年龄
UPDATE dbo.STUDENT SET AGE=AGE+10 FROM (SELECT TOP 1 ID FROM dbo.STUDENT WHERE AGE<15 ORDER BY AGE ASC) TB_TOP WHERE TB_TOP.ID=dbo.STUDENT.ID

使用TOP限制插入行数

示例：

-- 向新创建的表中插入任意两条学生数据
    -- 先创建STUDENT02表
    -- 在插入数据
SELECT * INTO STUDENT02 FROM dbo.STUDENT WHERE 1=2
GO
INSERT TOP(2) INTO STUDENT02
OUTPUT Inserted.NAME,Inserted.SEX,Inserted.AGE
SELECT NAME,SEX,AGE FROM dbo.STUDENT
GO

-- 向创建的新表中插入年龄最小的两条学生数据
SELECT * INTO STUDENT03 FROM dbo.STUDENT WHERE 1=2
GO
INSERT INTO STUDENT03
OUTPUT Inserted.NAME,Inserted.SEX,Inserted.AGE
SELECT TOP(2) NAME,SEX,AGE FROM dbo.STUDENT ORDER BY AGE ASC
GO

注：Inserted是一张系统的临时表，将要插入的数据都会在这个表里有记录，OUTPUT就是将这些要插入的数据显示出来