SQL複習架構

標籤：des   io   使用   ar   java   for   strong   檔案   資料   

資料庫系統

1. 資料：date描述事物的符號 ，可以出現多種載體，比如：聲音、圖片、文字等
2. 資料庫：database（簡寫db）資料的倉庫    在電腦內可以長期儲存，並且可以共用的資料的集合
3. 資料庫管理系統:（DBMS）管理資料庫的一些軟體 sql 2008   mysql  oracle  DB2
4. 資料庫系統：包含資料庫管理系統和管理員 （DBS）

 

（第一部分）:資料庫

        1：系統資料庫

n  Master：記錄系統資訊sys開頭

n  Msdb：代理資料庫發出作業警報

n  Tempdb：臨時資料庫

n  Mode模型資料庫l

n  注意事項：

n  資料庫管理系統以資料模型為核心：網狀模型  層次模型  關聯式模式

            2.使用者自訂資料庫

建立資料庫	查看資料庫	修改資料庫	刪除資料庫
Create database dbname	Exec sp_helpdb（查看所有資料庫）  Exec       sp_helpdb dbname(查看具體的某一個資料庫)	Exec sp_renamedb ‘oldname’ ‘newname’(修改名稱) Alter database dbname(修改資料庫)	Drop database dbname
注意事項：資料檔案（首要：mdf次要：ndf）和交易記錄檔（ldf）
執行個體:	//(1)建立學生資料庫student Create database student //(2)使用當前資料庫student Use student //(3)查看學生資料庫student的資訊 Exec sp_helpdb student //(4)修改學生資料庫student名稱為stu Exec sp_renamedb ’student’,’ stu’ //(5)刪除學生資料庫stu Drop database stu

               

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

(第二部分)：表

1. 表結構

資料類型	(1)       整型tinyint  int smallint bigint (2)       實型float real   numeric decimal (3)       字元型char(固定位元組) varchar（可變位元組） text (1)       日期與時間類型datetime
建立表	查看錶結構	修改表結構	刪除表結構
Create table studentinfor( Son int, Ssname varchar(20), Ssex varchar(20), Ssage int )	Exec sp_help studentinfor	Alter tablename columnname 增加欄位 Add 欄位名 type 刪除欄位 Drop column 欄位名  修改列名 Exec rename ‘stuno’,’snum’ 修改表名 Exec sp_rename  修改屬性 Alter column sno varchar(20)	Drop studentinfor
執行個體	//(1)建立學生資訊表studentinfor (包含學號sno 姓名sname 性別ssex 年齡sage) //(2)查看錶結構 //(3)修改表 n  增加欄位籍貫snative  學院scollege n  刪除學段年齡 n  修改欄位年齡sage為stuage n  修改欄位性性學號sno 整型為字元型 //(4)刪除表結構

 

1. 資料表條件約束

