SQL Server数据库命令整理大全

CREATE DATABASE MyDatabase;

DROP DATABASE MyDatabase;

USE MyDatabase;

CREATE TABLE Employees (
    EmployeeID int PRIMARY KEY,
    FirstName nvarchar(50),
    LastName nvarchar(50),
    HireDate datetime
);

INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
VALUES (1, 'John', 'Doe', '二0两3-01-01');

UPDATE Employees
SET FirstName = 'Jane'
WHERE EmployeeID = 1;

DELETE FROM Employees
WHERE EmployeeID = 1;

ALTER TABLE Employees
ADD DepartmentID int FOREIGN KEY REFERENCES Departments(DepartmentID);

SELECT * FROM Employees;

SELECT * FROM Employees WHERE DepartmentID = 两;

SELECT * FROM Employees ORDER BY HireDate DESC;

SELECT COUNT(*) FROM Employees;

SELECT DepartmentID, COUNT(*) AS CountOfEmployees
FROM Employees
GROUP BY DepartmentID;

SELECT E.FirstName, D.DepartmentName 
FROM Employees E
INNER JOIN Departments D ON E.DepartmentID = D.DepartmentID;

CREATE PROCEDURE GetEmployeesByDepartment @deptId INT
AS
BEGIN
    SELECT * FROM Employees WHERE DepartmentID = @deptId;
END

EXEC GetEmployeesByDepartment 二;

CREATE FUNCTION dbo.GetTotalEmployees(@deptId INT) RETURNS INT
AS
BEGIN
    RETURN (SELECT COUNT(*) FROM Employees WHERE DepartmentID = @deptId);
END

SELECT dbo.GetTotalEmployees(两) AS TotalEmpInDept二;

CREATE VIEW EmployeeNames AS
SELECT FirstName, LastName FROM Employees;

SELECT * FROM EmployeeNames;

CREATE INDEX IX_Employees_DepartmentID ON Employees (DepartmentID);

DROP INDEX IX_Employees_DepartmentID ON Employees;

BEGIN TRANSACTION;
-- 执止一系列垄断...
COMMIT TRANSACTION;

BACKUP DATABASE MyDatabase TO DISK = 'C:\backup\MyDatabase.bak';
RESTORE DATABASE MyDatabase FROM DISK = 'C:\backup\MyDatabase.bak';

CREATE LOGIN NewUser WITH PASSWORD = 'StrongPassword!';

CREATE USER UserForDB FOR LOGIN NewUser;
ALTER ROLE db_datareader ADD MEMBER UserForDB; -- 给以读权限
ALTER ROLE db_datawriter ADD MEMBER UserForDB; -- 给以写权限

DROP USER UserForDB;

GRANT SELECT ON Employees TO UserForDB;
DENY UPDATE ON Employees TO UserForDB;
REVOKE DELETE ON Employees FROM UserForDB;

BACKUP DATABASE MyDatabase
TO DISK = 'C:\Backup\MyDatabase.bak'
WITH FORMAT, MEDIANAME = 'MyDatabase_Full', NAME = 'Full Backup';

BACKUP DATABASE MyDatabase
TO DISK = 'C:\Backup\MyDatabase_diff.bak'
WITH DIFFERENTIAL, FORMAT, MEDIANAME = 'MyDatabase_Diff', NAME = 'Differential Backup';

BACKUP LOG MyDatabase
TO DISK = 'C:\Backup\MyDatabase_log.trn'
WITH NOFORMAT, NOINIT, NAME = N'MyDatabase_LogBackup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;

RESTORE DATABASE MyDatabase
FROM DISK = 'C:\Backup\MyDatabase.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5;

SELECT @@VERSION;

sp_help 'Employees';

SELECT GETDATE() AS CurrentDateTime;

SELECT 
    EmployeeID, 
    FirstName, 
    Salary,
    AVG(Salary) OVER (PARTITION BY DepartmentID) AS AvgSalaryInDept
FROM Employees;

WITH EmpSalaries AS (
    SELECT EmployeeID, Salary
    FROM Employees
)
SELECT * FROM EmpSalaries WHERE Salary > (SELECT AVG(Salary) FROM EmpSalaries);

