Java-MySQL数据库和JDBC编程-学习笔记

（主要参考《疯狂Java》）。

1.JDBC

JDBC全称为Java Database Connectivity，即Java数据库连接，是一种可以执行SQL语句的Java API，程序通过它可连接到关系数据库，并使用结构化查询语言（SQL)来查询、更新数据库。

JDBC可以完成以下三个基本工作：

• 建立与数据库的连接
• 执行SQL语句
• 获得SQL语句的执行结果

大部分数据库系统（如Oracle，Sybase）都有相应的JDBC驱动程序。JDBC驱动通常有以下四种类型：

• 第一种，JCBC-ODBC桥，最早实现的JDBC驱动程序，将JDBC API映射到ODBC API，在Java 8中已经删除了这种方式。
• 第二种，将JDBC API映射成数据库特定的客户端API。这种驱动包含特定数据库的本地代码，用于访问特定数据库的客户端。
• 第三种，支持三层结构的JDBC访问方式，只要用于Applet阶段，通过Applet访问数据库。
• 第四种，纯Java的，直接与数据库实例交互，时目前最流行的JDBC驱动。

2.SQL语法

SQL语句是对所有关系数据库都通用的命令语句。下面以MySQL数据库为例介绍SQL语法知识。

2.1 Windows上安装MySQL

可以参考

https://www.runoob.com/w3cnote/windows10-mysql-installer.html

，这里我按照书上的流程一步步安装完成了。

1. 首先登陆网站

https://dev.mysql.com/downloads/windows/installer/

下载MySQL最新版，如图：

2. 然后安装，等一段时间出现下面界面，选择第五个
3. （直接上图好了）-注意点击Advanced options设置安装路径和数据文件的存储路径
4. -
5. -
6. -
7. -
8. -
9. -
10. -
11. -
12. -
13. -
14. -
15. -
16. -

这样就算安装完成了。

2.2 关系数据库的基本概念和MySQL基本命令

严格地说，数据库（Database）只是存放用户数据的地方，当用户访问、操作数据库中的数据时就需要数据库管理系统（Database Management System ，DBMS)了。DBMS是所有数据的知识库，负责管理数据的存储、安全、一致性、并发、恢复和访问等操作。它有一个数据字典（系统表），用于存储它拥有的每个事务的相关信息，如名字、结果、位置和类型，这种关于数据的数据成为元数据（metadata）。

按照时间顺序有以下几种数据库系统：

• 网状型数据库
• 层次型数据库
• 关系数据库
• 面向对象数据库

其中关系数据库是理论最成熟、应用最广泛的数据库。

MySQL的一个实例（Server Instance）可以同时包含多个数据库，使用如下命令查看当前实例包含多少数据库：

show databases;

创建新的数据库

create database if not exists 数据库名;

删除数据库

drop databases 数据库名;

进入指定数据库

use  数据库名;

查询数据库下有多少数据表

show tables;

查看指定数据表的表结构（查看该表有多少列，每列的数据类型等）

desc 表名;

如图

MySQL通常支持以下两种存储机制：

• MyISAM：MySQL早期默认的存储机制，对事务支持不够好
• InnoDB：提供事务安全的存储机制…

通常推荐使用InnoDB，由于系统默认使用InnoDB，如果需要指定存储机制，可以在标准建表语法后添加：

• ENGINE=MyISAM
• ENGINE=InnoDB

2.3 SQL语句基础

SQL(Structurd Query Lauguage 结构化查询语句)，是操作和检索关系数据库的标准语言，可用于操作任何关系数据库。可以完成以下任务：

• 在数据库中检索信息
• 对数据库进行更新
• 改变数据库的结构
• 更改系统的安全设置
• 增加或回收用户对数据库、表的许可权限

程序员一般可以管理前三个任务，后两个由数据库管理员（DBA）来完成。

SQL语句通常有以下几种类型：

