Mysql 常用语句模版

mysql 语句模版

DDL 语句模版

建表语句

use `service_score`;CREATE TABLE `student` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `student_name` varchar(64) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` bigint(20) NOT NULL DEFAULT '0' COMMENT '年龄',
  `deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '逻辑删除：0=未逻辑删除，1=已逻辑删除',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_sequence_student_name` (`student_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='表注释';

添加列

ALTER TABLE 表 ADD COLUMN `字段名称` int(5) NULL COMMENT '注释' AFTER `某字段名称`;

修改字段

ALTER TABLE 表 MODIFY COLUMN `SENDPAY` VARCHAR(500) DEFAULT NULL COMMENT '注释' ;

添加索引

ALTER TABLE 表 ADD INDEX idx_waybill_code (字段名称);

删除索引

alter table 表名 drop index|key 索引名;

常用DML语句

插入数据

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

查询语句

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column_name [ASC | DESC]]
[LIMIT number];

更新语句

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

删除语句

DELETE FROM table_name
WHERE condition;