MySQL学习笔记

最新推荐文章于 2024-10-02 00:27:17 发布
最新推荐文章于 2024-10-02 00:27:17 发布
阅读量301 收藏
点赞数
分类专栏： MySQL自学笔记 文章标签： 数据库 database mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_51687297/article/details/123887061
版权

文章目录

前言：

个人学习笔记，学习资源为B站【狂神说Java】，使用数据库为MySQL，可视化软件为SQLyog。

一、操作数据库

1.1 MySQL基本命令行操作

1.1.1 连接数据库
mysql -u root -proot -- 命令行连接 -p与密码之间没有空格，命令行需要以管理员身份运行
1.1.2 MySQL简单命令行操作
-- 之前使用过的
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost'; -- 修改初始密码
fulush privileges; -- 刷新权限
set password for root@localhost = password('123456'); -- 将用户root的密码改为123456
---------------------------------------------------------------------------------------
-- 所有语句都使用分号结尾
-- 单行注释
/*
	多行注释
*/
show databases; -- 查看全部数据库
use DBname; -- 切换数据库（显示Database changed即为切换成功）
show tables; -- 查看数据库中全部的表
describe tableName; -- 查看表的属性信息
create database newDBname; -- 创建新数据库
exit; -- 退出连接
1.1.3 数据库语言种类
DDL 数据库定义语言
DML 数据库操作语言
DQL 数据库查询语言
DCL 数据库控制语言

1.2 操作数据库语句

操作数据库 > 操作数据库中的表 > 操作数据库表中的数据

MySQL关键字不区分大小写

1.2.1 操作数据库（了解）

  1. 创建数据库

    create database [if not exists] newDBname; -- 中括号中为可选项
/* 
	不加中括号中字段，创建同名数据库会报错，
	加上中括号中字段，创建同名数据库只有警告，但不会创建新的同名数据库。
*/

  2. 删除数据库

    drop database [if exists] DBname; 
/* 
	不加中括号中字段，删除不存在的数据库会报错，
	加上中括号中字段，删除不存在的数据库只有警告，但不会执行删除操作，因为不存在。
*/

  3. 使用数据库

    use DBname; --  切换数据库
use `DBname`; -- 如果数据库名或表名是特殊字符，就需要用``把名字括起来

  4. 查看数据库

    show databases; -- 查看全部数据库
show create database databaseName; -- 查看数据库的定义语句

1.3 列的数据类型

1.3.1 数值

  1. 整数

    类型描述大小
    tinyint十分小的数据1个字节
    smallint较小的数据2个字节
    mediumint中等大小的数据3个字节
    int（常用）标准的整数4个字节
    bigint较大的数据8个字节

  2. 浮点数

    类型描述大小
    float浮点数4个字节
    double（常用）浮点数8个字节
    decimal（金融常用）字符串形式的浮点数∞个字节

decimal为金融类项目上常用的数据类型，因为以字符串形式存储，所以不存在精度不够的问题，使用时再将字符串转换为浮点数。

设置为int(1)时，依然可以存放超过一位的整数，这是因为存储位数不影响最大存储值，而是影响零填充的位数，但字符串设置的最大长度就是最大字符数量。

1.3.2 字符串
类型描述大小
char固定大小的字符串0~255
varchar（常用）可变长的字符串0~65535
tinytext微型文本255个字节
text（常用）文本串，用于大型文章65535个字节

char为固定长度的字符串，假设建表时设置数据类型为char(10)，输入数据为"abcd"，占用长度依旧为10。适合存放手机号、身份证号等长度相对固定的数据。

varchar为可变长度的字符串，引用上述例子，varchar(10)中写入"abcd"占长度为4个字节，适合用于长度不确定的数据，如姓名等。

1.3.3 时间日期
类型描述
dateXXXX-XX-XX
timeH : M : S
datetime（常用）XXXX-XX-XX H : M : S
timestamp（较常用）时间戳（过去某天到现在的毫秒数）
year年份表示
1.3.4 NULL

没有值，未知。

尽量不要使用NULL进行运算，可以但不建议。

1.4 数据库的字段属性（重点）

1.4.1 Unsigned
  • 无符号的整数（只有数据类型为整数时才可以勾选）。
  • 勾选后不能声明为负数。
1.4.2 zerofill
  • 用0填充，不足的位数会用0来填充，比如，类型为int(5)，输入数据为12，填充后数据为00012。
1.4.3 自增
  • 默认为自动在上一条记录的基础上 + 1（只有整数类型可选自增）。
  • 通常用来设置主键。
  • 可以自定义设置自增的起始值和步长（创建表时在“高级”中进行设置）。
1.4.4 非空
  • 勾选非空，填入数据时，若该列没有赋值，就会报错。
1.4.5 默认
  • 设置默认值，如果不给该列赋值，则填入默认值。
1.4.6 拓展 *

（每个表必须存在的字段，未来做项目用，保证规范性和安全性）

  1. 主键。
  2. ‘version’ 乐观锁
  3. is_delete 伪删除
  4. qmt_create 创建时间
  5. qmt_update 修改时间

1.5 表的相关操作

1.5.1 创建和删除表
-- 创建新表
CREATE TABLE [IF NOT EXISTS] `表名`(
	`字段名1` 列类型 [属性] [索引] [注释],
    `字段名2` 列类型 [属性] [索引] [注释],
    `字段名3` 列类型 [属性] [索引] [注释],
    `字段名4` 列类型 [属性] [索引] [注释],
)[表类型] [字符集设置] [注释];

-- auto_increment：自增
-- not null：非空
-- primary key：主键
-- default：默认值
-- comment：说明
-- enging：数据库引擎
-- charset：设置字符集编码，不设置的话会是默认的字符集编码，不支持中文
	-- 可以在my.ini中配置默认编码：character-set-server=utf8
	-- 但最好还是建表时写上charset=utf8

