mysql數(shù)據(jù)庫實(shí)驗(yàn)答案

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

2、no， jbgz， zwgz，hj) ；Create Database Student default character set utf8 default COLLATE utf8_bi;nUse Student;Create Table Student(SNo char(20) primary key,SName char(20) ,SSex char(4) default ' 男',SAge int) ENGINE=InnoDB ;Create Table Course(CNo char(20) primary key,CName char(20) NOT NULL,CR

3、edit float) ENGINE=InnoDB ;Create Table SC(SNo char(20) NOT NULL,CNo char(20) NOT NULL,Grade float,Primary Key(SNo, CNo),Foreign Key(SNo) References Student(SNo) On Delete Cascade,Foreign Key(CNo) References Course(CNo) ENGINE=InnoD B;Create Table T(TNo char(20) Primary Key,TName char(20) NOT NULL,T

4、Sex char(4) default ' 男 ',1birthday DateTime,dept char(20),title char(20),address char(20) ENGINE=InnoDB ;Create Table Salary(TNo char(20) NOT NULL,jbgz float,zwgz float,hj float,Foreign Key(TNo) References T(TNo) On Delete Cascade) ENGINE=InnoDB ;2、用alter修改基本表(1)在已存在的學(xué)生表student中增加一個(gè)sdept (系

5、)的新的屬性列；alter table Student add Dept char(20);(2)將學(xué)生表student中sname屬性列的數(shù)據(jù)類型修改為變長(zhǎng)字符串varchar(10)。alter able Student modify colum sname varchar(10)3、建立一個(gè)臨時(shí)表，然后將其刪除Create Table temp (ANo char(20) NOT NULL,B float, C char(10) )Drop table temp實(shí)驗(yàn)二建立與刪除索引1、用create index學(xué)生表student的學(xué)號(hào)sno上建立聚簇索引。Create Clustere

6、d Index SNo_Index On Student(SNo);2、在學(xué)生表student中，為姓名 snameit立非聚簇索引。Create Index SName_Index On Student(SName);3、在課程表的課程號(hào)Cno上建立唯一索引。Create Unique Index CNo_Index On Course(CNo);4、在選課表的學(xué)號(hào)snoK成績(jī)Grade上建立復(fù)合索引，要求學(xué)號(hào)為升序，學(xué)號(hào)相同時(shí)成績(jī)?yōu)?降序。Create Index SCNo_Index On sC(SNo ASC, Grade DESC);5>用drop刪除學(xué)生表student的索引

7、Index Student.SNo_Index;6、增加學(xué)生表student中姓名唯一約束。Alter Table Student Add Unique(SName);7、增加學(xué)生表student中性另1J 男''女，唯一約竦);Alter Table Stud8、增加學(xué)生表ent Add Constraint:SSex check(pSex男''or student中年齡l825歲約束。SSex ='女<=25); 9K增加選課表SC中學(xué)號(hào)sno的外碼約束Alter Table SC AddForeign Key(SNo) references

8、Student(SNo);Alter Table Student Add Constraint:SAge check(SAge >= 18 And SAge日驗(yàn)三數(shù)巧的插入、更新斗刪除操作1、用insert輸入婁據(jù)。生表student的數(shù)據(jù)991201991202991101張三李四王五991102991103000101凍六 吳七 劉八222123192422男 男 男男 女 女wa索信息系數(shù)學(xué)系 計(jì)算機(jī) 索數(shù)學(xué)系信息系Insert Into Stude系');Insert Into :t Values('991201'mK二,'男',22,&#

9、39;計(jì)算機(jī)科學(xué)與技術(shù)tfflenwouesM1202lW，F(xiàn) 'BHBS，)Insert Into Student Values('991101'二五'，男',23,數(shù)理系');Insert Into Student Values('991102',陳六，男，甘,計(jì)算機(jī)科 j與技術(shù)系')；Insert Into Student Values('991103'Hr ','女',2,數(shù)理系');Insert Into Student Values('000101劉八,女

10、',22,'信息科學(xué)系')；課L表course的或據(jù)I3345數(shù)據(jù)庫原理 操作系統(tǒng)Insert Into Course Values（'1',數(shù)學(xué)',5）;Insert Into Course Values（'2',數(shù)據(jù)結(jié)構(gòu)',4）;Insert Into Course Values（'3',程序設(shè)計(jì)',2）;Insert Into Course Values（'4',數(shù)據(jù)庫原理',3）;Ipsert Into Coursp Values（'5',操作系統(tǒng)&#

11、39;，3）;選課表SC的數(shù)991201，9912019912019912019911029911020001011 一k322-90-8085-90859891Insert Into SC Values（'991201', '1', 90）;Insert Into SC Values（'991201', '5', 80）;Insert Into SC Values（'991201', '3', 85）;Insert Into SC Values（'991201', '4&#

12、39;, 90）;InsertInsertnto SC Values（'991102', '1', 85）;nto SC Values（'991102', '2', 98）;Insert Into SC Values（'000101', '2', 91）;T的數(shù)據(jù)0001|張三 男1968-10 ”信息副教授 潭0002李四女1001王五男1008凍六男1956-11信息教授沙 湘 潭 北 京1973-07計(jì)算機(jī)講師1970-08計(jì)算機(jī)副教授','男','1968-1

13、0-Insert Into T Values（'0001','張三10','信息科學(xué)系,副教授','湘潭'）;','女','1956-11-Insert Into T Values（'0002', ' 李四10','信息科學(xué)系,'教授','長(zhǎng)沙'）;Insert Into T Values（'1001',王五,'男,'1973-07-'計(jì)算j 機(jī)科學(xué)與技術(shù),講師,湘潭'）;20&#

14、39;,系Insert Into T Values('1008', '陳六', '男', '1970-08-20',計(jì)算機(jī)科學(xué)與技'', '副教授', '北京術(shù)系');基本表Salary的數(shù)據(jù)00011000300000215005001001800200130020001000Insert Into Salary Values('0001', 1000, 300, 1300);4Insert Into Salary Values（'0002', 15

15、00, 500, 2000）;Insert Into Salary Values（'1001', 800, 200, 1000）;*/2、用delete刪除數(shù)據(jù)記錄（ 1）刪除教師表T 中教師號(hào)為0001 的元組。（ 2）刪除教師表T 中的全部數(shù)據(jù)。where Tnoupdate t set birthday='1961-10-04' ='0001' Delete From T;3、用update更新數(shù)據(jù)記錄 把 0001號(hào)教師的基本工（ 1） 資加100。把所有教師的基本工資（ 2） 都加100。Update Salary Set jbgz =

16、 jbgz + 100 Where TNo ='0001' Update Salary Set jbgz = jbgz + 100實(shí)驗(yàn)四數(shù)據(jù)的查詢（ 3） 詢，用select檢索（ 4） 詢所有學(xué)生的基本情況。select * from student;（ 5） 詢教師每月應(yīng)交納的個(gè)人所得稅。select hj*0.005 as monthshui from Salary;（ 3）查詢張三與李四兩位同學(xué)的基本情況。select * from student where sname=K三'or sname='李四'（ 4）查詢9911 班學(xué)生的基本信息（規(guī)

17、定學(xué)生學(xué)號(hào)的前四位是班級(jí)號(hào)）select * from student where sno like '9911%'（ 5）查詢所有年齡在20 歲以下的學(xué)生姓名及其年齡。 select sname,sage from student where sage<20;（ 6）查詢選修了2 門以上課程的學(xué)生學(xué)號(hào)。select sno from SC group by snohaving count(*)>2;2、多表查詢，用select檢索5（ 1）查詢教師的收入情況，包括教師號(hào)、姓名及月總收入。select T.Tno,Tname, hj / 不能寫成 select Tn

18、o因?yàn)?Tno 不明確from T,Salary where T.Tno=Salary.Tno;（2）查詢每個(gè)學(xué)生的學(xué)號(hào)、姓名、選修課程及成績(jī)。select student.sno,sname,cno,grade from student,sc where student.sno=sc.sno;（3）查詢每一門課的間接先修課。select CA.cno ASSM 程號(hào)，CB.PreCourse AS接先修課 號(hào) from course CA,course CBwhere CA.PreCourse=CB.cno and CB.PreCourse is not null;（4）查詢有相同地址的兩位

19、教師的信息。select *from T Txwhere Tx.address in （select addressfrom T Ty where Tx.Tname<>Ty.Tname）;select * from T Tx, T Ty where Tx. address=Ty. Address and Tx.Tname<>Ty.Tname （5）查詢選修2 號(hào)課程且成績(jī)?cè)?0分以上的所有學(xué)生。select * from student,SC where student.sno=SC.sno and SC.cno='2' and SC.grade>