• 查询语句：主要由Select关键字来完成，最复杂、功能最丰富的语句
• DML（Data Manipulation Language，数据操作语言）语句：主要由insert、update和delete关键字完成
• DDL（Data Definition Language，数据定义语言）语句：create、alter、drop、truncate
• DCL（Data Control Language，数据控制语言）语句：grant、revoke
• 事务控制语句：commit、rollback、savepoint

SQL不区分大小写。DCL用于为数据库用户授权或回收指定用户权限，无需程序员操作，所有就不用介绍关于DCL的知识。

SQL的标识符用于定义表明、列名和变量，命名规则如下：

• 以字母开头
• 包含字母、数字、三个特殊字符（#_$）
• 不能使用关键字、保留字
• 同模式下对象不能重名

2.4 DDL语句

1. 创建表

DDL是操作数据库对象的语句（create创建、drop删除、alter修改），数据库中可包含以下几种常见的数据库对象：

对象	关键字	描述
表	table	存储数据的逻辑单元，列为字段，行为记录
数据字典		系统表，存放数据库相关信息的表，由数据库系统维护，通常只可查看
约束	constraint	执行数据校验的规则，保证数据的完整性
视图	view	数据表里的逻辑显示，不存储数据
索引	index	提高查询性能
函数	function	完成一次特定的计算，有一个返回值
存储过程	procedure	完成一次完整的业务处理，无返回值，可传出参数将多个值给调用环境
触发器	trigger	相当于一个事件监听器

建表时需要指定每列的数据类型，MySQL支持如表的几种列类型

列类型	说明
tinyint/smallint/mediumint/ int(integer)/bigint	1/2/3/4/8字节整数
float/double	单精度、双精度浮点型
decimal(dec)	精确小数类型
date	日期类型，不能保存时间
time	时间类型
datetime	日期、时间类型
timestamp	时间戳类型
year	年类型
char	定长字符串类型
varchar	可边长字符串类型
binary	定长二进制字符串类型
varbinary	可变长二进制字符串类型
tinyblob/blob/mediumblob/longblob	1/2/3/4字节二进制对象，可存储超长音乐、图片等二进制数据：25B/64KB/16MB/4GB大小
tinytext/text/mediumtext/longtext	1/2/3/4字节文本对象，可存储超长字符串：25B/64KB/16MB/4GB大小
enum(‘value1’,‘value2’,…)	枚举类型
set(‘value1’,‘value2’,…)	集合类型

如果使用子查询建表语句，可以在建表的同时插入数据：

create tabel [模式名.] 表名 [colum[,column...]]
as subquery;

使用子查询创建一个和test完全相同的表

create tabel he as select * from test;

2. 修改表结构

增加字段

alter table test1 add
(
aaa varchar(255) defaut 'xxx',
bbb varchar(255)
);

修改列定义

 alter table test1 modify bbb int;

删除列

alter table test1 drop test_i;

重命名表名

alter table test1 rename to test;

改变列名

alter table test change aaa a1 int;

删除表

drop table t1;

2.5 数据库约束

约束是在表上强制执行的数据校验规则，用于保证数据库中数据的完整性，同时当表中数据存在相互依赖性是可以保护相关数据不被删除。
大多数数据库支持以下5种完整性约束：

• Not NULL：非空约束，指定某列不能为空
• UNIQE：唯一约束，指定某列或几列组合不能重复
• PRIMARY KEY：主键，指定该列的值可以唯一的标识该条记录
• FOREING KEY：外键，指定该行记录从属于主表中的一条记录，主要用于保证参照的完整性
• CHECK：检查，指定一个布尔表达式指定对应列的值必须满足该表达式
  MySQL不支持CHECK约束。

1.NOT NULL 约束

create table t2(
i1 int not null,#建立非空约束
name char(255) default 'xy' not null,
c char(255) null #默认为空
);

