SQL 教程

偏移 - 从 x 行到 y 行

ORDER BY Continent ASC, Subregion DESC
偏移 29 行,只获取后 50 行;

并列最后一名

选择排名前 10%的并列者;

COALESCE - 1. 非空值

选择 COALESCE(NULL,NULL,2,5,NULL)
COALESCE(PickedByPersonID,BackorderOrderID);

WHERE - 字符替换

通配符搜索 (%) - SQL Server | Microsoft Learn

WHERE StockItemName LIKE '%ham[^m]%'.
WHERE StockItemName LIKE 'a[l-m]%[l-p]'.
WHERE CountryName LIKE 'H__[g,d]%

WHERE IN - 是否包含在列表中

WHERE PickedByPersonID IN (3,4,17) AND OrderDate >= '2013-01-01' AND OrderDate <= '2013-12-30'.

聚合查询 - avg()、sum()、min()、max()、count()、count_big()

SUM(Quantity) AS total_pack,AVG(UnitPrice) AS avg_unitprice,COUNT(OrderLineID) AS cnt_orderline

AGGREGATORS - 不处理 NULL

SELECT COUNT(ISNULL(PickingCompletedWhen,'1970-01-01'))
SELECT COUNT_BIG(*) FROM Sales.OrderLines;
COUNT(CASE WHEN PickingCompletedWhen IS NULL THEN 1 ELSE NULL END) AS unkown_values

子查询 - 独立/简单

SELECT OrderId, AVG(Quantity) as avg_perorderid
FROM Sales.OrderLines
GROUP BY OrderId
使 AVG(数量)大于(SELECT AVG(数量)AS avg_q
FROM Sales.OrderLines)
ORDER BY avg_perorderid ASC;

SELECT OrderId, (SELECT AVG(Quantity) AS avg_q FROM Sales.OrderLines)
FROM Sales.OrderLines;

相关子查询 - 最后一个订单号 

SELECT CustomerId, OrderId, OrderDate
FROM Sales.Orders o1
WHERE OrderID=(SELECT MAX(OrderID)
FROM Sales.Orders o2
WHERE o1.CustomerId=o2.CustomerId)
通过 orderDate DESC 排序;

返回列表

SELECT OrderID, CustomerId, OrderDate FROM Sales.Orders
WHERE CustomerID IN (SELECT CustomerID FROM Sales.Customers WHERE CreditLimit IS NULL OR CreditLimit < 2000)
ORDER BY CustomerID ASC;

存在

SELECT DISTINCT CustomerID, CustomerName
FROM Sales.Customers c
WHERE PostalCityID = 33832
并且存在
(SELECT * FROM Sales.Orders o
WHERE o.CustomerID=c.CustomerID);

运行总计 - 戈尔杜洛-莱克德泽斯

SELECT CityID, CityName, (SELECT MIN(CityID) FROM Application.Cities c2 WHERE c2.CityID > c1.CityID) AS nextcityid
FROM Application.Cities c1
ORDER BY CityID ASC;
--贾维塔尼
SELECT CityID, CityName, LatestRecordedPopulation, LatestRecordedPopulation + (SELECT SUM(LatestRecordedPopulation) FROM Application.Cities c2 WHERE c1.CityID=c2.CityID) AS run_total
FROM 应用程序.城市 c1
WHERE StateProvinceID IN (SELECT StateProvinceID FROM Application.StateProvinces WHERE StateProvinceName='Colorado')
GROUP BY CityID, CityName, LatestRecordedPopulation;

衍生表格版本

SELECT order_count,month_number
FROM (
SELECT MONTH(OrderDate) AS month_number, COUNT(OrderDate) AS order_count
FROM Sales.Orders
GROUP BY MONTH(OrderDate)
)作为衍生表
WHERE order_count > 6000
ORDER BY month_number ASC;

SELECT order_count, month_number
FROM (
SELECT MONTH(OrderDate), COUNT(OrderDate)
FROM Sales.Orders
GROUP BY MONTH(OrderDate)
) AS derivedtable (month_number,order_count)
WHERE order_count > 6000
ORDER BY month_number ASC;

SELECT CustomerID, ORderID, OrderDate
FROM Sales.Orders AS a -- a=schema_name.table_name
INNER JOIN
(
SELECT MAX(OrderDate) AS maxorderdate
FROM Sales.Orders AS b
) AS derivedtable ON a.orderDate=derivedtable.maxorderdate;

CTE

WITH cte_table AS (
SELECT MONTH(OrderDate) AS month_number, COUNT(OrderDate) AS order_count
FROM Sales.Orders
GROUP BY MONTH(OrderDate)
)
SELECT order_count, month_number FROM cte_table
WHERE order_count > 6000
ORDER BY month_number ASC;

案例 - 正常 - 导出表 - CTE 

SELECT CASE
WHEN 2013=YEAR(OrderDate) THEN '2013y' (订购日期)
WHEN 2014=FORMAT(OrderDate,'yyyy') THEN '2014y'
WHEN OrderDate like '2015%' THEN '2015y
WHEN OrderDate >= '20160101' AND OrderDate = '20160101' AND OrderDate = '20160101' AND OrderDate = '20160101' AND OrderDate < '20170101' THEN '2016y'
END 作为年份
FROM Sales.Orders)
SELECT years, COUNT(years)
FROM whatever
GROUP BY years;

JOIN AND(连接和)--这将是 "where in "和 "and "的关系

SELECT *
FROM dbo.Varosok v JOIN dbo.Orszagok o ON v.OrszagID = o.Id AND o.Nev='USA'

老板 Boss JOIN

SELECT subordinate.Nev, braids.Nev, braids.Nev FROM dbo.people as Subordinate
INNER JOIN dbo.People as fonok ON subordinate.FonokId = fonok.Id
LEFT JOIN dbo.People as majorfonok ON fonok.Fonok.FonokId = majorfonok.Id

数据长度

SELECT emp_name、
LEN(emp_name) LEN
DATALENGTH(emp_name) data_length
FROM dbo.employees;

uniqueidentifier

创建表 #testunique(
id UNIQUEIDENTIFIER DEFAULT NEWID()
, name varchar(100)
);

自动创建 id

DROP TABLE IF EXISTS HR.Employees
CREATE TABLE HR.Employees (
id int IDENTITY(1,1)
, emp_name NVARCHAR(200)
)

自己的类型

CREATE TYPE hunschema.hun_mySSN FROM varchar(9);
创建表 hunschema.testtable (
id INT NOT NULL
,socnumberHUN hunschema.hun_mySSN
);
INSERT INTO hunschema.testtable
值(1,'123456789');

避免自动递增和 INSERT 问题

SET IDENTITY_INSERT Hr.Employees ON;

INSERT INTO HR.Employees (id,emp_name)
VALUES(666,'firstname');

SET IDENTITY_INSERT Hr.Employees OFF;

输出插入、删除

INSERT INTO HR.Employees (emp_name)
OUTPUT inserted.emp_name INTO HR.Emplog(emp_name)
VALUES ('testname')

DELETE Hr.Employees
OUTPUT deleted.emp_name INTO HR.Emplog(emp_name)
WHERE id=666;

DELETE testlines where orderdate='20130101′ > LOG 

DELETE dbo.testlines
OUTPUT deleted.*
FROM dbo.testlines tol
INNER JOIN dbo.testorders tor ON tor.OrderID=tol.OrderID
WHERE OrderDate='20130101

主键、外键、唯一键、约束

主键

/* v1 */
ALTER 表 dbo.employees
ALTER COLUMN emp_id int NOT NULL;

ALTER 表 dbo.employees
ADD CONSTRAINT PK_employee_empid PRIMARY KEY (emp_id);
/* v2 */
ALTER 表 dbo.employees
ADD emp_id int IDENTITY(1,1) NOT NULL
CONSTRAINT PK_employees_emp PRIMARY KEY;
/* v3 */
执行
创建表 dbo.testtable4(
id INT NOT NULL PRIMARY KEY
);
/* v4 */
DROP TABLE IF EXISTS dbo.testtable4;
CREATE TABLE dbo.testtable4(
id INT NOT NULL IDENTITY(1,1
, name varchar(50)
CONSTRAINT PK_testtable4_id PRIMARY KEY(id)
);

外键 - 1 > n - 主键

ALTER 表 dbo.employees
ADD CONSTRAINT FK_employees_depid FOREIGN KEY(dep_id) REFERENCES dbo.departments(dep_id);

级联版本

ALTER 表 dbo.employee
ADD CONSTRAINT FK_employees_depid FOREIGN KEY(dep_id) REFERENCES dbo.departments(dep_id) ON UPDATE CASCADE; -- ON DELETE CASCADE

转换表 - 雇员和学历 - N > M

DROP TABLE IF EXISTS dbo.conn_educations_employees;
创建表 dbo.conn_educations_employees (
id INT NOT NULL IDENTITY(1,1)
CONSTRAINT PK_conn_eduemp_id PRIMARY KEY
,edu_id INT NOT NULL
CONSTRAINT FK_conn_edu_eduid FOREIGN KEY (edu_id) REFERENCES dbo.educations(edu_id) ,emp_id INT NOT NULL
,emp_id INT NOT NULL
CONSTRAINT FK_conn_edu_empid FOREIGN KEY (edu_id) REFERENCES dbo.employees (emp_id)
);

1 > 1

DROP TABLE IF EXISTS dbo.itusers;
创建表 dbo.itusers(
id int NOT NULL IDENTITY(1,1)
CONSTRAINT PK_itusers_id PRIMARY KEY
CONSTRAINT PK_itusers_id PRIMARY KEY , username VARCHAR(50) NOT NULL
CONSTRAINT UK_itsuser_username UNIQUE
, emp_id INT
CONSTRAINT FK_itusers_empid FOREIGN KEY(emp_id) REFERENCES dbo.employees(emp_id)
CONSTRAINT UK_itusers_empid UNIQUE (emp_id)
);

唯一关键字

ALTER 表 dbo.employees
ADD CONSTRAINT UK_employees_person UNIQUE (emp_name,birth_date,mother_name);

检查约束

ALTER 表 dbo.employees
ADD CONSTRAINT CK_employees_birthdate CHECK ( birth_date > '19600101');

检查约束是否有效

ALTER 表 dbo.employees WITH CHECK
CHECK CONSTRAINT CK_employees_contacts;

默认常数

ALTER 表 dbo.employees
ADD CONSTRAINT DK_phonenumber DEFAULT '0690111111' FOR phone_number;

时序表 sql2017+ - 高级 sql server 2019 cu18

创建表 dbo.persondate (
id int IDENTITY(1,1) PRIMARY KEY NOT NULL
,last_name NVARCHAR(50) NOT NULL
名 NVARCHAR(50) NOT NULL
电话号码 VARCHAR(22)
电子邮件地址 VARCHAR(50)
,sysfirstdate DATETIME2 生成 ALWAYS AS ROW START NOT NULL
,系统日期 DATETIME2 生成总是作为行结束而非空
,PERIOD FOR SYSTEM_TIME(sysfirstdate,sysenddate)
) WITH (
system_versioning = on (history_retention_period = 3 months
,HISTORY_TABLE = dbo.persondate_history)
)
/* 删除临时表*/
SELECT * FROM dbo.persondate_history temp
LEFT JOIN dbo.persondate actual ON temp.id=actual.id
WHERE actual.id IS NULL;

索引

索引类型:
- 内存索引:OLTP
- 列存储索引
- 行存储:聚类 1 , 非聚类

行存储索引类型:
- 聚类索引 - 电话簿,全表
- 非聚类:堆 - 如果表上没有聚类索引,则堆
索引,例如第 120、130 页中哪一页包含 "法治 "一词

索引节点:索引键,地址
叶片级别:桌子
根节点 > 中间节点 > 叶节点

B 树

叶级别:索引键、聚类索引

适合在索引关键字中搜索,但你无法摆脱聚类的束缚

聚类索引

DROP INDEX idx_testlines_clustered ON dbo.testlines;
CREATE CLUSTERED INDEX idx_testlines_clustered ON dbo.testlines(OrderLineID DESC);

非聚类索引适合经常使用的查询,如 where

CREATE CLUSTERED INDEX idx_testlines_clustered ON dbo.testlines(OrderLineID DESC);

CREATE NONCLUSTERED INDEX idx_testlines_stockitemid ON dbo.testlines(StockItemID);

强制使用索引 - 不要使用

费用: 2.24

SELECT OrderLineID, StockItemID, UnitPrice, Quantity
FROM dbo.testlines WITH (INDEX (idx_testlines_stockitemid))
WHERE StockItemID=148;

叶水平包含在 UnitePrice 中,数量

DROP INDEX idx_testlines_stockitemid ON dbo.testlines;
CREATE NONCLUSTERED INDEX idx_testlines_stockitemid ON dbo.testlines(StockItemID) INCLUDE (UnitPrice,Quantity);

筛选索引

CREATE UNIQUE INDEX idx_testorders_isinprogress ON dbo.testorders (CustomerID,isInProgress) WHERE isInProgress=1;
/*FOREIGN KEY > 无索引!最佳做法--非聚类索引过滤 testdb isHIgh education table-ben*/
/*maybe masterwork - no good for date*/
CREATE INDEX idx_testorders_2016 ON dbo.testorders (OrderDate) INCLUDE(CustomerID) WHERE OrderDate >= '20160101' AND OrderDate<'20170101';

变量

批处理和变量 (GO)
- 在两个 GO 之间,你所拥有的是一个批次
- 变量只活在一个批次中
DECLARE @stockItemId INT;
SET @stockItemId = 2;

DECLARE @maxValidTo DATETIME2 = '2020-02-02';

作为过滤条件,即使在多个查询中也是如此

SELECT * FROM Warehouse.StockItems
WHERE StockItemID = @stockItemId;

DECLARE @myStockItemName NVARCHAR(500);

SET @myStockItemName = (SELECT StockItemName FROM Warehouse.StockItems)
WHERE StockItemID = @stockItemId);

SELECT @myStockItemName AS MyStockItemName;
/* 只能存储一个值 */
/* 同上 */
DECLARE @stockItemId INT;
SET @stockItemId = 2;
DECLARE @myStockItemName NVARCHAR(500);
SELECT @myStockItemName=StockItemName FROM Warehouse.StockItems
WHERE StockItemID = @stockItemId;

如果在选择

DECLARE @myStockItemName NVARCHAR(500);
SELECT @myStockItemName=StockItemName FROM Warehouse.StockItems;

DECLARE @myStockItemName NVARCHAR(500);
DECLARE @myStockItemColorId NVARCHAR(500);

SELECT @myStockItemName=StockItemName, @myStockItemColorId=ColorID FROM Warehouse.StockItems;

SELECT @myStockItemName=StockItemName, @myStockItemColorId=ColorID
FROM Warehouse.StockItems
ORDER BY StockItemName;

SELECT @myStockItemName AS MyStockItemName;
SELECT @myStockItemColorId AS MyStockColorId;

如果没有查询结果,变量的值为 NULL

SELECT StockItemName FR Warehouse.StockItems
WHERE StockItemID = 10000000;

DECLARE @myStockItemName NVARCHAR(500);
SET @myStockItemName = (SELECT StockItemName FROM Warehouse.StockItems
WHERE StockItemID = 10000000);
SELECT @myStockItemName AS MyStockItemName;

DECLARE @myStockItemName NVARCHAR(500);
SELECT @myStockItemName=StockItemName FROM Warehouse.StockItems
WHERE StockItemID = 10000000;
SELECT @myStockItemName AS MyStockItemName;

数据

https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver16

数值与浮点

浮点近似取整,对于大数,整数部分可能并不精确,只有前 7 位精确,x*2^y => 199*2*5 - 物理、统计计算中的大数值,例如十进制是不够的
数字/十进制,你得到什么,对于财务数据,如价格是 1.99。

货币 - 实际上是十进制,只有四个小数部分,是固定的 - 你可以写货币,但它不存储它

DECLARE @mymoney_sm SMALLMONEY = 3148.29、
        @mymoney MONEY = 3148.29;
SELECT CAST(@mymoney_sm AS VARCHAR) AS 'SM_MONEY varchar'、
        CAST(@mymoney AS DECIMAL) AS 'MONEY DECIMAL';

日期时间与日期时间2

DECLARE @ido_datetime DATETIME = GETDATE();
DECLARE @ido_datetime2 DATETIME2 = GETDATE();

SELECT @ido_datetime, @ido_datetime2;

日期时间偏移

DECLARE @ido_datetimeoffset datetimeoffset = GETDATE();
DECLARE @ido_datetimeoffset2 datetimeoffset = SYSDATETIMEOFFSET()
SELECT @ido_datetime, @ido_datetimeoffset,@ido_datetimeoffset2;

DECLARE @mostaniIdoUTC datetime2=GETUTCDATE();
SELECT @ido_datetime, @ido_datetimeoffset,@ido_datetimeoffset2,@mostaniIdoUTC;

char 与 varchar

DECLARE @mychar CHAR(3)='test
SELECT @mychar;

DECLARE @mychar CHAR(3)='te'
SELECT @mychar;

DECLARE @mychar CHAR(3)='te'
SELECT LEN(@mychar);
SELECT DATALENGTH(@mychar);

DECLARE @myvarchar VARCHAR(3)='test
SELECT @myvarchar;

DECLARE @myvarchar VARCHAR(3)='te
SELECT @myvarchar;

DECLARE @myvarchar VARCHAR(3)='te'
SELECT LEN(@myvarchar);
SELECT DATALENGTH(@myvarchar);

返回

nchar 与 nvarchar

DECLARE @mychar NCHAR(3)='test
SELECT @mychar;

DECLARE @mychar NCHAR(3)='te'
SELECT @mychar;

DECLARE @mychar NCHAR(3)='te'
SELECT LEN(@mychar);
SELECT DATALENGTH(@mychar);

DECLARE @myvarchar NVARCHAR(3)='test
SELECT @myvarchar;

DECLARE @myvarchar NVARCHAR(3)='te
SELECT @myvarchar;

DECLARE @myvarchar NVARCHAR(3)='te'
SELECT LEN(@myvarchar);
SELECT DATALENGTH(@myvarchar);

二进制

DECLARE @myBinary BINARY(10) = 0x00A1B2C3;
SELECT @myBinary;

用 ascii 编码输入 unicode 字符

DECLARE @mychar CHAR(3) = 'Győr';
DECLARE @mychar2 CHAR(3) = 'Győr';
SELECT @mychar, @mychar2, LEN(@mychar), DATALENGTH(@mychar),LEN(@mychar2), DATALENGTH(@mychar2);

数值过高

DECLARE @chunk SMALLINT = 32768;
SELECT @darabszam;

 隐式转换

https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-ver16

CAST 与 CONVERT

DECLARE @chunk SMALLINT = 32764;
SELECT @darabszam;
SELECT CAST(@darabszam AS nvarchar)+'?';

DECLARE @ma DATETIME2 =GETUTCDATE();

SELECT @ma,CONVERT(nvarchar,@ma,1)

TRY_CAST vs TRY_CONVERT

DECLARE @chunk SMALLINT = 32764;
SELECT @darabszam;
SELECT TRY_CAST(CAST(@darabszam AS nvarchar)+'?' AS INTAS INT);

格式

DECLARE @ma DATETIME2 =GETUTCDATE();

SELECT @ma,FORMAT(@ma,'yyyy. MM. dd.')

DECLARE @datum DATETIME2 = '2022-02-15', @datum2 DATETIME2;
SET @datum2 =CONVERT(DATETIME2, '2/15/2022',101)
SELECT @datum,@datum2

DECLARE @float FLOAT = 2.0 / 5;
SELECT @float,FORMAT(@float,'N2')

数字操作:修剪、LTIM、RTIM

在这里,len 并没有看到文本的结尾:

SELECT TRIM(' a '),LEN(' a '), LEN(TRIM(' a '));
SELECT LTRIM(' a '),LEN(' a '), LEN(LTRIM(' a '));
SELECT RTRIM(' a '),LEN(' a '), LEN(RTRIM(' a '));

SELECT TRIM(' a '),LEN(' a '), LEN(TRIM(' a '));
SELECT LTRIM(' a '),DATALENGTH(' a '), DATALENGTH(LTRIM(' a '));
SELECT RTRIM(' a '),LEN(' a '), LEN(RTRIM(' a '));

上,下

SELECT UPPER('Accent')
select lower('sentence')

复制、左转、逆转、替换

SELECT REPLICATE('ho',3)
select left('hello world', 5)

SELECT REVERSE('hello')

SELECT REPLACE('HELLO world', 'World', 'JOE')

更新仓库.库存项目
SET StockItemName = REPLACE(StockItemName,'StockItemName', 'launcher')

日期时间操作: DATEDIFF

SELECT ValidFrom, '2022-01-01 00:00', DATEDIFF(yyyy, ValidFrom,'2022-01-01 00:00')
FROM Warehouse.StockItems

SELECT ValidFrom, '2022-01-01 00:00', DATEDIFF(s, ValidFrom,'2022-01-01 00:00')
FROM Warehouse.StockItems

SELECT ValidFrom, '2022-01-01 00:00', DATEDIFF(s, ValidFrom,'2022-01-01 00:00')
FROM Warehouse.StockItems

SELECT DATEDIFF(s,'2022-01-01 00:00','2022-01-01 05:01')

四舍五入

选择 round(123.45,0)
选择 round(123.55,0)
选择舍入(123.55,1)

临时表,表变量

临时表

只存在于会话中,存储在 tempdb 中,对许多人来说 tempdb 太大,用于存储表的查询,优点:过滤数据,查询速度更快,代码更少,性能更高

创建表 #myTempTable
(
	Id uniqueidentifier 主键,非聚类、
	name NVARCHAR(100) NOT NULL
);

全局临时数据库:在会话之间持续存在,在所有查询和会话结束后终止

创建表 ##globalTempTable
(
	Id uniqueidentifier 主键(非聚类)、
	name NVARCHAR(100) NOT NULL
);
DROP TABLE ##globalTempTable;

表变量:只存在于批处理中,对于大量数据来说并不理想

DECLARE @myTableVariable TABLE (
	Id uniqueidentifier 主键,非聚类、
	name NVARCHAR(100) NOT NULL
);
-- 制作普通黑板
USE WideWorldImporters;
GO

SELECT COUNT(*) FROM Sales.Orders;
-- 0.39 成本
SELECT COUNT(*) FROM Sales.OrderLines;

SELECT TOP 1 * FROM Sales.OrderLines;

创建表 #Temp_OrderLines
(
	OrderLineID int PRIMARY KEY、
	StockItemID int NOT NULL, --/*FOREIGN KEY*/ 引用 Warehouse.StockItems(StockItemID),--不要这样做,因为它没有名称,你必须找到名称
	description nvarchar(1000)、
	数量 int NOT NULL
);
-- 164 库存项目 ID
INSERT INTO #Temp_OrderLines(OrderLineID,StockItemID,[DESCRIPTION],Quantity)
SELECT OrderLineID,StockItemID,[Description],Quantity
FROM Sales.OrderLines
WHERE StockItemID=164;

SELECT * FROM #Temp_OrderLines;
--0.013成本
SELECT COUNT(*) FROM #Temp_OrderLines;

INSERT INTO #Temp_OrderLines(OrderLineID,StockItemID,[DESCRIPTION],Quantity)
VALUES (10000,1,'Test', 1);

SELECT COUNT(*) FROM #Temp_OrderLines;

DECLARE @myTableVariable TABLE (
	Id uniqueidentifier 主键,非聚类、
	Name NVARCHAR(100) NOT NULL
);

INSERT INTO @myTableVariable (Id,Name) VALUES (NEWID(), 'Teszt');

SELECT * FROM @myTableVariable;

USE WideWorldImporters;

/*
    声明表格类型
*/
DROP TYPE IF EXISTS mytable;
CREATE TYPE mytable AS table
(
    id int NOT NULL、
	name NVARCHAR(100) NOT NULL
);

/*
    从现在起,您不必用列信息声明表变量,只需引用类型即可。
    列信息,只需引用类型即可。
*/
DECLARE @t AS mytable;
INSERT INTO @t VALUES(1,'HELLO WORLD');

SELECT * FROM @t;


CREATE TABLE dbo.testtable AS mytable; -- 没有

/*这是肯定的*/
SELECT *
INTO dbo.testtable
FROM @t
WHERE 1 = 2; --列可以,但行不行


SELECT * FROM dbo.testtecske;

控制流元素(if、while)

代码分支

如果......那么
IF 表达式

