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}

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}