這篇文章為大家帶來了關於視訊教學的相關知識，其中主要介紹了關於資料庫設計概念的相關問題，包括了設計簡介、多表查詢、事務操作等等內容，下面一起來看看吧，希望對大家有幫助。 推薦學習：mysql影片教學 #資料庫設計簡介 ##1.資料庫設計概念 資料庫設計就是根據業務系統具體需求，結合我們所選用的DBMS，為這個業務系統建構出最優的資料儲存模型。 建立資料庫中的表格結構以及表與表之間的關聯關係的過程。 有哪些表？表裡有哪些欄位？表和表之間有什麼關係？ 2.資料庫設計步驟 #需求分析：資料庫是什麼？數據具體有哪些屬性？數據與屬性的特徵是什麼？ 邏輯分析：透過ER圖對資料庫進行邏輯建模，不需要考慮我們所選用的資料庫管理系統。 物理設計：根據資料庫本身的特性把邏輯設計轉換成物理設計。 維護設計：對新的需求進行建表和對錶的最佳化。 3.表格關係簡介 #在真實的開發中，一個專案中的數據，一般都會保存在同一個資料庫中，但是不同的資料需要保存在不同的資料表中。這時不能把所有的資料都保存在同一張表中。 那麼在設計保存資料的資料表時，我們就要根據具體的資料進行分析，然後把同一類資料保存在同一張表中，不同的資料進行分錶處理。 資料之間必然會有一定的聯繫，我們把不同的資料保存在不同的資料表中之後，同時還要在資料表中維護這些資料之間的關係。這時就會導致表和表之間必然會有一定的關聯。這時要求設計表的人員，就需要考慮不同表之間的具體關係。 在資料庫中，表總共有三種關係，真實的資料表之間的關係：多對多關係、一對多（多對一）、一對一(極少)，(一對一關係就是我們先前學習的Map集合的key-value關係)表關係（多對多） 1.多對多 如：訂單和商品一個商品對應多個訂單，一個訂單對應多個商品 實作方式：建立第三張中間表，中間表至少包含兩個外鍵，分別關聯兩方主鍵 #說明：如果兩張表是多對多的關係，需要建立第三張表，並在第三張表中增加兩列，引入其他兩張表的主鍵作為自己的外鍵。 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 外鍵約束名稱foreign key(目前表的欄位名稱) references 主表(主鍵) 3.有了外鍵約束操作資料注意事項? 要求新增資料需要先新增主表，然後再新增從表。 要求刪除資料需要先刪除從表，然後再刪除主表。 表格關係（一對多） 一對多（多對一）# 如：部门表 和 员工表 一个部门对应多个员工，一个员工对应一个部门 实现方式：在多的一方建立外键，指向一的一方的主键 表关系之一对一 一对一 如：用户和 用户信息 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能 实现方式：在任意一方加入外键，关联另一方主键，并且设置外键为唯一(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） means that once a transaction is committed, its changes to the database are permanent. Even if an exception occurs in the database, the data will still exist after restarting Atomicity refers to a group of sql statements (a set of business logic) packaged by a transaction as an indivisible unit of work. Either all operations in the transaction occur or none occur Consistency Consistency means that the data is in a semantically meaningful and correct state; Transaction consistency means that the result of transaction execution must be such that the data Change from one consistency state to another consistency state. The success or failure of the transaction, the final data in the database is Business logic that conforms to real life. Consistency relies overwhelmingly on business logic and atomicityThree questions caused by concurrent access to transactions (interview)Transactions in operation The ideal state: multiple transactions do not affect each other. If the isolation level is not set properly, it may cause concurrent access problems Concurrent access problems Meaning Dirty readA transaction has read data that has not been committed in another transaction. The most serious thing is to prevent it from happening. Non-repeatable readingThe data content read twice in one transaction is inconsistent. What is required is when reading multiple times in one transaction The data is inconsistent. This is a problem caused by transactionupdate Phantom reading (virtual reading) Something else is read in a transaction The transaction inserts or deletes data, resulting in a different number of record rows read before and after. This is a problem caused when insert or delete 1. Dirty read: refers to one transaction reading uncommitted data of another transaction data. (Very dangerous) #2. Non-repeatable read: Read the data in the table multiple times within a transaction, and the results of multiple reads are different. 3. Phantom reading (virtual reading): One transaction reads data inserted or deleted by another transaction, resulting in a different number of record rows read before and after 4. SummaryDirty reading: When one transaction reads data that another transaction has not submitted, it must be avoided. Non-repeatable reading: A transaction reads multiple times with different data contents, mainly update statements. The transaction has been submitted. It can happen. Phantom reading (virtual reading): A transaction reads multiple times with different numbers, mainly delete or insert statements. The transaction has been submitted. It can happen. Isolation level of transactions Through the above problem demonstration, we found that if we do not consider the isolation of transactions, we will encounter dirty reads, non-repeatable reads, virtual reads, etc. question. Therefore, we need to solve the above three problems in the database. The MySQL database specification stipulates 4 isolation levels, which are used to describe all situations in which two transactions are concurrent. Thing isolation level The level above is the lowest and the level below is the highest. Yes means that this problem will occur, No means that this problem will not occur. LevelNameIsolation levelDirty readNon-repeatable readPhantom readingDatabase default isolation level 1 Read uncommitted read uncommitted是是是 ##2 Read committed read committed No Yes Yes Oracle and SQL Server 3 Repeatable read repeatable read No No Yes MySQL 4 Serializable serializable No No no Security and performance comparison ##Security: Serialization>Repeatable Read>Read Committed>Read Uncommitted Performance: Serialization In fact, among the three problems,The most serious thing is dirty reading (wrong data is read), this problem must be avoided;About non-repeatable reading and virtual reading are not actually logical errors , but a timeliness problem of the data, so this problem is not a very serious error;If the requirements for data timeliness are not very high, we can Accept the occurrence of non-repeatable reads and virtual reads Recommended learning: mysql video tutorial
