数据库技术之MySQL高级

--(1) 检索包含物品TNT2的所有订单的编号。

select order_num from orderitems where prod_id = 'TNT2';

+-----------+

| order_num |

+-----------+

| 20005 |

| 20007 |

+-----------+

--(2) 检索具有前⼀步骤列出的订单编号的所有客户的ID

select cust_id from orders where order_num IN (20005,20007);

+---------+

| cust_id |

+---------+

| 10001 |

| 10004 |

+---------+

--(3) 检索前⼀步骤返回的所有客户ID的客户信息。

select cust_name,cust_contact from customers where cust_id in (10001,10004);

+----------------+--------------+

| cust_name | cust_contact |

+----------------+--------------+

| Coyote Inc. | Y Lee |

| Yosemite Place | Y Sam |

+----------------+--------------+

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

select cust_name,cust_contact

from customers

where cust_id in (select cust_id

 from orders

 where order_num IN (select order_num

 from orderitems

 where prod_id = 'TNT2'));

+----------------+--------------+

| cust_name | cust_contact |

+----------------+--------------+

| Coyote Inc. | Y Lee |

| Yosemite Place | Y Sam |

+----------------+--------------+

--为了执⾏上述SELECT语句，MySQL实际上必须执⾏3条SELECT语句。

--最⾥边的⼦查询返回订单号列表，此列表⽤于其外⾯的⼦查询的WHERE⼦句。

--外⾯的⼦查询返回客户ID列表，此客户ID列表⽤于最外层查询的WHERE⼦句。

--最外层查询确实返回所需的数据。

这⾥给出的代码有效并获得所需的结果。

但是，使⽤⼦查询并不总是执⾏这种类型的数据检索的最有效的⽅法。

作为计算字段使⽤⼦查询

使⽤⼦查询的另⼀⽅法是创建计算字段。

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

-- (1) 从customers表中检索客户列表。

select cust_id,cust_name from customers ;

+---------+----------------+

| cust_id | cust_name |

+---------+----------------+

| 10001 | Coyote Inc. |

| 10002 | Mouse House |

| 10003 | Wascals |

| 10004 | Yosemite Place |

| 10005 | E Fudd |

+---------+----------------+

-- (2) 对于检索出的每个客户，统计其在orders表中的订单数⽬。

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

+--------+

| orders |

+--------+

| 2 |

+--------+

为了对每个客户执⾏COUNT(*)*计算，应该将*COUNT(*)作为⼀个⼦查询。

select cust_id,cust_name,

 (select count(*)

 from orders

 where orders.cust_id = customers.cust_id) as orders

 )

from customers

order by cust_name;

+---------+----------------+--------+

| cust_id | cust_name | orders |

+---------+----------------+--------+

| 10001 | Coyote Inc. | 2 |

| 10005 | E Fudd | 1 |

| 10002 | Mouse House | 0 |

| 10003 | Wascals | 6 |

| 10004 | Yosemite Place | 1 |

+---------+----------------+--------+

orders是⼀个计算字段，它是由圆括号中的⼦查询建⽴的。该⼦查询对检索出的每个客户执⾏⼀次。在此例⼦中，该⼦查询执⾏了5次，因为检索出了5个客户。

注意:⼦查询中的WHERE⼦句与前⾯使⽤的WHERE⼦句稍有不同，因为它使⽤了完全限定列名这种类型的⼦查询称为相关⼦查询。==任何时候只要列名可能有多义性，就必须使⽤这种语法（表名和列名由⼀个句点分隔）。因为有两个cust_id列，⼀个在customers中，另⼀个在orders中，需要⽐较这两个列以正确地把订单与它们相应的顾客匹配。如果不完全限定列名，MySQL将假定你是对orders表中的cust_id进⾏⾃身⽐较。

外键

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

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

物理外键：就是在创建表时，就指定这个表中的字段是一个外键，并且强关联某个表中的某个字段需要在定义字段时，使用sql语句来实现
逻辑外键：就是在表中创建一个普通的字段，没有强关联关系，需要通过程序逻辑来实现

