分割线

用户/权限管理

基本操作

/*
用户信息表:mysql.user
*/

-- 刷新权限
FLUSH PRIVILEGES;

/*
增加用户
必须拥有mysql数据库的全局CREATE USER权限,或拥有INSERT权限。
用户名/密码注意加引号,如 'user_name'@'192.168.1.1'
*/
CREATE USER weidows IDENTIFIED BY '123456';

-- 重命名用户
RENAME USER weidows TO weidows2;

-- 删除用户
DROP USER weidows2;

-- 设置密码
-- 为当前用户设置密码
SET PASSWORD = PASSWORD ('密码');
-- 为指定用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD ('密码');
-- 上面两种报错的话
ALTER USER root@localhost IDENTIFIED BY '密码';

/*
授予权限
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
all privileges 表示所有权限
*.* 表示所有库的所有表
库名.表名 表示某库下面的某表
只有root用户拥有grant权限,其他用户无法开启.
*/
GRANT ALL PRIVILEGES ON *.* TO weidows IDENTIFIED BY '123456';

-- 查看权限
SHOW GRANTS FOR root@localhost;
-- 查看当前用户权限
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

/**
撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名
*/
REVOKE ALL PRIVILEGES ON *.* FROM weidows;

权限解释

  • (查询用,不用硬记)
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] ...

导出&导入

  • 可以用 SQL GUI 程序操作,下面介绍命令行操作

  • 导出

    • 可以加上-w 携带备份条件
    • -xxx 后面不加空格,直接带上参数

    1. 导出一张表 mysqldump -u 用户名 -p 密码 库名 表名 > 文件名(D:/a.sql)
    mysqldump -uroot -p123456 school student >D:/a.sql
    1. 导出多张表 mysqldump -u 用户名 -p 密码 库名 表 1 表 2 表 3 > 文件名(D:/a.sql)
    mysqldump -uroot -p123456 school student result >D:/a.sql
    1. 导出所有表 mysqldump -u 用户名 -p 密码 库名 > 文件名(D:/a.sql)
    mysqldump -uroot -p123456 school >D:/a.sql
    1. 导出一个库 mysqldump -u 用户名 -p 密码 -B 库名 > 文件名(D:/a.sql)
    mysqldump -uroot -p123456 -B school >D:/a.sql

  • 导入

    1. 在已经登录 mysql,切换到指定数据库的情况下: source 备份文件
    source D:/a.sql
    1. 在不登录的情况下: mysql -u 用户名 -p 密码 [库名] < 备份文件 (如果导入一个库的话就不加库名)
    mysql -uroot -p123456 school <D:/a.sql

分割线

设计数据库

  • 目的:

    1. 节省数据存储空间
    2. 保证数据的完整性
    3. 方便进行数据库应用系统的开发

  • 阶段设计

    1. 需求分析: 分析客户的业务和数据处理需求

    2. 概要设计: 设计数据库的 E-R 模型图 , 确认需求信息的正确和完整.

    3. 收集信息: 搜集与需求相关的信息需求.

      1. 页面 A
      2. 功能 B
      3. 特殊需求 C
      4. 自定义 D
    4. 标识实体: 标识数据库所要管理的对象,详细信息(Attribute)以及关系(Relationship)

      1. 写博客 user -> blog
      2. 分类 blog -> category
      3. 评论 user(作者) -> user(评论者) -> blog(博客页面)

三大范式

数据库设计的三大范式通俗讲解

  • 三大范式出现为了解决一下四种问题

    • 信息重复

    • 更新异常

    • 插入异常

    • 删除异常


第一范式

确保每列的原子性,每列都是不可再分的最小数据单元

20210317202650
  • 上面家庭信息和学校信息都可以再拆分为两列

第二范式

必须在满足第一范式的条件下: 每个表只做一件事(每行有唯一标识,其他数据只依赖于此标识列)

20210317203230
  • 简单来讲就是标识列和其他列有共同重复数据行,那么标识列有重复数据的列可以分到另一个表

    20210317203601

第三范式

在第二范式基础上,其他列数据必须与标识列数据有直接关系

20210317204105
  • 也就是说不存在标识列与其他列共同重复了,但还是可能存在多个其他列有共同重复(班主任的那三列有共同重复数据)

  • 把班主任分出去

    20210317204341

性能考量

关联查询不能超过三张表.

  • 实际应用中需要考虑多表的额外开销

  • 如果不规范程度低,而且性能需求高的场景,则需要考虑是否遵循范式.

分割线

JDBC

HelloWorld

