【MySQL语法】02 语法再探

目录

• 1. MySQL 高级
  • 1.1. Prepare 语句
    • 1.1.1. 语法
    • 1.1.2. 应用 & 优势
  • 1.2. 自定义函数
    • 1.2.1. MySql创建自定义函数（Function）
• 2. 表结构
  • 2.1. 外键
• 3. 查询
  • 3.1. Union all
  • 3.2. all join
  • 3.3. select 结果处理
    • 3.3.1. Compute
    • 3.3.2. .
• 4. 数据格式
  • 4.1. 日期

---

1. MySQL 高级

1.1. Prepare 语句

• 参考：
  1. mysql prepare 存储过程使用

1.1.1. 语法

1. 用途

   1. Prepared Statements很像存储过程，是一种运行在后台的SQL语句集合,可以获得不错的性能和更安全；
   2. 对于只是参数不同的多条sql语句，prepare可以节省硬解析的时间；-- prepare 语法适用频繁执行且复杂的SQL。
   3. 可以检查一些你绑定好的变量；
   4. 动态增减临时表的字段；
   5. 存储过程中动态的拼接表名，字段名，来达到动态查询的效果；
   6. sql语句中还可以用?来代表参数，这样可以有效的防止sql注入；

2. 定义

   PREPARE statement_name FROM sql_text      /*定义sql语句为变量名*/
   EXECUTE statement_name [USING variable [,variable...]]   /*执行预处理语句*/
   DEALLOCATE PREPARE statement_name         /*删除定义*/
   

3. 示例

   mysql> PREPARE prod FROM "INSERT INTO examlple VALUES(?,?)";
   mysql> SET @p='1';
   mysql> SET @q='2';
   mysql> EXECUTE prod USING @p,@q;
   mysql> SET @name='3';
   mysql> EXECUTE prod USING @p,@name;   // 传入两个会话变量来填充sql中的 ?
   mysql> DEALLOCATE PREPARE prod;
   

4. 注意：

   1. 用变量做表名： 简单的用set或者declare语句定义变量，然后直接作为sql的表名是不行的，mysql会把变量名当作表名。
   2. mysql的解决方法是将整条sql语句作为变量，其中穿插变量作为表名，然后用sp_executesql调用该语句。

5. 使用 PREPARE 的几个注意点：

   A：PREPARE stmt_name FROM preparable_stmt;预定义一个语句，并将它赋给 stmt_name ，tmt_name 是不区分大小写的。
   B： 即使 preparable_stmt 语句中的 ? 所代表的是一个字符串，你也不需要将 ? 用引号包含起来。
   C： 如果新的 PREPARE 语句使用了一个已存在的 stmt_name ，那么原有的将被立即释放！ 即使这个新的 PREPARE 语句因为错误而不能被正确执行。
   D： PREPARE stmt_name 的 **作用域** 是当前客户端连接会话可见。
   E： 要释放一个预定义语句的资源，可以使用 DEALLOCATE PREPARE 句法。
   F： EXECUTE stmt_name 句法中，如果 stmt_name 不存在，将会引发一个错误。
   G： 如果在终止客户端连接会话时，没有显式地调用 DEALLOCATE PREPARE 句法释放资源，服务器端会自己动释放它。
   H： 在预定义语句中，CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, 和大部分的 SHOW 句法被支持。
   I： PREPARE 语句 不可以用于存储过程，自定义函数！但从 MySQL 5.0.13 开始，它可以被用于存储过程，仍不支持在函数中使用！
   

1.1.2. 应用 & 优势

---

1.2. 自定义函数

1.2.1. MySql创建自定义函数（Function）

---

2. 表结构

2.1. 外键

1. 作用：
   外键的作用，就是保证两个表外键关联后，主表中任意一条数据的完整性，这就是约束。
   应用在： delete update 场景中；

2. 创建外键：

   # 更改表属性
     alter table t1 add  constraint `t1_t2_fk` foreign key (t1_id) references t2(id);
         -- 将表t1的t1_id外键关联到表t2的id字段。
         -- 每个外键都有一个名字，可以通过 constraint 指定
   # 创建表时
   CREATE TABLE `Client` (
    `Cid` int(11) NOT NULL AUTO_INCREMENT,
    `phone_number` DATETIME,
    `Email` VARCHAR(50),?
    `Createtime` VARCHAR(20) NOT NULL,
    `Ckind` INT,
    PRIMARY KEY (`Cid`),
    foreign key (Ckind) references Clientkind(Kid) on delete cascade on update cascade);
   

