数据库作业16个题
现在有一教学管理系统,具体的关系模式如下:
Student (no, name, sex, birthday, class)
Teacher (no, name, sex, birthday, prof, depart)
Course (cno, cname, tno)
Score (no, cno, degree)
其中表中包含如下数据:
Course表:
Score表:
Student表:
Teacher表:
根据上面描述完成下面问题:
(注意:注意保存脚本,尤其是DDL和DML,以便进行数据还原)
DDL
- 写出上述表的建表语句。
student表
create table student(
no int primary key,
name varchar(16),
sex char(6),
birthday varchar(32),
class int );
Teacher表
create table Teacher(
no int primary key auto_increment,
name varchar(16),
sex char(6),
birthday varchar(211),
prof varchar(21),
depart varchar(122));
course表
create table course(
cno int primary key auto_increment,
cname varchar(23),
tno int ));
score表
create table score(
no int primary key,
cno int ,
degree double(8,2));
命令:
DML
2,给出相应的INSERT语句来完成题中给出数据的插入。
数据我放在我的C盘了
mysql> truncate table db1.teacher;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from teacher;
Empty set (0.01 sec)
mysql> LOAD DATA INFILE 'C:\db1_teacher.txt' INTO TABLE teacher;
Query OK, 15 rows affected (0.01 sec)
mysql> truncate table db1.score;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from teacher;
Empty set (0.01 sec)
mysql> LOAD DATA INFILE 'C:\db1_score.txt' INTO TABLE score;
Query OK, 15 rows affected (0.01 sec)
mysql> truncate table db1.student;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from student;
Empty set (0.01 sec)
mysql> LOAD DATA INFILE 'C:\db1_teacher.txt' INTO TABLE student;
Query OK, 15 rows affected (0.01 sec)
mysql> truncate table db1.course;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from course;
Empty set (0.01 sec)
mysql> LOAD DATA INFILE 'C:\db1_teacher.txt' INTO TABLE course;
Query OK, 15 rows affected (0.01 sec)
命令:
单表查询
3,以class降序输出student的所有记录(student表全部属性)
命令:
select * from student order by class desc;