计算机基础：数据库-Sqlserver
前言
好好学习。
内容参考：
纯净天空、Sqlserver教程、Mockdata
小记
//值得记录的点：
1.Sql server 多列去重复值，相同的只显示一条数据
SELECT CASE when row=1 THEN headerNo ELSE '' END headerNo,
CASE when row=1 THEN machineNO ELSE '' END machineNO，
artNo,qty,repartno,repqty FROM
(SELECT *,row_number()OVER(PARTITION BY headerNo,machineNO ORDER BY GETDATE()) row
FROM #tp )M
2. 临时表查询
select DName,DCode,DDate from
(values
('DemoA','AAA',GETDATE()),
('DemoB','BBB',GETDATE()),
('DemoC','CCC',GETDATE()),
('DemoD','DDD',GETDATE()),
('DemoE','EEE',GETDATE())
)
Demo_Values (DName,DCode,DDate)
3. navicat sqlserver设置上默认值
GETDATE()
4. navicat sqlserver设置自增字段
必须在创建时设置选项里面的标识字段，即可。
5. 数据库引擎：innodb、myisam、memery、csv、blackhole、archive、federated、performance_schema。其中innodb和myisam占到95%以上。
6. with 用法
- 利用with建立临时表，当建立第二个的时候可以with可以省略
- 不可嵌套
WITH EmployeeCTE AS (
SELECT EmployeeID,FirstName,LastName,Salary FROM Employees WHERE Department = 'IT'
)
-- Query using the CTE
SELECT EmployeeID,FirstName,LastName,Salary FROM EmployeeCTE WHERE Salary > 50000;
7. 在 SQL 中，count(1)、count(*) 和 count(列名) 区别？？？
-- count(1)、count(*) 选择所有行包含null值；count(列名)，该列不包含null值得所有行。
-- 列名为主键，或索引，count(列名）效率最佳（那个那count(fid)，不杠杠的）。
8. 关键词
- 常见的函数：
>ISNUMERIC、IIF、mid、ISNULL、SubString、avg、count、first、last、max、min、sum、len、lenght、ROUND、Now、FORMAT、UCASE、LCASE、STUFF、ISNULL、
- 查询：
>distinct、top 50 PERCENT 、* 、as ；not 、and、or 、IN、 BETWEEN AND 、IS NULL、IS NOT NULL、union、union all、LIMIT 5、 ASC|DESC、Like、NOT LIKE （）、 print变量或表达式 || select
exists，not exists,(all大于平均价格) ,any,some;
数据库代码案例
//函数例子：
1. ISNUMERIC(expression)检查指定的表达式是否为数字.数字形式，则此函数返回1。不是数字，则此函数返回0
DECLARE @exp INT;
SET @exp = 44;
SELECT ISNUMERIC(@exp); //1
2. IIF()函数类似于CASE表达式
SELECT IIF(40 < 60, 'True', 'False') AS Result ;
3.MID() 函数用于从文本字段中提取字符。
SELECT MID(name,1,4) AS ShortTitle FROM Websites;
4.ROUND() 函数用于把数值字段舍入为指定的小数位数。
SELECT ROUND(1.298, 1);//1.3
SELECT ROUND(-1.23);//-1
5. FORMAT() 函数用于对字段的显示进行格式化。
SELECT name, url, DATE_FORMAT(Now(),'%Y-%m-%d') AS date
FROM Websites;
6. stuff() 函数用于从源字符串中删除给定长度的字符序列，
select stuff('geeksforgeeks',5,3,'1234');
//geek1234rgeeks
7. 指定的表达式，要检查它是否为NULL
SELECT ISNULL('gfg', 'Geeks'); //gfg
SELECT ISNULL(NULL, 'Geeks'); //Geeks
DECLARE @exp VARCHAR(50);
DECLARE @val VARCHAR(50);
SET @exp = NULL;
SET @val = 'GFG';
SELECT ISNULL(@exp, @val);
//select 查询数据
1. LIKE 'G%'、'%k'、'%oo%'like '[^TP]%'、like '_oogle' //不以TP开头的替代一个字符
2. SELECT name, CONCAT(url, ', ', alexa) AS site_info
3. SELECT country FROM Websites UNION SELECT country FROM apps ORDER BY country;
4. SELECT name, COUNT(*) FROM employee_tbl GROUP BY name
5. 空白列
- select case when ISNUMERIC(字段)=1 then 汉字 else 字段 end from 表名;
- select 字段,NULL as 空白列 from 表名;
6. select * from st where not exists(select * from st where id>10)子查询
//insert:插入数据
1. INSERT INTO Websites (name, url, alexa, country) VALUES ('百度','url','4','CN');
2. INSERT INTO Websites VALUES ('百度','https://www.baidu.com/','4','CN');
3.INSERT INTO table2 (column_name) SELECT column_name FROM table1;//只有几列
4.SELECT fid,fname INTO #TempUpdateData FROM table1;
DROP TABLE #TempUpdateData;
5.insert into 表名(列名1，列名n) values(值1，值n) , values(值1，值n)//插入多行
//update:更新数据
- UPDATE Websites SET alexa='5000', country='USA' WHERE name='菜鸟教程';
-多表关联更新：
update a set a.f_sfur_jhy = two.fplanner
from t_sal_order a
join t_sal_orderentry b on b.fid = a.fid
join t_bas_prebdtwo con c.fid = b.f_bgj_ixm
where a.count = 0
//delete:删除数据
- DELETE FROM Websites WHERE name='Facebook' AND country='USA';
//index:索引；更快地查找数据。
//在 "Persons" 表的 "LastName" 列上创建一个名为 "PIndex" 的索引
- CREATE INDEX PIndexON Persons (LastName, FirstName)
//DROP： 语句，可以轻松地删除索引、表和数据库。
1.DROP TABLE table_name //删除表
2.DROP DATABASE database_name //删除数据库
3.TRUNCATE TABLE table_name //删除表内的数据
4.DROP VIEW view_name//删除视图
5.drop trigger trigger_name//删除触发器
//Join: 七种
自然连接：结果中把重复的属性列去掉。而等值连接并不去掉重复的属性列。
内连接：返回两张表都满足条件的部分
左外连接：取左边的表的全部，右边的表按条件，符合的显示，不符合则显示null
右外连接：取右边的表的全部，左边的表按条件，符合的显示，不符合则显示null
全连接：全连接（full join）结合的左，右外连接的结果。连接表将包含的所有记录来自两个表，并使用NULL值作为两侧缺失匹配结果
select A.a,A.b,A.c,B.d from A.c = B.c
select * from A inner join B on A.id=B.id
select * from A left join B on A.id=B.id
select * from A right join B on A.id=B.id
select * from A left join B on A.id=B.id where b.id is null
select * from A right join B on A.id=B.id where a.id is null
select * from A full outer join B on A.id=B.id
select * from A full outer join B on A.id=B.id where a.id is null or b.id is null
视图
//。是一个虚拟的表，它基于一个或多个表的查询结果而创建。
可以考虑创建视图：
--简化复杂查询：如果数据库中存在复杂的查询逻辑，涉及多个表的连接、筛选和聚合操作，可以创建视图来封装这些复杂查询，使其更易于使用和理解。视图可以提供一个简化的数据访问界面，隐藏底层查询的复杂性。
--数据安全和访问控制：通过创建视图，可以限制用户对敏感数据的直接访问。视图可以仅暴露特定的列或行，并且可以根据用户的权限设置来控制数据的访问。这有助于确保数据的安全性和隐私保护。
-性能优化：在某些情况下，通过创建视图可以提高查询性能。视图可以预计算、缓存或索引某些查询结果，以减少重复计算和提高查询的响应时间。
更新视图（Updatable Views）：某些视图可以允许对其进行数据的更新、插入和删除操作。这种更新是通过触发器或与视图相关联的基础表上的规则实现的。要使视图可更新，需要满足一定的条件，如视图必须包含唯一标识符、不包含GROUP BY、HAVING或DISTINCT子句等。
1.创建视图：
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
2.
CREATE VIEW SalesView AS
SELECT ProductName, UnitPrice, Quantity, UnitPrice * Quantity AS TotalPrice
FROM Sales
WHERE OrderDate >= '2023-01-01';
触发器
1.insert update delete 操作时（当时，之后）
- 行级触发器（Row-Level Triggers）：这种触发器在每一行受到影响时都会触发执行。行级触发器可以定义在单个表上。
- 语句级触发器（Statement-Level Triggers）：这种触发器在每个SQL语句执行完成时触发执行，而不是在每一行受到影响时触发。语句级触发器可以定义在多个表上。
2. 一般语法：
inserted、deleted //是一个临时表,包含由INSERT或UPDATE操作插入或更新的数据行。
DECLARE @columnName INT（VARCHAR(30) DECIMAL(10, 2)）//声明变量
DECLARE @StartDate DATE = '2023-01-01';
DECLARE @EndDate DATE = '2023-06-30';
@@ROWCOUNT //返回受最近的INSERT、UPDATE或DELETE语句影响的行数。
@@IDENTITY 、SCOPE_IDENTITY()//在执行插入操作后返回最近生成的自增主键值
-IF语句：例如，IF EXISTS(SELECT * FROM inserted WHERE Column1 = 'Value') BEGIN ... END 可以在inserted表中的特定列的值满足条件时执行一段逻辑。
-RAISERROR语句：例如，RAISERROR('Error Message', 16, 1);
-SET： SET @variable = (SELECT Column1 FROM inserted);
-SELECT：SELECT @variable = Column1 FROM OtherTable WHERE Column2 = (SELECT Column3 FROM inserted);
CREATE TRIGGER trigger_name
ON table_name //AFTER
FOR INSERT, UPDATE, DELETE
AS
BEGIN
-- 触发器的逻辑代码
END;
3. 向一个名为"Orders"的表插入新的订单时，我们可以创建一个行级触发器来自动更新与该订单相关的其他表
-- 创建触发器
CREATE TRIGGER UpdateOrderTotal
ON Orders
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
-- 更新订单总额
UPDATE O
SET TotalAmount = (SELECT SUM(Quantity * Price) FROM OrderItems WHERE OrderID = O.OrderID)
FROM Orders O
INNER JOIN inserted I ON O.OrderID = I.OrderID
WHERE O.OrderID IN (SELECT OrderID FROM inserted)
-- 更新客户的订单总额
UPDATE C
SET TotalOrderAmount = (SELECT SUM(TotalAmount) FROM Orders WHERE CustomerID = C.CustomerID)
FROM Customers C
INNER JOIN Orders O ON C.CustomerID = O.CustomerID
INNER JOIN inserted I ON O.OrderID = I.OrderID
WHERE C.CustomerID IN (SELECT CustomerID FROM inserted)
END;
4. 我们希望在更新员工薪水时，确保薪水不低于特定的最低值。
CREATE TRIGGER UpdateSalaryTrigger
ON Employees
AFTER UPDATE
AS
BEGIN
-- 检查薪水是否低于最低值
IF EXISTS (SELECT * FROM inserted WHERE Salary < 1000.00)
BEGIN
RAISERROR('Salary cannot be lower than the minimum value of 1000.00.', 16, 1);
ROLLBACK TRANSACTION;
RETURN;
END;
-- 更新薪水
UPDATE E
SET Salary = I.Salary
FROM Employees E
INNER JOIN inserted I ON E.EmployeeID = I.EmployeeID;
END;
5. 向 SourceTable 中插入新的数据时，自动将该数据复制到 DestinationTable。
CREATE TRIGGER InsertCopyTrigger
ON SourceTable
AFTER INSERT
AS
BEGIN
INSERT INTO DestinationTable (ID, Data)
SELECT ID, Data
FROM inserted;
END;
6. 自动更新对应订单的总额，并将更新后的总额插入到Orders表中的TotalAmount字段。
BEGIN
DECLARE @OrderID INT;
DECLARE @TotalAmount DECIMAL(10, 2);
-- 获取插入的订单项的订单ID和总额
SELECT @OrderID = OrderID, @TotalAmount = SUM(Quantity * Price)
FROM inserted
WHERE OrderID IS NOT NULL
GROUP BY OrderID;
-- 更新对应订单的总额
UPDATE Orders
SET TotalAmount = @TotalAmount
WHERE OrderID = @OrderID;
END;
索引
唯一索引（UNIQUE）：每一行的索引值都是唯一的（创建了唯一约束，系统将自动创建唯一索引）
主键索引：当创建表时指定的主键列，会自动创建主键索引，并且拥有唯一的特性。
聚集索引（CLUSTERED）：聚集索引就相当于使用字典的拼音查找，因为聚集索引存储记录是物理上连续存在的，即拼音 a 过了后面肯定是 b 一样。
非聚集索引（NONCLUSTERED）：非聚集索引就相当于使用字典的部首查找，非聚集索引是逻辑上的连续，物理存储并不连续。
PS：聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。
聚集索引（Clustered Index）：
聚集索引决定了数据行在表中的物理存储顺序。每个表只能有一个聚集索引，因为它决定了数据行在磁盘上的存储方式。聚集索引的建立会对表的物理结构产生影响。
非聚集索引（Nonclustered Index）：
非聚集索引创建一个独立的索引结构，其中索引的顺序与数据行的物理存储顺序不一致。一个表可以有多个非聚集索引，它们可以加速基于非聚集索引列的查询。
唯一索引（Unique Index）：
唯一索引是一种非聚集索引，它确保索引列的值在表中是唯一的。唯一索引可以帮助确保数据完整性，并加速唯一值的查找。
主键索引（Primary Key Index）：
主键索引是一种特殊的唯一索引，它定义了表中的主键列。主键索引可以确保主键列的值在表中是唯一的，并且主键列不允许为空。
覆盖索引（Covering Index）：
覆盖索引是一种包含了查询所需的所有列的索引。当查询只需要索引包含的列而无需访问实际数据行时，覆盖索引可以显著提高查询性能。
空间索引（Spatial Index）：
空间索引是用于处理空间数据类型（如几何形状）的索引。它可以帮助加速空间查询，如查找包含在指定范围内的对象。
全文索引（Full-Text Index）：
全文索引是用于全文搜索的索引类型。它可以加速对文本内容的搜索，支持关键字搜索、模糊搜索和近似搜索等操作。
XML 索引（XML Index）：
XML索引是用于处理XML数据类型的索引。它可以帮助加速针对XML数据的查询和分析。
假设我们有一个名为"Employees"的表，其中包含以下列：EmployeeID（聚集索引列）、FirstName、LastName、Salary。
CREATE CLUSTERED INDEX IX_Employees_EmployeeID ON Employees (EmployeeID);
CREATE NONCLUSTERED INDEX IX_Employees_LastName ON Employees (LastName);
锁：key
共享锁（Shared Lock）：多个事务可以同时持有共享锁，用于读取数据。共享锁不阻止其他事务获取相同的共享锁，但会阻止其他事务获取排他锁。这意味着共享锁允许并发读取，但不允许并发写入。
排他锁（Exclusive Lock）：排他锁用于写操作，只允许持有排他锁的事务进行修改，并阻止其他事务获取共享锁或排他锁。当一个事务持有排他锁时，其他事务无法同时持有任何类型的锁。
更新锁（Update Lock）：更新锁是一种特殊的锁模式，用于提高并发性能。它在读取操作之前获取共享锁，在修改操作之前升级为排他锁。这样可以避免两个事务同时对同一数据进行修改，但允许多个事务同时读取相同的数据。
意向锁（Intent Lock）：意向锁是一种辅助锁，用于表示某个事务准备在资源上获取共享或排他锁。它们在层次结构中存在，以帮助其他事务确定资源的整体锁状态，从而避免冲突。
表锁（Table Lock）：表锁是应用于整个表的锁模式。当事务需要对整个表进行操作时，可以使用表锁。它可以是共享的（多个事务可以同时持有共享表锁），也可以是排他的（只允许一个事务持有排他表锁）。
① . 有大量重 复值 、且 经 常有范 围查询 （ between, >,< ， >=,< = ）和 order by 、 group by 发 生的列，可考 虑 建立群集索引；
② . 经 常同 时 存取多列，且 每 列都含有重 复值 可考 虑 建立 组 合索引；
③ . 组 合索引要尽量使 关键查询 形成索引覆盖，其前 导 列一定是使用最 频 繁的列。
原文链接：https://blog.csdn.net/gprime/article/details/1687930
窗口函数
select *,
rank() over (order by 成绩 desc) as ranking,
dense_rank() over (order by 成绩 desc) as dese_rank,
row_number() over (order by 成绩 desc) as row_num
from 班级表
游标
alter table st add rank int; --添加一列
--创建一个游标
declare my_cursor1 cursor for --my_cursor为游标的名称，随便起
select id,Chinese,English,Math from st
--打开游标
open my_cursor1
--变量
declare @rank int
declare @Chinese int
declare @English int
declare @Math int
declare @id int
--循环游标
fetch next from my_cursor1 into @id,@Chinese,@English,@Math
while @@FETCH_STATUS=0
begin
set @rank=0 --为变量赋初始值
if @Chinese>=60 set @rank=@rank+1
if @English>=60 set @rank=@rank+1
if @Math>=60 set @rank=@rank+1
update [st] set [rank]=@rank where id=@id
fetch next from my_cursor1 into @id,@Chinese,@English,@Math --获取下一条数据并赋值给变量
end--关闭释放游标
close my_cursor1 --关闭游标
deallocate my_cursor1 --删除游标
1 zhangsan 56 42 88 1
2 lisi 67 43 100 2
3 wangwu 34 72 32 1
4 zhaoliu 88 80 36 2
//事务：
declare @errorSum int --定义变量，用于累计事务执行过程中的错误
set @errorSum=0 --初始化为0，即无错误
begin transaction
begin
update ST set name='11111'
where id=1
set @errorSum=@errorSum+@@error --累计是否有错误
update ST set name='2222'
where id=2
set @errorSum=@errorSum+@@error --累计是否有错误
select @errorSum
if(@errorSum>0)
rollback transaction
else
commit transaction
end
