一、SQL Server数据库语法基础SQL语句主要分为三类DDL数据定义语言、DML数据操作语言和DQL数据查询语言。1 DDL数据定义语言DDL用于定义和管理数据库对象数据库、表、字段等的结构。-- 创建数据库 CREATE DATABASE MyDatabase; -- 删除数据库 DROP DATABASE MyDatabase; -- 使用数据库 USE MyDatabase; -- 创建表 CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY IDENTITY(1,1), FirstName NVARCHAR(50) NOT NULL, LastName NVARCHAR(50) NOT NULL, Email NVARCHAR(100) UNIQUE, HireDate DATE DEFAULT GETDATE(), Salary DECIMAL(10,2) CHECK (Salary 0), DepartmentID INT FOREIGN KEY REFERENCES Departments(DepartmentID) ); -- 增加字段 ALTER TABLE Employees ADD PhoneNumber NVARCHAR(20); -- 删除字段 ALTER TABLE Employees DROP COLUMN PhoneNumber; -- 修改字段类型 ALTER TABLE Employees ALTER COLUMN Salary DECIMAL(12,2); -- 删除表 DROP TABLE Employees;2 DML数据操作语言DML用于对表中的数据进行增、删、改操作。-- 插入数据 INSERT INTO Employees (FirstName, LastName, Email, Salary, DepartmentID) VALUES (张, 三, zhangsanexample.com, 5000.00, 1); -- 插入多行数据 INSERT INTO Employees (FirstName, LastName, Email, Salary, DepartmentID) VALUES (李, 四, lisiexample.com, 6000.00, 1), (王, 五, wangwuexample.com, 5500.00, 2); -- 删除数据 DELETE FROM Employees WHERE EmployeeID 1; -- 删除所有数据慎用 DELETE FROM Employees; -- 更新数据 UPDATE Employees SET Salary Salary * 1.1, Email newemailexample.com WHERE DepartmentID 1; -- 更新所有记录 UPDATE Employees SET HireDate GETDATE();3 DQL数据查询语言DQL用于从数据库中查询数据SELECT语句是最常用的查询语句。-- 查询所有字段 SELECT * FROM Employees; -- 查询特定字段 SELECT EmployeeID, FirstName, LastName, Salary FROM Employees; -- 带条件的查询 SELECT * FROM Employees WHERE Salary 5000 AND DepartmentID 1; -- 排序查询 SELECT * FROM Employees ORDER BY Salary DESC, LastName ASC; -- 分组统计 SELECT DepartmentID, COUNT(*) AS EmployeeCount, AVG(Salary) AS AvgSalary, MAX(Salary) AS MaxSalary, MIN(Salary) AS MinSalary FROM Employees GROUP BY DepartmentID HAVING COUNT(*) 5; -- 连接查询 SELECT e.FirstName, e.LastName, d.DepartmentName FROM Employees e INNER JOIN Departments d ON e.DepartmentID d.DepartmentID; -- 子查询 SELECT FirstName, LastName, Salary FROM Employees WHERE Salary (SELECT AVG(Salary) FROM Employees); -- 分页查询SQL Server 2012 SELECT * FROM Employees ORDER BY EmployeeID OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY; -- 常用聚合函数 SELECT COUNT(*) AS TotalEmployees, SUM(Salary) AS TotalSalary, AVG(Salary) AS AverageSalary, MAX(Salary) AS HighestSalary, MIN(Salary) AS LowestSalary FROM Employees;4 常用SQL函数-- 字符串函数 SELECT UPPER(FirstName) AS UpperName, LOWER(LastName) AS LowerName, LEN(FirstName) AS NameLength, CONCAT(FirstName, , LastName) AS FullName, SUBSTRING(Email, 1, CHARINDEX(, Email) - 1) AS Username FROM Employees; -- 日期函数 SELECT GETDATE() AS CurrentDateTime, YEAR(HireDate) AS HireYear, MONTH(HireDate) AS HireMonth, DAY(HireDate) AS HireDay, DATEDIFF(YEAR, HireDate, GETDATE()) AS YearsOfService FROM Employees; -- 数学函数 SELECT ROUND(Salary, 0) AS RoundedSalary, CEILING(Salary) AS CeilingSalary, FLOOR(Salary) AS FloorSalary, ABS(Salary - 5000) AS SalaryDifference FROM Employees;掌握这些基础SQL语法后可以更好地理解C#中执行的SQL语句并编写出更高效、安全的数据库操作代码。二、 数据库与C#连接1.1 基础连接示例using System.Data; using Microsoft.Data.SqlClient; public class DatabaseConnector { public void ConnectToDatabase() { // 连接字符串 string connectionString Serverlocalhost\\SQLEXPRESS;DatabaseTestDB;Trusted_ConnectionTrue;; //server为数据库地址一般为主机127.0.0.1/localhost //Database为要连接的数据库名 //Trusted_ConnectionTrue表示是否使用windows是否验证进行登录 //除此之外还有其他登录方法Serverlocalhost\\SQLEXPRESS;DatabaseTestDB;Password 数据库密码;User Id 登录的用户名一般为sa; // 创建并打开连接使用using语法结束后就会自动关闭数据库连接connection.close(); using (SqlConnection connection new SqlConnection(connectionString)) { try { connection.Open();//开启连接 Console.WriteLine(连接成功); Console.WriteLine($数据库版本: {connection.ServerVersion}); Console.WriteLine($数据库名称: {connection.Database}); string yoursql select * from student;//你的sql语句 using(SqlCommand sqlcom new SqlCommand(yoursql,connection )){ //1.查询语句使用ExecuteReader方法 using (SqlDataReader reader sqlcom .ExecuteReader()) { while(reader.Read()) { //如果reader 有下一个值就为true Console.WriteLine(reader[Name]); } } //2.非查询语句就使用ExecuteNonQuery();相应的本sql语句yoursql应该更改 int rows sqlcom .ExecuteNonQuery();//返回受影响的行 } } catch (SqlException ex) { Console.WriteLine($数据库连接错误: {ex.Message}); } } } }2. 数据查询操作2.1 查询并读取数据public ListProduct GetProducts() { ListProduct products new ListProduct(); string connectionString Serverlocalhost\\SQLEXPRESS;DatabaseNorthwind;Trusted_ConnectionTrue;; using (SqlConnection connection new SqlConnection(connectionString)) { string query SELECT ProductID, ProductName, UnitPrice, UnitsInStock FROM Products; using (SqlCommand command new SqlCommand(query, connection)) { connection.Open(); using (SqlDataReader reader command.ExecuteReader()) { while (reader.Read()) { Product product new Product { ProductID reader.GetInt32(0), ProductName reader.GetString(1), UnitPrice reader.GetDecimal(2), UnitsInStock reader.GetInt16(3) }; products.Add(product); } } } } return products; }2.2 参数化查询防止SQL注入public Product GetProductById(int productId) { Product product null; string connectionString Serverlocalhost\\SQLEXPRESS;DatabaseNorthwind;Trusted_ConnectionTrue;; using (SqlConnection connection new SqlConnection(connectionString)) { string query SELECT ProductID, ProductName, UnitPrice FROM Products WHERE ProductID ProductID; using (SqlCommand command new SqlCommand(query, connection)) { // 添加参数 command.Parameters.AddWithValue(ProductID, productId); connection.Open(); using (SqlDataReader reader command.ExecuteReader()) { if (reader.Read()) { product new Product { ProductID reader.GetInt32(0), ProductName reader.GetString(1), UnitPrice reader.GetDecimal(2) }; } } } } return product; }3. 数据修改操作3.1 插入数据public int InsertProduct(string productName, decimal unitPrice) { int rowsAffected 0; string connectionString Serverlocalhost\\SQLEXPRESS;DatabaseNorthwind;Trusted_ConnectionTrue;; using (SqlConnection connection new SqlConnection(connectionString)) { string query INSERT INTO Products (ProductName, UnitPrice) VALUES (ProductName, UnitPrice); using (SqlCommand command new SqlCommand(query, connection)) { command.Parameters.AddWithValue(ProductName, productName); command.Parameters.AddWithValue(UnitPrice, unitPrice); connection.Open(); rowsAffected command.ExecuteNonQuery(); } } return rowsAffected; }3.2 更新数据public int UpdateProductPrice(int productId, decimal newPrice) { int rowsAffected 0; string connectionString Serverlocalhost\\SQLEXPRESS;DatabaseNorthwind;Trusted_ConnectionTrue;; using (SqlConnection connection new SqlConnection(connectionString)) { string query UPDATE Products SET UnitPrice UnitPrice WHERE ProductID ProductID; using (SqlCommand command new SqlCommand(query, connection)) { command.Parameters.AddWithValue(UnitPrice, newPrice); command.Parameters.AddWithValue(ProductID, productId); connection.Open(); rowsAffected command.ExecuteNonQuery(); } } return rowsAffected; }3.3 删除数据public int DeleteProduct(int productId) { int rowsAffected 0; string connectionString Serverlocalhost\\SQLEXPRESS;DatabaseNorthwind;Trusted_ConnectionTrue;; using (SqlConnection connection new SqlConnection(connectionString)) { string query DELETE FROM Products WHERE ProductID ProductID; using (SqlCommand command new SqlCommand(query, connection)) { command.Parameters.AddWithValue(ProductID, productId); connection.Open(); rowsAffected command.ExecuteNonQuery(); } } return rowsAffected; }以上为数据库的基本操作额外内容还包括事务、异常等感兴趣自行搜索。
