MySQL数据库常用SQL集合

董方糖

已于 2023-03-22 16:57:36 修改

阅读量140

点赞数

分类专栏： DBA 文章标签：数据库 mysql sql

于 2023-01-31 15:15:09 首次发布

本文链接：https://blog.csdn.net/qq_44179756/article/details/128817895

版权

DBA 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

一，常用基础SQL

数据库容量

一，常用基础SQL

查看数据库版本

select version();

登陆数据库

mysql -u queuecloud -p

linux执行SQL

source 路径/datebase.sql(SQL文件)

show databases;（查看数据库）
show tables;（查看选中数据库下的所有表）
use mysql(使用数据库);

数据库打开防火墙端口3306

firewall-cmd --zone=public --add-port=3306/tcp --permanent

查询用户(表)详细信息

select Host,User,Create_priv,password_last_changed from user;

查看mysql.db表指定用户的库权限

select Host, Db ,User from mysql.db where User='zhangshuai';

查看用户对单个数据列的权限

select Host,User from mysql.columns_priv where User like 'zhangshuai';

查看用户对单个表的权限

select Host,User from mysql.tables_priv where User like 'zhangshuai';

查询数据库用户网段

select user,host from mysql.db where user='zzzd';

查看用户权限

#show grants for 用户@'网段';
show grants for zhzcdb@'10.253.163.%';

增加用户权限

#grant 权限级别 on `库名` . * to '用户名'@'用户访问范围';
grant select,insert,update,delete,create on `db_zzd` . * to `username`@'10.209.xx.%';

revoke撤销用户权限

#revoke..alter on..from
revoke insert,select,update,delete,drop，create，alter on huanqiu.* from wang@'%'; 
#revoke..from 
revoke SELECT,INSERT,UPDATE,DELETE,CREATE ON `zzzddb`.*  from 'zhzcdb'@'10.253.163.%';

mysql刷新权限，提交命令

FLUSH PRIVILEGES; #flush privileges;权限刷新
commit;#提交

修改用户访问IP范围

#rename user ..to
rename user zhzcdb@'%' to zhzcdb@'10.253.163.%';

修改数据库用户密码

UPDATE user SET Password = password ( 'new-password' ) WHERE User = 'root' ; 
#5.7版本以下
update mysql.user set authentication_string=password('密码') where User="用户" and Host="10.209.6.%";

数据库备份

数据库备份恢复：mysql -u root -p test</home/test1.sql
数据库备份(忽略gtid信息)：mysqldump -u root -p --set-gtid-purged=OFF userdb > userdb.sql

单库备份：mysqldump -uroot -p test >/download/testbak_$(date +%F).sql
压缩备份：mysqldump -uroot -p -B test|gzip >/download/testbak_$(date +%F).sql.gz
多库备份；mysqldump -uroot -p -B test mysql|gzip >/download/testbak_$(date +%F).sql01.gz

单表备份：mysqldump -uroot -p -B test test >/download/test_testbak_$(date +%F).sql

备份脚本

#!/bin/sh

##########################################

#this scripts create by root of mingongge

#create at 2016-11-11

#######################################

ip=`grep 'IPADDR' /etc/sysconfig/network-scripts/ifcfg-eth0|awk -F "=" '{print $2}'`

#定义服务器IP变量

BAKDIR=/backup

#定义备份路径

[ ! -d $BAKDIR/${ip} ] && mkdir -p $BAKDIR/${ip}

 #判断如果不存在这个路径就创建一个，为了服务器多的时候方便看

DB_PWD="mingongge"

DB_USER="root"

MYSQL="/application/mysql/bin/mysql"

MYSQL_DUMP="/application/mysql/bin/mysqldump"

DATA=`date +%F`

####bak data of test's databses####

DB_NAME=`$MYSQL -u$DB_USER -p$DB_PWD -e "show databases;"|sed '1,5d'`

 #定义数据库变量

for name in $DB_NAME

#for循环语句取库名

do

 $MYSQL_DUMP -u$DB_USER -p$DB_PWD -B ${name} |gzip >$BAKDIR/${ip}/${name}_$DATA.sql.gz

 #全库备份

[ ! -d $BAKDIR/${ip}/${name} ] && mkdir -p $BAKDIR/${ip}/${name}

#判断这个路径，为了区别哪个库的备份文件

for tablename in `$MYSQL -u$DB_USER -p$DB_PWD -e "show tables from ${name};"|sed '1d'`

#for循环语句取表名

 do

  $MYSQL_DUMP -u$DB_USER -p$DB_PWD ${name} ${tablename} |gzip >$BAKDIR/${ip}/${name}/${tablename}_$DATA.sql.gz

#分表备份
 done
done

参考：MySql数据库备份的几种方式 - KillerTwo - 博客园

