day019 mysql多表查询&JDBC

最新推荐文章于 2023-09-07 19:06:47 发布

MustangYL

最新推荐文章于 2023-09-07 19:06:47 发布

阅读量182

点赞数

分类专栏： Java课堂笔记文章标签： java mysql

本文链接：https://blog.csdn.net/mustang_yl/article/details/114959800

版权

Java课堂笔记专栏收录该内容

48 篇文章 1 订阅

订阅专栏

1 多表关系实战

1.1 一对多（省市）

省市一对多、省市自我一对多

省市一对多.png

两张表

/*  
    省市一对多的关系
    
    省表（主表）
    市表（从表）
*/

# 省表（主表）
CREATE TABLE province (
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(20),
    description VARCHAR(20)
);

# 市表(从表)
CREATE TABLE city (
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(20),
    description VARCHAR(20),
    p_id INT -- 外键列，表示的是省id，说明了这个市是属于哪个省的。
);

# 添加外键约束
ALTER TABLE city ADD FOREIGN KEY (p_id) REFERENCES province (id);


# 添加数据
INSERT INTO province (`name`, description) VALUES ('河北省', '燕赵大地');
INSERT INTO province (`name`, description) VALUES ('山东省', '齐鲁大地');
INSERT INTO province (`name`, description) VALUES ('河南省', '人类的发源地');

INSERT INTO city (`name`, description, p_id) VALUES ('石家庄', '国际庄', 1);
INSERT INTO city (`name`, description, p_id) VALUES ('张家口', '马上要开冬奥会了', 1);

-- 向从表添加数据，一定要保证这个数据它的外键在主表中是存在的
INSERT INTO city (`name`, description, p_id) VALUES ('日喀则', '海拔很高', 5);

-- 删除
DELETE FROM province WHERE id = 1;

一张表（省市自我一对多）

/*
    省市自我一对多
    
    建立一张表，这个表示地区表，既可以表示省，又可以表示市又可以表示县区
*/

# 地区表
CREATE TABLE `area`(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(20),
    description VARCHAR(20),
    p_id INT -- 外键列， 用来和自己表中的主键产生关联关系。这个列表示的是这个地区的上级地区的id
);

# 添加外键约束
# alter table 从表 add [constraint 约束名称] foreign key (外键列) references 主表 (主键列);
# 自我一对多中，这张表即是从表又是主表
ALTER TABLE `area` ADD FOREIGN KEY (p_id) REFERENCES `area` (id);

# 添加数据
# p_id 是NULL，表示他没有上级地区
# p_id是外键列，外键列允许为null
INSERT INTO `area` (`name`, description, p_id) VALUES ('河北省', '燕赵大地', NULL);
INSERT INTO `area` (`name`, description, p_id) VALUES ('四川省', '天府之国', NULL);

INSERT INTO `area` (`name`, description, p_id) VALUES ('保定市', '有新的开发区', 1);
INSERT INTO `area` (`name`, description, p_id) VALUES ('石家庄市', '国际庄', 1);

INSERT INTO `area` (`name`, description, p_id) VALUES ('雄安', '新区之一', 3);

-- 如果添加数据时对应的外键是不存在的，就会报错
INSERT INTO `area` (`name`, description, p_id) VALUES ('火星', '外星球', 10000);

-- 删除
-- 如果要删除的数据被其他数据引用着，那么就不能删除
DELETE FROM `area` WHERE id = 3;

1.2 用户和角色（多对多）

用户角色权限.png

/*
    用户和角色（多对多）
    
    需要建立三张表
    
    用户表
    角色表
    中间表
*/
# 用户表
CREATE TABLE `user` (
    uid INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20), -- 用户名
    `password` VARCHAR(20) -- 密码
);

# 角色表
CREATE TABLE role (
    rid INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(20)
);

# 建立中间表
CREATE TABLE user_role (
    uid INT, -- 表示用户id
    rid INT  -- 表示角色id
);

# 添加外键约束， 
# 让中间表中的uid和用户表中的主键id产生关联关系。
ALTER TABLE user_role ADD FOREIGN KEY (uid) REFERENCES `user` (uid);

# 让中间表中的rid和角色表中的主键id产生关联关系
ALTER TABLE user_role ADD FOREIGN KEY (rid) REFERENCES role (rid);

