常用MySQL语句汇总-追风逐雨

新增

#新增数据
insert into `表名` (`字段1`,`字段2`,`字段3`) values('值1','值2','值3');

#把源数据表的记录添加到目标数据表
insert into `目标数据表` select * from `源数据表`;

#新增数据

insert into `表名` (`字段1`,`字段2`,`字段3`) values('值1','值2','值3');

#把源数据表的记录添加到目标数据表

insert into `目标数据表` select * from `源数据表`;

查询

#按字段排序 顺序=ASC  倒序=DESC
SELECT * FROM `表名` ORDER BY `排序字段` DESC LIMIT 0,100;

#查询表空间占用
SELECT TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1048576 as size_Mb, TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA=`数据库` AND TABLE_NAME=`表名`

#按字段排序顺序=ASC 倒序=DESC

SELECT * FROM `表名` ORDER BY `排序字段` DESC LIMIT 0,100;

#查询表空间占用

SELECT TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1048576 as size_Mb, TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA=`数据库` AND TABLE_NAME=`表名`

修改

#批量替换指定字段部分内容
update `表名` set `字段` = replace(`字段`,'被替换的文字','替换文字') where `条件字段` like '%被替换的文字%';

#批量替换指定字段部分内容

update `表名` set `字段` = replace(`字段`,'被替换的文字','替换文字') where `条件字段` like '%被替换的文字%';

删除

#条件表达式删除
delete from `表名` where `条件字段` = '值';

#删除字段值包含某内容的记录
delete from `表名` where `条件字段` like '%内容%';

#将数据表所有记录删除
delete from `表名`;

#删除表
drop table `表名`;

#清空表
truncate table `表名`;

#条件表达式删除

delete from `表名` where `条件字段` = '值';

#删除字段值包含某内容的记录

delete from `表名` where `条件字段` like '%内容%';

#将数据表所有记录删除

delete from `表名`;

#删除表

drop table `表名`;

#清空表

truncate table `表名`;

MySQL表达式函数

AVG(字段名)得出一个表格栏平均值
COUNT(*|字段名)对数据行数的统计或对某一栏有值的数据行数统计
MAX(字段名)取得一个表格栏最大的值
MIN(字段名)取得一个表格栏最小的值
SUM(字段名)把数据栏的值相加
DATE_FORMAT(create_date,’%Y%u’) weeks 按周分组

#sum函数用法
seect sum(`字段名`) as `别名` from `表名` where `字段` = '值'

#sum函数用法

seect sum(`字段名`) as `别名` from `表名` where `字段` = '值'

存储过程操作

#删除存储过程
DROP PROCEDURE  if EXISTS init_fg_data;

#创建存储过程
CREATE PROCEDURE init_fg_data()
BEGIN
    declare  id int default  2200000; 
    declare  num int  default 4000000;
    while num > 0 DO
        INSERT INTO `cms_content`(`id`, `site_id`, `title`, `user_id`, `check_user_id`, `category_id`, `model_id`, `parent_id`, `quote_content_id`, `copied`, `contribute`, `author`, `editor`, `only_url`, `has_images`, `has_files`, `has_products`, `has_static`, `url`, `description`, `tag_ids`, `dictionary_values`, `cover`, `childs`, `scores`, `comments`, `clicks`, `publish_date`, `expiry_date`, `check_date`, `update_date`, `create_date`, `sort`, `status`, `disabled`) VALUES (id, 1, CONCAT(id,'重庆市人民政府办公厅发重庆市人民政府办公厅转发市建委关于贯彻<<中华人民共和国招标法>>严格建设工程招标投标监督管理意见>>的通知'), 1, 1, 3, 'fg', NULL, NULL, 0, 0, '重庆市人民政府办公厅', '渝办发〔2000〕37号', 0, 0, 0, 0, 0, concat('content/checkProjectDetail.html?id=',id), 'http://www.cebpubservice.com/ctpsp_policylaw/jsp/zz/policies_info.jsp?documentId=daafd2820cf24f76812f9512b244b447', NULL, 'wjlx_6 wjzt_2 dqfw_50', NULL, 0, 1, 1, 2, now(), NULL, NULL, NULL, now(), 0, 1, 0);
        INSERT INTO `cms_content_attribute`(`content_id`, `source`, `source_url`, `data`, `search_text`, `text`, `word_count`) VALUES (id, NULL, NULL, NULL, '搜索内容', '内容', 800);
        set id = id + 1;
        SET num = num - 1;
    END WHILE;
