数据库的成长之路

一、查询

１、连接查询

2、分页查询

  
利用row_number() over(order by id)函数计算出行数，选定相应行数返回即可，不过该关键字只有在SQL server 2005版本以上才有。

  SELECT TOP #{pageSize} * FROM 
  (SELECT ROW_NUMBER() OVER(ORDER BY bus.id) AS RowNumber,
   bus.XX,pro.XX FROM tableNameA bus join tableNameB pro 
   on pro.id = bus.id)AS A 
   WHERE RowNumber > (${pageSize}*(${startPage}-1))
利用offset /fetch next（2012版本及以上才有）offset A rows ,将前A条记录舍去，fetch next B rows only ，向后在读取B条数据

  -- 分页查询（通用型） 
   select * from student order by sno 
   offset ((@pageIndex-1)*@pageSize) rows 
   fetch next @pageSize rows only; 
  -- 分页查询第2页，每页有10条记录 
    select * from student order by sno offset 10 rows fetch next 10 rows only ;

3、系统表

-----找那个SP里面用到这个表

select a.name, m.definition from sysobjects a, sys.sql_modules m
where a.id = m.object_id and a.xtype = 'P' and lower(m.definition) like '表名'

------找那个表里用到这个字段

SELECT * FROM SYS.objects JOIN SYS.columns ON SYS.objects.object_id=SYS.columns.object_id WHERE SYS.columns.name='列名'

--以下几个查询信息
select * from sys.tables
select * from sys.procedures
select * from sys.indexes
select * from sys.sequences
select name from sysobjects where type='U'

--它可以查看sql服务器上当前所有建立的数据连接

sp_who2

--查看是否被谁锁表 看这列数据是否有值BlkBy，如果有就在SPID里面去找那个值，就能找出对应的主机名，看是谁锁住了
sp_who2 active

二、增加

三、修改

（１）update　select语法　，当修改的值往数据库存的时候需要查别的表才能确定修改的值，例如，往回传的性别是女，但是存的时候存的是１，这个１是需要查别的表才能确定的


 UPDATE a SET a.XXX='cc',a.XX='cc', a.XXX = b.XXX
 FROM tableNAme a ,tableNAme b 
 where  b.XXX = 'bbb' and a.XXX=1

四、删除

（1）删除约束条件（默认值约束，主键约束，等等）

ALTER TABLE dbo.表名 DROP CONSTRAINT 约束名

alter table #MEMBER_TEMP ALTER  column DOB datetime null 删除非空约束

（2）删除表数据

drop table tablename

truncate table tablename

truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚

DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作

五、建表／修改表结构

（1）删除/增加列，约束

alter table 表名 drop column 列名1,列名2  --删除列

alter table  表名 add 列名 int(4)  --增加列

--删除约束条件（默认值约束，主键约束，等等）
ALTER TABLE dbo.TPMLPLASENTRYH DROP CONSTRAINT PK_TPMLPLASENTRYH

（2）修改列名 XXX表的列名改成列名1

exec sp_rename 'XXX.列名','列名1'

（3）修改表中某一列约束可以为null

alter table #MEMBER_TEMP ALTER  column DOB datetime NULL

六、其他积累

（1）游标

declare @polno char(15),@CODE char(10) -----声明变量
declare pol_cur cursor                     -----声明游标 并为游标赋值
    for select polno,CODE from XXXX
    where INIEFFDT > '2019-10-01 00:00:00.000'
open pol_cur                        -----打开游标
fetch next from pol_cur into @polno,@CODE --提取第一行数据
print N'XXX号     XXXX编号'  -----打印表头
while @@FETCH_STATUS = 0     --------循环操作游标里的数据，这里是全局变量不用声明
begin 
    print cast(@polno as char(12)) + @CODE
    fetch next from pol_cur into @polno,@CODE  --------拿取下一条数据，就相当于for循环的i++
end
close pol_cur    ---------关闭游标
deallocate pol_cur   ---------释放游标

(2)

(3)主键自增开关

set IDENTITY_INSERT 表名 on
set IDENTITY_INSERT 表名 off

（4）SP

sp_tables 'XXX%'      --模糊查询表名

sp_helptext SP名       --显示存储过程的源代码

sp_help SP名            --显示存储过程的参数及数据类型

sp_depends SP名       --显示和存储过程相关的数据库对象信息

sp_stored_procedures  --返回数据库中的存储过程列表

（5）格式化时间

select  stuff(stuff(convert(varchar(10),getdate(),112),5,0,N'年'),8,0,N'月')+N'日'
select  stuff(stuff(convert(varchar(10),dateadd(day,14,getdate()),112),5,0,N'年'),8,0,N'月')+N'日' --当前时间加上14天

（6）生成sql


----------------（注意：如果是单个字段所有的值是单元格的数据就是这样写'"&A2&"'，如果是字符串拼接就这样写'/file/contracts/"&C2&"'）----------------
="update users set empid = '"&C2&"'  where plan_num ='"&A2&"'  and  part_num ='"&B2&"'"
="insert into tmfcurrency(curr_code,base_curr,exchg_rate,as_at_date,rate_buy,rate_sell,approve_flag,update_userid,update_date,approve_userid,approve_date,upload_flag)values('150','',1.00000,'"&A2&"',1.00000,1.00000,'Y','eada045','"&A2&"','eada048','"&A2&"','Y')"
="insert into Contracts(Contract_ID,Branch_ID,Contract_Name,File_Name,File_Path,ref1,cmpprodcode,Effdate,File_Type) values('"&B2&"','0',N'"&E2&"','"&A2&"','/file/contracts/"&C2&"','GMMA5','GMMA2 S00T','1970-01-01 00:00:00.000','"&D2&"')"
="select  * from TMEMBER where CLNTCODE = '"&A2&"' and  CERTNO ='"&B2&"'"

