开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> 2.数据库简介 -> 正文阅读

[大数据]2.数据库简介

文章目录

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

功能	InnoDB	MYISAM	Memory	Archive
存储限制	64TB	256TB	RAM	None
支持事务¹	Y	N	N	N
支持全文索引²	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 中的参数设定
```
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

-- 学生信息表 (学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号)
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;

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

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

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

最佳实践

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