END

#执行存储过程
CALL init_fg_data();

#删除存储过程

DROP PROCEDURE if EXISTS init_fg_data;

#创建存储过程

CREATE PROCEDURE init_fg_data()

BEGIN

declare id int default 2200000;

declare num int default 4000000;

while num > 0 DO

INSERT INTO `cms_content`(`id`, `site_id`, `title`, `user_id`, `check_user_id`, `category_id`, `model_id`, `parent_id`, `quote_content_id`, `copied`, `contribute`, `author`, `editor`, `only_url`, `has_images`, `has_files`, `has_products`, `has_static`, `url`, `description`, `tag_ids`, `dictionary_values`, `cover`, `childs`, `scores`, `comments`, `clicks`, `publish_date`, `expiry_date`, `check_date`, `update_date`, `create_date`, `sort`, `status`, `disabled`) VALUES (id, 1, CONCAT(id,'重庆市人民政府办公厅发重庆市人民政府办公厅转发市建委关于贯彻<<中华人民共和国招标法>>严格建设工程招标投标监督管理意见>>的通知'), 1, 1, 3, 'fg', NULL, NULL, 0, 0, '重庆市人民政府办公厅', '渝办发〔2000〕37号', 0, 0, 0, 0, 0, concat('content/checkProjectDetail.html?id=',id), 'http://www.cebpubservice.com/ctpsp_policylaw/jsp/zz/policies_info.jsp?documentId=daafd2820cf24f76812f9512b244b447', NULL, 'wjlx_6 wjzt_2 dqfw_50', NULL, 0, 1, 1, 2, now(), NULL, NULL, NULL, now(), 0, 1, 0);

INSERT INTO `cms_content_attribute`(`content_id`, `source`, `source_url`, `data`, `search_text`, `text`, `word_count`) VALUES (id, NULL, NULL, NULL, '搜索内容', '内容', 800);

set id = id + 1;

SET num = num - 1;

END WHILE;

END

#执行存储过程

CALL init_fg_data();

删除无副表的主表数据

SELECT
	* 
FROM
	cms_content 
WHERE
	( SELECT COUNT( 1 ) AS num FROM cms_content_attribute WHERE cms_content.id = cms_content_attribute.content_id )= 0;
DELETE 
FROM
	cms_content 
WHERE
	( SELECT COUNT( 1 ) AS num FROM cms_content_attribute WHERE cms_content.id = cms_content_attribute.content_id )= 0;

SELECT

FROM

cms_content

WHERE

( SELECT COUNT( 1 ) AS num FROM cms_content_attribute WHERE cms_content.id = cms_content_attribute.content_id )= 0;

DELETE

FROM

cms_content

WHERE

( SELECT COUNT( 1 ) AS num FROM cms_content_attribute WHERE cms_content.id = cms_content_attribute.content_id )= 0;

删除无主表的副表数据

SELECT
	* 
FROM
	cms_content_attribute 
WHERE
	( SELECT COUNT( 1 ) AS num FROM cms_content WHERE cms_content.id = cms_content_attribute.content_id )= 0;
DELETE 
FROM
	cms_content_attribute 
WHERE
	( SELECT COUNT( 1 ) AS num FROM cms_content WHERE cms_content.id = cms_content_attribute.content_id )= 0;

SELECT

FROM

cms_content_attribute

WHERE

( SELECT COUNT( 1 ) AS num FROM cms_content WHERE cms_content.id = cms_content_attribute.content_id )= 0;

DELETE

FROM

cms_content_attribute

WHERE

( SELECT COUNT( 1 ) AS num FROM cms_content WHERE cms_content.id = cms_content_attribute.content_id )= 0;

清理数据表碎片

#该操作执行的时候会把该表格先写入一个tmp临时表，所以磁盘剩余空间必须大于[表空间]否则会执行失败。
alter table 表名 engine = innodb;

#该操作执行的时候会把该表格先写入一个tmp临时表，所以磁盘剩余空间必须大于[表空间]否则会执行失败。

alter table 表名 engine = innodb;

优化表空间(释放空间)

