数据库 SQL 高级用法

目录

一、INSERT INTO SELECT 用法

二、逻辑控制语句

 三、公式表表达式

四、存储程序

五、触发器

---

一、INSERT INTO SELECT 用法

INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。

1、从一个表中复制所有的列插入到另一个已存在的表中

INSERT INTO 表2
SELECT * FROM 表1;

实例

INSERT INTO student2
SELECT * FROM student;

以上 SQL 语句复制 " student " 表中数据插入到 " student2 " 中。

2、从一个表中只复制希望的列插入到另一个已存在的表中

INSERT INTO 表2
(列名1,列名2)
SELECT 列名1,列名2 
FROM 表1;
‍

实例

INSERT INTO student2
(ID,name)
SELECT ID,name 
FROM student;

以上 SQL 语句只复制 " student " 中的 " ID "、" name " 列 到 " student2 " 中。

参考：数据库 SQL 高级用法（四） - 码农教程

二、逻辑控制语句

  if-else语句

--语法结构：
if( 条件表达式 )
	begin
		命令行或程序块
	end
else
	begin
	 	命令行或程序块
	end

   while-continue-break语句

--语法结构：
while(条件表达式)
	begin
		命令行或程序块
		[break]	→→ 跳出循环
		[continue] →→ 可以让程序跳过continue命令之后的语句
		命令行或程序块
	end

case语句

--语法结构：
case	
		when 条件表达式 then 运算式
		when 条件表达式 then 运算式
		[else 运算式]
	end

实例代码：

--用法一
select case when Line='B01' then '00' else Line end from [ALL-Line_place]
--用法二
select case Line when 'B01' then '00' else Line end from [ALL-Line_place]

 三、公式表表达式

  将公用表表达式（CET）视为临时结果集，在select、insert、update、delete 或 create view语句的执行范围内进行定义

--CET基本语法结构：
	with 公用表表名 (字段列表)
	as	( 命令行或程序块 )
	select * from 公用表表名

实例代码：

;WITH TRR0 AS (select * from Line_Machine_tx),
 TRR1 AS(select * from Machine_tx),
 TRR2 AS(select * from [ALL-Line_place])
 select * from TRR0,TRR1,TRR2

四、存储程序

创建位置：

 

创建存储程序

USE [D_total]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[proc_search_alm_machine_total]
   
	@linn varchar(255),@Time_Num varchar(255),@Startt varchar(255),@Endt varchar(255)	
