数据库高级

一，子查询与表连接

首先准备测试用的表结构及数据：（将代码复制到txt文件，修改后缀名为sql并保存，在navicat 中新建数据库，右击将要创建表的数据库，选择运行sql文件，右击【表 / 新建的数据库】刷新即可看到新建的表）

  1 /*
  2  Navicat Premium Data Transfer
  3  Source Server         : Mysql
  4  Source Server Type    : MySQL
  5  Source Server Version : 50728
  6  Source Host           : localhost:3306
  7  Source Schema         : chuange
  8  Target Server Type    : MySQL
  9  Target Server Version : 50728
 10  File Encoding         : 65001
 11  Date: 04/06/2020 10:52:20
 12 */
 13  
 14 SET NAMES utf8mb4;
 15 SET FOREIGN_KEY_CHECKS = 0;
 16  
 17 -- ----------------------------
 18 -- Table structure for customers
 19 -- ----------------------------
 20 DROP TABLE IF EXISTS `customers`;
 21 CREATE TABLE `customers` (
 22   `cust_id` int(11) NOT NULL AUTO_INCREMENT,
 23   `cust_name` char(50) NOT NULL,
 24   `cust_address` char(50) DEFAULT NULL,
 25   `cust_city` char(50) DEFAULT NULL,
 26   `cust_state` char(5) DEFAULT NULL,
 27   `cust_zip` char(10) DEFAULT NULL,
 28   `cust_country` char(50) DEFAULT NULL,
 29   `cust_contact` char(50) DEFAULT NULL,
 30   `cust_email` char(255) DEFAULT NULL,
 31   PRIMARY KEY (`cust_id`) USING BTREE
 32 ) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
 33  
 34 -- ----------------------------
 35 -- Records of customers
 36 -- ----------------------------
 37 BEGIN;
 38 INSERT INTO `customers` VALUES (10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
 39 INSERT INTO `customers` VALUES (10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse', NULL);
 40 INSERT INTO `customers` VALUES (10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
 41 INSERT INTO `customers` VALUES (10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
 42 INSERT INTO `customers` VALUES (10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd', NULL);
 43 COMMIT;
 44  
 45 -- ----------------------------
 46 -- Table structure for orderitems
 47 -- ----------------------------
 48 DROP TABLE IF EXISTS `orderitems`;
 49 CREATE TABLE `orderitems` (
 50   `order_num` int(11) NOT NULL,
 51   `order_item` int(11) NOT NULL,
 52   `prod_id` char(10) NOT NULL,
 53   `quantity` int(11) NOT NULL,
 54   `item_price` decimal(8,2) NOT NULL,
 55   PRIMARY KEY (`order_num`,`order_item`) USING BTREE,
 56   KEY `fk_orderitems_products` (`prod_id`) USING BTREE,
 57   CONSTRAINT `fk_orderitems_orders` FOREIGN KEY (`order_num`) REFERENCES `orders` (`order_num`),
 58   CONSTRAINT `fk_orderitems_products` FOREIGN KEY (`prod_id`) REFERENCES `products` (`prod_id`)
 59 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
 60  
 61 -- ----------------------------
 62 -- Records of orderitems
 63 -- ----------------------------
 64 BEGIN;
 65 INSERT INTO `orderitems` VALUES (20005, 1, 'ANV01', 10, 5.99);
 66 INSERT INTO `orderitems` VALUES (20005, 2, 'ANV02', 3, 9.99);
 67 INSERT INTO `orderitems` VALUES (20005, 3, 'TNT2', 5, 10.00);
 68 INSERT INTO `orderitems` VALUES (20005, 4, 'FB', 1, 10.00);
 69 INSERT INTO `orderitems` VALUES (20006, 1, 'JP2000', 1, 55.00);
 70 INSERT INTO `orderitems` VALUES (20007, 1, 'TNT2', 100, 10.00);
 71 INSERT INTO `orderitems` VALUES (20008, 1, 'FC', 50, 2.50);
 72 INSERT INTO `orderitems` VALUES (20009, 1, 'FB', 1, 10.00);
 73 INSERT INTO `orderitems` VALUES (20009, 2, 'OL1', 1, 8.99);
 74 INSERT INTO `orderitems` VALUES (20009, 3, 'SLING', 1, 4.49);
 75 INSERT INTO `orderitems` VALUES (20009, 4, 'ANV03', 1, 14.99);
 76 COMMIT;
 77  
 78 -- ----------------------------
 79 -- Table structure for orders
 80 -- ----------------------------
 81 DROP TABLE IF EXISTS `orders`;
 82 CREATE TABLE `orders` (
 83   `order_num` int(11) NOT NULL AUTO_INCREMENT,
 84   `order_date` datetime DEFAULT NULL,
 85   `cust_id` int(11) NOT NULL,
 86   PRIMARY KEY (`order_num`) USING BTREE,
 87   KEY `fk_orders_customers` (`cust_id`) USING BTREE,
 88   CONSTRAINT `fk_orders_customers` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`)
 89 ) ENGINE=InnoDB AUTO_INCREMENT=20010 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
 90  
 91 -- ----------------------------
 92 -- Records of orders
 93 -- ----------------------------
 94 BEGIN;
 95 INSERT INTO `orders` VALUES (20005, '2005-09-01 00:00:00', 10001);
 96 INSERT INTO `orders` VALUES (20006, '2005-09-12 00:00:00', 10003);
 97 INSERT INTO `orders` VALUES (20007, '2005-09-30 00:00:00', 10004);
 98 INSERT INTO `orders` VALUES (20008, '2005-10-03 00:00:00', 10005);
 99 INSERT INTO `orders` VALUES (20009, '2005-10-08 00:00:00', 10001);
100 COMMIT;
101  
102 -- ----------------------------
103 -- Table structure for productnotes
104 -- ----------------------------
105 DROP TABLE IF EXISTS `productnotes`;
106 CREATE TABLE `productnotes` (
107   `note_id` int(11) NOT NULL AUTO_INCREMENT,
108   `prod_id` char(10) NOT NULL,
109   `note_date` datetime DEFAULT NULL,
110   `note_text` text,
111   PRIMARY KEY (`note_id`) USING BTREE,
112   FULLTEXT KEY `note_text` (`note_text`)
113 ) ENGINE=MyISAM AUTO_INCREMENT=115 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
114  
115 -- ----------------------------
116 -- Records of productnotes
117 -- ----------------------------
118 BEGIN;
119 INSERT INTO `productnotes` VALUES (101, 'TNT2', '2005-08-17 00:00:00', 'Customer complaint:\r\nSticks not individually wrapped, too easy to mistakenly detonate all at once.\r\nRecommend individual wrapping.');
120 INSERT INTO `productnotes` VALUES (102, 'OL1', '2005-08-18 00:00:00', 'Can shipped full, refills not available.\r\nNeed to order new can if refill needed.');
121 INSERT INTO `productnotes` VALUES (103, 'SAFE', '2005-08-18 00:00:00', 'Safe is combination locked, combination not provided with safe.\r\nThis is rarely a problem as safes are typically blown up or dropped by customers.');
122 INSERT INTO `productnotes` VALUES (104, 'FC', '2005-08-19 00:00:00', 'Quantity varies, sold by the sack load.\r\nAll guaranteed to be bright and orange, and suitable for use as rabbit bait.');
123 INSERT INTO `productnotes` VALUES (105, 'TNT2', '2005-08-20 00:00:00', 'Included fuses are short and have been known to detonate too quickly for some customers.\r\nLonger fuses are available (item FU1) and should be recommended.');
124 INSERT INTO `productnotes` VALUES (106, 'TNT2', '2005-08-22 00:00:00', 'Matches not included, recommend purchase of matches or detonator (item DTNTR).');
125 INSERT INTO `productnotes` VALUES (107, 'SAFE', '2005-08-23 00:00:00', 'Please note that no returns will be accepted if safe opened using explosives.');
126 INSERT INTO `productnotes` VALUES (108, 'ANV01', '2005-08-25 00:00:00', 'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.');
127 INSERT INTO `productnotes` VALUES (109, 'ANV03', '2005-09-01 00:00:00', 'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.');
128 INSERT INTO `productnotes` VALUES (110, 'FC', '2005-09-01 00:00:00', 'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.');
129 INSERT INTO `productnotes` VALUES (111, 'SLING', '2005-09-02 00:00:00', 'Shipped unassembled, requires common tools (including oversized hammer).');
130 INSERT INTO `productnotes` VALUES (112, 'SAFE', '2005-09-02 00:00:00', 'Customer complaint:\r\nCircular hole in safe floor can apparently be easily cut with handsaw.');
131 INSERT INTO `productnotes` VALUES (113, 'ANV01', '2005-09-05 00:00:00', 'Customer complaint:\r\nNot heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.');
132 INSERT INTO `productnotes` VALUES (114, 'SAFE', '2005-09-07 00:00:00', 'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.\r\nComment forwarded to vendor.');
133 COMMIT;
134  
135 -- ----------------------------
136 -- Table structure for products
137 -- ----------------------------
138 DROP TABLE IF EXISTS `products`;
139 CREATE TABLE `products` (
140   `prod_id` char(10) NOT NULL,
141   `vend_id` int(11) NOT NULL,
142   `prod_name` char(255) NOT NULL,
143   `prod_price` decimal(8,2) NOT NULL,
144   `prod_desc` text,
145   PRIMARY KEY (`prod_id`) USING BTREE,
146   KEY `fk_products_vendors` (`vend_id`) USING BTREE,
147   CONSTRAINT `fk_products_vendors` FOREIGN KEY (`vend_id`) REFERENCES `vendors` (`vend_id`)
148 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
149  
150 -- ----------------------------
151 -- Records of products
152 -- ----------------------------
153 BEGIN;
154 INSERT INTO `products` VALUES ('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
155 INSERT INTO `products` VALUES ('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
156 INSERT INTO `products` VALUES ('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
157 INSERT INTO `products` VALUES ('DTNTR', 1003, 'Detonator', 13.00, 'Detonator (plunger powered), fuses not included');
158 INSERT INTO `products` VALUES ('FB', 1003, 'Bird seed', 10.00, 'Large bag (suitable for road runners)');
159 INSERT INTO `products` VALUES ('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
160 INSERT INTO `products` VALUES ('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
161 INSERT INTO `products` VALUES ('JP1000', 1005, 'JetPack 1000', 35.00, 'JetPack 1000, intended for single use');
162 INSERT INTO `products` VALUES ('JP2000', 1005, 'JetPack 2000', 55.00, 'JetPack 2000, multi-use');
163 INSERT INTO `products` VALUES ('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
164 INSERT INTO `products` VALUES ('SAFE', 1003, 'Safe', 50.00, 'Safe with combination lock');
165 INSERT INTO `products` VALUES ('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
166 INSERT INTO `products` VALUES ('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
167 INSERT INTO `products` VALUES ('TNT2', 1003, 'TNT (5 sticks)', 10.00, 'TNT, red, pack of 10 sticks');
168 COMMIT;
169  
170 -- ----------------------------
171 -- Table structure for vendors
172 -- ----------------------------
173 DROP TABLE IF EXISTS `vendors`;
174 CREATE TABLE `vendors` (
175   `vend_id` int(11) NOT NULL AUTO_INCREMENT,
176   `vend_name` char(50) NOT NULL,
177   `vend_address` char(50) DEFAULT NULL,
178   `vend_city` char(50) DEFAULT NULL,
179   `vend_state` char(5) DEFAULT NULL,
180   `vend_zip` char(10) DEFAULT NULL,
181   `vend_country` char(50) DEFAULT NULL,
182   PRIMARY KEY (`vend_id`) USING BTREE
183 ) ENGINE=InnoDB AUTO_INCREMENT=1007 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
184  
185 -- ----------------------------
186 -- Records of vendors
187 -- ----------------------------
188 BEGIN;
189 INSERT INTO `vendors` VALUES (1001, 'Anvils R Us', '123 Main Street', 'Southfield', 'MI', '48075', 'USA');
190 INSERT INTO `vendors` VALUES (1002, 'LT Supplies', '500 Park Street', 'Anytown', 'OH', '44333', 'USA');
191 INSERT INTO `vendors` VALUES (1003, 'ACME', '555 High Street', 'Los Angeles', 'CA', '90046', 'USA');
192 INSERT INTO `vendors` VALUES (1004, 'Furball Inc.', '1000 5th Avenue', 'New York', 'NY', '11111', 'USA');
193 INSERT INTO `vendors` VALUES (1005, 'Jet Set', '42 Galaxy Road', 'London', NULL, 'N16 6PS', 'England');
194 INSERT INTO `vendors` VALUES (1006, 'Jouets Et Ours', '1 Rue Amusement', 'Paris', NULL, '45678', 'France');
195 COMMIT;
196  
197 SET FOREIGN_KEY_CHECKS = 1;

1，子查询（嵌套sql）

SELECT语句是SQL的查询。迄今为⽌我们所看到的所有SELECT语句都是简单查询，即从单个数据库表中检索数据的单条语句。

SQL还允许创建⼦查询（subquery），即嵌套在其他查询中的查询。

就是在一个sql当中，它的where条件来源于另外一个sql；
或者反过来理解，一个sql语句的结果，作为外层sql语句的条件。
2，利用子查询进行过滤

接下来将通过实例来理解子查询：

已知条件

订单存储在两个表中。对于包含订单号、客户ID、订单⽇期的每个订单，orders表存储⼀⾏。
各订单的物品存储在相关的orderitems表中。
orders表不存储客户信息。它只存储客户的ID。实际的客户信息存储在customers表中。
现在，假如需要列出订购物品TNT2的所有客户，应该怎样检索？

直观流程如下：

1，在orderitems表中找到prod_id为TNT2的订单编号order_num

SELECT order_num, prod_id FROM orderitems WHERE prod_id = 'TNT2'
运行，结果如下：

2，在订单表中找到order_num对应的顾客idcust_id

SELECT cust_id, order_num FROM orders WHERE order_num in(20005, 20007)

SELECT cust_id, order_num FROM orders WHERE order_num in(20005, 20007)

3，在顾客表中找到顾客id对应的顾客信息

SELECT cust_name, cust_contact, cust_id FROM customers WHERE cust_id in(10001, 10004)

可以把其中的WHERE⼦句转换为⼦查询⽽不是硬编码这些SQL返回的数据

 1 SELECT cust_id, cust_name
 2 FROM customers
 3 WHERE cust_id IN (
 4     SELECT cust_id
 5     FROM orders
 6     WHERE order_num IN (
 7         SELECT order_num 
 8         FROM orderitems 
 9         WHERE prod_id = 'TNT2' )
10     );

为了执⾏上述SELECT语句，MySQL实际上必须执⾏3条SELECT语句。
最⾥边的⼦查询返回订单号列表，此列表⽤于其外⾯的⼦查询的WHERE⼦句。
外⾯的⼦查询返回客户ID列表，此客户ID列表⽤于最外层查询的WHERE⼦句。
最外层查询确实返回所需的数据。
3，作为计算字段使用子查询

假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。

1，从customers表中获取用户列表

select cust_id,cust_name from customers

2，先获取一个用户在orders表中的订单数

select count(*) as orders_num from orders where cust_id = 10001;

3，考虑如何获取每个客户的订单数，对每个客户进行count函数的统计计算（即作为计算字段使用子查询）

select cust_id,cust_name,
(select count(*) from orders where orders.cust_id = customers.cust_id) as orders_num
from customers;

注意：这里计算字段使用子查询时，采用完全限定表名

二，表关系

1，关系表

假如有⼀个包含产品⽬录的数据库表，其中每种类别的物品占⼀⾏。

对于每种物品要存储的信息包括产品描述和价格，以及⽣产该产品的供应商信息。

现在，假如有由同⼀供应商⽣产的多种物品，那么在何处存储供应商信息（如，供应商名、地址、联系⽅法等）呢？

可以看出，若供应商相同，供应商之后的字段将会全部一致

相同数据出现多次决不是⼀件好事，此因素是关系数据库设计的基础。

关系表的设计就是要保证把信息分解成多个表，⼀类数据⼀个表。

各表通过某些常⽤的值（即关系设计中的关系（relational））互相关联。

在这个例⼦中，可建⽴两个表，⼀个存储供应商信息，另⼀个存储产品信息。

vendors表的主键⼜叫作products的外键，它将vendors表与products表关联，利⽤供应商ID能从vendors表中找出相应供应商的详细信息。这样做的好处如下：

供应商信息不重复，从⽽不浪费时间和空间；
如果供应商信息变动，可以只更新vendors表中的单个记录，相关表中的数据不⽤改动；
由于数据⽆重复，显然数据是⼀致的，这使得处理数据更简单
关系数据可以有效地存储和⽅便地处理。因此，关系数据库的可伸缩性远⽐⾮关系数据库要好。

2，一对一关系与外键

2.1 外键

表关系：表与表之间的关系

外键：

在一个表中，定义一个字段，这个字段中存储的数据是另外一张表中的主键
就是在一个表中的字段，代表着这个数据属于谁
了解：

外键实现的方式，有两种：物理外键、逻辑外键

物理外键：

就是在创建表时，就指定这个表中的字段是一个外键，并且强关联某个表中的某个字段
需要在定义字段时，使用sql语句来实现
缺点：删除当前表时需要先删除其依赖的表；由于强关系的存在，并发时效率会受到影响；
逻辑外键：推荐

就是在表中创建一个普通的字段，没有强关联关系，需要通过程序逻辑来实现
一对一：

就是在一个表中的数据，对应着另外一张表中的一个数据，只能有一个

举例：

员工表：

id，姓名、性别、年龄、籍贯、联系方式、学历、工龄、。。。。

由上面的一个表，拆分成两个表

上面的表关系就是一对一的表关系，通过详情表中的yid这个字段来标记员工表中的主键。

一个员工有着一个对应的详情信息，存储在详情表中，

在详情表中的数据，也只属于某一个员工。

3，一对多

在一个表中的一条数据对应着另外一个表中的多条数据

在一个表中的多条数据，对应着另外一张表中一个数据

4，多对多

举例：例如一本书，有多个标签，同时每一个标签下又对应多本书

三，表联结

1，where与join的用法

1.1 使用where进行表联结

如果数据存储在多个表中，怎样⽤单条SELECT语句检索出数据？

答案是使⽤联结。简单地说，联结是⼀种机制，⽤来在⼀条SELECT语句中关联表，因此称之为联结。

使⽤特殊的语法，可以联结多个表返回⼀组输出，联结在运⾏时关联表中正确的⾏。

例如：我们需要查询出所有的商品及对应的供应商信息怎么办？

select vend_name,prod_name,prod_price
from vendors,products
where vendors.vend_id = products.vend_id
order by vend_name,prod_name;

这两个表⽤WHERE⼦句正确联结，WHERE⼦句指示MySQL匹配vendors表中的vend_id和products表中的vend_id。

可以看到要匹配的两个列以 vendors.vend_id 和 products. vend_id指定。这⾥需要这种完全限定列名，因为如果只给出vend_id，则MySQL不知道指的是哪⼀个（它们有两个，每个表中⼀个）。

在引⽤的列可能出现⼆义性时，必须使⽤完全限定列名（⽤⼀个点分隔的表名和列名）

在联结两个表时，你实际上做的是将第⼀个表中的每⼀⾏与第⼆个表中的每⼀⾏配对。

WHERE⼦句作为过滤条件，它只包含那些匹配给定条件（这⾥是联结条件）的⾏。

你能想象上⾯的sql如果没有where条件时会怎样吗？

select vend_name,prod_name,prod_price from vendors,products

如果没有where条件，第⼀个表中的每个⾏将与第⼆个表中的每个⾏配对，⽽不管它们逻辑上是否可以配在⼀起

由没有联结条件的表关系返回的结果为笛卡⼉积。检索出的⾏的数⽬将是第⼀个表中的⾏数乘以第⼆个表中的⾏数。

不要忘了WHERE⼦句

应该保证所有联结都有WHERE⼦句，否则MySQL将返回⽐想要的数据多得多的数据。

同理，应该保证WHERE⼦句的正确性。不正确的过滤条件将导致MySQL返回不正确的数据

1.2 使用join进行表联结

其实，对于这种联结可以使⽤稍微不同的语法来明确指定联结的类型。

select vend_name,prod_name,prod_price
from vendors
inner join products 
on vendors.vend_id = products.vend_id;

两个表之间的关系是FROM⼦句的组成部分，以INNER JOIN指定。

在使⽤这种语法时，联结条件⽤特定的ON⼦句⽽不是WHERE⼦句给出。

传递给ON的实际条件与传递给WHERE的相同。

SQL规范⾸选INNER JOIN语法。（这里直接使用JOIN结果相同）

1.3 联结多个表

SQL对一条SELECT语句中可以联结的表的数目没有限制。

创建联结的基本规则也相同。首先列出所有表，然后定义表之间的关系。

案例：查询出订单号为20005的订单中购买的商品及对应的产品供应商信息

1 select prod_name,vend_name,prod_price,quantity
2 from orderitems,products,vendors
3 where products.vend_id = vendors.vend_id
4 and orderitems.prod_id = products.prod_id
5 and order_num = 20005;

改写为join语法

1 select prod_name,vend_name,prod_price,quantity
2 from orderitems
3 inner join products on orderitems.prod_id = products.prod_id
4 inner join vendors on products.vend_id = vendors.vend_id
5 where order_num = 20005;

MySQL在运⾏时关联指定的每个表以处理联结。这种处理可能是⾮常耗费资源的，因此应该仔细，不要联结不必要的表。联结的表越多，性能下降越厉害。

1.4 使用表别名AS

别名除了⽤于列名和计算字段外，SQL还允许给表名起别名。

这样做有两个主要理由：

缩短SQL语句；
允许在单条SELECT语句中多次使⽤相同的表
应该注意，表别名只在查询执⾏中使⽤。与列别名不⼀样，表别名不返回到客户机

2，联结查询中的自联结

自联结:当前这个表与自己这个表做联结（join）

举例

假如你发现某物品（其ID为DTNTR）存在问题，因此想知道⽣产该物品的供应商⽣产的其他物品是否也存在这些问题。

此查询要求⾸先找到⽣产ID为DTNTR的物品的供应商，然后找出这个供应商⽣产的其他物品。

 1 -- 使⽤⼦查询(嵌套查询)
 2 select prod_id,prod_name
 3 from products
 4 where vend_id = (select vend_id from products where prod_id = 'DTNTR');
 5  
 6 -- 使用 自联结方式查询
 7 select p1.prod_id,p2.prod_name
 8 from products as p1
 9 join products as p2 
10 on p1.vend_id = p2.vend_id
11 where p2.prod_id = 'DTNTR';
12  
13 -- 改成where语句
14 select p1.prod_id,p2.prod_name
15 from products as p1, products as p2
16 where p1.vend_id = p2.vend_id and p2.prod_id = 'DTNTR';

-- 此查询中需要的两个表实际上是相同的表，因此products表在FROM⼦句中出现了两次。虽然这是完全合法的，但对products的引⽤具有⼆义性，因为MySQL不知道你引⽤的是products表中的哪个实例。

-- 为解决此问题，使⽤了表别名。products的第⼀次出现为别名p1，第⼆次出现为别名p2。现在可以将这些别名⽤作表名。

--例如，SELECT语句使⽤p1前缀明确地给出所需列的全名。如果不这样，MySQL将返回错误，因为分别存在两个名为prod_id、prod_name的列。MySQL不知道想要的是哪⼀个列（即使它们事实上是同⼀个列）。WHERE（通过匹配p1中的vend_id和p2中的vend_id）⾸先联结两个表，然后按第⼆个表中的prod_id过滤数据，返回所需的数据

⽤⾃联结⽽不⽤⼦查询。⾃联结通常作为外部语句⽤来替代从相同表中检索数据时使⽤的⼦查询语句。

虽然最终的结果是相同的，但有时候处理联结远⽐处理⼦查询快得多。

深入了解 join，依次执行下面三条SQL语句，观察结果

1，不添加where语句

1 select
2 p1.prod_id,p1.prod_name,p1.vend_id,
3 p2.prod_id,p2.prod_name,p2.vend_id
4 from products as p1,products as p2;

两张表数据的乘积14*14

2，只限定两张表的id相等

1 select
2 p1.prod_id,p1.prod_name,p1.vend_id,
3 p2.prod_id,p2.prod_name,p2.vend_id
4 from products as p1,products as p2
5 where p1.vend_id = p2.vend_id;

3，限定p2表prod_id为DTNTR

1 select
2 p1.prod_id,p1.prod_name,p1.vend_id,
3 p2.prod_id,p2.prod_name,p2.vend_id
4 from products as p1,products as p2
5 where p1.vend_id = p2.vend_id
6 and p2.prod_id = 'DTNTR';

4，不展示p2表相关字段

1 select
2 p1.prod_id,p1.prod_name,p1.vend_id
3 from products as p1,products as p2
4 where p1.vend_id = p2.vend_id
5 and p2.prod_id = 'DTNTR';

子查询（嵌套查询）是目前可明确知道的 sql中运行效率最低的一种方式，尽可能不使用嵌套语句。

3，外部联结-left join与right join

许多联结将⼀个表中的⾏与另⼀个表中的⾏相关联。但有时候会需要包含没有关联⾏的那些⾏。

例如，可能需要使⽤联结来完成以下⼯作：

对每个客户下了多少订单进⾏计数，包括那些⾄今尚未下订单的客户；（之前联结的结果只保留了表之间有关联的记录，如果订单表中没有客户c的订单记录，那么客户c的信息就不会出现在联结的结果中，也就没法统计所有客户的订单信息）
列出所有产品以及订购数量，包括没有⼈订购的产品；
计算平均销售规模，包括那些⾄今尚未下订单的客户；
在上述例⼦中，联结包含了那些在相关表中没有关联⾏的⾏。这种类型的联结称为外部联结。

还以之前使用的数据库为例，查询所有客户的订单数据：

1 -- 内部连接
2 select customers.cust_id,orders.order_num 
3 from customers,orders 
4 where orders.cust_id = customers.cust_id;
5  
6 select customers.cust_id,orders.order_num 
7 from customers 
8 join orders 
9 on orders.cust_id = customers.cust_id;

select cust_id from customers;

可以看出客户10002的记录并未出现在内部联结的结果中。

那什么是外部联结呢？

left join ：是以 left join 左侧表为基准，去关联右侧的表进行联结，如果有未关联的数据，那么结果为null

right join ：是以 right join 右侧表为基准，去关联左侧的表进行联结，如果有未关联的数据，那么结果为null

 1 -- 以用户表为基准，去关联查询 订单表数据
 2 select customers.cust_id,orders.order_num
 3 from customers 
 4 left join orders
 5 on customers.cust_id = orders.cust_id;
 6 
 7 select customers.cust_id,orders.order_num
 8 from orders 
 9 right join customers
10 on customers.cust_id = orders.cust_id;

-- 以上两条语句等价

聚集函数也可以⽅便地与其他联结⼀起使⽤。

如果要检索所有客户及每个客户所下的订单数，下⾯使⽤了COUNT()函数的代码可完成此⼯作

包含那些没有任何下订单的客户。

1 -- 对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户；
2 select customers.cust_id,count(orders.order_num) as nums
3 from customers 
4 left join orders
5 on customers.cust_id = orders.cust_id
6 group by customers.cust_id;

注意：

保证使⽤正确的联结条件，否则将返回不正确的数据。
应该总是提供联结条件，否则会得出笛卡⼉积。
在⼀个联结中可以包含多个表，甚⾄对于每个联结可以采⽤不同的联结类型。虽然这样做是合法的，⼀般也很有⽤，但应该在⼀起测试它们前分别测试每个联结。这将使故障排除更为简单

四，UNION联合查询

MySQL也允许执⾏多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。

这些组合查询通常称为并（union）或复合查询（compound query）。

UNION规则

UNION必须由两条或两条以上的SELECT语句组成，语句之间⽤关键字UNION分隔（因此，如果组合4条SELECT语句，将要使⽤3个UNION关键字）。
UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的⽇期类型）。
假如需要价格⼩于等于5的所有物品的⼀个列表，⽽且还想包括供应商1001和1002⽣产的所有物品。

1 -- 先查询第⼀个结果
2 select vend_id,prod_id,prod_price from products where prod_price <= 5;

1 -- 再查询第⼆个结果
2 select vend_id,prod_id,prod_price from products where vend_id in(1001,1002);

1 --使⽤union将两个sql⼀并执⾏
2 select vend_id,prod_id,prod_price from products where prod_price <= 5
3 union
4 select vend_id,prod_id,prod_price from products where vend_id in(1001,1002);

1 -- 使⽤union将两个sql⼀并执⾏
2 -- 这条语句由前⾯的两条SELECT语句组成，语句中⽤UNION关键字分隔。
3 -- UNION指示MySQL执⾏两条SELECT语句，并把输出组合成单个查询结果集
4 -- 以下是同样结果,使⽤where的多条件来实现
5 select vend_id,prod_id,prod_price 
6 from products 
7 where prod_price <= 5 
8 or vend_id in (1001,1002);

在这个简单的例⼦中，使⽤UNION可能⽐使⽤WHERE⼦句更为复杂。

但对于更复杂的过滤条件，或者从多个表（⽽不是单个表）中检索数据的情形，使⽤UNION可能会使处理更简单。

现在思考⼀个问题,上⾯的语句分别返回了⼏条数据?

第⼀条sql返回4⾏,第⼆条sql返回5⾏,那么union返回了⼏⾏?

UNION从查询结果集中⾃动去除了重复的⾏（换句话说，它的⾏为与单条SELECT语句中使⽤多个WHERE⼦句条件⼀样）。

这是UNION的默认⾏为，但是如果需要，可以改变它。如果想返回所有匹配⾏，可使⽤UNION ALL⽽不是UNION

1 select vend_id,prod_id,prod_price from products where prod_price <= 5
2 union all
3 select vend_id,prod_id,prod_price from products where vend_id in(1001,1002);

对组合查询结果排序

SELECT语句的输出⽤ORDER BY⼦句排序。在⽤UNION组合查询时，只能使⽤⼀条ORDER BY⼦句，它必须出现在最后⼀条SELECT语句之后。
对于结果集，不存在⽤⼀种⽅式排序⼀部分，⽽⼜⽤另⼀种⽅式排序另⼀部分的情况，因此不允许使⽤多条ORDER BY⼦句。

1 select vend_id,prod_id,prod_price from products where prod_price <= 5
2 union
3 select vend_id,prod_id,prod_price from products where vend_id in(1001,1002)
4 order by prod_price;

这条UNION在最后⼀条SELECT语句后使⽤了ORDER BY⼦句。

虽然ORDER BY⼦句似乎只是最后⼀条SELECT语句的组成部分，但实际上MySQL将⽤它来排序所有SELECT语句返回的所有结果。

五，MySQL事务

事务（Transaction）是由⼀系列对系统中数据进⾏访问与更新的操作所组成的⼀个程序执⾏逻辑单元。

1，事务的语法

1. start transaction;/ begin;

2. commit; 使得当前的修改确认

3. rollback; 使得当前的修改被放弃

2，事务的ACID特性

2.1 原子性（Atomicity）

事务的原⼦性是指事务必须是⼀个原⼦的操作序列单元。事务中包含的各项操作在⼀次执⾏过程中，只允许出现两种状态之⼀。

全部执⾏成功
全部执⾏失败
事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。

事务执⾏过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发⽣⼀样。

也就是说事务是⼀个不可分割的整体，就像化学中学过的原⼦，是物质构成的基本单位。

2.2 一致性（Consistency）

事务的⼀致性是指事务的执⾏不能破坏数据库数据的完整性和⼀致性，⼀个事务在执⾏之前和执⾏之后，数据库都必须处以⼀致性状态。

⽐如：如果从A账户转账到B账户，不可能因为A账户扣了钱，⽽B账户没有加钱。

2.3 隔离性（Isolation）

事务的隔离性是指在并发环境中，并发的事务是互相隔离的。也就是说，不同的事务并发操作相同的数据时，每个事务都有各⾃完整的数据空间。

⼀个事务内部的操作及使⽤的数据对其它并发事务是隔离的，并发执⾏的各个事务是不能互相⼲扰的。隔离性分4个级别，下⾯会介绍。

2.4 持久性（Duration）

事务的持久性是指事务⼀旦提交后，数据库中的数据必须被永久的保存下来。

即使服务器系统崩溃或服务器宕机等故障。只要数据库重新启动，那么⼀定能够将其恢复到事务成功结束后的状态。

3，事务的并发问题

脏读：读取到了没有提交的数据。事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。

不可重复读：同⼀条命令返回不同的结果集（更新）。事务 A 多次读取同⼀数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同⼀数据时，结果不⼀致。

幻读：重复查询的过程中，数据就发⽣了量的变化（insert， delete）。

4，事务隔离级别

4种事务隔离级别从上往下，级别越⾼，并发性越差，安全性就越来越⾼。⼀般数据默认级别是读以提交或可重复读

4.1 查看当前会话中事务的隔离级别

select @@tx_isolation;

4.2 读未提交（READ_UNCOMMITTED）

读未提交，该隔离级别允许脏读取，其隔离级别是最低的。

换句话说，如果⼀个事务正在处理某⼀数据，并对其进⾏了更新，但同时尚未完成事务，因此还没有提交事务；⽽以此同时，允许另⼀个事务也能够访问该数据。

脏读示例：

在事务A和事务B同时执⾏时可能会出现如下场景：

余额应该为1500元才对。请看T5时间点，事务A此时查询的余额为0，这个数据就是脏数据，他是事务B造成的，很明显是事务没有进⾏隔离造成的。

4.3 读已提交（READ_COMMITTED）

读已提交是不同的事务执⾏的时候只能获取到已经提交的数据。这样就不会出现上⾯的脏读的情况了。但是在同⼀个事务中执⾏同⼀个读取,结果不⼀致

不可重复读示例

可是解决了脏读问题，但是还是解决不了可重复读问题。

事务A其实除了查询两次以外，其它什么事情都没做，结果钱就从1000变成0了，这就是不可重复读的问题。

4.4 可重复读（REPEATABLE_READ）

可重复读就是保证在事务处理过程中，多次读取同⼀个数据时，该数据的值和事务开始时刻是⼀致的。

事务A执行过程中查询的数据和事务A开始时相同，事务A提交后查询的数据才是事务B修改后的数据。

因此该事务级别限制了不可重复读和脏读，但是有可能出现幻读的数据。

幻读

幻读就是指同样的事务操作，在前后两个时间段内执⾏对同⼀个数据项的读取，可能出现不⼀致的结果。

原本在事务A结束之前，是看不到事务B中新增是数据的，但是进行范围修改后（比如某一列全部加上一个值），又能在本次事务A内查询到新增的数据，所以被称为“诡异的更新事件”

4.5 顺序读（SERIALIZABLE）

顺序读是最严格的事务隔离级别。它要求所有的事务排队顺序执⾏，即事务只能⼀个接⼀个地处理，不能并发。

两个事务可以同时读，但是一个事务A执行更新数据的语句后将会卡住，直到另一事务B提交/回滚后，事务A才能继续进行

5，不同的隔离级别的锁的情况(了解)

1. 读未提交（RU）: 有⾏级的锁，没有间隙锁。它与RC的区别是能够查询到未提交的数据。
2. 读已提交（RC）：有⾏级的锁，没有间隙锁，读不到没有提交的数据。
3. 可重复读（RR）：有⾏级的锁，也有间隙锁，每次读取的数据都是⼀样的，并且没有幻读的情况。
4. 序列化（S）：有⾏级锁，也有间隙锁，读表的时候，就已经上锁了
6，隐式提交(了解)

DQL:查询语句
DML:写操作(添加,删除,修改)
DDL:定义语句(建库,建表,修改表,索引操作,存储过程,视图)
DCL:控制语⾔(给⽤户授权,或删除授权)
DDL（Data Define Language）：都是隐式提交。

隐式提交：执⾏这种语句相当于执⾏commit; DDL

六，MySQL进阶扩展

1，存储过程

1.1 什么是存储过程?

⽬前使⽤的⼤多数SQL语句都是针对⼀个或多个表的单条语句。并⾮所有操作都这么简单，经常会有⼀个完整的操作需要多条语句才能完成。

例如以下的情形。

为了处理订单，需要核对以保证库存中有相应的物品。
如果库存有物品，需要预定以便不将它们再卖给别的⼈，并减少可⽤的物品数量以反映正确的库存量。
库存中没有的物品需要订购，这需要与供应商进⾏某种交互。
执⾏这个处理需要针对许多表的多条MySQL语句。可能需要执⾏的具体语句及其次序也不是固定的。

那么，怎样编写此代码?可以单独编写每条语句，并根据结果有条件地执⾏另外的语句。

在每次需要这个处理时(以及每个需要它的应⽤中)都必须做这些⼯作。

可以创建存储过程

存储过程简单来说，就是为以后的使⽤⽽保存的⼀条或多条MySQL语句的集合。储存过程是⼀组为了完成特定功能的SQL语句集，经过编译之后存储在数据库中，在需要时直接调⽤。

存储过程就像脚本语⾔中函数定义⼀样。

1.2 为什么要使用存储过程?

优点:

可以把⼀些复杂的sql进⾏封装,简化复杂操作
保证了数据的完整性,防⽌错误
简单的变动只需要更改存储过程的代码即可
提⾼性能。因为使⽤存储过程⽐使⽤单独的SQL语句要快。(预先编译)
缺点:

存储过程的编写⽐SQL语句复杂
⼀般可能还没有创建存储过程的权限,只能调⽤
1.3 实战总结

在电信、银⾏业、⾦融⽅⾯以及国企都普遍使⽤存储过程来熟悉业务逻辑，但在互联⽹中相对较少。

业务逻辑不要封装在数据库⾥⾯,应该由应⽤程序(JAVA、Python、PHP)处理。
让数据库只做它擅⻓和必须做的，减少数据库资源和性能的消耗。
维护困难，⼤量业务逻辑封装在存储过程中，造成业务逻辑很难剥离出来。动A影响B。
⼈员也难招聘，因为既懂存储过程，⼜懂业务的⼈少。使⽤困难。
1.4 存储过程操作

创建存储过程

\d // 修改MySQL默认的语句结尾符 ; ，改为 // 。（因为；在存储过程中有别的用途）

create procedure 创建语句

BEGIN和END语句⽤来限定存储过程体

 1 -- 定义存储过程
 2 \d //
 3 create procedure p1()
 4 begin
 5 set @i=10;
 6 while @i<90 do
 7 insert into users values(null,concat('user:',@i),@i,0);
 8 set @i=@i+1;
 9 end while;
10 end;
11 //

执行存储

call p1()

查看存储过程

show create procedure p1\G

删除存储过程

drop procedure p1

2，触发器

MySQL语句在需要时被执⾏，存储过程也是如此。

但是，如果你想要某条语句(或某些语句)在事件发⽣时⾃动执⾏，怎么办呢?

例如:

每当增加⼀个顾客到某个数据库表时，都检查其电话号码格式是否正确;
每当订购⼀个产品时，都从库存数量中减去订购的数量;
⽆论何时删除⼀⾏，都在某个存档表中保留⼀个副本。
所有这些例⼦的共同之处是它们都需要在某个表发⽣更改时⾃动处理。这确切地说就是触发器。
2.1 触发器的定义

触发器是MySQL响应写操作(增、删、改)⽽⾃动执⾏的⼀条或⼀组定义在BEGIN和END之间的MySQL语句

或可理解为：提前定义好⼀个或⼀组操作,在指定的SQL操作前或后来触发指定的SQL⾃动执⾏

触发器就像是JavaScript中的事件⼀样

举例: 定义⼀个update语句,在向某个表中执⾏insert添加语句时来触发执⾏,就可以使⽤触发器

2.2 触发器语法

 1 CREATE TRIGGER trigger_name trigger_time trigger_event
 2  ON tbl_name FOR EACH ROW trigger_stmt
 3 说明：
 4 # trigger_name：触发器名称
 5 # trigger_time:触发时间，可取值：BEFORE或AFTER
 6 # trigger_event：触发事件，可取值：INSERT、UPDATE或DELETE。
 7 # tb1_name：指定在哪个表上
 8 # trigger_stmt：触发处理SQL语句。
 9 -- 查看所有的 触发器
10 show triggers\G;
11 -- 删除触发器
12 drop trigger trigger_name;

2.3 触发器Demo

-- 创建⼀个删除的触发器,在users表中删除数据之前,往del_users表中添加⼀个数据
tips：

在INSERT触发器代码内，可引⽤⼀个名为NEW的虚拟表，访问被插⼊的⾏;

在DELETE触发器代码内，可以引⽤⼀个名为OLD的虚拟表，访问被删除的⾏;

OLD中的值全都是只读的，不能更新。
在AFTER DELETE的触发器中⽆法获取OLD虚拟表
在UPDATE触发器代码中

可以引⽤⼀个名为OLD的虚拟表访问更新以前的值
可以引⽤⼀个名为NEW的虚拟表访问新更新的值;
1，复制一个已经存在的表结构

create table del_users like users;

2，创建删除触发器

注意在创建删除触发器时,只能在删除之前（必须是before）才能获取到old(之前的)数据

1 \d //
2 create trigger deluser before delete on users for each row
3 begin
4 insert into del_users values(old.id,old.name,old.age,old.account);
5 end;
6 //
7 \d ;

其中的old是删除时引用的一个虚拟表，详见上方tips

3，删除users表中的数据去实验

2.4 练习

⽤触发器来实现数据的统计

-- 1.创建⼀个表, users_count ⾥⾯有⼀个 num的字段 初始值为0或者是你当前users表中的count
-- 2,给users表创建⼀个触发器
-- 当给users表中执⾏insert添加数据之后,就让users_count⾥⾯num+1,
-- 当users表中的数据删除时,就让users_count⾥⾯num-1,
-- 想要统计users表中的数据总数时,直接查看 users_count

3，视图

3.1 什么是视图？

视图是虚拟的表。与包含数据的表不⼀样，视图只包含使⽤时动态检索数据的查询。
视图仅仅是⽤来查看存储在别处的数据的⼀种设施或⽅法。
视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。
在添加或更改这些表中的数据时，视图将返回改变过的数据。
因为视图不包含数据，所以每次使⽤视图时，都必须处理查询执⾏时所需的任⼀个检索。
如果你⽤多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。
3.2 视图的作用

1. 重⽤SQL语句。
2. 简化复杂的SQL操作。在编写查询后，可以⽅便地重⽤它⽽不必知道它的基本查询细节。
3. 使⽤表的组成部分⽽不是整个表。
4. 保护数据。可以给⽤户授予表的特定部分的访问权限⽽不是整个表的访问权限。
5. 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
6. 注意:视图不能索引，也不能有关联的触发器或默认值。
3.3 视图的基础用法

 1 -- 创建视图:
 2 create view v_users as select id,name,age from users where age >= 25 and age <= 35;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 --view视图的帮助信息:
 6 mysql> ? view
 7 ALTER VIEW
 8 CREATE VIEW
 9 DROP VIEW
10 
11 -- 查看当前库中所有的视图
12 show tables; --可以查看到所有的表和视图
13 show table status where comment='view'; --只查看当前库中的所有视图
14 
15 -- 删除视图v_t1:
16 mysql> drop view v_t1;

不想展示所有数据（比如这里的accont），所以选择创建视图

七，MySQL索引

1，MySQL索引与SQL优化索引的概述与分类

1.1 什么是索引

索引类似图书的目录索引，可以提高数据检索的效率,降低数据库的IO成本。

MySQL官方对索引的定义为:

索引(Index)是帮助MySQL高效获取数据的数据结构。
我们可以简单理解为:快速查找排好序的一种数据结构。
1.2 索引分类

【效率从高到低】

1，主键索引

即主索引，根据主键建立索引，不允许重复，不允许空值;

2，唯一索引

用来建立索引的列的值必须是唯一的,允许空值

3，普通索引

用表中的普通列构建的索引，没有任何限制

4，全文索引

用大文本对象的列构建的索引。（比如用文章作为索引，可以将文章分为若干词，通过词建立索引，这样可以搜索词就能快速找到文章）

MYSQL5.6之前的版本中，全文索引只能用于MyISAM存储引擎
MYSQL5.6及以后的版本，MyISAM 和InnoDB均支持全文索引
在之前的MySQL 中，全文索引只对英文有用，目前对中文还不支持（MYSQL8之后支持）
5，组合索引

用多个列组合构建的索引，这多个列中的值不允许有空值

组合索引的最左原则：

总结

SQL语句的优化

1.避免嵌套语句(子查询)
2.避免多表查询(复杂查询简单化)
索引优化

1.适当建立索引
2.合理使用索引

SET FOREIGN_KEY_CHECKS = 1;

posted @ 2021-04-13 14:46 简单爱娜阅读(105) 评论(0) 收藏举报

刷新页面返回顶部

简单爱娜