MySQL数据库管理

MySQL 商业版与社区版

• MySQL 商业版是由 MySQL AB 公司负载开发与维护，需要付费才能使用
• MySQL 社区版是由分散在世界各地的 MySQL开发者、爱好者一起开发与维护，可以免费使用
• 两者区别
  - 商业版组织管理与测试环节更加严格，会比社区版更稳定
  - 商业版不遵守 GPL，社区版遵从 GPL 可以免费使用
  - 商业版可获得 7*24 小时的服务，社区版则没有

Mysql 数据库

日志

数据库日志文本用于备份，恢复，故障定位、分析是最核心的部分
mysql 与 oracle 日志有所区别
mysql 写一条数据，同步到日志中一条

PS：insert into ky19 (id,name,score) values (1,‘zhangsan’,20) ;

oracle ：重做日志组
一个组中至少3个日志成员，轮流存储日志
还会有另一个组与之同步 / 备份

阿里云 OSS 对象存储，一式三份备份（使用重做日志组思想）
对象存储：存储大都是一些静态文件，图片、音频、视频不能直接在对象存储中修改数据

例：飞天平台，盘古系统，钟馗；安全，女娲；选举 / 调度，盘古一式三份

Mysql 存储引擎

myisam 和 innodb
myisam：快速读取，不支持事务
innodb：更注重写，支持事务

MySQL 数据库管理

查看数据库信息

show database

查看数据库信息

use 数据库名

show tables
show tables in mysql

显示数据表的结构（字段）
describe user;

Field：字段名称
Type：数据类型
Null：是否允许为空
Key：主键
Default：默认值
Extra：扩展属性，例如：标志符列（标识了种子，增量 / 步长）1 2
id：1 3 5 7

常用的数据类型

int：整型			——用于定义整数类型的数据
float：单精度浮点 4 字节 32 位			——准确表示到小数点后六位
double：双精度浮点 8 字节 64 位
char：固定长度的字符类型				——用于定义字符类型数据
varchar：可变长度的字符类型
text：文本
image：图片
decimal（7,5）：7个有效长度数字，小数点后面有5位		指定长度数组
Char： 如果存入数据的实际长度比指定长度要小，会补空格至指定长度，如果存入的数据的实际长度大于指定长度，低版本会被截取，高版本会报错

主键是唯一的，但主键可以由多个字段构成

MySQL 数据库的数据文件存放在 /usr/local/mysql/data 目录下，每个数据库对应一个子目录，用于存储数据表文件。每个数据表对应为三个文件，扩展名分别为 “ .frm ”、“ .MYD ” 和 “ .MYI ”。

" .MYD " 文件是 MyISAM 存储引擎专用，存储 MyISAM 表的索引相关信息。对于 MyISAM 存储来说，可以被 cache 的内容主要就是来源于 " .MYI " 文件中。每一个 MyISAM 表对应一个 " .MYI "文件，存放于位置和 " .frm " 以及 " .MYD " 一样。

" .MYI " 文件也是专属于 MyISAM 存储引擎的，主要存放 MyISAM 表的索引相关信息。对于 MyISAM 存储来说，可以被 cache 的内容主要就是来源于 " .MYI "文件中。每一个 MyISAM 表对应一个 " .MYI " 文件，存放于位置和 " .frm " 以及 " .MYD "一样。

MyISAM 存储引擎的表在数据库中，每一个表都被存放为三个以表名命名的物理文件（frm，myd，myi）。每个表都有且仅有这样三个文件作为 MyISAM
存储类型的表的存储，也就是说不管这个表有多少个索引，都是存放在同一个 .MYI 文件中。

另外还有" .ibd " 和 ibdata 文件，这两种文件都是用来存放 Innodb 数据的，之所以有两种文件来存放 Innodb 的数据（包括索引），是因为 Innodb 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是独享表空间存放数据，且每个表一个 " .ibd " 文件，文件存放在和 MyISAM 数据相同的位置。如果选用共享存储表空间来存放数据，则会使用 ibdata 文件来存放，所有表共同使用一个（或者多个，可自行配置）ibdata 文件。

SQL 语言分类

DDL：数据定义语言，用于创建数据库对象，如库、表。索引等
DML：数据操纵语言，用于对表中的数据进行管理，用来插入、删除和修改数据库中的数据
DQL：数据查询语言，用于从数据表中查找符合条件的数据记录
DCL：数据控制语言，用于设置或者更改数据库用户或者角色权限（数据控制语句，用于控制不通数据段直接的许可和访问级别的语句，这些语句定义了数据库、表、字段、用户的访问权限和安全级别，如 COMMIT、ROLLBACK、GRANT、REVOKE）

1. DDL：数据定义语言，用于创建数据库对象，如库、表、索引等
   create 创建
   drop 删除
   alter 修改

创建新的表

