mysql數(shù)據(jù)庫實驗答案

1、實驗一 創(chuàng)建、修改數(shù)據(jù)庫和表結(jié)構(gòu)1、用create建立教學數(shù)據(jù)庫的五個基本表：（1） 學生表（學號，姓名，性別，年齡），student(Sno， sname， ssex，sage) ；（2）課程表（課程號，課程名，學分），Course (Cno, Cname, credit) ；（3）選課表（學號，課程號，成績），SC (Sno, Cno, grade ) ；(4) 教師表（教師號，姓名，性別，出生年月，系部，職稱，地址），T(Tno，Tname，ssex，birthday，dept，title，address) ；(5) 工資表（教師號，基本工資，職務(wù)工資，合計），Salary(Tno，jb

2、gz，zwgz，hj)；Create Database Student default character set utf8 default COLLATE utf8_bin;Use Student;Create Table Student(SNochar(20) primary key,SNamechar(20) ,SSexchar(4) default '男',SAgeint) ENGINE=InnoDB;Create Table Course(CNochar(20) primary key,CNamechar(20) NOT NULL,CReditfloat) ENGIN

3、E=InnoDB;Create Table SC(SNochar(20) NOT NULL,CNochar(20) NOT NULL,Gradefloat,Primary Key(SNo, CNo),Foreign Key(SNo) References Student(SNo) On Delete Cascade,Foreign Key(CNo) References Course(CNo) ENGINE=InnoDB;Create Table T(TNochar(20) Primary Key,TNamechar(20) NOT NULL,TSexchar(4) default '

4、男',birthday DateTime,deptchar(20),titlechar(20),address char(20) ENGINE=InnoDB;Create Table Salary(TNochar(20) NOT NULL,jbgzfloat,zwgzfloat,hjfloat,Foreign Key(TNo) References T(TNo) On Delete Cascade) ENGINE=InnoDB;2、用alter修改基本表（1）在已存在的學生表student中增加一個sdept（系）的新的屬性列； alter table Student add Dept

5、 char(20);（2）將學生表student中sname屬性列的數(shù)據(jù)類型修改為變長字符串varchar(10)。alter able Student modify colum sname varchar(10)3、建立一個臨時表，然后將其刪除 Create Table temp (ANochar(20) NOT NULL,Bfloat, C char(10) ) Drop table temp實驗二 建立與刪除索引1、用create index在學生表student的學號sno上建立聚簇索引。Create Clustered Index SNo_Index On Student(SNo);

6、2、在學生表student中，為姓名sname建立非聚簇索引。 Create Index SName_Index On Student(SName);3、在課程表的課程號Cno上建立唯一索引。 Create Unique Index CNo_Index On Course(CNo);4、在選課表的學號sno、成績Grade上建立復(fù)合索引，要求學號為升序，學號相同時成績?yōu)榻敌?。Create Index SCNo_Index On SC(SNo ASC, Grade DESC);5、用drop刪除學生表student的索引。 Drop Index Student.SNo_Index;6、增加學生表

7、student中姓名唯一約束。 Alter Table Student Add Unique(SName);7、增加學生表student中性別男、女唯一約束。 Alter Table Student Add Constraint:SSex check(SSex = '男' or SSex = '女');8、增加學生表student中年齡1825歲約束。Alter Table Student Add Constraint:SAge check(SAge >= 18 And SAge <= 25);9、增加選課表SC中學號sno的外碼約束。Alter T

8、able SC Add Foreign Key(SNo) references Student(SNo);-實驗三 數(shù)據(jù)的插入、更新及刪除操作1、用insert輸入數(shù)據(jù)。學生表student的數(shù)據(jù)991201 張三22男計算機系991202 李四 21男信息系991101王五23男數(shù)學系991102陳六19男計算機系991103吳七24女數(shù)學系000101劉八22女信息系Insert Into Student Values('991201', '張三', '男', 22, '計算機科學與技術(shù)系');Insert Into Stud

9、ent Values('991202', '李四', '男', 21, '信息科學系');Insert Into Student Values('991101', '王五', '男', 23, '數(shù)理系');Insert Into Student Values('991102', '陳六', '男', 19, '計算機科學與技術(shù)系');Insert Into Student Values('991

10、103', '吳七', '女', 24, '數(shù)理系');Insert Into Student Values('000101', '劉八', '女', 22, '信息科學系');課程表course的數(shù)據(jù)1 數(shù)學52 數(shù)據(jù)結(jié)構(gòu)43 程序設(shè)計24 數(shù)據(jù)庫原理35 操作系統(tǒng)3Insert Into Course Values('1', '數(shù)學', 5);Insert Into Course Values('2', '數(shù)據(jù)結(jié)構(gòu)&

11、#39;, 4);Insert Into Course Values('3', '程序設(shè)計', 2);Insert Into Course Values('4', '數(shù)據(jù)庫原理', 3);Insert Into Course Values('5', '操作系統(tǒng)', 3);選課表SC的數(shù)據(jù)991201190991201580991201385991201490991102185991102298000101291Insert Into SC Values('991201', '

12、1', 90);Insert Into SC Values('991201', '5', 80);Insert Into SC Values('991201', '3', 85);Insert Into SC Values('991201', '4', 90);Insert Into SC Values('991102', '1', 85);Insert Into SC Values('991102', '2', 98);Ins

13、ert Into SC Values('000101', '2', 91);基本表T的數(shù)據(jù)0001張三男1968-10信息副教授湘潭0002李四 女1956-11信息教授長沙1001王五男1973-07計算機講師湘潭1008陳六男1970-08計算機副教授北京Insert Into T Values('0001', '張三', '男', '1968-10-10', '信息科學系', '副教授', '湘潭');Insert Into T Values(&

14、#39;0002', '李四', '女', '1956-11-10', '信息科學系', '教授', '長沙');Insert Into T Values('1001', '王五', '男', '1973-07-20', '計算機科學與技術(shù)系', '講師', '湘潭');Insert Into T Values('1008', '陳六', '

15、男', '1970-08-20', '計算機科學與技術(shù)系', '副教授', '北京');基本表Salary的數(shù)據(jù)00011000300130000021500500200010018002001000Insert Into Salary Values('0001', 1000, 300, 1300);Insert Into Salary Values('0002', 1500, 500, 2000);Insert Into Salary Values('1001', 800,

16、 200, 1000);*/2、用delete刪除數(shù)據(jù)記錄（1）刪除教師表T中教師號為0001的元組。（2）刪除教師表T中的全部數(shù)據(jù)。update t set birthday='1961-10-04' where Tno ='0001' Delete From T;3、用 update更新數(shù)據(jù)記錄（1） 把0001號教師的基本工資加100。（2） 把所有教師的基本工資都加100。 Update Salary Set jbgz = jbgz + 100 Where TNo = '0001'Update Salary Set jbgz = jbgz

17、 + 100實驗四 數(shù)據(jù)的查詢1、簡單查詢，用select檢索（1）查詢所有學生的基本情況。select * from student;（2）查詢教師每月應(yīng)交納的個人所得稅。 select hj*0.005 as monthshui from Salary;（3）查詢張三與李四兩位同學的基本情況。 select * from student where sname='張三' or sname='李四'（4）查詢9911班學生的基本信息（規(guī)定學生學號的前四位是班級號）。 select * from student where sno like '9911%

18、'（5）查詢所有年齡在20歲以下的學生姓名及其年齡。 select sname,sage from student where sage<20;（6）查詢選修了2門以上課程的學生學號。 select sno from SC group by snohaving count(*)>2;2、多表查詢，用select檢索（1）查詢教師的收入情況，包括教師號、姓名及月總收入。select T.Tno,Tname, hj /不能寫成select Tno,因為Tno不明確from T,Salarywhere T.Tno=Salary.Tno;（2）查詢每個學生的學號、姓名、選修課程及成

19、績。 select student.sno,sname,cno,gradefrom student,sc where student.sno=sc.sno;（3）查詢每一門課的間接先修課。 select CA.cno AS 課程號,CB.PreCourse AS 間接先修課號from course CA,course CBwhere CA.PreCourse=CB.cno and CB.PreCourse is not null;（4）查詢有相同地址的兩位教師的信息。 select *from T Txwhere Tx.address in (select address from T Ty

20、where Tx.Tname<>Ty.Tname); select * from T Tx, T Ty where Tx. address=Ty. Address and Tx.Tname<>Ty.Tname（5）查詢選修2號課程且成績在90分以上的所有學生。 select * from student,SCwhere student.sno=SC.sno and SC.cno='2' and SC.grade>90;（6）查詢與王五在同一個系學習的學生。select *from studentwhere sdept=(select sdept f

21、rom student where sname='王五');實驗五 視圖1、建立男學生的視圖，屬性包括學號、姓名、選修課程名和成績。 create view boystudent_view as select student.sno,sname,cno,gradefrom student,SCwhere student.ssex=男 and student.sno=SC. sno;2、在男學生視圖中查詢平均成績大于80分的學生學號與姓名。 select sno,snamefrom boystudent_viewgroup by sno,snamehaving AVG(grade

22、)>803、建立信息系選修了1號課程的學生的視圖。 create view xinxi_view1 as select student.sno,sname,ssex,sage from student,SC where student.sdept='信息' and student.sno=SC.sno and SC.cno='1'4、建立信息系選修了1號課程且成績在90分以上的學生的視圖。 create view xinxi_view2 as select student.sno,sname,sage,ssex from student,SC where

23、student.sdept='信息' and student.sno=SC.sno and SC.cno='1' and SC.grade>905、建立計算機系選修了2號課程的學生的視圖。create view jisuanji_viewas select student.sno,sname,sage,ssexfrom student,SCwhere student.sdept='計算機' and student.sno=SC.sno and SC.cno='2'6、建立一個學生出生年份的視圖。create view yea

24、r_viewas select student.sno,sname,2007-sage as birthdayfrom student7、建立一個計算機系學生的視圖，并要求在進行修改、插入操作時，仍然要確保視圖只有計算機系的學生。create view jisuanji2_viewas select student.sno,sname,sage,ssexfrom studentwhere sdept='計算機'with check option8、向?qū)W生視圖中插入一條新記錄（951101，錢進，男，20） create view student_view1 as select

25、sno,sname,ssex,sage from student;insert into student_view1 values('951101','錢進','男',20)9、將學生視圖中學號為991101的學生姓名改為“劉平平”。 update student_view1 set sname='劉平平' where sno='991101'10、刪除計算機系視圖中學號為991201的記錄。 deletefrom jisuanji2_view where sno='991201'11、刪除學生出生

26、年份的視圖。drop view year_view;實驗六 集合函數(shù)的應(yīng)用1、使用select語句查詢（1）9911班學生的人數(shù)和平均成績（規(guī)定學生學號的前四位是班級號）SELECT COUNT(sno) as 總?cè)藬?shù)， AVG(sc.grade)AS 平均分 FROM scWHERE sno like 9912%（2）每個學生的學號和相應(yīng)的平均成績SELECT sno, AVG(grade) FROM,scGROUP BY student.sno（3）成績大于90分的學生學號SELECT sno,MAX(sc.grade) as maxgrade FROM scGROUP BY snoHAV

