SqlServer学习笔记

SqlServer教程第四版的一些学习内容+b站视频课

sql server视频教程

五.查询

16.分页Sql语句

1.分页语句1（主键id是标识列 必须是自增的）

必须带有主键id 且主键id是标识列 必须是自增的 依赖于id

  declare @pagesize int--每一页的数量
  select @pagesize=5
  declare @pageindex int --查询第几页
  select @pageindex=2
--此处的dept_id是唯一标识
  select top (@pagesize) * from jsc_countxgf where dept_id not in 
  (
  --(@pagesize)* (@pageindex-1)为当前页大小
  select  top ((@pagesize)* (@pageindex-1)) dept_id from jsc_countxgf order by dept_id
  )
  order by dept_id
  前台传过来的应该是pagesize和pageindex,实现分页功能

2.分页语句2（id必须是int类型的）

id必须是int类型的 依赖于id

  select top (@pagesize) * from jsc_countxgf where dept_id >
  (  -- 此处仍是跨过多少条
     -- 如果为空 直接返回0
    select isnull(max(dept_id),0)
    from(      
       select  top ((@pagesize)* (@pageindex-1)) dept_id from jsc_countxgf order by dept_id
         ) a
   )
  order by dept_id

3.分页语句3（id没有自增 且不是int类型）

如果id没有自增 且不是int类型 原理是通过select row_number() over (order by dept_id ) as rownumber 生成一个带有自增的int属性的id列

select top (@pagesize) * from 
  (
  --这一行的作用是生成一列标识
  select row_number() over (order by dept_id ) as rownumber,* from jsc_countxgf
  )
  A   --A是一个临时表
  where rownumber >((@pagesize)* (@pageindex-1))

4.分页语句4（sql server 2012以上版本可用）（使用offset偏移函数）

 select * from  [xgfdb].[dbo].[jsc_countxgf] order by dept_id
  offset ((@pagesize)* (@pageindex-1)) --间隔多少条开始
  rows fetch next(@pagesize)  --获取多少条
  rows only

17.行转列和列转行

1.行转列

 --一条记录去描述一个人的信息 行转列
  select dept_id,
    sum( case when projectdate='2023-11-01' then sumPersons else 0 end )as '近1个月',
    sum( case when projectdate='2023-10-01' then sumPersons else 0 end) as '近2个月',
    sum( case when projectdate='2023-09-01' then sumPersons else 0 end )as '近3个月',
    sum( case when projectdate='2023-08-01' then sumPersons else 0 end )as '近4个月',
    sum( case when projectdate='2023-07-01' then sumPersons else 0 end )as '近5个月',
    sum( case when projectdate='2023-06-01' then sumPersons else 0 end )as '近6个月'
  from [xgfdb].[dbo].[jsc_qg_temperson] group by dept_id 

2.列转行

 select dept_id,dept_name ,options ='总人数',score=personsum  FROM [xgfdb].[dbo].[jsc_countqg]
  union all
  select dept_id,dept_name ,options ='出勤人数',score=qg_onduty   FROM [xgfdb].[dbo].[jsc_countqg]
  union all
  select dept_id,dept_name , options ='出勤率',score=attendence   FROM [xgfdb].[dbo].[jsc_countqg]

21.递归查询

可以通过一个参数，可以查询出无限层级下的数据，带有无限的层级查询

就是去查询层级结构的数据存储，查询数据树形结构。

with Con(dept_id,dept_name,dept_parent_id,le) as 
   (
    select dept_id,dept_name,dept_parent_id,le=1 from [xgfdb].[dbo].[bm_deptxgf] where dept_id =1102
    union all
    select a.dept_id,a.dept_name ,a.dept_parent_id,le=le+1 from [xgfdb].[dbo].[bm_deptxgf] a
    join Con on a.dept_parent_id=Con.dept_id
   )
    select dept_id,dept_name,dept_parent_id,le from Con

六.视图

七.同义词

八.数据类型

九.T-SQL编程

1.变量 分支结构 循环

1.变量

declare @text varchar(20)   --定义变量（变量名开始必须是@）

赋值方法1：

set @text='啦啦啦啦啦'   --必须使用set 去定义一下
select @text

赋值方法2：

select @text ='啦啦啦啦啦'

输出语句

 print @text

2.分支结构

使用begin end begin end的作用相当于是{}

 declare @a int;
  declare @b int;
  select @a=4;
  select @b=6;

  if @a>@b
  begin 
    print'a的值大于b的值'
  end 
  else 
  begin
   print'a的值小于b的值'
     end

3.循环语句和循环退出

break用于退出本层循环，当程序中有多层循环嵌套时，使用break只能退出所在的这一层循环

continue 用于结束本次循环，重新转到下一次循环条件的判断

declare @aa int;
  select @aa=1;--初始条件
  while @aa<10 --循环条件
  begin
    if @aa=5
     begin
	   break;  --退出本层循环
	   continue;--结束本次循环
     end 
	else
     begin
	   select @aa as 结果,@aa; --循环体
  select @aa=@aa+1; --状态改变
	 end 
  
  end

当循环条件 @aa < 10 成立时，进入循环体执行查询语句。在循环体中，如果 @aa 的值等于 5，则执行 continue 语句。

使用 continue 语句会跳过当前循环的剩余部分，并继续下一次循环的执行。在你的代码中，如果 @aa 的值等于 5，那么查询语句后面的状态改变语句 select @aa=@aa+1; 将不会执行，但是循环条件检查 @aa<10 仍然成立，所以会继续执行下一次循环的查询语句。

