SQLserver基础语句大全_sqlserver语句大全-CSDN博客

本文链接：https://blog.csdn.net/qq_34156628/article/details/91352131

本文全面介绍了SQL的基础知识，包括SQL的DML和DDL部分，详细解释了SELECT、UPDATE、DELETE、INSERT INTO等数据操作语言，以及CREATE DATABASE、ALTER DATABASE、CREATE TABLE等数据定义语言。文章还涵盖了SQL的语法、执行步骤、运算符、通配符、JOIN操作、约束、索引、视图、日期处理、数据类型、合计函数、GROUP BY和HAVING子句等内容。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL 基础

结构化查询语言(Structured Query Language)简称 SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统; 同时也是数据库脚本文件的扩展名。

SQL DML 和 DDL

可以把 SQL 分为两个部分:数据操作语言 (DML) 和数据定义语言 (DDL)。

SQL (结构化查询语言)是用于执行查询的语法。但是 SQL 语言也包含用于更新、插入和删除记录的语法。

查询和更新指令构成了 SQL 的 DML 部分: ➢ SELECT - 从数据库表中获取数据
➢ UPDATE - 更新数据库表中的数据
➢ DELETE - 从数据库表中删除数据

➢ INSERT INTO - 向数据库表中插入数据

SQL 的数据定义语言 (DDL) 部分使我们有能力创建或删除表格。我们也可以定义索引 (键)，规定表之间的链接，以及施加表间的约束。

SQL 中最重要的 DDL 语句:
➢ CREATE DATABASE - 创建新数据库
➢ ALTER DATABASE - 修改数据库
➢ CREATE TABLE - 创建新表
➢ ALTER TABLE - 变更(改变)数据库表 ➢ DROP TABLE - 删除表
➢ CREATE INDEX - 创建索引(搜索键) ➢ DROP INDEX - 删除索引

重要事项

一定要记住，SQL 对大小写不敏感!

SQL SELECT 语句

Sql 语法:

SELECT[ALL|DISTINCT|DISTINCTROW|TOP] {*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,...]]} FROM tableexpression[,...][IN externaldatabase]
[WHERE...]
[GROUP BY...]
[HAVING...]
[ORDER BY...]

执行步骤:
SQL Select 语句完整的执行顺序【从 DBMS 使用者角度】:

1、from 子句组装来自不同数据源的数据; 2、where 子句基于指定的条件对记录行进行筛选; 3、group by 子句将数据划分为多个分组; 4、使用聚集函数进行计算;
5、使用 having 子句筛选分组; 6、计算所有的表达式;
7、使用 order by 对结果集进行排序。

SQL Select 语句的执行步骤【从 DBMS 实现者角度，这个对我们用户意义不大】:

1)语法分析，分析语句的语法是否符合规范，衡量语句中各表达式的意义。
2) 语义分析，检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。 3)视图转换，将涉及视图的查询语句转换为相应的对基表查询语句。 4)表达式转换，将复杂的 SQL 表达式转换为较简单的等效连接表达式。 5)选择优化器，不同的优化器一般产生不同的“执行计划”
6)选择连接方式， ORACLE 有三种连接方式，对多表连接 ORACLE 可选择适当

的连接方式。
7)选择连接顺序，对多表连接 ORACLE 选择哪一对表先连接，选择这两表中哪

个表做为源数据表。 8)选择数据的搜索路径，根据以上条件选择合适的数据搜索路径，如是选用全表

搜索还是利用索引或是其他的方式。 9)运行“执行计划”。

运算符

字符匹配符

脚本示例:

with Persons as (
select '1' as id, 'Adams' as lastname, 'John' as firstname, 'Oxford Street' as address, 'London' as city union all
select '2' as id, 'Bush' as lastname, 'George' as firstname, 'Fifth Avenue' as address, 'New York' as city union all
select '3' as id, 'Carter' as lastname, 'Thomas' as firstname, 'Changan Street' as address, 'Beijing' as city )
select * from Persons

with Orders as (
select 'IBM' as company ,'3532' as ordernumber union all select 'W3School' as company ,'2356' as ordernumber union all select 'Apple' as company ,'4698' as ordernumber union all select 'W3School' as company ,'6953' as ordernumber)
select * from Orders

SQL distinct

关键词 DISTINCT 用于返回唯一不同的值。

with Orders as (
select 'IBM' as company ,'3532' as ordernumber union all select 'W3School' as company ,'2356' as ordernumber union all select 'Apple' as company ,'4698' as ordernumber union all select 'W3School' as company ,'6953' as ordernumber)
select distinct company from Orders

SQL where

WHERE 子句用于规定选择的标准。

如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句。

AND 和 OR 运算符

AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。

