SQLServer SQL基础

SQL概述

SQL（Structured Query Language）是用于管理关系型数据库的标准语言。SQLServer支持ANSI SQL标准，并扩展了许多专有功能。SQL语句主要分为以下几类：

数据查询语言（DQL）：用于查询数据，主要包括SELECT语句
数据操作语言（DML）：用于修改数据，主要包括INSERT、UPDATE、DELETE语句
数据定义语言（DDL）：用于定义数据库对象，主要包括CREATE、ALTER、DROP语句
数据控制语言（DCL）：用于控制数据访问权限，主要包括GRANT、REVOKE语句
事务控制语言（TCL）：用于管理事务，主要包括BEGIN TRANSACTION、COMMIT、ROLLBACK语句

数据类型

SQLServer支持多种数据类型，包括数值型、字符型、日期时间型、二进制型等。选择合适的数据类型对于提高查询性能和减少存储空间非常重要。

数值型

数据类型	描述	存储大小	范围
INT	整数	4字节	-2^31 到 2^31-1
BIGINT	大整数	8字节	-2^63 到 2^63-1
SMALLINT	小整数	2字节	-2^15 到 2^15-1
TINYINT	微型整数	1字节	0 到 255
DECIMAL(p,s)	固定精度小数	5-17字节	取决于精度
NUMERIC(p,s)	与DECIMAL相同	5-17字节	取决于精度
FLOAT(n)	浮点型	4或8字节	取决于精度
REAL	单精度浮点型	4字节	-3.40E+38 到 3.40E+38

字符型

数据类型	描述	存储大小	最大长度
CHAR(n)	固定长度字符串	n字节	8000
VARCHAR(n)	可变长度字符串	实际长度 + 2字节	8000
VARCHAR(MAX)	大可变长度字符串	实际长度 + 2字节	2^31-1
TEXT	文本数据	实际长度 + 2字节	2^31-1
NCHAR(n)	固定长度Unicode字符串	2n字节	4000
NVARCHAR(n)	可变长度Unicode字符串	实际长度 * 2 + 2字节	4000
NVARCHAR(MAX)	大可变长度Unicode字符串	实际长度 * 2 + 2字节	2^31-1
NTEXT	Unicode文本数据	实际长度 * 2 + 2字节	2^31-1

日期时间型

数据类型	描述	存储大小	范围
DATE	日期	3字节	0001-01-01 到 9999-12-31
TIME	时间	3-5字节	00:00:00.0000000 到 23:59:59.9999999
DATETIME	日期和时间	8字节	1753-01-01 到 9999-12-31
SMALLDATETIME	日期和时间	4字节	1900-01-01 到 2079-06-06
DATETIME2	高精度日期和时间	6-8字节	0001-01-01 到 9999-12-31
DATETIMEOFFSET	带时区的日期和时间	8-10字节	0001-01-01 到 9999-12-31

二进制型

数据类型	描述	存储大小	最大长度
BINARY(n)	固定长度二进制数据	n字节	8000
VARBINARY(n)	可变长度二进制数据	实际长度 + 2字节	8000
VARBINARY(MAX)	大可变长度二进制数据	实际长度 + 2字节	2^31-1
IMAGE	图像数据	实际长度 + 2字节	2^31-1

基本SQL语句

SELECT语句

SELECT语句用于查询数据，是最常用的SQL语句之一。

基本语法：

sql

SELECT column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column1, column2, ...
HAVING condition
ORDER BY column1, column2, ... ASC|DESC;

示例：

sql

-- 查询所有员工信息
SELECT * FROM Employees;

-- 查询员工姓名和工资，并按工资降序排序
SELECT EmployeeName, Salary FROM Employees ORDER BY Salary DESC;

-- 查询部门平均工资，并筛选平均工资大于5000的部门
SELECT DepartmentID, AVG(Salary) AS AvgSalary 
FROM Employees 
GROUP BY DepartmentID 
HAVING AVG(Salary) > 5000;

INSERT语句

INSERT语句用于向表中插入数据。

基本语法：

sql

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

示例：

sql

