MySQL学习笔记 下

MySQL学习笔记（需接上篇）

1. 通配符

• 用于替换字符串中的一个或多个字符；
• 通配符常与LIKE关键字一起使用，用于搜索字段中的指定模式；

2. 别名

• 用于为表或表中的列提供临时名称；

• 别名用于为表或表中的字段提供临时的名称，且仅在该查询期间内存在，只存在于查询的结果表中；

• 使用AS关键字创建别名，具体语法格式如下：

  //给选中的字段取别名
  SELECT column_name AS alias_name
  FROM table_name;  
  
  //给选中的表取别名  
  SELECT column_name(s)
  FROM table_name AS alias_name;
  

• 【注意】：如果别名中包含空格，请用单引号或者双引号将别名括起来表示一个整体；

• 别名在以下的情况中非常有用：

  1. 一个查询涉及多个表；
  2. 查询中使用了函数；
  3. 列名太大或不太可读；
  4. 将两列或多列组合在一起；

3. JOIN

• 用于根据它们之间的相关列组合来自两个或多个表的行;
• 它会以字段值作为比较关系的值，以此来框定新产生的结果应该包含哪些记录。
• MySQL支持的了解联接类型：
  1. INNER JOIN：返回两个表中值匹配的记录，也就是取交；
  2. LEFT JOIN：返回左表中所有记录，右表中没有匹配的记录，结果集会在右表的列显示NULL;
  3. RIGHT JOIN：与上述相反，返回右表中所有记录，左表中没有匹配的记录，结果集会在左表的列显示NULL；
  4. FULL JOIN：返回两个表中所有的记录；
  5. CROSS JOIN：返回两个表选中的行的笛卡尔积，即行与行之间交叉乘起来。显然，如果没有指定WHERE子句来限制结果集的内容，返回的结果集将非常庞大；
  6. NATURAL JOIN：自动匹配两个表中具有相同名称的列。在没有指定JOIN的类型，则默认为INNER JOIN,原有的联结条件字段就不用显示了;
     

4. 自联结

• 是指在同一个表上进行的联结操作，通常用于将表中的行与同一个表中的其他行进行比较或关联；
• 自联结的典型应用场景：
  1. 组织结构中的层次关系（如员工和经理关系）；
  2. 对于时间序列数据的时间前后记录比较；
  3. 查找符合某种条件的配对数据；
• 自联结的基本语法与普通的联结类似，只不过表名相同并且需要使用别名来区分（例如需要创建一个表的两个实例，对两个实例的相同对象建立别名，并用别名作为字段输出）；
• 语法格式如：

  SELECT Column1 AS Alias1,Column2 AS Alias2,···  
  FROM Table a1
  JOIN Table a2  
  ON expression//条件表达式
  

5. UNION

• 用于组合两个或多个SELECT语句的结果集;
• 关于UNION，有以下的注意事项：
  1. SELECT语句必须具有相同的列数；
  2. 列必须具有相似的数据类型；
  3. 列也必须是相同的顺序；

一个实例：

• 当我们想合并两个查询时，但是它们的列明不同，我们可以在SELECT语句中使用别名来确保列名一致；
• UNION运算符默认只选择不同的值。要允许重复值，需要使用UNION ALL;
• 我们可以使用 ORDER BY 子句对 UNION 的结果进行排序;

6. GROUP BY

• 用于对数据进行分组统计，根本目的是为了去重，统计可以使用表达式配合；
• 它通常与聚合函数（COUNT()、MAX()、MIN()、SUM()、AVG()）配合使用，经过聚合函数处理后每一个字段都只有一个记录。
  一个实例：
  

6. HAVING

• WHERE关键字不能和聚合函数一起使用，但HAVING可以；
• 其语法格式为：

  //第一种情况
  SELECT column_name(s)
  FROM table_name
  WHERE condition
  
  //第二种情况
  SELECT column_name(s),aggregate_function(column)//聚合函数
  FROM table_name
  HAVING condition
  GROUP BY column_name
  

7. EXISTS

• 用于测试子查询中是否存在任何记录;
• 如果子查询返回一条或多条记录，EXISTS运算符返回TRUE;
• 其语法格式是：

  SELECT column_name(s)
  FROM table_name
  WHERE EXISTS
  (SELECT column_name FROM table_name WHERE condition);
  

• 较IN而言，WHERE EXISTS或者WHERE NOT EXISTS使用更加灵活，可以判断子查询是否返回结果（前者只会返回结果表，没有结果则空白）；

8. ANY & ALL