CREATE TABLE [IF NOT EXISTS]`student`(
     `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号', 
     `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', 
     `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码', 
     `sex` CHAR(1) NOT NULL DEFAULT '男' COMMENT '性别',
     `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
     `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
     `email` VARCHAR(20) DEFAULT NULL COMMENT '电子邮箱',
     `number` VARCHAR(11) DEFAULT NULL COMMENT '电话号码',
     PRIMARY KEY(`id`)
 )ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 删除表
DROP TABLE [IF EXISTS] tableName;

注意：

  • 括号必须是英文的()
  • 表的名称和字段尽量使用单引号括起来
  • 字符串使用单引号括起来
  • 所有的语句后面加逗号，最后一个不用加
1.5.2 数据库引擎扩展
MYISAM（早些年使用）INNODB（默认使用）
事务支持不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间大小较小较大，约为前者二倍

常规使用操作

  • MYISAM：节约空间，速度较快
  • INNODB：安全性高，事务的处理，多表多用户操作

所有数据库文件都存在data目录下，一个文件夹代表一个数据库，本质还是文件的存储。

MySQL引擎在物理文件上的区别：

  • InnoDB 在数据库表中只有一个 *.frm 文件，以及上级目录下的 ibdata 文件
  • MYISAM对应文件
    • *.frm 表结构的定义文件
    • *.MYD 数据文件（data）
    • *.MYI 索引文件（index）
1.5.3 表的基本操作

  1. 查看表的定义语句：

    SHOW CREATE TABLE tableName;

  2. 查看表的具体结构：

    DESCRIBE tableName;

  3. 操作表的字段

    -- 修改表名
ALTER TABLE tableName RENAME AS newTableName;
-- 增加新的列
ALTER TABLE tableName ADD newColName attribute(); 
-- 修改表的字段
	-- CHANGE 用来重命名，不能修改表的字段
	-- MODIFY 只能修改字段类型和约束，不能用来重命名
ALTER TABLE tableName CHANGE colName newColName attribute(); -- 重命名
ALTER TABLE tableName MODIFY colName attribute(); -- 修改字段
-- 删除表的字段
ALTER TABLE tableName DROP colName;
1.5.4 注意事项
  • 所有字段名用``括起来；
  • 注释为“-- ”或“/**/”；
  • 所有符号用英文。

二、MySQL数据管理

2.1 外键（了解）

2.1.1 设置外键

  1. 方法一：在创建表的时候增加约束（麻烦，比较复杂）

    -- 创建`grade`表与`student`表，将`grade`表中的`gradeid`设置为`student`表的外键。
CREATE TABLE IF NOT EXISTS `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级号',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS`student`(
     `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号', 
     `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', 
     `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码', 
     `sex` CHAR(1) NOT NULL DEFAULT '男' COMMENT '性别',
     `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
     `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
     `email` VARCHAR(20) DEFAULT NULL COMMENT '电子邮箱',
     `number` VARCHAR(11) DEFAULT NULL COMMENT '电话号码',
     `gradeid` INT(10) NOT NULL COMMENT '年级号',
     PRIMARY KEY(`id`),
     KEY `FK_gradeid` (`gradeid`),
     CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
 )ENGINE=INNODB DEFAULT CHARSET=utf8;

  2. 方法二：创建表后，再添加外键约束

    -- 创建表的时候没有外键
CREATE TABLE IF NOT EXISTS `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级号',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS`student`(
     `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号', 
     `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', 
     `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码', 
     `sex` CHAR(1) NOT NULL DEFAULT '男' COMMENT '性别',
     `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
     `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
     `email` VARCHAR(20) DEFAULT NULL COMMENT '电子邮箱',
     `number` VARCHAR(11) DEFAULT NULL COMMENT '电话号码',
     `gradeid` INT(10) NOT NULL COMMENT '年级号',
     PRIMARY KEY(`id`)
 )ENGINE=INNODB DEFAULT CHARSET=utf8;
 
 -- 创建完成后使用以下语句添加外键
 ALTER TABLE `student` -- 分号结尾，长的语句可以换行写
 ADD CONSTRAINT `FK_grade` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

    以上操作都是物理外键（数据库级别的外键），不建议使用（避免数据库过多造成困扰）。

    2.1.2 补充

  • 删除有外键关系的表，必须要先删除引用别人的表（从表），再删除被引用的表（主表）。

  • 最佳实践

    • 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）；
    • 需要使用多张表时，用程序去实现外键。

2.2 DML（重点）

本章操作使用的数据表：

CREATE TABLE IF NOT EXISTS `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级号',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS`student`(
     `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号', 
     `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', 
     `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码', 
     `sex` CHAR(1) NOT NULL DEFAULT '男' COMMENT '性别',
     `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
     `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
     `email` VARCHAR(20) DEFAULT NULL COMMENT '电子邮箱',
     `number` VARCHAR(11) DEFAULT NULL COMMENT '电话号码',
     `gradeid` INT(10) NOT NULL COMMENT '年级号',
     PRIMARY KEY(`id`)
 )ENGINE=INNODB DEFAULT CHARSET=utf8;
2.2.1 数据库管理语言DML

DML（==D==atabase ==M==anage L anguage）：数据库管理语言

主要包含：

  • insert
  • update
  • delete
2.2.2 添加（insert）
/*  INSERT INTO `tableName` [(`colName1`,`colName2`,`colName3`...)] 
	VALUES ('values1'),('values2'),('values3')...);   */
-- 如果省略字段名(列名)，会按顺序一一匹配value的值，不建议这么用
INSERT INTO `student` VALUES ('123','吼吼','132435','女','2000.02.03',
                              '中国','123@qq.com','13500000000','2');
-- 插入单个字段：
INSERT INTO `grade` (`gradename`) VALUES ('大一');
-- 插入多个字段，同一行写在一个括号里，如下所示
	-- '1','ZhangSan','abcdefg','1'为同行的数据，写在一个括号里
	-- 而('1','ZhangSan','abcdefg','1')和('2','LiSi','111111','2')为不同行的数据
INSERT INTO `student` (`id`,`name`,`pwd`,`gradeid`) 
VALUES ('1','ZhangSan','abcdefg','1'),('2','LiSi','111111','2'),('3','DSB','666999','3');
2.2.3 修改（update）
-- 举例：修改学生名字
UPDATE `student` SET `name`='ASan' WHERE id = 3; -- 将id为3的学生名字改为‘ASan’
-- 如果不加 WHERE 将会修改表格中的全部数据（PS：在公司干了这种事儿赶紧跑路，别犹豫）
UPDATE `student` SET `name`='ASan'; -- 将所有表中所有的名字全部改为‘ASan’

-- 修改同一行的多个属性
UPDATE `student` SET `name`='Heihei',`email`='330@qq.com' WHERE id = 2;

条件：where子句 运算符：等于，大于，或小于······，运算符返回的是布尔值。

操作符含义举例
=等于id = 1
<> 或 !=不等于id==<>==2 或 id != 2
>大于age > 18
<小于age < 40
>=大于等于score >= 60
<=小于等于age <= 25
BETWEEN … AND …闭合区间score BETWEEN 90 AND 100
AND且age > 18 AND sex = ‘女’
OR或id > 10 OR id < 3
-- 通过多个条件修改数据
UPDATE `student` SET `name`='Abcd' WHERE age > 18 AND sex = '女';

注意：

  • 列名必须是数据库存在的列，列名最好用 `` 括起来。
  • 如果不指定条件，会修改所有行的值，不建议这么做，想辞职除外
  • 被修改的内容可以是一个值，也可以是一个变量(例:CURRENT_TIME 当前时间)
  • 多个设置的属性之间，使用英文逗号隔开
2.2.4 删除（delete）
DELETE FROM `tableName` WHERE colName = somevalues;
-- 例：
DELETE FROM `student` WHERE id =1; -- 删除 id=1 的行
DELETE FROM `student`; -- 清空表，但建议用TRUNCATE `tableName`

TRUNCATE 和 DELETE 的区别

  • 相同点：都能删除数据，但不会删除表结构。

  • 不同点

    TRUNCATEDELETE
    重新设置自增列，计数器归零不会重设，计数器不归零
    不会影响事务（后面讲）影响事务

    （PS：使用delete删除，如果引擎是innodb，重启数据库后会重置自增，如果引擎是MyISAM，则从上一个增量继续计数）

2.3 DQL（重点）

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
    [left | right | inner join table_name2]  -- 联合查询
    [WHERE ...]  -- 指定结果需满足的条件
    [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
    [HAVING]  -- 过滤分组的记录必须满足的次要条件
    [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
    [LIMIT {[offset,]row_count | row_countOFFSET offset}];
    --  指定查询的记录从哪条至哪条
    
-- 顺序有严格的语法要求，比如GROUP BY不能写到WHERE的前面
2.3.1 数据库查询语言DQL

DQL（==D==atabase ==Q==uery L anguage）：数据库查询语言

  • 所有查询操作都用select
  • 简单，复杂的查询都能做
  • 数据库最核心的语言（最重要的语言，使用频率最高）
2.3.2 简单查询（select）

select可以操作的对象可以是：文本值，列，NULL，函数，计算表达式，系统变量…

-- 数据库建表代码：见附录1

-- 查询全部学生
SELECT * FROM `student`;
-- 查询指定字段
SELECT `student_no`,`student_name` FROM `student`;
-- 使用别名 AS，可以给字段起别名，也可以给表起别名
SELECT `student_no` AS 学号,`student_name` AS 姓名 FROM `student`; -- 显示的表格中，表头的		                                                             `student_no`和`student_name`                                                                   分别显示为“学号”和“姓名”
-- 函数 CONCAT(a,b)
SELECT CONCAT('姓名：',`student_name`) AS 学生姓名 FROM `student`; -- 表格中显示格式为 “姓名：名字”                                                                    表头为“学生姓名”
-- 查询系统版本
SELECT VERSION();
-- 查询自增步长（变量）
SELECT @@auto_increment_increment;
-- 集体操作，例：给所有同学加一分
SELECT `student_no`,`student_result`+1 FROM`result`;
2.3.3 去重（distinct）

作用：去除 SELECT 查询的结果中重复的数据，只显示一条。

-- 例：想知道哪些同学参加了考试
SELECT DISTINCT `student_no` FROM `result`; -- 从成绩表中查询学生学号，并去重
2.3.4 WHERE条件子句

作用：检索数据中符合条件的值

  1. 逻辑运算符

    运算符语法描述
    AND &&a AND b a && b逻辑与
    OR ||a OR b a || b逻辑或
    NOT !NOT a ! a逻辑非

    尽量使用英文字母

    -- 查询分数在95~100的学生学号及成绩
SELECT `student_no`,`student_result` FROM `result` 
WHERE `student_result`>=95 AND `student_result` <= 100;
-- 以上两行为一句，以分号结尾

  2. 模糊查询

    运算符语法描述
    IS NULLa IS NULL判断a是否为NULL
    IS NOT NULLa IS NOT NULL判断a是否不为空
    BETWEEN … AND …a BETWEEN b AND c判断a是否在b和c之间
    LIKEa LIKE b判断a是否能与b匹配
    INa IN (b,c,d,…)判断a是否包含在b,c,d…中的某一个
    -- IS NULL：查询地址为空的学生
SELECT `student_no`,`student_name`,`address` FROM `student`
WHERE `address` IS NULL OR `address` = '';

-- IS NOT NULL：查询出生日期不为空的学生
SELECT `student_no`,`student_name`,`born_date` FROM `student`
WHERE `born_date` IS NOT NULL AND NOT `born_date` = '';

-- BETWEEN：查询分数在95~100的学生学号及成绩
SELECT `student_no`,`student_result` FROM `result`
WHERE `student_result` BETWEEN 95 AND 100;

-- LIKE：查询名字中有“张”的同学的姓名学号
SELECT `student_no`,`student_name` FROM `student`
WHERE `student_name` LIKE '张%'; -- % 意为张后可以有任意个字符，如 张伟，张益达，张阿巴阿巴，如果要查询名字中有某个字符的同学，可以写成’%字%‘，这样这个字前后就都可以有任意个字符
SELECT `student_no`,`student_name` FROM `student`
WHERE `student_name` LIKE '张_'; -- _ 意为张后面只有一个字符，张伟符合条件，张益达不符合，要查后面有几个字的就加几个下划线，比如'张__'可以查询到张益达，但查不到张伟

-- IN：查询1001，1002号学生的信息
SELECT `student_no`,`student_name` FROM `student`
WHERE `student_no` IN (1001,1002);
2.3.5 联表查询（join）

下图为七种连接查询：

-- 查询所有同学的成绩（学号，姓名，科目编号，分数）：
/*思路
	1.分析需查询的字段来自哪些表（学生表，成绩表）
	2.确定使用了哪种连接查询
	3.确定交叉点，即判断两个表中相同的属性（学号）
*/
SELECT s.`student_no`,`student_name`,`subject_no`,`student_result` -- 两个表都有的字段要明确
FROM `student` AS s -- student 别名 s，student为左表
INNER JOIN `result` AS r -- 联合result表，result 别名 r，result为右表
ON s.`student_no` = r.`student_no`; -- 联合条件为二者相等

-- 查询参加考试的同学：
SELECT s.`student_no`,`student_name`,`subject_no`,`student_result` -- 两个表都有的字段要明确
FROM `student` AS s -- student 别名 s，student为左表
RIGHT JOIN `result` AS r -- 联合result表，result 别名 r，result为右表
ON s.`student_no` = r.`student_no`; -- 联合条件为二者相等
/*只有参加考试的同学在成绩单(右表)中，使用RIGHT JOIN，在同学名单(左)中但不在成绩单(右)中的不显示*/

-- 查询所有同学的成绩（学号，姓名，科目编号，科目名，分数）：
/*涉及三个表：学生表，成绩表，课程表*/
SELECT s.`student_no`,`student_name`,r.`subject_no`,`subject_name`,`student_result`
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.`student_no`=r.`student_no`
LEFT JOIN `subject` AS su
ON r.`subject_no`=su.`subject_no`;

/*（ON的作用是根据条件建立联系，WHERE是筛选条件，先执行ON，再执行WHERE）*/
操作描述
INNER JOIN两个表都匹配，就返回行
LEFT JOIN左表有但右表没有的也会显示，但右表有左表没有的不显示
RIGHT JOIN右表有但左表没有的也会显示，但左表有右表没有的不显示

练习：

-- 1.查询学生姓名及所属年级（如：张三  大一）
SELECT `student_name`,`grade_name`
FROM `student` AS s
LEFT JOIN `grade` AS g
ON s.`grade_id` = g.`grade_id`;

-- 2.查询科目所属年级（如：高数-1  大一）
SELECT `subject_name`,`grade_name`
FROM `subject` AS s
LEFT JOIN `grade` AS g
ON s.`grade_id` = g.`grade_id`;

-- 3.查询参加”高等数学-3“考试的学生的学号，姓名，成绩
SELECT st.`student_no`,`student_name`,`student_result`
FROM `student` AS st
LEFT JOIN `result` AS r
ON st.`student_no` = r.`student_no`
LEFT JOIN `subject` AS  sub
ON r.`subject_no` = sub.`subject_no`
WHERE `subject_name`='高等数学-3';
2.3.6 自连接

（本节数据库建表语句见附录2）

自连接：自己和自己的表连接（核心：一张表拆为两个一样的表）

如图，该表是两张表合成的一张表，通过PID(父ID)进行区分：

该表可根据PID拆分为如下几张表：

  1. 父类

    categoryidcategoryName
    2信息技术
    3软件开发
    5美术设计

  2. 子类

    pidcategoryidcategoryName
    34数据库
    28办公信息
    36web开发
    57PS设计

操作：查询父类和子类的对应关系，根据分析，应得出下面结果

父类子类
信息技术办公信息
软件开发数据库
软件开发web开发
美术设计PS设计

使用如下语句：

-- 查询父子信息
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目' -- 显示结果两列都是`categoryName`
FROM `category` AS a,`category` AS b -- a和b表示的是两个一样的表，类似于方程的重根
WHERE a.`categoryid` = b.`pid`; -- 通过匹配，进行连接，执行后即可得到上表,结果如下

2.3.7 分页（limit）和排序（order by）

  1. 排序：ORDER BY

    -- 升序：ASC(默认)
SELECT s.`subject_no`,`subject_name`,`grade_name`
FROM `subject` AS s
LEFT JOIN `grade` AS g
ON s.`grade_id` = g.`grade_id`
ORDER BY s.`subject_no` ASC; -- 按程序升序排列

-- 降序：DESC
SELECT s.`subject_no`,`subject_name`,`grade_name`
FROM `subject` AS s
LEFT JOIN `grade` AS g
ON s.`grade_id` = g.`grade_id`
ORDER BY s.`subject_no` DESC; -- 按课程号降序排列

  2. 分页：LIMIT

    • 缓解数据库压力
    • 给人好的使用体验
    -- 例：只显示五条数据
SELECT s.`subject_no`,`subject_name`,`grade_name`
FROM `subject` AS s
LEFT JOIN `grade` AS g
ON s.`grade_id` = g.`grade_id`
ORDER BY s.`subject_no` ASC
LIMIT 0,5; -- 0：代表起始位置(0为第一条，1为第二条，以此类推)    5：显示五条数据
/*LIMIT (N-1)*PAGE_SIZE,PAGE_SIZE   第N页的起始位置是(N-1)*PAGE_SIZE
  总页数 = 数据总数/页面大小    向上取整*/

-- 练习：查询“高等数学-3”课程成绩排名前十的学生，并且成绩大于80的学生信息（学号，姓名，课程名称，分数）
SELECT st.`student_no`,`student_name`,`subject_name`,`student_result`
FROM `student` AS st
INNER JOIN `result` AS r
ON st.`student_no` = r.`student_no`
INNER JOIN `subject` AS sub
ON r.`subject_no` = sub.`subject_no`
WHERE `student_result` >= 80 AND `subject_name` = '高等数学-3'
ORDER BY `student_result` DESC
LIMIT 0,10;
2.3.8 子查询

之前学的 WHERE 判断的值都是固定的，但 WHERE 后面也可以嵌套一个查询，即WHERE后的值是通过计算得出的。

-- 查询“高等数学-3”的所有考试结果（学号，课程编号，分数）降序排列
SELECT `student_no`,`subject_no`,`student_result`
FROM `result`
WHERE `subject_no` = (
	SELECT `subject_no` FROM `subject`
	WHERE `subject_name` = '高等数学-3'
)
ORDER BY `student_result` DESC; 

-- 查询分数不小于80分的学生的学号和姓名
SELECT `student_no`,`student_name`
FROM `student`
WHERE `student_no` IN (
	SELECT `student_no` FROM `result`
	WHERE `student_result` >= 80
)

-- 查询“高等数学-3”的考试成绩在80以上的学生学号和姓名
SELECT `student_no`,`student_name` FROM `student`
WHERE `student_no` IN (
	SELECT `student_no` FROM `result` 
	WHERE `student_result` >= 80 AND `subject_no` = (
		SELECT `subject_no` FROM `subject`
		WHERE `subject_name` = '高等数学-3'
	)
);

2.4 MySQL函数

MySQL5.7 帮助文档 功能和方法

2.4.1 常用函数

  • 数学运算

    函数功能
    ABS(x)返回x的绝对值
    CEILING(x)x向上取整
    FLOOR(x)x向下取整
    RAND()获取0-1的随机数
    SIGN(x)判断x的符号，正1零0负-1

  • 字符串函数

    函数功能
    CHAR_LENGTH( ‘str’ )返回 str 的长度
    CONCAT( ‘str1’ , ‘str2’ , … )拼接字符串
    INSERT( ‘str’ , pos , len , ‘newstr’ )将 str 中第 pos 个字符开始的 len 个字符替换为 newstr
    LOWER( ‘str’ )转小写
    UPPER( ‘str’ )转大写
    INSTR( ‘str’ , ‘cstr’ )返回子串 cstr 在 str 第一次出现的位置
    REPLACE( ‘str’ , ‘str1’ , ‘str2’ )将 str 中的子串 str1 替换为 str2
    SUBSTR( ‘str’ , pos , len )截取 str 从 pos 位置开始的 len 个字符
    REVERSE( ‘str’ )反转 str

  • 时间和日期函数

    函数功能
    CURRENT_DATE获取当前日期
    NOW()获取当前的日期时间
    LOCALTIME()获取本地时间
    SYSDATE()获取系统日期时间
    YEAR()获取年份
    MONTH()获取月份
    DAY()获取日
    HOUR()获取小时
    MINUTE()获取分钟
    SECOND()获取秒

  • 系统函数

    函数功能
    USER()获取当前用户
    VERSION获取MySQL版本
2.4.2 聚合函数（真 · 常用）
函数功能
COUNT()计数
SUM()求和
AVG()求平均值
MAX()获取最大值
MIN()获取最小值
-- 查询一共有多少学生
SELECT COUNT(`student_no`) FROM `student`; -- count(字段名)：会忽略所有的NULL值，一般只用主键
SELECT COUNT(*) FROM `student`; -- count(*)：不会忽略NULL值，本质为计算行数
SELECT COUNT(1) FROM `student`; -- count(1)：不会忽略NULL值，本质为计算行数

-- 查询每科的平均分，最高分，最低分
SELECT `subject_name` AS 科目,
AVG(`student_result`) AS 平均分,
MAX(`student_result`) AS 最高分,
MIN(`student_result`) AS 最低分
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.`subject_no` = sub.`subject_no`
GROUP BY r.`subject_no`;

-- 查询平均分大于等于80分的课程名称，平均分，最高分，最低分
SELECT `subject_name` AS 科目,
AVG(`student_result`) AS 平均分,
MAX(`student_result`) AS 最高分,
MIN(`student_result`) AS 最低分
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.`subject_no` = sub.`subject_no`
GROUP BY r.`subject_no` -- 根据课程号进行分组，为了统计平均分，最高分等值
HAVING 平均分 >= 80; -- 分组后再筛选条件不能用where，只能用having

2.5 数据库级别的MD5加密（拓展）

MD5是一种加密算法，增强算法复杂度和不可逆性，但同样的值被MD5加密后的值是一样的，所以MD5破解网站的原理是背后有一个字典，通过对比加密后的值，返回加密前的值，不是真正意义上的破译。

-- 建立测试表
CREATE TABLE `testmd5`(
  `id` INT(4) NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `pwd` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 插入数据
INSERT INTO `testmd5`(`id`,`name`,`pwd`)
VALUES
(1,'张三','123456'),
(2,'李四','123456'),
(3,'王五','123456');
-- 加密
UPDATE `testmd5` SET `pwd`=MD5(`pwd`);
-- 插入时加密
INSERT INTO `testmd5` VALUES ( 4 , '赵六' , MD5('123456'));
-- 如何校验，将用户传递进来的密码，进行MD5加密，然后比对加密后的值
SELECT * FROM `testmd5` WHERE `name`='小明' AND pwd = MD5('123456');

2.6 事务

2.6.1 什么是事务

介绍事务前先看一个例子：

假设 A 有1000元，B 有500元，A 给 B 转账200元：

  1. SQL执行：A 给 B 转账
  2. SQL执行：B 收到 A 的钱

分析：正常情况应该是如下情况

AB
1000 元200 元
给 B 转账 200 元，余额 800 元
收到转账，余额 400 元

但如果转账过程中系统崩溃，可能会出现如下情况，导致财产丢失：

AB
1000 元200 元
给 B 转账 200 元，余额 800 元
系统崩溃系统崩溃
未收到转账，余额 200 元

如上，若不希望财产丢失，则同一组SQL要么都成功，要么都失败。

事务原则（ACID 原则）：原子性，一致性，隔离性，持久性

  1. 原子性(Atomicity)：同一组操作要么都成功，要么都失败。

    如：A 给 B 转账，和 B 收到转账，要么都成功，要么都失败。

  2. 一致性(Consistency)：操作执行前后应保持一致。

    如：A 和 B 的总资产就是 1200 元，不会因为 A 给 B 转账而改变。

  3. 隔离性(Isolation)：多用户同时操作时，不会互相影响

    如：两组用户都在进行转账操作，他们之间不会互相影响

  4. 持久性(Durability)：操作开始时，事务也开始记录，操作结束后，提交事务，若在提交事务前，系统崩溃，重启系统后，恢复到事务开始前的状态，避免过过程中数据意外丢失。

    如：转账开始前，数据状态为 A：1000元 B：200元

    ​ 若中途系统崩溃，事务未提交，重启后系统恢复到 A：1000元 B：200元

    ​ 若转账完成，A：800元 B：400元，此时事务提交，系统崩溃后重启，依旧是A：800元 B：400元

隔离导致的问题

  1. 脏读：一个事务读取了另一个事务未提交的数据。

    如：A：1000元 B：200元 C：500元 A 给 B 转200元，C 给 B 转100元

    ​ 若 A 给 B 转账的过程中，C 也给 B 转账，A 转账后，B 应该有400元，可是 C 转账时读取到的 B 的余额是200元，转账后，A 的事务提交的 B 的余额是400元，C 的事务提交的 B 的余额是300元，而实际上 B 应该有500元。

  2. 不可重复读：在一个事务内读取表中的某一行数据，多次读取的结果不同（结果不一定错误，只是某些场合不对）。

  3. 虚读（幻读）：在一个事务内，读取到了别的事务插入的数据，导致前后提取结果不一样。

    如：A：1000元 B：200元，A 给 B 转账 200 元，转账后，A 有800元，B 有400元，但提交的却是 A 800元、

    ​ B 200元、C 300元，这里的C就是虚读的数据。

2.6.2 测试事务实现转账

执行事务的相关命令

-- MySQL是默认开启事务自动提交的
SET autocommit = 0; -- 关闭自动提交
SET autocommit = 1; -- 开启自动提交（默认）

START TRANSACTION; -- 标记一个事务的开始，表示从这个之后的sql都在同一个事务内
COMMIT; -- 提交：将操作结果持久化
ROLLBACK; -- 回滚：回到事务开启前的样子

-- 以下内容了解
SAVEPOINT pointName; -- 设置一个事务的保存点
ROLLBACK TO pointName; -- 回滚到某个保存点
RELEASE SAVEPOINT pointName; -- 撤销保存点

模拟转账过程

  1. 创建测试用的数据库

    -- 创建shop数据库
CREATE DATABASE `shop` CHARACTER SET utf8 COLLATE utf8_general_ci;
--------------------------------------------------------------------------------------
-- 建表
USE shop;
CREATE TABLE `account`(
	`id` INT(3) NOT NULL AUTO_INCREMENT COMMENT '会员号',
	`name` VARCHAR(30) NOT NULL COMMENT '会员名字',
	`money` DECIMAL(9,2) NOT NULL COMMENT '余额',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
--------------------------------------------------------------------------------------
-- 插入数据 A：1000元，B：200元
INSERT INTO `account`(`name`,`money`) 
VALUES ('A',1000.00),('B',200.00);

  2. 测试：A 给 B 转账 500 元

    SET autocommit = 0; -- 关闭自动提交事务
START TRANSACTION; -- 开启一个事务

UPDATE `account` SET `money` = `money` - 500
WHERE `name`= 'A'; -- A 转出 500 元，此时A：500元，B：200元
UPDATE `account` SET `money` = `money` + 500
WHERE `name`= 'B'; -- B 收到 500 元，此时A：500元，B：700元

COMMIT; -- 提交事务，提交后当前状态被持久化
ROLLBACK; -- 回滚
/*若提交事务前回滚，则回到开启事务之前的状态，但事务仍然开着，提交后事务关闭，事务存在期间的操作被持久化，回滚也无法回到之前的状态*/

SET autocommit = 1; -- 测试结束后，开启自动提交事务

2.7 索引

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构。

提取句子主干，就可以得到索引的本质：索引是数据结构。

2.7.1 索引的分类
  • 主键索引（PRIMARY KEY）
    • 唯一的标识，主键不可重复，只能有一个主键（主键可以由不同的列共同组成）。
  • 唯一索引（UNIQUE KEY）
    • 避免重复的列出现，可以有多个列被标识为唯一索引。
  • 常规索引（KEY/INDEX）
    • 默认的，可以用index或key来设置。
  • 全文索引（FULLTEXT）
    • 在特定的数据库引擎下才有（MyISAM）
2.7.2 索引的使用
  1. 在创建表的时候给字段增加索引
  2. 建表完成后，再给字段增加索引
SHOW INDEX FROM `student`; -- 显示所有的索引信息

ALTER TABLE `student` ADD FULLTEXT INDEX `name`(`student_name`);
-- 增加一个索引：给student表增加了一个全文索引，被设置为全文索引的列是`student_name`，索引名是`name`。

EXPLAIN SELECT * FROM `student`; -- EXPLAIN 分析sql执行状况
2.7.3 百万数据测试索引

  1. 建测试表

    CREATE TABLE app_user (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` VARCHAR(50)  DEFAULT '' COMMENT '用户昵称',
  `email` VARCHAR(50)  NOT NULL COMMENT '用户邮箱',
  `phone` VARCHAR(20)  DEFAULT '' COMMENT '手机号',
  `gender` TINYINT(4)  UNSIGNED DEFAULT '0' COMMENT '性别（0：男  1：女）',
  `password` VARCHAR(100)  NOT NULL COMMENT '密码',
  `age` TINYINT(4)  DEFAULT '0' COMMENT '年龄',
  `create_time` DATETIME  DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

  2. SQL编程实现百万数据插入

    DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
  DECLARE num INT DEFAULT 1000000;
  DECLARE i INT DEFAULT 0;
  WHILE i<num DO
    INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
    VALUES(CONCAT('用户',i),'123345@qq.com',
    CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),
    FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
    SET i = i+1;
  END WHILE;
  RETURN i;
END;

SELECT mock_data();

  3. 开始测试

    查找用户99999：EXPLAIN SELECT * FROM app_user WHERE name = ‘用户599999’; 结果截图如下

    创建索引后再查找

    1. 创建索引

      -- id_表名_字段名  索引名
-- CREATE INDEX 索引名 ON 表名(`字段名`);
CREATE INDEX id_app_user_name ON app_user(`name`);

    2. 使用索引，再运行同样的代码，结果如下

结论：索引在数据量小的时候，用处不大，但大数据的情况下数据十分明显。

2.7.4 索引原则
  • 索引不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表不需要索引
  • 索引一般加载常用来查询的字段上
2.7.5 索引的数据结构

Hash类型的索引

Btree：InnoDB的默认数据结构

阅读：CodingLabs - MySQL索引背后的数据结构及算法原理

2.8 权限管理和备份

2.8.1 用户管理

SQLyog可视化管理用户

SQL命令进行用户管理

用户表：mysql.user

管理用户的本质：对这张表进行增删改查。

-- 创建新用户。
CREATE USER zhangsan IDENTIFIED BY '123456'; -- 123456是密码。
-- 修改密码
SET PASSWORD = PASSWORD('111111'); -- 修改当前用户的密码
SET PASSWORD for zhangsan = PASSWORD('111111'); -- 为指定用户修改密码
-- 重命名
RENAME USER zhangsan TO lisi;
-- 用户授权
GRANT ALL PRIVILEGES ON *.* TO AUser; -- 给一个用户授予除GRANT外的全部的权限，*.*表示全部的库和表
-- 查询权限
SHOW GRANTS FOR AUser; -- 查询普通用户的权限
SHOW GRANTS FOR root@localhost; -- 查看root的权限
-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM AUser; -- 撤销一个用户的全部权限
-- 删除用户
DROP USER Auser;

（PS：尽管给一个用户授予了全部权限，它的权限依然没有root高，原因是没有GRANT的权限，也就是说这个用户不能给其他人授权，如果加上GRANT，权限就和root一样了。）

2.8.2 MySQL备份

为什么要备份：

  • 保证重要的数据不丢失
  • 数据转移

MySQL数据库备份方式

  1. 直接拷贝物理文件

  2. 在可视化工具中手动导出

    • 想要导出的表或库–>右键–>选择备份或导出
      • 导出/备份结构：建库或建表的语句
      • 导出/备份结构：插入数据的语句
    • 使用时直接把导出的文件拖拽到询问框就可以

  3. 命令行导出（mysqldump），命令行必须是cmd，不能是可视化工具的询问框

    # 导出，在命令行写
mysqldump -hlocalhost -uroot -p123456 school student > D:/
# 解释：mysqldump -h主机名 -u用户名 -p密码 数据库名 表名 ... > 路径

#导入，不登陆mysql，直接导入
mysql -uroot -p123456 school < D:/a.sql
# 解释：mysql -u用户名 -p密码 数据库名 < 备份文件的路径

    -- 导入(登录到mysql)：在命令行的mysql视图写
USE databaseName;
SOURCE D:/a.sql
/*SOURCE sql文件的路径*/

三、规范设计数据库

3.1 关于设计数据库

3.1.1 设计数据库的重要性

当数据库复杂的时候，就需要对数据库进行设计。

良好的数据库设计糟糕的数据库设计
节省内存数据冗余，浪费空间
保证数据库完整性插入删除麻烦
方便开发系统异常多，性能差
3.1.2 设计数据库的步骤
  1. 分析需求：收集信息，分析业务和需要处理的数据库的需求
  2. 概要设计：设计E-R图

3.2 三大范式

参考：关系型数据库设计：三大范式的通俗理解

3.2.1 为什么要数据规范化
  1. 避免信息重复
  2. 避免操作时产生异常（无法正确显示信息、丢失有效信息等）
3.2.2 第一范式

原子性：要求数据表的每一列都是不可再分的原子项。

3.2.3 第二范式

前提：满足第一范式

每张表只描述一个关系。

3.2.4 第三范式

前提：满足第一、二范式

任何非主属性不依赖于其他非主属性。

3.2.5 注意

三大范式只是为了规范数据库，不要求数据库一定要严格遵守三大范式，在真实项目中，要兼顾性能等其他问题，这些问题比三大范式更加重要，但只是不要求严格遵守，整体还是要围绕三大范式进行设计。

四、JDBC：用Java操作数据库（重点）

4.1 数据库驱动
应用程序
MySQL驱动
MySQL数据库
Oracle驱动
Oracle数据库

应用程序不能直接连接数据库，中间需要驱动，就像人无法直接看到内存里的东西，有了显卡，人才能在屏幕上看到想看到的东西，数据库也是如此，数据库的驱动由数据库厂商提供，程序会通过驱动与数据库打交道。

4.2 JDBC

4.2.1 JDBC存在的意义

为了简化开发人员对数据库的统一操作，提供了一个JAVA操作数据库的规范，也就是JDBC，这些规范的实现，由各自的厂商去做，对于开发人员来说，只需要掌握JDBC接口的操作即可。
JAVA应用程序
JDBC
MySQL驱动
MySQL数据库
Oracle驱动
Oracle数据库
4.2.2 Java与数据库相关的包

  1. java.sql

  2. javax.sql

  3. 还需要自己导入几个相关的包

    commons-dbcp-1.4.jar

    commons-pool-1.6.jar

    mchange-commons-java-0.2.19.jar

    mysql-connector-java-5.1.47.jar

    c3p0-0.9.5.5.jar

4.2.3 第一个JDBC程序

  1. 在MySQL中新建一个用于学习JDBC的数据库，建表，插入数据

    CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE `users`(
	id INT PRIMARY KEY,
	`NAME` VARCHAR(40),
	`PASSWORD` VARCHAR(40),
	`email` VARCHAR(60),
	`birthday` DATE
);

INSERT INTO `users`(id,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES(1,'zhangsan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04');

  2. 导入数据库驱动

    1. 在项目目录下创建lib文件夹
    2. 将mysql-connector-java-5.1.47.jar复制到lib目录下
    3. 右键lib点击Add as Library…

  3. 编写测试代码

    package com.impropercharacter.lesson01;

import java.sql.*;

/**
 * 我的第一个JDBC程序
 * @author WangQM
 * @date 2022/3/29 21:01
 */
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
    //    1.加载驱动
        Class.forName("com.mysql.jdbc.Driver"); //固定写法

    //    2.用户信息和url
    //    useUnicode=true&characterEncoding=utf8&useSSL=true：中文字符集编码，编码格式utf-8，安全连接
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "123456";

    //    3.连接成功，数据库对象,Connection代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);

    //    4.执行sql的对象 Statement
        Statement statement = connection.createStatement();

    //    5.让 执行sql的对象 去执行sql，并查看返回结果
        String sql = "select * from users;";

        ResultSet resultSet = statement.executeQuery(sql); //返回结果集

        while (resultSet.next()){
            System.out.println("id=" + resultSet.getObject("id"));
            System.out.println("name=" + resultSet.getObject("name"));
            System.out.println("pwd=" + resultSet.getObject("password"));
            System.out.println("email=" + resultSet.getObject("email"));
            System.out.println("birthday=" + resultSet.getObject("birthday") + "\n");
        }

        //    6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }

}

    步骤总结：

    1. 加载驱动

      //1.加载驱动
Class.forName("com.mysql.jdbc.Driver"); //固定写法

    2. 连接数据库 DriverManager

      //    2.用户信息和url
//    useUnicode=true&characterEncoding=utf8&useSSL=true：中文字符集编码，编码格式utf-8，安全连接
//    url = "协议://主机地址:端口号/数据库名？参数1&参数2&参数3..."
//拓展（Oracle数据库写法）：jdbc:oracle:thin:@主机地址:端口号:sid

String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "123456";

//    3.连接成功，数据库对象,Connection代表数据库
Connection connection = DriverManager.getConnection(url, username, password);

    3. 获得执行sql的对象 Statement

      //    4.执行sql的对象 Statement
Statement statement = connection.createStatement();

//还有一个对象是prepareStatement，也可以执行sql语句

statement.executeQuery("SQL语句");  //查询操作返回 ResultSet
statement.execute("SQL语句");  //执行任何SQL，但效率会变低
statement.executeUpdate("SQL语句");  //更新、查询、删除都用这个，返回一个受影响的行数

    4. 获得返回的结果集

      //    5.让 执行sql的对象 去执行sql，并查看返回结果
String sql = "select * from users;";

ResultSet resultSet = statement.executeQuery(sql); //返回结果集，ResultSet是只有查询才有的

while (resultSet.next()){
	System.out.println("id=" + resultSet.getObject("id"));
	System.out.println("name=" + resultSet.getObject("name"));
	System.out.println("pwd=" + resultSet.getObject("password"));
    System.out.println("email=" + resultSet.getObject("email"));
	System.out.println("birthday=" + resultSet.getObject("birthday") + "\n");
    }

      //不知道列类型的时候使用getObject，知道列类型就是用特定的方法，操作更加精确
resultSet.getObject(); 
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDouble();
resultSet.getDate();   //等.....

      // 遍历数据库
resultSet.next(); //移动到下一个
resultSet.beforeFirst(); //移动到最前面
resultSet.afterLast(); //移动到最后面
resultSet.previous(); //移动到前一个
resultSet.absolute(row); //移动到指定行

    5. 释放连接

      //    6.释放连接
resultSet.close();
statement.close();
connection.close();
4.2.4 statement对象详解

jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。

Statement对象的executeUpdate方法，用于向数据库发送增、删、改的SQL语句，executeUpdate执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发送了变化）。

Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。

CRUD操作-create

Statement statement = connection.createStatement();
String sql = "insert into user(...) values(...)";
int num = statement.executeUpdate(sql);
if (num > 0) {
    System.out.println("插入成功~");
}

CRUD操作-delete

Statement statement = connection.createStatement();
String sql = "delete from user where id=1";
int num = statement.executeUpdate(sql);
if (num > 0) {
    System.out.println("删除成功~");
}

CRUD操作-update

Statement statement = connection.createStatement();
String sql = "update user set name='' where name =''";
int num = statement.executeUpdate(sql);
if (num > 0) {
    System.out.println("修改成功~");
}

CRUD操作-read

Statement statement = connection.createStatement();
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
    //根据获取列的数据类型，分别调用resultSet的相应方法映射到java对象中
}

