文章目录
- 表操作
- 数据类型
- 常用类型
- bit(M)
- 小数
- 字符串、文本
- CLOB、BLOB
- 日期类型
- 创建表
- 约束
- primary key
- foreign key
- unique
- not null
- check
- auto_increment
- 表结构操作
- 查看
- 删除
- 修改
- 添加数据
- 删除数据
- 修改数据
- 复制
- 蠕虫复制
- 去重
表操作
数据类型
常用类型
- MySQL常用数据类型
- 创建表时需要指定表中的字段，字段需要指定数据类型
create table demo (
name varchar(12) PRIMARY key, # varchar 类型，长度 12
)- 满足需求的情况下尽量使用占用空间较小的类型
unsigned：无符号类型，不分正负
- 默认为有符号数据类型；例
# 有符号int类型
create table db_1(id int(1))
# 无符号int类型
create table db_2(id int(1) unsigned)类型 | 数值类型 | 说明 |
数值类型 |
| 位类型；M 指定位数，范围：1 ~ 64，默认为 1 |
整数类型 |
| 带符号位范围-128 ~ 127，无符号 0~255，默认有符号 |
| 带符号位范围 - 2^15~ 2^15 - 1，无符号 0 ~ 2^16 -1，默认有符号 | |
| 带符号位范围 - 2^23~ 2^23 - 1，无符号 0 ~ 2^24 -1，默认有符号 | |
| 整型；带符号位范围 - 2^31~ 2^31 - 1，无符号 0 ~ 2^32 -1，默认有符号 | |
| 长整型；带符号位范围 - 2^63~ 2^63 - 1，无符号 0 ~ 2^64 -1，默认有符号 | |
小数类型 |
| 4个字节，单精度 |
| 比float精度更大的小数，双精度，8字节 | |
| 定点数：M 指定长度，D表示小数点位数 | |
字符串 |
| 固定长度字符串，0 ~ 255 |
字符串 |
| 可变长字符串，0 ~ 2^16-1 |
文本 |
| 短文本类型，0 ~ 2^8 - 1（256B） |
| 文本类型，0 ~ 2^16 - 1（64K） | |
| 中等文本类型，0 ~ 2^24 - 1（16M） | |
| 长文本类型，0 ~ 2^32 - 1（4G） | |
| 字符大对象，存储较大文本 | |
二进制 |
| 二进制大对象；0 ~ 2^16-1；存储图片、视频等流媒体信息 |
| 0 ~ 2^32 -1 | |
时间日期 |
| 日期类型（ |
| 年，1字节 | |
| 时间类型（ | |
| 日期时间类型（ | |
| 时间戳，自动记录insert、update操作的时间；4字节 |
bit(M)
bit字段显示的时候按照位的方式，即二进制
- 查询时可按照数值查询
- 若只有 0、1 的值可使用 bit(1) 节约空间
- M：指定位数，默认为 1，范围 1 ~ 64
create table db_1(num bit(8))
-- 表示 bit 类型 8 位，即一个字节，范围 0 ~ 255- 不常使用
小数
-
float：单精度 -
double：双精度 decimal[M, D] [unsigned]
- 支持更加精确的小数位
M：总位数，D：小数点后位数
- D 为 0 时无小数点后部分
- M 最大 65，默认 10
- D 最大 30，默认 0
- 需要高精度小数时推荐使用
字符串、文本
char(size)： 0 ~ 255 字符
- 定长：
char占用空间是固定的
- 插入的字符未达到指定的大小同样占用分配的空间
- 例如：char(4)，即使插入 ‘aa’ 同样占用四个字符空间
- 很可能造成空间浪费
varachar(size)
- 可变长，最大65532字节，留有 1 ~ 3 字节记录大小
- utf8 编码最大 21844 字符
- 变长：根据实际占用空间分配占用空间
- 实际占用空间 = 内容占用 + 本身占用
- 本身占用 1~3 字节记录内容长度
- 例如：varchar(4)
- 插入 ‘aa’ 不占用四个字符，而是 2 +（1 ~ 3字节）不定
size：指定字符数
- 以所在表编码格式确定一个字符占几个字节
- 查询速度：char > varchar
text：存放文本时使用，可视为varchar，但不能有默认值
- 存放更多数据：
-
mediumtext： 0 ~ 2^24 - 1 -
longtext0 ~ 2^32 - 1
CLOB、BLOB
CLOB：文本大对象
- Character Large Object
- 存储图片、视频等二进制流对象
- 必须通过 Java IO 流插入数据
BLOB：字符大对象
- Binary Large Object
- 存储文本文件等字符对象
- 必须通过 Java IO 流插入数据
很少使用
- 很少会直接将文件存到数据库
- 可将文件存到硬盘或服务器，将文件路径存到表中
日期类型
date、datetime、timestamp
create table `time`(
a date, # 年月日
b DATETIME, # 年月日 时分秒
c timestamp # 时间戳
not null # 不允许为空
default current_timestamp # 默认当前时间
on update current_timestamp # 更新该行数据时自动更新为当前时间
)
insert into time(a,b) values('2021-10-01','2022-10-07 15:30:30') # 不指定时间戳默认为当前时间
-- 表中数据：
# 2021-10-01 -- date
# 2022-10-07 15:30:30 -- datetime
# 2021-11-26 16:00:56 -- timestamp，默认为操作时间创建表
- 格式：
create table table_name (字段名 数据类型)
- 字段名 和 数据类型 必须定义，其他可默认
CREATE TABLE `table_name` (
`empno` mediumint(8) unsigned NOT NULL DEFAULT '0' comment '员工编号',
`ename` varchar(20) NOT NULL DEFAULT '',
`mgr` mediumint(8) unsigned DEFAULT NULL,
`hiredate` date NOT NULL,
`comm` decimal(7,2) DEFAULT 0.00,
`deptno` mediumint(8) unsigned NOT NULL DEFAULT '0'
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; # 存储引擎类型 InnoDB，默认字符集 utf8mp4
-- 将查询结果创建为表
create table `table_name` as select ... ;- 表名较长使用
_连接 - 每个字段定义之间用
,隔开；最后一句不加 -
comment：字段注释 -
mediumint(8)：数据类型，长度最大 8 位 -
unsigned：数值类型无符号，无正负 -
default：默认值，不添加数据时默认使用 -
not unll：非空约束，该字段不允许为空 -
engine：存储引擎，决定表的格式 -
charset：字符集
约束
- 列级约束
- 约束加在指定字段之后
-
not null：不允许为空 -
primary key：主键，不允许为空，不允许重复 -
unique：唯一约束，不允许重复
- 表级约束
- 约束定义在所有字段之后
- 可定义联合约束
primary keyforeign keyunique
primary key
- 主键约束
- 按字段数
- 单一主键
- 复合主键
- 多字段联合主键
- 按功能
- 自然主键
- 无关业务的自然数做主键
- 业务主键
- 使用业务数据做主键
- 不建议使用，后续维护性较弱
- 主键唯一，标识 唯一的非空数据
- 定义约束后该字段不允许重复，且不能为空
- 同一张表只能有一个主键
- 可以是复合主键，不建议使用
- 可以写在指定字段后
- 或在所有字段之后定义
- 每张表通常都会有主键约束作为唯一标识
-- 主键约束，唯一且不为空；指定在字段后
字段名 字段类型 primary key
-- 复合主键；定义在所有字段之后（也可指定单列主键）
create table tab1(
id int,
`name` varchar(10),
primary key(id,`name`) -- id 、 name 设为复合主键， 两列都不为空且不能同时相同
)foreign key
- 外键约束
- 定义主表和从表之间的关系
- 在所有字段之后定义
- 外键定义在从表
- 被引用字段必须具有唯一性
- 外键字段类型必须和被引用字段类型一致
- 如果要删除主表字段记录
- 必须确保从表中没有该记录的外键关联
- 否则无法删除
- 存储引擎为
innodb才支持外键
主从表外键关联字段 操作顺序要求
- 删除数据：先删除从表数据，才能删除主表数据
- 添加数据：先添加主表数据，才能添加从表数据
- 创建表：先有主表，才有从表
- 删除表：先删从表，再删主表
create table tab1(
id int primary key, -- 主表中定义主键
`name` varchar(10) not null default '' -- 不允许为空，默认值为''
)
# 外键约束
create table tab2(
id int,
`name` varchar(10),
class_id int,
foreign key(class_id) references tab1(id) -- class_id 字段外键连接 ab1 表中的 id 字段
)
/*
外键约束成功后，添加在class_id字段的数据必须在id字段中存在，或者添加 null
若id字段中有记录在class_id字段使用，必须取消外键或删除class_id中相关记录才能删除该记录
*/unique
- 唯一性约束，不允许重复
- 在没有非空约束时，可以存放
null
- 可存放多个
null -
null不被当作具体值
- 字段指定
unique not null约束则效果类似于主键约束 - 同张表可以有多个
unique约束 - 可以将字段联合约束
- 只能在所有字段之后定义
-- 唯一约束，不允许重复，没有非空约束时可以为null
字段名 字段类型 unique
-- 联合约束
create table demo(
name varchar(12),
age int,
unique (name, age) -- 不允许 name age 字段同时相同
)not null
非空约束，该字段不允许为 null
check
- 用于强制行数据必须满足的条件
- oracle 和 sql server 均支持 check
- mysql5.7 目前不支持check，只做语法校验，并不生效
列名 数据类型 check(check条件)
-- 示例 check
create table tab(
id int primary key, -- 主键约束
`name` varchar(32) not null, -- 该字段不允许为空
sex char(3) check(sex in ('男',"女")), -- 检查性别只能为 男 或 女
salary double check(salary > 2000) -- 检查工资必须大于2000
)auto_increment
- 自增长，一般自增长和主键配合使用
- 单独使用需要配和 unique
- Oracle 自增机制：Sequence，序列
- 修饰整型数据的字段
- 可以修饰小数但很少使用
- 自增长默认从1开始
- 可以修改
- 指定插入数据
- 之后数据从此数据开始增长
- 一般不会指定插入
字段名 整型 primary key auto increment -- 基本语法
-- 示例自增长
create table tab(
-- 主键约束，自增长
id int primary key auto_increment,
-- 该字段不允许为空
`name` varchar(32) not null,
)
-- 插入数据 null 或 不给数据 则按上条记录值自增长 + 1
-- 插入 id = 1
insert into tab values(null,'张三');
-- 插入 id = 5
insert into tab values(5,'张三');
-- 插入 id = 6
insert into tab values(null,'张三')
-- 修改自增长默认值从100开始
alter table tab auto_increment = 100表结构操作
查看
-- 查看 表结构（表中所有的列及数据类型等）
desc tablename;
-- 显示建表语句
show create table `table_name`;删除
- 删除表：
drop table table_name;
修改
alter table语句：修改表结构
- 追加、修改、删除列
- 正常开发很少修改表结构
- 修改是对前期设计的否定
- 修改结构的代码不会出现在 Java 程序中
- 修改表结构常用的语法格式：
ALTER TABLE <表名> [修改选项]
- 添加字段：
add
- 多个添加可写到一个括号或
,分隔
- 修改
-
rename：修改表名 change：修改字段名
- 新字段名需要完整定义：至少字段名+数据类型
-
modify：修改字段类型、约束 - 多个修改
,分隔
- 删除字段：
drop
- 多个删除
,分隔
# 添加字段
-- 指定位置添加字段：first-首位，after-指定字段后
-- 单个字段
alter table demo
add `info` varchar(50) not null default '' after `age`;
-- 多个字段
alter table demo
add `info` varchar(50) not null after age,
add `hobby` varchar(12) default '' first
-- 多个字段，追加在最后；不可添加约束，不能指定位置
alter table demo add(`hobby` varchar(1), `info` varchar(50));
# 修改字段数据类型或大小、约束条件
alter table demo
modify`hobby` char(6),
modify`info` varchar(20);
# 修改字段名
alter table tablename
change `info` `infomation` varchar(32)
not null default '';
# 修改表名为 newTableName
alter table tablename Rename table tableName to newTableName
# 删除字段 info、hobby
alter table demo drop `info`, drop `hobby`;
# 修改表的字符集
alter table tablename character set 字符集;添加数据
insert into ... values( ... )- 根据指定字段添加数据
insert into 表名(字段, ...) values (数据, ...);
- 字段与数据相互对应
- 未指定字段添加默值，无默认值插入 null
- 全部字段都添加
insert into 表名 (全部字段) values(数据);
- 字段顺序与数据顺序对应
insert into 表名 values(数据);
- 按照表中字段定义顺序添加数据
- 默认插入全部字段
- 同时添加多条记录
inset into 表名 values(), () .... ;- 插入数据顺序跟字段顺序匹配且数据类型匹配
- 字段允许时可以插入 null
- 某字段不赋值时插入默认值，无默认值插入 null
- 插入执行成功后，表中必然多一条记录
-- 根据指定字段添加数据
insert into `table_name`(`col1`, `col2`) values ('data1', 'data2');
-- 全部字段添加数据
insert into `table_name` values();删除数据
delete
delete from ... where ...
- 没有
where语句表示删除表中所有数据
- 删除效率较低
- 未释放数据真实存储空间
- 可以回滚
truncate（慎重使用）
truncate table table_name- 截断表，删除效率高
- 删除大表使用，只保留表头信息
- 不可回滚，永久丢失
-- delete：删除数据
delete from `table_name` where 限定条件
-- 删除名字为张三的人的所有记录
delete from `user` where name = '张三'修改数据
update ... set ... where ...
-
set：指定要修改的字段值 - 格式：
set 字段名 = 数据, 字段名 = 数据 ... where ...
- 无
where字句修改全部记录
-- update：修改数据
update `table_name` set 修改内容 where 限定条件;
-- 将名字为 张三 的人薪水改为50，名字改为 李四
update `user` set salary = 50, name = '李四' where name = '张三'复制
-- 将 emp 表的结构复制给表 emp1，不含数据
create table emp1 like emp
-- 将从 emp 表查询的结果插入表 emp1：两张表结构必须相同，否则无法插入数据
insert into emp1 select * from emp蠕虫复制
- 测试sql语句效率需要海量数据
- 使用自复制翻倍添加记录
-- 查询表自身的所有记录并添加到表自身
insert into `table_name` select * from `table_name`去重
- 创建表存放
distinct去重后的数据
- 再改名为原表名
- 使用约束不允许重复数据
-- 将查询结果创建为一张表
create table newEmp [as] select * from emp;
-- 创建表使用约束不允许字段数据重复
create table demo (
name varchar(12) primary key, # 主键约束，不允许重复、不允许为空
age int unique, # 唯一约束，不允许重复
sex char(1) not null default '男' # 非空约束，不允许为空；默认为 男
)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
