8. 数据库备份
8.1 用户管理
sql yog可视化管理
用户表：mysql.user
本质：对这张表进行增删改查
-- ==============权限==========
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY 密码
CREATE USER kuangshen IDENTIFIED BY '123456'
-- 修改密码（修改当前用户密码）
SET PASSWORD =PASSWORD('111111')
-- 修改密码（修改指定用户密码）
SET PASSWORD FOR kuangshen=PASSWORD('111111')
-- 重命名 RENAME USER 原名 TO 新名
RENAME USER kuangshen TO liutao
-- 用户授权 ALL PRIVILEGES全部权限 库，表
-- ALL PRIVILEGES除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON *.* TO liutao
-- 查询权限
SHOW GRANTS FOR liutao -- 查看指定用户权限
SHOW GRANTS FOR root@localhost -- root用户管理的权限
-- 撤销权限 REVOKE 哪些权限 在哪个库 给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM liutao;
-- 删除用户
DROP USER liutao
8.2 mysql备份
为什么备份：
- 重要数据不丢失
- 数据转移
mysql数据库备份的方式
-
直接拷贝物理文件
-
在sqlyog这种可视化工具中手动导出
-
使用命令行导出 mysqldump
mysqldump -h主机名 -u用户名 -p密码 数据库 表1 表2 表3>物理磁盘位置/文件名
- 导入
在登录的情况下，切换到指定的数据库
source 备份文件
mysql -u用户名 -p密码 库名<备份文件
假设你要备份数据库，防止数据丢失
把数据库给别人，sql文件给别人即可
9.权限管理
9.1 为什么需要设计
当数据库比较复杂的时候，我们需要设计
糟糕的数据库设计：
- 数据冗余，浪费空间
- 数据库插入和删除都会麻烦、异常【屏蔽物理外键】
- 程序性能差
良好的数据库设计：
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
软件开发中，关于数据库的需求 - 分析需求：分析业务和需要处理的数据库的需求
- 概要设计：设计关系图E-R图
设计数据库的步骤：（个人博客）
- 收集信息，分析需求
- 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章信息）
- 友链表（友链信息）
- 自定义表（系统信息，某个关键的字，或者一些主题）key:value
- 标识实体（把需求落实到每个字段）
- 标识实体之间的关系
- 写博客 user—>blog
- 创建分类 user —>category
- 关注 user—>user
- 友链：links
- 评论：user—>user
9.2 三大范式
为什么需要数据规范化
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效信息
三大范式
- 第一范式（INF）:原子性，保证每列不可再分
- 第二范式（2NF）:前提：满足第一范式，每张表只描述一件事情
- 第三范式（3NF）:前提：满足第一范式和第二范式 。第三范式需要确保数据表中的每一列数据都和主键相关，而不能间接相关。
规范性和性能的问题
关联查询的表不能超过三张表
- 考虑商业化的需求和目标。（成本、用户体验！）
- 在规范性能的问题的时候，需要适当的考虑规范性
- 故意给某些表增加冗余字段（夺标查询变为单表查询）
- 故意增加一些计算列（大数据量降低为小数据来量的查询）
10. JDBC（重点）
10.1 数据库驱动
驱动： 声卡、显卡、数据库
我们程序会通过数据库驱动和数据库打交道
10.2 JDBC
SUN为了简化开发人员的操作，提供了一个（java操作数据库的）规范，俗称JDBC。这些规范的实现由具体的厂家去做。
对于开发人员来说，我们只需要掌握JDBC接口的操作即可
java.sql
javax.sql
还需要导入一个数据库驱动包 mysql-connector-java-5.1.47.jar
10.3 第一个JDBC程序
创建测试数据库
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,'zhansan','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')
- 创建一个普通项目
- 导入数据库驱动
- 编写测试代码
package com.kuang.lesson01;
import java.sql.*;
/**
* @author shkstart
* @create 2020-09-14 13:58
*/
public class jdbcfirst {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.用户名和url
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String password = "123456";
String user ="root";
//3.连接成功，数据库对象
Connection connection= DriverManager.getConnection(url,user,password);
//4.执行sql对象Statement 执行sql的对象
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("password = " +resultSet.getObject("password"));
System.out.println("email = " +resultSet.getObject("email"));
System.out.println("birthday = " +resultSet.getObject("birthday"));
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
结果为：
步骤总结：
1.加载驱动
2.连接数据库DriverManager
3.获得执行sql的对象 Statement
4.获得返回的结果集
DriverManager
Class.forName("com.mysql.jdbc.Driver");//固定写法，加载驱动
//connection代表数据库
//数据库自动提交
//事务提交
//事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();
URL
```java
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
//mysql默认 --3306
//jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3
Statemen执行sql的对象
statement.executeQuery();//查询操作返回 ResultSet
statement.execute(); //执行任何sql
statement.executeUpdate();//更新，插入，删除，返回受影响的行数
ResultSet 查询的结果集：封装了所有的查询结果
释放资源必须做
resultSet.close();
statement.close();
connection.close();
10.4 statement对象
自建配置文件 db.properties
driver= com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username =root
password = 123456
自建工具类
package com.kuang.lesson02.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
import static java.sql.DriverManager.getConnection;
/**
* @author shkstart
* @create 2020-09-14 15:29
*/
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 (IOException e) {
e.printStackTrace();
}catch (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();
}
}
}
}
}
CRUD操作
insert delete
代码实现
public class TestInsert {
public static void main(String[] args) {
Connection connection =null;
Statement statement = null;
ResultSet resultSet = null;
try {
//创建连接
connection =JdbcUtils.getConnection();
//获得sql执行对象
statement= connection.createStatement();
String sql1 = "INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES" +
"(8,'liutao','123651','9561333@qq.com','1978-6-12')";
String sql ="DELETE FROM users WHERE id =4";
int i =statement.executeUpdate(sql);
//
if(i > 0){
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
update
public class TestUpdate {
public static void main(String[] args) {
Connection connection =null;
Statement statement = null;
ResultSet resultSet = null;
try{
//创建连接
connection =JdbcUtils.getConnection();
//获得sql执行对象
statement= connection.createStatement();
String sql1 = "INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES" +
"(8,'liutao','123651','9561333@qq.com','1978-6-12')";
String sql ="UPDATE users SET NAME ='liudeyang',email = '955314@qq.com',PASSWORD= '144599' WHERE id =7";
int i =statement.executeUpdate(sql);
//
if(i > 0){
System.out.println("成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
Select
public class TestSelect {
public static void main(String[] args) {
Connection connection =null;
Statement statement = null;
ResultSet resultSet =null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
//sql
String sql = "SELECT * FROM users;";
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println(resultSet.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
sql注入问题
sql存在漏洞，会被攻击导致数据泄露，sql会被攻击
public class SQL注入 {
public static void main(String[] args) {
login(" 'or '1=1"," 'or '1=1");
}
//登录业务
public static void login(String username,String password) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
//sql
String sql = "SELECT * FROM users WHERE name ='"+username+"' AND password = '"+password+"'";
resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println(resultSet.getObject("name"));
System.out.println(resultSet.getObject("password"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection, statement, resultSet);
}
}
}
10.5 PrepareStatement对象
PrepareStatement可以防止sql注入，效率更高
1.新增
package com.kuang.lesson02.utils;
import java.sql.*;
import java.util.Date;
import java.sql.SQLException;
/**
* @author shkstart
* @create 2020-09-14 20:56
*/
public class TestInsert {
public static void main(String[] args) {
Connection connection =null;
PreparedStatement preparedStatement =null;
try {
connection = JdbcUtils.getConnection();
//区别
//使用?占位符代替参数
String sql = "INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES (?,?,?,?,?)" ;
preparedStatement = connection.prepareStatement(sql);//预编译sql,先写sql,然后不执行
//手动给参数赋值
preparedStatement.setInt(1,6);
preparedStatement.setString(2,"liudadadad");
preparedStatement.setString(3,"122332");
preparedStatement.setString(4,"097665@qq.com");
//注意点：sql.Date sql
// util.Date java
// new Date().getTime() 获得时间戳
long date= new Date().getTime();
preparedStatement.setDate(5,new java.sql.Date(date));
//执行
int i =preparedStatement.executeUpdate();
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JdbcUtils.release(connection,preparedStatement,null);
}
}
}
2.删除
package com.kuang.lesson02.utils;
import javax.print.DocFlavor;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
/**
* @author shkstart
* @create 2020-09-14 21:39
*/
public class TestDelete {
public static void main(String[] args) {
Connection connection =null;
PreparedStatement preparedStatement =null;
try {
connection = JdbcUtils.getConnection();
//区别
//使用?占位符代替参数
String sql = "INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES (?,?,?,?,?)" ;
String sql1 = "DELETE FROM `users` WHERE `id`=?" ;
preparedStatement = connection.prepareStatement(sql1);//预编译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,null);
}
}
}
3.修改
package com.kuang.lesson02.utils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
/**
* @author shkstart
* @create 2020-09-14 21:43
*/
public class TestUpdate {
public static void main(String[] args) {
Connection connection =null;
PreparedStatement preparedStatement =null;
try {
connection = JdbcUtils.getConnection();
//区别
//使用?占位符代替参数
String sql = "UPDATE `users` SET `NAME` = 'liudada' WHERE `id` =?" ;
preparedStatement = connection.prepareStatement(sql);//预编译sql,先写sql,然后不执行
//手动给参数赋值
preparedStatement.setInt(1,1);
//执行
int i =preparedStatement.executeUpdate();
if(i>0){
System.out.println("更新成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JdbcUtils.release(connection,preparedStatement,null);
}
}
}
4.查询
package com.kuang.lesson02.utils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @author shkstart
* @create 2020-09-14 21:56
*/
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 * FROM `users` where id =? ";//编写sql
preparedStatement = connection.prepareStatement(sql);//预编译
preparedStatement.setInt(1,1);//传递参数
resultSet = preparedStatement.executeQuery();//执行
if(resultSet.next()){
System.out.println(resultSet.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,preparedStatement,resultSet);
}
}
}
5.防止sql注入
package com.kuang.lesson02.utils;
import jdk.nashorn.internal.scripts.JD;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @author shkstart
* @create 2020-09-14 22:06
*/
public class sql注入 {
public static void main(String[] args) {
// login("liudada","123456");
login(" ' or 1=1","123456");
}
public static void login(String username, String password){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
//PreparedStatement防止sql注入的本质，将传递进来的参数当作字符
//假设其中存在转义字符，比如说' ，会被直接转义
String sql = "SELECT * FROM `users` WHERE `NAME` = ? AND `PASSWORD`=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,username);
preparedStatement.setString(2,password);
resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
System.out.println(resultSet.getString("name"));
System.out.println(resultSet.getString("password"));
System.out.println("================================");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,preparedStatement,resultSet);
}
}
}
952
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