代码实现

  1. 编写工具类

    // 在src目录下新建文件db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

    //写代码的目录下新建utils包，包内新建类JdbcUtils
package com.impropercharacter.lesson02.utils;

import com.sun.org.apache.xerces.internal.dom.PSVIAttrNSImpl;

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

/**
 * @author WangQM
 * @date 2022/3/30 16:07
 */
public class JdbcUtils {

    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static{
        try{
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //1.驱动只需要加载一次
            Class.forName(driver);

        }catch(Exception e){
            e.printStackTrace();
        }
    }

    //获取链接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }


    //释放资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet){

        if(resultSet!=null){
            try{
                resultSet.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }

        if(statement!=null){
            try{
                statement.close();
            }catch(SQLException e){
                e.printStackTrace();
            }
        }

        if(connection!=null){
            try{
                connection.close();
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
        
    }
}

  2. 编写测试增、删、改

    //写代码的目录下，编写测试类 TestInsert
package com.impropercharacter.lesson02;

import com.impropercharacter.lesson02.utils.JdbcUtils;

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

/**
 * @author WangQM
 * @date 2022/3/30 16:26
 */
public class TestInsert {
    public static void main(String[] args) {

        Connection conn = null;
        Statement st = null;
        ResultSet re = null;

        try {
            
            conn = JdbcUtils.getConnection(); //获得数据库连接
            st = conn.createStatement(); //获得SQL的执行对象
            String sql = "insert into users(id,`name`,`password`,`email`,`birthday`)" +
                    "values (4,'ABAB','123456','ABAB@qq.com','2015-1-1')";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("Sucessful!");
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,re);
        }

    }
}