CREATE TABLE 表名（字段1 数据类型,字段2 数据类型[,…][,PRIMARY KEY(主键名)]）;
主键名一般选择能代理唯一性的字段不允许取空值（NULL），一个表只能有一个主键。

CREATE DATABASE kgc_school;
USE kgc_school;
CREATE TABLE KY19 (id int NOT NULL,name char(10) NOT NULL,score decimal(5,3),passwd
char(48) DEFAULT ' ', PRIMARY KEY (id));
DESC KY19;

#NOT NULL				——不允许为空值
#DEFAULT ' '			——默认值为空

PRIMARY KEY ：主键一般选择没有重复并且不为空值得字段

删除指定的数据表
use 数据库名
DROP TABLE 表名

DROP TABLE [数据库名.] 表名;
如不用USE进入库中，则需加上数据库名

删除指定的数据库
DROP DATABASE 数据库名;

2. DML管理表中的数据记录
   数据操纵语言，用于对表中的数据进行管理，用来插入、删除和修改数据库中的数据
   insert
   update
   delete

   格式：
   INSERT INTO KY19 (id,name,score,passwd)
   values(1,‘zhangsan’,80,PASSWORD(‘123456’);
   #PASSWORD(‘123456’) ：查询数据记录时，密码字串以加密形式显示：若不使用 PASSWORD()，查询时以明文显示。

   INSERT INTO zzz VALUES (2,‘lisi’,80,654321);
   SELECT * FROM zzz; ——查询表的数据记录

修改、更新数据表中的数据记录

格式：
UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];

在数据表中删除指定的数据记录

格式：
DELETE FROM zzz WHERE id=4;

DQL 查询数据记录

select

格式
SELECT 字段名1,字段名2[,…] FROM 表名 [WHERE 条件表达式];

扩展

在数据表中删除指定的数据记录

格式
DELETE FROM 表名 [WHERE 条件表达式];

DCL

修改表名和表结构
ALTER TABLE 旧表名 RENAME 新表名;

示例：
ALTER TABLE zzz RENAME yyy;
ALTER TABLE yyy RENAME zzz;

扩展表结构（增加字段）
ALTER TABLE 表名 ADD address varchar(50) default ‘地址不详’;
——default ‘地址不详’：表示此字段设置默认值为地址不详，可与 NOT NULL 配合使用
ALTER TABLE yyy ADD address varchar(50) NOT NULL default ‘地址不详’;

修改字段(列)名，添加唯一键
ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];
unique key：唯一键（特性：唯一，但可以为空，空值只允许出现一次）
Primary key：唯一且非空（主键一般选择能代理唯一性的字段不允许取空值（NULL），一个表只能有一个主键。）

ALTER TABLE yyy CHANGE name user_name varchar(10) unique key;
CHANGE 可修改字段名、数据类型、约束等所有项。

ALTER TABLE 表名 modify column 字段名类型。
数据库中表字段是 varchar(30)，修改类型可以用（谨慎修改类型，可能会导致原有数据出错）

删除字段
格式
ALTER TABLE 表名 DROP 字段名;

数据表高级操作

克隆表

create table yyy2 like yyy;

复制格式，通过LIKE方法，复制yyy表结构生成yyy2表

insert into yyy2 select * from yyy;		——备份内容

克隆表将数据表的数据记录生成到新的表中

CREATE TABLE test02 (SELECT * from test);		——复制 test 表数据到 test02 中

show create table test02\G				——获取数据表的表结构、索引等信息
SELECT * from test02;

清空表，删除表内的所有数据
方法一

delete from yyy3;
DELETE 清空表后，返回的结果内有删除的记录条目;
DELETE
工作时是一行一行的删除记录数据的；如果表中有自增长字段，使用 DELETE FROM 删除所有记录后，再次新添加的记录会从原来最大的记录 ID 后面继续自增写入记录。

方法二

truncate table test01;
TRUNCATE 清空表后，没有返回被删除的条目：TRUNCATE

工作时是将表结构按原样重新建立，因此在速度上 TRUNCATE 会比 DELETE 清空表快；使用 TRUNCATE TABLE 清空表内数据后，ID 会从 1 开始重新记录。

创建临时表

临时表创建成功之后，使用 SHOWTABLE 命令是看不到创建的临时表的，临时表会在连接退出后被销毁。

若在连接退出之前，也可以执行增删改查等操作，如使用 DROP TABLE 语句手动直接删除临时表。

PS：无法创建外键
CREATE TEMPORARY TABLE 表名 (字段1 数据类型，字段2 数据类型 [, …] [, PRIMARY KEY(主键名)]);

sql 写入 ——> 数据表中 ——> 先把数据保存在内存中 ——> 写入到磁盘 insert into info ——> info 这张表，会先复制一份表数据到内存里面，给进行修改 ——> 敲完回车后，确定提交，才会写入数据表中 ——> 再保存在磁盘里面