因此，如果 @aa 的值等于 5，会一直执行查询语句，直到 @aa 的值增加到不满足循环条件为止。如果你希望在 @aa 的值等于 5 时直接跳出循环，可以使用 break 语句代替 continue 语句。

4.自定义错误处理语句

declare @aa int;
  select @aa=1;--初始条件
  while @aa<10 --循环条件
  begin
    begin try
          --sql 如果发生异常
    end try
          
    begin catch
          --sql 就在这里处理异常
    end  catch
  select @aa as 结果; --循环体
  print '结果为：'+convert(varchar(10),@aa)
  select @aa=@aa+1; --状态改变
  end

5.等待语句

DELAY 等待时间 ：指的是运行批处理，存储过程和事务必须等待的时间，最长可达24小时

TIME 执行时间 ：指的是waitfor语句完成的时间

BEGIN 

 WAITFOR  TIME '8:00'

 SELECT * FROM xsb

END

2.全局变量+运算符

1.全局变量

全局可用，系统预定义，用户不可修改，不可定义 全局变量以@@开头

一些常用的全局变量：

select @@connections  --总共的连接次数(无论是成功还是失败)
select @@CPU_BUSY  --自上次启动后的工作时间
select @@CURSOR_ROWS  --返回连接的数据库上打开的上一个游标中当前限定行的数目，为了提高性能
@@ERROR：返回最近一条执行的 Transact-SQL 语句的错误代码。如果没有错误发生，则返回 0。
@@ROWCOUNT：返回最后一条执行的 Transact-SQL 语句所影响的行数。
@@IDENTITY：返回最后插入到标识列中的值。适用于使用自增标识列进行插入操作的情况。
@@VERSION：返回当前 SQL Server 实例的版本信息。
@@SERVERNAME：返回当前 SQL Server 实例的名称。
@@LANGUAGE：返回当前会话使用的语言设置。
@@SPID：返回当前用户会话的进程 ID。
@@MAX_CONNECTIONS：返回 SQL Server 实例支持的最大并发连接数。

2.运算符

1.算术运算符

加号（+）除了做数字运算 还可以做字符串拼接

%(取模/取余) 返回一个除法的整数余数 注意：两边必须是整数数据

declare @num1 int;
declare @num2 int;
declare @str1 varchar(50);
set @num1=12;
set @num2=10;
set @str1='lalalalala';
select @num1+@num2;
select @num1-@num2;
select @num1*@num2;
select @num1/@num2;
select @num1%@num2;
--数字和字符串相加需要类型转换
select convert(varchar(50),@num1)+@str1;

2.比较运算符

= < <= > >= < >(不等于) != !< !>

3.逻辑运算符

AND(且) OR(或) NOT(非) BETWEEN (范围) IN LIKE ANY/SOME(任何一个满足条件即可)

ALL(所有都要满足条件) EXISTS（如果子查询中包含一些行）

4.连接运算符

substring用法

declare @str1 varchar(10)
declare  @str2 varchar(10)
set @str1='lalalalal'
set @str2='hahahah'
select substring (@str1,0,3) --从0开始截取三个字符串

3.无条件转移语句：GOTO

无条件转移语句将执行流程转移到标号指定的位置 GOTO 标号

“标号”是指向的语句标号，标号必须符合标识符规则 规则 => 标号：语句

十 .存储过程

存储过程的概念：

存储过程（procedure）是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行

1.自定义存储过程+分页存储过程使用

DROP PROCEDURE IF EXISTS [dao].[SP_CustomPager];  --如果已经存在了这个存储过程，就进行删除
GO  --删除之后再进行创建
CREATE PROCEDURE [dbo].[SP_CustomPager]
  @TableName varchar(50),           --表名
  @ReFieldsStr varchar(200)='*',    --字段名（全部字段为*）
  @OrderString varchar(200),        --排序字段（必须！支持多字段不用加oreder by）
  @WhereString varchar(500)=N'',    --条件语句（不用加where）
  @PageSize INT,                    --每页多少条记录
  @PageIndex INT=1,                 --指定当前为第几页
  @TotalRecord INT OUTPUT           --返回总记录数
AS
BEGIN
      --处理开始点和结束点
	  DECLARE @StartRecord INT;
	  DECLARE @EndRecord INT;
	  DECLARE @TotalCountSql nvarchar(500);
	  DECLARE @SqlString nvarchar(2000);
	  SET @StartRecord=(@PageIndex-1)*@PageSize +1    --起始记录
	  SET @EndRecord=@StartRecord +@PageSize-1        --结尾记录
	  SET @TotalCountSql=N' select @TotalRecord =count(*)from '+ @TableName;--总记录条数
	  SET @SqlString=N' (select row_number () over (order by '+@OrderString +') AS rowId,'+@ReFieldsStr +'from'+@TableName;  --查询语句
	  IF (@WhereString!='' OR @WhereString !=null)
	  BEGIN 
	    SET @TotalCountSql=@TotalCountSql+'where'+@WhereString;
		SET @SqlString=@SqlString+'where'+@WhereString;
	  END

	  --第一次执行得到
	  EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord OUTPUT ;--返回总记录数
	
	  --执行主语句
	  SET @SqlString='SELECT * FROM '+@SqlString +') as t where rowId between '+ltrim(str(@StartRecord))+'and'+ltrim(str(@EndRecord));
	   EXEC (@SqlString)

END 


