Linux C-language operation MySQL

Original: Linux C language Operation MySQL

Introduction to 1.MySQL Databases

MySQL is an open source code of small relational database management system, small size, fast, low overall cost, open source. MySQL has the following features:

(1) Use C and C + + written, and use a variety of compilers to test, to ensure the portability of the source code.

(2) Support multi-threading, utilize CPU resources, support multi-user.

(3) A large database with thousands records can be processed.

(4) can be used as a separate application in the Client server network environment, but also as a library and embedded in other software.

2. mysql database installation requires three packages to install the MySQL database:

(1) Server

(2) Client

(3) Development Library-If you develop in C or C language, you have to install the development library.

mysql-server-5.5.8-1.rhel5.i386.rpm

mysql-client-5.5.8-1.rhel5.i386.rpm

MYSQL-DEVEL-5.5.8-1.RHEL5.I386.RPM can be installed with RPM-IVH under Linux.

3. mysql-related directory

/usr/bin MySQL client program or script

/usr/sbin mysqld Server Related programs

/var/lib/mysql log file, database data file storage directory

/usr/share/doc/packages User Manual, developing document storage directory

/usr/include/mysql Interface Function library header file storage directory

/usr/lib/mysql interface function dynamic Link Inventory Place directory

/usr/share/mysql error message and character set file storage directory

/usr/share/sql-bench Basic Program Storage Directory

4. Basic operation of MySQL database

(1) Start MySQL Server service MySQL start

(2) Stop MySQL Server service MySQL stop

(3) Root user refers to the root directory under the MySQL database and not the root user under Linux

Add password for root user: mysqladmin-u root password 123//Add new password for root user 123

Modify the root user password to 234:mysqladmin-u root-p password 234

Enter Password:

Or: mysqladmin-u root-p password

Enter Password:

New Password:

Confirm New Password:

(4) Establishment of databases and tables

First enter the user, such as under the root user to establish a Db_record database: Mysql-u root-p

Enter Password:

Create DATABASE Db_record

Then switch to the current database to create the table: Use Db_record Create tables Friends (name Char (20), telephone VarChar (+));

Finally, insert data into the table insert into Friends (name,telephone) Values ("John", "222");

Query: SELECT * from friends;

(5) Establishment, deletion and authorization of users

Insert into Mysql.user (Host,user,password) VALUES ("localhost", "min", Password ("123"));

Flush privileges; Refresh Permissions List

Grant all privileges the tree.* to [e-mail protected] identified by ' 123 '; Authorized min Users have ownership of the tree database

Also: Grant SELECT, Insert,update,delete on db_record.* to [email protected] identified by ' 123 ';

Delete User: Deletes the user's records from the MySQL database:

Use MySQL//switch database

Delete from user where user= ' min ' and host= ' localhost '; Delete min User's records

Flush privileges; Refresh Permissions

5. C-language operation MySQL Database

Steps:

(1) Initialize the MySQL library, the library can be the Mysqlclient C client library, or mysqld Embedded server library, function Mysql_library_init (), for the application linked to the client, provides improved memory management capabilities.

(2) Initialize the connection handler mysql_init (); Primarily used to initialize the database identifiers for connection preparation

(3) Connect to MySQL server, Mysql_real_connect () function.

(4) Call Mysql_close () to close the connection to the MySQL server

(5) Call the Mysql_library_end () function to end the use of the MySQL library, including operations such as freeing memory, preventing memory leaks, and so on.

The following is an example of using the C language to operate the MySQL database:

