金典 SQL筆記(1)，金典sql筆記

金典 SQL筆記(1)，金典sql筆記

page(1-75)

主鍵最好是無意義的欄位便於以後擴充.
PS:假設以標書編碼為主鍵,以後標書編碼填錯需要改的時候,關聯表都需要跟著改.如果是一個無意義的自增欄位是主鍵就無此原因.

主鍵最好不要設定為聯合主鍵,否則降低效率,不利於擴充
PS:原文[聯合主鍵可以解決表中沒有唯一主鍵的問題,不過聯合主鍵有如下缺點:]
1.效率低.在進行資料的添加、刪除、尋找及更新的時候,資料庫系統必須處理倆個欄位,這樣大大降低了資料的處理速度.
2.使資料庫的結構設計變得槽糕.組成聯合主鍵的欄位通常都是有業務含義的欄位,這與”使用邏輯主鍵而不是業務主鍵”的實踐相衝突,容易造成系統開發及維護上的麻煩.
3.使建立指向此表的外部索引鍵關聯關係變得非常麻煩,甚至無法建立指向此表的外部索引鍵關聯關係.
4.加大開發難度.很多開發工具及架構只對單個主鍵有良好的支援,對於聯合主鍵經常需要進行非常複雜的特殊處理.
考慮到這些缺點,我們應該只在相容遺留系統等特殊場合才使用聯合主鍵,而在其它場合則應該使用唯一主鍵.