--执行存储过程
declare @total int;
exec sp_CustomPager 'bm_deptxgf','*','id','',20,1,@total  output
select @total

十一.函数

1.函数和存储过程的区别

函数和存储过程的共同点：都是预编译优化后存储在磁盘中，所以效率比t-sql效率高

不同：1.存储过程可以创建或者访问临时表，而函数不可以

2.函数不可以修改表中的数据，或者调用产生副作用的函数（比如rand,newid,getdate）

3.函数可以作为select或者from或where子句的一部分，而存储过程不可以

2.系统函数

1.字符串函数

1.LEN：返回一个字符串表达式的长度。

 SELECT LEN('Hello World!') -- 返回 12

2.LEFT：返回一个字符串表达式的左侧指定数量的字符。

 SELECT LEFT('Hello World!', 5) -- 返回 'Hello'

3.RIGHT：返回一个字符串表达式的右侧指定数量的字符。

 SELECT RIGHT('Hello World!', 6) -- 返回 'World!'

4.SUBSTRING：返回一个字符串表达式的指定子字符串。

 SELECT SUBSTRING('Hello World!', 7, 5) -- 返回 'World'

5.REPLACE：替换字符串中的某些字符或字符串。

 SELECT REPLACE('Hello World!', 'World', 'Universe') -- 返回 'Hello Universe!'

6.CONCAT：将两个或多个字符串合并为一个字符串。

 SELECT CONCAT('Hello', ' ', 'World') -- 返回 'Hello World'

7.LOWER：将字符串中的所有字符转换为小写。

 SELECT LOWER('Hello World!') -- 返回 'hello world!'

8.UPPER：将字符串中的所有字符转换为大写。

 SELECT UPPER('Hello World!') -- 返回 'HELLO WORLD!'

9.LTRIM：从字符串的开头删除空格。

 SELECT LTRIM('   Hello World!') -- 返回 'Hello World!'

10.RTRIM：从字符串的结尾删除空格。

 SELECT RTRIM('Hello World!   ') -- 返回 'Hello World!'

2.数学函数

1. ABS：返回一个数的绝对值。

2. CEILING：返回大于或等于指定数的最小整数。（向上取整）

3. FLOOR：返回小于或等于指定数的最大整数。（向下取整）

4. ROUND：将一个数四舍五入为指定的长度或精度。

5. SQRT：返回一个数的平方根。

6. POWER：返回一个数的指定次幂。

7. rand:随机数产生器

8. sign:根据正数，0，负数返回+1，0，-1

3.日期时间函数

1. GETDATE()：返回当前的系统日期和时间。

  SELECT GETDATE() -- 返回当前日期和时间

2.DATEADD()：向日期添加指定的时间间隔。

  SELECT DATEADD(DAY, 7, '2023-12-01') -- 返回 '2023-12-08'

3.DATEDIFF()：返回两个日期之间的时间间隔。

  SELECT DATEDIFF(DAY, '2023-12-01', '2023-12-08') -- 返回 7

4.DATEPART()：返回日期或时间部分的整数值。

  SELECT DATEPART(YEAR, '2023-12-08') -- 返回 2023

5.YEAR()：返回日期的年份部分。

  SELECT YEAR('2023-12-08') -- 返回 2023

6.MONTH()：返回日期的月份部分。

  SELECT MONTH('2023-12-08') -- 返回 12

7.DAY()：返回日期的天数部分。

  SELECT DAY('2023-12-08') -- 返回 8

8.CONVERT()：将一个日期时间类型转换为另一个类型。

  SELECT CONVERT(VARCHAR, GETDATE(), 106) -- 返回 '08 Dec 2023'

9.DATEFROMPARTS()：根据指定的年、月和日创建一个日期。

  SELECT DATEFROMPARTS(2023, 12, 8) -- 返回 '2023-12-08'

10.EOMONTH()：返回指定日期的月末日期。

  SELECT EOMONTH('2023-12-08') -- 返回 '2023-12-31'

11.DATENAME():返回名称如June

写法和意义：

yy 年份 qq刻(1-4) mm月 dy日(1-366) wk周(1-54) dw周几（1-7）

4.系统其他函数

suser_name()用户登录名 user_name()用户在数据库中的名字

user用户在数据库中的名字 show_role()对当前用户起作用的规则

db_name()数据库名字 object_name(obj_id)数据库对象名

col_name(obj_id,co1_id) 列名 col_1ength(objname,colname)列长度

valid_name(char_expr)是否是有效标识符

5.类型转换函数

1. convert(数据[(长度)]，表达式[，样式]) 将一种数据类型的表达式显式转换为另一种数据类型的表达式; 长度:如果数据类型允许设置长度，可以设置长度，例如 varchar(10);样式:用于将日期类型数据转换为字符数据类型的日期格式的样式。

2. cast (表达式AS数据类型[(长度)]) 将一种数据类型的表达式显式转换为另一种数据类型的表达式。例如:select cast(123 as nvarchar)返回123 select N'年龄:'+ cast(23 as nvarchar)返回年龄:23

6.排名函数

row_number 无参数：为结果集内每一行进行编号，从一开始后边一次加1，常用于产生编号

3.标量值函数

返回单个数据类型的值（除了BLOB.游标，时间戳），比如return int as... return @var

1.创建标量值函数

CREATE FUNCTION dbo.MySqlMethod
(
	@parameter1 int,
	@parameter2 int
)
RETURNS int
AS
BEGIN
	return @parameter1+@parameter2;