/**1. Connecting and querying the MySQL database connection mysq database steps: (1) call Mysql_library_init () to initialize the MySQL database, provide initialization database management, and provide improved memory management capabilities for applications linked to clients (2) Mysql_init () Initializes the connection handler and calls the Mysql_real_connect () function to connect to the MySQL server (3) mysql_close () function to close the connection to the MySQL server (4) Mysql_libary_ The End function ends the use of the MySQL library, including operations such as freeing memory mysql_library_init (int argc,char**argv,char**groups)//used to receive parameters passed by the application, if the application does not pass parameters, Then the first parameter in the ARGC=0,ARGV pointer array is the application name groups is the null-terminated string in the list of selected activities in the options file groups,groups can also be nullmysql_init (Mysql*mysql) The passed parameter is the database identifier that is used to assign or initialize the MySQL object that is compatible with Mysql_real_connect, and if the actual parameter of MySQL is a null pointer, the new object is initialized, otherwise the object is initialized, and the address of the object is returned Mysql_real_ Connect (MYSQL *mysql,const char* host,const char* user,const char* passwd,const char* db,unsigned int port,const char* Uni x_socket,unsigned long Client_flag)//try to establish a connection with the MySQL database engine running on the host//The first parameter is the MySQL fabric address that is available, host must be the hostname or IP, If null or localhost is considered a local host, user is the username, passwd is the password, DB is the database name, port is the port number, Unix_socket is the socket that should be used, Client_flag parameter value is usually 0mysql_close (mysql*mysql)//Close the connection, freeing the object's memory space Mysql_library_end () if the function is not called, it can cause a memory leak of 2. MySQL function using query MySQL database: int mysql_query (mysql*mysql,const char* query)              The int mysql_real_query (MySQL *mysql,const char* query,unsinged long length)//mysql_query function is a null-terminated string query that points to the sq L query, should not be added to the statement, or/g//and Mysql_real_query is a query that ends with a count string if the query string contains binary data, it can only be used with mysql_real_query, because the binary data may contain/ 0, resulting in incorrect length of the computed string mysql_real_query is faster than mysql_query because the latter requires strlen to calculate the length of the query string mysql_res *mysql_store_result (MySQL *mysql) Mysql_res *mysql_use_result (MySQL *mysql)//mysql_store_result reads the result set of the query to the client and puts it in a 1 mysql_res structure, and returns a null pointer if the read fails The Mysql_use_result function is to initialize the result set retrieval, rather than reading the data to the client, which must be retrieved per row through the Mysql_fetch_row () function//and read only one row at a time compared to Mysql_store_result. So fast, and without cache, memory consumption, only the current line and the communication buffer allocation memory can be Mysql_row mysql_fetch_row (mysql_res*result)//actually retrieve rows from the server, retrieve the next row of the result set unsigned Long *mysql_fetch_lengths (mysql_res *result)//is used to return the length of the column in the current row, which helps optimize the information, avoids the use of the Strlen function, and for a result set containing two of data, only this function void The Mysql_free_result (mysql_res*result) function frees the result set memory allocated for Mysql_store_result,mysql_use_result Mysql_store_ Result returns the results of all queries to the client, not only continuous access to the query results, but also the ability to move forward or backward in the result set with the Mysql_row_seek () function, and to call Mysql_num_rows () to discover how many rows on the other hand, if the data is large, May cause memory Overflow Mysql_use_result ClientTo save the query result set of less memory, return only one at a time, so faster than the former but not random access to the result set, only sequential access, can not know the query structure of the total number of rows must be fast processing query structure, otherwise affect the performance of the server, because the query operation of the session still exists **///GCC-G-O MySQL mysql1.c-i/usr/include/mysql-l/usr/lib-lmysqlclient-lpthread-lm-lrt-ldl//$ (mysql_config--cflags) $ (mysql_ Config--libs) #include <mysql.h> #include <stdio.h> #include <stdlib.h> #include <string.h> Static char* server_groups[]={"embedded", "Server", "This_program_server", (char*) Null};int main () {MySQL mysql; Mysql_res *res; Mysql_row Row;char sqlcmd[200];int t,r;mysql_library_init (0,null,server_groups);//Initialize MySQL database Mysql_init (&  MySQL);//Initialize the MySQL identifier to connect if (!mysql_real_connect (&mysql, "localhost", "root", "root", "Db_record", 0,null,0)) { fprintf (stderr, "Unable to connect to database, error reason:%s/n", Mysql_error (&mysql));} else {puts ("database connection succeeded");//First insert a record into the database sprintf (sqlcmd, "%s", "INSERT into Friends (name,telephone) Values (' xx ', ' xx ')"); mysql_query (&mysql,sqlcmd); sprintf (sqlcmd, "%s", "SELECT * from friends"); T=mysql_real_query (&mysql,sqlcmd, (unsigned int) strLen (sqlcmd)); if (t) {printf ("Query database failed%s/n", Mysql_error (&mysql));} else {res=mysql_store_result (&mysql);//Returns the entire result set of the query while (Row=mysql_fetch_row (res)) {//mysql_fetch_row The next row of the result set For (T=0;t<mysql_num_fields (res); t++) {//The number of columns in the result set printf ("%s/t", Row[t]);} printf ("/n");} Mysql_free_result (res);//operation completed, query result set}mysql_close (&AMP;MYSQL);//Close database connection}mysql_library_end ();//close MySQL library return EXIT _success;} Compile Link:

Gcc-g-o  mysql MYSQL1.C-I/USR/INCLUDE/MYSQL-L/USR/LIB-LMYSQLCLIENT-LPTHREAD-LM-LRT-LDL

can also: gcc-g-o mysql mysql.c $ (mysql_config--cflags) $ (mysql_config--libs)

Explain:

(1) Mysql_config--cflags search for MySQL compiler related header files and other information.

(2) Mysql_config--libs   look for the library functions required for MySQL compilation.

(3)-l/usr/lib indicates the path that the static library is looking for. You can also not write here, because the system will go to the default/lib,/usr/lib to find the library function. If LIBMYSQLCLIENT.A is under/usr/lib/mysql, then-l/usr/lib/mysql this directory.

(4) Non-standard library functions, linked with-lmysqlclient. The original library is named Libmysqlclient.a minus Lib and. A.

(5)-I is a header file and other information, and include. (6) The search order of dynamic Link libraries:

A. Ld_library_path settings in the directory.

B./etc/ld.so.conf add to the directory in this file.

C. The default/usr/lib,/lib directory. (7) Search Order of header files

A.-I in the specified directory to find

B.GCC environment variable Specifies the directory c_include_path, Cplus_include_path, Objc_include_pathC./usr/include/usr/local/include and other directories.

(8) Search Order of static libraries

A. Static library search path specified by-l

B.GCC environment variable Library_path

C./lib,/usr/lib,/usr/local/lib If the MySQL database does not start up, you can perform the Killall mysqld first, and then try the service MySQL start again.

The basic operation of MySQL database is summed up here.

Linux C-language operation MySQL