查看存储过程函数

查询数据库中的存储过程和函数
select `name` from mysql.proc where db = 'xx' and `type` = 'PROCEDURE'   //存储过程
select `name` from mysql.proc where db = 'xx' and `type` = 'FUNCTION'   //函数
show procedure status; //存储过程
show function status;     //函数
查看存储过程或函数的创建代码
show create procedure proc_name;
show create function func_name;

查看视图

SELECT * from information_schema.VIEWS   //视图
SELECT * from information_schema.TABLES   //表

查看触发器

SHOW TRIGGERS [FROM db_name] [LIKE expr]
SELECT * FROM triggers T WHERE trigger_name=”mytrigger” \G

检查是否开启binlog

#查看mysql的配置文件/etc/my.cnf
show variables like '%log_bin%';
# 查看详细的日志配置信息
show global variables like '%log%';
# mysql数据存储目录
show variables like '%dir%';
# 查看binlog的目录
show global variables like "%log_bin%";
# 查看当前服务器使用的biglog文件及大小
show binary logs;
# 查看主服务器使用的biglog文件及大小
# 查看最新一个binlog日志文件名称和Position
show master status;
# 事件查询命令
# IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件)
# FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
# LIMIT [offset,] ：偏移量(不指定就是0)
# row_count ：查询总条数(不指定就是所有行)
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
# 查看 binlog 内容
show binlog events;
# 查看具体一个binlog文件的内容 （in 后面为binlog的文件名）
show binlog events in 'master.000003';
# 设置binlog文件保存事件，过期删除，单位天
set global expire_log_days=3; 
# 删除当前的binlog文件
reset master; 
# 删除slave的中继日志
reset slave;
# 删除指定日期前的日志索引中binlog日志文件
purge master logs before '2019-03-09 14:00:00';
# 删除指定日志文件
purge master logs to 'master.000003';

参考：mysql查看binlog日志 - 沧海一滴 - 博客园

返回表的详细信息

show columns from 表名;

创建/删除数据库

create database 数据库名称 ;(注意分号结尾)
drop database old_db;

创建数据库用户

grant all privileges on 数据库名称.* to 数据库用户名@'授权范围' identified by '数据库用户名密码';
grant all privileges on wxactivity.* to mysqluser@'%' identified by 'mysqluseradmin';（这是例子，%代表授权全网，localhost代表授权本机）

修改数据库表字段

ALTER TABLE 表名 MODIFY COLUMN 字段名  数据类型(修改后的长度);

新增默认为空的字段

ALTER TABLE 表名 ADD COLUMN 字段名 字段类型 DEFAULT NULL;

新增不为空的字段

ALTER TABLE 表名ADD COLUMN 字段名 字段类型  NOT NULL;

删除字段

ALTER TABLE 表名 DROP COLUMN 字段名;

修改表名

rename table 原表名 to 新表名;
#使用RENAME TABLE命令修改表名，将表移动到新的库里
rename table old_db.tb to new_db.tb;

参考：安全快速修改Mysql数据库名的5种方法 - 星朝 - 博客园

数据库容量

查看所有数据库容量大小

select 
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

查看所有数据库各表容量大小

select 
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

查看指定数据库容量大小

#例：查看mysql库容量大小
select 
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql';

查看指定数据库各表容量大小

#例：查看mysql库各表容量大小
select 
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql'
order by data_length desc, index_length desc;

mysql查看当前的字符编码方法

show variables like'character%';

mysql删除某个用户

DROP USER 'linuxidc'@'localhost';

mysql删除某个库中的所有表

SELECT concat('DROP TABLE IF EXISTS ', table_name, ';') FROM information_schema.tables WHERE table_schema = 'databasesname';

查单个表空间情况

select df.BYTES/1024/1024/1024,df.* from dba_data_files df where df.TABLESPACE_NAME='CPOE_DATA'

查看表空间使用情况

select a.tablespace_name as "表空间名","最大空间(GB)","占用空间(GB)",("占用空间(GB)"-"剩余空间(GB)") as "使用空间(GB)",round(("占用空间(GB)"-"剩余空间(GB)")/"占用空间(GB)"*100,2) as "使用率1(%)"
,round(("占用空间(GB)"-"剩余空间(GB)")/"最大空间(GB)"*100,2) as "使用率2(%)"
from (select tablespace_name,sum(decode(AUTOEXTENSIBLE,'NO',bytes,'YES',maxbytes))/1024/1024/1024 as "最大空间(GB)",sum(bytes)/1024/1024/1024 as "占用空间(GB)" from dba_data_files group by tablespace_name) a
,(select tablespace_name,sum(bytes)/1024/1024/1024 as "剩余空间(GB)" from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name order by 6 desc;