-- 插入单条记录
INSERT INTO Employees (EmployeeName, DepartmentID, Salary, HireDate) 
VALUES ('张三', 1, 5000, '2023-01-01');

-- 插入多条记录
INSERT INTO Employees (EmployeeName, DepartmentID, Salary, HireDate) 
VALUES 
('李四', 2, 6000, '2023-02-01'),
('王五', 1, 5500, '2023-03-01');

-- 从其他表插入数据
INSERT INTO Employees (EmployeeName, DepartmentID, Salary, HireDate)
SELECT CandidateName, DepartmentID, ExpectedSalary, InterviewDate
FROM Candidates WHERE InterviewResult = 'Pass';

UPDATE语句

UPDATE语句用于更新表中的数据。

基本语法：

sql

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

示例：

sql

-- 更新单个员工的工资
UPDATE Employees SET Salary = 6000 WHERE EmployeeID = 1;

-- 更新多个员工的工资
UPDATE Employees SET Salary = Salary * 1.1 WHERE DepartmentID = 1;

-- 使用子查询更新数据
UPDATE Employees 
SET Salary = Salary * 1.05 
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = '北京');

DELETE语句

DELETE语句用于删除表中的数据。

基本语法：

sql

DELETE FROM table_name WHERE condition;

示例：

sql

-- 删除单个员工
DELETE FROM Employees WHERE EmployeeID = 1;

-- 删除多个员工
DELETE FROM Employees WHERE DepartmentID = 2;

-- 删除所有员工
DELETE FROM Employees;

-- 使用子查询删除数据
DELETE FROM Employees 
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = '上海');

CREATE TABLE语句

CREATE TABLE语句用于创建新表。

基本语法：

sql

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
    constraint_name constraint_type (column1, column2, ...)
);

示例：

sql

-- 创建员工表
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    EmployeeName VARCHAR(50) NOT NULL,
    DepartmentID INT NOT NULL,
    Salary DECIMAL(10,2) NOT NULL,
    HireDate DATE NOT NULL,
    Email VARCHAR(100) UNIQUE,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

ALTER TABLE语句

ALTER TABLE语句用于修改现有表的结构。

基本语法：

sql

-- 添加列
ALTER TABLE table_name ADD column_name datatype constraint;

-- 修改列
ALTER TABLE table_name ALTER COLUMN column_name datatype constraint;

-- 删除列
ALTER TABLE table_name DROP COLUMN column_name;

-- 添加约束
ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_type (column1, column2, ...);

-- 删除约束
ALTER TABLE table_name DROP CONSTRAINT constraint_name;

示例：

sql

-- 添加新列
ALTER TABLE Employees ADD Phone VARCHAR(20);

-- 修改列的数据类型
ALTER TABLE Employees ALTER COLUMN Salary DECIMAL(12,2);

-- 删除列
ALTER TABLE Employees DROP COLUMN Phone;

-- 添加唯一约束
ALTER TABLE Employees ADD CONSTRAINT UQ_Employees_Email UNIQUE (Email);

DROP TABLE语句

DROP TABLE语句用于删除表。

基本语法：

sql

DROP TABLE table_name;

示例：

sql

-- 删除员工表
DROP TABLE Employees;

-- 级联删除相关表
DROP TABLE Employees CASCADE;

版本差异

SQLServer 2008及以上版本

支持DATE、TIME、DATETIME2、DATETIMEOFFSET等新日期时间数据类型
支持MERGE语句，用于合并数据
支持表值参数，允许将表作为参数传递给存储过程

SQLServer 2012及以上版本

支持OFFSET FETCH子句，用于分页查询
支持FORMAT函数，用于格式化日期、时间和数字
支持IIF函数，用于条件判断
支持CHOOSE函数，用于从值列表中返回指定索引的值

SQLServer 2016及以上版本

支持STRING_SPLIT函数，用于分割字符串
支持JSON函数，用于处理JSON数据
支持DROP IF EXISTS语句，用于条件删除数据库对象
支持TEMPORARY TABLES的自动清理

SQLServer 2017及以上版本