表关系

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

一对一：就是在一个表中的数据，对应着另外一张表中的数据，没有或者有且只能有一个。

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

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

员工表：
    id，姓名、联系方式、工龄、
    12  张三  1010    3
    13  李四  1020    2

详情表：
   yid 性别、籍贯、学历、、、、、
    12    男    山东 本科
    13    男    山西 本科


上面的表关系就是一对一的表关系，通过详情表中的yid这个字段来标记员工表中的主键。
一个员工有着一个对应的详情信息，存储在详情表中，
在详情表中的数据，也只属于某一个员工。

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

    商品分类
        id 分类名
        1  手机
        2  电脑

    商品
        id 所属分类id，商品名
        1  1         小米手机
        2  1         华为手机

    新闻分类
        id   分类名
        1    体育
        2    国际


    新闻
        id  title             分类id
        1   国足加油              1
        2   川川加油            2
        3   川川被网民称为特没谱  2

mysql> select * from orders;
+-----------+---------------------+---------+
| order_num | order_date          | cust_id |
+-----------+---------------------+---------+
|     20005 | 2005-09-01 00:00:00 |   10001 |
|     20006 | 2005-09-12 00:00:00 |   10003 |
|     20007 | 2005-09-30 00:00:00 |   10004 |
|     20008 | 2005-10-03 00:00:00 |   10005 |
|     20009 | 2005-10-08 00:00:00 |   10001 |
+-----------+---------------------+---------+
5 rows in set (0.00 sec)


mysql> select * from orderitems;
+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
|     20005 |          1 | ANV01   |       10 |       5.99 |
|     20005 |          2 | ANV02   |        3 |       9.99 |
|     20005 |          3 | TNT2    |        5 |      10.00 |
|     20005 |          4 | FB      |        1 |      10.00 |
|     20006 |          1 | JP2000  |        1 |      55.00 |
|     20007 |          1 | TNT2    |      100 |      10.00 |
|     20008 |          1 | FC      |       50 |       2.50 |
|     20009 |          1 | FB      |        1 |      10.00 |
|     20009 |          2 | OL1     |        1 |       8.99 |
|     20009 |          3 | SLING   |        1 |       4.49 |
|     20009 |          4 | ANV03   |        1 |      14.99 |
+-----------+------------+---------+----------+------------+
11 rows in set (0.01 sec)

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

books 图书
id  name          author
1   <跟川哥学编程>    川哥
2   <跟川哥学数据分析> 川哥
3   <川哥讲法律故事>   川哥

tags 标签
id   name
1    编程
2    计算机
3    互联网
4    法律
5    文学

从图书角度看，一本书有多个标签
1   <跟川哥学编程>    川哥   ， 编程、计算机、互联网
2   <跟川哥学数据分析> 川哥  ，  互联网、计算机
3   <川哥讲法律故事>   川哥 ，  法律

换一个角度，从标签这个角度看，一个标签包含多个图书
计算机， <跟川哥学编程>， <跟川哥学数据分析>


案例二：
    一个班级有多个老师来讲课（化学、物理、数学、、、）
    一个老师要带多个班级   （一班，二班，三班）

关系表

SQL最强⼤的功能之⼀就是能在数据检索查询的执⾏中联结（join）表。
在能够有效地使⽤联结前，必须了解关系表以及关系数据库设计的⼀些基础知识。

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

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

产品表：

 产品，描述，价格，供应商名称，供应商地址,供应商联系⽅式

 A6 ... ... 奥迪 ... ....

 520li .. .... 宝马 ... ...

 ...

 

--现在，假如有由同⼀供应商⽣产的多种物品，那么在何处存储供应

--商信息（如，供应商名、地址、联系⽅法等）呢？

 产品，描述，价格，供应商名称，供应商地址,供应商联系⽅式

 A6 ... ... 奥迪 ... ....

 520li .. .... 宝马... ...

A8 .. ... 奥迪 ... ...

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

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

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

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

