select Student.Sname,course.cname,score from Student,SC,Course ,Teacher where Student.s_id=SC.s_id and SC.c_id=Course.c_id and sc.t_id=teacher.t_id and Teacher.Tname='tname553' and SC.score=(selectmax
(score)from SC where sc.t_id=teacher.t_Id);
selectmax(score) from SC ,Teacher where sc.t_id=teacher.t_Id and Teacher.Tname='tname553'; select sc.t_id,sc.s_id,score from SC ,Teacher where sc.t_id=teacher.t_Id and score=590 and Teacher.Tname='tname553'; select Student.Sname,course.cname,score from Student,SC ,course where Student.s_id=SC.s_id and sc.s_id in (20769800,48525000,26280200) and course.c_id = sc.c_id;
use stu; droptableifexists student; createtable student ( s_id int(11) notnull auto_increment , sno int(11), sname varchar(50), sage int(11), ssex varchar(8) , father_id int(11), mather_id int(11), note varchar(500), primary key (s_id), uniquekey uk_sno (sno) ) engine=innodbdefaultcharset=utf8mb4; truncatetable student; delimiter $$ dropfunctionif
exists insert_student_data $$ createfunction insert_student_data() returnsintdeterministic begin declare i int; set i=1; while i<50000000 do insertinto student values(i ,i, concat('name',i),i,casewhenfloor(rand()*10)%2=0then'f'else'm'end,floor(rand()*100000),floor(rand()*1000000),concat('note',i) ); set i=i+1; endwhile; return 1; end$$ delimiter ; select insert_student_data(); selectcount(*) from student; use stu; createtable course ( c_id int(11) notnull auto_increment , cname varchar(50) note varchar(500), primary key (c_id) ) engine
=innodbdefaultcharset=utf8mb4; truncatetable course; delimiter $$ dropfunctionifexists insert_course_data $$ createfunction insert_course_data() returnsintdeterministic begin declare i int; set i=1; while i<=1000 do insertinto course values(i , concat('course',i),floor(rand()*1000),concat('note',i) ); set i=i+1; endwhile; return 1; end$$ delimiter ; select insert_course_data(); selectcount(*) from course; use stu; droptableifexists sc; createtable sc ( s_id int(11), c_id int(11), t_id int(11), score int(11) ) engine
=innodbdefaultcharset=utf8mb4; truncatetable sc; delimiter $$ dropfunctionifexists insert_sc_data $$ createfunction insert_sc_data() returnsintdeterministic begin declare i int; set i=1; while i<=50000000 do insertinto sc values( i,floor(rand()*1000),floor(rand()*10000000),floor(rand()*750)) ; set i=i+1; endwhile; return 1; end$$ delimiter ; select insert_sc_data(); commit; select insert_sc_data(); commit; createindex idx_s_id on sc(s_id) ; createindex idx_t_id on sc(t_id) ; createindex idx_c_id on sc(c_id) ; selectcount(*) from sc; use stu; droptableifexists teacher; createtable teacher ( t_id int(11) notnull auto_increment , tname varchar(50) , note varchar(500),primary key (t_id) ) engine=innodbdefaultcharset=utf8mb4;
truncatetable teacher; delimiter $$ dropfunctionifexists insert_teacher_data $$ createfunction insert_teacher_data() returnsintdeterministic begin declare i int; set i=1; while i<=10000000 do insertinto teacher values(i , concat('tname',i),concat('note',i) ); set i=i+1; endwhile; return 1; end$$ delimiter ; select insert_teacher_data(); commit; selectcount(*) from teacher;
这个是oracle的测试和造数据脚本 createtablespace scott_data datafile'/home/oracle/oracle_space/sitpay1/scott_data.dbf'size1024m autoextendon; createtablespace scott_index datafile'/home/oracle/oracle_space/sitpay1/scott_index.dbf'size64m autoextendon; createtemporarytablespace scott_temp tempfile '/home/oracle/oracle_space/sitpay1/scott_temp.dbf'size64m autoextendon; dropuser scott cascade; createuser scott identifiedby tiger defaulttablespace scott_data temporarytablespace scott_temp ; grantresource,connect,dba to scott; droptable student; createtable student ( s_id number(11) , sno number(11) , sname varchar2(50), sage number(11), ssex varchar2(8) , father_id number(11), mather_id number(11), note varchar2(500) ) nologging; truncatetable student; createorreplaceprocedure insert_student_data is q number(11); begin q:=0; for i in 1..50 loop insert/*+append*/into student selectrownum+q as s_id,rownum+q as sno, concat('sutdent',rownum+q ) as sname,floor(dbms_random.value(1,100)) as sage,'f'as ssex,rownum+q as father_id,rownum+q as mather_id,concat('note',rownum+q ) as note from dual connectbylevel<=1000000; q:=q+1000000; commit; endloop; end insert_student_data; / call insert_student_data(); altertable student addconstraint pk_student primary key (s_id); commit; selectcount(*) from student; createtable course ( c_id number
(11) primary key, cname varchar2(50), note varchar2(500) ) ; truncatetable course; createorreplaceprocedure insert_course_data is q number(11); begin
for i in1..1000loop insert/*+append*/into course values(i , concat('name',i),concat('note',i) ); endloop; end insert_course_data; / call insert_course_data(); commit; selectcount(*) from course; createtable sc ( s_id number(11), c_id number(11), t_id number(11), score number(11) ) nologging; truncatetable sc; createorreplaceprocedure insert_sc_data is q number(11
); begin q:=0; for i in 1..50 loop insert/*+append*/into sc selectrownum+q as s_id, floor(dbms_random.value(0,1000)) as c_id,floor(dbms_random.value(0,10000000)) t_id,floor(dbms_random.value(0,750)) as score from dual connectbylevel<=1000000; q:=q+1000000; commit; endloop; end insert_sc_data; / call insert_sc_data(); createindex idx_s_id on sc(s_id) ; createindex idx_t_id on sc(t_id) ; createindex idx_c_id on sc(c_id) ; selectcount(*) from sc; createtable teacher ( t_id number(11) , tname varchar2(50) , note varchar2(500) )nologging ; truncatetable teacher; createorreplaceprocedure insert_teacher_data is q number
(11); begin q:=0; for i in 1..10 loop insert/*+append*/into teacher selectrownum+q as t_id, concat('teacher',rownum+q ) as tname,concat('note',rownum+q ) as note from dual connectbylevel<=1000000; q:=q+1000000; commit; endloop; end insert_teacher_data; / call insert_teacher_data(); altertable teacher addconstraint pk_teacher primary key (t_id); selectcount(*) from teacher;