学习背景

2018年，我开始系统学习SQL Server数据库知识。今天的学习主题是SQL Server的基础语法与查询，这是数据库操作的基础。

SQL Server基础语法

1. 数据库操作

创建数据库

CREATE DATABASE TestDB;
GO

删除数据库

DROP DATABASE TestDB;
GO

使用数据库

USE TestDB;
GO

2. 表操作

创建表

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Age INT,
    Department NVARCHAR(50),
    Salary DECIMAL(10, 2)
);
GO

修改表

ALTER TABLE Employees
ADD Email NVARCHAR(100);
GO

ALTER TABLE Employees
ALTER COLUMN Salary DECIMAL(12, 2);
GO

删除表

DROP TABLE Employees;
GO

3. 数据操作

插入数据

INSERT INTO Employees (EmployeeID, FirstName, LastName, Age, Department, Salary)
VALUES (1, 'John', 'Doe', 30, 'IT', 50000.00),
       (2, 'Jane', 'Smith', 25, 'HR', 45000.00),
       (3, 'Bob', 'Johnson', 35, 'Finance', 60000.00);
GO

更新数据

UPDATE Employees
SET Salary = Salary * 1.1
WHERE Department = 'IT';
GO

删除数据

DELETE FROM Employees
WHERE Age > 65;
GO

SQL查询

1. 基本查询

选择所有列

SELECT * FROM Employees;
GO

选择特定列

SELECT FirstName, LastName, Salary
FROM Employees;
GO

带条件的查询

SELECT * FROM Employees
WHERE Department = 'IT' AND Salary > 50000;
GO

2. 排序和分组

排序

SELECT * FROM Employees
ORDER BY Salary DESC;
GO

分组

SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department;
GO

分组带条件

SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 50000;
GO

3. 连接查询

内连接

SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;
GO

左连接

SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID;
GO

右连接

SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID;
GO

实践练习

练习1：创建表并插入数据

1. 创建一个Departments表
2. 向Employees表中插入更多数据
3. 编写查询语句获取每个部门的员工数量和平均工资

练习2：复杂查询

1. 编写查询语句获取工资高于平均工资的员工
2. 编写查询语句获取每个部门工资最高的员工
3. 编写查询语句获取入职日期在过去一年内的员工

总结

通过今天的学习，我掌握了SQL Server的基础语法和基本查询操作。这些是SQL Server使用的基础，为后续的学习和应用打下了坚实的基础。在实践过程中，我发现SQL Server的语法与其他关系型数据库（如MySQL）有一些差异，但核心概念是相同的。接下来，我将学习SQL Server的高级特性，如存储过程、触发器、索引等。