mysql2

mysql的备份文件简单,没有加密,其他一些高级的数据库都是加密的文件

所谓关系型数据库就是表与表之间存在关系

每次在表加列的方式,这样取数据类名不固定,行不通

所以我么可以加表

表与表之间的关系:

展示:

我们学习表结构,一定要掌握谁是主键谁是外键

数据准备表都不用自己写了,直接粘

增加外键约束语法:

alter table 从表名 add constraint foreign key 外键名(本列的外键列) references 主表名(主键列名)

代码演示:

数据准备

/*
  商品的分类表
  cid主键自动增长
  cname 商品分类名称
*/
CREATE TABLE category(
	cid INT PRIMARY KEY AUTO_INCREMENT,
	cname VARCHAR(32)
);
/*
  商品表
  pid主键自动增长
  category_id 列,是一个外键列
  列的数据,必须以category表的cid列数据为基准
*/

CREATE TABLE product(
	pid INT PRIMARY KEY AUTO_INCREMENT,
	pname VARCHAR(32),
	price INT,
	category_id INT
);

/*
  创建外键约束
  category表的cid列数据 控制  product表的category_id列中的数据
  语法:
    alter table 从表名 add constraint foreign key [约束名] (从表的外键的列名)
    references 主表名(主键列名)
*/
ALTER TABLE product ADD CONSTRAINT FOREIGN KEY fk_category_id (category_id)
REFERENCES category(cid);

添加数据测试

# 商品分类表添加测试数据
INSERT INTO category VALUES(NULL,'家电');
INSERT INTO category VALUES(NULL,'服装');

# 商品信息表添加测试数据,注意category_id列的数据,参照主表的主键编写
INSERT INTO product VALUES(NULL,'联想笔记本',5999,1);
INSERT INTO product VALUES(NULL,'花花公子',50,2);

# 但是如果我们添加的数据的category_id不在主表的cid中,那么我们就是添加失败
INSERT INTO product VALUES(NULL,'饼干',99,3);
-- error,但是我们主键已经自增了,后面的顺序就会跳过这一个

在架构器中可以看见表与表之间的关系

多表关系实战

1 实战1：省和市

• 方案1：多张表，一对多

-- 创建省份表
create table province(
	pid int PRIMARY KEY,
	pname varchar(32), -- 省份名称
	description varchar(100) -- 描述
);

-- 创建城市表
create table city (
	cid int PRIMARY KEY,
	cname varchar(32), -- 城市名称
	description varchar(100), -- 描述
	province_id int,
	CONSTRAINT city_province_fk foreign key(province_id) references province(pid)
);

• 方案2：一张表，自关联一对多(扩展)

create table area (
	id int PRIMARY key AUTO_INCREMENT,
	`name` varchar(32),
	description varchar(100),
	parent_id int,
	CONSTRAINT area_area_fk FOREIGN KEY(parent_id) REFERENCES area(id)
);

INSERT into area values(null, '辽宁省', '这是一个省份', null);
INSERT into area values(null, '大连市', '这是一个城市', 1);
INSERT into area values(null, '沈阳市', '这是一个城市', 1);
INSERT into area values(null, '河北省', '这是一个省份', null);
INSERT into area values(null, '石家庄市', '这是一个城市', 4);
INSERT into area values(null, '保定市', '这是一个城市', 4);

2 实战2：用户和角色

• 多对多关系

-- 用户表
create table `user` (
	uid varchar(32) PRIMARY KEY,
	username varchar(32),
	`password` varchar(32)
);

-- 角色表
create table role (
	rid varchar(32) PRIMARY KEY,
	rname varchar(32)
);

-- 中间表
create table user_role(
	user_id varchar(32),
	role_id varchar(32),
	CONSTRAINT user_role_pk PRIMARY KEY(user_id,role_id),
	CONSTRAINT user_id_fk FOREIGN KEY(user_id) REFERENCES `user`(uid),
	CONSTRAINT role_id_fk FOREIGN KEY(role_id) REFERENCES role(rid)
);

第三章 多表查询 重点重点重点

提供表结构如下：

# 分类表
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_category_fk FOREIGN KEY (category_id) REFERENCES category (cid)
);

1 初始化数据

#分类
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','JACK JONES',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');

2 多表查询

交叉连接查询(开发中不使用-得到的是两个表的乘积) [了解]

• 语法：select * from A,B;

select * from category,products ORDER BY cid, pid;

• 效果: category表中每条记录, 与 products表中每条记录分别连接

-- 上课代码
-- 两张表排列组合的结果 称之为 笛卡尔积
-- 问题: 存在很多错误的数据
select * from products, category
where products.category_id=category.cid;

