MySQL基础之表结构操作
编辑
995
2019-06-15
表结构操作
表结构操作
添加字段和删除字段
-
添加字段语法:
ALTER TABLE table_name ADD 字段名称 字段属性
+[完整性约束条件]
+[FIRST|AFTER 字段名称]
-
删除字段语法
ALTER TABLE tb_name DROP [COLUMN] 字段名称
注意同一张表的多个字段 操作之间用
,
分割
添加默认值和删除默认值
- 添加默认值
ALTER TABLE tb_name ALTER 字段名称 SET DEFAULT 默认值;
- 删除默认值
ALTER TABLE tb_name ALTER 字段名称 DROP DEFAULT;
USE db_imist;
CREATE TABLE IF NOT EXISTS user1(
id INT UNSIGNED AUTO_INCREMENT KEY
);
-- 测试添加删除字段
-- 添加 username字段
ALTER TABLE user1 ADD username VARCHAR(20);
-- 添加password字段 ,默认按顺序添加
ALTER TABLE user1 ADD password CHAR(32) NOT NULL;
-- 添加邮箱字段,添加到username之后 FFTER关键字
ALTER TABLE user1 ADD email VARCHAR(50) NOT NULL UNIQUE AFTER username;
-- 添加 测试字段 添加到首位 FIRST关键字
ALTER TABLE user1 ADD test TINYINT(1) NOT NULL DEFAULT 0 FIRST ;
-- 删除test 字段
ALTER TABLE user1 DROP test;
-- 批量操作 删除添加多个字段 ,选中user1表添加age addr字段,删除email字段
ALTER TABLE user1
ADD age1 TINYINT UNSIGNED NOT NULL DEFAULT 18,
ADD addr VARCHAR(100) NOT NULL DEFAULT '北京',
DROP COLUMN addr;
DESC user1;
-- 测试添加删除默认值
CREATE TABLE user2(
id INT UNSIGNED AUTO_INCREMENT KEY ,
username VARCHAR(20) NOT NULL ,
age TINYINT UNSIGNED NOT NULL DEFAULT 18,
email VARCHAR(50) NOT NULL
);
-- 给email 字段添加默认值
ALTER TABLE user2
ALTER email SET DEFAULT 'iMist@qq.com';
-- 删除age字段的默认值
ALTER TABLE user2
ALTER age DROP DEFAULT ;
### 修改字段类型,字段属性,字段顺序
-
ALTER TABLE tb_name MODIFY 字段名称 字段类型[字段属性][FIRST|AFTER 字段名称]
修改字段名称,字段类型,字段属性,字段顺序
-
ALTER TABLE tb_name CHANGE 原字段名称 新字段名称 字段类型 字段属性 [FIRST|AFTER 字段名称]
-- 测试修改字段类型和字段属性
CREATE TABLE user3(
id INT UNSIGNED AUTO_INCREMENT KEY ,
username VARCHAR(20) NOT NULL UNIQUE,
password VARCHAR(32) NOT NULL ,
email VARCHAR(50) NOT NULL
);
-- 将用户名字段名类型字节长度改为20,相当于将该表中的字段重新定义 以MODIFY修饰
ALTER TABLE user3
MODIFY username VARCHAR(21) NOT NULL UNIQUE ;
-- 注意:表原结构中的不需要改变的字段需要照写,不写的话会试做为修改(如:NOT NULL),但是实际操作中UNIQUE不写也不会被删除;
-- 修改密码长度为40
ALTER TABLE user3
MODIFY password CHAR(40) NOT NULL ;
-- 将email 字段改成VARCHAR(50) NOT NULL FIRST
ALTER TABLE user3
MODIFY email VARCHAR(50) NOT NULL FIRST;
-- 测试CHANGE 关键字和modify 不同这个可以修改字段名称,可以在对同一张表的多条字段进行操作
-- 将username 改为user 将password 改为pwd 其他不变
ALTER TABLE user3
CHANGE username user VARCHAR(20) NOT NULL UNIQUE ,
CHANGE password pws VARCHAR(32) NOT NULL ;
-- 将email改为userEmail 类型Varchar(100) Default 'imist@qq.com'
ALTER TABLE user3
CHANGE email userEmail VARCHAR(100) DEFAULT 'imist@qq.com';-- 注意这里相当于添加了默认,因为元字段中没有
ALTER TABLE user3
CHANGE userEmail email VARCHAR(100) ; -- 这里改回去并且不设置默认值 ,相当于默认值删除了
-- 特别说明这里的属性修改MODIFY 和CHANGE 修改属性对 NOTNULL 和 DEFAULT都有效但是 UNIQUEKEY修改无效添加有效(若是之前没有就相当于添加)
DESC user3;
添加主键和删除主键
- 添加主键
ALTER TABLE table_name ADD PRIMARY KEY (设为主键的字段);
,自动设置为NOT NULL - 删除主键
ALTER TABLE tb_name DROP PRIMARY KEY;
,这样就可以直接删除主键,因为一个表只有一个主键
注意 :
- 添加主键的时候表中不可以有主键,即使添加的主键就是该表中已有的主键,也不可以重复添加(修改同理)
- 删除主键的时候若是主键属性包含AUTO_INCREMENT ,不可以直接删除,因为这个属性必须和主键配合使用,得先修改表结构删除AUTO_INCREMENT属性
- CHANGE|MODIFY修改表属性的时候 ,若是表中已经有主键了,不可以带上PRIMARYKEY 原因和第一条一样
-- 测试添加主键和删除主键
CREATE TABLE user4(
id INT UNSIGNED ,
username VARCHAR(20) NOT NULL
);
-- 添加主键
ALTER TABLE user4
ADD PRIMARY KEY (id);
-- 删除主键
ALTER TABLE user4 DROP PRIMARY KEY ;
DESC user4;
CREATE TABLE user5(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
username VARCHAR(20) NOT NULL
);
ALTER TABLE user5 DROP PRIMARY KEY ;
-- 执行失败 `Incorrect table definition; there can be only one auto column and it must be defined as a key`
-- 因为AUTO_INCREMENT 和主键一起使用,若是只删除主键则报错
-- 正确操作 先使用MODIFY或者CHANGE 去掉AUTO_INCREMENT 然后再删除主键
ALTER TABLE user5 MODIFY id INT UNSIGNED ;
-- 这里修改不可以带上PRIMARY KEY 报错`Multiple primary key defined`,即使修改的就是是主键字段也不可以,但是若是没有主键就不会有多
ALTER TABLE user5 DROP PRIMARY KEY ;
DESC user5;
添加唯一索引和删除唯一索引
- 添加唯一索引
ALTER table_name ADD UNIQUE KEY|INDEX index_name(字段名称);
- 删除唯一索引
ALTER table_name DROP INDEX index_name;
-- 测试添加和删除唯一索引
CREATE TABLE user6(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
username VARCHAR(20) NOT NULL UNIQUE ,
password CHAR(32) NOT NULL ,
email VARCHAR(50) NOT NULL UNIQUE
); -- 这里不指定唯一索引的名字的话 默认使用该字段名称作为索引名
-- 删除唯一索引
ALTER TABLE user6 DROP INDEX `username`;
ALTER TABLE user6 DROP INDEX `email`;
-- 添加唯一索引
ALTER TABLE user6 ADD UNIQUE KEY (username); -- 不指定索引名称
ALTER TABLE user6 ADD UNIQUE KEY uni_email(email); -- 指定索引名称
DESC user6;
SHOW CREATE TABLE user6 ;
<p>![](https://gitee.com/iMist/MySqlCode/raw/master/SqlDemo/readme/images2/image9.png)
修改数据表名称
- 语法结构1,ALTER修改表结构
ALTER TABLE table_name RENAME[TO|AS] new_table_name
- 语法结构2,RENAME关键字修改
RENAME TABLE tb_name TO new_table_name
-- 测试修改数据表表名称
-- 将user6 改为user666
RENAME TABLE user6 TO user666;-- 这里RENAME TO 关键字修改不可以改TO为AS
DESC user666;
ALTER TABLE user666 RENAME TO user6;
ALTER TABLE user666 RENAME AS user6;
DESC user6;
修改表的字符编码和排序方式
ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
- 0
- 0
-
分享