END
GO

2.调用标量值函数

select dbo.MySqlMethod(123,555)   --可以用作是查询内容
select .. from .. where dbo.MySqlMethod(123,555)>id   --也可以用作where中的查询条件

3.示例

use xgfdb
go
--判断是否存在需要创建的函数 如果有的话先进行删除
if exists (select * from sys.objects where name ='NewMethod')
drop function 'NewMethod';
go
--创建用户自定义函数
create function dbo.NewMethod
(
  @parameter_name int,
  @@parameter_name1 varchar(10)[=default ])
  return int
  as 
  begin 

  函数体
  return 标量表达式式
  end 

4.表值函数

返回一张表 比如return table as 或者return select...(实际上也是一张表)

1.内嵌表值函数

--内嵌函数定义
CREATE FUNCTION dbo.CompanyTableList(@companyId  int ) 
RETURNS TABLE --returns字句只包含关键字table  返回一个表
AS
RETURN 
(
	select * from bm_deptxgf where dept_id= @companyId 
)
GO
--内嵌函数调用
select * from dbo.CompanyTableList(12)

2.多语句表值函数

内嵌表值函数没有函数主体，而多语句表值函数在BEGIN...END中定义的函数主体包含的T-SQL语句，这些语句可以生成行并将行插入至表内，最后返回表

--定义多表值函数
use xgfdb
go 
create function chuqin_table (@id varchar(50)) returns 
@chuqin TABLE
(
部门名称 varchar(50),
出勤人数 int
)
AS
BEGIN 
INSERT @chuqin
SELECT A.dept_name,B.xgf_onduty_persons
from bm_deptxgf as A inner join xgf_timecard_count as B on(A.dept_id=b.xgf_dept_id)
where A.dept_id=@id and B.getdata_date like '2023-10%'
RETURN 
END 
--调用多表值函数
select * from dbo.chuqin_table('1102')

3.用户自定义函数的删除

1. 可以通过控制面板进行删除

2. 使用DROP FUNCTION 进行删除

sql server2012教材教程

第四章 数据库的查询，视图和游标

4.1一些特殊的操作和方法

1.日期转化为工作日

DATEPART(dw, getdata_date) BETWEEN 2 AND 6;

2.取出日期类型的数据

YEAR函数用于取出日期类型数据的年份

3.全文索引的一些知识

派生项：

搜索包含原词的派生词，派生词指的是原词的名词单，复数形式或者动词的各种时态等

INFLECTIONAL选项是对指定的简单字词使用与语言相关的词干分析器

THESAURUS选项表示指定使用对应于列全文语言或指定的查询语言的同义词库

加权项：

指明本语句是加权搜索，即查询的数据与给定的权重进行加权匹配 weight(加权值 范围是0~1)

4.2 数据库的查询 select

4.2.1 选择查询结果输出列

4.替换查询结果中的数据

要替换查询结果中的数据 则要使用查询中case表达式

 =  case      when 条件1   then 表达式1

​             when 条件2   then 表达式2

​             else 表达式

  end

select xgf_dept_id ,xgf_onduty = case when xgf_onduty_percent<0.8 and xgf_onduty_percent>0.6 then '合格' when xgf_onduty_percent >0.8 then '良好' else '不及格' end from xgf_timecard_count where getdata_date  = '2023-10-23'

5.计算列值

计算列值使用算术运算符 + - * / （除） %(取余)

 select xgf_dept_id,出勤率 = xgf_onduty_percent *100 from xgf_timecard_count where getdata_date  = '2023-10-23'

6.消除结果集中的重复行

distinct的含义是对结果集中的重复行只选择一个 ，以保证行的唯一性

all会保留结果集的所有行，当省去all和distinct时，默认值为all

select distinct xgf_dept_id from xgf_timecard_count

7.限制结果集返回行数

top 数字 （选择前多少行 ） top 数字 percent （选择前%行 ）

SELECT TOP 1000 [id]                   选择前一千行
      ,[getdata_date]
      ,[xgf_dept_id]
  FROM [xgfdb].[dbo].[xgf_timecard_count]

SELECT TOP 5 percent [id]             选择前5%行
      ,[getdata_date]
      ,[xgf_dept_id]
  FROM [xgfdb].[dbo].[xgf_timecard_count]

9.聚合函数

(1)sum和avg(这两个会自动忽略null值)

SELECT CAST(CAST(SUM(xgf_onduty_persons) AS FLOAT) / SUM(xgf_sum_persons) AS DECIMAL(14, 4)) AS avgpercent 
FROM xgf_timecard_count 
WHERE xgf_dept_id ='1136' AND getdata_date LIKE '2023-10%' AND DATEPART(dw, getdata_date) BETWEEN 2 AND 6;

 select cast( avg(xgf_onduty_percent)as  DECIMAL(14, 4) )  as avgpercent  from xgf_timecard_count WHERE xgf_dept_id ='1136' AND getdata_date LIKE '2023-10%' AND DATEPART(dw, getdata_date) BETWEEN 2 AND 6;

(2)max和min

求最大值和最小值， 表达式可以是常量，列，函数或者表达式，数据类型可以是数字，字符和时间日期类

select min (getdata_date) as 第一天 from xgf_timecard_count
select max (xgf_onduty_percent) as 最大出勤率 from xgf_timecard_count

(3)count

用于统计组中满足条件的行数和总行数

    select count (1) as comment from bm_roles 