-- 问题: 太长 解决: 别名
select * from products as p, category as c
where p.category_id=c.cid;

select * from products  p, category  c
where p.category_id=c.cid;

select p.pname, c.cname from products  p, category  c
where p.category_id=c.cid;

内连接查询(使用的关键字 inner join – inner可以省略)

• 隐式内连接：select * from A,B where 条件;

• 显示内连接：select * from A inner join B on 条件;

-- 查询那些分类的商品已经上架
-- 隐式内连接
SELECT DISTINCT
	c.cname
FROM
	category c,
	products p
WHERE
	c.cid = p.category_id;

-- 显示内连接
SELECT DISTINCT
	c.cname
FROM
	category c INNER JOIN products p 
ON 
	c.cid = p.category_id;

外连接查询(使用的关键字 outer join – outer可以省略)

• 左外连接：left outer join
  • select * from A left outer join B on 条件;
• 右外连接：right outer join
  • select * from A right outer join B on 条件;

#2.查询所有分类商品的个数
#左外连接
INSERT INTO category(cid,cname) VALUES('c004','奢侈品');

SELECT cname,COUNT(category_id) 
FROM category c LEFT OUTER JOIN products p 
ON c.cid = p.category_id 
GROUP BY cname;

• 效果

3 子查询

需求1:查询归属于化妆品的商品
需求2:查询归属于化妆品和家电的商品

子查询：一条select语句结果作为另一条select语法一部分（查询条件，查询结果，表等）。
语法：select ....查询字段 ... from ... 表.. where ... 查询条件

-- 子查询, 查询“化妆品”分类上架商品详情

-- 内连接
select 
	p.*
from 
	category c, products p
WHERE
	c.cid = p.category_id and c.cname = '化妆品'


-- 子查询 第一种(作为查询条件值使用)
select 
	* 
from
	products p
where
	p.category_id = (SELECT cid from category where cname='化妆品') -- 'c003'

-- SELECT cid from category where cname='化妆品';



-- 子查询 第二种(作为 一张表 使用)
select 
	p.*
FROM
	products p, (select * from category where cname='化妆品') c
WHERE
	p.category_id = c.cid;


-- select * from category where cname='化妆品'

• 效果

子查询练习：

#查询“化妆品”和“家电”两个分类上架商品详情
select
	*
from 
	products 
WHERE
	category_id in (select cid from category where cname='家电' or cname='化妆品');  -- 'c001', 'c003'


-- select cid from category where cname='家电' or cname='化妆品';
-- select cid from category where cname in ('家电', '化妆品');

敢比会重要

积极

学完数据库的基本操作之后，我们可以开始学习JDBC。

什么是JDBC呢？

就是我们当都使用数据库那是没有意义的，只能存储数据而无法使用，于是需要java和database进行交互,这个交互技术就是JDBC(java,数据库连接技术),

那么问题又来了,我们有很多种数据库,mysql只是其中的一种,那么我们的代码是不是要对每一种数据库都要重新的编写?

如果是这样的话,那对我们的编程人员也太过于繁琐了,于是java提供了一个规范sql,这个规范规定了我们的数据库的代码要怎么写,我们只需要根据这个来实现我们的需求就可以了,当然这是接口啊,那我们如果想要实现类,那我们该如何写呢??

毕竟我们的实现类是和数据库有关,而数据库的具体原理我们并不清楚,所以数据库的厂商就根据java提供的这个规范而开发出了一个专属于他们自己数据库的实现类,这个实现类的包我们称为驱动

package com.zjf.jdbc;

import com.mysql.jdbc.Driver;

import java.sql.*;

public class JDBCDemo1 {
    public static void main(String[] args) throws SQLException {
        //首先我们要获取到我们的驱动对象呀
        //获取完了驱动对象之后我们是不是要告诉我们的java我们要连接的是哪一个数据库呀
        //连接完了数据库之后我们是不是要获得一个可以执行我们的sql语句的对象呀,我们不能让连接对象去帮我们执行我们的sql吧
        //获得了执行者对象之后,我们是不是用该要写sql语句了呀
        //然后通过执行者对象调用他自己的方法来执行我们的sql
        //之后关流

        //首先我们要获取到我们的驱动对象呀
        DriverManager.registerDriver(new Driver());

        //获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/day_02", "root", "root");
        System.out.println(connection);

        //获取执行这
        Statement statement = connection.createStatement();
        System.out.println(statement);

        //执行
        String s = "SELECT * FROM `user`";
        ResultSet resultSet = statement.executeQuery(s);
        //对结果集进行获取数据
        while (resultSet.next()){
            System.out.println(resultSet.getInt("id") + "\t\t" + resultSet.getString("username") + "\t\t" + resultSet.getString("password") + "\t\t" + resultSet.getString("nickname"));
        }
        //关流
        resultSet.close();
        statement.close();
        connection.close();

    }
}

