好的，以下是根据您的要求编写的SQL Server语句，包括创建数据库、表、视图、索引、约束、存储过程和触发器，以及各种查询语句：
1. 创建数据库：
```
CREATE DATABASE MyDatabase;
```
2. 创建表：
```
CREATE TABLE Students (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Gender VARCHAR(10),
Major VARCHAR(50)
);
CREATE TABLE Courses (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Teacher VARCHAR(50)
);
CREATE TABLE Scores (
ID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
Score FLOAT,
CONSTRAINT FK_Student FOREIGN KEY (StudentID) REFERENCES Students(ID),
CONSTRAINT FK_Course FOREIGN KEY (CourseID) REFERENCES Courses(ID)
);
CREATE TABLE Departments (
ID INT PRIMARY KEY,
Name VARCHAR(50),
ManagerID INT,
CONSTRAINT FK_Manager FOREIGN KEY (ManagerID) REFERENCES Employees(ID)
);
CREATE TABLE Employees (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Gender VARCHAR(10),
DepartmentID INT,
CONSTRAINT FK_Department FOREIGN KEY (DepartmentID) REFERENCES Departments(ID)
);
```
3. 插入数据：
```
INSERT INTO Students (ID, Name, Age, Gender, Major)
VALUES (1, 'Tom', 20, 'Male', 'Computer Science'),
(2, 'Jane', 21, 'Female', 'English'),
(3, 'Mike', 19, 'Male', 'Mathematics'),
(4, 'Lily', 22, 'Female', 'History'),
(5, 'John', 20, 'Male', 'Physics');
INSERT INTO Courses (ID, Name, Teacher)
VALUES (1, 'Database', 'Mr. Smith'),
(2, 'English Composition', 'Ms. Lee'),
(3, 'Calculus', 'Dr. Johnson'),
(4, 'World History', 'Prof. Brown'),
(5, 'Quantum Mechanics', 'Dr. Kim');
INSERT INTO Scores (ID, StudentID, CourseID, Score)
VALUES (1, 1, 1, 95),
(2, 2, 2, 88),
(3, 3, 3, 92),
(4, 4, 4, 85),
(5, 5, 5, 90);
INSERT INTO Departments (ID, Name, ManagerID)
VALUES (1, 'Computer Science', 1),
(2, 'English', 2),
(3, 'Mathematics', 3),
(4, 'History', 4),
(5, 'Physics', 5);
INSERT INTO Employees (ID, Name, Age, Gender, DepartmentID)
VALUES (1, 'Mr. Smith', 40, 'Male', 1),
(2, 'Ms. Lee', 35, 'Female', 2),
(3, 'Dr. Johnson', 45, 'Male', 3),
(4, 'Prof. Brown', 50, 'Male', 4),
(5, 'Dr. Kim', 38, 'Female', 5);
```
4. 模式匹配查询：
```
SELECT * FROM Students WHERE Name LIKE '%Tom%';
```
5. TOP查询：
```
SELECT TOP 3 * FROM Courses ORDER BY Name DESC;
```
6. IN查询：
```
SELECT * FROM Students WHERE Major IN ('Computer Science', 'Mathematics');
```
7. 降序查询：
```
SELECT * FROM Scores ORDER BY Score DESC;
```
8. COUNT集合函数查询：
```
SELECT COUNT(*) FROM Students;
```
9. 分组统计查询：
```
SELECT Major, AVG(Age) AS AvgAge, COUNT(*) AS Total FROM Students GROUP BY Major;
```
10. 连接条件的多表查询：
```
SELECT Students.Name, Courses.Name AS CourseName, Scores.Score
FROM Students
INNER JOIN Scores ON Students.ID = Scores.StudentID
INNER JOIN Courses ON Scores.CourseID = Courses.ID;
```
11. 比较运算符的子查询：
```
SELECT * FROM Students WHERE Age > (SELECT AVG(Age) FROM Students);
```
12. IN的子查询：
```
SELECT * FROM Courses WHERE ID IN (SELECT CourseID FROM Scores WHERE Score > 90);
```
13. 创建视图：
```
CREATE VIEW StudentInfo AS
SELECT Students.Name, Courses.Name AS CourseName, Scores.Score
FROM Students
INNER JOIN Scores ON Students.ID = Scores.StudentID
INNER JOIN Courses ON Scores.CourseID = Courses.ID;
```
14. 创建索引：
```
CREATE INDEX idx_StudentName ON Students (Name);
```
15. 创建唯一性约束、检查约束、默认值约束、外键约束：
```
ALTER TABLE Employees ADD CONSTRAINT UC_EmployeeName UNIQUE (Name);
ALTER TABLE Departments ADD CONSTRAINT CK_DepartmentName CHECK (LEN(Name) > 0);
ALTER TABLE Courses ALTER COLUMN Teacher SET DEFAULT 'Unknown';
ALTER TABLE Scores ADD CONSTRAINT FK_Student FOREIGN KEY (StudentID) REFERENCES Students(ID) ON DELETE CASCADE;
```
16. 创建存储过程：
```
CREATE PROCEDURE sp_GetStudentInfo @StudentName VARCHAR(50)
AS
BEGIN
SELECT Students.Name, Courses.Name AS CourseName, Scores.Score
FROM Students
INNER JOIN Scores ON Students.ID = Scores.StudentID
INNER JOIN Courses ON Scores.CourseID = Courses.ID
WHERE Students.Name = @StudentName;
END;
```
17. 创建插入、更新的DML触发器：
```
CREATE TRIGGER tr_StudentInsert ON Students
AFTER INSERT
AS
BEGIN
INSERT INTO AuditTrail (TableName, Action, UserName, DateTime)
VALUES ('Students', 'Insert', SUSER_SNAME(), GETDATE());
END;
CREATE TRIGGER tr_StudentUpdate ON Students
AFTER UPDATE
AS
BEGIN
INSERT INTO AuditTrail (TableName, Action, UserName, DateTime)
VALUES ('Students', 'Update', SUSER_SNAME(), GETDATE());
END;
```
在尝试批量更新SQL Server数据库字段时遇到错误，错误信息显示数据类型varchar和text不兼容。解决方法是将text类型转换为varchar(8000)，例如使用`UPDATE表名SET字段=CAST(字段AS VARCHAR(8000))+‘自己的字符串’`。务必注意转换过程中的长度限制，防止数据截断。
2164
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
