武威市网站建设_网站建设公司_测试工程师_seo优化

俗话说：地基不牢，地动山摇。SQL常见知识点还是很重要的，掌握了，操作数据库是错错有余。

1. 数据库基本操作

创建数据库

-- 创建数据库 CREATE DATABASE EmployeeDB; GO -- 使用数据库 USE EmployeeDB; GO

创建表

-- 创建员工表 CREATETABLE Employees ( EmployeeID INTIDENTITY(1,1) PRIMARY KEY, FirstName NVARCHAR(50) NOTNULL, LastName NVARCHAR(50) NOTNULL, Email NVARCHAR(100) UNIQUE, HireDate DATEDEFAULTGETDATE(), Salary DECIMAL(10,2) CHECK (Salary > 0), DepartmentID INT ); -- 创建部门表 CREATETABLE Departments ( DepartmentID INTIDENTITY(1,1) PRIMARY KEY, DepartmentName NVARCHAR(100) NOTNULL, ManagerID INT );

2. 数据作语言（DML）

插入数据

-- 插入部门数据 INSERTINTO Departments (DepartmentName) VALUES ('技术部'), ('销售部'), ('人力资源部'); -- 插入员工数据 INSERTINTO Employees (FirstName, LastName, Email, Salary, DepartmentID) VALUES ('张三', '张', 'zhangsan@company.com', 8000.00, 1), ('李四', '李', 'lisi@company.com', 7500.00, 1), ('王五', '王', 'wangwu@company.com', 9000.00, 2);

更新数据

-- 更新员工薪资 UPDATE Employees SET Salary = Salary * 1.1 WHERE DepartmentID = 1; -- 使用JOIN更新 UPDATE e SET e.Salary = e.Salary * 1.05 FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID WHERE d.DepartmentName = '技术部';

删除数据

-- 删除特定记录 DELETE FROM Employees WHERE EmployeeID = 3; -- 使用事务删除 BEGIN TRANSACTION; DELETE FROM Employees WHERE HireDate < '2020-01-01'; COMMIT TRANSACTION;

3. 数据查询

基础查询

-- 选择所有列 SELECT * FROM Employees; -- 选择特定列 SELECT FirstName, LastName, Salary FROM Employees; -- 使用别名 SELECT FirstName AS'名字', LastName AS'姓氏', Salary AS'薪资' FROM Employees;

WHERE条件查询

-- 基本条件查询 SELECT * FROM Employees WHERE Salary > 8000; -- 多条件查询 SELECT * FROM Employees WHERE Salary BETWEEN7000AND9000 AND DepartmentID = 1; -- LIKE模糊查询 SELECT * FROM Employees WHERE LastName LIKE'张%'; -- IN查询 SELECT * FROM Employees WHERE DepartmentID IN (1, 2);

聚合函数

-- 常用聚合函数 SELECT COUNT(*) AS '员工总数', AVG(Salary) AS '平均薪资', MAX(Salary) AS '最高薪资', MIN(Salary) AS '最低薪资', SUM(Salary) AS '薪资总额' FROM Employees;

4. 表连接查询

INNER JOIN

-- 内连接查询员工和部门信息 SELECT e.FirstName, e.LastName, e.Salary, d.DepartmentName FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

LEFT/RIGHT JOIN

-- 左外连接：显示所有员工，即使没有部门 SELECT e.FirstName, e.LastName, d.DepartmentName FROM Employees e LEFTJOIN Departments d ON e.DepartmentID = d.DepartmentID; -- 右外连接：显示所有部门，即使没有员工 SELECT e.FirstName, e.LastName, d.DepartmentName FROM Employees e RIGHTJOIN Departments d ON e.DepartmentID = d.DepartmentID;

5. 高级查询技巧

分组查询

-- 按部门分组统计 SELECT d.DepartmentName, COUNT(e.EmployeeID) AS '员工数量', AVG(e.Salary) AS '平均薪资' FROM Departments d LEFT JOIN Employees e ON d.DepartmentID = e.DepartmentID GROUP BY d.DepartmentID, d.DepartmentName HAVING COUNT(e.EmployeeID) > 0;

子查询

