数据库1

数据库查询

create databasecstp;
usecstp;
/*1:建立学生表*/
create tablestudent (
  学号char(3)primarykey,
  姓名char(4),
  性别char(2),
  年龄int,
  班级char(5)
);
insert intostudentvalues('108','曾华','男',19,'95033');
insert intostudentvalues('105','匡明','男',20,'95031');
insert intostudentvalues('107','王丽','女',20,'95033');
insert intostudentvalues('101','李军','男',19,'95033');
insert intostudentvalues('109','王芳','女',22,'95031');
insert intostudentvalues('103','陆君','男',20,'95031');
/*2:建立教师表*/
create tableteacher(
  教师号char(3)primarykey,
  姓名char(4),
  性别char(2),
  年龄int ,
  级别char(6),
  专业char(8)
);
insert intoteachervalues('804','李成','男',42,'副教授','计算机系');
insert intoteachervalues('856','张旭','男',35,'讲师','电子工程');
insert intoteachervalues('825','王萍','女',28,'助教','计算机系');
insert intoteachervalues('831','刘冰','女',25,'助教','电子工程');
/*3:建立课程表*/
create tablecourse (
  课程号char(5)primarykey,
  课程名char(10),
  教师号char(3),
  foreign key(教师号)references teacher(教师号)
);
insert intocoursevalues('3-105','计算机导论','825');
insert intocoursevalues('3-245','操作系统','804');
insert intocoursevalues('6-166','数字电路','856');
insert intocoursevalues('9-888','高等数学','831');
/*4:建立选课表*/
create tablesc(
  学号char(3),
  课程号char(5),
  primary key(学号,课程号),
  成绩int,
  foreign key(学号)references student(学号),
  foreign key(课程号)references course(课程号)
);
insert into scvalues('103','3-245',86);
insert into scvalues('105','3-245',75);
insert into scvalues('109','3-245',68);
insert into scvalues('103','3-105',92);
insert into scvalues('105','3-105',88);
insert into scvalues('109','3-105',76);
insert into scvalues('101','3-105',64);
insert into scvalues('107','3-105',91);
insert into scvalues('108','3-105',78);
insert into scvalues('101','6-166',85);
insert into scvalues('107','6-166',79);
insert into scvalues('108','6-166',81);
/*5:所有表内容*/
select *from student;
select *from course;
select * from teacher;
select *from sc;