Mysql数据库原理及其应用

数据和数据库

数据和信息

  1. 信息: 信息就是事物包含的意义(可以有不同的理解)如:

    对于杯水,属性是无限多的,体积、温度、密度这些属性是客观存在的,就是说如果这杯水的温度是33度,就算没有人去观察这杯水,它的温度也还是33度,而“水的温度是33度”只是这杯水的众多信息之一。(这个概念对于学过哲学唯物内容的同学来说,是比较好理解的。)

  2. 数据: 数据是信息的具体表现形式,是记录信息可识别的符号.
    如: 数字,文字,图像,视频等

  3. 数据和信息的联系: 数据是信息的表示或者载体,信息是数据的内涵和意义,是对数据的语义解释.

数据库

  1. 数据库: 数据库DB(database), 长期储存在计算机内、有组织的、可共享的大量数据的集合.数据库就是一个存放数据的仓库
    如: 二维表,电话簿等
  2. 数据库管理系统DBMS(database management system):DBMS是软件,位于用户与操作系统之间。DBMS能够组织和存储数据、获取和维护数据。
  3. 数据库系统DBS(database system): 数据库系统由数据库数据库管理系统(及其应用开发工具)、应用程序数据库管理员四部分构成。
  4. 数据库管理员DBA(Database Administrator): 俗称运维,负责控制数据库系统,是数据库系统中最重要的

数据库体系结构

模式:
实例: 模式的一个具体值,如Student(1,张三,18)

三层模式

  1. 逻辑模式/模式(Schema):对数据库中全体数据(实体)的逻辑结构和特征的描述,不涉及具体值.如Student(id,name,age).
    • 一个数据库只有一个逻辑模式
  2. 外模式/子模式/用户模式: 是用户能看见和使用的局部数据的逻辑模式和特征的描述
    • 一个数据库有多个外模式
    • 是模式的子集
    • 是用户的数据视图
  3. 内模式/存储模式: 是对数据的物理结构与存储方式的描述,数据在数据库内部的组织方式
    • 一个数据库只有一个内模式

两级映像

  1. 外模式/模式映像: 定义外模式与逻辑模式之间的对应关系,当模式改变时,改变映像可以使外模式不变,应用程序不变,保证数据的逻辑独立性.
    • 每一个外模式都对应一个 外模式/模式映像
    • 映像的定义通常在各自外模式的描述中
  2. 内模式/模式映像: 定义了全局逻辑结构和存储结构的对应关系,当存储结构改变时,改变映像,可是模式不变,应用程序不变,保证数据的物理独立性

    作用

  • 应用程序与数据之间互相独立
  • 逻辑独立性
  • 物理独立性

数据模型

什么是数据模型

数据模型是对现实世界的模拟表述,数据模型应该满足三方面要求:

  • 比较真实的模拟现实世界
  • 容易理解
  • 容易被计算机实现

    数据模型三要素

  1. 数据结构(静态特征)
  2. 数据操作(动态特征)
  3. 数据的约束条件(规则集合)

    数据模型的分类

    分类条件为数据结构不同
  4. 关系模型-二维表
  5. 半结构化的模型数据
    如:
  • 网状模型-网状结构
  • 层次模型-树形结构
  • XML-树形结构
  • JSON-树形结构

关系数据模型

关系模型建立在集合代数的基础上

