Loading...

Java EE入门（五）——MySQL基础 （多表关系、范式、多表查询、事务、DCL）

Java EE入门（五）——MySQL基础 （多表关系、范式、多表查询、事务、DCL）
　　iwehdio的博客园：https://www.cnblogs.com/iwehdio/

导图

1、多表关系

  • 多表之间的关系：

    1. 一对一的关系。
    2. 一对多 或 多对一 的关系。
    3. 多对多的关系。

  • 一对多关系实现：在多的一方建立外键列，指向一的一方的主键。

  • 多对多关系实现：需要借助第三张中间表，中间表中至少包含两个字段，作为外键分别指向两张表的主键。这两个字段构成了联合主键。

  • 一对一关系实现：在任意一方添加外键列，指向另一方的主键。而且需要在外键列添加唯一约束。

  • 分析示例：用户收藏旅游线路。

    1. 三个实体：旅游线路分类、旅游线路、用户。

    2. 旅游线路分类和旅游线路是 一对多 的关系。

    3. 用户和收藏的旅游线路是 多对多 的关系。

    4. 实现：

      CREATE TABLE category(
	cid INT PRIMARY KEY AUTO_INCREMENT,
	cname VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE route(
	rid INT PRIMARY KEY AUTO_INCREMENT,
	rname VARCHAR(100) NOT NULL UNIQUE,
	CONSTRAINT r_c FOREIGN KEY (rid) REFERENCES category(cid)
);

CREATE TABLE users(
	uid INT PRIMARY KEY AUTO_INCREMENT,
	uname VARCHAR(100)
);

CREATE TABLE favorite(
	rid INT,
	uid INT,
	PRIMARY KEY(rid,uid),	# 添加联合主键
	CONSTRAINT f_r FOREIGN KEY (rid) REFERENCES route(rid),
	CONSTRAINT f_u FOREIGN KEY (uid) REFERENCES users(uid)
);

2、范式

  • 数据库设计的范式：设计数据库时，所需要遵循的规范。

    • 目前关系数据库有六种范式，各个范式呈递次规范。即要遵循某个的范式，必须先遵循前边的范式。主要的是前三种范式。

    • 第一范式：每一列都是不可分割的原子数据项。

      可能存在一些问题：

      • 存在严重的数据冗余、重复。
      • 数据添加可能存在问题。
      • 数据删除可能会将将相关的数据都受到影响。

    • 第二范式：在第一范式的基础上，非码属性必须完全依赖于码（在第一范式的基础上消除非主属性对主码的部分函数依赖）。

      • 函数依赖：如果通过A属性（属性组，即多个属性的组合）的值，可以唯一确定B属性的值，则称B依赖于A。
      • 完全函数依赖：如果A是一个属性组，则B属性值的确定需要依赖于A属性组中的所有属性值。
      • 部分函数依赖：如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中的部分属性值。
      • 传递函数依赖：如果B依赖于A，C依赖于B，则称C传递函数依赖于A。
      • 码：如果在一张表中，一个属性或属性组，被其他所有属性或属性组依赖，则称其为这张表的码。
        • 主属性：码属性组中的所有属性。
        • 非主属性：除了码属性组中的属性。

      可能存在一些问题：

      • 解决了数据的冗余、重复问题。
      • 没有解决数据添加和删除的问题。

    • 第三范式：在第二范式的基础上，任何非主属性不依赖于其他非主属性（在第二范式的基础上消除传递依赖）。

      • 解决了数据添加和删除的问题。

  • 使用三个范式的示例：学生成绩表。

    • 原始表分为，列：学号、姓名、系、课程名称和分数。其中系列下分为系名和系主任。
    • 应用第一范式：将系列分割为列系名和系主任。
    • 应用第二范式：
      • 原表中，主属性组是 学号和课程名称，分数同时依赖于主属性组，但姓名、系名和系主任只部分依赖于主属性组中的学号。
      • 将原表分为选课表和学生表。选课表包括列：学号、课程名称和分数。主属性组是学号和课程名称，分数完全依赖于主属性组。学生表包括列：学号、姓名、系名和系主任。主属性是学号，其他非主属性完全依赖于主属性。
    • 应用第三范式：
      • 在学生表中，系主任对学号是传递依赖。
      • 将原学生表分为学生表和系表。系表包括列：系名和系主任。系名为主属性，系主任完全依赖于系名。

  • 数据库的备份与还原：

    • 命令行备份：mysqldump -u用户名 -p密码 数据库名 > 保存的路径。
    • 命令行还原：
      1. 登录数据库。
      2. 创建数据库。
      3. 使用数据库。
      4. 读入备份文件：source 备份路径 。

3、多表查询

  • 从多张表中查询数据：select * from 表1,表2。得到的是表1和表2中内容的笛卡尔积（所有排列组合）。完成多表查询需要消除笛卡尔积中无用的数据。

  • 多表查询的分类：

    1. 内连接查询。
    2. 外连接查询。
    3. 子查询。

  • 内连接查询：

    • 隐式内连接：使用 where 条件，查询满足条件的数据。
      • select 列名列表 from 表1,表2 where 条件字段。
      • 使用 表名.列名 的方式，获取不同表中的列。表名也可以用别名替代。
    • 显式内连接：
      • select 列名列表 from 表名1 inner join 表2 on 条件字段。
      • inner 关键字可以省略。
    • 内连接查询步骤：
      1. 确定所要查询的表。
      2. 确定查询的条件。
      3. 查询哪些字段（列）。
    • 内连接查询的是两个表的交集部分，

  • 外连接查询：

    • 左外连接：
      • select 列名列表 from 表1 left outer join 表2 on 条件字段。
      • outer 关键字可省略。
      • 左外连接查询的是左表（表1）的所有数据以及其（与表2）交集部分。
    • 右外连接：
      • select 列名列表 from 表1 right outer join 表2 on 条件字段。
      • 右外连接查询的是右表（表2）的所有数据以及其（与表1）交集部分。

  • 子查询：

    • 查询中嵌套查询，称嵌套查询为子查询。
    • 子查询语句需要被括号（）括起来。

    子查询的不同情况：

    • 子查询的结果是单行单列的：
      • 子查询的结果可以作为条件，使用单值判断运算符 >, <, = 做判断。
    • 子查询的结果是多行单列的：
      • 子查询的结果可以作为条件，使用关系判断运算符 in 做判断。
    • 子查询的结果是多行多列的：
      • 子查询到的结果可以作为一张虚拟表，作为被查询的对象。

4、事务

  • 事务：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

  • 事务中的操作：

    1. 开启事务：start transaction。在事务的所有步骤开始前开启。
    2. 回滚：rollback。如果有一个步骤出问题则回滚，将数据库还原到开启事务时的状态。
    3. 提交事务：commit。如果没有出现问题，则正常运行，提交事务。如果没有提交事务，数据只会被暂时更改，关闭进程后会默认回滚。

  • MySQL 数据库中事务默认自动提交，一条 DML（增删改） 语句会提交一次事务。如果是手动开启事务的，也必须手动提交。

  • 查看默认提交方式：select @@autocommit，1代表自动提交，0代表手动提交。set @@autocommit = 0，可更改为默认手动提交。

  • 事务的四大特征：

    • 原子性：不可分割的的最小操作单位，要么同时成功，要么同时失败。
    • 持久性：事务一旦提交或者回滚，数据库的会持久化保存数据。
    • 隔离性：多个事务之间，希望是相互独立的。
    • 一致性：事务操作前后，数据总量不变。

  • 事务的隔离级别：

    • 多个事务之间是隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的事务隔离级别可以解决这些问题。

    • 会存在那些问题：

      1. 脏读：一个事务，读取到了另一个事务中没有提交的数据。
      2. 不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样。
      3. 幻读：一个事务操作（DML）数据表中的所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

    • 隔离级别：

      1. read uncommitted：读未提交。可能会产生：脏读、不可重复读、幻读。
      2. read committed：读已提交。可能会产生：不可重复读、幻读。（Oracle默认）
      3. repeatable read：可重复读。可能会产生：幻读。（MySQL默认）
      4. serializable：串行化，相当于对表加锁，同一时间只能有一个事务对这张表进行操作或查询。不会出现任何问题。
      • 隔离级别从小到大安全性越来越高，但是效率越来越低。

    • 数据库查询隔离级别：select @@tx_isolation。

    • 数据库设置隔离级别：set global transaction isolation level 隔离级别。

5、DCL

  • 管理用户：

    • 查询用户：切换到 mysql 数据库，查询其中的 user 表。
    • 创建用户：create user '用户名'@'主机名' identified by '密码'。
      • localhost 表示本地主机，% （通配符）表示任意远程主机。
    • 删除用户：drop user '用户名'@'主机名' 。
    • 修改密码：update user set password = password('密码') where user='用户名' 或 set password for '用户名'@'主机名'=password('密码')。
    • 修改 root 用户密码：
      1. 在 cmd 中停止 mysql 服务：net stop mysql（管理员权限）。
      2. 使用无验证方式启动 mysql 服务：mysqld --skip-grant-tables。
      3. 打开新的 cmd 窗口，输入 mysql 启动。
      4. 使用前述方式更改密码。
      5. 手动结束 mysqld 进程。

  • 权限管理：

    • 查询权限：show grants for '用户名'@'主机名'。

    • 授予权限：grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'。

      • 权限列表包括 select、delete、update 等。

      • 授予所有权限：grant all on *.* to '用户名'@'主机名'。

    • 撤销权限：revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名' 。


iwehdio的博客园：https://www.cnblogs.com/iwehdio/
posted @ 2020-02-19 11:08  iwehdio  阅读(224)  评论(0)  编辑  收藏  举报