VIEW（视图）

作用：

  • 简化sql语句；
  • 减小数据库设计改动带来的影响；

  • 限制对基础表的访问和改动。

    创建视图

    只是创建视图的话，可以只使用CREATE VIEW sales_by_client…；
    修改视图的方法：

  • 在数据库中删除原视图DROP VIEW…，然后再创建，否则会报错；

  • 在CREATE VIEW中间加上OR REPLACE，这样数据库会自动删除原视图，再创建视图。

    1. CREATE OR REPLACE VIEW sales_by_client AS
    2. SELECT
    3.   c.client_id,
    4.   c.name,
    5.   SUM(invoice_total) AS total_sales
    6. FROM clients c
    7.        JOIN invoices USING (client_id)
    8. GROUP BY client_id,name;

    可更新视图

    创建视图的语句中不含有以下的视图是可更新视图：

  • DISTINCT

  • 聚合函数（MIN，MAX，SUM…）
  • GROUP BY / HAVING

  • UNION

    存储过程

    存储过程是一个包含一堆SQL代码的数据库对象，在应用代码里，可以调用这些过程来获取或保存数据，所以我们使用存储过程来存储和管理SQL代码。
    优点：

  • 执行速度更快

  • 数据安全性更强

    创建存储过程

    这里改表分隔符的原因是希望MySQL将CREATE PROCEDURE get_clients() BEGIN SELECT * FROM clients; END作为一个整体。
    1. DELIMITER $$ -- 改变默认分隔符；
    2. CREATE PROCEDURE get_clients()
    3. BEGIN
    4.   SELECT * FROM clients;
    5. END$$
    6. DELIMITER ;
    image.png
    调用方法：CALL get_clients();

    删除存储过程

    1. DROP PROCEDURE IF EXISTS get_clients;

    参数

    使用参数传递值和为调用程序赋值，

    基本用法

    ```plsql DROP PROCEDURE IF EXISTS get_clients_by_state; DELIMITER $$ CREATE PROCEDURE get_clients_by_state ( state CHAR(2) ) BEGIN SELECT * FROM clients c WHERE c.state = state; END$$ DELIMITER ;

CALL get_clients_by_state(‘CA’);

  1. <a name="cd3G4"></a>
  2. ### 带默认值的参数
  3. ```plsql
  4. DROP PROCEDURE IF EXISTS get_clients_by_state;
  5. DELIMITER $$
  6. CREATE PROCEDURE get_clients_by_state
  7. (
  8.     state CHAR(2)
  9. )
  10. BEGIN
  11.     IF state IS NULL THEN
  12.         SET state = 'CA';
  13.     end if;
  14.     SELECT * FROM clients c
  15.     WHERE c.state = state;
  16.     -- SELECT * FROM clients c
  17.     -- WHERE c.state = IFNULL(state, c.state);
  18. END$$
  19. DELIMITER ;
  21. CALL get_clients_by_state(NULL);

参数验证

确认存储过程不会往数据库存储错误数据
IF payment_amount <= 0 THEN
SIGNAL SQLSTATE '22003'
SET MESSAGE_TEXT = 'Invalid payment amout';
end if ;

  1. DROP PROCEDURE IF EXISTS make_payment;
  2. DELIMITER $$
  3. CREATE PROCEDURE make_payment
  4. (
  5.     invoice_id INT,
  6.     payment_amount DECIMAL(9, 2),
  7.     payment_date DATE
  8. )
  9. BEGIN
  10.     IF payment_amount <= 0 THEN
  11.         SIGNAL SQLSTATE '22003'
  12.             SET MESSAGE_TEXT = 'Invalid payment amout';
  13.     end if ;
  14.     UPDATE invoices i
  15.     SET
  16.         i.payment_total = payment_amount,
  17.         i.payment_date = payment_date
  18.     WHERE i.invoice_id = invoice_id;
  19. end $$
  20. DELIMITER ;
  22. call make_payment(2, -100, '2019-01-01');

输出参数

  1. DROP PROCEDURE IF EXISTS get_inpaid_invoices_for_client;
  2. DELIMITER $$
  3. CREATE PROCEDURE  get_inpaid_invoices_for_client
  4. (
  5.     client_id INT,
  6.     OUT invoices_count INT,
  7.     OUT invoice_total DECIMAL(9, 2)
  8. )
  9. BEGIN
  10.     SELECT COUNT(*),SUM(invoice_total)
  11.     INTO invoices_count, invoice_total
  12.     FROM invoices i
  13.     WHERE i.client_id = client_id
  14.         AND payment_total = 0;
  15. end $$
  16. DELIMITER ;
  17. -- 调用get_inpaid_invoices_for_clent;
  18. set @invoices_count = 0;
  19. set @invoice_total = 0;
  20. call get_inpaid_invoices_for_client(
  21.         3,
  22.         @invoices_count,
  23.         @invoice_total
  24.     );
  25. select @invoices_count,@invoice_total;

