浏览目录

  • 创建（复制）

  • 删除

  • 修改

  • 查询

  • 存储引擎介绍

一、创建（复制）

1、语法：

1
2
3
4
5
CREATE TABLE 表名(
    字段名1 类型[(宽度) 约束条件],
    字段名2 类型[(宽度) 约束条件],
    字段名3 类型[(宽度) 约束条件]
)ENGINE=innodb DEFAULT CHARSET utf8;

2、创建示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create table student(
    id int not null auto_increment primary key,
    name varchar(50) not null,
    age int not null,
    sex enum('男','女') not null default '男',
    salary double(10,2) not null
)engine=innodb default charset=utf8;
 
not null :表示此列不能为空
auto_increment :表示自增长,默认每次增长+1
注意:自增长只能添加在主键或者唯一索引字段上
 
primary key :表示主键(唯一且不为空)
engine =innodb :表示指定当前表的存储引擎
default charset utf8 :设置表的默认编码集

3、主键

一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。

  • 单一列：
1
2
3
4
create table tb1(
    nid int not null auto_increment primary key,
    num int null
)
  • 多列组合主键：
1
2
3
4
5
create table tb1(
    nid int not null,
    num int not null,
    primary key(nid,num)
)

4、自增

自增，如果为某列设置自增列，插入数据时无需设置此列，默认将自增（表中只能有一个自增列）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
create table tb1(
    nid int not null auto_increment primary key,
    num int null
)
或
create table tb1(
    nid int not null auto_increment,
    num int null,
    index(nid)
)
注意：
     1、对于自增列，必须是索引（含主键）
     2、对于自增可以设置步长和起始值
    show session variables like 'auto_inc%';
    set session auto_increment_increment=2;
    set session auto_increment_offset=10;
 
    show global variables like 'auto_inc%';
    set global auto_increment_increment=2;
    set global auto_increment_offset=10;

5、复制表　　

  • 只复制表结构和表中数据 　　
1
2
create table tb2 select * from tb1;
ps:主键自增、索引、触发器、外键，不会被复制
  • 只复制表结构　
1
2
create table tb2 like tb1;
ps: 数据、触发器、外键，不会被复制

二、删除

  • 删除表(drop后的表自增字段不清空，继续计数)　　
1
drop table 表名;
  • 清空表(truncate后的表自增字段清空，重新开始计数) 
1
truncate table 表名;

三、修改

1、添加表字段　　

1
alter table 表名 add 字段名 类型 约束;

例如：

1
alter table student add age int not null default 0 after name;

ps: after name 表示在name字段后添加字段 age.

2、修改表字段 

1
2
3
4
方式一:
alter table student modify 字段 varchar(100) null;
方式二:
alter table student change 旧字段 新字段 int not null default 0;

ps:二者区别: 
change 可以改变字段名字和属性 
modify只能改变字段的属性　　

3、删除表字段 　

1
alter table student drop 字段名;

4、更新表名称

1
rename table 旧表名 to 新表名;

5、添加主键　　

1
alter table student add primary key(字段,"多个","间隔");

6、移除主键

1
alter table student drop primary key;

ps:如果主键为自增长,以上方式则不被允许执行,请先去掉主键自增长属性,然后再移除主键 

1
alter table student modify id int not null,drop primary key

7、添加外键　　

1
alter table 从表 add CONSTRAINT fk_test foreign key 从表(字段) REFERENCES 主表(字段);

8、移除外键　　

1
alter table 表 drop foreign key 外键名称;

ps:如果外键设置后想修改,那么只能是先删除,再添加

9、修改默认值　

1
alter table 表 alter 字段 set default 100;

10、删除默认值　　

1
alter table 表 alter 字段 drop default;

四、查询　

1、查询表数据　

1
select 字段(多个以","间隔) from 表名;

例如：　　

1
select name,sex from student;

或者：

1
select * from student;

2、查看表结构　

1
desc 表名;

　例：

1
desc student;

3、查看创建表信息　　　

1
show create table student;

五、存储引擎介绍　

介绍　

存储引擎 : 其实就是指定 表 如何存储数据，如何为存储的数据 建立索引 以及 如何更新，查询数据等技术实现的方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）

了解: 在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎

下表显示了各种存储引擎的特性：

特性 MyISAM InnoDB Memory Archive NDB
存储限制 没有 64TB 有 m没有 没有
事务   支持     支持
锁粒度 b表 行 表 行 页
B树索引 支持 支持 支持   支持
哈希索引   支持 z支持    
全文索引 支持        
集群索引   支持      
数据缓存   支持 支持    
索引缓存 支持 支持 支持    
数据压缩 支持     支持  
批量插入 高 相对低 高 非常高 高
内存消耗 d低 高 中 低 低
外键支持   支持      
复制支持 支持 支持 支持 支持 支持
查询缓存 支持 支持 支持 支持 支持
备份恢复 支持 支持 支持 支持 支持
集群支持         支持