关系数据结构

  1. 域(Domain)/列: 是一组具有相同数据类型集合,在二维表中就是.例如

    1. 整数
      1
      1,2,3,4,5,6,7,8,9,10,...
    2. 实数
      1
      1.1,2.2,3.3,...
    3. 其它…
  2. 笛卡尔积(Cartesian Product): 就是两个集合相乘,例如
    假设现在有三个表学生,专业,老师.他们的结构图下

    学生表

    博客mysql学习学生表

    专业表

    博客mysql学习专业表

    老师表

    博客mysql学习老师表
    计算方式就是每个学生都与其它两个表的元组相乘,计算出所有可能的结果.
    最终结算结果如下
    博客mysql学习笛卡尔计算结果

  3. 关系/二维表: 关系也是一个二维表
    • 表的每一行就是元组
    • 表的每一列的取值范围就是
    • 列的名字称为属性(Attribute)
      1. 码(Key)/键:
        • 候选码(Candidate Key)/主键: 在关系中能唯一标识属性或属性集
        • 主属性(Prime Attribute): 候选码的各个属性
        • 主码(Primary Key)/唯一主键: 候选码其中的一个,一个表必须有一个主码
        • 全码: 所有属性``都是这个关系的候选码
        • 外码/外键(Foreign Key): 如果一个关系R中的一个属性F对应着另一关系S的主码K那么F在关系R中称为外码.
  4. 关系的基本性质
    • 列是同质的-数据类型/域相同
    • 列和行的顺序无所谓
    • 元组不能完全相同
    • 分量必须取原子值-即元组的某一个属性不能再往下分,比如性别

关系操作

  1. 关系模式(Relation Schema): 是对关系的描述
    • 关系模式是,关系是,列如:
      其中关系是学生,关系模式是学生(学号,姓名,年龄,性别,籍贯)
      博客mysql学习关系模式
    • 关系模式通常写为R(U),其中R是关系,U是属性集合
    • 和关系的区别: 是对关系的描述,他是静态的稳定的.关系是整个二维表其中的值会发生变化,而关系模式是属性,不会经常变化
  2. 关系数据库: 是实体之间的关系集合,列如
    假设有关系:教师关系(T),课程关系(C),学生关系(S),那么该关系数据库可以描述为有三个关系模式:
    1
    2
    3
    T(TID,TNAME,TITLE)
    S(SID,SNAME)
    C(CID,CNAME)
  3. 关系操作
    1. 常用关系操作: 插入,查询,删除,修改
    2. 关系操作特点: 集合操作方式
    3. 关系数据语言种类: 关系代数语言,关系演算语言(SQL具有以上两种特点)

完整性约束

  1. 实体完整性: 实体和实体之间保证不重复
    • 主码唯一且不能为空
    • 主属性不能取空值
  2. 参照完整性: 外码和主码之间的引用规则
    • 外码取值暂时为空值或者是被参照表中的元组中的主码值
  3. 用户定义的完整性: 是用户工具实际情况自己定义的约束条件.例如XXX属性必须唯一等.

关系代数

  1. 关系数据语言: 对数据库进行操作的语言
    • 关系代数/关系演算: 早期的原理性的,不用于现在商用的DBMS中
    • SQL: 是讲SQL语言转换成关系代数或者类似的表现形式
  2. 运算规则
    • 用对关系的运算来表达查询
    • 运算对象: 关系
    • 运算符: (并:∪,交:∩,差:-),(选择:σ,投影:π,笛卡尔积:×,连接:∞,重命名:ρ),(比较运算符:< > <= >= = <>),(非:¬,与:∧,或:∨)
    • 运算结果: 关系
  3. 并,差,交
    • 进行运算的条件是两个集合的属性个数必须相同
    • 并和差属于高中集合知识,不细讲
    • 差: 由属于R而不属于S的所有集合组成,如下图为红色部分
      博客mysql学习差集合
  4. 投影,选择
    • 投影: 从R关系中选择若干满足条件属性组成新的关系.假设有以下算式:博客mysql学习投影算式
      • π表示投影运算符
      • L表示要从R中取那些属性,可以是属性名,或者属性的列号
      • R表示从R中取
      • 结果要去掉重复元组
    • 选择: 在R关系中选择满足条件的元组组成新的关系.假设有以下算式:博客mysql学习选择算式
      • σ表示选择运算符
      • c表示条件,格式为列名 运算符 值.
      • R表示从R中取
      • 结果要去掉重复元组
  5. 笛卡尔积,连接
    • 笛卡尔积: 就是将两个集合的元组进行匹配连接
      • 列数: n+m
      • 行数: k1xk2(如果属性重名,就采用R.S和S.A分别命名)
    • 连接: 将集合进行笛卡尔积后在进行选择操作.博客mysql学习连接
      • A和B:分别是R和S上相同长度且可以进行比较的属性组合
      • θ:比较的条件
      • 运算方法: 从R和S的笛卡尔集中选取(R关系)在A属性组上的值与(S关系)在B属性组上值满足比较条件的元组.概括就先笛卡尔再选择
      • 等值连接: 其实就是θ为=运算符,从两个关系中选择相同的属性值的元组
      • 自然连接: 等值连接运算后去掉重复属性
        • 两个关系必须具有公共属性
        • 结果要去掉重复属性
  6. 重命名: 给一个关系或者关系中的属性起一个代号.博客mysql学习重命名
    • ρ: 重命名运算符
    • s: 被重命名后的名字
    • A1…An: 重命名的属性名
    • R: 被重命名的关系
  7. 等价运算: 博客mysql学习等价运算
  8. 运算符优先级: 博客mysql学习运算符优先级

