MySQL

推荐链接

链接目录

查看数据库版本

SELECT VERSION();

函数

CONCAT（字符串连接）

SELECT CONCAT('Hello', ' ', 'World') AS result;
输出：Hello World

连接多个字符串：
SELECT CONCAT('I', ' ', 'like', ' ', 'MySQL') AS result;
输出：I like MySQL

连接字段和字符串：
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
上述示例中，first_name和last_name是表users中的两个列，CONCAT函数将这两个列的值连接成一个完整的姓名。

CONCAT函数还可以处理NULL值。如果任意一个参数为NULL，则返回的结果也为NULL。

SELECT CONCAT('Hello', NULL, 'World') AS result;
输出：NULL

DATE_FORMAT （日期时间值按指定的格式进行格式化）

//date是要格式化的日期或时间值，format是指定的格式字符串。
DATE_FORMAT(date, format);

SELECT DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date
FROM orders;

获得具体的时间（当前日期的年月）

-- 获得当前时间的年月 2024-05
SET @month = CONCAT(YEAR(CURRENT_DATE()), '-', LPAD(MONTH(CURRENT_DATE()), 2, '0'));
-- 方案二（性能更加优秀）
SET @month = DATE_FORMAT(CURRENT_DATE(), '%Y-%m')

select @month

计算当前日期与日期列之间的天数差异

-- CURDATE() 返回当前日期。
-- 如果 日期列中存储的日期是 2022-01-01，而当前日期是 2024-05-09，那么 DATEDIFF(CURDATE(), 日期列) 将返回 859，表示当前日期与 日期列之间相差 859 天。

DATEDIFF(CURDATE(), 日期列) 

创建表

CREATE TABLE table_name (
   id INT PRIMARY KEY AUTO_INCREMENT,
   f1 INT,
   f2 VARCHAR(50) NOT NULL,
   f3 VARCHAR(100) DEFAULT '默认值',
   f4 DECIMAL(10, 2),
   f5 DATE,
   f16 TEXT,
  INDEX idx_f1 (f1),			-- 设置普通索引，索引名称idx_f2
  CONSTRAINT idx_f2 UNIQUE (f2), -- 自主对索引的名称命名成为idx_f2
  UNIQUE (f3)					-- 让系统自动为唯一索引命名
  
)AUTO_INCREMENT = 1; -- 自增的起始值为1

ALTER TABLE table_name AUTO_INCREMENT = 1; -- 设置了 AUTO_INCREMENT 的起始值为 1
SET @@auto_increment_increment=2; -- 设置步长为2

查看查看数据表的字段类型和其他属性

DESCRIBE table_name;
或者
SHOW COLUMNS FROM table_name;

Field: 字段名，表示数据表中的列名。
Type: 字段的数据类型，指定了该列可以存储的数据类型，例如整数、字符串等。
Null: 指示该字段是否允许为空值（NULL）。如果允许为空，则显示 "YES"；如果不允许为空，则显示 "NO"。
Key: 指示该字段是否是键，即是否属于索引的一部分。如果该字段是主键或具有索引，则显示 "PRI"；如果该字段具有唯一索引，则显示 "UNI"；如果该字段没有索引，则显示空白。
Default: 指示该字段的默认值，即在插入新记录时，如果没有提供该字段的值，将使用的默认值。
Extra: 显示额外的信息或标志，用于描述该字段的其他特性。例如，如果该字段是自增字段（auto-increment），则显示 "auto_increment"。

字段操作

'新增字段'
ALTER TABLE table_name
ADD column_name data_type [column_attributes] [FIRST|AFTER existing_column];

将 table_name 替换为要添加字段的数据表名称。
将 column_name 替换为要添加的字段名。
将 data_type 替换为字段的数据类型，如 VARCHAR、INT、DATE 等。
可选项：column_attributes 是字段的其他属性，如 NOT NULL（非空约束）、DEFAULT（默认值）等。
可选项：使用 FIRST 关键字将新字段添加为第一个字段，或使用 AFTER existing_column 将新字段添加在已存在的某个字段之后。

示例：添加一个字段设置为非空，默认值为0，并且在表中的一个字段后面
ALTER TABLE 表名
ADD 字段名 TINYINT(1) NOT NULL DEFAULT 0 AFTER 旧字段;

创建时间字段添加默认值

/**
 create_time   默认值可以设置为CURRENT_TIMESTAMP  插入数据时候会自动添加时间,默认值为当前时间
 update_time  默认值可以设置为CURRENT_TIMESTAMP  ON UPDATE CURRENT_TIMESTAMP  表示每次更新这条数据的时候，该字段都会更新成当前时间
*/
CREATE TABLE your_table_name (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    other_column VARCHAR(50) NOT NULL,
    createTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

索引操作

//InnoDB存储引擎下，创建索引时默认使用B-tree索引，不支持Hash索引
/**
B-tree索引（平衡树索引）：
B-tree（B树）是一种自平衡的搜索树数据结构，被广泛用于数据库索引中。B-tree索引在MySQL中是最常见的索引类型。B-tree索引按照一定的顺序将索引键值存储在树结构中，使得数据的查找更加高效。B-tree索引适用于范围查询，可以支持大部分查询类型，包括等值查询、范围查询和排序。B-tree索引适用于存储范围较大的数据。
*/
/**
Hash索引（哈希索引）：
Hash索引使用哈希函数将索引键值映射到一个哈希表中的特定位置。哈希表是一种通过哈希值来快速访问数据的数据结构。Hash索引适用于等值查询，即根据完整的索引键值进行查询。对于给定的索引键值，哈希索引可以快速定位到存储该键值的位置。然而，Hash索引不适用于范围查询和排序操作，因为哈希函数的特性使得无法有效地支持这些操作。
*/
//创建普通索引
CREATE INDEX 索引名称 ON 表名 (表中字段名称);

//创建唯一索引
ALTER TABLE 表名 ADD UNIQUE INDEX 索引名 (字段名);

定时任务

-- 每天凌晨1点执行
-- DO后是需要执行的sql

DELIMITER $$
	CREATE EVENT IF NOT EXISTS `定时任务时间名称`   
    ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)   
    ON COMPLETION PRESERVE ENABLE   
    DO 
    insert into tb_user_days
	(
		SELECT * FROM tb_user WHERE `month` = DATE_FORMAT(NOW(), '%Y-%m')
	);
