Mysql基础

Mysql

1.基本操作

命令行连接

mysql -uroot -proot  -- 连接数据库

UPDATE mysql.`user` SET authentication_string=`Password`('root') WHERE user='root' AND `Host`= 'localhost';    -- 修改密码
flush privileges；


show databses;	-- 查看所有的数据
describe book.blog;	-- 显示数据库中的所有信息
create database ks;	-- 创建数据库

DML （数据库操作语言）

DDL (数据库定义语言)

DQL （数据库查询语言）

DCL （数据库控制语言）

2.操作数据库

2.1操作数据库

1.创建数据库

CREATE DATABASE [IF NOT EXISTS] ks;	-- 大括号表示可选

2.删除数据库

drop DATABASE if EXISTS ks;

3.使用数据库

use `ks`;

2.2数据库的列类型

2.3创建数据库表

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 '123' COMMENT '密码',
	`sex` VARCHAR(2) NOT 	NULL DEFAULT '男' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- ENGINE=INNODB 引擎

SHOW CREATE DATABASE ks; -- 显示创建数据库语句
SHOW CREATE TABLE student; -- 显示创建表的语句
DESC student; -- 显示表结构

数据表的类型（MYISAM和INNODB）

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大

常规使用操作：

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

在物理空间存在的位置

• INNODB 在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
• MYISAM 对应文件
  • *.frm 表结构的定义文件
  • *.MYD 数据文件
  • *.MYI 索引文件

2.4修改删除表

-- 修改表
ALTER TABLE 旧表名 RENAME AS 新表名;
-- 增加表的字段
ALTER TABLE 表名 ADD	字段 INT(11)
-- 修改表的字段
ALTER TABLE 表名 MODIFY 字段 VARCHAR(11) -- 修改约束
ALTER TABLE 表名 CHANGE 旧字段 新字段 INT(11) -- 修改表名
-- 删除表的字段
ALTER TABLE 表名 DROP 字段
-- 删除表
DROP TABLE IF EXISTS 表名

3.MYSQL数据管理

外键约束

CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`name` VARCHAR(20) NOT NULL COMMENT '年级',
	PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 添加外键约束
ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`grade`)
REFERENCES `grade`(`gradeid`);

-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(外键的列) REFERENCES 表（字段）;

3.1 DML

insert

-- INSERT
-- INSERT INTO 表名([字段1，字段2..]) VALUES ('值1','值2'...)
INSERT INTO `grade`(`name`) VALUES ('大一');
-- 插入多个值
INSERT INTO `grade`(`name`) VALUES 
('大二'),('大三');

注意点:每个值要和字段一一对应，值的每一个括号就是一个对象

update

-- 语法
-- UPDATE 表名	SET column_name=value where [条件]

-- 修改学员的名字
UPDATE `student` SET `name`='小张' WHERE id=1; 
-- 修改多个属性
update `student` SET `name`='小李',`address`='北京' WHERE id=2;

运算符

delete

语法：delete from 表名 [where 条件]

-- 删除数据
DELETE FROM `student` WHERE id = 3;mysql

truncate(完全清空一个数据库表，表的结构和索引约束不会变)

-- 清空student表
truncate 表名

delete和truncate的区别

• 相同点：都能删除数据，都不会删除表的结构
• 不同点
  • truncate 重新设置自增列，计数器会归零
  • truncate 不会影响事务
  • delete删除问题，重启数据库现象
    • INNODB引擎 自增列会重1开始（存在内存当中的，断电即失）
    • MYISAM引擎 继续从上一个值增量开始（存在文件中的，不会丢失）

3.2 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}];
   -- 指定查询的记录从哪条至哪条

简单查询

-- 查询全部学生
SELECT * FROM student;

-- 查询指定字段
SELECT `sname`,`sage` FROM student;

-- 别名
SELECT `sname` AS 姓名,`sage`as 性别 FROM student as 学生表

-- 函数 concat(a,b)  字符串拼接
SELECT CONCAT('姓名:',sname)AS 名字 FROM student

语法：select 字段名... from 表

去重

查询那些同学参加了考试

-- 查询全部成绩
SELECT * FROM sc;
-- 查询那些同学参加了考试
SELECT `sno` FROM sc;
-- 去重
SELECT DISTINCT `sno` FROM sc;·