3. 报错总结:

   1. Mysql错误1452 - Cannot add or update a child row: a foreign key constraint fails 原因及解决方法
      1. 添加的外键列与另一个表的唯一索引列（一般是主键）的数据类型不同;
         解决：设置相同数据类型；
      2. 要添加外键的表类型与另一个表的存储引擎是不是都为innodb引擎;
         解决：show create table 表名； --查看引擎类型；
      3. 设置的外键与另一个表中的唯一索引列（一般是主键）中的值不匹配;
         解决：删除要成为外键的列，再次创建并默认为NULL；

4. 添加外键时Missing index for constraint

   1. 作为被引用的外键，它必须是唯一值，因此得添加约束unique或者primary key.

---

3. 查询

3.1. Union all

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
Intersect：对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；
Minus：对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。 – 会进行去重;

3.2. all join

---

3.3. select 结果处理

3.3.1. Compute

1. 概述

   1. COMPUTE BY子句可以在结果集内生成控制中断和小计，得到更详细的或总的记录。
      它把数据分成较小的组，然后为每组建立详细记录结果数据集(如SELECT)，
      也可为每组产生总的记录(如GROUP BY)；
   2. BY 子句非必须的；

2. 语法

   [ COMPUTE
   { { AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM }
   ( EXPRESSION ) } [ ,...n ]
   [BY  EXPRESSION [ ,...n ] ]
   ]
   
   示例：
      SELECT ProductName,Amount,price,total
      FROM Products
      COMPUTE SUM(total)；   -- sum 聚类函数；
   
      SELECT ProductName,Amount,price,total,Country
      FROM Products
      ORDER BY Country
      COMPUTE SUM(total)BY Country；  --compute & order by
   

3. GROUP BY 和 COMPUTE 之间的区别汇总如下：

   1. GROUP BY 生成单个结果集。每个组都有一个只包含分组依据列和显示该组子聚合的聚合函数的行。
      选择列表只能包含分组依据列和聚合函数。
   2. COMPUTE 生成多个结果集。一种结果集包含每个组的明细行，其中包含选择列表中的表达式。
      另一种结果集包含组的子聚合，或 SELECT 语句的总聚合。
      选择列表可包含除分组依据列或聚合函数之外的其他表达式。
      聚合函数在 COMPUTE 子句中指定，而不是在选择列表中指定。

4. 注意：-- COMPUTE 和 ORDER BY

   1. 当在COMPUTE中使用BY时，要求在所有组合字段中必须包含ORDER BY语句。
   2. 在COMPUTE子句指定的行聚合函数中，不允许使用DISTINCT关键字。
   3. 由于包含COMPUTE 的语句生成表并且这些表的汇总结果不存储在数据库中，因此在SELECT INTO 语句中不能使用 COMPUTE。
      因而，任何由 COMPUTE 生成的计算结果都不会出现在用 SELECT INTO 语句创建的新表内。
   4. COMPUTE BY语句后出现的字段必须是ORDER BY语句后出现的字段的子集，
      并且COMPUTE BY语句后出现的字段必须和 ORDER BY语句后面的字段具有相同的出现顺序，
      并以相同的表达式开头，中间不能遗漏任何表达式。

参考：

1. COMPUTE 与COMPUTE BY子句

3.3.2. .

---

4. 数据格式

4.1. 日期

%y 两位数的年份表示（00-99）
%Y 四位数的年份表示（000-9999）
%m 月份（01-12）
%d 月内中的一天（0-31）
%H 24小时制小时数（0-23）
%I 12小时制小时数（01-12）
%M 分钟数（00=59）
%S 秒（00-59）
%a 本地简化星期名称
%A 本地完整星期名称
%b 本地简化的月份名称
%B 本地完整的月份名称
%c 本地相应的日期表示和时间表示
%j 年内的一天（001-366）
%p 本地A.M.或P.M.的等价符
%U 一年中的星期数（00-53）星期天为星期的开始
%w 星期（0-6），星期天为星期的开始
%W 一年中的星期数（00-53）星期一为星期的开始
%x 本地相应的日期表示
%X 本地相应的时间表示
%Z 当前时区的名称
%% %号本身

参考：

1. python时间函数与mysql时间函数转换
2. MySQL DATE_FORMAT() 函数

---