SQL基础语句整理

     有次笔试最后一页的三个数据库连接查询，没有写出来，被考官暗讽了下。现在想来，实习初，确实很LOW。现公司刚入职的时候，负责过ETL方面，所以和数据库打了不少交道，五十行的联合查询、上百行的存储过程很常见，游标、视图、索引频频接触，包括在SQL中断点调试，测值等等，在这里将所接触所了解的进行整理，做一个小总结，慢慢的积累，对游标、视图等等也做一个简单的说明。我有时候遇到相同问题，会出现忘记上次是如何解决问题的情况，直到花费大量的时间、精力去重新找到解决方案。这很浪费，所以凡是遇到的问题，所做解决的方案，我强迫自己写个备注，将大致思路进行汇总整理，以方便自己及时回顾。毕竟好记性不如烂笔头，古人诚不欺我也！

     

1、数据库常见三种数据模型： 层次、网状、关系

2、关系的 三类完整性约束：实体完整性（主键不为空）、参照完整性、用户自定义完整性

3、常用易忘数据类型：

   ①int  4byte = 32bit   一个汉字2字节，中文标点、全角占两字节；字母、英文标点、半角占一个字节

   ②char        固定长度的非unicode 字符数据，最大长度为8000个字符

   ③varchar    可变长度的非unicode 字符数据，最大长度为8000个字符，存储大小为输入数据的实际长度

   ④nchar       固定长度的unicode 字符数据，最大长度为4000个字符，存储大小为实际长度的两倍

   ⑤nvarchar  可变长度的unicode 字符数据，最大长度为4000个字符，存储大小为实际长度的两倍

4. 查询31到40条记录（面试）

--id连续
SELECT * FROM IBBC.dbo.Students AS s WHERE s.Stu_id BETWEEN 31 AND 40

--id不连续
--普快
SELECT TOP 10 * FROM Students AS s WHERE s.Stu_id NOT IN (SELECT TOP 30 s2.Stu_id FROM Students AS s2)
--动车
SELECT TOP 10 * FROM  (SELECT TOP 40 * FROM IBBC.dbo.Students AS s ORDER BY s.Stu_id ASC) AS temp ORDER BY temp.Stu_id DESC
--高铁
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY s.Stu_id) Newcolumn,* FROM Students AS s)s1 WHERE s1.Newcolumn BETWEEN 31 AND 40 

--蹦蹦车  易混淆 实际查询的是最后十条记录的降序
SELECT TOP 10 * FROM  (SELECT TOP 40 * FROM IBBC.dbo.Students AS s) AS temp ORDER BY temp.Stu_id DESC

5、常见的判断操作

--判断表是否存在
IF exists(select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_22]'))

--判断表栏位是否存在
IF EXISTS (select * from syscolumns where id=object_id('Table_1') and name='IDD')

--判断存储过程是否存在
If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]')) 

--判断视图是否存在
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_test]'))

6、常见的表操作

--增加栏位
ALTER TABLE IBBC.dbo.Table_1 ADD YYY CHAR(5) 
--删除栏位
ALTER TABLE IBBC.dbo.Table_1 DROP COLUMN YYY
--更改栏位类型型
ALTER TABLE IBBC.dbo.Table_1
ALTER COLUMN b_id INT NOT NULL

--删除表索引
drop tablleName.indexName

--重命名栏位
EXEC sp_rename 'TableName.ColumnName', 'NewColumnName', 'column'

--指定栏位添加描述
exec sp_addextendedproperty N'MS_Description', N'我是描述信息', N'user', N'dbo', N'table', N'Table_1', N'column', N'YYY'

--添加主键
ALTER TABLE IBBC.dbo.Table_2 ADD CONSTRAINT test_main PRIMARY KEY(b_id)
--删除主键
ALTER TABLE IBBC.dbo.Table_2 DROP CONSTRAINT test_main 

--添加外键
alter table IBBC.dbo.Table_1 add constraint test_foreign foreign key(bas_id) references IBBC.dbo.Table_2(b_id)
--删除外键
ALTER TABLE IBBC.dbo.Table_1 DROP CONSTRAINT test_foreign 

--设计器修改后保存失败： 工具->选项->设计器->表和数据库设计->保存时拒绝修改数据设计内容（取消勾选）

 7. 常用查询操作