#创建数据库的时候设置 innodb_file_per_table，这样 InnoDB 会对每个表创建一个数据文件，然后只需要运行 OPTIMIZE TABLE 命令就可以释放所有已经删除的磁盘空间。my.cnf 在 innodb 段中加入 innodb_file_per_table=1 # 1 为启用，0 为禁用，以下为查看该变量的值
show variables like '%per_table%';
#使用 optimize table。被删除的记录被保持在链接清单中，后续的 insert 操作会重新使用旧的记录位置。
optimize table 表名 ;

#创建数据库的时候设置 innodb_file_per_table，这样 InnoDB 会对每个表创建一个数据文件，然后只需要运行 OPTIMIZE TABLE 命令就可以释放所有已经删除的磁盘空间。my.cnf 在 innodb 段中加入 innodb_file_per_table=1 # 1 为启用，0 为禁用，以下为查看该变量的值

show variables like '%per_table%';

#使用 optimize table。被删除的记录被保持在链接清单中，后续的 insert 操作会重新使用旧的记录位置。

optimize table 表名 ;

清理重复数据

# 查询有重复的数据
select count(id),cms_content.* from cms_content WHERE model_id='xm' group by title,category_id,model_id having count(id)>1 order by id;

# 删除有重复的数据
delete from cms_content where id not in ( select max_id from ( select MAX(id) as max_id from cms_content WHERE model_id='xm' group by title,category_id,model_i ) as tmp );

# 查询重复数据
SELECT * FROM student WHERE id NOT IN( SELECT t.id FROM ( SELECT MIN( id) AS id FROM student GROUP BY `name` ) t )

# 删除重复数据
DELETE FROM student WHERE NAME IN( SELECT t.NAME FROM ( SELECT NAME FROM student GROUP BY NAME HAVING count( 1) > 1 ) t)

# 查询有重复的数据

select count(id),cms_content.* from cms_content WHERE model_id='xm' group by title,category_id,model_id having count(id)>1 order by id;

# 删除有重复的数据

delete from cms_content where id not in ( select max_id from ( select MAX(id) as max_id from cms_content WHERE model_id='xm' group by title,category_id,model_i ) as tmp );

# 查询重复数据

SELECT * FROM student WHERE id NOT IN( SELECT t.id FROM ( SELECT MIN( id) AS id FROM student GROUP BY `name` ) t )

# 删除重复数据

DELETE FROM student WHERE NAME IN( SELECT t.NAME FROM ( SELECT NAME FROM student GROUP BY NAME HAVING count( 1) > 1 ) t)

为日期增加一个时间间隔

now() //now函数为获取当前时间

select date_add(now(), interval 1 day); – 加1天

select date_add(now(), interval 1 hour); -加1小时

select date_add(now(), interval 1 minute); – 加1分钟

select date_add(now(), interval 1 second); -加1秒

select date_add(now(), interval 1 microsecond);-加1毫秒

select date_add(now(), interval 1 week);-加1周

select date_add(now(), interval 1 month);-加1月

select date_add(now(), interval 1 quarter);-加1季

select date_add(now(), interval 1 year);-加1年

MySQL adddate(), addtime()函数，可以用date_add() 来替代。

为日期减去一个时间间隔

MySQL date_sub() 日期时间函数和date_add() 用法一致。

MySQL 中subdate(),subtime()函数，建议，用date_sub()来替代。

举例说明，我有一张销售记录表，里面有一个销售日期字段（xsrq），如果我想查询最近一周的销售记录

SELECT * FROM XSJL WHERE XSRQ >= DATE_ADD(NOW(),INTERVAL -7 day);

1 2	SELECT * FROM XSJL WHERE XSRQ >= DATE_ADD(NOW(),INTERVAL -7 day);

按周期统计不同分类数据记录

SELECT 
DATE_FORMAT(create_date,'%Y%u') `周数`,
sum(category_id=1) as `项目`,
sum(category_id=2) as `案例`,
sum(category_id=19) as `风险`,
sum(category_id=3) as `法规`,
sum(category_id in (10,12,13)) as `资讯` 
FROM cms_content 
WHERE has_static=1 and disabled=0 and status=1 and create_date>=DATE_ADD(NOW(),INTERVAL -9 week) and create_date<=DATE(NOW())
GROUP BY `周数`
ORDER BY `周数` DESC
LIMIT 0,10;

SELECT

DATE_FORMAT(create_date,'%Y%u') `周数`,

sum(category_id=1) as `项目`,

sum(category_id=2) as `案例`,

sum(category_id=19) as `风险`,

sum(category_id=3) as `法规`,