1. COUNT(1): 这种写法会对指定的列中的非空值进行计数。在这里，1 是一个常量值，它并不会对表中的实际数据进行操作，而是简单地对每一行的这个常量进行计数。

2. COUNT(*): 这种写法会对符合条件的所有行进行计数，包括NULL值。通常情况下，数据库优化器会对 COUNT(*) 进行特殊处理，因为它不需要考虑列值是否为 NULL，所以在某些数据库系统中，COUNT(*) 的性能可能会比 COUNT(1) 稍微好一些。

3. 在实际使用中，一般来说 COUNT(*) 和 COUNT(1) 的效果是一样的，因为大部分情况下我们都是关心行的数量而不是特定列的取值情况。如果你的数据库系统对这两者有特殊的优化处理，那么可能会有一些微小的性能差别。

   综上所述，COUNT(1) 和 COUNT(*) 在功能上是等价的，但在一些数据库系统中可能会有轻微的性能差异。

4.2.2 选择查询条件 where

1.表达式比较

= < <= > >= < >(不等于) != !< !>

2.模式匹配（模糊查询） like (not like)

%代表倒数 _代表单个字符 []指定范围 [^]注定不属于范围

3.范围比较

between ...and ...

in(.. , .. , .. )

4.空值比较

is (not) null

5.子查询

1.in子查询

in子查询用于进行一个给定值是否在子查询结果集中的判断 in和not in子查询只能返回一列数据

select sumPersons from jsc_qg_temperson where dept_id in (select dept_id from jsc_qg_temperson where dept_id ='GTGFLT' )

2.比较子查询

比较子查询：使得表达式的值与子查询的结果进行比较运算

表达式 {比较运算符} {all|some|any} {子查询}

all指定表达式要与子查询结果集中的每个值都进行比较 any|some只要有与某个值满足比较的关系 即可返回true 否则返回true

3.exist子查询

select dept_name from BmDepts1 where exists (select * from jsc_countqg where dept_id =BmDepts1.dept_id)

可以起到连接多个表进行查询的功能

6.contains函数

1.创建全文索引 使用contains函数的需要创建全文索引

2.contains用法

 select * from sys_users where contains(*,'李')

3.freetext用法

SELECT * FROM sys_users WHERE FREETEXT(*, '李', LANGUAGE 'English');

4.2.3指定查询对象

1.表或者视图名

可以查多个表或者视图

  select dept_allname from bm_deptxgf,BmDepts1 where bm_deptxgf.dept_serverowner =BmDepts1.dept_id and dept_level ='1'

2.导出表（子查询）

 select dept_name from (select * from bm_deptxgf where dept_level = '2') as deptfirst where dept_sign like '%1102%'

子查询中要为列指定别名，则必须要为所有列指定别名

 select deptfirst.dept_nameszx from (select * from bm_deptxgf where dept_level = '2' and dept_sign like '%1102%') as deptfirst(dept_id,dept_nameszx,dept_level,dept_parent_id,dept_sign,dept_idonecard,dept_isstop,dept_comment,
dept_ismanaged,dept_serverowner,dept_location,dept_shortname,dept_seq,dept_servicekind,updatetime,insertdate)

3.行集函数

行集函数通常返回一个表或者视图

1.OPENROWSET函数

可以用于插入图片文件，文本文件，Word文件，Excel文件等内容

SELECT TOP (1000) [testid]
      ,[blobname]
      ,[blobdata]
  FROM [xgfdb].[dbo].[test]
  insert into test (blobname,blobdata) select 'picture',BulkColumn from openrowset (Bulk 'E:\图片\lalala.jpg',single_blob) as BLOB

最终插入的是二进制流数据

4.表值函数

5.行转列表和列转行表

1.行转列表

SELECT dept_name, dept_level, [检修], [维护], [管理]
FROM (
    SELECT dept_name, dept_level, dept_servicekind, dept_id
    FROM bm_deptxgf
    WHERE dept_servicekind IN ('检修', '维护', '管理')
      AND dept_level = '1'
) AS subquery
PIVOT
(
  COUNT(dept_id)   --此处使用聚合函数
  FOR dept_servicekind IN ([检修], [维护], [管理])
) AS pvt;

​    

2.列转行表

格式相似 但是不使用聚合函数

SELECT dept_id, dept_level, 选项 ,名称
FROM (
    SELECT *
    FROM bm_deptxgf
    WHERE dept_servicekind IN ('检修', '维护', '管理')
      AND dept_level = '1'
) AS subquery
UNPIVOT
(
  名称   --此处不用聚合函数
  FOR 选项 IN (dept_name,dept_shortname)
) unpvt;   --此处没有as

4.2.4连接：=/JOIN

1.连接谓词

比较符号可以是= < <= > >= < >(不等于) != !< !>

=时 就是等值连接

1.自然连接

2.多表连接

需要用多个表的数据时，

 select bm_deptxgf.dept_id, bm_deptxgf.dept_name,xgf_onduty_percent
 from bm_deptxgf,xgf_timecard_count
 where bm_deptxgf.dept_id = xgf_timecard_count.xgf_dept_id
and bm_deptxgf.dept_level = '1' AND dept_servicekind IN ( '维护')
AND xgf_timecard_count.getdata_date = '2023-11-05'

注意与子查询的区别：子查询的select语句结果只能来自于一个表，连接可以合并两个表的数据

2.以join为关键字指定的连接

1.内连接 join on