20、90; （6）查詢與王五在同一個(gè)系學(xué)習(xí)的學(xué)生。select *from studentwhere sdept=（select sdept from student where sname=EEZEL '）;實(shí)驗(yàn)五視圖1、建立男學(xué)生的視圖，屬性包括學(xué)號(hào)、姓名、選修課程名和成績(jī)。create view boystudent_viewas select student.sno,sname,cno,gradefrom student,SCwhere student.ssex =男'and student.sno=SC. sno;2、在男學(xué)生視圖中查詢平均成績(jī)大于80分的學(xué)生學(xué)號(hào)與姓名。

21、select sno,snamefrom boystudent_viewgroup by sno,snamehaving AVG(grade)>803、建立信息系選修了1 號(hào)課程的學(xué)生的視圖。create view xinxi_view1as select student.sno,sname,ssex,sagefrom student,SCwhere student.sdept=W息'and student.sno=SC.sno and SC.cno='1'4、建立信息系選修了1 號(hào)課程且成績(jī)?cè)?0 分以上的學(xué)生的視圖。create view xinxi_view

22、2as select student.sno,sname,sage,ssexfrom student,SCwhere student.sdept=W息'and student.sno=SC.sno and SC.cno='1' and SC.grade>90 5、建立計(jì)算機(jī)系選修了2 號(hào)課程的學(xué)生的視圖。create view jisuanji_viewas select student.sno,sname,sage,ssexfrom student,SCwhere student.sdept卦算機(jī)'and student.sno=SC.sno and S

