MySQL数据库原理及应用

大学生本科MySQL数据库原理及应用期末复习框架整理，包括操作代码，希望这份脑图会对你有所帮助。

MySQL 数据库原理及应用

• 第一章 数据库技术概述
  • 数据库系统
    • 数据特点
      • 永久存储
      • 有组织
      • 可共享
    • 组成
      • 数据库
      • 数据库管理系统
      • 用户应用程序
      • 用户
    • 特点
      • 数据结构化
      • 高度共享、低冗余度、易扩充
      • 数据独立
        • 物理独立
          • 应用程序与数据库数据相互独立
        • 逻辑独立
          • 应用程序与数据库逻辑结构相互独立
    • 发展
      • 人工管理阶段
        • 不能单独保存数据
        • 应用程序管理数据
        • 数据不能共享
      • 文件系统阶段
        • 数据可以长期保存
        • 文件系统管理数据
        • 共享差、冗余大
        • 数据独立性差
      • 数据库管理阶段
        • 数据结构化
        • 共享性高、冗余度低、易扩充
        • 独立性较高
        • 数据统一由DBMS统一管理
  • 三级数据结构模式
    • 数据模式
      • 外模式（子模式、用户视图）
      • 模式（逻辑模式）
      • 内模式（存储模式、物理模式）
    • 二级映像技术
      • 外模式/模式映像
        • 实现数据逻辑独立性
      • 模式/内模式映像
        • 实现数据物理独立性
  • 概念模型
    • 实体-联系模型（E-R模型）
      • 运用最广泛 由实体、属性、联系组成
      • 基本概念
        • 实体
        • 属性
          • 不可再次细分
          • 同一属性不得与其他多个实体具有联系
        • 实体集
          • 具有相同属性的实体的集合
        • 键
          • 唯一标识实体
          • 取值唯一
          • 不能为空
        • 实体型
          • 从实体集中抽象出来的类
          • 带下划线的属性是键
        • 联系
          • 可以拥有属性
          • 外部
            • 实体集之间
          • 内部
            • 实体属性之间
      • 表示方法
        • 矩形
          • 实体集
        • 椭圆形
          • 属性
            • 其中键属性加下划线
        • 菱形
          • 联系
      • 联系类型
        • 1：1
          • 实体集之间互相至多对应一个，可以没有
            • 不是一一对应
        • 1：n
          • 对其中一个实体集至多有一个，另一个至多有n个对应
        • m：n
          • 分别至多有n、m个
  • 逻辑模型
    • 数据模型
      • 数据结构
        • 描述数据的静态特性
          • 层次模型
          • 网状模型
          • 关系模型
      • 数据操作
        • 描述数据的动态特性
          • 查询
          • 更新
            • 插入
            • 删除
            • 修改
      • 数据完整性约束
    • 常见的数据模型
      • 层次模型
        • 特点
          • 有且仅有一个根结点，没有父结点
          • 其他结点有且仅有一个父结点
      • 网状模型
        • 特点
          • 多个结点
          • 任意个父结点
          • 结点间可有多种关系
      • 关系模型
        • 二维表格
      • 面向对象模型
        • 面向对象方法与数据库相结合所构成的数据模型
  • 关系数据库
    • 关系模型的基本概念
      • 关系
        • 二维表格
      • 属性
        • 列
      • 元组
        • 行
      • 域
        • 取值范围
      • 关键字（主属性）
        • 可唯一表示元组
          • 主关键字
            • 一个关系一个
          • 候选关键字
            • 一个关系可多个
      • 外部关键字
        • 来自其他关系
      • 关系模式
        • 是一种型
          • 如：关系名（column1，column2，……）
    • 基本性质
      • 一个关系就是一张二维表格
      • 每一列是一个属性，每一列有唯一的别名
      • 每一列都是不可再分的数据项
      • ……
    • 规范化
      • 解决的问题
        • 冗余大
        • 更新异常
        • 插入异常
        • 删除异常
      • 实质
        • 不断分解关系的过程
          • 每一种范式自动包含前面的范式
          • 1NF
            • 都是简单属性
          • 2NF
            • 非主属性直接依赖于主属性
          • 3NF
            • 非主属性不传递函数关系依赖于主属性
    • 完整性约束
      • 域完整性
        • 规定
          • 数据类型
          • 格式
          • 值域范围
          • 是否允许空值
      • 实体完整性
        • 主键
      • 参照完整性（引用完整性）
        • 外键
      • 用户定义完整性
    • 操作基础
      • 集合运算
        • 并
        • 交
        • 差
        • 笛卡尔积
      • 关系运算
        • 选择
          • 筛选元组
        • 投影
          • 选取属性
        • 连接
          • 积、选择、投影的组合
            • 等值连接
              • 基于公共属性值
            • 自然连接
              • 当赋新增属性为NULL 而非不出现时，产生了
                • 左外连接
                  • 以左表为基础表
                • 右外连接
                  • 以右表为基础表
                • 全外连接
                  • 左外和右外的组合运用
              • 基于公共属性值
              • 删除重复值
  • 数据库设计的基本步骤
    • 需求分析
    • 概念设计
    • 逻辑设计
    • 物理设计
    • 数据库实施
    • 运行和维护
