【Mybatis】Mybatis之xml开发—4个案例带你快速入门

今天药忘吃喽~ 2023-09-27 18:47 111阅读 0赞

## **目录** ##

数据库

练习题

--------------------

## 数据库 ##

#### 1.类型表 ####

CREATE TABLE `tb_teacher_type` (
      `type_id` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
      `content` VARCHAR(50) COMMENT '类型'
    );
    
    INSERT  INTO `tb_teacher_type`(`type_id`,`content`) VALUES (1,'授课老师');
    INSERT  INTO `tb_teacher_type`(`type_id`,`content`) VALUES (2,'助理老师');
    INSERT  INTO `tb_teacher_type`(`type_id`,`content`) VALUES (3,'辅导员老师');

#### 2.老师表 ####

CREATE TABLE `tb_teacher` (
      `tid` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
      `tname` VARCHAR(50) DEFAULT NULL COMMENT '老师姓名',
      `type` INT(11) DEFAULT NULL COMMENT '老师类型ID'
    );
    INSERT  INTO `tb_teacher`(`tid`,`tname`,`type`) VALUES (1,'梁桐老师',1);
    INSERT  INTO `tb_teacher`(`tid`,`tname`,`type`) VALUES (2,'马坤老师',2);
    INSERT  INTO `tb_teacher`(`tid`,`tname`,`type`) VALUES (3,'仲燕老师',3);
    INSERT  INTO `tb_teacher`(`tid`,`tname`,`type`) VALUES (4,'袁新奇老师',1);
    INSERT  INTO `tb_teacher`(`tid`,`tname`,`type`) VALUES (5,'任林达老师',2);
    INSERT  INTO `tb_teacher`(`tid`,`tname`,`type`) VALUES (6,'王珊珊老师',3);

#### 3.班级表 ####

CREATE TABLE `tb_class` (
      `cid` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
      `cname` VARCHAR(50) DEFAULT NULL COMMENT '班级名称',
      `teacher1_id` INT(11) DEFAULT NULL COMMENT '授课老师',
      `teacher2_id` INT(11) DEFAULT NULL COMMENT '助理老师',
      `teacher3_id` INT(11) DEFAULT NULL COMMENT '辅导员老师'
    );
    
    INSERT  INTO `tb_class`(`cid`,`cname`,`teacher1_id`,`teacher2_id`,`teacher3_id`) VALUES (1,'Java56',1,2,3);
    INSERT  INTO `tb_class`(`cid`,`cname`,`teacher1_id`,`teacher2_id`,`teacher3_id`) VALUES (2,'Java78',1,2,3);
    INSERT  INTO `tb_class`(`cid`,`cname`,`teacher1_id`,`teacher2_id`,`teacher3_id`) VALUES (3,'Java12',4,5,6);
    INSERT  INTO `tb_class`(`cid`,`cname`,`teacher1_id`,`teacher2_id`,`teacher3_id`) VALUES (4,'Java34',4,5,6);

#### 4.城市表 ####

CREATE TABLE tb_city(
      c_id VARCHAR(32) PRIMARY KEY COMMENT '城市ID',
      city_name VARCHAR(20) COMMENT '城市名称' ,
      parent_id VARCHAR(32) COMMENT '父ID'
    );
    
    INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('320000','江苏省','0');
    INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('140000','山西省','0');
    INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('130000','河北省','0');
    
    INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('320100','南京市','320000');
    INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('320102','玄武区','320100');
    INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('320103','白下区','320100');
    
    INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('321300','宿迁市','320000');
    INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('321322','沭阳县','321300');
    INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('321323','泗阳县','321300');
    
    
    INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('140100','太原市','140000');
    INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('140106','迎泽区','140100');
    INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('140108','尖草坪区','140100');
    
    INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('140800','运城市','140000');
    INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('140823','闻喜县','140800');
    INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('140828','夏　县','140800');
    
    INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('130100','石家庄市','130000');
    INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('130127','高邑县','130100');
    INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('130185','鹿泉市','130100');
    
    INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('131000','廊坊市','130000');
    INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('131003','广阳区','131000');
    INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('131022','固安县','131000');

