Java学习日志(三十一): 多表查询，内连接，外连接

JavaEE学习日志持续更新----> 必看！JavaEE学习路线（文章总汇）

Java学习日志（三十一）用户和角色的多对多关系多表查询多表查询的方式准备数据交叉连接查询内连接查询外连接查询子查询 用户和角色的多对多关系

首先先创建一个新的数据库

-- 创建数据库day03 CREATE DATABASE day03; -- 使用数据库 USE day03;

建表原则：创建一张中间表，使用两个主表的主键作为外键

主表：用户表users 角色表roles 中间表users_roles -- 创建主表用户表users 字段：用户主键，用户名，密码 CREATE TABLE users( -- 用户主键 uid INT PRIMARY KEY AUTO_INCREMENT, -- 用户名 username VARCHAR(20), -- 密码 PASSWORD VARCHAR(20) ); -- 用户表添加数据 INSERT INTO users(username,PASSWORD) VALUES('群演1','1234'),('群演2','5678'); -- 创建主表角色表roles 字段：角色主键，角色名称 CREATE TABLE roles( -- 角色主键 rid INT PRIMARY KEY AUTO_INCREMENT, -- 角色名称 rname VARCHAR(20) ); -- 角色表添加数据 INSERT INTO roles(rname) VALUES('皇上'),('皇后'),('太监'),('宫女'); -- 创建中间表 users_roles CREATE TABLE users_roles( -- 用户主键 users_uid INT, -- 角色主键 roles_rid INT, -- 添加外键约束 FOREIGN KEY(users_uid) REFERENCES users(uid), FOREIGN KEY(roles_rid) REFERENCES roles(rid) ); -- 中间表添加两个主表都有的数据 INSERT INTO users_roles VALUES(1,1),(1,2),(1,3),(1,4),(2,2),(2,3),(2,4); -- 中间表添加主表用户表不存在的数据 -- Cannot add or update a child row INSERT INTO users_roles VALUES(3,1); -- 中间表添加主表角色表不存在的数据 -- Cannot add or update a child row INSERT INTO users_roles VALUES(1,5); -- 删除主表角色表名称为太监的数据 -- Cannot delete or update a parent row DELETE FROM roles WHERE rname='太监'; -- 先删除中间表中使用了太监的数据，再删除角色表中的数据，则可以删除 DELETE FROM users_roles WHERE roles_rid=3; DELETE FROM roles WHERE rname='太监'; 多表查询 多表查询的方式

多表查询：一次查询两张以上的表
一、交叉连接查询：很少使用，有错误数据
格式：select* from 表A,表B;

二、内连接查询：在交叉连接查询的基础上，使用外键约束作为查询条件

隐式内连接：不使用关键字[inner]join on
格式：select * from 表A,表B where 表A.主键=表B.外键; 显式内连接：使用关键字[inner]join on
格式：select * from 表A [inner]join 表B on 表A.主键=表B.外键;

三、外连接查询：在交叉连接查询的基础上，使用外键约束作为查询条件

左外连接查询：使用关键字left [outer] join on
格式：select * from 表A left [outer] join 表B on 表A.主键=表B.外键;
注意：
- 左外连接查询以左边的表为主：
- 左边有的数据，右边没有，使用空代替
- 左边没有的数据，右边也不能出现 右外连接查询：使用关键字right [outer] join on
格式：select * from 表A right [outer] join 表B on 表A.主键=表B.外键;
注意：
- 右外连接查询以右边的表为主：
- 右边有的数据，左边没有，使用空代替
- 右边没有的数据，左边也不能出现

四、子查询(sql语句的嵌套查询)