-- vendors表包含所有供应商信息

|vend_id | vend_name | vend_address| vend_city ....

-- products表只存储产品信息，它除了存储供应商ID（vendors表的主键）外不存储其他供应商信息。

prod_id | vend_id | prod_name | prod_price | prod_desc

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

供应商信息不重复，从⽽不浪费时间和空间；
如果供应商信息变动，可以只更新vendors表中的单个记录，相关表中的数据不⽤改动；
由于数据⽆重复，显然数据是⼀致的，这使得处理数据更简单

关系数据可以有效地存储和⽅便地处理。因此，关系数据库的可伸缩性远⽐⾮关系数据库要好。

表联结

如果数据存储在多个表中，怎样⽤单条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;

+-------------+----------------+------------+

| vend_name | prod_name | prod_price |

+-------------+----------------+------------+

| ACME | Bird seed | 10.00 |

| ACME | Carrots | 2.50 |

| ACME | Detonator | 13.00 |

| ACME | Safe | 50.00 |

| ACME | Sling | 4.49 |

| ACME | TNT (1 stick) | 2.50 |

| ACME | TNT (5 sticks) | 10.00 |
| Anvils R Us | .5 ton anvil | 5.99 |
| Anvils R Us | 1 ton anvil | 9.99 |
| Anvils R Us | 2 ton anvil | 14.99 |
| Jet Set | JetPack 1000 | 35.00 |
| Jet Set | JetPack 2000 | 55.00 |
| LT Supplies | Fuses | 3.42 |
| LT Supplies | Oil can | 8.99 |
+-------------+----------------+------------+
14 rows in set (0.00 sec)

这两个表⽤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返回不正确的数据

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

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语法。

联结多个表

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

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

#案例： 查询出订单号为20005的订单中购买的商品及对应的产品供应商信息
select prod_name,vend_name,prod_price,quantity

from orderitems,products,vendors

 where products.vend_id = vendors.vend_id

 and orderitems.prod_id = products.prod_id

 and order_num = 20005;

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

使⽤表别名 AS

别名除了⽤于列名和计算字段外，SQL还允许给表名起别名。
这样做有两个主要理由：

缩短SQL语句；
允许在单条SELECT语句中多次使⽤相同的表

应该注意，表别名只在查询执⾏中使⽤。与列别名不⼀样，表别名不返回到客户机 ## ⾃联结

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

假如你发现某物品（其ID为DTNTR）存在问题，因此想知道⽣产该物品的供应商⽣产的其他物品是否也存在这些问题。此查询要求⾸先找到⽣产ID为DTNTR的物品的供应商，然后找出这个供应商⽣产的其他物品。

-- 使⽤⼦查询(嵌套查询)

select prod_id,prod_name from products where vend_id = (select vend_id from products where prod_id = 'DTNTR');

+---------+----------------+

| prod_id | prod_name |

+---------+----------------+

| DTNTR | Detonator |

| FB | Bird seed |

| FC | Carrots |

| SAFE | Safe |

| SLING | Sling |

| TNT1 | TNT (1 stick) |

| TNT2 | TNT (5 sticks) |

+---------+----------------+

-- 使⽤联结的相同查询：

select p1.prod_id,p1.prod_name

from products as p1,products as p2

where p1.vend_id = p2.vend_id and p2.prod_id = 'DTNTR';

+---------+----------------+

| prod_id | prod_name |

+---------+----------------+

| DTNTR | Detonator |

| FB | Bird seed |

| FC | Carrots |

| SAFE | Safe |

| SLING | Sling |

| TNT1 | TNT (1 stick) |

| TNT2 | TNT (5 sticks) |

+---------+----------------+

-- 此查询中需要的两个表实际上是相同的表，因此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过滤数据，返回所需的数据

⽤⾃联结⽽不⽤⼦查询⾃联结通常作为外部语句⽤来替代从相同表中检索数据时使⽤的⼦查询语句。
虽然最终的结果是相同的，但有时候处理联结远⽐处理⼦查询快得多。 ## 外部联结