27、ING MAX(grade)>90（4）月工資大于平均工資的教師號SELECT TNo FROM Salary WHERE hj>=( SELECT AVG(hj) from Salary ) 2、實驗完成后，撤消建立的基本表和視圖。 實驗七 創(chuàng)建、執(zhí)行、修改和刪除存儲過程1、建立查詢計算機系女學生的存儲過程。Use student;CREATE PROCEDURE SELECT_WOMAN()BEGIN SELECT * FROM student where ssex='女'end;call select_wanman();2、定義一個存儲過程不帶參數(shù)，作用是查詢

28、選修了課程學生的學號，并執(zhí)行這個存儲過程。use student;Create procedure numofsc() BEGIN select sno from SC group by snohaving count(*)>0;End;call numofsc();3、定義一個帶輸出參數(shù)的存儲過程，作用是查詢Student表中的學生個數(shù)，并賦值給輸出參數(shù),執(zhí)行該存儲過程，同時通過輸出參數(shù)來輸出學生個數(shù)。 CREATE PROCEDURE proc1(OUT s int) BEGIN SELECT COUNT(*) INTO s FROM student; END; 執(zhí)行 CALL pr

29、oc1 ( p0 ); SELECT p0 AS 學生人數(shù); 4、定義一個帶輸出參數(shù)和輸出參數(shù)的存儲過程，執(zhí)行該存儲過程，通過輸入?yún)?shù)學號,查到到姓名,同時輸入姓名,。use student;Create procedure getnamebysno(in xh char (10),out name char(20)BeginSelect sname into name from student where sno=xh;End;執(zhí)行set name=null;call getnamebysno('000105',name);select name;5、修改剛建立的存儲過程。

30、ALTER PROCEDURE SELECT_ '6、刪除剛建立的存儲過程。DROP PROCEDURE SELECT_WOMAN 實驗八 觸發(fā)器的插入、刪除、更新和創(chuàng)建1、 對student表創(chuàng)建delete觸發(fā)器，當刪除某個學生記錄時，同時在選課表SC中刪除該學生的選課記錄。mysql 通過use student;Insert Into Student Values('000101', '劉八', '女', 22, '信息科學系');Insert Into SC Values('000101', 

31、9;2', 91);select * from student ; select * from sc ; create trigger t1 AFTER delete on student FOR EACH ROW BEGIN Delete from SC WHERE sno = old.sno; END; 2、 在student表上定義了一個update觸發(fā)程序，用于檢查更新sage時將使用的新值，小于16時取得16,大于40時取40，并更改值。use student;create trigger upd_check before update on student FOR EACH

32、 ROW begin if new.sage<16 then set new.sage=16; elseif new.sage>60 then set new.sage=40; end if; end;select * from student; update student set sage=2 where sno='000105' select * from student ;3、 在student表上定義了一個insert觸發(fā)程序，用于檢查插入ssex時只能取男和女，輸入其它時取NULL。use student;create trigger ins_check

33、 before insert on student FOR EACH ROW begin if new.ssex<>'男' and new.ssex<>'女' then set new.ssex=null; end if; end; select * from student;Insert Into Student Values('000106', '陳規(guī)則', '五', 22, '信息科學系');select * from student;4、 alter trigger

34、5、 drop trigger <觸發(fā)名> on 表名;6、create database trigg;CREATE TABLE t_a ( id smallint(1) unsigned NOT NULL AUTO_INCREMENT, username varchar(20) DEFAULT NULL, groupid mediumint(8) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (id) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;CREATE

35、TABLE t_b ( id smallint(1) unsigned NOT NULL AUTO_INCREMENT, username varchar(20) DEFAULT NULL, groupid mediumint(8) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (id) ENGINE=MyISAM AUTO_INCREMENT=57 DEFAULT CHARSET=latin1;CREATE TRIGGER tr_a_insert AFTER INSERT ON t_a FOR EACH ROW BEGIN INSERT

36、 INTO t_b SET username = NEW.username, groupid=NEW.groupid; END;INSERT INTO t_a (username,groupid) VALUES ('',123); SELECT id,username,groupid FROM t_a; SELECT id,username,groupid FROM t_b;CREATE TRIGGER tr_a_delete AFTER DELETE ON t_a FOR EACH ROW BEGIN DELETE FROM t_b WHERE username=Old.us

37、ername AND groupid=OLD.groupid; END;delete from t_a where groupid=123;SELECT id,username,groupid FROM t_a;SELECT id,username,groupid FROM t_b;CREATE TRIGGER tr_a_update AFTER UPDATE ON t_a FOR EACH ROW BEGIN IF new.groupid != old.groupid OR old.username != new.username THEN UPDATE t_b SET groupid=NE

38、W.groupid,username=NEW.username WHERE username = OLD.username AND groupid = OLD.groupid; END IF; end; 實驗九 MYSQL權(quán)限與安全1、用戶的創(chuàng)建利用CREATE USER語句創(chuàng)建用戶user1、user2、user3，密碼均為123456。mysql> create user 'user1''localhost' identified by '123456', 'user2''localhost' ident

39、ified by '123456', 'user3''localhost' identified by '123456'Query OK, 0 rows affected (0.00 sec)利用INSERT INTO語句向USER表創(chuàng)建用戶user4，密碼均為123456。mysql> insert into mysql.user(host,user,password,ssl_cipher,x509_issuer,x509_subject) values('localhost','user5'

40、;,password('123456'),'','','');Query OK, 1 row affected (0.00 sec)利用GRANT語句創(chuàng)建用戶user5，密碼均為123456，且為全局級用戶。mysql> grant select,insert,update on *.* to 'user5''localhost' identified by'123456'Query OK, 0 rows affected (0.00 sec)2、用戶授權(quán)（利用GRANT語句）

41、授予user1用戶為數(shù)據(jù)庫級用戶，對數(shù)據(jù)庫StudDB擁有所有權(quán)。mysql> grant all on *.* to 'user1''localhost' identified by'123456'Query OK, 0 rows affected (0.01 sec)授予user2用戶為表級用戶，對StudDB中的student表select，create,drop權(quán)限。mysql> grant select,create,drop on StudDB.student to 'user2''localhos

42、t' identified by'123456'Query OK, 0 rows affected (0.00 sec)授予user3用戶為列級用戶，對StudDB中的student表的sname列用戶select和update權(quán)限。mysql> grant select,update(sname) on StudDB.student to 'user2''localhost' identified by'123456'Query OK, 0 rows affected (0.02 sec)授予user4用戶為過程級

43、用戶，對StudDB.student中的numofstudent存儲過程擁有EXECUTE執(zhí)行的權(quán)限。mysql> grant execute on procedure numofstudent to 'user4''localhost' identified by'123456'Query OK, 0 rows affected (0.00 sec)利用上述建立的用戶連接并登陸MYSQL數(shù)據(jù)庫，對相關(guān)權(quán)限進行驗證。查看權(quán)限mysql> select * from mysql.user where user='test1'Empty set (0.00 sec)mysql> show grants for 'root' 'loc