三表关联查询

一、创建三张表：学生表、选课表、课程表

• 在自定义的数据库中创建三个表

1、学生表(学号, 姓名, 性别, 年龄, 电话) - 学号是字符串类型

1. 运行SQL语句创建学生表
   

CREATE TABLE student (
  s_id int(11) NOT NULL AUTO_INCREMENT,
	s_number int(11) NOT NULL,
  s_name varchar(30) DEFAULT NULL,
  s_gender varchar(10) DEFAULT NULL,
  s_age int(11) DEFAULT NULL,
  s_phone VARCHAR(11) DEFAULT NULL,
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

2. 运行SQL语句给student学生表插入记录

INSERT INTO student VALUES ('1','19205111', '张三', '女',18,'1234569290');
INSERT INTO student VALUES ('2','19205112', '李四', '女',20,'1234785890');
INSERT INTO student VALUES ('3','19205113', '王五', '女',19,'1238737890');
INSERT INTO student VALUES ('4','19205114', '刘艳', '女',10,'1234593690');

2、选课表(学号, 课程号, 成绩)

1. 运行SQL语句创建学生表
   

CREATE TABLE selecourse (
  sc_id int(11) NOT NULL AUTO_INCREMENT,
	sc_number int(11) NOT NULL,
	sc_classnumber int(20) NOT NULL,
  sc_score int(10) NOT NULL,
  PRIMARY KEY (`sc_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

2. 运行SQL语句给student学生表插入记录
   

INSERT INTO selecourse VALUES ('1','19205111', '123', '90');
INSERT INTO student VALUES ('2','19205112','234', '89');
INSERT INTO student VALUES ('3','19205113','345', '99');
INSERT INTO student VALUES ('4','19205114','456', '78');

3、课程表(课程号, 课程名, 课时数) - 课程号是字符串类型

1. 运行SQL语句创建学生表

CREATE TABLE timetable (
  t_id int(11) NOT NULL AUTO_INCREMENT,
	t_classnumber int(20) NOT NULL,
	t_name VARCHAR(30) NOT NULL,
  t_classhours int(10) NOT NULL,
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

2. 运行SQL语句给student学生表插入记录
   

INSERT INTO timetable VALUES ('1', '123', '心理健康教育','10');
INSERT INTO student VALUES ('2','234', 'java','30');
INSERT INTO student VALUES ('3','345', 'python','25');
INSERT INTO student VALUES ('4','456', '前端技术','25');

二、查询任务

1、在映射器配置文件里引入结果映射元素

2、添加按姓名查询用户记录功能，更新，查找，删除

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="net.tp.mybatis.mapper.UserMapper">
    <resultMap id="UserMap" type="User">
        <result column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="age" property="age"/>
        <result column="address" property="address"/>
    </resultMap>

    <select id="findById" parameterType="int" resultType="User">

        SELECT * FROM t_user WHERE id = #{
   id};
    </select>

    <select id=