Deng
Deng
MySQL基础之表的管理 | odjBlog
    欢迎来到odjBlog的博客!
> MySql > MySQL基础之表的管理

MySQL基础之表的管理

MySql odjbin 4年前 (2021-09-11) 112次浏览 0个评论

添加和删除字段操作

  • 添加字段：ALTER TABLE tbl_name
  • ADD 字段名称 字段属性 [完整性约束条件] [FIRST|AFTER 字段名称]
  • 删除字段 O：ALTER TABLE tbl_name DROP 字段名称
-- 测试添加和删除字段
CREATE TABLE IF NOT EXISTS user1(
    id INT UNSIGNED AUTO_INCREMENT KEY
);
-- 添加用户名字段 username VARCHAR(20)
ALTER TABLE user1
ADD username VARCHAR(20);
DESC user1;
-- 添加密码字段 password CHAR(32) NOT NULL
ALTER TABLE user1
ADD password CHAR(32) NOT NULL;
-- 添加邮箱字段 email VARCHAR(50) NOT NULL UNIQUE 加到 username 之后
ALTER TABLE user1
ADD email VARCHAR(50) NOT NULL UNIQUE AFTER username;
-- 添加测试字段 test TINYINT(1) NOT NULL DEFAULT 0; 加到首位
ALTER TABLE user1
ADD test TINYINT(1) NOT NULL DEFAULT 0 FIRST;
-- 删除 test 字段
ALTER TABLE user1
DROP test;
-- 添加 age、addr 字段，删除 email 字段
ALTER TABLE user1
ADD age TINYINT UNSIGNED NOT NULL DEFAULT 18,
ADD addr VARCHAR(100) NOT NULL DEFAULT '北京',
DROP email;
DESC user1;
-- 创建数据表 id / proName
-- 添加 price FLOAT(8,2) UNSIGNED NOT NULL DEFAULT 0
-- 添加 num INT UNSIGNED NOT NULL DEFAULT 100
-- 添加测试字段 test VARCHAR(50) NOT NULL 加到首位
-- 添加测试字段 test1 CHAR(23) NOT NULL 添加到 price 之后
-- 删除 test 字段
-- 选中一次数据表完成多次操作，添加一个 desc TEXT, 添加 isSale 布尔类型 0 下架 1 代表在卖
-- 删除 test1 字段

添加和删除默认值操作

  • 添加默认值：ALTER TABLE tbl_name ALTER 字段名称 SET DEFAULT 默认值;
  • 删除默认值：ALTER TABLE tbl_name ALTER 字段名称 DROP DEFAULT
-- 测试添加删除默认值操作
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 字段添加默认值 imooc@qq.com
ALTER TABLE user2
ALTER email SET DEFAULT 'imooc@qq.com';
-- 删除 age 字段的默认值
ALTER TABLE user2
ALTER age DROP DEFAULT;
-- 添加地址字段
-- 给地址字段添加默认值为北京
-- 删除商品数量的默认值

MODIFY 和 CHANGE 关键字的使用

  • 修改字段类型、字段属性：ALTER TABLE tbl_name MODIFY 字段名称 字段类型 [字段属性] [FIRST | AFTER 字段名称]
  • 修改字段名称、字段类型、字段属性：ALTER TABLE tbl_name CHANGE 原字段名称 新字段名称 字段类型 字段属性 [FIRST | AFTER 字段名称]
-- 测试修改字段类型和字段属性、字段名称
CREATE TABLE user3(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(5) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(10) NOT NULL
);
-- 将用户名字段的类型改为 20
ALTER TABLE user3
MODIFY username VARCHAR(20) NOT NULL;
-- 将密码的长度改为 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;
-- 将 username 名称改为 user
ALTER TABLE user3
CHANGE username user VARCHAR(20) NOT NULL;
-- 将 password 名称改为 pwd
ALTER TABLE user3
CHANGE password pwd CHAR(40) NOT NULL;
-- 将 email 改成 userEmail 类型改成 VARCHAR(100) DEFAULT 'imooc@qq.com'
ALTER TABLE user3
CHANGE email userEmail VARCHAR(100) DEFAULT 'imooc@qq.com';

主键和唯一索引操作

  • 添加主键：ALTER TABLE tbl_name ADD PRIMARY KEY(字段名称)
  • 删除主键：ALTER TABLE tbl_name DROP PRIMARY KEY;
-- 测试添加和删除主键
CREATE TABLE user4(
id INT UNSIGNED,
username VARCHAR(20) NOT NULL
);
-- 添加主键
ALTER TABLE user4
ADD PRIMARY KEY(id);
-- 删除主键
ALTER TABLE user4
DROP PRIMARY KEY;
CREATE TABLE user5(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL
);
-- 将 id 的 AUTO_INCREMENT 去掉,先去掉，才可以去主键
ALTER TABLE user5
MODIFY id INT UNSIGNED;
ALTER TABLE user5
DROP PRIMARY KEY;
  • 添加唯一：ALTER TABLE tbl_name ADD UNIQUE KEY | INDEX [index_name] (字段名称)
  • 删除唯一 ：ALTER TABLE tbl_name DROP index_name;