主鍵:一個表中只能有一個，欄位可以有多個，主索引值不可為空 (primary key)	外鍵：一個表中可以有多個，參考的那個欄位必須是主鍵  外鍵所在的表叫做外鍵表（從表） (foreign key)	唯一：只可以出現一次空 (unique)	預設：可以有多個 (default)	空/非空 (null/not null	檢查 (check) 一個表可以有多個
識別欄位 identity	只適用於數實值型別，預設起始值是1，增量也是1
	建立學生資訊表stuinfor                                                                 學號         姓名         性別         年齡         電話         學院         sno         sname         ssex         sage         sphone         scollege       建立學生課程表cinfor                                 課程號         課程名         cno         cname       建立學產生績表scinfor                                         學號         課程號         成績         sno         cno         scroe
執行個體 (建立表 後添加)	Create table stuinfor (sno int, Sname varchar(20), Ssex varchar(20), Sage int, Sphone varchar(20), Scollege varchar(20)) Alter table stuinfor Add constraint zj_1 primary key(sno) Alter column sname int not null Add constraint  jc_1  check(ssex=’男’ or ssex=’女’) Add constraint jc_2 check(sage between 18 and 50) Add constraint wy_1 unique(sphone) Add constraint df_1 default ‘軟工’ for scollege
執行個體 (建立表 同時添加)	Create table stuinfor( Sno int primary key, Sname varchar(20) not null, Ssex varcar(20) check(ssex=‘男‘ or ssex=‘‘女) default ‘女‘, Sage varchar(20) check (sage between 20 and 30), Sphone varchar(20)  unique, Scollege varchar(20) default ‘軟工‘ ) Create table cinfor( Cno int primary key, Cname varchar(20) not null ) Create table scinfor( Sno int foreign key (sno) references primary key  stuinfor(sno), Cno int foreign key(cno) references primary key cinfor(cno), Scroe int check(check between 0 and 100) )

 

1. 表操作

添加insert    into	n  完全添加 insert into 表名 values(值，值,....) n  部分添加 insert into 表名(欄位,欄位,...) values(值,值,...) n  一次添加多條 insert into 表名(欄位,欄位,...) values(值,值,...),(值,值,..) n  簡寫格式 insert 表 values(值,值,..) //(1)將1,張三,男,22,13245673452,軟工添加到stuinfor insert into stuinfor values(1,‘張三‘,‘男‘,22,‘13245673452‘,default) //(2)將2,李四,男添加至stuinfor Insert inton stuinfor(sno,sname,ssex) values(2,‘李四‘,‘男‘) //(3)將1,HTML  2.JAVA    3JAVASCRIP添到cinfor Insert into cinfor values(1,‘HTML‘),(2,‘java‘),(3,‘javascript‘) //(4)將1,1,68添加至scinfor(簡寫格式) Insert  scinfor   values(1,1,68)
更改 updae	n  表中全部記錄的一個或多個欄位更改      update 表 set 欄位=值,欄位=值  update stuinfor set sage=sage+1 n  表中滿足條件記錄的一個或多欄位更改 update 表 set 欄位=值,欄位=值  where 條件(and or...) n  以空為條件的更改 update 表 set 欄位=值  where 欄位 is null   //(1)將stunfor表中全部學生的學院改為環境 Update stuinfor set scolege=‘環境‘ //(2)將性別為男的學生的學院改為軟工，年齡增加一歲 Update stuinfor set scolege=‘軟工‘ ,sage=sage+1 where ssex=‘男‘ //(3)將所有學院為空白的學生記錄的年齡改為20 Update stuinfor set sage=20 where scollege is null
刪除 delete	n  刪除滿足條件的記錄 delete  from stuinfor  where 條件 n  以空為條件的刪除 delete from 表 where 欄位 is null n  刪除簡寫格式 delete 表 n  刪除表中全部記錄 delete from 表 //(1)將所有廣告學院女同學的資訊刪除 Delete from stuinfor where scollege=‘廣告‘ and SSEX=‘女‘ //(2)將所有電話為空白的記錄刪除 delete from stuinfor where sphone is null //(3)刪除表中的全部記錄 delete from stuinfor
查詢 select	(一) 單表查詢 n  查詢表中全部記錄 select * from 表 n  查詢表中指定欄位記錄 select 欄位,欄位,...from   表 n  查詢前n條記錄 select top n  percent * from 表 n  帶條件的查詢 select * from 表 where 條件 n  查詢去掉重複項 select distinct 欄位 from 表 n  查詢使用彙總函式(並起別名)        max() sum()   min() avg() count()        select A=B from 表        select B a from 表        select B as a from 表 n  模糊查詢 like  _單字元 %一個或多個字元 [] 範圍內 [^ ]不在範圍 n  查詢時對欄位排序 order by (asc 升 desc 降) n  合并查詢結果  union n  查詢建立新表         select 欄位,欄位 into 新表名 from 表 n  查詢使用分組命令. select * from stuinfor --統計女學生的平均年齡 select AVG(stuage )from stuinfor where ssex=‘女‘ --統計男學生的平均年齡 select AVG(stuage )from stuinfor where ssex=‘男‘ --分別統計男女學生的平均年齡 select stusex,AVG(stuage) from stuinfor group by stusex   (二) 多表查詢 n  交叉串連 select * from stuinfor,cinfor,scinfor n  內串連(正確) select 欄位,欄位 from 表,表 where 表.欄位=表.欄位 select 欄位,欄位 from 表 inner join   表 on 表.欄位=表.欄位 and ... n  外串連outer join l  左外串連 (LEFT join)  左表全顯右表滿足顯   不滿足以null顯示 l  右外串連 (right join)右表全顯左表滿足顯 不滿足以null顯示 l  完全串連 (full join)左+右 //(1)查詢學生資訊表中的全部記錄 select * from stuinfor //(2)查詢學生資訊表中學生的學號，姓名，學院 Select sno,sname,scollege from stuinfor //(3)查詢學生資訊表中軟工或動畫學院學生資訊 Select * from stuinfor where scollege=‘軟工‘ or scollege=‘動畫‘ select * from stuinfor where scollege in(‘軟工‘,‘動畫‘) //(4)查詢學生資訊表中年齡在23—27之間學生資訊 Select * from stuinfor where sage between 23 and 27 select * from stuinfor where sage>=23 and sage<=27 //(5)查詢學生資訊表中年齡>28並且是軟工的學生資訊 Select * from stuinfor where sage<28 and scollege=‘軟工‘ //(6)查詢學生資訊表中前五條記錄的學號，姓名，性別 Select top 5 sno,sname,ssex from stuinfor //(7)查詢學生資訊表中學生來自哪些學院(幾個) Select scollege from stuinfor (select count(distinct scollege )from stuinfor) //(8)查詢學生資訊表中學生的最大年齡及最小年齡 Select max(sage) 最大值 ,min(sage)   最小值 from stuinfor //(9)查詢成績表中選修1號課程的總分及平均分 Select sum(score) ,avg(score) from scinfor where cno=1 //(10)查詢成績表中成績合格的個數 Select count(score) from scinfor where score>=60 //(11)按由高到低的順序查詢學產生績表中的學生資訊 Select * from scinfor order by score desc //(12)查詢姓李的學生資訊 Select * from stuinfor where sname like ‘%李‘ //(13)分別統計每個學院的平均年齡 Select avg(sage) from stuinfor group by scollege //(14)查詢學生的學號，姓名，學院建立一個新表n1 Select sno,sname,scollege into n1 from stuinfor //(15)查詢所有選修了Java課程的學生的學號，姓名，性別，學院及成績 select stuinfor.sno,sname,ssex,scollege,scroe  from stuinfor,cinfor,scinfor where scinfor.sno=stuinfor.sno and scinfor.cno=cinfor.cno  and cname=‘java‘     select stuinfor.sno,sname,ssex,scollege,scroe  from stuinfor inner join scinfor  on stuinfor.sno =scinfor.sno inner join  cinfor on scinfor.cno=cinfor.cno and cname=‘java‘   //(16)查詢所有學生的選課情況 select * from stuinfor left join scinfor on stuinfor.sno=scinfor.sno

 

(第三部分)T-SQL

1. 基本概念

n  T-SQL

DDL  DCL   DML   流程式控制制語句

n  變數的聲明，賦值，計算，輸出

n  函數

分類	函數名	作用
彙總函式
數學函數
字串函數
日期與時間函數
轉換函式

 

1. 流程式控制制語句

n  If..else

n  Case

n  While

n  Begin…end

n  Return

n  Goto

n  waitfor

 

(第四部分)視圖

建立視圖	查看視圖	修改視圖	刪除視圖
create view view_1 as select * from   select * from sysobjects where type=‘v‘

 

(第五部分)資料庫的安全性

n  分離與附加加

n  備份與還原

n  修改身分識別驗證模式

SQL複習架構