Mysql，创建/删除表、添加表字段、修改表字段、添加索引等常用语句。_mysql创建表sql语句

   以下方 user 表为例，讲解如何Mysql创建表语句、添加表字段、修改表字段、添加索引等常用的SQL语句。

   出于编写本篇文章的考虑，使用下方建表语句创建的  user表 有很多不合理的地方，例如：teacher_id 是 varchar类型而非 bigint类型、 '创建时间' 有2个字段 等。 大家在自己公司数据库环境中，根据自己的实际需求创建对应的表，不要把这张建表语句直接复制、粘贴使用。

一、操作数据库表：

1、创建表SQL语句

-- 创建SQL表语句 .
CREATE TABLE user (
  id bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID, 主键.',
  is_deleted char(1) DEFAULT 'n' COMMENT '是否删除',
  creator varchar(255) DEFAULT NULL,
  gmt_create datetime DEFAULT NULL COMMENT '创建时间',
  modifier varchar(255) DEFAULT NULL COMMENT '修改人',
  gmt_modified datetime DEFAULT NULL COMMENT '修改时间',
  name varchar(100) DEFAULT NULL COMMENT '姓名',
  age int(3) DEFAULT NULL COMMENT '年龄',
  create_date datetime DEFAULT NULL COMMENT '创建时间',
  teacher_id varchar(20) DEFAULT NULL COMMENT '教师表ID',
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1015 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
 
 
 

2、删除表语句

-- 删除表
DROP TABLE user;
 
 

3、给表添加字段

-- 向 table_name 表中添加字段
ALTER TABLE table_name ADD COLUMN column_name VARCHAR(100) DEFAULT NULL COMMENT '新加字段' AFTER old_column;
 
-- 给 user表 中添加 'age' 字段，字段默认值为：NULL，在 'name' 字段后.
ALTER TABLE user ADD COLUMN age int(3) DEFAULT NULL COMMENT '年龄' AFTER name;
 
-- 给 user表 中添加 'sex' 字段，字段默认值为：1，在 'age' 字段后.
ALTER TABLE user ADD COLUMN sex int(1) DEFAULT 1 COMMENT '性别：1-男，0-女.' AFTER age;
 
 
 

4、修改表字段：字段类型、字段注释信息、给表字段添加默认值.

-- 将 user 表中 teacher_id 字段修改成 bigint 类型.
ALTER TABLE user MODIFY teacher_id BIGINT(20) COMMENT '教师ID';
 
-- 修改 user表中 name 字段的长度.
ALTER TABLE user MODIFY name varchar(256) COMMENT '姓名_注释信息.';
 
-- 给 user表中 creator 设置默认值
-- 推荐使用 
ALTER TABLE `user` MODIFY creator varchar(255) DEFAULT 'system' COMMENT '创建人';
-- ALTER TABLE `user` ALTER COLUMN creator SET DEFAULT 'system22';  -- 不建议使用
-- ALTER TABLE `user` MODIFY creator varchar(255) COMMENT '创建人22';  -- 修改注释信息
 
-- 给 user表中 gmt_create 设置默认值
ALTER TABLE user MODIFY gmt_create datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间';
-- 给 user表中 gmt_modified 设置默认值，更新操作时设置默认值.
ALTER TABLE user MODIFY gmt_modified datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间';
 
 
 

5、删除表字段

-- 删除 user表 中1个字段.
ALTER TABLE `user` DROP COLUMN age;
 
-- 删除 user表 中2个字段.
ALTER TABLE `user` DROP COLUMN age, DROP COLUMN sex;
 
 
 

6、给表添加索引

-- 给 user表 添加索引
ALTER TABLE user ADD INDEX idx_gmt_modified_id (gmt_modified, id);   -- 2023/05/25
 
 
 

二、对数据库表中的数据进行操作.

1、新增表数据

INSERT INTO user(id, is_deleted, creator, gmt_create, modifier, gmt_modified, name, age, create_date, teacher_id) VALUES (1, 'n', '柳乐儿', '2023-06-22 11:31:38', '牛彪彪', '2023-06-21 11:31:55', '张三', 20, '2020-04-02 09:54:23', '16');
INSERT INTO user(id, is_deleted, creator, gmt_create, modifier, gmt_modified, name, age, create_date, teacher_id) VALUES (2, 'n', '马三', '2023-06-14 11:32:05', NULL, NULL, '张三年', 22, '2020-01-05 11:20:00', NULL);
INSERT INTO user(id, is_deleted, creator, gmt_create, modifier, gmt_modified, name, age, create_date, teacher_id) VALUES (4, 'n', NULL, NULL, '李思思', '2023-06-17 11:32:18', '李四', 25, '2019-10-22 16:27:03', NULL);
INSERT INTO user(id, is_deleted, creator, gmt_create, modifier, gmt_modified, name, age, create_date, teacher_id) VALUES (1014, 'n', NULL, NULL, NULL, NULL, '二狗', 22, '2022-10-31 13:38:01', NULL);
INSERT INTO user(id, is_deleted, creator, gmt_create, modifier, gmt_modified, name, age, create_date, teacher_id) VALUES (1015, 'n', NULL, NULL, NULL, NULL, '大队长', 24, '2023-06-29 11:33:01', '12');
 
 

2、查询表数据

-- 简单查询 
SELECT
	u.*
	
FROM user u
WHERE
	1 = 1
	AND u.is_deleted = 'n'
	AND u.`name` LIKE CONCAT('%', '三', '%')
LIMIT 100;	
 
 

3、更新表数据

-- 更新表多个字段
UPDATE user u	
SET
	u.creator = '王大锤',
	u.gmt_create = '2021-02-15 08:22:54',
	u.teacher_id = '101'
	
WHERE
	u.id = 4;
 

4、删除表数据

-- 删除表数据，需要说明的是：在删除表数据的SQL中，表不能起别名，否则SQL语句无效。
DELETE FROM user
WHERE
	id = 4
	AND teacher_id = 101;
 
 

5、清空表数据

     user表中ID是自动增长，清空表后，ID将会从1重新开始增长。

-- 清空表
TRUNCATE TABLE user;