练习_20110513.doc

姓名：师东青 Not very special It is the only【实验名称】SDQ查询使用【实验拓扑】【实验步骤】一、 建数据库jwgl代码：create database jwgl二、新建数据表student代码：create table student(student_id char(10) primary key,student_name nchar(10)not null,sex int not null,age int not null,birth nchar(10) not null,class_id nchar(10) not null,enteance_date nchar(10) not null,home_addr nchar(30),)insert into student values(g940210,张虹,1,15,1984-9-1,g9902,1999-9-1,南京市鼓楼区平岗3号)insert into student values(g940212,林红,0,22,1984-6-1,g9901,1999-9-1,南京市鼓楼区平岗3号)insert into student values(g940213,曹雨,1,21,1983-12-1,g9902,1999-9-1,南京市鼓楼区平岗3号)insert into student values(g940214,苪华,1,18,1984-9-1,g9901,1999-9-1,南京市鼓楼区平岗3号)insert into student values(g940215,李红【大】,0,17,1983-9-9,g9902,1999-9-1,南京市鼓楼区平岗3号)insert into student values(g940216,李红【小】,0,22,1984-9-8,g9901,1999-9-1,南京市鼓楼区平岗3号)insert into student values(g940217,赵青,1,19,1985-9-9,g9902,1999-9-1,南京市鼓楼区平岗3号)insert into student(student_id,student_name,sex,age,birth,class_id,enteance_date) values(g940218,林海,1,16,1985-10-9,g9901,1999-9-1,NULL) 新建数据表class_info代码：create table class_info(class_id nchar(10) primary key,class_name nchar(20),)insert into class_info values (g9901,网页一班)insert into class_info values (g9902,网页二班)insert into class_info values (g9903,网页三班)建数据表course代码：create table course(class_id nchar(10) ,student_id nchar(10),grade int)insert into course values (1,g940210,60)insert into course values (2,g940212,90)insert into course values (1,g940213,87)insert into course values (2,g940210,88)insert into course values (3,g940210,98)select * from course建数据表course_info代码：create table course_info(courseid int,coursename nchar(10),)insert into course_info values (1,flash)insert into course_info values (2,fireworks)insert into course_info values (3,ps)select * from course_info)select * from course_info三、对student表做如下操作1、 插入一条学生记录，student_id：g940220，student_name：赵凯，sex：1，age：20，birth：1985-1-9，class_id：g9903，entrance_date：1999-9-1，home_addr：江苏市仪征青年路16号。代码：insert into student values(g940220,赵凯,1,20,1985-1-9,g9903,1999-9-1,江苏市仪征青年路16号)2、 把赵青学生的年龄改为20代码：update student set age=20 where student_id=g940217（update student set age=20 where student_name=赵青）3、 显示所有学生的信息代码：select * from student4、 显示所有女同学的信息代码：select * from student where sex=05、 显示所有大于18岁的女生信息代码：select * from student where sex=0 and age186、 显示所有在84年到85年之间出生的学生信息代码：select * from student where birth between 1984 and 19857、 从student表中检索出家庭地址列为空值的同学信息。代码：select * from student where home_addr is null8、 （1）从student表中分别检索出姓张的所有同学的资料；代码：select * from student where student_name like 张%（2）名字的第二个字是“红”或“虹”的所有同学的资料；代码：select * from student where student_name like _红,虹%（3）名字的第二个字不是“红”或“虹”的同学的资料。代码1：select * from student where student_name like _红,虹%代码2：select * from student where student_name not like _红,虹%9、 从student表中分别检索出学生的编号、姓名信息并分别以“学生编号”、“学生姓名”标题显示。代码：select student_id as 学生编号,学生姓名=student_name from student10、从student表中检索编号为g940210,g940215,g940218的学生信息。代码：select * from student where student_id in (g940210,g940215,g940218)（select * from student where student_id=g940210 or student_id=g940215 or student_id=g940218）11、统计学生的最大年龄，最小年龄，平均年龄、年龄总和及学生总数。统计最大年龄代码1：select max(age) from student统计最大年龄代码2：select max(age) as 学生的最大年龄 from student统计最小年龄代码：select min(age) as 学生的最小年龄 from student统计学生的平均年龄代码：select avg(age) as 学生的平均年龄 from student统计学生年龄总和代码：select sum(age) as 学生的年龄总和 from student统计学生总数代码：select count(student_id) as 学生的总数 from student将以上汇总代码：select max(age) as 学生的最大年龄,min(age) as 学生的最小年龄,avg(age) as 学生的平均年龄,count(student_id) as 学生的总数 from student12.显示85年以后出生的学生的姓名,性别,班级及出生日期;代码：select student_name,sex,class_id,birth from student where birth198513.显示g9901班的女生的所有信息;select * from student where class_id=g9901 and sex=014.按出生日期排序显示学生的姓名,性别,出生日期及住址;代码：select student_name,sex,birth,home_addr from student order by birth15.显示每个学生的学号,总分;代码：select student_id,sum(grade) as 总分 from student_course group by student_id16.按学号排序显示每个学生的学号,姓名,课程号及成绩;代码：select student_id,course_id,grade from student_course order by student_id compute sum(grade) by student_id17.显示每个学生的学号,姓名,课程名称及成绩;把查询结果生成视图代码：create view course_new as select student.student_id,student_name,coursename,grade from student join student_course on student.student_id=student_course.student_idjoin course on course.courseid=student_course.course_id18.显示成绩不及格,及80分以上的学生学号,姓名,课程名称及成绩;select student.student_id,student_name,coursename,gradefrom student join student_courseon student.student_id=student_course.student_idjoin courseon course.courseid=student_course.course