变量

  1. -- user or session variables（用户变量）
  2. -- 在客户端被关闭前一直存在；
  3. SET @invoices_count = 0;
  5. -- local variable（本地变量）
  6. -- 不会在整个客户端会话过程中被保存，一旦存储过程完成任务，这些变量就被清空；
  7. DELIMITER $$
  8. CREATE PROCEDURE get_risk_factor()
  9. BEGIN
  10.     DECLARE risk_factor DECIMAL(9,2) DEFAULT  0 ;
  11.     DECLARE invoices_total DECIMAL(9, 2);
  12.     DECLARE invoices_count INT;
  14.     SELECT COUNT(*),SUM(invoice_total)
  15.     INTO invoices_count,invoices_total
  16.     FROM invoices;
  18.     SET risk_factor = invoices_total / invoices_count * 5;
  20.     SELECT  risk_factor;
  21. end $$
  22. DELIMITER ;

函数

  1. DELIMITER $$
  2. CREATE FUNCTION get_risk_factor_for_client
  3. (
  4.     client_id INT
  5. )
  6. -- 设置返回值类型
  7. RETURNS INT
  8. -- 设置函数属性
  9. -- DETERMINISTIC -- 确定性，同样的输入，同样的输出
  10. READS SQL DATA -- 函数中会配置选择语句，用以读取数据
  11. -- MODIFIES SQL DATA -- 函数中有插入、更新或删除
  12. BEGIN
  13.     DECLARE risk_factor DECIMAL(9,2) DEFAULT  0 ;
  14.     DECLARE invoices_total DECIMAL(9, 2);
  15.     DECLARE invoices_count INT;
  17.     SELECT COUNT(*),SUM(invoice_total)
  18.     INTO invoices_count,invoices_total
  19.     FROM invoices i
  20.     WHERE i.client_id = client_id;
  22.     SET risk_factor = invoices_total / invoices_count * 5;
  24.     RETURN IFNULL(risk_factor, 0);
  25. end $$
  27. DELIMITER ;
  29. SELECT
  30.     client_id,
  31.     name,
  32.     get_risk_factor_for_client(client_id)
  33. FROM clients

触发器和事件

（trigger）触发器

触发器是在插入、更新和删除语句前后自动执行的一堆SQL代码，通常使用触发器增强数据一致性。

基本用法

  1. DELIMITER $$
  2. CREATE TRIGGER payment_after_insert
  3.     AFTER INSERT ON payments
  4.     FOR EACH ROW
  5. BEGIN
  6.     UPDATE invoices
  7.         SET payment_total = payment_total + NEW.amount
  8.         WHERE invoice_id = NEW.invoice_id;
  9.     -- NEW 会返回刚刚插入的行；
  10.     -- OLD 返回更新前的行以及对于数值；
  11. end $$
  12. DELIMITER ;
  14. INSERT INTO payments
  15. VALUES (DEFAULT,5,3,'2019-01-01',10, 1)

查看触发器

SHOW TRIGGERS LIKE 'payments%'
image.png

删除触发器

DROP TRIGGER IF EXISTS payment_after_insert;

（event）事件

事件是根据计划执行的任务或一堆SQL代码，可以执行一次或按照某种规律执行。

创建事件

  1. DELIMITER $$
  3. CREATE EVENT yearly_delete_stale_audit_rows
  4. ON SCHEDULE
  5.     -- AT '2019-05-01' -- 执行一次；
  6.     EVERY 1 HOUR STARTS '2019-01-01' ENDS '2020-12-12'
  7.     -- 周期性执行，每小时执行一次；
  8. DO  BEGIN
  9.     DELETE FROM payments_audit
  10.         WHERE action_date < NOW() - INTERVAL 1 YEAR ;
  11. end $$
  12. DELIMITER ;

查看事件

SHOW EVENTS ;
image.png

删除事件

DROP EVENT IF EXISTS yearly_delete_stale_audit_rows;