内连接使用inner关键字 内连接是系统默认的 可以省略inner关键字 使用内连接之后仍可使用where字句指定条件

  select bm_deptxgf.dept_id, bm_deptxgf.dept_name,xgf_onduty_percent
 from bm_deptxgf join xgf_timecard_count
 on bm_deptxgf.dept_id = xgf_timecard_count.xgf_dept_id
where bm_deptxgf.dept_level = '1' AND dept_servicekind IN ( '维护')
AND xgf_timecard_count.getdata_date = '2023-11-05'

2.外连接 （其中的outer关键字均可省略）

指定outer关键字的外连接 外连接的结果表不但包含满足连接条件的行 还包括相应表中的所有行

左外连接：(LEFT OUTER JOIN) :结果表中除了包括满足连接条件的行外，还包括左表的所有行

右外连接：(RIGHT OUTER JOIN) :结果表中除了包括满足连接条件的行外，还包括右表的所有行

完全外连接：(FULL OUTER JOIN) :结果表中除了包括满足连接条件的行外，还包括两个表的所有行

3.交叉连接

实际上是将两个表的结果进行笛卡儿积运算，结果表是由第一个表的每一行与第二个表的每一行拼接后形成的表，因此结果表的行数等于两个表的函数之积

4.2.5 指定查询结果分组方法：GROUP BY

GROUP BY主要用于根据字段进行分组

1.rollup:

生成简单的group by聚合行，小计行或者超聚合行，还生成一个总计行

 select dept_servicekind,dept_ismanaged,count(*) as '数量' from bm_deptxgf group by rollup (dept_servicekind,dept_ismanaged)

2.cube

生成简单的group by聚合行，rollup超聚合行和交叉表格行

 select dept_servicekind,dept_ismanaged,count(*) as '数量' from bm_deptxgf group by cube (dept_servicekind,dept_ismanaged)

3.grouping sets

在一个查询中指定数据的多个分组，仅聚合指定组，而不聚合cube或rollup生成的整数组合 （即只从指定的字段聚合 不进行交叉）

	  select dept_servicekind,dept_ismanaged,count(*) as '数量' from bm_deptxgf group by grouping sets (dept_servicekind,dept_ismanaged)

4.2.6 指定查询结果分组后的筛选条件 ：HAVING

having字句中可以使用聚合函数，而where子句中不可以

  select xgf_dept_id ,avg(xgf_onduty_percent) as 出勤率 from xgf_timecard_count where getdata_date='2023-06-06' group by xgf_dept_id having avg(xgf_onduty_percent)>0.5

group by用于对where 的结果进行分组，having则是对group by以后的分组数据进行过滤

4.2.7指定查询结果排序顺序 order by

1.对查询结果排序输出

DESC表示降序排序 ASC表示升序排序（系统默认值为ASC）

2.对排序结果附加汇总 COMPUTE（2005版本后已弃用）

compute字句产生附加的汇总行，其列标题是系统自定的

4.2.8其它 into union except intersect cte

1.into

select .. .. into [新表] from  .. where 

2.union:将多个select查询的结果合并成一个结果集

union all :其实就是将后边的结果追加到前边的执行结果（全部展示出来） (不会自动去重)

union:会自动的去重

规则（1）所有查询中的列和列数的顺序必然相同

（2）数据类型必须兼容（否则会转化失败）

  select * from jsc_countqg union all select * from jsc_countxgf 

3.except和intersect

用于比较两个查询的结果，返回非重复值

except从关键字左边的查询中返回右边查询没有找到的所有非重复值

intersect返回关键字左右两个查询都返回的所有非重复值

4.cte 公用表表达式 （临时命名的结果集）

在select语句的最前面可以使用一条with语句来指定临时的结果集

在select delete update create view 语句中都可以建立一个cte cte下方的select语句可以直接查询cte中的数据

4.3视图

4.3.2创建视图 CRETAE VIEW

2.通过命令创建视图

T-sql中用于创建视图的语句是CREATE VIEW语句

1.语句主体

（1）架构名

（2）列名

（3）WITH<视图属性>：指出视图的属性

（4）SELECT语句 ：用来创建视图的select语句，可以在select语句中查询多个表或者视图，以表明新创建的视图所参照的表或者试图。但是有以下限制。

1. 定义视图的用户必须有参照的表或者视图的权限
1. 不可使用compute和compute by字句
1. 不能使用order by ，  into字句
1. 不可在临时表或表变量上创建视图

（5）with check options 指出在视图上所进行的修改都要符合select语句所指定的限制条件

2.<视图属性>定义

1. ENCRYPTION:表示在系统表syscomments中存储CREATE VIEW 语句时进行加密

2. SCHEMABINGDING:说明将视图与其所依赖的表或视图结构相关联

3. VIEW_METADATA:当引用视图的浏览模式的元数据时，向dblib,odbc,oledb api返回有关视图的元数据信息，而不返回基本表的元数据信息

3.分区视图

在一台或者多台服务器间水平连接一组成员表中的分区数据，是数据看起来像来自一个表

本地分区视图：所有参与表和视图都位于一个sql server实例上

分布式分区视图：至少有一个参与表位于不同的（远程）服务器上

4.3.3 查询视图 SELECT

和查询基本表一样

4.3.4 更新视图INSERT /UPDATE /DELETE

第八章 备份与恢复

8.2 数据库备份

8.2.1创建备份设备

1.创建永久性备份设备

执行系统存储过程sp_addumpdevice创建命名备份设备，也可以将数据定向到命名管道

