student.sql

来自「JAVA数据库编程实例随书源码」· SQL 代码 · 共 45 行

	create database "student";					
	drop table studentbase;						
	drop table studentaddress;
	drop table studentclass;
	create table studentbase( 
		student_number		INTEGER NOT NULL,
		name   			VARCHAR(20),
		age    			INTEGER NOT NULL,
		sex    			VARCHAR(2),
		department    		VARCHAR(20),
		primary key (student_number) );
	create table studentaddress(
		number_id		INTEGER NOT NULL,	
		telephone_number	VARCHAR(20),
		Email_address		VARCHAR(30),
		student_number		INTEGER NOT NULL,
		primary key (number_id,student_number));
	create table studentclass(					
		lesson_number		VARCHAR(10) NOT NULL,
		lesson_name		VARCHAR(10),
		lesson_grade		INTEGER NOT NULL,
		lesson_time		INTEGER NOT NULL,
		other_lesson_id		VARCHAR(10),
		primary key (lesson_number) );
	insert into studentbase(student_number,name,age,sex,department) values (200166001,'赵明',20,'女','计算机系');
	insert into studentbase (student_number,name,age,sex,department) values (200166002,'钱晓',21,'男','物理系');
	insert into studentbase (student_number,name,age,sex,department) values (200166003,'孙晨',19,'男','计算机系');
	insert into studentaddress(
number_id,telephone_number,Email_address,student_number) values (1,'010-62000001','[e-mail]',200166001);
	insert into studentaddress (
number_id,telephone_number,Email_address,student_number) 
values(2,'010-63000001','[e-mail]',200166002);
	insert into studentaddress (
number_id,telephone_number,Email_address,student_number)
values(3,'010-63000002','[e-mail]',200166002);
	insert into studentaddress (
number_id,telephone_number,Email_address,student_number)
values(4,'010-64000001','[e-mail]',200166003);
	insert into studentclass (
lesson_number,lesson_name,lesson_grade,lesson_time,other_lesson_id) values ('A01','高等数学',6,100,'B01');
	insert into studentclass (
lesson_number,lesson_name,lesson_grade,lesson_time,other_lesson_id) values ('A02','计算机',8,160,'B02');
	insert into studentclass (
lesson_number,lesson_name,lesson_grade,lesson_time,other_lesson_id) values ('A03','英语',10,200,'B03');