23、C.cno='2'6、建立一個(gè)學(xué)生出生年份的視圖。create view year_viewas select student.sno,sname,2007-sage as birthdayfrom student7、建立一個(gè)計(jì)算機(jī)系學(xué)生的視圖，并要求在進(jìn)行修改、插入操作時(shí)，仍然要確保視 圖只有計(jì)算機(jī)系的學(xué)生。create view jisuanji2_viewas select student.sno,sname,sage,ssexfrom studentwhere sdept=# 算機(jī)' with check option8、向?qū)W生視圖中插入一條新記錄(951101

24、，錢進(jìn)，男，20)create view student_view1as select sno,sname,ssex,sagefrom student;insert into student_view1 values（'95110微進(jìn)','男',20）平”9、將學(xué)生視圖中學(xué)號(hào)為991101的學(xué)生姓名改為“劉平7update student_view1set sname訓(xùn)平平,where sno='991101'10、刪除計(jì)算機(jī)系視圖中學(xué)號(hào)為991201的記錄。deletefrom jisuanji2_viewwhere sno='9912

25、01'11、刪除學(xué)生出生年份的視圖。drop view year_view;實(shí)驗(yàn)六集合函數(shù)的應(yīng)用1、使用selec暗旬查詢( 1) 9911 班學(xué)生的人數(shù)和平均成績(jī)(規(guī)定學(xué)生學(xué)號(hào)的前四位是班級(jí)號(hào))SELECT COUNT(sno) as 總?cè)藬?shù),AVG(sc.grade)A肝均分 FROM scWHERE sno like 9912% ( 2)每個(gè)學(xué)生的學(xué)號(hào)和相應(yīng)的平均成績(jī)SELECT sno, AVG(grade) FROM,scGROUP BY student.sno( 3)成績(jī)大于90分的學(xué)生學(xué)號(hào)SELECT sno,MAX(sc.grade) as maxgrade FROM

