31. MySQL数据库：表的详细操作

2021-09-14 12:06:36

MySQL数据库
- 表的详细操作
  - 存储引擎
  - 表介绍
  - 修改表详细操作
  - 复制表
  - 表字段之数据类型

表的详细操作

存储引擎

mysql中建立的库======>文件夹

库中建立的表======>文件

用来存储数据的文件有不同的类型，每种文件类型对应各自不同的处理机制：

处理图片用jpg、png

处理视频用mp4

处理音频用mp3

处理文本用txt

数据库中的表也应该有不同的类型，表的类型不同，会对应mysql不同的存取机制，表类型又称为存储引擎engine

表的本质就是文件，文件用来存储数据的，某一种格式的表，就相当于某一种格式的文件，每一种不同类型的文件，都有自己专门的存储机制。存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）

mysql数据库管理软件会为每一种表类型定制专门的处理代码，相当于一段小的程序

show engines\G  #查看所有支持的存储引擎
show variables like ‘storage_engine%‘; #查看正在使用的存储引擎

常见的存储引擎：

myisam 存储引擎:
    拥有较高的插入，查询速度，但不支持事务

innodb 存储引擎: 
    Mysql的默认数据库，事务型数据库的首选引擎，支持ACID事务，支持行级锁定

blackhole 存储引擎: 
    黑洞引擎，写入的任何数据都会消失，一般用于记录binlog做复制的中继

memory 存储引擎: 
    所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和
    数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失
    
"""
创建表t1：
create table t1(id int)engine=innodb;        
以后处理t1表的时候就有一个专门的程序叫innodb存储引擎
innodb存储引擎：本质就是一段处理程序 
等同于：
文件.mp4==========》 视频播放器

"""

MySQL逻辑架构图

31. MySQL数据库：表的详细操作

在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySQL最
重要、最与众不同的特性就是它的存储引擎架构，这种架构将：查询处理、其他系统任务、数据的存储与提取
三部分分离。所以，带来的好处就是可以在使用时根据性能、特性，以及其他需求来选择数据存储方式。用户
也可以根据自己的需要编写自己的存储引擎

存储引擎架构分为三层，自上而下，分为第一层：连接层；第二层：服务层；第三层：引擎层。

1.连接层：

MySQL的最上层是连接服务，引入了线程池的概念，允许多台客户端连接。
主要工作是：连接处理、授权认证、安全防护等。

连接层为通过安全认证的接入用户提供线程，同样，在该层上可以实现基于SSL 的安全连接。

2.服务层：

服务层用于处理核心服务，如标准的SQL接口、查询解析、SQL优化和统计、全局的和引擎依赖的缓存与缓冲器
等等。所有的与存储引擎无关的工作，如过程、函数等，都会在这一层来处理。在该层上，服务器会解析查询并
创建相应的内部解析树，并对其完成优化，如确定查询表的顺序，是否利用索引等，最后生成相关的执行操作。
如果是SELECT语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够
很好的提升系统的性能。

3.引擎层：

存储引擎层，存储引擎负责实际的MySQL数据的存储与提取，服务器通过API 与 存储引擎进行通信。不同的存
储引擎功能和特性有所不同，这样可以根据实际需要有针对性的使用不同的存储引擎。

存储引擎实验

create database day02;
use day02;
create table t1(id int)engine=myisam;
create table t2(id int)engine=innodb;
create table t3(id int)engine=blackhole;
create table t4(id int)engine=memory;

insert t1 values(111);
insert t2 values(222);
insert t3 values(333);
insert t4 values(444);

31. MySQL数据库：表的详细操作

表介绍

表相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段。id，name，qq，age称为字段，其余的，一行内容称为一条记录

修改表详细操作

语法：
1. 修改表名
      ALTER TABLE 表名 
                          RENAME 新表名;

2. 增加字段
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…],
                          ADD 字段名  数据类型 [完整性约束条件…];
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
                            
3. 删除字段
      ALTER TABLE 表名 
                          DROP 字段名;

4. 修改字段
      ALTER TABLE 表名 
                          MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
        
示例：
1. 修改存储引擎
mysql> alter table service 
    -> engine=innodb;

2. 添加字段
mysql> alter table student10
    -> add name varchar(20) not null,
    -> add age int(3) not null default 22;
    
mysql> alter table student10
    -> add stu_num varchar(10) not null after name;                //添加name字段之后

mysql> alter table student10                        
    -> add sex enum(‘male‘,‘female‘) default ‘male‘ first;          //添加到最前面

3. 删除字段
mysql> alter table student10
    -> drop sex;

mysql> alter table service
    -> drop mac;

4. 修改字段类型modify
mysql> alter table student10
    -> modify age int(3);
mysql> alter table student10
    -> modify id int(11) not null primary key auto_increment;    //修改为主键

5. 增加约束（针对已有的主键增加auto_increment）
mysql> alter table student10 modify id int(11) not null primary key auto_increment;
ERROR 1068 (42000): Multiple primary key defined

mysql> alter table student10 modify id int(11) not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

6. 对已经存在的表增加复合主键
mysql> alter table service2
    -> add primary key(host_ip,port);        

7. 增加主键
mysql> alter table student1
    -> modify name varchar(10) not null primary key;

8. 增加主键和自动增长
mysql> alter table student1
    -> modify id int not null primary key auto_increment;

9. 删除主键
a. 删除自增约束
mysql> alter table student10 modify id int(11) not null; 