#### 5.学生表 ####

CREATE TABLE `tb_student` (
      `s_id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学生ID',
      `sname` VARCHAR(50) DEFAULT NULL COMMENT '姓名',
      `age` INT(11) DEFAULT NULL COMMENT '年龄',
      `birthday` DATETIME DEFAULT NULL COMMENT '生日',
      `gender` CHAR(1) DEFAULT NULL COMMENT '性别',
      `c_id` INT DEFAULT NULL COMMENT '班级ID',
      `province_id` VARCHAR(32) DEFAULT NULL COMMENT '省ID',
      `city_id` VARCHAR(32) DEFAULT NULL COMMENT '市ID',
      `county_id` VARCHAR(32) DEFAULT NULL COMMENT '县ID'
    );
    
    INSERT  INTO `tb_student`(`s_id`,`sname`,`age`,`birthday`,`gender`,`c_id`,`province_id`,`city_id`,`county_id`) VALUES (1,'赵三33',21,'2001-01-17 00:00:00','1',1,'320000','321300','321322');
    INSERT  INTO `tb_student`(`s_id`,`sname`,`age`,`birthday`,`gender`,`c_id`,`province_id`,`city_id`,`county_id`) VALUES (2,'钱四444',1900,'2001-05-16 00:00:00','1',2,'320000','321300','321322');
    INSERT  INTO `tb_student`(`s_id`,`sname`,`age`,`birthday`,`gender`,`c_id`,`province_id`,`city_id`,`county_id`) VALUES (3,'孙五56',189,'2022-03-15 00:00:00','0',1,'320000','321300','321322');
    INSERT  INTO `tb_student`(`s_id`,`sname`,`age`,`birthday`,`gender`,`c_id`,`province_id`,`city_id`,`county_id`) VALUES (4,'张三',20,'2020-12-21 00:00:00','0',2,'320000','321300','321322');
    INSERT  INTO `tb_student`(`s_id`,`sname`,`age`,`birthday`,`gender`,`c_id`,`province_id`,`city_id`,`county_id`) VALUES (5,'xxx',18,'2020-12-21 00:00:00','0',2,'140000','140800','140823');
    INSERT  INTO `tb_student`(`s_id`,`sname`,`age`,`birthday`,`gender`,`c_id`,`province_id`,`city_id`,`county_id`) VALUES (6,'123',18,'2020-11-01 00:00:00','0',3,'130000','130100','130127');
    INSERT  INTO `tb_student`(`s_id`,`sname`,`age`,`birthday`,`gender`,`c_id`,`province_id`,`city_id`,`county_id`) VALUES (7,'xx',18,'2020-11-02 00:00:00','0',1,'130000','131000','131003');

#### 6.课程表 ####

CREATE TABLE `tb_course` (
      `c_id` INT NOT NULL PRIMARY KEY COMMENT '课程ID',
      `cname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
      `desc` VARCHAR(100) DEFAULT NULL COMMENT '课程描述'
    );
    
    INSERT  INTO `tb_course`(`c_id`,`cname`,`desc`) VALUES (1,'Java基础','JavaSE所有课程');
    INSERT  INTO `tb_course`(`c_id`,`cname`,`desc`) VALUES (2,'JavaWeb','Java Web 所有课程');
    INSERT  INTO `tb_course`(`c_id`,`cname`,`desc`) VALUES (3,'SSM','Spring Mvc、Spring、MyBatis所有课程');

#### 7.中间表 ####

