Mysql常用语句备忘录

一、基础

新增

--- 新增一条
INSERT INTO t_user_info (id, mobile, name, is_valid, create_time, update_time) VALUES 
('1540220849750048', '18900000000', '小明', '1', '2023-04-10 17:35:33', NULL);

--- 新增多条
INSERT INTO t_user_info (id, mobile, name, is_valid, create_time, update_time) VALUES 
('1540220849750048', '18900000000', '小明', '1', '2023-04-10 17:35:33', NULL),
('1540220849750049', '18900000001', '小李', '1', '2023-04-10 17:35:35', NULL);

修改

UPDATE t_user_info set mobile='13500000000' where id='1540220849750048';

查询

SELECT id, mobile, name FROM t_user_info where id='1540220849750048';

删除

DELETE FROM t_user_info where id='1540220849750048';

二、查询类

查重

SELECT name,COUNT(1) FROM t_user_info GROUP BY name having count(name)>1;

去重

SELECT DISTINCT name FROM t_user_info;

查询字段最大长度

SELECT max(length(id)) from t_user_info;

三、插入类

从查询结果插入

INSERT INTO t_user_info (id, mobile, id_no, name, is_valid, create_time) select
id, mobile, id_no, name, is_valid, create_time from t_user_info_temp where
id_no='身份证号码';

四、修改类

从查询结果修改

UPDATE t_user_info t1 INNER JOIN (SELECT id_no,name FROM t_user_info_temp) t2
SET t1.name = t2.name,t1.id_no=t2.id_no WHERE t1.mobile = '手机号';