目录
前言
本文可适用于自学者的开篇文章,小白写作,不足敬请指正。
提示:以下是本篇文章正文内容,下面案例可供参考
MySQL数据库的组成
MySQL数据库的组成
- 数据库的本质是特殊的文件,它是由多个表组成的,是数据表的集合;
- 表(table) 某种特定类型数据的结构化清单,是一种结构化的文件,可用来存储某种特定类型的数据,数据是以数据表的形式进行存储的;
- MySQL存储引擎决定了数据表在计算机中的存储方式。MySQL的核心就是存储引擎。
- MySQL的数据库包括系统数据库和用户数据库
MySQL系统数据库
数据库名 | 说明 |
mysql | 包含用于存储MySQL服务的系统信息表,包括授权系统表、系统对象信息表、日志系统表、服务器端辅助系统表等。此数据库中的表默认情况下多为MYASIM引擎。 |
information_schema | 用于保存MySQL服务器所维护的所有其他数据库的信息,包括数据库名、数据库的表、表中列的数据类型与访问权限等。此数据库中的表均为视图,因此在用户或安装目录下无对应用数据文件。 |
performance_schema | 主要用于收集数据库服务器的性能参数。此数据库中所有表的存储引擎为performance_schema,用户不能创建存储引擎为performance_schema的表。默认情况下些数据库为关闭状态。 |
test | 用于测试的数据库 |
MySQL所创建的数据库文件
由MySQL所创建的数据库文件扩展名为“.frm”,用于存储数据库中数据表的框架结构,MySQL的数据库文件名与数据库中的表名相同,每个表都对应有一个同名的frm文件。
E.g.在Oracle和SQLServer等关系型数据库系统都只提供一种存储引擎,所以数据存储的管理机制都一样。
MySQL的存储引擎不同,会创建各自不同的数据库文件
- MYSQL目前有近10个存储引擎,比如: InnoDB、 MyISAM、HEAP(也称为MEMORY)、CSV等
- 当存储引擎为MyISAM时,默认存放位置“C:\ProgramData\MySQL\MySQL Server 5.7\data”,每个数据库都会有独立的文件夹;
- 当存储引擎为InnoDB时,数据库文件存储位置有两个,其中.frm文件存放在“C:\ProgramData\MySQL\MySQL Server 5.7\data”下命名为数据库名称的文件夹下,ibdata1等文件则默认存放在MySQL的安装目录下的data文件夹中。
MySQL的常用的存储引擎
InnoDB存储引擎
InnoDB是MySQL的默认事务型引擎,也是最重要、使用最广泛的存储引擎,被设计用来处理大量短期(short-lived)事务。InnoDB的性能和自动崩溃恢复特性,使得它在非事务型存储的需求中也很流行,在MySQL一般优先考虑InnoDB引擎。
MyISAM存储引擎
MyISAM提供了大量的特性,包括全文索引、压缩、空间函数,广泛应用在Web和数据仓储应用环境下,但不支持事物和等级锁,崩溃后无法安全恢复等。由于MyISAM引擎设计简单,数据以紧密格式存储,对只读的数据性能较好。
Memory存储引擎
Memory存储引擎将表中的数据存储到内存中,不需要进行磁盘I/O,且支持Hash索引,因此查询速度非常快,主要适用于目标数据较小,而且被非常频繁地访问的情况。
CSV存储引擎
CSV存储引擎可将普通的CSV文件(逗号分割值的文件)作为MySQL的表来处理。CSV引擎可以在数据库运行时拷贝文件,可以将Excel电子表格软件中的数据存储为CSV文件,并复制到MySQL的数据目录中,就可以在MySQL中打开。
设置MySQL字符集
MySQL支持的常用字符集和校对规则
- latin1:是系统启动时默认的字符集,它是一个8位的字符集,它把介于128~255之间的字符用于拉丁字母表中的特殊字符的编码,也因此而得名。
- utf8:也称为通用转换格式(8-bit Unicode Transformation Format),是针对Unicode字符的一种变长字符编码。由Ken Thompos在1992年创建,用以解决国际上字符的一种多字节编码,它对英文使用8位、中文使用24位来编码。utf-8包含了全世界所有国家需要用到的字符,是一种国际编码,通用性强。在Internet应用开始中广泛使用。(对于中文乱码问题,建议使用这种编码方式)
- gb2312和gbk:gb2312是简体中文集,而gbk是对gb2312的扩展。gbk的文字编码采用双字节表示,即不论中文和英文字符都使用双字节,为了区分中英文,gbk在编码时将中文每个字节的最高位设为1。gbk包含了全部中文字符,是中国国家编码。
1、查看MySQL支持的字符集。
show character set
2、查看utf8相关字符集的校对规则。
show collation like 'utf8%';
3、查看user数据表的字符集。
show create table user;
E.g.在建立数据库、数据表及进行数据库操作时尽量显式设定使用的字符集。建议在服务器级、结果级、客户端、连接级、数据库级、表级和字段级的字符集都统一设置为一种字符集utf8。
一、查看和创建数据表
1、查看数据表 show tables
E.g.查看onlinedb数据库下数据表
(1)使用USE语句将onlinedb设为当前数据库。
use onlinedb;
Database changed
其中“Database changed”表示数据库切换成功。
(2)查看数据表
show tables;
2、创建数据表 create table语句
create [tempoary] table 表名(字段定义1,字段定义2,……字段定义n);
- TEMPORAY:使用该关键字表示创建的表为临时表;
- 表名:表示所要创建的表的名称;
- 字段定义:定义表中的字段。包括字段名、数据类型、是否允许为空,指定默认值、主键约束、唯一性约束、注释字段、是否为外键以及字段类型的属性等。字段定义格式如下。
- 字段名 类型 [NOT NULL | NULL] [DEFAULT 默认值][AUTO_INCREMENT] [UNIQUE KEY | PRIMARY KEY][COMMENT ‘字符串’][外键定义]
- NULL(NOT NULL):表示字段是否可以为空;
- DEFUALT:指定字段的默认值;
- AUTO_INCREMENT:设置字段为自增,只有整型类型的字段才能设置自增。自增默认从1开始,每个表只能有一个自增字段;
- UNIQUE KEY:唯一性约束;
- PRIMARY KEY:主键约束;
- COMMENT:注释字段;
- 外键定义:外键约束。
序号 | 字段名 | 数据类型 | 标识 | 主键 | 允许空 | 默认值 | 说明 |
1 | uID | int | 是 | 是 | 否 | 用户ID | |
2 | uName | varchar(30) | 否 | 姓名 | |||
3 | uPwd | varchar(30) | 否 | 密码 | |||
4 | uSex | ENUM(‘男’,’女’) | 是 | 男 | 性别 |
CREATE TABLE users (
uID int(11) PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
uName varchar(30) NOT NULL,
uPwd varchar(30) NOT NULL,
uSex ENUM('男','女') DEFAULT '男'
);
3、查看表结构
describe=desc
show [full] columns from 表名;
若使用full关键字,则除显示基本结构外,还会显示权限和注释字段;不用full时和desc效果相同
show create table 表名;
该语句不仅可以查看数据表的详细定义,而且可以查看数据表默认使用的存储引擎和字符集
4、修改数据库
修改表名
ALTER TABLE 旧表名 RENAME [TO] 新表名;
修改字段名
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;
修改字段类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型;
修改字段的排列位置
ALTER TABLE 表名 MODIFY 字段名1 数据类型 FIRST|AFTER 字段名2;
添加字段
ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件] [FIRST| AFTER 已存在的字段名];
删除字段
ALTER TABLE 表名 DROP 字段名;
修改表的存储引擎
ALTER TABLE 表名 ENGINE=存储引擎名;
复制表结构及数据到新表
CREATE TABLE 新表名 SELECT * FROM 源表名;
只复制表结构到新表
CREATE TABLE 新表名 SELECT * FROM 源表名 WHERE FALSE ;
MySQL5.0后,还支持使用如下格式实现表结构的复制
CREATE TABLE新表名LIKE源表名;
复制表的部分字段和数据到新表
CREATE TABLE 新表名 AS(SELECT 字段1,字段2,...... FROM 源表名);
删除数据表
删除一个表时,表的结构定义、数据、约束等都将被启示删除。
DROP TABLE 表名;