8 权限管理与备份
8.1 用户管理
SQL 基本命令
/* 用户和权限管理 */ ------------------
用户信息表：mysql.user
-- 刷新权限
FLUSH PRIVILEGES
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER chujiu IDENTIFIED BY '123456'
-- 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD('111111')
-- 修改密码（修改指定 用户密码）
SET PASSWORD FOR chujiu = PASSWORD('111111')
-- 重命名 RENAME USER 原名字 TO 新名字
RENAME USER chujiu TO chujiu2
-- 用户授权 ALL PRIVILEGES 全部的权限, 库、表
-- ALL PRIVILEGES 除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON *.* TO chujiu2
-- 查看权限
SHOW GRANTS FOR chujiu2 -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost
-- 撤销权限 revoke， 在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM chujiu2
权限解释
-- 权限列表
ALL [PRIVILEGES] -- 设置除GRANT OPTION之外的所有简单权限
ALTER -- 允许使用ALTER TABLE
ALTER ROUTINE -- 更改或取消已存储的子程序
CREATE -- 允许使用CREATE TABLE
CREATE ROUTINE -- 创建已存储的子程序
CREATE TEMPORARY TABLES -- 允许使用CREATE TEMPORARY TABLE
CREATE USER -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW -- 允许使用CREATE VIEW
DELETE -- 允许使用DELETE
DROP -- 允许使用DROP TABLE
EXECUTE -- 允许用户运行已存储的子程序
FILE -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX -- 允许使用CREATE INDEX和DROP INDEX
INSERT -- 允许使用INSERT
LOCK TABLES -- 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS -- 允许使用SHOW FULL PROCESSLIST
REFERENCES -- 未被实施
RELOAD -- 允许使用FLUSH
REPLICATION CLIENT -- 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE -- 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT -- 允许使用SELECT
SHOW DATABASES -- 显示所有数据库
SHOW VIEW -- 允许使用SHOW CREATE VIEW
SHUTDOWN -- 允许使用mysqladmin shutdown
SUPER -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE -- 允许使用UPDATE
USAGE -- “无权限”的同义词
GRANT OPTION -- 允许授予权限
/* 表维护 */
-- 分析和存储表的关键字分布
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ...
-- 检查一个或多个表是否有错误
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- 整理数据文件的碎片
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
8.2 MySQL备份
数据库备份必要性
- 保证重要数据不丢失
- 数据转移
MySQL数据库备份方法
-
使用命令行导出 mysqldump 命令备份工具
-- 导出一张表 mysqldump -h主机名 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名.sql C:\Users\龚慧雯>mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. -- 导出多张表 dmysqldump -h主机名 -u用户名 -p密码 数据库 表1 表2 表3 >物理磁盘位置/文件名.sql C:\Users\龚慧雯>mysqldump -hlocalhost -uroot -p123456 school student result >D:/b.sql -- 导出所有表 mysqldump -h主机名 -u用户名 -p密码 数据库>物理磁盘位置/文件名.sql C:\Users\龚慧雯>mysqldump -hlocalhost -uroot -p123456 school>D:/c.sql -- 导入一个库 mysqldump -h主机名 -u用户名 -p密码 -B 库名 >文件名(D:/a.sql) C:\Users\龚慧雯>mysqldump -hlocalhost -uroot -p12345 -B school >D:/a.sql -- 导入 -- 登录的情况下，切换到指定的数据库 -- source 备份文件 source d:/a.sql -
数据库管理工具,如SQLyog这种可视化工具
-
直接拷贝物理文件
mysqldump客户端
作用 :
- 转储数据库
- 搜集数据库进行备份
- 将数据转移到另一个SQL服务器,不一定是MySQL服务器
9 规范数据库设计
9.1 为什么需要设计
当数据库比较复杂的时候，我们就需要设计了
糟糕的数据库设计 :
- 数据冗余,存储空间浪费
- 数据更新和插入的异常
- 程序性能差
良好的数据库设计 :
- 节省数据的存储空间
- 能够保证数据的完整性
- 方便进行数据库应用系统的开发
软件项目开发周期中数据库设计 :
- 需求分析阶段: 分析客户的业务和数据处理需求
- 概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.
设计数据库的步骤 :
-
收集信息，分析需求
-
- 与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.
-
标识实体（把需求落地到每个字段）
-
- 标识数据库要管理的关键对象或实体,实体一般是名词
-
标识实体之间的关系[Relationship]
- 写博客：user->blog
- 创建分类： user->category
- 关注：user->user
- 友链：links
- 评论：user->user->blog
9.2 三大范式
为什么需要数据规范化？
-
信息重复
-
更新异常
-
插入异常
-
- 无法正确表示信息
-
删除异常
-
丢失有效的信息
三大范式（规范数据库的设计）
- 第一范式（1NF）
原子性：保证每一列不可再分
- 第二范式（2NF）
前提：满足第一范式
每张表只描述一件事情
- 第三范式（3NF）
前提：满足第一范式和第二范式
数据表中的每一列数据都和主键直接相关，而不是间接相关
规范性 和 性能 的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标，（成本，用户体验！）数据库的性能更加重要
- 在规范性能的问题时候，需要适当考虑一下 规范性！
- 故意给某些表增加一些冗余的字段（从多表查询变为单表查询）
- 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）
10 JDBC（重点）
java操作数据库
10.1 数据库驱动
驱动：声卡、显卡、数据库
程序通过数据库驱动，和数据库打交道！
10.2 JDBC
为了简化开发人员的（对数据库的统一）操作，提供了一个（java操作数据库的）规范。俗称JDBC
对于开发人员，只需掌握jdbc的接口
10.3 第一个JDBC 程序
创建第一个jdbc程序
package com.demo.lesson01;
import java.sql.*;
//第一个jdbc程序
public class jdbcFirstDemo {
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=false&serverTimezone=UTC";
String username="root";
String password ="123456";
//3.连接成功，数据库对象 Connection 代表数据库
Connection connection = DriverManager.getConnection(url, username, password);//ctrl+alt+v
//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("birth="+resultSet.getObject("birthday"));
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结：
1.加载驱动
2.连接数据库DriverManager connection
3.获取执行sql的对象 Statement
4.获得返回的结果集 Resultset
5.释放连接
connection SQL对象
Statement 执行SQL的对象 PrepareStatement 执行sql的对象
statement.executeQuery();//查询操作返回 ResultSet
statement.execute();//执行任何SQL
statement.executeUpdate();//更行、插入、删除。返回一个受影响的行数
ResultSet 查询结果集对象 ：封装了所有的查询结果
获得指定的数据类型
resultSet.getObject();//在不知道列类型的情况下使用
//如果指定列类型，就使用指定的类型
resultSet.getFloat();
resultSet.getInt();
遍历，指针
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行
释放资源
//6.释放连接
resultSet.close();
statement.close();
connection.close();//耗资源 用完关掉
10.4 statement 对象
CRUD 操作 -create
使用executeUpdate(String sql)方法完成数据添加操作，示例操作
Statement st = connection.createStatement();
String sql = "insert into use(...) values(...)";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("插入成功！！");
}
CRUD 操作 -delete
使用executeUpdate(String sql)方法完成数据删除操作，示例操作
Statement st = connection.createStatement();
String sql = "delete from user where id=1";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("删除成功！！");
}
CRUD 操作 -read
使用executeQuery(String sql)方法完成数据查询操作，示例操作
Statement st = connection.createStatement();
String sql = "select * from user where id=1";
ResultSet rs = st.executeQuery(sql);
while(rs.next){
//根据获取列的数据类型，分别调用rs的相应方法映射到java对象中
}
代码实现
- 工具类实现
package com.demo.lesson02.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
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");
username = properties.getProperty("username");
url = properties.getProperty("url");
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 conn, Statement st, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
- 测试插入实现
package com.demo.lesson02;
import com.demo.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) {
Connection conn= null;
Statement st = null;
ResultSet rs =null;
try{
conn = JdbcUtils.getConnection();//获取数据库连接
st = conn.createStatement();//获取sql的执行对象
String sql ="INSERT INTO users(id,NAME,`PASSWORD`,`email`,`birthday`)\n" +
"VALUES(4,'zhangsan','123456','zs@sina.com','1980-12-04')";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("插入成功！！");
}
} catch (SQLException e){
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
SQL注入问题
SQL注入：不是利用操作系统的BUG来实现攻击，而是针对程序员编写时的疏忽，通过SQL语句，实现无账号登录，甚至篡改数据库。
SQL语句会被拼接
login(username:" 'or '1=1",password:" 'or'1=1")//技巧
SELECT * FROM user_table WHERE `username`=''or '1=1' and `password` =''or'1=1';
10.5 PreparedStatement对象
PreparedStatement 对象可以防止sql注入，并且效率更高！
PreparedStatement 防止SQL注入的本质是把传递进来的参数当作字符。
假设其中存在转义字符，比如说 ‘ 会直接被转义
package com.demo.lesson03;
import com.demo.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestInsert {
public static void main(String[] args) {
Connection conn =null;
PreparedStatement st= null;
//PreparedStatement 防止SQL注入的本质是把传递进来的参数当作字符。
//假设其中存在转义字符，比如 ' 会直接被转义
try{
conn = JdbcUtils.getConnection();
//区别
//使用？占位符代替参数
String sql= "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
st=conn.prepareStatement(sql);//预编译SQL，先写sql，然后不执行
//手动给参数赋值
st.setInt(1,6);
st.setString(2,"qinjiang");
st.setString(3,"123456125");
st.setString(4,"45411896587@qq.com");
//注意点：sql.Date 数据库 java.sql.Date()
// util.Date java new Date().getTime() 获得时间戳
st.setString(5,"1875-05-12");
//执行
int i =st.executeUpdate();
if(i>0){
System.out.println("插入成功！");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
10.6 事务
要么都成功，要么都失败
ACID原则
原子性，要么全部完成 要么都不完成
一致性，总数不变
隔离性，多个进程互不干扰
持久性，一旦提交不可逆，持久化到数据库了
隔离性(导致的问题)
脏读：一个事务读取了另一个没有提交的事务
不可重复读：在同一个事务内，重复读取表中的数据，表数据发生了改变
幻读（虚读），在一个事务内，读取了别人插入的数据，导致前后读出来结果不一致
代码实现
1.开启事务
conn.setAutoCommit(false);
2.一组业务执行完毕，提交事务
3.可以在catch语句中显示定义 回滚语句，但默认失败就会回滚
10.6 数据库连接池
数据库连接–执行完毕–释放
连接–释放 十分浪费系统资源
池化技术：准备一些预先的资源，过来就连接预先准备好的
常用连接数、最小连接数、最大连接数、排队等待、等待超时
编写连接池，实现一个接口 DataSource
开源数据源实现 DBCP C3PO Druid（阿里）
- 使用数据库连接池一会，在项目开发种就不需要编写连接数据库的代码了
//创建数据源 工厂模式 --> 创建
DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
