MySQL学习心得和技巧运用

田钧元

已于 2024-06-17 23:15:48 修改

阅读量534

点赞数 11

文章标签： mysql 数据库

于 2024-06-17 23:02:06 首次发布

本文链接：https://blog.csdn.net/2401_84154561/article/details/139756323

版权

MySQL作为一种开源的关系型数据库管理系统，不仅在性能上表现出色，而且拥有广泛的用户群体和丰富的资源支持。

在mysql的学习中，也整理了一些小技巧分享给大家

一：实用的编程技巧和方法

1.QL语句的简化：

通过合理地使用SQL语句的语法和函数，可以大大简化查询语句的编写。例如，使用JOIN语句可以一次性查询多个表中的数据，避免了多次查询和数据的冗余；使用聚合函数和分组查询可以快速地统计和分析数据；使用子查询和条件语句可以实现复杂的查询需求等。

2.存储过程和函数的运用：

MySQL支持自定义的存储过程和函数，这为我们封装复杂的业务逻辑提供了便利。我尝试将一些常用的查询操作封装成存储过程或函数，并在需要时直接调用它们，这不仅提高了代码的复用性，也减少了代码量和维护成本。

3.事物的处理：

事务是数据库编程中非常重要的一部分。我学习了如何使用MySQL的事务管理功能来确保数据的完整性和一致性。在涉及多个表或行的更新操作时，我使用事务来确保这些操作要么全部成功执行，要么全部回滚到操作前的状态，从而避免了数据的不一致性和冲突。

4.备份与恢复：

数据库的备份与恢复是保障数据安全的重要手段。我学习了MySQL的备份工具和方法，并尝试定期备份数据库以防范数据丢失的风险。同时，我也了解了数据恢复的方法和步骤，以便在数据发生意外时能够尽快地恢复数据。

二：查询技巧代码

1.char_length

有时候我们需要获取字符的长度，然后根据字符的长度进行排序。MYSQL给我们提供了一些有用的函数，比如：char_length。通过该函数就能获取字符长度。

例如：获取字符长度并且排序的sql如下：

SELECT char_length('Hello World');

运行结果：

2.replace

我们经常会有替换字符串中部分内容的需求，比如：将字符串中的字符A替换成B。

这种情况就能使用replace函数。

例如：

这样就能轻松实现字符替换功能。

也能用该函数去掉前后空格：

使用该函数还能替换json格式的数据内容，真的非常有用。

3.now

时间是个好东西，用它可以快速缩小数据范围，我们经常有获取当前时间的需求。

在MYSQL中获取当前时间，可以使用now()函数，例如：

SELECT NOW() AS current_time;

4.insert into ... select

在工作中很多时候需要插入数据。

传统的插入数据的sql是这样的：

它主要是用于插入少量并且已经确定的数据。但如果有大批量的数据需要插入，特别是是需要插入的数据来源于，另外一张表或者多张表的结果集中。

这种情况下，使用传统的插入数据的方式，就有点束手无策了。

这时候就能使用MYSQL提供的：insert into ... select语法。

列入：

5.on duplicate key update

通常情况下，我们在插入数据之前，一般会先查询一下，该数据是否存在。如果不存在，则插入数据。如果已存在，则不插入数据，而直接返回结果。

在没啥并发量的场景中，这种做法是没有什么问题的。但如果插入数据的请求，有一定的并发量，这种做法就可能会产生重复的数据。

当然防止重复数据的做法很多，比如：加唯一索引、加分布式锁等。

但这些方案，都没法做到让第二次请求也更新数据，它们一般会判断已经存在就直接返回了。

这种情况可以使用on duplicate key update语法。

该语法会在插入数据之前判断，如果主键或唯一索引不存在，则插入数据。如果主键或唯一索引存在，则执行更新操作。
具体需要更新的字段可以指定，例如：

这样一条语句就能轻松搞定需求，既不会产生重复数据，也能更新最新的数据。

但需要注意的是，在高并发的场景下使用on duplicate key update语法，可能会存在死锁的问题，所以要根据实际情况酌情使用。

6.show create table

有时候，我们想快速查看某张表的字段情况，通常会使用desc命令，比如：

运行结果：

确实能够看到order表中的字段名称、字段类型、字段长度、是否允许为空，是否主键、默认值等信息。

但看不到该表的索引信息，如果想看创建了哪些索引，该怎么办呢？

使用show index命令。

例如：

也能查出该表所有的索引：

但查看字段和索引数据呈现方式，总觉得有点怪怪的，有没有一种更直观的方式？

使用show create table命令方式。

例如：

其中Table表示表名，Create Table就是我们需要看的建表信息，将数据展开：我们能够看到非常完整的建表语句，表名、字段名、字段类型、字段长度、字符集、主键、索引、执行引擎等都能看到。

非常直接明了。

7.create table ... select

