MySQL数据库

数据库范式

• 第一范式

  指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值，或不能有重复的属性。如果出现重复的属性，可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。

  简而言之，第一范式遵从原子性，字段不可再分，否则就不是关系型数据库

• 第二范式（依赖于第一范式）

  要求实体的属性完全依赖于主键。所谓完全依赖，是指不能存在仅依赖主键一部分的属性，如果存在，那么这个属性和主键的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实体的唯一标识。

  简而言之，第二范式遵从唯一性，就是非主键字段需依赖主键

• 第三范式（依赖于第二范式）

  要求一个数据表中的每一列数据都和主键直接相关，而不能间接相关。

  简而言之，第三范式就是非主键字段不能互相依赖

按照三范式设计的数据库能很大程度上减小数据冗余

数据库基本操作

创建数据库：

CREATE DATABASE 数据库名称;
// 创建数据库

CREATE DATABASE IF NOT EXISTS 数据库名称;
// 通过使用 IF NOT EXISTS 防止要创建的数据库已经存在

查看数据库：

SHOW CREATE DATABASE 数据库名称;
// 创建并查看数据库

SHOW DATABASE;
// 查看已经存在的数据库

选择数据库：

USE 数据库名称;
// 选择一个数据库

删除数据库：

DROP DATABASE 数据库名称;
// 删除并回收已分配的存储空间

DROP IF NOT EXISTS 数据库名称;
// 防止要删除的数据库不存在

数据表操作

创建数据表：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 数据表名
[(
col_name type [完整性约束条件],
col_name type [完整性约束条件],
  ...
)][table_options] [select_statement];

/**
* 完整性约束条件 指 字段的某些特殊约束条件
**/

查看数据表：

SHOW TABLES;
// 查看数据库中的表

SHOW CREATE TABLE 数据表名 \G;
//  '\G'用于将显示结果纵向排列

DESCRIBE 数据表名;
// 用于查看表的字段信息，DESCRIBE可以简写成DESC

DESCRIBE 数据表名 列名;
// 可以指定查看某一列的信息

//SHOW COLUMNS   用于在MySQL数据库中，查看表结构

# 语法格式1
SHOW [FULL] COLUMNS FROM 数据表名 [FROM 数据库名];

# 语法格式2
SHOW [FULL] COLUMNS FROM 数据库名,数据表名;

修改表结构：

ALTER TABLE 数据表名
ADD [COLUMN] creat_definition [ FIRST | AFTER column_name ]    # 添加新字段
|    CHANGE [COLUMN] old_col_name new_col_name type            # 修改字段名称及类型
|    MODIFY [COLUMN] create_definition                        # 修改子句定义字段
|    DROP [COLUMN] col_name                                    # 删除字段
|    RENAME [AS] new_tbl_name                                # 更改表名

// 上述语法中，ALTER TABLE 语句允许指定多个动作，动作间使用英文逗号（,）分隔。

数据约束：

字段名 数据类型 NOT NULL;
// 非空约束 NOT NULL

#列级约束
字段名 数据类型 UNIQUE;
#表级约束
UNIQUE (字段名1,字段名2,......);
// 唯一约束 UNIQUE

# 列级约束
字段名 数据类型 PRIMARY KEY;
# 表级约束
PRIMARY KEY (字段名1,字段名2,......);
// 主键约束 PRIMARY KEY

字段名 数据类型 DEFAULT 默认值;
// 默认约束

数据的管理

• 插入记录

  INSERT INTO 表名 (字段1,字段2,...) VALUES (值1,值2,...);
  // 也可省略字段列表，若省略字段列表，则必须严格按照数据表定义字段时的顺序，在 值列表 中为字段指定相应的数据。
  
  INSERT INTO 表名 SET 字段名1 = 值1,字段名2 = 值2,...;
  //通过 SET 的方式可以为指定字段添加数据

• 修改记录

  UPDATE 表名
  SET 字段名1 = 值1 [,字段名2 = 值2,...]
  [WHERE 条件表达式]
  // WHERE表达式用于选择哪些记录需要被更新，否则数据表中的所有记录都将被更新

• 删除记录

  DELETE FROM 表名 [WHERE 条件表达式]
  // 满足条件的记录才会被删除
  
  TRUCATE [TABLE] 表名
  // 清空表中的所有记录

数据的查询

