SQL常用語句整理 - Sientuo－IT工程師數位筆記本

文章出處

有次筆試最后一頁的三個數據庫連接查詢，沒有寫出來，被考官暗諷了下。現在想來，實習初，確實很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

--重命名欄位
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 = '';

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