数据库学习记录（四）

最新推荐文章于 2024-08-12 23:11:19 发布

淺い空

最新推荐文章于 2024-08-12 23:11:19 发布

阅读量199

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_44914651/article/details/105163943

版权

数据库设计

多表之间的关系
1. 分类
  1. 一对一
    - 如：人和身份证
    - 分析：一个人只有一个身份证，一个身份证只能对应一个人
  2. 一对多(多对一)
    - 如：部门和员工
    - 分析：一个部门多个员工，，一个员工只能对应一个部门
  3. 多对多：
    - 如：学生课程
    - 分析：一个学生可以选择很多们课程，一个课程也可以被多个学生选择
2. 实现关系：
  1. 一对多(多对一)
    - 实现方式：在多的一方建立外键，指向一方的主键
  2. 多对多：
    - 实现方式：需要借助第三张中间表，中间表至少包含两个字段，这两个字段作为第三章标的外键，分别指向两张表的主键
  3. 一对一
    - 实现方式：可以在任意一方添加唯一外键指定另一方主键
3. 案例
数据库设计的范式
- 分类
  1. 第一范式(1NF)：每一列都是不可分割的原子数据项
  2. 第二范式(2NF)：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
  3. 第三范式(3NF)：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

数据库的备份与还原

命令行
1. 备份
```
mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
```
2. 还原
3. 登陆数据库
4. 创建数据库
5. 使用数据库
6. 执行文件 source
图形化工具

多表查询

多表查询的分类

内连接查询

隐式内连接

SELECT
       t1.`NAME`, -- 员工表的姓名
       t1.gender, -- 员工表的性别
       t2.`NAME` -- 部门表的名称
FROM
       emp t1, 
       dept t2
WHERE
       t1.dept_id = t2.id;

显式内连接

select 字段列表 from 表名1 [inner] join 表名2 on 条件;

注意事项
1. 从哪些表查数据
2. 条件是什么
3. 查询那些字段

外连接查询

左外连接

select 字段列表 from 表名1 left [outer] join 表名2 on 条件;

保留左边悬浮元组

右外链接

select 字段列表 from 表名1 right [outer] join 表名2 on 条件;

保留右边悬浮元组

子查询

概念：查询中嵌套查询

   -- 查询工资最高的员工
   
   -- 1. 查询最高的工资是多少 9000
   SELECT MAX(salary) FROM emp;
   
   -- 查询员工信息，并且工资等于9000
   SELECT * FROM emp WHERE emp.salary = 9000;
   
   -- 一条sql完成操作
   SELECT * FROM emp WHERE emp.salary =(SELECT MAX(salary) FROM emp);

子查询的结果是单行单列的

子查询可以作为条件，使用运算符判断

-- 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(emp.salary) FROM emp);

子查询结果是多行单列的

子查询可以作为条件,可以使用运算符 IN来判断啊

-- 查询财务部的所有员工信息
SELECT * FROM emp WHERE emp.dept_id IN (SELECT id FROM dept WHERE dept.`NAME` = '财务部');

子查询的结果是多行多列的

子查询可以做一张虚拟表

SELECT * FROM dept t1,(SELECT * FROM emp WHERE emp.join_date > '2011-11-11')  t2 WHERE t1.id = t2.dept_id;

