搜索
[实验一]createdatabasetest;//（1）分别利用命令行方式将下面各表建立到教学管理数据库中。表的创建存在先后顺序，在实际操作中要注意体会。createtabledepartment(dept_nochar(2)unique,dept_namechar(10)notnull);createtableclass(class_nochar(5)unique,class_namechar(10)notnull,dept_nochar(2)foreignkey(dept_no)referencesdepartment(dept_no));createtablestudent(s_nochar(6)unique,s_namechar(10)notnull,s_sexchar(2)check(s_sex='男'ors_sex='女'),s_birthdaydatetime,s_scorenumeric(5,1),s_addfnumeric(3,1),class_nochar(5),foreignkey(class_no)referencesclass(class_no));createtablecourse(course_nochar(5)unique,course_namechar(20)notnull);createtableteacher(t_nochar(6)unique,t_namechar(10)notnull,t_sexchar(2)check(t_sex='男'ort_sex='女'),t_birthdaydatetime,tech_titlechar(10));createtablechoice(s_nochar(6),course_nochar(5),scorenumeric(5,1)primarykey(s_no,course_no),foreignkey(s_no)referencesstudent(s_no),foreignkey(course_no)referencescourse(course_no));createtableteaching(t_nochar(6),course_nochar(5),primarykey(t_no,course_no),foreignkey(t_no)referencesteacher(t_no),foreignkey(course_no)referencescourse(course_no));//（2）利用命令行方式查看这些基本表的各种信息。select*fromstudent;select*fromclass;select*fromdepartment;select*fromcourse;select*fromteacher;select*fromchoice;select*fromteaching;//（3）为某基本表创建索引，查看其信息，然后删除它。createindexTeat_noonteacher(t_no);dropindexTeat_no;//（4）向教师表中添加数据insertintoteacher(t_no,t_name,t_sex,t_birthday,tech_title)values('2095','我','男','1992-08-01','老子');insertintoteacher(t_no,t_name,t_sex,t_birthday,tech_title)values('2096','爱','女','1995-02-04','庄子');insertintoteacher(t_no,t_name,t_sex,t_birthday,tech_title)values('2097','九','男','1988-05-07','孔子');insertintoteacher(t_no,t_name,t_sex,t_birthday,tech_title)values('2098','五','女','1999-06-08','墨子');insertintoteacher(t_no,t_name,t_sex,t_birthday,tech_title)values('2099','寝','男','1999-03-09','荀子');//（5）修改学生表（student），增加联系电话（phone）属性altertablestudentadds_phonechar(8);//（6）为学生表（student）按出生日期字段建立索引，并练习删除索引createindexStubirthdayonstudent(s_birthday);dropindexStubirthday;[实验二]//（1）向教学管理系统的每个表中录入5条记录。录入时注意体会外键约束。insertintodepartmentvalues('1','信息系统');insertintodepartmentvalues('2','工商管理');insertintodepartmentvalues('3','市场营销');insertintodepartmentvalues('4','会计');insertintodepartmentvalues('5','财务管理');insertintoclassvalues('9501','信息','1');insertintoclassvalues('9502','工商','2');insertintoclassvalues('9503','营销','3');insertintoclassvalues('9504','会计','4');insertintoclassvalues('9505','财务','5');insertintostudentvalues('95011','严二姣','男','1991-04-04','555.5',NULL,'9501','04313887');insertintostudentvalues('95012','严傻姣','男','1995-06-07','666.6',NULL,'9501','04313889');insertintostudentvalues('95013','严二傻','男','1992-11-19','777.7',NULL,'9501','04313886');insertintostudentvalues('95014','严傻傻','男','1993-10-05','888.8',NULL,'9501','04313888');insertintostudentvalues('95015','傻瓜严','女','1990-12-15','999.9',NULL,'9501','04313885');insertintocoursevalues('2091','孔子');insertintocoursevalues('2092','老子');insertintocoursevalues('2093','庄子');insertintocoursevalues('2094','墨子');insertintocoursevalues('2090','荀子');insertintochoicevalues('95011','2091','100');insertintochoicevalues('95012','2092','19');insertintochoicevalues('95013','2093','59');insertintochoicevalues('95014','2094','58');insertintochoicevalues('95015','2090','57');select*fromchoice;insertintoteachingvalues('2095','2091');insertintoteachingvalues('2096','2092');insertintoteachingvalues('2097','2093');insertintoteachingvalues('2098','2094');insertintoteachingvalues('2095','2090');//(2）创建教师表2(teacher2)，向表中录入5条不同的记录，注意与教师表中数据必须不同，主要体现在主键上。createtableteacher2(t_nochar(6)unique,t_namechar(8)notnull,t_sexchar(2)check(t_sex='男'ort_sex='女'),t_birthdaydatetime,tech_titlechar(10),);select*fromteacher2;insertintoteacher2values('2100','驴大傻','男','1990-02-02','鉴宝');insertintoteacher2values('2101','大傻驴','男','1995-03-03','考古');insertintoteacher2values('2102','崴脚驴','男','1992-08-08','哲学');insertintoteacher2values('2103','瘸驴','男','1998-08-09','人类起源');insertintoteacher2values('2104','面板驴','女','1994-09-30','君主论');select*fromteacher2;//（3）用多行数据插入的方法将教师表2中数据录入到教师表。insertintoteacherselect*fromteacher2;*****!!!!!select*fromteacher;//（4）利用SELECTINTO方法创建教师表3(teacher3)，查看教师表3的内容，体会表间数据复制的含义。select*intoteacher3fromteacher2;select*fromteacher3;//（5）删除教师表3中所有数据，删除教师表3。@@@@@!!!deleteteacher3;droptableteacher3;//（6）删除教师表2中数据，删除教师表2。deleteteacher2;droptableteacher2;select*fromteacher2;检查//（7）显示所有学生情况。select*fromstudent;//（9）显示与某个学生某门课程成绩相同的选修该门课程的所有学生的学号、姓名。selectt_no,t_name,t_birthdayfromteacher;//（9）显示与某个学生某门课程成绩相同的选修该门课程的所有学生的学号、姓名。(自连接)selectbb.s_no,s_namefromstudent,choiceaa,choicebbwhereaa.s_no='95011'andaa.course_no='2091'andbb.course_no=aa.course_noandbb.score=aa.scoreandbb.s_no=student.s_no;//（10）显示成绩在60分以上的学生姓名、教师姓名和课程名称。selectstudent.s_name,t_name,course_namefromstudent,choice,teacher,course,teachingwherescore='60'andchoice.s_no=student.s_noandchoice.course_no=course.course_noandchoice.course_no=teaching.course_noandteaching.t_no=teacher.t_noselect*fromchoice;//（11）将某个学生所学课程成绩低于60分的课程列出来。selectcourse_namefromchoice,cours