3、MySQL安装与开发 - 《全栈物联网平台搭建》

一、安装MySQL
二、验证安装：
三、修改ROOT密码
四、Mysql C Api
五、Mysql管理工具
六、测试连接代码
七、Mysql 通配符大全
C 常用API
Myslq常用语句

MySQL是一个关系型数据库管理系统，目前属于Oracle旗下产品。
常用于WEB数据库，门户网站，云平台网站等等，拥有体积小，速度快，免费等特性。

一、安装MySQL

开发环境：ubuntu20.02

#安装MYSQL服务端
sudo apt-get install mysql-server
#安装sql开发者库
sudo apt-get install libmysqlclient-dev
#服务端安装完成，验证是否启动成功
nmap 127.0.0.1

二、验证安装：

#include <stdio.h>
#include <mysql/mysql.h>
int main(int argc,const char *argv[]){
    MYSQL *mysql;
    return 0;
}

三、修改ROOT密码

#打开mysql配置文件，里面包含账号密码
$ sudo vi /etc/mysql/debian.cnf

#另开一个shell 输入 ，密码复制过来即可，回车
$ mysql -u debian-sys-maint -p
#在mysql命令输入如下命令：
mysql> flush privileges;
#返回：Query OK, 0 rows affected (0.01 sec)
mysql>ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';
#返回：Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
#返回：Query OK, 0 rows affected (0.01 sec)
mysql>quit;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#以上操作已经将ROOT用户更改成自己得密码了，登录测试一下
$ mysql -u root -p
#输入密码，出现mysql命令窗即可。

四、Mysql C Api

mysql目前更新到8.0的版本，不同版本会有差异，通过ubuntu输入命令查看你当前的mysql版本来选择对应的文档。

#注意是大写的V
$ mysql -V
#返回内容：mysql  Ver 8.0.23-0ubuntu0.20.10.1 for Linux on x86_64 ((Ubuntu))

进入官网找对应的文档（另外提供百度网盘下载链接）：https://www.mysql.com/cn/
资料下载链接：https://pan.baidu.com/s/1R1rnz0W—P-l8D-_F9T15A
提取码：nqiz

五、Mysql管理工具

https://www.navicat.com.cn/download/navicat-premium （免费试用）

资料下载链接：https://pan.baidu.com/s/1R1rnz0W—P-l8D-_F9T15A
提取码：nqiz — 工具目录下
拷贝到ubuntu内，右击打开，或者挂载也可以，本质上是个镜像

具体创表，后期工程总体现

六、测试连接代码

#include <stdio.h>
#include <mysql/mysql.h>
#define HOST "127.0.0.1"
#define USER "root"
#define PASSWORD "zhaolc"
#define DB "mysql"
#define PORT 0
int main(int argc,const char *argv[])
{
    MYSQL mysql; //创建一个mysql句柄
    mysql_init(&mysql); //初始化这个句柄，分配内存
    if(&mysql == NULL)  //判断初始化是否成功
    {
        printf("MYSQL is NULL \n");
        return 0;        
    }
    if( ! mysql_real_connect(&mysql,HOST,USER,PASSWORD,DB,PORT,NULL,0)) //连接mysql服务器，填充1 句柄，2 地址,3 用户，4 密码，5 数据库名，6 写0 ，7 写NULL，8 写0
    {
       printf("Failed to connect to database: Error: %s\n",mysql_error(&mysql)); //如果连接错误，打印错误码
       return -1;
    }
    printf("mysql connect is ok \n"); // 连接成功则打印该语句
    printf("+++++++++++++++++++++++++++++\n");
    printf("\n");
    printf("\n");
    printf("%s\n",mysql_get_client_info()); //获取mysql版本信息。
    printf("\n");
    printf("\n");
    printf("+++++++++++++++++++++++++++++\n");
    MYSQL_RES *res;
    res = mysql_list_tables(&mysql,"%");  //查找表名%为通配符，如果搜寻指定名字，在字符串输入即可
    if(res == NULL)
    {
        printf("res is NULL \n");        
    }
    printf("共有 %ld 行数据\n",mysql_num_rows(res)); //显示查找到的行数
    return 0;
}

七、Mysql 通配符大全