数据库的列（表达式）

	SELECT VERSION(); -- 查询系统版本
	SELECT 100*9 AS 结果;	-- 计算
	SELECT @@auto_increment_increment -- 查询自增步长
	
	-- 学生考试成绩+1
	SELECT `sno`,`score`+1 as '提分后的成绩' FROM sc;

数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量…

select 表达式 from 表

where 条件子句

	-- 查询考试成绩在70-90之间
	SELECT `sno`,`score` FROM sc WHERE `score`>=70 AND `score`<=90	
	-- 模糊查询
	SELECT `sno`,`score` FROM sc WHERE score BETWEEN 70 AND 90	
	-- 除了s001学生的成绩
	SELECT sno,score FROM sc
	WHERE NOT sno='s001' AND NOT sno='s002'

模糊查询

	-- 模糊查询
	-- LIKE %(代表0-任意字符)  _(一个字符)
	-- 查询名字中c开头的同学
	SELECT `sno`,`sname` FROM student 
	WHERE sname LIKE 'c%'
	-- 查询名字c开头的同学且名字只有2个字
	SELECT `sno`,`sname` FROM student
	WHERE sname LIKE 'c__'
	-- 查询名字中含有l的
	SELECT `sno`,`sname`FROM student
	WHERE sname LIKE '%l%'
	
	-- IN(具体的值)
	-- 查询s004，s005学员
	SELECT `sno`,`sname` FROM student
	WHERE sno IN('s004','s005','s006')
	
	-- NULL   NOT NULL
	-- 查询性别为空的
	SELECT `sno`,`sname` FROM student
	WHERE ssex is NULL

联表查询

	/*思路
	1.分析需求，分析查询的字段来自那些表
	2.确定使用那种连接查询
	确定交叉点
	判断条件
	*/
	
	-- 查询参加考试的同学(学号，姓名，学科，分数)
	SELECT s.sno,sname,cno,score
	FROM student AS s
	INNER JOIN sc
	WHERE s.sno=sc.sno
	
	-- right join
	SELECT s.sno,sname,cno,score
	FROM student s
	RIGHT JOIN sc
	ON s.sno = sc.sno
	
		-- left join
	SELECT s.sno,sname,cno,score
	FROM student s
	LEFT JOIN sc
	ON s.sno = sc.sno

操作	描述
inner join	如果表中至少有一个匹配，就返回行
left join	即使右表中没有匹配，也会从左表中返回所有的值
right join	即使左表中没有匹配，也会从右表中返回所有的值

• join on（判断条件） 连接查询
• where 等值查询

	-- 查询参加考试同学的信息（学号，姓名，科目，分数）
	SELECT s.sno,sname,cname,score
	FROM student s
	RIGHT JOIN sc
	ON s.sno=sc.sno
	INNER JOIN course c
	ON sc.cno=c.cno

自连接

自己和自己进行连接，核心：将一张表拆分为两张一模一样的表

-- 自连接
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','java')

-- 查询父子对应关系
SELECT a.`categoryName` as '父',b.`categoryName` as '子'
FROM `category` as a,`category` as b
WHERE a.categoryid=b.pid

分页和排序

排序

-- 查询j2se考试同学的信息（学号，姓名，科目，分数）
-- 根据结果进行排序 ORDER BY 通过那个字段排序
-- DESC 降序 ASC 升序
SELECT s.sno,sname,cname,score
FROM student s
RIGHT JOIN sc
ON s.sno=sc.sno
INNER JOIN course c
ON sc.cno =c.cno
WHERE cname='J2SE'
ORDER BY score DESC

分页

-- 查询学生信息并分页，每页5条
-- LIMIT 起始值，页面大小
SELECT * FROM student LIMIT 0,5

页面计算
【pageSize:页面大小】
【(n-1)*pasgeSize：起始页】
【n:当前页】
【数据总数/页面大小=总页数】

参考链接：查询

3.3 MYSQL函数

常用函数

聚合函数

count查询表的记录

SELECT COUNT(sname) FROM student; -- count(字段)，会忽略所有的null值
SELECT COUNT(*) FROM student; -- count(*),不会忽略null值
SELECT COUNT(1) FROM student; -- count(1),不会忽略null值