练习

      -- 部门表
      CREATE TABLE dept (
        id INT PRIMARY KEY PRIMARY KEY, -- 部门id
        dname VARCHAR(50), -- 部门名称
        loc VARCHAR(50) -- 部门所在地
      );
      -- 职务表，职务名称，职务描述
      CREATE TABLE job (
        id INT PRIMARY KEY,
        jname VARCHAR(20),
        description VARCHAR(50)
      );
      -- 员工表
      CREATE TABLE emp (
        id INT PRIMARY KEY, -- 员工id
        ename VARCHAR(50), -- 员工姓名
        job_id INT, -- 职务id
        mgr INT , -- 上级领导
        joindate DATE, -- 入职日期
        salary DECIMAL(7,2), -- 工资
        bonus DECIMAL(7,2), -- 奖金
        dept_id INT, -- 所在部门编号
        CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
        CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
      );
      -- 工资等级表
      CREATE TABLE salarygrade (
        grade INT PRIMARY KEY,   -- 级别
        losalary INT,  -- 最低工资
        hisalary INT -- 最高工资
      );
      -- 需求：
      -- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
      SELECT emp.id,ename,salary,jname,description FROM emp, job WHERE emp.job_id = job.id;
      -- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
      SELECT emp.id,ename,salary,jname,description,dept.dname,dept.loc FROM emp, job, dept WHERE emp.job_id = job.id AND dept.id = emp.dept_id;
      -- 3.查询员工姓名，工资，工资等级
      SELECT ename,salary,grade FROM emp,salarygrade WHERE salary BETWEEN losalary AND hisalary;
      -- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
      SELECT ename,salary,jname,description,dept.dname,dept.loc,grade 
      FROM emp, job, dept, salarygrade 
      WHERE emp.job_id = job.id AND dept.id = emp.dept_id AND salary BETWEEN losalary AND hisalary;
      -- 5.查询出部门编号、部门名称、部门位置、部门人数
      SELECT dept.id,dept.dname,dept.loc,COUNT(dept.dname) FROM emp, dept WHERE emp.dept_id = dept.id GROUP BY dept.dname;
      -- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
      SELECT DISTINCT t1.ename 姓名,t2.ename 领导 FROM emp t1 LEFT JOIN emp t2 ON t2.id = t1.mgr;

事务

事务的基本特征

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

操作

开启事务： start transaction;
回滚：rollback;

提交：commit;

-- 创建数据表 
CREATE TABLE account ( 
id INT PRIMARY KEY AUTO_INCREMENT, 
NAME VARCHAR ( 10 ), 
balance DOUBLE 
);
-- 添加数据 
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
UPDATE account SET balance = 1000;

SELECT * FROM account;

   -- 张三给李四转账 500 元
   -- 0. 开启事务
   START TRANSACTION;
   -- 1. 张三账户 -500
   UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
   -- 2. 李四账户 +500 
UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';

   -- 执行没有问题，提交
COMMIT;

   -- 发现问题，回滚事务
   ROLLBACK;

MySQL数据库事务默认提交
- 事务提交的两种方式
  - 自动提交：
    - MySQL就是自动提交的
    - 一条DML(增删改)语句会自动提交一次事务
  - 手动提交：
    - Oracle默认手动提交
    - 需要开启事务在提交
- 修改事务的默认提交方式：
  - 查看事务默认提交方式：
```
SELECT @@autocommit; -- 1 代表自动提交，2 代表手动提交
```
  - 修改事务默认提交方式：
```
SET @@autocommit = 0;
```

事务的四大特征
1. 原子性：是不可分割的最小操作单位，要么同事成功，要么同时是失败
2. 持久性：如果事务一旦提交和回滚后，数据库会持久化的保存数据
3. 隔离性：多个事务，相互独立
4. 一致性：事务操作前后，数据总量不变
事务的隔离级别
- 概念：多个食物之间隔离的，相互独立的。但是如果多个事务操作同意批数据，则会引发一些问题。设置不同的隔离级别就可以结局这些问题。
- 存在问题：
  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 级别字符串;
```

淺い空

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库学习记录（四）

数据库设计多表之间的关系分类一对一如：人和身份证分析：一个人只有一个身份证，一个身份证只能对应一个人一对多(多对一)如：部门和员工分析：一个部门多个员工，，一个员工只能对应一个部门多对多：如：学生课程分析：一个学生可以选择很多们课程，一个课程也可以被多个学生选择实现关系：一对多(多对一)实现方式：在多的一方建立外键，指向一方的主键多...
复制链接

扫一扫