编辑代码

CREATE DATABASE test;
use test;
CREATE TABLE student (
	s_id int,
	s_name varchar(255),
	s_birth varchar(255),
    s_sex varchar(255)
);

INSERT INTO student VALUES (01, '刘一', '1990-01-01', '男');
INSERT INTO student VALUES (02, '陈二',  '1990-02-01', '男');
INSERT INTO student VALUES (03, '张三',  '1990-03-01', '男');
INSERT INTO student VALUES (04, '李四',  '1990-04-01', '男');
INSERT INTO student VALUES (05, '王五',  '1990-05-01', '男');
INSERT INTO student VALUES (06, '赵六',  '1990-06-01', '男');
INSERT INTO student VALUES (07, '孙七',  '1990-07-01', '男');
INSERT INTO student VALUES (08, '周八',  '1990-08-01', '男');
INSERT INTO student VALUES (09, '吴九',  '1990-09-01', '男');
INSERT INTO student VALUES (10, '郑十',  '1990-11-01', '女');

/*SELECT * FROM student;*/

CREATE DATABASE test1;
use test1;
CREATE TABLE course (
	c_id int,
	c_name varchar(255),
	t_id int
);

INSERT INTO course VALUES (01, '语文', '02');
INSERT INTO course VALUES (02, '数学',  '03');
INSERT INTO course VALUES (03, '王五',  '01');

/*SELECT * FROM course;*/

CREATE DATABASE test2;
use test2;
CREATE TABLE teacher (
	t_id int,
	t_name varchar(255)
);

INSERT INTO teacher VALUES (01, '张三');
INSERT INTO teacher VALUES (02, '李四');
INSERT INTO teacher VALUES (03, '王五');

/*SELECT * FROM teacher;*/

CREATE DATABASE test3;
use test3;
CREATE TABLE score (
	s_id int,
    c_id int,
	s_score int
);

INSERT INTO score VALUES (01, 01, 80);
INSERT INTO score VALUES (01, 02, 90);
INSERT INTO score VALUES (01, 03, 99);
INSERT INTO score VALUES (02, 01, 70);
INSERT INTO score VALUES (02, 02, 60);
INSERT INTO score VALUES (02, 03, 80);
INSERT INTO score VALUES (03, 01, 80);
INSERT INTO score VALUES (03, 02, 80);
INSERT INTO score VALUES (03, 03, 80);
INSERT INTO score VALUES (04, 01, 50);
INSERT INTO score VALUES (04, 02, 30);
INSERT INTO score VALUES (04, 03, 20);
INSERT INTO score VALUES (05, 01, 76);
INSERT INTO score VALUES (05, 02, 87);
INSERT INTO score VALUES (06, 01, 31);
INSERT INTO score VALUES (06, 03, 34);
INSERT INTO score VALUES (07, 02, 89);
INSERT INTO score VALUES (07, 03, 98);

/*SELECT * FROM score;*/


/*1. 查询01课程比02课程成绩高的学生的信息及课程分数
way 1   自连接
*/

SELECT
    c.*,
    a.s_score s01,
    b.s_score s02
FROM
    score a, score b, stduent c
WHERE
    a.c_id='01'
and b.c_id='02'
and a.s_id=b.s_id
and c.s_id=a.s_id
and a.s_score>b.s_score;