- 三值逻辑:真(TRUE)、假(FALSE)、未知(NULL
简单if示例

DECLARE @year SMALLINT;
SET @year = 2022;

IF @year = YEAR(GETDATE())
开始
PRINT '匹配!当前年份是:' + CAST(@year AS varchar(5));
结束
否则
开始
    打印 '没有匹配!年份变量:' + CAST(@year AS varchar(5));
    print 'there is no match!当前年份:' + CAST(YEAR(GETDATE()) AS varchar(5));
结束

DECLARE @year SMALLINT;
SET @year = (SELECT YEAR(MAX(OrderDate)) FROM WideWorldImporters.Sales.Orders);

IF @year = YEAR(GETDATE())
开始
PRINT '匹配!当前年份是:' + CAST(@year AS varchar(5));
结束
否则
开始
    打印 '没有匹配!年份变量:' + CAST(@year AS varchar(5));
    print 'there is no match!当前年份:' + CAST(YEAR(GETDATE()) AS varchar(5));
结束

NULL 处理嵌套的 if 元素

DECLARE @year SMALLINT;
SET @year = (SELECT YEAR(MAX(OrderDate)) FROM WideWorldImporters.Sales.Orders WHERE OrderID=500000);

IF @year = YEAR(GETDATE())
开始
PRINT '匹配!当前年份是:' + CAST(@year AS varchar(5));
结束
否则
开始
    如果 @year is NOT NULL
    开始
    print 'there is no match!年份变量:' + CAST(@year AS varchar(5));
    print 'there is no match!当前年份:' + CAST(YEAR(GETDATE()) AS varchar(5));
    结束
    否则
    开始
    打印"@year 值为 NULL
    结束
结束
结束

活动 DB,它只为您自己所用,其他会话仍可使用,请将其关闭

DECLARE @year SMALLINT;
SET @year = 2023;
IF @year = YEAR(GETDATE())
开始
    使用 WideWorldImporters
结束

复杂的 if 表达式

上个月最后一天 = 今天-20 || 今天在(周一、周二、周五) || 今天 = 2023-02-20
真假

print day(getdate())
PRINT EOMONTH(GETDATE(),-1); -- 二月
PRINT DATEDIFF(dd, GETDATE(),1 )
PRINT CAST(DATEADD(day, -20,GETDATE()) AS date);
PRINT DATENAME(dw,GETDATE())
打印 getdate()

IF EOMONTH(GETDATE(),-1)= CAST(DATEADD(day, -20,GETDATE()) AS date) OR DATENAME(dw,GETDATE()) IN ('Monday','Tuesday', 'Friday') OR CAST(GETDATE() AS date) = '2023-02-20'.
开始
print '这是真的!' END
结束

/*案例-2

如果 EOMONTH('2023-03-20',-1)= CAST(DATEADD(day, -20,GETDATE()) AS date) 或 DATENAME(dw,'2023-02-19') IN ('Monday','Tuesday', 'Friday') 或 CAST(GETDATE() AS date) = '2023-02-20'
开始
打印 '此为 true!
print 'this is still true!
结束

/*测试用例 3*/

IF EOMONTH('2023-03-20',-1)= CAST(DATEADD(day, -20,GETDATE()) AS date) OR DATENAME(dw,'2023-02-19') IN ('Monday','Tuesday', 'Friday') OR CAST(GETDATE() AS date) = '2023-01-28'.
开始
打印 '此为 true!
print 'this is still true!
结束
否则
开始
打印'非为真
结束
DECLARE @yeara SMALLINT=2015, @yearb SMALLINT;
 SELECT @yearb;

如果存在

使用 WideWorldImporters;
GO

DECLARE @yeara SMALLINT=2015, @yearb SMALLINT=2023;

IF EXISTS (SELECT OrderID FROM SAles.Orders WHERE YEAR(OrderDate) IN (@yeara,@yearb)
)
开始
PRINT 'There are orders in ' + CAST(@yeara as VARCHAR(5))+ ' or in '+CAST(@yearb as VARCHAR(5))
结束
否则
开始
    PRINT 'There are not orders in ' + CAST(@yeara as VARCHAR(5))+ ' or in '+CAST(@yearb as VARCHAR(5))
结束 ;
/* 添加了一点 NULL 的相同任务*/
DECLARE @yeara SMALLINT=2015, @yearb SMALLINT;
 SELECT @yearb;

IF EXISTS (SELECT OrderID FROM SAles.Orders WHERE YEAR(OrderDate) IN (@yeara,@yearb)
)
开始
PRINT 'There are orders in ' + CAST(@yeara as VARCHAR(5))+ ' or in '+CAST(@yearb as VARCHAR(5))
结束
否则
开始
    PRINT 'There are not orders in ' + CAST(@yeara as VARCHAR(5))+ ' or in '+CAST(@yearb as VARCHAR(5))
END ;

结束
/*空处理

DECLARE @yeara SMALLINT=2015, @yearb SMALLINT;
 SELECT @yearb;

如果(@yeara 为空或 @yearb 为空)
开始
PRINT '检测到未知值';

结束
ELSE
开始
如果存在(SELECT OrderID FROM SAles.Orders WHERE YEAR(OrderDate) IN (@yeara,@yearb) AND (@yeara IS NOT NULL OR @yearb IS NOT NULL)
)
开始
PRINT 'There are orders in ' + CAST(@yeara as VARCHAR(5))+ ' or in '+CAST(@yearb as VARCHAR(5));
结束
否则
开始
    PRINT 'There are not orders in ' + CAST(@yeara as VARCHAR(5))+ ' or in '+CAST(@yearb as VARCHAR(5));
结束
结束;

LOOPS

句法

WHILE (表达式)
开始

结束
(继续,中断,继续 1000)

DROP TABLE IF EXISTS dbo.t1,dbo.t2,dbo.t3;
CREATE TABLE dbo.t1 (num int);
CREATE TABLE dbo.t2 (num int IDENTITY(1,1));
CREATE TABLE dbo.t3 (num int IDENTITY(1,1));

/* 简单插入循环 INSERT 1-1000 > dbo.t1 方法 1*/

DECLARE @cnt int=1;

WHILE @cnt 1000 except 5 WITH Autoincrement column(方法 2)
 CONTINUE IF @cnt=5
*/

SET IDENTITY_INSERT dbo.t2 ON;

DECLARE @cnt int=0;
WHILE (@cnt < 1001)
开始
    SET @cnt +=1;
    IF @cnt=5 CONTINUE;
    INSERT INTO dbo.t2 (num) VALUES (@cnt);
结束

SELECT num FROM dbo.t2;

SET IDENTITY_INSERT dbo.t2 OFF;

/*
在 @cnt=5 处中断循环
*/
TRUNCATE TABLE dbo.t2;

SET IDENTITY_INSERT dbo.t2 ON;

DECLARE @cnt int=0;
WHILE (@cnt  dbo.t3 方法 3
*/
INSERT INTO dbo.t3 DEFAULT VALUES;
GO 1000

SELECT * FROM dbo.t3;

延时循环

DECLARE @cnt int=1;

WHILE @cnt <= 10
开始

    PRINT 'The counter value is '+CAST(@cnt AS varchar(5));
    等待延迟'00:00:01
     SET @cnt+= 1;
   

结束

/*这很好*/

DECLARE @cnt int=1;

WHILE @cnt <= 10
开始

    RAISERROR('The counter value is %d',0,1,@cnt) WITH NOWAIT;
    等待延迟'00:00:01
     SET @cnt+= 1;
结束

@@ROWCOUNT

计算最后一句话

DECLARE @rows int;
更新 #mytemp
设置数量=1000
WHERE Quantity>100;
SET @rows=@@ROWCOUNT;
如果 @rows > 0
开始
    SELECT CAST(@rows as varchar(5)) + 'rows were updated' (行已更新)
结束
否则
开始
SELECT '未更新记录
结束

错误处理

错误类型:
- 数据类型转换错误
- 估值错误
- 语法错误 > 在 t-sql 中无法处理
- 逻辑错误
- 名称解析错误 > 无法在 t-sql 中处理
- 限制违反错误
- 触发错误

数量 -1000

为什么要编写错误处理?
- 我不想破坏代码
- 我想记录错误
- 生成更易消化的错误代码

怎么做?
- 不要丢失密码
- 或删除代码,以避免出现重大问题

故障排除方法:
- 结构化错误处理:TRY > CATCH
- @@ERROR 内置函数,请勿使用

09:17:15 在第 42 行开始执行查询
Msg 8134,第 16 级,第 1 状态,第 1 行
遇到除以零错误。
总执行时间:00:00:00.013

信息 8134 > 错误代码 (id)
级别 > 严重程度
状态 > 状态
行 > 存在错误的行

零点除法示例

打印 10/0;

尝试......捕捉

开始尝试
    打印 10/0;
结束尝试
开始捕捉
    PRINT '0 is dumb!';
END CATCH;

错误处理内置函数

PRINT 'Error occurred!';
PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
PRINT '错误信息:' + ERROR_MESSAGE();
PRINT 'Error state: ' + CAST(ERROR_STATE() AS nvarchar(10));
PRINT 'Error severity: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
PRINT 'Error line: ' + CAST(ERROR_LINE() AS nvarchar(10));
PRINT 'Error module: ' + COALESCE(ERROR_PROCEDURE(),");

开始尝试
    打印 10/0;
结束尝试
开始捕捉
    PRINT 'Error occurred!
    PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Error message: ' + ERROR_MESSAGE();
    PRINT 'Error state: ' + CAST(ERROR_STATE() AS nvarchar(10);
    PRINT 'Error severity: ' + CAST(ERROR_SEVERITY() AS nvarchar(10);
    PRINT 'Error line: ' + CAST(ERROR_LINE() AS nvarchar(10);
    PRINT 'Error module: ' + COALESCE(ERROR_PROCEDURE(),'');
结束 CATCH;

PRINT 'Error module: ' + COALESCE(ERROR_PROCEDURE(),");

   开始尝试
   EXEC dbo.badprocedure
   结束尝试
   开始捕获

   结束捕获

名称恢复错误

开始尝试
    DECLARE @unitprice INT=0;
    SELECT * FROM dbo.notavailable;

    PRINT 10/@unitprice;
END TRY
BEGIN CATCH
    PRINT 'Error occurred!
    PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Error message: ' + ERROR_MESSAGE();
    PRINT 'Error state: ' + CAST(ERROR_STATE() AS nvarchar(10);
    PRINT 'Error severity: ' + CAST(ERROR_SEVERITY() AS nvarchar(10);
    PRINT 'Error line: ' + CAST(ERROR_LINE() AS nvarchar(10);
    PRINT 'Error module: ' + COALESCE(ERROR_PROCEDURE(),'');
结束 CATCH;

按模块处理名称解析错误

开始
创建或更改存储过程 dbo.badprocedure
AS
    SELECT * FROM dbo.notavailable;
执行
执行 dbo.badprocedure;
开始尝试
    DECLARE @unitprice int=0;
    EXEC dbo.badprocedure;
    PRINT 10/@unitprice;
END TRY
开始捕获
    PRINT 'Error occurred!
    PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Error message: ' + ERROR_MESSAGE();
    PRINT 'Error state: ' + CAST(ERROR_STATE() AS nvarchar(10);
    PRINT 'Error severity: ' + CAST(ERROR_SEVERITY() AS nvarchar(10);
    PRINT 'Error line: ' + CAST(ERROR_LINE() AS nvarchar(10);
    PRINT 'Error module: ' + COALESCE(ERROR_PROCEDURE(),'');
结束 CATCH;

处理 THROW 负单价错误

开始尝试
    DECLARE @unitprice INT=-20;
    如果 @单位价格 < 0
    开始
    -- THROW 有效范围从 50000 开始,定义严重性级别 16
        THROW 55555, 'The @unitprice parameter value is less than zero!', 1;
    结束
    PRINT 'SOR
结束尝试
开始捕获
    PRINT '发生错误!';
    PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Error message: ' + ERROR_MESSAGE();
    PRINT 'Error state: ' + CAST(ERROR_STATE() AS nvarchar(10);
    PRINT 'Error severity: ' + CAST(ERROR_SEVERITY() AS nvarchar(10);
    PRINT 'Error line: ' + CAST(ERROR_LINE() AS nvarchar(10);
    PRINT 'Error module: ' + COALESCE(ERROR_PROCEDURE(),'');
END CATCH;

THROW in CATCH - 我破解了代码,但首先删除了错误原因

开始尝试
    DECLARE @unitprice INT=-20;
    如果 @单位价格 < 0
    开始
    -- THROW 有效范围从 50000 开始,定义严重性级别 16
        THROW 55555, 'The @unitprice parameter value is less than zero!', 1;
    结束
    PRINT 'SOR
结束尝试
开始捕获
    PRINT '发生错误!';
    PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Error message: ' + ERROR_MESSAGE();
    PRINT 'Error state: ' + CAST(ERROR_STATE() AS nvarchar(10);
    PRINT 'Error severity: ' + CAST(ERROR_SEVERITY() AS nvarchar(10);
    PRINT 'Error line: ' + CAST(ERROR_LINE() AS nvarchar(10);
    PRINT 'Error module: ' + COALESCE(ERROR_PROCEDURE(),'');
    SELECT CAST(@unitprice AS NVARCHAR(100)) + ' 导致错误的单价值';
    抛出;
结束 CATCH;

RAISE ERROR - 在 catch 中不会破坏代码

BEGIN TRY
    DECLARE @myvar int;

    SET @myvar = -1;

    /* 如果参数值不可接受,则抛出错误 */
    IF @myvar < 0
        RAISERROR ('The @myvar parameter value is %d!', 16, 1, @myvar);

    PRINT '此行不执行!' */
结束尝试
开始捕获
    PRINT '发生错误!' PRINT '错误编号:''
    PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT '错误信息:' + ERROR_MESSAGE();
    PRINT 'Error state: ' + CAST(ERROR_STATE() AS nvarchar(10);
    PRINT 'Error severity: ' + CAST(ERROR_SEVERITY() AS nvarchar(10);
    PRINT 'Error line: ' + CAST(ERROR_LINE() AS nvarchar(10);
    PRINT 'Error module: ' + COALESCE(ERROR_PROCEDURE(),'');
结束 CATCH;

开始尝试
    DECLARE @unitprice INT=-20;
    IF @unitprice < 0
    开始
    -- THROW 有效范围从 50000 开始,定义严重性级别 16
        THROW 55555, 'The @unitprice parameter value is less than zero!', 1;
    结束
    PRINT 'SOR
结束尝试
开始捕获
    PRINT '发生错误!';
    PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Error message: ' + ERROR_MESSAGE();
    PRINT 'Error state: ' + CAST(ERROR_STATE() AS nvarchar(10);
    PRINT 'Error severity: ' + CAST(ERROR_SEVERITY() AS nvarchar(10);
    PRINT 'Error line: ' + CAST(ERROR_LINE() AS nvarchar(10);
    PRINT 'Error module: ' + COALESCE(ERROR_PROCEDURE(),'');
    SELECT CAST(@unitprice AS NVARCHAR(100)) + 'unitprice valuse causes the error';
    RAISERROR ('%d 单位价格值导致错误!', 15, 1, @unitprice);
结束 CATCH;

RAISERROR 可集成到事件查看器 > 窗口日志 > 应用程序中
生成唯一错误代码

开始尝试
    DECLARE @unitprice INT=-20;
    如果 @单位价格 < 0
    开始
    -- 引发错误
        DECLARE @myerrorcode UNIQUEIDENTIFIER=NEWID();
        DECLARE @myerrorstring varbinary(16)= CAST(@myerrorcode AS varbinary(16));
        RAISERROR ('%d 单位价格值导致错误!您的错误代码:%x', 16, 1, @unitprice,@myerrorstring) WITH LOG;
    结束
    PRINT 'SOR
结束尝试
开始捕获
    PRINT '发生错误!
    PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT '错误信息:'+ ERROR_MESSAGE();
    PRINT 'Error state: ' + CAST(ERROR_STATE() AS nvarchar(10);
    PRINT 'Error severity: ' + CAST(ERROR_SEVERITY() AS nvarchar(10);
    PRINT 'Error line: ' + CAST(ERROR_LINE() AS nvarchar(10);
    PRINT 'Error module: ' + COALESCE(ERROR_PROCEDURE(),'');
    SELECT CAST(@unitprice AS NVARCHAR(100)) + 'unitprice valuse causes the error';
    抛出;
结束 CATCH;

INSERT 的错误处理

USE testdb;
运行

DROP TABLE IF EXISTS dbo.military;
CREATE TABLE dbo.military(
    id INT IDENTITY(1,1) PRIMARY KEY
    ,士兵名称 varchar(100) NOT NULL
    如果没有,请将其删除。
)

开始尝试
    INSERT INTO dbo.military (soldier_name,soldier_bdate)
    VALUES ('John Ramobo', '19770101')
    -- 引发错误
结束尝试
开始捕获
    if error_number()=547
    开始
    DECLARE @myerrorcode UNIQUEIDENTIFIER=NEWID();
        DECLARE @myerrorstring varbinary(16)= CAST(@myerrorcode AS varbinary(16));
        RAISERROR ('Check constraint violation. Your error code: %x', 16, 1,@myerrorstring) WITH LOG;
    结束
    PRINT '发生错误!
    PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT '错误信息:'+ ERROR_MESSAGE();
    PRINT 'Error state: ' + CAST(ERROR_STATE() AS nvarchar(10);
    PRINT 'Error severity: ' + CAST(ERROR_SEVERITY() AS nvarchar(10);
    PRINT 'Error line: ' + CAST(ERROR_LINE() AS nvarchar(10);
    PRINT 'Error module: ' + COALESCE(ERROR_PROCEDURE(),'');
END CATCH;
/*任何错误*/
开始尝试
    INSERT INTO dbo.military (soldier_name,soldier_bdate)
    VALUES ('John Ramobo', '19770101')
    -- 引发错误
END TRY
开始捕获
    DECLARE @newerrormessage NVARCHAR(1000);
    SET @newerrormessage = 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + CHAR(13) + 'Error message: ' + ERROR_MESSAGE()+ CHAR(13) + 'Error state: ' + CAST(ERROR_STATE() AS nvarchar(10))+ CHAR(13) + 'Error severity:' + CAST(ERROR_SEVERITY() AS nvarchar(10))+ CHAR(13) + 'Error line: ' + CAST(ERROR_LINE() AS nvarchar(10))+ CHAR(13) + 'Error module: ' + COALESCE(ERROR_PROCEDURE(),'');
    DECLARE @myerrorcode UNIQUEIDENTIFIER=NEWID();
        DECLARE @myerrorstring varbinary(16)= CAST(@myerrorcode AS varbinary(16));
        RAISERROR ('%s 检查约束违反。 您的错误代码:%x', 16, 1,@newerrormessage,@myerrorstring) WITH LOG;
结束 CATCH;

嵌套的 try-catch 块

开始尝试
    打印 10/0;
结束尝试
开始捕捉
    开始尝试
        INSERT INTO dbo.military (id,soldier_name,soldier_bdate)
        VALUES (1,'John Ramobo', '19790101')
    结束尝试
    开始捕获
        THROW 55555, 'The @unitprice parameter value is less than zero!', 2;
    结束 CATCH
结束 CATCH

观点

- 参考,数值在计算时重新计算,除非索引视图

- 如果我向用户隐藏一个视图,我就不会在视图中放置某些列并允许用户访问它们。
- 在实践中,我们将其放在一个单独的模式中,并赋予其访问权限

使用 WideWorldImporters
GO
SELECT c.CustomerName, o.OrderID, SUM(ol.Quantity) as QuantitySum
FROM Sales.Customers as c
JOIN Sales.Orders as o ON o.CustomerID = c.CustomerID
JOIN Sales.OrderLines as ol ON ol.OrderID = o.OrderID
JOIN Warehouse.StockItems as si ON si.StockItemID = ol.StockItemID
GROUP BY c.CustomerName, o.OrderID;
删除 VIEW
DROP VIEW IF EXISTS FirsView;
删除 VIEW IF EXISTS FirsView
创建 VIEW FirstView
VIEW
SELECT c.CustomerName, o.OrderID, SUM(ol.Quantity) as QuantitySum
FROM Sales.Customers as c
JOIN Sales.Orders as o ON o.CustomerID = c.CustomerID
JOIN Sales.OrderLines as ol ON ol.OrderID = o.OrderID
JOIN Warehouse.StockItems as si ON si.StockItemID = ol.StockItemID
GROUP BY c.CustomerName, o.OrderID;
返回
SELECT * FROM FirstView;

与模式绑定、加密

计划编制: 我们不能修改它使其无法使用,例如删除列
加密: 我们无法看到您的代码(创建)

开始
创建视图 SecondView
模式绑定,加密
VIEW
SELECT c.CustomerName, o.OrderID, SUM(ol.Quantity) as QuantitySum
FROM Sales.Customers as c
JOIN Sales.Orders as o ON o.CustomerID = c.CustomerID
JOIN Sales.OrderLines as ol ON ol.OrderID = o.OrderID
JOIN Warehouse.StockItems as si ON si.StockItemID = ol.StockItemID
GROUP BY c.CustomerName, o.OrderID;
返回

更改视图

- 修改视图而不删除 - DROP VIEW IF EXISTS dbo.VarosokOrsz odok2;
-使用 VarosokOrszegok2 页面,但仍有 SCHEMABIDING 功能

GO
ALTER VIEW dbo.VarosokOrsz odours2
作为
SELECT v.Nev AS VarosNev
o.Nev AS OrszagNev
FROM dbo.Varosok v
JOIN dbo.Orszagok o ON v.OrszagId=o.Id;
返回

索引视图

持续存在?

- 视图必须是确定的
- 模式编辑,也适用于用户功能
- 只能使用标志

在哪些情况下?有哪些好处?
- 在磁盘上,因此速度更快

问题:如果有复杂的连接,查询速度会更快
如果修改,则必须同时修改视图

如果您在表格中写的内容和读的内容一样多或一样多,请不要申请

开始
CREATE VIEW dbo.NemDet
作为
SELECT Nev, GetDate() AS RandomDate
FROM dbo.Varosok
执行

SELECT * FROM dbo.NemDet

CREATE UNIQUE CLUSTERED INDEX ix_Nemdet ON dbo.NemDet(Nev);
-- 非确定性
执行
ALTER VIEW dbo.NemDet
模式绑定
模式绑定
SELECT Nev, GetDate() AS RandomDate
FROM dbo.Varosok
完成
CREATE UNIQUE CLUSTERED INDEX ix_Nemdet ON dbo.NemDet(Nev);

-- 联接,只能是内部联接

DROP VIEW IF EXISTS dbo.TobbTabla;

删除
创建视图 dbo.TobbTabla
的模式绑定
模式绑定
SELECT v.Nev as VarosNev, o.Nev AS OrszagNev
FROM dbo.Varosok v
JOIN dbo.Orszagok o ON v.OrszagId=o.Id

返回
SELECT * FROM dbo.TobbTabla;

CREATE UNIQUE CLUSTERED INDEX ix_Nemdet ON dbo.TobbTabla(VarosNev,OrszagNev);

-- CRUD - 创建、读取、更新、删除

-- ORDER BY - 默认情况下无法使用
-- ORDER BY - 使用置顶
返回
创建视图 dbo.ViewOrderBy
的模式绑定
模式绑定
SELECT TOP 1 v.Nev as VarosNev, o.Nev AS OrszagNev
FROM dbo.Varosok v
JOIN dbo.Orszagok o ON v.OrszagId=o.Id
ORDER BY o.Nev ASC;
返回

存储过程

- 输入
- 输出
- 可多次调用

存储过程的优势

- 参数传送
- 为存储过程创建 > 执行计划 - 性能 - 参数嗅探 - 使用其他参数可能会非常慢 > WITH RECPOMPILE(每次调用都有新的执行计划)
- 能否将其作为一个单独的案例
-https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/

- 每个参数的类型必须是

GO
ALTER VIEW dbo.VarosokOrsz odours2
作为
SELECT
v.Nev as VarosNev、
o.Nev 作为 OrszagNev
从
dbo.Varosok as v
JOIN dbo.Orszagok as o ON v.OrszagId = o.Id
WHERE v.Nev LIKE 'B%'.
返回

创建过程 dbo.TaroltEljarasVarosokOrszagok
作为
开始
	SELECT
	v.Nev as VarosNev、
	o.Nev 作为 OrszagNev
	从
	dbo.Varosok as v
	JOIN dbo.Orszagok as o ON v.OrszagId = o.Id
	WHERE v.Nev LIKE 'B%'.
结束

SELECT * FROM dbo.VarosokOrszagok2;

EXEC dbo.TaroltEljarasVarosokOrszagok;

创建表 #Eredmeny1
(
	VarosNev NVARCHAR(100)、
	LocationNev NVARCHAR(100)、
)

INSERT INTO #Eredmeny1
EXEC dbo.TaroltEljarasVarosokOrszagok

SELECT * FROM #Eredmeny1
执行
CREATE PROCEDURE dbo.VarosKereses(@SearchName NVARCHAR(100))
作为
开始
	SELECT
	v.Nev as VarosNev、
	o.Nev 作为 OrszagNev
	从
	dbo.Varosok as v
	JOIN dbo.Orszagok as o ON v.OrszagId = o.Id
	WHERE v.Nev LIKE @SearchName
结束
结束

执行 dbo.VarosKereses'B%'。
EXEC dbo.VarosKereses'V%'(执行 dbo.VarosKereses 'V%')。

什么是 SQL 注入?

- 用户在输入什么?这样可以吗?
- V" > 确定
- "V" ; DROP TABLE dbo.students; SELECT * FROMdbo.Teachers WHERE something LIKE '"> 非常不确定
- WHERE studentId = 123 AND v.Nev LIKE '"' " 或 WHERE v.Nev LIKE '"none these ' OR 1=1″ > VERY NOT OK" 或 WHERE v.Nev LIKE '"none of these ' OR 1=1″ > VERY NOT OK
-/**/或//**/

- 解决方案是什么?

EXEC dbo.VarosKereses '"none OR 1=1 "'

DECLARE @param NVARCHAR(100) = '"no such OR 1=1 "';
EXEC dbo.VarosKereses @param;

执行
CREATE PROCEDURE dbo.VarosKereses2(@SearchName NVARCHAR(100))
作为
开始
	SELECT * FROM dbo.Varosok WHERE Id=1 AND Nev LIKE @SearchName
结束
结束

INSERT INTO dbo.Varosok(ID, NEV, OrszagId)
VALUES (100, 'none' OR ''''=''', 1);

SELECT * FROM dbo.Varosok WHERE Id =100;

您需要指定存储过程表参数
作为只读

DROP TYPE IF EXISTS mytable;
CREATE TYPE mytable AS table
(
    id int
);

执行
CREATE OR ALTER PROCEDURE dbo.GetSales(@myt mytable READONLY)
作为
SELECT id FROM @myt WHERE id > 1;
执行

DECLARE @t mytable;
INSERT INTO @t VALUES(1);
INSERT INTO @t VALUES(2);
INSERT INTO @t VALUES(3);

EXEC dbo.GetSales @t

如何退款?
OUTPUT - 参数!我们可以从存储过程中发送多个输出值

执行
CREATE OR ALTER PROCEDURE dbo.OutputTest (@param1 INT, @outparam int OUTPUT)
作为
开始
SET @outparam=@param1*2;
结束
结束

-- 返回 NULL
DECLARE @doubledValue int;
EXEC dbo.OutputTest 2,@doubledValue
SELECT @doubledValue;
返回

-- 这很好:
DECLARE @doubledValue int;
EXEC dbo.OutputTest 2, @doubledValue OUTPUT
SELECT @doubledValue;

-- 参数顺序不能颠倒
DECLARE @doubledValue int;
EXEC dbo.OutputTest @doubledValue OUTPUT,2;
SELECT @doubledValue;
执行

DECLARE @doubledValue int;
EXEC dbo.OutputTest 2,@doubledValue OUTPUT;
SELECT @doubledValue;

-- 除了用名称指定 haa 之外
GO
DECLARE @doubledValue int;
EXEC dbo.OutputTest 2,@outparam = @doubledValue OUTPUT;

SELECT @doubledValue;

返回值 - RETURN

执行
CREATE OR ALTER PROCEDURE dbo.ReturnTest(@varosId INT, @varosNev NVARCHAR(100) OUTPUT )
AS
如果 @varosId < 0
	返回 1;
否则
	SET @varosNev = (SELECT Nev FROM dbo.Varosok WHERE Id = @varosId);
	返回 0;
返回

DECLARE @ret int;
DECLARE @varos NVARCHAR(100);
EXEC @ret = dbo.ReturnTest 2, @varos OUTPUT
SELECT @ret, @varos
返回
DECLARE @ret int;
DECLARE @varos NVARCHAR(100);
EXEC @ret = dbo.ReturnTest 1, @varos OUTPUT
SELECT @ret, @varos
返回
DECLARE @ret int;
DECLARE @varos NVARCHAR(100);
EXEC @ret = dbo.ReturnTest -3, @varos OUTPUT
SELECT @ret, @varos

SELECT * FROM dbo.Varosok

运行至第一个 RETURN

- 因此,它不再覆盖

执行
CREATE OR ALTER PROCEDURE dbo.ReturnTest (@varosId INT, @varosNev nvarchar(100) OUTPUT)
返回
开始
如果 @varosID < 0
开始
返回 1;
结束

SELECT @varosNev= (SELECT Nev FROM dbo.Varosok WHERE Id = @varosID)
SET @varosNev = @varosNev + '!
返回 0;
结束
结束

DECLARE @ret int;
DECLARE @varos NVARCHAR(100);

EXEC @ret = dbo.ReturnTest 2, @varos OUTPUT
SELECT @ret,@varos;
- 之前已返回 @varos,因此不再覆盖
EXEC @ret = dbo.ReturnTest -3, @varos OUTPUT
SELECT @ret,@varos;
----------------MEGOLDÁS
GO
CREATE OR ALTER PROCEDURE dbo.ReturnTest (@varosId INT, @varosNev nvarchar(100) OUTPUT)
返回
SET @varosNev = NULL;
开始
如果 @varosID < 0
开始
返回 1;
结束

SELECT @varosNev= (SELECT Nev FROM dbo.Varosok WHERE Id = @varosID)
SET @varosNev = @varosNev + '!
返回 0;
结束
结束

DECLARE @ret int;
DECLARE @varos NVARCHAR(100);

EXEC @ret = dbo.ReturnTest 2, @varos OUTPUT
SELECT @ret,@varos;
--之前输入的内容,因此不再覆盖
EXEC @ret = dbo.ReturnTest -3, @varos OUTPUT
SELECT @ret,@varos;
返回

错误

GO
CREATE OR ALTER PROCEDURE dbo.PlaceOrder (@PaymentDate datetime2)
AS
如果 @PaymentDate > GETDATE()
THROW 50001, 'Cannot Place an order in future',1;
执行
EXEC dbo.PlaceOrder '2024-01-01
------------------------------------------------
执行
创建或更改存储过程 dbo.Osztas(@v1 INT, @v2 INT, @hanyados INT OUTPUT)
作为
开始尝试
SET @hanyados = @v1 / @v2
返回 0;
结束尝试
开始捕获
SET @hanyados=NULL;
返回 1;
END CATCH
返回
DECLARE @ertek INT,@ret INT;
EXEC @ret=dbo.Osztas 10,2, @ertek OUTPUT;
SELECT @ret, @ertek;
执行
DECLARE @ertek INT,@ret INT;
EXEC @ret=dbo.Instal 10,0, @ertek OUTPUT;
SELECT @ret, @ertek;

默认值

执行
CREATE OR ALTER PROCEDURE dbo.Osztas(@v1 INT, @v2 INT= 3, @hanyados INT OUTPUT)
返回
开始尝试
SET @hanyados = @v1 / @v2
返回 0;
结束尝试
开始捕获
SET @hanyados=NULL;
返回 1;
END CATCH
返回
DECLARE @ertek INT,@ret INT;
EXEC @ret=dbo.Div 10, @hanyados = @ertek OUTPUT;
SELECT @ret, @ertek;
执行
DECLARE @ertek INT,@ret INT;
EXEC @ret=dbo.Instal 10,5, @ertek OUTPUT;
SELECT @ret, @ertek;

设置 NOCOUNT ON

GO
CREATE OR ALTER PROCEDURE dbo.TaroltEljarasVarosokOrsz odors
作为
开始
设置 NOCOUNT ON
SELECT v.Nev AS VarosNev
o.Nev AS OrszagNev
FROM dbo.Varosok v
JOIN dbo.Orszagok o ON v.OrszagId=o.Id
WHERE v.Nev like 'B%';
结束
执行
EXEC dbo.TaroltEljarasVarosokOrszagok

修改了多少行?

执行
CREATE OR ALTER PROCEDURE dbo.VarosFrissites (@searchParam nvarchar(10), @affectedRows INT OUTPUT)
返回
开始
UPDATE dbo.Varosok
SET Nev =LOWER(Nev)
WHERE Nev like @searchParam;
SET @affectedRows=@@ROWCOUNT
结束
结束

DECLARE @ar INT;
EXEC dbo.VarosFrissites 'B%', @ar OUTPUT;
PRINT @ar;

UDF:

- 一往无前
- 非状态,给出数据类型
- 输入参数
- 存储的程序不能被调用

类型
- 系统功能
- 表值:不能在视图中参数化
- 标量值:可以选择
- 汇总 < 可导入外部语言,如 .Net 和 C# 函数
https://learn.microsoft.com/en-us/sql/t-sql/statements/create-aggregate-transact-sql?view=sql-server-ver15

try-catch - 不工作!!!!!!!!!!!!!!!!
但我从存储过程中调用,它就在那里,那么是的

DDL、插入、更新和删除不起作用
使用 WideWorldImporters;
转到
转到
创建或更改函数 Sales.function_date()
RETURNS datetime /* 指定返回值的数据类型 */
返回
开始
    /* 返回值 */
    返回 current_timestamp;
结束;
返回
-- 调用函数
SELECT Sales.function_date();

订单总和

开始
创建函数 Sales.total_sum_orderline(
    @ordelineID int
)
RETURNS decimal(20,3) --WITH SCHEMABINDING --我们将其附加到表结构,有时有助于提高性能
作为
开始
    返回(SELECT Quantity*UnitPrice FROM Sales.OrderLines Where OrderLineID = @ordelineID)
结束;
返回
/*
    在 SELECT 子句中使用标量 UDF 返回已征税行的总值
    在 SELECT 子句中。
*/

SELECT Sales.total_sum_orderline(2);
SELECT Quantity*UnitPrice FROM Sales.OrderLines Where OrderLineID = 2;
/* 对每一行计算总数--会很慢,对每一行计算总数,CPU 会很高,我们不喜欢标量函数 */
SELECT OrderLineID,Sales.total_sum_orderline(2)
FROM Sales.OrderLines;

UDF 标量内联

- 不执行 UDF,将其分解为表格操作 > 占用更少 CPU - 高级功能
从 2019 年起,我们重新包装了
您必须遵守:

可内联标量 UDF 要求部分
https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver15
Kiegészítés: https://support.microsoft.com/en-us/topic/kb4538581-fix-scalar-udf-inlining-issues-in-sql-server-2022-and-2019-f52d3759-a8b7-a107-1ab9-7fbee264dd5d

- 例如,返回只能是
- 例如,Scalar UDF 内联对计算列不起作用!

执行
CREATE OR ALTER FUNCTION dbo.total_sum_orderline(
    @ordelineID int
)
RETURNS decimal(20,3) WITH SCHEMABINDING -- 我们将其附加到表结构,有时有助于提高性能
作为
开始
    返回(SELECT Quantity*UnitPrice FROM dbo.testlines Where OrderLineID = @ordelineID)
结束;
返回
SELECT dbo.total_sum_orderline(2);
SELECT Quantity*UnitPrice FROM dbo.testlines Where OrderLineID = 2;
/* 计算列 - 如果这样做,我们将失去内联功能 - 保留在表内
在检查约束中也会丢失 */
ALTER 表 dbo.testlines
ADD totalsum AS dbo.total_sum_orderline(OrderID);

SELECT OrderLineID, totalsum FROM dbo.testlines
WHERE OrderLineID<200;
/*view-n - 我们更喜欢在这里使用 */
执行
创建或更改视图 dbo.testview
的模式绑定
模式绑定
SELECT OrderLIneID AS 'lineID
, dbo.total_sum_orderline(OrderLineID) as 'totalsum
FROM dbo.testlines
返回
SELECT * FROM dbo.testview
WHERE LineID < 200;
/* 如果具有模式禁用的视图包含标量函数,那么标量函数也必须具有模式禁用功能*/
/* 检查约束--内联不起作用 */
ALTER TABLE dbo.testlines
ADD CONSTRAINT CK_test_1 CHECK (
(OrderLineID = 200);
更新 dbo.testlines
SET OrderLineID=1600
WHERE OrderLineID  500
    返回 500;

    返回 @days;
结束
GO
SELECT OrderID, OrderDate, dbo.datediffer(OrderID)
FROM dbo.testorders
WHERE OrderID  500
    开始
        SET @days=500;
    结束

    返回 @days;
结束
GO
SELECT OrderID, OrderDate, dbo.datediffer(OrderID)
FROM dbo.testorders
WHERE OrderID  500
    开始
        SET @days=500;
    结束

    返回 @days;
结束
GO
SELECT OrderID, OrderDate, dbo.datediffer(OrderID, GETDATE())
FROM dbo.testorders
WHERE OrderID  500
		开始
			SET @days = 500;
		结束
	返回 @days
结束
结束

SELECT OrderID,OrderDate,dbo.datediffer2(OrderDate,GETDATE()) AS 'days'.
FROM dbo.testorders;

标量 UDF 的默认值

执行
CREATE OR ALTER FUNCTION dbo.defaultfn(@id int=6)
返回 int
作为
开始
    返回 @id;
结束
返回
SELECT dbo.defaultfn(2);
SELECT dbo.defaultfn(default);

表值函数

- 总是带表返回
- 无连接

类型
- 内联表值函数 iTVF > 1 SELECT STATEMENT;
- 多语句 - MSTVF(糟糕) - 不要使用,优化效果差

USE WideWorldImporters;
GO
/*
    创建内联 TVF 以获取 ColorId 和 ColorName
*/
CREATE FUNCTION Warehouse.GetColor(@colorid int)
返回表
作为
返回
    (SELECT ColorID, ColorName
     FROM Warehouse.Colors
     WHERE ColorID = @colorid);
返回
/*
    在 FROM 子句中的简单 SELECT 查询中使用内联 TVF。
*/
SELECT ColorId, ColorName
FROM WareHouse.GetColor(36);

SELECT ColorId, ColorName
FROM WareHouse.GetColor(10);

TVF UDF 的默认值

GO
CREATE OR ALTER FUNCTION Warehouse.GetColor(@colorid int=10)
返回表
返回
返回
(SELECT ColorID, ColorName
FROM Warehouse.Colors
WHERE ColorID = @colorid);
返回
SELECT ColorId, ColorName
FROM WareHouse.GetColor(default);

应用 - 交叉应用,如内部连接

SELECT StockItemID,StockItemName,ColorName
FROM WareHouse.StockItems s
交叉应用 WareHouse.GetColor(s.ColorID) c.GetColor(s.ColorID);

应用 - 外层应用,如左或右外层连接

SELECT StockItemID,StockItemName,ColorName
FROM WareHouse.StockItems s
OUTER APPLY WareHouse.GetColor(s.ColorID) c.GetColor(s.ColorID);

非功能部分
表操作员:
交叉查询代替子查询(返回)
某些子查询

交叉应用代替子查询(子查询返回 1 个标量值)
例如
https://github.com/green-fox-academy/teaching-materials/blob/master/workshop/sql-server-subqueries-aggregations/correlated-quantity/correlated-quantity.md
按以下内容修改下面的查询:
- 新增一列,显示库存物品 ID 的最大数量
- 再增加一列,显示最大值和最小值之间的差值。
库存物品 ID 的数量和每个订单的数量
| 订单 ID | 库存项目 ID | 数量 | Max_StockItem_Qty | Max_Diff_Qty | Max_Diff_Qty

/* 子查询版本: 0.1452 */
SELECT o.OrderID
				o.StockItemID
				数量
				,( SELECT MAX(Quantity)
					FROM Sales.OrderLines
					WHERE StockItemID = 180
				 ) AS Max_StockItem_Qty
				,(SELECT maxq.qty - Quantity
						从 ( SELECT MAX(Quantity) AS qty
										FROM Sales.OrderLines
										WHERE StockItemID = 180
								 ) AS maxq
				) AS Max_Diff_Qty
FROM Sales.OrderLines AS o
WHERE StockItemID = 180;
/* CROSS APPLY - 表操作符版本 - CROSS APPLY 成本 0.102256 */
SELECT OrderId,StockItemID,Quantity,Max_StockItem_Qty,Max_StockItem_Qty-Quantity AS Max_Diff_Qty
FROM Sales.OrderLines
	交叉应用(SELECT MAX(Quantity) AS Max_StockItem_Qty FROM Sales.OrderLines
		WHERE StockItemID=180) temptable
WHERE StockItemID = 180;

触发器

- 我可以运行触发器吗?> 不可以
- 事件激活触发器 !"FIRE_TRIGGER"(火警触发器
- 我可以为触发器提供参数吗?
- IF ... ELSE 结构可用
- try ...catch 结构化错误处理 > 是
- 创建简单触发器
触发器组:
- DML 触发器:INSERT、UPDATE、DELETE > MASTERWORK
- DDL 触发器:ALTER、CREATE、DROP
- 登录触发器

登录触发器:危险!您可能登录失败

USE testdb;
DROP TABLE IF EXISTS dbo.logonsactivity;
CREATE TABLE dbo.logonsactivity (
    id INT NOT NULL IDENTITY(1,1) PRIMARY KEY、
    Login VARCHAR(50)、
    servername VARCHAR(50)、
    服务器版本 VARCHAR(350)、
    服务名称 VARCHAR(50)、
    logintime datetime2 DEFAULT GETDATE()
);
-- 登录名
select suser_name();
-- 服务器名称 (实例)
select @@servername;
-- 服务器版本
select @@version;
-- 服务名称(不含服务器的实例名称)
select @@servicename;
/*触发器*/
GO
创建或更改触发器 tr_logtouser
用于登录所有服务器
AS
开始
    INSERT INTO testdb.dbo.logonsactivity
    select suser_name(),@@servername,@@version,@@servicename,current_timestamp;
结束
返回
SELECT * FROM testdb.dbo.logonsactivity;
SELECT * FROM sys.server_triggers;
-- 删除登录触发器
DROP TRIGGER tr_logtouser ON ALL SERVER;

DDL 触发器

DROP TABLE IF EXISTS dbo.Invoices;
CREATE TABLE dbo.Invoices (
    id INT PRIMARY KEY、
    customer_id INT NOT NULL、
    total INT NOT NULL
);
INSERT INTO dbo.Invoices
VALUES
(3,5,200),(4,9,10000), (20,9,14000);
执行
CREATE OR ALTER TRIGGER invoiceprotector ON DATABASE FOR DROP_TABLE,ALTER_TABLE
作为
开始
PRINT '您不能更改或删除任何表!' ROLLBACK; '您不能更改或删除任何表!'。
ROLLBACK;
结束
PRINT 'hello';
返回
SELECT * FROM sys.triggers;
ALTER 表 dbo.Invoices
DROP COLUMN total;
DROP TRIGGER invoiceprotector ON DATABASE;

DML 触发器

- 触发后
- 代替触发器

删除后

DROP TABLE IF EXISTS dbo.storno_invoices
CREATE TABLE dbo.storno_invoices (
    id INT, --PRIMARY KEY、
    customer_id INT NOT NULL、
    total INT NOT NULL、
    delete_time DATETIME2 NOT NULL DEFAULT CURRENT_TIMESTAMP
);
/* 事后触发器 - 创建触发器,将删除的发票移至 strono_table
SQL SERVER 触发器按语句工作!!!!!
- 避免使用变量 !*/
执行
CREATE TRIGGER dbo.TR_stroni_invoice ON dbo.Invoices AFTER DELETE
AS
set nocount on;
开始
    INSERT INTO dbo.cancellation_invoices (id, customer_id,total)
    SELECT * FROM deleted;
结束
结束
SELECT * FROM dbo.Invoices;
SELECT * FROM dbo.storno_invoices;

DELETE FROM dbo.Invoices
WHERE total > 1000;

SELECT * FROM dbo.Invoices;
SELECT * FROM dbo.storno_invoices;

创建拒绝触发器:修改发票

执行
CREATE OR ALTER TRIGGER dbo.tr_forbidden_modify_invoice
上的触发器
更新后
AS
set nocount on;
开始
回滚;
结束
GO
SELECT * FROM dbo.Invoices;

UPDATE dbo.Invoices
SET total=1000
WHERE id=3;

INSERT INTO dbo.Invoices
VALUES (25,9,2000),(92,20,20000),(100,19,21000);
UPDATE dbo.Invoices
SET total=1000
WHERE id=3;

DML 触发器在哪里?

!!!!!TRUNCATE 无法使用触发器激活 !!!!!!!!!!

代替触发器?> DML 触发器

例如,有了 Samlak,大的就会到其他地方去,这很罕见

创建触发器
where total >= 1100 > dbo.Invoces

where total dbo.smalltotalinvoices

DROP TABLE IF EXISTS dbo.smalltotalinvoices
  CREATE TABLE dbo.smalltotalinvoices (
    id INT PRIMARY KEY、
    customer_id INT NOT NULL、
    total INT NOT NULL
);
SELECT * FROM dbo.Invoices;
执行
CREATE OR ALTER TRIGGER dbo.tr_smalltotal
上的触发器
而不是插入
AS
set nocount on;
开始
 INSERT INTO dbo.Invoices
 SELECT * FROM inserted
 WHERE total >=1100;

 INSERT INTO dbo.smalltotalinvoices
 SELECT * FROM inserted
 WHERE total < 1100;
结束
GO
 INSERT INTO dbo.Invoices
 值(3,200,50000);

  INSERT INTO dbo.Invoices
 值(5,200,900);

SELECT * FROM dbo.Invoices;
SELECT * FROM dbo.smalltotalinvoices;

批量插入默认忽略触发器

批量插入 dbo.Invoices
  FROM N'C:\Users\mrhen\Documents\READER\MatReview\WEEK-4\w1d1_smalltotal.csv' WITH (fieldterminator=';',rowterminator='0x0A'/* not '\n' because from Zoli.
  WITH (fieldterminator=';',rowterminator='0x0A'/* not '\n' because it was from Zoli, FIRSTROW=2 if there is a header*/)
TRUNCATE TABLE dbo.invoices;
TRUNCATE TABLE dbo.smalltotalinvoices;
/* 删除 fire_triggers */
批量插入 dbo.invoices
  FROM 'C:\Users\mrhen\Documents\READER\MatReview\WEEK-4\w1d1_smalltotal.csv'.
  WITH (fieldterminator=';',rowterminator='0x0A', FIRE_TRIGGERS/* not '\n' because it was from Zoli, FIRSTROW=2 if there is a header*/)

SELECT * FROM dbo.Invoices;
SELECT * FROM dbo.smalltotalinvoices;
SELECT * FROM sys.triggers;
DROP TRIGGER dbo.TR_stroni_invoice, dbo.tr_forbidden_modify_invoice, dbo.tr_smalltotal;

窗口功能

- 窗口聚合函数(COUNT、SUM、MIN、MAX)
- 排序功能(ROW_NUMBER、RANK、DENSE_RANK、NTILE)
- 偏移函数(LAG、LEAD、FIRST_VALUE、LAST_VALUE)
- 统计函数(PERCENTILE_CONT、PERCENTILE_DISC、PERCENT_RANK、CUME_DIST)

OVER() == 窗口函数/SELECT/...

的行集、

USE WideWorldImporters;
GO
/* SELECT StockItemId, SUM(Quantity) AS sum_stock_quantities */
SELECT StockItemId, SUM(Quantity) AS sum_stock_quantities
FROM Sales.OrderLines
GROUP BY StockItemID;
返回
SELECT SUM(Quantity) AS sum_all_quantities
FROM Sales.OrderLines;
/* FELADAT
orderlineid stockitems sum_stock sum_all
1 1 20000 9 百万
2 1 20000
3 2 1000
*/
WITH myCTE AS (
    SELECT StockItemId, SUM(Quantity) AS sum_stock_quantities
    FROM Sales.OrderLines
    GROUP BY StockItemID
),
myCTE2 AS (
    SELECT SUM(Quantity) AS sum_all_quantities
    FROM Sales.OrderLines
)
SELECT sol.StockItemID,OrderLineID, myCTE.sum_stock_quantities,myCTE2.sum_all_quantitis, FORMAT(myCTE.sum_stock_quantities/(myCTE2.sum_all_quantitis*1.0),'P2') AS pct_all_quantity
FROM Sales.OrderLines sol
INNER JOIN myCTE ON myCTE.StockItemID=sol.StockItemID
CROSS JOIN myCTE2
ORDER BY sol.[StockItemID] ASC, OrderLineID ASC;
/*使用窗口函数重置 CTE2 */
SELECT SUM(Quantity) OVER ()
FROM Sales.OrderLines;
/*用新函数替换 CTE1*/
SELECT SUM(Quantity) OVER (PARTITION BY StockItemID)
FROM Sales.OrderLines;
/* 窗口函数版本*/
SELECT sol.StockItemID、
OrderLineID、
SUM(Quantity) OVER (PARTITION BY StockItemID) AS sum_stock_quantitis、
SUM(Quantity) OVER () AS sum_all_quantities
, FORMAT(SUM(Quantity) OVER (PARTITION BY StockItemID)/(SUM(Quantity) OVER ()*1.0),'P2') AS pct_all_quantity
FROM Sales.OrderLines sol
ORDER BY sol.[StockItemID] ASC, OrderLineID ASC;

窗口功能,按分组

SELECT sol.StockItemID、
SUM(Quantity) /*OVER (PARTITION BY StockItemID)*/ AS sum_stock_quantitis、
SUM(SUM(Quantity)) OVER () AS sum_all_quantities
, FORMAT(SUM(Quantity) /*OVER (PARTITION BY StockItemID)*//(SUM(SUM(Quantity)) OVER ()*1.0),'P2') AS pct_all_quantity
FROM Sales.OrderLines sol
GROUP BY StockItemID
ORDER BY sol.[StockItemID] ASC;

排名功能

语法要求:OVER 子句内的 ORDER BY

row_number、rank、dense_rank、ntile

SELECT OrderId, Customerid,OrderDate、
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS ROW_NUMBER
    , Rank() OVER(ORDER BY OrderDate) AS rnk -- 死循环跳转和该行的下一个等级
    , DENSE_RANK() OVER(ORDER BY OrderDate) AS dense_rnk -- 死循环跳转到下一等级
    , NTILE (20) OVER(ORDER BY OrderDate) AS n_tile -- 分成 20 份,返回堆结果
FROM Sales.Orders;

按顾客 ID 排序

SELECT OrderId, CUstomerid,OrderDate、
    ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS ROW_NUMBER
    , Rank() OVER(PARTITION BY CustomerID ORDER BY OrderDate) AS rnk
    , DENSE_RANK() OVER(PARTITION BY CustomerID ORDER BY OrderDate) AS dense_rnk
    , NTILE (10) OVER(PARTITION BY CustomerID ORDER BY OrderDate) AS n_tile -- 分成 20 份,返回堆结果
FROM Sales.Orders;

网店订单中包含哪些商品 - 订单总额、订单号

SELECT OrderId、
StockItemID、
数量
单价
(UnitPrice*Quantity) AS OrderLine_SUM、
ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY (SELECT NULL)) AS orderline_number、
SUM(UnitPrice*Quantity) OVER (PARTITION BY OrderID Order BY OrderLineID) as running_total
 FROM Sales.OrderLines;

 SELECT OrderId、
StockItemID、
数量、
单价、
OrderLineID、
(UnitPrice*Quantity) AS OrderLine_SUM、
ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY (SELECT NULL)) AS orderline_number、
SUM(UnitPrice*Quantity) OVER (PARTITION BY OrderID Order BY OrderLineID DESC) as running_total
 FROM Sales.OrderLines;

运行总数

SELECT CityID, CityName, LatestRecordedPopulation AS population、
       (SELECT SUM(LatestRecordedPopulation)
        FROM Application.Cities c2
        WHERE c2.StateProvinceID = c1.StateProvinceID AND c2.CityID <= c1.CityID) AS run_total
FROM 应用程序.城市 c1
WHERE StateProvinceID = 6
ORDER BY CityID;
/* Windows 功能版本 */
 SELECT CityID, CityName, LatestRecordedPopulation、
 SUM(LatestRecordedPopulation) OVER(ORDER BY CityID) as run_total
FROM Application.Cities
WHERE StateProvinceID = 6

偏移功能
(滞后,领先,first_value,last_value)。

/* 下一个城市 ID */
SELECT CityID, CityName, (SELECT MIN(CityID) FROM Application.Cities c2 WHERE c2.CityID > c1.CityID) AS nextcityid
FROM 应用程序.城市 c1
按 CityID ASC 排序;
/* 窗口函数顺序 */
SELECT CityID, CityName, LEAD(CityID) OVER(ORDER BY CityID) AS nextcityid
FROM Application.Cities
按 CityID ASC 排序;

SELECT CityID、CityName、LAG(CityID) OVER(ORDER BY CityID) AS nextcityid
FROM Application.Cities
ORDER BY CityID ASC;

卡性估计

ALTER DATABASE WideWorldImporters
SET Compatibility_Level = 150;
GO
更改数据库作用域配置
SET Legacy_Cardinality_Estimation = OFF

--------------
SELECT name, value
FROM sys.database_scoped_configurations
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION' ;
-----------
SELECT *
FROM sys.databases
WHERE name = 'WideWorldImporters';

第一次模拟考试

/* 练习 1
设计一个假想图书数据库的小型子集。

在 Books 数据库中,至少要存储以下数据:
    - 书籍
    - 作者
    - 根据需要确定书籍和作家之间的关系

每个作家可以有多本书,但一本书只能有一个作家。

至少必须存储以下数据:
    - 图书标题
    - 图书长度(页数
    - 图书出版日期
    - 该书的作者
    - 流派名称(每本书只有一个流派:科幻、动作、恐怖等)

创建具有所需数据列和适当数据类型的相关表格。

至少创建以下约束:
    - PRIMARY KEY
    - 前置键

确保没有标题相同的书籍。
如有必要,请指定其他约束。

编写表脚本。

需要多少个表格?
表格关系?1->N(书籍、作者),1->N(书籍、流派)


*/

创建 DATABASE matrev20230303;
GO
USE matrev20230303;
创建
创建 SCHEMA books;
开始

DROP TABLE IF EXISTS books.books、books.authors、books.genres;

创建表 books.authors(
     id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    ,author_name NVARCHAR(200) NOT NULL
);

CREATE TABLE books.genres (
     id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    ,genre_name NVARCHAR(200) NOT NULL
        CONSTRAINT UK_genres_name UNIQUE
);

创建表 books.books(
     id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    ,title NVARCHAR(200) NOT NULL CONSTRAINT UK_books_title UNIQUE
    ,pagenr SMALLINT NOT NULL
        CONSTRAINT CK_books_pagenr CHECK (pagenr > 0)
    出版日期 DATE
    ,genre_id INT
    ,作者 ID INT
    CONSTRAINT FK_books_genreid FOREIGN KEY (genre_id) REFERENCES books.genres (id)
    AND CONSTRAINT FK_books_authorid FOREIGN KEY (author_id) REFERENCES books.authors (id)
);

/*
INSERT INTO books.authors
VALUES ('Robert Ludlum'),('Jack Higgins');

INSERT INTO books.genres
VALUES ('spy thriller'),('action');

INSERT INTO books.books
VALUES('钱学森手稿',700,'19760101',1,1);

INSERT INTO books.books
VALUES('Angel of Death',300,'19940302',2,2);

SELECT * FROM books.authors ba
INNER JOIN books.books bb ON ba.id=bb.author_id
INNER JOIN books.genres bg ON bg.id=bb.genre_id;

*/

/* 练习 2
-- 应该使用几个表?1

-- 应组合更多的 "结果集"? (子查询/集运算符/CTE/等...) ANTI SEMI JOIN (WHERE NOT EXISTS/EXCEPT)

-- 应该使用哪种聚合?否

-- 应使用哪种连接?否

-- 重命名哪一列?否

-- 过滤器应在何处使用?/ where/having,select/case/where

-- 是否应使用 "ORDER BY"?否

返回库存商品名称及其建议零售价
单价大于 20 且小于 100、
但不包括 "限量库存 "和空标签。

使用适当的 SET 运算符。

使用下表
    - Warehouse.StockItems

    
报告应显示以下列:
    - 库存物品名称
    - 建议零售价

*/

/* 版本 1 */

SELECT StockItemName,RecommendedRetailPrice FROMarehouse.StockItems
WHERE 单价 > 20 AND 单价  20 AND 单价  20 AND 单价  20 AND UnitPrice  20 AND 单价  YEAR(MIN(OrderDate)) THEN '日期:'+CAST(MIN(OrderDate) AS varchar(10))
    END AS [首次订购日期]
FROM Sales.Customers sc
INNER JOIN Sales.Orders so ON sc.CustomerID=so.CustomerID
GROUP BY sc.CustomerID,CustomerName
ORDER BY [首次订购日期] DESC;

/* 版本 2 */

SELECT CustomerName、
    案例
        WHEN 2016 = YEAR(MIN(OrderDate)) THEN 'Month: '+CAST(MONTH(MIN(OrderDate)) AS varchar(2))+', Day: '+CAST(DAY(MIN(OrderDate)) AS varchar(2))
        WHEN 2016 > YEAR(MIN(OrderDate)) THEN '日期:'+CAST(MIN(OrderDate) AS varchar(10))
    END AS [首次订购日期]
FROM Sales.Customers sc
INNER JOIN Sales.Orders so ON sc.CustomerID=so.CustomerID
GROUP BY CustomerName
ORDER BY [首次订购日期] DESC;


/* 版本 3 */

SELECT CustomerName、
    案例
        WHEN 2016 = YEAR(MIN(OrderDate)) THEN 'Month: '+CAST(MONTH(MIN(OrderDate)) AS varchar(2))+', Day: '+CAST(DAY(MIN(OrderDate)) AS varchar(2))
        WHEN 2016 > YEAR(MIN(OrderDate)) THEN '日期:'+CAST(MIN(OrderDate) AS varchar(10))
    END AS [首次订购日期]
FROM Sales.Customers sc
INNER JOIN Sales.Orders so ON sc.CustomerID=so.CustomerID
GROUP BY CustomerName
ORDER BY MIN(OrderDate) DESC;

/* 版本 4 */


WITH FirstOrder AS (
    SELECT CustomerID, MIN(OrderDate) AS MinOrderDate
    FROM Sales.Orders
    GROUP BY CustomerID
)
SELECT CustomerName
,(CASE
WHEN fo.MinOrderDate >= '20160101' AND fo.MinOrderDate < '20170101' THEN 'Month: ' + CAST(MONTH(fo.MinOrderDate) AS varchar(2)) + ', Day: ' + CAST(DAY(fo.MinOrderDate) AS varchar(2))
WHEN fo.MinOrderDate <'20160101' THEN 'DATE: ' + CAST(fo.MinOrderDate AS varchar)
END) AS '首次订购日期
FROM Sales.Customers c
INNER JOIN FirstOrder fo ON c.CustomerID=fo.CustomerID
ORDER BY [First Order Date] DESC;

/* 版本 5 */

SELECT CustomerName、
    条件
    WHEN(OrderDate BETWEEN '2016-01-01' AND '2016-12-31') THEN
        '月: ' + CAST(MONTH(OrderDate) AS varchar(2)) + ',日: ' + CAST(Date(OrderDate) AS varchar(2)) + '。
        ',日:'+ CAST(DAY(OrderDate) AS varchar(2))
    否则
        日期:' + CAST(OrderDate AS varchar(10))
    END AS '首次订购日期
FROM (
    SELECT CustomerID, MIN(OrderDate) AS 'OrderDate
    FROM Sales.Orders
    GROUP BY CustomerID) AS first_orders
INNER JOIN Sales.Customers c
    ON first_orders.CustomerID = c.CustomerID
ORDER BY
    订单日期 DESC


/* 练习 5
-- 应该使用几个表?   2

-- 更多的 "结果集 "应该合并吗?否

-- 应该使用哪种聚合?计数

-- 应该使用哪种连接?  内层

-- 重命名?重命名

-- 过滤器应在何处使用?/ where/having,select/case/case

-- 是否应使用 "ORDER BY"?否

在销售模式中编写一个名为 PickupReport 的视图。

它应使用模式绑定。

使用该视图按 PostalCityID 返回已取货和未取货的订单
按未取货数量降序排列。

使用以下表格
    - 销售订单
    - 销售.客户

    
报告应显示以下列:
    [邮政编号]:Sales.Customers 中的 PostalCityID 列
    [已取货]:已取货的订单数
	[未取货]:尚未取货的订单数
*/
执行
创建或更改视图 Sales.PickupReport
模式绑定
模式绑定
SELECT PostalCityID、
    COUNT(CASE
        WHEN PickingCompletedWhen IS NOT NULL THEN 1
    END) AS [已提取]
   ,COUNT(CASE
        WHEN PickingCompletedWhen IS NULL THEN 0
    END) AS [未提取]
FROM Sales.Customers sc
INNER JOIN Sales.Orders so
ON sc.CustomerID=so.CustomerID
GROUP BY PostalCityID;
返回

/* 版本 2 */
创建或 ALTER VIEW Sales.PickupReport
模式绑定
模式绑定
SELECT c.PostalCityID AS [Postal ID], COUNT(o.PickingCompletedWhen) AS [Picked up].
		, COUNT(o.PickingCompletedWhen) AS [已取货]
		,COUNT(*)-COUNT(o.PickingCompletedWhen) AS [Not picked up]
FROM Sales.Customers AS c
INNER JOIN Sales.Orders AS o
ON c.CustomerID=o.CustomerID
GROUP BY c.PostalCityID;
返回
/* SHOWCASE */

SELECT * FROM Sales.PickupReport
ORDER BY [Not picked up] DESC;

/* 练习 6
-- 应该使用几个表?  2

-- 应该组合更多的 "结果集"????

-- 应使用哪种聚合方式?COUNT/SUM

-- 应使用哪种连接?  左

-- 重命名?是

-- 过滤器应在何处使用?/何处/有,选择/案例/格式

-- 是否应使用 "ORDER BY"?否

在销售模式中编写一个名为 SupplierReport 的视图。

应使用模式绑定。

显示供应商及其产品占总销售产品的比例。
如果供应商没有产品,则应显示 0.00

使用以下表格:
    - 采购.供应商
    - 仓库.库存项目

    
报告应显示以下列:
    [供应商]: 采购.供应商中的供应商名称
    [总百分比]:该供应商售出的物品占所有供应商售出的所有物品的两位数百分比。
        与所有供应商售出的所有物品的百分比


*/
返回
创建或更改视图 Sales.SupplierReport
模式绑定
AS
   SELECT
     供应商名称 AS [供应商]
    ,FORMAT(COUNT(ws.StockItemID)/(SUM(COUNT(ws.StockItemID)) OVER() *1.0),'P2') AS [总百分比]
    FROM Purchasing.Suppliers ps
    LEFT JOIN Warehouse.StockItems ws
    ON ps.SupplierID=ws.SupplierID
    GROUP BY SupplierName
执行

SELECT
     供应商名称
    ,FORMAT(COUNT(ws.StockItemID)/(maxstockitem*1.0),'P2') AS [总百分比].
FROM Purchasing.Suppliers ps
    CROSS APPLY (SELECT COUNT(*) AS maxstockitem FROM Warehouse.StockItems) AS temptable
LEFT JOIN Warehouse.StockItems ws
ON ps.SupplierID=ws.SupplierID
GROUP BY SupplierName,maxstockitem

 --版本 2

SELECT
     供应商名称
    ,FORMAT(COUNT(ws.StockItemID)/((SELECT COUNT(StockItemID) FROM Warehouse.StockItems)*1.0),'P2') AS [Total percentage] (总百分比)
FROM Purchasing.Suppliers ps
LEFT JOIN Warehouse.StockItems ws
ON ps.SupplierID=ws.SupplierID
GROUP BY SupplierName

--版本 3
SELECT
     供应商名称
    ,COUNT(ws.StockItemID)
    ,FORMAT(COUNT(ws.StockItemID)/(SUM(COUNT(ws.StockItemID)) OVER() *1.0),'P2') AS [Total percentage] (总百分比)
FROM Purchasing.Suppliers ps
LEFT JOIN Warehouse.StockItems ws
ON ps.SupplierID=ws.SupplierID
GROUP BY SupplierName

/* 练习 7
-- 应使用几个表?  2

-- 应组合更多的 "结果集"?

-- 应使用哪种聚合?

-- 应使用哪种连接?  

-- 应使用哪一列?

-- 过滤器应在何处使用?/ where/having、select/case/......。

-- 应使用 "ORDER BY"?

在仓库模式中创建一个内联表值函数,名为 StockUsbReport
返回所有 USB 相关记录。

该函数应使用一个参数:
    - nvarchar(10) 的 @stockitemname

返回库存项目、零售价和总库存价
根据单价和参数值计算的数量。

使用以下表格:
    - 仓库.库存物品持有量
    - Warehouse.StockItems

报告应显示以下列:
    [库存项目名称]:Warehouse.StockItems 中的 StockItemName 列
    [零售价]:Warehouse.StockItems 中的 RecommendedRetailPrice 列
    [当前库存值]:单价乘以物品数量
        数值应如下所示: $1,234,567

展示如何使用表值函数!

*/
执行
CREATE OR ALTER FUNCTION Warehouse.StockUsbReport( @stockitemname nvarchar(10))
返回表
作为
返回
    SELECT
         库存项目名称 AS [库存项目名称]
        建议零售价 AS [零售价]
        ,FORMAT((QuantityOnHand*UnitPrice),'C0','en-US') AS [当前库存值]
    FROM Warehouse.StockItemHoldings wsh
    INNER JOIN Warehouse.StockItems wsi
    ON wsh.StockItemID=wsi.StockItemID
    WHERE StockItemName LIKE '%'+@stockitemname+'%')
    )
返回

/* SHOWCASE */

SELECT * FROM Warehouse.StockUsbReport('USB');

----
SELECT
     库存项目名称
    建议零售价
    ,FORMAT((QuantityOnHand*UnitPrice),'C0','en-US') FROM Warehouse.StockItemHoldings wsh
INNER JOIN Warehouse.StockItems wsi
ON wsh.StockItemID=wsi.StockItemID
其中 StockItemName LIKE '%'+'USB'+'%'

/* 版本 2 */

选择
     库存物品名称
    建议零售价
    ,FORMAT((QuantityOnHand*UnitPrice),'$###,###,###') FROM Warehouse.StockItemHoldings wsh
INNER JOIN Warehouse.StockItems wsi
ON wsh.StockItemID=wsi.StockItemID



/* 练习 8 */
----------------------------------------------------------------------------------------------
 DROP TABLE IF EXISTS Application.Fruits;
    删除表
    创建表 Application.Fruits
    (
        FruitId int NOT NULL IDENTITY(1,1) PRIMARY KEY、
        FruitName nvarchar(50) NOT NULL
    );

    INSERT INTO Application.Fruits (FruitName)
        VALUES ('banana'),('banana'),('apple'),('peach'),('peach'),('peach');
/*
在应用程序模式中创建名为 DeleteFruit 的存储过程。

存储过程将删除 Application.Fruits 表中的记录。

存储过程应接受必选列作为参数:
    - @FruitName nvarchar(50)

存储过程应在成功时输出
	-@FruitsDeleted int

使用错误处理和错误处理函数显示错误编号、
错误信息和错误严重程度!
同时打印出一条信息 "删除水果失败!"。

如果处理过程没有出错,但删除的行数为 0、
则会引发错误,并显示以下信息:
未找到此类水果",严重性级别为 16。

展示如何使用存储过程!
不要在 T-SQL 查询中将受影响的行数显示为信息消息。
*/
执行
创建或 ALTER 存储过程 Application.DeleteFruit (
     @FruitName NVARCHAR(50)
    ,@FruitsDeleted INT OUTPUT
    )
AS
    set nocount on;
    开始尝试
        DELETE Application.Fruits
        WHERE FruitName=@FruitName;
        SET @FruitsDeleted= @@ROWCOUNT;
        IF @FruitsDeleted = 0
            开始
                RAISERROR('No such fruit found',16,7);
            结束
    结束尝试
    BEGIN CATCH
        PRINT '删除水果失败!';
        PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS NVARCHAR(10));
        PRINT '错误信息:'+ ERROR_MESSAGE();
        PRINT 'Error severity: ' + CAST(ERROR_SEVERITY() AS NVARCHAR(10));
    结束 CATCH
返回

/* 版本 2 */

执行
创建或 ALTER PROCEDURE Application.DeleteFruit (
    @FruitName nvarchar(50)、
    @FruitsDeleted int OUTPUT)
作为
开始
    set nocount on;
    set xact_abort on;
    BEGIN TRY
        开始事务;
        DELETE FROM Application.Fruits WHERE FruitName = @FruitName;
        SET @FruitsDeleted = @@ROWCOUNT;
        IF @FruitsDeleted = 0
        开始
            RAISERROR('No such fruit found', 16, 1);
        结束
        提交事务;
    结束尝试
    开始捕获
        DECLARE @ErrorNumber int = ERROR_NUMBER();
        DECLARE @ErrorMessage nvarchar(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity int = ERROR_SEVERITY();
        RAISERROR('删除水果失败!错误编号:%d,错误信息:%s,错误严重性:%d', 16, 1, @ErrorNumber, @ErrorMessage, @ErrorSeverity);
        回滚事务;
    结束 CATCH
结束;
返回


/* SHOWCASE */

DECLARE @outputSP INT;
EXEC Application.DeleteFruit 'banana',@FruitsDeleted = @outputSP OUTPUT;
SELECT @outputSP;
删除
DECLARE @outputSP INT;
EXEC Application.DeleteFruit 'banana',@FruitsDeleted = @outputSP OUTPUT;
SELECT @outputSP;

第一次考试

/*
练习 #1:
设计一个假想餐厅数据库的小型子集。

在餐厅数据库中,至少要存储以下数据:

特许经营
店铺
特许经营与店铺之间的关系
一家店铺只能拥有一种特许经营权。

至少必须存储以下数据:

企业名称(也可存储非英文字符)
特许经营类型(计划在此存储简要说明--如产品、制造等)
店铺地址
商店开业日期
商店是否全天候营业(真或假)
创建具有所需数据列和适当数据类型的相关表格。

至少创建以下约束:

PRIMARY KEY
FOREIGN KEY
如有必要,请指定其他约束。

创建表格脚本
*/
创建 DATABASE Restaurants;
执行
USE Restraurants;
创建模式 rt
创建模式 rt;
开始

DROP TABLE IF EXISTS RT.FranchiseTypes;
CREATE TABLE RT.FranchiseTypes (
    FranchiseTypeID INT IDENTITY(1,1) PRIMARY KEY、
    FranchiseName NVARCHAR(100) NOT NULL
        CONSTRAINT UK_FranchiseName UNIQUE
);

DROP TABLE IF EXISTS RT.Franchises;
CREATE TABLE RT.Franchise (
    FranchiseID INT IDENTITY(1,1) PRIMARY KEY、
    BusinessName NVARCHAR(100) NOT NULL、
    FranchiseTypeID INT NOT NULL、
    CONSTRAINT FK_Franchises_FranchiseTypeID FOREIGN KEY (FranchiseTypeID) REFERENCES RT.FranchiseType (FranchiseTypeID)
);
DROP TABLE IF EXISTS RT.Stores;
CREATE TABLE RT.Store (
    StoreID INT IDENTITY(1,1) PRIMARY KEY、
    FranchiseID INT NOT NULL、
    商店地址 NVARCHAR(300) NOT NULL、
    开业日期 DATE NOT NULL、
    IsOpen24Hours BIT NOT NULL、
    CONSTRAINT FK_Store_Franchise FOREIGN KEY (FranchiseID)
        REFERENCES RT.Franchise(FranchiseID)
);

/*
练习 #2
创建一份包含所有供应商、其交易数量和评论的报告。

返回所有供应商,无论其交易数量是多少(零或更多)

按供应商名称和备注对结果进行分组。

按交易数量降序排列报告。

使用以下表格:

采购.供应商
采购.供应商交易
报告应显示以下列:

[供应商名称]:采购.供应商表中的供应商名称
[交易次数]:每个供应商的交易次数
并非所有供应商都有成功交易,如果供应商没有成功交易,则显示 0(零)
[备注]:供应商内部备注/ InternalComments/
并非所有供应商都有注释,如果供应商没有注释,则显示 "N/A
*/

选择 *
from information_schema.columns
WHERE COLUMN_NAME LIKE '%transaction%' */ SELECT * from information_schema.columns.

SELECT * FROM Purchasing.Supplier
SELECT * FROM Purchasing.SupplierTransactions;

SELECT
    s.SupplierName AS [供应商名称], st.SupplierTransactionID
从
    采购.供应商 s
    LEFT JOIN Purchasing.SupplierTransactions st ON s.SupplierID = st.SupplierID


SELECT
    s.SupplierName AS [供应商名称], COUNT(st.SupplierTransactionID)
FROM
    采购.供应商 s
    LEFT JOIN Purchasing.SupplierTransactions st ON s.SupplierID = st.SupplierID
GROUP BY
    s.SupplierName

SELECT s.SupplierName AS [Supplier name]、
    ISNULL(COUNT(st.FinalizationDate), 0) AS [交易次数]、
    ISNULL(s.InternalComments, 'N/A') AS [备注]
FROM Purchasing.Suppliers s
    LEFT JOIN Purchasing.SupplierTransactions st ON s.SupplierID = st.SupplierID
GROUP BY s.SupplierName, s.InternalComments
ORDER BY [交易次数] DESC;

SELECT s.SupplierName AS [供应商名称]、
    COUNT(st.SupplierTransactionID) AS [交易次数]、
    ISNULL(s.InternalComments, 'N/A') AS [备注]
FROM Purchasing.Suppliers s
    LEFT JOIN Purchasing.SupplierTransactions st ON s.SupplierID = st.SupplierID
GROUP BY s.SupplierName, s.InternalComments
ORDER BY [交易数量] DESC;
-----

USE WideWorldImporters;
GO
SELECT s.SupplierName AS [供应商名称]、
    ISNULL(COUNT(st.SupplierTransactionID), 0) AS [交易次数]、
    ISNULL(s.InternalComments, 'N/A') AS [备注]
FROM Purchasing.Suppliers s
    LEFT JOIN Purchasing.SupplierTransactions st ON s.SupplierID = st.SupplierID
GROUP BY s.SupplierName, s.InternalComments
ORDER BY [交易次数] DESC;


/*
练习 #3
创建一份报告,列出供应商参考信息/SupplierReference/、其预期总外部价格(单价乘以订购的外部产品/OrderedOuters*ExpectedUnitPricePerOuter/)以及每个 "供应商参考信息 "的订购外部产品总数/OrderedOuters/。
以及每个 "供应商参考 "的订购外发件总数/OrderedOuters/。

仅返回预计交货日期大于 2014-03-31 的供应商。

按供应商编号对结果进行分组。

按 [供应商编号] 升序排列报告。

使用任何类型的美钞格式返回价格。

使用以下表格:

采购.采购订单行
采购.采购订单
报告应显示以下列:

[供应商参考信息] -> 采购.采购订单表中的供应商参考信息
[预期总外层价格] -> 按外层计算的预期价格
[已订购外发订单] -> 已订购外发订单总数
*/

SELECT * FROM Purchasing.PurchaseOrderLines
SELECT * FROM Purchasing.PurchaseOrders;

USE WideWorldImporters;
GO
SELECT
    po.SupplierReference AS [供应商参考]、
    FORMAT(SUM(pol.OrderedOuters * pol.ExpectedUnitPricePerOuter), 'C') AS [Expected overall outer price]、
    SUM(pol.OrderedOuters)AS[排序外价格]。
FROM
    Purchasing.PurchaseOrderLines pol
    INNER JOIN Purchasing.PurchaseOrders po ON pol.PurchaseOrderID = po.PurchaseOrderID
所在
    po.预计交货日期 > '2014-03-31
GROUP BY
    po.SupplierReference
ORDER BY
    [供应商编号] ASC;


/*
练习 #4
创建每个客户最新订单的报告,并显示该订单与某个时间点的比较时间。

首先声明一个保存日期值的变量,并将当前日期设置为该变量。

然后声明另一个保存字符串的变量,并将其值设置为 "%Toys%"。

您的报告中不应包含任何带有已创建字符串变量值的客户名称。

使用以下表格:

销售订单
销售.客户
按 [客户名称] 降序排列报告。

报告应显示以下列:

[客户名称]:Sales.Customers 表中的 CustomerName 列
[已过天数]:创建日期变量与 Sales.Orders 表中最新 Orderdate 之间的天数差。
*/
USE WideWorldImporters;
执行

DECLARE @currentdate AS DATE = GETDATE();
DECLARE @excludestring AS NVARCHAR(50) = '%Toys%';

SELECT
    c.CustomerName AS [客户名称]、
    DATEDIFF(DAY, o.OrderDate, @currentdate) AS [已通过天数]
FROM
    销售订单 o
    INNER JOIN Sales.Customers c ON o.CustomerID = c.CustomerID
所在
    c.CustomerName NOT LIKE @excludeString
    AND o.OrderDate = (
        SELECT MAX(OrderDate)
        FROM Sales.Orders
        WHERE CustomerID = o.CustomerID
    )
ORDER BY
    [客户名称] DESC;

/*
练习 #5:
在销售模式中创建一个与模式绑定的标量用户定义函数,名为 SmallestOrderQuantity。

将订单 ID 作为参数传递。

函数应返回一个 Int 类型的值(数量)。

使用以下表格:

Sales.OrderLines
函数应接受一个参数:

@orderid int
展示如何在查询中使用标量用户定义函数!
*/
SELECT TOP 1 Quantity
    FROM Sales.OrderLines
    WHERE OrderID = 1
    order by quantity desc;

SELECT MIN(Quantity)
    FROM Sales.OrderLines
    WHERE OrderID = 1

USE WideWorldImporters;
执行
CREATE OR ALTER FUNCTION Sales.SmallestOrderQuantity(@orderid int)
返回 int
与模式绑定
作为
开始
    DECLARE @quantity int;
    SELECT @quantity = MIN(Quantity)
    FROM Sales.OrderLines
    WHERE OrderID = @orderid;
    返回 @quantity;
结束;
GO


-- SHOWCASE
SELECT OrderID, Sales.SmallestOrderQuantity(OrderID) AS SmallestQuantity
FROM Sales.OrderLines
ORDER BY OrderID;

/*
练习 #6:
在销售模式中创建一个具有模式绑定的视图,名为 CustomerYearlyOrder。

返回 "客户 "及其关联的年度订单号以及订单成功率。

当取货完成时,订单即为成功。

按客户名称和订单年份分组。

使用以下表格:

销售.客户
销售订单
报告应显示以下列:

[客户名称]:Sales.Customers 中的客户名称
[订单年份]:来自销售订单的订单日期年份
[订单]:来自销售订单的订单数量
[已完成拣货]:从 Sales.Orders / PickingCompletedWhen / 中提取的已拣货订单数。
[差值]: 订单列和拣货完成列之间的差值
展示如何在查询中使用视图!
*/
返回
创建或更改视图 Sales.CustomerYearlyOrder
模式绑定
视图
SELECT c.CustomerName AS [Customer Name]、
       YEAR(o.OrderDate) AS [Order Year]、
       COUNT(*) AS [订单]、
       COUNT(o.PickingCompletedWhen) AS [已完成拣选]、
       COUNT(*) - COUNT(*) AS [差额]
FROM Sales.Customers c
JOIN Sales.Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName, YEAR(o.OrderDate);
返回
选择 *
FROM Sales.CustomerYearlyOrder


USE WideWorldImporters;
执行
创建或更改视图 Sales.CustomerYearlyOrder
模式绑定
模式绑定
SELECT c.CustomerName AS [Customer Name]、
       YEAR(o.OrderDate) AS [Order Year]、
       COUNT(o.OrderID) AS [订单]、
       SUM(
           CASE WHEN o.PickingCompletedWhen IS NOT NULL THEN 1
           ELSE 0 END) AS [拣选完成]、
       COUNT(o.OrderID) - SUM(CASE WHEN o.PickingCompletedWhen IS NOT NULL THEN 1 ELSE 0 END) AS [Difference] (差值)
FROM Sales.Customers c
JOIN Sales.Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName, YEAR(o.OrderDate);
返回
--显示案例
选择 *
FROM Sales.CustomerYearlyOrder;

/*
练习 #7:
在应用程序模式中创建一个名为 CityReport 的内联表值函数。

该函数应使用两个参数:

@salesterritory 为 nvarchar(50),默认值为 "新英格兰
@bigcitylimit 为 decimal(18,2),默认值为 100000.00
返回销售区域、所属城市以及城市人口,但仅限于已知城市人口的城市(不会丢失)!

使用以下表格:

Application.StateProvinces
应用程序.城市
报告应显示以下列:

[销售地区]:来自 Application.StateProvinces 的 SalesTerritory 列
[州]:Application.StateProvinces 中的 StateProvinceName 列
[城市]:来自 Application.Cities 的 CityName 列
[城市人口]: Application.Cities 中的 LatestRecordedPopulation 列
[大城市百分比]:城市最新记录人口占 @bigcitylimit 参数值的百分比 显示精度为 2 位数的百分比值
展示如何使用表值函数!
*/
SELECT * FROM Application.StateProvinces
SELECT * FROM Application.Cities

SELECT sp.SalesTerritory AS [Sales Territory]、
        sp.StateProvinceName AS [State]、
        c.CityName AS [城市]、
        c.LatestRecordedPopulation AS [City Population]、
        CAST((c.LatestRecordedPopulation / 100000.00 * 100) AS decimal(6,2)) AS [Big City Percentage] (大城市百分比)
FROM Application.StateProvinces sp
INNER JOIN Application.Cities c ON sp.StateProvinceID = c.StateProvinceID
--其中 sp.SalesTerritory = 'New England' AND c.LatestRecordedPopulation IS NOT NULL



USE WideWorldImporters
返回

CREATE OR ALTER FUNCTION Application.CityReport (@salesterritory nvarchar(50) = 'New England'、
                                        @bigcitylimit decimal(18,2) = 100000.00)
返回表
作为
返回
(
    SELECT sp.SalesTerritory AS [Sales Territory]、
        sp.StateProvinceName AS [State]、
        c.CityName AS [城市]、
        c.LatestRecordedPopulation AS [City Population]、
        CAST((c.LatestRecordedPopulation / @bigcitylimit * 100) AS decimal(18,2)) AS [Big City Percentage] (大城市百分比)
    FROM Application.StateProvinces sp
    INNER JOIN Application.Cities c ON sp.StateProvinceID = c.StateProvinceID
    WHERE sp.SalesTerritory = @salesterritory AND c.LatestRecordedPopulation IS NOT NULL
)
返回

选择 *
FROM Application.CityReport(default,default);
SELECT *
FROM Application.CityReport('Plains',1);


/*
练习 #8:
创建下表

DROP TABLE IF EXISTS Application.LogAudit;
删除
创建表 Application.LogAudit
(
    LogAuditId int NOT NULL IDENTITY(1,1) PRIMARY KEY、
    LogData nvarchar(50) NOT NULL
);
使用 INSERT 语句向表中插入记录:

INSERT INTO Application.LogAudit (LogData) VALUES ('This is a log data!');
在 Application 模式中创建名为 DeleteLogData 的存储过程。

存储过程将根据作为参数传递的 LogAuditId 从 Application.LogAudit 表中删除一条记录。

存储过程应接受必选列作为参数:

@logauditid int
它还应接受一个 OUTPUT 参数:

@deletedlogdata nvarchar(50)
使用错误处理和错误处理函数显示错误编号和错误信息!

如果删除成功,则在 OUTPUT 参数中返回已删除的 LogData 值!

如果发生错误或删除的行数为 0,则处理该错误:

打印:'删除日志审计失败!'。
打印: '错误编号:'和错误编号
打印:'错误信息:'和错误信息
返回值为-1
在 T-SQL 查询中不显示受影响的行数作为信息消息。

展示如何使用存储过程!
*/

DELETE FROM Application.LogAudit
        WHERE LogAuditId = 10;

SELECT * FROM Application.LogAudit

 SELECT LogData
        FROM Application.LogAudit
        WHERE LogAuditId = 3


DECLARE @deletedlogdata nvarchar(50);
SELECT @deletedlogdata = LogData
        FROM Application.LogAudit
        WHERE LogAuditId = 2
SELECT @deletedlogdata

USE WideWorldImporters;
执行

DROP TABLE IF EXISTS Application.LogAudit;
删除表
创建表 Application.LogAudit
(
    LogAuditId int NOT NULL IDENTITY(1,1) PRIMARY KEY、
    LogData nvarchar(50) NOT NULL
);

INSERT INTO Application.LogAudit (LogData) VALUES ('This is a log data!');


执行
CREATE OR ALTER PROCEDURE Application.DeleteLogData (
    @logauditid int、
    @deletedlogdata nvarchar(50) OUTPUT
)
AS
开始
    set nocount on;

    开始尝试
        SELECT @deletedlogdata = LogData
        FROM Application.LogAudit
        WHERE LogAuditId = @logauditid;

        DELETE FROM Application.LogAudit
        WHERE LogAuditId = @logauditid;
        如果 @@rowcount = 0
        开始
            --throw 55555, 'rowcount=0', 1;
            raiserror ('rowcount=0', 16, 1);
        结束
    结束尝试
    BEGIN CATCH
        PRINT '删除日志审计失败!
        PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
        PRINT 'Error message: ' + ERROR_MESSAGE();
        SET @deletedlogdata = NULL;
        返回 -1;
    结束 CATCH
结束;
GO
--SHOWCASE
DECLARE @deletedlogdatavar nvarchar(50);
EXEC Application.DeleteLogData @logauditid = 10, @deletedlogdata = @deletedlogdatavar OUTPUT;
SELECT @deletedlogdatavar AS Deleted;
GO
DECLARE @deletedlogdatavar nvarchar(50);
EXEC Application.DeleteLogData @logauditid = 1, @deletedlogdata = @deletedlogdatavar OUTPUT;
SELECT @deletedlogdatavar AS Deleted;
GO

试用

hsg-reader-sqlserver-final-trial-exam

重要

  • 您可以使用任何在线资源,但请单独工作!
  • 分叉该 repo,然后将分叉的 repo 克隆到自己的机器上
  • 使用不含 BOM 的 UTF8 字符编码
  • 不要只是复制粘贴答案和解决方案,而要使用自己的话
  • 没有自动交易评估。
  • 任务 4 需要使用 mongoDB,可在学习了 NoSQL 材料后解决
  • 对于任务 4,您可以在系统中安装 mongoDB

任务 #1:

一家领先的快餐连锁店要求您创建一个销售其产品的外卖公司数据库。

在这个 "宅急送 "数据库中,您现在必须设计其中的一部分。

应处理以下数据

  • 商店(商店详细信息)
  • 菜单(商店现有商品目录)
  • 表格之间的联系

应处理以下数据

  • 商店 ID(获取唯一标识符)
  • 当前活动目录 ID
  • 商店上一次成功收到产品目录是什么时候?
  • 上一次成功的目录是什么时候
  • 从固定清单中选择送货公司
  • 开放时间
  • 商店 7/24 日营业

一个商店可以有多个目录,但只有一个商店可以使用一个目录(通过 ID)。一家商店暂时只能与一家供应商合作。

创建具有正确列类型的正确数据库表。

至少使用以下限制条件:

  • 主键
  • FOREIGN KEY

必要时定义附加约束和索引!

注意正常化!

发回黑板脚本!

创建 DATABASE Homedelivery;
创建
-- 商店、目录、供应商
-- 商店->目录 1:N, 商店->供应商 1:N
USE Homedelivery;
开始


DROP TABLE IF EXISTS dbo.catalogs,dbo.stores,dbo.transporters;
删除

创建表 dbo.transporters(
	 id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
	,transp_name NVARCHAR(150) NOT NULL UNIQUE
);

创建表 dbo.stores(
	 id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
	存储名 NVARCHAR(150) NOT NULL UNIQUE
	,运输商_id INT
		CONSTRAINT FK_stores_transporterid FOREIGN KEY REFERENCES dbo.transporters(id) ,opening_start TIME
	,opening_start时间
	,opening_end时间
	,isOpenAllday BIT NOT NULL
	CONSTRAINT CK_stores_openingcheck
		CHECK ( ( isOpenAllday = 1 AND opening_start IS NULL AND opening_end IS NULL) OR
		( isOpenAllday = 0 AND opening_start IS NOT NULL AND opening_end IS NOT NULL )
		)
	, CONSTRAINT CK_stores_openingstartend CHECK ( opening_start != opening_end )
);

CREATE TABLE dbo.catalogs (
	 id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
	,catalog_name NVARCHAR(150) NOT NULL UNIQUE
	,catalog_success_created DATETIME2 NOT NULL
	,last_success_transport DATETIME2 NOT NULL
	,usedbystore_id INT
		CONSTRAINT FK_catalogs_storeid FOREIGN KEY REFERENCES dbo.stores(id) ,isActive BIT NOTULL.
	,isActive BIT NOT NULL
		INDEX idx_catalogs_activeforstore UNIQUE (usedbystore_id,isActive) WHERE isActive=1
	CONSTRAINT CK_catalog_successtime CHECK (last_success_transport >catalog_success_created ); CONSTRAINT CK_catalog_successtime CHECK (last_success_transport >catalog_success_created ).
);

/*
INSERT INTO dbo.transporters
VALUES ('corleone family'),('ngdrangheta'),('bratva');
INSERT INTO dbo.stores
VALUES('Jimmy kebap',2,'09:10','09:10',0);
INSERT INTO dbo.stores
VALUES('Jimmy kebap',2,'09:10','09:15',0);
INSERT INTO dbo.stores
VALUES('videki robi',2,NULL,NULL,0);
INSERT INTO dbo.stores
VALUES('videki robi',2,'18:00','07:00',0);
INSERT INTO dbo.stores
VALUES('korcsma',2,NULL,NULL,1);
INSERT INTO dbo.catalogs
VALUES('BBQ Burger','2021-11-10','2021-11-11',2,0);
INSERT INTO dbo.catalogs
VALUES('Vega Burger','2021-11-10','2021-11-11',2,1);
INSERT INTO dbo.catalogs
VALUES('萨拉米汉堡','2021-11-10','2021-11-11',2,1);
*/

任务 #2:

使用 WideWorldImporters 示例数据库!

为 Application.Countries 表编写一个简单的存储过程,返回大陆名称和相关人口总数。

应用程序模式中的存储过程应称为 GetContinentRanking。

存储过程需要一个参数: - @rank tinyint:默认为 1

整数类型参数的值是存储过程根据人口数量排名返回的大陆数量。

例如

  • 如果参数设置为 3,则应返回人口第三多的大洲的名称和人口。
  • 如果参数设置为 5,则应返回人口最多的第五大洲的名称和人口。
  • 如果参数设置为 1,则应返回人口最多的大洲的名称和人口。

请使用下面的表格:

  • 应用.国家

返回包含以下列的存储过程

  • [排名]:排名值
  • [大陆名]: Application.Countries 表中的大陆列
  • [人口]:汇总 Application.Countries 表中的 LatestRecordedPopulation 列

演示如何使用存储过程!

USE WideWorldImporters;
GO
CREATE OR ALTER PROCEDURE Application.GetContinentRanking
(@rank TINYINT=1)
AS
	set nocount on;
	SELECT @rank AS ranking
		,大陆 AS [大陆名称]
		SUM(LatestRecordedPopulation) population
	FROM Application.Countries
	GROUP BY 洲
	ORDER BY SUM(LatestRecordedPopulation) DESC
	OFFSET (@rank-1) ROWS FETCH NEXT 1 ROWS ONLY;
返回

/*
存储过程需要一个参数: - @rank tinyint:默认值为 1
存储过程应返回以下列:
    [排名]:排名值
    [大陆名称]:Application.Country 表中的大陆列
    [人口]:Application.Country 表中汇总的 LatestRecordedPopulation 列。
*/

/*
演示如何使用存储过程!
*/

EXEC Application.GetContinentRanking DEFAULT;

EXEC Application.GetContinentRanking 3;

-- 窗口函数版本
GO
CREATE OR ALTER PROC Application.GetContinentRanking
    @rank TINYINT = 1
作为
开始
    SELECT ranking, continent_name, population
    FROM (
        SELECT
            RANK() OVER (ORDER BY SUM(LatestRecordedPopulation) DESC) ranking、
            Continent AS 'continent_name'、
            SUM(LatestRecordedPopulation) AS 'population
        FROM Application.Countries
        GROUP BY Continent) AS continent_population
    WHERE ranking = @rank
结束;
返回

EXEC Application.GetContinentRanking DEFAULT;

EXEC Application.GetContinentRanking 3;

任务 #3:

使用 WideWorldImporters 示例数据库!

编写包含以下内容的 T-SQL 脚本:

  1. 它创建了一个名为 "SampleLogin "的 SQL 登录名和密码,并将 WideWorldImporters 设置为默认数据库。
  2. 在 WideWorldImporters 数据库中,为上次登录创建一个 "SampleUser "用户。
  3. 将用户添加到 "外部销售 "数据库角色中。
  4. 在 Application.People 表中为该角色添加 SELECT 权限。
  5. 通过冒充用户 "SampleUser "并使用 Application.People 表中的 SELECT 查询来测试数据读取。
  6. 将执行上下文恢复为前一个用户。
  7. 获取当前用户的名称。

每个任务都是一条 T-SQL 语句。

将以下内容复制到 github:

  • 脚本文件
USE WideWorldImporters;
返回
CREATE LOGIN [SampleLogin] WITH PASSWORD=N'password'、
DEFAULT_DATABASE=[WideWorldImporters], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
运行
CREATE USER SampleUser FOR LOGIN [SampleLogin];
执行
ALTER ROLE [External Sales] ADD MEMBER [SampleUser];
执行
GRANT SELECT ON Application.People TO [External Sales];
执行
EXECUTE AS USER='SampleUser';
执行
SELECT TOP 5 * FROM Application.People;
--选择 user_name();
执行
返回
返回
选择 user_name();

任务 #5:

使用以下脚本重现死锁!

打开 3 个新的查询窗口!

在高亮显示的窗口中依次运行以下脚本:

  1. 窗户
删除 表格 如果存在 应用.僵局;
去
创建 表格 应用.死锁
(
    DeadlockId int 非空 主键
);
开始 交易
INSERT INTO 应用.僵局 (deadlockId) 价值 (1);
  1. 窗户
开始 交易
INSERT INTO 应用.僵局 (deadlockId) 价值 (2);
  1. 窗户
选择 死锁标识  应用.僵局 地点 死锁标识 = 2;
  1. 窗户
开始 交易
INSERT INTO 应用.僵局 (deadlockId) 价值 (3);
  1. 窗户
选择 死锁标识  应用.僵局 地点 死锁标识 = 3;
  1. 窗户
选择 死锁标识  应用.僵局 地点 死锁标识 = 1;

3 个窗口中有一个返回红色死锁错误信息。

ROLLBACK TRAN 命令可用于停止任何仍在运行的指令。

  1. 为您重现的死锁收集死锁图 xml!

  2. 将死锁图 xml 保存为 .XDL 文件格式!

  3. 用图形显示死锁图形(使用自己选择的工具)并截图。

  4. 配置数据库,避免再次发生这种死锁!

将以下内容复制到 github,并对文件编号如下::

  • 1: .XDL 文件
  • 2:截图图像文件
  • 3:T-SQL 语句
使用 DBAHEALTH;
去

--EXEC dbo.sp_BlitzLock @EndDate='20230423', @StartDate='20230410';

ALTER DATABASE [WideWorldImporters] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
开始

------------------------------------ BAL
--- 1. ----------- left session:
BEGIN TRAN t1;
UPDATE dbo.ExamLefty SET Numbers = Numbers + 1;
--- 4. ----------- left session:
SELECT * FROM dbo.ExamRighty;

ROLLBACK TRAN

------------------------------------ JOBB
--- 2:
BEGIN TRAN t2;
UPDATE dbo.ExamRighty SET Numbers = Numbers + 1;
--- 3. -- 右侧会话:
SELECT * FROM dbo.ExamLefty;

任务 #6:

使用以下脚本创建 SQLTestDB 表!

使用 [master]
开始

创建数据库 [SQLTestDB]
开始

USE [SQLTestDB]
运行
创建 表格 SQLTest
   (
      ID INT 非空 主键,
      c1 VARCHAR(100) 非空,
      dt1 DATETIME 非空 DEFAULT getdate()
   );
返回

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) 价值 (1, '测试1')
INSERT INTO SQLTest (ID, c1) 价值 (2, '测试2')
INSERT INTO SQLTest (ID, c1) 价值 (3, '测试3')
INSERT INTO SQLTest (ID, c1) 价值 (4, '测试4')
INSERT INTO SQLTest (ID, c1) 价值 (5, '测试5')
GO

选择 *  SQLTest
开始

SQLTestDB 数据库需要根据业务要求进行时间点恢复。

编写包含以下内容的 T-SQL 脚本:

  1. 根据业务需求配置数据库恢复模式。

  2. 备份前,它会跳过对非聚类索引的检查,对 SQLTestDB 数据库执行完整的数据库完整性检查。

  3. 创建具有固定路径和文件名为 SQLTestDB_Full.bak 的完整备份。

  4. 在固定路径上压缩差异备份,文件名为 SQLTestDB_Diff.bak。

  5. 在固定路径上创建带压缩和校验的日志备份,文件名为 SQLTestDB_Log.trn。

  6. 检查备份,查看备份集是否完整或可读(但不会还原)。

每个任务都是一条 T-SQL 语句。语句只应包含所要求的选项/设置!

  1. 使用 SQL Agent 作业(也可以使用 Ola Hallengren 作业)安排上述备份语句的时间,并在考虑到以下因素的情况下创建适当的备份计划:
    • 数据库大小可达数百 GB
    • 优化灾难恢复
    • 在灾难中最多可丢失 5 分钟的数据
    • 计划表的命名应明确指出备份类型

在 SQL Server Management Studio 中,从备份计划的 "任务计划属性 "对话框中截图。

将以下内容复制到 github:

  • 脚本文件
  • 截图文件
使用 [master]
GO
ALTER DATABASE [SQLTestDB] SET RECOVERY FULL;
GO
--https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver15

DBCC CHECKDB (SQLTestDB, NOINDEX);
返回
USE master;
执行
备份数据库 [SQLTestDB]
TO DISK = N'S:\instance\MSSQL15.GFOX\MSSQL\Backup\SQLTestDB_Full.bak';
执行
备份数据库 [SQLTestDB]
TO DISK = N'S:\instance\MSSQL15.GFOX\MSSQL\Backup\SQLTestDB_Diff.bak'; GO BACKUP DATABASE [SQLTestDB].
使用差分、压缩;
执行
备份日志[SQLTestDB]
到 DISK = N'S:S\instance\MSSQL\MSSQL15.GFOX\MSSQL\Backup\SQLTestDB_Log.trn'
并进行压缩、校验;
去

只恢复验证
FROM DISK = N'S:\instance\MSSQL15.GFOX\MSSQL\Backup\SQLTestDB_Log.trn';
返回
只恢复验证
FROM DISK = N'S:\instance\MSSQL15.GFOX\MSSQL\Backup\SQLTestDB_Diff.bak';
恢复
只恢复验证
FROM DISK = N'S:\instance\MSSQL15.GFOX\MSSQL\Backup\SQLTestDB_Full.bak';
返回
--不是 hallegreen

/*
https://github.com/green-fox-academy/teaching-materials/blob/master/workshop/sql-server-database-maintenance/sql-server-database-maintenance.md
https://ola.hallengren.com/
*/
-- 工作 https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-jobschedule-transact-sql?view=sql-server-ver15
-- 完整备份
使用 [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'sqltestdb 完整备份工作'、
		@enabled=1、
		@notify_level_eventlog=0、
		@notify_level_email=2、
		@notify_level_page=2、
		@delete_level=0、
		@category_name=N'[Uncategorized (Local)]'、
		@owner_login_name=N'User3', @job_id = @jobId 输出
选择 @jobId
执行
EXEC msdb.dbo.sp_add_jobserver @job_name=N'sqltestdb 完整备份作业', @server_name = N'USER3-VM'
执行
USE [msdb]
执行
EXEC msdb.dbo.sp_add_jobstep @job_name=N'sqltestdb 完整备份任务',@step_name=N'sqltestdb dbcc 检查'、
		@step_id=1、
		@cmdexec_success_code=0、
		@on_success_action=3、
		@on_fail_action=2、
		@retry_attempts=0、
		@retry_interval=0、
		@os_run_priority=0, @subsystem=N'TSQL'、
		@command=N'DBCC CHECKDB (SQLTestDB, NOINDEX);'、
		@database_name=N'DBAHealth'、
		@flags=0
运行
USE [msdb]
执行
EXEC msdb.dbo.sp_add_jobstep @job_name=N'sqltestdb 完整备份任务',@step_name=N'sqltestdb 完整备份任务'、
		@step_id=2、
		@cmdexec_success_code=0、
		@on_success_action=1、
		@on_fail_action=2、
		@retry_attempts=0、
		@retry_interval=0、
		@os_run_priority=0, @subsystem=N'TSQL'、
		@command=N'EXECUTE dbo.DatabaseBackup
@Databases = ''SQLTestDB''、
@Directory = ''C:\MSSQL Server''、
@BackupType = ''FULL''、
@Verify = ''Y'';'、
		@database_name=N'DBAHealth'、
		@flags=0
运行
USE [msdb]
执行
EXEC msdb.dbo.sp_update_job @job_name=N'sqltestdb 完整备份工作'、
		@enabled=1、
		@start_step_id=1、
		@notify_level_eventlog=0、
		@notify_level_email=2、
		@notify_level_page=2、
		@delete_level=0、
		@description=N''、
		@category_name=N''[Uncategorized (Local)]'、
		@owner_login_name=N'User3'、
		@notify_email_operator_name=N''、
		@notify_page_operator_name=N''
返回
USE [msdb]
返回
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'sqltestdb full backup job', @name=N'sqltestdb full backup sch'、
		@enabled=1、
		@freq_type=8, -weekly
		@freq_interval=1, --sunday
		@freq_subday_type=1、
		@freq_subday_interval=0、
		@freq_relative_interval=0、
		@freq_recurrence_factor=1、
		@active_start_date=20230419,
		@active_end_date=99991231,
		@active_start_time=30000,
		@active_end_time=235959, @schedule_id = @schedule_id 输出
选择 @schedule_id
返回

-- 差异备份工作

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'sqltestdb diff backup job'、
		@enabled=1、
		@notify_level_eventlog=0、
		@notify_level_email=2、
		@notify_level_page=2、
		@delete_level=0、
		@category_name=N'[Uncategorized (Local)]'、
		@owner_login_name=N'User3', @job_id = @jobId 输出
选择 @jobId
执行
EXEC msdb.dbo.sp_add_jobserver @job_name=N'sqltestdb diff 备份工作', @server_name = N'USER3-VM'
执行
USE [msdb]
执行
EXEC msdb.dbo.sp_add_jobstep @job_name=N'sqltestdb diff 备份工作',@step_name=N'sqltestdb diff 工作'、
		@step_id=1、
		@cmdexec_success_code=0、
		@on_success_action=1、
		@on_fail_action=2、
		@retry_attempts=0、
		@retry_interval=0、
		@os_run_priority=0, @subsystem=N'TSQL'、
		@command=N'EXECUTE dbo.DatabaseBackup
@Databases = ''SQLTestDB''、
@Directory = ''C:\MSSQL Server''、
@BackupType = ''DIFF''、
@Compress = ''Y'';'、
		@database_name=N'DBAHealth'、
		@flags=0
运行
USE [msdb]
执行
EXEC msdb.dbo.sp_update_job @job_name=N'sqltestdb diff backup job'、
		@enabled=1、
		@start_step_id=1、
		@notify_level_eventlog=0、
		@notify_level_email=2、
		@notify_level_page=2、
		@delete_level=0、
		@description=N''、
		@category_name=N''[Uncategorized (Local)]'、
		@owner_login_name=N'User3'、
		@notify_email_operator_name=N''、
		@notify_page_operator_name=N''
返回
USE [msdb]
返回
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'sqltestdb diff backup job', @name=N'sqltestdb diff job sch'、
		@enabled=1、
		@freq_type=8、
		@freq_interval=8, --wednesday
		@freq_subday_type=1、
		@freq_subday_interval=0、
		@freq_relative_interval=0、
		@freq_recurrence_factor=1、
		@active_start_date=20230419,
		@active_end_date=99991231,
		@active_start_time=40000,
		@active_end_time=235959, @schedule_id = @schedule_id 输出
选择 @schedule_id
返回

-- 记录备份工作

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'sqltest 日志备份'、
		@enabled=1、
		@notify_level_eventlog=0、
		@notify_level_email=2、
		@notify_level_page=2、
		@delete_level=0、
		@category_name=N'[Uncategorized (Local)]'、
		@owner_login_name=N'User3', @job_id = @jobId 输出
选择 @jobId
执行
EXEC msdb.dbo.sp_add_jobserver @job_name=N'sqltest 日志备份',@server_name = N'USER3-VM
执行
USE [msdb]
执行
EXEC msdb.dbo.sp_add_jobstep @job_name=N'sqltest 日志备份',@step_name=N'sqltestdb 日志备份'、
		@step_id=1、
		@cmdexec_success_code=0、
		@on_success_action=1、
		@on_fail_action=2、
		@retry_attempts=0、
		@retry_interval=0、
		@os_run_priority=0, @subsystem=N'TSQL'、
		@command=N'EXECUTE dbo.DatabaseBackup
@Databases = ''SQLTestDB''、
@Directory = ''C:\MSSQL Server''、
@BackupType = ''LOG''、
@Compress = ''Y''、
@CheckSum = ''Y'';'、
		@database_name=N'DBAHealth'、
		@flags=0
运行
USE [msdb]
执行
EXEC msdb.dbo.sp_update_job @job_name=N'sqltest 日志备份'、
		@enabled=1、
		@start_step_id=1、
		@notify_level_eventlog=0、
		@notify_level_email=2、
		@notify_level_page=2、
		@delete_level=0、
		@description=N''、
		@category_name=N''[Uncategorized (Local)]'、
		@owner_login_name=N'User3'、
		@notify_email_operator_name=N''、
		@notify_page_operator_name=N''
返回
USE [msdb]
返回
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'sqltest log backup', @name=N'sqltestdb log backup sch'、
		@enabled=1、
		@freq_type=4, --daily
		@freq_interval=1、
		@freq_subday_type=4, -- 分钟
		@freq_subday_interval=5, -- 五 ...
		@freq_relative_interval=0、
		@freq_recurrence_factor=1、
		@active_start_date=20230419,
		@active_end_date=99991231,
		@active_start_time=0、
		@active_end_time=235959, @schedule_id = @schedule_id 输出
选择 @schedule_id
返回

任务 #7:

数据库 SQLRestoreDB 必须从备份恢复到最后可能的还原点。需要还原数据库是因为数据库被意外删除。

编写还原语句,在还原链中还原数据库,从而还原表的全部内容。

可用的备份有

  • SQLRestoreDB_Full.bak:完整备份
  • SQLRestoreDB_Log_2.trn:日志备份
  • SQLRestoreDB_Diff_3.bak:差异备份
  • SQLRestoreDB_Log_4.trn:日志备份

备份文件名称还显示备份类型和创建顺序。

数据库不在默认驱动器上,因此可能需要移动。编写一个还原命令,列出完整备份文件中的文件。

数据在 "SQLRestoreDB_Log_4.trn "日志备份后被删除!

使用最佳还原链!

恢复数据库后,运行下面的查询,并将结果保存为 .csv 格式。

USE SQLRestoreDB;
执行
选择 
    GETDATE() AS [考试时间戳]、
    @@SERVERNAME AS [我的服务器名]、
    * 
 dbo.SQLRestoreDB;
USE master;
GO
-- 还原列表
RESTORE FILELISTONLY FROM DISK = N'Y:\x\SQLRestoreDB_Full.bak';
GO
/*
RESTORE HEADERONLY FROM DISK = N'Y:\x\SQLRestoreDB_Full.bak';
GO
*/
-- 恢复完整备份v
RESTORE DATABASE [SQLRestoreDB] DATABASE
FROM DISK = N'Y:\x\SQLRestoreDB_Full.bak'; GO */ -- 恢复完整备份v
WITH MOVE N'SQLRestoreDB' TO N'T:\sqldata\SQLRestoreDB.mdf'、
MOVE N'SQLRestoreDB_log' TO N'L:\sqllogs\SQLRestoreDB_log.ldf', NORECOVERY;
--差异备份
RESTORE DATABASE [SQLRestoreDB] FROM DISK = N'Y:\xSQL\RestoreDB_Diff_3.bak' WITH NORECOVERY;
-- 日志备份
RESTORE LOG [SQLRestoreDB] FROM DISK = N'Y:\x\SQLRestoreDB_Log_4.trn' WITH RECOVERY;
返回
USE SQLRestoreDB;
GO
SELECT
    GETDATE() AS [examstimestamp]、
    @@SERVERNAME AS [myservername]、
    *
FROM dbo.SQLRestoreDB;

任务 #8:

编制下表

删除 表格 如果存在 应用.日志审计;
去
创建 表格 应用.LogAudit
(
    LogAuditId int 非空 IDENTITY(1,1) 主键,
    LogData nvarchar(50) 非空
);

使用以下 INSERT INTO 命令添加数据:

INSERT INTO 应用.日志审计 (LogData) 价值 ('这是日志数据!');

在应用程序模式中创建名为 DeleteLogData 的存储过程。

存储过程根据传递的 LogAuditId 参数从 Application.LogAudit 表中删除一条记录。

存储过程应使用以下参数:

  • @logauditid int

存储过程应使用以下 OUTPUT 参数:

  • @deletedlogdata nvarchar(50)

使用相应的内置错误编号和错误信息功能进行错误处理。

如果删除成功,存储过程将返回,并在 OUTPUT 参数中显示已删除的 LogData 值。

如果出了差错,要勇于面对:

  • 打印:'删除日志审计失败!'
  • 打印:"错误编号:"和错误编号
  • print message: 'Error message: ' 以及错误信息
  • 返回 -1 值

举例说明如何使用您准备的程序。

USE WideWorldImporters;
GO
DROP TABLE IF EXISTS Application.LogAudit;
删除表
创建表 Application.LogAudit
(
    LogAuditId int NOT NULL IDENTITY(1,1) PRIMARY KEY、
    LogData nvarchar(50) NOT NULL
);
INSERT INTO Application.LogAudit (LogData) VALUES ('This is a log data!');
返回
SELECT * FROM Application.LogAudit;
执行
CREATE OR ALTER PROC Application.DeleteLogData (
	 @logauditid int
	@deletedlogdata nvarchar(50) OUTPUT
)
AS
	set nocount on;
	开始尝试
		DECLARE @Mytemptable TABLE (
			logdatas NVARCHAR(50)
		);
		DELETE Application.LogAudit
		OUTPUT deleted.LogData INTO @Mytemptable
		WHERE LogAuditId=@logauditid;

		如果 @@rowcount  1
			开始
				RAISERROR ('Delete of log audit failed!',16,1);
			结束;

		SELECT @deletedlogdata = logdatas FROM @Mytemptable;
	结束尝试
	开始捕获
		PRINT '删除日志审计失败!
		PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
		PRINT 'Error message: ' + ERROR_MESSAGE();
		返回 -1;
	结束 CATCH
返回

-- 展示

DECLARE @outputvar NVARCHAR(50),@returnstatus INT;
EXEC @returnstatus=Application.DeleteLogData 1, @deletedlogdata = @outputvar OUTPUT;
SELECT @outputvar AS [Deleted log value],@returnstatus AS [return status];
GO
-- 显示错误
DECLARE @outputvar NVARCHAR(50),@returnstatus INT;
EXEC @returnstatus=Application.DeleteLogData 1, @deletedlogdata = @outputvar OUTPUT;
SELECT @outputvar AS [Deleted log value],@returnstatus AS [return status];
返回

HSG 阅读器期末正常考试

一般情况

  • 笔试部分的 Gradescope 链接
  • 考试时间为 9:00 至 10:00,您将在 60 分钟内回答 40 个问题。
  • 以下练习的解答 关于 Gradescope 上传到项目任务部分
  • 文件应按照任务中的指定命名
  • 解决方案可以 .zip 文件的形式一并上传到 Grade-scop 中。
  • 只对 Gradescope 内容进行评估!
  • 确保上传文件的编码为 UTF-8 WITHOUT BOM(签名)。
  • 确保提交的 SQL 文件是 SSMS/Azure Data Studio 的 "可执行 "文件

开始

  • 将此文件夹发到自己的 Github 用户名下
  • 将分叉仓库从你自己的名字克隆到你的机器上,在这里工作
  • 定期提交并使用清晰的提交注释
  • 所有解决方案都应上传至 github

您在机器上生成的 Azure 环境中发现了什么?

  • WideWorldImporters 数据库正常运行。
  • 运行正常的名为 ExamDB 的数据库。(如果您在自己的环境中工作,还可以在下面的备份链接中找到 ExamDB_Full.bak 备份文件)。
  • 还有一个名为 DBAHealth 的数据库,sp_Blitz 等脚本和 Ola Hallengren 等脚本都加载在这个数据库中。
  • 此外,默认备份文件夹还包含 RestoreDB 数据库的备份(您也可以在底部的备份链接中找到这些备份,另外。)

可以使用什么?

  • 可以使用任何在线资源,但 单干
  • 不要只是复制 解决方案,用你自己的知识、你的语言
  • NE Push-olj 到 GitHub,直到导师宣布可以
  • 不要忘记上传 您的解决方案: Gradescope

任务

备份文件

死锁脚本

任务 #1:

您参与了学校数据库的开发工作。他们要求数据库存储教师、学生、科目和相关数据。

应处理以下数据

  • 教师详细信息
  • 科目清单
  • 学生数据
  • 表格之间的联系

应处理以下数据

  • 教师/学生/学科标识符(获取唯一标识符)
  • 教师/学生姓名、电子邮件地址、出生日期
  • 主题名称
  • 学生须知
  • 教师电话号码
  • 学生的入学年份(从入学开始)/只应存储年号////////////////。
  • 现有学生

一个学生可以有多个科目,就像一个科目可以教给多个学生一样,但在这种情况下,教师只教一个科目。

创建具有正确列类型的正确数据库表。

至少使用以下限制条件:

  • 主键
  • FOREIGN KEY

必要时,定义其他限制!

注意正常化!

来自 执行上述操作的 SQL 脚本
文件名 WINSQL-01.sql
DROP DATABASE IF EXISTS SchoolDB;
CREATE DATABASE SchoolDB;
GO
USE SchoolDB;
GO
CREATE TABLE dbo.Subjects (
 id int identity(1,1)、
 name NVARCHAR(150) NOT NULL);

CREATE TABLE dbo.Students (
 id int identity(1,1)、
 姓名 NVARCHAR(500) NOT NULL、
 电子邮件 VARCHAR(255) NOT NULL、
 出生日期 DATE NOT NULL、
 入学年份 SMALLINT NOT NULL、
 类别 NVARCHAR(60) NOT NULL、
 注释 NVARCHAR(5000));

CREATE TABLE dbo.Teachers (
 id int identity(1,1)、
 姓名 NVARCHAR(500) NOT NULL、
 电子邮件 VARCHAR(255) NOT NULL、
 PhoneNumber VARCHAR(15) NOT NULL、
生日 DATE NOT NULL);

创建表 dbo.Conn_Students_Subjects (
 id int identity(1,1)、
 SubID INT NOT NULL、
 StudID INT NOT NULL);
返回
ALTER 表 dbo.Subjects
ADD CONSTRAINT PK_Subjects PRIMARY KEY (ID);
返回
ALTER 表 dbo.Students
ADD CONSTRAINT PK_Students PRIMARY KEY (ID);
返回
ALTER 表 dbo.Teachers
ADD CONSTRAINT PK_Teachers PRIMARY KEY (ID)、
CONSTRAINT FK_Teachers_SubID FOREIGN KEY (SubID) REFERENCES dbo.Subjects(ID);
返回
ALTER 表 dbo.Conn_Students_Subjects
ADD CONSTRAINT PK_Students_Subjects PRIMARY KEY (ID)、
CONSTRAINT FK_Conn_Students_Subjects_SubID FOREIGN KEY (SubID) REFERENCES dbo.Subjects(ID)、
CONSTRAINT FK_Conn_Students_Subjects_StudID FOREIGN KEY (StudID) REFERENCES dbo.Students(ID);
返回
ALTER 表 dbo.Subjects
ADD CONSTRAINT UQ_Subjects_Name UNIQUE (Name);
返回
ALTER 表 dbo.Students
ADD CONSTRAINT UQ_Students_Email UNIQUE (Email)、
CONSTRAINT CK_Students_Birthdate CHECK (YearOfEnrolment > YEAR(Birthdate))、
CONSTRAINT CK_Students_YearOfEnrolment CHECK (YearOfEnrolment >= 1900 AND YearOfEnrolment > YEAR(Birthdate));
执行
ALTER 表 dbo.Teachers
ADD CONSTRAINT UQ_Teachers_Email UNIQUE (Email)、
CONSTRAINT UQ_Teachers_PhoneNumber UNIQUE(电话号码);
返回

任务 #2:

使用 WideWorldImporters 示例数据库!

编写一个查询,返回以 "Abel "开头的客户的 ID、姓名和 CreditLimit 值,该客户在 2016 年 5 月 1 日至 2016 年 5 月 15 日期间没有下过订单(2016 年 5 月 1 日和 5 月 15 日的订单不应包括在内!)。

确保日期搜索条件是可 SARG 的!

请使用下面的表格:

  • 销售.客户
  • 销售订单

查询应返回以下列:

  • [客户 ID]:客户 ID
  • [客户名称]:客户名称
  • [信用额度]:买方的信用额度
来自 执行上述操作的 SQL 脚本
文件名 WINSQL-02.sql
USE WideWorldImporters;
GO
SELECT
    c.CustomerID AS [Customer ID]、
    c.CustomerName AS [客户名称]、
    c.CreditLimit AS [Credit Limit] (信用额度)
FROM
    客户 c
WHERE
    c.CustomerName LIKE "Abel%
    且不存在 (
        SELECT 1
        FROM Sales.Orders o
        WHERE
            o.CustomerID = c.CustomerID
            且 o.OrderDate > '2016-04-30
            且 o.OrderDate < '2016-05-16
    );

任务 #3:

使用 ExamDB 示例数据库!

在此之前,请运行以下脚本:

USE ExamDB;
运行
删除 模式 IF EXISTS Exam;
转到
创建 模式 考试;
去
如果存在,则删除存储过程 考试.程序1
开始
创建存储过程 考试.程序1
AS
选择 1;
 

编写包含以下内容的 T-SQL 脚本:

  1. 它会创建一个名为 "NewExamLogin "的 SQL 登录名和密码,并将 ExamDB 数据库设置为默认数据库。
  2. 在 ExamDB 数据库中,它会为上次登录的用户创建一个 "NewExamUser "用户。
  3. 在 ExamDB 数据库中创建名为 "考试管理员 "的新数据库角色。
  4. 在 ExamDB 数据库中创建另一个新的数据库角色,名为 "考试贡献者"。
  5. 将用户 "NewExamUser "添加到数据库角色 "Exam Admins"。
  6. 对于考试模式,EXECUTE 赋予 "考试管理员 "数据库角色权限。
  7. 考试模式明确禁用了 "考试贡献者 "角色的 EXECUTE 功能。
  8. 测试通过冒充用户 "NewExamUser "执行存储过程 Exam.Proc1。
  9. 将执行上下文恢复为前一个用户。
  10. 查询当前用户名和登录名。

每个任务都是一条 T-SQL 语句。

来自 执行上述操作的 SQL 脚本
文件名 WINSQL-03.sql
-- 创建一个名为 "NewExamLogin "的 SQL 登录名和密码,并将 ExamDB 数据库设置为默认数据库
CREATE LOGIN NewExamLogin WITH PASSWORD = 'SomeVeryStrongPassword123!', DEFAULT_DATABASE = [ExamDB];

-- 在 ExamDB 数据库中为上次登录创建用户 "NewExamUser
USE ExamDB;
CREATE USER NewExamUser FOR LOGIN NewExamLogin;

-- 在 ExamDB 数据库中创建名为 "考试管理员 "的新数据库角色
CREATE ROLE [Exam Admins];

-- 在 ExamDB 数据库中创建另一个名为 "考试贡献者 "的新数据库角色
CREATE ROLE [Exam Contributors];

-- 将用户 "NewExamUser "添加到数据库角色 "考试管理员
ALTER ROLE [Exam Admins] ADD MEMBER NewExamUser;

-- 授予数据库角色 "Exam Admins "执行考试模式的权限
GRANT EXECUTE ON SCHEMA::Exam TO [Exam Admins];

-- 在考试模式上,显式禁用角色 "考试贡献者 "的 EXECUTE 权限
DENY EXECUTE ON SCHEMA::Exam TO [Exam Contributors];

-- 通过冒充用户 "NewExamUser "测试存储过程 Exam.Proc1 的执行情况
EXECUTE AS USER = 'NewExamUser';
EXEC Exam.Proc1;
REVERT;

-- 将执行上下文重置为以前的用户
--(REVERT 语句已在上一步中重置了上下文)

-- 查询当前用户名和登录名
SELECT CURRENT_USER AS [当前用户], SUSER_NAME() AS [登录名];

任务 #4:

编写诊断查询(使用系统目录视图),返回服务器数据库中所有文件(用户和系统)的参数,如:文件类型(ROWS 或 LOG)、文件物理路径、文件大小以及文件是递增还是固定。

查询结果如下

  • database_id:数据库 ID
  • name:数据库名称
  • Recovery_model_desc:数据库恢复模式名称(如 SIMPLE、FULL)
  • file_id:数据库中文件的 ID,如:1、2 等。
  • type_desc:文件类型,如:ROWS、LOG 等。
  • name:文件的逻辑名称
  • physical_name:文件的物理路径和名称
  • file_size_MB:别名和计算值,文件大小(单位:兆字节
  • is_percent_growth:增量是百分比还是固定值
来自 执行上述操作的 SQL 脚本
文件名 WINSQL-04.sql
SELECT
    DB.database_id、
    DB.name AS 数据库名、
    DB.recovery_model_desc、
    FM.file_id、
    FM.type_desc、
    FM.name AS 文件名、
    FM.physical_name、
    (FM.size * 8.0 / 1024) AS file_size_MB、
    FM.is_percent_growth
从
    sys.databases AS DB
连接
    sys.master_files AS FM
在
    DB.database_id = FM.database_id;

任务 #5:

本练习需要 ExamDB 数据库。数据库必须可用(联机)。

使用 T-SQL 语句编写维护脚本,执行以下操作:

  1. 在服务器级别,它将默认设置为压缩备份!
  2. 禁用 ExamDB 数据库的并行化(仅限该数据库!)(不为该数据库创建并行查询计划)
  3. 检查(查询)压缩备份的服务器级设置!
  4. 它会检查(查询) ExamDB 数据库,查看是否已禁用并行化!
  5. 以读写模式在 ExamDB 数据库上启用查询存储,最大容量为 500MB,这样查询存储就能捕获所有查询!
来自 执行上述操作的 SQL 脚本
文件名 WINSQL-05.sql
-- 在服务器级别设置默认创建压缩备份
EXEC sp_configure 'backup compression default', 1;
重新配置;
-- 禁用 ExamDB 数据库的并行化(仅!)。
USE [ExamDB]
GO
alter database scoped configuration set maxdop = 1;
-- 检查(查询)压缩备份的服务器级别配置
EXEC sp_configure 'backup compression default';
-- 检查(查询)ExamDB 数据库是否禁用并行化
SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = 'MAXDOP';
-- 在 ExamDB 数据库上启用查询存储,采用读写模式,最大容量为 500MB,这样查询存储就能捕获所有查询。
USE [master]
运行
ALTER DATABASE ExamDB
set query_store = on (
    operation_mode = read_write、
    max_storage_size_mb = 500、
    query_capture_mode = all
);

任务 #6:

必须将名为 RestoreDB 的数据库从备份恢复到特定的恢复点(时间点恢复)。

还原前,编写一条命令以显示 RestoreDB_Full.bak 备份集中有哪些文件(数据和日志)!

然后编写还原语句,在还原链中还原数据库。

可用的 备份 如下(只能使用这些保存):

  • RestoreDB_Full.bak:完整备份
  • RestoreDB_Log_2.trn:日志备份
  • RestoreDB_Log_3.trn:日志备份
  • RestoreDB_Log_5.trn:日志备份
  • RestoreDB_Log_6.trn:日志备份

备份文件名称还显示备份类型和创建顺序。

恢复数据库时,应将 "RestoreDB_Log_5.trn "日志备份作为最终备份!

恢复数据库后,运行下面的查询,并将结果保存为 .csv 格式。

USE RestoreDB;
运行
选择 
    GETDATE() AS [考试时间戳]、
    @@SERVERNAME AS [我的服务器名]、
    * 
 dbo.还原表格;
 
来自 执行上述操作的 SQL 脚本
文件名 WINSQL-06.sql
来自 以 .csv 格式保存的结果
文件名 WINSQL-06.csv
USE master;
GO
-- 还原列表
RESTORE FILELISTONLY FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreDB_Full.bak';
GO
/*
RESTORE HEADERONLY FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreDB_Full.bak';
返回
*/
-- 恢复完整备份v
RESTORE DATABASE [RestoreDB] (恢复数据库)
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreDB_Full.bak'
WITH MOVE N'RestoreDB' TO N'C:\exam\RestoreDB.mdf'、
MOVE N'RestoreDB_log' TO N'C:\exam\RestoreDB_log.ldf', NORECOVERY; -- 我直接移动它是为了让我更容易检查我在 C# 上创建的文件夹。
/*--差异备份
RESTORE DATABASE [RestoreDB] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreDB_Diff_4.bak' WITH NORECOVERY;*/
-- 日志备份
RESTORE LOG [RestoreDB] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreDB_Log_2.trn' WITH NORECOVERY;
RESTORE LOG [RestoreDB] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreDB_Log_3.trn' WITH NORECOVERY;
RESTORE LOG [RestoreDB] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreDB_Log_5.trn' WITH NORECOVERY;
返回
USE RestoreDB;
返回
SELECT
    GETDATE() AS [examstimestamp]、
    @@SERVERNAME AS [myservername]、
    *
FROM dbo.RestoreTable;

任务 #7:

执行任务前运行以下脚本:

USE [master];
执行
更改 数据库 [考试局] 设置 关闭已提交快照读取功能
包含 回滚 立即;
 

使用死锁脚本: deadlock_script_exam.sql 到第 3 步!在步骤 3 中,SELECT 查询将等待(尚未出现死锁!)。

  • 在新的查询窗口中编写一个查询,显示挂起的 SELECT 查询(或其运行的会话)当前拥有哪些锁以及需要哪些锁!只返回当前正在等待的会话的锁数据!

  • 执行查询后,您发现您正在等待的 SELECT 查询需要哪种类型的锁?将答案写在查询旁边的注释中。

  • 按如下方式更改服务器配置:高级配置选项应可用,且该更改应在脚本运行后立即生效。

来自 执行上述操作的 SQL 脚本,并在注释中包含答案
文件名 WINSQL-07.sql
/*
SELECT tl.request_session_id, tl.resource_type, tl.request_mode, tl.request_status, tl.resource_database_id, tl.resource_associated_entity_id, tl.resource_description
FROM sys.dm_tran_locks tl
JOIN sys.dm_exec_requests er ON tl.request_session_id = er.session_id
WHERE ER.wait_type LIKE 'LCK%' AND tl.request_status = ER.session_ID
  且 tl.request_status = 'WAIT';
返回
*/
USE DBAHEALTH;
返回
dbo.sp_BlitzLock -- S - 共享锁

EXEC sp_configure 'show advanced options', 1;
执行
用覆盖重新配置;
执行

ALTER DATABASE [ExamDB] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT

任务 #8

使用 WideWorldImporters 示例数据库!

在销售模式中编写一个名为 GetPickedByDate 的存储过程,根据给定时间间隔(OrderDate)内某些订单何时到达/何时可能作为客户结束,返回订单行项目(Sales.OrderLines)的统计数据,具体说明如下!

存储过程需要两个参数:

  • @startDate 日期
    • 默认值应为开始日期 01.01.2013
  • @endDate 日期
    • 默认值应为完成日期 01.06.2016

例如

  • 如果使用默认参数值运行存储过程,存储过程将按客户名称(CustomerName)和选择完成时间(PickingCompletedWhen)返回 2013 年 1 月 1 日至 2016 年 6 月 1 日(OrderDate)期间的采购统计信息。

返回包含以下列的存储过程

  • [客户名称]:客户表中的 CustomerName 列
  • [拣选日期]:从订单行表中选择项目(PickingCompletedWhen)列的完成日期:
    • 如果不知道日期,则打印当前日期加 3 天
  • [总价]:订单行表中的行项目总数(数量 * 单价)(小数点后两位数)
  • [采购数量]:已统计的细列项目数量(OrderLine)
  • [刷新日期]:如果不知道分拣排序(PickingCompletedWhen)的日期,则打印 "预计分拣日期",如果知道,则打印 "已分拣"。

包括过滤功能,只返回不知道客户购买群组的信息。

进一步筛选,只返回 [Ovarall 价格] 列值高于 20000 的信息。

请使用下面的表格:

  • Sales.OrderLines
  • 销售订单
  • 销售.客户

编写存储过程时,应确保运行后 "信息 "选项卡中不会出现 "n 行受影响 "的信息。

举例说明如何使用!

来自 执行上述操作的 SQL 脚本
文件名 WINSQL-08.sql
USE WideWorldImporters;
GO
/*
SELECT * FROM Sales.Customers
SELECT * FROM Sales.Orders
SELECT * FROM Sales.OrderLines
*/
执行
创建或 ALTER PROCEDURE Sales.GetPickedByDate
    @startDate datetime = '2013-01-01'、
    @endDate datetime = '2016-06-01
作为
开始
    set nocount on;
    SELECT
        c.CustomerName AS [客户名称]、
        COALESCE(ol.PickingCompletedWhen, DATEADD(DAY, 3, GETDATE()))AS [拣选日期]、
        SUM(ol.Quantity * ol.UnitPrice) AS [Overall Price](总价)、
        COUNT(ol.OrderLineID) AS [采购数量]、
        案例
            WHEN ol.PickingCompletedWhen IS NULL THEN 'Expected Pick Date' (预计采摘日期
            ELSE '已采摘
        END AS [刷新日期]
    FROM Sales.OrderLines ol
    JOIN Sales.Orders o ON ol.OrderID = o.OrderID
    JOIN Sales.Customers c ON o.CustomerID = c.CustomerID
    WHERE c.BuyingGroupID IS NULL
        且 o.OrderDate >= @startDate
        且 o.OrderDate  20000
    ORDER BY c.CustomerName, ol.PickingCompletedWhen;
结束;
执行
EXEC Sales.GetPickedByDate;
EXEC Sales.GetPickedByDate @startDate = '2014-01-01', @endDate = '2015-12-31';

诊断查询

从 sys.tables 查询表格

SELECT * FROM sys.tables;

表及其模式 - 连接 sys.tables 和 sys.schemas 目录视图

SELECT t.name AS [table_name], t.type_desc, s.name AS [schema_name].
FROM sys.tables t INNER JOIN sys.schemas s ON
t.schema_id = s.schema_id;

我们在哪个环节

SELECT @@SPID;

获取所有用户表的列数据类型信息

SELECT c.object_id, OBJECT_NAME(c.object_id) AS [object_name]、
s.name AS [schema], o.name AS [table_name], c.name AS [col_name]、
type.name AS [col_type]、
CASE c.is_nullable WHEN 1 THEN 'yes' ELSE 'no' END AS [nullable].
FROM sys.columns c INNER JOIN sys.objects o ON
c.object_id = o.object_id
INNER JOIN sys.tables t ON
t.object_id = o.object_id
INNER JOIN sys.types typ ON
c.user_type_id = typ.user_type_id
INNER JOIN sys.schemas s ON
s.schema_id = t.schema_id;
-- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-ver15
	--数据库的恢复模式
	SELECT name, recovery_model_desc FROM sys.databases;

	--服务器中的所有文件
	SELECT db.name AS DBName, type_desc AS FileType, Physical_Name AS Location, size * 8 as FileSizeKB
	FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id;

每个表的计数索引

SELECT S.name,COUNT(SYS.INDEXES.name) FROM SYS.INDEXES
内连接 sys.objects s
on s.object_id=sys.indexes.object_id
WHERE SYS.INDEXES.OBJECT_ID IN (SELECT OBJECT_ID FROMS.OBJECTS WHERE name IN)
    (SELECT name FROMS.TABLES WHERE NAME  'SYSDIAGRAMS'))
GROUP BY S.name;

每个表的列表索引

SELECT S.name,SYS.INDEXES.name FROM SYS.INDEXES
内连接 sys.objects s
on s.object_id=sys.indexes.object_id
如果 SYS.INDEXES.OBJECT_ID 在 (SELECT OBJECT_ID FROMS.OBJECTS WHERE name IN
    (SELECT NAME FROMS.TABLES WHERE NAME  'SYSDIAGRAMS'));

索引分割

SELECT i. name, x.avg_fragmentation_in_percent, x.fragment_count
FROM sys.indexes i JOIN sys.dm_db_index_physical_stats(DB_ID( 'WideWor1dImporters' ) ,OBJECT_ID('INDEXTEST'), NULL,NULL,'DETAILED') x ON x.object_id = i.object_id
WHERE x.fragment_count > 1

--------------------------------------------------

SELECT
    dbschemas.[name] as 'Schema'、
    dbtables.[name] 作为 "表"、
    dbindexes.[name] as 'Index'、
    indexstats.avg_fragmentation_in_percent、
    indexstats.page_count
从
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN
    dbtables.[object_id] = indexstats.[object_id]。
INNER JOIN
    sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id] INNER JOIN
INNER JOIN
    on dbindexes.[object_id] = indexstats.[object_id]。
和
    indexstats.index_id = dbindexes.index_id
所在
    indexstats.database_id = DB_ID()

死锁?

选择 session_id、blocking_session_id
from sys.dm_exec_requests
其中 blocking_session_id != 0

计算数据库中的约束条件(检查+默认

SELECT (SELECT COUNT(*) FROM sys.check_constraints)+(SELECT COUNT(*) FROM sys.default_constraints);

确定性?

SELECT COLUMNPROPERTY(OBJECT_ID('dbo.testlines'),'total_price','isDeterministic') AS Determ;

Select * FROM sys.computed_columns;

按列名搜索

SELECT *
from information_schema.columns
WHERE COLUMN_NAME LIKE '%region%'.

按表格名称搜索

SELECT c.object_id, OBJECT_NAME(c.object_id) AS [object_name], s.name AS [schema], o.name AS [table_name], c.name AS [col_name], typ.name AS [col_type] , CASE c.is_ullable WHEN 1 THEN 'yes' ELSE 'no' END AS [nullable] FROM.
CASE c.is_nullable WHEN 1 THEN 'yes' ELSE 'no' END AS [nullable] FROM sys.columns c
INNER JOIN sys.objects o ON c.object_id = o.object_id
INNER JOIN sys.tables t ON t.object_id = o.object_id
INNER JOIN sys.types typ ON c.user_type_id = typ.user_type_id
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
其中 c.name LIKE '%Postal%

在数据库中搜索字符串

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = '库存有限

DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
开始
    SET @ColumnName = ''
    SET @TableName =
    (
        select min(quotename(table_schema) + '.' + quotename(table_name))
        from information_schema.tables
        where table_type = 'base table
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            和 objectproperty(
                    OBJECT_ID(
                        quotename(table_schema) + '.' + quotename(table_name)
                         ), 'IsMSShipped
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    开始
        SET @ColumnName =
        (
            select min(quotename(column_name))
            from information_schema.columns
            WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
                AND TABLE_NAME = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                且 QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        如果 @ColumnName 不为空
        开始
            INSERT INTO @Results
            执行
            (
                SELECT ''' + @TableName + '.' + @ColumnName + '''', LEFT(' + @Results
@ColumnName
+ ', 3630) from '
+
表名
+ ' (nolock) ' +
                ' WHERE '
+
@ColumnName
+ ' LIKE '
+
@SearchStr2
                
            )
        结束
    结束
结束

SELECT ColumnName, ColumnValue FROM @Results

Sys.messages & 在会话中修改语言

8134 系统错误信息 > 诊断。查询
SELECT * FROM sys.messages
WHERE message_id=8134;
/* 修改会话语言 */
SET LANGUAGE hungarian;
PRINT 10/0;
SET LANGUAGE english;
PRINT 10/0;

VIEW 诊断查询

SELECT * FROM sys.sql_modules WHERE [object_id] = OBJECT_ID('VarosokOrszagok2');
每个数据库 ID 的数据库日志
SELECT b.name,a.* FROM sys.databases b
交叉应用 sys.dm_db_log_info(b.database_id) a
WHERE b.name = 'WideWorldImporters';

列出所有触发器

SELECT * FROM sys.sql_modules WHERE [object_id] = OBJECT_ID('VarosokOrszagok2');

检测锁定的物体

SELECT resource_type、request_status、request_mode、o.object_id、o.name、o.type_desc
FROM sys.dm_tran_locks l, sys.objects o
WHERE l.resource_associated_entity_id = o.object_id
    且 resource_database_id = DB_ID();

检测隔离度

SELECT CASE 交易隔离级别
    WHEN 0 THEN 'Unspecified' (未指定
    WHEN 1 THEN 'ReadUncommitted' (已读未提交)
    WHEN 2 THEN '已读已提交
    WHEN 3 THEN '可重复
    WHEN 4 THEN '可序列化
    WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
-- 使用会话 ID 而不是 75
where session_id = 75;
------------------------------------------------------------
SELECT CASE transaction_isolation_level
    WHEN 0 THEN 'Unspecified' (未指定
    WHEN 1 THEN 'ReadUncommitted' (已读未提交)
    WHEN 2 THEN 'ReadCommitted' (已读已提交)
    WHEN 3 THEN '可重复
    WHEN 4 THEN '可序列化
    WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
where session_id = @@SPID

SQL 问答

https://github.com/green-fox-academy/safely-syllabus/tree/master/materialreview/mssql

##### SQL 基金 ##### ##

select in select, where in, where not in

select top (1000) [samlahead_id] ,[VEVO_ID] .
      ,[VEVO_ID]
      ,[CELTE]
      ,[TELJESITES]
      ,[FIZHATARIDO] ,[NAVATKULDVE
      ,[NAVATKULDVE] 。
      ,[状态]
  FROM [CEGESADATOK].[samlazas].[samlahead].
  WHERE VEVO_ID Not IN (select vevoo_id
						from samlazas.vevo)

更新

使用 [cegesadata]
去

UPDATE [samlazas].
   设置 [status] = 1
      ,[lastupdate] = (getdate())
 WHERE STATUS=0
执行

INSERT INTO

使用 [cegesadata]
去

INSERT INTO [samlazas].
           ([vevonev], [adoszam], [cim])
     值
           ('öTÖDIK Nyrt','3499789012-4-04','4000 Kisvarda, Negyedik utca. 5')、
		   ('Sixth Nyrt','3456789099-4-04','4000 Kisvarda, Negyedik utca. 6')
返回
/* MASIK */
使用 [cegesdata]
返回

INSERT INTO [samlazas].
           ([VEVONEV]
           ,[ADOSZAM]
           ,[CIM]
           电话
           电子邮件
           ,[STATUS])
     值
           ('Hetedik Bt、
           '11223344-2-05',
           '无'、
           '',
            null、
           0)
转到

删除

使用 CEGESDATA
转到

SELECT *
FROM szamlazas.szamlatetel
WHERE SZAMLAFEJ_ID Not IN (SELECT SZAMLAFEJ_ID
							FROM samlazas.samlahead)


SELECT HEAD_ID
FROM samlazas.samlahead

DELETE FROM samlazas.samlatetel
WHERE SZAMLAFEJ_ID Not IN (SELECT SZAMLAFEJ_ID
							FROM samlazas.samlahead)
转到

区分

SELECT DISTINCT v.*
FROM szamlazas.vevo v INNER JOIN szamlazas.szamlahead szf ON v.VEVO_ID=szf.VEVO_ID
where v.STATUS=1 and szf.STATUS='normal

转换,平均

SELECT *
  FROM [CEGESADATOK].[szamlazas].[ARU].

SEECT AVG(ASEGAR)
  从 [CEGESADATOK].[samlazas].[ARU] 中选择 *。


SELECT *
  FROM [CEGESADATOK].
  其中 EGYSEGAR < (SELECT AVG(EGYSEGAR) as EGYSEGAR)
					FROM [CEGESADATOK].[samlazas].[ARU])

SELECT ARU_ID, ARUNEV, MENNYEGYS,CONVERT(int,EGYSEGAR) as EGYSEGAR, CONVERT(int,AFAKULCS) as AFAKULCS, STATUS
  从 [CEGESADATOK].[szamlazas].[ARU]获取
  其中 EGYSEGAR < (SELECT AVG(EGYSEGAR) as EGYSEGAR)
					FROM [CEGESADATOK].[samlazas].[ARU])

fromart, convert

选择 getdate()
select FORMAT(getdate(), 'yyyyy')
选择 FORMAT(getdate(),'yyyy/MM')
选择 FORMAT(getdate(),'yyyy/MM/dd')
选择 FORMAT(getdate()+1,'yyyy/MM/dd')
选择 FORMAT(getdate()-2,'yyyy/MM/dd')
选择 FORMAT(getdate(),'yyyy/MM/dd HH:mm:ss.ms')

选择 "2022/3/15
选择 CONVERT(datetime, '2022/3/25')

拥有

/* 任务:
查找至少购买过两次的顾客的姓名、地址、税号和购买次数
错误:
select v.VEVONEV
from szamlazas.vevo v inner join szamlazas.szamlafej szf on v.VEVO_ID=szf.VEVO_ID
where (select COUNT(szf.VEVO_ID)
							从 szamlazas.szamlafej
							分组 by szf.VEVO_ID) > 1
GROUP by v.VEVONEV
*/

选择 COUNT(szf.VEVO_ID), szf.VEVO_ID
							from szamlazas.szamlafej szf
							分组 by szf.VEVO_ID

选择 v.VEVONEV,v.CIM,v.ADOSZAM、
	COUNT(szf.VEVO_ID) as VASARLASSZAM
from szamlazas.vevo v inner join szamlazas.szamlafej szf on v.VEVO_ID=szf.VEVO_ID
GROUP by v.VEVONEV,v.CIM, v.ADOSZAM
COUNT(szf.VEVO_ID) >=2
按 vasarlasszam 排序

/* 任务:
获取姓名、地址、税号和购买次数、总计*/
选择 v.VEVO_ID,v.VEVONEV,v.CIM,v.ADOSZAM、
	CONVERT(int,SUM(t.[BRUTTO ERTEK])) as SUM
from szamlazas.vevo v inner join szamlazas.szamlafej szf on v.VEVO_ID=szf.VEVO_ID
inner join samlazas.tetelek t on szf.SZAMLAFEJ_ID=t.SZAMLAFEJ_ID
GROUP by v.VEVO_ID,v.VEVONEV,v.CIM, v.ADOSZAM
ORDER BY SZUMMABRUTTO desc


选择 v.VEVO_ID,v.VEVONEV,v.CIM,v.ADOSZAM、
	CONVERT(int,SUM(t.MENNYISEG*a.EGYSEGAR*(a.AFAKULCS/100+1))) as SUMABRUTTO
from szamlazas.vevo v inner join szamlazas.szamlafej szf on v.VEVO_ID=szf.VEVO_ID
inner join samlazas.szamlatetel t on szf.SZAMLAFEJ_ID=t.SZAMLAFEJ_ID
inner join samlazas.ARU a on t.ARU_ID=a.ARU_ID
where v.CIM like '%Budapest%'
GROUP by v.VEVO_ID,v.VEVONEV,v.CIM, v.ADOSZAM
HAVING CONVERT(int,SUM(t.MENNYISEG*a.EGYSEGAR*(a.AFAKULCS/100+1))) > 121000
ORDER BY SUMABRUTTO desc

持续修正

使用 CEGESADATOK
转到

更新 szamlazas.szamlafej
	set STATUS='错误
where SZAMLAFEJ_ID not in (select SZAMLAFEJ_ID from szamlazas.szamlatetel)

转到

SOK JOINT, SOK WHERE

使用 CEGESDATA
开始

/* 谁买了 A7?
选择 VEVONEV
从 samlazas.vevo
WHERE VEVO_ID IN (select VEVO_ID
					FROM samlazas.samlahead
					WHERE SZAMLAFEJ_ID in (select SZAMLAFEJ_ID)
											from samlazas.szamlatetel
											where ARU_ID IN (select ARU_ID from samlazas.szamlatetel)
																从 samlazas.ARU
																where ARUNEV='A7')))


选择 v.*
FROM szamlazas.vevo v
INNER JOIN samlazas.samlahead f ON v.VEVO_ID=f.VEVO_ID
	INNER JOIN samlazas.szamlatetel t ON f.SZAMLAFEJ_ID=t.SZAMLAFEJ_ID
		INNER JOIN samlazas.ARU a ON t.ARU_ID=a.ARU_ID
			WHERE a.ARUNEV='A7'
按 vevo_id 排序

选择 *
From szamlazas.vevo v INNER JOIN szamlazas.szamlazfej sz ON v.VEVO_ID=sz.VEVO_ID
INNER JOIN samlazas.tetelek t ON sz.SZAMLAFEJ_ID=t.SZAMLAFEJ_ID
其中,t.ARUNEV='A7




选择 *
FROM samlazas.samlahead
WHERE SZAMLAFEJ_ID in (select SZAMLAFEJ_ID
						from samlazas.szamlatetel
						where ARU_ID IN (select ARU_ID from samlazas.szamlatetel)
											从 samlazas.ARU
											where ARUNEV='A7'))

选择 *
从 szamlazas.szamlatetel
where ARU_ID IN (select ARU_ID
					从 szamlazas.ARU
					where ARUNEV='A7')

/*
选择 *
From samlazas.tetelek t INNER JOIN samlazas.samlafej sz ON t.SZAMLAFEJ_ID=sz.SZAMLAFEJ_ID
其中 ARUNEV='A7'

选择 *
From samlazas.vevo v INNER JOIN (samlazas.tetelek t INNER JOIN samlazas.samlahead sz ON t.SZAMLAFEJ_ID=sz.SZAMLAFEJ_ID) ON
其中 ARUNEV='A7'。
*/

查询视图

使用 CEGESDATA
开始

SELECT t.*, a.ARUNEV, a.MENNYEGYS, a.EGYSEGAR, a.EGYSEGAR*t.MENNYISEG as 'Netto ertek', a.EGYSEGAR*t.MENNYISEG*(a.AFAKULCS/100) as 'AFA ERTEK'.
,(a.EGYSEGAR*t.MENNYISEG*(a.AFAKULCS/100))+(a.EGYSEGAR*t.MENNYISEG) 为'GROSS ERTEK' 。
FROM szamlazas.szamlatetel t INNER JOIN szamlazas.ARU a ON t.ARU_ID=a.ARU_ID
/*存在(t.ARU_ID=a.ARU_ID)*/

选择 *
FROM samlazas.tetelek

创建视图

创建视图 samlazas.bad_samla_head
作为
选择 *
from samlazas.samlahead
where VEVO_ID not in (select vevoo_id from szamlazas.vevo)

创建模式

使用 [cegesadata]
去

/****** Object: schema [samlazas] Script Date: 2022. 10/25 0:11:57 ******/
创建 [samlazas] 模式
GO

创建用户、登录、权限、角色

使用 [master]
运行
CREATE LOGIN [test1] WITH PASSWORD=N'Password123', DEFAULT_DATABASE=[CEGESADATOK], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
运行
使用 [CEGESADATOK]
运行
为登录 [test1] 创建用户 [test1]
开始
使用 [cegesdata]
开始
ALTER ROLE [db_backupoperator] ADD MEMBER [test1] 添加成员
执行
使用 [cegesdata]
GO
ALTER ROLE [db_datareader] ADD MEMBER [test1] Add.
GO
使用 [cegesadata]
运行
ALTER ROLE [db_datawriter] ADD MEMBER [test1] ALTER ROLE [db_datareader] ADD MEMBER [test1] GO
执行
使用 [cegesdata]
执行
ALTER ROLE [db_ddladmin] ADD MEMBER [test1] ALTER ROLE [db_datawriter] ADD MEMBER [test1] GO
执行
使用 [cegesdata]
GO
ALTER ROLE [db_securityadmin] ADD MEMBER [test1] ALTER ROLE [db_ddladmin] ADD MEMBER [test1] GO
GO

删除表格、截断表格

/****** SSMS 的 SelectTopNRows 命令脚本 ******/
select top (1000) [samlahead_id] ,[VEVO_ID] 。
      ,[VEVO_ID]
      ,[KELTE]
      ,[TELJESITES]
      ,[FIZHATARIDO]
      ,[NAVATKULDVE] 。
      ,[状态]
  从 [CEGESADATOK].[samlazas].[samlahead].

  截断表 szamlazas.szamlafej

  批量插入表 samlazas.samlahead
  FROM N'E:\szamlafej.table.csv'.
  with (fieldterminator=';',rowterminator='\n')

######3 SQL ###### 考试

创建表格(创建表格)

创建表 apprentices(
	id int、
	name varchar(50)、
	年龄 int、
	country varchar(50)、
	cohort_id int)

创建表 cohorts(
	id int、
	name varchar(50)、
	started_at date、
	类型 varchar(50))

导入表格(批量插入)

/****** SSMS 的 SelectTopNRows 命令脚本 ******/
  批量插入学徒
  FROM N'E:\apprentices.table.csv' WITH (fieldterminator=';',rowterminator='\n'/*)
  WITH (fieldterminator=';',rowterminator='\n'/*, FIRSTROW=2 ha van fejlec*/)

  批量插入队列
  FROM N'E:\cohorts.table.csv'
  WITH (fieldterminator=';',rowterminator='\n')

列出所有队列的名称(name)和开始日期(started_at)(队列表)
文件名: cohorts.sql

SELECT name, started_at
 FROM 队列

列出所有学生的姓名和原籍国(学徒表)
文件名: apprentices.sql

SELECT name, country
FROM 学徒

列出 21 岁以下学生(学徒表)的姓名和年龄
文件名:学徒-年轻-21.sql

SELECT name, age
FROM 学徒
WHERE age < 21

列出非匈牙利(国家)学生(学徒表)的身份和姓名
Filename: apprentices-not-from-hu.sql

SELECT id, name
FROM 学徒
WHERE country not like "Hungary

写出数据库中有多少学生(学徒表)的年龄在 20 岁或以上和 30 岁或以下
文件名:学徒-20 和 30 之间.sql

SELECT count(id)
FROM 学徒
WHERE age >= 20 and age <= 30

写出数据库中有多少个 "非全日制 "类型的队列表(类型)
文件名: cohorts-part-time.sql

SELECT count(id)
FROM 队列
WHERE type like 'part-time

列出 2018 年开始学习的学生的学生姓名(作为学徒_姓名)和组群名称(作为组群_名称)。
文件名: apprentices-in-2018.sql

SELECT a.name as apprentice_name, c.name as cohort_name
FROM 学徒 a INNER JOIN 队列 c ON a.cohort_id=c.id
WHERE FORMAT(started_at, 'yyyyy')=2018

写出数据库中没有学生的班级数量
文件名: cohorts-without-apprentice.sql

SELECT count(id)
FROM 队列
WHERE id NOT IN (SELECT cohort_id FROM apprentices)

按学生姓名字母顺序列出学生姓名和培训开始日期
文件名: apprentices-start-date-ordered.sql

SELECT a.name, c.started_at
FROM 学徒 a INNER JOIN 队列 c ON a.cohort_id=c.id
ORDER BY a.name

按照学徒人数从多到少的顺序,列出给定年级的名称和学徒人数(作为学徒人数)。包括目前没有学生的年级组。
文件名: cohort-sizes.sql

SELECT c.name, count(a.id) as numberOfApprentices
FROM cohorts c LEFT JOIN apprentices a ON a.cohort_id=c.id
GROUP BY c.name
ORDER BY count(a.id) DESC

####### ### SQL 管理项目任务

1) 在服务器上添加了一个尚未使用的磁盘
(将 S: 添加为驱动器和卷名:
SQLDATA 应为

2) 在 S: 驱动器上创建一个 SQLDATA 目录,并在其中
还有两个名为 DB 和 LOG 的子目录。

3) 登录 SQL 服务器(GREENFOX 实例)(此步骤
您不必保存!:-)

4) 还原 C:/install\MSSQL\AdventureWorks2016.bak
数据库到服务器的 GREENFOX 实例,以便数据库
文件在 S:\SQLDATA\DB,而日志文件在 S:\SQLDATA\DB。
日志库。

 
使用 [master]
RESTORE DATABASE [AdventureWorks2016] FROM DISK = N'C:\install\MSSQL\AdventureWorks2016.bak' WITH FILE = 1, MOVE N'AdventureWorks2016_Data' TO N'S:\SQLDATADB\AdventureWorks2016_Data.mdf', MOVE N'AdventureWorks2016_Log' TO N'S:\SQLDATA\LOG\AdventureWorks2016_Log.ldf', NOUNLOAD, STATS = 5

开始

5) 创建一个 interface_svc MS SQL 验证账户、
最低(公共)权限!您的密码应为 INTERface。

使用 [master]
运行
CREATE LOGIN [interface_svc] WITH PASSWORD=N'INTERFACE', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
运行

6) 创建名为 INTERFACE 的数据库,设置如下:
- 数据库名称: INTERFACE
- 所有者:interface_svc
- 恢复模式:FULL
- 初始数据库文件:
(a) 大小:32 MB 字节
(b) 位置:S:\SQLDATA\DB
- 启动日志文件:
(a) 大小:16 MByte
(b) location: S:\SQLDATA\LOG

创建数据库 [接口]
 包含 = 无
 ON PRIMARY
( NAME = N'INTERFACE', FILENAME = N'S:\SQLDATA\DB\INTERFACE.mdf' , SIZE = 32768KB , FILEGROWTH = 65536KB )
 登录
( NAME = N'INTERFACE_log' , FILENAME = N'S:\SQLDATA\LOG\INTERFACE_log.ldf' , SIZE = 16384KB , FILEGROWTH = 65536KB )
返回

使用 [interface]
GO
ALTER AUTHORIZATION ON DATABASE::[INTERFACE] TO [interface_svc] [接口授权
/*
执行 sp_changedbowner 'interface_svc' 命令
执行
*/

alter database [interface] set compatibility_level = 150
执行
alter database [interface] set ansi_null_default off
关闭
更改数据库 [interface] 设置 ansi_nulls 关闭
关闭
更改数据库 [界面],关闭 ansi_padding
关闭
更改数据库 [接口],关闭 ansi_警告
关闭
更改数据库 [接口],关闭 arithabort
关闭
更改数据库 [接口],关闭自动关闭功能
关闭
更改数据库 [接口] 关闭自动收缩
关闭
alter database [interface] set auto_create_statistics on(incremental = off)
关闭
alter database [interface] set auto_update_statistics on (incremental = off)
运行
alter database [interface] set cursor_close_on_commit off
关闭
更改数据库 [接口] 设置游标默认值为全局
运行
alter database [interface] set concat_null_yields_null off
关闭
alter database [interface] set numeric_roundabort off
关闭
更改数据库 [接口] 设置 quoted_identifier 关闭
关闭
更改数据库 [接口] 关闭递归触发器
关闭
更改数据库 [接口] 设置禁用经纪人
关闭
更改数据库 [接口] 设置自动更新统计同步关闭
关闭
更改数据库 [接口] 设置关闭日期相关性优化
关闭
更改数据库 [接口] 设置参数化简单
关闭
alter database [interface] set read_committed_snapshot off
关闭
更改数据库 [接口] 设置读写
运行
更改数据库 [界面] 设置完全恢复
执行
更改数据库 [界面] 设置多用户
运行
更改数据库 [界面] 设置页面验证校验和
运行
更改数据库 [interface] 设置 target_recovery_time = 60 秒
执行
alter database [interface] set delayed_durability = disabled
运行
使用 [接口]
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = Off;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = Primary;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINITY_ESTIMATION = Primary; GO
GO
alter database scoped configuration for secondary set maxdop = primary;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = On;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = Primary;
完成
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = Off;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = Primary;
完成
使用 [接口]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [INTERFACE] MODIFY FILEGROUP [PRIMARY] DEFAULT
转到

7) 仅向 interface_svc 账户授予查询权限,并使用
人力资源模式中的 AdventureWorks2016 数据库
v员工视口(VIEW)!

使用 [AdventureWorks2016] 功能
开始
为登录用户 [interface_svc] 创建用户 [interface_svc]
开始
GRANT SELECT ON [HumanResources].[vEmployee] TO [interface_svc].
GO

8) 在 INTERFACE 数据库中创建 AWDB 模式。脚本
"创建为 "格式

使用 [接口]
开始

/****** Object: schema [AWDB] Script Date: 12/9/2022 8:21:47 AM ******/
创建模式 [AWDB]
GO

9) 在 AWDB 模式中创建名为 AW_Employee 的同义词、
它位于 AdventureWorks2016 数据库的人力资源模式中
指向 vEmployee 视口!

使用 [接口]
开始

/****** 对象:同义词 [AWDB].[AW_Employee] 脚本日期:12/6/2022 1:59:44 PM ******/
CREATE SYNONYM [AWDB].[AW_Employee] FOR [AdventureWorks2016].[HumanResources].[vEmployee].
转到

10) 使用 AWDB 创建一个将发送到 INTERFACE 数据库的查询
模式中 AW_Employee 同义词的所有行和列
.................

使用 [接口]
开始
SELECT *
FROM AWDB.AW_Employee

11) 对 INTERFACE 数据库进行完整备份,备份内容如下
根据:
a) 备份文件名称:S:\SQLdata\INTERFACE.bak
b) 保存会覆盖同名介质集!

BACKUP DATABASE [INTERFACE] TO DISK = N'S:\SQLdata\INTERFACE.bak' WITH NOFORMAT, INIT, NAME = N'S:\SQLdata\INTERFACE.bak', SKIP, NOREWIND, NOUNLOAD, STATS = 10
运行

1) 获取 SQL 服务器版本和 SQL 服务器发布版本!

选择 @@version

2) 制作一个简单的 "Hello World!查询!

SELECT 'Hello World!

3) 创建一个 SQL 查询,计算 30 的平方根!

选择 sqrt(30)

4) 创建一个 SQL 查询,返回今天的日期!

SELECT CAST(GETDATE() as date)

5) 创建一个 SQL 查询,计算已经过去了多少天
自 1989 年 3 月 15 日起!

SELECT DATEDIFF(day,'1989-03-15', GETDATE());

6) 创建一个 SQL 查询,返回先前加载的
AdventureWorks 数据库中 Person.Address 表的前 20 个元素
(所有领域)!

/****** SSMS 的 SelectTopNRows 命令脚本 ******/
select top (20) *
  FROM [AdventureWorks2016].[人员].[地址]

7) 创建一个 SQL 查询,返回先前加载的
AdventureWorks 数据库中 Person.Person 表的前 20 个元素
(所有字段),但其中 PersonType 字段为 "EM"!

/****** SSMS 的 SelectTopNRows 命令脚本 ******/
select top (20) *
  FROM [AdventureWorks2016].
  WHERE [AdventureWorks2016].[Person].[Person].PersonType='EM'

8) 创建一个 SQL 查询,返回之前加载的
在 AdventureWorks 数据库中的 Person.Person 表的前 20 个元素,但
这样,当 PersonType 字段为 "EM "时,只有 PersonType、
查询结果中应包含 "名"(FirstName)和 "姓"(LastName)字段!

/****** SSMS 的 SelectTopNRows 命令脚本 ******/
SELECT TOP (20) PersonType, FirstName, LastName
  FROM [AdventureWorks2016].
  WHERE [AdventureWorks2016].[Person].[Person].PersonType='EM'

9) 创建一个 SQL 查询,返回先前加载的
在 AdventureWorks 数据库中的 Person.Person 表的前 20 个元素,但
这样,当 PersonType 字段为 "EM "时,只有 PersonType、
名"、"姓 "字段应该出现在查询结果中,但"......
名和姓应出现在一列中,并用一个空格隔开
用一个字符分隔!

/****** SSMS 的 SelectTopNRows 命令脚本 ******/
SELECT TOP (20) PersonType, FirstName + ' ' + LastName
  FROM [AdventureWorks2016].
  WHERE PersonType='EM'。

10) 创建一个 SQL 查询,返回先前加载的
AdventureWorks 数据库的 Person.Person 表中的记录
(所有字段),其中 PersonType 字段为 "EM",FirstName
字段应只显示名称为 "John "的信息!

/****** SSMS 的 SelectTopNRows 命令脚本 ******/
SELECT *
  FROM [AdventureWorks2016].
  WHERE PersonType='EM' AND FirstName='John'.

11) 创建一个 SQL 查询,返回之前加载的
AdventureWorks 数据库的 Person.Person 表中的记录
(所有字段),其中 PersonType 字段为 "EM",FirstName
字段应只显示名称为 "John "的信息,其中的
LastName 字段值以 "e "字符结尾!

/****** SSMS 的 SelectTopNRows 命令脚本 ******/
SELECT *
  FROM [AdventureWorks2016].
  WHERE PersonType='EM' AND FirstName='John' AND RIGHT(LastName,1)='e'

12) 创建一个 SQL 查询,返回先前加载的
AdventureWorks 数据库的 Person.Person 表中的记录
(所有字段),其中 PersonType 字段为 "EM",FirstName
字段应只显示名称为 "John "的信息,其中的
LastName 字段值包含 "Chen "或 "Kane"!

/****** SSMS 的 SelectTopNRows 命令脚本 ******/
SELECT *
  FROM [AdventureWorks2016].
  WHERE PersonType='EM' AND FirstName='John' AND (LastName='Chen' OR LastName='Kane')

13) 数一数有多少个不同的名字(FirstName)。
中的 Person.Person 表!

/****** SSMS 的 SelectTopNRows 命令脚本 ******/
SELECT COUNT(DISTINCT FirstName)
  FROM [AdventureWorks2016].

14) 计算数据库中名字为 "Ken"(FirstName)的记录数量。
中的 Person.Person 表!

/****** SSMS 的 SelectTopNRows 命令脚本 ******/
SELECT COUNT(FirstName)
  FROM [AdventureWorks2016].
  WHERE FirstName='Ken

15) 计算有多少条记录具有相同的姓名(名和姓)
是先前加载的 AdventureWorks 数据库 Person.Person
在黑板上!排列结果,使最常用的
在列表顶部的姓名配对,并且只有前 20 个结果
包括在内!

/****** SSMS 的 SelectTopNRows 命令脚本 ******/
SELECT TOP (20) FirstName, LastName, COUNT(1)
  FROM [AdventureWorks2016].[人员].[人员]
  GROUP BY FirstName, LastName
  order by count(1) desc

16) 计算有多少条记录具有相同的姓名(名和姓)
是先前加载的 AdventureWorks 数据库 Person.Person
在黑板上!将结果排列成只有 4 个和 4 个以上
应列出名称对中使用的

SELECT FirstName, LastName, COUNT(1)
  FROM [AdventureWorks2016].
  GROUP BY FirstName, LastName
  且 count(1) >= 4
  order by count(1) desc

17) 创建一个 SQL 查询,返回先前加载的
AdventureWorks 数据库 Person.Person 和 Person.EmailAddress
表,其中包含以下字段:Person.Person.FirstName.姓名
Person.人名.姓氏、Person.电子邮件地址.电子邮件地址,从而使
链接表时,BusinessEntityID 字段同时用于
的结果!只显示结果的前 50 行!

SELECT TOP (50) p.FirstName, p.LastName, e.EmailAddress
  FROM [AdventureWorks2016].[Person].[Person] p INNER JOIN [AdventureWorks2016].[Person].[EmailAddress] e on p.BusinessEntityID=e.BusinessEntityID

18)根据前面的查询,创建一个解决方案,其中电子邮件地址
(EmailAddress)只显示名称部分(在 @ 部分可见之前)!

SELECT TOP (50) p.FirstName, p.LastName, LEFT(e.EmailAddress,CHARINDEX('@',e.EmailAddress)-1)
  FROM [AdventureWorks2016].[Person].[Person] p INNER JOIN [AdventureWorks2016].[Person].[EmailAddress] e on p.BusinessEntityID=e.BusinessEntityID

19)或只查询域部分(@ 后)的查询
可见!

SELECT TOP (50) p.FirstName, p.LastName, RIGHT(e.EmailAddress,LEN(e.EmailAddress)- CHARINDEX('@',e.EmailAddress))
  FROM [AdventureWorks2016].[Person].[Person] p INNER JOIN [AdventureWorks2016].[Person].[EmailAddress] e on p.BusinessEntityID=e.BusinessEntityID

20) (时间任务)创建一个查询,返回
业务实体 ID 字段中的 Sales.SalesPerson 和一个 "minsales "字段。
是一个计算列。minsales 的值应为逐行的 SalesYTD
如果 SalesYTD > 1000000,则为字段,否则为 NULL
增加价值。

/****** SSMS 的 SelectTopNRows 命令脚本 ******/
SELECT [BusinessEntityID], CASE WHEN SalesYTD > 1000000 THEN SalesYTD ELSE NULL END as minsales
  FROM [AdventureWorks2016].[Sales].[SalesPerson].

21) (非常重要的任务)创建一个查询,返回
业务实体 ID 字段中的 Sales.SalesPerson 和一个 "salesinfo "字段。
是一个计算列。salesinfo 的值应在 SalesYTD 中按行设置
字段,如果 SalesYTD >= 2000000,如果
SalesYTD<2000000 但大于或等于 1000000,则
值应为 1500000,如果小于 1000000 则为 NULL。
产出 :

/****** SSMS 的 SelectTopNRows 命令脚本 ******/
SELECT [BusinessEntityID], CASE WHEN SalesYTD >= 2000000 THEN SalesYTD ELSE CASE WHEN SalesYTD>=1000000 THEN 1500000 ELSE NULL END END as salesinfo
  FROM [AdventureWorks2016].[销售].[销售人员]

Microsoft SQL Server(以下简称 SQL)知识水平评估

  • 使用 T-SQL 代码
  • 请务必使用提供的 SQL 文件名
  • 上传的 ZIP 文件不应包含子目录,只应包含文件
  • 确保数据库、用户和其他对象名称区分大小写
  • 在查询中,只有在任务特别要求时才更改要显示的列名
  • 中显示的查询 按练习中给出的顺序排列 出现
  • 确保始终选择正确的数据库
  • 使用 ASCII 或 UTF-8(不含 BOM)字符编码
  • 在 Docker 容器中 "运行 "作业的 MSSQL LOGIN 的默认起始数据库是前 10 个作业: .
  • 在 Docker 容器中,在 Docker 容器中 "运行 "作业的 MSSQL LOGIN 的默认起始数据库为 11) 到 20) 个作业: 冒险工场.对于这些任务,您上传的文件只需包含查询即可。
  • 对 sql 扩展文件的评估主要是 自动交易 其他则由人工评分。

