目錄
數(shù)據(jù)庫teaching中的表結(jié)構(gòu)和表記錄。
問題：
答案：
數(shù)據(jù)庫teaching中的表結(jié)構(gòu)和表記錄。
?。?）學(xué)生信息表student
#student表結(jié)構(gòu)
create table if not exists student (
studentno ?char(11) not null comment'學(xué)號(hào)',?
sname char(8) not null comment'姓名',?
sex enum('男', '女') default '男' comment'性別',?
birthdate date not null comment'出生日期',?
entrance int(3) ?null comment'入學(xué)成績',?? ??? ??
phone varchar(12) not null comment'電話',?
Email varchar(20) not null comment'電子信箱',
primary key (studentno)
);
#student表常用數(shù)據(jù)
studentno?? ?sname sex?? ?birthdate ? entrance?? ? ?phone?? ? ? ? ?Email
20112100072?? ?許東方 ?男?? ?2002/2/4?? ? ? ? 658?? ? ?12545678998?? ?su12@163.com?
20112111208?? ?韓吟秋 ?女?? ?2002/2/14?? ?666?? ? ?15878945612?? ?han@163.com?
20120203567?? ?封白玫 ?女?? ?2003/9/9?? ? ? ? 898?? ? ?13245674564?? ?feng@126.com
20120210009?? ?崔舟帆 ?男?? ?2002/11/5?? ?789?? ? ?13623456778?? ?cui@163.com?
20123567897?? ?趙雨思 ?女?? ?2003/8/4 ?? ?879?? ? ?13175689345 ? ?pinan@163.com
20125121109?? ?梁一葦 ?女?? ?2002/9/3 ??? ?777?? ? ?13145678921?? ?bing@126.com?
20126113307?? ?姚扶竹 ?女?? ?2003/9/7 ??? ?787?? ? ?13245678543?? ?zhu@163.com
21125111109?? ?敬秉辰 ?男?? ?2004/3/1?? ? ? ? 789?? ? ?15678945623 jing@sina.com?
21125221327?? ?何桐影 ?女?? ?2004/12/4?? ?879?? ? ?13178978999 ?he@sina.com?
21131133071?? ?崔依歌 ?男?? ?2002/6/6?? ? ? ? 787 ? 15556845645 ?cui@126.com?
21135222201?? ?夏文斐 ?女?? ?2005/10/6?? ?867?? ? ?15978945645 ?xia@163.com?
21137221508?? ?趙臨江 ?男?? ?2005/2/13?? ?789?? ? ?12367823453 ?ping@163.com?
?。?）課程信息表course
#course表結(jié)構(gòu)
create table if not exists course (
courseno ?char(6) not null,?
cname ?char(6) not null,?
type char(8) not null, ?
period int(2) not null,?
exp int(2) not null,
term int(2) not null,
primary key (courseno)
);?
#course表常用數(shù)據(jù)
courseno?? ?cname?? ??? ?type ? period ? ?exp?? ?term
c05103?? ??? ?高等數(shù)學(xué)?? ??? ?必修?? ??? ?64?? ??? ?16?? ? 2
c05109?? ??? ?C語言?? ??? ?必修?? ??? ?48?? ??? ?16?? ? 2
c05127?? ??? ?數(shù)據(jù)結(jié)構(gòu)?? ??? ?必修?? ??? ?64?? ??? ?16?? ? 2
c05138?? ??? ?軟件工程?? ??? ?選修?? ??? ?48?? ??? ?8?? ? 5
c06108?? ??? ?機(jī)械制圖?? ??? ?必修?? ??? ?60?? ??? ?8?? ? 2
c06127?? ??? ?機(jī)械設(shè)計(jì)?? ??? ?必修?? ??? ?64?? ??? ?8?? ? 3
c06172?? ??? ?鑄造工藝?? ??? ?選修?? ??? ?42?? ??? ?16?? ? 6
c08106?? ??? ?經(jīng)濟(jì)法?? ??? ?必修?? ??? ?48?? ??? ?0?? ? 7
c08123?? ??? ?金融學(xué)?? ??? ?必修?? ??? ?40?? ??? ?0?? ? 5
c08171?? ??? ?會(huì)計(jì)軟件?? ??? ?選修?? ??? ?32?? ??? ?8?? ? 8
?。?）成績信息表score
#score表結(jié)構(gòu)
create table if not exists score(
studentno ?char(11) not null,?
courseno ?char(6) not null,?
daily float(4,1) default 0,?
final float(4,1) default 0,
primary key (studentno , courseno)?
);?
#score表常用數(shù)據(jù)
studentno?? ?courseno ?? ?daily?? ?final
20112100072?? ?c05103?? ??? ?99 ?? ??? ?92
20120203567?? ?c05103?? ? ??? ?78?? ??? ?67
20120210009?? ?c05103?? ??? ?65?? ??? ?98
20125121109?? ?c05103?? ??? ?88?? ??? ?79
21125111109?? ?c05103?? ??? ?96?? ??? ?97
21137221508?? ?c05103?? ??? ?77?? ??? ?92
20112100072?? ?c05109?? ??? ?95?? ??? ?82
20120203567?? ?c05109?? ??? ?87?? ??? ?86
20125121109?? ?c05109?? ??? ?77?? ??? ?82
20126113307?? ?c05109?? ??? ?89?? ??? ?95
21125111109?? ?c05109?? ??? ?87?? ??? ?82
21125221327?? ?c05109?? ??? ?89?? ??? ?95
20120210009?? ?c05138?? ??? ?88?? ??? ?89
21137221508?? ?c05138?? ??? ?74?? ??? ?91
20112111208?? ?c06108?? ??? ?77?? ??? ?82
20120210009?? ?c06108?? ??? ?79?? ??? ?88
20123567897?? ?c06108?? ??? ?99?? ??? ?99
20126113307?? ?c06108?? ??? ?78?? ??? ?67
20112111208?? ?c06127?? ??? ?85?? ??? ?91
20120203567?? ?c06127?? ??? ?97?? ??? ?97
20112111208?? ?c06172?? ??? ?89?? ??? ?95
21125221327?? ?c06172?? ??? ?88?? ??? ?62
21131133071?? ?c06172?? ??? ?78?? ??? ?95
21125111109?? ?c08106?? ??? ?77?? ??? ?91
21135222201?? ?c08106?? ??? ?91?? ??? ?77
21137221508?? ?c08106?? ??? ?89?? ??? ?62
21131133071?? ?c08123?? ??? ?78?? ??? ?89
21135222201?? ?c08123?? ??? ?79?? ??? ?99
20112100072?? ?c08171?? ??? ?82?? ??? ?69
20125121109?? ?c08171?? ??? ?85?? ??? ?91
21131133071?? ?c08171?? ??? ?88?? ??? ?98
21135222201?? ?c08171?? ??? ?85?? ??? ?92
（4）教師信息表teacher
#teacher表結(jié)構(gòu)
create table if not exists teacher (
teacherno ?char(6) not null comment '教師編號(hào)',?
tname ?char(8) not null comment'教師姓名',?
major ?char(10) not null comment '專業(yè)',?
prof char(10) not null comment '職稱',
department char(16) not null comment '部門',
primary key (teacherno)
);?
#teacher表常用數(shù)據(jù)
teacherno?? ?tname?? ??? ?major?? ??? ?prof ?? ?department
t05001?? ??? ?蘇超然?? ??? ?軟件工程?? ??? ?教授?? ??? ?計(jì)算機(jī)學(xué)院
t05002?? ??? ??？捎^?? ??? ?會(huì)計(jì)學(xué)?? ??? ?助教?? ??? ?管理學(xué)院
t05003?? ??? ?孫釋安?? ??? ?網(wǎng)絡(luò)安全?? ??? ?教授?? ??? ?計(jì)算機(jī)學(xué)院
t05011?? ??? ?盧敖治?? ??? ?軟件工程?? ??? ?副教授?? ?計(jì)算機(jī)學(xué)院
t05017?? ??? ?茅佳峰?? ??? ?軟件測試?? ??? ?講師?? ??? ?計(jì)算機(jī)學(xué)院
t06011?? ??? ?夏期年?? ??? ?機(jī)械制造?? ??? ?教授?? ??? ?機(jī)械學(xué)院
t06023?? ??? ?盧釋舟?? ??? ?鑄造工藝?? ??? ?副教授?? ?機(jī)械學(xué)院
t07019?? ??? ?韓庭宇?? ??? ?經(jīng)濟(jì)管理?? ??? ?講師?? ??? ?管理學(xué)院
t08017?? ??? ?白成園?? ??? ?金融管理?? ??? ?副教授?? ?管理學(xué)院
t08058?? ??? ?孫有存?? ??? ?數(shù)據(jù)科學(xué)?? ??? ?副教授?? ?計(jì)算機(jī)學(xué)院
?。?）紐帶表teach_course
#teach_course表結(jié)構(gòu)
create table if not exists teach_course (
teacherno char(6) not null,?
courseno ?char(6) not null,?
primary key (teacherno,courseno)?
);
#teach_course表常用數(shù)據(jù)
teacherno ?courseno
t05001?? ??? ?c05103
t05002?? ??? ?c05109
t05003?? ??? ?c05127
t05011?? ??? ?c05138
t05017?? ??? ?c06108
t05017?? ??? ?c06172
t06011?? ??? ?c06127
t06023?? ??? ?c05127
t06023?? ??? ?c06172
t07019?? ??? ?c08106
t08017?? ??? ?c08123
t08058?? ??? ?c08171
（6）選課信息表se_course
#se_course表結(jié)構(gòu)
create table se_course
(sc_no int(6) not null auto_increment,?
studentno ?char(11) not null,?
courseno ?char(6) not null,?
teacherno char(6) not null, ?
score ? int(3) ? ?null,
sc_time timestamp not null default now(),?
primary key (sc_no)
);
#se_course表常用數(shù)據(jù)示例
sc_no ?studentno ? ?courseno ?teacherno ?score ? sc_time ? ? ??
1 ? ? ? 21125111109 ? c06172 ? ?t05017 ? ? NULL ? ?2020-12-09 18:33:45
2 ? ? ? 20120210009?? ?c06108?? ? ?t06023 ? ? NULL ? ?2020-12-24 18:30:15
3 ? ? ? 20123567897?? ?t01239?? ? ?t05003 ? ? NULL ? ?2020-12-26 18:09:09
?
問題：
（1）在course表的cname列上創(chuàng)建索引IDX_cname。
（2）在student表的studentno和phone列上創(chuàng)建唯一索引uq_stu。并輸出student表中的記錄，查看輸出結(jié)果的順序。
（3）創(chuàng)建一個(gè)視圖v_teacher，查詢所有“計(jì)算機(jī)學(xué)院”教師的信息。
（4）創(chuàng)建一個(gè)視圖v_avgstu，查詢每個(gè)學(xué)生的學(xué)號(hào)、姓名及平均分，并且按照平均分降序排序。
（5）修改v_teacher的視圖定義，添加with check option選項(xiàng)。
（6）通過視圖v_teacher向基表teacher中分別插入數(shù)據(jù)（'t05039'，'張馨月'，'計(jì)算機(jī)應(yīng)用'， '講師'， '計(jì)算機(jī)學(xué)院）和（'t06018'， '李書誠'， '機(jī)械制造'，'副教授'，'機(jī)械學(xué)院'），并查看插入數(shù)據(jù)情況。
（7）通過視圖v_teacher將基表teacher中教師編號(hào)為t05039的教師職稱修改為‘副教授’。
答案：
1.
create index IDX_cname on course (cname);
select * from course;
2.
create unique index uq_stu on student (studentno, phone);
select * from student;
3.
create view v_teacher as select * from teacher where teacher.department='計(jì)算機(jī)學(xué)院';
select * from v_teacher;文章來源：http://www.zghlxwxcb.cn/news/detail-461576.html
4.
create view v_avgstu as select student.studentno,student.sname,avg(score.final) as avg from student join score on student.studentno=score.studentno group by score.studentno order by avg desc;
select * from v_avgstu;
5.
create view v_teacher as select * from teacher where teacher.department='計(jì)算機(jī)學(xué)院' with check option;
select * from v_teacher;
6.（注意：這里需要使用第3題創(chuàng)建的視圖，因?yàn)檫@里插入了機(jī)械學(xué)院的數(shù)據(jù)）
insert into v_teacher(teacherno,tname,major,prof,department) values('t05039','張馨月','計(jì)算機(jī)應(yīng)用','講師','計(jì)算機(jī)學(xué)院');
insert into v_teacher(teacherno,tname,major,prof,department) values('t06018', '李書誠', '機(jī)械制造','副教授','機(jī)械學(xué)院');
select * from v_teacher;
7.
update v_teacher set prof='副教授' where teacherno='t05039';
select * from v_teacher;
文章來源地址http://www.zghlxwxcb.cn/news/detail-461576.html
到了這里，關(guān)于數(shù)據(jù)庫作業(yè)的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