支持STRING_AGG函数，用于字符串聚合
支持TRANSLATE函数，用于字符替换
支持CONCAT_WS函数，用于带分隔符的字符串连接
支持JSON路径表达式的增强

SQLServer 2019及以上版本

支持Batch Mode on Rowstore，提高行存储表的查询性能
支持Table Variable Deferred Compilation，提高表变量的性能
支持近似查询处理，用于大数据量的近似聚合

实际生产场景

场景1：员工信息管理

业务需求：

创建员工表，存储员工基本信息
支持员工信息的增删改查
支持按部门和工资范围查询员工
支持统计部门平均工资

实现步骤：

创建部门表和员工表
插入测试数据
实现员工信息的增删改查
实现部门工资统计

示例代码：

sql

-- 创建部门表
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY IDENTITY(1,1),
    DepartmentName VARCHAR(50) NOT NULL,
    Location VARCHAR(50) NOT NULL
);

-- 创建员工表
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    EmployeeName VARCHAR(50) NOT NULL,
    DepartmentID INT NOT NULL,
    Salary DECIMAL(10,2) NOT NULL,
    HireDate DATE NOT NULL,
    Email VARCHAR(100) UNIQUE,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

-- 插入部门数据
INSERT INTO Departments (DepartmentName, Location)
VALUES ('研发部', '北京'), ('市场部', '上海'), ('财务部', '广州');

-- 插入员工数据
INSERT INTO Employees (EmployeeName, DepartmentID, Salary, HireDate, Email)
VALUES 
('张三', 1, 5000, '2023-01-01', 'zhangsan@example.com'),
('李四', 1, 6000, '2023-02-01', 'lisi@example.com'),
('王五', 2, 5500, '2023-03-01', 'wangwu@example.com'),
('赵六', 2, 6500, '2023-04-01', 'zhaoliu@example.com'),
('孙七', 3, 7000, '2023-05-01', 'sunqi@example.com');

-- 查询研发部的员工
SELECT * FROM Employees WHERE DepartmentID = 1;

-- 查询工资大于6000的员工
SELECT * FROM Employees WHERE Salary > 6000;

-- 统计各部门的平均工资
SELECT d.DepartmentName, AVG(e.Salary) AS AvgSalary
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentName;

场景2：订单管理系统

业务需求：

创建订单表和订单详情表
支持订单的创建、查询和更新
支持按客户和日期范围查询订单
支持统计订单总额

实现步骤：

创建客户表、产品表、订单表和订单详情表
插入测试数据
实现订单的创建和查询
实现订单统计功能

示例代码：

sql

-- 创建客户表
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY IDENTITY(1,1),
    CustomerName VARCHAR(50) NOT NULL,
    ContactName VARCHAR(50),
    Phone VARCHAR(20),
    Email VARCHAR(100)
);

-- 创建产品表
CREATE TABLE Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    ProductName VARCHAR(50) NOT NULL,
    UnitPrice DECIMAL(10,2) NOT NULL,
    Stock INT NOT NULL
);

-- 创建订单表
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY IDENTITY(1,1),
    CustomerID INT NOT NULL,
    OrderDate DATE NOT NULL,
    TotalAmount DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- 创建订单详情表
CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY IDENTITY(1,1),
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- 插入测试数据
INSERT INTO Customers (CustomerName, ContactName, Phone, Email)
VALUES ('客户A', '张三', '13800138001', 'customerA@example.com'),
       ('客户B', '李四', '13800138002', 'customerB@example.com');

INSERT INTO Products (ProductName, UnitPrice, Stock)
VALUES ('产品1', 100.00, 100),
       ('产品2', 200.00, 50),
       ('产品3', 300.00, 20);

-- 创建订单
BEGIN TRANSACTION;

-- 插入订单
INSERT INTO Orders (CustomerID, OrderDate, TotalAmount)
VALUES (1, GETDATE(), 500.00);

DECLARE @OrderID INT = SCOPE_IDENTITY();

-- 插入订单详情
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, UnitPrice)
VALUES (@OrderID, 1, 2, 100.00),
       (@OrderID, 2, 1, 200.00),
       (@OrderID, 3, 1, 300.00);