26、scGROUP BY snoHAVING MAX(grade)>90( 4)月工資大于平均工資的教師號(hào)SELECT TNo FROM SalaryWHERE hj>=( SELECT A VG(hj)from Salary )2、實(shí)驗(yàn)完成后，撤消建立的基本表和視圖。創(chuàng)建、執(zhí)行、修改和刪除存儲(chǔ)過程1、建立查詢計(jì)算機(jī)系女學(xué)生的存儲(chǔ)過程。Use student;CREATE PROCEDURE SELECT_WOMAN()8BEGINSELECT * FROM student where ssex=女'end;call select_wanman();2、定義一個(gè)存儲(chǔ)過程不帶參數(shù)

27、，作用是查詢選修了課程學(xué)生的學(xué)號(hào)，并執(zhí)行這個(gè)存儲(chǔ) 過程。use student;Create procedure numofsc()BEGINselect sno from SC group by snohaving count(*)>0;End;call numofsc();3、定義一個(gè)帶輸出參數(shù)的存儲(chǔ)過程，作用是查詢Student表中的學(xué)生個(gè)數(shù)， 并賦值給輸出參數(shù)執(zhí)行該存儲(chǔ)過程，同時(shí)通過輸出參數(shù)來輸出學(xué)生個(gè)數(shù)。? CREATE PROCEDURE proc1(OUT s int)? BEGINSELECT COUNT(*) INTO s FROM student;END;，行 Cal