AS
BEGIN
    --set @linn ='C02'
    --set @Time_Num ='Num'
    --set @Startt ='2021-08-20 08:00:00'
    --set @Endt='2021-08-26 20:00:00'
	declare @we varchar(8000)
	--declare @dj varchar(2000)
	declare @alm varchar(2000)
	declare @alm1 varchar(2000)	
	if @Time_Num ='Time'
	begin 
	set @alm ='BETWEEN '''+@Startt+''' and '''+@Endt+''' '
	set @we='执行代码'
	end
	else --num
	begin 
	set @alm ='条件'
    set @we='执行代码'
	end	
	EXEC(@we)
	--print(@we)
END
GO

执行代码：

exec proc_search_alm_machine_total @linn ='B11',@Time_Num ='Time',@Startt ='2021-12-06 08:00:00',@Endt='2021-12-06 20:59:59'

五、触发器

触发器创建位置：

 

创建insert触发器

USE [D_total]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[B01_cfq_after]
ON [dbo].[B01_adress_record]
WITH EXECUTE AS CALLER
FOR INSERT
AS
BEGIN 
		insert into [B01_ALM_Data_Record]([Line],[Machine],[Adress],[Adress_num],[UpStart_time],[UpEnd_time],[Data],[Model])select INSERTED.Line,INSERTED.Machine,INSERTED.Adress,INSERTED.Adress_num,INSERTED.Up_time,INSERTED.Up_time,INSERTED.Data,INSERTED.Model  from INSERTED  
		where (INSERTED.Adress='M' OR INSERTED.Adress='WX') and INSERTED.Data=1;
		
END
GO

ALTER TABLE [dbo].[B01_adress_record] ENABLE TRIGGER [B01_cfq_after]
GO

创建delete触发器

USE [D_total]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE TRIGGER [dbo].[B01cfq_alm_de]
ON [dbo].[B01_adress_record]
WITH EXECUTE AS CALLER
FOR DELETE
AS
BEGIN
IF(SELECT MIN(DELETED.Data) FROM DELETED WHERE ( DELETED.Adress='M' OR DELETED.Adress='WX'))=1
 BEGIN 
	DELETE  [B01_ALM_Data_Record] from [B01_ALM_Data_Record] inner join DELETED on [B01_ALM_Data_Record].Line=DELETED.Line and [B01_ALM_Data_Record].Machine=DELETED.Machine and [B01_ALM_Data_Record].Adress=DELETED.Adress and [B01_ALM_Data_Record].Adress_num=DELETED.Adress_num  and [B01_ALM_Data_Record].UpStart_time=DELETED.Up_time 
	where  [B01_ALM_Data_Record].Data=1  
	END
END
GO

ALTER TABLE [dbo].[B01_adress_record] ENABLE TRIGGER [B01cfq_alm_de]
GO

创建update触发器

USE [D_total]
GO

/****** Object:  Trigger [dbo].[B01CFQ_upm]    Script Date: 2023/1/3 下午 03:58:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[B01CFQ_upm]
ON [dbo].[B01_adress_record]
WITH EXECUTE AS CALLER
INSTEAD OF INSERT
AS
BEGIN 
INSERT INTO B01_ALM_Data_Record([Line],[Machine],[Adress],[Adress_num],[UpStart_time] ,[UpEnd_time],[Data],[Model])
SELECT  INSERTED.Line,INSERTED.Machine,INSERTED.Adress,INSERTED.Adress_num,INSERTED.Up_time,INSERTED.Up_time,INSERTED.Data,INSERTED.Model 
from INSERTED 
LEFT JOIN Machine_adress ON Machine_adress.Machine=inserted.Machine AND Machine_adress.Adress=inserted.Adress AND Machine_adress.Adress_num=inserted.Adress_num
LEFT JOIN B01_ALM_Data_Record ON B01_ALM_Data_Record.Machine=inserted.Machine AND B01_ALM_Data_Record.Adress=inserted.Adress AND B01_ALM_Data_Record.Adress_num=inserted.Adress_num AND  B01_ALM_Data_Record.Data=inserted.Data 
WHERE inserted.Data=1 and Machine_adress.Data_Type='M2' AND B01_ALM_Data_Record.Machine IS NULL 
;
UPDATE B01_ALM_Data_Record SET B01_ALM_Data_Record.UpEnd_time=inserted.Up_time,B01_ALM_Data_Record.Data=inserted.Data 
FROM B01_ALM_Data_Record 
INNER JOIN inserted 
ON inserted.Machine=B01_ALM_Data_Record.Machine AND B01_ALM_Data_Record.Adress=inserted.Adress AND B01_ALM_Data_Record.Adress_num=inserted.Adress_num 
INNER JOIN Machine_adress 
ON Machine_adress.Machine=inserted.Machine AND Machine_adress.Adress=inserted.Adress AND Machine_adress.Adress_num=inserted.Adress_num
 WHERE inserted.Data=0 and Machine_adress.Data_Type='M2' and B01_ALM_Data_Record.Data=1

END
GO

ALTER TABLE [dbo].[B01_adress_record] ENABLE TRIGGER [B01CFQ_upm]
GO

总结：本篇文章主要讲解SQL数据库的INSERT INTO SELECT 、逻辑控制语句、公式表表达式、预存程序、触发器用法及其简单实例。