-- 更新产品库存
UPDATE Products SET Stock = Stock - 2 WHERE ProductID = 1;
UPDATE Products SET Stock = Stock - 1 WHERE ProductID = 2;
UPDATE Products SET Stock = Stock - 1 WHERE ProductID = 3;

COMMIT TRANSACTION;

-- 查询订单
SELECT o.OrderID, c.CustomerName, o.OrderDate, o.TotalAmount
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID;

-- 查询订单详情
SELECT o.OrderID, p.ProductName, od.Quantity, od.UnitPrice, od.Quantity * od.UnitPrice AS Subtotal
FROM OrderDetails od
JOIN Orders o ON od.OrderID = o.OrderID
JOIN Products p ON od.ProductID = p.ProductID
WHERE o.OrderID = 1;

-- 统计客户订单总额
SELECT c.CustomerName, SUM(o.TotalAmount) AS TotalOrdersAmount
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
GROUP BY c.CustomerName;

SQL编写最佳实践

1. 选择合适的数据类型

使用最小的合适数据类型，减少存储空间
对于字符串，优先使用VARCHAR(n)而不是CHAR(n)，除非字符串长度固定
对于日期时间，优先使用DATE、TIME、DATETIME2等新数据类型
避免使用TEXT、NTEXT、IMAGE等旧数据类型，改用VARCHAR(MAX)、NVARCHAR(MAX)、VARBINARY(MAX)

2. 优化SELECT语句

只查询需要的列，避免使用SELECT *
使用WHERE子句过滤数据，减少返回的行数
合理使用索引，提高查询性能
避免在WHERE子句中使用函数，否则会导致索引失效
对于大数据量的查询，使用分页查询

3. 优化INSERT、UPDATE、DELETE语句

使用批量插入，减少网络开销
避免在循环中执行DML语句
使用事务管理，确保数据一致性
合理设置事务隔离级别，平衡并发和一致性
对于大数据量的删除或更新，考虑分批处理

4. 优化JOIN操作

优先使用INNER JOIN，避免使用OUTER JOIN
对于复杂查询，考虑使用临时表或表变量
合理设置JOIN顺序，将返回行数少的表放在前面
避免在JOIN条件中使用函数

5. 使用参数化查询

避免使用动态SQL，防止SQL注入攻击
使用参数化查询，提高查询性能和安全性
对于重复执行的查询，使用存储过程

6. 合理使用约束

使用主键约束，确保数据唯一性
使用外键约束，维护数据完整性
使用唯一约束，避免重复数据
使用CHECK约束，限制数据取值范围
使用DEFAULT约束，设置默认值

常见问题（FAQ）

Q1：SQLServer中的IDENTITY和SEQUENCE有什么区别？

A：IDENTITY是表级别的自动增长列，每个表只能有一个IDENTITY列；SEQUENCE是数据库级别的对象，可以被多个表共享。SQLServer 2012及以上版本支持SEQUENCE。

Q2：如何处理NULL值？

A：处理NULL值的方法包括：

使用IS NULL或IS NOT NULL操作符判断NULL值
使用COALESCE或ISNULL函数替换NULL值
使用NULLIF函数将特定值转换为NULL
合理设置列的默认值，减少NULL值的使用

Q3：如何优化大数据量的查询？

A：优化大数据量查询的方法包括：

使用索引，提高查询性能
只查询需要的列，避免使用SELECT *
使用WHERE子句过滤数据，减少返回的行数
使用分页查询，限制返回的行数
考虑使用分区表，提高查询性能

Q4：如何防止SQL注入攻击？

A：防止SQL注入攻击的方法包括：

使用参数化查询，避免动态SQL
对输入数据进行验证和过滤
限制数据库用户的权限，遵循最小权限原则
使用存储过程，减少直接执行SQL语句

Q5：如何使用事务？

A：使用事务的方法包括：

使用BEGIN TRANSACTION开始事务
使用COMMIT提交事务
使用ROLLBACK回滚事务
使用SAVE TRANSACTION创建保存点
合理设置事务隔离级别