范式

关系数据库中的关系满足一定要求的,满足不同程度要求的为不同的范式。满足最低要求的叫第一范式,简称1NF;在第一范式的基础上满足进一步要求的称为第二范式,简称2NF,其余范式以此类推。对于各种范式之间有如下关系:
博客mysql学习范式表

  1. 函数依赖: 在一个关系中,有属性A能知道属性B,称为A为决定因素.
    • 完全函数依赖: 在一个关系中,想要知道一个属性必须需要两个及以上的属性才能推出该属性,就称该属性完全依赖另外两个属性x,y,....
    • 部分函数依赖: 只需要关系所有属性中的子集属性,此时称该属性部分函数依赖于关系。
  2. 1NF: 所有属性都不可再分
  3. 2NF: 消除非主属性主属性的部分函数依赖
  4. 3NF: 在2NF的基础上消除传递函数依赖.传递函数依赖指的是,如果存在:A->B->C的决定关系,则C传递函数依赖于A
  5. 4NF: 消除主属性对主键的部分与传递依赖
  6. 5NF: 消除表中的多值依赖

SQL 创建与删除数据库

创建数据库

在与数据进行任何其他操作之前,需要创建一个数据库。要在MySQL中创建数据库,请使用CREATE DATABASE语句,如下:

1
CREATE DATABASE [IF NOT EXISTS] database_name;

我们来更详细地看看CREATE DATABASE语句:

  • CREATE DATABASE语句的后面是要创建的数据库名称。
  • IF NOT EXISTS是语句的可选子句。用于检测是否存在重名的数据库

例如,要创建一个名称为mytestdb数据库

1
CREATE DATABASE IF NOT EXISTS mytestdb;

博客mysql学习创建数据库结果

查看数据库安装路径

任意一个命令都可以

1
2
3
select @@basedir as basePath from dual ;

show variables like '%basedir%';

查看数据库存放路径

任意一个命令都可以

1
2
3
select @@datadir as dataPath from dual ;

show variables Like '%datadir%';

删除数据库

删除数据库意味着数据库中的所有数据和关联对象将被永久删除,并且无法撤消。
要删除数据库,请使用DROP DATABASE语句,如下所示:

1
DROP DATABASE [IF EXISTS] database_name;

与CREATE DATABASE语句类似,IF EXISTS是该语句的可选部分,以防止您删除数据库服务器中不存在的数据库。

展示数据库列表

如果要查看当前 MySQL 服务器有哪些数据库,可以使用 SHOW DATABASES; 命令

选择数据库

使用 USE SQL命令来选择指定的数据库

1
USE 数据库名称;

数据类型

