MySQL操作大全

admin管理员组

文章数量:1607999

目录

文章目录

• • 目录 @[toc]
  • 基础操作
  • • 基本命令
    • • 1、用命令登录MySql
      • 2、查看MySql版本
      • 3、显示数据库列表
      • 创建数据库
      • 4、选择数据库
      • 5、显示库中的数据表
      • 6、显示数据表的结构
      • 7、导出数据
      • 8、导入数据
      • 9、创建用户
      • 10、用户授权
      • 11、事件
      • • 11.1、查看事件是否开启
        • 11.2、设置当前事件开启
        • 11.3、设置事件在mysql启动时自动开启方法
        • 11.4、创建事件
        • 11.5、删除事件
        • 11.6、查看事件
        • 11.7、开启/关闭事件
      • 12、触发器
      • • 12.1、创建触发器
        • 12.2、删除触发器
        • 12.3、禁用触发器
        • 12.4、启用触发器
        • 12.5、查询触发器
      • 13、存储过程
      • • 13.1、创建存储过程
        • 13.2、删除存储过程
      • 14、表操作
      • • 14.1、创建表
        • 14.2、删除表
        • 14.3、增加列
        • 14.4、删除列
        • 14.5、查看表结构
        • 14.6、清空表
    • 数据库服务基础配置myf
    • • 1、不区分大小写
      • 2、MySql取消密码强度验证功能
      • 设置的临时表的大小小于结果集的大小
      • 设置缓存索引和数据的内存大小
      • 增加主从同步效率
      • 居住证系统优化效率
    • 数据库服务操作
    • • 1、重启mysqld服务
  • 性能优化
  • • 数据容量
    • • 1.查看所有数据库各容量大小
      • 2.查看所有数据库各表容量大小
      • 3.查看指定数据库容量大小
      • 4.查看指定数据库各表容量大小
    • 碎片整理方案
    • • 1.查看数据库中每个存在碎片的表
      • 2.查看指定表的碎片情况
      • 3.找到碎片化最严重的表
      • 4.清理碎片(回收空间)的方法
    • 问题排查
    • • 连接数清理
      • • 查看mysql连接进程列表
        • 查看mysql最大连接数
        • 查看当前使用的连接数
        • 设置禁触休息多少秒后清除连接
        • 杀掉空闲时间在600秒以上的链接，拼接得到kill语句
        • 杀掉处于某个状态的链接，拼接得到kill语句
        • 杀掉某个用户发起的链接，拼接得到kill语句
  • 安全策略
  • 特殊函数
  • • 字段或字符串拼接
    • • 1、CONCAT 函数
      • 2、CONCAT_WS 函数
      • 3、GROUP_CONCAT 函数
    • 字符串截取
    • • 1、从左开始截取字符串
      • 2、从右开始截取字符串
      • 3、截取特定长度的字符串
      • • 3.1、从字符串的第9个字符开始读取直至结束
        • 3.2、从字符串的第9个字符开始，只取3个字符
        • 3.3、从字符串的倒数第6个字符开始读取直至结束
        • 3.4、从字符串的倒数第6个字符开始读取，只取2个字符
      • 4、按关键字进行读取
      • • 4.1、截取第二个“.”之前的所有字符
        • 4.2、截取倒数第二个“.”之后的所有字符
        • 4.3、如果关键字不存在，则返回整个字符串
    • find_in_set函数的语法
    • • 1、基本操作
      • 2、find_in_set() 和 in 的区别
      • 3、应用场景
      • • 3.1、文章表type字段查询
        • 3.2、部门树查询，匹配当前节点及所有子节点
        • • 3.2.1 数据表字段说明
          • 3.2.2 匹配部门id或父id为100的数据
    • 时间转化函数
    • • 字符串转时间
      • 时间转字符串
  • 常用sql
  • • 其他操作
    • • 更新时间加1秒
      • 比较两个时间字段是否一致
    • 批量操作
    • • 批量插入
      • 批量更新
    • 多表关联操作
    • • 多表关联-删除多表
      • 多表关联-删除一张表
      • 多表关联-多表更新
  • 问题排查
  • • 触发器
    • • 触发器无法创建： #1419 - You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
      • 触发器无法创建： #1227 Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation
    • bash: mysqldump: command not found
    • • 解决方案1
      • 解决方案2

基础操作

基本命令

1、用命令登录MySql