什么是外部联结？

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

许多联结将⼀个表中的⾏与另⼀个表中的⾏相关联。但有时候会需要包含没有关联⾏的那些⾏。
例如，可能需要使⽤联结来完成以下⼯作：

对每个客户下了多少订单进⾏计数，包括那些⾄今尚未下订单的客户；
列出所有产品以及订购数量，包括没有⼈订购的产品；
计算平均销售规模，包括那些⾄今尚未下订单的客户

在上述例⼦中，联结包含了那些在相关表中没有关联⾏的⾏。这种类型的联结称为外部联结。 -- 内部联结。它检索所有客户及其订单：

select customers.cust_id,orders.order_num from customers inner join orders on

customers.cust_id = orders.cust_id;

+---------+-----------+

| cust_id | order_num |

+---------+-----------+

| 10001 | 20005 |

| 10001 | 20009 |

| 10003 | 20006 |

| 10004 | 20007 |

| 10005 | 20008 |

+---------+-----------+

5 rows in set (0.00 sec)

--外部联结语法类似。检索所有客户，包括那些没有订单的客户

select customers.cust_id,orders.order_num from customers left join orders on

customers.cust_id = orders.cust_id;

+---------+-----------+

| cust_id | order_num |

+---------+-----------+

| 10001 | 20005 |

| 10001 | 20009 |

| 10002 | NULL |

| 10003 | 20006 |

| 10004 | 20007 |

| 10005 | 20008 |

+---------+-----------+

6 rows in set (0.00 sec)

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

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

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

select customers.cust_name,customers.cust_id,count(orders.order_num) as num_ord from customers left join orders on customers.cust_id = orders.cust_id group by customers.cust_id;

+----------------+---------+---------+

| cust_name | cust_id | num_ord |

+----------------+---------+---------+

| Coyote Inc. | 10001 | 2 |

| Mouse House | 10002 | 0 |

| Wascals | 10003 | 1 |

| Yosemite Place | 10004 | 1 |

| E Fudd | 10005 | 1 |

+----------------+---------+---------+

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

组合查询 UNION

MySQL也允许执⾏多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。
这些组合查询通常称为并（union）或复合查询（compound query）。

UNION规则

UNION必须由两条或两条以上的SELECT语句组成，语句之间⽤关键字UNION分隔（因此，如果组合4条SELECT语句，将要使⽤3个UNION关键字）。
UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的⽇期类型）。

--假如需要价格⼩于等于5的所有物品的⼀个列表，⽽且还想包括供应商1001和1002⽣产的所有物品。

-- 先查询第⼀个结果

select vend_id,prod_id,prod_price from products where prod_price <= 5;

+---------+---------+------------+

| vend_id | prod_id | prod_price |

+---------+---------+------------+

| 1003 | FC | 2.50 |

| 1002 | FU1 | 3.42 |

| 1003 | SLING | 4.49 |

| 1003 | TNT1 | 2.50 |

+---------+---------+------------+

4 rows in set (0.00 sec)

-- 再查询第⼆个结果

select vend_id,prod_id,prod_price from products where vend_id in(1001,1002);

+---------+---------+------------+

| vend_id | prod_id | prod_price |

+---------+---------+------------+

| 1001 | ANV01 | 5.99 |

| 1001 | ANV02 | 9.99 |

| 1001 | ANV03 | 14.99 |

| 1002 | FU1 | 3.42 |

| 1002 | OL1 | 8.99 |

+---------+---------+------------+

5 rows in set (0.00 sec)

--使⽤union将两个sql⼀并执⾏

select vend_id,prod_id,prod_price from products where prod_price <= 5

union

select vend_id,prod_id,prod_price from products where vend_id in(1001,1002);

+---------+---------+------------+

| vend_id | prod_id | prod_price |

+---------+---------+------------+

| 1003 | FC | 2.50 |

| 1002 | FU1 | 3.42 |

| 1003 | SLING | 4.49 |