• 第三章 数据库基本操作
  • 创建数据库
    • 数据库来源
      • 系统数据库
        • 系统自带
      • 用户自定义数据库
        • 用户创建
    • CREATE DATABASE <> 【CHARACTER SET <>】;
      • 默认的character是Server Default 存放中文最好使用gbk
      • 查看所有字符集
        • SHOW CHARACTER SET；
  • 查看数据库
    • 查看所有数据库
      • SHOW DATABASES;
    • 查看数据库详细信息
      • SHOW CREATE DATABASE <>;
    • 选择数据库
      • USE <>;
    • 查看当前数据库
      • 子主题
  • 删除数据库
    • DROP DATABASE <>;
  • 数据库存储引擎
    • 多种存储引擎
      • MyISAM
      • InnoDB
      • MEMORY
    • 重要特点
      • 支持插件式存储引擎
        • 区别于其他数据库管理系统
    • 查看存储引擎
      • SHOW ENGINES;
  • 事务与锁
    • 事务
      • 原子性
        • 事务中的操作要么都执行，要么都不执行
      • 一致性
        • 事务前后，完整性不会被破坏
      • 隔离性
        • 防止多个并发事务由于交叉执行而导致的数据不一致
      • 持久性
        • 数据修改是永久的，系统故障也不会丢失
    • 锁
      • 为了保证事务完整性和数据一致性，采用锁机制
      • 表级锁定
      • 行级锁定
      • 页级锁定
    • 常用存储引擎介绍
      • InnoDB
        • 默认存储引擎
        • 锁定在行级
        • 支持外键完整性约束
      • MyISAM
        • 不支持事务
        • 不支持外键
      • MEMORY
        • 数据长度格式不变
• 第六章 结构化查询语言SQL
  • 运算符
    • 算术运算符
      • （）的优先级最高；+-的优先级低于%，%与*/同级
    • 比较运算符
      • 运算结果
        • 1，0，NULL
      • 等于运算符（=）
        • 字母不区分大小写；比较对象中有NULL，结果就为NULL
      • 安全等于运算符(<=>)
        • 可判断NULL，两边都是NULL返回1，一边是NULL返回0
      • LIKE运算符
        • %匹配零个或任意个字符
        • _匹配一个字符
      • REGEXP运算符
        • ^h匹配以h开头的字符串
        • h$匹配以h结尾的字符串
        • .匹配任何一个单字符
        • [...]匹配在方括号内的任何字符
        • *可以代替任意多个字符
      • 逻辑运算符
        • 逻辑非运算符（NOT或！）NOT优先级低于+-，！优先级最高
        • 逻辑与运算符（AND或&&）
        • 逻辑或运算符（OR或||）
        • 逻辑异或运算符（XOR）
          • 都非0或为0，结果为0
  • 函数
  • 简单查询
  • 条件查询
  • 排序查询
  • 总计查询
  • 连接查询
  • 子查询
  • 合并查询结果
  • 数据操作
