MySQL多表查询（JAVA实现）

1、多表查询会导致慢SQL，如果业务对性能要求不高，使用多表查询，比使用JAVA实现更加合适

，如果业务对性能要求比较高，通常使用JAVA来实现，尽可能避免多表查询

2、创建两个表

（1）文章表

-- 创建⽂章表
DROP TABLE IF EXISTS articleinfo;
1
2
比特就业课
 
CREATE TABLE articleinfo (
 id INT PRIMARY KEY auto_increment,
 title VARCHAR ( 100 ) NOT NULL,
 content TEXT NOT NULL,
 uid INT NOT NULL,
 delete_flag TINYINT ( 4 ) DEFAULT 0 COMMENT '0-正常, 1-删除',
 create_time DATETIME DEFAULT now(),
 update_time DATETIME DEFAULT now() 
) DEFAULT charset 'utf8mb4';
-- 插⼊测试数据
INSERT INTO articleinfo ( title, content, uid ) VALUES ( 'Java', 'Java正⽂', 1
);

（2）用户表

-- 创建数据库
DROP DATABASE IF EXISTS mybatis_test;
CREATE DATABASE mybatis_test DEFAULT CHARACTER SET utf8mb4;
-- 使⽤数据数据
USE mybatis_test;
-- 创建表[⽤⼾表]
DROP TABLE IF EXISTS userinfo;
CREATE TABLE `userinfo` (
 `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
 `username` VARCHAR ( 127 ) NOT NULL,
 `password` VARCHAR ( 127 ) NOT NULL,
 `age` TINYINT ( 4 ) NOT NULL,
 `gender` TINYINT ( 4 ) DEFAULT '0' COMMENT '1-男 2-⼥ 0-默认',
 `phone` VARCHAR ( 15 ) DEFAULT NULL,
 `delete_flag` TINYINT ( 4 ) DEFAULT 0 COMMENT '0-正常, 1-删除',
 `create_time` DATETIME DEFAULT now(),
 `update_time` DATETIME DEFAULT now(),
 PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4; 
-- 添加⽤⼾信息
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'admin', 'admin', 18, 1, '18612340001' );
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'zhangsan', 'zhangsan', 18, 1, '18612340002' );
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'lisi', 'lisi', 18, 1, '18612340003' );
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'wangwu', 'wangwu', 18, 1, '18612340004' );

3、多表查询SQL代码

（1）articleinfo文章表 userinfo用户表

select * from articleinfo ta 
LEFT JOIN userinfo tb on ta.uid=tb.id
WHERE ta.id=1;

（2）也可以查部分数据

select ta.*,tb.age from articleinfo ta 
LEFT JOIN userinfo tb on ta.uid=tb.id
WHERE ta.id=1;

4、利用MyBatis查询

（1）这是我们要查询的文章表

package com.example.demo;

import lombok.Data;

import java.util.Date;

@Data
public class ArticleInfo {
    private Integer id;
    private String title;
    private String content;
    private Integer uid;
    private Integer deleteFlag;
    private Date createTime;
    private Date updateTime;

}

（2）用户表

package com.example.demo;
import lombok.Data;
import java.util.Date;

@Data
public class UserInfo {
    //数据库里面有什么我们这里就定义什么
    private  Integer id;
    private String username;
    private  String password;
    private Integer age;
    private Integer gender;
    private String phone;
    private Integer deleteFlag;
    private Date createTime;
    private Date updateTime;
}

（3）接口代码

package com.example.demo;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface ArticleInfoMapper {
    @Select("select ta.*,tb.username,tb.age " +
            "from articleinfo ta left join userinfo tb " +
            "on ta.uid=tb.id " +
            "where ta.id=#{id}")
    ArticleInfo queryArticleInfo(Integer id);
}

（4）测试部分代码

package com.example.demo;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import static org.junit.jupiter.api.Assertions.*;
@SpringBootTest
class ArticleInfoMapperTest {
    @Autowired
    private ArticleInfoMapper articleInfoMapper;
    @Test
    void queryArticleInfo() {
        articleInfoMapper.queryArticleInfo(1);
    }
}