| 1003 | TNT1 | 2.50 |

| 1001 | ANV01 | 5.99 |

| 1001 | ANV02 | 9.99 |

| 1001 | ANV03 | 14.99 |

| 1002 | OL1 | 8.99 |

+---------+---------+------------+

8 rows in set (0.09 sec)

-- 这条语句由前⾯的两条SELECT语句组成，语句中⽤UNION关键字分隔。

-- UNION指示MySQL执⾏两条SELECT语句，并把输出组合成单个查询结果集

-- 以下是同样结果,使⽤where的多条件来实现

select vend_id,prod_id,prod_price from products where prod_price <= 5 or

vend_id in (1001,1002);

+---------+---------+------------+

| vend_id | prod_id | prod_price |

+---------+---------+------------+

| 1001 | ANV01 | 5.99 |

| 1001 | ANV02 | 9.99 |

| 1001 | ANV03 | 14.99 |

| 1003 | FC | 2.50 |

| 1002 | FU1 | 3.42 |

| 1002 | OL1 | 8.99 |

| 1003 | SLING | 4.49 |

| 1003 | TNT1 | 2.50 |

+---------+---------+------------+

8 rows in set (0.00 sec)

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

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

现在思考⼀个问题,上⾯的语句分别返回了⼏条数据?
第⼀条sql返回4⾏,第⼆条sql返回5⾏,那么union返回了⼏⾏?
UNION从查询结果集中⾃动去除了重复的⾏（换句话说，它的⾏为与单条SELECT语句中使⽤多个WHERE⼦句条件⼀样）。

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

SELECT语句的输出⽤ORDER BY⼦句排序。在⽤UNION组合查询时，只能使⽤⼀条ORDER BY⼦句，它必须出现在最后⼀条SELECT语句之后。

对组合查询结果排序

对于结果集，不存在⽤⼀种⽅式排序⼀部分，⽽⼜⽤另⼀种⽅式排序另⼀部分的情况，因此不允许使⽤多条ORDER BY⼦句。

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

+---------+---------+------------+

| vend_id | prod_id | prod_price |

+---------+---------+------------+

| 1003 | FC | 2.50 |

| 1003 | TNT1 | 2.50 |

| 1002 | FU1 | 3.42 |

| 1003 | SLING | 4.49 |

| 1001 | ANV01 | 5.99 |

| 1002 | OL1 | 8.99 |

| 1001 | ANV02 | 9.99 |

| 1001 | ANV03 | 14.99 |

+---------+---------+------------+

8 rows in set (0.00 sec)

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

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

总结：表联结

内部联结：where, join(inner join)
自联结：是在一个sql中，用当前这个表，连接自己这个表进行关联查询。
外部联结：left join,right join

练习题

列出所有产品以及订购数量，包括没有⼈订购的产品；

select products.prod_id,products.prod_name,count(orderitems.quantity) as num_ord from products left join orderitems on products.prod_id = orderitems.prod_id group by products.prod_id;

sql_mode

sql_mode是MySQL数据库中的一个环境变量定义了mysql应该支持的sql语法，数据校验等.
可以通过 select @@sql_mode; 查看当前数据库使用的sql_mode

查看当前数据库的sql_mode 

select @@sql_mode; 

修改sql_mode 

1. 在当前数据库中进行修改(服务器重启后失败) 

set @@sql_mode= 'xxx' 

2. 修改配置文件 my.cnf 

sql_mode = 'xxxx' 

修改完成后要重启mysql服务 

brew services stop mysql@5.7

sql_mode值的含义

关于ONLY_FULL_GROUP_BY是否开启的建议:

建议开启,符合SQL标准分组字段.(和关闭only_full_group_by模式相同)

MySQL事务

概述

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

事务的语法

事务的特性

事务并发问题

事务隔离级别

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

隐式提交(了解)

⼀,事务的语法

start transaction;/ begin;

commit; 使得当前的修改确认

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

⼆,事务的ACID特性

1. 原⼦性（Atomicity）

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

