sql.txt

简单实现网上选课

1.数据库建立
CREATE DATABASE database_cs

2.建立课程表

create table Info_course (Course_id varchar(50) not null,
                      Course_name   varchar(50) not null,
                      Teacher_name  varchar(50) not null,
                      Teacher_id    varchar(50) not null,
                      CollectedMan 	int,
                      Capacity 	int,
                      Course_time varchar(50),
                      Course_place varchar(50),
                      credit       float

                      primary key (Course_id));

3.建立学生信息表
  
create table Info_student(Student_name varchar(50) not null,
                   Student_id varchar(50) not null,
                   Dept  varchar(50) ,
                   Major  varchar(50),
                  
                   sex char(4) ,
                   class varchar(50),
                   password varchar(50),
                   primary key (Student_id));

4.建立教师信息表


create table Info_teac(Teacher_name varchar(50) not null,
                   Teacher_id varchar(50) not null,
                   Dept  varchar(50) ,
                   
                  
                   sex char(4) ,
                   Zhicheng  varchar(50),
                   password varchar(50),
                   primary key (Teacher_id));
5.建立管理员信息表

create table Info_admin( Admin_no varchar(50) not null,
                   Admin_name varchar(50) not null,
                 
                   password varchar(50),
                   primary key (Admin_no));
6.建立选课表
create table selected(Stu_name varchar(50) not null,
                   Stu_id varchar(50) not null,
                   Dept  varchar(50) ,
                   
                   class varchar(50),
                   Major varchar(50),
                   Course_name  varchar(50),
                   Course_id    varchar(50),
                   primary key (Course_id,Stu_id));



6.输入课程信息
insert into Info_course values('  ',' ',' ',' ', ,  ,' ',' ', );


7.输入学生信息
insert into Info_student values('  ',' ',' ',' ', ,  ,' ',' ', );

7.输入管理员信息
insert into Info_admin values('1','justin','163122',);

8.插入选课信息

insert into selected(Stu_id,Stu_name,Dept,Major,class,Course_name,Course_id) 
 select s.Student_id,s.Student_name,s.Dept,s.Major,s.class,c.Course_name,c.Course_id
              from Info_student s ,Info_course c
              where  s.Student_id='24'and c.Course_id='001';





8.查询某学生的全部已选课信息(学号为xx)
select cs.Course_id,cs.Course_name,c.Teacher_name,c.Course_time,c.Course_place,c.credit
from selected cs,Info_course c
where cs.Stu_id='xx'and cs.Course_id=c.Course_id;
 
查询学生可选课程名单


select *
from Info_course c
where c.CollectedMan<c.Capacity




9.查询某教师的公布课程情况
select *
from  info_course c,
where c.Teacher_id='xx'




10.查询某教师的某课堂的学生名单

select Stu_name,Stu_id,Dept,Major,class
from  selected cs
where Course_id='001'


11.修改学生信息




update Info_student
set    
where  Info_student.Student_id='xx';


12.修改课程信息表

update Info_course
set    
where  Info_course.Course_id='xx';

13.修改教师信息表

update Info_Teacher
set    
where  Info_Teacher.Teacher_id='xx';

14.修改管理员信息表
update Info_admin
set    
where  Info_admin.admin_no='xx';

15.删除学生信息

delete
from Info_Student s
where s.Student_id='xx';

16.删除教师信息

delete
from Info_Teacher t
where t.teacher_id='xx';
17.删除课程信息

delete
from Info_Course c
where c.Course_id='xx';

18.删除选课信息

delete
from selected cs
where cs.Student_id='xx'and cs.Course_id='xx';

19.删除管理员表信息

delete
from Info_admin
where admin_no='xx';