java内连接外连接_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来接收子查询的查询结果，否则会报错