-- 查询不同课程的平均分，最高分，最低分,平均分大于70
-- 核心：课程分组
SELECT cname,AVG(`score`),MAX(`score`),MIN(`score`)
FROM sc
LEFT JOIN course c
ON sc.cno=c.cno
GROUP BY sc.cno   -- 分组字段
HAVING avg>70

注意点：

• 使用group by遇见的bug：Expression #1 of SELECT list is not in GROUP BY clause and contains

• 原因：因为当因为mysql版本大于5.7时默认的模式是ONLY_FULL_GROUP_BY

• 解决方案：

  • 方案一：重新设置sql_mode，删除ONLY_FULL_GROUP_BY

  • set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
    
    

  • 方案二：修改my.ini文件，在最后添加

  • sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    

参考链接：(6条消息) MySQL报错“Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre”解决方法_lucky多多的博客-CSDN博客

手动安装mysql不存在my.ini配置文件的解决方案：

• 删除MySql服务：sc delete MySql（以管理员的身份打开cmd），为配置环境需要到mysql下的bin文件夹打开
• 新建my.ini

# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\exploitation_Tool\Mysql\mysql-8.0.29-winx64
# 设置mysql数据库的数据的存放目录，在安装mysql-5.7.30-winx64.zip版本的时候，此配置不可添加，否则mysql将无法启动。
datadir=D:\exploitation_Tool\Mysql\mysql-8.0.29-winx64\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
# 关闭ssl
skip_ssl
# 配置时区
default-time_zone='+8:00'
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION


注意：
1. basedir 和 datadir 根据实际MySql安装的位置进行修改
2. 每个版本的mysql的my.ini文件是不相同的，以上的文件是mysql8的

• 重新生成data文件夹，如若存在，需将之前的文件夹删除，记得备份

• mysqld --initialize-insecure --user=mysql  生成新的data文件夹
  

• 重新安装mysql服务，同时绑定my.ini配置文件

• mysqld --install "MySql" --defaults-file="my.ini文件的位置" 
  

• 重启服务设置密码

• net start mysql
  mysql -u root -p
  回车
  ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';
  update mysql.user set authentication_string=password("你的密码") where user="root";
  

参考链接：(6条消息) 【详记MySql问题】安装之后没有my.ini配置文件怎么办_my-default.ini下载_Acheng1011的博客-CSDN博客

会遇见的问题：

• MySQL 启动报 ：“发生系统错误 193. *** 不是有效的Win32 应用程序”解决方法

  • 解决方案：使用mysqld --install命令，找到mysqld文件，删除

• mysql启动失败

  • 解决方案：查看当前端口是否被占用，同时也检查一下mysql服务是否已经开启

  • netstat -aon|findstr "3306"  查看端口是否被占用 	
    tasklist|findstr "PID"  查看当前端口是被那个程序占用
    

  • 如若当前端口未被占用，检查my.ini文件是否存在问题，再次重启

4.事务

事务原则:ACID 原子性、一致性、隔离性、持久性

• 原子性：要么同时成功，要么同时失败，不能只发生一个
• 一致性：最终一致性（最后的总数一定不变）
• 持久性（事务一旦提交就不可逆）：事务没提交就恢复到原状，事务一旦提交就持久化到数据库
• 隔离性：多个用户操作，互不影响，会被隔离

隔离导致的问题

手动处理事务

-- 手动处理事务
SET autocommit=0; -- 关闭自动提交
-- 事务开启
START TRANSACTION

-- 提交
COMMIT
-- 回滚
ROLLBACK
-- 事务结束
SET autocommit=1; -- 开启自动提交

模拟转账

-- 模拟转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop

CREATE TABLE `account`(
	`id` INT(10) NOT NULL auto_increment COMMENT 'id',
	`name` VARCHAR(30) NOT NULL COMMENT '姓名',
	`money`DECIMAL(9,2) NOT NULL COMMENT '钱',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account(`name`,`money`)VALUES('A',2000),('B',1000)

-- 模拟事务转账
-- 关闭事务自动提交
SET autocommit=0;
-- 开启事务
START TRANSACTION

UPDATE account SET money=money-500 WHERE `name`='A'
UPDATE account SET money=money+500 WHERE `name`='B'

-- 提交
COMMIT
-- 回滚
ROLLBACK
--  事务结束
SET autocommit=1;

5.索引

• 主键索引(primary key)
  • 主键不可重复，只有一个列作为主键
• 唯一索引(unique key)
  • 避免重复的列出现，唯一索引可以重复，多个列都可以标识位
• 常规索引(key/index)
  • 默认，可以使用key、index关键字设置
• 全文索引(fulltext)
  • 特定引擎才支持

/*
#方法一：创建表时
  　　CREATE TABLE 表名 (
               字段名1 数据类型 [完整性约束条件…],
               字段名2 数据类型 [完整性约束条件…],
               [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
               [索引名] (字段名[(长度)] [ASC |DESC])
               );


#方法二：CREATE在已存在的表上创建索引
       CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
                    ON 表名 (字段名[(长度)] [ASC |DESC]) ;


#方法三：ALTER TABLE在已存在的表上创建索引
       ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                            索引名 (字段名[(长度)] [ASC |DESC]) ;
                           
                           
#删除索引：DROP INDEX 索引名 ON 表名字;
#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;


#显示索引信息: SHOW INDEX FROM student;
*/

/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);

/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';

/*使用全文索引*/
-- 全文搜索通过 MATCH() 函数完成。
-- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');

/*
开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
*/

测试

建表app_user：

CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`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,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

批量插入数据：100w

set global log_bin_trust_function_creators=TRUE;  -- 自定义函数错误临时解决方案

DROP FUNCTION IF EXISTS mock_data; -- 如果自定义函数存在则删除

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), '24736743@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 * FROM app_user WHERE name = '用户9999'; --  1.245s
SELECT * FROM app_user WHERE name = '用户9999'; --  1.247s
SELECT * FROM app_user WHERE name = '用户9999'; -- 1.24s

-- 添加索引
-- CREATE INDEX id_表名_字段名 ON 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`)
-- 存在索引
SELECT * FROM app_user WHERE name = '用户9999'; --  0.006s
SELECT * FROM app_user WHERE name = '用户9999'; --  0s
SELECT * FROM app_user WHERE name = '用户9999'; -- 0s
EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'; -- 查看sql的执行

小结：索引在针对大数据量进行查询的时候，可以大大的提高查询的效率

索引原则

• 索引不是越多越好
• 经常变动的数据不需要添加索引
• 小数据量表不需要添加索引
• 索引一般加在常用来查询的字段上

索引的数据结构:

-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

-- 不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

6.数据库用户管理

7.数据库备份

mysqldump导出数据

# mysqldump -h主机 -uroot -proot 库 表1 表2 > 物理磁盘位置
mysqldump -hlocalhost -uroot -proot ks sc > D:/a.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
#导出多张表使用空格分开

# 导入
# 登录情况
# source 备份文件
source d:/a.sql

mysql -uroot -proot 库名< 备份文件

8.数据库的设计

三大范式

第一范式

原子性：保证每一列不可再分

第二范式

满足第一范式的前提下，每张表只描述一件事情

第三范式

满足第一、第二的范式的前提下，每一列数据都要和主键直接相关

9.JDBC

创建测试数据库

CREATE DATABASE jdbctest CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbctest

CREATE TABLE users(
	`id` INT(10) PRIMARY KEY,
	`name` VARCHAR(40),
	`psw` VARCHAR(40),
	`email` VARCHAR(60),
	`birthday` DATE
);

INSERT INTO users(`id`,`name`,`psw`,`email`,`birthday`)
VALUES(1,'张三','123456','zs@sina.com','1980-12-11'),
(2,'lishi','123456','ls@sina.com','1980-12-11'),
(3,'xiaomin','123456','xm@sina.com','1980-12-11')

SELECT * FROM users

数据库驱动下载:https://dev.mysql.com/downloads/connector/j/

package com.xx.lesson01;

import java.sql.*;