    //写代码的目录下，编写测试类 TestDelete
package com.impropercharacter.lesson02;

import com.impropercharacter.lesson02.utils.JdbcUtils;

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

/**
 * @author WangQM
 * @date 2022/3/30 16:46
 */
public class TestDelete {

    public static void main(String[] args) {

        Connection conn = null;
        Statement st = null;
        ResultSet re = null;

        try {
            conn = JdbcUtils.getConnection(); //获得数据库连接
            st = conn.createStatement(); //获得SQL的执行对象
            String sql = "delete from `users` where `id` = 4";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("Sucessful!");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,re);
        }

    }
}


    //写代码的目录下，编写测试类 TestUpdate
package com.impropercharacter.lesson02;

import com.impropercharacter.lesson02.utils.JdbcUtils;

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

/**
 * @author WangQM
 * @date 2022/3/30 16:48
 */
public class TestUpdate {

    public static void main(String[] args) {

        Connection conn = null;
        Statement st = null;
        ResultSet re = null;

        try {
            conn = JdbcUtils.getConnection(); //获得数据库连接
            st = conn.createStatement(); //获得SQL的执行对象
            String sql = "update users set `name`='ABAB' where `id`=3";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("Sucessful!");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,re);

        }

    }
}

  3. 测试查询

    //写代码的目录下，编写测试类 TestSelect
