MySQL命令集

HUNIAN-DAJI

已于 2023-03-29 22:23:51 修改

阅读量81

点赞数

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

于 2023-03-29 22:20:41 首次发布

本文链接：https://blog.csdn.net/m0_67244660/article/details/129846165

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL

MySQL是一种开源的关系型数据库管理系统（RDBMS），是最流行的关系系型数据库之一，被广泛应用于Web应用程序和企业级软件开发中。 MySQL还有丰富的工具和驱动程序，包括命令行客户端、图形化界面、JDBC和ODBC驱动程序等，可以方便地进行数据库管理和应用程序开发。MySQL的语法类似于SQL标准，可以进行数据的增删改查、表结构设计、索引优化等操作。同时它也支持多种存储引擎，如InnoDB、MyISAM等，可以根据不同需求选择合适的存储引擎。总之，MySQL是一款功能强大、性能高效、易用可靠的关系型数据库管理系统，广泛应用于Web开发、企业信息化等领域。

一、MySQL的一些特点和功能？

开源免费：MySQL使用GPL许可证，可以免费使用、修改和分发，降低了系统成本。
多平台支持：MySQL能够运行在多个操作系统上，包括Linux、Windows、macOS等。
高性能：MySQL采用了多种优化技术，如索引、缓存、查询优化器等，具有较高的性能。
可扩展性：MySQL可以通过水平或垂直扩展来满足不断增长的数据需求。
安全性：MySQL提供了多种安全机制，包括用户权限控制、加密连接、防火墙等，保障数据库的安全性。
数据复制和备份：MySQL支持主从复制机制和多种备份方式，提高了系统的可用性和数据恢复能力。
支持存储过程和触发器：MySQL支持存储过程和触发器，可以实现更复杂的业务逻辑和数据处理任务。

二、使用步骤

1.数据库操作

创建数据库：

CREATE DATABASE test_db;

列出所有数据库：

SHOW DATABASES;

切换到指定的数据库：

USE test_db;

删除指定的数据库：

DROP DATABASE test_db;

2.表操作

在MySQL中，表是数据存储和管理的基本单位。

创建表：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

查看表结构：

DESCRIBE users;

插入数据：

INSERT INTO users (id, name, age) VALUES (1, 'John', 30);

查询数据：

SELECT * FROM users;

更新数据：

UPDATE users SET age = 31 WHERE name = 'John';

删除数据：

DELETE FROM users WHERE age >= 30;

删除表：

DROP TABLE users;

3.索引操作

在MySQL中，索引可以提高查询效率。

创建单列索引：

CREATE INDEX idx_name ON users(name);

创建多列索引：

CREATE INDEX idx_name_age ON users(name, age);

查看表的索引信息：

SHOW INDEXES FROM users;

删除指定索引：

DROP INDEX idx_name ON users;

4.数据查询

基本查询：

SELECT * FROM users;

条件查询：

SELECT * FROM users WHERE age > 20;

排序查询：

SELECT * FROM users ORDER BY age DESC;

分组查询：

SELECT name, COUNT(*) FROM users GROUP BY name;

联合查询：

SELECT u.name, a.address FROM users u JOIN addresses a ON u.id = a.user_id;

5.数据操作

插入数据：

INSERT INTO users (id, name, age) VALUES (1, 'John', 30);

更新数据：

UPDATE users SET age = 31 WHERE name = 'John';

删除数据：

DELETE FROM users WHERE age >= 30;

6.系统函数

返回当前时间：

SELECT NOW();

返回指定时间段内的日期：

SELECT DATE_ADD(NOW(), INTERVAL 2 DAY);

返回字符串长度：

SELECT LENGTH('Hello, world!');

7.备份与恢复操作

MySQL提供了多种备份和恢复数据库的方式。

备份整个数据库：

mysqldump -u root -p test_db > /backup/test_db.sql

恢复整个数据库：

mysql -u root -p test_db < /backup/test_db.sql

备份指定表：

mysqldump -u root -p test_db users > /backup/users.sql

恢复指定表：

mysql -u root -p test_db < /backup/users.sql

8.用户管理

创建新用户：

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

删除用户：

DROP USER 'newuser'@'localhost';

修改用户密码：

ALTER USER 'newuser'@'localhost' IDENTIFIED BY 'newpassword';

授予用户权限：

GRANT ALL PRIVILEGES ON test_db.* TO 'newuser'@'localhost';

撤销用户权限：

REVOKE ALL PRIVILEGES ON test_db.* FROM 'newuser'@'localhost';

9.视图操作