有时候，我们需要快速备份表。

通常情况下，可以分两步走：

创建一张临时表

将数据插入临时表

创建临时表可以使用命令：

创建成功之后，就会生成一张名称叫：order_2022121819，表结构跟order一模一样的新表，只是该表的数据为空而已。

接下来使用命令：

执行之后就会将order表的数据插入到order_2022121819表中，也就是实现数据备份的功能。

但有没有命令，一个命令就能实现上面这两步的功能呢？用create table ... select命令。

使用create table ... select命令。

例如：

执行完之后，就会将order_2022121820表创建好，并且将order表中的数据自动插入到新创建的order_2022121820中。

以上就是些查询技巧代码

三：MySQL数据库的常用命令：

连接数据库

本地的：

mysql -u root -pXXX -P 3306 -D test

远程的加-h选项：

mysql -u admin -pXXXX -h XX.XX.XX.235 -P 3306 -D testdb

-D后跟数据库名。

查看版本

select version();

查看DB

show databases;

创建DB

create database testdb;

root用户赋予所有权限并允许从任意机器登录：

GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "密码";
flush privileges;

注意：IDENTIFIED BY后跟的是root用户的密码

创建用户并给用户授权

CREATE USER 'ares'@'%' IDENTIFIED BY '密码';
GRANT ALL ON aresdb.* TO 'ares'@'%';
flush privileges;

查看用户

select * from mysql.user

忘记用户密码

如果已登录但不记得用户密码，可使用如下语句修改密码：

update mysql.user set authentication_string=password('新密码') where user='root' and Host ='localhost';
flush privileges;

创建DB的用户，仅允许从本地登录

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON sdnctl.* TO sdnctl@localhost IDENTIFIED BY 'gamma';
flush privileges;

创建DB的用户，允许从任意机器登录

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON sdnctl.* TO sdnctl@'%' IDENTIFIED BY 'gamma';

flush privileges;

查看表

show tables;

四：MySQL函数介绍

在此，先准备测试数据，代码如下：

-- 创建数据库
DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb;
USE mydb;

-- 创建student表
CREATE TABLE student (
    sid CHAR(6),
    sname VARCHAR(50),
    age INT,
    gender VARCHAR(50) DEFAULT 'male'
);

-- 向student表插入数据
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1001', 'lili', 14, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1002', 'wang', 15, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1003', 'tywd', 16, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1004', 'hfgs', 17, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1005', 'qwer', 18, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1006', 'zxsd', 19, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1007', 'hjop', 16, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1008', 'tyop', 15, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1009', 'nhmk', 13, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1010', 'xdfv', 17, 'female');

1.聚合函数

在开发中，我们常常有类似的需求：统计某个字段的最大值、最小值、平均值等等。为此，MySQL中提供了聚合函数来实现这些功能。所谓聚合，就是将多行汇总成一行；其实，所有的聚合函数均如此——输入多行，输出一行。聚合函数具有自动滤空的功能，若某一个值为NULL，那么会自动将其过滤使其不参与运算。
聚合函数使用规则：
只有SELECT子句和HAVING子句、ORDER BY子句中能够使用聚合函数。例如，在WHERE子句中使用聚合函数是错误的。
接下来，我们学习常用聚合函数。

1、count（）
统计表中数据的行数或者统计指定列其值不为NULL的数据个数
查询有多少该表中有多少人
MySQL命令：

select count(*) from student;

运行效果展示：

2、max（）
计算指定列的最大值，如果指定列是字符串类型则使用字符串排序运算

查询该学生表中年纪最大的学生
MySQL命令：

select max(age) from student;

运行效果展示：

3、min（）
计算指定列的最小值，如果指定列是字符串类型则使用字符串排序运算

查询该学生表中年纪最小的学生 MySQL命令：

select sname,min(age) from student;

运行效果展示：

4、sum（）
计算指定列的数值和，如果指定列类型不是数值类型则计算结果为0
查询该学生表中年纪的总和 MySQL命令：

select sum(age) from student;

运行效果展示：

5、avg（）
计算指定列的平均值，如果指定列类型不是数值类型则计算结果为

查询该学生表中年纪的平均数 MySQL命令：

select avg(age) from student;

运行效果展示：

2.其他常用函数

SELECT NOW();
SELECT DAY (NOW());
SELECT DATE (NOW());
SELECT TIME (NOW());
SELECT YEAR (NOW());
SELECT MONTH (NOW());
SELECT CURRENT_DATE();
SELECT CURRENT_TIME();
SELECT CURRENT_TIMESTAMP();
SELECT ADDTIME('14:23:12','01:02:01');
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);
SELECT DATE_SUB(NOW(),INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(),INTERVAL 1 MONTH);
SELECT DATEDIFF('2019-07-22','2019-05-05');

以上就是我分析的一些mysql心得技巧