MySQL之DML操作

表操作

数据类型

常用类型

• 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) 
        

• 类型	数值类型	说明
  数值类型	bit（M）	位类型；M 指定位数，范围：1 ~ 64，默认为 1
  整数类型	tinyint[unsigned]（1字节）	带符号位范围-128 ~ 127，无符号 0~255，默认有符号
  	smallint[unsigned]（2字节）	带符号位范围 - 2^15~ 2^15 - 1，无符号 0 ~ 2^16 -1，默认有符号
  	mediumint[unsigned]（3字节）	带符号位范围 - 2^23~ 2^23 - 1，无符号 0 ~ 2^24 -1，默认有符号
  	int[unsigned]（4字节）	整型；带符号位范围 - 2^31~ 2^31 - 1，无符号 0 ~ 2^32 -1，默认有符号
  	bigint[unsigned]（8字节）	长整型；带符号位范围 - 2^63~ 2^63 - 1，无符号 0 ~ 2^64 -1，默认有符号
  小数类型	float[unsigned]	4个字节，单精度
  	double[unsigned]	比float精度更大的小数，双精度，8字节
  	decimal(M, D) [unsigned]	定点数：M 指定长度，D表示小数点位数
  字符串	char(size)：char(20)	固定长度字符串，0 ~ 255
  字符串	varchar(size)：varchar(20)	可变长字符串，0 ~ 2^16-1
  文本	tinytext	短文本类型，0 ~ 2^8 - 1（256B）
  	text	文本类型，0 ~ 2^16 - 1（64K）
  	mediumtext	中等文本类型，0 ~ 2^24 - 1（16M）
  	longtext	长文本类型，0 ~ 2^32 - 1（4G）
  	clob	字符大对象，存储较大文本
  二进制	blob	二进制大对象；0 ~ 2^16-1；存储图片、视频等流媒体信息
  	longblob	0 ~ 2^32 -1
  时间日期	data	日期类型（YYYY-MM-DD），3字节
  	year	年，1字节
  	time	时间类型（HH:mm:ss），3字节
  	datetime	日期时间类型（YYYY-MM-DD HH:mm:ss），8字节
  	TimeStamp	时间戳，自动记录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^²⁴ - 1
    • longtext 0 ~ 2^³² - 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 ... ;
    

    1. 表名较长使用 _ 连接
    2. 每个字段定义之间用 , 隔开；最后一句不加
    3. comment：字段注释
    4. mediumint(8)：数据类型，长度最大 8 位
    5. unsigned：数值类型无符号，无正负
    6. default：默认值，不添加数据时默认使用
    7. not unll：非空约束，该字段不允许为空
    8. engine：存储引擎，决定表的格式
    9. charset：字符集

约束

• 列级约束
  • 约束加在指定字段之后
  • not null：不允许为空
  • primary key：主键，不允许为空，不允许重复
  • unique：唯一约束，不允许重复
• 表级约束
  • 约束定义在所有字段之后
  • 可定义联合约束
  • primary key
  • foreign key
  • unique

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`

去重

1. 创建表存放 distinct 去重后的数据
   • 再改名为原表名
2. 使用约束不允许重复数据

-- 将查询结果创建为一张表
create table newEmp [as] select * from emp;

-- 创建表使用约束不允许字段数据重复
create table demo (
    name varchar(12) primary key,		# 主键约束，不允许重复、不允许为空
    age int unique,						# 唯一约束，不允许重复
    sex char(1) not null default '男'    # 非空约束，不允许为空；默认为 男
)