package com.impropercharacter.lesson02;

import com.impropercharacter.lesson02.utils.JdbcUtils;

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

/**
 * @author WangQM
 * @date 2022/3/30 17:05
 */
public class TestSelect {

    public static void main(String[] args) {

        Connection conn = null;
        Statement st = null;
        ResultSet re = null;

        try {
            conn = JdbcUtils.getConnection(); //获得数据库连接
            st = conn.createStatement(); //获得SQL的执行对象

            String sql = "select * from `users` where `id` = 1";

            re = st.executeQuery(sql);
            if(re.next()){
                System.out.println(re.getString("name"));
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,re);

        }

    }
}

4.3 SQL注入

4.3.1 什么是SQL注入

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

4.3.2 SQL注入实现
package com.impropercharacter.lesson02;

import com.impropercharacter.lesson02.utils.JdbcUtils;

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

/**
 * @author WangQM
 * @date 2022/3/30 19:36
 */
public class SQL注入 {
    public static void main(String[] args) {
        // 正常登录
        // login("ABAB","123456");
        //非正常登录
        login("'or'1=1","'or'1=1");


    }


    public static void login(String username,String password){
        Connection conn = null;
        Statement st = null;
        ResultSet re = null;

        try {
            conn = JdbcUtils.getConnection(); //获得数据库连接
            st = conn.createStatement(); //获得SQL的执行对象

            String sql = "select * from `users` " +
                    "where `name` = '"+username+"' and `password` = '"+password+"'";

            re = st.executeQuery(sql);
            while(re.next()){
                System.out.println(re.getInt("id"));
                System.out.println(re.getString("name"));
                System.out.println(re.getString("password"));
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,re);
        }


    }
}
4.3.3 解决SQL注入：PreparedStatement对象