• 第五章 索引
  • 概述
    • 特点
      • 优点
        • 二分查找（最快方法）
        • 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性
        • 提高数据查询速度（主要原因）
        • 可以实现数据的参照完整性
          • 只有创建索引后，字段才能成为外键
        • 使用分组和排序查询时，索引可以提高其速度
      • 缺点
        • 创建和维护索引需要耗费时间
        • 索引单独存储在存储设备中，占据存储空间；打开数据表，索引也会同时打开
      • 原则
        • 并非越多越好
        • 避免对经常更新的表建立过多的索引，索引字段尽量少
        • 数据量少的表可以不建立索引
        • 在不同的值较多的字段建立索引，较少时不用建立
        • 对唯一性的字段建立唯一索引
          • 可以提高查询速度
          • 检查数据唯一性
        • 当表的修改（INSERT,UPDATE,DELETE)操作远大于检索操作时不应该创建索引
        • 在频繁分组和排序的字段建立索引可以组合效率
    • 分类
      • 普通索引和唯一索引
        • 可以空值或者重复值
          • 唯一索引只要求唯一
            • 主键索引是特殊的唯一索引，还要求非空
              • ALTER TABLE <> ADD PRIMARY KEY(col1);
      • 单字段索引和多字段索引
      • 全文索引
        • 可以插入重复值和空值
        • MySQL中只有MyISAM存储引擎支持全文索引
      • 空间索引
        • 对空间数据类型建立
        • MySQL中只有MyISAM存储引擎支持全文索引
    • 查询的基本原理
      • 顺序查询
        • O(N)
      • 二分法查询
        • O(lnN)
      • 二叉树查询
    • MySQL的索引
      • MySQL的索引实现
        • MyISAM采用非聚簇索引
        • InnoDB采用聚簇索引
  • 创建索引
    • 创建表时创建索引
      • CREATE TABLE <table_name> (col1 datatype [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col1) [ASC | DESC])
        • unique,fulltext,spatial是可选参数，分别表示唯一索引、全文索引和空间索引
        • index，key是同义词，均表示创建索引
        • index_name是索引名，若没有指定，默认为字段名
        • ASC和DESC可选，指定索引值存放的方式是升序还是降序，默认为ASC
      • 具体创建普通索引
        • 在创建表的最后一句
          • INDEX(col));
    • 创建唯一索引
      • UNIQUE INDEX(col));
    • 创建组合索引
      • INDEX <index_name>(col1,col2));
        • 在组合索引中，遵循“最左前缀”原则， 先按col1排序，后按col2排序，故不能局部搜索col2
    • 在已有表中创建索引
      • ALTER TABLE
        • ALTER TABLE <> ADD INDEX(col1);
      • CREATE INDEX
        • CREATE INDEX ON <> (col1);
    • 查看索引
      • SHOW INDEX FROM <>;
        • 可用\G代替，使字符界面更加清晰
  • 删除索引
    • 有自动增值约束的唯一索引不能删除
    • ALTER TABLE
      • ALTER TABLE <> DROP INDEX <>;
    • DROP INDEX
      • DROP INDEX <> ON <>;