CREATE PARTITION FUNCTION pf_EmployeesRange (int)
AS RANGE RIGHT FOR VALUES 
(二000, 两005, 二010, 两015);

CREATE PARTITION SCHEME ps_Employees 
AS PARTITION pf_EmployeesRange 
TO (
    [PrimaryFileGroup], 
    [SecondaryFileGroup1],
    [SecondaryFileGroup两],
    [SecondaryFileGroup3]
);

CREATE TABLE PartitionedEmployees (
    EmployeeID int PRIMARY KEY,
    HireDate int NOT NULL
) ON ps_Employees(HireDate);

SELECT FirstName, LastName FROM Employees WHERE DepartmentID = 1
UNION ALL
SELECT FirstName, LastName FROM Employees WHERE DepartmentID = 两;

SELECT EmployeeID FROM Employees WHERE DepartmentID = 1
INTERSECT
SELECT EmployeeID FROM Employees WHERE DepartmentID = 两;

SELECT EmployeeID FROM Employees WHERE DepartmentID = 1
EXCEPT
SELECT EmployeeID FROM Employees WHERE DepartmentID = 两;

USE msdb;
GO
EXEC sp_add_job @job_name=N'MyBackupJob', 
                @enabled=1, 
                @description='Daily backup job';
GO

-- 加添功课步调
EXEC sp_add_jobstep @job_name=N'MyBackupJob', 
                   @step_name=N'Backup Database',
                   @subsystem=N'TSQL', 
                   @co妹妹and=N'BACKUP DATABASE MyDatabase TO DISK = ''C:\Backup\MyDatabase.bak'';',
                   @retry_attempts=5, 
                   @retry_interval=5;
GO

-- 封用功课调度
EXEC dbo.sp_add_schedule
    @schedule_name = N'DailyAtMidnight',
    @freq_type = 4, -- 逐日
    @freq_interval = 1, -- 天天运转一次
    @active_start_time = 000000; -- 正在三更入手下手

-- 将功课取调度联系关系
EXEC sp_attach_schedule
    @job_name = N'MyBackupJob', 
    @schedule_name = N'DailyAtMidnight';
GO

-- 正在盘问语句前加添EXPLAIN 或者者 SET SHOWPLAN_ALL ON
SET SHOWPLAN_ALL ON;
SELECT * FROM Employees WHERE DepartmentID = 1;
SET SHOWPLAN_ALL OFF;

-- 或者应用图形化体式格局查望
-- 正在SQL Server Management Studio外，运转盘问后左键选择"包含现实执止设计"
SELECT * FROM Employees WHERE DepartmentID = 1;

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.1两.0', 'Text;Database=C:\Data;HDR=YES;FMT=Delimited', 'SELECT * FROM [Employees.txt]');

-- 建立链接任事器
EXEC sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'OtherDB', @provider=N'SQLNCLI', @datasrc=N'ServerName\InstanceName';

-- 应用链接做事器盘问数据
SELECT * 
FROM MyLinkedServer.RemoteDB.dbo.Employees;

DECLARE @DepartmentID INT = 1;
DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM Employees WHERE DepartmentID = ' + CAST(@DepartmentID AS NVARCHAR(10));
EXEC sp_executesql @SQL;

CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1),
    CustomerID INT,
    OrderDate DATE,
    PRIMARY KEY (OrderID)
);

SELECT CAST('1二34' AS INT), CONVERT(INT, '1二34');

SELECT EmployeeID, FirstName, LastName,
    CASE WHEN Salary > 50000 THEN 'High'
         WHEN Salary > 30000 THEN 'Medium'
         ELSE 'Low'
    END AS SalaryLevel
FROM Employees;

SELECT EmployeeID, FirstName, LastName,
    IIF(Salary > 50000, 'High', IIF(Salary > 30000, 'Medium', 'Low')) AS SalaryLevel
FROM Employees;

CREATE DATABASE MyDatabase_snapshot ON 
    (NAME = MyDatabase, FILENAME = 'C:\Snapshots\MyDatabase_snapshot.ss')
AS SNAPSHOT OF MyDatabase;

RESTORE DATABASE MyDatabase FROM DATABASE_SNAPSHOT = 'MyDatabase_snapshot';