linux学习8mysql

数据库建模与建库建表

DROP DATABASE KING_DB;#删除数据库

CREATE DATABASE KING_DB;#创建数据库

SHOW DATABASES;#显示数据库

USE KING_DB;#使用数据库

CREATE TABLE TBL_USER( #创建User表
U_ID INT PRIMARY KEY AUTO_INCREMENT, #主键，int型，自增
U_NAME VARCHAR(32), #名字char型
U_GENGDER VARCHAR(8) #性别
);

SHOW TABLES; #实现table

数据库编程连接与插入数据

在node server服务器上安装

sudo apt-get install libmysqlclient-dev

于是可以在ns代码里写mysql语句

#include<mysql.h>
#include<stdio.h>
#include<string.h>
//C R U D -->增删改查

#define KING_DB_SERVER_IP        "192.168.190.129"
#define KING_DB_SERVER_PORT      3306

#define KING_DB_USERNAME         "admin"
#define KING_DB_PASSWORD         "123456"

#define KING_DB_DEFAULTDB        "KING_DB"

#define SQL_INSERT_TBL_USER      "INSERT TBL_USER(U_NAME,U_GENGDER) VALUES('qiuxiang','woman');"
int main(){

	MYSQL mysql;//ns使用数据库的句柄，通道

	if (NULL == mysql_init(&mysql)) {  //初始化mysql
		printf("mysql_init : %s\n", mysql_error(&mysql));
		return -1;
	}

	if (!mysql_real_connect(&mysql, KING_DB_SERVER_IP, KING_DB_USERNAME, KING_DB_PASSWORD,   //连接数据库，返回0不成功
		KING_DB_DEFAULTDB, KING_DB_SERVER_PORT, NULL, 0)) {

		printf("mysql_real_connect : %s\n", mysql_error(&mysql));
		return -2;
	}

	//mysql -->insert

	if (mysql_real_query(&mysql, SQL_INSERT_TBL_USER, strlen(SQL_INSERT_TBL_USER))) {    //返回0成功
		printf("mysql_real_query : %s\n", mysql_error(&mysql));
	}

	mysql_close(&mysql);

	return 0;
}

执行：

gcc -o mysql2 mysql2.c -I /usr/include/mysql/ -lmysqlclient

读取数据

#include<mysql.h>
#include<stdio.h>
#include<string.h>
//C R U D -->增删改查

#define KING_DB_SERVER_IP        "192.168.190.129"
#define KING_DB_SERVER_PORT      3306

#define KING_DB_USERNAME         "admin"
#define KING_DB_PASSWORD         "123456"

#define KING_DB_DEFAULTDB        "KING_DB"

#define SQL_INSERT_TBL_USER      "INSERT TBL_USER(U_NAME,U_GENGDER) VALUES('darren','man');"
#define SQL_SELECT_TBL_USER      "SELECT * FROM TBL_USER;"

int king_mysql_select(MYSQL *handle) {
	
	//mysql_real_query -->sql
	if (mysql_real_query(handle, SQL_SELECT_TBL_USER, strlen(SQL_SELECT_TBL_USER))) {    //返回0成功
		printf("mysql_real_query : %s\n", mysql_error(handle));
		return -1;
	}
	//store  -->
	MYSQL_RES *res = mysql_store_result(handle);
	if (res == NULL) {
		printf("mysql_store_result : %s\n", mysql_error(handle));
		return -2;
	}
	//rows/fields
	int rows = mysql_num_rows(res);
	printf("rows :%d\n", rows);

	int fields = mysql_num_fields(res);
	printf("fields :%d\n", fields);
	//fetch
	MYSQL_ROW row;
	while ((row = mysql_fetch_row(res))) {

		int i = 0;
		for (i = 0; i < fields; i++) {
			printf("%s\t",row[i]);
		}
		printf("\n");
	}
	
	mysql_free_result(res);

	return 0;
}



