mysql 常用sql语句

更改注释

ALTER TABLE TableName COMMENT = ‘备注的内容’ ;

添加字段

alter table user add name varchar(100) not null comment ‘姓名’;

更改字段类型

alter table user modify column name int not null comment ‘姓名’;

删除字段

alter table user drop run_time;

#更改字段名
alter table user change name name1 varchar(1000) null comment ‘姓名’;

添加索引

alter table user
add unique index unique_name_mobile(name, mobile);

删除索引

alter table user
drop index unique_name_mobile;

删除表

drop table user;

查询结果换行展示

select * from student where id = 1 \G;
(结果加上 \G)

取时间的差

SELECT TIMESTAMPDIFF(SECOND, start_time, end_time)
from user;

获取邮箱前缀

select substring_index(login_email, ‘@’, 1) from user where id = ‘saddd’;

更新字段内容固定值"test_"加邮箱前缀
update user set name = concat(‘test_’, substring_index(email, ‘@’, 1)) where create_time < ‘2024-04-09 14:00:02’;

str=www.baidu.com
substring_index(str,‘.’,1) 处理的结果是：www
substring_index(str,‘.’,2) 得到的结果是：www.baidu
substring_index(str,‘.’,-1) 处理的结果是：.com