首先感謝博客園,創建了這個專屬於開發者的知識共享平台,需要強調的是我並不是一個大牛級別的人物,只是一個初入園子還未見過世面的IT小菜鳥,本著一顆向園內大牛們學習的心,加入了這個圈子,借此機會,對自己日常所學進行實時整理,還望各位大神多多指教。
聯系網址:hovertree.com
以下是近期對SQL Server數據庫所學知識的相關總結,希望可以在一些地方幫助初入門者,對於相關的基礎名詞我就不一一介紹了,就從SQL語言操作說起吧。
1.數據庫入門之SQL語句總結
首先先對幾個數據庫中的數據類型進行下了解:
nchar ----------------存儲數據類型為寬字節字符數組,最多4000字節
nvarchar-------------存儲數據類型為寬字節字符串,最多4000字節
char------------------存儲數據類型為多字節字符數組,最多8000字節
varchar--------------存儲數據類型為多字節字符串,最多8000字節
舉個例子:
用char(10)和varchar(10)分別對”hello”字符串進行存儲,char(10)中該字符串所占字節數為10個,varchar(10)中所占字節數為6個。
如果有超大文本存儲,此時選用text,ntext類型
1.1SQL語句之定義功能
此項包括對基本表、索引和視圖的定義和撤銷。針對於表,我們可以對其進行創建、刪除以及修改,而針對於視圖、索引,我們可以創建和刪除。
1.1.1創建、修改、刪除表
創建:
create table 表名
{
<列名> <數據類型> <約束條件>
}
如:
create table studentinfo
(
number int primary key, --主鍵,唯一標識,不許重不許空
name varchar(10) unique, --唯一約束,除主鍵外唯一標識,可重可空
sex varchar(5) check(sex='男' or sex='女'),--檢查約束,保證值在一定范圍內
age int default(18) --默認約束
)
create table studentcourse
(
number int foreign key references studentinfo(number),--外鍵
course varchar(20)
)
修改:
如:
alter table 表名
{
add <新列名> <數據類型> <約束條件>
drop<約束條件>
alter column <列名> <數據類型>
}
刪除:
drop table 表名
如:
drop table studentinfo
1.1.2創建、刪除視圖
創建:
Create view 視圖名 as
(語句塊)
刪除:
Drop view 視圖名
需要注意的是視圖是由一個或多個表導出的虛擬的表
基於一個表:
如:
create view myview as
(select * from student)
select * from myview
update myview set ssex='女' where s#='01'
insert into myview values('09','aa','1994-07-09','男')
delete from myview where s#='09'
update myview set ssex='男' where s#='01'
基於多個表:
如:
create view view1 as
select s.*,sc.score,sc.c# from student s inner join
sc on s.s#=sc.s#
select * from view1
注意:這裡值得說明的是不論針對一個表還是多表,只要加了聚合函數的視圖將不能增刪改,只有針對原表的視圖才可以進行增刪改。
1.1.2創建、刪除索引
一般情況下超大型數據庫會選擇為數據庫添加索引,分為聚集索引和非聚集索引。
創建:
Create index 索引名 on 表名(列名)
刪除:
Drop index 索引名
1.2SQL語句之查詢功能
語句格式(包括語句順序):
Select [all | distinct] 列名
From 表名/視圖名
[where 條件表達式]
[group by 列名][having 條件表達式]
[order by 列名][asc | desc]
1.2.1單表查詢
(1)查詢列
Select 列名(多列用“,”隔開,所有列用“*”) from 表名
(2)查詢行
A.消除重復行
Select distinct 列名 from 表名
B.比較大小:=,>,>=,!=,!>,!<等
Select sname from student where cname=’english’
C.確定范圍:between…and…,not between…and…
Select sname,sage from student where sage between 20 and 23
D.確定集合:in,not in
Select sname from student where sdept in (‘IS’,’MA’,’CS’)
E.模糊查詢:like
Select s#,sname from student where sname like ‘劉%’
注意:若為’%劉%’,則查詢名字中有劉這個字的
F.涉及空值的:isnull
Select *,isnull(ssex,’未知’) from student
注:ssex項若為空,則將其置為“未知”
G.多重條件查詢:and,or
Select sname,sage from student where sage >= 20 and ssex=’男’
H.聚合函數:count,sum,max,min,avg等
Select avg(score) from sc where c#=’01’
I.查詢表中前幾條:top
Select top 2 * from student
J.條件:
select 列名 case when 條件
then 語句塊
when條件
then語句塊
else語句塊
end
from 表名
如:
按照出生日期來算,當前月日< 出生年月的月日則,年齡減一
select *,datediff(yy,birth,getdate()),
case when month(getdate())<month(birth)
then datediff(yy,birth,getdate())-1
when day(getdate())<day(birth)
then datediff(yy,birth,getdate())-1
else datediff(yy,birth,getdate())
end
from teacherinfo
K.類型轉換
Select cast()
Select cast(‘12’ as int) +1
Select convert(int, ‘12’)+1 //convert類型轉換有風格選項
1.2.2多表查詢
多表查詢中,有以下5種查詢方式:內連,左連,右連,笛卡爾積,嵌套
A.取兩表的交集,內連 inner join
select * from student inner join SC on student.S#=sc.s#
B.以左表為主,左連 left join
select * from student left join SC on student.S#=sc.s#
C.以右表為主,右連 right join
select * from student right join SC on student.S#=sc.s#
D.將兩表行乘積,即笛卡爾積
select * from student,sc where student.S#=sc.s#
E.嵌套查詢
如:查詢"01"課程比"02"課程成績高的學生的信息及課程分數
select * from
(select *,(select score from sc where c#='01' and student.s#=sc.s#) sc01,
(select score from sc where c#='02' and student.s#=sc.s#) sc02 from student) a
where sc01>sc02
嵌套查詢中有帶in謂詞的子查詢,比較運算符的子查詢,帶有any或or的謂詞子查詢,exists謂詞子查詢,還有集合查詢的union,這裡就不一一舉例了。
1.3SQL語句之操作功能
此項內容主要包括數據的插入、修改及刪除。
插入:
Insert into 表名 values(值,值)
如:
insert into studentinfo values(5,'ee','男',35)
insert into studentinfo(number, age) values(6,60)
修改:
Update 表名 set 列名= 值
如:
update studentinfo set age=60 where number=6
update studentinfo set name='gg',sex='女' where number=6
刪除:
Delete from 表名
delete from studentinfo where number=6;
2.數據庫入門之建表規范
若表與表的關系為n:n時,創建的第三個表的主鍵為兩個表的組合主鍵;若關系為1:n時,則把1的主鍵放到n裡
第一范式:
表的任一屬性不可拆分,也就是說每一個表中的一列屬性下不允許再分出幾個屬性
第二范式:
候選鍵(非主鍵)對組合主鍵部分依賴不允許
如:(學號,課程號)[組合主鍵](成績,學生姓名)
成績依賴於組合主鍵,而學生姓名只依賴於學號,產生了部分依賴,不符合第二范式
第三范式:
不允許有傳遞依賴。
舉個例子:
(學號)((姓名,年齡,學校,學校地址)
學生姓名,年齡,在哪一所學校都依賴於學號,而學校地址是不依賴於學生的學號的,無論學生學號是多少,學校就在那裡,而學校地址依賴於學校是哪所,產生了傳遞依賴,所以不符合第三范式。
2.數據庫入門之存儲過程
說道存儲過程,我認為可以這樣去理解,在SQL語句中,為了避免多次進行相同的操作,將這樣相同操作的語句進行一次封裝的過程,相當於函數的作用吧。
那麼在說存儲過程前,就先來看一些語法規則吧:
定義變量:(局部變量用1個@,全局變量用2個@)
Declare @變量名 變量的類型,@變量名 變量的類型
如:
declare @sum int,@i int
變量賦值:
Set:只能給一個變量進行賦值;select:可以給多個變量進行賦值
如:
select @sum=1,@i=9
打印變量:
Print @變量名
條件:
If 條件
Begin 語句塊
End
Else
Begin 語句塊
End
如:
declare @number int,@size int
select @number=100,@size=200
if @number<50
begin
print @number
end
else
begin
print @size
end
循環:(循環語句中只有while,死循環即為while 1=1)
While 條件
Begin 語句塊
End
如:
declare @sum int ,@i int
select @sum=1,@i=9
while @i>0
begin
set @sum=(@sum+1)*2
set @i=@i-1
end
print @sum
下面我們就來看看存儲過程吧
創建存儲過程:
Create procedure 存儲過程名 @變量名 變量類型,@變量名 變量類型
As
Begin
語句塊
End
如:
alter procedure monkey @sum int , @i int , @value int output
as
begin
--declare @sum int,@i int
--select @sum=1,@i=9
while @i>0
begin
set @sum=(@sum+1)*2
set @i=@i-1
end
--print @sum
--return @sum
set @value=@sum
end
--print的
execute monkey 1,9 //執行存儲過程的語句
--return的
declare @sum int
execute @sum=monkey 1,9 //執行存儲過程的語句
print @sum
--output的
declare @sum int
execute monkey 1,9,@sum output //執行存儲過程的語句
print @sum
刪除存儲過程:
Drop procedure 存儲過程名
3.數據庫入門之觸發器,游標,臨時表,復制表
3.1觸發器
觸發器是一種特殊的存儲過程,當指定的事件發生時,自動調用,用白話的理解就是比如說有學生表還有選課表,那麼學號為一號的同學刪除時,選課表中還存有一號同學的選課信息等需要一同刪除,而觸發器則能完成這一功能。
觸發器的種類有3種:插入、修改、刪除觸發器。在創建觸發器時同時會創建兩個表,inserted和deleted表
刪除:
Create trigger 觸發器名
On 表名
After delete
As
Begin
語句塊
End
如:
create trigger mytrigger
on student
after delete
as
begin
declare @s# varchar(3)
set @s#=(select s# from deleted)
delete from sc where s#=@s#
end
delete from student where s#='01'
插入:
Create trigger 觸發器名
On 表名
After insert
As
Begin
語句塊
End
如:
create trigger myinsert
on student
after insert
as
begin
declare @s# varchar(3)
set @s#=(select s# from inserted)
insert into SC values(@s# , '01' , 80)
insert into SC values(@s# , '02' , 90)
insert into SC values(@s# , '03' , 99)
end
insert into student values('01' , '趙雷' , '1990-01-01' , '男')
修改:
Create trigger 觸發器名
On 表名
After update
As
Begin
語句塊
End
如:
create trigger myupdate
on student
after update
as
begin
declare @s#old varchar(3),@s#new varchar(3)
select @s#old=s# from deleted
select @s#new=s# from inserted
update sc set s#=@s#new where s#=@s#old
end
update student set s#='01' where s#='11'
3.2游標
游標即為處理數據的一種方法,對結果集進行一行或多行的浏覽,可以把它想象成為指針來用。
游標的創建需要經歷5個步驟:定義游標,打開游標,遍歷數據,關閉游標,釋放內存。
如:
--定義游標,打開游標,遍歷游標,關閉游標,釋放內存
declare @s# varchar(10),@sname varchar(50)
--定義游標
declare mycursor cursor for
select s#,sname from student
--打開游標
open mycursor
--遍歷游標
fetch next from mycursor into @s#,@sname
while @@FETCH_STATUS=0 //fetch函數的返回值,為0時有值
begin
print @s#+'---------'+@sname
fetch next from mycursor into @s#,@sname
end
--關閉游標
close mycursor
--釋放內存
deallocate mycursor
3.3創建臨時表
Create table #臨時表名---------------創建
Drop table #臨時表名---------------刪除
3.4復制表
不存在的表:
Select * into 新起的一個不存在的表名 from 已有的表名
如:
Select * into aaa from student
存在的表:
Insert into 表名(存在的表名) 內容
如:
Insert into aaa select * from student
以上是我認為初學數據庫時應該會用到的一些知識點,僅供大家參考,如果有說的不對的地方,還望多多指教。