/*
    建立权限表，让角色和权限产生关联关系（多对多的关系）
    需要再建立两张表
    权限表
    中间表
*/

# 建立权限表
CREATE TABLE privilege(
    pid INT PRIMARY KEY AUTO_INCREMENT, -- 主键id
    pname VARCHAR(20) -- 权限名称
);

# 建立中间表。 角色和权限的中间表
CREATE TABLE role_privilege (
    rid INT, -- 角色id， 和角色表中的主键id进行关联
    pid INT  -- 权限id， 用权限表中的主键id进行关联
);

# 添加外键约束
# 让中间表中的角色id和角色表中的主键id产生关联关系
ALTER TABLE role_privilege ADD FOREIGN KEY (rid) REFERENCES role (rid);

# 让中间表中的权限id 和 权限表中的主键id关联
ALTER TABLE role_privilege ADD FOREIGN KEY (pid) REFERENCES privilege (pid);

2 多表查询

笛卡尔积.png

2.1 连接查询

/*
    准备数据
*/
CREATE TABLE category (
  cid VARCHAR(32) PRIMARY KEY ,
  cname VARCHAR(50)
);
CREATE TABLE product(
  pid VARCHAR(32) PRIMARY KEY , -- 商品id 是主键
  pname VARCHAR(50), -- 商品名称
  price INT, -- 商品价格
  flag VARCHAR(2),              #是否上架标记为：1表示上架、0表示下架
  category_id VARCHAR(32), -- 表示外键id， 是这个商品所属分类的id
  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 product(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');
INSERT INTO product(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');
INSERT INTO product(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');

INSERT INTO product (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');
INSERT INTO product (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');
INSERT INTO product (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');
INSERT INTO product (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');

INSERT INTO product (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');
INSERT INTO product (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');

/*
    交叉连接查询
    select * from 表A, 表B;
    
    会产生笛卡尔积，是一种错误的写法。
*/
# 查询商品表和分类表的详情
SELECT * FROM product, category;


/*
    内连接查询
    隐式内连接（推荐）：
        select * from 表A, 表B where 条件;
    显式内连接：
        select * from 表A inner join 表B on 条件;
        
        inner 可以省略
    
    多表查询的时候，字段前面要加上表名
    表名.字段名
    内连接查询只会查询关联着的数据
*/
-- 使用隐式内连接查询所有商品，以及对应分类的详情。
SELECT * FROM product, category WHERE product.category_id = category.cid;

-- 多表查询结合别名
SELECT * FROM product AS p, category AS c WHERE p.category_id = c.cid;

-- as 可以省略
SELECT * FROM product p, category c WHERE p.category_id = c.cid;

-- 起完别名之后一定要使用别名，否则就会有问题。
SELECT * FROM product p, category c WHERE product.category_id = category.cid;

# 显式内连接：
# select * from 表A inner join 表B on 条件;
# 查询商品表和他分类的详情信息。
SELECT * FROM product INNER JOIN category ON product.category_id = category.cid;

# 查询所有的商品名以及其分类的名字。
# 查询各个表中的指定字段，前面要加上表名
SELECT p.pname, c.cname FROM product p, category c WHERE p.category_id = c.cid;

# 查询哪些分类的商品已经上架
SELECT DISTINCT c.cname FROM product p, category c WHERE p.category_id = c.cid AND p.flag = '1';

/*
    外连接查询
    左外连接：
        select * from 表A left outer join 表B on 条件;
        会查询左表中的所有数据，以及右表中关联着的数据。
    右外连接：
        select * from 表A rigth outer join 表B on 条件;
        会查询右表中的所有数据，以及左表中关联着的数据。
        
    outer 关键字可以省略
*/
# 左外连接查询product和category表中的数据
SELECT * FROM category LEFT JOIN product ON product.category_id = category.cid;

# 右外连接
SELECT * FROM product RIGHT JOIN category ON product.category_id = category.cid;

# 查询所有分类商品的个数
SELECT c.cname, COUNT(p.pid) FROM category c LEFT JOIN product p ON p.category_id = c.cid 
GROUP BY c.cid;

2.2 子查询

/*
    子查询。
    一个查询语句作为另一个查询语句的一部分出现。
    可以作为另一个查询语句的条件，也可以作为另一个查询语句的表。
*/
# 查询“化妆品”分类商品详情
# 内连接查询
SELECT * FROM product p, category c WHERE p.category_id = c.cid AND c.cname = '化妆品';

# 
SELECT * FROM product WHERE category_id = 'c003';

# 查询化妆品对应的分类id是几
SELECT cid FROM category WHERE cname = '化妆品';

# 变形
# 子查询，此时一个查询语句作为了另一个查询语句的条件出现
SELECT * FROM product WHERE category_id = (SELECT cid FROM category WHERE cname = '化妆品');


# 一个查询语句作为另一个查询语句的表出现
# 对商品表和temp表进行查询
SELECT * FROM product p, temp t WHERE p.category_id = t.cid;

# 查询分类表，查询化妆品分类详情
SELECT * FROM category WHERE cname = '化妆品';

# 变形，一个查询语句作为另一个查询语句的表出现
SELECT * FROM product p, (SELECT * FROM category WHERE cname = '化妆品') t 
WHERE p.category_id = t.cid;

# 查询“化妆品”和“家电”两个分类上架商品详情

# 查询化妆品和家电对应的分类id
SELECT cid FROM category WHERE cname = '化妆品' OR cname = '家电';

SELECT * FROM product WHERE category_id IN ('c001', 'c003');


-- 合并最终版
SELECT * FROM product WHERE category_id IN (SELECT cid FROM category WHERE cname = '化妆品' OR cname = '家电');

3 逻辑外键和物理外键（扩展）

/*
    逻辑外键和物理外键
    
    逻辑外键：不需要手动添加foreign key外键约束，只需要在表的字段上建立逻辑关系即可。
    物理外键：手动添加的foreign key约束。
    
    逻辑外键：使用起来非常的灵活。 不能保证数据的完整性。（会在java程序中加入完整性的验证）。
    物理外键：可以保证数据的完整性， 使用起来非常的不灵活。 
*/
CREATE TABLE category (
  cid VARCHAR(32) PRIMARY KEY ,
  cname VARCHAR(50)
);
CREATE TABLE product(
  pid VARCHAR(32) PRIMARY KEY , -- 商品id 是主键
  pname VARCHAR(50), -- 商品名称
  price INT, -- 商品价格
  flag VARCHAR(2),              #是否上架标记为：1表示上架、0表示下架
  category_id VARCHAR(32)
);

INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
#商品
INSERT INTO product(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');
INSERT INTO product(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');
INSERT INTO product(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');

INSERT INTO product (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');
INSERT INTO product (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');
INSERT INTO product (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');
INSERT INTO product (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');

INSERT INTO product (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');
INSERT INTO product (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');



-- 查询商品表和分类表所有的信息
SELECT * FROM product p, category c WHERE p.category_id = c.cid;

备注：一般项目中，数据库是整个环节中嘴脆弱的地方，一般能不用数据库做就不用数据库做。

4 sql优化

/*
    表结构的优化：
        如果表的关联关系比较远，可以多加入中间表
        如果表中的某个字段特别大，或者这个字段不太常用，可以单独放入到一个表中。
        可以对数据表进行水平拆分，垂直拆分。
    优化查询：
        可以使用数据库索引
        如何添加索引
        唯一索引：
            alter table 表名 add unique (字段名);
            数据库会对唯一约束的列自动加上唯一索引。
        普通索引
            alter table 表名 add index (字段名);
    建议对经常使用where条件的字段加上索引。
*/
ALTER TABLE product ADD INDEX (pname);
SELECT * FROM product WHERE category_id IS NULL;

数据库索引

数据库索引.png

3 JDBC

JDBC（Java DataBase Connectivity,java数据库连接）是一种用于执行SQL语句的Java API。JDBC是Java访问数据库的标准规范，可以为不同的关系型数据库提供统一访问，它由一组用Java语言编写的接口和类组成。

JDBC规范

jdbc规范.png

3.1 JDBC准备工作及开发步骤

jdbc准备工作：

1. 创建数据库，以及数据表，然后往表中添加数据。

2. 导入数据库驱动jar包。

3. 核心API介绍（全都在java.sql包下，这是由java提供的规范）。

DriverManager：类，用于注册驱动以及获取到一个数据库连接对象

Connection：接口。表示数据库连接

Statement：接口。表示sql语句执行者对象。

ResultSet：接口。表示结果集

上面接口的实现类都在驱动jar包中。

jdbc开发步骤（非常重要，必须记住）：

1. 注册驱动.

2. 获取连接。

3. 获取sql语句执行者对象

4. 执行sql语句

5. 处理结果集（只有查询需要处理结果集，增删改操作是不需要）

6. 释放资源

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import com.mysql.jdbc.Driver;


public class Demo01JDBC {
    public static void main(String[] args) throws SQLException {
        //1. 注册驱动
        //可以使用DriverManager里面的一个方法去注册驱动。
        //static void registerDriver(Driver driver)：参数是一个java.sql.Driver 接口。 需要传递这个接口的实现类对象，实现类在驱动jar包中。com.mysql.jdbc.Driver
        DriverManager.registerDriver(new Driver());
        
        //2. 获取连接。
        //也是通过DriverManager里面的一个方法去获取
        //static Connection getConnection(String url, String username, String password)：获取连接
        /*
         * 参数url：用于表示连接数据库的字符串。固定格式：  jdbc:mysql://ip地址:端口号/数据库名称
         * 参数username： 表示的是数据库用户名
         * 参数password：表示的是数据库的密码
         * 返回值是数据库连接对象
         */
        String url = "jdbc:mysql://localhost:3306/mydb";
        String username = "root";
        String password = "root";
        Connection con = DriverManager.getConnection(url, username, password);
        
        //3. 获取sql语句执行者对象
        //可以通过Connection中的一个方法区获取sql语句执行者对象
        //Statement createStatement()：创建sql语句执行者对象
        Statement st = con.createStatement();
        
        //4. 执行sql语句
        //执行sql需要使用Statement里面的方法去执行
        //int executeUpdate(String sql)：用于执行sql语句。 注意：此方法只能执行增删改的操作。  不能执行查询。
        String sql = "INSERT INTO product (pname, price, flag) VALUES ('小灵通', 800, '0');";
        int row = st.executeUpdate(sql);// 返回值为受影响的行数
        System.out.println("row：" + row);
        
        //5. 增删改操作不需要处理结果集
        
        //6. 释放资源
        st.close();
        con.close();
    }
}

3.2 JDBC执行查询语句（重点）

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.mysql.jdbc.Driver;

/*
    jdbc 执行查询操作。
    
    1. 注册驱动。
    2. 获取连接
    3. 获取sql语句执行者对象
    4. 执行sql
    5. 处理结果集。
    6. 释放资源
 */
public class Demo05JDBC {
    public static void main(String[] args) throws Exception {
        //注册驱动
        //DriverManager.registerDriver(new Driver());
        
        Class.forName("com.mysql.jdbc.Driver");
        
        
        //获取连接
        String url = "jdbc:mysql://localhost:3306/mydb";
        String username = "root";
        String password = "root";
        Connection con = DriverManager.getConnection(url, username, password);
        
        //获取sql语句执行者对象
        Statement st = con.createStatement();
        
        //执行sql
        String sql = "SELECT * FROM product";
        //执行sql不能再使用executeUpdate了，因为这个方法只能执行增删改操作，执行查询需要使用其他的方法
        //ResultSet executeQuery(String sql)：用来执行查询，返回值为ResultSet类型，表示查询后的结果集
        ResultSet rs = st.executeQuery(sql);
        /*
         * ResultSet 表示查询后的结果集。 里面其实封装了查询后得到的表。
         * ResultSet中 有一个光标，最开始指向第一行的前一行。
         * 里面有两个方法，可以帮助我们处理这个结果集。
         * next()：把光标向下移动一个位置，然后判断这行有没有数据，如果有返回值为true，如果没有返回值为false。
         * getXXX(列名)： 获取当前行指定列的数据
         */
        
        //使用while循环获取每一行的数据
        
        while(rs.next()) {
            //如果有数据，就获取到这一行每一列的数据
            int pid = rs.getInt("pid");
            String pname = rs.getString("pname");
            /*
            if(pname.length() > 4) {
                pname = pname.substring(0, 3) + "..";
            }
            */
            int price = rs.getInt("price");
            String flag = rs.getString("flag");
            System.out.println(pid + "\t" + pname + "\t" + price  + "\t" + flag);
        }
        
        /*
        while(rs.next()) {
            //不管数据库中的类型是什么都可以使用getObject方法去获取。
            System.out.println(rs.getObject("pid") + "\t" + rs.getObject("pname"));
        }
        */
        
        /*
         * 如何获取ResultSet结果集中的内容。
         *  1. 先把光标向下移动一行，判断有没有数据。
         *  2. 如果有数据，就调用getXXX获取数据。
         */
        /*
        if(rs.next()) {//先把光标向下移动一行，判断有没有数据。
            //获取数据，使用getXXX方法
            int pid = rs.getInt("pid");
            String pname = rs.getString("pname");
            int price = rs.getInt("price");
            String flag = rs.getString("flag");
            System.out.println(pid + "\t" + pname + "\t" + price  + "\t" + flag);
        }
        
        if(rs.next()) {//先把光标向下移动一行，判断有没有数据。
            //获取数据，使用getXXX方法
            int pid = rs.getInt("pid");
            String pname = rs.getString("pname");
            int price = rs.getInt("price");
            String flag = rs.getString("flag");
            System.out.println(pid + "\t" + pname + "\t" + price  + "\t" + flag);
        }
        
        if(rs.next()) {//先把光标向下移动一行，判断有没有数据。
            //获取数据，使用getXXX方法
            int pid = rs.getInt("pid");
            String pname = rs.getString("pname");
            int price = rs.getInt("price");
            String flag = rs.getString("flag");
            System.out.println(pid + "\t" + pname + "\t" + price  + "\t" + flag);
        }
        */
        
        //释放资源
        rs.close();
        st.close();
        con.close();
    }
}

3.3 注册驱动的两种方式

import java.sql.DriverManager;
import java.sql.SQLException;

import com.mysql.jdbc.Driver;

/*
    之前注册驱动的方式
        DriverManager.registerDriver(new Dirver());
        
    之后的方式
        Class.forName("com.mysql.jdbc.Driver");
 */
public class Demo06JDBCDriver {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        //方式一：会注册两次，因为Driver类中的静态代码块也会注册一次，不推荐。
        //DriverManager.registerDriver(new Driver());
        
        //方式二：会注册一次，当把类加载到内存中的时候，会执行静态代码块，会把Driver里面的注册驱动的代码执行了。 推荐的
        Class.forName("com.mysql.jdbc.Driver");
    }
}

4 jdbc工具类

JDBCUtils类

import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/*
    如果把数据库的四大信息（驱动类名，url，用户名，密码）写在java程序中，会很不灵活。
*/
public class JDBCUtils {
    
    private static String url;
    private static String username;
    private static String password;
    
    static {
        //静态代码块执行的时候会读取配置文件并且给url，username，以及password进行赋值。
        try {
            //创建Properties集合
            Properties p = new Properties();
            //创建输入流
            FileReader fr = new FileReader("db.properties");
            //调用load加载键值对
            p.load(fr);
            //获取连接
            url = p.getProperty("url");
            username = p.getProperty("username");
            password = p.getProperty("password");
            String className = p.getProperty("className");
            //注册驱动，放在静态代码块，只执行一次
            Class.forName(className);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    /*
     * 定义了方法，用于获取数据库连接
     */
    public static Connection getConnection() throws Exception {
        Connection con = DriverManager.getConnection(url, username, password);
        return con;
    }
    
    /*
     * 定义方法，用来释放资源
     */
    public static void close(ResultSet rs, Statement st, Connection con)  {
        
        try {
            if(rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        try {
            if(st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        try {
            if(con != null) {
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

在项目下创建文件

db.properties文件

className=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mydb
username=root
password=root

MustangYL

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
day019 mysql多表查询&JDBC

1 多表关系实战1.1 一对多（省市）省市一对多、省市自我一对多两张表/* 省市一对多的关系省表（主表）市表（从表）*/# 省表（主表）CREATE TABLE province ( id INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(20), description VARCHAR(20));# 市表(从表)CREATE TABLE city ( .
复制链接

扫一扫