MySQL表生成数据 mysql生成表查询

文章目录

• 前言
• 一、准备工作
• 二、内外连接的使用

• 1.内连接
• 2. 外连接

• 2.1 左连接
• 2.2 右连接

• 3. 关于union和union all的区别

• 三、 子查询
• 总结

---

前言

mysql单表操作玩起来难度不大，但工作中经常要进行多表连接查询和进行子查询，所以有必要把常见的多表基本操作整明白～

---

一、准备工作

创建学生表

# 学生表
DROP TABLE IF EXISTS student;
CREATE TABLE student(
    id INT AUTO_INCREMENT    COMMENT '学生id' ,
    student_name VARCHAR(255)    COMMENT '学生名' ,
    `student_id` INT    COMMENT '学号' ,
    age INT    COMMENT '年龄' ,
    class_id INT    COMMENT '班级id' ,
	PRIMARY KEY(id)
)ENGINE = INNODB CHARSET = utf8  COMMENT = '学生表';

创建班级表

# 班级表
DROP TABLE IF EXISTS class;
CREATE TABLE class(
    id INT AUTO_INCREMENT    COMMENT '班级id' ,
    username VARCHAR(255)    COMMENT '班级名' ,
    `class_id` INT    COMMENT '班级号' ,
	  PRIMARY KEY(id)
)ENGINE = INNODB CHARSET = utf8  COMMENT = '班级表';

给表录入数据

# 给班级表录入数据
INSERT INTO `class`(classname, class_id) VALUES("软件工程1班", 101);
INSERT INTO `class`(classname, class_id) VALUES("软件工程2班", 102);
INSERT INTO `class`(classname, class_id) VALUES("软件工程3班", 103);
INSERT INTO `class`(classname, class_id) VALUES("软件工程4班", 104);
# 给学生表录入数据
INSERT INTO `student`(student_name, student_id, age, class_id) VALUES("李四", 1022, 20, 101);
INSERT INTO `student`(student_name, student_id, age, class_id) VALUES("张伟", 1023, 20, 101);
INSERT INTO `student`(student_name, student_id, age, class_id) VALUES("张三", 1025, 20, 101);
INSERT INTO `student`(student_name, student_id, age, class_id) VALUES("星然", 1026, 20, 101);
INSERT INTO `student`(student_name, student_id, age, class_id) VALUES("鹏煊", 1121, 20, 102);
INSERT INTO `student`(student_name, student_id, age, class_id) VALUES("君昊", 1122, 20, 102);
INSERT INTO `student`(student_name, student_id, age, class_id) VALUES("浩阔", 1123, 20, 102);
INSERT INTO `student`(student_name, student_id, age, class_id) VALUES("景中", 1228, 20, 103);
INSERT INTO `student`(student_name, student_id, age, class_id) VALUES("昆卉", 1027, 20, 101);
INSERT INTO `student`(student_name, student_id, age, class_id) VALUES("鸿宝", 1030, 20, 101);
INSERT INTO `student`(student_name, student_id, age, class_id) VALUES("曜坤", 1124, 20, 102);
INSERT INTO `student`(student_name, student_id, age, class_id) VALUES("景平", 1018, 20, 101);
# 没有班级的学生
INSERT INTO `student`(username, student_id, age) VALUES("昆琦", 1018, 20);

二、内外连接的使用

1.内连接

查询只有student表和class表中匹配到的记录，student或class表没有匹配到的记录则不会被查到

# 内连接（只有student表和class表中匹配到的记录）
SELECT student_name, student_id, age, classname 
FROM student AS s 
INNER JOIN class AS c 
ON s.class_id = c.class_id;

2. 外连接

2.1 左连接

返回包括左表中的所有记录和右表中连接字段相等的记录且左表不加限制，全查；右表查出没有与其对应的数据用null去填补。

# 左外连接(保留了student表中没有匹配到class表中的记录)
SELECT student_name, student_id, age, classname 
FROM student AS s 
LEFT JOIN class AS c 
ON s.class_id = c.class_id;

2.2 右连接

返回包括右表中的所有记录和左表中连接字段相等的记录且右表不加限制，右表内容全查，左边没有与右边匹配的就拿空值去填。

# 右外连接(保留了class表中没有匹配到student表中的记录)
SELECT student_name, student_id, age, classname 
FROM student AS s 
RIGHT OUTER JOIN class AS c 
ON s.class_id = c.class_id;

3. 关于union和union all的区别

UNION ALL 把左连接和右连接查询的所有结果都查出来不做去重处理
UNION 把左连接和右连接查询的所有结果都查出来并去重

# 查询student和class表中所有的记录
SELECT student_name, student_id, age, classname 
FROM student AS s 
LEFT JOIN class AS c 
ON s.class_id = c.class_id
UNION ALL  # 对左连接和右连接的查询结果组合
SELECT student_name, student_id, age, classname 
FROM student AS s 
RIGHT OUTER JOIN class AS c 
ON s.class_id = c.class_id;

可以看到上面虽然把结果都查出来了，但是会有一些重复的记录

# 查询student和class表中所有的记录(使用UNION)
SELECT student_name, student_id, age, classname 
FROM student AS s 
LEFT JOIN class AS c 
ON s.class_id = c.class_id
UNION  # 对左连接和右连接的查询结果组合
SELECT student_name, student_id, age, classname 
FROM student AS s 
RIGHT OUTER JOIN class AS c 
ON s.class_id = c.class_id;

可以看到上面把结果都查出来了，重复的记录也被过滤掉了。

三、 子查询

查询软件工程1班和软件工程3班的学生信息

# 1.查询软件工程1班和软件工程3班的编号
# 2.再通过软件工程1班和3班的编号作为条件查询学生信息
SELECT class_id FROM class WHERE classname = "软件工程1班" OR classname = "软件工程3班";
SELECT student_name, student_id, age, classname
FROM student AS s, class AS c 
WHERE s.class_id = c.class_id AND c.class_id 
IN(SELECT class_id FROM class WHERE classname = "软件工程1班" OR classname = "软件工程3班");

查询软件工程1班和软件工程3班的学生信息，第二种方法。未用到子查询

SELECT student_name, student_id, age, classname
FROM student AS s, class AS c 
WHERE s.class_id = c.class_id AND (classname = "软件工程3班" OR classname = "软件工程1班");

---

总结

上面就是mysql多表查询的一些基本操作，以后可能要3张表4张表甚至更多表的连接查询操作也是建立在这里之上的。