-- 测试添加和删除唯一
CREATE TABLE user6(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL UNIQUE
);
DESC user6;
SHOW CREATE TABLE user6;
-- 删除唯一索引 username 和 email
ALTER TABLE user6
DROP INDEX username;
SHOW CREATE TABLE user6;-- 查看详细信息
ALTER TABLE user6
DROP INDEX email;
-- 添加唯一索引
ALTER TABLE user6
ADD UNIQUE KEY(username);
DESC user6;
SHOW CREATE TABLE user6;-- 查看详细信息
--加上索引名称
ALTER TABLE user6
ADD UNIQUE INDEX uni_email(email);
DESC user6;
SHOW CREATE TABLE user6;-- 查看详细信息
--删除索引名称 UNI
ALTER TABLE user6 DROP  INDEX uni_email;
DESC user6;

重命名表的操作

  • 修改数据表名称：ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
  • RENAME TABLE tbl_name TO new_tbl_name;
-- 将 user6 改为 user666
ALTER TABLE user6
RENAME TO user666;
SHOW TABLES;
-- 将 user666 改为 user6
ALTER TABLE user666
RENAME AS user6;
ALTER TABLE user6
RENAME user666;
RENAME TABLE user666 TO user6;

MyISAM 存储引擎

  • 默认 MyISAM 的表会在磁盘中产生 3 个文件：.frm 表结构文件 .MYD 数据文件 .MYI 索引文件
  • 可以在创建表的时候指定数据文件和索引文件的存储位置，只有 MyISAM 表支持
    • DATA DIRECORY [=] 数据保存的绝对路径
    • INDEX DIRECTORY[=]索引文件保存的绝对路径
  • MyISAM 单表最大支持的数据量 2 的 64 次方条记录
  • 每个表最多可以建立 64 个索引
  • 如果是复合索引，每个复合索引最多包含 16 个列，索引值最大长度是 1000B
  • MyISAM 引擎的存储格式：
  • 定长(FIXED 静态) ：是指字段中不包含 VARCHAR/TEXT/BLOB
  • 动态(DYNAMIC ) ：只要字段中包含了 VARCHAR/TEXT/BLOB【空间少】
  • 压缩(COMPRESSED )：myisampack 创建
-- 测试 MyISAM 存储引擎
CREATE TABLE test_myisam(
    a INT UNSIGNED,
    b VARCHAR(20),
    c CHAR(32)
)ENGINE=MyISAM;
SHOW TABLES;
SHOW CREATE TABLE test_myisam;
-- 定长:Row_format:Fixed
CREATE TABLE myisam_1(
a char(30),
id int
)ENGINE=MyISAM;
SHOW CREATE TABLE myisam_1;
SHOW TABLE STATUS LIKE 'myisam_1'\G
-- 变长：Row_format:Dynamic
CREATE TABLE myisam_2(
a VARCHAR(30),
id int
)ENGINE=MyISAM;
SHOW TABLE STATUS LIKE 'myisam_2'\G
--变静态定长
CREATE TABLE myisam_3(
a VARCHAR(30),
id int
)ENGINE=MyISAM ROW_FORMAT=FIXED;

InnoDB 存储引擎【用的多】

  • 设计遵循 ACID 模型，支持事务，具有从服务奔溃中恢复的能力，能够最大限度保护用户的数据
    • 原子性( Atomiocity )
    • 一致性(Consistency)
    • 隔离性( Isolation )
    • 持久性(Durability )
  • 支持行级锁，可以提升多用户并发时的读写性能
  • 支持外键，保证数据的一致性和完整性
  • InnoDB 拥有自己独立的缓冲池，常用的数据和索引都在缓存中
  • 对于 INSERT、UPDATE、DELETE 操作，InnoDB 会使用一种 change buffering 的机制来自动优化，还可以提供一致性的读，并且还能够缓存变更的数据，减少磁盘 I/O，提高性能
  • 创建 InnoDB 表之后会产生两个文件：
    • .frm 表结构文件
    • .ibd，数据和索引存储表空间中
  • 所有的表都需要创建主键，最好是配合上 AUTO_INCREMENT,也可以放到经常查询的列作为主键
CREATE TABLE test_innodb(
 id INT UNSIGNED AUTO_INCREMENT KEY,
 username VARCHAR(20) NOT NULL
);
喜欢 (1)赏
[]
分享 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi，您需要填写昵称和邮箱！

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
已稳定运行：3年301天9小时0分