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

75 篇文章 0 订阅
68 篇文章 0 订阅

   以下方 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;


  • 1
    点赞
  • 10
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值