數(shù)據(jù)庫實(shí)驗(yàn)-電子科技大學(xué)

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

電子科技大學(xué)實(shí)驗(yàn)報(bào)告實(shí)驗(yàn)一實(shí)驗(yàn)名稱：創(chuàng)立，備份與恢復(fù)數(shù)據(jù)庫實(shí)驗(yàn)學(xué)時(shí)：2實(shí)驗(yàn)內(nèi)容和目的：本實(shí)驗(yàn)要求學(xué)生掌握創(chuàng)立數(shù)據(jù)庫的方法及相關(guān)操作，向數(shù)據(jù)庫中添加樣本數(shù)據(jù)，學(xué)習(xí)SQLSERVER數(shù)據(jù)庫的恢復(fù)和備份。創(chuàng)立數(shù)據(jù)庫，命名為DB+學(xué)號(hào)，如：DB000000001該數(shù)據(jù)庫包括5個(gè)表：建議：在定義表中字段時(shí)，盡量使用意義的英文單詞。‘系別代碼表’ 表名：dep‘教師表’ 表名：teacher‘學(xué)生表’ 表名：stud‘課程表’ 表名：course‘選課表’ 表名：sc為每個(gè)表準(zhǔn)備大約5-10條記錄，使用Insert語句將這些數(shù)據(jù)插入到相應(yīng)表中數(shù)據(jù)錄入完成后，將數(shù)據(jù)庫備份到磁盤上，在以后的的試驗(yàn)中備用。實(shí)驗(yàn)原理：使用數(shù)據(jù)庫管理系統(tǒng)DB、DDL創(chuàng)立數(shù)據(jù)庫及數(shù)據(jù)庫對(duì)象。實(shí)驗(yàn)器材〔設(shè)備、元器件〕計(jì)算機(jī)，MicrosoftSQLsever2014實(shí)驗(yàn)步驟：啟動(dòng)SQLSERVER創(chuàng)立數(shù)據(jù)庫：STUD創(chuàng)立表‘系別代碼表’表名：dep其結(jié)構(gòu)如下：〔該表的主鍵為’系代碼’〕字段名稱字段類型字段大小/格式是否可為空系代碼depidvarchar8否PK系名depnamevarchar20否Notnull‘教師表’表名：teacher其結(jié)構(gòu)如下：〔該表的主鍵為’教師號(hào)’〕字段名稱字段類型字段大小/格式是否可為空教師號(hào)tidVarchar8否PK教師名tnameVarchar8否Notnull職稱titleVarchar10是所屬院系編號(hào)depidVarchar20是 ‘學(xué)生表’ 表名：student 其結(jié)構(gòu)如下：〔該表的主鍵為’學(xué)生號(hào)’〕字段名稱字段類型字段大小/格式是否可為空學(xué)號(hào)sidVarchar11否PK學(xué)生名snameVarchar8否Notnull性別sexChar2否院系編號(hào)depidVarchar20是出生年月birthdDate是郵箱semailVarchar20是家庭地址homeaddrVarchar40是‘課程表’表名：course其結(jié)構(gòu)如下：〔該表的主鍵是課程號(hào)〕字段名稱字段類型字段大小/格式是否可為空課程號(hào)cidvarchar8否PK課程名cnamevarchar30否Notnull先修課程號(hào)cid_prevarchar8是學(xué)分creditsnumeric3(小數(shù)位數(shù)1)否Notnull‘選課表’表名：sc其結(jié)構(gòu)如下：〔該表的主鍵是課程號(hào)〕字段名稱字段類型字段大小/格式是否可為空學(xué)號(hào)sidvarchar8否Notnull,PK課程號(hào)cidvarchar8否Notnull,PK教師號(hào)tidvarchar8否成績scoreinteger是備份數(shù)據(jù)庫恢復(fù)數(shù)據(jù)庫PK:主鍵，Notnull:不能取空值實(shí)驗(yàn)數(shù)據(jù)及結(jié)果分析：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','計(jì)算機(jī)');insertintodep(depid,depname)values('02','信軟');insertintodep(depid,depname)values('03','通信');insertintodep(depid,depname)values('04','電工');insertintodep(depid,depname)values('05','自動(dòng)化');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ù)學(xué)','10001','2');insertintocourse(cid,cname,cid_pre,credits)values('00102','大學(xué)物理下','00100','2');insertintocourse(cid,cname,cid_pre,credits)values('00103','數(shù)據(jù)結(jié)構(gòu)','10000','3');insertintocourse(cid,cname,cid_pre,credits)values('00104','概率論','10002','4');insertintocourse(cid,cname,cid_pre,credits)values('00105','計(jì)算機(jī)導(dǎo)論','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í)驗(yàn)結(jié)論、心得體會(huì)和改良建議：按步驟進(jìn)行試驗(yàn)，本實(shí)驗(yàn)相對(duì)簡單。

電子科技大學(xué)實(shí)驗(yàn)報(bào)告實(shí)驗(yàn)二實(shí)驗(yàn)名稱：數(shù)據(jù)庫的完整性實(shí)驗(yàn)學(xué)時(shí)：4實(shí)驗(yàn)內(nèi)容和目的：通過設(shè)置表的檢查約束、外鍵約束體會(huì)數(shù)據(jù)庫完整性的含義，約束條件下數(shù)據(jù)修改操作的限制，以及實(shí)現(xiàn)修改操作的技巧。表結(jié)構(gòu)中的完整性約束條件如下：PK:主鍵，F(xiàn)K:外鍵，其它::約束條件‘系別代碼表’表名：dep其結(jié)構(gòu)如下：〔該表的主鍵為’系代碼’〕字段名稱字段類型字段大小/格式是否可為空約束條件系代碼depidvarchar8否PK系名depnamevarchar20否Notnull‘教師表’表名：teacher其結(jié)構(gòu)如下：〔該表的主鍵為’教師號(hào)’〕字段名稱字段類型字段大小/格式是否可為空約束條件教師號(hào)tidVarchar8否PK教師名tnameVarchar8否Notnull職稱titleVarchar10是所屬院系編號(hào)depidVarchar20是 ‘學(xué)生表’ 表名：student 其結(jié)構(gòu)如下：〔該表的主鍵為’學(xué)生號(hào)’〕字段名稱字段類型字段大小/格式是否可為空約束條件學(xué)號(hào)sidVarchar11否PK學(xué)生名snameVarchar8否Notnull性別sexChar2否院系編號(hào)depidVarchar20是FK出生年月birthdDate是郵箱semailVarchar20是家庭地址homeaddrVarchar40是‘課程表’表名：course其結(jié)構(gòu)如下：〔該表的主鍵是課程號(hào)〕字段名稱字段類型字段大小/格式是否可為空約束條件課程號(hào)cidvarchar8否PK課程名cnamevarchar30否Notnull先修課程號(hào)cid_prevarchar8是學(xué)分creditsnumeric3(小數(shù)位數(shù)1)否Notnull‘選課表’表名：sc其結(jié)構(gòu)如下：〔該表的主鍵是課程號(hào)〕字段名稱字段類型字段大小/格式是否可為空約束條件學(xué)號(hào)sidvarchar8否Notnull,PK,FK課程號(hào)cidvarchar8否Notnull,PK,FK教師號(hào)tidvarchar8否Notnull,FK成績scoreinteger是0<score<100設(shè)置選課表的三個(gè)外鍵約束〔學(xué)號(hào)，課程號(hào)，教師號(hào)〕設(shè)置教師表，學(xué)生表中的院系字段(depid)的外鍵約束設(shè)置學(xué)生表中姓名字段為非空字段〔必須有數(shù)據(jù)，不能是空值〕設(shè)置選課表中成績字段的取值范圍是0到100設(shè)置學(xué)生表中性別字段的取值為’男’或’女’設(shè)置學(xué)生表電子郵件字段的取值必須包含@符號(hào)向’系別代碼表’添加數(shù)據(jù)，數(shù)據(jù)如下：向’教師表’添加數(shù)據(jù),2條向’學(xué)生表’添加數(shù)據(jù),5條以上向’課程表’添加數(shù)據(jù),3條以上向’選課表’添加數(shù)據(jù),10條實(shí)驗(yàn)原理：數(shù)據(jù)庫庫的完整性、約束條件、結(jié)構(gòu)化查詢語言。實(shí)驗(yàn)器材〔設(shè)備、元器件〕計(jì)算機(jī)，MicrosoftSQLsever2014.實(shí)驗(yàn)步驟：恢復(fù)數(shù)據(jù)庫，將數(shù)據(jù)庫備份stud恢復(fù)到SQLSERVER中執(zhí)行SQL命令完成實(shí)驗(yàn)內(nèi)容備份數(shù)據(jù)庫實(shí)驗(yàn)數(shù)據(jù)及結(jié)果分析：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','計(jì)算機(jī)');insertintodep(depid,depname)values('02','信軟');insertintodep(depid,depname)values('03','通信');insertintodep(depid,depname)values('04','電工');insertintodep(depid,depname)values('05','自動(dòng)化');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ù)學(xué)','10001','2');insertintocourse(cid,cname,cid_pre,credits)values('00102','大學(xué)物理下','00100','2');insertintocourse(cid,cname,cid_pre,credits)values('00103','數(shù)據(jù)結(jié)構(gòu)','10000','3');insertintocourse(cid,cname,cid_pre,credits)values('00104','概率論','10002','4');insertintocourse(cid,cname,cid_pre,credits)values('00105','計(jì)算機(jī)導(dǎo)論','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');實(shí)驗(yàn)結(jié)論、心得體會(huì)和改良建議：注意sql語句語法，認(rèn)真編寫代碼根本就可以順利完成實(shí)驗(yàn)。電子科技大學(xué)實(shí)驗(yàn)報(bào)告實(shí)驗(yàn)三一實(shí)驗(yàn)名稱：數(shù)據(jù)的修改二實(shí)驗(yàn)學(xué)時(shí)：2三實(shí)驗(yàn)內(nèi)容和目的：練習(xí)UPDATE、DELETE命令的使用，實(shí)現(xiàn)對(duì)數(shù)據(jù)的修改和刪除。將院系中，原院系名’IS’改為’Information‘在選課表中，刪除計(jì)算機(jī)科學(xué)與工程系學(xué)生選修2號(hào)課程的記錄在選課表中，刪除軟件工程系學(xué)生選課1號(hào)課程的紀(jì)錄記錄學(xué)號(hào)為2406010103的同學(xué)由原來的計(jì)算機(jī)科學(xué)與工程系轉(zhuǎn)入信息平安系，學(xué)號(hào)更改為2406030102，在數(shù)據(jù)庫中做出相應(yīng)修改。四實(shí)驗(yàn)原理：使用結(jié)構(gòu)化查詢語言，在滿足約束條件的情況下完成數(shù)據(jù)修改五實(shí)驗(yàn)器材〔設(shè)備、元器件〕計(jì)算機(jī)，MicrosoftSQLsever六實(shí)驗(yàn)步驟：恢復(fù)數(shù)據(jù)庫，將數(shù)據(jù)庫備份stud恢復(fù)到SQLSERVER中執(zhí)行SQL命令完成實(shí)驗(yàn)內(nèi)容備份數(shù)據(jù)庫七實(shí)驗(yàn)數(shù)據(jù)及結(jié)果分析：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','計(jì)算機(jī)');insertintodep(depid,depname)values('02','軟件工程');insertintodep(depid,depname)values('03','信息平安');insertintodep(depid,depname)values('04','電工');insertintodep(depid,depname)values('05','自動(dòng)化');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','石心遠(yuǎn)','男','01','1996.12.30','45161664@163','吉林省長春市');insertintocourse(cid,cname,cid_pre,credits)values('0001','離散數(shù)學(xué)','0002','2');insertintocourse(cid,cname,cid_pre,credits)values('0002','大學(xué)物理下','0003','2');insertintocourse(cid,cname,cid_pre,credits)values('0003','數(shù)據(jù)結(jié)構(gòu)','0004','3');insertintocourse(cid,cname,cid_pre,credits)values('0004','概率論','0005','4');insertintocourse(cid,cname,cid_pre,credits)values('0005','計(jì)算機(jī)導(dǎo)論','0001','1');insertintocourse(cid,cname,cid_pre,credits)values('0006','計(jì)算機(jī)網(wǎng)絡(luò)','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';//刪除計(jì)算機(jī)學(xué)院選擇1號(hào)課程與軟件學(xué)院選擇2號(hào)課程的同學(xué)deletefromscwheresid='2406010103';updatestudentsetsid='2406030102'anddepid=’03’wheresname='石心遠(yuǎn)';insertintosc(sid,cid,tid,score)values('2406030102','0007','200604','95');//計(jì)算機(jī)學(xué)院學(xué)號(hào)為2406010103的同學(xué)轉(zhuǎn)入信息平安學(xué)院，學(xué)號(hào)變?yōu)?406030102，并修改相關(guān)數(shù)據(jù)八實(shí)驗(yàn)結(jié)論、心得體會(huì)和改良建議：熟練掌握delete，update，rename的相關(guān)語法用法，確定結(jié)構(gòu)并設(shè)計(jì)好相關(guān)代碼即可。

電子科技大學(xué)實(shí)驗(yàn)報(bào)告實(shí)驗(yàn)四1實(shí)驗(yàn)名稱：簡單查詢、多表查詢2實(shí)驗(yàn)學(xué)時(shí)：23實(shí)驗(yàn)內(nèi)容和目的：練習(xí)用SELECT查詢語句，設(shè)置查詢條件，實(shí)現(xiàn)單表查詢。練習(xí)使用SELECT語句從多個(gè)表中查詢數(shù)據(jù)，表的內(nèi)連接、左外連接、右外連接的使用以及設(shè)置連接條件，理解連接條件和查詢條件的在目的和功能上的區(qū)別。查詢年齡在20—22之間的學(xué)生姓名〔通過出生日期和當(dāng)前日期計(jì)算年齡，方法見第八章〕查詢年齡在20—22之間的學(xué)生姓名、院系和年齡查詢姓’張’的學(xué)生的學(xué)號(hào)、姓名、郵件地址查詢所有有成績〔成績不為空〕的學(xué)生學(xué)號(hào)和課程號(hào)查詢選修了2號(hào)課程成績在60分以下的所有學(xué)生的學(xué)號(hào)、姓名、學(xué)生的郵件地址、課程名、教師姓名和教師的郵件地址及課程成績查詢既選修了1號(hào)課程，又選修了2號(hào)課程的學(xué)生學(xué)號(hào)4實(shí)驗(yàn)原理：結(jié)構(gòu)化查詢語言、表的連接、關(guān)系運(yùn)算5實(shí)驗(yàn)器材〔設(shè)備、元器件〕計(jì)算機(jī)，MicrosoftSQLsever20146實(shí)驗(yàn)步驟：恢復(fù)數(shù)據(jù)庫，將數(shù)據(jù)庫備份stud恢復(fù)到SQLSERVER中執(zhí)行SQL命令完成實(shí)驗(yàn)內(nèi)容7實(shí)驗(yàn)數(shù)據(jù)及結(jié)果分析：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','計(jì)算機(jī)');insertintodep(depid,depname)values('02','信軟');insertintodep(depid,depname)values('03','通信');insertintodep(depid,depname)values('04','電工');insertintodep(depid,depname)values('05','自動(dòng)化');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ù)學(xué)','10001','2');insertintocourse(cid,cname,cid_pre,credits)values('00102','大學(xué)物理下','00100','2');insertintocourse(cid,cname,cid_pre,credits)values('00103','數(shù)據(jù)結(jié)構(gòu)','10000','3');insertintocourse(cid,cname,cid_pre,credits)values('00104','概率論','10002','4');insertintocourse(cid,cname,cid_pre,credits)values('00105','計(jì)算機(jī)導(dǎo)論','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院系號(hào),datediff(year,birthd,getdate())as年齡fromstudentwheredatediff(year,birthd,getdate())between20and22;//選擇年齡在20到22歲之間的學(xué)生輸出姓名，院系，年齡selectsid學(xué)號(hào),sname姓名,semail郵件地址fromstudentwheresnamelike'張%';//選擇姓張的學(xué)生輸出學(xué)號(hào)，姓名，郵件地址selectsid學(xué)號(hào),cid課程號(hào)fromscwherescoreisnotnull;//選擇成績不為空的同學(xué)輸出學(xué)號(hào)和課程號(hào)selectsc.sid學(xué)號(hào),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號(hào)課程并且不及格的同學(xué)輸出學(xué)號(hào)，姓名，郵件地址，課程名，教師姓名，教師郵件地址和成績selectsid學(xué)號(hào)fromscswherecidin('00101','00102')andexists(selectsidfromscwheresid=s.sidandcidin('00101','00102')andcid<>s.cid)//選擇同時(shí)選修一號(hào)和二號(hào)課程的同學(xué)輸出學(xué)號(hào)8實(shí)驗(yàn)結(jié)論、心得體會(huì)和改良建議：認(rèn)真學(xué)習(xí)select語句語法，內(nèi)連接外連接，連接條件的相關(guān)語法用法，設(shè)計(jì)代碼即可

電子科技大學(xué)實(shí)驗(yàn)報(bào)告實(shí)驗(yàn)五1實(shí)驗(yàn)名稱：分組統(tǒng)計(jì)查詢 2實(shí)驗(yàn)學(xué)時(shí)：43實(shí)驗(yàn)內(nèi)容和目的：練習(xí)使用聚集函數(shù)count(),max(),min(),avg()等在SQL命令中實(shí)現(xiàn)統(tǒng)計(jì)功能。使用GROUPBY子句實(shí)現(xiàn)分組查詢，以及聚集函數(shù)在分組查詢中的應(yīng)用。體會(huì)分組查詢的功能特點(diǎn)。查詢選修數(shù)據(jù)庫并成績在６０分以上的人數(shù)求每個(gè)學(xué)生的選課的門數(shù)，顯示學(xué)號(hào)和選課門數(shù)求每個(gè)學(xué)生選課的總學(xué)分?jǐn)?shù)，顯示學(xué)號(hào)和學(xué)分求每個(gè)學(xué)生的總成績，顯示學(xué)號(hào)和總成績查詢獲得’數(shù)據(jù)庫’課程最高分的學(xué)生姓名及成績求每門課程的平均成績，并顯示課程名及平均成績求每門課程的學(xué)生選修人數(shù)，并顯示課程名及選修人數(shù)求選修了5門以上課程的學(xué)生姓名及郵件地址4實(shí)驗(yàn)原理：結(jié)構(gòu)化查詢語言、分組查詢、集函數(shù)5實(shí)驗(yàn)器材〔設(shè)備、元器件〕計(jì)算機(jī)，MicrosoftSQLsever20146實(shí)驗(yàn)步驟：恢復(fù)數(shù)據(jù)庫，將數(shù)據(jù)庫備份stud恢復(fù)到SQLSERVER中執(zhí)行SQL命令完成實(shí)驗(yàn)內(nèi)容7實(shí)驗(yàn)數(shù)據(jù)及結(jié)果分析：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','計(jì)算機(jī)');insertintodep(depid,depname)values('02','信軟');insertintodep(depid,depname)values('03','通信');insertintodep(depid,depname)values('04','電工');insertintodep(depid,depname)values('05','自動(dòng)化');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ù)學(xué)','10001','2');insertintocourse(cid,cname,cid_pre,credits)values('00102','大學(xué)物理下','00100','2');insertintocourse(cid,cname,cid_pre,credits)values('00103','數(shù)據(jù)結(jié)構(gòu)','10000','3');insertintocourse(cid,cname,cid_pre,credits)values('00104','概率論','10002','4');insertintocourse(cid,cname,cid_pre,credits)values('00105','計(jì)算機(jī)導(dǎo)論','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學(xué)號(hào),count(cid)as選課門數(shù)fromscgroupbysid;//學(xué)生的選課數(shù)selectsid學(xué)號(hào),sum(credits)as總學(xué)分fromsc,coursewheresc.cid=course.cidgroupbysc.sid;//每個(gè)學(xué)生的總學(xué)分selectsid學(xué)號(hào),sum(score)as總成績fromscgroupbysid;selectA.sid學(xué)號(hào),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學(xué)號(hào),student.semail郵件地址,new.選課數(shù)from(selectsid,count(cid)as選課數(shù)fromscgroupbysid)asnew,studentwherenew.sid=student.sidandnew.選課數(shù)>5;//求選課門數(shù)大于5的學(xué)生學(xué)號(hào)和郵件地址8實(shí)驗(yàn)結(jié)論、心得體會(huì)和改良建議：學(xué)習(xí)好聚合語句用法，要求5和要求8比擬難，需要嵌套查詢

電子科技大學(xué)實(shí)驗(yàn)報(bào)告實(shí)驗(yàn)六1實(shí)驗(yàn)名稱：集合操作、子查詢2實(shí)驗(yàn)學(xué)時(shí)：23實(shí)驗(yàn)內(nèi)容和目的：IN、EXISTS、NOTEXISTS運(yùn)算在WHERE子句中的應(yīng)用；靜態(tài)集合和由SELECT命令產(chǎn)生的動(dòng)態(tài)結(jié)果集運(yùn)算。查詢其他系中比信息系(depid=’IS’)某一學(xué)生年齡小的學(xué)生姓名和年齡查詢沒有選修任何課程的學(xué)生姓名、所在院系及郵件地址 查詢選修了全部課程的學(xué)生姓名查詢既選修了1號(hào)課程，又選修了2號(hào)課程的學(xué)生姓名4實(shí)驗(yàn)原理：結(jié)構(gòu)化查詢語言、集合運(yùn)算、子查詢5實(shí)驗(yàn)器材〔設(shè)備、元器件〕操作系統(tǒng)：Windows數(shù)據(jù)庫：MSSQLSERVER6實(shí)驗(yàn)步驟：恢復(fù)數(shù)據(jù)庫，將數(shù)據(jù)庫備份stud恢復(fù)到SQLSERVER中執(zhí)行SQL命令完成實(shí)驗(yàn)內(nèi)容7實(shí)驗(yàn)數(shù)據(jù)及結(jié)果分析：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','計(jì)算機(jī)');insertintodep(depid,depname)values('02','軟件工程');insertintodep(depid,depname)values('03','IS');insertintodep(depid,depname)values('04','電工');insertintodep(depid,depname)values('05','自動(dòng)化');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','石心遠(yuǎn)','男','01','1996.12.30','