全部执⾏成功
全部执⾏失败

事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执⾏过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发⽣⼀样。也就是说事务是⼀个不可分割的整体，就像化学中学过的原⼦，是物质构成的基本单位。

2. ⼀致性（Consistency）

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

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

3. 隔离性（Isolation）

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

⼀个事务内部的操作及使⽤的数据对其它并发事务是隔离的，并发执⾏的各个事务是不能互相⼲扰的。

隔离性分4个级别，下⾯会介绍。

4. 持久性（Duration）

事务的持久性是指事务⼀旦提交后，数据库中的数据必须被永久的保存下来。即使服务器系统崩溃或服务器宕机等故障。只要数据库重新启动，那么⼀定能够将其恢复到事务成功结束后的状态。

三,事务的并发问题

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

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

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

四,事务隔离级别

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

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

select @@tx_isolation;

设置当前会话中的事务隔离级别

set session transaction isolation level read uncommitted;

1. 读未提交（READ_UNCOMMITTED）

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

脏读示例：

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

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

2. 读已提交（READ_COMMITTED）

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

不可重复读示例

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

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

3. 可重复读（REPEATABLE_READ）

可重复读就是保证在事务处理过程中，多次读取同⼀个数据时，该数据的值和事务开始时刻是⼀致的。因此该事务级别限制了不可重复读和脏读，但是有可能出现幻读的数据。

幻读

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

诡异的更新事件

4. 顺序读（SERIALIZABLE）

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

五,不同的隔离级别的锁的情况(了解)

读未提交（RU）: 有⾏级的锁，没有间隙锁。它与RC的区别是能够查询到未提交的数据。

读已提交（RC）：有⾏级的锁，没有间隙锁，读不到没有提交的数据。

可重复读（RR）：有⾏级的锁，也有间隙锁，每次读取的数据都是⼀样的，并且没有幻读的情况。

序列化（S）：有⾏级锁，也有间隙锁，读表的时候，就已经上锁了

六,隐式提交(了解)

DQL:查询语句

DML:写操作(添加,删除,修改)DDL:定义语句(建库,建表,修改表,索引操作,存储过程,视图)

DCL:控制语⾔(给⽤户授权,或删除授权)

DDL（Data Defifine Language）：都是隐式提交。

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

https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

MySQL存储过程

什么是存储过程?

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

例如以下的情形。

为了处理订单，需要核对以保证库存中有相应的物品。

如果库存有物品，需要预定以便不将它们再卖给别的⼈，并减少可⽤的物品数量以反映正确的库存量。

库存中没有的物品需要订购，这需要与供应商进⾏某种交互。

执⾏这个处理需要针对许多表的多条MySQL语句。可能需要执⾏的具体语句及其次序也不是固定的。

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

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

可以创建存储过程。

存储过程简单来说，就是为以后的使⽤⽽保存的⼀条或多条MySQL语句的集合。

储存过程是⼀组为了完成特定功能的SQL语句集，经过编译之后存储在数据库中，在需要时直接调⽤。

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

为什么要使⽤存储过程?

优点:

可以把⼀些复杂的sql进⾏封装,简化复杂操作

保证了数据的完整性,防⽌错误

简单的变动只需要更改存储过程的代码即可

提⾼性能。因为使⽤存储过程⽐使⽤单独的SQL语句要快。(预先编译)

缺点:

存储过程的编写⽐SQL语句复杂

⼀般可能还没有创建存储过程的权限,只能调⽤

个⼈观点

业务逻辑不要封装在数据库⾥⾯,应该由应⽤程序(JAVA、Python、PHP)处理。

让数据库只做它擅⻓和必须做的，减少数据库资源和性能的消耗。

维护困难，⼤量业务逻辑封装在存储过程中，造成业务逻辑很难剥离出来。动A影响B。

⼈员也难招聘，因为既懂存储过程，⼜懂业务的⼈少。使⽤困难。

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

创建存储过程

\d // 修改MySQL默认的语句结尾符 ; ，改为 // 。
create procedure 创建语句
BEGIN和END语句⽤来限定存储过程体

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

