权限管理和备份
用户管理
-
SQL yog 可视化管理
-
SQL 命令操作
用户表:mysql.user
本质对这张表进行增删改查
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER fengzi IDENTIFIED BY '123456'
-- 修改密码(修改当前用户密码,当前登录的用)
SET PASSWORD = PASSWORD('123456')
-- 修改密码(修改指定用户密码)
SET PASSWORD FOR fengzi = PASSWORD('111111')
-- 给用户重命名 RENAME USER 原来的名字 TO 新名字
RENAME USER fengzi TO jianghu
-- 用户授权 GRANT ALL PRIVILEGES 授予全部的权限,哪个库.哪个表
-- ALL PRIVILEGES 除了给别人授权,其它都能够干
GRANT ALL PRIVILEGES ON *.* TO jiagnhu -- 所有的库和所有的表
-- 查询权限
SHOW GRANTS FOR jianghu -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost
-- root用户权限:GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-- 撤销权限 revoke 哪些权限,在哪个库撤销,给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM jiagnhu
-- 删除用户
DROP USER jianghu
MySQL备份
为什么要备份:
- 保证重要的数据不丢失
- 数据转移
MySQL数据库备份的方式
-
直接拷贝物理文件
-
在SQLyog这种可视化工具中手动导出
- 在想要导出的表或者库中,右键,选择备份或导出
-
使用命名行导出 mysqldump 命令行使用
# mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
# mysqldump -h主机 -u用户名 -p密码 数据库 表1 表2 表3 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/b.sql
# mysqldump -h主机 -u用户名 -p密码 数据库 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/c.sql
#导入
#登录的情况下,切换到指定的数据库
#source 备份的文件
source d:/a.sql
mysql -u用户名 -p密码 库名<备份文件
假设你要备份数据库,防止数据丢失
把数据库给朋友时。
规范数据库的设计
为什么需要设计‘
当数据库比较复杂的时候,我们就需要设计了
糟糕的数据库设计:
- 数据冗余,浪费空间
- 有物理外键时,数据库的插入删除都会麻烦,异常(屏蔽使用物理外键)
- 程序的性能差
良好的数据库设计:
- 节省内存空间
- 保证数据的完整性
- 方便我们开发系统
软件开发中,关于数据库的设计
- 分析需求:分析业务和需要处理的数据库的需求
- 概要设计:设计关系E-R图
设计数据库的步骤:(以个人博客为例)
- 收集信息,分析需求
- 用户表(用户登陆注销,用户的个人信息,写博客,创建分类)
- 分类表(文章分类,谁创建的)
- 评论表
- 文章表(文章的信息)
- 友情连接表(友情链接信息)
- 标识实体(把需求落地到每个字段)
- 标识实体之间的关系
- 写博客:user–>blog
- 创建分类:user–>category
- 关注:user–>user
- 友情链接表:links
- 评论:user–user–blog
三大范式
- 为什么需要数据规范化?
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效的信息
三大范式
第一范式(1NF)
- 原子性:要求数据库表的每一列都是不可分割的原子数据项
第二范式(2NF)
- 前提:满足第一范式。
- 第二范式需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。
每张表只能描述一件事情。
第三范式(3NF)
-
前提:满足第一范式 和 第二范式
-
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能简介相关。
(规范数据库的设计)
规范性 和 性能的问题
- 想要满足规范的话,性能就不一定高。
- 关联查询的表不得超过三张表
- 考虑商业化的需求和目标,(成本 和 用户的体验问题)。这时数据库的性能更加重要。
- 在规范性能的问题的时候,需要适当的考虑一下 规范性!
- 有时需要故意给某些表增加一些冗余的字段。(使得从多表查询变为单表查询)
- 故意增加一些计算列(从大数据量降低为小数据量的查询)
JDBC(重点)
数据库驱动
- 应用程序不能和数据库直接挂钩,所以需要数据库驱动(不同的数据库有不同的数据库驱动,由数据库厂商提供)来将二者进行连接。
- 程序会通过数据库驱动,和数据库打交道!
JDBC
- 如果开发时所用到的数据库很多,就会有很多的数据库驱动,相应的就要写多种程序,就带来很多不便。
- 所以 SUN 公司 为了简化开发人员的(对数据库统一)操作,就提供了一个(Java操作数据库的)规范,俗称 JDBC。这些规范的具体实现由具体的厂商去做!
- 对于开发人员来说,我们只需要掌握 JDBC 接口的操作即可!
知道两个包
java.sql
javax.sql
还需要导入一个数据库驱动包 mysql-connector-java-5.1.47.jar
第一个JDBC 程序
创建所用测试数据库
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci
USE jdbcStudy
CREATE TABLE users(
id INT PRIMARY KEY,
`name` VARCHAR(40),
`password` VARCHAR(40),
email VARCHAR(60),
birthday DATE
);
INSERT INTO users
VALUES(1,'zhangsan','123456','zs@sina.com','2000-06-22'),
(2,'lisi','123456','lisi@sina.com','1999-05-02'),
(3,'wangwu','123456','wangwu@sina.com','1998-08-07');
-
创建一个普通项目
-
导入数据库驱动
- 编写测试代码
package com.feng.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
//useUnicode=true&characterEncoding=utf8&useSSL=true
String url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "123456";
//3.连接成功,数据库对象
Connection connection = DriverManager.getConnection(url, username, password);
//4.执行SQL的对象
Statement statement = connection.createStatement();
//5.执行SQL的对象 去 执行SQL,可能存在结果
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了我们查询出来的全部结果。
while (resultSet.next()){
System.out.print("id=" + resultSet.getObject("id")+"\t");
System.out.print("name=" + resultSet.getObject("name")+"\t");
System.out.print("pwd=" + resultSet.getObject("password")+"\t");
System.out.print("email=" + resultSet.getObject("email")+"\t");
System.out.print("birth=" + resultSet.getObject("birthday")+"\n");
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结:
- 加载驱动
- 连接数据库 DriverManager
- 获得执行sql的对象
- 获得返回的结果集
- 释放连接
DriverManager
// DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");//固定写法,加载驱动
Connection connection = DriverManager.getConnection(url, username, password);
//connection 代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.setAutoCommit();
connection.commit();
connection.rollback();
url
String url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
//mysql 默认端口号 3306
//jdbc:mysql://主机地址:端口号/数据库?参数1&参数2&参数3
//协议://主机地址:端口号/数据库?参数1&参数2&参数3
//oralce 默认端口号 1521
//jdbc:oralce:think:@localhost:1521:sid
Statement 执行SQL的对象 PerpareStatement 执行SQL的对象
String sql = "SELECT * FROM users"; //编写SQL
statement.executeQuery(); //查询操作返回 ResultSet
statement.execute(); //执行任何SQL
statement.executeUpdate() //更新,插入,删除,都是用这个,返回一个受影响的行数
ResultSet 查询的结果集:封装了所有的查询结果
获得指定的数据类型
resultSet.getObject(); //在不知道列类型的情况下使用
//如果知道列的类型就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
....
遍历,指针
resultSet.beforeFirst(); //移动到最前面resultSet.afterLast(); //移动到最后面resultSet.next(); //移动到下一个数据resultSet.previous(); //移动到前一行resultSet.absolute(row); //移动到指定行
释放资源
//6.释放连接resultSet.close();statement.close();connection.close(); //耗资源,用完关掉
statement对象
- jdbc中的statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。
- Statement对象的executeUpdate方法,用于向数据库发送增,删,改的SQL语句,executeUpdate执行完后,将会返回一个整数(即增删改查语句导致了数据库几行数据发生了变化)。
- Statement.executeQuery方法用于向数据库发送查询语句,executeQuery方法返回代表查询结果的ReaultSet对象。
CRUD操作-create
使用executeUpdate(String sql)方法完成数据添加操作,示例操作:
Statement st =conn.creareStatement();
String sql = "insert into user(...) values(...)";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("插入成功!!!");
}
CRUD操作-delete
使用executeUpdate(String sql)方法完成数据删除操作,示例操作:
Statement st =conn.creareStatement();
String sql = "delete from user where id=1";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("删除成功!!!");
}
CRUD操作-update
使用executeUpdate(String sql)方法完成数据修改操作,示例操作:
Statement st =conn.creareStatement();
String sql = "update user set name='' where name=''";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("修改成功!!!");
}
CRUD操作-read
使用executeQuery(String sql)方法完成数据查询操作,示例操作:
Statement st =conn.creareStatement();
String sql = "select *from user where id=1";
ResultSet rs = st.executeUpdate(sql);
while(rs.next()){
//根据获取的数据类型,分别调用rs的相应方法映射到Java对象中
}
代码实现
- 提取工具类
package com.feng.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");
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 throwables) {
throwables.printStackTrace();
}
}
if (statement!=null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection!=null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
- 编写增删改的方法,executeUpdate
增:
package com.feng.lesson02;
import com.feng.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 connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();//获取数据库联接
statement = connection.createStatement();//获得SQL的执行对象
String sql = "INSERT INTO users(id,`name`,`password`,`email`,`birthday`)VALUES(6,'zhan','123456','z2615s@sina.com','2000-06-22')";
int i = statement.executeUpdate(sql);
if (i>0){
System.out.println("插入成功!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
删:
package com.feng.lesson02;
import com.feng.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
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 = "DELETE FROM users WHERE id = 6";
int i = statement.executeUpdate(sql);
if (i>0){
System.out.println("删除成功!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
改:
package com.feng.lesson02;
import com.feng.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestUpdate {
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 = "UPDATE users SET `name`='haha',`email`='1545451@qq.com' WHERE id =1";
int i = statement.executeUpdate(sql);
if (i>0){
System.out.println("更新成功!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
查询:
package com.feng.lesson02;
import com.feng.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
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 where id=2";
resultSet = statement.executeQuery(sql);
if (resultSet.next()){
System.out.println(resultSet.getString("name"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
SQL注入的问题
-
sql存在漏洞,会被攻击导致数据泄露 sql会被拼接
-
SELECT * FROM users WHERE
name
= ‘lisi’ ANDpassword
= ‘123456’ -
SELECT * FROM users WHERE
name
= ‘’ or ‘1=1’ ANDpassword
= ‘’ or ‘1=1’
package com.feng.lesson02;
import com.feng.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQL注入 {
public static void main(String[] args) {
//login("haha","123456");
login("'or '1=1","'or'1=1");//通过拼接字符串改变,sql语句
}
//登录业务
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
//SELECT * FROM users WHERE `name` = 'lisi' AND `password` = '123456'
//SELECT * FROM users WHERE `name` = '' or '1=1' AND `password` = '' or '1=1'
String sql = "select * from users where name='"+username+"' AND password='"+password+"'";
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println(resultSet.getString("name"));
System.out.println(resultSet.getString("password"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}