2.数据库简介

2.数据库简介

文章目录

• MySQL数据库简介
• • 数据库控制
  • • show 命令
  • 数据库表的列类型
  • • 数值类型
    • 字符串类型
    • 日期和时间型类型
    • NULL值
  • 数据库表的字段属性
  • 数据库表的引擎
  • 数据库表的在物理空间的位置
  • 数据库表的字符集
  • 数据库表的外键（了解）
  • • 概念
    • 作用
    • MySQL命令 *** 作

MySQL数据库简介 数据库控制

命令行 *** 作数据库

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 命令

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

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

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

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

id		主键
version		乐观锁
is_delete	伪删除
gmt_create	创建时间
gmt_update	修改时间

数据库表的引擎

默认使用 INNODB

功能InnoDBMYISAMMemoryArchive存储限制64TB256TBRAMNone支持事务¹YNNN支持全文索引²NYN支持哈希索引NNYN支持数据缓存YNN/AN支持外键YNNN

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

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

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

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

• 文件类型：

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

• 具体文件介绍

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

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

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

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

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

数据库表的字符集

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

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

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

  character-set-server=utf8
  

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

数据库表的外键（了解） 概念

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

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

引用别人的表——主表；

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

作用

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

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

MySQL命令 *** 作

• 创建外键

  -- 创建外键的方式一 : 创建子表同时创建外键
  
  -- 年级表 (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
  
  -- 学生信息表 (学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份z号)
  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`);
  

• 删除外键

  -- 删除外键
  ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
  -- 发现执行完上面的,索引还在,所以还要删除索引
  -- 注:这个索引是建立外键的时候默认生成的
  ALTER TABLE student DROP INDEX FK_gradeid;
  

  • 删除具有主外键关系的表时 , 要先删子表 , 后删主表

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

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

最佳实践

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

---

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

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