• 意如其名，即选择子表中的$\exists$或者$\forall$是否满足条件，满足的话就返回结果(相当于一种特殊的SELECT)。
• 语法形式为：

  SELECT column_name(s)
  FROM table_name
  WHERE column_name operator ANY/ALL
    (SELECT column_name
    FROM table_name
    WHERE condition);//表示较子查询中的记录更···的所有或者任意记录作为返回结果，查询得到则返回true。
  

• 注意运算符和字句之间组合表示的数量差异关系：
  1. 小于任何=小于最大值；
  2. 大于任何=大于最小值；
  3. 大于所有，小于所有字如其名；

9. INSERT INTO SELECT

• 用于从一个表中复制数据并将其插入另一表中；
• 要求原表和目标表的数据类型匹配；
• 语法格式：

  INSERT INTO table2 (column1, column2, column3, ...)
  SELECT column1, column2, column3, ...
  FROM table1
  WHERE condition;  
  //意思为从table1中按照条件选择出对应列的数据，将其插入到table2中去，并可能在table2中使用不同的字段名。
  

10. CASE

• 遍历条件并在满足第一个条件时返回一个结果（result可以设置成一串字符串），所以一旦条件为真，它将停止读取并返回结果；
• 如果没有条件为真，则返回 ELSE 子句中的值，具体语法格式如下：

  CASE
      WHEN condition1 THEN result1
      WHEN condition2 THEN result2
      WHEN conditionN THEN resultN
      ELSE result
  END;
  

11. NULL

• 没有特殊要求的情况，没有赋值的时候，字段对应的记录内容就是置空的。

• 在调用记录进行计算时，如果存在计算的某一项为NULL,则结果必然为NULL;

• 为了避免出现出现上边的情况，可以使用INNULL(可能为NULL的字段名，代替值)的形式来设置阈值；

• 当然使用COALESCE()函数也能达到同样的结果。

12. 注释

• 单行注释以--开头，任何介于--和行尾之间的文本将被忽略；
• 多行注释以/*开头并以*/结尾，两者之间的任何文本都将被忽略；
• 我们可以使用多行注释的功能来忽略行内的部分内容；

13. SOME

• SOME和ANY在逻辑上是等价的，都表示 “任意一个”。在某些数据库系统中，SOME可能不被支持，而ANY被广泛使用。在这种情况下，ANY可以作为 SOME的替代；
• SOME常与IN子句合用；

14. 运算符

复合运算符

逻辑运算符

15. 数据库操作

15.1 创建数据库

• 单独创建空白数据库：

  CREATE DATABASE databasename;
  

• 在数据库中创建表并初始化字段情况：

  CREATE TABLE tablename{
    字段1 类型1（可以包括大小说明） 字段1格式要求，  
    字段2 类型2 字段2格式要求， 
    字段3 类型3 字段3格式要求， 
    ……
  }
  

• 在上述创建数据库后，我们可以使用USE databasename语句选择数据库进行操作；

• 如果我们想对创建的数据库进一步插入数据，可以使用：

  INSERT INTO tablename (column1, column2, column3, ...)
  VALUES 
      (value1a, value2a, value3a, ...),
      (value1b, value2b, value3b, ...),
      (value1c, value2c, value3c, ...);  
  //即对数据库中的某些字段插入对应的记录，而且对应关系是按照申明从左至右的。**暂时没有被赋值的字段位置除非是非空。不然就是`NULL`**。
  

  故一段完整的语法可以是：

    //创建数据库
    CREATE DATABASE databasename;
    //创建表
    CREATE TABLE tablename{
    字段1 类型1（可以包括大小说明） 字段1格式要求，  
    字段2 类型2 字段2格式要求， 
    字段3 类型3 字段3格式要求， 
    ……
    }
    //插入数据
    INSERT INTO tablename (column1, column2, column3, ...)
    VALUES 
      (value1a, value2a, value3a, ...),
      (value1b, value2b, value3b, ...),
      (value1c, value2c, value3c, ...);
  

15.2 删除数据库

• 使用以下语句：

  DROP DATABASE databasename;   
  

15.3 创建表

• 除了上文我们所提到的方式外，我们还可以使用已有表创建表,例如：

  CREATE TABLE new_table_name AS
      SELECT column1, column2,...
      FROM existing_table_name
      WHERE ....;
  

15.4 字段的数据类型

• 字段的数据类型定义了该列可以保存的值:整数、字符、货币、日期和时间、二进制等。每一个字段都应该有名称和数据类型；

数值类型

• INTEGER 或 INT：标准整数类型。
• SMALLINT：小整数类型，占用较少的存储空间。
• BIGINT：大整数类型，用于存储非常大的整数。
• DECIMAL(p, s) 或 NUMERIC(p, s)：固定精度和标度的数值类型，p 表示数字的总位数，s 表示小数点后的位数。
• FLOAT：浮点数类型，用于存储单精度或双精度浮点数。
• REAL：真实浮点数，通常用于科学计算。
• DOUBLE PRECISION：双精度浮点数。

