• MySQL之DML操作

    文章目录

    表操作

    数据类型
    常用类型

    • MySQL常用数据类型

      • 创建表时需要指定表中的字段，字段需要指定数据类型

        • create table demo (
	name varchar(12) PRIMARY key,		# varchar 类型，长度 12
)

          • 1
          • 2
          • 3
          • 满足需求的情况下尽量使用占用空间较小的类型

        • unsigned：无符号类型，不分正负

          • 默认为有符号数据类型；例

            # 有符号int类型
create table db_1(id int(1)) 
# 无符号int类型
create table db_2(id int(1) unsigned) 

            • 1
            • 2
            • 3
            • 4
    • 类型数值类型说明
      数值类型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；存储图片、视频等流媒体信息
      longblob0 ~ 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

        • 1
        • 2

    • 不常使用

    小数
    • 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
        • longtext 0 ~ 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，默认为操作时间

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
    创建表

    • 格式：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
        • 4
        • 5
        • 6
        • 7
        • 8
        • 9
        • 10
        • 11
        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 设为复合主键， 两列都不为空且不能同时相同 
)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    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中相关记录才能删除该记录
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    unique

    • 唯一性约束，不允许重复

      • 在没有非空约束时，可以存放 null
        • 可存放多个 null
        • null 不被当作具体值

    • 字段指定 unique not null 约束则效果类似于主键约束

    • 同张表可以有多个 unique 约束

    • 可以将字段联合约束

      • 只能在所有字段之后定义
    -- 唯一约束，不允许重复，没有非空约束时可以为null
字段名 字段类型 unique  
-- 联合约束
create table demo(
    name varchar(12),
    age int,
    unique (name, age)			-- 不允许 name age 字段同时相同
)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    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
)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    表结构操作
    查看
    -- 查看 表结构（表中所有的列及数据类型等）
desc tablename; 

-- 显示建表语句
show create table `table_name`;

    • 1
    • 2
    • 3
    • 4
    • 5
    删除
    • 删除表：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 字符集;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      • 15
      • 16
      • 17
      • 18
      • 19
      • 20
      • 21
      • 22
      • 23
      • 24
      • 25
      • 26
      • 27
      • 28
      • 29
      • 30
    添加数据
    • 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();

    • 1
    • 2
    • 3
    • 4
    删除数据
    • delete
      • delete from ... where ...
        • 没有 where 语句表示删除表中所有数据
      • 删除效率较低
        • 未释放数据真实存储空间
        • 可以回滚
    • truncate （慎重使用）
      • truncate table table_name
      • 截断表，删除效率高
        • 删除大表使用，只保留表头信息
      • 不可回滚，永久丢失
    -- delete：删除数据
delete from `table_name` where 限定条件

-- 删除名字为张三的人的所有记录
delete from `user` where name = '张三'

    • 1
    • 2
    • 3
    • 4
    • 5
    修改数据
    • update ... set ... where ...
      • set：指定要修改的字段值
      • 格式：set 字段名 = 数据, 字段名 = 数据 ... where ...
    • 无 where 字句修改全部记录
    -- update：修改数据
update `table_name` set 修改内容 where 限定条件;

-- 将名字为 张三 的人薪水改为50，名字改为 李四
update `user` set salary = 50, name = '李四' where name = '张三'

    • 1
    • 2
    • 3
    • 4
    • 5
    复制
    -- 将 emp 表的结构复制给表 emp1，不含数据
create table emp1 like emp

-- 将从 emp 表查询的结果插入表 emp1：两张表结构必须相同，否则无法插入数据
insert into emp1 select * from emp

    • 1
    • 2
    • 3
    • 4
    • 5
    蠕虫复制
    • 测试sql语句效率需要海量数据
      • 使用自复制翻倍添加记录
    -- 查询表自身的所有记录并添加到表自身
insert into `table_name` select * from `table_name`

    • 1
    • 2
    去重
    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 '男'    # 非空约束，不允许为空；默认为 男
)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
  • 相关阅读:
    基于WPSOffice+Pywpsrpc构建Docker镜像，实现文档转换和在线预览服务
    02-线性结构3 Reversing Linked List
    Python数据攻略-Pandas进行CSV和Excel文件读写
    LQ0209 颠倒的价牌【枚举+进制】
    新时代火热技术栈：大数据-＞人工智能（AI）-＞区块链
    AM驱动架构—优质Mini-LED显示技术解决方案
    uni-app：多方法实现两个view在同一行展示
    Zookeeper1：相关理论
    【系统设计】邻近服务
    SAP UI5 FileUploader 控件实现本地文件上传，接收服务器端的响应时遇到跨域访问错误的试读版
  • 原文地址：https://blog.csdn.net/qq_66991094/article/details/126573716