數(shù)據(jù)庫實驗-電子科技大學

計算機專業(yè)類課程實驗報告課程名稱：數(shù)據(jù)庫系統(tǒng)及應用學院：計算機科學與工程專業(yè)：計算機科學與技術學生姓名：李鶴鳴指導教師：鄭莉華日期：2016年4月24日

電子科技大學實驗報告實驗一實驗名稱：創(chuàng)立，備份與恢復數(shù)據(jù)庫實驗學時：2實驗內(nèi)容和目的：本實驗要求學生掌握創(chuàng)立數(shù)據(jù)庫的方法及相關操作，向數(shù)據(jù)庫中添加樣本數(shù)據(jù)，學習SQLSERVER數(shù)據(jù)庫的恢復和備份。創(chuàng)立數(shù)據(jù)庫，命名為DB+學號，如：DB000000001該數(shù)據(jù)庫包括5個表：建議：在定義表中字段時，盡量使用意義的英文單詞?！祫e代碼表’ 表名：dep‘教師表’ 表名：teacher‘學生表’ 表名：stud‘課程表’ 表名：course‘選課表’ 表名：sc為每個表準備大約5-10條記錄，使用Insert語句將這些數(shù)據(jù)插入到相應表中數(shù)據(jù)錄入完成后，將數(shù)據(jù)庫備份到磁盤上，在以后的的試驗中備用。實驗原理：使用數(shù)據(jù)庫管理系統(tǒng)DB、DDL創(chuàng)立數(shù)據(jù)庫及數(shù)據(jù)庫對象。實驗器材〔設備、元器件〕計算機，MicrosoftSQLsever2014實驗步驟：啟動SQLSERVER創(chuàng)立數(shù)據(jù)庫：STUD創(chuàng)立表‘系別代碼表’表名：dep其結構如下：〔該表的主鍵為’系代碼’〕字段名稱字段類型字段大小/格式是否可為空系代碼depidvarchar8否PK系名depnamevarchar20否Notnull‘教師表’表名：teacher其結構如下：〔該表的主鍵為’教師號’〕字段名稱字段類型字段大小/格式是否可為空教師號tidVarchar8否PK教師名tnameVarchar8否Notnull職稱titleVarchar10是所屬院系編號depidVarchar20是 ‘學生表’ 表名：student 其結構如下：〔該表的主鍵為’學生號’〕字段名稱字段類型字段大小/格式是否可為空學號sidVarchar11否PK學生名snameVarchar8否Notnull性別sexChar2否院系編號depidVarchar20是出生年月birthdDate是郵箱semailVarchar20是家庭地址homeaddrVarchar40是‘課程表’表名：course其結構如下：〔該表的主鍵是課程號〕字段名稱字段類型字段大小/格式是否可為空課程號cidvarchar8否PK課程名cnamevarchar30否Notnull先修課程號cid_prevarchar8是學分creditsnumeric3(小數(shù)位數(shù)1)否Notnull‘選課表’表名：sc其結構如下：〔該表的主鍵是課程號〕字段名稱字段類型字段大小/格式是否可為空學號sidvarchar8否Notnull,PK課程號cidvarchar8否Notnull,PK教師號tidvarchar8否成績scoreinteger是備份數(shù)據(jù)庫恢復數(shù)據(jù)庫PK:主鍵，Notnull:不能取空值實驗數(shù)據(jù)及結果分析：Createtabledep(depidvarchar(8)primarykey,depnamevarchar(20)notnull);Createtableteacher(tidvarchar(8)primarykey,tnamevarchar(8)notnull,titlevarchar(10),depidvarchar(20));Createtablestudent(sidvarchar(8)primarykey,snamevarchar(8)notnull,sexchar(2)notnull,depidvarchar(20),birthddate,semailvarchar(20),homeaddrvarchar(40));Createtablecourse(cidvarchar(8)primarykey,cnamevarchar(30)notnull,cid_prevarchar(8),creditsnumeric(3,1)notnull);Createtablesc(sidvarchar(8)notnull,cidvarchar(8)notnullprimarykey,tidvarchar(8)notnull,scoreinteger);insertintodep(depid,depname)values('01','計算機');insertintodep(depid,depname)values('02','信軟');insertintodep(depid,depname)values('03','通信');insertintodep(depid,depname)values('04','電工');insertintodep(depid,depname)values('05','自動化');insertintoteacher(tid,tname,title,depid)values('200601','劉琦','講師','03');insertintoteacher(tid,tname,title,depid)values('200602','林閑','講師','02');insertintoteacher(tid,tname,title,depid)values('200605','李遷','教授','01');insertintoteacher(tid,tname,title,depid)values('200604','曹薇','講師','04');insertintoteacher(tid,tname,title,depid)values('200608','孫峰','講師','05');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201401','張澄','男','01','1994.10.15','13456289@163','江蘇省南京市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201402','明青云','男','02','1996.1.11','465187@163','河北省邯鄲市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201403','孔玲瓏','女','03','1995.6.25','97644163@163','福建省泉州市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201404','雷破','男','04','1994.2.14','4941647@163','廣東省深圳市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201405','李肅','男','05','1994.5.26','8794164@163','湖北省武漢市');insertintocourse(cid,cname,cid_pre,credits)values('00101','離散數(shù)學','10001','2');insertintocourse(cid,cname,cid_pre,credits)values('00102','大學物理下','00100','2');insertintocourse(cid,cname,cid_pre,credits)values('00103','數(shù)據(jù)結構','10000','3');insertintocourse(cid,cname,cid_pre,credits)values('00104','概率論','10002','4');insertintocourse(cid,cname,cid_pre,credits)values('00105','計算機導論','00010','1');insertintosc(sid,cid,tid,score)values('201401','00101','200601','80');insertintosc(sid,cid,tid,score)values('201402','00102','200604','76');insertintosc(sid,cid,tid,score)values('201403','00103','200605','80');insertintosc(sid,cid,tid,score)values('201404','00104','200608','84');insertintosc(sid,cid,tid,score)values('201405','00105','200602','86');實驗結論、心得體會和改良建議：按步驟進行試驗，本實驗相對簡單。

