Mysql
事务 Transaction
要么都成功、要么都失败
将一组SQL放进一个批次执行
事务原则 ACID原则
原子性 Atomicity
原子性是指事务是一个不可分割的工作单位、事务中的操作要么都发生、要么都不发生
一致性 Consistency
事务前后数据的完整性必须保持一直
隔离性 Isolation
事务的隔离性是多个用户并发访问数据库时、数据库为每一个用户开启的事务、不能被其他事务的操作数据所干扰、多个并发事务之间要相互隔离
持久性 Durability
持久性是指一个事务一旦被提交、对数据库中数据的改变是永久性的、接下来使数据库发生故障也不应该对其有任何影响
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SET AUTOCOMMIT = 0;
START TRANSACTION
UPDATE account SET money = money - 1000 WHERE `name` = 'A'
UPDATE account SET money = money + 1000 WHERE `name` = 'B'
COMMIT
ROLLBACK
SET AUTOCOMMIT = 1;
|
可能发生的一些问题
脏读
指一个事务读取了另一个事务未提交的数据
不可重复读
在一个事务内读取表中的某一行数据、多次读取结果不同
续读(幻读)
在一个事务内读取到了别的事务插入的数据、导致前后读取不一致
索引
索引是帮助MySQL高效获取数据的数据结构
索引的分类
主键索引(PRIMARY KEY)
唯一的标识、主键不可重复、只能有一个列作为主键
唯一索引(UNIQUE KEY)
避免重复的列出现、唯一索引可以重复、多个列都可以标识位、唯一索引
常规索引(KEY / INDEX)
默认
全文索引(FULLTEXT)
快速定位
索引的原则
索引不是越多越好
不要对进程变动数据加索引
小数据量的表不需要加索引
用户管理
可视化工具可直接点击操作
命令行形式
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
CREATE USER xiaobo IDENTIFIED BY 'xiaobo'
ALTER USER 'xiaobo' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT ALL PRIVILEGES ON *.* TO xiaobo
RENAME USER xiaobo TO wangyibo
SHOW GRANTS FOR wangyibo
REVOKE ALL PRIVILEGES ON *.* FROM wangyibo
DROP USER wangyibo
|
备份和导入
备份
可视化工具可直接点击操作
命令行
1
2
|
mysqldump -hlocalhost -uroot -pxiaobo test > D://a.sql
|
导入
可视化工具可直接点击操作
命令行
1
2
3
4
5
6
|
source D://a.sql
mysql -u用户名 -p密码 库名 < 要导入的路径文件
|
数据库设计
不理想的数据库
数据冗余、浪费时间
数据库插入和删除都麻烦
程序的性能差
良好的数据库设计
三大范式
第一范式
保证每一列不可再分
分量不可再分
第二范式
满足第一范式
每张表只描述一类事情
不存在非主属性对候选键的部分函数依赖
第三范式
满足第一范式 和 第二范式
确保数据表中每一列数据都和主键直接有关、不能间接相关
不存在非主属性对候选键的传递函数依赖
规范性 和 性能的问题
关联查询的表不得超过三张表
商业化需求和目标中、数据库性能更重要
应适当考虑规范性
给某些表增加冗余字段(从多表查询变为单表查询)
增加计算列(从大数据量降低为小数据量的查询)
JDBC
SUN 公司简化开发人员的对数据库的统一操作、提供一个java操作数据库的规范
java jdbc 连接 mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
| public class JDBCUtil {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet query = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false";
String username = "root";
String password = "xiaobo";
connection = DriverManager.getConnection(url, username, password);
statement = connection.createStatement();
String sql = "SELECT * FROM student";
query = statement.executeQuery(sql);
while (query.next()){
System.out.println("id =" + query.getObject("id"));
System.out.println("username =" + query.getObject("username"));
System.out.println("password =" + query.getObject("password"));
System.out.println("birthday = " + query.getObject("birthday"));
System.out.println("address = " + query.getObject("address"));
System.out.println("clazzid = " + query.getObject("clazzid"));
System.out.println("====================================================");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
query.close();
statement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
|
Class.forName("com.mysql.cj.jdbc.Driver") 加载驱动
DriverManager.getConnection(url, username, password) DriverManager 连接数据库对象
connection.createStatement() Statement 获取sql对象 connection就是数据库对象、数据库能做的事它都能做
statement.executeQuery(sql) 执行sql statement是sql对象、它能做sql能做的事、crud都可以 executeUpdate(sql)更新 返回受影响的行数 executeQuery(sql) 查询 返回查询到的结果集
ResultSet ResultSet 结果集对象
对象.close() 释放资源、关闭连接
工具类
1
2
3
4
| driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8
username = root
password = xiaobo
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
| 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 inputStream = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(inputStream);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
public static void releae(Connection connection, Statement statement, ResultSet resultSet){
if (null != resultSet){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (null != statement){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (null != connection){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
| public class TestInsert {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
statement = connection.createStatement();
String sql = "DELETE FROM student WHERE id = 1";
int i = statement.executeUpdate(sql);
if (i > 0){
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBCUtils.releae(connection,statement,resultSet);
}
}
}
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
| public class TestQuery {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet query = null;
try {
connection = JDBCUtils.getConnection();
statement = connection.createStatement();
String sql = "SELECT * FROM student";
query = statement.executeQuery(sql);
while (query.next()){
System.out.println("id =" + query.getObject("id"));
System.out.println("username =" + query.getObject("username"));
System.out.println("password =" + query.getObject("password"));
System.out.println("birthday = " + query.getObject("birthday"));
System.out.println("address = " + query.getObject("address"));
System.out.println("clazzid = " + query.getObject("clazzid"));
System.out.println("====================================================");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
|
SQL注入
sql存在漏洞、会被攻击导致数据泄露
写sql往sql语句中拼接、写一些条件拼接在sql里面来逃避校验
PreparedStatement对象
可防止SQL注入问题
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
| public class TestInsert2 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
String sql = "DELETE FROM student WHERE id = ?";
statement = connection.prepareStatement(sql);
statement.setInt(1,2);
int i = statement.executeUpdate();
if (i > 0){
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBCUtils.releae(connection,statement,resultSet);
}
}
}
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
| public class TestQuery2 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet query = null;
try {
connection = JDBCUtils.getConnection();
String sql = "SELECT * FROM student WHERE id = ?";
statement = connection.prepareStatement(sql);
statement.setInt(1,3);
query = statement.executeQuery();
while (query.next()){
System.out.println("id =" + query.getObject("id"));
System.out.println("username =" + query.getObject("username"));
System.out.println("password =" + query.getObject("password"));
System.out.println("birthday = " + query.getObject("birthday"));
System.out.println("address = " + query.getObject("address"));
System.out.println("clazzid = " + query.getObject("clazzid"));
System.out.println("====================================================");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
|
JDBC事务处理
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
| public class TestAccount {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
//关闭数据库自动提交、自动会开启事务
connection.setAutoCommit(false);
//SQL用?代替
String sql1 = "UPDATE account SET money = money - 1000 WHERE name = 'A'";
//预编译、先写SQL
statement = connection.prepareStatement(sql1);
statement.executeUpdate();
//异常
int i = 1 / 0;
String sql2 = "UPDATE account SET money = money + 1000 WHERE name = 'B'";
//预编译、先写SQL
statement = connection.prepareStatement(sql1);
statement.executeUpdate();
//业务结束、提交事务
connection.commit();
System.out.println("更新成功");
} catch (SQLException e) {
try {
//事务回滚
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
}finally{
JDBCUtils.releae(connection,statement,resultSet);
}
}
}
|
正确的开始、微小的长进、然后持续、嘿、我是小博、带你一起看我目之所及的世界……