🚀进修MySQL~(肆)

用户/权限管理
导出&导入
设计数据库
三大范式
JDBC

用户/权限管理

基本操作

/*
  用户信息表：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)
1
mysqldump -uroot -p123456 school student >D:/a.sql
1. 导出多张表 mysqldump -u 用户名 -p 密码库名表 1 表 2 表 3 > 文件名(D:/a.sql)
1
mysqldump -uroot -p123456 school student result >D:/a.sql
1. 导出所有表 mysqldump -u 用户名 -p 密码库名 > 文件名(D:/a.sql)
1
mysqldump -uroot -p123456 school >D:/a.sql
1. 导出一个库 mysqldump -u 用户名 -p 密码 -B 库名 > 文件名(D:/a.sql)
1
mysqldump -uroot -p123456 -B school >D:/a.sql

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

设计数据库

目的:
1. 节省数据存储空间
2. 保证数据的完整性
3. 方便进行数据库应用系统的开发
阶段设计
1. 需求分析: 分析客户的业务和数据处理需求
2. 概要设计: 设计数据库的 E-R 模型图 , 确认需求信息的正确和完整.
3. 收集信息: 搜集与需求相关的信息需求.
  1. 页面 A
  2. 功能 B
  3. 特殊需求 C
  4. 自定义 D
  5. …
4. 标识实体: 标识数据库所要管理的对象,详细信息(Attribute)以及关系(Relationship)
  1. 写博客 user -> blog
  2. 分类 blog -> category
  3. 评论 user(作者) -> user(评论者) -> blog(博客页面)
  4. …

三大范式

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

三大范式出现为了解决一下四种问题
- 信息重复
- 更新异常
- 插入异常
- 删除异常

第一范式

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

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

第二范式

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

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

第三范式

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

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

性能考量

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

实际应用中需要考虑多表的额外开销
如果不规范程度低,而且性能需求高的场景,则需要考虑是否遵循范式.

JDBC

HelloWorld

工具类 (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', 'zs@sina.com', '1980-12-04'),
      (2, 'lisi', '123456', 'lisi@sina.com', '1981-12-04'),
      (3, 'wangwu', '123456', 'wangwu@sina.com', '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 = zs@sina.com
birthday = 1980-12-04
===============================================================
id = 2
name = lisi
password = 123456
email = lisi@sina.com
birthday = 1981-12-04
===============================================================
id = 3
name = wangwu
password = 123456
email = wangwu@sina.com
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, 'utsuko27@qq.com', '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 = zs@sina.com
birthday = 1980-12-04
===============================================================
id = 2
name = lisi
password = 123456
email = lisi@sina.com
birthday = 1981-12-04
===============================================================
id = 3
name = wangwu
password = 123456
email = wangwu@sina.com
birthday = 1979-12-04
===============================================================
id = 9
name = weidows
password = 654321
email = utsuko27@qq.com
birthday = 2020-07-28
===============================================================
删除成功.

🚀进修MySQL~(肆)

用户/权限管理

基本操作

权限解释

表维护

导出&导入

设计数据库

三大范式

第一范式

第二范式

第三范式

性能考量

JDBC

HelloWorld

对象操作

解耦案例

Utils

CRUD

-> 欢迎,锟斤拷! <-

低情商：代码一坨屎
高情商：兼容性极强

用户/权限管理

基本操作

权限解释

表维护

导出&导入

设计数据库

三大范式

第一范式

第二范式

第三范式

性能考量

JDBC

HelloWorld

对象操作

解耦案例

Utils

CRUD

-> 欢迎,锟斤拷! <-

低情商：代码一坨屎高情商：兼容性极强

低情商：代码一坨屎
高情商：兼容性极强