日期和时间类型

• DATE：存储日期的类型，格式通常为 YYYY-MM-DD。
• TIME：存储时间的类型，格式通常为 HH:MM:SS。
• DATETIME：同时存储日期和时间。
• TIMESTAMP：与 DATETIME 类似，但时间戳还包含秒的小数部分，并且可以根据时区存储时间。
• INTERVAL：用于存储时间间隔。

布尔类型

• BOOLEAN：布尔类型，存储 TRUE 或 FALSE。

其他类型

• JSON：存储 JSON 数据的类型，一些现代数据库支持直接存储和查询 JSON 数据。
• XML：存储 XML 数据的类型，一些数据库支持 XML 数据的存储和查询。

空间数据类型

• GEOMETRY：空间数据类型，用于存储几何数据，如点、线、多边形等。
• POINT、LINESTRING、POLYGON 等：具体的空间几何类型。

请注意，上面列出的数据类型是大多数 SQL 数据库系统中常见的，但具体的语法和支持可能会根据你使用的数据库（如 MySQL、PostgreSQL、SQL Server、SQLite 等）有所不同。此外，一些数据库系统可能还有它们自己的专有数据类型。

15.5 更改表

• 使用ALTER TABLE语句；
• 既可以用来添加、删除或修改现有表中的字段，又用于在现有表上添加和删除各种约束；
• 具体语法格式如下：

  //要在表中添加列
  ALTER TABLE table_name
  ADD column_name datatype;  
  
  //要删除表中的列  
  ALTER TABLE table_name
  DROP COLUMN column_name;
    
  //要更改表中列的数据类型
  ALTER TABLE table_name
  MODIFY COLUMN column_name datatype;
  
  

15.6 约束

• 用于为表中的数据指定规则,约束可用来规范表字段对应的数据类型，限制可以进入表的数据类型，保证表中数据的准确性和；
• 可以在使用CREATE TABLE语句创建表时或使用ALTER TABLE语句在创建表后指定约束；

  //批量创建格式如下：
  CREATE TABLE table_name (
      column1 datatype constraint1,
      column2 datatype constraint2,
      column3 datatype constraint3,
      ....
  );
  
  //单独创建组合限制并重命名如下：  
  CONSTRAINT 自定义的限制名 限制类别 (列1，列2，……)  
  //或者直接
  限制类别(列1，列2，……)  
  
  //在已有表上指定限制  
  ALTER TABLE Persons
  ADD 限制类型 (列名1，……);
  

• 常见的约束有：
  • NOT NULL - 确保列不能有 NULL 值
  • UNIQUE - 确保列中的所有值都不同
  • PRIMARY KEY - NOT NULL 和 UNIQUE 的组合。 唯一标识表中的每一行（主键）
  • FOREIGN KEY - 约束防止无效数据插入外键列，而防止破坏表之间链接的操作（外键）
  • CHECK - 确保列中的值满足特定条件，用表示式形式发挥作用，如：

    CHECK(表达式)--限制填入数据的大小范围
    

  • DEFAULT - 如果没有指定值，则为列设置默认值，如：

    字段名 数据类型 DEFAULT 默认值--字符串用""括起来
    

  • CREATE INDEX - 用于非常快速地从数据库中创建和检索数据
• 删除表上的限制，可以使用：

  ALTER TABLE 表名
  DROP 限制类型 (自定义)限制名;
  

15.7 主键和外键

• 主键用于标识数据库中的每一行记录，它要求：这一列为非空、主键的值在表中必须是唯一的，也就是这一列数据是无重复的；
• 主键可以再创建表的一开始设置，也可以在表建立之后通过ALTER TABLE更改：

  //在创建表的时候设置限制  
  CREATE TABLE table_name (
    column1 datatype,
    column2 datatype, --(PRIMARY KEY)这里也可以定义
    PRIMARY KEY (column1, column2)--这里设允许同时设置多个列成为复合主键的
    ...
  );   
  //在已有的表上做修改   
  ALTER TABLE table_name
  ADD CONSTRAINT /*(constraint_name)*/ PRIMARY KEY (column1, column2);
  