PreparedStatement 可以防止SQL注入，而且效率更高。

  1. 增

    package com.impropercharacter.lesson03;

import com.impropercharacter.lesson02.utils.JdbcUtils;

import java.sql.*;

import java.util.Date;

/**
 * @author WangQM
 * @date 2022/3/30 20:05
 */
public class TestInsert {

    public static void main(String[] args) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();

            String sql = "insert into users(`id`,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)"; //问好是占位符，效率更高

             preparedStatement = connection.prepareStatement(sql); //预编译SQL，先写，不执行

            //手动赋值
            preparedStatement.setInt(1,4);
            preparedStatement.setString(2,"WOC");
            preparedStatement.setString(3,"123456");
            preparedStatement.setString(4,"WOC@qq.com");
            preparedStatement.setDate(5,new java.sql.Date(new Date().getTime()));
            //preparedStatement.setDate(5, java.sql.Date.valueOf("2002-7-12"));

            int i = preparedStatement.executeUpdate();
            if (i>0) {
                System.out.println("插入成功");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }

}

  2. 删

    package com.impropercharacter.lesson03;

import com.impropercharacter.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

/**
 * @author WangQM
 * @date 2022/3/30 20:47
 */
public class TestDelete {
    public static void main(String[] args) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();

            String sql = "delete from users where id = ?"; //问好是占位符，效率更高

            preparedStatement = connection.prepareStatement(sql); //预编译SQL，先写，不执行

            //手动赋值
            preparedStatement.setInt(1,4);

            int i = preparedStatement.executeUpdate();
            if (i>0) {
                System.out.println("删除成功");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }

}

  3. 改

    package com.impropercharacter.lesson03;

import com.impropercharacter.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

/**
 * @author WangQM
 * @date 2022/3/30 20:50
 */
public class TestUpdate {
    public static void main(String[] args) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();

            String sql = "update users set `name`=? where `id` = ?"; //问好是占位符，效率更高

            preparedStatement = connection.prepareStatement(sql); //预编译SQL，先写，不执行

            //手动赋值
            preparedStatement.setString(1,"NMD");
            preparedStatement.setInt(2,2);

            int i = preparedStatement.executeUpdate();
            if (i>0) {
                System.out.println("修改成功");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }

}

  4. 查

    package com.impropercharacter.lesson03;

import com.impropercharacter.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

/**
 * @author WangQM
 * @date 2022/3/30 20:54
 */
public class TestSelect {

    public static void main(String[] args) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();

            String sql = "select `name`,`password` from `users` where id = ?"; //问好是占位符，效率更高

            preparedStatement = connection.prepareStatement(sql); //预编译SQL，先写，不执行

            //手动赋值
            preparedStatement.setInt(1,2);

            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                System.out.println(resultSet.getString("name"));
                System.out.println(resultSet.getString("password"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }

}

  5. 再次测试SQL注入

    package com.impropercharacter.lesson03;

import com.impropercharacter.lesson02.utils.JdbcUtils;

import java.sql.*;

/**
 * @author WangQM
 * @date 2022/3/30 21:03
 */
public class 再次测试SQL注入 {

    public static void main(String[] args) {
        // 正常登录
        //login("ABAB","123456");
        //非正常登录
        login("'or'1=1","'or'1=1");


    }


    public static void login(String username,String password){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet re = null;

        try {
            conn = JdbcUtils.getConnection(); //获得数据库连接

            String sql = "select * from `users` where `name` = ? and `password` = ?";
            st = conn.prepareStatement(sql); //获得SQL的执行对象

            st.setString(1,username);
            st.setString(2,password);

            re = st.executeQuery();
            while(re.next()){
                System.out.println(re.getInt("id"));
                System.out.println(re.getString("name"));
                System.out.println(re.getString("password"));
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,re);
        }


    }
}

    PreparedStatement防止SQL注入的本质：把传递进来的参数当字符，如果存在转义字符，会被直接转义。

4.4 Idea连接数据库

测试事务

-- 在jdbc数据库下创建account表用于测试事务
create table `account`
(
    `id`    int primary key auto_increment,
    `name`  varchar(40),
    `money` float
);

insert into `account`(`name`, `money`)
values ('A', 1000),
       ('B', 1000),
       ('C', 1000);

package com.impropercharacter.lesson04;

import com.impropercharacter.lesson02.utils.JdbcUtils;

import javax.tools.JavaCompiler;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author WangQM
 * @date 2022/3/31 16:08
 */
public class TestTranscation1 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            //Java中关闭数据库自动提交时会自动开启事务
            connection.setAutoCommit(false);
            String sql1 = "update account set money=money-500 where name ='A'";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();

            int x = 1/0;//用于测试rollback

            String sql2 = "update account set money=money+500 where name ='B'";
            preparedStatement=connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();

