记录Mysql的基本操作

Mysql的增删改

增

INSERT

按字段插入，一次只能插入一条。
INSERT INTO tablename (fieldname1,fieldname2,fieldname3……) VALUES(%s,%s,%s……)
将查询结果插入，可以批量插入，但查询结果必须和字段对应。
INSERT INTO tablename (fieldname1,fieldname2,fieldname3……) SELECT …… FROM ……
替换插入，这种插入方式是按照主键替换。所以表中必须要有主键。
如果表中不存在该数据，就直接插入。如果存在，则先删除表中数据，再插入。所以如果主键是自增主键的，每次替换会将自增ID增加2。
REPLACE INTO tablename (fieldname1,fieldname2,fieldname3……) VALUES(%s,%s,%s……)

删

DROP

DROP TABLE tablename 删表，该方法会删除表结构和数据文件
DROP DATABASE dbname 删库（慎用）

DELETE

删表数据，该方法并未真正删除数据，只是将数据文件中的数据标为不显示。所以可以恢复，自增ID不会重置。操作会有日志产生，会激活触发器。
DELETE FROM tablename 清空表数据
DELETE FROM tablename WHERE …… 按条件删除数据

TRUNCATE

删表数据，该方法直接清除表数据文件，速度比DELETE方法快，不可恢复，自增ID会重置。相当于重建表，无日志产生，不会激活触发器。
TRUNCATE TABLE tablename

改

UPDATE

单表更新

UPDATE [LOW_PRIORITY] [IGNORE] table_name   
SET   
column_name1 = expr1,  
column_name2 = expr2,  
...  
WHERE  
condition

跨表更新

UPDATE T1, T2,　　　　　　　　　　　　　　　　　　　　　　　　　　　　
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1　　　　　　　　　 
SET T1.C2 = T2.C2, 
    T2.C3 = expr
WHERE condition

跨表更新，要更新的表需要写在第一个（即T1的位置）。UPDATE语句后接多个表的关联方式。

修改表名称

RENAME TABLE old_table_name TO new_table_name;

ALTER方法

1. 增加表字段

ALTER TABLE tablename 
ADD colname coltype 
[CHARACTER SET …… [COLLATE ……]] 
[[NOT] NULL | DEFAULT | COMMENT | AFTER]

可选参数：

CHARACTER SET: 设置字段字符集编码
COLLATE: 设置字段排序规则，如果语句中有CHARACTER SET，COLLATE必须在其后面
[NOT] NULL: 是否可以未空
DEFAULT: 默认值
COMMENT: 注释
AFTER: 新增字段放置在哪个字段后

2. 修改表字段

更新表字段类型

ALTER TABLE tablename 
MODIFY colname new_coltype [[NOT] NULL | COMMENT]

更新表字段字符集编码

ALTER TABLE tablename
MODIFY colname CHARACTER SET …… coltype [[NOT] NULL | COMMENT]

重命名字段名

ALTER TABLE tablename RENAME oldname newname coltype [NULL | COMMENT]

删除指定字段

ALTER TABLE tablename DROP COLUMN columnname

Mysql的建表语句

CREATE TABLE tablename(
  -- 建立自增主键 
  id bigint not null auto_increment comment '自增主键',
  -- 设置字段字符集编码及排序规则，会覆盖表的字符集编码和排序规则
  name varchar(20) character set utf8mb4 collate utf8mb4_general_ci not null comment '名称',
  -- 为字段设置默认值
  create_time date not null default current_timestamp comment '入库时间',
  -- 为字段设置默认值的同时，更新时自动变更
  update_time date not null default current_timestamp on update current_timestamp comment '更新时间',
  -- 设置主键
  primary key (id),
  -- 设置唯一索引
  unique key idx_name (name),
  -- 普通索引
  key idx_create_time (create_time),
  -- 组合索引
  key idx_update_time_name (update_time, name)
)ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

ENGINE: 选择数据库引擎，一般是InnoDB。
AUTO_INCREMENT：自增起始值
CHARSET：字符集编码，mysql最好设置为utf8mb4。mysql的utf8只能存储3字节的字符，在使用utf8作为字符集编码时，一些超过3字节的特殊字符入不了表。比如表情。
COLLATE：排序规则，对应字符集编码都有对应的排序规则，一般utf8mb4使用utf8mb4_general_ci。以_ci结尾的COLLATE是大小写不敏感的排序规则，即排序时'a'和'A'是同时排前面的。以_cs结尾的COLLATE是大小写敏感的排序规则。对COLLATE更详细的解释看此博文
ROW_FORMAT：行存储格式，一般采用DYNAMIC。DYNAMIC是一种节省空间的存储方式，不会存储为NULL的列。