更改事件

  2. DELIMITER $$
  4. ALTER EVENT yearly_delete_stale_audit_rows
  5.     ON SCHEDULE
  6.         -- AT '2019-05-01' -- 执行一次；
  7.         EVERY 1 HOUR STARTS '2019-01-01' ENDS '2020-12-12'
  8.     -- 周期性执行，每小时执行一次；
  9.     DO  BEGIN
  10.     DELETE FROM payments_audit
  11.     WHERE action_date < NOW() - INTERVAL 1 YEAR ;
  12. end $$
  13. DELIMITER ;

开启或关闭事件
ALTER EVENT yearly_delete_stale_audit_rows DISABLE ;

事务和并发

事务时一组负责某单一部分工作的SQL语句。事务的语句必须全部成功执行，否则事务就会失败。在需要对数据库进行多处修改的场景里，当我们希望所有的修改都完成，并将这些修改视为一个整体操作，这时就需要使用事务。
事务的ACID属性：

  • 原子性（Atomicity），指事务是一个不可分割的工作单位，事务的操作要么都发生，要么都不发生
  • 一致性（consistency)，事务必须使数据库从一个一致性状态变换到另一个一致性状态
  • 隔离性（Isolation），指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据堆与并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰，每次只能有一个事务有权修改
  • 持久性（Durabiliy），指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，解析来的其他操作和数据库故障不应该对其有其他影响

    创建事务（Transactions）

    ```plsql USE sql_store; — 开始一个新事务 START TRANSACTION;

INSERT INTO orders (customer_id,order_date, status) VALUES (1,’2019-01-01’, 1);

INSERT INTO order_items VALUES (LAST_INSERT_ID(), 1, 1, 1); — 将所有修改提交，如果因为硬件故障等原因没有提交，数据库将自动回滚到事务开始之前； COMMIT; — 手动回滚上一个事务之前 ROLLBACK；

  1. <a name="FOVYj"></a>
  2. ## 事务并发可能的问题
  4. - 丢失更新（Lost Update），发生在两个事务同时更新一条数据时，这是后提交的事务会覆盖前面的修改
  5. - 脏读（Dirty Reads），发生在事务读取还没有提交数据时，对于两个事务T1，T2，T1读取了已经被T2更新但还没有被提交的字段之后，若T2回滚，T1读取的内容就是临时且无效的
  6. - 不可重复读（Non-repeating Reads），对于两个事务T1，T2，T1读取了一个字段，然后T2更新了该字段，之后，T1再次读取同一个字段，值就不一样了
  7. - 幻读（Phantom Reads）：对于两个事务T1，T2，从一个表中读取了一个字段，然后T2在该表中插入了一些新的行，之后，如果T1再次读取同一个表，就会多出几行
  9. <a name="ml1lG"></a>
  10. ## 事务隔离等级
  12. - READ UNCOMMITTED 未提交
  13.    - 会出现脏读，幻读，不可重复读，丢失更新
  14. - READ COMMTITED 已提交读
  15.    - 能避免脏读，幻读、丢失更新和不可重复读依然会出现
  16. - REPEATABLE READ 可重复读 默认
  17.    - 不能解决幻读
  18. - SERIALIZABLE 串行化
  20. MySQL默认隔离等级为REPEATEBALE READ<br />SHOW VARIABLES LIKE 'transaction_isolation';//显示隔离级别<br />set session transaction isolation level read uncommitted //设置最低隔离级别
  21. <a name="BBDTz"></a>
  22. # 设计数据库
  23. <a name="EltHF"></a>
  24. ## 一些概念：
  26. - 元组（Tuple）：在关系二维表中的一行，称为一个元组
  27. - 域（Domain）：属性列的取值范围
  28. - 基数（Radix）：一个值域的取值个数
  29. <a name="wITxu"></a>
  30. ## 数据建模：
  32. 1. 理解问题
  33. 1. 建立概念模型 
  34. 1. 建立逻辑模型
  35. 1. 建立物理模型
  36. <a name="ryCpP"></a>
  37. ### ER图（实体关系图）
  38. <a name="sh8Tk"></a>
  39. #### 基本元素
  41. - 实体（Entity），指数据对象，在应用中可以区别的客观存在的事物；实体集（Entity Set），由实体组成的集合
  42. - 属性（Attribute），实体的某一特性，特殊的，一个实体中能够唯一标识实体的属性或属性集称为“实体标识符”
  43. - 联系（Relationship），表示一个或多个实体之间的关联关系，一个联系设计到的实体集个数称为该联系的元数或度数（Degree）
  44. <a name="bh01k"></a>
  45. #### 关系模式
  46. 关系的描述称为关系模式（Relation Schema）它可以形式化地表示为：<br />R（U，D，dom，F）<br />其中R为关系名，U为组成该关系的属性名集合，D为属性组U中属性所来自的域，dom为属性向域的[映象](https://baike.baidu.com/item/%E6%98%A0%E8%B1%A1)集合，F为属性间数据的依赖关系集合。<br />通常简记为：<br />R(U)或R(A1，A2，…,An)<br />其中R为关系名，U为属性名集合，A1，A2，…,An为各属性名。<br />比如：班级表（班级ID，班级名，学校名，系名，年级）
  47. <a name="WqUmq"></a>
  48. #### ER图转换成关系模式集
  50. - 实体类型的转换：将每个实体类型转换成一个关系模式，它的属性几位关系模式的属性，实体标识符即为关系模式的键
  51. - 联系类型的转换：
  52. 1. 二元联系类型的转换：若实体之间的联系是1：1，可以在两个实体类型转换成的两个关系模式中任意一个关系模式的属性中加入另一个关系模式的键的联系类型的属性；若实体之间的联系是1：n，则在n端实体类型转换成的关系模式中加入1端实体类型的键和联系类型的属性；若实体之间的联系是m：n，则键联系关系也转换成关系模式，其属性为两端实体类型的键加上联系类型的属性，而键为两端实体键的组合
  53. 1. 三元联系类型的转换：若实体之间的类型是1：1：1，可以在转换成的三个关系模式中任意一个关系模式的属性中加入另外两个关系模式的键（作为外键）和联系类型的属性；
  54. <a name="KFSIV"></a>
  55. ### 概念模型
  56. 概率模型是按**用户的观点**来对数据和信息建模，用于数据库设计，它是对现实世界中问题内的事务的描述，这个模型实际上是现实世界到机器时间的一个中间层次。可以使用**实体关系（ER）**和 统一建模语言（UML）建立模型。
  58. 使用ER进行数据库概念模型设计的步骤：
  60. 1. 设计局部ER
  61. 1. 然后把个局部ER综合在一个全局ER
  62. 1. 最后对全局ER进行优化，得到最终的ER模型，即概念模型
  63. <a name="Vqc4a"></a>
  64. ## 逻辑模型
  65. 逻辑模型是按**计算机系统的观点**对数据建模，用以DBMS实现，它反映的是系统设计人员对数据存储的观点，是对概念模型进一步的分解和细化，以将其转化成符合DBMS支持的数据模型的逻辑结构。
  66. <a name="s2Wvc"></a>
  67. #### 使用ER模型的逻辑模型设计：
  69. 1. 导出初始关系模式集
  70. 1. 规范化处理
  71.    1. 逐一考察关系模型
  72.    1. 判断是否满足规范要求
  73. 3. 模式评价
  74. 3. 模式修正
  75. 3. 设计子模式
  77. <a name="lGpiy"></a>
  78. ## 物理模型
  79. 物理模型是对真实数据库的描述，它戳数据库最底层的抽象，描述数据在系统内部的表示方式和存取方式，数据库的一些对象是：表，视图，字段，数据类型，长度，主键，外键，索引，是否可为空和默认值。
  81. <a name="CWt7e"></a>
  82. ## 函数依赖
  83. 函数依赖是指关系中属性间（或者说是表中字段间）的对应关系。<br />定义：设 R 为任一给定关系，如果对于 R 中属性 X 的每一个值，R 中的属性 Y 只有唯一值与之对应，则称 X 函数决定 Y 或称 Y 函数依赖于 X ，记作 X—>Y。其中，X 称为决定因素。
  84. <a name="5kJJZ"></a>
  85. ### 完全函数依赖
  86. 设X,Y是关系R的两个属性集合，X’是X的真子集，存在X→Y，但对每一个X’都有X’!→Y，则称Y完全函数依赖于X。<br />例如：学生数学成绩完全由该学生的学号和数学课决定，所以数学课成绩完全依赖于（学号，数学课）
  87. <a name="rmfMY"></a>
  88. ### 部分函数依赖
  89. 设X,Y是关系R的两个属性集合，存在X→Y，若X’是X的真子集，存在X’→Y，则称Y部分函数依赖于X。<br />例如：学生学号和姓名可以决定唯一的学生，但是学生号也可以决定唯一的学生。
  90. <a name="aG7t4"></a>
  91. ### 传递函数依赖
  92. 设X,Y,Z是关系R中互不相同的属性集合，存在X→Y(Y !→X),Y→Z，则称Z传递函数依赖于X。<br />例如：书的出版编号是唯一，版权归出版社所有，所以只能由该出版社出版。所以存在函数依赖：书出版编号—>出版社名，出版社名—>出版社地址，但是出版社名不能决定唯一的出版书编号（除非出版社只出版过一本书，那我没话说?），则有出版社地址传递函数依赖于出版书编号。
  93. <a name="J0XgY"></a>
  94. ##  平凡函数依赖与非平凡函数依赖
  95. X→Y，但Y⊈X则称X→Y是非平凡的函数依赖。<br />X→Y，但Y⊆X 则称X→Y是平凡的函数依赖。<br />即，X能函数确定Y，若Y是X的子集，则为平凡函数依赖，若Y不是X的子集则为非平凡的函数依赖。根据刚刚的定义来理解，两个元组在X上的属性值相等，那么它的子集里这两个元组的属性值也必定是相等的吖，所以平凡函数依赖都是必然成立的，我们一般讨论的是非平凡函数依赖。
  96. <a name="V6inF"></a>
  97. ### 多值依赖
  98. 设U是关系模式R的属性集，X和Y是U的子集，Z=U−X−Y,xyz表示属性集XYZ的值。对R的关系r，在r中存在元组(x, y1, z1)和(x, y2, z2)时，也存在元组(x, y1, z2)和(x, y2, z1)，那么称多值依赖（Multi Valued Dependency,MVD），即X→→Y在模式R上成立。
  99. <a name="eQJVS"></a>
  100. ### 连接依赖
  101. 对关系R及其属性的子集A,B,C,…,Z，当且仅当R的每个合法元组都与其在A,B,C,…,Z上投影的连接结果相同时，则称关系R满足连接依赖。
  102. <a name="ul2AK"></a>
  103. ### 码
  104. 表中可以唯一确定一个元组的某个属性（或者属性组），如果这样的码有不止一个，那么大家都叫候选码，我们从候选码中挑一个出来做老大，它就叫主码。
  105. <a name="RjaUP"></a>
  106. ## 主属性
  107. 一个属性只要在任何一个候选码中出现过，这个属性就是主属性。
  108. <a name="BqiwQ"></a>
  109. ## 非主属性
  110. 没有在任何候选码中出现过，这个属性就是非主属性。
  112. <a name="UTsOW"></a>
  113. ## 范式
  114. 关系数据库中的关系必须满足一定的要求，满足不同程度的要求为不同范式，范式是符合某一级别的关系模式的集合
  115. <a name="Vr0F1"></a>
  116. ### 第一范式（1NF）
  117. 第一范式（1NF）是指数据库表的每一列(即每个属性)都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。简而言之，第一范式就是无重复的列。<br />在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。
  118. <a name="JlIJa"></a>
  119. ### 第二范式（2NF）
  120. 满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键、主码。第二范式（2NF）同时也要求实体的属性完全依赖于主关键字。
  121. <a name="l9hwM"></a>
  122. ### 第三范式（3NF）
  123. 满足第三范式（3NF）必须先满足第二范式（2NF）。另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。
  124. <a name="Csec3"></a>
  125. ### BC范式（BCNF）
  127. 1. 所有的非主属性对每一个码都是完全函数依赖 （暗含 主关键字里面可能有多个码可以将实体区分）
  128. 1. 所有的主属性对每一个不包含它的码也是完全函数依赖（即所选码与未选择的码之间也是完全函数依赖的）
  129. 1. 没有任何属性完全函数依赖于非码的任何一组属性（即非主属性之间不能函数依赖）
  130. <a name="ymZ84"></a>
  131. ### 第四范式（4NF）
  132. 关系模式R<U,F>∈1NF，如果对于R的每个**非平凡多值依赖X→→Y（Y ⊈ X）**，**X都含有码**，则R<U,F>∈4NF。
  133. <a name="FLXmk"></a>
  134. ### 第五范式（5NF）
  135. 如果关系模式R中的每一个连接依赖均由R的候选码所隐含，则称此关系模式符合第五范式。<br />关系模式规范化的步骤：
  137. 1. 1NF→2NF：消除非主属性对码的部分函数依赖
  138. 1. 2NF→3NF：消除非主属性对码的传递函数依赖
  139. 1. 3NF→BCNF：消除主属性对码的部分和函数依赖
  140. 1. BCNF→4NF：消除非平凡且非函数依赖的多值依赖
  141. 1. 4NF→5NF：消除不是有候选码所蕴含的连接依赖
  142. <a name="V5dgl"></a>
  143. # 索引
  144. 索引是一种针对表中指定列的值进行排序的数据结构，使用它可以加快表中数据的查询。<br />索引的成本：
  146. - 增大数据库的大小
  147. - 减缓了数据写入的速度
  149. 在数据库系统开发中，需根据实际应用需求，仅对需要快速查询的数据库表相应列建立索引。此外，在数据库中，一般需要为每个表的主键列创建索引。
  150. <a name="MzgHe"></a>
  151. ## 创建索引
  152. CREATE INDEX <索引名>ON <表名><（列名[,..,]）>;
  153. ```plsql
  154. -- 创建索引
  155. CREATE INDEX idx_state ON customers (state);
  156. -- 修改表结构
  157. ALTER table tableName ADD INDEX indexName(columnName)
  159. EXPLAIN SELECT customer_id FROM customers WHERE state = 'CA';