执⾏储存

call p1()

查看存储过程

show create procedure p1\G

删除存储过程

drop procedure p1

MySQL的触发器

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

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

例如:

每当增加⼀个顾客到某个数据库表时，都检查其电话号码格式是否正确;

每当订购⼀个产品时，都从库存数量中减去订购的数量;

⽆论何时删除⼀⾏，都在某个存档表中保留⼀个副本。

所有这些例⼦的共同之处是它们都需要在某个表发⽣更改时⾃动处理。这确切地说就是触发器。

触发器的定义

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

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

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

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

触发器语法

CREATE TRIGGER trigger_name trigger_time trigger_event

 ON tbl_name FOR EACH ROW trigger_stmt

说明：

\# trigger_name：触发器名称

\# trigger_time:触发时间，可取值：BEFORE或AFTER

\# trigger_event：触发事件，可取值：INSERT、UPDATE或DELETE。

\# tb1_name：指定在哪个表上

\# trigger_stmt：触发处理SQL语句。

-- 查看所有的 触发器

show triggers\G;

-- 删除触发器

drop trigger trigger_name;

触发器Demo

注意:如果触发器中SQL有语法错误,那么整个操作都会报错

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

-- 1,复制当前的⼀个表结构
create table del_users like users;

-- 2,创建 删除触发器 注意在创建删除触发器时,只能在删除之前才能获取到old(之前的)数据
\d //
create trigger deluser before delete on users for each row
begin
insert into del_users values(old.id,old.name,old.age,old.account);
end;
//
\d ;
-- 3 删除users表中的数据去实验

create trigger user_count after insert on users for each row
begin
update user_count set num=num+1 
end;
create trigger user_count before delete on users for each row
begin
update user_count set num=num-1 
end;
create trigger user_count after count on users for each row
begin
show user_counts;
end;
//

tips：

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

在DELETE触发器代码内，可以引⽤⼀个名为OLD的虚拟表，访问被删除的⾏;
- OLD中的值全都是只读的，不能更新。
- 在AFTER DELETE的触发器中⽆法获取OLD虚拟表

在UPDATE触发器代码中
- 可以引⽤⼀个名为OLD的虚拟表访问更新以前的值
- 可以引⽤⼀个名为NEW的虚拟表访问新更新的值;

练习题

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

-- 1.创建⼀个表, users_count ⾥⾯有⼀个 num的字段 初始值为0或者是你当前users表中的count

-- 2,给users表创建⼀个触发器

-- 当给users表中执⾏insert添加数据之后,就让users_count⾥⾯num+1,

-- 当users表中的数据删除时,就让users_count⾥⾯num-1,

-- 想要统计users表中的数据总数时,直接查看 users_count

MySQL中的视图

什么是视图？

视图是虚拟的表。与包含数据的表不⼀样，视图只包含使⽤时动态检索数据的查询。

视图仅仅是⽤来查看存储在别处的数据的⼀种设施或⽅法。

视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。

在添加或更改这些表中的数据时，视图将返回改变过的数据。

因为视图不包含数据，所以每次使⽤视图时，都必须处理查询执⾏时所需的任⼀个检索。

如果你⽤多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。

视图的作⽤

重⽤SQL语句。

简化复杂的SQL操作。在编写查询后，可以⽅便地重⽤它⽽不必知道它的基本查询细节。

使⽤表的组成部分⽽不是整个表。

保护数据。可以给⽤户授予表的特定部分的访问权限⽽不是整个表的访问权限。

更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

注意:视图不能索引，也不能有关联的触发器或默认值。

视图的基础语法

创建视图:

create view v_users as select id,name,age from users where age >= 25 and age<= 35;

-- Query OK, 0 rows affected (0.00 sec)

view视图的帮助信息:

mysql> ? view

ALTER VIEW

CREATE VIEW

DROP VIEW

查看当前库中所有的视图

show tables; --可以查看到所有的表和视图