public class jdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.连接信息（用户信息和url）
        //useUnicode=true&characterEncoding=utf8&&useSSL=true
        String url = "jdbc:mysql://localhost:3306/jdbctest?useUnicode=true&characterEncoding=utf8&&useSSL=true";
        String username="root";
        String password="root";
        //3.连接成功，数据库对象 connection代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);
        //4.执行sql的对象 statement 执行sql对象
        Statement statement = connection.createStatement();
        //5.写SQL,执行sql
        String sql="SELECT * FROM users";
        //resultSet 返回结果集  结果集中封装了查询出来的全部结果
        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("psw="+resultSet.getObject("psw"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birthday="+resultSet.getObject("birthday"));
        }
        //释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

注意：Loading class com.mysql.jdbc.Driver'. This is deprecated. The new driver class is com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary

Class.forName("com.mysql.jdbc.Driver")修改为Class.forName(“com.mysql.cj.jdbc.Driver”)`

步骤:

1. 加载驱动
2. 连接数据库DriveManger
3. 获得执行sql的对象 Statement
4. 获得返回的结果
5. 释放连接

9.1 Statement对象

JDBC中的statement对象用于向数据库发送sql语句，主要用于执行sql

Statement.excuteQuery 查询 Statement.Update 删、改、查

工具类提取

properties

drive=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbctest?useUnicode=true&characterEncoding=utf8&&useSSL=true
username=root
password=root

jdbcUtils

package com.xx.lesson02.utils;

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

public class jdbcUtils {
    private static String drive = 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);
            drive = properties.getProperty("drive");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            //加载驱动
            Class.forName(drive);

        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    //获取连接
    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) {
                throw new RuntimeException(e);
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

test

package com.xx.lesson02;

import com.xx.lesson02.utils.jdbcUtils;

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

public class tsetInsert {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //获得数据的连接
            connection = jdbcUtils.getConnection();
            //创建执行SQL的对象
            statement = connection.createStatement();
            //SQL
            String sql = "insert into users(id, name, psw, email, birthday) values (4,'ks','ks','123456@qq.com','2022-01-01')";
            int i = statement.executeUpdate(sql);
            if (i > 0) {
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            jdbcUtils.release(connection, statement, null);
        }
    }
}

注意点：

• properties文件不需要使用引号
• properties文件必须放在src（源文件）下面否则会报错

Could not initialize class com.XX.utils.JdbcUtils：https://www.cnblogs.com/shiyingying/p/16248587.html

statement是不安全的 存在sql注入

9.2 PreparedStatement

PreparedStatement相比较Statement要安全，可以防止sql注入

package com.xx.lesson03;

import com.xx.lesson02.utils.jdbcUtils;

import javax.xml.crypto.Data;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @author NM
 */
public class testInsert {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement st = null;
        try {
            connection = jdbcUtils.getConnection();
            String sql = "insert into users(id, name, psw, email, birthday) values (?,?,?,?,?)";
            //预编译sql
            st = connection.prepareStatement(sql);
            //参数赋值
            //setInt(参数下标，参数值)
            st.setInt(1, 4);
            st.setString(2, "ks");
            st.setString(3, "ks");
            st.setString(4, "ks");
            //获取系统时间
            //第一个Date为java.sql里面的  第二个为java.utils里面的
            Date date = new Date(new java.util.Date().getTime());
            st.setDate(5, date);
            int i = st.executeUpdate();
            if (i > 0) {
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            jdbcUtils.release(connection, st, null);
        }
    }

}

9.3 事务

package com.xx.lesson04;

import com.xx.lesson02.utils.jdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestTransaction {
    public static void main(String[] args) {
        PreparedStatement st = null;
        Connection connection = null;
        try {
            connection = jdbcUtils.getConnection();
            //关闭自动提交,开启事务
            connection.setAutoCommit(false);

            //执行sql
            String sql1 = "update account set money=money-100 where name='A'";
            //预编译
            st = connection.prepareStatement(sql1);
            //执行
            st.executeUpdate();

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

            //业务成功
            connection.commit();
            System.out.println("成功");

        } catch (SQLException e) {
//            try {
//                connection.rollback();
//            } catch (SQLException ex) {
//                throw new RuntimeException(ex);
//            }
            throw new RuntimeException(e);
        }finally {
            jdbcUtils.release(connection,st,null);
        }
    }
}

对于事务的提交，如果失败，我们可以显示的定义进行回滚，默认的异常就可以回滚

9.4 数据库连接池