電子科技大學實驗報告實驗二實驗名稱：數(shù)據(jù)庫的完整性實驗學時：4實驗內(nèi)容和目的：通過設置表的檢查約束、外鍵約束體會數(shù)據(jù)庫完整性的含義，約束條件下數(shù)據(jù)修改操作的限制，以及實現(xiàn)修改操作的技巧。表結構中的完整性約束條件如下：PK:主鍵，F(xiàn)K:外鍵，其它::約束條件‘系別代碼表’表名：dep其結構如下：〔該表的主鍵為’系代碼’〕字段名稱字段類型字段大小/格式是否可為空約束條件系代碼depidvarchar8否PK系名depnamevarchar20否Notnull‘教師表’表名：teacher其結構如下：〔該表的主鍵為’教師號’〕字段名稱字段類型字段大小/格式是否可為空約束條件教師號tidVarchar8否PK教師名tnameVarchar8否Notnull職稱titleVarchar10是所屬院系編號depidVarchar20是 ‘學生表’ 表名：student 其結構如下：〔該表的主鍵為’學生號’〕字段名稱字段類型字段大小/格式是否可為空約束條件學號sidVarchar11否PK學生名snameVarchar8否Notnull性別sexChar2否院系編號depidVarchar20是FK出生年月birthdDate是郵箱semailVarchar20是家庭地址homeaddrVarchar40是‘課程表’表名：course其結構如下：〔該表的主鍵是課程號〕字段名稱字段類型字段大小/格式是否可為空約束條件課程號cidvarchar8否PK課程名cnamevarchar30否Notnull先修課程號cid_prevarchar8是學分creditsnumeric3(小數(shù)位數(shù)1)否Notnull‘選課表’表名：sc其結構如下：〔該表的主鍵是課程號〕字段名稱字段類型字段大小/格式是否可為空約束條件學號sidvarchar8否Notnull,PK,FK課程號cidvarchar8否Notnull,PK,FK教師號tidvarchar8否Notnull,FK成績scoreinteger是0<score<100設置選課表的三個外鍵約束〔學號，課程號，教師號〕設置教師表，學生表中的院系字段(depid)的外鍵約束設置學生表中姓名字段為非空字段〔必須有數(shù)據(jù)，不能是空值〕設置選課表中成績字段的取值范圍是0到100設置學生表中性別字段的取值為’男’或’女’設置學生表電子郵件字段的取值必須包含@符號向’系別代碼表’添加數(shù)據(jù)，數(shù)據(jù)如下：向’教師表’添加數(shù)據(jù),2條向’學生表’添加數(shù)據(jù),5條以上向’課程表’添加數(shù)據(jù),3條以上向’選課表’添加數(shù)據(jù),10條實驗原理：數(shù)據(jù)庫庫的完整性、約束條件、結構化查詢語言。實驗器材〔設備、元器件〕計算機，MicrosoftSQLsever2014.實驗步驟：恢復數(shù)據(jù)庫，將數(shù)據(jù)庫備份stud恢復到SQLSERVER中執(zhí)行SQL命令完成實驗內(nèi)容備份數(shù)據(jù)庫實驗數(shù)據(jù)及結果分析：Createtabledep(depidvarchar(20)primarykey,depnamevarchar(20)notnull);Createtableteacher(tidvarchar(8)primarykey,tnamevarchar(8)notnull,titlevarchar(10),depidvarchar(20),foreignkey(depid)referencesdep(depid));Createtablestudent(sidvarchar(8)primarykey,snamevarchar(8)notnull,sexchar(2)notnull,depidvarchar(20),birthddate,semailvarchar(20),homeaddrvarchar(40),check(sexin('男','女')),check(semaillike'%@%'));Createtablecourse(cidvarchar(8)primarykey,cnamevarchar(30)notnull,cid_prevarchar(8),creditsnumeric(3,1)notnull);Createtablesc(sidvarchar(8)notnull,cidvarchar(8)notnull,tidvarchar(8)notnull,scoreinteger,check(scorebetween0and100),foreignkey(sid)referencesstudent(sid),foreignkey(cid)referencescourse(cid),foreignkey(tid)referencesteacher(tid));insertintodep(depid,depname)values('01','計算機');insertintodep(depid,depname)values('02','信軟');insertintodep(depid,depname)values('03','通信');insertintodep(depid,depname)values('04','電工');insertintodep(depid,depname)values('05','自動化');insertintoteacher(tid,tname,title,depid)values('200605','李遷','教授','01');insertintoteacher(tid,tname,title,depid)values('200604','曹薇','講師','02');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201401','張澄','男','01','1994.10.15','13456289@163','江蘇省南京市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201402','明青云','男','02','1996.1.11','465187@163','河北省邯鄲市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201403','孔玲瓏','女','03','1995.6.25','97644163@163','福建省泉州市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201404','雷破','男','04','1994.2.14','4941647@163','廣東省深圳市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201405','李肅','男','05','1994.5.26','8794164@163','湖北省武漢市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201406','林清','女','01','1995.5.28','8745156@163','湖北省孝感市');insertintocourse(cid,cname,cid_pre,credits)values('00101','離散數(shù)學','10001','2');insertintocourse(cid,cname,cid_pre,credits)values('00102','大學物理下','00100','2');insertintocourse(cid,cname,cid_pre,credits)values('00103','數(shù)據(jù)結構','10000','3');insertintocourse(cid,cname,cid_pre,credits)values('00104','概率論','10002','4');insertintocourse(cid,cname,cid_pre,credits)values('00105','計算機導論','00010','1');insertintosc(sid,cid,tid,score)values('201401','00101','200605','80');insertintosc(sid,cid,tid,score)values('201402','00102','200604','76');insertintosc(sid,cid,tid,score)values('201403','00103','200605','80');insertintosc(sid,cid,tid,score)values('201404','00104','200604','84');insertintosc(sid,cid,tid,score)values('201405','00105','200604','86');insertintosc(sid,cid,tid,score)values('201401','00102','200604','69');insertintosc(sid,cid,tid,score)values('201402','00101','200605','85');insertintosc(sid,cid,tid,score)values('201403','00104','200604','80');insertintosc(sid,cid,tid,score)values('201404','00103','200605','75');insertintosc(sid,cid,tid,score)values('201405','00101','200605','94');實驗結論、心得體會和改良建議：注意sql語句語法，認真編寫代碼根本就可以順利完成實驗。電子科技大學實驗報告實驗三一實驗名稱：數(shù)據(jù)的修改二實驗學時：2三實驗內(nèi)容和目的：練習UPDATE、DELETE命令的使用，實現(xiàn)對數(shù)據(jù)的修改和刪除。將院系中，原院系名’IS’改為’Information‘在選課表中，刪除計算機科學與工程系學生選修2號課程的記錄在選課表中，刪除軟件工程系學生選課1號課程的紀錄記錄學號為2406010103的同學由原來的計算機科學與工程系轉(zhuǎn)入信息平安系，學號更改為2406030102，在數(shù)據(jù)庫中做出相應修改。四實驗原理：使用結構化查詢語言，在滿足約束條件的情況下完成數(shù)據(jù)修改五實驗器材〔設備、元器件〕計算機，MicrosoftSQLsever六實驗步驟：恢復數(shù)據(jù)庫，將數(shù)據(jù)庫備份stud恢復到SQLSERVER中執(zhí)行SQL命令完成實驗內(nèi)容備份數(shù)據(jù)庫七實驗數(shù)據(jù)及結果分析：Createtabledep(depidvarchar(20)primarykey,depnamevarchar(20)notnull);Createtableteacher(tidvarchar(8)primarykey,tnamevarchar(8)notnull,titlevarchar(10),depidvarchar(20),foreignkey(depid)referencesdep(depid));Createtablestudent(sidvarchar(20)primarykey,snamevarchar(8)notnull,sexchar(2)notnull,depidvarchar(20),birthddate,semailvarchar(20),homeaddrvarchar(40),check(sexin('男','女')),check(semaillike'%@%'));Createtablecourse(cidvarchar(8)primarykey,cnamevarchar(30)notnull,cid_prevarchar(8),creditsnumeric(3,1)notnull);Createtablesc(sidvarchar(20)notnull,cidvarchar(8)notnull,tidvarchar(8)notnull,scoreinteger,check(scorebetween0and100),foreignkey(sid)referencesstudent(sid),foreignkey(cid)referencescourse(cid),foreignkey(tid)referencesteacher(tid));insertintodep(depid,depname)values('01','計算機');insertintodep(depid,depname)values('02','軟件工程');insertintodep(depid,depname)values('03','信息平安');insertintodep(depid,depname)values('04','電工');insertintodep(depid,depname)values('05','自動化');insertintodep(depid,depname)values('06','通信');insertintoteacher(tid,tname,title,depid)values('200605','李遷','教授','01');insertintoteacher(tid,tname,title,depid)values('200604','曹薇','講師','02');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406010101','張澄','男','01','1994.10.15','13456289@163','江蘇省南京市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406020101','明青云','男','02','1996.1.11','465187@163','河北省邯鄲市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406020102','孔玲瓏','女','02','1995.6.25','97644163@163','福建省泉州市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406030101','雷破','男','03','1994.2.14','4941647@163','廣東省深圳市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406040101','李肅','男','04','1994.5.26','8794164@163','湖北省武漢市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406010102','林清','女','01','1995.5.28','8745156@163','湖北省孝感市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406010103','石心遠','男','01','1996.12.30','45161664@163','吉林省長春市');insertintocourse(cid,cname,cid_pre,credits)values('0001','離散數(shù)學','0002','2');insertintocourse(cid,cname,cid_pre,credits)values('0002','大學物理下','0003','2');insertintocourse(cid,cname,cid_pre,credits)values('0003','數(shù)據(jù)結構','0004','3');insertintocourse(cid,cname,cid_pre,credits)values('0004','概率論','0005','4');insertintocourse(cid,cname,cid_pre,credits)values('0005','計算機導論','0001','1');insertintocourse(cid,cname,cid_pre,credits)values('0006','計算機網(wǎng)絡','0007','2');insertintocourse(cid,cname,cid_pre,credits)values('0007','數(shù)字邏輯','0006','2');insertintosc(sid,cid,tid,score)values('2406010101','0002','200604','80');insertintosc(sid,cid,tid,score)values('2406020101','0001','200605','76');insertintosc(sid,cid,tid,score)values('2406010102','0003','200605','80');insertintosc(sid,cid,tid,score)values('2406030101','0004','200604','84');insertintosc(sid,cid,tid,score)values('2406040101','0005','200604','86');insertintosc(sid,cid,tid,score)values('2406010102','0006','200605','87');insertintosc(sid,cid,tid,score)values('2406010103','0007','200604','95');updatedepsetdepname='information'wheredepname='IS';//修改IS系名稱為informationdeletefromscwheresidlike'240601010_'andcid='0002';deletefromscwheresidlike'240602010_'andcid='0001';//刪除計算機學院選擇1號課程與軟件學院選擇2號課程的同學deletefromscwheresid='2406010103';updatestudentsetsid='2406030102'anddepid=’03’wheresname='石心遠';insertintosc(sid,cid,tid,score)values('2406030102','0007','200604','95');//計算機學院學號為2406010103的同學轉(zhuǎn)入信息平安學院，學號變?yōu)?406030102，并修改相關數(shù)據(jù)八實驗結論、心得體會和改良建議：熟練掌握delete，update，rename的相關語法用法，確定結構并設計好相關代碼即可。

