表相关操作

目录

• 一：存储引擎介绍
  • 1：什么是存储引擎？
    • • 扩展
  • 2:mysql四种主要的存储引擎介绍
    • 总结
  • 3:验证不同的存储引擎
• 二：表介绍
• 三:创建表的完整语法
• 四：数据库的三种模式指定
  • • 查看当前模式的
    • 修改模式
• 五：数据类型
• 六：表完整性约束
• 七：修改表alter table 了解
  • • • 注意：一下所有的约束条件可以指定多个
      • 修改表名
      • 增加字段
      • 删除字段
      • 修改字段
• 八：复制表
• 九：删除表
• 十：总结

一：存储引擎介绍

1：什么是存储引擎？

存储引擎就是表的类型，针对不同类型的表，mysql使用不同的代码处理

现实生活中我们用来存储数据的文件有不同的类型，每种文件类型对应各自不同的处理机制：比如处理文本用txt类型，处理表格用excel，处理图片用png等 数据库中的表也应该有不同的类型，表的类型不同，会对应mysql不同的存取机制，表类型又称为存储引擎。 存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方 法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和 操作此表的类型）

扩展

在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql 数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据 自己的需要编写自己的存储引擎 SQL 解析器、SQL 优化器、缓冲池、存储引擎等组件在每个数据库中都存在,但不是每 个数据库都有这么多存储引擎。MySQL 的插件式存储引擎可以让存储引擎层的开发人员设 计他们希望的存储层,例如,有的应用需要满足事务的要求,有的应用则不需要对事务有这 么强的要求 ;有的希望数据能持久存储,有的只希望放在内存中,临时并快速地提供对数据 的查询。

2:mysql四种主要的存储引擎介绍

1. InnoDB

   使用版本: MySQL5.5版本及之后默认的存储引擎 优点: 支持事务, 行级锁定和外键约束对数据的安全性较 半个缺点: 访问速度没有MyISAM快, 但是我们更加注重的是数据的安全. 每个InnoDB在磁盘上存储成2个文件，其中文件名和表名都相同，但是扩展名分别为： .frm(存储表结构) .idb(i -> index. 索引+数据组织表)

2. MyISAM

   使用版本: MySQL5.5版本之前默认的存储引擎 优点: 访问速度要比Innodb更快 缺点: 不支持事务，也不支持外键, 对事务完整性没有要求, 数据的安全性没有Innodb高. 每个MyISAM在磁盘上存储成3个文件，其中文件名和表名都相同，但是扩展名分别为： .frm(存储表结构) .MYD(MyData，存储数据) .MYI(MyIndex，存储索引)

3. MEMORY

   优点: 采用内存存储, 访问数据可以得到最快的响应. 缺点: 断电数据丢失. 或当mysqld守护进程崩溃时，所有的Memory数据也会丢失. 每个MEMORY在磁盘上存储成1个文件，其中文件名和表名都相同，但是扩展名分别为： .frm(存储表结构)

4. BLACKHOLE

   特点: 无论存什么，都立刻消失(黑洞). 类似于回收站 每个BLACKHOLE在磁盘上存储成1个文件，其中文件名和表名都相同，但是扩展名分别为： .frm(存储表结构)

总结

1: InnoDB支持事务，MyISAM 不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要 哪个出错还可以回滚还原，而MyISAM就不可以了.

2:MyISAM适合查询以及插入为主的应用，InnoDB 适合频繁修改以及涉及到安全性较高的应用.

3:InnoDB支持外键，MyISAM 不支持

4:InnoDB中不保存表的行数, InnoDB需要扫描一遍整个表来计算有多少行. MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要扫描整个表;

5:对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引;

6:清空整个表时，InnoDB 是一行一行的删除，效率非常慢。MyISAM 则会重建表;

7:InnoDB支持行锁(注意: 某些情况下还是锁整表)

3:验证不同的存储引擎

指定不同存储引擎进行插入数据测试

查看所有的支持的存储引擎

show engines;

常看正在使用的存储引擎

# storage存储 储存 保管
show variables like '%storage_engine%'

创建不同的表指定不同的存储引擎

create database test;
use test;
create table innodb_t1(id int) engine=innodb;
create table myisam_t1(id int) engine=myisam;
create table memory_t1(id int) engine=memory;
create table blackhole_t1(id int) engine=blackhole;

插入数据

insert into innodb_t1 values(1);
insert into myisam_t1 values(1);
insert into memory_t1 values(1);
insert into blackhole_t1 values(1)

查看

select * from innodb_t1
select * from myisam_t1
select * from memory_t1
select * from blackhole_t1

结果

mysql> select * from innodb_t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.07 sec)

mysql> select * from myisam_t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.07 sec)

mysql> select * from memory_t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.06 sec)

mysql> select * from blackhole_t1;
Empty set (0.07 sec)

重启测试

net stop mysql & net start mysql

