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

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

2、 webserver -創(chuàng)建數(shù)據(jù)庫drop database webserver -刪除數(shù)據(jù)庫create database stusystem -創(chuàng)建數(shù)據(jù)庫use stusystem -打開數(shù)據(jù)庫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 成績總表 as select.where.with check option -創(chuàng)建視圖(虛表 drop view 成績總表 -刪除視圖*/-數(shù)據(jù)控制語言: DCL(grant,revoke/*-權(quán)限設置: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 學生成績管理系統(tǒng) -打開數(shù)據(jù)庫-select * from 學生表A /* 檢索語句 */-select 學號,姓名,性別 from 學生表A -投影操作(針對列-where 性別='女' -選擇操作(針

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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