Mysql 常用语句

增

增加一个字段/增加一列

alter table test add  column name varchar(10) not null default '' comment 'name'; 

删

删除一列

alter table test drop  column name; 

删除一条记录

delete from test where clause;

改

修改表名称

alter table test rename test1;

修改字段名/列名

alter table test change  column address address1 varchar(30);

修改字段类型/列类型

alter table test change address address  char(40); 
## 或者
alter table test modify address char(10) ;

修改记录

update tablename set columnname = xxx where clause;

查

查一张表的建表语句

show create table tablename;

\G 选项使得查询结果更好查看

普通查询：

MySQL [gifshow]> select * from xyz_external_user limit 1;
+------+---------------------+--------+----------+-----------------+-------------------------------------+--------+---------------+---------------+-----------------+
| id   | user_id             | bucket | platform | name            | avatar                              | status | create_time   | update_time   | target_platform |
+------+---------------------+--------+----------+-----------------+-------------------------------------+--------+---------------+---------------+-----------------+
| 5503 | 6554891701571436550 | 0      |        8 | nimishagarwal90 | ins_head_XlKnpq01V7U_1626846177.jpg |      1 | 1566553687000 | 1566553687000 |               0 |
+------+---------------------+--------+----------+-----------------+-------------------------------------+--------+---------------+---------------+-----------------+

\G 优化显示

# 错误写法
select * from xyz_external_user limit 1 \G;
# 正确写法
select * from xyz_external_user limit 1 \G
# 原因： \G 本身就是提交语句，不需要再加分号