当前位置:Gxlcms > 数据库问题 > [MySQL数据库之表的详细操作:存储引擎、表介绍、表字段之数据类型]

[MySQL数据库之表的详细操作:存储引擎、表介绍、表字段之数据类型]

时间:2021-07-01 10:21:17 帮助过:16人阅读

[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逻辑架构图

    技术图片

    在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);

技术图片

表介绍

表相当于文件,表中的一条记录就相当于文件的一行内容,不同的是,表中的一条记录有对应的标题,称为表的字段。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);

技术图片

时间类型示例

  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;

技术图片

技术图片

字符类型示例

  # (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|
  

技术图片

技术图片

枚举与集合类型示例

 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 aaa (name VARCHAR(40),size ENUM(‘x-small‘, ‘small‘, ‘medium‘, ‘large‘, ‘x-large‘));
 insert into aaa values (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 bbb (col set(‘a‘, ‘b‘, ‘c‘, ‘d‘));
insert into bbb values (‘a,d‘), (‘d,a‘), (‘a,d,a‘), (‘a,d,d‘), (‘d,a,d‘);

[MySQL数据库之表的详细操作:存储引擎、表介绍、表字段之数据类型]

标签:个数   mem   角度   modify   客户端连接   处理程序   ror   统计   atime   

人气教程排行