MySQL basic and MySQL C API programming

MySQL basic and MySQL C API programming

One, MySQL for Windows installation:

1. Download:

The Officer network download can:
http://www.mysql.com/downloads/

2. Installation:

There is nothing to say, a fool, and nothing to pay attention to.

3. Configuration:

Add System Variables mysql_home and modify path to allow the system to find the MySQL command by default. (/bin below the installation directory)

Ps:
To add, some friends may download a free install version, copy to a place to do, this time, the configuration is still needed, but after the configuration is complete, you need to manually install the MySQL service. Because we want to build MySQL server side, but also build MySQL client, server side is need to open the service, so need to manually install and start the MySQL service. There is also to note that some of the online Wamp integration installation package, only contains the SQL Web related tools, is not complete, need to install that version of more than 200 m.

Several of the relevant commands are as follows:

mysqld -install //安装mysql服务mysqld -remove  //卸载mysql服务net start mysql  //启动mysql服务net stop mysql   //停止mysql服务mysqladmin -version //查看mysql服务是否安装成功

You can also configure MySQL through the Windows service tools.

second, the MySQL Foundation:

1. Connect to MySQL:

First of all, we are now learning how to use MySQL, our MySQL server is installed on this machine, and the MySQL client is also installed on this machine. We log in to MySQL is through the cmd command line tool, with the client command MySQL login to the native MySQL server side, the native service is MySQL, the process is mysql.exe.

After the installation is complete, the default host and user are:

Where the passwords are empty.

We have two ways to connect to the MySQL server, do not specify Host/user/password default is to follow the way of localhost anonymous user connection.

Exit the MySQL server using the command exit or quit.

mysql //localhost+匿名方式登录mysql -u root -p //localhost+root+password方式登录exit //退出mysql服务器

2. Modify the MySQL server user password:

mysqladmin -u root -p password //中间提示输入当前密码一次，新密码两次

3. mysql basic syntax:

• 1. Create a database with the name Runoob

mysqladmin -u root -p create RUNOOB

• 2. Delete the database with the name Runoob

mysqladmin -u root -p drop RUNOOB

• 3. Display the database:

mysql> show databases; //注意后面要加;号，表示一条mysql语句结束

• 4. Select the database:

mysql> use RUNOOB;

• 5. Data type:

http://www.runoob.com/mysql/mysql-data-types.html

• 6. Displays the data table under the current database:

mysql> show tables;

• 7. Create a data table:

mysql> CREATE TABLE runoob_tbl(    -> NOTNULL AUTO_INCREMENT,    -> runoob_title VARCHAR(100NOTNULL,    -> runoob_author VARCHAR(40NOTNULL,    -> DATE,    -> PRIMARY KEY ( runoob_id )    -> );

• 8. Delete the data sheet:

mysql> DROP TABLE runoob_tbl

• 9. Insert Data:

mysql> INSERT INTO runoob_tbl     ->(runoob_title, runoob_author, submission_date)    ->VALUES    ->("Learn PHP", "John Poul", NOW());

• 10. Query the data in the data table:

SELECT * from runoob_tbl

• 11.Show syntax for:

 SHOW DATABASES;List the databases on the MySQL server SHOW TABLES [ from db_name];List tables in a database SHOW TABLE STATUS [ from db_name];Lists table information for the database, in more detail SHOW COLUMNS  from Tbl_name [ from db_name];Lists column information for the table, with the SHOW fields  from Tbl_name [ from db_name],DESCRIBE tbl_name [Col_name] SHOW  Full COLUMNS  from Tbl_name [ from db_name];Lists column information for the table, in more detail, with the show  tbl_name [  from db_name] show INDEX  from Tbl_name [ from db_name];List index information for a table SHOW STATUS;To list Server status information SHOW VARIABLES;List MySQL system parameter values SHOW processlist;View the current MySQL query process SHOW GRANTS  for user;List the user's authorization commands

• 12.where clause:

mysql> SELECT * from runoob_tbl WHERE runoob_author=‘Sanjay‘; //查询某一个符合条件的表项

• 13.update query:

//查询并修改表中的一项，最后更新表格mysql> UPDATE runoob_tbl     -> SET runoob_title=‘Learning JAVA‘     -> WHERE runoob_id=3;

• 14.delete statement:

mysql> DELETE FROM runoob_tbl WHERE runoob_id=3; //删除表中的某一项

• 15.ALTER command:

Mysql> ALTERTABLETestalter_tbl DROP i;//Remove the I field from the table aboveMysql> ALTERTABLETestalter_tbl ADD i INT;//Add the I field to the table testalter_tbl and define the data typeMysql> ALTERTABLETestalter_tbl MODIFY C CHAR (Ten);//Change the type of field C from char (1) to char (TEN)Mysql> ALTERTABLETestalter_tbl-MODIFY J BIGINT not NULL DEFAULT -;//Specifies that the field J is not NULL and the default value isMysql> ALTERTABLETestalter_tbl ALTER ISETDEFAULT +;//Use ALTER to modify the default value of a fieldMysql> ALTERTABLETestalter_tbl ALTER i DROP DEFAULT;//Use the ALTER command and the drop clause to remove the default value of the fieldMysql> ALTERTABLETestalter_tbl RENAME toALTER_TBL;//Modify table name

• 16. Regular Expressions:

Regular expressions are generic, like C and Linux shells.

Mysql>SELECTName fromPerson_tblWHEREName REGEXP' ^st ';//Find all data in the Name field beginning with ' St 'Mysql>SELECTName fromPerson_tblWHEREName REGEXP' ok$ ';//Find all data that ends with ' OK ' in the Name fieldMysql>SELECTName fromPerson_tblWHEREName REGEXP' Mar ';//Find all data with the ' Mar ' string in the Name fieldMysql>SELECTName fromPerson_tblWHEREName REGEXP' ^[aeiou]|ok$ ';//Find all data in the Name field that begins with a vowel character and ends with an ' OK ' string

4. mysql key and index concept:

Http://www.runoob.com/mysql/mysql-index.html
http://blog.csdn.net/duck_arrow/article/details/8264686
Http://www.jb51.net/article/34037.htm

Third, MySQL C API programming:

The essence is that C language calls the Libmysql.lib library, using its provided functions to connect, access, and modify the contents of the MySQL database.

1. mysql Structure:

This structure is mainly used to connect and save some connected information.

typedef structst_mysql {NET net;/ * Communication parameters * /Gptr connector_fd;/ * CONNECTORFD for SSL * / Char*host,*user,*passwd,*unix_socket, *server_version,*host_info,*info,*db;unsigned intPort,client_flag,server_capabilities;unsigned intProtocol_version;unsigned intField_count;unsigned intServer_status;unsigned Longthread_id;/ * Id for connection in server * /My_ulonglong affected_rows; My_ulonglong insert_id;/ * ID if insert on table with NEXTNR * /My_ulonglong Extra_info;/ * Used by mysqlshow * / unsigned LongPacket_length;enumMysql_status status; Mysql_field *fields; Mem_root Field_alloc; My_bool Free_me;/ * If free in Mysql_close * /My_bool Reconnect;/ * Set to 1 if automatic reconnect * / structSt_mysql_options options;Charscramble_buff[9];structCharset_info_st *charset;unsigned intServer_language;} MYSQL;

2. Mysql_res structure:

This structure mainly returns the results of some operational databases (SELECT, SHOW, DESCRIBE, explain, etc.), which are "datasets".

typedef  struct  st_mysql_res {my_ulonglong row_count; unsigned  int  field_count, Current_field; Mysql_field *fields; Mysql_data *data; Mysql_rows *data_cursor; Mem_root Field_alloc;            Mysql_row ROW; /* If unbuffered Read */    Mysql_row Current_row; /* buffer to current row */ unsigned        long  *lengths; /* column lengths of current row */        MYSQL *handle;            /* for unbuffered reads */ My_bool EOF; /* used my mysql_fetch_row */} mysql_res;  

3. MySQL C API Programming Steps:

Specifically what functions, you can go to the Internet to search the relevant introduction, here to talk about the programming steps:

(1), first we want to include MySQL header file, and link MySQL dynamic library. That is, add the following statement:

#include <WinSock2.h>      // 进行网络编程需要winsock2.h   #include <mysql.h>   #pragma comment(lib, "libmysql.lib")  

(2), create a MySQL variable. Such as:

 MYSQL mysql;

(3), initialize the MySQL variable.

  mysql_init(&mysql);

(4), call the Mysql_real_connect function to connect to the MySQL database. The Mysql_real_connect function is prototyped as follows:

MYSQL *  STDCALL 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 clientflag);    参数说明：mysql--前面定义的MYSQL变量；host--MYSQL服务器的地址；user--登录用户名；passwd--登录密码；db--要连接的数据库；port--MYSQL服务器的TCP服务端口；unix_socket--unix连接方式，为NULL时表示不使用socket或管道机制；clientflag--Mysql运行为ODBC数据库的标记，一般取0。连接失败时该函数返回0。

(5), call the Mysql_real_query function for database query. The Mysql_real_query function is prototyped as follows:

int  STDCALL mysql_real_query(MYSQL *mysql, const char *q, unsigned long length);参数说明：mysql--前面定义的MYSQL变量；q--SQL查询语句；length--查询语句的长度。查询成功则该函数返回0。

(6) Get query result data by calling the Mysql_res variable returned by the Mysql_store_result or Mysql_use_result function.

两个函数的原型分别为：MYSQL_RES *     STDCALL mysql_store_result(MYSQL *mysql);MYSQL_RES *     STDCALL mysql_use_result(MYSQL *mysql);这两个函数分别代表了获取查询结果的两种方式。第一种，调用端，然后读取；第二种，调用mysql_use_result初始化检索，以便于后面一行一行的读取结果集，而它本身并没有从服务器读取任何数据，这种方式较之第一种速度更快且所需内存更少，但它会绑定服务器，阻止其他线程更新任何表，而且必须重复执行mysql_fetch_row读取数据，直至返回NULL，否则未读取的行会在下一次查询时作为结果的一部分返回，故经常我们使用mysql_store_result。

(7), call the Mysql_fetch_row function to read the result set data.

上述两种方式最后都是重复调用mysql_fetch_row函数读取数据。mysql_fetch_row函数的原型如下：MYSQL_ROW STDCALL mysql_fetch_row(MYSQL_RES *result);参数result就是mysql_store_result或mysql_use_result的返回值。该函数返回MYSQL_ROW型的变量，即字符串数组，假设为row，则row[i]为第i个字段的值。当到结果集尾部时，此函数返回NULL。

(8), after the result set is exhausted, call the Mysql_free_result function to release the result set to prevent memory leaks. The Mysql_free_result function is prototyped as follows:

void  STDCALL mysql_free_result(MYSQL_RES *result);

(9), when the MySQL database is no longer queried, call the Mysql_close function to close the database connection. The prototype of the Mysql_close function is:

void STDCALL mysql_close(MYSQL *sock);

4. MySQL C API Programming Example:

#if defined (_WIN32) | | defined (_WIN64) //In order to support compilation on the Windows platform #include <windows.h>#endif#include <stdio.h>#include <stdlib.h>#include "mysql.h" //On my machine this file is under/usr/local/include/mysql //define a macro for database operations, or you can leave it behind and write directly into the code#define SELECT_QUERY "Select username from tbb_user where UserID =%d"intMainintargcChar**ARGV)//char **argv equivalent to char *argv[]{MYSQL mysql,*sock;//Defines a handle to the database connection, which is used for almost all MySQL functionsMysql_res *res;//query result set, struct typeMysql_field *FD;//structure that contains field informationMysql_row ROW;//array of strings that hold a row of query results    Charqbuf[ the];//Store query SQL statement string    if(ARGC! =2) {//Check input parameters        fprintf(stderr,"Usage:mysql_select <userid>\n\n");Exit(1); } mysql_init (&mysql);if(! (sock = Mysql_real_connect (&mysql,"localhost","Dbuser","Dbpwd","9tmd_bbs_utf8",0Null0))) {fprintf(stderr,"couldn ' t connect to engine!\n%s\n\n", Mysql_error (&mysql)); Perror ("");Exit(1); }sprintf(Qbuf,select_query,atoi (argv[1]));if(mysql_query (SOCK,QBUF)) {fprintf(stderr,"Query failed (%s) \ n", Mysql_error (sock));Exit(1); }if(! (Res=mysql_store_result (sock))) {fprintf(stderr,"couldn ' t get result from%s\n", Mysql_error (sock));Exit(1); }printf("Number of fields returned:%d\n", Mysql_num_fields (res)); while(row = mysql_fetch_row (res)) {printf("ther userid #%d ' s username is:%s\n", Atoi (argv[1]), (((row[0]==null) && (!strlen(row[0]))) ?"NULL": row[0])) ;puts("Query OK!\n") ;    } mysql_free_result (res); Mysql_close (sock);Exit(0);return 0;//. Join this line for compatibility with most compilers}

MySQL basic and MySQL C API programming