（7）需求：两个表，一个多数据(包括少数据)#temp1，一个少数据#temp2，现在需要在多的里面去掉少的那部分（去重，是一条数据全部重复，全字段的重复才不要）

select * from #temp1  t1 where  not exists (
    select 1 from #temp2 t2 where 
    t1.字段1 = t2.字段1
    t1.字段2 = t2.字段2
    t1.字段3 = t2.字段3
)

（8）查一个表数据大于1条的信息

select * from (select count(*) cc,SEQ from table group by SEQ) t  where t.cc>1

select count(1),name from table where ISLAST=1 and REMOVEDATE ='2200-01-01' group by name having count(1)>1    --推荐这种呦

（9）各种函数积累

LTRIM(RTRIM(FAMILYID))+'_0'+cast(ROWNUM as varchar) as FAMILYID --去左右空格并且拼接窗口函数生成的排序作为FAMILYID 这一列

convert(datetime,(CASE WHEN isdate(substring(T1.BENID,7,8))=1 THEN substring(T1.BENID,7,8) ELSE null END))as DOB  
--从身份证号BENID拿出出生日期DOB，用isdate函数去校验是否符合

(CASE WHEN isnumeric(SUBSTRING(T1.BENID,17,1))=1 THEN (CASE WHEN 0=(SUBSTRING(T1.BENID,17,1)%2) THEN 'F'  ELSE 'M' END) ELSE '' END) as SEX

--从身份证号BENID拿出性别

where len(T1.BENID) =18 ---取字符串的长度

select UPPER ('invoiceAmount')---或者选中Ctrl+Shift+U 在notepad++中也适应这个快捷键

not in 子查询不包括NULL值。

有专门的函数isnull(T1.BENID,'')

判空的语句有专门的子句 is not null

（10）快速的备份一个表

快速备份一张表，select * into userbackup20200110 from user --就会创建并插入user数据到userbackup20200110


select * into #user from xxx 与insert into #user select * from xx


select * into #user from tmember 此条语句是创建临时表并且插入数据，如果这条语句再次执行，就会出现报错，临时表已经存在，因此再次插入数据只能 写成下面的这种方式。
insert into #user select * from tmember 这种方式是select出来的东西插入到临时表中，只是插入不带创建临时表的。

（10）在数据库存储xml怎样修改呢

因为XML文件中有很多特殊符号会与sql语句中的字符串的单引号冲突，在执行update的时候，需要用两个单引号来替换一个单引号去执行，这样就可以成功执行

七、窗口函数

参考原文地址 https://zhuanlan.zhihu.com/p/92654574

https://blog.csdn.net/zhangshk_/article/details/82756557?utm_medium=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.edu_weight&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.edu_weight

八、循环

1） while循环方法（不推荐）

2）CTE公用表达式递归方式（推荐）

第1天给丈母娘1分钱，第2天给2分钱，第3天给4分钱，以此类推，每天给前一天的2倍，给一个月（按30天）算就行。问：第30天给多少钱，总共给多少钱？

with cte_table(Day, Money, MoneyTotal) as --给定字段

(

    select 1, cast(0.01 as decimal(18,2)), cast(0.01 as decimal(18,2)) --第一条模板数据

    union all

    select Day + 1, cast(Money * 2 as decimal(18,2)), cast(MoneyTotal + Money * 2 as decimal(18,2))--按规律给相应字段赋值

    from cte_table

    where Day < 30 --循环条件

)

select * from cte_table

九、存储过程调试

在调试的时候最好不要在原来的创建的文件里改，重新开一个窗口，改动下面的内容，不用在哪调用，直接F5就行。

--if exists (select * from sysobjects where id = object_id('dbo.UspGMOS171AMLMAIN') and sysstat & 0xf = 4)

--drop procedure dbo.UspGMOS171AMLMAIN

--GO这些注释掉

begin tran //加上这句

--CREATE PROCEDURE dbo.UspGMOS171AMLMAIN //注释

DECLARE  @PARAM VARCHAR(200)   //声明执行SP需要传入的参数

set @PARAM='2017-08-13-23.59.59'　//给需要传入的参数赋值

--AS／／注释

SELECT 'DEBUG', '@BeginDate = ', @BeginDate //可以在任何你想调试的位置加上想查的数据

select 'line(145)'  //方便的话，可以使用这句，放哪行括号里的数字就改成哪行，只是想看程序有没有执行到这一行，如果执行到了下面的控制台会有显示，如果没执行到，就什么也不显示。

也可以写任意的查询语句。

Rollback   //最后也加上这句

十、面试

count(1) count(*) count(列名)

执行效果上：
count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

执行效率上：
列名为主键，count(列名)会比count(1)快
列名不为主键，count(1)会比count(列名)快
如果表多个列并且没有主键，则 count（1）的执行效率优于 count（*）
如果有主键，则 select count（主键）的执行效率是最优的
如果表只有一个字段，则 select count（*）最优。

SQLServer 中 rank over 的使用

rank() over (order by 排序字段顺序)
rank() over (partition by 分组字段 order by 排序字段顺序)

posted @ 2020-08-18 18:53 向日葵呀阅读(186) 评论(0) 编辑收藏举报

刷新页面返回顶部

向日葵呀