数字数据类型

类型 大小 范围(有符号) 范围(无符号) 用途
TINYINT 1 Bytes (-128,127) (0,255) 小整数值
SMALLINT 2 Bytes (-32 768,32 767) (0,65 535) 大整数值
MEDIUMINT 3 Bytes (-8 388 608,8 388 607) (0,16 777 215) 大整数值
INT或INTEGER 4 Bytes (-2 147 483 648,2 147 483 647) (0,4 294 967 295) 大整数值
BIGINT 8 Bytes (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) (0,18 446 744 073 709 551 615) 极大整数值
FLOAT 4 Bytes (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) 0,(1.175 494 351 E-38,3.402 823 466 E+38) 单精度
DOUBLE 8 Bytes (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 双精度
DECIMAL 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

字符串类型

类型 大小 用途
CHAR 0-255 bytes 定长字符串
VARCHAR 0-65535 bytes 变长字符串
TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字符串
TINYTEXT 0-255 bytes 短文本字符串
BLOB 0-65 535 bytes 二进制形式的长文本数据
TEXT 0-65 535 bytes 长文本数据
MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据

日期和时间类型

类型 大小 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 ‘-838:59:59’/‘838:59:59’ HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ YYYY-MM-DD hh:mm:ss 混合日期和时间值
TIMESTAMP 4 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC 结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYY-MM-DD hh:mm:ss 混合日期和时间值,时间戳

MySQL 列出数据表

输入 SHOW TABLES; 命令就可以查看当前选择的数据库有哪些表

1
SHOW TABLES;

MySQL 创建数据表

面的 SQL 语句为创建 MySQL 数据表的通用语法

1
CREATE TABLE table_name (column_name column_type other,...);

MySQL 删除表

使用 DROP TABLE SQL 语句删除数据表的通用语法如下

1
DROP TABLE table_name ;

字段(属性)插入/添加数据

使用 INSERT INTO SQL 语句往表中插入数据的语法格式如下

1
INSERT INTO table_name ( 属性1, 属性2,...属性N ) VALUES ( value1, value2,...valueN );

或者Msql独有的语句
1
INSERT INTO table_name SET 属性1=1[,属性2=2]

如果数据是字符型,必须使用单引号''或者双引号"",如 “value”

如果值包含了全部字段,且按照属性的顺序,那么可以用下面的简写语法

1
INSERT INTO table_name VALUES ( value1, value2,...valueN );

如果是多条语句,可以用 逗号, 分隔每条数据.例如:
1
2
3
4
INSERT INTO `tbl_language` VALUES
(1,'Python','https://www.twle.cn','1991-2-20'),
(2,'PHP','http://www.php.net','1994-1-1'),
(3,'Ruby','https://www.ruby-lang.org/','1996-12-25')

Mysql 修改表名

  1. rename
    1
    rename table 旧表名 to 新表名;
    1
    rename table 旧表名1 to 新表名1[,旧表名2 to 新表名2...];
  2. alter
    1
    alter table 旧表名  to 新表名

修改表编码

1
ALTER TABLE `table_name` DEFAULT CHARACTER SET utf8;

修改属性/字段名

1
ALTER TABLE  `table_name` CHANGE 旧字段名 新字段名 类型;

修改字段的类型

1
ALTER TABLE `table_name` MODIFY 字段名 类型;

查询数据

1
select 属性1[,属性2..属性N] from `table_name` 

条件查询

1
select 属性1[,属性2..属性N] from `table_name` where 属性=

修改/更新数据

1
update `table_name` set 属性1=1[,属性2=2...属性N=值N] [where 条件表达式]

删除数据

1
delete from `table_name` [where 条件表达式]

Mysql数据库原理及其应用
https://007666.xyz/2022/09/14/Mysql数据库原理及其应用/
作者
梦无念
发布于
2022年9月14日
许可协议
CC BY-NC-SA 4.0