with persons as (
select 'Adams' as lastname,'John' as firstname ,'Oxford Street' as address,'London' as city,'1970' as year union all
select 'Bush'as lastname,'George' as firstname ,'Fifth Avenue' as address,'New York'

as city,'1975' as year union all
select 'Carter' as lastname,'Thomas' as firstname ,'Changan Street' as address,'Beijing' as city,'1980' as year union all
select 'Gates' as lastname,'Bill' as firstname ,'Xuanwumen 10' address,'Beijing' as city,'1985' as year )
select * from persons WHERE FirstName='Thomas'

AND 和 OR 运算符用于基于一个以上的条件对记录进行过滤。

SQL as

with persons as (
select 'Adams' as lastname ,'John' as FirstName,'Oxford Street'
address ,'London' as city union all
select 'Bush'as lastname ,'George' as FirstName,'Fifth Avenue' as address ,'New York' as city union all
select 'Carter' as lastname ,'Thomas' as FirstName,'Changan Street' as
address ,'Beijing' as city union all
select 'Carter' as lastname ,'William'as FirstName,'Xuanwumen 10' as
address ,'Beijing' as city)
select * from persons WHERE FirstName='Thomas' AND LastName='Carter'

SQL order by

ORDER BY 语句用于对结果集进行排序。

ORDER BY 语句
ORDER BY 语句用于根据指定的列对结果集进行排序。
ORDER BY 语句默认按照升序对记录进行排序。如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。

with Orders as (
select 'IBM' as company ,'3532' as ordernumber union all select 'W3School' as company ,'2356' as ordernumber union all select 'Apple' as company ,'4698' as ordernumber union all select 'W3School' as company ,'6953' as ordernumber)
select distinct company from Orders

SQL top

TOP 子句

TOP 子句用于规定要返回的记录的数目。对于拥有数千条记录的大型表来说，TOP 子句是非常有用的。注释:并非所有的数据库系统都支持 TOP 子句。

with Persons as (
select '1' as id, 'Adams' as lastname, 'John' as firstname, 'Oxford Street' as

as

address, 'London' as city union all
select '2' as id, 'Bush' as lastname, 'George' as firstname, 'Fifth Avenue' as address, 'New York' as city union all
select '3' as id, 'Carter' as lastname, 'Thomas' as firstname, 'Changan Street' as address, 'Beijing' as city )
select top 2 * from Persons

SQL like

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

LIKE 操作符
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

with Persons as (
select '1' as id, 'Adams' as lastname, 'John' as firstname, 'Oxford Street' as address, 'London' as city union all
select '2' as id, 'Bush' as lastname, 'George' as firstname, 'Fifth Avenue' as address, 'New York' as city union all
select '3' as id, 'Carter' as lastname, 'Thomas' as firstname, 'Changan Street' as address, 'Beijing' as city )
select * from Persons where City LIKE 'N%'

在搜索数据库中的数据时，SQL 通配符可以替代一个或多个字符。 SQL 通配符必须与 LIKE 运算符一起使用。

with Persons as (
select '1' as id, 'Adams' as lastname, 'John' as firstname, 'Oxford Street' as address, 'London' as city union all
select '2' as id, 'Bush' as lastname, 'George' as firstname, 'Fifth Avenue' as address, 'New York' as city union all
select '3' as id, 'Carter' as lastname, 'Thomas' as firstname, 'Changan Street' as address, 'Beijing' as city )
select * from Persons where City LIKE '%lond%'

with Persons as (
select '1' as id, 'Adams' as lastname, 'John' as firstname, 'Oxford Street'

as address, 'London' as city union all
select '2' as id, 'Bush' as lastname, 'George' as firstname, 'Fifth Avenue' as address, 'New York' as city union all
select '3' as id, 'Carter' as lastname, 'Thomas' as firstname, 'Changan Street' as address, 'Beijing' as city )
select * from Persons where FirstName LIKE '_eorge'

with Persons as (
select '1' as id, 'Adams' as lastname, 'John' as firstname, 'Oxford Street' as address, 'London' as city union all
select '2' as id, 'Bush' as lastname, 'George' as firstname, 'Fifth Avenue' as address, 'New York' as city union all

select '3' as id, 'Carter' as lastname, 'Thomas' as firstname, 'Changan Street' as address, 'Beijing' as city )
select * from Persons where LastName LIKE 'C_r_er'

with Persons as (
select '1' as id, 'Adams' as lastname, 'John' as firstname, 'Oxford Street' as address, 'London' as city union all
select '2' as id, 'Bush' as lastname, 'George' as firstname, 'Fifth Avenue' as address, 'New York' as city union all
select '3' as id, 'Carter' as lastname, 'Thomas' as firstname, 'Changan Street' as address, 'Beijing' as city )
select * from Persons where City LIKE '[ALN]%';

with Persons as (
select '1' as id, 'Ad