-- 学生课程中间表
    CREATE TABLE `tb_student_course` (
      `s_id` INT NOT NULL COMMENT '学生ID',
      `c_id` INT NOT NULL COMMENT '课程ID',
      `score` DOUBLE DEFAULT NULL,
      PRIMARY KEY (`s_id`,`c_id`)
    );
    
    INSERT INTO tb_student_course(s_id,c_id,score) VALUES(1,1,100);
    INSERT INTO tb_student_course(s_id,c_id,score) VALUES(1,2,95);
    INSERT INTO tb_student_course(s_id,c_id,score) VALUES(1,3,NULL);
    
    INSERT INTO tb_student_course(s_id,c_id,score) VALUES(2,1,100);
    INSERT INTO tb_student_course(s_id,c_id,score) VALUES(2,2,95);
    INSERT INTO tb_student_course(s_id,c_id,score) VALUES(2,3,100);
    
    INSERT INTO tb_student_course(s_id,c_id,score) VALUES(3,1,80);
    INSERT INTO tb_student_course(s_id,c_id,score) VALUES(3,2,NULL);
    INSERT INTO tb_student_course(s_id,c_id,score) VALUES(3,3,90);
    
    
    -- 老师课程中间表
    create table `tb_teacher_course`(
    	`tid` int not null COMMENT '老师id',
    	`c_id` int not null COMMENT '课程id',
    	PRIMARY KEY (`tid`,`c_id`)
    );
    
    insert into tb_teacher_course(tid,c_id) values(1,1);
    insert into tb_teacher_course(tid,c_id) values(1,2);
    insert into tb_teacher_course(tid,c_id) values(1,3);
    insert into tb_teacher_course(tid,c_id) values(2,3);
    insert into tb_teacher_course(tid,c_id) values(3,1);
    insert into tb_teacher_course(tid,c_id) values(4,1);
    insert into tb_teacher_course(tid,c_id) values(4,2);
    insert into tb_teacher_course(tid,c_id) values(4,3);
    insert into tb_teacher_course(tid,c_id) values(5,2);
    insert into tb_teacher_course(tid,c_id) values(5,3);
    insert into tb_teacher_course(tid,c_id) values(6,3);

--------------------

## 练习题 ##

1. 查询所有的老师，同时

>  *  查询老师对应的类型
>  *  查询老师所教授的课程

2.查询所有的班级，同时

>  *  查询班级对应的老师
>  *  查询班级学生的总人数
>  *  查询班级包含的学生详情

3. 查询所有的学生，同时

>  *  查询学生所属班级
>  *  查询学生省市县信息
>  *  查询学生所学课程

4. 查询所有课程，同时

>  *  查询学生学习该课程总人数
>  *  查询学生学习该课程详情

**要求：在查询所有同时，对每一个相关双向信息都要进行指定。**

例如：班级中有学生，学生总数，还有授课老师；学生中对应班级信息；同时每一个学生有省市县地址信息，而学生又在班级中。在进行查询所有班级的时候，即要查询出所有的班级，还有查询班级对应学生的总人数和班级包括学生的学生详情，而学生详情中又有班级信息，地址信息。

#### 查询所有老师 ####

![324c7fd3961944a4981e5da764601a7a.png][]

#### 查询所有班级 ####

![4379cafaf8134354b85f3b4ee154e8bc.png][]

#### 查询所有学生 ####

![15fbc475c8ab497292ac85d15199c2e7.png][]

#### 查询所有课程 ####

![c22bdbc59f5d406cb1008cf983d623a5.png][]

如有需要答案的请查看相应资源。

[324c7fd3961944a4981e5da764601a7a.png]: https://img-blog.csdnimg.cn/324c7fd3961944a4981e5da764601a7a.png
[4379cafaf8134354b85f3b4ee154e8bc.png]: https://img-blog.csdnimg.cn/4379cafaf8134354b85f3b4ee154e8bc.png
[15fbc475c8ab497292ac85d15199c2e7.png]: https://img-blog.csdnimg.cn/15fbc475c8ab497292ac85d15199c2e7.png
[c22bdbc59f5d406cb1008cf983d623a5.png]: https://img-blog.csdnimg.cn/c22bdbc59f5d406cb1008cf983d623a5.png