#下面都是做练习用的表结构与测试数据。
#教师表 create table teacher(
teacher_no char(10) primary key,
teacher_name char(10) not null,
teacher_contact char(20) not null )engine=innodb default charset=utf8;
#班级表 create table classes(
class_no int auto_increment primary key,
class_name char(20) not null unique,
department_name char(20) not null )engine=innodb default charset=utf8;
#课程表 create table course(
course_no int auto_increment primary key,
course_name char(20),
up_limit int default 60,
description text not null,
status char(6) default '未审核',
teacher_no char(10) not null unique, constraint course_teacher_fk foreign key (teacher_no) references teacher(teacher_no)
)engine=innodb default charset=utf8;
#学生表 create table student(
student_no char(11) primary key,
student_name char(10) not null,
student_contact char(20) not null,
class_no int, constraint student_class_fk foreign key (class_no) references classes(class_no)
)engine=innodb default charset=utf8;
#选择表 create table choose(
choose_no int auto_increment primary key,
student_no char(11) not null,
course_no int not null,
score tinyint unsigned,
choose_time datetime not null, constraint choose_student_fk foreign key (student_no) references student(student_no), constraint choose_course_fk foreign key (course_no) references course(course_no)
)engine=innodb default charset=utf8;
#向教师表添加测试数据 insert into teacher values('001','张老师','11000000000'); insert into teacher values('002','里老师','12000000000'); insert into teacher values('003','王老师','13000000000');
#向班级表插入数据. insert into classes(class_no,class_name,department_name)values(null,'2012自动化1班','机电工程'); insert into classes(class_no,class_name,department_name)values(null,'2012自动化2班','机电工程'); insert into classes(class_no,class_name,department_name)values(null,'2012自动化3班','机电工程');
#向课程表添加测试数据 insert into course values(null,'java语言程序基础',default,'暂无','已审核','001'); insert into course values(null,'mysql数据库',150,'暂无','已审核','002'); insert into course values(null,'c语言程序设计',230,'暂无','已审核','004');
#向学生表添加测试数据 insert into student values ('2012001','张三','15000000000',1),
('2012002','李四','16000000000',1),
('2012003','王五','17000000000',3),
('2012004','马六','18000000000',2),
('2012005','田七','19000000000',2);
#向choose表添加测试数据 insert into choose values (null,'2012001',2,40,'2015-09-20 11:32:59'),
(null,'2012001',1,50,'2015-09-20 11:33:59'),
(null,'2012002',3,60,'2015-09-20 11:34:59'),
(null,'2012002',2,70,'2015-09-20 11:35:59'),
(null,'2012003',1,80,'2015-09-20 11:36:59'),
(null,'2012004',2,90,'2015-09-20 11:37:59'),
(null,'2012005',3,null,'2015-09-20 11:38:59'),
(null,'2012005',1,null,'2015-09-20 11:39:59');
select 语句是在所有数据库操作中使用频率最高的SQL语句。首先数据库用户编写合法的select语句,mysql客户机将select语句发送给mysql服务实例。mysql服务实例根据改select语句的要求进行解析、编译。然后选择合适的执行计划从表中查找满足特定条件的若干记录,最后按照规定的格式整理成结果集返回给mysql客户机。