電子科技大學實驗報告實驗四1實驗名稱：簡單查詢、多表查詢2實驗學時：23實驗內(nèi)容和目的：練習用SELECT查詢語句，設置查詢條件，實現(xiàn)單表查詢。練習使用SELECT語句從多個表中查詢數(shù)據(jù)，表的內(nèi)連接、左外連接、右外連接的使用以及設置連接條件，理解連接條件和查詢條件的在目的和功能上的區(qū)別。查詢年齡在20—22之間的學生姓名〔通過出生日期和當前日期計算年齡，方法見第八章〕查詢年齡在20—22之間的學生姓名、院系和年齡查詢姓’張’的學生的學號、姓名、郵件地址查詢所有有成績〔成績不為空〕的學生學號和課程號查詢選修了2號課程成績在60分以下的所有學生的學號、姓名、學生的郵件地址、課程名、教師姓名和教師的郵件地址及課程成績查詢既選修了1號課程，又選修了2號課程的學生學號4實驗原理：結構化查詢語言、表的連接、關系運算5實驗器材〔設備、元器件〕計算機，MicrosoftSQLsever20146實驗步驟：恢復數(shù)據(jù)庫，將數(shù)據(jù)庫備份stud恢復到SQLSERVER中執(zhí)行SQL命令完成實驗內(nèi)容7實驗數(shù)據(jù)及結果分析：Createtabledep(depidvarchar(20)primarykey,depnamevarchar(20)notnull);Createtableteacher(tidvarchar(8)primarykey,tnamevarchar(8)notnull,titlevarchar(10),depidvarchar(20),foreignkey(depid)referencesdep(depid));Createtablestudent(sidvarchar(8)primarykey,snamevarchar(8)notnull,sexchar(2)notnull,depidvarchar(20),birthddate,semailvarchar(20),homeaddrvarchar(40),check(sexin('男','女')),check(semaillike'%@%'));Createtablecourse(cidvarchar(8)primarykey,cnamevarchar(30)notnull,cid_prevarchar(8),creditsnumeric(3,1)notnull);Createtablesc(sidvarchar(8)notnull,cidvarchar(8)notnull,tidvarchar(8)notnull,scoreinteger,check(scorebetween0and100),foreignkey(sid)referencesstudent(sid),foreignkey(cid)referencescourse(cid),foreignkey(tid)referencesteacher(tid));insertintodep(depid,depname)values('01','計算機');insertintodep(depid,depname)values('02','信軟');insertintodep(depid,depname)values('03','通信');insertintodep(depid,depname)values('04','電工');insertintodep(depid,depname)values('05','自動化');insertintoteacher(tid,tname,title,depid)values('200605','李遷','教授','01');insertintoteacher(tid,tname,title,depid)values('200604','曹薇','講師','02');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201401','張澄','男','01','1994.10.15','13456289@163','江蘇省南京市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201402','明青云','男','02','1996.1.11','465187@163','河北省邯鄲市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201403','孔玲瓏','女','03','1995.6.25','97644163@163','福建省泉州市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201404','雷破','男','04','1994.2.14','4941647@163','廣東省深圳市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201405','李肅','男','05','1994.5.26','8794164@163','湖北省武漢市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201406','林清','女','01','1995.5.28','8745156@163','湖北省孝感市');insertintocourse(cid,cname,cid_pre,credits)values('00101','離散數(shù)學','10001','2');insertintocourse(cid,cname,cid_pre,credits)values('00102','大學物理下','00100','2');insertintocourse(cid,cname,cid_pre,credits)values('00103','數(shù)據(jù)結構','10000','3');insertintocourse(cid,cname,cid_pre,credits)values('00104','概率論','10002','4');insertintocourse(cid,cname,cid_pre,credits)values('00105','計算機導論','00010','1');insertintosc(sid,cid,tid,score)values('201401','00101','200605','80');insertintosc(sid,cid,tid,score)values('201402','00102','200604','76');insertintosc(sid,cid,tid,score)values('201403','00103','200605','80');insertintosc(sid,cid,tid,score)values('201404','00104','200604','84');insertintosc(sid,cid,tid,score)values('201405','00105','200604','86');insertintosc(sid,cid,tid,score)values('201401','00102','200604','69');insertintosc(sid,cid,tid,score)values('201402','00101','200605','85');insertintosc(sid,cid,tid,score)values('201403','00104','200604','80');insertintosc(sid,cid,tid,score)values('201404','00103','200605','75');insertintosc(sid,cid,tid,score)values('201405','00101','200605','94');insertintosc(sid,cid,tid,score)values('201405','00102','200604','56');selectsname姓名,depid院系號,datediff(year,birthd,getdate())as年齡fromstudentwheredatediff(year,birthd,getdate())between20and22;//選擇年齡在20到22歲之間的學生輸出姓名，院系，年齡selectsid學號,sname姓名,semail郵件地址fromstudentwheresnamelike'張%';//選擇姓張的學生輸出學號，姓名，郵件地址selectsid學號,cid課程號fromscwherescoreisnotnull;//選擇成績不為空的同學輸出學號和課程號selectsc.sid學號,sname姓名,semail郵件地址,cname課程名,tname教師姓名, temail教師郵件地址,score成績fromsc,student,teacher,coursewheresc.cid='00102'andsc.score<60and sc.sid=student.sidand sc.tid=teacher.tidand sc.cid=course.cid;//選擇選修2號課程并且不及格的同學輸出學號，姓名，郵件地址，課程名，教師姓名，教師郵件地址和成績selectsid學號fromscswherecidin('00101','00102')andexists(selectsidfromscwheresid=s.sidandcidin('00101','00102')andcid<>s.cid)//選擇同時選修一號和二號課程的同學輸出學號8實驗結論、心得體會和改良建議：認真學習select語句語法，內(nèi)連接外連接，連接條件的相關語法用法，設計代碼即可