END$$
DELIMITER ;

-- 查询定时任务相关时间
SELECT 
    EVENT_NAME,
    STARTS,
    ENDS,
    LAST_EXECUTED,
    INTERVAL_VALUE,
    INTERVAL_FIELD,
    CASE 
        WHEN INTERVAL_FIELD = 'SECOND' THEN DATE_ADD(LAST_EXECUTED, INTERVAL INTERVAL_VALUE SECOND)
        WHEN INTERVAL_FIELD = 'MINUTE' THEN DATE_ADD(LAST_EXECUTED, INTERVAL INTERVAL_VALUE MINUTE)
        WHEN INTERVAL_FIELD = 'HOUR' THEN DATE_ADD(LAST_EXECUTED, INTERVAL INTERVAL_VALUE HOUR)
    END AS NEXT_EXECUTION
FROM
    information_schema.EVENTS
WHERE
    EVENT_NAME = '定时任务名称';

存储过程

// 这个存储过程接受一个表名作为输入参数，并返回该表中的行数作为输出参数
DELIMITER //

CREATE PROCEDURE count_rows_in_table(IN table_name VARCHAR(255), OUT row_count INT)
BEGIN
    SET @sql_query = CONCAT('SELECT COUNT(*) FROM ', table_name);
    PREPARE stmt FROM @sql_query;
    EXECUTE stmt INTO row_count;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

CALL count_rows_in_table('tb_user', @row_count);
SELECT @row_count;

计算列

/**
CREATE TABLE test (
    id INT,
    quantity INT NULL DEFAULT 0,
    price DECIMAL(18, 2) NULL DEFAULT 0.00,
    total DECIMAL(18, 2) AS (quantity * price)  -- 计算列
);
注意：
使用计算列，你需要确保所有参与计算的列都已经存在，并且在表定义中正确命名。
计算列的 （） 这个括号必需要加上
被计算列一定要设置默认值，不然会导致计算列无法计算出来
*/

//下面的两条语句都可以使用，在返回的结果中，如果某列的类型为 VIRTUAL，那么它就是一个计算列。
SHOW COLUMNS FROM tb_finance_report;
DESCRIBE tb_finance_report;

触发器

删除触发器

DROP TRIGGER IF EXISTS 触发器名称;

新增触发器

//数据库新增一条记录的时候SystemContractID的值为id字段增加100000000

DELIMITER //

CREATE TRIGGER 触发器名称
BEFORE INSERT ON 表名
FOR EACH ROW
BEGIN
    DECLARE max_id INT;
    SELECT MAX(id) INTO max_id FROM 表名;
    SET NEW.SystemContractID = IFNULL(max_id, 0) + 100000001;
END //

DELIMITER ;

创建新增 100 万条数据的函数（navicat里面图形化生成数据更好用）

SET GLOBAL log_bin_trust_function_creators=TRUE; -- 创建函数一定要写这个
DELIMITER $$   -- 写函数之前必须要写，该标志

CREATE FUNCTION mock_data()        -- 创建函数（方法）
RETURNS INT                         -- 返回类型
BEGIN                                -- 函数方法体开始
    DECLARE num INT DEFAULT 1000000;         -- 定义一个变量num为int类型。默认值为100 0000
    DECLARE i INT DEFAULT 0; 

    WHILE i < num DO                 -- 循环条件
   			-- 注意表名和对应的字段名称还有生成条件 					
         INSERT INTO user(`name`,`email`,`phone`,`gender`,`password`,`age`) 
         VALUES(CONCAT('用户',i),'xxxxxxqq.com',CONCAT('18',FLOOR(RAND() * ((999999999 - 100000000) + 1000000000))),FLOOR(RAND()  *  2),UUID(),FLOOR(RAND()  *  100));
        SET i =  i + 1;    -- i自增   
         
    END WHILE;        -- 循环结束
    RETURN i;
END;                                 -- 函数方法体结束

//调用函数
SELECT mock_data(); -- 调用函数

选取在特定时间区间内的记录

/**
PASSWORDTIME 大于当前日期往前推算 90 天的日期（即过去 90 天之后的日期）。
PASSWORDTIME 小于当前日期往前推算 80 天的日期（即过去 80 天之后的日期）。
CURDATE() 返回当前日期，DATE_SUB() 函数用于计算日期的差值。INTERVAL 关键字用于指定日期的间隔。
该查询语句会返回 TB_User 表中 PASSWORDTIME 字段在过去 80 天和 90 天之间的记录。
*/
SELECT PASSWORDTIME
FROM TB_User
WHERE PASSWORDTIME > DATE_SUB(CURDATE(), INTERVAL 90 DAY)
AND PASSWORDTIME < DATE_SUB(CURDATE(), INTERVAL 80 DAY);

使用Navicat 同步 测试数据库 与 正式数据库 数据