MySQL超详细讲解表的用法
一、表的基本概念
在MySQL数据库中，表是存储数据的基本单元，所有结构化的数据都按照行和列的形式组织存放在表中。每一行代表一条独立的数据记录，每一列代表记录中的一个属性字段，例如用户信息表可以包含用户ID、用户名、密码、注册时间等列，每一行就对应一个用户的具体信息。
二、表的创建
创建表需要使用CREATE TABLE语句，语法结构如下：
CREATE TABLE 表名 ( 列名1 数据类型 [约束条件], 列名2 数据类型 [约束条件], ... [表级约束条件] ) [表选项];
下面是一个创建用户表的示例，包含常见的字段和数据类型：
CREATE TABLE user_info ( user_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID，自增主键', username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名，不允许重复且非空', password VARCHAR(100) NOT NULL COMMENT '用户密码，非空', email VARCHAR(100) COMMENT '用户邮箱', register_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间，默认当前时间' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';
上述示例中用到的常见约束说明：
PRIMARY KEY：主键约束，确保该列的值唯一且非空，一张表只能有一个主键
AUTO_INCREMENT：自增约束，通常用于整数类型的主键，每次插入新记录时自动递增赋值
NOT NULL：非空约束，确保该列的值不能为NULL
UNIQUE：唯一约束，确保该列的所有值不重复，允许存在NULL值（如果存在多个NULL也不会冲突）
DEFAULT：默认值约束，当插入记录时未指定该列的值，会自动使用默认值
三、表的查询操作
查询表结构可以使用DESCRIBE或者SHOW CREATE TABLE语句：
-- 查看表结构 DESCRIBE user_info; -- 或者简写为 DESC user_info; -- 查看创建表的完整SQL语句 SHOW CREATE TABLE user_info;
查询表中的数据使用SELECT语句，基础语法如下：
-- 查询表中所有数据 SELECT * FROM user_info; -- 查询指定列的数据 SELECT user_id, username, email FROM user_info; -- 带条件查询 SELECT * FROM user_info WHERE username = 'test_user'; -- 排序查询 SELECT * FROM user_info ORDER BY register_time DESC; -- 分页查询，查询第1页，每页10条数据 SELECT * FROM user_info LIMIT 0, 10;
四、表的修改操作
当表结构需要调整时，可以使用ALTER TABLE语句完成修改，常见修改场景如下：
1. 添加列
-- 在user_info表中添加年龄列，类型为INT，默认值为NULL ALTER TABLE user_info ADD COLUMN age INT COMMENT '用户年龄'; -- 在指定列后面添加新列，在username列后添加手机号列 ALTER TABLE user_info ADD COLUMN phone VARCHAR(20) COMMENT '用户手机号' AFTER username;
2. 修改列
-- 修改email列的长度为150 ALTER TABLE user_info MODIFY COLUMN email VARCHAR(150) COMMENT '用户邮箱'; -- 修改列名，把age列改名为user_age ALTER TABLE user_info CHANGE COLUMN age user_age INT COMMENT '用户年龄';
3. 删除列
-- 删除user_age列 ALTER TABLE user_info DROP COLUMN user_age;
4. 修改表名
-- 把user_info表重命名为sys_user_info ALTER TABLE user_info RENAME TO sys_user_info;
五、表的数据操作
1. 插入数据
-- 插入单条数据，自增主键和默认值的列可以不用指定
INSERT INTO user_info (username, password, email) VALUES ('zhangsan', '123456', 'zhangsan@ipipp.com');
-- 插入多条数据
INSERT INTO user_info (username, password, email) VALUES
('lisi', 'abcdef', 'lisi@ipipp.com'),
('wangwu', '111111', 'wangwu@ipipp.com');2. 更新数据
更新数据需要特别注意WHERE条件，如果没有WHERE条件会更新表中所有记录：
-- 更新指定用户名的邮箱 UPDATE user_info SET email = 'new_zhangsan@ipipp.com' WHERE username = 'zhangsan'; -- 更新多个字段 UPDATE user_info SET password = 'new_password', phone = '13800138000' WHERE user_id = 1;
3. 删除数据
删除数据同样需要注意WHERE条件，没有WHERE条件会删除表中所有数据：
-- 删除指定用户ID的记录 DELETE FROM user_info WHERE user_id = 3; -- 清空表数据，效率比DELETE高，且自增主键会重置 TRUNCATE TABLE user_info;
六、表的删除操作
删除表使用DROP TABLE语句，执行后表结构和所有数据都会被永久删除，操作前需要确认数据是否需要备份：
-- 删除单张表 DROP TABLE user_info; -- 删除多张表 DROP TABLE user_info, order_info; -- 如果表存在才删除，避免报错 DROP TABLE IF EXISTS user_info;
七、表的约束与索引补充
除了列级约束，还可以添加表级约束，例如联合主键：
-- 创建学生选课表，使用学生ID和课程ID作为联合主键 CREATE TABLE student_course ( student_id INT COMMENT '学生ID', course_id INT COMMENT '课程ID', score DECIMAL(5,2) COMMENT '课程成绩', -- 表级联合主键约束 PRIMARY KEY (student_id, course_id) );
索引可以提升查询效率，常见的索引创建方式如下：
-- 为user_info表的email列创建普通索引 CREATE INDEX idx_user_email ON user_info(email); -- 为user_info表的phone列创建唯一索引 CREATE UNIQUE INDEX idx_user_phone ON user_info(phone); -- 查看表的索引信息 SHOW INDEX FROM user_info; -- 删除索引 DROP INDEX idx_user_email ON user_info;
八、注意事项
创建表时需要合理选择数据类型，避免过度浪费存储空间，例如能用VARCHAR(50)就不要使用VARCHAR(255)
修改表结构尤其是生产环境的表时，需要先评估影响，大表的结构修改可能会锁表影响业务
DELETE和TRUNCATE的区别：DELETE是逐行删除，支持回滚，会触发触发器；TRUNCATE是直接清空表数据，不支持回滚，不会触发触发器，且重置自增主键
设计表时建议为每个字段和表添加COMMENT注释，方便后续维护