那些你不知道的数据库进阶知识点（非常详细）

一、存储过程和函数

1.1 存储过程

1.1.1 定义

SQL语句需要先编译然后执行，而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。

存储过程是可编程的函数，在数据库中创建并保存，可以由 SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。

1.1.2 存储过程特点

1、能完成较复杂的判断和运算
2、可编程行强，灵活
3、SQL编程的代码可重复使用
4、执行的速度相对快一些
5、减少网络之间的数据传输，节省开销

1.1.3 语法

CREATE PROCEDURE 过程名([ [IN | OUT | INOUT] 参数名 
数据类型[, [IN | OUT | INOUT] 参数名 数据类型 ...]]) [特性 ...] 程序体

参数：
存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。
MySQL存储过程的参数用在存储过程的定义，共有三种参数类型 IN 、 、INOUT。

IN:参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值OUT:该值可在存储过程内部被改变，并可返回
INOUT:调用时指定，并且可被改变和返回

过程体：
过程体的开始与结束使用开始与结束进行标识。

delimiter $

create procedure proc_test1()
begin
  select current_time();
  select current_date();
end $

delimiter ;

# 调用存储过程
call proc_test1();

1.1.3.1 IN 参数

IN 代表的是输入参数

delimiter $

create procedure proc_in(IN p_in int)
begin
  select p_in;  // 第二次输出  1
  SET p_in = 2;
  select p_in;  // 第三次输出  2
end $

delimiter ;

SET @p2_in = 1;
select @p2_in;  // 第一次输出   1
call proc_in(@p2_in);
select @p2_in;  // 第四次输出   1

1.1.3.2 OUT 参数

输出参数

delimiter $

create procedure proc_out(IN p_out int)
begin
  select p_out;  // 第二次输出   空
  SET p_out = 2;
  select p_out;  // 第三次输出   2
end $

delimiter ;

SET @p2_out = 1;
select @p2_out;  // 第一次输出     1
call proc_out(@p2_out);
select @p2_out;  // 第四次输出     2

1.1.3.3 INOUT 参数

既能作为输入，也能作为输出

delimiter $

create procedure proc_inout(IN p_inout int)
begin
  select p_inout;  // 第二次输出   1
  SET p_inout = 2;
  select p_inout;  // 第三次输出   2
end $

delimiter ;

SET @p2_inout = 1;
select @p2_inout;  // 第一次输出     1
call proc_out(@p2_inout);
select @p2_inout;  // 第四次输出     2

1.1.3.4 游标

创建表

use 0voice_test;

create table teacher(
  teacher_id int primary key auto_increment,
  teacher_name varchar(32),
  teacher_sex TINYINT default 0 comment '1:男;2:女',
  teacher_age int
) default charset=utf8;

insert into teacher(teacher_name, teacher_sex, teacher_age) values
  ('秋香', 0, 20),
  ('贝贝', 0, 19),
  ('依依', 0, 20),
  ('小雯', 1, 19),
  ('King', 1, 35),
  ('Mark', 1, 34);

select *from 0voice_test.teacher;

游标操作

delimiter //

create procedure proc_normal(IN age_in INT)
begin
  -- 声明游标结束的标志
  declare done int default 0;

  -- 声明变量来保存每一行的数据
  declare v_teacher_id int;
  declare v_teacher_name varchar(32);

  -- 声明游标
  declare cur_teacher cursor for select teacher_id, teacher_name from teacher where teacher_age = age_in;

  -- 声明NOT FOUND处理程序
  declare continue handler for not found set done = 1;

  -- 打开游标
  open cur_teacher;

  -- 遍历游标中获取的所有行
  fetch_loop: loop
    fetch cur_teacher into v_teacher_id, v_teacher_name;
    if done = 1 then
      -- 如果没有更多行，则退出循环
      leave fetch_loop;
    end if;

    -- 示例操作：使用获取的数据
    -- 在这里，我们通常会对v_teacher_id和v_teacher_name执行一些操作

  end loop;

  -- 关闭游标
  close cur_teacher;

end //

delimiter ;

1.2 函数

1.2.1 内置函数

MySQL函数包括数学函数、字符串函数、日期和时间兩数、条件判断函数、系统信息函数加密函数、格式化函数等。

1.2.2 自定义函数

1.2.2.1 语法

参数列表格式： 变量名 数据类型

二、触发器

2.1 概念

触发器(trigger)是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作(insert，delete，update)时就会激活它执行。

简单理解:触发器是与表有关的数据库对象，在满起定条件时触发，并执行触发器中定义的语句集合。

触发器四要素：
(1)监视地点(table)
(2)监视事件(insert/update/delete)
(3)触发时间(after/before)
(4)触发事件(insert/update/delete)

2.2创建触发器

2.2.1 语法

-- 创建触发器
create trigger trig_test1 before insert
on `work` for each row
insert into `time` values(null,now()); 
 