[root@heyong ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 487032
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

假设远程主机的IP为：110.110.110.110，用户名为root,密码为abcd123。则键入以下命令：

mysql -h110.110.110.110 -uroot -pabcd123                       // 远程登录

2、查看MySql版本

select version();

3、显示数据库列表

show databases;

创建数据库

create database `settle` default character set utf8mb4 collate utf8mb4_unicode_ci;

4、选择数据库

use databasename;

5、显示库中的数据表

show tables;

6、显示数据表的结构

describe tabname(表名);

7、导出数据

mysqldump --opt test > mysql.test

即将数据库test数据库导出到mysql.test文件，后者是一个文本文件，如：

mysqldump -u root -p123456 --databases dbname > mysql.dbname

就是把数据库dbname导出到文件mysql.dbname中。

8、导入数据

mysqlimport -u root -p123456 < mysql.dbname

##导入sql文件
source /usr/app/project/reservation6.sql

9、创建用户

CREATE USER canal IDENTIFIED BY 'root'; 

#canal：用户账号
#root：用户密码

10、用户授权

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'%';

GRANT SYSTEM_VARIABLES_ADMIN on *.*  to 'root';
flush privileges;

11、事件

11.1、查看事件是否开启

SHOW VARIABLES LIKE 'event_scheduler'

11.2、设置当前事件开启

SET GLOBAL event_scheduler = 1; 
或
SET GLOBAL event_scheduler = ON;

11.3、设置事件在mysql启动时自动开启方法

1. 开启事件，通过动态参数修改：SET GLOBAL event_scheduler = ON;
2. 在myf中添加event_scheduler=ON。（如果没有添加的话，mysql重启事件又会回到原来的状态）

11.4、创建事件

--创建事件执行存储过程，
CREATE EVENT IF NOT EXISTS update_status_even
--必选，用于定义执行的时间和时间间隔
ON SCHEDULE EVERY 1 DAY STARTS '2022-11-01 00:10:00'
--可选，用于定义事件是否循环执行，即是一次执行还是永久执行，默认为一次执行，即 NOT PRESERVE
ON COMPLETION  PRESERVE  COMMENT '每天凌晨12:10点执行一次'
--DO 后面是要执行的SQL,可以是存储过程，也能是其他语句
DO CALL update_status() ;
 
--100秒执行一次
CREATE EVENT IF NOT EXISTS update_status_even
ON SCHEDULE EVERY '100' SECOND STARTS '2022-11-29 10:01:00'
ON COMPLETION  PRESERVE COMMENT '每隔100秒执行一次更新'
DO CALL update_status(); 

11.5、删除事件

--删除事件
DROP EVENT IF EXISTS e_test;

11.6、查看事件

--查看系统中所有的事件
SHOW EVENTS;
--查看事件的具体信息  EVENT_SCHEMA= 所在的数据库库名
SELECT * FROM information_schema.events WHERE EVENT_SCHEMA='db_name1' ; 

11.7、开启/关闭事件

--开启事件
alter event 事件名 on completion preserve enable;
--关闭事件
alter event 事件名 on completion preserve disable; 

12、触发器

12.1、创建触发器

CREATE TRIGGER `update_book_flow_trigger` AFTER UPDATE ON tab_book FOR EACH ROW BEGIN  
	IF NEW.state <> OLD.state THEN
		IF NEW.update_time IS NULL OR NEW.update_time = '' THEN
			SET new.update_time = NOW();
		END IF;
		INSERT INTO tab_book_flow (book_id, book_time, order_no, idcard, site_id, service_type, service_name, allocation_id, state, create_by, create_time)  
		VALUES (NEW.id, NEW.book_time, NEW.order_no, NEW.idcard, NEW.site_id, NEW.service_type, NEW.service_name, NEW.allocation_id, NEW.state, NEW.update_by, NEW.update_time);  
	END IF;
END;

12.2、删除触发器

DROP TRIGGER IF EXISTS `update_book_flow_trigger`;

12.3、禁用触发器

在某些情况下，可能想保留触发器的定义，但是不希望其在某个时间段内执行任何操作。这时可以使用DISABLE TRIGGER语句来禁用触发器。以下是禁用触发器的语法：

DISABLE TRIGGER trigger_name ON table_name;

其中，trigger_name是要禁用的触发器名称，table_name是触发器所属的表名。

12.4、启用触发器

当想要重新启用一个已经禁用的触发器时，可以使用ENABLE TRIGGER语句。以下是启用触发器的语法：

ENABLE TRIGGER trigger_name ON table_name;

其中，trigger_name是要启用的触发器名称，table_name是触发器所属的表名。

12.5、查询触发器

在删除触发器之前，需要先确认要删除的触发器名称。可以通过查询information_schema数据库下的TRIGGERS表来获取触发器列表，其中包含触发器的名称、所属的数据库、表、事件以及触发器类型等信息。

SELECT TRIGGER_NAME, EVENT_OBJECT_SCHEMA, EVENT_OBJECT_TABLE FROM information_schema.TRIGGERS;

13、存储过程

13.1、创建存储过程

13.2、删除存储过程

14、表操作

14.1、创建表

CREATE TABLE students (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    age INT NULL,
    PRIMARY KEY (id)
);

14.2、删除表

DROP TABLE table_name;

14.3、增加列

ALTER TABLE table_name
ADD column_name column_definition [FIRST|AFTER existing_column];

#例如：
ALTER TABLE sys_dept ADD residence_police_station VARCHAR(50);

14.4、删除列

ALTER TABLE table_name
DROP column_name;

#例如
ALTER TABLE sys_dept DROP residence_police_station;

14.5、查看表结构

DESCRIBE table_name;

14.6、清空表

TRUNCATE TABLE table_name;

数据库服务基础配置myf

1、不区分大小写

编辑查找到的myf文件，在myf文件中的[mysqld]下加入一行：

lower_case_table_names=1

2、MySql取消密码强度验证功能

#编辑配置文件：
$ sudo vi /etc/myf
#在文件末尾添加以下内容：
plugin-load=validate_password.so 
validate-password=OFF

设置的临时表的大小小于结果集的大小

[mysqld]
max_hea

本文标签： 操作大全mysql