            //提交事务
            connection.commit();
            System.out.println("Successfully!");
            connection.setAutoCommit(true);


        } catch (SQLException e) {
            try {
                connection.rollback();//显示定义回滚语句，不写会默认回滚
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

4.5 数据库连接池

常规的jdbc操作数据库中，过程为：数据库连接----执行完毕----释放，在从连接到释放的过程十分浪费系统资源。

池化技术：准备一些预先的资源，直接连接预先准备好的资源。也就是事先准备好数据库连接，让程序可以直接拿过来用，用完放回连接池，不需要自己创建连接再释放。

最小连接数：一般等于常用连接数。

最大连接数：业务最高承载上限，超出上限后需要排队。

等待超时：超过一定时间，自动断开。

编写连接池：实现一个接口 DataSource

开源数据源实现

DBCP

C3P0

Druid：阿里巴巴

使用了这些数据库连接池后，在项目开发中就不需要编写连接数据库的代码了。

4.5.1 DBCP

  1. 需要导入的包：commons-dbcp-1.4.jar commons-pool-1.6.jar

    //在src目录下创建新的资源文件dbcpconfig.properties
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456

#初始化连接
initialSize=10

#最大连接数量
maxActive=50

#最大空闲连接
maxIdle=20

#最小空闲连接
minIdle=5

#超时等待时间以毫秒为单位 6000毫秒/1000等于60秒
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：【属性名=property;】
#注意：user 与 password 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_COMMITTED

  2. 写工具类

    //写代码的目录下新建utils包，包内新建类JdbcUtils_DBCP
package com.impropercharacter.lesson05.utils;

import com.impropercharacter.lesson02.utils.JdbcUtils;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @author WangQM
 * @date 2022/3/31 22:06
 */
public class JdbcUtils_DBCP {

    private static DataSource dataSource = null;

    static{
        try{
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);

            //创建数据源 工厂模式--->创建对象
            dataSource = BasicDataSourceFactory.createDataSource(properties);

        }catch(Exception e){
            e.printStackTrace();
        }
    }

        public static Connection getConnection() throws SQLException {
            return dataSource.getConnection(); //从数据源中获取连接
        }


    //释放资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet){

        if(resultSet!=null){
            try{
                resultSet.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }

        if(statement!=null){
            try{
                statement.close();
            }catch(SQLException e){
                e.printStackTrace();
            }
        }

        if(connection!=null){
            try{
                connection.close();
            }catch(SQLException e){
                e.printStackTrace();
            }
        }

    }

}

  3. 写测试文件

    //这里以插入为例，测试DBCP
package com.impropercharacter.lesson05;

import com.impropercharacter.lesson02.utils.JdbcUtils;
import com.impropercharacter.lesson05.utils.JdbcUtils_DBCP;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

/**
 * @author WangQM
 * @date 2022/3/31 22:20
 */
public class TestDBCP {

    public static void main(String[] args) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils_DBCP.getConnection();

            String sql = "insert into users(`id`,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)"; //问好是占位符，效率更高

            preparedStatement = connection.prepareStatement(sql); //预编译SQL，先写，不执行

            //手动赋值
            preparedStatement.setInt(1,5);
            preparedStatement.setString(2,"WORLD");
            preparedStatement.setString(3,"123456");
            preparedStatement.setString(4,"WORLD@qq.com");
            preparedStatement.setDate(5,new java.sql.Date(new Date().getTime()));
            //preparedStatement.setDate(5, java.sql.Date.valueOf("2002-7-12"));

            int i = preparedStatement.executeUpdate();
            if (i>0) {
                System.out.println("插入成功");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils_DBCP.release(connection,preparedStatement,resultSet);
        }
    }
}
4.5.2 C3P0

需导入的包：c3p0-0.9.5.5.jar mchange-commons-java-0.2.19.jar

  1. 配置文件

    //在src目录下创建新的资源文件c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!--
    c3p0的缺省（默认）配置
    如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource();这样写就表示使用的是c3p0的缺省（默认）
    -->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <property name="acquiredIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>


    <!--
    c3p0的命名配置
    如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");这样写就表示使用的是name是MySQL
    -->
    <name-config name="MySQL">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">123456</property>
        
        <property name="acquiredIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </name-config>
</c3p0-config>

  2. 工具类

    //写代码的目录下新建utils包，包内新建类JdbcUtils_C3P0
package com.impropercharacter.lesson06;

import com.impropercharacter.lesson05.utils.JdbcUtils_DBCP;
import com.impropercharacter.lesson06.utils.JdbcUtils_C3P0;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

/**
 * @author WangQM
 * @date 2022/3/31 23:32
 */
public class TestC3P0 {
    public static void main(String[] args) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils_C3P0.getConnection(); //原来是自己实现的，现在用别人实现的

            String sql = "insert into users(`id`,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)"; //问好是占位符，效率更高

            preparedStatement = connection.prepareStatement(sql); //预编译SQL，先写，不执行

            //手动赋值
            preparedStatement.setInt(1,6);
            preparedStatement.setString(2,"WORLD");
            preparedStatement.setString(3,"123456");
            preparedStatement.setString(4,"986554@qq.com");
            preparedStatement.setDate(5,new java.sql.Date(new Date().getTime()));
            //preparedStatement.setDate(5, java.sql.Date.valueOf("2002-7-12"));

            int i = preparedStatement.executeUpdate();
            if (i>0) {
                System.out.println("插入成功");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils_DBCP.release(connection,preparedStatement,resultSet);
        }
    }

}

  3. 测试类

    //这里以插入为例，测试C3P0
package com.impropercharacter.lesson06;

import com.impropercharacter.lesson05.utils.JdbcUtils_DBCP;
import com.impropercharacter.lesson06.utils.JdbcUtils_C3P0;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

/**
 * @author WangQM
 * @date 2022/3/31 23:32
 */
public class TestC3P0 {
    public static void main(String[] args) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils_C3P0.getConnection(); //原来是自己实现的，现在用别人实现的

            String sql = "insert into users(`id`,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)"; //问好是占位符，效率更高

            preparedStatement = connection.prepareStatement(sql); //预编译SQL，先写，不执行

            //手动赋值
            preparedStatement.setInt(1,6);
            preparedStatement.setString(2,"EN");
            preparedStatement.setString(3,"123456");
            preparedStatement.setString(4,"EN@qq.com");
            preparedStatement.setDate(5,new java.sql.Date(new Date().getTime()));
            //preparedStatement.setDate(5, java.sql.Date.valueOf("2002-7-12"));

            int i = preparedStatement.executeUpdate();
            if (i>0) {
                System.out.println("插入成功");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils_C3P0.release(connection,preparedStatement,resultSet);
        }
    }

}
4.5.3 总结

无论使用什么数据源，DataSource的接口不会改变，方法就不会变。

附录：

附录1

