DDL数据定义语言
新建表create
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型
);
// 快速创建表,即复制表(了解)
create table 要创建的表名 as select * from 要复制的表名;
// 把查询结果当作一张表新建(了解)
create table 要创建的表名 as select 字段1,字段2,... from 要复制的表名 where 条件;
删除表drop
drop table 表名; // 这张表不存在的时候语句会报错
drop table 表名 if exists; //如果这张表存在,则删除
修改表alter
在实际的开发中,需求一旦确定之后,表一旦设计好之后,很少进行表结构的修改。因为开发进行中的时候,修改表的结构,对应的java代码就需要进行大量的修改,成本是比较高的。
// 修改表名
alter table 旧表名 rename 新表名;
// 添加字段和数据类型到表结构最后一行
alter table 表名 add 字段名 数据类型;
// 修改字段的数据类型
alter table 表名 modify 字段名 新数据类型;
// 修改字段在表结构中的位置
alter table 表名 modify 字段名 数据类型 位置[first(放到第一行)、after 字段1(放到字段1下面一行)];
// 修改字段名
alter table 表名 change 旧字段名 新字段名 新数据类型;
// 删除表中字段
alter table 表名 drop 要删除的字段名;
// 删除主键约束
alter table 表名 drop primary key;
mysql中的数据类型
varchar(最长255) 可变长度的字符串
会根据实际的数据长度动态分配空间。
优点:节省空间
缺点:需要动态分配空间,速度慢。
char(最长255) 定长字符串
不管实际的数据长度是多少。
分配固定长度的空间去存储数据。
优点:不需要动态分配空间,速度快。
缺点:使用不当可能会导致空间的浪费。
int(最长11) 数字中的整数型。等同于java的int。
bigint 数字中的长整型。等同于java中的long。
float 单精度浮点型数据
double 双精度浮点型数据
date 短日期类型,只包括年月日信息
datetime 长日期类型,包括年月日时分秒信息
clob 字符大对象
最多可以存储4G的字符串。
比如:存储一篇文章,存储一个说明。
超过255个字符的都要采用CLOB字符大对象来存储。
Character Large OBject:CLOB
blob 二进制大对象
Binary Large OBject
专门用来存储图片、声音、视频等流媒体数据。
往BLOB类型的字段上插入数据的时候,例如插入一个图片、视频等,需要使用IO流才行。
约束constraint(重要)
非空约束:not null
唯一性约束: unique
主键约束: primary key (简称PK)
外键约束:foreign key(简称FK)
检查约束:check(mysql不支持,oracle支持)
非空约束:not null
非空约束not null约束的字段不能为NULL。
// 列级约束(可以多个)
create table 表名(
字段名1 数据类型 not null, // not null只有列级约束,没有表级约束!
字段名2 数据类型
);
insert into 表名(字段名1) values('')
// [Err] 1364 - Field '字段名1' doesn't have a default value
唯一性约束: unique
唯一性约束unique约束的字段不能重复,但是可以为NULL,NULL不算重复。
// 列级约束(可以多个)
// 字段1 唯一
create table 表名(
字段名1 数据类型 unique, // 约束直接添加到列后面的,叫做列级约束。
字段名2 数据类型
);
// 值1 = 值1,值2 != 值22
insert into t_vip(字段名1,字段名2) values(值1,值2);
insert into t_vip(字段名1,字段名2) values(值1,值22);
// [Err] 1062 - Duplicate entry '值1' for key '字段1'
insert into t_vip(字段名2) values(值2);
insert into t_vip(字段名2) values(值22);
// 这样没问题
// 表级约束,需要给多个字段联合起来添加某一个约束的时候使用
// 字段1、字段2 联合起来唯一
create table 表名(
字段名1 数据类型,
字段名2 数据类型
unique(字段名1,字段名2) // 约束没有添加在列的后面,这种约束被称为表级约束。
);
// 值1 = 值1,值2 != 值22
insert into t_vip(字段名1,字段名2) values(值1,值2);
insert into t_vip(字段名1,字段名2) values(值1,值22);
// 这样没问题
insert into t_vip(字段名1,字段名2) values(值1,值2);
insert into t_vip(字段名1,字段名2) values(值1,值2);
// [Err] 1062 - Duplicate entry '值1-值2' for key '字段名1'
主键约束: primary key (重点)
主键的特征:not null + unique(主键值不能是NULL,同时也不能重复!)
// 列级约束(只能一个)
// 一个字段做主键,叫做:单一主键
create table 表名(
字段名1 数据类型 primary key, // 列级约束
字段名2 数据类型
);
// 值1 = 值1,值2 != 值22
insert into t_vip(字段名1,字段名2) values(值1,值2);
insert into t_vip(字段名1,字段名2) values(值1,值22);
// [Err] 1062 - Duplicate entry '值1' for key 'PRIMARY'
// 一张表主键约束只能有一个
create table 表名(
字段名1 数据类型 primary key, // 列级约束
字段名2 数据类型 primary key
);
// [Err] 1068 - Multiple primary key defined
// 表级约束
// 两个字段联合起来做主键,叫做:复合主键(不建议使用)
create table 表名(
字段名1 数据类型, // 列级约束
字段名2 数据类型,
primary key(字段名1,字段名2)
);
insert into t_vip(字段名1,字段名2) values(值1,值2);
insert into t_vip(字段名1,字段名2) values(值1,值2);
// [Err] 1062 - Duplicate entry '值1-值2' for key 'PRIMARY'
主键值建议使用:int、bigint、char等类型,不建议使用varchar(可变长度),因为主键值一般都是数字(定长)。
主键除了单一主键和复合主键之外,还可以这样进行分类:
自然主键:主键值是一个自然数,和业务没关系。
业务主键:主键值和业务紧密关联。(不建议使用)
// 主键自增
create table 表名(
字段名1 数据类型 primary key auto_increment, //从1开始自增
字段名2 数据类型
);
外键约束:foreign key(重点)
外键值可以为NULL。
create table 表1( // 父表
字段名1 数据类型 primary key,
字段名2 数据类型
);
create table 表2( // 子表
字段名4 数据类型 primary key,
字段名5 数据类型,
字段名6 数据类型,
foreign key(字段名6) references 表1(字段名1)
);
父表:主键是子表的外键
子表:外键是父表的主键
创建表的顺序?先创建父,再创建子。
删除表的顺序?先删子,再删父。
插入数据的顺序?先插入父,再插入子。
删除数据的顺序?先删子,再删父。
子表中的外键引用的父表中的某个字段,被引用的这个字段不一定必须是主键,但至少要具有unique约束。
存储引擎(了解)
mysql支持的九大存储引擎:
- FEDERATED
- MRG_MYISAM
- MyISAM
- BLACKHOLE
- CSV
- MEMORY
- ARCHIVE
- InnoDB
- PERFORMANCE_SCHEMA
// 查看mysql支持的存储引擎
show engines \G
// 完整建表语句
create table 表名(
字段名1 数据类型 primary key auto_increment, //主键自增
字段名2 数据类型
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
MyISAM存储引擎
它管理的表具有以下特征:
使用三个文件表示每个表:
格式文件 — 存储表结构的定义(mytable.frm)
数据文件 — 存储表行的内容(mytable.MYD)
索引文件 — 存储表上索引(mytable.MYI)
优点:可被转换为压缩、只读表来节省空间
缺点:MyISAM不支持事务机制,安全性低。
InnoDB存储引擎
这是mysql默认
的存储引擎,同时也是一个重量级的存储引擎。
InnoDB支持事务,支持数据库崩溃后自动恢复机制。
InnoDB存储引擎最主要的特点是:非常安全。
它管理的表具有下列主要特征:
-
每个 InnoDB 表在数据库目录中以.frm 格式文件表示
-
InnoDB 表空间 tablespace 被用于存储表的内容(表空间是一个逻辑名称。表空间存储数据+索引。)
-
提供一组用来记录事务性活动的日志文件
-
用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
-
提供全 ACID 兼容
-
在 MySQL 服务器崩溃后提供自动恢复
-
多版本(MVCC)和行级锁定
-
支持外键及引用的完整性,包括级联删除和更新
InnoDB最大的特点就是支持事务:
以保证数据的安全。效率不是很高,并且也不能压缩,不能转换为只读,
不能很好的节省存储空间。
MEMORY存储引擎
使用 MEMORY 存储引擎的表,其数据存储在内存中,且行的长度固定,
这两个特点使得 MEMORY 存储引擎非常快。
MEMORY 存储引擎管理的表具有下列特征:
- 在数据库目录内,每个表均以.frm 格式的文件表示。
- 表数据及索引被存储在内存中(目的就是查询快)。
- 表级锁机制。
- 不能包含 TEXT 或 BLOB 字段。
MEMORY 存储引擎以前被称为HEAP 引擎。
MEMORY引擎优点:查询效率是最高的。不需要和硬盘交互。
MEMORY引擎缺点:不安全,关机之后数据消失。因为数据和索引都是在内存当中。