電子科技大學實驗報告實驗五1實驗名稱：分組統(tǒng)計查詢 2實驗學時：43實驗內(nèi)容和目的：練習使用聚集函數(shù)count(),max(),min(),avg()等在SQL命令中實現(xiàn)統(tǒng)計功能。使用GROUPBY子句實現(xiàn)分組查詢，以及聚集函數(shù)在分組查詢中的應用。體會分組查詢的功能特點。查詢選修數(shù)據(jù)庫并成績在６０分以上的人數(shù)求每個學生的選課的門數(shù)，顯示學號和選課門數(shù)求每個學生選課的總學分數(shù)，顯示學號和學分求每個學生的總成績，顯示學號和總成績查詢獲得’數(shù)據(jù)庫’課程最高分的學生姓名及成績求每門課程的平均成績，并顯示課程名及平均成績求每門課程的學生選修人數(shù)，并顯示課程名及選修人數(shù)求選修了5門以上課程的學生姓名及郵件地址4實驗原理：結構化查詢語言、分組查詢、集函數(shù)5實驗器材〔設備、元器件〕計算機，MicrosoftSQLsever20146實驗步驟：恢復數(shù)據(jù)庫，將數(shù)據(jù)庫備份stud恢復到SQLSERVER中執(zhí)行SQL命令完成實驗內(nèi)容7實驗數(shù)據(jù)及結果分析：Createtabledep(depidvarchar(20)primarykey,depnamevarchar(20)notnull);Createtableteacher(tidvarchar(8)primarykey,tnamevarchar(8)notnull,titlevarchar(10),depidvarchar(20),foreignkey(depid)referencesdep(depid));Createtablestudent(sidvarchar(8)primarykey,snamevarchar(8)notnull,sexchar(2)notnull,depidvarchar(20),birthddate,semailvarchar(20),homeaddrvarchar(40),check(sexin('男','女')),check(semaillike'%@%'));Createtablecourse(cidvarchar(8)primarykey,cnamevarchar(30)notnull,cid_prevarchar(8),creditsnumeric(3,1)notnull);Createtablesc(sidvarchar(8)notnull,cidvarchar(8)notnull,tidvarchar(8)notnull,scoreinteger,check(scorebetween0and100),foreignkey(sid)referencesstudent(sid),foreignkey(cid)referencescourse(cid),foreignkey(tid)referencesteacher(tid));insertintodep(depid,depname)values('01','計算機');insertintodep(depid,depname)values('02','信軟');insertintodep(depid,depname)values('03','通信');insertintodep(depid,depname)values('04','電工');insertintodep(depid,depname)values('05','自動化');insertintoteacher(tid,tname,title,depid)values('200605','李遷','教授','01');insertintoteacher(tid,tname,title,depid)values('200604','曹薇','講師','02');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201401','張澄','男','01','1994.10.15','13456289@163','江蘇省南京市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201402','明青云','男','02','1996.1.11','465187@163','河北省邯鄲市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201403','孔玲瓏','女','03','1995.6.25','97644163@163','福建省泉州市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201404','雷破','男','04','1994.2.14','4941647@163','廣東省深圳市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201405','李肅','男','05','1994.5.26','8794164@163','湖北省武漢市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201406','林清','女','01','1995.5.28','8745156@163','湖北省孝感市');insertintocourse(cid,cname,cid_pre,credits)values('00101','離散數(shù)學','10001','2');insertintocourse(cid,cname,cid_pre,credits)values('00102','大學物理下','00100','2');insertintocourse(cid,cname,cid_pre,credits)values('00103','數(shù)據(jù)結構','10000','3');insertintocourse(cid,cname,cid_pre,credits)values('00104','概率論','10002','4');insertintocourse(cid,cname,cid_pre,credits)values('00105','計算機導論','00010','1');insertintocourse(cid,cname,cid_pre,credits)values('00106','數(shù)據(jù)庫','00020','3');insertintosc(sid,cid,tid,score)values('201401','00101','200605','80');insertintosc(sid,cid,tid,score)values('201401','00102','200604','69');insertintosc(sid,cid,tid,score)values('201401','00103','200605','79');insertintosc(sid,cid,tid,score)values('201401','00104','200604','54');insertintosc(sid,cid,tid,score)values('201401','00105','200604','98');insertintosc(sid,cid,tid,score)values('201401','00106','200604','65');insertintosc(sid,cid,tid,score)values('201402','00102','200604','76');insertintosc(sid,cid,tid,score)values('201402','00101','200605','85');insertintosc(sid,cid,tid,score)values('201402','00106','200604','85');insertintosc(sid,cid,tid,score)values('201403','00103','200605','80');insertintosc(sid,cid,tid,score)values('201403','00104','200604','80');insertintosc(sid,cid,tid,score)values('201404','00104','200604','84');insertintosc(sid,cid,tid,score)values('201404','00103','200605','75');insertintosc(sid,cid,tid,score)values('201405','00105','200604','86');insertintosc(sid,cid,tid,score)values('201405','00101','200605','94');insertintosc(sid,cid,tid,score)values('201405','00102','200604','56');selectcount(*)as數(shù)據(jù)庫成績在60分以上人數(shù)fromscwherecid='00106'andscore>60;//篩選數(shù)據(jù)庫成績在60以上的人數(shù)selectsid學號,count(cid)as選課門數(shù)fromscgroupbysid;//學生的選課數(shù)selectsid學號,sum(credits)as總學分fromsc,coursewheresc.cid=course.cidgroupbysc.sid;//每個學生的總學分selectsid學號,sum(score)as總成績fromscgroupbysid;selectA.sid學號,A.score數(shù)據(jù)庫最高分fromscAwhereA.score=(selectmax(B.score)fromscBwhereA.cid='00106'andB.cid='00106'groupbyB.cid)//數(shù)據(jù)庫的最高分selectcname課程名,avg(sc.score)平均成績fromcourse,scwherecourse.cid=sc.cidgroupbycourseame;//求每門課的平均成績selectcname課程名,count(sid)選修人數(shù)fromcourse,scwherecourse.cid=sc.cidgroupbycourseame;//求每門課的選修人數(shù)selectnew.sid學號,student.semail郵件地址,new.選課數(shù)from(selectsid,count(cid)as選課數(shù)fromscgroupbysid)asnew,studentwherenew.sid=student.sidandnew.選課數(shù)>5;//求選課門數(shù)大于5的學生學號和郵件地址8實驗結論、心得體會和改良建議：學習好聚合語句用法，要求5和要求8比擬難，需要嵌套查詢