int main(){

	MYSQL mysql;//ns使用数据库的句柄，通道

	if (NULL == mysql_init(&mysql)) {  //初始化mysql
		printf("mysql_init : %s\n", mysql_error(&mysql));
		return -1;
	}

	if (!mysql_real_connect(&mysql, KING_DB_SERVER_IP, KING_DB_USERNAME, KING_DB_PASSWORD,   //连接数据库，返回0不成功
		KING_DB_DEFAULTDB, KING_DB_SERVER_PORT, NULL, 0)) {

		printf("mysql_real_connect : %s\n", mysql_error(&mysql));
		return -2;
	}

	//mysql -->insert
	
#if 1
	if (mysql_real_query(&mysql, SQL_INSERT_TBL_USER, strlen(SQL_INSERT_TBL_USER))) {    //返回0成功
		printf("mysql_real_query : %s\n", mysql_error(&mysql));
	}
#endif
	king_mysql_select(&mysql);
	mysql_close(&mysql);

	return 0;
}

执行：

数据删除与存储过程调用

DELIMITER $$  #以$$结尾
CREATE PROCEDURE PROC_DELETE_USER(IN UNAME VARCHAR(32)) #创建存储函数
BEGIN
SET SQL_SAFE_UPDATES=0;
DELETE FROM TBL_USER WHERE U_NAME=UNAME;
SET SQL_SAFE_UPDATES=1;
END$$
CALL PROC_DELETE_USER('King') #调用函数删除

可以看到king被删除掉了

#include<mysql.h>
#include<stdio.h>
#include<string.h>
//C R U D -->增删改查

#define KING_DB_SERVER_IP        "192.168.190.129"
#define KING_DB_SERVER_PORT      3306

#define KING_DB_USERNAME         "admin"
#define KING_DB_PASSWORD         "123456"

#define KING_DB_DEFAULTDB        "KING_DB"

#define SQL_INSERT_TBL_USER      "INSERT TBL_USER(U_NAME,U_GENGDER) VALUES('King','man');"
#define SQL_SELECT_TBL_USER      "SELECT * FROM TBL_USER;"

#define SQL_DELETE_TBL_USER      "CALL PROC_DELETE_USER('King');"

int king_mysql_select(MYSQL *handle) {
	
	//mysql_real_query -->sql
	if (mysql_real_query(handle, SQL_SELECT_TBL_USER, strlen(SQL_SELECT_TBL_USER))) {    //返回0成功
		printf("mysql_real_query : %s\n", mysql_error(handle));
		return -1;
	}
	//store  -->
	MYSQL_RES *res = mysql_store_result(handle);
	if (res == NULL) {
		printf("mysql_store_result : %s\n", mysql_error(handle));
		return -2;
	}
	//rows/fields
	int rows = mysql_num_rows(res);
	printf("rows :%d\n", rows);

	int fields = mysql_num_fields(res);
	printf("fields :%d\n", fields);
	//fetch
	MYSQL_ROW row;
	while ((row = mysql_fetch_row(res))) {

		int i = 0;
		for (i = 0; i < fields; i++) {
			printf("%s\t",row[i]);
		}
		printf("\n");
	}
	
	mysql_free_result(res);

	return 0;
}



int main(){

	MYSQL mysql;//ns使用数据库的句柄，通道

	if (NULL == mysql_init(&mysql)) {  //初始化mysql
		printf("mysql_init : %s\n", mysql_error(&mysql));
		return -1;
	}

	if (!mysql_real_connect(&mysql, KING_DB_SERVER_IP, KING_DB_USERNAME, KING_DB_PASSWORD,   //连接数据库，返回0不成功
		KING_DB_DEFAULTDB, KING_DB_SERVER_PORT, NULL, 0)) {

		printf("mysql_real_connect : %s\n", mysql_error(&mysql));
		return -2;
	}

	//mysql -->insert
	printf("case : mysql --> insert\n");
#if 1
	if (mysql_real_query(&mysql, SQL_INSERT_TBL_USER, strlen(SQL_INSERT_TBL_USER))) {    //返回0成功
		printf("mysql_real_query : %s\n", mysql_error(&mysql));
	}
#endif
	king_mysql_select(&mysql);

	//mysql -->delete
	printf("case : mysql --> delete\n");

#if 1
	if (mysql_real_query(&mysql, SQL_DELETE_TBL_USER, strlen(SQL_DELETE_TBL_USER))) {    //返回0成功
		printf("mysql_real_query : %s\n", mysql_error(&mysql));
	}
#endif


	king_mysql_select(&mysql);
	mysql_close(&mysql);

	return 0;
}

执行过程：