SQL的模式匹配允许你使用“”匹配任何单个字符，而“%”匹配任意数目字符(包括零个字符)。在 MySQL中，SQL的模式缺省是忽略大小写的。下面显示一些例子。
注意在你使用SQL模式时，你不能使用=或!=；而使用LIKE或NOT LIKE比较操作符。

为了找出以“b”开头的名字：
mysql> SELECT FROM pet WHERE name LIKE “b%”;
+————+————+————-+———+——————+——————+
| name | owner | species | sex | birth | death |
+————+————+————-+———+——————+——————+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+————+————+————-+———+——————+——————+

为了找出以“fy”结尾的名字：
mysql> SELECT FROM pet WHERE name LIKE “%fy”;
+————+————+————-+———+——————+———-+
| name | owner | species | sex | birth | death |
+————+————+————-+———+——————+———-+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+————+————+————-+———+——————+———-+

为了找出包含一个“w”的名字：
mysql> SELECT * FROM pet WHERE name LIKE “%w%”;
+—————+———-+————-+———+——————+——————+
| name | owner | species | sex | birth | death |
+—————+———-+————-+———+——————+——————+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+—————+———-+————-+———+——————+——————+

为了找出包含正好5个字符的名字，使用“”模式字符：
mysql> SELECT FROM pet WHERE name LIKE “_“;
+———-+————+————-+———+——————+———-+
| name | owner | species | sex | birth | death |
+———-+————+————-+———+——————+———-+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+———-+————+————-+———+——————+———-+
由MySQL提供的模式匹配的其他类型是使用扩展正则表达式。当你对这类模式进行匹配测试时，使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE，它们是同义词)。

扩展正则表达式的一些字符是：
“.”匹配任何单个的字符。
一个字符类“[…]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”、“b”或“c”。为了命名字符的一个范围，使用一个“-”。“[a-z]” 匹配任何小写字母，而“[0-9]”匹配任何数字。
“ ”匹配零个或多个在它前面的东西。例如，“x”匹配任何数量的“x”字符，“[0-9]”匹配的任何数量的数字，而“.”匹配任何数量的任何东西。
正则表达式是区分大小写的，但是如果你希望，你能使用一个字符类匹配两种写法。例如，“[aA]”匹配小写或大写的“a”而“[a-zA-Z]”匹配两种写法的任何字母。
如果它出现在被测试值的任何地方，模式就匹配(只要他们匹配整个值，SQL模式匹配)。
为了定位一个模式以便它必须匹配被测试值的开始或结尾，在模式开始处使用“^”或在模式的结尾用“$”。
为了说明扩展正则表达式如何工作，上面所示的LIKE查询在下面使用REGEXP重写：

为了找出以“b”开头的名字，使用“^”匹配名字的开始并且“[bB]”匹配小写或大写的“b”：
mysql> SELECT FROM pet WHERE name REGEXP “^[bB]”;
+————+————+————-+———+——————+——————+
| name | owner | species | sex | birth | death |
+————+————+————-+———+——————+——————+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+————+————+————-+———+——————+——————+

为了找出以“fy”结尾的名字，使用“$”匹配名字的结尾：
mysql> SELECT FROM pet WHERE name REGEXP “fy$”;
+————+————+————-+———+——————+———-+
| name | owner | species | sex | birth | death |
+————+————+————-+———+——————+———-+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+————+————+————-+———+——————+———-+

为了找出包含一个“w”的名字，使用“[wW]”匹配小写或大写的“w”：
mysql> SELECT FROM pet WHERE name REGEXP “[wW]”;
+—————+———-+————-+———+——————+——————+
| name | owner | species | sex | birth | death |
+—————+———-+————-+———+——————+——————+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+—————+———-+————-+———+——————+——————+
既然如果一个正规表达式出现在值的任何地方，其模式匹配了，就不必再先前的查询中在模式的两方面放置一个通配符以使得它匹配整个值，就像如果你使用了一个SQL模式那样。

为了找出包含正好5个字符的名字，使用“^”和“$”匹配名字的开始和结尾，和5个“.”实例在两者之间：
mysql> SELECT FROM pet WHERE name REGEXP “^…..$”;
+———-+————+————-+———+——————+———-+
| name | owner | species | sex | birth | death |
+———-+————+————-+———+——————+———-+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+———-+————+————-+———+——————+———-+