Q6：如何使用索引提高查询性能？

A：使用索引提高查询性能的方法包括：

为频繁用于WHERE子句、JOIN条件、ORDER BY和GROUP BY的列创建索引
选择选择性高的列作为索引键
避免为经常更新的列创建过多索引
定期维护索引，减少索引碎片

Q7：如何处理死锁？

A：处理死锁的方法包括：

合理设置事务隔离级别
避免长时间运行的事务
按相同的顺序访问数据库对象
使用锁提示，如NOLOCK、ROWLOCK等
监控死锁，分析死锁原因

Q8：如何优化INSERT语句的性能？

A：优化INSERT语句性能的方法包括：

使用批量插入，减少网络开销
关闭自动提交，使用显式事务
禁用索引和约束，插入完成后再启用
使用表值参数，传递大量数据
考虑使用BULK INSERT，提高插入速度

总结

SQL是SQLServer数据库开发的基础，掌握SQL基础语法对于数据库开发人员至关重要。本文介绍了SQLServer中的数据类型、基本SQL语句、版本差异、实际生产场景和最佳实践。在实际应用中，需要根据业务需求和数据量选择合适的SQL语句和优化策略，以提高查询性能和系统可靠性。

同时，随着SQLServer版本的不断更新，新的SQL功能和优化特性不断涌现，数据库开发人员需要不断学习和掌握新的知识，以适应业务发展的需求。

SQLServer SQL基础 ​

SQL概述 ​

数据类型 ​

数值型 ​

字符型 ​

日期时间型 ​

二进制型 ​

基本SQL语句 ​

SELECT语句 ​

INSERT语句 ​

UPDATE语句 ​

DELETE语句 ​

CREATE TABLE语句 ​

ALTER TABLE语句 ​

DROP TABLE语句 ​

版本差异 ​

SQLServer 2008及以上版本 ​

SQLServer 2012及以上版本 ​

SQLServer 2016及以上版本 ​

SQLServer 2017及以上版本 ​

SQLServer 2019及以上版本 ​

实际生产场景 ​

场景1：员工信息管理 ​

场景2：订单管理系统 ​

SQL编写最佳实践 ​

1. 选择合适的数据类型 ​

2. 优化SELECT语句 ​

3. 优化INSERT、UPDATE、DELETE语句 ​

4. 优化JOIN操作 ​

5. 使用参数化查询 ​

6. 合理使用约束 ​

常见问题（FAQ） ​

Q1：SQLServer中的IDENTITY和SEQUENCE有什么区别？ ​

Q2：如何处理NULL值？ ​

Q3：如何优化大数据量的查询？ ​

Q4：如何防止SQL注入攻击？ ​

Q5：如何使用事务？ ​

Q6：如何使用索引提高查询性能？ ​

Q7：如何处理死锁？ ​

Q8：如何优化INSERT语句的性能？ ​

总结 ​

SQLServer SQL基础

SQL概述

数据类型

数值型

字符型

日期时间型

二进制型

基本SQL语句

SELECT语句

INSERT语句

UPDATE语句

DELETE语句

CREATE TABLE语句

ALTER TABLE语句

DROP TABLE语句

版本差异

SQLServer 2008及以上版本

SQLServer 2012及以上版本

SQLServer 2016及以上版本

SQLServer 2017及以上版本

SQLServer 2019及以上版本

实际生产场景

场景1：员工信息管理

场景2：订单管理系统

SQL编写最佳实践

1. 选择合适的数据类型

2. 优化SELECT语句

3. 优化INSERT、UPDATE、DELETE语句

4. 优化JOIN操作

5. 使用参数化查询

6. 合理使用约束

常见问题（FAQ）

Q1：SQLServer中的IDENTITY和SEQUENCE有什么区别？

Q2：如何处理NULL值？

Q3：如何优化大数据量的查询？

Q4：如何防止SQL注入攻击？

Q5：如何使用事务？

Q6：如何使用索引提高查询性能？

Q7：如何处理死锁？

Q8：如何优化INSERT语句的性能？

总结