sum(category_id in (10,12,13)) as `资讯`

FROM cms_content

WHERE has_static=1 and disabled=0 and status=1 and create_date>=DATE_ADD(NOW(),INTERVAL -9 week) and create_date<=DATE(NOW())

GROUP BY `周数`

ORDER BY `周数` DESC

LIMIT 0,10;

数据总数统计

SELECT 
sum(category_id=1) as 项目,
sum(category_id=2) as 案例,
sum(category_id=19) as 风险,
sum(category_id=3) as 法规,
sum(category_id in (10,12,13)) as 资讯,
(SELECT COUNT(1) FROM sys_user WHERE superuser=0 and disabled=0 and dept_id IS NULL and registered_date>='2022-08-26') 用户
FROM cms_content 
WHERE has_static=1 and disabled=0 and status=1;

SELECT

sum(category_id=1) as 项目,

sum(category_id=2) as 案例,

sum(category_id=19) as 风险,

sum(category_id=3) as 法规,

sum(category_id in (10,12,13)) as 资讯,

(SELECT COUNT(1) FROM sys_user WHERE superuser=0 and disabled=0 and dept_id IS NULL and registered_date>='2022-08-26') 用户

FROM cms_content

WHERE has_static=1 and disabled=0 and status=1;

数据处理

/*
* 查询数据库正在运行的查询
*/
show full processlist;
kill {id}
/*
* 查询有重复的记录
*/
SELECT max(content_id),source_url FROM cms_content_attribute WHERE source_url<>"" GROUP BY source_url HAVING count(1) > 1;
/*
* 根据来源网址查询副表重复记录（重复字段添加索引）
*/
SELECT a.content_id,a.source_url FROM cms_content_attribute a WHERE EXISTS (
SELECT b.source_url FROM cms_content_attribute b WHERE b.source_url<> "" AND a.source_url=b.source_url GROUP BY b.source_url HAVING count(b.source_url)> 1) AND a.content_id NOT IN (
SELECT max(content_id) FROM cms_content_attribute WHERE source_url<> "" GROUP BY source_url HAVING count(1)> 1);
/*
* 根据来源网址查询主表重复记录
*/
SELECT id FROM cms_content WHERE id IN (
SELECT a.content_id FROM cms_content_attribute a WHERE EXISTS (
SELECT b.source_url FROM cms_content_attribute b WHERE b.source_url<> "" AND a.source_url=b.source_url GROUP BY b.source_url HAVING count(b.source_url)> 1) AND a.content_id NOT IN (
SELECT max(c.content_id) FROM cms_content_attribute c WHERE c.source_url<> "" GROUP BY c.source_url HAVING count(1)> 1));
/*
* 根据来源网址删除主表重复记录
*/
DELETE FROM cms_content WHERE id IN (
SELECT a.content_id FROM cms_content_attribute a WHERE EXISTS (
SELECT b.source_url FROM cms_content_attribute b WHERE b.source_url<> "" AND a.source_url=b.source_url GROUP BY b.source_url HAVING count(b.source_url)> 1) AND a.content_id NOT IN (
SELECT max(c.content_id) FROM cms_content_attribute c WHERE c.source_url<> "" GROUP BY c.source_url HAVING count(1)> 1));

/*
*删除无副表的主表数据
*/
#查询
SELECT*FROM cms_content WHERE (
SELECT COUNT(1) AS num FROM cms_content_attribute WHERE cms_content.id=cms_content_attribute.content_id)=0;
#删除
DELETE FROM cms_content WHERE (
SELECT COUNT(1) AS num FROM cms_content_attribute WHERE cms_content.id=cms_content_attribute.content_id)=0;
/*
*删除无主表的副表数据
*/
#查询
SELECT content_id FROM cms_content_attribute WHERE (
SELECT COUNT(1) AS num FROM cms_content WHERE cms_content.id=cms_content_attribute.content_id)=0;
#删除
DELETE FROM cms_content_attribute WHERE (
SELECT COUNT(1) AS num FROM cms_content WHERE cms_content.id=cms_content_attribute.content_id)=0;

* 查询数据库正在运行的查询

show full processlist;

kill {id}

* 查询有重复的记录

SELECT max(content_id),source_url FROM cms_content_attribute WHERE source_url<>"" GROUP BY source_url HAVING count(1) > 1;

* 根据来源网址查询副表重复记录（重复字段添加索引）