alter table t2 modify c char(255) not null;#修改增加非空约束
alter table t2 modify name char(255) null;#取消非空
alter table t2 modify c char (255) defaut 'xxx' null;#取消非空并设置默认值

2.UNIQE

唯一约束的列不可出现重复值，但可以出现多个null值，注意。
建表时添加唯一约束

create table t3(t int unique);
create table t4(tt1 int ,tt2 int ,tt3 int,
unique (tt1),#表级约束语法
constraint test_name unique(tt2) #指定约束名
);
create table t5(
tt1 int,tt2 int,tt3 int,
constraint test_name unique(tt1,tt2)#指定两类组合不允许重复
);

修改表时添加唯一约束

alter table t4 add unique (tt3);
alter table t5 modify tt3 int unique;

删除约束

alter t4 drop index  test_name ;

3.PRIMARY KEY

逐渐约束相当于非空约束加唯一约束，即不重复且非null；对于多列组合建立主键约束则要求每列非空，但只要求这些列的组合不能重复。主键列的值可用于唯一的标识表中的一条记录。…
每一个表中最多允许一个主键。
建表时建立约束

create table t6(
t1 int primary key);
create table t7(
t2 int ,
constraint t2_name primary key(t2));
create table t8(
t3 int,t4 int ,
primary key (t3,t4));

删除约束

alter t6 drop primary key;

增加约束

alter table t6 add primary key(t1);
alter table t5 modify tt1 int primary key;

自增长特性

create table t9(
t1 int auto_increment primary key,
t2 varchar(255),
t3 varchar(225))

4. FOREIGN KEY

外键约束保证一个或两个数据表之间的参照的完整性，外键是构建一个表的两个字段或者两个表的两个字段之间的参照关系。
如

create teacher(
t1 int auto_increment,
t2 varchar(255),
primary key(t1));
create table student(
t11 int auto_increment,
t12 varchar(255),
t13 int references teacher (t1));

create teacher1(
t1 int auto_increment,
t2 varchar(255),
primary key(t1));
create table student1(
t11 int auto_increment,
t12 varchar(255),
t13 int ,
foreign key(t13) references teacher1 (t1));

可以用constraint 来指定约束名字

constraint name foreign key(t13) references teacher1 (t1));

删除和增加和前面的约束类似

alter table student drop foreign key;
alter table stduent add foreign key(t13) references teacher (t1);

另外外键约束还可以参照自身

create table fore(
t1 int auto_increment primary key,
t2 varchar(225) ,
t3 int ,
foreign key(t3) references fore(t1)
);

定义删除主表记录时从表记录全部级联删除 ： on delete cascade
定义删除主表记录时从表记录的外键设为null：on delete set null

create teacher2(
t1 int auto_increment,
t2 varchar(255),
primary key(t1));
create table student2(
t11 int auto_increment,
t12 varchar(255),
t13 int ,
foreign key(t13) references teacher2 (t1) on delete cascade
);

5.CHECK

create che(
t1 int ,
t2 int ,
t3 varchar(225),
check(t3>0));

实际上MySQL建立的这个约束不会起作用的。

2.6 索引

语法：

create index index_name on teacher2 (t1,t2);
drop index index_name on teacher2;

2.7 视图

create or replace view vname as select t1 from teacher2 with check option;
drop view vname;

2.8 DML语句

DML 可以实现：

• 插入新数据
• 修改已有数据
• 删除不再需要的数据

1. insert into

insert into teacher2 (t2) values('xxx');

2. update

update teacher2 set t2='axx' while t1>1;

3. delete from

delete from teacher2
while t1>2;

2.9 单表查询

select 用于选择那些列，where 用于确定选择那些行

然后利用distinct 可以去除重复行，还可以用order by 排序。

2.10 数据库函数

2.11 分组和组函数

常见的组函数有如下5个：

• avg () 计算平均值
• coumt() 计算总条数
• max()
• min()
• sum()

3. JDBC的典型用法

3.1 连接mysql