mysql> select * from memory_t1;
Empty set (0.08 sec)

二：表介绍

表相当于文件，表中的一条记录就相当于文件的一行内容，不用的是，表中的一条记录有对应的标题，称为表的字段

id,name,age,sex称为字段，其余，一行内容称为一条记录

三:创建表的完整语法

# 语法
create table 表名(
	字段名1  类型(宽度) 约束条件,
    字段名2  类型(宽度) 约束条件,
    字段名3  类型(宽度) 约束条件,
 )

注意

1. 同一张表中字段名不能重复

2. 宽度和约束条件是可选的(可有可无)而字段名和字段类型是必须的

   约束条件写的话，可以支持多个，字段名1 类型(宽度) 约束条件1 约束条件2....,

3. 最后一行指定不能有逗号

   create table t(
       id int,
       name char, # -->出错了
   );
   

警告

1. char不指定宽度，默认宽度为1

   create table t1(name char);
   insert into t1 values('json')
   

2. 关键字null没有宽度限制

   insert into t1 values(null);
   

3. 针对不同的版本会出现不同的问题

   1. 5.6版本默认没有开启严格模式 规定只能存一个字符你给了多个字符，那么会自动帮你截取.
   2. 5.7版本及以上或者开启了严格模式 那么规定只能存几个 就不能超，一旦超出范围立刻报错(Data too long for column 'name' at row 1.)

4. 严格模式开不开？

   mysql5.7之后的版本默认都是开启严格模式的

   使用数据库准则

   1. 能尽量少的让数据库干活就尽量少 不要给数据库增加额外的压力.
   2. 因为没有开启严格模式, 当遇到宽度超出时, 会进行额外的截取操作这种操作就会给数据库增加额外的压力. 换个角度来说, 本来数据的存储就不因该超出限制范围.

5. 总结

   宽度是用来限制数据的存储

   约束条件是在宽度的基础之上增加额外的约束

四：数据库的三种模式指定

variables /'vɛrɪəbl/ 变量

session /ˈseʃn/ 会 话

trans /trænz 反式

pad /pæd/ 填补

substitution /ˌsʌbstɪˈtuːʃn/ 替代 换人 换船

strict trans tables 严格的反式表

no engine substitution 没有引擎提交

pad char to full length 填补字符到全长度'

查看当前模式的

一：方式一

大范围查找 variables + 模糊匹配
show variables like "%mode%";
# like 模糊匹配关键字
# % 匹配任意多个字符
# _ 匹配任意单个字符

二：方式二

select @@sql_mode;

修改模式

注意!!: 这种修改是替换操作, 如果想保留原来得模式并增添新得模式需要使用逗号隔开. 示例: 'strict_trans_tables,no_engine_substitution'

方式

set global sql_mode = 'STRICT_TRANS_TABLES';
set  session # 只在当前窗口有效
set golbal # 全局有效
	# 注意修改完之后，需要重新登录服务端

五：数据类型

六：表完整性约束

七：修改表alter table 了解

注意：一下所有的约束条件可以指定多个

修改表名

alter table 表名 rename 新的表名;

增加字段

alter table 表名 add 字段名 字段类型(宽度) 约束条件;
alter table 表名 add  字段名 字段类型(宽度) 约束条件 first;
alter table 表名 add 字段名 字段类型(宽度) 约束条件 after 字段名;

删除字段

alter table 表名  drop 字段名;

修改字段

alter table 表名 modify 字段名 字段类型(宽度) 约束条件;
alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;

八：复制表

注意：

复制不包含的key. 主键key, 外键key, 索引key

如果通过where筛选的旧表中没有数据, 会生成一张空的表.

create table 新表名 select * from 就表名;
create table 新表名  select * from 旧表名 where id 1 != 1;

九：删除表

drop table 表名;

十：总结

# mysql四种存储引擎: InnoDB, MyISAM, MEMORY, BLACKHOLE
"""
InnoDB: 支持事务, 外键与约束, 行锁, 数据安全, 访问速度没有MyISAM快. 5.5版本后
    创建一个该模式的表生成2个文件. 后缀名: .frm, .idb
    
MyISAM: 不支持事务, 外键与约束, 行锁, 数据不太安全, 只是访问问数据快于InnoDB. 5.5版本前
    创建一个该模式的表生成3个文件. 后缀名: .frm, .MYD, .MYI
    
MEMORY: 内存
    创建一个该模式的表生成1个文件. 后缀名: .frm
    
BLACKHOLE: 垃圾回收站
    创建一个该模式的表生成1个文件. 后缀名: .frm
"""

# 了解3种模式
"""
普通模式: no_engine_substitution
严格模式: strict_trans_tables
取消剔除char空格模式: pad_char_to_full_length

注意: 模式的的修该方式是替换.

查看模式2种方式:
show variable like '%mode';
select @@sql_mode;

修改模式的2种方式:
set session sql_mode=模式;
set global sql_mode=模式;