package com.zjf.jdbc;

import com.mysql.jdbc.Driver;
import com.zjf.POJO.User;

import java.sql.*;
import java.util.ArrayList;

public class JDBCdemo4 {
    public static void main(String[] args) throws SQLException {
        DriverManager.registerDriver(new Driver());
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/day_02", "root", "root");
        Statement statement = connection.createStatement();
        //写sql语句
        String s = "SELECT * FROM `user`";
        ResultSet resultSet = statement.executeQuery(s);
        //创建存储的集合
        ArrayList<User> users = new ArrayList<>();
        while (resultSet.next()) {
            //创建user对象
            User user = new User();
            user.setId(resultSet.getInt("id"));
            user.setUsername(resultSet.getString("username"));
            user.setPassword(resultSet.getString("password"));
            user.setNickname(resultSet.getString("nickname"));
            
            users.add(user);
        }
        //关流
        resultSet.close();
        statement.close();
        connection.close();
        //输出集合
        for (User user1 : users) {
            System.out.println(user1);
        }
    }
}

为什么我们调用接口的方法,得到了实现类的对象呢?

这是多态的引用啊

仔细想想,我们左边是父类,右边是子类,这样不就是多态吗,方法编译看左边,执行看右边

至于result那是一个结果集,我们通过它来对查询获得的数据做提取,当然next与getXXX这个和迭代器很类似,处理结果集 (打印控制台)next() 有数据,返回true, getXXX(“列名”) XXX->数据类型.