你也可以使用“{n}”“重复n次”操作符重写先前的查询：
mysql> SELECT FROM pet WHERE name REGEXP “^.{5}$”;
+———-+————+————-+———+——————+———-+
| name | owner | species | sex | birth | death |
+———-+————+————-+———+——————+———-+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+———-+————+————-+———+——————+———-+

C 常用API

//创建初始化
mysql_init(MYSQL *mysql); 
//连接数据库
MYSQL *mysql_real_connect(MYSQL *mysql,const char *host,const char *user,const char *passwd,const char *db,unsigned int port,const char *unix_socket,unsigned long client_flag);
//执行sql语句，句柄，SQL语句，长度
int mysql_real_query(MYSQL *mysql,const char *stmt_str,unsigned long length);
//获取结果，返回给MYSQL_RES类型
MYSQL_RES *mysql_store_result(MYSQL *mysql);
//返回数据列数
unsigned int mysql_num_fields(MYSQL_RES *result);
//获取返回数据的行数据，重复调用，依次指向每一行数据的起始地址。 配合之前获取的列数， 可以将每一行数按照自己要求输出。
MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);
//获取行数据
unsigned int mysql_num_fields(MYSQL_RES *result)
//获取每一列的信息。MYSQL_FIELD *field 有字段名，类型，大小等。
//该函数返回的是一个指针，指向所有列的MYSQL_FIELD 的结构。 可以通过指针访问 ，也可以通过重复调用获取下一个来使用。
MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result)
//释放集合
void mysql_free_result(MYSQL_RES *result);

Myslq常用语句