• 查询语句

  SELECT [DISTINCT] *| {字段名1, 字段名2, 字段名3,...}
  FROM 表名
  [where 条件表达式1]
  [GROUP BY 字段名 [HAVING 条件表达式2]]
  [ORDER BY 字段名 [ASC | DESC]]
  [LIMIT [OFFSET] 记录数]
  // DISTINCT 可选参数，用于剔除查询结果中重复的数据
  
  SELECT * FROM 表名;
  // 利用通配符 * 查询出所有字段，但是字段顺序为表中定义的顺序
  
  SELECT 字段名1,字段名2,... FROM 表名
  // 查询出来的顺序与SQL语句中编写的顺序有关
  
  SELECT 字段名称 [AS] 别名, 字段名2 [AS] 别名, ... FROM 表名;
  // 使用别名方便查询，通过调用AS子句，且AS可以省略

• 条件查询

  SELECT * FROM 表名 WHERE 条件表达式;
  // 通过WHERE条件表达式
  
  SELECT [DISTINCT] * | {字段名1,字段名2,字段名3,...} FROM 表名;
  // 使用 DISTINCT 关键字去除查询结果中重复的记录。
  
  SELECT * | {字段名1,字段名2,字段名3,...}
  FROM 表名
  WHERE [NOT] IN (元素1,元素2,...)
  // 使用 IN 关键字判断被查询出来的值是否在指定集合中
  
  SELECT * | {字段名1,字段名2,字段名3,...}
  FROM 表名
  WHERE 字段名 [NOT] BETWEEN 值1 AND 值2
  // BETWEEN AND 用于判断某个字段的值是否在指定的范围之内
  
  SELECT * | {字段名1,字段名2,字段名3,...}
  FROM 表名
  WHERE 字段名 [NOT] LIKE '匹配字符串'
  // 带LIKE的字符匹配查询
  /**
  通配符：
  % ，可以匹配 N (N>=0)个字符，
  _ ， 仅可匹配一个字符
  **/
  
  SELECT * | {字段名1,字段名2,字段名3,...}
  FROM 表名
  WHERE 条件表达式1 AND 条件表达式2 [... AND 条件表达式N];
  // 带AND的多条件查询
  
  SELECT * | {字段名1,字段名2,字段名3,...}
  FROM 表名
  WHERE 条件表达式1 OR 条件表达式2 [... OR 条件表达式N];
  // 带OR的多条件查询
  

排序与限量

• ORDER BY 排序查询

  SELECT 字段名1, 字段名2, ...
  FROM 表名
  ORDER BY 字段名1 [ASC | DECS] [, 字段名2 [ASC |　DESC] ... ];
  // ASC表示升序、DESC表示降序

• LIMIT 限量查询

  SELECT 字段名1, 字段名2, ...
  FROM 表名
  LIMIT [OFFSET,] 记录数;
  // 偏移量表示第一条记录从第几条开始

聚合函数与分组

• 聚合函数

  COUNT()
  // 计算表中记录的个数或者列中值的个数
  
  SUM()
  // 获取符合条件所有结果的和
  
  AVG()
  // 计算一列中数据值的平均值
  
  MAX()
  // 获取查询数据中的最大值
  
  MIN()
  // 获取查询数据中的最小值

• GROUP BY分组查询

  SELECT 字段名1, 字段名2, ...
  FROM 表名
  GROUP BY 字段名1 [, 字段名2, ... [HAVING 条件表达式]];
  // 使用GROUP BY进行分组，字段中值相同的为一组

多表查询

• 合并查询

  SELECT ......
  UNION [ ALL | DISTINCT ]
  SELECT ......
  // 合并查询就是将多个SELECT语句的查询结果合并到一起

• 连接查询

  • 交叉连接

    SELECT 查询字段 FROM 表1 CROSS JOIN 表2;
    // 返回两个表中所有数据行的笛卡尔积

  • 内连接

    SELECT 查询字段 FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表二.关系字段 WHERE 条件;
    // 内连接就是简单连接，作为默认连接方式，INNER可以省略

  • 外连接

    SELECT 所查字段 FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2
    ON 表1.关系字段 = 表2.关系字段 WHERE 条件
    //外连接生成的结果集不仅可以包括符合连接条件的数据记录，而且还可以包括左表、右表或两表中所有的数据记录。

• 子查询

  • 单行子查询

    将一个SELECT查询语句的结果作为另一个查询语句的WHERE条件

  • 带IN关键字的子查询

    子查询返回一个结果集，作为外层SQL语句的判断条件

  • 带EXISTS关键字的子查询

    子查询不返回查询记录，而是返回一个真假值

  • 带ANY关键字的子查询

    只要其后的子查询的子查询满足其中任意一个判断条件，就返回结果作为外层SQL语句的执行条件

  • 带ALL关键字的子查询

    只有满足内层查询语句返回的所有结果，才可以执行外层查询语句