文章目录
前两篇笔记传送门
MySQL笔记（一）：设计范式、基础概念、数据库定义语言DDL
MySQL笔记（二） 数据库操纵语言DML 、数据库查询语言DQL、数据库控制语言DCL、计算字段、子查询、函数、聚集函数
联结
SQL最强大的功能之一就是能在数据检索查询的执行中联结（join）表，联结是利用SQL的SELECT能执行的最重要的操作
关系表
假如有一个包含产品目录的数据库表，其中每种类别的物品占一行。对于每种物品要存储的信息包括产品描述和价格，以及生产该产品的供应商信息。
现在，假如有由同一供应商生产的多种物品，那么在何处存储供应商信息（如，供应商名、地址、联系方法等）呢？将这些数据与产品信息分开存储的理由如下。
❑ 因为同一供应商生产的每个产品的供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间。
❑ 如果供应商信息改变（例如，供应商搬家或电话号码变动），只需改动一次即可。
❑ 如果有重复数据（即每种产品都存储供应商信息），很难保证每次输入该数据的方式都相同。不一致的数据在报表中很难利用。
关键是，相同数据出现多次决不是一件好事，此因素是关系数据库设计的基础。关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值（即关系设计中的关系（relational））互相关联。
在这个例子中，可建立两个表，一个存储供应商信息，另一个存储产品信息。vendors表包含所有供应商信息，每个供应商占一行，每个供应商具有唯一的标识。此标识称为主键（primary key），可以是供应商ID或任何其他唯一值。
products表只存储产品信息，它除了存储供应商ID（vendors表的主键）外不存储其他供应商信息。vendors表的主键又叫作products的外键，它将vendors表与products表关联，利用供应商ID能从vendors表中找出相应供应商的详细信息。
外键（foreign key） 外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
这样做的好处如下：
❑ 供应商信息不重复，从而不浪费时间和空间；
❑ 如果供应商信息变动，可以只更新vendors表中的单个记录，相关表中的数据不用改动；
❑ 由于数据无重复，显然数据是一致的，这使得处理数据更简单
总之，关系数据可以有效地存储和方便地处理。因此，关系数据库的可伸缩性远比非关系数据库要好
可伸缩性（scale） 能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好（scale well）
为什么要使用联结
分解数据为多个表能更有效地存储，更方便地处理，并且具有更大的可伸缩性
如果数据存储在多个表中，怎样用单条SELECT语句检索出数据？
答案是使用联结。简单地说，联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。
维护引用完整性
联结不是物理实体。换句话说，它在实际的数据库表中不存在。联结由MySQL根据需要建立，它存在于查询的执行当中。
在使用关系表时，仅在关系列中插入合法的数据非常重要。回到这里的例子，如果在products表中插入拥有非法供应商ID（即没有在vendors表中出现）的供应商生产的产品，则这些产品是不可访问的，因为它们没有关联到某个供应商
为防止这种情况发生，可指示MySQL只允许在products表的供应商ID列中出现合法值（即出现在vendors表中的供应商）。这就是维护引用完整性，它是通过在表的定义中指定主键和外键来实现的
使用
SELECT tid,sid
FROM teacher,student
WHERE tid = sid
ORDER BY tid,sid;
在联结两个表的时候，我们实际上是在将第一个表中的每一行与第二个表中的每一行进行配对，所以WHERE语句就显得尤其关键，这样才能显示出符合条件的列
笛卡尔积：由没有联结条件的表关系返回的结果为笛卡尔积，检索出的行的数目是行数之积；
内部联结
目前为止用的所有联结称为等值联结（equijoin），基于两个表之间的相等测试，这种联结也称为内部联结
以INNER JOIN指定，在使用这种语法的时候要使用ON子句而不是WHERE，传递给ON的实际条件与传递给WHERE的相同
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
联结多个表
SQL对一条SELECT语句可以联结的表的数目没有限制，创建联结的基本规则也相同，首先列出所有的表然后定义关系
创建高级联结
使用表别名
别名除了用于列名和计算字段之外，SQL还允许给表名起别名，这样做有两个理由
- 缩短SQL语句
- 允许在单条SELECT语句中多次使用相同的表
例
SELECT cust.name, cust.contact
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'TNT2';
注意：表别名只在查询执行中使用，与列别名不一样，表别名不返回到客户机
自联接
自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然结果一样但是处理联结比处理子查询快的多
子查询 vs 联结
子查询 ：并不是执行复杂SELECT操作的最有效的方法
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'));
联结
SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'TNT2';
自然联结
自然联结排除多次出现，使每个列只返回一次；只能选择那些唯一的列，一般是通过对表使用通配符，对所有其他表的列使用明确的子集来完成的
事实上，我们所建立的每个内部联结都是自然联结，且大概率用不到不是自然联结的内部联结
SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'FB';
在这个例子中，通配符只对第一个表使用，所有其他列明确列出，所以没有重复的列被检索出来
外部联结 OUTER JOIN
许多联结需要将一个表中的行与另一个表中的行相关联，但有时候也需要包含没有关联行的
定义：联结中包含了在相关表中没有关联行的行
在使用 OUTER JOIN语法的时候，必须使用 RIGHT或者LEFT 关键字指定包括其所有行的表
外部联结的类型
存在两种基本的外部联结形式：左外部联结和右外部联结。它们之间的唯一差别是所关联的表的顺序不同。换句话说，左外部联结可通过颠倒FROM或WHERE子句中表的顺序转换为右外部联结。因此，两种类型的外部联结可互换使用，而究竟使用哪一种纯粹是根据方便而定
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;
使用带聚集函数的联结
SELECT customers.cust_name, customers_id, COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;
要点
- 注意所使用的联结类型，一般使用内部联结
- 保证使用正确的联结条件
- 总是提供联结条件
- 一个联结中可以包含多个表，甚至对于每个联结都可以采用不同的联结类型，虽然合法，且有用，但应该在测试他们之前分别测试每一个联结，方便排除故障
组合查询 UNION
MySQL允许执行多个查询（SELECT），并将结果作为单个查询结果集返回，这些组合查询通常称为并（union）和符合查询（compound query）
有两种情况需要使用组合查询
在单个查询中从不同的表返回类似结构的数据
对单个表执行多个查询按单个查询返回数据
组合查询和多个WHERE条件 多数情况下，组合相同表的两个查询完成的工作与具有多个WHERE子句条件的单条查询完成的工作相同。换句话说，任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询给出，在以下段落中可以看到这一点。这两种技术在不同的查询中性能也不同。因此，应该试一下这两种技术，以确定对特定的查询哪一种性能更好。
UNION 可以极大的简化复杂的WHERE子句、简化从多个表中检索数据的工作
创建
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);
对于更复杂的过滤条件，或者从多个表中检索数据的情形，使用UNION可能会使处理更加简单
规则
- 必须是两条及以上的SELECT语句
- 每个查询必须包含相同的列、表达式或聚集函数
- 列数据类型必须兼容，类型不必完全相同，但必须是DBMS可以隐式转换的类型
注意
UNION从查询结果中自动去重，如果需要返回所有匹配行，可使用UNION ALL,这个时候WHERE也取代不了它的工作了
组合查询可以应用于不同的表
全文本搜索
InnoDB引擎不支持全文本搜索
在使用全文本搜索的时候，MySQL不需要分别查看每个行，不需要分别分析和处理每个词，MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行，从而高效判断
为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的更新不断地重新索引，在对表列进行适当设计后，MySQL会自动进行所有索引和重新索引
CREATE TABLE test (
note_id int NOT NULL
...
note_text text NULL,
PRIMARY KEY(note_id),
FULLTEXT(note_text)) ENGINE = MyISAM;
不要在导入数据时使用FULLTEXT 更新索引要花时间，虽然不是很多，但毕竟要花时间。如果正在导入数据到一个新表，此时不应该启用FULLTEXT索引。应该首先导入所有数据，然后再修改表，定义FULLTEXT。这样有助于更快地导入数据（而且使索引数据的总时间小于在导入每行时分别进行索引所需的总时间）
在索引之后，使用两个函数Match() 和 Against() 指定要使用的搜索表达式
SELECT note_text
FROM productionnotes
WHERE Match(note_text) Against('rabbit'); #WHERE note_text) LIKE %rabbit%;
使用完整的Match() 传递给match的值必须于fulltext()定义中相同，如果指定多个列，则必须依次列出
搜索除非使用BINARY否则不区分大小写
查询拓展
SELECT note_text
FROM productionnotes
WHERE Match(note_text) Against('rabbit' WITH QUERY EXPANSION);
布尔文本搜索
- 提供的细节：
- 要匹配的词；
- 要排斥的词；
- 排列提示（指定某些词更加重要）；排列但不降序
- 表达式分组
- 另外一些内容
SELECT note_text
FROM productionnotes
WHERE Match(note_text) Against('rabbit' IN BOOLEAN MODE);
SELECT note_text
FROM productionnotes
WHERE Match(note_text) Against('rabbit -rope*' WITH QUERY EXPANSION); #指示排除包含rope的
SELECT note_text
FROM productionnotes
WHERE Match(note_text) Against('rabbit rope' WITH QUERY EXPANSION); #两者有一个就行
总结
- 在索引全文本数据时，短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）
- MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表
- 许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。因此，MySQL规定了一条
50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE - 如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）
- 忽略词中的单引号 例如，don’t索引为dont
- 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果
- 如前所述，仅在MyISAM数据库引擎中支持全文本搜索
- 没有邻近操作符 邻近搜索是许多全文本搜索支持的一个特性，它能搜索相邻的词（在相同的句子中、相同的段落中或者在特定数目的词的部分中，等等）
视图
视图本质就是一个查询的结果，不过我们每次都可以通过打开视图来按照我们想要的样子查看数据。既然视图本质就是一个查询的结果，那么它本身就是一个虚表，并不是真实存在的，数据实际上还是存放在原来的表中
为什么使用视图
我们已经看到了视图应用的一个例子。下面是视图的一些常见应用
- 重用SQL语句。
- 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。
- 使用表的组成部分而不是整个表。
- 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
- 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
在视图创建之后，可以用与表基本相同的方式利用它们。可以对视图执行SELECT操作，过滤和排序数据，将视图联结到其他视图或表，甚至能添加和更新数据（添加和更新数据存在某些限制）
视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据
性能问题 因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。
视图的规则和限制
- 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）
- 对于可以创建的视图数目没有限制
- 为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予
- 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图
- ORDER BY可以用在视图中，但如果从该视图检索数据的SELECT语句中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖
- 视图不能索引，也不能有关联的触发器或默认值。
- 视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句。
创建视图
CREATE VIEW 视图名称(列名) as 子查询语句 [WITH CHECK OPTION];
mysql> CREATE VIEW need as SELECT * FROM student WHERE sex = 'male';
WITH CHECK OPTION是指当创建后，如果更新视图中的数据，是否要满足子查询中的条件表达式，不满足将无法插入，创建后，我们就可以使用select语句来直接查询视图上的数据了，因此，还能在视图的基础上，导出其他的视图。
- 若视图是由两个以上基本表导出的，则此视图不允许更新。
- 若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。
- 若视图的字段来自集函数，则此视图不允许更新。
- 若视图定义中含有GROUP BY子句，则此视图不允许更新。
- 若视图定义中含有DISTINCT短语，则此视图不允许更新。
- 若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。例如将成绩在平均成绩之上的元组定义成一个视图GOOD_SC： CREATE VIEW GOOD_SC AS SELECT Sno, Cno, Grade FROM SC WHERE Grade > (SELECT AVG(Grade) FROM SC); 导出视图GOOD_SC的基本表是SC，内层查询中涉及的表也是SC，所以视图GOOD_SC是不允许更新的。
- 一个不允许更新的视图上定义的视图也不允许更新
删除视图
drop view 名字;
索引
当数据量变得非常庞大的时候，一个索引能很好的帮助；能够快速定位元素的位置
不能过度使用，会占用资源
创建索引
-- 创建索引
CREATE INDEX 索引名称 ON 表名 (列名)
-- 查看表中的索引
show INDEX FROM student
mysql> CREATE INDEX i ON student(name);
mysql> SHOW INDEX FROM student;
删除索引
DROP INDEX 索引名称 FROM 表名;
触发器
触发器就像其名字一样，在某种条件下会自动触发，在select/update/delete时，会自动执行预先设定的内容，触发器通常用于检查内容的安全性，相比直接添加约束，触发器显得更加灵活。
触发器所依附的表称为基本表，当触发器表上发生select/update/delete等操作时，会自动生成两个临时的表（new表和old表，只能由触发器使用）
比如在insert操作时，新的内容会被插入到new表中；在delete操作时，旧的内容会被移到old表中，我们仍可在old表中拿到被删除的数据；在update操作时，旧的内容会被移到old表中，新的内容会出现在new表中
CREATE TRIGGER 触发器名称 [BEFORE / AFTER] [INSERT/UPDATE/DELETE] ON 表名/视图名 FOR EACH ROW DELETE FROM student WHERE student.sno = new.sno;
mysql> CREATE TRIGGER t BEFORE DELETE ON student FOR EACH ROW DELETE FROM teach WHERE old.sid = teach.sid;
查看触发器
SHOW TRIGGERS;
删除触发器
DROP TRIGGER 触发器名称;
注：要保持每个数据库的触发器名称唯一；只有表才支持触发器
INSERT触发器
- 可引用一个名为NEW的虚拟表，访问被插入的行
- 在BEFORE INSERT触发器当中，NEW的值可以被更新，允许更改被插入的值
- 对于AUTO INCREMENT列，NEW 在 INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值
DELETE触发器
在DELETE触发器内部，可以访问一个名为OLD的虚拟表，访问被删除的行；
OLD中的值都只可读，不能更新
UPDATE 触发器
- 可以引用一个名为OLD的虚拟表访问UPDATE语句前的值，引用一个名为NEW的虚拟表访问新更新的值
- 在BEFORE UPDATE 触发器中，NEW的值可能也被更新（允许更改将要用于UPDATE语句中的值）
- OLD中的值都只是可读，不能更新
进一步介绍
- 创建触发器可能需要特殊的安全访问权限，但是触发器的执行是自动的，如果语句能执行，则相关触发器也可执行
- 应该用触发器来保证数据的一致性；优点是他总是进行这种处理，而且是透明的进行，与客户机应用无关
- 非常有意义的使用是创建审计跟踪，使用触发器把更改记录到另一个表非常容易
- 不能从触发器调用存储过程，所需存储过程代码需要复制到触发器内（MySQL触发器中不支持CALL语句）
事务
当我们要进行的删除非常多的时候，需要执行大量的SQL语句，这些数据库操作语句就会构成一个事务
只有InnoDB才支持事务
查看引擎
SHOW ENGINES;
特性
原子性 一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样
一致性 在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作
隔离性 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）
持久性 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失
使用
通过以下例子来探究事务
begin; #开始事务
...
rollback; #回滚事务
savepoint 回滚点; #添加回滚点
rollback to 回滚点; #回滚到指定回滚点，不能回退CREATE;DROP;SELECT语句
...
commit; #提交事务
-- 一旦提交，就无法再进行回滚了！
SET autocommit = 0; #指示MySQL不自动提交更改，不管有没有COMMIT语句；autocommit标志是针对每个连接而不是服务器
注意：此处虽然已经体现添加内容，但实际上在数据库中还是没有添加的，在commit之后，才是真正的添加
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