b. 删除主键
mysql> alter table student10                                 
    -> drop primary key;

复制表

复制表结构＋记录 （key不会复制: 主键、外键和索引）
mysql> create table new_service select * from service;

只复制表结构
mysql> select * from service where 1=2;        //条件为假，查不到任何记录
Empty set (0.00 sec)
mysql> create table new1_service select * from service where 1=2;  
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table t4 like employees;

表字段之数据类型

存储引擎决定了表的类型，而表内存放的数据也要有不同的类型，每种数据类型都有自己的宽度，但宽度是可选的

1. 数字：
    整型：tinyinit  int  bigint
      作用：存储年龄，等级，id，各种号码等
        
    浮点型：
      定点数类型：decimal
      浮点型：float  double
      作用：存储薪资、身高、体重、体质参数等
        float ：在位数比较短的情况下不精准
        double ：在位数比较长的情况下不精准
            0.000001230123123123
            存成：0.000001230000

        decimal：（如果用小数，则用推荐使用decimal）
            精准
            内部原理是以字符串形式去存

2. 字符串：
    char（10）：简单粗暴，浪费空间，存取速度快，用于表示固定长度的字符串
        root存成root000000
    varchar：精准，节省空间，存取速度慢，用于变长的字符串

    sql优化：创建表时，定长的类型往前放（比如性别），变长的往后放（比如地址或描述信息）
                              
   ps：长度限制最高255个字符，超了就把文件路径存放到数据库中。
            比如图片，视频等找一个文件服务器，数据库中只存路径或url。
    
注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度
更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方
面的因素，以求达到最佳的平衡


3. 时间类型：year  date  time  datetime  timestamp
	作用：存储用户注册时间，文章发布时间，员工入职时间，出生时间，过期时间等
    最常用：datetime
     year: 年
          YYYY（1901/2155）
     date: 年月日
          YYYY-MM-DD（1000-01-01/9999-12-31）
     time: 时分秒
          HH:MM:SS（‘-838:59:59‘/‘838:59:59‘）
     datatime: 年月日时分秒
          YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
     timestamp：年月日时分秒（有年范围限制，但在这个时间之前使用更方便）
          YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）

4. 枚举类型与集合类型
    字段的值只能在给定范围中选择，如单选框，多选框
    enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female
    set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）

数字类型示例

整型：（唯有整型指的宽度是显示宽度，不是存储宽度）
create table t8(id int(1));  -- (1)指的是打印时候的显示宽度
insert t8 values(1212312312312312312);

create table t9(id int);  -- 整型有默认宽度，不需要指定存储宽度

浮点型：
create table t10(x float(255,30)); 
create table t11(y double(255,30));
create table t12(z decimal(65,30));

insert t10 values(1.1111111111111111111111111111111);
insert t11 values(1.1111111111111111111111111111111);
insert t12 values(1.1111111111111111111111111111111);

31. MySQL数据库：表的详细操作

时间类型示例

create table student(
    id int,
    name varchar(16),
    birth date,
    class_time time,
    reg_time datetime,
    born_year year
);

insert student values
(1,‘egon‘,"1993-01-23","08:30:00","2020-3-23 09:30:21","1993");


create table t13(x datetime);
create table t14(x timestamp); 

create table t15(id int,name varchar(16),commit_time timestamp);
insert t15(id,name) values(1,‘今天天气真好‘);
update t15 set name = "今天阳光明媚" where id=1;

31. MySQL数据库：表的详细操作

字符类型示例

# （4）代表的是字符个数不是字节，表示存储宽度最大为4个
create table t16(name char(4)); 
create table t17(name varchar(4));

insert t16 values("你好啊哈哈");
insert t17 values("你好啊哈哈");

insert t16 values("你"); -- 会存成 "你   "
insert t17 values("你"); -- 会存成 "你"

|a   |ab  |abc |abcd|
|1byte+a|1byte+ab|1byte+abc|1byte+abcd|

31. MySQL数据库：表的详细操作

枚举与集合类型示例

create table t18(
    id int,
    name varchar(16),
    gender enum("男","女","未知"),
    hobbies set("play","music","read","movie")
);
insert t18 values
(1,"egon","男","play,music");


枚举类型（enum多选一）
An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
示例：
CREATE TABLE shirts (name VARCHAR(40),size ENUM(‘x-small‘, ‘small‘, ‘medium‘, ‘large‘, ‘x-large‘));
INSERT INTO shirts (name, size) VALUES (‘dress shirt‘,‘large‘), (‘t-shirt‘,‘medium‘),(‘polo shirt‘,‘small‘);

  
集合类型（set多选多）
A SET column can have a maximum of 64 distinct members.
示例：
CREATE TABLE myset (col SET(‘a‘, ‘b‘, ‘c‘, ‘d‘));
INSERT INTO myset (col) VALUES (‘a,d‘), (‘d,a‘), (‘a,d,a‘), (‘a,d,d‘), (‘d,a,d‘);

31. MySQL数据库：表的详细操作

码农公寓

MySQL数据库

表的详细操作

存储引擎

表介绍

修改表详细操作

复制表

表字段之数据类型

表的详细操作

存储引擎

mysql中建立的库======>文件夹

库中建立的表======>文件

MySQL逻辑架构图

存储引擎实验

表介绍

修改表详细操作

复制表

表字段之数据类型

数字类型示例

时间类型示例

字符类型示例

枚举与集合类型示例

相关文章