catalogue

Database modeling and database and table building

mysql database programming connection and data insertion

  On Node   Insert information on the database Server through SQL statements on the Server

Query operation of mysql database

mysql data deletion and stored procedure call

Full code:

Database modeling and database and table building

1. Put forward the demand, save the user's name and gender and put them in a table.
2. First create a database in the database server.
3. Create a User table using the created database.
4. Display table.

CREATE DATABASE GQ_DB;    # Create database

SHOW DATABASES;

USE GQ_DB;                               # Use database

CREATE TABLE TBL_USER(    # Create User table
U_ID INT PRIMARY KEY AUTO_INCREMENT,
U_NAME VARCHAR(32),
U_GENGDER VARCHAR(8)
);

SHOW TABLES;                         # Display table

Note: when writing SQL statements. a. uppercase is recommended. b. add TBL and DB in front of each table. The first thing you think of before doing another project is database modeling.

mysql database programming connection and data insertion

First, establish a connection. The SQL statement is transmitted above.

 SELECT *FROM TBL_USER;

 

INSERT TBL_USER(U_NAME, U_GENGDER) VALUES('GQ', 'man');     # Insert name gender

 SELECT *FROM TBL_USER;

 

  On Node   Insert information on the database Server through SQL statements on the Server

First, install MySQL development tool on NS server and execute the following command on Ubuntu:

 sudo apt-get install libmysqlclient-dev

  Code implementation:

#include<stdio.h>

#include<mysql.h>

#include<string.h>


#define GQ_DB_SERVER_IP              "192.168.80.128"
#define GQ_DB_SERVER_PORT            3306

#define GQ_DB_SERVER_USERNAME     	 "admin"
#define GQ_DB_SERVER_PASSWORD        "521125"

#define GQ_DB_SERVER_DEFAULTBD  	 "GQ_DB" 

#define SQL_INSERT_TBL_USER          "INSERT TBL_USER(U_NAME, U_GENGDER) VALUES('GQ', 'man'); "




//C U R D  

int main(){
	
	MYSQL mysql;   //Operation handle mysql | (pipeline)

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

	if(!mysql_real_connect(&mysql, GQ_DB_SERVER_IP, GQ_DB_SERVER_USERNAME,
		GQ_DB_SERVER_PASSWORD, GQ_DB_SERVER_DEFAULTBD, GQ_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))){
		printf("mysql_real_query: %s\n",mysql_error(&mysql));
	}

	mysql_close(&mysql);

	return 0;
	
}

Compile command:  

  gcc -o mysql mysql.c -I /usr/include/mysql/        # - I add system header file

  gcc -o mysql mysql.c -I /usr/include/mysql/ -lmysqlclient     # - Lmysqlclient introduces libraries required for compilation

  Code result:

We modify the information in line 17 and add user information.

#define SQL_INSERT_TBL_USER          "INSERT TBL_USER(U_NAME, U_GENGDER) VALUES('GCJ', 'woman');

  Note: it is worth paying attention to the if judgment in lines 28 and 33. Most of the Linux API s return 0 as success (as in line 42), while the interfaces in lines 28 and 33 provided by mysql are just the opposite.

Query operation of mysql database

At least four steps are required:

int Gq_mysql_select(MYSQL *handle){

	//mysql_real_query --> sql
	if(mysql_real_query(handle, SQL_SELECT_TBL_USER, strlen(SQL_SELECT_TBL_USER))){
		printf("mysql_real_query: %s\n",mysql_error(handle));
		return -1;
	}
	

	//sotre -->
	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;

}

  Code run result:

mysql data deletion and stored procedure call

Enter the following command in MYSQL workbench:

DELETE FROM TBL_USER WHERE U_NAME='GQ';

         The following error occurred: DELETE FROM TBL_USER WHERE U_NAME='GQ '      Error Code: 1046. No database selected Select the default DB to be used by double-clicking its name in the SCHEMAS list in the sidebar.  

         After version 5.6, the method of deleting by primary key is provided. U_NAME is not a primary key (key). If we have to do so, we can set it to safe mode:

SET SQL_SAFE_UPDATES=0;                             # Set safe mode
DELETE FROM TBL_USER WHERE U_NAME='GQ';
SET SQL_SAFE_UPDATES=1;

SELECT *FROM TBL_USER;

 

You can see that the user GQ has been deleted successfully.

            During code implementation, we need to implement the three statements together, which can be but prone to errors. Here we introduce a stored procedure and define a stored procedure:

DELIMITER @@         # Define the identifier and what the stored procedure ends with
CREATE PROCEDURE PRCO_DELETE_USER(IN UNAME VARCHAR(32)) # create stored procedure
BEGIN
SET SQL_SAFE_UPDATES=0;
DELETE FROM TBL_USER WHERE U_NAME=UNAME;
SET SQL_SAFE_UPDATES=1;
END @@        

CALL PROC_DELETE_USER('GQ')                         # D call stored procedure

          Let's see how to define a stored procedure in the code implementation.

#define SQL_DELETE_TBL_USER			 "CALL PROC_DELETE_USER('GQ')"


// mysql -->delete
	printf("case: mysql -->delete\n");
#if 1
	if(mysql_real_query(&mysql, SQL_DELETE_TBL_USER, strlen(SQL_DELETE_TBL_USER))){
		printf("mysql_real_query: %s\n",mysql_error(&mysql));
	}
		
#endif

We insert the user GQ first and then delete the result:

 

Full code:

#include<stdio.h>

#include<mysql.h>

#include<string.h>


#define GQ_DB_SERVER_IP              "192.168.80.128"
#define GQ_DB_SERVER_PORT            3306

#define GQ_DB_SERVER_USERNAME     	 "admin"
#define GQ_DB_SERVER_PASSWORD        "521125"

#define GQ_DB_SERVER_DEFAULTBD  	 "GQ_DB" 

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

#define SQL_DELETE_TBL_USER			 "CALL PROC_DELETE_USER('GQ')"

int Gq_mysql_select(MYSQL *handle){

	//mysql_real_query --> sql
	if(mysql_real_query(handle, SQL_SELECT_TBL_USER, strlen(SQL_SELECT_TBL_USER))){
		printf("mysql_real_query: %s\n",mysql_error(handle));
		return -1;
	}
	

	//sotre -->
	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;

}


//C U R D  

int main(){
	
	MYSQL mysql;   //Operation handle mysql | (pipeline)

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

	if(!mysql_real_connect(&mysql, GQ_DB_SERVER_IP, GQ_DB_SERVER_USERNAME,
		GQ_DB_SERVER_PASSWORD, GQ_DB_SERVER_DEFAULTBD, GQ_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))){
		printf("mysql_real_query: %s\n",mysql_error(&mysql));
	}

#endif

	Gq_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))){
		printf("mysql_real_query: %s\n",mysql_error(&mysql));
	}
		
#endif
	
	Gq_mysql_select(&mysql);


	mysql_close(&mysql);

	return 0;
	
}