MySQL 常用语句

PkyShare

已于 2023-12-14 09:30:23 修改

阅读量366

点赞数

分类专栏： MySQL 文章标签： mysql 数据库服务器

于 2020-06-02 10:59:53 首次发布

本文链接：https://blog.csdn.net/qq_34369588/article/details/106491451

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1 新增

1.1 新增字段

ALTER TABLE shanghai_info ADD created DATETIME;

2 查询

3 修改

3.1 修改表名

alter table ts01 rename to ts01_new;

3.2 修改字段

ALTER TABLE shanghai_info MODIFY created DATETIME DEFAULT NULL COMMENT "新增时间";

3.3 替换字段中的某个字符串

UPDATE tableName SET phone = REPLACE(phone,'.000000','')

4 删除

4.1 清空表数据

truncate 是整体删除（速度较快）， delete 是逐条删除（速度较慢）。
truncate 不写服务器 log，delete 写服务器 log，也就是 truncate 效率比 delete 高的原因。
truncate 不激活 trigger (触发器)，但是会重置 Identity（标识列、自增字段），相当于自增列会被置为初始值，又重新从 1 开始记录，而不是接着原来的 ID 数。而 delete 删除以后，Identity 依旧是接着被删除的最近的那一条记录 ID 加 1 后进行记录。

4.2 删除某字段

ALTER TABLE table_name DROP COLUMN column_name;

5 重复记录操作

5.1 查询重复

如，查找重复记录是根据单个字段（peopleId）来判断。

SELECT * FROM Tpeople 
  WHERE peopleId IN ( SELECT peopleId FROM Tpeople GROUP BY peopleId HAVING COUNT(peopleId) > 1)

根据多个字段来判断

SELECT * FROM illegal_info 
  WHERE (illegal_code, city_id, reason) IN ( SELECT illegal_code, city_id, reason FROM illegal_info GROUP BY illegal_code, city_id, reason HAVING COUNT(*) > 1) ORDER BY illegal_code;

5.2 删除表中多余的重复记录

例如，重复记录是根据单个字段（peopleId）来判断，只保留最先增加的记录，下面是保留 ID 最小的记录

DELETE FROM test WHERE id NOT IN (
	SELECT a.id FROM (
		SELECT MIN(id) AS id FROM test GROUP BY username HAVING COUNT(username) > 1 
		)AS a
	) AND username IN (
		SELECT b.username FROM (
			SELECT username FROM test GROUP BY username HAVING COUNT(username) > 1
			) AS b
		);

根据多个字段进行删除，只留有 rowid 最小的记录

delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

6 索引

6.1 添加普通索引

存在表时添加普通索引

ALTER TABLE shanghai_info ADD INDEX (car_number);

存在表时添加唯一索引

ALTER TABLE llegal_info ADD UNIQUE INDEX [索引名](字段1, 字段2, 字段3);

建表时添加索引

CREATE TABLE `hunan_car` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `car_number` varchar(255) DEFAULT NULL COMMENT '车牌号',
  `vin` varchar(255) DEFAULT NULL COMMENT '车架号',
  `eng` varchar(255) DEFAULT NULL COMMENT '发动机',
  `car_type` varchar(255) DEFAULT NULL COMMENT '车辆类型',
  `status` int(11) DEFAULT NULL COMMENT '读取状态，0 未读 / 1 已读',
  `success` int(11) DEFAULT NULL COMMENT '读取成功状态 0 爬取失败 / 1 爬取成功',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `car_number` (`car_number`) 
) ENGINE=InnoDB AUTO_INCREMENT=110280 DEFAULT CHARSET=utf8;

6.2 添加唯一索引

存在表时添加索引

ALTER TABLE shanghai_info ADD UNIQUE (car_number);

建表时添加索引

CREATE TABLE `hunan_car` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `car_number` varchar(255) DEFAULT NULL COMMENT '车牌号',
  `vin` varchar(255) DEFAULT NULL COMMENT '车架号',
  `eng` varchar(255) DEFAULT NULL COMMENT '发动机',
  `car_type` varchar(255) DEFAULT NULL COMMENT '车辆类型',
  `status` int(11) DEFAULT NULL COMMENT '读取状态，0 未读 / 1 已读',
  `success` int(11) DEFAULT NULL COMMENT '读取成功状态 0 爬取失败 / 1 爬取成功',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `car_number`` (`car_number``) 
) ENGINE=InnoDB AUTO_INCREMENT=110280 DEFAULT CHARSET=utf8;

6.3 创建组合索引

存在表时添加索引

ALTER TABLE illegal_info ADD INDEX idx_reason_money_city_id (reason,money,city_id);

建表时添加索引

CREATE TABLE `illegal_info` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `illegal_code` varchar(6) DEFAULT NULL COMMENT '违章代码，截取第二个字符则为扣分，扣分为7则为12',
  `reason` varchar(300) DEFAULT NULL COMMENT '违章信息',
  `city_id` int(11) DEFAULT '0' COMMENT '城市id',
  `money` varchar(10) DEFAULT NULL COMMENT '罚款',
  PRIMARY KEY (`id`),
  KEY `idx_reason_money_city_id` (`reason`,`money`,`city_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16448 DEFAULT CHARSET=utf8 COMMENT='各地违章信息扣分罚款详情表'

6.4 删除索引

ALTER TABLE shanghai_info DROP INDEX cphm;

7、以指定格式时间归类

SELECT STATUS AS '状态码', COUNT(1) AS '条数', DATE_FORMAT(updated, '%Y-%m') AS '月份' 
FROM notice_info GROUP BY STATUS, DATE_FORMAT(updated, '%Y-%m');

在这里插入图片描述

SELECT STATUS AS '状态码', COUNT(1) AS '条数', DATE_FORMAT(updated, '%Y-%m-%d') AS '日期' 
FROM notice_info GROUP BY STATUS, DATE_FORMAT(updated, '%Y-%m-%d')

在这里插入图片描述

8、有数据则修改，无则新增

方法一：replace into

replace into 会根据唯一索引或主键进行判断，如果存在则覆盖写入字段，如果不存在则新增。该方法底层是先 delete 再 insert，如果有子表依赖的话不建议使用。

REPLACE INTO demo(id,name,age) VALUES (1,''张山',19)

方法二：on duplicate key

on duplicate key 新增时如果导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE，如果不会导致唯一值列重复的问题，则插入新行。该方法使用是要小心避免多个唯一索引的情况下使用，当出现多个唯一索引时会出现修改任意一行数据的结果。

#values(col_name)函数只是取当前插入语句中的插入值
INSERT INTO demo(id,name,age) VALUES (1,''张山',19)
ON DUPLICATE KEY UPDATE name = values(name),age = values(age);

#age = age + values(age)可起到累加作用
INSERT INTO demo(id,name,age) VALUES (1,''张山',19)
ON DUPLICATE KEY UPDATE name = values(name),age = age + values(age)

#@ageVal可起到原子性查询数据
INSERT INTO demo(id,name,age) VALUES (1,''张山',@ageVal:=19)
ON DUPLICATE KEY UPDATE name = values(name),age = age + values(age);
select @ageVal;

#当id为主键，age为唯一索引时，数据中存在id=1,age=19时，上面的语句就相当于
UPDATE demo SET name='张山' WHERE id=1 OR age=19 LIMIT 1;