-- 使用子查询查找薪资高于平均值的员工 SELECT FirstName, LastName, Salary FROM Employees WHERE Salary > (SELECTAVG(Salary) FROM Employees); -- 使用IN的子查询 SELECT FirstName, LastName FROM Employees WHERE DepartmentID IN ( SELECT DepartmentID FROM Departments WHERE DepartmentName LIKE'%技术%' );

6. 视图的创建与使用

创建视图

-- 创建员工信息视图 CREATE VIEW vw_EmployeeDetails AS SELECT e.EmployeeID, e.FirstName + ' ' + e.LastName AS FullName, e.Email, e.Salary, d.DepartmentName, e.HireDate FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

使用视图

-- 查询视图 SELECT * FROM vw_EmployeeDetails WHERE Salary > 8000; -- 更新视图（有限制条件） UPDATE vw_EmployeeDetails SET Salary = 8500 WHERE EmployeeID = 1;

7. 存储过程

创建存储过程

-- 创建获取员工信息的存储过程 CREATEPROCEDURE sp_GetEmployeesByDepartment @DepartmentID INT = NULL, @MinSalary DECIMAL(10,2) = 0 AS BEGIN SELECT FirstName, LastName, Salary, HireDate FROM Employees WHERE (@DepartmentID ISNULLOR DepartmentID = @DepartmentID) AND Salary >= @MinSalary ORDERBY Salary DESC; END;

执行存储过程

-- 执行存储过程 EXEC sp_GetEmployeesByDepartment @DepartmentID = 1, @MinSalary = 7000; -- 使用默认参数 EXEC sp_GetEmployeesByDepartment;

8. 索引优化

创建索引

-- 创建单列索引 CREATEINDEX IX_Employees_DepartmentID ON Employees(DepartmentID); -- 创建复合索引 CREATEINDEX IX_Employees_Name ON Employees(LastName, FirstName); -- 创建唯一索引 CREATEUNIQUEINDEX IX_Employees_Email ON Employees(Email); -- 查看索引信息 SELECT nameAS IndexName, type_desc ASIndexType FROM sys.indexes WHERE object_id = OBJECT_ID('Employees');

9. 事务处理

事务基本使用

-- 简单事务示例 BEGINTRANSACTION; BEGIN TRY -- 更新员工薪资 UPDATE Employees SET Salary = Salary * 1.1 WHERE DepartmentID = 1; -- 记录日志（假设有Log表） INSERTINTO SalaryLog (EmployeeID, OldSalary, NewSalary, ChangeDate) SELECT EmployeeID, Salary / 1.1, Salary, GETDATE() FROM Employees WHERE DepartmentID = 1; COMMITTRANSACTION; PRINT '事务执行成功'; END TRY BEGIN CATCH ROLLBACKTRANSACTION; PRINT '事务执行失败: ' + ERROR_MESSAGE(); END CATCH;

10. 常用系统函数

字符串函数

-- 字符串处理示例 SELECT FirstName, LastName, UPPER(FirstName) AS UpperFirstName, LOWER(LastName) AS LowerLastName, LEN(FirstName) AS NameLength, LEFT(FirstName, 1) AS FirstInitial, REVERSE(FirstName) AS ReversedName FROM Employees;

日期函数

-- 日期处理示例 SELECT FirstName, HireDate, GETDATE() AS CurrentDate, DATEDIFF(YEAR, HireDate, GETDATE()) AS YearsOfWork, DATEADD(MONTH, 6, HireDate) AS ProbationEndDate, DATENAME(MONTH, HireDate) AS HireMonth FROM Employees;

11. 窗口函数

排名函数

-- 使用窗口函数进行排名 SELECT FirstName, LastName, Salary, DepartmentID, ROW_NUMBER() OVER (PARTITIONBY DepartmentID ORDERBY Salary DESC) AS DeptSalaryRank, RANK() OVER (ORDERBY Salary DESC) AS OverallSalaryRank, DENSE_RANK() OVER (ORDERBY Salary DESC) AS DenseSalaryRank, NTILE(4) OVER (ORDERBY Salary DESC) AS SalaryQuartile FROM Employees;