字元類型知識點:
1.大部分資料庫中固定長度字元類型名稱為char(使用固定長度字元類型儲存資料時,由於剩餘部分會以空格填充,那麼在讀取的欄位值時就會將後面填充的空格也讀出來.
2.可變長度字元類型一般為varchar
PS:固定長度字元類型和可變長度字元類型都只能儲存基於ACSII的字元,這樣對於使用中文、韓文、日文等UniCode字元集的程式來說將會造成儲存問題.為瞭解決這個問題,我們可以使用國際化可變長度字元類型,這種類型可以用倆個位元組來儲存一個字元.這樣就可以解決中文、韓文等字串儲存問題了.在大部分資料庫中可變長度字元類型名稱為nvarchar.
但是如果欄位中沒有存放雙位元組字元的話,盡量不要使用國際化可變長度字元.
3.固定長度字元類型和可變長度字元類型一般都不能指定過於大的長度,比如長度超過1024是不允許的.超過這個長度的建議使用大字元類型欄位

SQL執行順序
WHERE語句在GROUP BY語句之前；SQL會在分組之前計算WHERE語句。
HAVING語句在GROUP BY語句之後；SQL會在分組之後計算HAVING語句

對於分組來說，SELECT和GROUP　ＢＹ列必須匹配。而SELECT語句包含彙總函式時這一規則是一個例外。

插入語句
insert into 表名(欄位,欄位2,欄位3) values(‘值’,’值1’,’值2’)
PS:忽略欄位的話,則會按照定義表中的欄位順序進行插入.建議不使用忽略寫法.忽略後不容易查看對應的欄位和值的對應容易反倒容易出錯.

select * from People –所有
select name,age from People –部分列
select max(age) from People –最大值
select min(age) from People –最小值
select avg(age) from People –平均值
select sum(age) from People –求和
select count(age) from People –統計記錄數量

select * from T_Employee order by FAge asc –排序 asc升序 預設升序可省略
select * from T_Employee order by FAge desc –降序
select * from T_Employee order by FAge desc,FSalary desc –多組排序

二元操作符 or and 左運算式為待匹配的欄位,而右運算式為待匹配的萬用字元運算式.
select * from T_Employee where FName like ‘erry’ –單字元匹配的萬用字元””
select * from T_Employee where FName like ‘%n_’ –多字元匹配的萬用字元為”%”

————–S
select * from T_Employee where FName like ‘[SJ]%’ –集合匹配 匹配第一個字元為S或者J長度不限的字串
select * from T_Tmployee where FName like ‘^[SJ]%’ –上面的運算式取反 即不包含 S或者J開頭的長度不限的字串 等同於下面運算式
select * from T_Tmployee where NOT(FName like ‘S%’) and NOT(FName like ‘J%’) 萬用字元過濾是非常強大的功能,不過在使用萬用字元過濾的時候,資料庫系統會對全表進行掃描,所以執行
速度非常慢.因此不要過多的使用萬用字元過濾.在使用其他方式可以實現效果的時候就應該避免使用萬用字元過濾
————–E

select * from T_Tmployee where FName IS NULL –空值判斷 不要使用時 FName = NULL 去判定這種寫法是錯誤的!
select * from T_Tmployee where FName IS NOT NULL –判斷不為空白的值

–反義運算子 ‘=’ ‘>’ ‘<’ 等於 大於 小於 可以通過 ‘!’ 來取反 ‘!=’ ‘!>’ ‘!<’ 不等於 不大於 不小於
select * from T_Tmployee where FAge != 22 and FSALARY != 2000 – 檢索年齡不等於22誰 工資不等於2000員的員工
– 不等於運算分 ‘<>’
– ! 運算子 只能運行在MS SQLSERVER 和 DB2 這倆種資料庫上,如果要移植到其它資料庫上的話 要避免使用這種方式.
–同義運算子 能夠在所有主流資料庫上運行.不過由於粗心等原因 很容易將 ‘不大於’ 表示為 ‘<’ ,從而忘記’不大於’是包含 ‘小於’ 和’等於’這倆個意思.容易出錯.
–因此推薦 使用 NOT 運算子.來表示 ‘非’的意思 除了’<>’這種方式之外

–多值檢測 公司要為23,25,28歲員工發福利 檢索 姓名 年齡 工號
–一般性我們的思路是 用or Fage = 23 or Fage = 25 or Fage = 28 數量一旦變多 反倒不好維護
– SQL 提供了 in 方式. Fage in (23,25,28) in語句只能進行多個離散值的檢測.
–範圍值檢測 查詢某一區間的值 諸如23-27歲. 用In 則窮舉 或者 Fage >=23 and Fage <=27
–在sql中 推薦使用 where between 23 and 27 等同於 Fage >=23 and Fage <= 27 而且效能更高.

–慎用 where 1=1 ,使用where 1=1後 資料庫無法使用索引等查詢最佳化策略,資料庫將被迫對每條資料進行掃描(也就是全表掃描)以比較此行是否滿足過濾條件.資料庫較大時會比較慢

建表及案例資料

USE [NB]GO/****** 對象:  User [sasa]    指令碼日期: 06/25/2015 10:40:21 ******/CREATE USER [sasa] FOR LOGIN [sasa] WITH DEFAULT_SCHEMA=[dbo]GO/****** 對象:  StoredProcedure [dbo].[SP_Select]    指令碼日期: 06/25/2015 10:40:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCreate Proc [dbo].[SP_Select]       @OName varchar(100)      As  Declare @Str Varchar(1000),    @dbname varchar(40)      set @dbname=db_name()      Set @Str='Select * from '+@dbname+'.dbo.'+@OName      Exec (@Str)GO/****** 對象:  StoredProcedure [dbo].[sp_syscolumns]    指令碼日期: 06/25/2015 10:40:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROC [dbo].[sp_syscolumns]   --Exec sp_syscolumns 'eemployee'@Object NVARCHAR(1000)As  /*    Function:取得一個對象中的所有列的項目(主要針對錶)   Remark:  Create By Deam L 2013/4/7*/Begin    Set nocount on    Declare @Name NVARCHAR(1000)    Select  @Name=Isnull(@Name+',','')+name From syscolumns Where id=object_id(@Object)    Print   @Name    Set nocount offENDGO/****** 對象:  StoredProcedure [dbo].[前言]    指令碼日期: 06/25/2015 10:40:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:      <CXP,,>-- Create date: <2014-10-8 09:11:56,,>-- Description: <擷取OA系統進行中的申請的採購流程,,>-- =============================================CREATE PROCEDURE [dbo].[前言]ASGO/****** 對象:  StoredProcedure [dbo].[c_CreateSqlBaseTable]    指令碼日期: 06/25/2015 10:40:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[c_CreateSqlBaseTable]ASBEGIN    --T_Person為記錄人員的資料表 其中主鍵欄位FName為人員姓名,FAge為年齡,FRemark為備忘資訊,    --T_Debt為債務資訊.其中主鍵為FNumber為債務編號,FAmount為欠債金額,FPerson為欠債人姓名,    --FPerson與T_person中FName欄位建立了外部索引鍵關聯關係    CREATE TABLE T_Person(FName VARCHAR(20),FAge INT,FRemark VARCHAR(20),primary KEY(FName))    CREATE TABLE T_Debt(FNumber VARCHAR(20),FAmount NUMERIC(10,2) NOT NULL    ,FPerson VARCHAR(20),PRIMARY KEY(FNumber),FOREIGN KEY(FPerson) REFERENCES T_Person(FName))    --插入範例資料    INSERT INTO T_Person(FName,FAge,FRemark) VALUES('Tom',18,'USA')    INSERT INTO T_Person(FName,FAge,FRemark) VALUES('Jim',20,'USA')    INSERT INTO T_Person(FName,FAge,FRemark) VALUES('Lili',22,'China')    INSERT INTO T_Person(FName,FAge,FRemark) VALUES('XiaoWang',17,'China')    INSERT INTO T_Person(FName,FAge,FRemark) VALUES('Kimisushi',18,'Korea')    INSERT INTO T_Person(FAge,FName) VALUES(22,'LXF')    INSERT INTO T_Person VALUES('lurenl',23,'China') --不推薦此寫法,容易出錯    INSERT INTO T_Debt(FNumber,FAmount,FPerson) VALUES('1',300,'Jim')    INSERT INTO T_Debt(FNumber,FAmount,FPerson) VALUES('2',300,'Jim')    INSERT INTO T_Debt(FNumber,FAmount,FPerson) VALUES('3',100,'Tom')ENDGO/****** 對象:  Table [dbo].[T_Person]    指令碼日期: 06/25/2015 10:40:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[T_Person](    [FName] [varchar](20) NOT NULL,    [FAge] [int] NULL,    [FRemark] [varchar](20) NULL,PRIMARY KEY CLUSTERED (    [FName] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGO/****** 對象:  Table [dbo].[T_Debt]    指令碼日期: 06/25/2015 10:40:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[T_Debt](    [FNumber] [varchar](20) NOT NULL,    [FAmount] [numeric](10, 2) NOT NULL,    [FPerson] [varchar](20) NULL,PRIMARY KEY CLUSTERED (    [FNumber] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGO/****** 對象:  ForeignKey [FK__T_Debt__FPerson__07020F21]    指令碼日期: 06/25/2015 10:40:21 ******/ALTER TABLE [dbo].[T_Debt]  WITH CHECK ADD FOREIGN KEY([FPerson])REFERENCES [dbo].[T_Person] ([FName])GO