數(shù)據(jù)庫(kù)命令大全-

1、數(shù)據(jù)庫(kù)命令大全2008-11-13 20:35-載入語句:用于轉(zhuǎn)存數(shù)據(jù)庫(kù)/*EXEC sp_attach_db dbname = '學(xué)生成績(jī)管理系統(tǒng)',filename1 = 'E:Microsoft SQL ServerMSSQL$WEBDATASERVERData學(xué)生成績(jī)管理系統(tǒng)_Data.mdf',filename2 = 'E:Microsoft SQL ServerMSSQL$WEBDATASERVERData學(xué)生成績(jī)管理系統(tǒng)_Log.ldf'*/-數(shù)據(jù)定義語言:DDL(create,alter,drop/*create database

2、 webserver -創(chuàng)建數(shù)據(jù)庫(kù)drop database webserver -刪除數(shù)據(jù)庫(kù)create database stusystem -創(chuàng)建數(shù)據(jù)庫(kù)use stusystem -打開數(shù)據(jù)庫(kù)create table student(sno nvarchar(12primary key -創(chuàng)建基本表create table course(cno nvarchar(12primary key -創(chuàng)建基本表create table SC( -創(chuàng)建基本表sno nvarchar(12,cno nvarchar(12 default'1',grade int,primary ke

3、y(sno,cno,foreign key(snoreferences student(sno,foreign key(cnoreferences course(cno,check(grade between 0 and 100-修改基本表alter table student add sname nvarchar(50,sage nvarchar(12-增加新列alter table student alter column sage int -修改列定義alter table student drop column sage -刪除列create table age(sage int-創(chuàng)建

4、表 drop table age -刪除表truncate table sc-刪除表中所有行(=delete from sccreate unique index scindex on SC(sno asc,cno desc-按sno升序,cno降序建立唯一索引drop index SC.scindex -刪除索引create view 成績(jī)總表 as select.where.with check option -創(chuàng)建視圖(虛表 drop view 成績(jī)總表 -刪除視圖*/-數(shù)據(jù)控制語言: DCL(grant,revoke/*-權(quán)限設(shè)置:select 、update、insert、delet

5、egrant update(sno,select on student to happy with grant option -給用戶happy 授權(quán)并可傳播該權(quán)限r(nóng)evoke select on student from happy cascade -給用戶happy卸權(quán)*/-數(shù)據(jù)操縱語言:DML(select,update,insert,delete-SELECT-use 學(xué)生成績(jī)管理系統(tǒng) -打開數(shù)據(jù)庫(kù)-select * from 學(xué)生表A /* 檢索語句 */-select 學(xué)號(hào),姓名,性別 from 學(xué)生表A -投影操作(針對(duì)列-where 性別='女' -選擇操作(針

6、對(duì)行-where 姓名='陳真明' -等值查詢-where 姓名 like '陳%' -模糊查詢(右模糊-where 姓名 like '%林%' -模糊查詢(左右模糊-where 姓名 like '%云' -模糊查詢(左模糊-where 姓名 like '%' -模糊查詢(全模糊-where 姓名 like '林%' and 性別='女'-where 姓名 like '陳%' and 性別='女' or 性別='男'-where 姓名

7、like '陳%' and (性別='女' or 性別='男'/*第一題:檢索所有姓“劉”的男生和所有姓名里包含“林”字的女生。*/select 學(xué)號(hào),姓名,性別 from 學(xué)生表Awhere 姓名 like '劉%' and 性別='男'or 姓名 like '%林%' and 性別='女'/*第二題:檢索所有姓“陳”和姓“張”的男生記錄。*/select 學(xué)號(hào),姓名,性別 from 學(xué)生表Awhere (姓名 like '陳%' or 姓名 like '張

8、%' and 性別='男'-聚合函數(shù)-聚合函數(shù)/*select count(* as 成績(jī)總數(shù) from 成績(jī)表 -計(jì)數(shù)select avg(成績(jī) as 總平均 from 成績(jī)表 -求平均值select max(成績(jī) as 最高分 from 成績(jī)表 -求最大值select min(成績(jī) as 最低分 from 成績(jī)表 -求最小值select sum(成績(jī) as 總分?jǐn)?shù) from 成績(jī)表 -求和*/*select count(* as 成績(jī)總條數(shù),avg(成績(jī) as 總平均,max(成績(jī) as 最高分, min(成績(jī) as 最低分,sum(成績(jī) as 總分?jǐn)?shù)from 成績(jī)

9、表*/*第三題:求所有籍貫為“福清”的人數(shù)。*/-select count(* from 學(xué)生表B where 籍貫='福清'/*-排序-select * from 成績(jī)表where 學(xué)期='第一學(xué)期' and 類別='考試科'and 科目編號(hào)='300001'-order by 成績(jī) ASC -升序(默認(rèn)值order by 成績(jī) DESC -降序*/-取前n個(gè)-/*select top 5 * from 學(xué)生表A -top n:前n條order by 性別 DESC,姓名 ASC -多字段排序*/*第四題:檢索第四學(xué)期考試科中成

10、績(jī)排在前10名的成績(jī)記錄。*/*select top 10 * from 成績(jī)表where 學(xué)期='第四學(xué)期' and 類別='考試科'order by 成績(jī) DESC*/*第五題:檢索各學(xué)期考試科目的成績(jī)記錄,要求各學(xué)期內(nèi)的成績(jī)按從高到低排列。*/*select * from 成績(jī)表where 類別='考試科'order by 學(xué)期,成績(jī) DESC*/-分組-/*select 學(xué)期,類別,avg(成績(jī) as 平均成績(jī) from 成績(jī)表group by 學(xué)期,類別 -分組order by 學(xué)期,類別 -排序*/*-統(tǒng)計(jì)各個(gè)班級(jí)的男女生人數(shù)sele

11、ct 班級(jí)編號(hào),性別,count(* as 人數(shù) from 學(xué)生表Agroup by 班級(jí)編號(hào),性別order by 班級(jí)編號(hào)*/-分組條件-查看第五學(xué)期各個(gè)類別各門科目的平均成績(jī)?cè)?0分及-以上的記錄/*select 學(xué)期,類別,科目編號(hào),avg(成績(jī) as 平均分from 成績(jī)表where 學(xué)期='第五學(xué)期'group by 學(xué)期,類別,科目編號(hào)having avg(成績(jī)>=80 -and 學(xué)期='第五學(xué)期'order by 學(xué)期,類別,科目編號(hào)*/*分組條件規(guī)則:當(dāng)條件表達(dá)式有包含聚合函數(shù)時(shí),一般把條件放于having語句中,其它情況一般放于wher

12、e語句中。*/-1、統(tǒng)計(jì)各個(gè)民族各個(gè)籍貫的學(xué)生人數(shù)在2人及以上的人數(shù)信息。-(民族,籍貫,人數(shù)/*select 民族,籍貫,count(* as 學(xué)生人數(shù) from 學(xué)生表Bgroup by 民族,籍貫having count(*>=2order by 民族,籍貫*/-2、統(tǒng)計(jì)第一、二學(xué)期內(nèi)各考查科目的平均分在75分及以上的信息。-(學(xué)期,類別,科目編號(hào),平均分/*select 學(xué)期,類別,科目編號(hào),avg(成績(jī) as 平均分 from 成績(jī)表where (學(xué)期='第一學(xué)期' or 學(xué)期='第二學(xué)期' and 類別='考查科' group

13、by 學(xué)期,類別,科目編號(hào)having avg(成績(jī)>=75order by 學(xué)期,類別,科目編號(hào)*/-3、檢索在第六學(xué)期考試中,平均分排在前3名的相關(guān)信息。-(學(xué)期,平均成績(jī)/*select top 3 avg(成績(jī) as 平均分,學(xué)期 from 成績(jī)表where 學(xué)期='第六學(xué)期'group by 學(xué)期order by avg(成績(jī) desc*/-4、檢索第三學(xué)期中各考試科目最高分在90以上的相關(guān)信息。-(學(xué)期,類別,科目編號(hào),最高分/*select 學(xué)期,類別,科目編號(hào),max(成績(jī) as 最高分 from 成績(jī)表where 學(xué)期='第三學(xué)期' an

14、d 類別='考試科'group by 學(xué)期,類別,科目編號(hào)having max(成績(jī)>=90order by 學(xué)期,類別,科目編號(hào)*/-5、檢索各學(xué)期中各科目的不及格人數(shù)信息。-(學(xué)期,科目編號(hào),不及格人數(shù)/*select 學(xué)期,科目編號(hào),count(* as 不及格人數(shù) from 成績(jī)表where 成績(jī)<60group by 學(xué)期,科目編號(hào)order by 學(xué)期,科目編號(hào)*/-多表查詢- -多表查詢use 學(xué)生成績(jī)管理系統(tǒng)-檢索所有籍貫為南平的姓“林”的學(xué)生記錄/*select 學(xué)生表A.學(xué)號(hào),姓名,性別,籍貫,通信地址from 學(xué)生表A,學(xué)生表Bwhere 學(xué)生

15、表A.學(xué)號(hào)=學(xué)生表B.學(xué)號(hào) -等值條件and 籍貫='南平' and 姓名 like '林%'*/-可視化創(chuàng)建視圖- /*select * from 學(xué)生表 -學(xué)生表:視圖where 性別='男'*/-代碼創(chuàng)建視圖- /*create view 成績(jī)總表 -創(chuàng)建視圖asselect 學(xué)生表.學(xué)號(hào),姓名,性別,通信地址,學(xué)期,科目名稱,類別,成績(jī)from 學(xué)生表,科目表,成績(jī)表where 學(xué)生表.學(xué)號(hào)=成績(jī)表.學(xué)號(hào)and 科目表.科目編號(hào)=成績(jī)表.科目編號(hào)*/-檢索第一學(xué)期英語科目不及格的女生的學(xué)號(hào)、姓名、性別、-通信地址、學(xué)期、科目名稱、類別、成

16、績(jī)/*select * from 成績(jī)總表where 學(xué)期='第一學(xué)期' and 科目名稱='英語'and 性別='女' and 成績(jī)<60*/-成績(jī)總表-視圖查詢-6、檢索各學(xué)期內(nèi)所有英語有補(bǔ)考的女生記錄/*select * from 成績(jī)總表where 科目名稱='英語' and 性別='女' and 成績(jī)<60*/-7、統(tǒng)計(jì)各學(xué)期內(nèi)各考試科目的最高分、最低分和平均分。/*select 學(xué)期,科目名稱,max(成績(jī) as 最高分,min(成績(jī) as 最低分,avg(成績(jī) as 平均分from 成績(jī)總

17、表where 類別='考試科'group by 學(xué)期,科目名稱order by 學(xué)期,科目名稱-8、統(tǒng)計(jì)各英語考試中男、女生的平均分。/*select 學(xué)期,科目名稱,性別,avg(成績(jī) as 平均分from 成績(jī)總表where 科目名稱='英語'group by 學(xué)期,科目名稱,性別order by 學(xué)期,科目名稱,性別*/-9、列出所有英語科目有過補(bǔ)考的男生姓名/*select distinct 姓名 from 成績(jī)總表where 科目名稱='英語' and 成績(jī)<60 and 性別='男'*/*-同解select 姓名

18、 from 成績(jī)總表where 科目名稱='英語' and 成績(jī)<60 and 性別='男'group by 姓名*/-10、列出補(bǔ)考次數(shù)最多的學(xué)生的學(xué)號(hào)、姓名、性別、補(bǔ)考次數(shù)。/*select top 1 學(xué)號(hào),姓名,性別,count(* as 補(bǔ)考次數(shù)from 成績(jī)總表where 成績(jī)<60group by 學(xué)號(hào),姓名,性別order by count(* DESC*/-子查詢- -子查詢-檢索所有姓“林”的學(xué)生的成績(jī)記錄。/*select * from 成績(jī)表where 學(xué)號(hào) in(select 學(xué)號(hào) from 學(xué)生表Awhere 姓名 lik

19、e '林%'*/-創(chuàng)建字段select *, 成績(jī)+30 as 附加分 from 成績(jī)表where 學(xué)號(hào) in(select 學(xué)號(hào) from 學(xué)生表Awhere 姓名 like '林%'*/-INSERT,DELETE,UPDATE-insert into <表名> (<屬性列1>,<屬性列2>. values(<常量1>,<常量2>. delete from <表名> where <條件>update <表名> set <列名>=<表達(dá)式>,

20、<列名>=<表達(dá)式>.where <條件>use 學(xué)生成績(jī)管理系統(tǒng)-select * into 學(xué)生表C from 學(xué)生表 -創(chuàng)建新表-select * into 成績(jī)表C from 成績(jī)總表 -創(chuàng)建新表-insert into 學(xué)生表C(學(xué)號(hào),姓名,性別 values('1','張三','男' -插入操作-insert into 學(xué)生表C values('2','李四','女','南平','' -插入操作-delete from

21、學(xué)生表C where 學(xué)號(hào)='1' -刪除操作-delete from 學(xué)生表C where 學(xué)號(hào) in('1','2' -刪除操作-update 學(xué)生表C set 性別='男' where 姓名='李小蘋'-更新操作-select * from 學(xué)生表C/*update 成績(jī)表C set 成績(jī)=成績(jī)+10where 學(xué)期='第二學(xué)期' and 性別='女' and 科目名稱='英語'and 姓名='薛明真'select * from 成績(jī)表Cwher

22、e 學(xué)期='第二學(xué)期' and 性別='女' and 科目名稱='英語'and 姓名='薛明真'*/-1、將第一學(xué)期里所有女生的英語成績(jī)加20分。-(要求最高分不能超過100分/*update 成績(jī)表C set 成績(jī)=成績(jī)+20where 學(xué)期='第一學(xué)期' and 性別='女' and 科目名稱='英語'update 成績(jī)表C set 成績(jī)=100where 學(xué)期='第一學(xué)期' and 性別='女' and 科目名稱='英語'and

23、成績(jī)>100select * from 成績(jī)表Cwhere 學(xué)期='第一學(xué)期' and 性別='女' and 科目名稱='英語'*/-自身連接-查詢每一門課的間接選修課select T1.Cno,T2.Cpno from Course T1,course T2where T1.Cpno=T2.Cno -(本例中,需要為Course表取兩個(gè)別名.比如,一個(gè)用T1,另一個(gè)用T2-并操作UNION的使用-查詢計(jì)算機(jī)科學(xué)系的學(xué)生及年齡不大于19歲的學(xué)生select * from Student where Sdept='CS'UNI

24、ONselect * from student where Sage<=19-(用union合并起來的,系統(tǒng)會(huì)自動(dòng)去掉重復(fù)元組 -事務(wù)控制/*事務(wù):1.自動(dòng)提交事務(wù)2.顯式事務(wù)(begin mit3.set implicit_transactions on-打開隱式事務(wù)set implicit_transactions off-關(guān)閉隱式事務(wù)4.save transaction abc 設(shè)置保存點(diǎn)abc*/declare k int -定義局部變量 -全局變量-set k=0 -給變量賦值begin transactionupdate 成績(jī)表C set 成績(jī)=成績(jī)-kwhere 學(xué)號(hào)='0404004' and 學(xué)期='第一學(xué)期' and 科目名稱='英語'if(rowcount=0rollback transaction -事務(wù)回滾elseupdate 成績(jī)表C set 成績(jī)=成績(jī)+kwhere 學(xué)號(hào)='0404005' and 學(xué)期='第一學(xué)期' and 科目名稱='英語'if(rowcount=0rollback transaction -事務(wù)回滾elsecommit transaction -提交事務(wù)select * fro