--创建永久备份
use xgfdb
go
exec sp_addumpdevice 'disk', --这是设备类型,也就是介质
                             --disk(硬盘文件),tape(磁带)
'myBackups',                           --这是逻辑名
'C:\Users\yangliu\Desktop\备份数据库\myBackup.bak' --这是物理名
--不再需要的时候可以进行删除
exec sp_dropdevice 'myBackups' ,delfile

2.创建临时备份设备

临时的只能用物理名来引用，创建临时备份时，要指定介质类型（一般为磁盘），完整的路径名及文件名称。可以使用T-SQL的BACKUP DATABASE语句来创建临时备份设备

--创建临时备份
use xgfdb
go
backup database xgfdb to disk='C:\Users\yangliu\Desktop\临时备份数据库'

3.使用多个备份设备

SQL Server可以同时向多个备份设备写入数据，进行并行的备份。并行备份将需备份的数据分别备份在多个设备上，这多个备份设备构成了备份集

8.2.2 通过命令方式备份数据库 BACKUP DATABASE

1.备份整个数据库

BACKUP DATABASE xgfdb TO myBackups                  --完全备份
BACKUP DATABASE xgfdb TO myBackups WITH INIT        --覆盖原有的的
BACKUP DATABASE xgfdb TO myBackups WITH NOINIT      --执行追加的完全数据库备份，该设备上原有的备份内容都被保存

第十章 存储过程和触发器

7.1 存储过程

7.1.1存储过程的类型

1.系统存储过程

exec用于调用存储过程

系统存储过程定义在系统数据库master中 以sp做前缀的就是系统的存储过程

1. sp_helpdb：用于列出当前 SQL Server 实例中所有数据库的信息。

2. sp_who：用于列出当前 SQL Server 实例上正在运行的用户进程和相关信息。

3. sp_helpindex：用于列出指定表的所有索引信息。

4. sp_recompile：用于重新编译指定对象的查询计划。

5. sp_addlogin：用于创建新的登录名和密码。

6. sp_adduser：用于将现有登录名添加到数据库中的用户列表中。

7. sp_droplogin：用于删除指定的登录名和与之关联的用户、角色和权限。

8. sp_addrolemember：用于向数据库角色中添加一个或多个成员。

9. sp_helpconstraint：用于列出指定表的所有约束信息。

10. sp_helptrigger：用于列出指定表中所有触发器的信息。

7.1.2存储过程的创建与执行

7.1.3存储过程的修改

7.1.4存储过程的删除

7.1.5通过界面方式来操作存储过程

7.2 触发器

是与表事件相关的特殊的存储过程，它的执行不由程序调用，也不手工启用，而是由事件触发（比如对一个表进行insert delete update时候），触发器常用来加强数据的的完整性约束和业务规则

1.DML触发器（表或者视图）（数据操作语言）

应考虑使用DML触发器的场景

1. 通过数据库中的相关表实现级联更改

2. 防止恶意的insert,update和delete操作，并强制执行check约束定义的限制更为复杂的其它限制

3. 评估数据修改前后表的状态，并根据该差异去采取措施

1.创建insert触发器

在向目标表中插入数据后，会触发该表的insert触发器，系统自动在内存中创建inserted表；如果不满足判断数据会进行回滚，插入的数据操作会失败

--创建触发器 每次插入操作的时候将@str设为trigger is working并显示
use xgfdb
go
create table table1(a int)
go
create trigger table1_insert1  --创建触发器
       on table1               --指定操作对象
	   after insert  
	 --with encrytion          --说明是否采用加密方式
	 --not for replaction      --说明该触发器不用于复制
as                         --触发器的t-sql语句，可以有一条或者多条，指定DML触发器触发后执行的动作
begin
declare @str char(50)
set @str='trigger is working'
print @str
end
--向表中插入数据从而激活触发器
insert into table1 values(10)

2.创建delete触发器

在向目标表中删除数据后，会触发该表的delete触发器，系统自动在内存中创建deleted表，deleted表中存放的是删除的数据

以下代码实现一个delete触发 从jsc_countqg中删除信息后 会触发删除 jsc_countqgcp中的信息

use xgfdb
go 
--检查是否已经存在该触发器 如果存在进行删除
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[jsc_countqg_delete]'))
DROP TRIGGER [dbo].[jsc_countqg_delete];

--批处理中只能存在一个create  所以和上一步的检查应该分开写
use xgfdb
go 
--创建delete触发器
create trigger jsc_countqg_delete  --创建触发器
    on jsc_countqg                 --说明作用对象
	after delete                   --说明触发时间
    as
	begin
	 delete from jsc_countqgcp where dept_id in (select dept_id from deleted)
	end
--修改一行数据
   delete from  jsc_countqg where dept_id ='GTGFLG'

3.创建update触发器

update（）函数返回一个布尔值，指示是否对表或视图的指定列进行了insert或者update操作

在向目标表中 更新数据后，会触发该表的Update触发器，系统自动在内存中创建deleted表和inserted表，delete表存放的是更新前的数据，inserted表存放的是更新的数据

use xgfdb
go 
--创建修改触发器
create trigger jsc_countqg_update  --创建触发器
    on jsc_countqg                 --说明作用对象
	after update                   --说明触发时间
    as
	begin
	 declare @old_name varchar(50),@new_name varchar(50)
	 select @old_name=dept_name from deleted
	 select @new_name=dept_name from inserted
	 update jsc_countqg set dept_name =@new_name where dept_name =@old_name
	end