INSERT INTO `work` (`address`) VALUES ('长沙');

三、事件

3.1 事件调度器开关

# 查看事件调度器
show variables like 'event_scheduler';

# 开启事件调度器
set global event_scheduler = ON;

# 关闭事件调度器
set global event_scheduler = OFF;

3.2 通过更改配置文件

show variables like 'event_scheduler';

set global event_scheduler = on;

-- 创建用户信息表
DROP TABLE IF EXISTS t_user;
CREATE TABLE IF NOT EXISTS t_user
(
  `id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号',
  `name` VARCHAR(30) NOT NULL COMMENT '用户名',
  `create_time` TIMESTAMP COMMENT '创建时间'
) COMMENT = '用户信息表';

-- 创建统计用户信息表
DROP TABLE IF EXISTS t_total;
CREATE TABLE IF NOT EXISTS t_total
(
  `id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号',
  `user_number` VARCHAR(30) NOT NULL COMMENT '用户数量',
  `create_time` TIMESTAMP COMMENT '创建时间'
) COMMENT = '统计用户信息表';


create event if not exists event_user
on schedule every 5 second
on completion preserve
comment '新增用户信息定时信息'
do insert into t_user(`name`,`create_time`)values('miko',now());

select *from t_user;

生成事件

delimiter //
DROP procedure IF EXISTS proc_total;
create procedure proc_total()
begin
	declare p_total int default 0;
    select count(*) into p_total from t_user;
    insert into t_total (user_number,create_time)values(p_total,now());
end
//

create event if not exists event_autoTotal
on schedule every 30 second
on completion preserve
comment '统计   新增用户信息定时信息 综述' 
do call proc_total();

关闭事件

# 关闭事件
alter event event_user disable;
alter event event_autoTotal disable;

set global event_scheduler = off;   # 这是全局

删除

# 删除
drop event event_user;

四、MySQL的备份于恢复（windows）

4.1 备份

4.2 恢复

五、MySQL 开发

流程如下：

5.1 windows libmysql 库开发 MySQL

#include <iostream>
#include <mysql.h>

int main()
{

    MYSQL* conn;
    MYSQL_RES* res;
    MYSQL_ROW row;

    const char* host = "localhost";
    const char* user = "root";
    const char* passwd = "123456789";
    const char* db = "0voice_test";
    unsigned int port = 3306;

    
    // 1.初始化
    conn = mysql_init(NULL);

    // 2.创建连接
    if (!mysql_real_connect(conn, host, user, passwd, db, port, NULL, 0))
    {
        std::cout << stderr << "%s\n" << mysql_error(conn);
        return -1;
    }

    mysql_query(conn, "set names gbk");

    // 3.执行SQL语句（查询表）
    if (mysql_query(conn, "select *from teacher"))
    {
        std::cout << stderr << "%s\n" << mysql_error(conn);
        return -1;
    }

    // 4.读取数据
    res = mysql_use_result(conn);
    while((row = mysql_fetch_row(res)) != NULL)
    {
        printf("%s %s %s %s\n", row[0], row[1], row[2], row[3]);
    }

    // 释放
    mysql_free_result(res);

    //关闭连接
    mysql_close(conn);

    // std::cout << "Hello World!\n";

    getchar();

    return 0;
}

5.2 Qt 库开发 Mysql

// #include "mainwindow.h"
#include<QSqlDatabase>
#include<QSqlQuery>
#include<QDebug>

#include <QApplication>

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);

    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");

    db.setHostName("localhost");
    db.setUserName("root");
    db.setPassword("123456789");
    db.setDatabaseName("0voice_test");
    db.setPort(3306);

    if(db.open())
    {
        qDebug() << "打开成功" ;

        QSqlQuery query(db);

        QString sql;

        //删除表
        sql = "drop table if exists course;";

        if(query.exec(sql))
        {
            qDebug()<<"删除陈工";
        }else
        {
            qDebug()<<"删除失败";
        }


        //创建表
        sql = "create table if not exists course ("
              "id int primary key auto_increment,"
              "course_name varchar(128),"
              "teachers varchar(64),"
              ")default character set utf8";
        if(query.exec(sql))
        {
            qDebug()<<"建表成功";
        }else
        {
            qDebug()<<"简表失败";
        }

        //添加数据

        for(int i= 0;i<10;i++){
            QString course_name = QString("linux%1").arg(i+1);
            QString teachers = QString("king%1").arg(i+1);

            sql = QString("insert into course(course_name ,teachers) values ('%1','%2')").arg(course_name).arg(teachers);
            if(query.exec(sql))
            {
                qDebug()<<"添加成功";
            }else
            {
                qDebug()<<"添加失败";
            }
        }


        db.close();
    }else
    {
        qDebug() <<"打开失败";
    }


    // MainWindow w;
    // w.show();
    return a.exec();
}