• 第四章 数据表
  • 数据类型
    • 数值类型
      • 整数类型
        • 默认的整数宽度为11
        • TINYINT
          • 1
        • SMALLINT
          • 2
        • MEDIUMINT
          • 3
        • INT
          • 4
        • BIGINT
          • 8
      • 实数类型
        • FLOAT
          • 4
        • DOUBLE
          • 8
        • DECIMAL(M,D)
          • max(M,D)+2
      • 位类型
        • BIT(M)
          • 存放M位二进制数1~64，默认为1
            • SELECT命令看不到结果，要用二进制或者十六进制函数读取
              • SELECT不能直接显示二进制
                • ×　SELECT＊FROM＜＞;
              • 用BIN或者HEX函数查看
                • √ SELECT BIN(col1),HEX(col1) FROM <>;
    • 日期时间类型
      • DATE
        • 年月日
      • TIME
        • 时分秒
      • YEAR
        • 2位格式
          • 不在1970~2069，慎用
        • 4位格式
          • 任意
      • DATETIME
        • 年月日时分秒
      • TIMESTAMP
        • 系统日期
    • 字符串类型
      • 非二进制字符串
        • CHAR(M)
          • 定长M
            • 定义时小于M，会补空格
              • 输入数据时，会删除空格
        • VARCHAR(M)
          • 变长：实际长度+1
            • 不会补
              • 输入数据时，不会删除空格
        • TINYTXT
        • TEXT
        • MEDIUMTEXT
        • LONGTEXT
  • 创建数据表
    • 语法
      • CREATE TABLE 【IF NOT EXISTS】 <> ( col1 datatype, col2 datatype, … )ENGINE=engine DEFAULT CHARSET=charset;
        • 表名不能使用关键字，由字母、汉字、下划线开头
        • 【IF NOT EXISTS】避免因表存在而发生错误
        • ENGINE用来设置存储引擎，默认为InnoDB
        • CHARSET用来指定字符集
        • 查看数据表是否已成功创建
          • SHOW TABLES
    • 主键约束
      • 单字段主键（可命名）
        • 定义字段时定义主键
          • col1 datatype PRIMARY KEY,
        • 定义字段后，表级约束中指定主键
          • PRIMARY KEY(col1));
      • 多字段联合主键
        • 定义字段后，表级约束中指定主键
          • PRIMARY KEY(col1,col2,...,coln));
    • 外键约束
      • 单字段or多字段，一个or多个，均可
      • 语法
        • FOREIGN KEY (col1) REFERENCES <col1_fathertable_name>(col1)
    • 非空约束
      • 语法
        • Col_name datatype NOT NULL
    • 默认值约束
      • 插入数据时，若不对该字段赋值，就会用默认值作为该字段的值
      • 语法
        • Col_name datatype DEFAULT <>
          • 若默认值为汉字，可在创建表最后添加 CHARSET='utf8';
    • 自动增值
      • 由AUTO_INCREMENT约束的字段初始值为1，每插入一条纪录，字段值自动加1； 一个表只能有一个AUTO_INCREMENT约束的字段，是主键的一部分； 对任何整型字段均有效
      • 语法
        • Col_name datatype 【PRIMARY KEY】AUTO_INCREMENT
    • 唯一约束
      • 限定字段值不能重复
      • 创建
        • 列级唯一约束
          • Col_name datatype UNIQUE
            • 可对多列设置唯一约束
        • 表级唯一约束
          • UNIQUE (col_name)
  • 数据表操作
    • 数据表查看（已创建的所有数据表）
      • SHOW TABLES;
    • 查看表结构（数据表的字段信息）
      • DESCRIBE <>;
    • 查看创建表的详细结构（CREATE TABLE语句）
      • SHOW CREATE TABLE <>;
        • 可用/G代替； 因为这里的结果相对复杂
    • 数据表删除
      • DROP TABLE <>1,<>2, ......
        • 删除没有被关联的表
        • 删除被其他表关联的表
          • 无需保留子表
            • 先删除子表，才能删除主表
          • 要保留子表
            • 先删除表之间的参照完整性（外键），再删除主表
    • 数据表更名
      • ALTER TABLE <> RENAME <>;
  • 修改表结构
    • 修改字段数据类型
      • ALTER TABLE <tablename> MODIFY <col1> <datatype>;
        • 若出错，可能是字符集的问题，修改语句： ALTER TABLE <> CHACSET='utf8';
    • 修改字段名
      • ALTER TABLE <tablename> CHANGE <oldname> <newname> <datatype>;
        • 数据类型必须填写，若不修改，写原数据类型
    • 添加字段
      • ALTER TABLE <tablename> ADD <colname> <type>【FIEST | AFTER <>】;
        • 不指定位置，则直接添加到尾部
    • 删除字段
      • ALTER TABLE <> DROP <>;
    • 修改字段排列顺序
      • ALTER TABLE <> MODIFY <colname> <type>【FIEST | AFTER <>】;
    • 修改完整性约束
      • 添加主键约束
        • ALTER TABLE <> ADD [CONSTRAINT <name it>] PRIMARY KEY(col1,col2,...,coln);
      • 删除主键约束
        • ALTER TABLE <> DROP PRIMARY KEY;
      • 添加外键约束
        • ALTER TABLE <> ADD [CONSTRAINT <name it>] FOREIGN KEY(col) REFERENCES (father_table_name)(col);
      • 删除外键约束
        • ALTER TABLE <> DROP FOREIGN KEY <name>;
      • 添加非空约束
      • 添加默认值
      • 删除默认值
      • 添加唯一约束
  • 表数据编辑
    • 插入数据
      • INSERT INTO <table_name> (col1,col2,...,coln) VALUES (val1,val2,...,valn);
        • 如果只插入部分字段的值，则要指定插入的字段列表
          • 一定要包含主键和非空字段的值
        • 可一次插入多条数据，用逗号隔开
    • 修改数据
      • UPDATE <table_name> SET col1=val1, col2=val2,...【WHERE <>】；
    • 删除数据
      • DELETE FROM <table_name>【WHERE <>】；
• 可类比设置单字段主键时的方法
• TEXT类型列不能指定为主键
• 输入数据时，变长字符串后面的空格将保留 而定长字段的空格则被删除
• 变长
• 四舍五入型保留