20210319102812
  • 工具类 (Java 自带)

    • java.sql
    • javax.sql
  • SQL 驱动需要导入(在 pom.xml 中)

    • mysql-connector-java-版本号 jar
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.23</version>
    </dependency>

  • 创建数据库

    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`(id, NAME, PASSWORD, email, birthday)
    VALUES (1, 'zhansan', '123456', '[email protected]', '1980-12-04'),
    (2, 'lisi', '123456', '[email protected]', '1981-12-04'),
    (3, 'wangwu', '123456', '[email protected]', '1979-12-04');
  • 连接,helloworld

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;

    import com.mysql.cj.jdbc.Driver;

    public class JDBCFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
    /**
    * 1.设置用户信息
    useUnicode = true 支持中文编码
    characterEncoding = utf8 设定字符集
    useSSL = true 安全连接
    */
    String url = "jdbc:mysql://localhost:3306/jdbcStudy?useUnicode = true & characterEncoding = utf8 & useSSL = true";
    String user = "root";
    String password = "123456";

    //2.加载驱动(固定写法,二选一)
    // Class.forName("com.mysql.cj.jdbc.Driver");
    DriverManager.registerDriver(new Driver());

    /**
    * 3.连接,数据库对象 Connection 代表数据库
    //事务提交
    connection.commit();
    //事务回滚
    connection.rollback();
    //数据库设置自动提交
    connection.setAutoCommit(true);
    */
    Connection connection = DriverManager.getConnection(url, user, password);

    //4.创建SQL的对象
    Statement statement = connection.createStatement();
    String sql = "SELECT * FROM users";

    //5.执行SQL的对象,查看返回结果
    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"));
    System.out.println("===============================================================");
    }

    //6.释放连接
    resultSet.close();
    statement.close();
    connection.close();
    }

    }

  • 执行结果

    id = 1
    name = zhansan
    password = 123456
    email = [email protected]
    birthday = 1980-12-04
    ===============================================================
    id = 2
    name = lisi
    password = 123456
    email = [email protected]
    birthday = 1981-12-04
    ===============================================================
    id = 3
    name = wangwu
    password = 123456
    email = [email protected]
    birthday = 1979-12-04
    ===============================================================

分割线

对象操作

  • connection

    • connection.rollback();
    • connection.commit();
    • connection.setAutoCommit();

  • statement

    • statement.executeQuery(); 执行查询 SQL
    • statement.execute(); 可以执行任何 SQL 指令
    • statement.executeUpdate(); 更新,插入,删除,返回受影响的行数
    • statement.executeBatch(); 执行一组 SQL

  • ResultSet

    • resultSet.getObject(); 在不知道列类型下使用
    • resultSet.getString(); 如果知道则指定使用
    • resultSet.getInt();

    • resultSet.next(); 移动到下一个
    • resultSet.previous(); 移动到上一个
    • resultSet.beforeFirst(); 移动到最前
    • resultSet.afterLast(); 移动到最后
    • resultSet.absolute(row); 移动到指定行

解耦案例

  • 上面的 JDBC 包括加载连接,业务逻辑,状态转化都写到一个类中,导致扩展时很不方便,需要 解耦

  • 把加载连接分离到 db.properties

    driver=com.mysql.cj.jdbc.Driver
    url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode = true & characterEncoding = utf8 & useSSL = true
    username=root
    password=123456

Utils

  • 把 SQL connect 和 release 分离到 utils.JDBCUtils

    import java.io.FileInputStream;
    import java.io.IOException;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    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 {
    // 加载连接信息
    Properties properties = new Properties();
    try {
    properties.load(new FileInputStream("Java/src/main/java/twenty_one/jdbc/demo2/db.properties"));

    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 releaseConnection(Connection connection,Statement statement,ResultSet resultSet) throws SQLException {
    if (resultSet != null) {
    resultSet.close();
    }
    if (statement != null) {
    statement.close();
    }
    if (connection != null) {
    connection.close();
    }
    }
    }

CRUD

  • 业务类: 测试增删改查(CRUD)

    • 增删改用.executeUpdate(), 查用.executeQuery()
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;

    import twenty_one.jdbc.demo2.utils.JDBCUtils;

    public class Test {
    private static Connection connection = null;
    private static Statement statement = null;
    private static ResultSet resultSet = null;

    public static void main(String[] args) throws SQLException {
    // 连接
    connection = JDBCUtils.getConnection();
    statement = connection.createStatement();

    // 增
    String insert = "INSERT INTO jdbcstudy.users(NAME, PASSWORD, email, birthday) VALUES ('weidows', 123456, '[email protected]', '2020-07-28');";
    if (statement.executeUpdate(insert) > 0) { // 返回int:受影响的行数
    System.out.println("插入成功. insert -> weidows");
    }

    // 改
    String update = "update jdbcstudy.users set password =654321 where name = 'weidows';";
    if (statement.executeUpdate(update) > 0) {
    System.out.println("修改成功. weidows.password -> 654321");
    }

    // 查
    String query = "SELECT * From jdbcstudy.users;";
    resultSet = statement.executeQuery(query);
    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"));
    System.out.println("===============================================================");
    }

    // 删
    String delete = "delete from jdbcstudy.users where name = 'weidows';";
    if (statement.executeUpdate(delete) > 0) {
    System.out.println("删除成功.");
    }

    // 断连
    JDBCUtils.releaseConnection(connection, statement, resultSet);
    }
    }

  • 执行结果

    插入成功. insert -> weidows
    修改成功. weidows.password -> 654321
    id = 1
    name = zhansan
    password = 123456
    email = [email protected]
    birthday = 1980-12-04
    ===============================================================
    id = 2
    name = lisi
    password = 123456
    email = [email protected]
    birthday = 1981-12-04
    ===============================================================
    id = 3
    name = wangwu
    password = 123456
    email = [email protected]
    birthday = 1979-12-04
    ===============================================================
    id = 9
    name = weidows
    password = 654321
    email = [email protected]
    birthday = 2020-07-28
    ===============================================================
    删除成功.