SELECT a.content_id,a.source_url FROM cms_content_attribute a WHERE EXISTS (

SELECT b.source_url FROM cms_content_attribute b WHERE b.source_url<> "" AND a.source_url=b.source_url GROUP BY b.source_url HAVING count(b.source_url)> 1) AND a.content_id NOT IN (

SELECT max(content_id) FROM cms_content_attribute WHERE source_url<> "" GROUP BY source_url HAVING count(1)> 1);

* 根据来源网址查询主表重复记录

SELECT id FROM cms_content WHERE id IN (

SELECT a.content_id FROM cms_content_attribute a WHERE EXISTS (

SELECT b.source_url FROM cms_content_attribute b WHERE b.source_url<> "" AND a.source_url=b.source_url GROUP BY b.source_url HAVING count(b.source_url)> 1) AND a.content_id NOT IN (

SELECT max(c.content_id) FROM cms_content_attribute c WHERE c.source_url<> "" GROUP BY c.source_url HAVING count(1)> 1));

* 根据来源网址删除主表重复记录

DELETE FROM cms_content WHERE id IN (

SELECT a.content_id FROM cms_content_attribute a WHERE EXISTS (

SELECT b.source_url FROM cms_content_attribute b WHERE b.source_url<> "" AND a.source_url=b.source_url GROUP BY b.source_url HAVING count(b.source_url)> 1) AND a.content_id NOT IN (

SELECT max(c.content_id) FROM cms_content_attribute c WHERE c.source_url<> "" GROUP BY c.source_url HAVING count(1)> 1));

*删除无副表的主表数据

#查询

SELECT*FROM cms_content WHERE (

SELECT COUNT(1) AS num FROM cms_content_attribute WHERE cms_content.id=cms_content_attribute.content_id)=0;

#删除

DELETE FROM cms_content WHERE (

SELECT COUNT(1) AS num FROM cms_content_attribute WHERE cms_content.id=cms_content_attribute.content_id)=0;

*删除无主表的副表数据

#查询

SELECT content_id FROM cms_content_attribute WHERE (

SELECT COUNT(1) AS num FROM cms_content WHERE cms_content.id=cms_content_attribute.content_id)=0;

#删除

DELETE FROM cms_content_attribute WHERE (

SELECT COUNT(1) AS num FROM cms_content WHERE cms_content.id=cms_content_attribute.content_id)=0;

配置远程用户连接

# 登录mysql
mysql -uroot -p
# 打开mysql库
use mysql;
# 查询用户表详情
select `user`,authentication_string,`Host` from `user`;
# 创建远程root用户，注意设置password
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
#mysql8.0创建+赋权
CREATE USER 'root'@'%' IDENTIFIED BY '4e81943bfa23f8a5';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
GRANT PROCESS, RELOAD, SHUTDOWN, SUPER ON *.* TO 'root'@'%';


# 重载用户权限表
FLUSH PRIVILEGES;

# 登录mysql

mysql -uroot -p

# 打开mysql库

use mysql;

# 查询用户表详情

select `user`,authentication_string,`Host` from `user`;

# 创建远程root用户，注意设置password

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

#mysql8.0创建+赋权

CREATE USER 'root'@'%' IDENTIFIED BY '4e81943bfa23f8a5';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

GRANT PROCESS, RELOAD, SHUTDOWN, SUPER ON *.* TO 'root'@'%';

# 重载用户权限表

FLUSH PRIVILEGES;

转载请注明：追风逐雨 » 常用MySQL语句汇总

常用MySQL语句汇总

新增

查询

修改

删除

MySQL表达式函数

存储过程操作

删除无副表的主表数据

删除无主表的副表数据

清理数据表碎片

优化表空间(释放空间)

清理重复数据

为日期增加一个时间间隔

为日期减去一个时间间隔

按周期统计不同分类数据记录

数据总数统计

数据处理

配置远程用户连接

与本文相关的文章

您必须登录才能发表评论！

新增

查询

修改

删除

MySQL表达式函数

存储过程操作

删除无副表的主表数据

删除无主表的副表数据

清理数据表碎片

优化表空间(释放空间)

清理重复数据

为日期增加一个时间间隔

为日期减去一个时间间隔

按周期统计不同分类数据记录

数据总数统计

数据处理

配置远程用户连接

与本文相关的文章

您必须 登录 才能发表评论！

您必须登录才能发表评论！