一条sql语句的查询结果，作为另外一条sql语句的查询条件
格式：
select * from 表B where 字段 = (select 字段 from 表A [where 条件]) 一条sql语句的查询结果，作为另一条sql语句的一张表(隐式内连接查询)
格式：
select * from (select * from 表A [where 条件]),表B where 表A.主键=表B.外键; 准备数据 # 分类表 CREATE TABLE category ( cid VARCHAR(32) PRIMARY KEY, cname VARCHAR(50) ); #商品表 CREATE TABLE products( pid VARCHAR(32) PRIMARY KEY, pname VARCHAR(50), price INT, flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架 category_id VARCHAR(32), CONSTRAINT products_fk FOREIGN KEY (category_id) REFERENCES category (cid) ); #分类 INSERT INTO category(cid,cname) VALUES('c001','家电'); INSERT INTO category(cid,cname) VALUES('c002','服饰'); INSERT INTO category(cid,cname) VALUES('c003','化妆品'); #商品 INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001'); INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001'); INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001'); INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p004','JACKJONES',800,'1','c002'); INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002'); INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002'); INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002'); INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003'); INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');

category表

products表

架构图

交叉连接查询

交叉连接查询：很少使用，有错误数据
格式：select* from 表A,表B;

一次性查询出分类表和商品表的所有数据

SELECT * FROM category,products;

查询结果：两个表所有数据的乘积(3*9=27)–>笛卡尔积

内连接查询

内连接查询：在交叉连接查询的基础上，使用外键约束作为查询条件

隐式内连接：不使用关键字[inner]join on
格式：select * from 表A,表B where 表A.主键=表B.外键; 显式内连接：使用关键字[inner]join on
格式：select * from 表A [inner]join 表B on 表A.主键=表B.外键;

隐式内连接

SELECT * FROM category,products WHERE category.cid = products.category_id; -- 使用给表起别名，简化sql语句 SELECT * FROM category c,products p WHERE c.cid = p.category_id;

显式内连接

SELECT * FROM category c INNER JOIN products p ON c.cid = p.category_id;

查询哪些分类的商品已经上架

SELECT * FROM category c,products p WHERE c.cid = p.category_id AND p.flag = '1' 外连接查询

外连接查询：在交叉连接查询的基础上，使用外键约束作为查询条件

左外连接查询：使用关键字left [outer] join on
格式：select * from 表A left [outer] join 表B on 表A.主键=表B.外键;
注意：
- 左外连接查询以左边的表为主：
- 左边有的数据，右边没有，使用空代替
- 左边没有的数据，右边也不能出现 右外连接查询：使用关键字right [outer] join on
格式：select * from 表A right [outer] join 表B on 表A.主键=表B.外键;
注意：
- 右外连接查询以右边的表为主：
- 右边有的数据，左边没有，使用空代替
- 右边没有的数据，左边也不能出现

修改category表

左外连接 SELECT * FROM category c LEFT OUTER JOIN products p ON c.cid = p.category_id;

右外连接

SELECT * FROM category c RIGHT OUTER JOIN products p ON c.cid = p.category_id;

使用左外连接查询，查询每类商品的个数

SELECT cid,COUNT(p.category_id) FROM category c LEFT JOIN products p ON c.cid = p.category_id GROUP BY cid; 子查询 一条sql语句的查询结果，作为另外一条sql语句的查询条件
格式：
select * from 表B where 字段 = (select 字段 from 表A [where 条件]) 一条sql语句的查询结果，作为另一条sql语句的一张表(隐式内连接查询)
格式：
select * from (select * from 表A [where 条件]),表B where 表A.主键=表B.外键;

查询商品表，只显示化妆品的信息

-- 一条sql语句的查询结果，作为另外一条sql语句的查询条件 SELECT * FROM products WHERE category_id = (SELECT cid FROM category WHERE cname = '化妆品')

-- 一条sql语句的查询结果，作为另一条sql语句的一张表(隐式内连接查询) SELECT * FROM products p, (SELECT * FROM category WHERE cname = '化妆品') c WHERE c.cid = p.category_id ;

注意：如果用子查询，且查询的条件中有in，则必须使用in来接收子查询的查询结果，否则会报错

作者：隔叶听风

JAVA 连接 内连接 外连接 java学习 java学