在MySQL中，视图是一种虚拟表，它的内容是从基础表中获取的。

创建视图：

CREATE VIEW v_users AS SELECT name, age FROM users WHERE age >= 30;

查询视图：

SELECT * FROM v_users;

更新视图：

UPDATE v_users SET age = 31 WHERE name = 'John';

删除视图：

DROP VIEW v_users;

10.存储过程和函数

存储过程和函数是一组SQL语句的集合，可以被重复调用。

创建存储过程：

CREATE PROCEDURE sp_get_users()
BEGIN
    SELECT * FROM users;
END;

调用存储过程：

CALL sp_get_users();

创建函数：

CREATE FUNCTION f_get_user_name(id INT) RETURNS VARCHAR(50)
BEGIN
    DECLARE name VARCHAR(50);
    SELECT name INTO name FROM users WHERE id = id;
    RETURN name;
END;

调用函数：

SELECT f_get_user_name(1);

11.事务处理

事务是一组数据库操作，这些操作要么全部执行成功，要么全部失败回滚。

开始事务：

START TRANSACTION;

提交事务：

COMMIT;

回滚事务：

ROLLBACK;

12.外键约束

外键约束用于保证表与表之间数据的完整性和一致性。

创建带有外键约束的表：

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

添加外键约束：

ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);

删除外键约束：

ALTER TABLE orders DROP FOREIGN KEY fk_user_id;

13.触发器

触发器是一种特殊的存储过程，它可以在指定的事件发生后自动执行一些操作。

创建触发器：

CREATE TRIGGER tr_update_user_age AFTER UPDATE ON users FOR EACH ROW
BEGIN
    INSERT INTO user_logs (user_id, log_time, log_content)
    VALUES (NEW.id, NOW(), CONCAT('User ', NEW.name, ' age updated to ', NEW.age));
END;

删除触发器：

DROP TRIGGER tr_update_user_age;

14.分区表

分区表是将一张大表拆分成多个小表，可以提高查询效率。

创建分区表：

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    order_time TIMESTAMP,
    order_amount DECIMAL(10, 2),
    PARTITION BY RANGE (YEAR(order_time))
    (
        PARTITION p2020 VALUES LESS THAN (2021),
        PARTITION p2021 VALUES LESS THAN (2022),
        PARTITION p2022 VALUES LESS THAN MAXVALUE
    )
);

添加分区：

ALTER TABLE orders ADD PARTITION (PARTITION p2023 VALUES LESS THAN (2024));

删除分区：

ALTER TABLE orders DROP PARTITION p2023;

15.全文检索

全文检索可以在大量文本数据中快速查找指定的关键词。

创建全文索引：

CREATE FULLTEXT INDEX idx_content ON articles(content);

查询包含指定关键词的记录：

SELECT * FROM articles WHERE MATCH (content) AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);

16.JSON数据类型

MySQL支持存储和查询JSON格式的数据，可以方便地处理复杂的数据结构。

创建带有JSON字段的表：

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    price DECIMAL(10, 2),
    attributes JSON
);

插入JSON数据：

INSERT INTO products (id, name, price, attributes)
VALUES (1, 'Phone', 1000, '{"color": "black", "screen_size": 6}');

查询JSON数据：

SELECT attributes->>"$.color" AS color FROM products WHERE id = 1;

17.GIS数据类型

GIS是地理信息系统的缩写，MySQL支持存储和查询GIS数据类型。

创建带有GIS字段的表：

CREATE TABLE locations (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    coords POINT
);

插入GIS数据：

INSERT INTO locations (id, name, coords)
VALUES (1, 'New York City', POINT(40.7128, -74.0060));

查询距离最近的位置：

SELECT name, ST_DISTANCE(coords, POINT(41.49008, -71.312796)) AS distance
FROM locations ORDER BY distance LIMIT 1;

18.加密函数

MySQL提供了多种加密函数，可以在存储密码等敏感信息时进行加密操作。

使用MD5加密：

SELECT MD5('password');

使用SHA2加密：

SELECT SHA2('password', 256);

19.数据库性能优化

优化数据库性能是MySQL使用过程中必不可少的一环，常用的优化方法包括索引优化、查询优化和缓存优化等。

创建索引：

CREATE INDEX idx_name ON users(name);

EXPLAIN语句分析查询执行计划：

EXPLAIN SELECT * FROM users WHERE age > 20;

缓存查询结果：

SELECT SQL_CACHE * FROM users WHERE age > 20;

20.日志管理

MySQL可以记录各种日志，包括错误日志、查询日志、慢查询日志等，方便开发人员进行问题诊断和系统监控。

