2.MySQL数据库简介

本文最后更新于：2021年10月1日 晚上

MySQL数据库简介

数据库控制

命令行操作数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql -u root -p123456		-- 连接数据库

update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';	-- 更新密码
flush privileges;	-- 刷新权限

-----------------------------------------
show tables;		-- 查看数据库所有的表
describe 表名；	-- 显示数据库中表的信息

exit;	-- 退出连接

--	单行注释
/*  */	-- 多行注释

show 命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
show databases; -- 显示mysql中所有数据库的名称。

-- 如果当前没有使用 use 选择数据库，那么对所有表操作都要带上其对应d
show tables或show tables from database_name; -- 显示当前数据库中所有表的名称。
show table status; -- 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间。
show columns from database_name.table_name; -- 显示表中列名称。
	desc 表名		-- 显示表的结构
show index from table_name; -- 显示表的索引。

show grants for user_name; -- 显示一个用户的权限，显示结果类似于grant 命令。
show status; -- 显示一些系统特定资源的信息，例如，正在运行的线程数量。
show variables; -- 显示系统变量的名称和值。
show processlist; -- 显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。
show privileges; -- 显示服务器所支持的不同权限。

show create database database_name; -- 显示create database 语句是否能够创建指定的数据库。
show create table table_name; -- 显示create database 语句是否能够创建指定的数据库。

show logs; -- 显示BDB存储引擎的日志。
show warnings; -- 显示最后一个执行的语句所产生的错误、警告和通知。
show errors; -- 只显示最后一个执行语句所产生的错误。

show [storage] engines; 	-- 显示安装后的可用存储引擎和默认引擎。
show character set;		-- 显示服务器支持的数据集
show innodb status; -- 显示innoDB存储引擎的状态。

数据库表的列类型

数值类型

图片
  • int(M)：M指示最大显示宽度。显示宽度与存储大小或类似所包含值的范围无关

字符串类型

图片

日期和时间型类型

图片

NULL值

  • 理解为 “没有值” 或 “未知值”
  • 不要用NULL进行算术运算 , 结果仍为NULL

数据库表的字段属性

UnSigned

  • 无符号的
  • 声明该数据列不允许负数 .

ZEROFILL

  • 0填充的
  • 不足位数的用0来填充 , 如int(3),5则为005

Auto_InCrement

  • 自动增长的 , 每添加一条数据 , 自动在上一个记录数上加 1(默认)

  • 通常用于设置主键 , 且为整数类型

  • 可定义起始值和步长

    • 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
    • SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)

NULL 和 NOT NULL

  • 默认为NULL , 即该列允许为空
  • 如果设置为NOT NULL , 则该列必须有值

DEFAULT

  • 用于设置默认值
  • 例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值

拓展：阿里云项目数据库规范

每一个表都要有这几个字段

1
2
3
4
5
id		主键
version		乐观锁
is_delete	伪删除
gmt_create	创建时间
gmt_update	修改时间

数据库表的引擎

默认使用 INNODB

功能 InnoDB MYISAM Memory Archive
存储限制 64TB 256TB RAM None
支持事务[1] Y N N N
支持全文索引[2] N Y N
支持哈希索引 N N Y N
支持数据缓存 Y N N/A N
支持外键 Y N N N
  • 选择策略：
    • 如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择
    • 如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率
    • 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎
      • MySQL中使用该引擎作为临时表，存放查询的中间结果
    • 如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。
      • Archive非常适合存储归档数据，如记录日志信息可以使用Archive

数据库表的在物理空间的位置

  • MySQL数据表以文件方式存放在磁盘中

    • 包括表文件 , 数据文件 , 以及数据库的选项文件
    • 位置 : Mysql安装目录\data\下存放数据表 . 目录名对应数据库名 , 该目录下文件名对应数据表

  • 文件类型：

    • *.frm ：存储表的列信息（表的结构）
    • *.MYD ：数据文件
    • *.MYI：索引文件
    • *.ibd：数据+索引

  • 具体文件介绍

    文件结构名称 作用
    ibdata1 系统数据字典信息（元数据信息），undo表空间数据
    ib_logfiel0~ib_logfile1 REDO日志文件，事务日志文件
    ibtmp1 临时表空间磁盘位置，存储临时表

    ibdata1存储所有InnoDB引擎的表的元信息和undo信息

    各个表.idb文件存储自己的数据行和索引信息

  • 一张InnoDB表由一个 *.frm文件 、一个*.idb文件, 以及上一级目录的ibdata1文件；

    一张MyISAM表由.frm、MYD、MYI组成

数据库表的字符集

我们可为数据库,数据表,数据列设定不同的字符集，设定方法 :

  • 创建时通过命令来设置 , 如 : CREATE TABLE 表名()CHARSET = utf8;

  • 如无设定 , 则根据MySQL数据库配置文件my.ini 中的参数设定

    1
    character-set-server=utf8

不建修改配置文件，这样会导致sql语句的移植性降低

数据库表的外键（了解）

概念

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。

由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。

引用别人的表——主表；

被别人引用的表——从表；

作用

保持数据一致性，完整性，主要目的是控制存储在外键表中的数据约束，使两张表形成关联。

主表中的外键只能引用外表中的对应列的值或使用空值。

MySQL命令操作

  • 创建外键

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    -- 创建外键的方式一 : 创建子表同时创建外键

    -- 年级表 (id\年级名称)
    CREATE TABLE `grade` (
    `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID',
    `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
    PRIMARY KEY (`gradeid`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8

    -- 学生信息表 (学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号)
    CREATE TABLE `student` (
    `studentno` INT(4) NOT NULL COMMENT '学号',
    `studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `sex` TINYINT(1) DEFAULT '1' COMMENT '性别',
    `gradeid` INT(10) DEFAULT NULL COMMENT '年级',
    PRIMARY KEY (`studentno`),
    KEY `FK_gradeid` (`gradeid`),	-- 定义外键key
    CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)	-- 给外键添加约束（执行引用）
    ) ENGINE=INNODB DEFAULT CHARSET=utf8

    --------------------------------------------
    -- 创建外键方式二 : 创建子表完毕后,修改子表字段 添加外键约束
    ALTER TABLE `student`
    ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

  • 删除外键

    1
    2
    3
    4
    5
    -- 删除外键
    ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
    -- 发现执行完上面的,索引还在,所以还要删除索引
    -- 注:这个索引是建立外键的时候默认生成的
    ALTER TABLE student DROP INDEX FK_gradeid;
    • 删除具有主外键关系的表时 , 要先删子表 , 后删主表

以上操作都是物理外键、数据库级别的外键，不建议使用

  • 原因：对于数据库的所有外键的每次插入、更新和删除后，数据库服务器进行完整性检查是一个耗费时间和资源的过程，它可能影响性能

最佳实践

  • 数据库就是单纯的表，只用来存数据，只有行、列
  • 我们想使用多张表，想使用外键，可以使用程序实现（联合查询）

  1. 事务是在数据库上按照一定的逻辑顺序执行的任务序列,是并发控制的单位。这些操作要么都做，要么都不做，不可分割 ↩︎

  2. 原理是先定义一个词库，然后在文章中查找每个词条(term)出现的频率和位置，把这样的频率和位置信息按照词库的顺序归纳 ↩︎

MySql基础学习
MySql

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议 ，转载请注明出处！