show table status where comment='view'; --只查看当前库中的所有视图

删除视图v_t1:

mysql> drop view v_t1;

MySQL索引与SQL优化

索引的概述与分类

什么是索引？

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

MySQL官方对索引的定义：是帮助MySQL高效获取数据的数据结构
我们可以简单理解为：快速查找排好序的一种数据结构。

索引的分类（索引效率从上往下依次降低）

主键索引

即主索引，根据主键简历索引，不允许重复，不允许空值；

创建，修改，删除主键索引的方法：
唯一索引

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

创建，添加，删除唯一索引

普通索引

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

创建，添加，删除普通索引

全文索引

用大文本对象（如text类型）的列构建的索引

组合索引

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

添加，删除组合索引

组合索引的“最左”原则

总结

索引就像是一本书的目录是为了提高数据的检索速度
在MySQL中有不同的索引类型，要求和效率也各不一样

索引原理——索引与B+Tree

B树

B树即平衡查找树，一般理解为平衡多路查找树。

B树是一种自平衡树状数据结构，一般多用于存储系统上，比如数据库或文件系统。

B树的遍历

B树的缺点

B+树

什么是B+树

B+树的优势

总结

主索引和辅助索引

聚簇索引和非聚簇索引

聚簇索引：索引即数据，数据即索引
非聚簇索引：找到索引仅仅是找到当前索引值和key，如果需要索引外的内容，则需要回表。

以上关于索引原理和聚簇与非聚簇索引都是以InnoDB表引擎为基础。

MyISAM的索引

MyISAM中主键，索引，数据三者是分开的，需要通过索引回表来获取数据
MyISAM中的所有索引都是非聚簇索引。

MyISAM和InnoDB的区别

总结

慢查询与SQL优化

什么是慢查询

慢查询配置

慢查询日志

查看执行计划—explain

使用Explain分析一下SQL

通过name字段搜索全部

通过id主键索引单个值

显然，使用id主键索引时比使用name进行查找快得多了，为了解决此问题，我们可给name字段添加索引

name字段添加索引

SQL优化

主要分为适当简历索引和合理使用索引

适当建立索引
- 1.创建并使用自增数字来建立主键索引
- 2.经常作为where条件的字段建立索引
- 3.添加索引的字段尽可能的保持唯一性
- 4.可考虑使用联合索引并进行索引覆盖
合理使用索引 MySQL索引通常是被用于提高WHERE条件的数据行匹配时的搜索速度, 在索引的使用过程中，存在一些使用细节和注意事项。因为不合理的使用可能会导致建立了索引之后，不- -定就使用上了索引
- 1.不要在列上使用函数和进行运算
- 2.饮食转换可能影响索引失效
- 3.like语句的索引失效问题
- 4.复合索引的使用
  - 1.多个单列索引并不是最佳选择 MySQL只能使用一个索引，会从多个索引中选择多个限制最为严格的索引，因此,为多个列创建单列索引，并不能提高MySQL的查询性能。
  - 2.复合索引的最左前缀原则查询条件中使用了复合索引的第一个字段，索引会被使用。因此，在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找，则无法使用索引。
  - 3.尽可能达成索引覆盖如果一个索引包含所有需要的查询的字段的值，直接根据索引的查询结果返回数据，而无需读表，能够极大的提高性能。因此，可以定义一个让索引包含的额外的列，即使这个列对于索引而言是无用的。

总结

tuc1

码上学习笔记

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库技术之MySQL高级

目录子查询与表连接子查询(嵌套sql)利⽤⼦查询进⾏过滤作为计算字段使⽤⼦查询外键表关系关系表表联结联结多个表使⽤表别名 AS组合查询 UNION总结：表联结练习题sql_modesql_mode值的含义MySQL事务概述⼀,事务的语法⼆,事务的ACID特性三,事务的并发问题四,事务隔离级别1. 读未提交（READ_UNCOMMITTED）2. 读已提交（READ_COMMITTED）3. 可重复读（REP.
复制链接

扫一扫