28、l proc1 ( p0 );SELECT p0 AS '學(xué)生人數(shù) '4、定義一個(gè)帶輸出參數(shù)和輸出參數(shù)的存儲(chǔ)過程，執(zhí)行該存儲(chǔ)過程，通過輸入?yún)?shù)學(xué)號(hào)，查到到姓名，同時(shí)輸入姓名 ,。use student;Create procedure getnamebysno(in xh char (10),out name char(20)BeginEnd;執(zhí)行set name=null;call getnamebysno('000105',name);select name;95、修改剛建立的存儲(chǔ)過程。ALTER PROCEDURE SELECT_ '6、刪除剛建立

29、的存儲(chǔ)過程。DROP PROCEDURE SELECT_WOMAN實(shí)驗(yàn)八觸發(fā)器的插入、刪除、更新和創(chuàng)建1、對(duì)student表創(chuàng)建delete觸發(fā)器，當(dāng)刪除某個(gè)學(xué)生記錄時(shí)，同時(shí)在選課表SC中刪除該學(xué)生的選課記錄。mysql 通過use student;Insert Into Student Values('000101'劉','女',22,'信息科學(xué)系')；Insert Into SC Values('000101', '2', 91);select * from student ;select * from

30、 sc ;create trigger t1 AFTER delete on studentFOR EACH ROW BEGINDelete from SC WHERE sno = old.sno;END;2、在student表上定義了一個(gè)update觸發(fā)程序，用于檢查更新sage寸將使用的新值，小于16時(shí)取得16,大于40 時(shí)取40，并更改值。use student;create trigger upd_check before update on studentFOR EACH ROWbeginif new.sage<16 thenset new.sage=16;elseif new

31、.sage>60 thenset new.sage=40;end if;end;select * from student;update student set sage=2 where sno='000105'10select * from student ;3、在student表上定義了一個(gè)insert觸發(fā)程序，用于檢查插入ssex時(shí)只能取男和女，輸入其它時(shí)取 NULL 。use student;create trigger ins_check before insert on studentFOR EACH ROWbeginif new.ssex<>男&

32、#39;and new.ssex<>c ' thenset new.ssex=null;end if;end;select * from student;Insert Into Student Values('000106'陳規(guī)則，五',22,信息科學(xué)系'); select * from student;4 alter trigger5 drop trigger <觸發(fā)名> on 表名 ;6 create database trigg;CREATE TABLE t_a (id smallint(1) unsigned NOT NU

33、LLAUTO_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 TABLE t_b (id smallint(1) unsigned NOT NULLAUTO_INCREMENT, username varchar(20)DEFAULT NULL,groupid m

34、ediumint(8) unsigned NOT NULL DEFAULT'0', PRIMARY KEY ('id') ENGINE=MyISAM AUTO_INCREMENT=57 DEFAULT CHARSET=latin1;CREATETRIGGER tr_a_insert AFTER INSERT ON t_aFOR EACH ROW BEGININSERT INTO t_b SET username = NEW.username,groupid=NEW.groupid; END;11INSERT INTO t_a (username,groupid)

35、 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_aFOR EACH ROW BEGINANDDELETE FROM 't_b' WHERE username=Old.username groupid=OLD.groupid;END;delete from t_a where groupid=123;SELECT id,user

36、name,groupid FROM t_a;SELECT id,username,groupid FROM t_b;CREATE TRIGGER tr_a_update AFTER UPDATE ON t_aFOR EACH ROW BEGINIF new.groupid != old.groupid OR old.username != new.usernameTHEN UPDATE t_b SETgroupid=NEW.groupid,username=NEW.usernameWHERE username = OLD.username AND groupid = OLD.groupid;E

37、ND IF;end;實(shí)驗(yàn)九MYSQL 權(quán)限與安全1、用戶的創(chuàng)建利用CREATE USER語句創(chuàng)建用戶useH、user2 user3,密碼均為'123456mysql> create user 'user1''localhost' identified by '123456', 'user2''localhost' identified by '123456', 'user3''localhost' identified by '123456&#

38、39;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',password('123456'),'','','');12Query OK, 1 row affect

39、ed (0.00 sec)利用GRANT語句創(chuàng)建用戶user5,密碼均為'123456 ,且為全局級(jí)用戶。mysql> grant select,insert,update on *.* to 'user5''localhost' identified by'123456'Query OK, 0 rows affected (0.00 sec)2、用戶授權(quán)(利用GRANT 語句)授予useH用戶為數(shù)據(jù)庫級(jí)用戶，對(duì)數(shù)據(jù)庫StudDB擁有所有權(quán)。mysql> grant all on *.* to 'user1'&

40、#39;localhost' identified by'123456'Query OK, 0 rows affected (0.01 sec)授予user2用戶為表級(jí)用戶，對(duì) StudDB中的student表select create,dro限 限。mysql> grant select,create,drop on StudDB .student to 'user2''localhost' identified by'123456'Query OK, 0 rows affected (0.00 sec)授予use

41、r3用戶為列級(jí)用戶，對(duì) StudDB中的student表的snamgU用戶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用戶為過程級(jí)用戶，對(duì) StudDB .student中的numofstudent存儲(chǔ)過程擁有 EXECUTE 執(zhí)行的權(quán)限。mysql> grant ex

42、ecute on procedure numofstudent to 'user4''localhost' identifiedby'123456'Query OK, 0 rows affected (0.00 sec)利用上述建立的用戶連接并登陸MYSQL 數(shù)據(jù)庫，對(duì)相關(guān)權(quán)限進(jìn)行驗(yàn)證。查看權(quán)限mysql> select * from mysql.user where user='test1'Empty set (0.00 sec)mysql> show grants for 'root' 'localhost'13select * from mysql.user where user='root'mysql> delete from mysql.user where user="root" and host="localhost"Query OK, 1 row affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)刪除權(quán)限mysql> d