新建用户：
　　>CREATE USER name IDENTIFIED BY 'ssapdrow';
更改密码：
　　>SET PASSWORD FOR name=PASSWORD('fdddfd');
权限管理
　　>SHOW GRANTS FOR name;    //查看name用户权限
　　>GRANT SELECT ON db_name.* TO name;　　　　//给name用户db_name数据库的所有权限
　　>REVOKE SELECT ON db_name.* TO name;　　　　//GRANT的反操作，去除权限；
数据库操作：　
　　查看数据库：
　　>SHOW DATABASES;
　　创建数据库：
　　>CREATE DATABASE db_name;　　//db_name为数据库名
　　使用数据库：
　　>USE db_name;
　　删除数据库：
　　>DROP DATABASE db_name;
创建表：
　　创建表：
　　>CREATE TABLE table_name(
　　>id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,　　　　//id值，无符号、非空、递增——唯一性，可做主键。
　　>name VARCHAR(60) NOT NULL
　　>score TINYINT UNSIGNED NOT NULL DEFAULT 0,　　　　//设置默认列值
　　>PRIMARY KEY(id)
　　>)ENGINE=InnoDB　　　　//设置表的存储引擎，一般常用InnoDB和MyISAM；InnoDB可靠，支持事务；MyISAM高效不支持全文检索
　　>DEFAULT charset=utf8;　　//设置默认的编码，防止数据库中文乱码
　　如果有条件的创建数据表还可以使用   >CREATE TABLE IF NOT EXISTS tb_name(........
　     复制表：
　　>CREATE TABLE tb_name2 SELECT * FROM tb_name;
　　或者部分复制：
　　>CREATE TABLE tb_name2 SELECT id,name FROM tb_name;
　　创建临时表：
　　>CREATE TEMPORARY TABLE tb_name(这里和创建普通表一样);
　　查看数据库中可用的表：
　　>SHOW TABLES;
　　查看表的结构：
　　>DESCRIBE tb_name;
　　也可以使用：
　　>SHOW COLUMNS in tb_name; 　　　　//from也可以
　　删除表：
　　　　>DROP [ TEMPORARY ] TABLE [ IF EXISTS ] tb_name[ ,tb_name2.......];
　　　　实例：
　　　　>DROP TABLE IF EXISTS tb_name;
　　表重命名：
　　　　>RENAME TABLE name_old TO name_new;
　　　　还可以使用：
　　　　>ALTER TABLE name_old RENAME name_new;
修改表：
　　更改表结构：
　　　　>ALTER TABLE tb_name ADD[CHANGE,RENAME,DROP] ...要更改的内容...
　　　　实例：
　　　　>ALTER TABLE tb_name ADD COLUMN address varchar(80) NOT NULL;
　　　　>ALTER TABLE tb_name DROP address;
　　　　>ALTER TABLE tb_name CHANGE score score SMALLINT(4) NOT NULL;
插入数据：
　　插入数据：
　　　　>INSERT INTO tb_name(id,name,score)VALUES(NULL,'张三',140),(NULL,'张四',178),(NULL,'张五',134);
　　　　这里的插入多条数据直接在后边加上逗号，直接写入插入的数据即可；主键id是自增的列，可以不用写。
　　插入检索出来的数据：
　　　　>INSERT INTO tb_name(name,score) SELECT name,score FROM tb_name2;
更新数据：
　　指定更新数据：
　　　　>UPDATE tb_name SET score=189 WHERE id=2;
　　　　>UPDATE tablename SET columnName=NewValue [ WHERE condition ]
删除数据：
　　删除数据：
　　　>DELETE FROM tb_name WHERE id=3;
条件控制：
　　WHERE 语句：
　　　>SELECT * FROM tb_name WHERE id=3;
　　HAVING 语句：
　　　>SELECT * FROM tb_name GROUP BY score HAVING count(*)>2
　　相关条件控制符： 
　　　　=、>、<、<>、IN(1,2,3......)、BETWEEN a AND b、NOT
　　　　AND 、OR
　　　　Linke()用法中      %  为匹配任意、  _  匹配一个字符（可以是汉字）
　　　　IS NULL 空值检测
 MySQL的正则表达式：
　　Mysql支持REGEXP的正则表达式：
　　　　>SELECT * FROM tb_name WHERE name REGEXP '^[A-D]'   //找出以A-D 为开头的name
　　特殊字符需要转义。
 MySQL的一些函数：
　　字符串链接——CONCAT()
　　　　>SELECT CONCAT(name,'=>',score) FROM tb_name
　　数学函数：
　　　　AVG、SUM、MAX、MIN、COUNT；
　　文本处理函数：
　　　　TRIM、LOCATE、UPPER、LOWER、SUBSTRING
　　运算符：
　　　　+、-、*、\
　　时间函数：
　　　　DATE()、CURTIME()、DAY()、YEAR()、NOW().....
 分组查询：
 　　分组查询可以按照指定的列进行分组：
　　　　>SELECT COUNT(*) FROM tb_name GROUP BY score HAVING COUNT(*)>1;
　　条件使用Having；
　　ORDER BY 排序：
　　　　ORDER BY DESC|ASC　　　　=>按数据的降序和升序排列
        UNION规则——可以执行两个语句（可以去除重复行）
 全文检索——MATCH和AGAINST
　　SELECT MATCH(note_text)AGAINST('PICASO') FROM tb_name;
　　InnoDB引擎不支持全文检索，MyISAM可以；
 十三、视图
　　1、创建视图
　　　　>CREATE VIEW name AS SELECT * FROM tb_name WHERE ~~ ORDER BY ~~;
　　2、视图的特殊作用：
　　　　　　a、简化表之间的联结（把联结写在select中）；
　　　　　　b、重新格式化输出检索的数据（TRIM，CONCAT等函数）；
　　　　　　c、过滤不想要的数据（select部分）
　　　　　　d、使用视图计算字段值，如汇总这样的值。
 十四、使用存储过程：
　　个人理解，存储过程就是一个自定义函数，有局部变量参数，可传入参数，可以返回值，不过这语法够呆滞的~~~
　　1、创建存储过程：
　　　　>CREATE PROCEDURE pro(
　　　　>IN num INT,OUT total INT)
　　　　>BEGIN
　　　　>SELECT SUM(score) INTO total FROM tb_name WHERE id=num;
　　　　>END;
　　　***这里的  IN (传递一个值给存储过程)，OUT（从存储过程传出一个值），INOUT（对存储过程传入、传出），INTO（保存变量）
　　2、调用存储过程：
　　　　>CALL pro(13,@total)　　　　　　//这里的存储过程两个变量，一个是IN一个是OUT，这里的OUT也是需要写上的，不写会出错
　　　　>SELECT @total　　　　　　　　　//这里就可以看到结果了；
　　3、存储过程的其他操作：
　　　　>SHOW PROCEDURE STATUS;　　　　　　//显示当期的存储过程
　　　　>DROP PROCEDURE pro;　　　　　　　　　//删除指定存储过程
十五、使用游标：
　　对这个理解不是很懂，朋友多多指点哦~~~
　　　1、游标的操作
　　　　>CREATE PROCEDURE pro()
　　　　>BEGIN 
　　　　>DECLARE ordername CURSOR FOR
　　　　>SELECT order_num FROM orders;
　　　　>END;
　　　　
　　　　>OPEN ordername;　　　　//打开游标
　　　　>CLOSE ordername;　　　　//关闭游标
十六、触发器：
　　触发器是指在进行某项指定操作时，触发触发器内指定的操作；
　　1、支持触发器的语句有DELETE、INSERT、UPDATE,其他均不支持
　　2、创建触发器：
　　　　>CREATE TRIGGER trig AFTER INSERT ON ORDERS FOR EACH ROW SELECT NEW.orser_name;
　　　　>INSERT语句，触发语句，返回一个值
　　3、删除触发器
　　　　>DROP TRIGGER trig;
十七、语法整理：
　　1、ALTER TABLE（修改表）
　　　　ALTER TABLE table_name
　　　　(　　ADD　　　　column　　datatype  　　[ NULL | NOT NULL ]　　[ CONSTRAINTS ]
　　　　　　 CHANGE　　column 　　datatype 　　COLUMNS　　[ NULL | NOT NULL ]　　 [ CONSTRAINTS ]
　　　　　　 DROP　　　 column，
　　　　　　　。。。。
　　　　)
　　2、COMMIT(处理事务)
　　　　>COMMIT;
 　　3、CREATE INDEX(在一个或多个列上创建索引)
　　　　CREATE INDEX index_name ON tb_name (column [ ASC | DESC ] , .......);
 　　4、CREATE PROCEDURE (创建存储过程)
　　　　CREATE PROCEDURE pro([ parameters ])
　　　　BEGIN
　　　　........
　　　　END
 　　5、CREATE TABLE(创建表)
　　　　CREATE TABLE tb_name(
　　　　column_name　　datetype　　[ NULL | NOT NULL ] 　　[ condtraints]   ,
　　　　column_name　　datetype　　[ NULL | NOT NULL ] 　　[ condtraints]   ,
　　　　.......
　　　　PRIMARY KEY( column_name )
　　　　)ENGINE=[  InnoDB | MyiSAM ]DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
 　　6、CREATE USER(创建用户)
　　　　CREATE USER user_name [ @hostname ] [ IDENTIFIED BY [ PASSWORD ] 'pass_word' ];
 　　7、CREATE VIEW （在一个或多个表上创建视图）
　　　　CREATE [ OR REPLACE ] VIEW view_name AS SELECT。。。。。。
 　　8、DELETE (从表中删除一行或多行)
　　　　DELETE FROM table_name [WHERE ......]
 　　9、DROP(永久删除数据库及对象，如视图、索引等)
　　　　DROP DATEBASE | INDEX | PROCEDURE | TABLE | TRIGGER | USER | VIEW  name
 　　10、INSERT （给表添加行）
　　　　INSERT INTO tb_name [ ( columns,...... ) ]  VALUES(value1,............);
　　　　使用SELECT值插入：
　　　　INSERT INTO tb_name [ ( columns,...... ) ]
　　　　SELECT columns , .......   FROM tb_name [ WHERE ...... ] ;
　　 11、ROLLBACK（撤销一个事务处理块）
　　　　ROLLBACK [  TO  savapointname  ];
　　 12、SAVEPOINT(为ROLLBACK设置保留点)
　　　　SAVEPOINT sp1;
　　 13、SELECT (检索数据，显示信息)
　　　　SELECT column_name,.....FROM tb_name  [ WHERE ]   [ UNION ]    [ RROUP BY ]   [ HAVING ]   [ ORDER BY ]
 　　14、START TRANSACTION (一个新的事务处理块的开始)
　　　　START TRANSACTION
　　 15、UPDATE(更新一个表中的一行或多行)
　　　　UPDATE tb_name SET column=value,......[ where ]