请教大神一个数据库连接查询的问题

在mysql数据库中这样的问题该怎么写sql语句

你第一个表相当于就是外部展示咯，你第二张表存的就是那些信息。全部查出去，解析的时候按teacher解析，有就标识1，无就标识0.重点是你怎么处理数据吧，如果安照你的表结构。

看了一下，觉得用存储过程应该比较容易解决，查出table2教课字段，然后以逗号分割，判断包含哪一门课，在一条判断结束后插入table1.

SELECT
t.teacher,
locate('语文',t.class) AS '语文',
CASE
WHEN locate('数学',t.class) > 0
THEN 1
ELSE 0
END AS '数学',
CASE
WHEN locate('英语',t.class) > 0
THEN 1
ELSE 0
END AS '英语',
CASE
WHEN locate('化学',t.class) > 0
THEN 1
ELSE 0
END AS '化学',
CASE
WHEN locate('物理',t.class) > 0
THEN 1
ELSE 0
END AS '物理',
CASE
WHEN locate('生物',t.class) > 0
THEN 1
ELSE 0
END AS '生物'
FROM
Teacher t;
SELECT
*
FROM
Teacher;

SELECT
t.teacher,
locate('语文',t.class) AS '语文',
CASE WHEN locate('数学',t.class) > 0 THEN 1 ELSE 0 END AS '数学',
CASE WHEN locate('英语',t.class) > 0 THEN 1 ELSE 0 END AS '英语',
CASE WHEN locate('化学',t.class) > 0 THEN 1 ELSE 0 END AS '化学',
CASE WHEN locate('物理',t.class) > 0 THEN 1 ELSE 0 END AS '物理',
CASE WHEN locate('生物',t.class) > 0 THEN 1 ELSE 0 END AS '生物'
FROM Teacher t;

SELECT
t.teacher,
CASE WHEN locate('语文',t.class) > 0 THEN 1 ELSE 0 END AS '语文',
CASE WHEN locate('数学',t.class) > 0 THEN 1 ELSE 0 END AS '数学',
CASE WHEN locate('英语',t.class) > 0 THEN 1 ELSE 0 END AS '英语',
CASE WHEN locate('化学',t.class) > 0 THEN 1 ELSE 0 END AS '化学',
CASE WHEN locate('物理',t.class) > 0 THEN 1 ELSE 0 END AS '物理',
CASE WHEN locate('生物',t.class) > 0 THEN 1 ELSE 0 END AS '生物'
FROM Teacher t;

加主键也行，不想动结构的话可以给2表加个索引，还有就是数据量大，写个存储过程，例如：

 DROP PROCEDURE IF EXISTS SP_TEST;

DELIMITER $$

CREATE PROCEDURE SP_TEST()
BEGIN

    DECLARE i INT DEFAULT 0;

    SELECT COUNT(*) INTO @COUNT FROM t2;

    WHILE i < @COUNT DO

        SELECT t2.teacher, t2.course INTO @TEACHER, @COURSE FROM t2 LIMIT I,1;

        IF( LOCATE('chinese', @COURSE) > 0 ) THEN SET @CHINESE = 1; ELSE SET @CHINESE = 0; END IF;
        IF( LOCATE('math', @COURSE) > 0 ) THEN SET @MATH = 1; ELSE SET @MATH = 0; END IF;
        IF( LOCATE('English', @COURSE) > 0 ) THEN SET @ENGLISH = 1; ELSE SET @ENGLISH = 0; END IF;
        IF( LOCATE('chemistry', @COURSE) > 0 ) THEN SET @CHEMISTRY = 1; ELSE SET @CHEMISTRY = 0; END IF;
        IF( LOCATE('physics', @COURSE) > 0 ) THEN SET @PHYSICS = 1; ELSE SET @PHYSICS = 0; END IF;
        IF( LOCATE('biology', @COURSE) > 0 ) THEN SET @BIOLOGY = 1; ELSE SET @BIOLOGY = 0; END IF;

        IF ( SELECT COUNT(*) FROM t1 WHERE teacher = @TEACHER ) THEN DELETE FROM t1 WHERE teacher = @TEACHER; END IF;
        INSERT INTO t1(teacher,chinese,math,English,chemistry,physics,biology) SELECT @TEACHER, @CHINESE, @MATH, @ENGLISH, @CHEMISTRY, @PHYSICS, @BIOLOGY;
        SET i = i + 1;

    END WHILE;

END$$
DELIMITER ;