• 外键用于在两个表中之间建立链接，并确保引用的数据的完整性和准确性。外键通常应用另一个表的主键，从而创建两个表之间的一对多关系；
• 创建外键是为了与另一表相连通，创建语法如下：

  CREATE TABLE table_name (
      ...
      foreign_key_column datatype,
      CONSTRAINT constraint_name FOREIGN KEY (foreign_key_column) 
      REFERENCES referenced_table_name (referenced_key_column)
      [ON DELETE action]
      [ON UPDATE action]
      --table_name：当前创建的表的名称。
      --foreign_key_column：当前表中的外键列。
      --constraint_name：外键约束的名称（可选）。
      --referenced_table_name：包含主键或唯一键的被引用表的名称。
      --referenced_key_column：被引用表中的主键或唯一键列。
      --ON DELETE action 和 ON UPDATE action：定义当被引用表中的数据被删除或更新时，当前表中的数据如何处理。action 可以是 CASCADE（清空）、SET NULL（设零）、NO ACTION（无反应） 或 RESTRICT（限制不给删）。
  );
  

  • 一个实例如：

    CREATE TABLE Orders (
        OrderID INT PRIMARY KEY,
        CustomerID INT,
        OrderDate DATE,
        CONSTRAINT fk_customer
        FOREIGN KEY (CustomerID) 
        REFERENCES Customers (CustomerID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
        -- 其他列...
    );
    

16. 内置函数

MySQL有非常多的内置函数，详细可以参考【MySQL函数】文章的内容，里面有每个的详细用法和语法格式说明；

17. CREATE INDEX

• 用于在表中创建索引；
• 索引用于比其他方式更快地从数据库中检索数据。 用户看不到索引，它们只是用来加速搜索/查询（类似于快表）；
• 更新带有索引的表比不更新表需要更多时间（因为索引也需要更新）。 因此，仅在将经常搜索的列上创建索引。
• 其语法格式为：

  //创建索引
  CREATE INDEX index_name
  ON table_name (column1, column2, ...);  
  
  //删除索引  
  ALTER TABLE table_name
  DROP INDEX index_name;  
  

18. AUTO INCREMENT

• 又叫自增字段，允许在向表中插入新记录时自动生成唯一编号（通常作为主键使用）；
• 默认情况下AUTO_INCREMENT的起始值为1，每新增一条记录就加1;
• 建立语法：

  //默认为1的初始化
  字段名 数据类型 限制 AUTO_INCREMENT
    
  //更改自增字段序列的初始值  
  ALTER TABLE 列名 AUTO_INCREMENT=自定义值;
  

19. 使用时间

MySQL 带有以下数据类型，用于在数据库中存储日期或日期/时间值:

1. DATE - 格式 YYYY-MM-DD
2. DATETIME - 格式:YYYY-MM-DD HH:MI:SS
3. TIMESTAMP - 格式:YYYY-MM-DD HH:MI:SS
4. YEAR - 格式 YYYY 或 YY

• 在数据库中创建新表时，系统会自动为列设置日期数据类型。处理日期最困难的部分是确保您尝试插入/查询的日期格式与数据库中日期列的格式相匹配，只要您的数据仅包含日期部分，您的查询就会按预期工作。 但是，如果涉及到时间部分，它就会变得更加复杂；
• 【注意】：如果不涉及时间组件，可以轻松比较两个日期

20. 视图/VIEW

• 视图是基于 SQL 语句结果集的虚拟表。其内容由 SQL 查询定义。视图并不在数据库中实际存储数据，而是在查询视图时动态生成数据。
• 同时提供了一种安全机制，可以用来限制用户对某些数据的访问，防止用户对数据的破坏；
• 关于视图的语法格式如下：

  //创建视图的基本语法如下：
  CREATE VIEW view_name AS
  SELECT column1, column2, ...
  FROM table_name
  WHERE condition;
  --view_name：视图的名称。
  --column1, column2, ...：选择显示在视图中的列。
  --table_name：视图基于的表。
  --condition：查询条件（可选）。  
  
  //使用视图就像是使用表一样，可以对其增删改查，但是不会影响到实际的数据库中的表  
  
  //修改和删除视图  
  -- 修改视图
  ALTER VIEW view_name AS
  SELECT column1, column2, ...
  FROM table_name
  WHERE condition;
  
  -- 删除视图
  DROP VIEW view_name;
  
  ```sql
  //创建视图的基本语法如下：
  CREATE VIEW view_name AS
  SELECT column1, column2, ...
  FROM table_name
  WHERE condition;
  --view_name：视图的名称。
  --column1, column2, ...：选择显示在视图中的列。
  --table_name：视图基于的表。
  --condition：查询条件（可选）。  
  
  //使用视图就像是使用表一样，可以对其增删改查，但是不会影响到实际的数据库中的表  
  
  //修改和删除视图  
  -- 修改视图
  ALTER VIEW view_name AS
  SELECT column1, column2, ...
  FROM table_name
  WHERE condition;
  
  -- 删除视图
  DROP VIEW view_name;