任务。

  1. 向服务器添加一个尚未使用的磁盘 (20GB安装后,使用 NTFS 文件系统创建一个 20 GB 的分区,并添加 S: 挂载为驱动器,卷名为 SQLDATA 应该是
证据。 计算机管理 => 存储 => 磁盘管理
文件名称。 WINSQL-01.jpg
  1. S: 推动创建一个 SQLDATA 目录,以及其中的两个子目录: DB 和 日志 的名字。
证据。 打开文件资源管理器 => S: => SQLDATA 目录,当看到要求的两个子目录时。
文件名称。 WINSQL-02.jpg
  1. 登录 SQL 服务器 (格林福克斯 实例)!
证据。 在 Microsoft SQL Server Management Studio 中打开的 GREENFOX 实例。
文件名称。 WINSQL-03.jpg
  1. 检索 C:/install\MSSQL\AdventureWorks2016.bak 数据库到服务器的 GREENFOX 实例 冒险工场 以便数据库文件在 S:\SQLDATA\DB中的日志文件,而 S:\SQLDATA\LOG 到图书馆。

分级班 您可以在这里找到备份: /AdventureWorks2016.bak数据库文件: /SQLDATA/DB/AdventureWorks_Data.mdf中的日志文件,而 /SQLDATA/LOG/AdventureWorks_Log.ldf 到文件

证据。 在 Microsoft SQL Server Management Studio 中复制或保存还原脚本。
文件名称。 WINSQL-04.sql
使用 [master]
RESTORE DATABASE [AdventureWorks] FROM DISK = N'/home/AdventureWorks2016.bak' WITH FILE = 1, MOVE N'AdventureWorks2016_Data' TO N'/SQLDATA/DB/AdventureWorks_Data.mdf', MOVE N'AdventureWorks2016_Log' TO N'/SQLDATA/LOG/AdventureWorks_Log.ldf', NOUNLOAD, STATS = 5

运行
  1. 创建一个 isvc 具有最小(公共)权限的 MS SQL Server 验证账户!密码应为 接口21 用户首次登录时不必更改密码。
证据。 在 Microsoft SQL Server Management Studio 中复制或保存脚本。
文件名称。 WINSQL-05.sql
使用 [master]
运行
CREATE LOGIN [isvc] WITH PASSWORD=N'interfAce21', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
运行
  1. 创建一个 界面 设置如下
    1. 数据库名称: INTERFACE
    2. 所有者: isvc
    3. 恢复模式:FULL
    4. 初始数据库文件:
      1. 大小:64 MByte
      2. 位置: S:\SQLDATA\DB (关于 Gradescope /SQLDATA/DB)
    5. 启动日志文件:
      1. 大小: 24 MByte
      2. location: S:\SQLDATA\LOG (关于 Gradescope /SQLDATA/LOG)
创建数据库 [接口]
 包含 = 无
 ON PRIMARY
( NAME = N'INTERFACE', FILENAME = N'/SQLDATA/DB/INTERFACE.mdf' , SIZE = 65536KB , FILEGROWTH = 65536KB )
 登录
( NAME = N'INTERFACE_log', FILENAME = N'/SQLDATA/LOG/INTERFACE_log.ldf' , SIZE = 24576KB , FILEGROWTH = 65536KB )
返回

使用 [interface]
去
ALTER AUTHORIZATION ON DATABASE::[INTERFACE] TO [isvc] (更改数据库::[接口]的授权)

alter database [interface] set compatibility_level = 150
执行
alter database [interface] set ansi_null_default off
执行
更改数据库 [interface] 设置 ansi_nulls 关闭
关闭
更改数据库 [界面],关闭 ansi_padding
关闭
更改数据库 [接口],关闭 ansi_警告
关闭
更改数据库 [接口],关闭 arithabort
关闭
更改数据库 [接口],关闭自动关闭功能
关闭
更改数据库 [接口] 关闭自动收缩
关闭
alter database [interface] set auto_create_statistics on(incremental = off)
关闭
alter database [interface] set auto_update_statistics on (incremental = off)
运行
alter database [interface] set cursor_close_on_commit off
关闭
更改数据库 [接口] 设置游标默认值为全局
运行
alter database [interface] set concat_null_yields_null off
关闭
alter database [interface] set numeric_roundabort off
关闭
更改数据库 [接口] 设置 quoted_identifier 关闭
关闭
更改数据库 [接口] 关闭递归触发器
关闭
更改数据库 [接口] 设置禁用经纪人
关闭
更改数据库 [接口] 设置自动更新统计同步关闭
关闭
更改数据库 [接口] 设置关闭日期相关性优化
关闭
更改数据库 [接口] 设置参数化简单
关闭
alter database [interface] set read_committed_snapshot off
关闭
更改数据库 [接口] 设置读写
执行
更改数据库 [界面] 设置完全恢复
运行
更改数据库 [界面] 设置多用户
运行
更改数据库 [界面] 设置页面验证校验和
运行
更改数据库 [interface] 设置 target_recovery_time = 60 秒
执行
alter database [interface] set delayed_durability = disabled
运行
使用 [接口]
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = Off;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = Primary;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINITY_ESTIMATION = Primary; GO
GO
alter database scoped configuration for secondary set maxdop = primary;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = On;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = Primary;
完成
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = Off;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = Primary;
完成
使用 [接口]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [INTERFACE] MODIFY FILEGROUP [PRIMARY] DEFAULT
转到
  1. 只需将查询权交给 isvc 占 冒险工场 资料库 人力资源 计划中 雇员 在你的观察名单上!
证据。 在 Microsoft SQL Server Management Studio 中复制或保存脚本。
文件名称。 WINSQL-07.sql
USE [AdventureWorks]
开始
为登录用户 [isvc] 创建用户 [isvc]
开始
GRANT SELECT ON [HumanResources].
转到
  1. 界面 数据库来创建一个 AWdatabase 计划!
证据。 Microsoft SQL Server Management Studio 在 GREENFOX 实例的 INTERFACE 数据库 AWdatabase 模式中的 "创建至 "脚本。
文件名称。 WINSQL-08.sql
使用 [接口]
开始

/****** 对象:模式 [dbo] 脚本日期:2023 Jan 15 13:45:32 ******/
创建 [AWdatabase] 模式
GO
  1. 来自 AWdatabase 计划,以创建一个 同义词 AW_Employee 即 冒险工场 资料库 人力资源 计划中 雇员 指向视口!
证据。 Microsoft SQL Server Management Studio 中 GREENFOX 实例的 INTERFACE 数据库 AWdatabase 模式 AW_Employee 与 "创建至 "脚本同义。
文件名称。 WINSQL-09.sql
使用 [接口]
开始
CREATE SYNONYM [AWdatabase].[AW_Employee ] FOR [AdventureWorks].[HumanResources].[vEmployee].
开始
  1. 使 界面 数据库的完整备份,如下所示:
  1. 备份文件名称: S:\SQLDATA\INTERFACE.bak (在 Gradescope 中 /var/backups/INTERFACE.bak)
  2. 保存会覆盖同名介质集!
证据。 在 GREENFOX 实例的 Microsoft SQL Server Management Studio 中,INTERFACE 数据库备份脚本。
文件名称。 WINSQL-10.sql
备份 DATABASE [INTERFACE] 到 DISK = N'/var/backups/INTERFACE.bak' WITH NOFORMAT, INIT, NAME = N'INTERFACE-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
运行
  1. 创建一个 SQL 查询,计算 25 的平方根!
证据。 在 Microsoft SQL Server Management Studio 中复制或保存脚本。
文件名称。 WINSQL-11.sql
选择 sqrt(25)
  1. 创建一个 SQL 查询,以这种格式返回今天的日期: 2022-10-25
证据。 在 Microsoft SQL Server Management Studio 中复制或保存脚本。
文件名称。 WINSQL-12.sql
SELECT CAST(GETDATE() as date)
  1. 创建一个 SQL 查询,计算自 1994 年 4 月 25 日以来已经过去了多少天!
证据。 在 Microsoft SQL Server Management Studio 中复制或保存脚本。
文件名称。 WINSQL-13.sql
SELECT DATEDIFF(day,'2022-04-25',GETDATE())
  1. 创建一个 SQL 查询,返回之前加载的 冒险工场 资料库 个人地址 表(所有字段,请勿更改列的顺序)!
证据。 在 Microsoft SQL Server Management Studio 中复制或保存脚本。
文件名称。 WINSQL-14.sql
/****** SSMS 的 SelectTopNRows 命令脚本 ******/
USE [AdventureWorks]
执行
select top (15) *
  FROM [AdventureWorks].[人员].[地址]
  1. 创建一个 SQL 查询,返回之前加载的 冒险工场 资料库 Person.Person 从 前 5 项(所有字段--不要改变列的顺序),但要使 人物类型 字段值 'EM‘!
证据。 在 Microsoft SQL Server Management Studio 中复制或保存脚本。
文件名称。 WINSQL-15.sql
USE [AdventureWorks]
开始
select top (5) *
  FROM [AdventureWorks].
  WHERE PersonType='EM'人名类型
  1. 创建一个 SQL 查询,返回之前加载的 冒险工场 资料库 Person.Person 从 前30项但这样一来 人物类型 字段值 'EM',只有 人物类型、名、姓 查询结果中的字段!
证据。 在 Microsoft SQL Server Management Studio 中复制或保存脚本。
文件名称。 WINSQL-16.sql
USE [AdventureWorks]
返回
SELECT TOP (30) PersonType, FirstName,LastName
  FROM [AdventureWorks].
  WHERE PersonType='EM'人名类型
  1. 创建一个 SQL 查询,返回之前加载的 冒险工场 资料库 Person.Person 从 前20项但这样一来 人物类型 字段值 'EM',只有 人物类型、名、姓 字段,但 名和姓 应出现在一列中,中间用一个空格隔开、 全名 名下!
证据。 在 Microsoft SQL Server Management Studio 中复制或保存脚本。
文件名称。 WINSQL-17.sql
USE [AdventureWorks]
返回
SELECT TOP (20) PersonType, FirstName+ ' ' + LastName as FullName
  FROM [AdventureWorks].
  WHERE PersonType='EM'人名类型
  1. 创建一个 SQL 查询,返回之前加载的 冒险工场 资料库 Person.Person 的表格(所有列、所有字段--不要改变列的顺序),其中 人物类型 字段值 'EM'和  字段只包含"......"。约翰'应包括在内!
证据。 在 Microsoft SQL Server Management Studio 中复制或保存脚本。
文件名称。 WINSQL-18.sql
USE [AdventureWorks]
开始
SELECT *
  FROM [AdventureWorks].
  WHERE PersonType='EM' AND FirstName='John'.
  1. 创建一个 SQL 查询,返回之前加载的 冒险工场 资料库 Person.Person 的表格(所有列、所有字段--不要改变列的顺序),其中 人物类型 字段值 'EM'和  字段只包含"......"。约翰'的地方,只显示 姓氏 字段值 'e字符结尾!
证据。 在 Microsoft SQL Server Management Studio 中复制或保存脚本。
文件名称。 WINSQL-19.sql
USE [AdventureWorks]
开始
SELECT *
  FROM [AdventureWorks].
  WHERE PersonType='EM' AND FirstName='John' AND RIGHT(LastName,1)='e'
  1. 数数有多少 'Ken'名 ()记录是之前加载的 冒险工场 资料库 Person.Person 在你的电路板上!
证据。 在 Microsoft SQL Server Management Studio 中复制或保存脚本。
文件名称。 WINSQL-20.sql
USE [AdventureWorks]
执行
select count(*)
  FROM [AdventureWorks].
  WHERE FirstName='Ken