この記事では、設計の導入、複数テーブルのクエリ、トランザクション操作など、データベースの設計概念に関する関連問題を主に紹介する ビデオ チュートリアル に関する関連知識を提供します。見てみましょう。以下、皆さんのお役に立てれば幸いです。 #推奨学習: mysql ビデオ チュートリアルデータベース設計の概要 1. データベース設計概念 #データベース設計はビジネス システムの特定のニーズに基づいており、選択した DBMS と組み合わせて、このビジネス システムに最適なデータ ストレージ モデルを構築します。 データベース内のテーブル構造とテーブル間の関連付けを確立するプロセス。 どのようなテーブルがありますか?テーブルにはどのようなフィールドがありますか?テーブル間の関係は何ですか? #2. データベース設計の手順 要件分析: データベースとは何ですか?データには具体的にどのような属性があるのでしょうか?データと属性の特徴は何ですか? 論理分析: 選択したデータベース管理システムを考慮せずに、ER 図を使用してデータベースを論理モデリングします。 物理設計: データベース自体の特性に従って、論理設計を物理設計に変換します。 メンテナンス設計: テーブルを作成し、新しい要件に従ってテーブルを最適化します。 #3. テーブル リレーションシップの概要 #実際の開発では、プロジェクト内のデータは通常、次の場所に保存されます。同じデータベースであっても、異なるデータを異なるデータテーブルに保存する必要があります。現時点では、すべてのデータを同じテーブルに保存することはできません。 データを保存するデータテーブルを設計するときは、特定のデータに基づいて分析し、同じ種類のデータを同じテーブルに保存し、異なるデータを処理する必要があります。別々のテーブルに分割します。 データ間には特定の関係が必要です。異なるデータを異なるデータ テーブルに保存した後、データ テーブル内のこれらのデータ間の関係も維持する必要があります。これにより、テーブル間に特定の接続が確立されます。現時点では、テーブルを設計する人は、異なるテーブル間の特定の関係を考慮する必要があります。 データベースには、テーブル間に合計 3 つのリレーションシップがあります。実際のデータ テーブル間のリレーションシップ: 多対多のリレーションシップ、1 つ-to-many (多対 1)、1 対 1 (まれに)、(1 対 1 の関係は、前に学習した Map コレクションのキーと値の関係です)テーブル リレーションシップ (多対多) 1. 多対多 例: order と products 1 つの製品は複数の注文に対応し、1 つの注文は複数の製品に対応します 実装方法: 3 番目の 中間テーブル を作成します。中間テーブルには少なくとも 2 つの外部キー が含まれます。 これは 2 つのパーティの主キーに関連付けられています注: 2 つのテーブルに多対多のリレーションシップがある場合は、 3 番目のテーブルに 2 つの列を追加し、他の 2 つのテーブルの主キーを独自の外部キーとして導入します。 #2. 外部キー制約 外部キーは、2 つのテーブルのデータ間のリンクを確立し、整合性を確保するために使用されます。データの一貫性と整合性 (たとえば、上記の多対多の注文製品テーブルは、注文テーブルと製品テーブルの間の関係を維持するために使用されます) between を使用する目的テーブルは、2 つのテーブル間の多対多の関係を維持します。 関係: 中間テーブルに挿入されたデータは、多対多のメイン テーブルに存在する必要があります。メインテーブルは中間テーブル内の関係を維持しており、それらを自由に削除することはできません。削除する場合は、まず中間テーブルに関連付けられたデータを削除する必要があります 3. 外部キー制約の構文-- 关键字解释：constraint: 添加约束，可以不写foreign key(当前表中的列名): 将某个字段作为外键references 被引用表名(被引用表的列名) : 外键引用主表的主键-- 创建表时添加外键约束CREATE TABLE 表名( 列名 数据类型, … [CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名) ); -- 建完表后添加外键约束ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);-- 删除约束ALTER TABLE 表名 DROP FOREIGN KEY 外键名称; 4.外部キー制約の作成 -- 订单表CREATE TABLE tb_orders( id int primary key auto_increment, payment double(10, 2), payment_type TINYINT, -- 0 微信支付 1 支付宝支付 status TINYINT -- 0 未付款 1 已经支付);-- 商品表CREATE TABLE tb_goods( id int primary key auto_increment, title varchar(100), price double(10, 2));-- 订单商品中间表CREATE TABLE tb_order_goods( id int primary key auto_increment, order_id int, -- 外键，来自于订单表的主键 goods_id int, -- 外键，来自于商品表的主键 count int, -- 购买商品数量 foreign key(order_id) references tb_orders(id), foreign key(goods_id) references tb_goods(id)); 5.外部キーカスケード 主キーを変更・削除する場合メインテーブルの、テーブルの外部キー値を同時に更新または削除することをカスケード操作といいます。 ON UPDATE CASCADE – カスケード更新、主キーが更新されると、外部キーも更新されますON DELETE CASCADE – カスケード削除。主キーが削除されると、外部キーも削除されます 6. 概要 1. 外部キー制約を引用符で囲む必要があるのはなぜですか? テーブル内のデータを有効かつ正確なものにします。クエリ効率を向上させます。 2. 外部キー制約構文を追加しますか? constraint 外部キー 制約名 外部キー(現在のテーブルのフィールド名)がメインテーブル(主キー)を参照 3. でデータを操作する場合の注意点外部キー制約? データを追加するには、最初にマスター テーブルを追加し、次にスレーブ テーブルを追加する必要があります。 データを削除するには、まずスレーブ テーブルを削除し、次にマスター テーブルを削除する必要があります。 テーブル リレーションシップ (1 対多) 1 対多 (多対 1) 如：部门表 和 员工表 一个部门对应多个员工，一个员工对应一个部门 实现方式：在多的一方建立外键，指向一的一方的主键 表关系之一对一 一对一 如：用户和 用户信息 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能 实现方式：在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE) 多表查询 准备数据 -- 价格create table price( id int primary key auto_increment, price double);-- 水果 create table fruit( id int primary key auto_increment, name varchar(20) not null, price_id int, foreign key (price_id) references price (id));-- 数据insert into pricevalues (1, 2.30);insert into pricevalues (2, 3.50);insert into pricevalues (4, null);insert into fruitvalues (1, '苹果', 1);insert into fruitvalues (2, '橘子', 2);insert into fruitvalues (3, '香蕉', null); 笛卡尔积现象 1.什么是笛卡尔积现象 笛卡尔积问题：把多张表放在一起，同时去查询，会得到一个结果，而这结果并不是我们想要的数据，这个结果称为笛卡尔积。 笛卡尔积缺点：查询到的结果冗余了，里面有很多错误的数据，需要过滤。 多表查询语法：select * from 表名1,表名2; 需求：查询两张表中关于水果的信息，要显示水果名称和水果价格 表设计原则：将价格的主键作为水果的外键 -- 多表查询语法(同时查询多个表获取到需要的数据)select * from 表名1,表名2;-- 查询价格(我们向查询水果对应的价格,需要将水果表和价格表同时进行查询;)select * from fruit,price; 查询结果：2.笛卡尔积产生原因fruit表中的每一条记录，都和price表中的每一条进行匹配连接。所得到的最终结果是：fruit表中的条目数乘以price表中的数据的条目数。 将fruit表的每行记录和price表的每行记录组合的结果就是笛卡尔积 3.如何避免笛卡尔积 解决上述查询的方案：在查询两张表的同时添加条件进行过滤，比如fruit表的id和必须和price表的id相同 -- 条件过滤笛卡尔积select * from fruit,price where fruit.price_id=price.id; 内连接查询 1.什么是内连接 内连接查询又称为交集查询，也就是查询只显示满足条件的数据 2.显示内连接 显示内连接：使用INNER JOIN...ON语句，可以省略INNER关键字 -- 语法核心select * from 表名1 inner join 表名2 on 条件;-- 或者select * from 表名1 join 表名2 on 条件; 3.隐式内连接 看不到JOIN关键字，条件使用WHERE指定 select 列名,列名,... from 表名1,表名2 where 表名1.列名=表名2.列名; 4.示例 查询水果的价格 -- 隐式内连接select * from fruit,price where fruit.price_id=price.id;-- 显式内连接select * from fruit inner join price on fruit.price_id=price.id; 查询苹果的信息，显示苹果的id，名字，价格 -- 方式1select fruit.id, fruit.name, price.pricefrom fruit, pricewhere fruit.price_id = price.id and fruit.name = '苹果';-- 方式2select fruit.id, fruit.name, price.pricefrom fruit inner join price on fruit.price_id = price.id and fruit.name = '苹果'; 5.总结 1.内连接作用？ 过滤笛卡尔积 获取两表的交集部分(都满足条件的部分) 2.什么是隐式内连接和显示内连接？ 隐式内连接：看不到JOIN：select 列名,列名....from 表名1,表名2 where 表名1.列名=表名2.列名; 显示内连接：看得到JOIN：select * from 表名1 inner join 表名2 on 条件; 3.内连接查询步骤？ 1）确定查询几张表 2）确定表连接条件 3）根据需要在操作 外连接查询 1.左外连接 左表的记录全部显示出来 外表只会显示符合搜索条件的记录 语法格式： select * from 表1 left [outer] join 表2 on 条件; 说明： left关键字左边的表定义为左表，left关键字右边的表定义为右表，查询的内容以左表为主 如果左表有数据，而右表没有数据对应的数据，仍然会把左表数据进行显示 outer关键字可以省略 练习： 不管能否查到水果对应价格，都要把水果显示出来 -- 左外连接查询select * from fruit left outer join price on fruit.price_id=price.id; 2.右外连接 右表的记录全部表示出来 左表只会显示符合搜索条件的记录 语法格式： select * from 表名1 right [outer] join 表名2 on 条件; 说明： right关键字左边的表定义为左表，right关键字右边的表定义为右表，查询的内容以右表为主 如果右表没有数据，而左表没有对应的数据，仍然会把右表数据进行显示 outer关键字可以省略 练习： 不管能否查到价格对应的水果，都要把价格显示出来 select * from fruit right outer join price on fruit.price_id=price.id; 总结： 1.掌握左外连接查询格式？ select * from 表1 left outer join 表2 on 条件; 表1看作为左表，表2看做为右表 2.左外连接查询特点？ 在满足要求的基础上保证左表的数据全部显示 3.掌握右外连接查询格式？ select * from 表1 right outer join 表2 on 条件; 4.右外连接查询特点？ 在满足要求的基础上,保证右表的数据全部显示 嵌套查询（子查询） 1.什么是子查询 一条查询语句结果作为另一条查询语法一部分。 SELECT 查询字段 FROM 表 WHERE 条件;举例：SELECT * FROM employee WHERE salary=(SELECT MAX(salary) FROM employee); 说明：子查询需要放在（）中 三种子查询情况：单行单列、多行单列、多行多列。 2.单行单列 子查询结果是单列，在WHERE后面作为条件 SELECT 查询字段 FROM 表 WHERE 字段=（子查询）; 通常使用比较运算符: > 、>= 、、、=等 3.多行单列 子查询结果是多行单列，结果集类似于一个数组，在WHERE后面作为条件，父查询使用IN运算符 -- IN表示在数值中SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）; 4.多行多列 子查询结果是多列，在FROM后面作为表 SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件; 注意：子查询作为表需要取别名，使用as，可以省略，否则这张表没用名称无法访问表中的字段 事务操作 事务的概念 什么是事务 在实际的业务开发中，有些业务操作要多次访问数据库。一个业务要发送多条SQL语句给数据库执行。需要将多次访问数据库的操作视为一个整体来执行，要么所有的SQL语句全部执行成功。如果其中有一条SQL语句失败，就进行事务的回滚，所有的SQL语句全部执行失败。 简而言之，事务指的是逻辑上的一组操作,组成这组操作的各个单元要么全都成功,要么全都失败。 事务作用：保证在一个事务中多次操作数据库表中数据时，要么全都成功,要么全都失败。 事务的应用场景声明 关于事务在实际中的应用场景： 假设我在淘宝买了一部手机，然后当我付完款，钱已经从我的账户中扣除。正当此时，淘宝转账系统宕机了，那么此时淘宝还没有收到钱，而我的账户的钱已经减少了，这样就会导致我作为买家钱已经付过，而卖家还没有收到钱，他们不会发货物给我。这样做显然是不合理。实际生活中是如果淘宝出问题，作为用户的账户中钱是不应该减少的。这样用户就不会损失钱。 还有种情况，就是当我付完款之后，卖家看到我付款成功，然后直接发货了，我如果有权限操作，我可以撤销，这样就会导致我的钱没有减少，但是卖家已经发货，同样这种问题在实际生活中也是不允许出现的。 MySQL中可以有两种方式进行事务的操作： 手动提交事物：先开启，在提交 自动提交事物（默认的）：即执行一条sql语句提交一次事物 数据准备 # 创建账号表create table account( id int primary key auto_increment, name varchar(20), money double);# 初始化数据insert into account values (null,'a',1000);insert into account values (null,'b',1000); 手动提交事务 手动提交事务有关的sql语句 SQL语句 描述 start transaction 开启手动控制事物 commit 提交事物 rollback 回滚事物 手动提交事务使用步骤 开启事务–>执行SQL语句–>成功–>提交事务 开启事务–>执行SQL语句–>失败–>回滚事务 演示案例：演示提交事务，a给b转账100元 -- 1.开启事务start transaction;-- 2.执行sql语句update account set money=money-100 where name='a';update account set money=money+100 where name='b';-- 3.提交事务commit; 案例演示：演示回滚事务，a给b转账100元 -- 1.开启事务start transaction;-- 2.执行sql语句update account set money=money-100 where name='a';update account set money=money+100 where name='b';-- 3.回滚事务rollback; 注意： 提交事务(commit) ：事务提交之后，sql语句对数据库产生的操作才会被永久的保存 事务的回滚(rollback)：撤销已经成功执行的sql语句，回到开启事务之前的状态 只要提交事务，那么数据就会长久保存了，就不能回滚事务了。即提交或者回滚事务都是代表结束当前事务的操作 自动提交事务 MySQL的每一条DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，执行完毕自动提交事务，MySQL默认开始自动提交事务。自动提交，通过修改mysql全局变量autocommit进行控制。 通过以下命令可以查看当前autocommit模式 show variables like '%commit%'; 设置自动提交的参数为OFF set autocommit = 0; -- 0:OFF 1:ON 案例演示 -- 自动提交事务：每条sql语句就是一个事务，那么执行一条sql语句就会提交一次事务-- mysql数据库就是自动提交事务-- a给b转账100元update account set money=money-100 where name='a';update account set money=money+100 where name='b';-- 查看mysql是否自动提交事务-- autocommit的值是on表示自动提交事务，值是off表示关闭自动提交事务show variables like '%commit%';-- 我们可以使用命令临时设置mysql变为手动提交事务，即将自动提交事务关闭-- 下次重新连接mysql依然是自动提交事务set autocommit = 0; -- 0 表示关闭自动提交事务 1表示开启自动事务update account set money=money-100 where name='a' 注意： 1）MySql默认自动提交。即执行一条sql语句提交一次事务。 2）设置autocommit为off状态，只是临时性的，下次重新连接mysql，autocommit依然变为on状态。 3）如果设置autocommit为off状态，那么当我们执行一条sql语句，就不会自动提交事务，重新启动可视化工具，数据并没有改变。 4）如果设置autocommit为on状态，如果我们先执行start transaction 然后在执行修改数据库的语句： update account set money = money-100 where name='a'; update account set money = money+100 where name='b'; 那么此时就表示上述修改数据库的sql语句都在同一个事务中，此时必须手动提交事务，即commit； 换句话说，如果我们手动开启事务start transaction那么此时mysql就不会自动提交事务，必须手动提交事务。 5）如果设置autocommit为on状态，如果我们不执行start transaction 直接执行修改数据库的语句： update account set money = money-100 where name='a';update account set money = money+100 where name='b'; 那么此时mysql就会自动提交事务，即上述每条sql语句就是一个事务 事务原理和四大特征 事务原理 原理说明 一个用户登录成功以后，服务器会创建一个临时日志文件。日志文件用来保存用户事务状态。 如果没有使用事务，则所有的操作直接写到数据库中，不会使用日志文件。 如果开启事务，将所有的写操作写到日志文件中。 如果这时用户提交了事务，则将日志文件中所有的操作写到数据库中。 如果用户回滚事务，则日志文件会被清空，不会影响到数据库的操作。 事务的四大特征 事务的四大特征（ACID） 数据库的事务必须具备ACID特征，ACID是指Atomicity（原子性）、Consistensy（一致性）、Isolation（隔离性）和Durabiliyt（持久性） 隔离性（Isolation） 多个用户并发的访问数据库时，一个用户的事务不能被其他用户的事物干扰，多个并发的事务之间相互隔离持久性（Durability） 指一個事務一旦被提交，它對資料庫的改變是永久性的，哪怕資料庫發生異常，重啟之後資料依然會存在原子性（Atomicity） 指交易包裝的一組sql語句（一組業務邏輯）是一個不可分割的工作單位，事務中的操作要么都發生，要么都不發生 一致性（Consistency） 一致性是指資料處於一種語意上有意義且正確的狀態； 交易一致性是指交易執行的結果必須是使數據從一個一致性狀態變成另一個一致性狀態。 事務的成功與失敗，最終資料庫的資料都是符合實際生活的業務邏輯。一致性絕大多數依賴業務邏輯和原子性 事務的並發存取引發的三個問題(面試) 事務在操作時的理想狀態：多個交易之間互不影響，如果隔離等級設定不當就可能引發並發存取問題 並發存取的問題 意義 髒讀 一個交易讀取到了另一個事務中尚未提交的資料。最嚴重，杜絕發生。 不可重複讀取 一個事務中兩次讀取的資料內容不一致，要求的是一個事務中多次讀取時資料是不一致的，這是交易update時引發的問題 #幻讀(虛讀) 一個事務內讀取到了別的事務插入或刪除的資料，導致前後讀取記錄行數不同。這是insert或delete時引發的問題 #1.髒讀：指一個事務讀取了另外一個事務未提交的數據。 (非常危險)2.不可重複讀取：在一個事務內多次讀取表中的數據，多次讀取的結果不同。 3.幻讀（虛讀）:一個事務內讀取到了別的事務插入或刪除的數據，導致前後讀取記錄行數不同 4.總結 贓物讀：一個事務讀取另一個事務還沒有提交的資料,一定避免。 不可重複讀取：一個交易讀取多次資料內容不一樣，主要是update語句。事務已經提交了。可以發生的。 幻讀（虛讀）：一個交易讀取多次數量不一樣，主要是delete或insert語句。事務已經提交了。可以發生的。 交易的隔離等級 透過上述問題演示，我們發現如果不考慮交易的隔離性，會遇到髒讀、不可重複讀取和虛讀等問題。所以在資料庫中我們要對上述三種問題進行解決。 MySQL資料庫規格規定了4種隔離級別，分別用於描述兩個事務並發的所有情況。 事物隔離等級 上面的等級最低，下面的等級最高。 是表示會出現這種問題，否表示不會出現這種問題。 等級 名字 隔離等級 髒讀 無法重複讀取 幻讀 資料庫預設隔離等級 #1 讀取未提交 read uncommitted是是 是 #2讀取已提交read committed否是是 Oracle和SQL Server 3可重複讀取repeatable read#否否##是 MySQL 4 序列化 serializable 否 否 否 #######安全性與效能比較 安全性: 序列化>可重複讀取>讀已提交>讀未提交 效能: 序列化 其實三個問題中，最嚴重的就是髒讀（讀取了錯誤資料），這個問題一定要避免； 關於不可重複讀和虛讀其實並不是邏輯上的錯誤 ，而是資料的時效性問題，所以這種問題並不屬於很嚴重的錯誤； 如果對於資料的時效性要求不是很高的情況下，我們是可以接受不可重複讀取和虛讀的情況發生的 推薦學習：mysql影片教學
