MySQL操作大全

目录

基础操作

基本命令

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. 在my.cnf中添加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;

数据库服务基础配置my.cnf

1、不区分大小写

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

lower_case_table_names=1

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

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

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

[mysqld]
max_hea