sudo mysql//直接进入mysql中
SHOW VARIABLES LIKE 'validate_password%';//显示密码信息
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';//修改密码
sudo mysql -root -p //进入mysql中
show variables like '%secure%';
一 mysql内部操作符
mysql -u root -p
create database ota;//创建ota数据库
show databases;//查看数据库
use ota;//进入ota数据库
create table users(number varchar(1000),id varchar(1000));//创建表
show tables;//查看表
insert into users(number,id) values(2,99);//插入表数据
SELECT * FROM users;//查询表里面的数据
SELECT number FROM users where id = 17; //查询表里面的固定的数据
drop table users;//删除 user 表
delete from users;//删除user表的所有数据
systemctl start mysql.service //启动mysql
二 C语言程序操作MySQL
2.1 安装MySQL的库
sudo apt-get install libmysqlclient-dev
2.2 建立测试数据库
create database text;
create table children(id varchar(1000),name varchar(1000),age varchar(1000));
2.3 测试代码
#include<stdlib.h>
#include<stdio.h>
#include<mysql/mysql.h>
MYSQL *conn_ptr;
unsigned int timeout = 7; //超时时间7秒
int main()
{
int ret = 0;
if(!conn_ptr)
{
printf("mysql_init failed!\n");
return -1;
}
ret = mysql_options(conn_ptr,MYSQL_OPT_CONNECT_TIMEOUT,(const char*)&timeout);//设置超时选项
if(ret)
{
printf("Options Set ERRO!\n");
}
conn_ptr = mysql_real_connect(conn_ptr,"localhost","root","123456","text",0,NULL,0);//连接MySQL testdb数据库
if(conn_ptr)
{
printf("Connection Succeed!\n");
ret = mysql_query(conn_ptr,"INSERT INTO children(name,age) VALUES('李四',3)"); //执行SQL语句
if(!ret)
{
printf("Inserted %lu rows\n",(unsigned long)mysql_affected_rows(conn_ptr));//返回上次UPDATE更改行数
}
else
{
printf("Connect Erro:%d %s\n",mysql_errno(conn_ptr),mysql_error(conn_ptr));//返回错误代码、错误消息
}
mysql_close(conn_ptr);
printf("Connection closed!\n");
}
else //错误处理
{
printf("Connection Failed!\n");
if(mysql_errno(conn_ptr))
{
printf("Connect Erro:%d %s\n",mysql_errno(conn_ptr),mysql_error(conn_ptr));//返回错误代码、错误消息
}
return -2;
}
return 0;
}
编译:
gcc -o text 1.c -lmysqlclient
在mysql中插入时间戳:
#include<stdlib.h>
#include<stdio.h>
#include<mysql/mysql.h>
#include <time.h>
MYSQL *conn_ptr;
unsigned int timeout = 7; //超时时间7秒
time_t now_time;
char *get_time(void){
time(&now_time);
return ctime (&now_time);
}
int main()
{
int ret = 0;
char sql_insert[200]={0};
conn_ptr = mysql_init(NULL);//初始化
if(!conn_ptr)
{
printf("mysql_init failed!\n");
return -1;
}
ret = mysql_options(conn_ptr,MYSQL_OPT_CONNECT_TIMEOUT,(const char*)&timeout);//设置超时选项
if(ret)
{
printf("Options Set ERRO!\n");
}
conn_ptr = mysql_real_connect(conn_ptr,"localhost","root","123456","text",0,NULL,0);//连接MySQL testdb数据库
if(conn_ptr)
{
printf("Connection Succeed!\n");
sprintf(sql_insert, "INSERT INTO children (id,name,age) values('%s','李四',5);",get_time());
ret = mysql_query(conn_ptr,sql_insert); //执行SQL语句
if(!ret)
{
printf("Inserted %lu rows\n",(unsigned long)mysql_affected_rows(conn_ptr));//返回上次UPDATE更改行数
}
else
{
printf("Connect Erro:%d %s\n",mysql_errno(conn_ptr),mysql_error(conn_ptr));//返回错误代码、错误消息
}
mysql_close(conn_ptr);
printf("Connection closed!\n");
}
else //错误处理
{
printf("Connection Failed!\n");
if(mysql_errno(conn_ptr))
{
printf("Connect Erro:%d %s\n",mysql_errno(conn_ptr),mysql_error(conn_ptr));//返回错误代码、错误消息
}
return -2;
}
return 0;
}
改进版
#include<stdlib.h>
#include<stdio.h>
#include<mysql/mysql.h>
#include <time.h>
MYSQL *conn_ptr;
unsigned int timeout = 7; //超时时间7秒
char time1[20];
char *get_time(void)
{
time_t timep;
struct tm *p;
time (&timep);
p=gmtime(&timep);
sprintf(time1,"%d年%d月%d日 %d:%d",1900+p->tm_year,1+p->tm_mon,p->tm_mday,8+p->tm_hour,p->tm_min);
return time1;
}
int main()
{
int ret = 0;
char sql_insert[200]={0};
conn_ptr = mysql_init(NULL);//初始化
if(!conn_ptr)
{
printf("mysql_init failed!\n");
return -1;
}
ret = mysql_options(conn_ptr,MYSQL_OPT_CONNECT_TIMEOUT,(const char*)&timeout);//设置超时选项
if(ret)
{
printf("Options Set ERRO!\n");
}
conn_ptr = mysql_real_connect(conn_ptr,"localhost","root","123456","text",0,NULL,0);//连接MySQL testdb数据库
if(conn_ptr)
{
printf("Connection Succeed!\n");
sprintf(sql_insert, "INSERT INTO children (id,name,age) values('%s','李四',5);",get_time());
ret = mysql_query(conn_ptr,sql_insert); //执行SQL语句
if(!ret)
{
printf("Inserted %lu rows\n",(unsigned long)mysql_affected_rows(conn_ptr));//返回上次UPDATE更改行数
}
else
{
printf("Connect Erro:%d %s\n",mysql_errno(conn_ptr),mysql_error(conn_ptr));//返回错误代码、错误消息
}
mysql_close(conn_ptr);
printf("Connection closed!\n");
}
else //错误处理
{
printf("Connection Failed!\n");
if(mysql_errno(conn_ptr))
{
printf("Connect Erro:%d %s\n",mysql_errno(conn_ptr),mysql_error(conn_ptr));//返回错误代码、错误消息
}
return -2;
}
return 0;
}
运行结果:
终极进化版
将数据导出为excel表格
#include<stdlib.h>
#include<stdio.h>
#include<mysql/mysql.h>
#include <time.h>
MYSQL *conn_ptr;
unsigned int timeout = 7; //超时时间7秒
char time1[20];
char *get_time(void)
{
time_t timep;
struct tm *p;
time (&timep);
p=gmtime(&timep);
sprintf(time1,"%d年%d月%d日 %d:%d",1900+p->tm_year,1+p->tm_mon,p->tm_mday,8+p->tm_hour,p->tm_min);
return time1;
}
int main()
{
int ret = 0;
char sql_insert[200]={0};
conn_ptr = mysql_init(NULL);//初始化
if(!conn_ptr)
{
printf("mysql_init failed!\n");
return -1;
}
ret = mysql_options(conn_ptr,MYSQL_OPT_CONNECT_TIMEOUT,(const char*)&timeout);//设置超时选项
if(ret)
{
printf("Options Set ERRO!\n");
}
conn_ptr = mysql_real_connect(conn_ptr,"localhost","root","123456","text",0,NULL,0);//连接MySQL testdb数据库
if(conn_ptr)
{
printf("Connection Succeed!\n");
sprintf(sql_insert, "SELECT * FROM children INTO OUTFILE '/var/lib/mysql-files/children.xls';");
ret = mysql_query(conn_ptr,sql_insert); //执行SQL语句
if(!ret)
{
printf("/var/lib/mysql-files/children.xls success\n");//返回上次UPDATE更改行数
}
else
{
printf("/var/lib/mysql-files/children.xls error\n");//返回错误代码、错误消息
}
system("sudo cp /var/lib/mysql-files/children.xls /home/xer/code/.");
system("sudo chmod 777 /home/xer/code/children.xls");
sprintf(sql_insert, "INSERT INTO children (id,name,age) values('%s','李四',5);",get_time());
ret = mysql_query(conn_ptr,sql_insert); //执行SQL语句
if(!ret)
{
printf("Inserted %lu rows\n",(unsigned long)mysql_affected_rows(conn_ptr));//返回上次UPDATE更改行数
}
else
{
printf("Connect Erro:%d %s\n",mysql_errno(conn_ptr),mysql_error(conn_ptr));//返回错误代码、错误消息
}
mysql_close(conn_ptr);
printf("Connection closed!\n");
}
else //错误处理
{
printf("Connection Failed!\n");
if(mysql_errno(conn_ptr))
{
printf("Connect Erro:%d %s\n",mysql_errno(conn_ptr),mysql_error(conn_ptr));//返回错误代码、错误消息
}
return -2;
}
return 0;
}
超级最终版:
#include<stdlib.h>
#include<stdio.h>
#include<mysql/mysql.h>
#include <time.h>
MYSQL *conn_ptr;
unsigned int timeout = 7; //超时时间7秒
char time1[20];
char *get_time(void)
{
time_t timep;
struct tm *p;
time (&timep);
p=gmtime(&timep);
sprintf(time1,"%d年%d月%d日 %d:%d",1900+p->tm_year,1+p->tm_mon,p->tm_mday,8+p->tm_hour,p->tm_min);
return time1;
}
int main()
{
int ret = 0;
int i=0;
char sql_insert[200]={0};
conn_ptr = mysql_init(NULL);//初始化
if(!conn_ptr)
{
printf("mysql_init failed!\n");
return -1;
}
ret = mysql_options(conn_ptr,MYSQL_OPT_CONNECT_TIMEOUT,(const char*)&timeout);//设置超时选项
if(ret)
{
printf("Options Set ERRO!\n");
}
conn_ptr = mysql_real_connect(conn_ptr,"localhost","root","123456","text",0,NULL,0);//连接MySQL testdb数据库
if(conn_ptr)
{
printf("Connection Succeed!\n");
system("sudo rm /home/xer/code/children.xls /var/lib/mysql-files/children.xls");
sprintf(sql_insert, "SELECT * FROM children INTO OUTFILE '/var/lib/mysql-files/children.xls';");
ret = mysql_query(conn_ptr,sql_insert); //执行SQL语句
if(!ret)
{
printf("/var/lib/mysql-files/children.xls success\n");//返回上次UPDATE更改行数
}
else
{
printf("/var/lib/mysql-files/children.xls error\n");//返回错误代码、错误消息
}
system("sudo cp /var/lib/mysql-files/children.xls /home/xer/code/.");
system("sudo chmod 777 /home/xer/code/children.xls");
sprintf(sql_insert, "INSERT INTO children (id,name,age) values('%s','李四',%d);",get_time(),i);
ret = mysql_query(conn_ptr,sql_insert); //执行SQL语句
if(!ret)
{
printf("Inserted %lu rows\n",(unsigned long)mysql_affected_rows(conn_ptr));//返回上次UPDATE更改行数
}
else
{
printf("Connect Erro:%d %s\n",mysql_errno(conn_ptr),mysql_error(conn_ptr));//返回错误代码、错误消息
}
mysql_close(conn_ptr);
printf("Connection closed!\n");
}
else //错误处理
{
printf("Connection Failed!\n");
if(mysql_errno(conn_ptr))
{
printf("Connect Erro:%d %s\n",mysql_errno(conn_ptr),mysql_error(conn_ptr));//返回错误代码、错误消息
}
return -2;
}
return 0;
}