MySQL经典练习50题（完美解答版）.pdfVIP

MySQL经典练习50题（完美解答版）

MySQL经典练习50题（完美解答版）

创建数据库和表

数据库

dropdatabaseifexistsmysql_testcascade;

createdatabasemysql_test;

usemysql_test;

学⽣表student

创建学⽣表student

createtablestudent(

s_idint,

s_namevarcar(8),

s_birtdate,

s_sexvarcar(4)

);

插⼊学⽣数据

insertintostudentvalues

(1,赵,1990-01-01,男),

(2,钱电,1990-12-21,男),

(3,孙风,1990-05-20,男),

(4,李云,1990-08-06,男),

(5,周梅,1991-12-01,⼥),

(6,吴兰,1992-03-01,⼥),

(7,郑⽵,1989-07-01,⼥),

(8,王菊,1990-01-20,⼥);

课程表course

创建课程表course

createtablecourse(

c_idint,

c_namevarcar(8),

t_idint

);

插⼊课程数据

insertintocoursevalues

(1,语⽂,2),

(2,数学,1),

(3,英语,3);

教师表teacher

创建教师表teacher

createtableteacer(

t_idint,

t_namevarcar(8)

);

插⼊教师数据

insertintoteacervalues

(1,张三),

(2,李四),

(3,王五);

成绩表score

创建成绩表score

createtablescore(

s_idint,

c_idint,

s_scoreint

);

插⼊成绩数据

insertintoscorevalues

(1,1,80),

(1,2,90),

(1,3,99),

(2,1,70),

(2,2,60),

(2,3,65),

(3,1,80),

(3,2,80),

(3,3,80),

(4,1,50),

(4,2,30),

(4,3,40),

(5,1,76),

(5,2,87),

(6,1,31),

(6,3,34),

(7,2,89),

(7,3,98);

表关系

经典练习50题

1、查询01课程⽐02课程成绩⾼的学⽣的信息及课程分数

selects.*,sc1.s_scoreasscore_01,sc2.s_scoreasscore_02

fromstudents

innerjoin(

select*fromscorewerec_id=1

)sc1

ons.s_id=sc1.s_id

innerjoin(

select*fromscorewerec_id=2

)sc2

ons.s_id=sc2.s_id

weresc1.s_scoresc2.s_score;

+++++++

|s_id|s_name|s_birt|s_sex|score_01|score_02|

+++++++

|2|钱电|1990-12-21|男|70|60|

|4|李云|1990-08-06|男|50|30|

+++++++

2、查询01课程⽐02课程成绩低的学⽣的信息及课程分数

selects.*,sc1.