--case when then
SELECT  s.Stu_id,s.Stu_Name,s.Date_,
(CASE s.Stu_id
    WHEN 1 THEN DATEADD(mm,1,s.Date_) 
    WHEN 2 THEN DATEADD(mm,-1,s.Date_) 
    ELSE DATEADD(mm,2,s.Date_) 
 END) New_date
 FROM IBBC.dbo.Students AS s

--日期函數
SELECT DATEPART(yy,GETDATE()) AS 'Year' --yy/yyyy/yy
SELECT DATEPART(mm,GETDATE()) AS 'Month' --mm/m/Month
SELECT DATEPART(ww,GETDATE()) AS 'Week' -- ww/wk/Week


--日期轉換為字符串
SELECT CONVERT(VARCHAR,GETDATE(),112)

--標準字符串转换为日期 20080808
SELECT CASt('2003-02-02' AS  DATETIME)
--部分字符串转换为日期
select convert(datetime,substring('200808',1,4)+'-'+substring('200808',5,2)+'-1')


--类型转换
SELECT CONVERT(VARCHAR(8),GETDATE(),112)
SELECT CAST (GETDATE() AS VARCHAR(11))

--查询栏位值为null的数据
select * from table where column is null;
--查询栏位值为''的数据
select * from table where column = '';


--拼接字段

 select SchoolID, stuff((select ','+UserCode from xx where b.SchoolID = SchoolID for xml path('')),1,1,'') sss
 from xx b group by SchoolID;

 

 8. 聚合函数

--当前顺序 where -> group by -> having 函数 -> order by
--SQl顺序 from -> Join on -> where -> group by -> having -> select -> distinct -> order by -> top

--聚合函数 分组
select s.StudentName,sum(grade) as '总成绩' from IBCC.dbo.Student s group by s.StudentName 

--having 对聚合函数查询后的结果集进行筛选
select s.StudentName, sum(grade) as '总成绩'  from ibcc.dbo.Student s group by s.StudentName having sum(grade)>100

9. 视图

--视图 虚拟表
use IBCC
go
--创建
create view view_test as
select * from IBCC.dbo.Student where grade >80
go
--查询
select * from ibcc.dbo.view_test
--删除 不能指定数据库名为前缀
drop view dbo.view_test

10.存储过程

--不带参数的存储过程
--创建
create proc proc_test 
as 
select * from ibcc.dbo.Student
go

--带参数的存储过程
--创建
create proc proc_test1 @s_id int
as
select * from IBCC.dbo.Student where StudentID=@s_id
go
--调用
exec proc_test1 1002
exec proc_test1 @s_id =1002
--删除
drop proc proc_test1

 11. 事务

--一段SQl中途执行失败，可回滚至员状态，eg:转账、删除、更新等操作
BEGIN TRAN --开始事务
DECLARE @b INT;
SET @b = 0;
    DELETE FROM A ...
    DELETE FROM B ...
    DELETE FROM C ...

IF(@b>0)
    BEGIN
       
       COMMIT TRAN  --提交事务
    END
ELSE
    BEGIN
      ROLLBACK TRAN --回滚事务
    END    
     
END

 12. 查锁表-解锁表

--查询锁表进程
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName 
from sys.dm_tran_locks where resource_type='OBJECT'

--删除锁表进程
declare @spid int 
Set @spid = 52 --锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid as varchar)
exec(@sql)

13. 查询SQL 拼接字符时的 In 查询函数

USE [DateBaseName_demo]
GO

/****** Object:  UserDefinedFunction [dbo].[KiaInStr]    Script Date: 2020/10/29 15:32:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[KiaInStr]

(@SourceStr varchar(max))--源字符串    

RETURNS  @table table(list  varchar(50) ) 

AS

BEGIN

select @sourcestr =  replace(@sourcestr,';',',')

select @sourcestr = replace(@sourcestr,' ',',') 

if charindex(',',@sourcestr)>0     

begin

declare @i int

declare @n int

set @i=1

while charindex(',',@sourcestr,@i)>0 

begin   
set @n=charindex(',',@sourcestr,@i) 

insert into @table values(substring(@sourcestr,@i, @n-@i) )

set @i=@n+1
end 
insert into @table values(substring(@sourcestr,@i,len(@sourcestr)-@i+1))

end 

else insert into @table values(@sourcestr)

delete from @table where isnull(list,'') = ''
return

END
GO

 14. 连接更新语句

UPDATE table1 AS b
INNER JOIN table2  AS a ON a.id = b.from_id
SET b.company_id = a.company_id
WHERE b.fieldA= '61116' AND a.fieldB != 1;