DROP DATABASE IF EXISTS `school`;
-- 创建一个school数据库
CREATE DATABASE IF NOT EXISTS `school`;
-- 使用school数据库
USE `school`;
-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
    `student_no` INT(4) NOT NULL COMMENT '学号',
    `login_pwd` VARCHAR(20) DEFAULT NULL,
    `student_name` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
    `sex` TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
    `grade_id` INT(11) DEFAULT NULL COMMENT '年级编号',
    `phone` VARCHAR(50) NOT NULL COMMENT '联系电话',
    `address` VARCHAR(255) NOT NULL COMMENT '地址',
    `born_date` DATETIME DEFAULT NULL COMMENT '出生时间',
    `email` VARCHAR (50) NOT NULL COMMENT '邮箱账号',
    `identity_card` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
    PRIMARY KEY (`student_no`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
  `grade_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
  `grade_name` VARCHAR(50) NOT NULL COMMENT '年级名称',
   PRIMARY KEY (`grade_id`)
) ENGINE=INNODB DEFAULT CHARSET = utf8;

-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
  `subject_no`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
  `subject_name` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
  `class_hour` INT(4) DEFAULT NULL COMMENT '学时',
  `grade_id` INT(4) DEFAULT NULL COMMENT '年级编号',
   PRIMARY KEY (`subject_no`)
)ENGINE = INNODB  DEFAULT CHARSET = utf8;

-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
  `student_no` INT(4) NOT NULL COMMENT '学号',
  `subject_no` INT(4) NOT NULL COMMENT '课程编号',
  `exam_date` DATETIME NOT NULL COMMENT '考试日期',
  `student_result` INT (4) NOT NULL COMMENT '考试成绩'
  )ENGINE = INNODB DEFAULT CHARSET = utf8;
  
-- 插入学生数据 其余自行添加 这里只添加了2行
INSERT INTO `student` (`student_no`,`login_pwd`,`student_name`,`sex`,`grade_id`,`phone`,`address`,`born_date`,`email`,`identity_card`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');

-- 插入年级数据
INSERT INTO `grade` (`grade_id`,`grade_name`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');

-- 插入科目数据
INSERT INTO `subject`(`subject_no`,`subject_name`,`class_hour`,`grade_id`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);

-- 插入成绩数据  这里仅插入了一组，其余自行添加
INSERT INTO `result`(`student_no`,`subject_no`,`exam_date`,`student_result`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);

附录2

CREATE TABLE category (
	`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题ID',
	`pid` INT(10) NOT NULL COMMENT '父ID',
	`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
	PRIMARY KEY(categoryid)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET = utf8;

INSERT INTO category(categoryid,pid,categoryName)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','PS技术'),
('8','2','办公信息');

,
PRIMARY KEY (student_no)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

– 创建年级表
DROP TABLE IF EXISTS grade;
CREATE TABLE grade(
grade_id INT(11) NOT NULL AUTO_INCREMENT COMMENT ‘年级编号’,
grade_name VARCHAR(50) NOT NULL COMMENT ‘年级名称’,
PRIMARY KEY (grade_id)
) ENGINE=INNODB DEFAULT CHARSET = utf8;

– 创建科目表
DROP TABLE IF EXISTS subject;
CREATE TABLE subject(
subject_noINT(11) NOT NULL AUTO_INCREMENT COMMENT ‘课程编号’,
subject_name VARCHAR(50) DEFAULT NULL COMMENT ‘课程名称’,
class_hour INT(4) DEFAULT NULL COMMENT ‘学时’,
grade_id INT(4) DEFAULT NULL COMMENT ‘年级编号’,
PRIMARY KEY (subject_no)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

– 创建成绩表
DROP TABLE IF EXISTS result;
CREATE TABLE result(
student_no INT(4) NOT NULL COMMENT ‘学号’,
subject_no INT(4) NOT NULL COMMENT ‘课程编号’,
exam_date DATETIME NOT NULL COMMENT ‘考试日期’,
student_result INT (4) NOT NULL COMMENT ‘考试成绩’
)ENGINE = INNODB DEFAULT CHARSET = utf8;

– 插入学生数据 其余自行添加 这里只添加了2行
INSERT INTO student (student_no,login_pwd,student_name,sex,grade_id,phone,address,born_date,email,identity_card)
VALUES
(1000,‘123456’,‘张伟’,0,2,‘13800001234’,‘北京朝阳’,‘1980-1-1’,‘text123@qq.com’,‘123456198001011234’),
(1001,‘123456’,‘赵强’,1,3,‘13800002222’,‘广东深圳’,‘1990-1-1’,‘text111@qq.com’,‘123456199001011233’);

– 插入年级数据
INSERT INTO grade (grade_id,grade_name) VALUES(1,‘大一’),(2,‘大二’),(3,‘大三’),(4,‘大四’),(5,‘预科班’);

– 插入科目数据
INSERT INTO subject(subject_no,subject_name,class_hour,grade_id)VALUES
(1,‘高等数学-1’,110,1),
(2,‘高等数学-2’,110,2),
(3,‘高等数学-3’,100,3),
(4,‘高等数学-4’,130,4),
(5,‘C语言-1’,110,1),
(6,‘C语言-2’,110,2),
(7,‘C语言-3’,100,3),
(8,‘C语言-4’,130,4),
(9,‘Java程序设计-1’,110,1),
(10,‘Java程序设计-2’,110,2),
(11,‘Java程序设计-3’,100,3),
(12,‘Java程序设计-4’,130,4),
(13,‘数据库结构-1’,110,1),
(14,‘数据库结构-2’,110,2),
(15,‘数据库结构-3’,100,3),
(16,‘数据库结构-4’,130,4),
(17,‘C#基础’,130,1);

– 插入成绩数据 这里仅插入了一组，其余自行添加
INSERT INTO result(student_no,subject_no,exam_date,student_result)
VALUES
(1000,1,‘2013-11-11 16:00:00’,85),
(1000,2,‘2013-11-12 16:00:00’,70),
(1000,3,‘2013-11-11 09:00:00’,68),
(1000,4,‘2013-11-13 16:00:00’,98),
(1000,5,‘2013-11-14 16:00:00’,58);


### 附录2

```sql
CREATE TABLE category (
	`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题ID',
	`pid` INT(10) NOT NULL COMMENT '父ID',
	`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
	PRIMARY KEY(categoryid)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET = utf8;

INSERT INTO category(categoryid,pid,categoryName)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','PS技术'),
('8','2','办公信息');
键来ᝰ
关注
专栏目录
Mysql学习笔记（全）
m0_60264772的博客
12-07 2万+
目录 MyISAM和InnoDB 性能下降SQL慢的原因： Mysql执行顺序 SQLJoin 索引 索引的优劣 索引分类 mysql索引结构 那些情况建索引 哪些情况不要建索引 性能分析 Extra 索引优化 索引口诀 索引案例 Order By 索引优化 exsites Mysql慢查询日志命令 函数操作批量插入数据 show Profile分析sql 全局查询日志 Mysql锁 锁命令 表锁：读锁 表锁：写锁 行锁 事务 行锁测试 间隙锁 行
00_Oracle mysql学习笔记整理
liangwenhao1108的博客
06-14 426
目录1.为什么要学习数据库2.Oracle数据库2.1 登录2.2 简单查询：2.3 单条件查询：ed 命令2.4 多条件查询：2.5 模糊查询2.6 排序2.7 字符函数：单行函数2.8 数值函数2.9 转换函数2.10 日期函数2.11 通用函数2.12 条件语句 case、decode2.13 统计函数：多行计算函数2.14 分组统计2.15 多表查询1. 多表查询2. 表的连接：等值连接和不等值连接3. 表的连接：外连接2.16 自连接2.17 子查询2.18 集合运算2.19 新增数据：2.20
MySQL,Oracle系统学习,以及SQL语言-----数据库篇学习笔记
weixin_38717987的博客
02-26 921
MySQL和Oracle系统学习 开篇立意: 自我系统性学习数据库知识,包含基本SQL语句学习,MySQL.Oracle的学习,以及区别,后续会增加优化篇,水平有限,错误之处请批评.
关于MySQL的学习笔记（超详细超完整）
qq_46053369的博客
09-10 4142
SQL 1.什么是数据库? ​ 1)全称: DataBase 简称:DB ​ 2)数据库其实就是按照一定的格式进行存储数据的一些文件或者文件集合 ​ 理解:存储数据的仓库,而这些数据在实时存储的过程中是有特定格式的. 2.什么是数据库管理系统? ​ 1)全称: DataBaseManagement System 简称:DBMS 2).数据库管理系统专用来管理数据库中数据 可以对数据库中的数据进行增删改查操作 3)数据库管理系统类型: (1)关系型数据库 a.以行和列的方式进行存储 b.常见的关系型数据库:
尚硅谷MySQL学习笔记
热门推荐
IAMLSL的博客
02-11 2万+
MySQL笔记目录一、MySQL数据库基础篇大纲1. 数据库概述与MySQL安装篇第01章：数据库概述第02章：MySQL环境搭建2. SQL之SELECT使用篇第03章：基本的SELECT语句第04章：运算符第05章：排序与分页第06章：多表查询第07章：单行函数第08章：聚合函数第09章：子查询3. SQL之DDL、DML、DCL使用篇第10章：创建和管理表第11章：数据处理之增删改第12章：MySQL数据类型精讲第13章：约束4. 其它数据库对象篇第14章：视图第15章：存储过程与函数第16章：变量、
mysql 学习笔记
javaTalk
01-23 3474
mysql 学习记录，包含日志、事务、索引、锁等功能。
MYSQL学习笔记
weixin_44134589的博客
04-04 1298
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档 MYSQL 学习笔记（一）一、掌握概念库的增删改查1、系统数据库2、创建数据库2.1.语法2.2.数据库的命名规则3、数据库的相关操作二、存储引擎1、什么是存储引擎2、mysql 支持的存储引擎3、指定表的类型/存储引擎三、表的增删改查1、表的介绍2、创建表3、查看表的结构4、修改表的结构4.1.修改表名4.2.增加字段4.3.删除字段4.4.修改字段5、复制表 一、掌握概念库的增删改查 1、系统数据库 information_schem.
Mysql学习笔记
huangbaokang的博客
07-22 1131
查看mysql默认的存储引擎 show variable like 'storage_engine%'; 查看表的详情 show create table table_name \G mysql修改表名 alter table old_table_name rename [to] new_table_name mysql修改字段名字 alter table table_name change...
MySQL学习笔记8——游标
qq_44778824的博客
04-24 2175
本节学习了游标的使用方法，包括在存储过程中使用游标的4个步骤，分别是定义游标、打开游标、读取游标数据和关闭游标。除此之外，还介绍了经常与游标结合使用的流程控制语句，包括循环语句LOOP、WHILE和REPEAT；条件判断语句IF和CASE；还有跳转语句LEAVE和ITERATE。
MySQL学习笔记.zip
02-23
这份“MySQL学习笔记”将引导我们深入理解其核心概念和实用技能。 一、MySQL简介 MySQL是一个开源、免费的数据库系统，由瑞典的MySQL AB公司开发，后被Oracle公司收购。它的设计目标是速度、可移植性和简洁性，支持...
mysql学习笔记
12-16
这份“mysql学习笔记”涵盖了MySQL的基础知识，包括但不限于数据库的概念、SQL语言的使用、以及更深入的多表操作。 在“day08入门笔记”中，你可能会学到以下内容： 1. **数据库基本概念**：了解什么是数据库，...
数据库 MySQL 学习笔记高级篇.md
09-01
数据库 MySQL 学习笔记高级篇.md
实用SQL小总结
UntifA的博客
09-29 1366
SQL记录
MySQL之复合查询与内外连接
zdlynj的博客
09-30 1231
前面我们讲解的mysql表的查询都是对一张表进行查询，即数据的查询都是在某一时刻对一个表进行操作的。而在实际开发中，我们往往还需要对多个表同时进行查询。我们这里使用的测试表，为雇员信息表(来自Oracle 9i的经典测试表)：EMP员工表，DEPT部门表，SALGRADE工资等级表。
点评项目-2-完善注册登录业务
m0_75138009的博客
09-29 591
需要处理的业务：在网页发送 /user/code 路径下的 post 请求后，我们需要检验手机号后，向合法的手机号发送一个随机生成的电话号。
Spring的热部署工具和数据库密码加盐操作
2301_77053417的博客
09-28 820
加密过程：用户输入的密码+盐值 --> 字符串1，然后使用md5对字符串1加密得到字符串2，然后字符串2+盐值 --> 字符串3，这个字符串3就是存储在数据库中的密文。背景：如果我们的密码明文存储在数据库中，要是被窃取了是非常的危险的，所以我们在数据库中存储的密码不能是明文的，需要进行加密后存储，也就是数据库中存储的是密文。解密过程：取出数据库中的盐值和用户输入的密码得到字符串1，使用md5对字符串1加密得到字符串2，然后验证字符串2和数据库中的密文即可。做好以上的步骤，你项目中的热部署就已经做好了。
World of Warcraft [CLASSIC][80][Grandel] Call to Arms: Victory in Wintergrasp
最新发布
qq 117791303
10-02 290
Wintergrasp 冬拥湖 120 VS 120
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值