分类数据库下的文章 - 乌托邦

登录

标签搜索

天祈

累计撰写 66 篇文章
累计收到 14 条评论

搜索到 18 篇与的结果

2022-11-14
16. SQLServer : 函数表值函数内联表值函数{alert type="info"}IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL DROP FUNCTION Sales.ufn_SalesByStore; GO CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int) RETURNS TABLE AS RETURN ( SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total' FROM Production.Product AS P JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID WHERE C.StoreID = @storeid GROUP BY P.ProductID, P.Name );{/alert}多语句表值函数{alert type="info"}IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL DROP FUNCTION dbo.ufn_FindReports; GO CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER) RETURNS @retFindReports TABLE ( EmployeeID int primary key NOT NULL, FirstName nvarchar(255) NOT NULL, LastName nvarchar(255) NOT NULL, JobTitle nvarchar(50) NOT NULL, RecursionLevel int NOT NULL ) --Returns a result set that lists all the employees who report to the --specific employee directly or indirectly.*/ AS BEGIN WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns AS ( SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n FROM HumanResources.Employee e INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID WHERE e.BusinessEntityID = @InEmpID UNION ALL SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor FROM HumanResources.Employee e INNER JOIN EMP_cte ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID ) -- copy the required columns to the result of the function INSERT @retFindReports SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel FROM EMP_cte RETURN END; GO{/alert}标量函数{alert type="info"}IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL DROP FUNCTION ufnGetInventoryStock; GO CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int) RETURNS int AS -- Returns the stock level for the product. BEGIN DECLARE @ret int; SELECT @ret = SUM(p.Quantity) FROM Production.ProductInventory p WHERE p.ProductID = @ProductID AND p.LocationID = '6'; IF (@ret IS NULL) SET @ret = 0; RETURN @ret; END;SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply FROM Production.Product WHERE ProductModelID BETWEEN 75 and 80;{/alert}系统函数
- 2022年11月14日
- 148 阅读
- 0 评论
- 0 点赞
2022-11-12
15. SQLServer : 存储过程创建存储过程{callout color="#eee1ce"}存储过程的名称是区分大小写的，执行时需要注意使用SQL Server Management Studio创建存储过程中，在查询菜单中“指定模板参数的值(Ctrl+Shift+M)”补充模板信息,编写过程中使用“分析(Ctrl+F5)”进行检查和分析{/callout}{alert type="info"}-- 语法 CREATE [ OR ALTER ] { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] [ { @parameter_name [ type_schema_name. ] data_type } [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY] ] [ ,...n ] [ WITH <procedure_option> [ ,...n ] ] [ FOR REPLICATION ] AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] } [;] <procedure_option> ::= [ ENCRYPTION ] [ RECOMPILE ] [ EXECUTE AS Clause ]SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: 天祈 -- Create date: 2022-11-12 -- Description: 测试存储过程 -- ============================================= CREATE PROCEDURE [dbo].[Test2] ( -- Add the parameters for the stored procedure here @a int = 0, @b nvarchar = '') AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT @a, @b END GO{/alert}执行存储过程{alert type="info"}EXEC dbo.uspGetEmployeeManagers @BusinessEntityID = 50;{/alert}设置存储过程自执行{alert type="info"}打开SQL Server Management Studio,SQL Server代理–作业–点右键–新建作业定时自动执行SQL存储过程{/alert}指定参数{alert type="info"}USE AdventureWorks2012; GO -- Passing values as constants. EXEC dbo.uspGetWhereUsedProductID 819, '20050225'; GO -- Passing values as variables. DECLARE @ProductID int, @CheckDate datetime; SET @ProductID = 819; SET @CheckDate = '20050225'; EXEC dbo.uspGetWhereUsedProductID @ProductID, @CheckDate; GO -- Try to use a function as a parameter value. -- This produces an error message. EXEC dbo.uspGetWhereUsedProductID 819, GETDATE(); GO -- Passing the function value as a variable. DECLARE @CheckDate datetime; SET @CheckDate = GETDATE(); EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate; USE AdventureWorks2012; GO IF OBJECT_ID('Sales.uspGetSalesYTD', 'P') IS NOT NULL DROP PROCEDURE Sales.uspGetSalesYTD; GO CREATE PROCEDURE Sales.uspGetSalesYTD @SalesPerson nvarchar(50) = NULL -- NULL default value AS SET NOCOUNT ON; -- Validate the @SalesPerson parameter. IF @SalesPerson IS NULL BEGIN PRINT 'ERROR: You must specify the last name of the sales person.' RETURN END -- Get the sales for the specified sales person and -- assign it to the output parameter. SELECT SalesYTD FROM Sales.SalesPerson AS sp JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID WHERE LastName = @SalesPerson; RETURN {/alert}参数{alert type="info"}输入参数允许调用方将数据值传递到存储过程或函数。输出参数允许存储过程将数据值或游标变量传递回调用方。用户定义函数不能指定输出参数。每个存储过程向调用方返回一个整数返回代码。如果存储过程没有显式设置返回代码的值，则返回代码为 0。{/alert}{alert type="info"}-- Create a procedure that takes one input parameter and returns one output parameter and a return code. CREATE PROCEDURE SampleProcedure @EmployeeIDParm INT, @MaxTotal INT OUTPUT AS -- Declare and initialize a variable to hold @@ERROR. DECLARE @ErrorSave INT SET @ErrorSave = 0 -- Do a SELECT using the input parameter. SELECT FirstName, LastName, JobTitle FROM HumanResources.vEmployee WHERE EmployeeID = @EmployeeIDParm -- Save any nonzero @@ERROR value. IF (@@ERROR <> 0) SET @ErrorSave = @@ERROR -- Set a value in the output parameter. SELECT @MaxTotal = MAX(TotalDue) FROM Sales.SalesOrderHeader; IF (@@ERROR <> 0) SET @ErrorSave = @@ERROR -- Returns 0 if neither SELECT statement had an error; otherwise, returns the last error. RETURN @ErrorSave{/alert}@@ERROR{alert type="info"}定义: 返回执行的上一个 Transact-SQL语句的错误号。注意事项:　* 如果前一个T-SQL语句没有执行错误，则返回0　* 如果前一个语句遇到错误，则返回错误号。如果错误是 sys.messages 目录视图中的错误之一，则 @@ERROR 将包含 sys.messages.message_id 列中表示该错误的值。可以在 sys.messages 中查看与 @@ERROR 错误号相关的文本信息{/alert}返回数据使用结果集返回数据的示例{alert type="info"}USE AdventureWorks2019; GO IF OBJECT_ID('Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL DROP PROCEDURE Sales.uspGetEmployeeSalesYTD; GO CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD AS SET NOCOUNT ON; SELECT LastName, SalesYTD FROM Sales.SalesPerson AS sp JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID; RETURN; GO {/alert}使用输出参数返回数据{alert type="info"}{/alert}
- 2022年11月12日
- 139 阅读
- 0 评论
- 0 点赞
2022-11-12
14. SQLServer : 游标学习 CLOSE{callout color="#f4ddbd"}释放当前结果集，然后解除定位游标的行上的游标锁定，从而关闭一个开放的游标。 CLOSE 将保留数据结构以便重新打开，但在重新打开游标之前，不允许提取和定位更新。必须对打开的游标发布 CLOSE；不允许对仅声明或已关闭的游标执行 CLOSE。{/callout}{alert type="info"}DECLARE Employee_Cursor CURSOR FOR SELECT EmployeeID, Title FROM AdventureWorks2012.HumanResources.Employee; OPEN Employee_Cursor; FETCH NEXT FROM Employee_Cursor; WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM Employee_Cursor; END; CLOSE Employee_Cursor; DEALLOCATE Employee_Cursor; GO {/alert}DEALLOCATE{callout color="#91ec7e"}删除游标引用。当释放最后的游标引用时，组成该游标的数据结构由 Microsoft SQL Server 释放。DEALLOCATE <@cursor_variable_name> 语句只删除对游标命名变量的引用。直到批处理、存储过程或触发器结束时变量离开作用域，才释放变量。在 DEALLOCATE <@cursor_variable_name> 语句之后，可以使用 SET 语句将变量与另一个游标关联。{/callout}{alert type="info"}-- DECLARE CURSOR 语句分配游标并将其与游标名称关联 DECLARE abc SCROLL CURSOR FOR SELECT * FROM Person.Person; -- 将游标设置为游标变量 DECLARE @MyCrsrRef CURSOR; SET @MyCrsrRef = abc; -- 不定义游标名称而创建游标并将其与变量关联 DECLARE @MyCursor CURSOR; SET @MyCursor = CURSOR LOCAL SCROLL FOR SELECT * FROM Person.Person; -- 示例 DECLARE @MyCursor CURSOR; SET @MyCursor = CURSOR LOCAL SCROLL FOR SELECT * FROM Sales.SalesPerson; DEALLOCATE @MyCursor; SET @MyCursor = CURSOR LOCAL SCROLL FOR SELECT * FROM Sales.SalesTerritory; GO -- 以下脚本显示游标如何持续到最后的名称或持续到引用它们的变量已释放 DECLARE abc CURSOR GLOBAL SCROLL FOR SELECT * FROM Sales.SalesPerson; OPEN abc; GO -- Reference the named cursor with a cursor variable. DECLARE @MyCrsrRef1 CURSOR; SET @MyCrsrRef1 = abc; -- Now deallocate the cursor reference. DEALLOCATE @MyCrsrRef1; -- Cursor abc still exists. FETCH NEXT FROM abc; GO -- Reference the named cursor again. DECLARE @MyCrsrRef2 CURSOR; SET @MyCrsrRef2 = abc; -- Now deallocate cursor name abc. DEALLOCATE abc; -- Cursor still exists, referenced by @MyCrsrRef2. FETCH NEXT FROM @MyCrsrRef2; -- Cursor finally is deallocated when last referencing -- variable goes out of scope at the end of the batch. GO -- Create an unnamed cursor. DECLARE @MyCursor CURSOR; SET @MyCursor = CURSOR LOCAL SCROLL FOR SELECT * FROM Sales.SalesTerritory; -- The following statement deallocates the cursor -- because no other variables reference it. DEALLOCATE @MyCursor; {/alert}DECLARE CURSOR{alert type="info"}ISO Syntax DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ] [;] Transact-SQL Extended Syntax DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;] cursor_name : 游标的名称LOCAL : 指定该游标的范围对在其中创建它的批处理、存储过程或触发器是局部的GLOBAL : 指定该游标范围对连接是全局的。在由此连接执行的任何存储过程或批处理中，都可以引用该游标名称。该游标仅在断开连接时隐式释放。FORWARD_ONLY : 指定游标只能向前移动，并从第一行滚动到最后一行。STATIC : 指定游标始终以第一次打开时的样式显示结果集，并制作数据的临时副本，供游标使用。KEYSET : 指定当游标打开时，游标中行的成员身份和顺序已经固定。....{/alert}示例使用简单的游标和语法{callout color="#9483e7"}在打开该游标时所生成的结果集包括表中的所有行和所有列。可以更新该游标，并且所有的更新和删除都会在对该游标所做的提取操作中表现出来。 FETCH NEXT 是唯一可用的提取选项，因为未指定 SCROLL 选项{/callout}{alert type="info"}DECLARE vend_cursor CURSOR FOR SELECT * FROM Purchasing.Vendor OPEN vend_cursor FETCH NEXT FROM vend_cursor; {/alert}
- 2022年11月12日
- 136 阅读
- 0 评论
- 0 点赞
2022-11-11
13. SQLServer : 常规命令 EXECUTE(简写EXEC)使用 EXECUTE 传递单个参数{alert type="info"}-- 执行存储过程 --EXEC dbo.uspGetEmployeeManagers @EmployeeID = 6; EXEC dbo.uspGetEmployeeManagers 6; -- 单个参数时不用声明 GO {/alert}使用多个参数{alert type="info"}DECLARE @CheckDate DATETIME; SET @CheckDate = GETDATE(); EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate; {/alert}EXECUTE 'tsql_string'{alert type="info"}declare @str nvarchar(255) = 'select * from Production.Product' EXECUTE(@str) {/alert}对远程存储过程使用 EXECUTE 语句{alert type="info"}DECLARE @retstat INT; EXECUTE @retstat = SQLSERVER1.AdventureWorks2012.dbo.uspGetEmployeeManagers @BusinessEntityID = 6; {/alert}使用带存储过程变量的 EXECUTE 语句{alert type="info"}DECLARE @proc_name VARCHAR(30); SET @proc_name = 'sys.sp_who'; EXEC @proc_name; {/alert}使用带 DEFAULT 的 EXECUTE{alert type="info"}-- 创建存储过程 IF OBJECT_ID(N'dbo.ProcTestDefaults', N'P')IS NOT NULL DROP PROCEDURE dbo.ProcTestDefaults; GO -- Create the stored procedure. CREATE PROCEDURE dbo.ProcTestDefaults ( @p1 SMALLINT = 42, @p2 CHAR(1), @p3 VARCHAR(8) = 'CAR') AS SET NOCOUNT ON; SELECT @p1, @p2, @p3 ; GO -- 执行存储过程 -- Specifying a value only for one parameter (@p2). EXECUTE dbo.ProcTestDefaults @p2 = 'A'; -- Specifying a value for the first two parameters. EXECUTE dbo.ProcTestDefaults 68, 'B'; -- Specifying a value for all three parameters. EXECUTE dbo.ProcTestDefaults 68, 'C', 'House'; -- Using the DEFAULT keyword for the first parameter. EXECUTE dbo.ProcTestDefaults @p1 = DEFAULT, @p2 = 'D'; -- Specifying the parameters in an order different from the order defined in the procedure. EXECUTE dbo.ProcTestDefaults DEFAULT, @p3 = 'Local', @p2 = 'E'; -- Using the DEFAULT keyword for the first and third parameters. EXECUTE dbo.ProcTestDefaults DEFAULT, 'H', DEFAULT; EXECUTE dbo.ProcTestDefaults DEFAULT, 'I', @p3 = DEFAULT; {/alert}对用户定义函数使用 EXECUTE{alert type="info"}DECLARE @returnstatus NVARCHAR(15); SET @returnstatus = NULL; EXEC @returnstatus = dbo.ufnGetSalesOrderStatusText @Status = 2; PRINT @returnstatus; GO {/alert}使用 EXECUTE 重新定义单个结果集{alert type="info"}-- 对存储过程的返回结果重新定义列 EXEC uspGetEmployeeManagers 6 WITH RESULT SETS ( ([Reporting Level] INT NOT NULL, [ID of Employee] INT NOT NULL, [Employee First Name] NVARCHAR(50) NOT NULL, [Employee Last Name] NVARCHAR(50) NOT NULL, [Employee ID of Manager] NVARCHAR(max) NOT NULL, [Manager First Name] NVARCHAR(50) NOT NULL, [Manager Last Name] NVARCHAR(50) NOT NULL ) ); {/alert}使用 EXECUTE 重新定义两个结果集{alert type="info"}CREATE PROC Production.ProductList @ProdName NVARCHAR(50) AS -- First result set SELECT ProductID, Name, ListPrice FROM Production.Product WHERE Name LIKE @ProdName; -- Second result set SELECT Name, COUNT(S.ProductID) AS NumberOfOrders FROM Production.Product AS P JOIN Sales.SalesOrderDetail AS S ON P.ProductID = S.ProductID WHERE Name LIKE @ProdName GROUP BY Name; GO -- Execute the procedure EXEC Production.ProductList '%tire%' WITH RESULT SETS ( (ProductID INT, -- first result set definition starts here Name NAME, ListPrice MONEY) , -- comma separates result set definitions (Name NAME, -- second result set definition starts here NumberOfOrders INT) ); {/alert}在运行时确定的名称调用存储过程{alert type="info"}EXEC ('EXEC ' + @var); {/alert}
- 2022年11月11日
- 142 阅读
- 0 评论
- 0 点赞
2022-11-11
12. SQLServer : 控制流 BEGIN...END{alert type="info"}-- BEGIN 和 END 定义一系列一起执行的 Transact-SQL 语句。如果不包括 BEGIN...END 块，则将执行两个 ROLLBACK TRANSACTION 语句，并返回两条 PRINT 消息 USE AdventureWorks2012 GO BEGIN TRANSACTION GO IF @@TRANCOUNT = 0 BEGIN SELECT FirstName, MiddleName FROM Person.Person WHERE LastName = 'Adams'; ROLLBACK TRANSACTION; PRINT N'Rolling back the transaction two times would cause an error.'; END; ROLLBACK TRANSACTION; PRINT N'Rolled back the transaction.'; GO /* Rolled back the transaction. */ {/alert}BREAK{callout color="#a07df2"}BREAK 将退出当前 WHILE 循环。如果当前循环嵌套在另一个循环中，BREAK 将只退出当前循环，并且将控制给予外部循环中的下一个语句。BREAK 通常位于 IF 语句中。{/callout}{alert type="info"}WHILE (1=1) BEGIN IF EXISTS (SELECT * FROM ##MyTempTable WHERE EventCode = 'Done') BEGIN BREAK; -- 'Done' row has finally been inserted and detected, so end this loop. END PRINT N'The other process is not yet done.'; -- Re-confirm the non-done status to the console. WAITFOR DELAY '00:01:30'; -- Sleep for 90 seconds. END{/alert}CONTINUE{callout color="#dcb174"}重新开始 WHILE 循环。在 CONTINUE 关键字之后的任何语句都将被忽略。 CONTINUE 通常由一个 IF 测试打开，但并不始终这样。{/callout}{alert type="info"}DECLARE @m INT =1 WHILE (@m <= 10) BEGIN PRINT N'开始:'+ cast(@m as nvarchar(255)) IF @m = 3 BEGIN Set @m+=2 continue; END ELSE BEGIN Set @m+=1 END PRINT '@m:'+ cast(@m as nvarchar(255)) END select @m {/alert}IF...ELSE{alert type="info"}DECLARE @Number INT; SET @Number = 50; IF @Number > 100 PRINT 'The number is large.'; ELSE BEGIN IF @Number < 10 PRINT 'The number is small.'; ELSE PRINT 'The number is medium.'; END ; GO {/alert}GOTO{callout color="#beea6c"}将执行流更改到标签处。跳过 GOTO 后面的 Transact-SQL 语句，并从标签位置继续处理。 GOTO 语句和标签可在过程、批处理或语句块中的任何位置使用。 GOTO 语句可嵌套使用。{/callout}{alert type="info"}DECLARE @Counter int; SET @Counter = 1; WHILE @Counter < 10 BEGIN SELECT @Counter SET @Counter = @Counter + 1 IF @Counter = 4 GOTO Branch_One --Jumps to the first branch. IF @Counter = 5 GOTO Branch_Two --This will never execute. END Branch_One: SELECT 'Jumping To Branch One.' GOTO Branch_Three; --This will prevent Branch_Two from executing. Branch_Two: SELECT 'Jumping To Branch Two.' Branch_Three: SELECT 'Jumping To Branch Three.'; {/alert}RETURN{callout color="#7496fb"}从查询或过程中无条件退出。 RETURN 的执行是即时且完全的，可在任何时候用于从过程、批处理或语句块中退出。 RETURN 之后的语句是不执行的。{/callout}{alert type="info"}CREATE PROCEDURE findjobs @nm sysname = NULL AS IF @nm IS NULL BEGIN PRINT 'You must give a user name' RETURN -- 之后的代码不会再执行 END ELSE BEGIN SELECT o.name, o.id, o.uid FROM sysobjects o INNER JOIN master..syslogins l ON o.uid = l.sid WHERE l.name = @nm END; {/alert}THROW{alert type="info"}-- 语法 THROW [ { error_number | @local_variable }, -- 状态 { message | @local_variable }, -- 错误信息 { state | @local_variable } ] -- 状态码 0 - 255 [ ; ] -- 示例 THROW 51000, 'The record does not exist.', 6; {/alert}TRY...CATCH{callout color="#84e3f0"} 如果 TRY 块内部发生错误，则会将控制传递给 CATCH 块中包含的另一个语句组。{/callout}{alert type="info"}-- 语法 BEGIN TRY { sql_statement | statement_block } END TRY BEGIN CATCH [ { sql_statement | statement_block } ] END CATCH [ ; ] -- 示例 BEGIN TRY -- Generate a divide-by-zero error. SELECT 1/0; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; END CATCH; GO {/alert}WAITFOR{callout color="#8475f5"}阻止执行批处理、存储过程或事务，直到已过指定时间或时间间隔，或者指定语句发生修改或至少返回一行为止。{/callout}{alert type="info"}-- 语法 WAITFOR { DELAY 'time_to_pass' | TIME 'time_to_execute' | [ ( receive_statement ) | ( get_conversation_group_statement ) ] [ , TIMEOUT timeout ] } {/alert}WAITFOR TIME : 当时间某一刻时执行语句{alert type="info"}-- 在晚上 10:20 (22:20) 在 msdb 数据库中执行 sp_update_job 存储过程 EXECUTE sp_add_job @job_name = 'TestJob'; BEGIN WAITFOR TIME '22:20'; EXECUTE sp_update_job @job_name = 'TestJob', @new_name = 'UpdatedJob'; END; GO -- 只能是[时间]格式 BEGIN WAITFOR TIME '15:29:40' SELECT getdate() END{/alert}WAITFOR DELAY : 延迟指定时间之后运行语句{alert type="info"}-- 在两小时的延迟后执行存储过程 BEGIN WAITFOR DELAY '02:00'; BEGIN EXECUTE sp_helpdb; END END; GO {/alert}WHILE{callout color="#eccda2"}设置重复执行 SQL 语句或语句块的条件。只要指定的条件为真，就重复执行语句。可以使用 BREAK 和 CONTINUE 关键字在循环内部控制 WHILE 循环中语句的执行。{/callout}{alert type="info"}USE AdventureWorks2012; GO WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300 BEGIN UPDATE Production.Product SET ListPrice = ListPrice * 2 SELECT MAX(ListPrice) FROM Production.Product IF (SELECT MAX(ListPrice) FROM Production.Product) > $500 BREAK ELSE CONTINUE END PRINT 'Too much for the market to bear';{/alert}
- 2022年11月11日
- 121 阅读
- 0 评论
- 0 点赞
2022-11-10
11. SQLServer : 运算符运算符的优先级{callout color="#6eee5d"}如果一个表达式中的两个运算符有相同的优先级别，则按照它们在表达式中的位置对其从左到右进行求值。例如，在下面的 SET 语句所使用的表达式中，在加运算符之前先对减运算符进行求值。{/callout}{alert type="info"} 级别运算符 1 ~（位非） 2 *（乘）、/（除）、%（取模） 3 +（正）、-（负）、+（加）、+（串联）、-（减）、&（位与）、^（位异或）、|（位或） 4 =、><>=、<=、<>!=、!>!<（比较运算符） 5 NOT 6 AND 7 ALL、ANY、BETWEEN、IN、LIKE、OR、SOME 8 =（赋值） {/alert}一元运算符{alert type="info"} 运算符含义 +（正）数值为正。 -（负）数值为负。 ~（位非）返回数字的非。 {/alert}集运算符使用说明{alert type="info"}UNION ALL - 包括重复行UNION - 排除重复行{/alert}基本规则{alert type="info"}所有查询中的列数和列的顺序必须相同。数据类型必须兼容{/alert}算数运算符{alert type="info"}declare @mm int =3; declare @nn int =4; declare @zz nvarchar(255) ='5'; select -@nn+@mm -- 输出：-1 Set @mm += @mm -- 相当于： @mm = @mm + @mm print(@mm) -- 输出：6 print(@mm % @nn) -- 6 % 4 {/alert}比较运算符{alert type="info"} 运算符含义 =（等于）等于 >（大于）大于 <（小于）小于 >=（大于或等于）大于等于 <=（小于或等于）小于或等于 <>（不等于）不等于 !=（不等于）不等于（非 ISO 标准） !<（不小于）不小于（非 ISO 标准） !>（不大于）不大于（非 ISO 标准） {/alert}字符串运算符字符串串联{alert type="info"}DECLARE @x VARCHAR(4000) = REPLICATE('x', 4000) DECLARE @z VARCHAR(8000) = REPLICATE('z',8000) DECLARE @y VARCHAR(max); SET @y = ''; SET @y += @x + @z; SELECT LEN(@y) AS Y; -- 8000{/alert}通配符-百分比字符 %{alert type="success"}SELECT FirstName, LastName FROM Person.Person WHERE FirstName LIKE 'Dan%'; {/alert}通配符-中括号 []{alert type="info"}-- 返回以 m 字母开头的名称。 [n-z] 指定第二个字母必须是 n 到 z 范围内的某个字母 SELECT name FROM sys.databases WHERE name LIKE 'm[n-z]%'; -- 查找其地址中有四位邮政编码的所有 Adventure Works 雇员的 ID 和姓名 SELECT e.BusinessEntityID, p.FirstName, p.LastName, a.PostalCode FROM HumanResources.Employee AS e INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID INNER JOIN Person.BusinessEntityAddress AS ea ON e.BusinessEntityID = ea.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = ea.AddressID WHERE a.PostalCode LIKE '[0-9][0-9][0-9][0-9]'; {/alert}通配符-无需匹配的字符串 ^{callout color="#676ed0"}匹配不在方括号 [^] 之间指定的范围或集合内的任何单个字符{/callout}{alert type="info"}-- 使用 [^] 运算符查找 Contact 表中名字以 Al 开头且第三个字母不是a的前5名用户 SELECT TOP 5 FirstName, LastName FROM Person.Person WHERE FirstName LIKE 'Al[^a]%'; -- 使用 [^] 运算符来查找不以字母或数字开头的字符串 SELECT [object_id], OBJECT_NAME(object_id) AS [object_name], name, column_id FROM sys.columns WHERE name LIKE '[^0-9A-z]%';{/alert}通配符- 下划线(匹配一个字符) _{alert type="info"}-- 返回以字母 m 开头且第三个字母为 d 的所有数据库名称 SELECT name FROM sys.databases WHERE name LIKE 'm_d%'; -- 返回 db_owner、db_ddladmin 等固定数据库角色的名称(此时的下划线被视为通配符) SELECT name FROM sys.database_principals WHERE name LIKE 'db_%'; -- 过滤出以 db_ 开头的数据库角色名(转义操作) SELECT name FROM sys.database_principals WHERE name LIKE 'db[_]%';{/alert}
- 2022年11月10日
- 149 阅读
- 0 评论
- 0 点赞
2022-10-05
10. SQLServer : 表达式 {alert type="info"}-- 语法 --Simple CASE expression: CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END --Searched CASE expression: CASE WHEN Boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END -- 示例一 SELECT ProductNumber, Category = CASE ProductLine WHEN 'R' THEN 'Road' WHEN 'M' THEN 'Mountain' WHEN 'T' THEN 'Touring' WHEN 'S' THEN 'Other sale items' ELSE 'Not for sale' END, Name FROM Production.Product ORDER BY ProductNumber; GO --示例二 SELECT ProductNumber, Name, "Price Range" = CASE WHEN ListPrice = 0 THEN 'Mfg item - not for resale' WHEN ListPrice < 50 THEN 'Under $50' WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250' WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000' ELSE 'Over $1000' END FROM Production.Product ORDER BY ProductNumber ; {/alert}
- 2022年10月05日
- 174 阅读
- 0 评论
- 0 点赞
2022-10-05
9. SQLServer : 语言元素-控制流 BEGIN...END{callout color="#60ef4d"}包括一系列的 Transact-SQL 语句，从而可以执行一组 Transact-SQL 语句。 BEGIN 和 END 是控制流语言的关键字。{/callout}{alert type="success"}-- 语法 BEGIN { sql_statement | statement_block } END --示例 USE AdventureWorks2012 GO BEGIN TRANSACTION GO IF @@TRANCOUNT = 0 BEGIN SELECT FirstName, MiddleName FROM Person.Person WHERE LastName = 'Adams'; ROLLBACK TRANSACTION; PRINT N'Rolling back the transaction two times would cause an error.'; END; ROLLBACK TRANSACTION; PRINT N'Rolled back the transaction.'; GO /* Rolled back the transaction. */ {/alert}BREAK{callout color="#4defed"}BREAK 将退出当前 WHILE 循环。如果当前循环嵌套在另一个循环中，BREAK 将只退出当前循环，并且将控制给予外部循环中的下一个语句。{/callout}{alert type="info"}WHILE (1=1) BEGIN IF EXISTS (SELECT * FROM ##MyTempTable WHERE EventCode = 'Done') BEGIN BREAK; -- 'Done' row has finally been inserted and detected, so end this loop. END PRINT N'The other process is not yet done.'; -- Re-confirm the non-done status to the console. WAITFOR DELAY '00:01:30'; -- Sleep for 90 seconds. END{/alert}ELSE (IF...ELSE){alert type="success"}-- 语法 IF Boolean_expression { sql_statement | statement_block } [ ELSE { sql_statement | statement_block } ] -- 示例一 IF 1 = 2 PRINT 'Boolean_expression is true.' ELSE PRINT 'Boolean_expression is false.' ; GO -- 示例二 USE AdventureWorks2012; GO IF (SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5 PRINT 'There are more than 5 Touring-3000 bicycles.' ELSE PRINT 'There are 5 or less Touring-3000 bicycles.' ; GO -- 示例三 USE AdventureWorks2012; GO DECLARE @AvgWeight DECIMAL(8,2), @BikeCount INT IF (SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5 BEGIN SET @BikeCount = (SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%'); SET @AvgWeight = (SELECT AVG(Weight) FROM Production.Product WHERE Name LIKE 'Touring-3000%'); PRINT 'There are ' + CAST(@BikeCount AS VARCHAR(3)) + ' Touring-3000 bikes.' PRINT 'The average weight of the top 5 Touring-3000 bikes is ' + CAST(@AvgWeight AS VARCHAR(8)) + '.'; END ELSE BEGIN SET @AvgWeight = (SELECT AVG(Weight) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ); PRINT 'Average weight of the Touring-3000 bikes is ' + CAST(@AvgWeight AS VARCHAR(8)) + '.' ; END ; GO -- 示例四 DECLARE @Number INT; SET @Number = 50; IF @Number > 100 PRINT 'The number is large.'; ELSE BEGIN IF @Number < 10 PRINT 'The number is small.'; ELSE PRINT 'The number is medium.'; END ; GO {/alert}GOTO{callout color="#4d75ef"}将执行流更改到标签处。跳过 GOTO 后面的 Transact-SQL 语句，并从标签位置继续处理。 GOTO 语句和标签可在过程、批处理或语句块中的任何位置使用。 GOTO 语句可嵌套使用。{/callout}{alert type="success"}-- 语法 Define the label: label: Alter the execution: GOTO label -- 示例 DECLARE @Counter int; SET @Counter = 1; WHILE @Counter < 10 BEGIN SELECT @Counter SET @Counter = @Counter + 1 IF @Counter = 4 GOTO Branch_One --Jumps to the first branch. IF @Counter = 5 GOTO Branch_Two --This will never execute. END Branch_One: SELECT 'Jumping To Branch One.' GOTO Branch_Three; --This will prevent Branch_Two from executing. Branch_Two: SELECT 'Jumping To Branch Two.' Branch_Three: SELECT 'Jumping To Branch Three.'; {/alert}RETURN{callout color="#4def93"}从查询或过程中无条件退出。 RETURN 的执行是即时且完全的，可在任何时候用于从过程、批处理或语句块中退出。 RETURN 之后的语句是不执行的。{/callout}{alert type="success"}-- 示例一 CREATE PROCEDURE findjobs @nm sysname = NULL AS IF @nm IS NULL BEGIN PRINT 'You must give a user name' RETURN END ELSE BEGIN SELECT o.name, o.id, o.uid FROM sysobjects o INNER JOIN master..syslogins l ON o.uid = l.sid WHERE l.name = @nm END; -- 示例二 USE AdventureWorks2012; GO CREATE PROCEDURE checkstate @param VARCHAR(11) AS IF (SELECT StateProvince FROM Person.vAdditionalContactInfo WHERE ContactID = @param) = 'WA' RETURN 1 ELSE RETURN 2; GO {/alert}THROW{callout color="#4da1ef"}引发异常，并将执行转移到 TRY...CATCH 构造的 CATCH 块。{/callout}{alert type="success"}-- 语法 THROW [ { error_number | @local_variable }, { message | @local_variable }, { state | @local_variable } ] [ ; ] -- error_number : 表示异常的常量或变量 error_number 为 int，并且必须大于或等于50000且小于或等于2147483647 -- message : 描述异常的字符串或变量。 message 为 nvarchar(2048) -- State : 在 0 到 255 之间的常量或变量，指示与消息关联的状态。 state 为 tinyint -- 示例一 THROW 51000, 'The record does not exist.', 1; -- 示例二 USE tempdb; GO CREATE TABLE dbo.TestRethrow ( ID INT PRIMARY KEY ); BEGIN TRY INSERT dbo.TestRethrow(ID) VALUES(1); -- Force error 2627, Violation of PRIMARY KEY constraint to be raised. INSERT dbo.TestRethrow(ID) VALUES(1); END TRY BEGIN CATCH PRINT 'In catch block.'; THROW; END CATCH; {/alert}TRY...CATCH{alert type="success"}-- 语法 BEGIN TRY { sql_statement | statement_block } END TRY BEGIN CATCH [ { sql_statement | statement_block } ] END CATCH -- 示例 BEGIN TRY -- Generate a divide-by-zero error. SELECT 1/0; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; END CATCH; {/alert}WHILE{callout color="#4dbfef"}设置重复执行 SQL 语句或语句块的条件。只要指定的条件为真，就重复执行语句。可以使用 BREAK 和 CONTINUE 关键字在循环内部控制 WHILE 循环中语句的执行。{/callout}{alert type="success"}-- 示例 USE AdventureWorks2012; GO WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300 BEGIN UPDATE Production.Product SET ListPrice = ListPrice * 2 SELECT MAX(ListPrice) FROM Production.Product IF (SELECT MAX(ListPrice) FROM Production.Product) > $500 BREAK ELSE CONTINUE END PRINT 'Too much for the market to bear'; -- 示例二 WHILE ( SELECT AVG(ListPrice) FROM dbo.DimProduct) < $300 BEGIN UPDATE dbo.DimProduct SET ListPrice = ListPrice * 2; SELECT MAX ( ListPrice) FROM dbo.DimProduct IF ( SELECT MAX (ListPrice) FROM dbo.DimProduct) > $500 BREAK; END {/alert}
- 2022年10月05日
- 165 阅读
- 0 评论
- 0 点赞
2022-10-04
8. SQLServer : 变量表达式NULLIF{callout color="#ef4dea"}如果两个表达式不相等，则 NULLIF 返回第一个表达式。如果表达式相等，则 NULLIF 返回第一个表达式类型的空值。{/callout}{alert type="success"}NULLIF ( expression , expression ) {/alert}变量{alert type="success"}-- 创建 int 类型的变量 DECLARE @MyCounter INT; -- 声明多个变量 DECLARE @LastName NVARCHAR(30), @FirstName NVARCHAR(20), @StateProvince NCHAR(2); -- 引用变量 DECLARE @MyVariable INT; SET @MyVariable = 1; SELECT BusinessEntityID, NationalIDNumber, JobTitle FROM HumanResources.Employee WHERE BusinessEntityID = @MyVariable; -- 赋值操作 DECLARE @EmpIDVariable INT; SELECT @EmpIDVariable = MAX(EmployeeID) FROM HumanResources.Employee; -- 如果 SELECT 语句返回多行且变量引用非标量表达式，则该变量将设置为结果集中最后一行中表达式的返回值 DECLARE @EmpIDVariable INT; SELECT @EmpIDVariable = BusinessEntityID FROM HumanResources.Employee ORDER BY BusinessEntityID DESC; SELECT @EmpIDVariable;{/alert}Set{callout color="#4def9b"}将先前使用DECLARE语句创建的指定局部变量设置为指定值{/callout}{alert type="success"}-- 赋值操作 DECLARE @myvar CHAR(20); SET @myvar = 'This is a test'; SELECT @myvar; -- 从查询中赋值 DECLARE @rows INT; SET @rows = (SELECT COUNT(*) FROM Sales.Customer); SELECT @rows; {/alert}SELECT{alert type="success"}-- 初始化赋值之后使用select赋值 DECLARE @var1 VARCHAR(30); SELECT @var1 = 'Generic Name'; SELECT @var1 = [Name] FROM SalesLT.Product WHERE ProductID = 1000000; --Value does not exist SELECT @var1 AS 'ProductName';{/alert}DECLARE{callout color="#4def93"}变量是在批处理或过程的主体中用 DECLARE 语句声明的，并用 SET 或 SELECT 语句赋值。游标变量可使用此语句声明，并可用于其他与游标相关的语句。除非在声明中提供值，否则声明之后所有变量将初始化为 NULL。变量的名称。变量名必须以 at 符 (@) 开头。局部变量名称必须符合标识符规则。{/callout}
- 2022年10月04日
- 188 阅读
- 0 评论
- 1 点赞
2022-09-23
MySQL配置文件my.ini说明 [client]、[mysql]、[mysqld]组说明{card-describe title="[clent]:客户端"}配置信息应用范围是所有的客户端，包括mysql、mysqladmin、mysqldump等{/card-describe}{card-describe title="[mysql]:mysql客户端"}配置信息只对mysql客户端生效。当客户端使用的配置在被重复配置时，下面的配置优先(下面的会覆盖上面的原则){/card-describe}{card-describe title="[mysqld]:服务端"}配置信息对服务端生效{/card-describe}配置信息{alert type="success"}[client] # 客户端连接服务器端时使用的端口号 port=3306 # 默认字符集 default-character-set=utf8 [mysqld] # 默认用户 user=root # 默认密码 password=123456 # MySQL 的安装路径 basedir=C:/Program Files/MySQL/MySQL Server 5.7/ # MySQL 数据文件的存储位置，也是数据表的存放位置 datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data # 服务端默认字符集 character-set-server=utf8 # 允许同时访问 MySQL 服务器的最大连接数 max_connections=100 # 查询时的缓存大小，缓存中可以存储以前通过 SELECT 语句查询过的信息，再次查询时就可以直接从缓存中拿出信息改善效率 query_cache_size=100 # 默认存储引擎 default-storage-engine=INNODB # 1开启，0关闭将所有到达MySQL Server的SQL语句记录下来 general-log=0 # 设置日志文件保存位置 general_log_file="JOYWANG.log" # 慢查询日志是否开启1,0 slow-query-log=1 # 慢查询日志文件保存 slow_query_log_file="JOYWANG-slow.log" # 慢查询日志设置时间单位秒S long_query_time=10 # 是否启用错误日志的功能和错误日志的存储位置 log-error="JOYWANG.err" # 最大并发连接数，mysql会为每个连接提供缓冲区，会开销越多的内存，所以要适当的调整该值，不能盲目的提高设置值 max_connections=151 # 指定高速缓存的大小，每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容单位M table_open_cache=2000 # 增加一张临时表大小，提高查询速度 tmp_table_size=16M #线程池缓存大小，当客户端断开连接后，将当前线程缓存起来，当在接到新的连接请求时快速响应，无需创建新的线程 thread_cache_size=10 #MySQL重建索引时允许使用的临时文件最大大小 myisam_max_sort_file_size=100G #设置在REPAIR TABLE，或者用 CREATE INDEX 创建索引或 ALTER TABLE 的过程中排序索引所分配的缓冲区大小。可设置范围4Bytes 至 4GB，默认为8MB。 myisam_sort_buffer_size=8M #指定索引缓冲区的大小，决定了索引处理的速度，尤其是索引读的速度，建议设置成物理内存的1/4，甚至物理内存的30%-40%，如果设置太大，系统就会频繁的换页，降低系统性能 key_buffer_size=8M #MySQL读入缓冲区大小，对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。 read_buffer_size=0 #参数用在sort查询之后，以保证获取以顺序的方式获取到查询的数据。如果你有很多order by 查询语句，增长这值能够提升性能 read_rnd_buffer_size=0 #0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。 #1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。 #2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。 innodb_flush_log_at_trx_commit=1 #确保有足够大的日志缓冲区来保存脏数据在被写入到日志文件之前 innodb_log_buffer_size=1M #指定表数据和索引存储的空间，可以是一个或者多个文件。最后一个数据文件必须是自动扩充的，也只有最后一个文件允许自动扩充。这样，当空间用完后，自动扩充数据文件就会自动增长（以8MB为单位）以容纳额外的数据。例如： innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend两个数据文件放在不同的磁盘上。数据首先放在ibdata1中，当达到900M以后，数据就放在ibdata2中。一旦达到50MB，ibdata2将以 8MB为单位自动增长。如果磁盘满了，需要在另外的磁盘上面增加一个数据文件。 innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend #这是InnoDB最重要的设置，对InnoDB性能有决定性的影响。默认的设置只有8M，所以默认的数据库设置下面InnoDB性能很差。在只有InnoDB存储引擎的数据库服务器上面，可以设置60-80%的内存。更精确一点，在内存容量允许的情况下面设置比InnoDB tablespaces大10%的内存大小。 innodb_buffer_pool_size=8M #放置表空间数据的目录，默认在mysql的数据目录，设置到和MySQL安装文件不同的分区可以提高性能。 innodb_data_home_dir= #该参数决定了recovery speed。太大的话recovery就会比较慢，太小了影响查询性能，一般取256M可以兼顾性能和recovery的速度 innodb_log_file_size=48M #该参数设定了事务提交时内存中log信息的处理。 1) =1时，在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新。Truly ACID。速度慢。 2) =2时，在每个事务提交时，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新。只有操作系统崩溃或掉电才会删除最后一秒的事务，不然不会丢失事务。 3) =0时，日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新。任何mysqld进程的崩溃会删除崩溃前最后一秒的事务 innodb_flush_logs_at_trx_commit=2 #设置InnoDB同步IO的方式： ) Default – 使用fsync（）。 2) O_SYNC 以sync模式打开文件，通常比较慢。 3) O_DIRECT，在Linux上使用Direct IO。可以显著提高速度，特别是在RAID系统上。避免额外的数据复制和double buffering（mysql buffering 和OS buffering）。 innodb_flush_method=Default #InnoDB kernel最大的线程数。 1) 最少设置为(num_disks+num_cpus)*2。 2) 可以通过设置成1000来禁止这个限制 innodb_thread_concurrency=25 #此配置项作用主要是当tablespace 空间已经满了后，需要MySQL系统需要自动扩展多少空间，每次tablespace 扩展都会让各个SQL 处于等待状态。增加自动扩展Size可以减少tablespace自动扩展次数。 innodb_autoextend_increment=64 #可以开启多个内存缓冲池，把需要缓冲的数据hash到不同的缓冲池中，这样可以并行的内存读写。 innodb_buffer_pool_instances=8 #这个参数设置为一种tickets,默认是5000，我也不清楚到底它代表多久，从官方文档来看它和事物处理的行数有关，大事物需要处理的行数自然更多，小事物当然也就越少至少我们可以想成获得CPU的时间，干活期间他会不断减少，如果减少到0，这个线程将被提出innodb层次，进入前面说的等待队列，当然也就在队尾部了，这里假设有一个小的事物正在排队进入innodb层，又或者它已经进入了innodb层没有获得CPU时间轮片，突然一个大的事物tickets耗尽被提出了innodb层，那么这个小事物就自然而然能够获得CPU轮片干活，而小事物执行非常快，执行完成后，另外的事物又能尽快的获得CPU干活，不会由于OS线程调度不均匀的问题而造成的小事物饥饿问题，这很好理解。也就是前面我说的与其依赖OS的调度策略不如自己设置一种规则，让用到了一定时间轮片的线程先处于睡眠态放弃CPU的使用 innodb_concurrency_tickets=5000 innodb_old_blocks_time=1000 innodb_open_files=300 innodb_stats_on_metadata=0 #可以存储每个InnoDB表和它的索引在它自己的文件中。 innodb_file_per_table=1 #如果应用程序可以运行在READ-COMMITED隔离级别，做此设定会有一定的性能提升。 transaction-isolation=READ-COMITTED #这个参数用来表示页读取到mid位置后，需要等待多久才会被加入到LRU列表的热端。使LRU列表中的热点数据不被刷出 innodb_checksum_algorithm=0 #MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中 back_log=80 flush_time=0 #如果按照检索的性能方式来细分，那么无论是两表 INNER JOIN 还是多表 INNER JOIN，都大致可以分为以下几类：1.JOIN KEY 有索引，主键2.JOIN KEY 有索引，二级索引3.JOIN KEY 无索引；JOIN BUFFER 是 MySQL 用来缓存以上第二、第三这两类 JOIN 检索的一个 BUFFER 内存区域块。 join_buffer_size=256K #可以增大此值以便于server端接收更大的SQL max_allowed_packet=4M #同一主机最大连续请求错误次数，如果还没成功建立连接，mysql服务器会组织这台主机后续的所有请求 max_connect_errors=100 #限制mysqld能打开文件的最大数 open_files_limit=4161 #一个connection级参数，在每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存 sort_buffer_size=256K #就是控制总frm文件的数量，还是个hash表，内部维护。如果打开的表实例的数量超过了table_definition_cache设置，LRU机制将开始标记表实例以进行清除，并最终将它们从数据字典缓存中删除。简单通俗点frm文件有多少，就设置多少了 table_definition_cache=1400 #指定基于行的二进制日志事件的最大大小 binlog_row_event_max_size=8K #本参数用于主从库中配置从库大于0作用为每个命令之后刷盘，小与0作为为永不刷盘，默认均为1000 sync_master_info=10000 #这个参数和sync_binlog是一样的，当设置为1时，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区，然后刷入relay log中继日志里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量I/O。当设置为0时，并不是马上就刷入中继日志里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘I/O操作。这个值默认是0，可动态修改，建议采用默认值。 sync_relay_log=10000 #这个参数和sync_relay_log参数一样，当设置为1时，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区，然后刷入relay-log.info里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量I/O。当设置为0时，并不是马上就刷入relay-log.info里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘I/O操作。这个值默认是0，可动态修改，建议采用默认值 sync_relay_log_info=10000 {/alert}
- 2022年09月23日
- 187 阅读
- 0 评论
- 0 点赞
2022-09-20
7. SQLServer : 其他函数(JSON函数、逻辑函数、聚合函数、排名函数、配置) JSON函数相关(SQLServer16及以上)ISJSON ：测试字符串是否包含有效JSON{alert type="success"}select ISJSON('{"a":1,"b":2}'); -- 1 : true 0：fasle{/alert}JSON_VALUE : 从字符串中提取标量值{alert type="success"}select json_value('{"a":1,"b":2,"c":{"cc":3}}','$.b'); -- 2 select json_value('{"a":1,"b":2,"c":{"cc":3}}','$.c.cc'); -- 3{/alert}JSON_QUERY : 从 JSON 字符串中提取对象或数组{callout color="#4fef4d"}如果值不是对象或者数组、会报错或者返回NULL{/callout}{alert type="success"}select JSON_QUERY('{"a":1,"b":2,"c":{"cc":3}}','$.c.cc'); -- Null select JSON_QUERY('{"a":1,"b":2,"c":{"cc":3}}','$.c'); -- {"cc":3}{/alert}JSON_MODIFY : 更新 JSON 字符串中属性的值，并返回已更新的 JSON 字符串{alert type="success"}-- 语法 JSON_MODIFY ( expression , path , newValue ) -- 示例 select JSON_MODIFY('{"a":1,"b":2,"c":{"cc":3}}','$.c','{dd:4}'); -- {"a":1,"b":2,"c":"{dd:4}"}{/alert}将JSON 集合转换为行集{callout color="#9aaaea"}如果必须基于 JSON 数据创建查询或报表，可以通过调用 OPENJSON 行集函数，轻松地将 JSON 数据转换为行与列。{/callout}{alert type="info"}DECLARE @json NVARCHAR(MAX); SET @json = N'[ {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25}, {"id": 5, "info": {"name": "Jane", "surname": "Smith"}, "dob": "2005-11-04T12:00:00"} ]'; SELECT * FROM OPENJSON(@json) WITH ( id INT 'strict $.id', firstName NVARCHAR(50) '$.info.name', lastName NVARCHAR(50) '$.info.surname', age INT, dateOfBirth DATETIME2 '$.dob' ); DECLARE @json NVARCHAR(MAX); SET @json = N'[ {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25}, {"id": 5, "info": {"name": "Jane", "surname": "Smith", "skills": ["SQL", "C#", "Azure"]}, "dob": "2005-11-04T12:00:00"} ]'; SELECT id, firstName, lastName, age, dateOfBirth, skill FROM OPENJSON(@json) WITH ( id INT 'strict $.id', firstName NVARCHAR(50) '$.info.name', lastName NVARCHAR(50) '$.info.surname', age INT, dateOfBirth DATETIME2 '$.dob', skills NVARCHAR(MAX) '$.info.skills' AS JSON ) OUTER APPLY OPENJSON(skills) WITH (skill NVARCHAR(8) '$');{/alert}将 SQL Server 数据转换为 JSON 或导出 JSON{alert type="info"}SELECT id, firstName AS "info.name", lastName AS "info.surname", age, dateOfBirth AS dob FROM People FOR JSON PATH;{/alert}OPENJSON{alert type="info"}在不提供结果的显式架构的情况下使用 OPENJSON 函数时（即，在 OPENJSON 之后不使用 WITH 子句），该函数将返回包含以下三列的表：输入对象中属性的名称（或输入数组中元素的索引）。属性或数组元素的值。类型（例如，字符串、数字、布尔值、数组或对象）。OPENJSON 以单独的行返回 JSON 对象的每个属性或数组的每个元素。{/alert}具有默认输出的 OPENJSON{alert type="info"}下面是使用具有默认架构（即不包含可选的 WITH 子句）的 OPENJSON 的快捷示例，该示例为 JSON 对象的每个属性返回一行。 {/alert}{alert type="info"}DECLARE @json NVARCHAR(MAX) SET @json='{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}'; SELECT * FROM OPENJSON(@json);{/alert}具有显式结构的 OPENJSON 输出{alert type="info"}如果使用 OPENJSON 函数的 WITH 子句指定结果的架构，该函数返回的表只包含 WITH 子句中定义的列。在可选的 WITH 子句中，指定一组输出列、列类型和每个输出值的 JSON 源属性的路径。 OPENJSON 循环访问 JSON 对象的数组，读取每一列的指定路径上的值，并将值转换为指定类型。{/alert}{alert type="info"}DECLARE @json NVARCHAR(MAX) SET @json = N'[ { "Order": { "Number":"SO43659", "Date":"2011-05-31T00:00:00" }, "AccountNumber":"AW29825", "Item": { "Price":2024.9940, "Quantity":1 } }, { "Order": { "Number":"SO43661", "Date":"2011-06-01T00:00:00" }, "AccountNumber":"AW73565", "Item": { "Price":2024.9940, "Quantity":3 } } ]' SELECT * FROM OPENJSON ( @json ) WITH ( Number varchar(200) '$.Order.Number' , Date datetime '$.Order.Date', Customer varchar(200) '$.AccountNumber', Quantity int '$.Item.Quantity' ) {/alert}返回对象的各个属性{alert type="info"}-- 原始 SELECT * FROM OPENJSON('{"name":"John","surname":"Doe","age":45}') -- 分析 SELECT * FROM OPENJSON('{"name":"John","surname":"Doe","age":45}') with ( name nvarchar(255), surname nvarchar(255), age nvarchar(255) ) {/alert}返回数组各元素{alert type="info"}SELECT * FROM OPENJSON('["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]') {/alert}将 JSON 转换成临时表{alert type="info"}-- 以下查询返回 info 对象的所有属性 DECLARE @json NVARCHAR(MAX) SET @json=N'{ "info":{ "type":1, "address":{ "town":"Bristol", "county":"Avon", "country":"England" }, "tags":["Sport", "Water polo"] }, "type":"Basic" }' SELECT * FROM OPENJSON(@json,N'lax $.info'){/alert}使用 WITH 子句设置输出格式{alert type="info"}DECLARE @json NVARCHAR(MAX) = N'{"someObject": {"someArray": [ {"k1": 11, "k2": null, "k3": "text"}, {"k1": 21, "k2": "text2", "k4": { "data": "text4" }}, {"k1": 31, "k2": 32}, {"k1": 41, "k2": null, "k4": { "data": false }} ] } }' SELECT * FROM OPENJSON(@json, N'lax $.someObject.someArray') WITH ( k1 int, k2 varchar(100), col3 varchar(6) N'$.k3', col4 varchar(10) N'lax $.k4.data', col5 nvarchar(MAX) N'lax $.k4' AS JSON, array_element nvarchar(MAX) N'$' AS JSON ) {/alert}使用 FOR JSON 将查询结果格式化为 JSON{alert type="info"}使用 FOR JSON PATH 来保持对 JSON 输出格式的完全控制。你可以创建包装对象并嵌套复杂属性。若要根据 SELECT 语句的结构自动格式化 JSON 输出，请使用 FOR JSON AUTO。{/alert}{alert type="info"}SELECT name, surname FROM emp FOR JSON AUTO;{/alert}聚合函数AVG : 平均值计算{alert type="info"}-- 语法 AVG ( [ ALL | DISTINCT ] expression ) [ OVER ( [ partition_by_clause ] order_by_clause ) ]{/alert}搭配 GROUP BY 子句使用 SUM 和 AVG 函数{alert type="info"}SELECT TerritoryID, AVG(Bonus)as 'Average bonus', SUM(SalesYTD) as 'YTD sales' FROM Sales.SalesPerson GROUP BY TerritoryID; GO {/alert}带 DISTINCT 使用 AVG{callout color="#f0ad4e"}DISTINCT剔除字段中重复值{/callout}{alert type="info"}SELECT AVG(DISTINCT ListPrice) FROM Production.Product; SELECT AVG(ListPrice) FROM Production.Product; {/alert}使用 OVER 子句{callout color="#4def86"}{/callout}{alert type="success"}SELECT BusinessEntityID, TerritoryID ,DATEPART(yy,ModifiedDate) AS SalesYear ,CONVERT(VARCHAR(20),SalesYTD,1) AS SalesYTD ,CONVERT(VARCHAR(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy,ModifiedDate) ),1) AS MovingAvg ,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy,ModifiedDate) ),1) AS CumulativeTotal FROM Sales.SalesPerson WHERE TerritoryID IS NULL OR TerritoryID < 5 ORDER BY TerritoryID,SalesYear; {/alert}COUNT{alert type="info"}-- 语法 -- Aggregation Function Syntax COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } ) -- Analytic Function Syntax COUNT ( [ ALL ] { expression | * } ) OVER ( [ <partition_by_clause> ] ) {/alert}使用 COUNT 和 DISTINCT{alert type="success"}SELECT COUNT(DISTINCT Title) FROM HumanResources.Employee; {/alert}使用 COUNT(*){alert type="success"}SELECT COUNT(*) FROM HumanResources.Employee; {/alert}使用OVER子句{alert type="success"}SELECT DISTINCT Name , MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary , MAX(Rate) OVER (PARTITION BY edh.DepartmentID) AS MaxSalary , AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS AvgSalary ,COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept FROM HumanResources.EmployeePayHistory AS eph JOIN HumanResources.EmployeeDepartmentHistory AS edh ON eph.BusinessEntityID = edh.BusinessEntityID JOIN HumanResources.Department AS d ON d.DepartmentID = edh.DepartmentID WHERE edh.EndDate IS NULL ORDER BY Name; {/alert}MAX、MIN、SUM{alert type="success"}-- 语法 -- Aggregation Function Syntax MAX( [ ALL | DISTINCT ] expression ) -- Analytic Function Syntax MAX ([ ALL ] expression) OVER ( <partition_by_clause> [ <order_by_clause> ] ) {/alert}{alert type="info"}SELECT DISTINCT Name , MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary , MAX(Rate) OVER (PARTITION BY edh.DepartmentID) AS MaxSalary , AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS AvgSalary ,COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept FROM HumanResources.EmployeePayHistory AS eph JOIN HumanResources.EmployeeDepartmentHistory AS edh ON eph.BusinessEntityID = edh.BusinessEntityID JOIN HumanResources.Department AS d ON d.DepartmentID = edh.DepartmentID WHERE edh.EndDate IS NULL ORDER BY Name; {/alert}配置{alert type="info"}配置说明@@DBTS当前数据库的当前 timestamp 数据类型的值@@LANGUAGE当前所用语言的名称@@MAX_CONNECTIONS允许同时进行的最大用户连接数@@MAX_PRECISION返回 decimal 和 numeric 数据类型所用的精度级别@@SERVERNAME运行 SQL Server 的本地服务器的名称@@SPID返回当前用户进程的会话 ID@@VERSION当前 SQL Server 安装的系统和生成信息{/alert}逻辑函数CHOOSE{callout color="#f0ad4e"}在 SQL Server 中从值列表返回指定索引处的项。{/callout}{alert type="success"}SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result; SELECT Name, ModifiedDate, CHOOSE(MONTH(ModifiedDate),'Winter','Winter', 'Spring','Spring','Spring','Summer','Summer', 'Summer','Autumn','Autumn','Autumn','Winter') AS Quarter_Modified FROM SalesLT.ProductModel AS PM WHERE Name LIKE '%Frame%' ORDER BY ModifiedDate; {/alert}IIF{alert type="success"}-- 如果布尔表达式为 true，则返回 true_value；如果布尔表达式为 false 或未知，则返回 false_value IIF( boolean_expression, true_value, false_value ) DECLARE @P INT = NULL, @S INT = NULL; SELECT [Result] = IIF( 45 > 30, @P, @S );{/alert}排名函数{callout color="#4def6d"}排名函数为分区中的每一行返回一个排名值。根据所使用的函数，某些行可能会收到与其他行相同的值。排名函数是不确定的。{/callout}{alert type="success"}SELECT p.FirstName, p.LastName ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number" ,RANK() OVER (ORDER BY a.PostalCode) AS Rank ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS "Dense Rank" ,NTILE(4) OVER (ORDER BY a.PostalCode) AS Quartile ,s.SalesYTD ,a.PostalCode FROM Sales.SalesPerson AS s INNER JOIN Person.Person AS p ON s.BusinessEntityID = p.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = p.BusinessEntityID WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0; FirstName LastName Row Number Rank Dense Rank Quartile SalesYTD PostalCode Michael Blythe 1 1 1 1 4557045.0459 98027 Linda Mitchell 2 1 1 1 5200475.2313 98027 Jillian Carson 3 1 1 1 3857163.6332 98027 Garrett Vargas 4 1 1 1 1764938.9859 98027 Tsvi Reiter 5 1 1 2 2811012.7151 98027 Shu Ito 6 6 2 2 3018725.4858 98055 José Saraiva 7 6 2 2 3189356.2465 98055 David Campbell 8 6 2 3 3587378.4257 98055 Tete Mensa-Annan 9 6 2 3 1931620.1835 98055 Lynn Tsoflias 10 6 2 3 1758385.926 98055 Rachel Valdez 11 6 2 4 2241204.0424 98055 Jae Pak 12 6 2 4 5015682.3752 98055 Ranjit Varkey Chudukatil 13 6 2 4 3827950.238 98055 {/alert}DENSE_RANK{callout color="#f788c3"}此函数返回结果集分区中每行的排名，排名值没有间断。特定行的排名等于该特定行之前不同排名值的数量加一。{/callout}{alert type="success"}-- 语法 DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > ) SELECT i.ProductID, p.Name, i.LocationID, i.Quantity ,DENSE_RANK() OVER (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank FROM Production.ProductInventory AS i INNER JOIN Production.Product AS p ON i.ProductID = p.ProductID WHERE i.LocationID BETWEEN 3 AND 4 ORDER BY i.LocationID; {/alert}RANK{callout color="#f0ad4e"}返回结果集分区内每一行的排名。行的排名是相关行之前的排名数加1{/callout}{alert type="success"}RANK ( ) OVER ( [ partition_by_clause ] order_by_clause ) {/alert}NTILE{callout color="#4defdd"}将有序分区中的行分发到指定数目的组中。各个组有编号，编号从一开始。对于每一个行，NTILE 将返回此行所属的组的编号。{/callout}{alert type="success"}NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > ) SELECT p.FirstName, p.LastName ,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS Quartile ,CONVERT(NVARCHAR(20),s.SalesYTD,1) AS SalesYTD , a.PostalCode FROM Sales.SalesPerson AS s INNER JOIN Person.Person AS p ON s.BusinessEntityID = p.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = p.BusinessEntityID WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0; {/alert}ROW_NUMBER{callout color="#4defdd"}对结果集的输出进行编号。更具体地说，返回结果集分区内行的序号，每个分区中的第一行从 1 开始。ROW_NUMBER并且RANK是相似的。ROW_NUMBER按顺序编号所有行（例如 1、2、3、4、5）。RANK为平局提供相同的数值（例如 1、2、2、4、5）。{/callout}{alert type="info"}ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause ) {/alert}
- 2022年09月20日
- 241 阅读
- 0 评论
- 1 点赞
2022-09-12
MYSQL5.7安装步骤卸载MYSQL以及残留{alert type="success"}{timeline}{timeline-item color="#19be6b"}删除MYSQL隐藏文件：C盘的 ProgramData 下会有一个 MySQL 文件夹，这个文件夹要删除{/timeline-item}{timeline-item color="#ed4014"}删除注册表：Windows运行框中输入 regedit , 打开注册表编辑窗口，逐级打开找到如下项：HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQLHKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQLHKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MySQL{/timeline-item}{timeline-item color="#ed4014"}删除服务项：运行框中运行 CMD 打开 Windows 命令行窗口，执行如下命令 : sc delete MySQL服务名 (比如 mysql57, 可以在本机服务列表中查看具体名称){/timeline-item}{/timeline}{/alert}镜像站{alert type="info"}清华大学镜像站阿里云镜像站网易镜像{/alert}使用清华镜像包安装Mysql5.7安装包下载{alert type="success"}下载64位MSI安装文件之后,点击安装。安装时选择安装的位置。{/alert}环境变量配置{alert type="info"}在系统变量中Path中添加Mysql安装目录,一定要添加到bin目录级如果Path值过长无法保存，可修改原Path变量名为Path1,新建Path变量，添加%Path1%{/alert}MySQL服务的安装与初次登录{alert type="success"}1.以管理员启动命令窗2.进入到MYSQL安装目录(bin级)3.执行命令mysqld -install -- 输出 Service successfully installed mysqld --initialize -- 没有输出，重新回到光标4.两条命令执行完毕以后，在MySQL的安装目录下会出现一个data文件夹,在data文件夹中有一个以.err结尾的文件,文件中存储了数据库的临时密码。也可以使用记事本的查找功能查找关键词password。记住此密码！！！5.执行命令 net start mysql ，启动MySQL服务6.初次登录：mysql -u root -p7.修改密码alter user 'root'@'localhost' identified by '123456'8.使用quit命令退出MySQL9.在MYSQL安装目录下新建my.ini文件[client] default-character-set=utf8 [mysql] default-character-set=utf8 [mysqld] character-set-server=utf810.以管理员身份启动命令行，重启MySQL服务net stop mysql net start mysql11.登录MySQL，使用命令 status 查看字符集。由于配置了环境变量，直接使用普通命令行即可登录MySQL{/alert}
- 2022年09月12日
- 218 阅读
- 0 评论
- 0 点赞
2022-06-28
6. SQLServer : 字符串函数 ASCII{callout color="#f5d9b2"}　　返回字符表达式中最左侧的字符的 ASCII 代码值。由于ASCII码码位包含128位数字、字母、特殊符号，因此如果传入参数为汉字等码位超过128的字符时结果会与预期不一致。UNICODE函数前128位与ASCII一致,此时可以使用 UNICODE 函数。{/callout}{alert type="info"}-- character_expression : char 或 varchar 类型的表达式。 ASCII ( character_expression ) character_expression : char 或 varchar 类型的表达式。返回值： int {/alert}CHAR{callout color="#c1f452"}返回具有指定整数代码的单字节字符，由当前数据库默认排序规则的字符集和编码定义。{/callout}{alert type="success"}CHAR ( integer_expression ) integer_expression : 0 到 255 之间的整数。对于此输入范围外的整数表达式或不表示完整字符的整数表达式，CHAR 返回 NULL 值。字符超出了返回类型的长度时，CHAR 也会返回 NULL 值。注解：使用 CHAR 可将控制字符插入字符字符串中。此表显示了一些常用的控制字符。控制字符值选项卡char(9)换行char(10)回车char(13)空格char(32){/alert}CHARINDEX{callout color="#f5a432"}函数会在第二个字符表达式中搜索一个字符表达式，这将返回第一个表达式（如果发现存在）的开始位置。索引初始值为1,不存在查询时返回0。{/callout}{alert type="warning"}CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] ) expressionToFind : 一个字符表达式，其中包含要查找的序列。 expressionToFind 限制为 8000 个字符。expressionToSearch : 要搜索的字符表达式。start_location : 表示搜索开始位置的 integer 或 bigint 表达式。如果 start_location 未指定、具有负数值或 0，搜索将从 expressionToSearch 的开头开始。返回类型：如果 expressionToSearch 具有一个 nvarchar(max)、varbinary(max) 或 varchar(max) 数据类型，则为 bigint；否则为 int 。{/alert}CONCAT{callout color="#84af0e"}此函数以端到端的方式返回从串联或联接的两个或更多字符串值生成的字符串。{/callout}{alert type="info"}CONCAT ( string_value1, string_value2 [, string_valueN ] ) string_value : 要与其他值串联的字符串值。 CONCAT 函数需要至少两个 string_value 自变量，并且不得超过 254 个 string_value 自变量。返回类型：长度和类型取决于输入的字符串值。{/alert}CONCAT_WS{callout color="#ff9705"}版本最低要求2017此函数以端到端的方式返回从串联或联接的两个或更多字符串值生成的字符串。它会用第一个函数参数中指定的分隔符分隔连接的字符串值。{/callout}{alert type="success"}CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )分隔符：任何字符类型的表达式（char、ncharnvarchar 或 varchar）。argument1、argument2、argumentN：任何类型的表达式。 CONCAT_WS 函数需要至少两个自变量，并且不得超过 254 个自变量。{/alert}DIFFERENCE{callout color="#8c98f2"}该函数返回一个整数值，用于度量两个不同字符表达式的 SOUNDEX() 值之间的差异。 DIFFERENCE 比较两个不同的 SOUNDEX 值，并返回一个整数值。该值用于度量 SOUNDEX 值匹配的程度，范围为 0 到 4。值为 0 表示 SOUNDEX 值之间的相似性较弱或不相似；4 表示与 SOUNDEX 值非常相似，甚至完全相同。DIFFERENCE 和 SOUNDEX 具有排序规则敏感度。{/callout}{alert type="info"}DIFFERENCE ( character_expression , character_expression ) character_expression : 字符数据的字母数字表达式。 character_expression 可以是常量、变量或列。返回类型： int {/alert}FORMAT{callout color="#d642ff"}2008版本不能使用，最低2012版本返回以指定的格式和可选的区域性格式化的值。使用 FORMAT 函数将日期/时间和数字值格式化为识别区域设置的字符串。对于一般的数据类型转换，请使用 CAST 或 CONVERT。{/callout}{alert type="success"}FORMAT( value, format [, culture ] ) value : 支持格式化的数据类型的表达式。format : format 参数必须包含一个有效的 .NET Framework 格式字符串，要么作为标准格式字符串（例如，“C”或“D”），要么作为日期值和数值的自定义字符模式（例如，“MMMM DD, yyyy (dddd)”）。不支持组合格式。返回类型： nvarchar 或 Null{/alert}示例一 :{alert type="info"}--简单 FORMAT 示例 DECLARE @d DATE = '11/22/2020'; SELECT FORMAT( @d, 'd', 'en-US' ) 'US English' ,FORMAT( @d, 'd', 'en-gb' ) 'Great Britain English' ,FORMAT( @d, 'd', 'de-de' ) 'German' ,FORMAT( @d, 'd', 'zh-cn' ) 'Simplified Chinese (PRC)'; SELECT FORMAT( @d, 'D', 'en-US' ) 'US English' ,FORMAT( @d, 'D', 'en-gb' ) 'Great Britain English' ,FORMAT( @d, 'D', 'de-de' ) 'German' ,FORMAT( @d, 'D', 'zh-cn' ) 'Chinese (Simplified PRC)'; --结果如下 US English Great Britain English German Simplified Chinese (PRC) ---------- --------------------- ---------- ------------------------ 11/22/2020 22/11/2020 22.11.2020 2020/11/22 US English Great Britain English German Chinese (Simplified PRC) --------------------------- ---------------------- -------------------------- --------------------------------------- Sunday, November 22, 2020 22 November 2020 Sonntag, 22. November 2020 2020年11月22日 {/alert}示例二 : 使用自定义格式字符串执行 FORMAT{callout color="#f0ad4e"}有关这些格式和其他自定义格式的详细信息，请参阅自定义数字格式字符串。{/callout}{alert type="info"}DECLARE @d DATE = GETDATE(); SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS 'Date' , FORMAT(123456789,'###-##-####') AS 'Custom Number'; ----结果如下 Date Custom Number ---------- ------------- 22/11/2020 123-45-6789 {/alert}示例三 : 用于数值类型的 FORMAT{callout color="#f0ad4e"}有关这些格式和其他数字格式的详细信息，请参阅标准数字格式字符串。{/callout}{alert type="info"}SELECT TOP(5) CurrencyRateID, EndOfDayRate ,FORMAT(EndOfDayRate, 'N', 'en-us') AS 'Number Format' ,FORMAT(EndOfDayRate, 'G', 'en-us') AS 'General Format' ,FORMAT(EndOfDayRate, 'C', 'en-us') AS 'Currency Format' FROM Sales.CurrencyRate ORDER BY CurrencyRateID; {/alert}示例四 : 时间数据类型{alert type="success"}SELECT FORMAT(cast('07:35' as time), N'hh\.mm'); --> returns 07.35 SELECT FORMAT(cast('07:35' as time), N'hh\:mm'); --> returns 07:35 SELECT FORMAT(SYSDATETIME(), N'hh:mm tt'); -- returns 03:46 PM SELECT FORMAT(SYSDATETIME(), N'hh:mm t'); -- returns 03:46 P select FORMAT(CAST('2018-01-01 01:00' AS datetime2), N'hh:mm tt') -- returns 01:00 AM select FORMAT(CAST('2018-01-01 01:00' AS datetime2), N'hh:mm t') -- returns 01:00 A select FORMAT(CAST('2018-01-01 14:00' AS datetime2), N'HH:mm') -- returns 14:00 {/alert}LEN{alert type="info"}-- 返回指定字符串表达式的字符数，其中不包含尾随空格 select LEN("1234"); -- 4{/alert}LEFT{callout color="#4def58"}返回字符串中从左边开始指定个数的字符。{/callout}{alert type="info"}SELECT LEFT('abcdefg',2); {/alert}LOWER、UPPER{callout color="#78ef4d"}将大写字符数据转换为小写字符数据后返回字符表达式。{/callout}{alert type="info"}SELECT LOWER('ABC'); -- abc SELECT UPPER('abc'); -- ABC{/alert}LTRIM、RTRIM{callout color="#f0ad4e"}删除字符串开头(结尾)的空格字符char(32)或其他指定字符。{/callout}{alert type="info"}LTRIM ( character_expression , [ characters ] ) RTRIM ( character_expression , [ characters ] )character_expression : 字符或二进制数据的表达式characters : SQL Server 2022 (16.x) 预览版及更高版本{/alert}REPLACE{callout color="#68ef4d"}用另一个字符串值替换所有出现的指定字符串值{/callout}{alert type="success"}-- 语法 REPLACE( string_expression , string_pattern , string_replacement ) -- 示例 SELECT REPLACE('123123','3','7'); -- 127127{/alert}REPLICATE{callout color="#f0ad4e"}重复指定次数的字符串值{/callout}{alert type="success"}-- 语法 REPLICATE ( string_expression , integer_expression ) -- 示例 SELECT REPLICATE(){/alert}REVERSE{callout color="#f0ad4e"}返回字符串值的反向顺序{/callout}{alert type="info"}select REVERSE('123'); -- 321{/alert}RIGHT{callout color="#4defc7"}返回具有指定字符数的字符串的右边部分{/callout}{alert type="success"}SELECT RIGHT('1234',2); -- 34{/alert}STR{callout color="#4dcfef"}返回从数字数据转换而来的字符数据。字符数据右对齐，具有指定的长度和小数精度{/callout}{alert type="success"}select str('12',3); -- len(str('12',3)) = 3{/alert}SUTFF{callout color="#f0ad4e"}STUFF函数将一个字符串插入到另一个字符串中。它在开始位置删除第一个字符串中指定长度的字符，然后在开始位置将第二个字符串插入第一个字符串中。{/callout}{alert type="info"}-- 语法 STUFF ( character_expression , start , length , replaceWith_expression ) -- 示例 SELECT STUFF('abcdef', 6, 0, 'ijklmn'); -- abcdeijklmnf SELECT STUFF('abcdef', 1, 0, 'ijklmn'); -- abcdeijklmnf SELECT STUFF('abcdef', 2, 3, 'ijklmn'); -- aijklmnefcharacter_expression ：第一个字符串start : 第一个字符串位置(SQL中第一个索引为1)length : 删除的字符个数第二个字符串，将插入在start之前{/alert}SUBSTRING{alert type="info"}select SUBSTRING('1234',2,2); -- 23{/alert}UNICODE{callout color="#f0ad4e"}为输入表达式的第一个字符返回Unicode标准定义的整数值{/callout}{alert type="info"}select UNICODE('A'); -- 65{/alert}
- 2022年06月28日
- 209 阅读
- 0 评论
- 1 点赞
2022-06-25
5. SQLServer : 数据类型转换 CAST语法 : CAST ( expression AS data_type [ ( length ) ] ) {card-default label="示例" width="100%"}USE AdventureWorks2012; GO SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice FROM Production.Product WHERE CAST(ListPrice AS int) LIKE '33%'; GO {/card-default}CONVERT语法 : CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) {card-default label="示例" width="100%"}USE AdventureWorks2012; GO SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice FROM Production.Product WHERE CONVERT(int, ListPrice) LIKE '33%'; {/card-default} 不带世纪数位 (yy) (1) 带世纪数位 (yyyy) Standard 输入/输出 (3) - 0 或 100 (1,2) datetime 和 smalldatetime 的默认值 mon dd yyyy hh:miAM（或 PM） 1 101 美国 1 = mm/dd/yy 101 = mm/dd/yyyy 2 102 ANSI 2 = yy.mm.dd 102 = yyyy.mm.dd 3 103 英国/法国 3 = dd/mm/yy 103 = dd/mm/yyyy 4 104 德语 4 = dd.mm.yy 104 = dd.mm.yyyy 5 105 意大利语 5 = dd-mm-yy 105 = dd-mm-yyyy 6 106(1) - 6 = dd mon yy 106 = dd mon yyyy 7 107(1) - 7 = Mon dd, yy 107 = Mon dd, yyyy 8 或 24 108 - hh:mi:ss - 9 或 109 (1,2) 默认格式 + 毫秒 mon dd yyyy hh:mi:ss:mmmAM（或 PM） 10 110 USA 10 = mm-dd-yy 110 = mm-dd-yyyy 11 111 日本 11 = yy/mm/dd 111 = yyyy/mm/dd 12 112 ISO 12 = yymmdd 112 = yyyymmdd - 13 或 113 (1,2) 欧洲默认格式 + 毫秒 dd mon yyyy hh:mi:ss:mmm（24 小时制） 14 114 - hh:mi:ss:mmm（24 小时制） - 20 或 120 (2) ODBC 规范 yyyy-mm-dd hh:mi:ss（24 小时制） - 21、25 或 121 (2) time、date、datetime2 和 datetimeoffset 的 ODBC 规范（带毫秒）默认值 yyyy-mm-dd hh:mi:ss.mmm（24 小时制） 22 - 美国 mm/dd/yy hh:mi:ss AM（或 PM） - 23 ISO8601 yyyy-mm-dd - 126 (4) ISO8601 yyyy-mm-ddThh:mi:ss.mmm（无空格）注意：毫秒 (mmm) 值为 0 时，不会显示毫秒小数部分的值。例如，值“2012-11-07T18:26:20.000”显示为“2012-11-07T18:26:20”。 - 127(6, 7) 带时区 Z 的 ISO8601。 yyyy-MM-ddThh:mm:ss.fffZ（不带空格）注意：毫秒 (mmm) 值为 0 时，不会显示毫秒小数值。例如，值“2012-11-07T18:26:20.000”显示为“2012-11-07T18:26:20”。 - 130 (1,2) 回历 (5) dd mon yyyy hh:mi:ss:mmmAM 在此样式中，mon 表示完整月份名称的多标记回历 unicode 表示形式。该值在 SSMS 的默认 US 安装中不会正确呈现。 - 131 (2) 回历 (5) dd/mm/yyyy hh:mi:ss:mmmAM
- 2022年06月25日
- 166 阅读
- 0 评论
- 0 点赞
2022-06-25
4. SQLServer : 日期时间部分返回系统日期和时间值的函数精度较高的系统日期和时间函数SYSDATETIME{alert type="info"}语法 : SYSDATETIME()返回值 : 返回包含计算机的日期和时间的 datetime2(7) 值，SQL Server 的实例在该计算机上运行。返回值不包括时区偏移量。返回值类型 : datetime2(7){/alert}SYSDATETIMEOFFSET{alert type="info"}语法 : SYSDATETIMEOFFSET ()返回值 : 返回包含计算机的日期和时间的 datetimeoffset(7) 值，SQL Server 的实例在该计算机上运行。返回值包括时区偏移量。返回值类型 : datetimeoffset(7){/alert}SYSUTCDATETIME{alert type="info"}语法 : SYSDATETIME()返回值 : 返回包含计算机的日期和时间的 datetime2(7) 值，SQL Server 的实例正在该计算机上运行。该函数返回日期和时间作为 UTC 时间（协调世界时）。返回值类型 : datetime2(7){/alert}精度较高低系统日期和时间函数CURRENT_TIMESTAMP{alert type="success"}语法 : CURRENT_TIMESTAMP返回值 : 返回包含计算机的日期和时间的 datetime 值，SQL Server 的实例在该计算机上运行。返回值不包括时区偏移量。返回值数据类型 : datetime{/alert}GETDATE{alert type="success"}语法 : GETDATE()返回值 : 返回包含计算机的日期和时间的 datetime 值，SQL Server 的实例在该计算机上运行。返回值不包括时区偏移量。返回值数据类型 : datetime{/alert}GETUTCDATE{alert type="success"}语法 : GETUTCDATE()返回值 : 返回包含计算机的日期和时间的 datetime 值，SQL Server 的实例在该计算机上运行。该函数返回日期和时间作为 UTC 时间（协调世界时）。返回值数据类型 : datetime{/alert}返回日期和时间部分的函数DATENAME : 返回表示指定 date 的指定 datepart 的字符串{alert type="warning"}语法 : DATENAME ( datepart , date ) 返回值类型: nvarchar{/alert} datepart 缩写形式 year yy, yyyy quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww weekday dw, w hour hh minute mi, n second ss, s millisecond ms microsecond mcs nanosecond ns TZoffset tz ISO_WEEK ISOWK, ISOWW DATEPART : 返回表示指定 date 的指定 datepart 的整数{alert type="error"}语法 : DATEPART ( datepart , date )返回值类型 : int {/alert}DAY : 返回表示指定 date 的“日”部分的整数{alert type="info"}语法 : DAY ( date )返回值类型 : int{/alert}MONTH : 返回表示指定 date 的“月”部分的整数{alert type="info"}语法 : MONTH ( date )返回值类型 : int{/alert}YEAR : 返回表示指定 date 的“年”部分的整数{alert type="info"}语法 : YEAR ( date )返回值类型 : int{/alert}从相应部分返回日期和时间值的函数DATEFROMPARTS : 此函数返回映射到指定年、月、日值的 date 值{callout color="#a0f49a"}此函数可在 SQL Server 2012 (11.x) 及更高版本的服务器上执行远程处理。它不能无法在版本低于 SQL Server 2012 (11.x) 的服务器上执行远程处理。{/callout}{alert type="info"}语法 : DATEFROMPARTS ( year, month, day )返回值类型 : date {/alert}DATETIME2FROMPARTS : 对指定日期和时间参数返回 datetime2 值{callout color="#f0ad4e"}此函数可以在 SQL Server 2012 (11.x) 服务器以及更高版本上远程执行。但在 SQL Server 2012 (11.x) 之下的服务器版本中无法远程执行。{/callout}{alert type="info"}语法 : DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision ) 返回值类型 : datetime2 {/alert}DATETIMEFROMPARTS : 对指定日期和时间参数返回 datetime 值{callout color="#94e87d"}此函数可以在 SQL Server 2012 (11.x) 服务器以及更高版本上远程执行。但在 SQL Server 2012 (11.x) 之下的服务器版本中无法远程执行。{/callout}{alert type="info"}语法 : DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds ) 返回值类型 : datetime{/alert}TIMEFROMPARTS : 对指定的时间返回 time 值{alert type="info"}语法 : TIMEFROMPARTS ( hour, minute, seconds, fractions, precision ) 返回值类型 : time {/alert}
- 2022年06月25日
- 149 阅读
- 0 评论
- 1 点赞
2022-06-13
3. FOR XML (SQL Server) : SELECT 查询将结果作为行集返回环境要求{alert type="info"} MicroSoft SqlServer 文档所有版本 SQL Server版本均支持此语法 {/alert}基本初识{alert type="success"}在 FOR XML 子句中，有 RAW、AUTO、EXPLICIT、PATH 四种模式。RAW 模式：　　RAW 模式在 SELECT 语句返回的行集中为每个行生成一 <个行> 元素。可以通过编写嵌套 FOR XML 查询来生成 XML 层次结构。AUTO 模式：　　AUTO 模式将基于指定 SELECT 语句的方式来使用试探性方法在 XML 结果中生成嵌套。您对生成的 XML 的形状具有最低限度的控制能力。除了 AUTO 模式的试探性方法生成的 XML 形状之外，还可以编写 FOR XML 查询来生成 XML 层次结构。EXPLICIT模式：　　EXPLICIT 模式允许对 XML 的形状进行更多控制。您可以随意混合属性和元素来确定 XML 的形状。PATH模式：　　PATH 模式与嵌套 FOR XML 查询功能一起以较简单的方式提供了 EXPLICIT 模式的灵活性。{/alert}测试数据库：{card-default label="创建数据库表" width="100%"}CREATE TABLE [dbo].[userTable]( [id] [nvarchar](50) NULL, [encode] [nvarchar](50) NULL, [name] [nvarchar](50) NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[userTable] ADD CONSTRAINT [DF_user_id] DEFAULT (newid()) FOR [id] GO {/card-default} {card-default label="插入测试数据" width="100%"}INSERT [dbo].[userTable] ([id], [encode], [name]) VALUES (N'1A074FE5-BAB3-4B71-932D-393387E94B5F', N'001', N'张三') GO INSERT [dbo].[userTable] ([id], [encode], [name]) VALUES (N'F75F5FA1-D8A1-4076-B840-99941DB9038F', N'002', N'李四') GO INSERT [dbo].[userTable] ([id], [encode], [name]) VALUES (N'FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4', N'003', N'王五') GO INSERT [dbo].[userTable] ([id],[encode], [name]) VALUES (N'79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A', N'004',null) {/card-default}{card-default label="测试表视图" width=""}idencodename1A074FE5-BAB3-4B71-932D-393387E94B5F001张三F75F5FA1-D8A1-4076-B840-99941DB9038F002李四FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4003王五79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A004NULL{/card-default}使用示例：{callout color="#bff990"}将查询结果作为行集返回，即只有一个查询行，此行只有一个查询列。多个指令可使用逗号分割开。{/callout}RAW模式示例一 : 基本使用{card-default label="SQL语句" width="100%"}select * from userTable for xml raw ;{/card-default}{card-default label="执行结果" width="100%"} <row id="1A074FE5-BAB3-4B71-932D-393387E94B5F" encode="001" name="张三" /> <row id="F75F5FA1-D8A1-4076-B840-99941DB9038F" encode="002" name="李四" /> <row id="FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4" encode="003" name="王五" /> <row id="79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A" encode="004" />{/card-default}示例二 : ELEMENTS 指令{card-default label="SQL语句" width="100%"}select * from userTable for xml raw,ELEMENTS ;{/card-default} {card-default label="执行结果" width="100%"} <row> <id>1A074FE5-BAB3-4B71-932D-393387E94B5F</id> <encode>001</encode> <name>张三</name> </row> <row> <id>F75F5FA1-D8A1-4076-B840-99941DB9038F</id> <encode>002</encode> <name>李四</name> </row> <row> <id>FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4</id> <encode>003</encode> <name>王五</name> </row> <row> <id>79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A</id> <encode>004</encode> </row>{/card-default}示例三 : XSINIL 添加指令 ELEMENTS{card-default label="SQL语句" width="100%"}select * from userTable for xml raw,ELEMENTS XSINIL ;{/card-default}{card-default label="执行结果" width="100%"} <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <id>1A074FE5-BAB3-4B71-932D-393387E94B5F</id> <encode>001</encode> <name>张三</name> </row> <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <id>F75F5FA1-D8A1-4076-B840-99941DB9038F</id> <encode>002</encode> <name>李四</name> </row> <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <id>FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4</id> <encode>003</encode> <name>王五</name> </row> <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <id>79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A</id> <encode>004</encode> <name xsi:nil="true" /> </row>{/card-default}示例四 : XMLDATA 指令{card-default label="SQL语句" width="100%"}select * from userTable for xml raw,XMLDATA ;{/card-default}{card-default label="执行结果" width="100%"} <Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes"> <ElementType name="row" content="empty" model="closed"> <AttributeType name="id" dt:type="string" /> <AttributeType name="encode" dt:type="string" /> <AttributeType name="name" dt:type="string" /> <attribute type="id" /> <attribute type="encode" /> <attribute type="name" /> </ElementType> </Schema> <row xmlns="x-schema:#Schema1" id="1A074FE5-BAB3-4B71-932D-393387E94B5F" encode="001" name="张三" /> <row xmlns="x-schema:#Schema1" id="F75F5FA1-D8A1-4076-B840-99941DB9038F" encode="002" name="李四" /> <row xmlns="x-schema:#Schema1" id="FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4" encode="003" name="王五" /> <row xmlns="x-schema:#Schema1" id="79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A" encode="004" />{/card-default}其他{alert type="info"}FOR XML RAW ('xx'): 重命名 <行> 元素。ROOT 指令：为 FOR XML 生成的 XML 指定根元素。比如：ROOT('MyRoot');XMLSCHEMA 指令：返回 XSD 架构。比如：可以使用 XMLSCHEMA ('urn:example.com') 格式指定命名空间。BINARY BASE64 指令：返回在 varbinary(max)、binary 类型列中存储的以 base64 编码格式返回二进制数据。{/alert}AUTO 模式示例一:{card-default label="SQL语句" width="100%"}select * from userTable x for xml AUTO;{/card-default}{card-default label="执行结果" width="100%"} <userTable id="1A074FE5-BAB3-4B71-932D-393387E94B5F" encode="001" name="张三" /> <userTable id="F75F5FA1-D8A1-4076-B840-99941DB9038F" encode="002" name="李四" /> <userTable id="FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4" encode="003" name="王五" /> <userTable id="79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A" encode="004" />{/card-default}示例二:{callout color="#f0ad4e"}　　先指定 OrderHeader 表中的列，再指定 Cust 表中的列，因此，将创建第一个元素，然后将子元素添加到其中。{/callout}{card-default label="SQL语句" width="100%"}elect OrderHeader.CustomerID, OrderHeader.SalesOrderID, OrderHeader.Status, Cust.CustomerID, Cust.CustomerType from Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader where Cust.CustomerID = OrderHeader.CustomerID for xml auto; {/card-default}{card-default label="执行结果" width="100%"} <OrderHeader CustomerID="1" SalesOrderID="43860" Status="5"> <Cust CustomerID="1" CustomerType="S" /> </OrderHeader> ...{/card-default}PATH 模式示例一{card-default label="SQL语句" width="100%"}select * from userTable for xml PATH {/card-default}{card-default label="执行结果" width="100%"}<row> <id>1A074FE5-BAB3-4B71-932D-393387E94B5F</id> <encode>001</encode> <name>张三</name> </row> <row> <id>F75F5FA1-D8A1-4076-B840-99941DB9038F</id> <encode>002</encode> <name>李四</name> </row> <row> <id>FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4</id> <encode>003</encode> <name>王五</name> </row> <row> <id>79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A</id> <encode>004</encode> </row>{/card-default}示例二{card-default label="SQL语句" width="100%"}select * from userTable for xml PATH('') ;{/card-default}{card-default label="执行结果" width="100%"}<id>1A074FE5-BAB3-4B71-932D-393387E94B5F</id> <encode>001</encode> <name>张三</name> <id>F75F5FA1-D8A1-4076-B840-99941DB9038F</id> <encode>002</encode> <name>李四</name> <id>FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4</id> <encode>003</encode> <name>王五</name> <id>79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A</id> <encode>004</encode>{/card-default}示例三{callout color="#58d336"}如果列名称以符号 (@) 开头，并且不包含斜杠标记 (/)，则会创建具有相应列值的元素的属性 row在对应的语句中，可根据不同模式对表名、列名进行别名的设置而得到相应得到XML结构{/callout}{card-default label="SQL语句" width="100%"}select id as '@part',encode as '@code',name from userTable for xml PATH{/card-default}{card-default label="执行结果" width="100%"}<row part="1A074FE5-BAB3-4B71-932D-393387E94B5F" code="001"> <name>张三</name> </row> <row part="F75F5FA1-D8A1-4076-B840-99941DB9038F" code="002"> <name>李四</name> </row> <row part="FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4" code="003"> <name>王五</name> </row> <row part="79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A" code="004" />{/card-default}示例四{callout color="#f4d19f"}如果列名不以符号 () @ 开头，但包含斜杠标记 (/) ，则列名表示 XML 层次结构。 {/callout}{card-default label="SQL语句" width="100%"}select ID as 'ID1/ID2',ENCODE,NAME from userTable for xml PATH{/card-default}{card-default label="执行结果" width="100%"}<row> <ID1> <ID2>1A074FE5-BAB3-4B71-932D-393387E94B5F</ID2> </ID1> <ENCODE>001</ENCODE> <NAME>张三</NAME> </row> <row> <ID1> <ID2>F75F5FA1-D8A1-4076-B840-99941DB9038F</ID2> </ID1> <ENCODE>002</ENCODE> <NAME>李四</NAME> </row> <row> <ID1> <ID2>FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4</ID2> </ID1> <ENCODE>003</ENCODE> <NAME>王五</NAME> </row> <row> <ID1> <ID2>79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A</ID2> </ID1> <ENCODE>004</ENCODE> </row>{/card-default}示例五{callout color="#f2eade"}如果指定的列名是通配符 (*) ，则插入该列的内容就好像没有指定列名一样。如果此列不是xml 类型的列，则此列的内容将作为文本节点插入.{/callout}{card-default label="SQL语句" width="100%"}select ID as '*',ENCODE,NAME from userTable for xml PATH {/card-default}{card-default label="执行结果" width="100%"}<row>1A074FE5-BAB3-4B71-932D-393387E94B5F<ENCODE>001</ENCODE><NAME>张三</NAME></row> <row>F75F5FA1-D8A1-4076-B840-99941DB9038F<ENCODE>002</ENCODE><NAME>李四</NAME></row> <row>FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4<ENCODE>003</ENCODE><NAME>王五</NAME></row> <row>79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A<ENCODE>004</ENCODE></row>{/card-default}示例六{callout color="#c4c0ba"}如果列名是某个 XPath 节点测试，将如下表所示映射该列的内容。如果列名是某个 XPath 节点测试，则该列的内容将映射到相应的节点。如果该列的 SQL 类型是 xml，将返回一个错误。{/callout}列名行为text()对于名为 text() 的列，该列中的字符串值将被添加为文本节点。comment()对于名为 comment() 的列，该列中的字符串值将被添加为 XML 注释。node()对于节点名称 () 的列，结果与列名称是通配符 () * 时的结果相同。处理指令（名称）如果列名为处理指令，该列中的字符串值将被添加为此处理指令目标名称的 PI 值。{card-default label="SQL语句" width="100%"}select ID as 'text()',ENCODE as 'comment()',NAME as 'node()' from userTable for xml PATH {/card-default}{card-default label="执行结果" width="100%"}<row>1A074FE5-BAB3-4B71-932D-393387E94B5F张三</row> <row>F75F5FA1-D8A1-4076-B840-99941DB9038F李四</row> <row>FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4王五</row> <row>79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A</row>{/card-default}示例七{card-default label="SQL语句" width="100%"}select ','+ID from userTable for xml PATH(''){/card-default}{card-default label="执行结果" width="100%"},1A074FE5-BAB3-4B71-932D-393387E94B5F,F75F5FA1-D8A1-4076-B840-99941DB9038F,FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4,79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A{/card-default}EXPLICIT 模式{callout color="#37ae9b"}具体可参阅微软官方文档{/callout}
- 2022年06月13日
- 216 阅读
- 0 评论
- 1 点赞
2022-04-20
2. 分组聚合函数：ROW_NUMBER() OVER (PARTITION BY Column ORDER BY COLUMN {alert type="success"}分组聚合函数可以对数据分组并排序以按分组增加一个自动递增的列以得到理想的数据。SQLServer版本至少2005版语法:ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY COLUMN2)Column1: 分组列Column2：排序列演示：1.创建如下表Table_1结构并插入数据：idkysl1kysl22D0357A3-8CCF-442A-B0C1-35DFE2A881CBaa1AB022164-0C61-4321-8E36-3A5A3E35EA4Ecc3314C03C5-D79C-4EA0-82CD-25337935E0A0bb2BD9C0338-8933-4423-81DB-A0AEE05D03F0aa240718F52-F815-4C61-968E-2BAEE248CA81cc416088343-FB69-4917-9677-DAEA3ABB8A09bb57EE903A5-2CF9-4BB5-8B66-33E6AEFEF3FDaa30AF9501D-C4A9-4CC0-B753-CABDDB90B783cc5822DAA22-786C-4B67-827E-A5BB10B60845bb42.执行SQL：select *,ROW_NUMBER() OVER (PARTITION BY kysl ORDER BY KYSL2 )ws from [Table_1] 结果如下：idkysl1kysl2n2D0357A3-8CCF-442A-B0C1-35DFE2A881CBaa11BD9C0338-8933-4423-81DB-A0AEE05D03F0aa227EE903A5-2CF9-4BB5-8B66-33E6AEFEF3FDaa33314C03C5-D79C-4EA0-82CD-25337935E0A0bb21822DAA22-786C-4B67-827E-A5BB10B60845bb4216088343-FB69-4917-9677-DAEA3ABB8A09bb53AB022164-0C61-4321-8E36-3A5A3E35EA4Ecc3140718F52-F815-4C61-968E-2BAEE248CA81cc420AF9501D-C4A9-4CC0-B753-CABDDB90B783cc53{/alert}
- 2022年04月20日
- 239 阅读
- 0 评论
- 2 点赞
2021-12-23
1. MySQL的binlog恢复（Windows下）初识binlog　　MySQL的binlog就是大家经常所说的Binary Log，即bin-log，是MySQL存储对数据库改动的二进制文件，也就是记录了所有DDL与DML（select除外）语句. 　　首先查看 mysqlbinlog 是否已经打开:-- 查看MySQL的日志是否打开(log_bin项) mysql> SHOW VARIABLES LIKE 'log_bin%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin | ON | | log_bin_trust_function_creators | OFF | +---------------------------------+-------+ 2 rows in set (0.01 sec) -- 查看日志过期时间(过期之后日志会自动删除) mysql> show variables like '%expire_logs_days%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | expire_logs_days | 0 | +------------------+-------+ 1 row in set (0.00 sec)　　如果 log_bin 是关闭状态的话,我们需要修改mysql配置文件.比如我的mysql安装位置是:D:\mysql\bin\mysql.exe. 所以修改D:\mysql\my.ini 文件,添加以下配置:# 如果 log_bin=mysql-bin 则日志文件会默认存放在 C:\ProgramData\MySQL\MySQL Server 5.5\data\ log-bin=D:/mysql/data/mysql-bin binlog_format=mixed # 设置日志永不过期(一般设置为10 即十天之后过期删除) expire_logs_days = 0　　修改之后执行 services.msc 打开服务,重启mysql服务生效.数据恢复测试1.准备表person,插入基本数据:mysql> CREATE TABLE `person` ( -> `id` varchar(255) , -> `name` varchar(255) , -> `age` int(10) -> ); Query OK, 0 rows affected (0.02 sec) mysql> select * from person; Empty set (0.01 sec) mysql> INSERT INTO person VALUES (uuid(),'张三',18);INSERT INTO person VALUES (uuid(),'王五',30); Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) mysql> select * from person; +--------------------------------------+------+------+ | id | name | age | +--------------------------------------+------+------+ | bcaa790d-6499-11ec-86bc-8c16453d981b | 张三 | 18 | | bcaae031-6499-11ec-86bc-8c16453d981b | 王五 | 30 | +--------------------------------------+------+------+ 2 rows in set (0.00 sec)2.模拟误删除person表:mysql> delete from person; Query OK, 2 rows affected (0.00 sec)3.尝试恢复表数据:{card-default label="3.1 查看binlog日志位置信息" width="100%"}查看当前日志名称:mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 1856 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)查看当前日志节点:mysql> show binlog events in 'mysql-bin.000001'; +------------------+------+-------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+-------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.28-log, Binlog ver: 4 | | mysql-bin.000001 | 107 | Query | 1 | 263 | use `test`; CREATE TABLE `person` ( `id` varchar(255) , `name` varchar(255) , `age` int(10) ) | | mysql-bin.000001 | 263 | Query | 1 | 371 | use `test`; DROP TABLE `person` /* generated by server */ | | mysql-bin.000001 | 371 | Query | 1 | 522 | use `test`; CREATE TABLE `person` ( `id` varchar(255) , `name` varchar(255) , `age` int(10) ) | | mysql-bin.000001 | 522 | Query | 1 | 768 | use `test`; ALTER TABLE `person` MODIFY COLUMN `id` varchar(255) CHARACTER SET utf8 NULL DEFAULT NULL FIRST , MODIFY COLUMN `name` varchar(255) CHARACTER SET utf8 NULL DEFAULT NULL AFTER `id` | | mysql-bin.000001 | 768 | Query | 1 | 836 | BEGIN | | mysql-bin.000001 | 836 | Table_map | 1 | 887 | table_id: 36 (test.person) | | mysql-bin.000001 | 887 | Write_rows | 1 | 967 | table_id: 36 flags: STMT_END_F | | mysql-bin.000001 | 967 | Xid | 1 | 994 | COMMIT /* xid=112 */ | | mysql-bin.000001 | 994 | Query | 1 | 1062 | BEGIN | | mysql-bin.000001 | 1062 | Query | 1 | 1197 | use `test`; -- INSERT INTO person VALUES (uuid(),'张三',18); delete from person | | mysql-bin.000001 | 1197 | Xid | 1 | 1224 | COMMIT /* xid=119 */ | | mysql-bin.000001 | 1224 | Query | 1 | 1292 | BEGIN | | mysql-bin.000001 | 1292 | Table_map | 1 | 1343 | table_id: 36 (test.person) | | mysql-bin.000001 | 1343 | Write_rows | 1 | 1425 | table_id: 36 flags: STMT_END_F | | mysql-bin.000001 | 1425 | Xid | 1 | 1452 | COMMIT /* xid=123 */ | | mysql-bin.000001 | 1452 | Query | 1 | 1520 | BEGIN | | mysql-bin.000001 | 1520 | Table_map | 1 | 1571 | table_id: 36 (test.person) | | mysql-bin.000001 | 1571 | Write_rows | 1 | 1653 | table_id: 36 flags: STMT_END_F | | mysql-bin.000001 | 1653 | Xid | 1 | 1680 | COMMIT /* xid=124 */ | | mysql-bin.000001 | 1680 | Query | 1 | 1748 | BEGIN | | mysql-bin.000001 | 1748 | Query | 1 | 1829 | use `test`; delete from person | | mysql-bin.000001 | 1829 | Xid | 1 | 1856 | COMMIT /* xid=152 */ | +------------------+------+-------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 23 rows in set (0.00 sec)　　可以看待在1680节点开始到1856节点结束是处理最后的删除表操作.所以我们恢复数据的时候,只需要恢复数据到1680节点之前就行 {/card-default} {card-default label="3.2 通过binlog恢复数据" width="100%"}　　打开命令窗,切换路径到与安装的mysql执行文件同目录。比如我的mysql安装路径是:D:\mysql\bin\mysql.exe，所以目录切换到*D:\mysql\bin* 。　　一般有两种恢复操作：一种是将从日志文件中抽取到的sql存为件，然后执行.sql文件;另一种是直接将抽取到的sql执行.恢复命令格式如下：# 抽取sql存为文件 mysqlbinlog --start-position=开始节点号 --stop-posion=结束节点号日志文件名称(保险起见使用使用绝对路径) > 存储位置名称 # 执行.sql文件 source 路径名称.sql # 直接执行抽取到的sql mysqlbinlog --start-position=开始节点号 --stop-posion=结束节点号日志文件名称(保险起见使用使用绝对路径) | mysql -uroot -p123456 通过3.1的分析,可以得知需要执行的起始节点位置和日志路径.所以执行命令如下:# 第一种情况: 先存.sql 然后执行(两条命令分开执行) mysqlbinlog --start-position=4 --stop-position=1680 D:\mysql\data\mysql-bin.000001 > d:\\test1.sql source d://test1.sql # 第二种情况: 直接执行 mysqlbinlog --start-position=4 --stop-position=1680 D:\mysql\data\mysql-bin.000001 | mysql -uroot -p1216{/card-default} {callout color="#f0ad4e"}执行完毕之后可以看到,数据已经恢复到以前两条数据没有删除的状态.{/callout}　　　　
- 2021年12月23日
- 256 阅读
- 0 评论
- 1 点赞

天祈

66 文章数

14 评论量

人生倒计时

标签云

舔狗日记