其中最常见的两种存储引擎是MyISAM 和 InnoDB

MyISAM存储引擎

1、MyISAM 是MySQL (mysql 5.5版本以前) 原来的默认存储引擎.

2、MyISAM   这种存储引擎不支持事务，不支持行级锁，只支持并发插入的表锁。

3、MyISAM   类型的表支持三种不同的存储结构：静态型、动态型、压缩型。

　　（1）静态型：就是定义的表列的大小是固定（即不含有：xblob、xtext、varchar等长度可变的数据类型），这样mysql就会自动使用静态myisam格式。

        　　使用静态格式的表的性能比较高，因为在维护和访问的时候以预定格式存储数据时需要的开销很低。但是这高性能是用空间换来的，因为在定义的时候是固定的，所以不管列中的值有多大，都会以最大值为准，占据了整个空间。

　　（2）动态型：如果列（即使只有一列）定义为动态的（xblob, xtext, varchar等数据类型），这时myisam就自动使用动态型，虽然动态型的表占用了比静态型表较少的空间，但带来了性能的降低.

　　（3）压缩型：如果在这个数据库中创建的是在整个生命周期内只读的表，则这种情况就是用myisam的压缩型表来减少空间的占用。

　　　　压缩方式参考官方文档: https://dev.mysql.com/doc/refman/5.6/en/myisampack.html

4、MyISAM也是使用B+tree索引但是和Innodb的在具体实现上有些不同。

InnoDB存储引擎

（1）MySQL默认存储引擎(MySQL 5.5 版本后).

（2）innodb 支持事务，回滚以及系统崩溃修复能力和多版本迸发控制的事务的安全。

（3）innodb 支持自增长列（auto_increment）,自增长列的值不能为空，(一个表只允许存在一个自增,并且要求自增列必须为索引)

（4）innodb 支持外键（foreign key） ,外键所在的表称为子表,而所依赖的表称为父表。

（5）innodb存储引擎支持行级锁。

（6）innodb存储引擎索引使用的是B+Tree

补充3点：

    1.大容量的数据集时趋向于选择Innodb。因为它支持事务处理和故障的恢复。Innodb可以利用数据日志来进行数据的恢复。主键的查询在Innodb也是比较快的。

    2.大批量的插入语句时（这里是INSERT语句）在MyIASM引擎中执行的比较的快，但是UPDATE语句在Innodb下执行的会比较的快，尤其是在并发量大的时候。

    3.两种引擎所使用的索引数据结构是什么？

        答案:都是B+树!

　　　　MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。

　　　　Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。

Memory存储引擎

Memory存储引擎（之前称为Heap）将表中数据存放在内存中，如果数据库重启或崩溃，数据丢失，因此它非常适合存储临时数据。

Archive存储引擎

正如其名称所示，Archive非常适合存储归档数据，如日志信息。它只支持INSERT和SELECT操作，其设计的主要目的是提供高速的插入和压缩功能。

NDB存储引擎

NDB存储引擎是一个集群存储引擎，类似于Oracle的RAC，但它是Share Nothing(非共享)的架构，因此能提供更高级别的高可用性和可扩展性。NDB的特点是数据全部放在内存中，因此通过主键查找非常快。

关于NDB，有一个问题需要注意，它的连接(join)操作是在MySQL数据库层完成，不是在存储引擎层完成，这意味着，复杂的join操作需要巨大的网络开销，查询速度会很慢。

测试存储引擎

 创建三个表，分别使用innodb，myisam，memory 存储引擎，进行插入数据测试

1
2
3
4
5
6
7
8
9
10
create table t1(id int)engine=innodb;
create table t2(id int)engine=myisam;
create table t3(id int)engine=memory;
  
#看一下三个存储引擎创建的 表文件
 t1.frm  t1.ibd
 t2.MYD  t2.MYI  t2.frm
 t3.frm
#细心的同学会发现最后的存储引擎只有表结构，无数据
#memory，在重启mysql或者重启机器后，表内数据清空　　

重点（面试题）

innodb与MyIASM存储引擎的区别:
　　1.innodb 是mysql5.5版本以后的默认存储引擎, 而MyISAM是5.5版本以前的默认存储引擎.
　　2.innodb 支持事务,而MyISAM不支持事务
　　3.innodb 支持行级锁.而MyIASM 它支持的是并发的表级锁.
　　4.innodb 支持外键, 而MyIASM 不支持外键
　　5.innodb与MyIASM存储引擎都采用B+TREE存储数据, 但是innodb的索引与数据存储在一个文件中,这种方式我们称之为聚合索引. 
　　　　而MyIASM则会单独创建一个索引文件,也就是说,数据与索引是分离开的
　　6.在效率方面MyISAM比innodb高,但是在性能方面innodb要好一点.