image.png

查看索引

SHOW INDEXES IN customers;
image.png

前缀索引（Prefix Index）

当索引是很长的字符序列时，这个索引将会很占内存，而且会很慢，这时候就会用到前缀索引了。所谓的前缀索引就是去索引的前面几个字母作为索引，但是要降低索引的重复率，索引我们还必须要判断前缀索引的重复率。

  1. SELECT
  2.        COUNT(DISTINCT LEFT(last_name,1)) ,
  3.        COUNT(DISTINCT LEFT(last_name,5)) ,
  4.        COUNT(DISTINCT LEFT(last_name,10))
  5. FROM customers;
  7. CREATE INDEX idx_lastname ON customers (last_name(20));

image.png

image.png唯一索引（Unique Index）

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

  1. CREATE UNIQUE INDEX indexName ON mytable(username(length)) 
  3. ALTER table mytable ADD UNIQUE [indexName] (username(length))

全文索引（Full-Text Index）

主要用于查找文本中的关键字，而不是直接与索引中的值相比较，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。Full-Text索引配合match（）和against（）操作使用，目前只有char、varchar和text列上可以创建全文索引。

  1. -- 创建表示创建全文索引
  2. create table fulltext_test (
  3.     id int(11) NOT NULL AUTO_INCREMENT,
  4.     content text NOT NULL,
  5.     tag varchar(255),
  6.     PRIMARY KEY (id),
  7.     FULLTEXT KEY content_tag_fulltext(content,tag)  // 创建联合全文索引列
  8. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  10. -- 直接创建索引
  11. create fulltext index content_tag_fulltext
  12.     on fulltext_test(content,tag);
  14. -- 修改表结构
  15. alter table fulltext_test
  16.     add fulltext index content_tag_fulltext(content,tag);
  18. -- 使用方法
  19. select * from fulltext_test 
  20.     where match(content,tag) against('xxx xxx');

组合索引（Composite Index）

指多个字段上创建的索引，只有在查询条件中使用了创建索引的第一个字段，索引才会被使用，使用组合索引是遵循最左前缀集合。

  1. CREATE INDEX idx_state_points ON customers (state,points);
  3. EXPLAIN SELECT customer_id FROM customers
  4. WHERE state = 'CA' AND points > 1000;

覆盖索引（Covering Index）

如果索引包含所有满足查询需要的数据，则称该索引为覆盖索引，也就是平时所谓的不需要回表操作。

判断标准

使用explain，可以通过数出的extra列来判断，对于一个索引覆盖查询，显示为using extra，MySQL查询优化器在执行查询前回觉得是否有索引覆盖查询。

保护数据库

创建用户

CREATE USER john IDENTIFIED BY '1234';

查看用户

SELECT * FROM mysql.user;

删除用户

CREATE USER bob@codewithmosh.com IDENTIFIED BY '1234';
DROP USER bob@codewithmosh.com;

修改密码

SET PARTITION FOR john = '1234';

权限许可

  1. -- 1:web/desktop application
  2. CREATE USER moon_app IDENTIFIED BY '1234';
  3. GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE
  4. ON sql_store.*
  5. TO moon_app;
  6. -- 2:admin
  7. GRANT ALL ON sql_store.*
  8. TO moon_app;

查看权限

SHOW GRANTS FOR john;

撤销权限

REVOKE CREATE VIEWON sql_store.*FROM moon_app;