查看错误日志：

tail -f /var/log/mysql/error.log

开启查询日志：

SET GLOBAL general_log = 'ON';

查看慢查询日志：

SELECT * FROM mysql.slow_log;

21.多语言支持

MySQL支持多种语言和字符集，可以存储和处理不同语言的数据。

创建带有UTF-8字符集的表：

CREATE TABLE messages (
    id INT PRIMARY KEY,
    content VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

插入中文数据：

INSERT INTO messages (id, content) VALUES (1, '你好');

查询中文数据：

SELECT * FROM messages WHERE content = '你好';

22.安全管理

MySQL提供了多种安全措施，保护数据库免受攻击和数据泄露。

使用SSL连接：

GRANT USAGE ON *.* TO 'user'@'localhost' REQUIRE SSL;

开启审计日志：

SET global audit_log=ON;

使用密码复杂度检查插件：

INSTALL PLUGIN validate_password SONAME 'validate_password.so';

23.分布式架构

MySQL支持分布式架构，可以将数据分散存储在不同的节点上，提高系统的可扩展性和可靠性。

创建NDB集群：

ndb_mgm -e "CREATE -f /usr/local/mysql/bin/config.ini"

添加数据节点：

ndb_mgm -e "ADD NODE data2_host:2202";

查看集群状态：

ndb_mgm -e "SHOW";

24.高可用性

MySQL提供了多种高可用性解决方案，包括主从复制、半同步复制和Galera Cluster等。

配置主从复制：

# 在主节点上执行：
CHANGE MASTER TO 
    MASTER_HOST='master_host', 
    MASTER_USER='replication_user', 
    MASTER_PASSWORD='password', 
    MASTER_LOG_FILE='binlog.000001', 
    MASTER_LOG_POS=100;

# 在从节点上执行：
START SLAVE;

配置半同步复制：

# 在主节点上执行：
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

# 在从节点上执行：
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

# 在主节点上执行：
SET GLOBAL rpl_semi_sync_master_enabled = 1;

# 在从节点上执行：
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

配置Galera Cluster：

# 安装Galera插件
apt-get install galera-4 libmysqlclient-dev

# 配置集群节点IP地址
wsrep_cluster_address="gcomm://node1_ip,node2_ip,node3_ip"

# 启动第一个节点
mysqld --wsrep-new-cluster

# 启动其他节点
mysqld --wsrep_cluster_address=<cluster_address> --wsrep_node_name=<node_name>

25.NoSQL支持

MySQL也支持类似NoSQL的文档型数据库，如MongoDB等。

安装MySQL Document Store插件：

apt-get install mysql-router mysql-shell

初始化MySQL Shell：

mysqlsh

创建集合：

\c mydb
var myCollection = db.createCollection("my_collection");

插入文档：

myCollection.add({name: "John", age: 30});

查询数据：

myCollection.find("age >= :age", {age: 25}).toArray();

26.数据库备份和恢复

数据库备份和恢复是保障数据安全和业务连续性的重要手段。

备份整个数据库：

mysqldump -u root -p mydb > mydb_backup.sql

恢复整个数据库：

mysql -u root -p mydb < mydb_backup.sql

备份单个表：

mysqldump -u root -p mydb users > users_backup.sql

恢复单个表：

mysql -u root -p mydb < users_backup.sql

27.数据库迁移

在实际应用中，可能需要将数据从一个MySQL数据库迁移到另一个数据库或另一种存储方式。

导出数据为CSV文件：

SELECT * FROM users INTO OUTFILE '/var/lib/mysql-files/users.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

导入CSV文件到新的数据库：

LOAD DATA INFILE '/var/lib/mysql-files/users.csv' INTO TABLE users FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

使用mysqldump迁移整个数据库：

mysqldump -u root -p mydb | ssh user@new_server "mysql -u root -p mydb"

28.数据库性能监测

对MySQL数据库进行性能监测可以发现潜在问题并及时进行优化。

使用pt-query-digest分析查询日志：

pt-query-digest /var/log/mysql/mysql-slow.log > slow_query.log

使用MySQL Performance Schema查看系统性能指标：

SELECT event_name, COUNT_STAR, SUM_TIMER_WAIT FROM performance_schema.events_waits_summary_global_by_event_name;

使用SHOW PROFILE查看单个查询的性能指标：

SET PROFILING = 1;
SELECT * FROM users WHERE age > 20;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

总结

总之，MySQL是一款功能强大、性能高效、易用可靠的关系型数据库管理系统，广泛应用于Web开发、企业信息化等领域。