本文详细介绍了MySQL数据库的安装、配置、字段类型、DML操作、事务处理、索引、权限管理及备份等方面的知识，特别强调了数据库设计规范和JDBC操作数据库的步骤。此外，还探讨了数据库引擎的差异、SQL语句的使用以及如何通过JDBC进行安全的数据库交互，包括使用PreparedStatement避免SQL注入。最后，提到了数据库连接池的概念，推荐了Druid等开源数据源实现。
my数据库
一、MySQL
1、介绍
- DBMS（数据库管理系统）
MySQL是一个关系型数据库管理系统。
瑞典MySQL AB 公司 ，Oracle公司
最好的RDBMS（Relation Database Management System）应用软件之一。
开源的数据库软件~
体积小、速度快、总体拥有成本低
官网：https://www.mysql.com/
版本5.7 ， 8.0
2、安装
-
不要使用exe版本，尽可能使用压缩包安装；
-
解压到Java环境目录（习惯）
-
配置环境变量Path加入MySQL地址
-
新建mysql配置文件my.ini , 放在MySql 根目录
[mysqld] basedir=D:\Java\environment\mysql-5.7.33\ datadir=D:\Java\environment\mysql-5.7.33\data\ port=3306 skip-grant-tables #本行作用是跳过密码登录，第一次登录设置密码后，需要删除。 -
管理员模式启动CMD ， 进入mysql的bin目录 ， 执行命令
cd /d D:\Java\environment\mysql-5.7.33\bin mysqld -install #安装服务 mysqld --initialize-insecure --user=mysql #初始化数据文件 net start mysql #启动服务 mysql -uroot -p #登录(p后面没有空格，因为还没有设置密码) update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost'; #设置密码123456，sql语句结尾要分号; flush privileges; #刷新权限 -
删除或者注释掉my.ini中最后一行skip-grant-tables
-
重启测试
exit #退出mysql net stop mysql #停止服务 net start mysql #启动服务 mysql -uroot -p123456 #登录测试是否成功 -
安装报错处理
-
缺少组件，安装https://www.microsoft.com/zh-CN/download/details.aspx?id=40784
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DxkgadIy-1617433552352)(MySQL.assets/image-20210330170156483.png)]
-
命令行报错：mysql: [ERROR] mysql: unknown option ‘-a’
原因是：
mysqld -install不要忘记d
-
3、数据库列类型
- 数值
- tinyint 1个字节
- smallint 2个字节
- mediumint 3个字节
- int 4个字节
- bigint 8个字节
- float 4个字节 浮点数
- double 8个字节 浮点数
- decimal 字符串形式的浮点数
- 字符串
- char 字符串 固定大小 0-255
- varchar 可变字符串 0-65535
- tinytext 微型文本 2^8-1
- text 文本传 2^16-1
- 时间日期
- date YYYY-MM-DD
- time HH：mm：ss
- datetime YYYY-MM-DD HH：mm：ss
- timestamp 1970-1-1到目前的毫秒数！
- year 年份
- null
- 没有值，未知
- 不要使用null进行计算
4、数据库的字段属性（重点）
Unsigned：
- 无符号的整数
- 不能为负数
zerofill：
- 0填充
- 不足的位数使用0填充。int (3) - 5 > 005
自增：
- 自动+1
- 设计 ：唯一主键
非空：
- Null not null
- 默认是非空的 ，若为空，报错
默认：
- 设置默认值
- 如果不指定值，则为默认值
项目中每个表格固定的字段（规范）：
- id 主键
- version 乐观锁
- is_delete 伪删除
- gmt_create 创建时间
- gmt_update 修改时间
5、SQL语句
- show create database school 查看数据库的创建语句
- show create table student 查看表的创建语句
- desc student 显示表的结构
6、数据库引擎
INNODB：安全性高，事务，多表多用户操作
MYISAM：节约空间，速度较快
| INNODB | MYISAM | |
|---|---|---|
| 事务支持 | 支持 | 不支持 |
| 数据行锁定 | 支持 | 不支持 |
| 外键约束 | 支持 | 不支持 |
| 全文索引 | 不支持 | 支持 |
| 表空间的大小 | 较大，约为2倍 | 较小 |
7、DML语言（重点）
-
外键
-
DML语言
-
添加 insert
insert into 表名 (表中对应的字段名称) values (每个字段对应的值); -
修改 update
update 表名 set sal=4000 where 待更改的字段名='字段中具体的行';（后面这个其实就是一个匹配的条件） -
删除 delete
delete from 表名 where 待更改的字段名='字段中具体的行';- TRUNCATE 重新设置 自增计数会归零，不会影响事务
- delete删除问题，重启数据库，现象
- InnoDB 自增列会从1开始(内存中，断电失去)
- MyISAM 保持上一次自增量（存在文件中）
-
查询 select
- select distinct 去重
- select @@auto_increment_increment 查询自增步长
-
分页
- limit 0,5 0是起始位置，共5页
- 第n页 limit （n-1)*pageSize, pageSize pageSize页面大小
- 总页数 = 数据总数/页面大小
-
函数
- ABS() 绝对值
- CEILING() 向上取整
- FLOOR() 向下取整
- RAND() 0-1之间随机数
- SIGN() 符号。负数返回-1 ，正数返回1
- CHAR_LENGTH() 字符串长度
- CONCAT() 拼接字符串
- INSERT() 查询，从某个位置替换
- LOWER() 小写
- UPPER() 大写
- INSTR() 返回字符出现的索引
- REPALCE() 按字符串替换字符串
- SUBSTR() 截取字符串
- REVERSE() 反转
- CURRENT_DATE() 获取当前时间
- CURDATE() 获取当前时间
- NOW() 获取当前时间
- LOCALTIME() 本地时间
- SYSDATE() 系统时间
- MD5() 加密
8、事务
ACID
-
原子性Atomicity
- 要么都成功，要么都失败
-
一致性Consistency
- 事务前后数据保持一致性
-
隔离性Isolation
- 并发访问数据，不被其他事务干扰
-
持久性Durablity
- 事务一旦提交不可逆，持久到数据库中
隔离级别
- 脏读
- 一个事务读取另一个事务未提交的数据
- 不可重复读
- 多次读取结果不同
- 幻读
- 读取到了别的事务插入的数据，导致前后不一致
操作事务：
--开启手动提交事务。默认值是1 ，自动提交事务
SET AUTOCOMMIT = 0;
--事务开始
START TRANSACTION;
--提交
COMMIT;
--回滚
ROLLBACK;
--关闭手动提交事务。
SET AUTOCOMMIT = 1;
8、索引
索引分类：
- 主键索引：Primary Key
- 每个表主键索引只能有一个，且主键索引的列不能重复。
- 唯一索引：Unique Key
- 每个表唯一索引可以有多个，但是唯一索引的列不能重复。
- 常规索引：Key|Index
- 默认的，index关键字和key来设置
- 全文索引：FullText
- 在特定数据库引擎才有MyISAM
- 快速定位数据
索引可以在建表时设置，也可以创建完成后增加。
基础语法：
--查询索引
SHOW INDEX FROM `student`
--插入全文索引
ALTER TABLE `student` ADD FULLTEXT INDEX `name`(`name`)
--分析sql执行的状态
EXPLAIN SELECT * FROM student;--非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(NAME) AGAINST('a');
--创建Index索引
CREATE INDEX student_name ON `student`(`name`);
索引原则
- 索引加载常用查询字段上
- 不要对变动数据加索引
- 数据量大于100W，再加索引
索引的数据类型
Hash类型
Btree : InnoDB默认类型。
9、权限管理和备份
用户管理
--用户创建
create user ck identified by '123456'
--修改密码（修改当前用户）
SET PASSWORD = PASSWORD('123456')
--修改密码（修改其他用户）
SET PASSWORD FOR ck = PASSWORD('123456')
--用户授权
GRANT privileges ON databasename.tablename TO 'username'@'host'
--查看权限
Show grants for username
--撤销权限
Revoke select, delete on databaseName.* from username
备份
几种方式：
-
直接拷贝物理文件
-
使用SQLyog工具导出 , 文件为sql文件
-
命令行 mysqldump
10、规范数据库设计
- 减少数据冗余、节省空间
- 不使用物理外键
- 保证数据库的完整性
- 程序的性能好
设计数据库的步骤（个人博客）：
- 收集信息，分析需求
- 用户表
- 分类表
- 文章表
- 友链表
- 自定义表
- 标识实体
三大范式
- 原子性：每一列不可再分
- 每张表只描述一件事情
- 每一列数据都和主键直接相关，而不能间接相关
11、JDBC（重点）
java操作数据库的规范，由数据库厂家实现。
操作步骤
/**
* Author：ckvsok
* Date：2021/3/31
**/
package sql;
import java.sql.*;
public class JDBCTest01 {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
//1、加载驱动
try {
Class.forName("com.mysql.jdbc.Driver");
// Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
// 2、用户信息和URL jdbc:mysql://127.0.0.1:port/dbname 5.7版本以后的写useSSL=false
String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "123456";
// 3、连接成功，获得数据库对象 connection代表数据库
try {
connection = DriverManager.getConnection(url, username, password);
// 4、创建执行sql的对象
statement = connection.createStatement();
// 5、执行sql对象
String sql = "SELECT * FROM student";
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.print("id : "+resultSet.getObject("id"));
System.out.print(" name : "+resultSet.getObject("name"));
System.out.println(" age : "+resultSet.getObject("age"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
// 6、释放连接
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();
}
}
}
}
}
注意：URL 中添加useSSL=true后 ，会有 com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure报错。
拓展
ａ．oracle
驱动：oracle.jdbc.driver.OracleDriver
URL：jdbc:oracle:thin:127.0.0.1:port:dbname
注：127.0.0.1:数据库所在机器的名称
port:端口号，默认是1521
dbname:数据库名称
ｂ．SQL Server
驱动：com.microsoft.jdbc.sqlserver.SQLServerDriver
URL：jdbc:microsoft:sqlserver://<127.0.0.1><:port>;DatabaseName=
注：127.0.0.1:数据库所在机器的名称
port:端口号，默认是1433
dbname:数据库名称
c．mysql
驱动：com.mysql.jdbc.Driver
URL：jdbc:mysql://127.0.0.1:port/dbname
注：127.0.0.1:数据库所在机器的名称
port:端口号，默认是3306
dbname:数据库名称
ｄ．DB2
驱动：com.ibm.db2.jdbc.app.DB2Driver
URL：jdbc:db2://<127.0.0.1><:port>/dbname
注：127.0.0.1:数据库所在机器的名称
port:端口号，默认是5000
dbname:数据库名称
statement对象
（不安全），有SQL注入风险
CURD操作
-
statement.executeUpdate 增删改 返回影响的行数Int
-
statement.executeQuerry 查 返回数据集ResultSet
封装工具类
/**
* Author：ckvsok
* Date：2021/3/31
**/
package utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String DRIVER;
private static String URL;
private static String USERNAME;
private static String PASSWORD;
static {
try {
InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(inputStream);
DRIVER = properties.getProperty("driver");
URL = properties.getProperty("url");
USERNAME = properties.getProperty("username");
PASSWORD = properties.getProperty("password");
Class.forName(DRIVER);
} catch (IOException | ClassNotFoundException 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();
}
}
}
}
/**
* Author：ckvsok
* Date：2021/3/31
**/
package sql;
import utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCTest02 {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//调用工具类获取connection对象
connection = JdbcUtils.getConnection();
String sql = "INSERT INTO `student`(`name`,`age`) VALUE('xy',27)";
statement = connection.createStatement();
int i = statement.executeUpdate(sql);
if (i>0){
System.out.println("数据插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
Preparestatement对象
预编译, 可以防止SQL注入
/**
* Author：ckvsok
* Date：2021/3/31
**/
package sql;
import utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBCTest03 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
connection = JdbcUtils.getConnection();
String sql = "delete from student where name = ? and id=?";
ps = connection.prepareStatement(sql);
ps.setString(1,"b");
ps.setInt(2,2);
int i = ps.executeUpdate();
if (i>0){
System.out.println("删除成功.");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
数据库连接池
数据库连接–释放，占用系统资源非常大
使用连接池，预先准备好资源，然后连接预先准备好的
最小连接数
最大连接数
超过最大连接数，排队等待。
等待超时
实现DataSource接口
开源数据源实现
DBCP
C3P0
Druid
JdbcUtils.getConnection();
String sql = “delete from student where name = ? and id=?”;
ps = connection.prepareStatement(sql);
ps.setString(1,“b”);
ps.setInt(2,2);
int i = ps.executeUpdate();
if (i>0){
System.out.println(“删除成功.”);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
#### 数据库连接池
数据库连接--释放，占用系统资源非常大
使用连接池，预先准备好资源，然后连接预先准备好的
**最小连接数**
**最大连接数**
超过最大连接数，排队等待。
**等待超时**
实现DataSource接口
> 开源数据源实现
**DBCP**
**C3P0**
**Druid**
使用数据库连接池后，就不需要写连接程序了
扫一扫
3634
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
