SQL Server语句

好的，以下是根据您的要求编写的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;