这是修改数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dABBRAFK-1608434464303)(file:///C:/Users/Mr.Zhu/AppData/Local/Temp/msohtmlclip1/01/clip_image004.jpg)]

增加数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-B2u8zHsG-1608434464303)(file:///C:/Users/Mr.Zhu/AppData/Local/Temp/msohtmlclip1/01/clip_image006.jpg)]

删除数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ciNQzlWH-1608434464304)(file:///C:/Users/Mr.Zhu/AppData/Local/Temp/msohtmlclip1/01/clip_image008.jpg)]

同样的操作,只不过更改了sql语句而已,其他的都是相同的

案例

/** * 需求: * 查询数据表users *
每一行存储到Users类的对象中 *
4行数据,出现4个Users类的对象 --> 集合 */
分析: 创建存储的对象 我们想要表中的数据存到我们的引用类型中,
首先我们创建的这个bean对象是不是包含我们表中的每一个列的元素呀,
也就是和列一一对应,同时我们还要数据类型和数据库相同
有了这个对象,我们是不是要想从数据库中拿数据
通过我们上面自建类的set方法存入元素中,然后把元素方法集合

代码演示

package com.zjf.jdbc;

 import com.mysql.jdbc.Driver;
 import com.zjf.POJO.User;
 
 import java.sql.*;
 import java.util.ArrayList;
 
 public class JDBCdemo4 {
   public static void main(String[] args) throws SQLException {
     DriverManager.registerDriver(new Driver());
     Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/day_02", "root", "root");
     Statement statement = connection.createStatement();
     //写sql语句
     String s = "SELECT * FROM `user`";
     ResultSet resultSet = statement.executeQuery(s);
     //创建存储的集合
     ArrayList<User> users = new ArrayList<>();
     while (resultSet.next()) {
       //创建user对象
       User user = new User();
       user.setId(resultSet.getInt("id"));
       user.setUsername(resultSet.getString("username"));
       user.setPassword(resultSet.getString("password"));
       user.setNickname(resultSet.getString("nickname"));
       
       users.add(user);
    }
     //关流
     resultSet.close();
     statement.close();
     connection.close();
     //输出集合
     for (User user1 : users) {
       System.out.println(user1);
    }
  }
 }

注意:我们创建对象必须要在while中创建,因为我们集合中保存的是地址,当我们在while外面创建的时候,那么我们的集合只会保存最后一个数据,当然还是三个对象,只不过数据是相同的

其实在上面的那个执行对象startment是有问题的,它存在一个注入攻击的现象,也就是我们在后期的web登录是,我们只需要在我们的登入数据中加上一个or 自定义的条件判断那我们就可以登陆成功

为了解决这个现象,于是我们引入一个新的开始对象’’

注意我们的preparestart的?,是只能放置实际参数,形式参数是不可以的,也就是说数据表的列名无法放入.

例如

Select ? From user;
我们通过preparestart来执行它的时候我们的set就传递不了一个实际的参数,也就是说这个写法是错误的,因为我们的占位符必须是要实际的参数

注意上面的的代码中的第一行我们的注册驱动,没有使用DriveManager.registerDriver,因为我们通过翻看源码发现,我们的mysql的驱动类中的Driver里面有了该代码,那就表示这我们只需要把这个类加载到内存里面即可

通过Class.forName(“全类名”),不要忘记这个反射技术哦

在上面的学习中我们是不是发现我们的,我们在使用JDBC技术的时候很多的步骤都是重复的呀,就像注册驱动,获取连接,获取执行者对象,关流,这些操作都是重复的,我们最核心的目的只是为了在java中执行一条sql语句,但是我们却需要很多的铺垫.

更具我们前面学习过的封装的思想,我们是不是可以将这些代码封装进入一个工具类中,之后我们通过这个工具类调用方法即可.

//首先创建一个工具类utils

代码演示

工具类

package com.zjf.utils;

import java.sql.*;

public class JDBCUtils {
    /**
     * 分析:我们要封装的有哪些代码
     * 注册驱动
     * 首先我们注册驱动只需要注册一次,所以我们可以把这个代码放在static中
     * 获取连接
     * 没啥可说的
     * 关闭流
     * 这个不能放在static中了,但它似乎有个问题,我们的查询和修改所要关闭的流数量好像不同,如何解决呢?通过if是否为null判断
     */
    //代码实现
//静态代码块
    static {
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
        }
            catch (ClassNotFoundException e1){
            e1.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection(){
        //获取连接
        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/day_02", "root", "root");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    //关流
    public static void close(Connection con, Statement pre, ResultSet res){
        if (con == null){
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pre == null){
            try {
                pre.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (res == null){
            try {
                res.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

测试类

package com.zjf.jdbc;

import com.zjf.POJO.User;
import com.zjf.utils.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;

public class JDBCdemo6 {
    public static void main(String[] args) throws SQLException {
        //来个查询语句吧
        //首先注册驱动,是不是我们直接放在了工具类的静态代码块中,只需要调用这个类即可
        //直接调用获取连接,就使用了这个类了
        Connection connection = JDBCUtils.getConnection();
        //执行
        Statement statement = connection.createStatement();
        //获取结果
        ResultSet resultSet = statement.executeQuery("SELECT * FROM user");
        //创建存储的集合
        ArrayList<User> users = new ArrayList<>();

        while (resultSet.next()) {
            //创建user对象
            User user = new User();
            user.setId(resultSet.getInt("id"));
            user.setUsername(resultSet.getString("username"));
            user.setPassword(resultSet.getString("password"));
            user.setNickname(resultSet.getString("nickname"));

            users.add(user);
        }
        //关流
        JDBCUtils.close(connection, statement, resultSet);
        //输出集合
        for (User user1 : users) {
            System.out.println(user1);
        }
    }
}

既然可以把代码封装进工具类了,那我们如果有时候想要更改使用的数据库咋整???

直接改源码?

大忌,这不好不好

所以要解耦,我们要把这些遍的数据放进配置文件中,所以学习使用配置文件方JDBC的四要素

驱动包名,url,username,password

代码演示:

Proparties代码

ClassName=com.mysql.jdbc.Driver
JDBCurl=jdbc:mysql://localhost:3306/day_02
JDBCusername=root
JDBCpassword=root

utils代码的修改

package com.zjf.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {
    //代码实现
    //定义4个静态成员变量
    private static String driverClass;
    private static String url;
    private static String user;
    private static String password;
//静态代码块
    static {
        try {
            //这几个数据我们不能放在局部变量中,因为我们的变量是好几个方法一起用的
            //注册驱动
            //加载我们的配置文件,在反射的时候学过一种方法
            InputStream resourceAsStream = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
            //创建properties对象,获取数据
            Properties properties = new Properties();
            properties.load(resourceAsStream);
            //获取数据
            driverClass = properties.getProperty("ClassName");
            url = properties.getProperty("JDBCurl");
            user = properties.getProperty("JDBCusername");
            password = properties.getProperty("JDBCpassword");

            Class.forName(driverClass);
        }
            catch (ClassNotFoundException | IOException e1){
            e1.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection(){
        //获取连接
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url,user,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;

    }
    //关流
    public static void close(Connection con, Statement pre, ResultSet res){
        if (con == null){
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pre == null){
            try {
                pre.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (res == null){
            try {
                res.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

测试代码同上,略

DriverManager.getConnection(url,user,password);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;

}
//关流
public static void close(Connection con, Statement pre, ResultSet res){
    if (con == null){
        try {
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    if (pre == null){
        try {
            pre.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    if (res == null){
        try {
            res.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

}

测试代码同上,略