首先需要加载一个最新版的驱动包，版本一定要和你安装的MySQL版本一致否则可能会报错，下载地址https://www.runoob.com/java/java-mysql-connect.html 。

import java.sql.*;

public class con_mysql {
    public static void main(String[] args){
        try {

            Class.forName("com.mysql.cj.jdbc.Driver");//加载驱动
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("sss");
        }
        try {
            Connection conn= DriverManager.getConnection
                    ("jdbc:mysql://127.0.0.1:3306/a1?useSSL=false&serverTimezone=UTC",
                            "root","xhh22900");
            //注意第一个参数中的"a1"是你要访问的数据库名
            Statement stmt=conn.createStatement();
            ResultSet rs=stmt.executeQuery(" select *from tx;");//输入MySQL查询的查询语句，返回查询结果
            while (rs.next()){
                System.out.println(rs.getInt(1)+rs.getString(2)+rs.getInt(3));
            }//分别输出第一列、第二列、第三列 的值
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("xxx");
        }
    }
}

3.2 执行SQL语句

前面我知道查询一次数据库的程序一共需要四个变量：driver 、url、user、password。这四个变量相比较每次在程序里面写出来，把他保存为配置文件更好一点。我的做法是把这四个变量作为一个特殊的类的成员变量，这样每次导入也非常方便：

public class paramfile {
    public static final String driver="com.mysql.cj.jdbc.Driver";
    public static final String url="jdbc:mysql://127.0.0.1:3306/a1?useSSL=false&serverTimezone=UTC";
    public static final String user="root";
    public static final String password="xhh22900";
}

我们可以用executeUpdate方法来执行SQL语句如：

import java.sql.*;

public class executeddl {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        var driver=paramfile.driver;
        var url=paramfile.url;
        var user=paramfile.user;
        var password=paramfile.password;
        System.out.println(driver+"\n"+url+"\n"+user+"\n"+password+"\n");

        Class.forName(driver);
        Connection con= DriverManager.getConnection(url,user,password);
        Statement sta=con.createStatement();
        sta.executeUpdate("create table jt1(t1 int auto_increment primary key,t2 varchar(225));");
        sta.executeUpdate("insert into jt1 (t2) values('a');");
        sta.executeUpdate("insert into jt1 (t2) values('b');");
        sta.executeUpdate("insert into jt1 (t2) values('c');");
        boolean test=sta.execute("select *from jt1");
        if(test){
            ResultSet resultSet=sta.getResultSet();
            while (resultSet.next()){
                System.out.println(resultSet.getInt(1)+resultSet.getString(2));
            }
        }
        ResultSet res= sta.executeQuery("select *from jt1");
        while (res.next()){
            System.out.println(res.getInt(1)+res.getString(2));
        }
    }
}

execute()方法返回一个boolean值，表明语句是否返回ResultSet对象，通过getResultSet()方法可以回去其ResultSet对象，getUpdateCount()获取语句所影响的记录行数。

使用preparedStatement来指定SQL语句

import java.sql.*;

public class executeddl {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        var driver=paramfile.driver;
        var url=paramfile.url;
        var user=paramfile.user;
        var password=paramfile.password;
        //System.out.println(driver+"\n"+url+"\n"+user+"\n"+password+"\n");

        Class.forName(driver);
        Connection con= DriverManager.getConnection(url,user,password);
        Statement sta=con.createStatement();
        sta.executeUpdate("create table jt1(t1 int auto_increment primary key,t2 varchar(225));");

        PreparedStatement preparedStatement=con.prepareStatement("insert into jt1 values (null,?)");
        for (var i=0;i<100;i++){
            preparedStatement.setString(1,"index-"+i);
            preparedStatement.executeUpdate();
        }

        ResultSet res= sta.executeQuery("select *from jt1");
        while (res.next()){
            System.out.println(res.getInt(1)+res.getString(2));
        }
    }
}