mysql数据库常规操作2
对列表的增删改
# 添加表列 alter table 表名 add 新列名 类型（长度）[约束]; #修改表列 alter table 表名 change 旧列名 新列名 类型（长度）[约束]; #修改表删除列 alter table 表名 drop 列名; #重命名表名 rename table 库名.表名 to 库名.新表名; #增加表内数据 #一次增加一条 insert into 表名（字段1,字段2,字段3...） valus(字段1,字段2,字段3...); #一次增加多条 insert into 表名（字段1,字段2,字段3...） valus(字段1,字段2,字段3...); valus(字段1,字段2,字段3...); valus(字段1,字段2,字段3...); .... /* 添加字段必须和表内类型一样，数据大小必须在表内的字段范围内 插入空值可以不写 */ #修改表内数据 #更新所有数据的指定字段 update 表名 set 需要修改的字段名1=修改后的值1, 需要修改的字段名2=修改后的值2, ...; #更新符合条件的指定字段 update 表名 set 需要修改的字段名1=修改后的值1, 需要修改的字段名2=修改后的值2, ... where 条件; #删数据 drop from 表名 [约束条件] #底层机制是一条条删除表内数据 truncate table 表名 #底层机制是把整个表删除后重新建表对比delete 和 truncate的区别
| 对比维度 | DELETE FROM xxxxtable | TRUNCATE TABLE xxxxtable |
|---|---|---|
| 所属 SQL 分类 | 属于DML（数据操作语言，数据增删改语句） | 属于DDL（数据定义语言，库表结构增删改语句） |
| 执行机制 | 逐条删除表中的数据记录，逐行执行删除操作 | 先删除整张表，再重新创建一张结构相同的空表，执行效率更高 |
| 自增列处理 | 不会清空自增列的增长标记，后续插入数据会继续从之前的计数开始增长 | 重置自增列的值为默认初始值（如从 1 重新开始） |
| 事务支持 | 支持事务管理，可以通过ROLLBACK回滚删除操作 | 不支持事务管理，执行后无法回滚，操作不可逆 |
| 执行效率 | 大数据量场景下执行较慢（逐行扫描 + 删除） | 执行速度极快（直接重建表结构） |
| 触发机制 | 会触发表上的DELETE类型触发器 | 不会触发任何触发器 |
| 权限要求 | 需要表的DELETE权限 | 需要表的ALTER或更高权限（如DROP） |
反引号可以强制关键字作为变量名进行运行
约束关键字
| 约束名称 | 关键字 | 核心含义 | 典型使用场景 |
|---|---|---|---|
| 非空约束 | NOT NULL | 字段值不能为 NULL，插入或更新时必须赋值 | 用户名、手机号、商品名称 |
| 唯一约束 | UNIQUE | 字段值在整张表中唯一（可多个 NULL），不能重复 | 邮箱、身份证号、订单编号 |
| 主键约束 | PRIMARY KEY | 非空 + 唯一，一张表只能有一个主键，用于唯一标识一行数据 | 自增 id（id INT PRIMARY KEY AUTO_INCREMENT） |
| 外键约束 | FOREIGN KEY | 关联另一张表的主键，保证数据的引用完整性（子表值必须在主表中存在） | 订单表的user_id关联用户表id |
| 默认值约束 | DEFAULT | 插入数据时未指定该字段值，自动填充默认值 | 订单状态（DEFAULT 0）、创建时间（DEFAULT CURRENT_TIMESTAMP） |
| 检查约束 | CHECK | 限制字段值的范围 / 规则（MySQL8.0 + 正式支持） | 年龄（CHECK (age>0 AND age<150)）、金额（CHECK (amount>=0)） |
| 自增约束 | AUTO_INCREMENT | 仅用于整数主键，插入数据时自动生成唯一递增数值（从 1 开始） | 主键 id（id INT AUTO_INCREMENT） |
comment的作用
是否有双引号
主键约束
#设置主键方式1： create table 表名( 字段1 类型 primary key, 字段2 类型, .... ); # 设置主键方式2： create table 表名( 字段1 类型 , 字段2 类型, .... primary key(字段1) ); # 设置联合主键： create table 表名( 字段1 类型 , 字段2 类型, .... primary key(字段1,字段2，...) ); # 删除主键约束 alter table 表名 drop primary key; /* 删除联合主键时，需注意删除后,字段会保留非空属性 */ #删除联合主键，假设字段1和字段2为联合主键，现在改为字段1为主键 alter table 表名 drop primary key; alter table 表名 add primary key(字段1); alter table 表名 change 字段2 字段2 类型;自增约束
alter table 表名 auto_increment=100;#下一条自增从100开始 set @@auto_increment_increment = 2; #步长改为2 # 方式一：建表时 create table 表名( 字段1 类型 primary key auto_increment, -- 主键 + 自增 字段2 类型, 字段3 类型, .... ); # 方式2：建表后 create table 表名( 字段1 类型 primary key, 字段2 类型, .... ); alter table 表名 modify 字段1 类型 auto_increment;非空约束
create table 表名( 字段1 类型 not null, -- 该字段不能为空 字段2 类型 not null, -- 该字段不能为空 字段3 类型, -- 该字段可以为空 .... );唯一约束
#方式一:建表时 create table 表名( 字段1 类型 unique, -- 该字段值必须唯一 字段2 类型, 字段3 类型 unique, -- 多个字段可分别设置唯一 .... ); #方式2:越级约束 create table 表名( 字段1 类型, 字段2 类型, 字段3 类型, .... unique(字段1), -- 单字段唯一 unique(字段2, 字段3) -- 多字段组合唯一（联合唯一） ); # 方式3：建表后添加唯一约束 # 添加单字段唯一 alter table 表名 add unique(字段名); # 添加多字段联合唯一 alter table 表名 add unique(字段1, 字段2); # 删除唯一约束（先查索引名，再删除） alter table 表名 drop index 索引名;默认值约束
-- 方式1：字段级约束（创建表时直接指定） create table 表名( 字段1 类型 default 默认值, -- 该字段设置默认值 字段2 类型 default 默认值, 字段3 类型, -- 无默认值 .... ); -- 方式2：建表后修改添加默认值 -- 添加/修改字段的默认值 alter table 表名 modify 字段名 类型 default 默认值; -- 或 alter table 表名 alter 字段名 set default 默认值; -- 方式3：删除默认值 -- 删除字段的默认值 alter table 表名 alter 字段名 drop default;