電子科技大學實驗報告實驗六1實驗名稱：集合操作、子查詢2實驗學時：23實驗內(nèi)容和目的：IN、EXISTS、NOTEXISTS運算在WHERE子句中的應用；靜態(tài)集合和由SELECT命令產(chǎn)生的動態(tài)結果集運算。查詢其他系中比信息系(depid=’IS’)某一學生年齡小的學生姓名和年齡查詢沒有選修任何課程的學生姓名、所在院系及郵件地址 查詢選修了全部課程的學生姓名查詢既選修了1號課程，又選修了2號課程的學生姓名4實驗原理：結構化查詢語言、集合運算、子查詢5實驗器材〔設備、元器件〕操作系統(tǒng)：Windows數(shù)據(jù)庫：MSSQLSERVER6實驗步驟：恢復數(shù)據(jù)庫，將數(shù)據(jù)庫備份stud恢復到SQLSERVER中執(zhí)行SQL命令完成實驗內(nèi)容7實驗數(shù)據(jù)及結果分析：Createtabledep(depidvarchar(20)primarykey,depnamevarchar(20)notnull);Createtableteacher(tidvarchar(8)primarykey,tnamevarchar(8)notnull,titlevarchar(10),depidvarchar(20),foreignkey(depid)referencesdep(depid));Createtablestudent(sidvarchar(20)primarykey,snamevarchar(8)notnull,sexchar(2)notnull,depidvarchar(20),birthddate,semailvarchar(20),homeaddrvarchar(40),check(sexin('男','女')),check(semaillike'%@%'));Createtablecourse(cidvarchar(8)primarykey,cnamevarchar(30)notnull,cid_prevarchar(8),creditsnumeric(3,1)notnull);Createtablesc(sidvarchar(20)notnull,cidvarchar(8)notnull,tidvarchar(8)notnull,scoreinteger,check(scorebetween0and100),foreignkey(sid)referencesstudent(sid),foreignkey(cid)referencescourse(cid),foreignkey(tid)referencesteacher(tid));insertintodep(depid,depname)values('01','計算機');insertintodep(depid,depname)values('02','軟件工程');insertintodep(depid,depname)values('03','IS');insertintodep(depid,depname)values('04','電工');insertintodep(depid,depname)values('05','自動化');insertintodep(depid,depname)values('06','通信');insertintoteacher(tid,tname,title,depid)values('200605','李遷','教授','01');insertintoteacher(tid,tname,title,depid)values('200604','曹薇','講師','02');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406010101','張澄','男','01','1994.10.15','13456289@163','江蘇省南京市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406020101','明青云','男','02','1996.1.11','465187@163','河北省邯鄲市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406020102','孔玲瓏','女','02','1995.6.25','97644163@163','福建省泉州市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406030101','雷破','男','03','1994.2.14','4941647@163','廣東省深圳市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406040101','李肅','男','04','1994.5.26','8794164@163','湖北省武漢市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406010102','林清','女','01','1995.5.28','8745156@163','湖北省孝感市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406010103','石心遠','男','01','1996.12.30','