--修改一行数据
   update jsc_countqg set dept_name='dsc' where dept_name='鼎盛成' 
--并查看触发器的执行结果
   select * from jsc_countqg where dept_name='dsc'  

4.创建INSTEAD OF触发器

1. AFTER触发器是在触发语句执行后触发的，而INSTEAD OF 触发器触发时只执行触发器内部的SQL语句，而不执行激活该触发器的SQL语句，一个表或者一个视图中只能有一个INSTEAD OF触发器

2. INSTEAD OF 触发器的主要作用是使不可更新视图支持更新，如果视图的数据来自于多个基本表，则必须使用INSTED OF 触发器支持引用表中数据的插入 更新和删除操作

3. 示例一：

--创建一个instead of 触发器
USE xgfdb
GO 
CREATE TABLE table2(a int)    --创建一个表 包含一列a 整数型
go 
CREATE TRIGGER table2_insert  --创建一个触发器
ON table2                     --指明作用对象
instead of insert             --说明类型时间
as
print 'instead of trigger is working '
--调用insted of 触发器
insert into table2 values(10)
-- 观察结果 table2中并没有插入数据
select * from table2

1. 示例2 ：在视图上创建触发器 ，当向试图插入数据时分别向基本表中插入，从而实现向视图中插入的效果，未向视图中插入的结果可以设置为NULL

--创建视图
use xgfdb
go
create view qgcq_view
as 
select Bmdepts1.dept_allname,Bmdepts1.dept_id,Bmdepts1.dept_name,attendence
 from BmDepts1,jsc_countqg
 where BmDepts1.dept_id=jsc_countqg.dept_id
 
 
 --创建instead of触发器
 use xgfdb
go
 create trigger insteadtrig
 on qgcq_view
 instead of insert
 as
 begin
 declare @newname varchar(50),@newna varchar(50),
 @newid varchar(50),@newatt numeric(14,4)
 set @newname='新部门拉拉'
 select @newna=dept_name, @newid=dept_id,@newatt=attendence from inserted
 insert into BmDepts1(dept_allname,dept_id,dept_name) values(@newname,@newid,@newna)
 insert into jsc_countqg(dept_id,dept_name,attendence) values(@newid,@newname,@newatt)
 end
 
 --向视图中插入一条新数据
 insert into qgcq_view values('部门啦啦啦','2023','部门',0.2256)
 
 --分别查询视图和两个表 发现插入成功
select * from qgcq_view
select * from jsc_countqg
select * from BmDepts1

2.DDL触发器（create,drop,alter）（数据定义语言）

DDL触发器是当服务器或者数据库中发生数据定义语言（主要是create,drop,alter开头的语句）事件时被激活使用，使用DDL触发器可以防止对数据架构进行的某些更改或记录数据中的更改或事件操作

1. 注意作用域，作用域分别有数据库作用域，和服务器作用域

2. 示例 在删除表的时候提示不允许删除且进行回滚操作

--创建一个DDL触发器
use xgfdb
go  
create trigger safety   --创建触发器
on  database            --这是数据库作用域的
after drop_table
As
PRINT '不可删除此表'
ROLLBACK TRANSACTION    --用于回滚之前所做的修改，是数据库恢复到原来的状态
--尝试操作删除表
drop table jsc_countqg  --结果失败

create trigger safety    --创建触发器
on  all server           --这是服务器作用域的
after drop_database
As
PRINT '不可删除此表'
ROLLBACK TRANSACTION     --用于回滚之前所做的修改，是数据库恢复到原来的状态
--尝试操作删除表
drop table jsc_countqg   --结果失败

3.登陆触发器（相应LOGIN事件）

登陆时触发的触发器

4.触发器的修改：ALTER TRIGGER

1.修改DML触发器

USE xgfdb
GO 
ALTER TRIGGER table2_insert --架构名.触发器名
 ON table2                  --此处是表或者试图
 FOR UPDATE  --或者为 AFTER/INSTEAD OF/DALETE/INSERT/UPDATE
 --NOT FOR REPLTCATION      --不用于复制
 AS                         --AS后接sql语句
 PRINT '执行的操作是修改'

2.修改DDL触发器

ALTER TRIGGER table2_insert --触发器名
 ON DATABASE/ALL SERVER      --此处指定作用域是在数据库还是服务器
 FOR ALTER
 AS                         --AS后接sql语句
 PRINT '执行的操作是修改'

5.触发器的删除：DROP TRIGGER

DML触发器创建后名称一般保存在系统表sysobjects中 ，在删除前可以先判断该触发器的名称是否存在

IF EXISTS(SELECT NAME FROM sysobjects WHERE NAME ='YOUR TRIGGERNAME')

触发器本身是存在在表中的，当表被删除时候，表中的触发器也一同被删除掉。使用DROP TRIGGER

1.删除DML触发器

DROP TRIGGER 架构名.触发器

2.删除DDL触发器

DROP TRIGGER 触发器名 ON DATABASE/ALL SERVER  要用ON指定作用域

6.通过界面方式操作触发器

1.创建触发器

通过页面方式只能创建DML触发器 可以查看DDL触发器

2.修改触发器

DML触发器可以被修改 (但是被设置成WITH ENCRYPTION的触发器是不可修改的) DDL触发器不可被修改

3.删除触发器

均可进行删除

实际遇到的一些知识点

1.union和union all

1.union 是连接上下且去除完全相同的重复项 union all是直接连接，不去比较重复且去重。所以使用union all进行连接时速度更快。