8个实用SQL脚本

2021-01-27 17:31:28
3426次极悦

生成数据库项目时，预先部署脚本、数据库对象定义和后期部署脚本合并为一个生成脚本，只能指定一个预先部署脚本和一个后期部署脚本，但可在预先部署脚本和后期部署脚本中包含其他脚本。在MySQL数据库中，使用SQL脚本可以提高数据访问的效率，并进行相关的数据处理。本文为大家介绍8个实用SQL脚本，帮助我们便捷MySQL数据库的使用。

1.行转列的用法PIVOT

CREATE table test(id int,name nvarchar(20),quarter int,number int)insert into test values(1,N'苹果',1,1000)insert into test values(1,N'苹果',2,2000)insert into test values(1,N'苹果',3,4000)insert into test values(1,N'苹果',4,5000)insert into test values(2,N'梨子',1,3000)insert into test values(2,N'梨子',2,3500)insert into test values(2,N'梨子',3,4200)insert into test values(2,N'梨子',4,5500)select * from test

结果：

select ID,NAME,[1] as '一季度',[2] as '二季度',[3] as '三季度',[4] as '四季度'fromtestpivot(sum(number)for quarter in([1],[2],[3],[4]))as pvt

结果：

2.列转行的用法UNPIOVT

create table test2(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)insert into test2 values(1,'苹果',1000,2000,4000,5000)insert into test2 values(2,'梨子',3000,3500,4200,5500)select * from test2

结果：

--列转行select id,name,quarter,numberfromtest2unpivot(numberfor quarter in([Q1],[Q2],[Q3],[Q4]))as unpvt

结果：

3.连接远程服务器

方法1：

select *  from openrowset(
'SQLOLEDB',
'server=192.168.0.1;uid=sa;pwd=password',
'SELECT * FROM dbo.test')

--方法2：

select *  from openrowset(
'SQLOLEDB',
'192.168.0.1';
'sa';
'password',
'SELECT * FROM dbo.test')当然也可以参考以前的示例，建立DBLINK进行远程连接

4.把多行SQL数据变成一条多列数据，即新增列

SELECT
 id,
 name,
 SUM(CASE WHEN quarter=1 THEN number ELSE 0 END) '一季度',
 SUM(CASE WHEN quarter=2 THEN number ELSE 0 END) '二季度',
 SUM(CASE WHEN quarter=3 THEN number ELSE 0 END) '三季度',
 SUM(CASE WHEN quarter=4 THEN number ELSE 0 END) '四季度'FROM testGROUP BY id,name

结果：

我们将原来的4列增加到了6列。细心的朋友可能发现了这个结果和上面的行转列怎么一模一样？其实上面的行转列是省略写法，这种是比较通用的写法。

5、表复制

语法1：Insert INTO table(field1,field2,...) values(value1,value2,...)

语法2：Insert into Table2(field1,field2,...) select value1,value2,... from Table1

（要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，还可以插入常量。）

语法3：SELECT vale1, value2 into Table2 from Table1

（要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。）

语法4：使用导入导出功能进行全表复制。如果是使用【编写查询以指定要传输的数据】，那么在大数据表的复制就会有问题？因为复制到一定程度就不再动了，内存爆了？它也没有写入到表中。而使用上面3种语法直接执行是会马上刷新到数据库表中的，你刷新一下mdf文件就知道了。

6.利用带关联子查询Update语句更新数据

--方法1：Update Table1set c = (select c from Table2 where a = Table1.a)where c is null

--方法2：update Aset newqiantity=B.qiantityfrom A,Bwhere A.bnum=B.bnum

--方法3：update(select A.bnum ,A.newqiantity,B.qiantity from Aleft join B on A.bnum=B.bnum) AS Cset C.newqiantity = C.qiantitywhere C.bnum ='001'

7.字符串替换SUBSTRING/REPLACE

SELECT REPLACE('abcdefg',SUBSTRING('abcdefg',2,4),'**')

结果：

SELECT REPLACE('13512345678',SUBSTRING('13512345678',4,11),'********')

结果：

SELECT REPLACE('[email protected]','1234567','******')

结果：

8.查询一个表内相同纪录 HAVING

如果一个ID可以区分的话，可以这么写

SELECT * FROM HR.Employees

结果：

select * from HR.Employeeswhere title in (select title from HR.Employeesgroup by titlehaving count(1)>1)

结果：

对比一下发现，ID为1,2的被过滤掉了，因为他们只有一条记录

如果几个ID才能区分的话，可以这么写

select * from HR.Employeeswhere title+titleofcourtesy in(select title+titleofcourtesyfrom HR.Employeesgroup by title,titleofcourtesyhaving count(1)>1)

结果：