C basic MARIADB processing of simple examples _c language

Introduction

MARIADB is a very good gray source database. This is used directly to solve business problems.

Business requirements:

Now the database is represented by the talent list. Suddenly we need to process the data according to the month.

For example, enter a player ID to find out what this player has seen a few times during the month. Let's build an environment first.

Operating system:

Linux version 4.4.0-22-generic (buildd@lgw01-41)
(gcc version 5.3.1 20160413 (Ubuntu 5.3.1-14ubuntu2)) 
#40- Ubuntu SMP Thu may 22:03:46 UTC 2016

First install the MARIADB database

sudo apt-get install mariadb-server
sudo apt-get install mariadb-client sudo apt-get install libmariadb2
sudo apt-get install libmariadb-client-lgpl-dev
sudo apt-get install Libreoffice-mysql-connector

The following is a C access mariadb driver. Here to pull a little, the current about mariadb do not understand the problem, search directly as MySQL began to search.

MARIADB installation is successful, the default is open, look at the picture below describes

The test environment is built behind the first look at Oss_musicelves.sql

--MySQL dump 10.10----host:localhost database:oss_log----------------------------------------------------------
Server version 5.5.24-tmysql-1.4/*!40101 SET @OLD_CHARACTER_SET_CLIENT =@ @CHARACTER_SET_CLIENT * *;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS =@ @CHARACTER_SET_RESULTS * *;
/*!40101 SET @OLD_COLLATION_CONNECTION =@ @COLLATION_CONNECTION * *;
/*!40101 SET NAMES UTF8 * *;
/*!40103 SET @OLD_TIME_ZONE =@ @TIME_ZONE * *;
/*!40103 SET time_zone= ' +00:00 ' * *;
/*!40014 SET @OLD_UNIQUE_CHECKS =@ @UNIQUE_CHECKS, unique_checks=0 * *;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS =@ @FOREIGN_KEY_CHECKS, foreign_key_checks=0 * *;
/*!40101 SET @OLD_SQL_MODE =@ @SQL_MODE, sql_mode= ' no_auto_value_on_zero ' * *;

/*!40111 SET @OLD_SQL_NOTES =@ @SQL_NOTES, sql_notes=0 * *;
----Table structure for table ' oss_musicelves '--DROP Table IF EXISTS ' oss_musicelves '; CREATE TABLE ' oss_musicelves ' (' record_id ' bigint not null auto_increment, ' account_id ' bigint ' is not null, ' serv er_id ' int (one) not NULL, ' Char_id ' bigint not null, ' char_sex ' int (one) not null, ' type_id ' int (one) not null, ' TimeStamp ' datetime NOT NULL DEFAULT  ' 0000-00-00 00:00:00 ', ' ptype ' int (one) not null, ' Specifytype ' int (one) not null, ' Childtype ' int (one) not NULL, PRIMARY

Key (' record_id '), key ' Idx_specifytype ' (' Specifytype ')) Engine=innodb auto_increment=32 DEFAULT charset=latin1;
----dumping data for table ' oss_musicelves '--/*!40000 ALTER table ' oss_musicelves ' DISABLE KEYS * *;
LOCK TABLES ' oss_musicelves ' WRITE; INSERT into ' oss_musicelves ' VALUES (1,411948833,84869352,27899597414400801,0,1812, ' 2016-05-31 14:27:41 ', 0,1,1), ( 2,1344702709,90964200,30422720614402293,0,1812, ' 2016-05-31 14:58:26 ', 0,1,1, ( 3,706409913,90964200,30422720614401465,1,1812, ' 2016-05-31 14:58:27 ', 0,1,2, ( 4,706409913,392964857,30422720614401465,1,1812, ' 2016-05-31 14:58:59 ', 0,2,4, ( 5,1344702709,392964857,30422720614402293,0,1812, ' 2016-05-31 14:58:59 ', 0,2,4, ( 6,706409913,90964200,30422720614401465,1,1812, ' 2016-05-31 15:04:52 ', 0,1,2,(7,706409913,392964857,30422720614401465,1,1812, ' 2016-05-31 15:05:54 ', 0,2,4), ( 8,1344702709,392964857,30422720614402293,0,1812, ' 2016-05-31 15:05:54 ', 0,2,4, ( 9,1344702709,90964200,30422720614402293,0,1812, ' 2016-05-31 15:10:29 ', 0,1,1, ( 10,706409913,90964200,30422720614401465,1,1812, ' 2016-05-31 15:10:32 ', 0,1,2, ( 11,1344702709,392964857,30422720614402293,0,1812, ' 2016-05-31 15:10:54 ', 0,2,4, ( 12,3145910262,90964200,29520779366416374,1,1812, ' 2016-05-31 15:30:00 ', 0,1,1, ( 13,1372825842,90964200,30173879500803314,1,1812, ' 2016-05-31 15:30:01 ', 0,1,2, ( 14,3145910262,392964857,29520779366416374,1,1812, ' 2016-05-31 15:30:04 ', 0,2,4, ( 15,1372825842,392964857,30173879500803314,1,1812, ' 2016-05-31 15:30:04 ', 0,2,4, ( 16,3145910262,392964857,29520779366416374,1,1812, ' 2016-05-31 15:34:24 ', 0,2,4, ( 17,1372825842,392964857,30173879500803314,1,1812, ' 2016-05-31 15:34:24 ', 0,2,4, ( 18,706409913,90964200,30422720614401465,1,1812, ' 2016-05-31 15:40:14 ', 0,1,1, ( 19,1344702709,90964200,30422720614402293,0,1812, ' 2016-05-31 15: 40:16 ', 0,1,2), (20,3145910262,392964857,29520779366416374,1,1812, ' 2016-05-31 15:42:19 ', 0,2,4), ( 21,1372825842,392964857,30173879500803314,1,1812, ' 2016-05-31 15:42:19 ', 0,2,4, ( 22,1027763684,90964200,30175730790400484,0,1812, ' 2016-05-31 16:56:33 ', 1,1,1, ( 23,1372825842,90964200,30173879500803314,1,1812, ' 2016-05-31 16:56:50 ', 0,1,2, ( 24,1372825842,392964857,30173879500803314,1,1812, ' 2016-05-31 16:57:37 ', 0,2,3, ( 25,1027763684,392964857,30175730790400484,0,1812, ' 2016-05-31 16:57:37 ', 1,2,3, ( 26,1372825842,392964857,30173879500803314,1,1812, ' 2016-05-31 17:04:33 ', 0,2,3, ( 27,1027763684,392964857,30175730790400484,0,1812, ' 2016-05-31 17:04:33 ', 1,2,3, ( 28,1027763684,90964200,30175730790400484,0,1812, ' 2016-05-31 17:14:15 ', 1,1,2, ( 29,1372825842,392964857,30173879500803314,1,1812, ' 2016-05-31 17:14:50 ', 0,2,3, ( 30,1027763684,392964857,30175730790400484,0,1812, ' 2016-05-31 17:14:50 ', 1,2,3, (
31,751699770,90964200,30175199027201850,1,1812, ' 2016-05-31 18:14:59 ', 1,1,1);
UNLOCK TABLES; /*!40000 ALTER TABLE ' Oss_Musicelves ' ENABLE KEYS * *;

/*!40103 SET time_zone= @OLD_TIME_ZONE * *;
/*!40101 SET sql_mode= @OLD_SQL_MODE * *;
/*!40014 SET foreign_key_checks= @OLD_FOREIGN_KEY_CHECKS * *;
/*!40014 SET unique_checks= @OLD_UNIQUE_CHECKS * *;
/*!40101 SET character_set_client= @OLD_CHARACTER_SET_CLIENT * *;
/*!40101 SET character_set_results= @OLD_CHARACTER_SET_RESULTS * *;
/*!40101 SET collation_connection= @OLD_COLLATION_CONNECTION * *; /*!40111 SET sql_notes= @OLD_SQL_NOTES * *;

The main function of this oss_musicelves. sql file is to create a oss_musicelves database and populate it with data.

There's also a script to build the environment Mariadb_test.sql and the previous SQL file in the same directory

# Creating a test Database Create DB
oss_log;

# Enter the Oss_log database use
oss_log;

# Create Oss_musicelves database and import data
source Oss_musicelves.sql;

# CREATE TABLE and data
2016_6_1_oss_musicelves select * from Oss_musicelves;
CREATE TABLE 2016_6_2_oss_musicelves select * from Oss_musicelves;
CREATE TABLE 2016_6_3_oss_musicelves select * from Oss_musicelves;
CREATE TABLE 2016_6_4_oss_musicelves select * from Oss_musicelves;
CREATE TABLE 2016_6_5_oss_musicelves select * from Oss_musicelves;
CREATE TABLE 2016_6_9_oss_musicelves select * from Oss_musicelves;
CREATE TABLE 2016_6_10_oss_musicelves select * from Oss_musicelves;
CREATE TABLE 2016_6_12_oss_musicelves select * from Oss_musicelves;

# query table to create a successful show
tables;

# Here's where the data is processed
SELECT DISTINCT table_name from INFORMATION_SCHEMA.COLUMNS where table_name like ' 2016_6_%_oss_ Musicelves ';

Directly in the MARIADB console directly in the brush. The specific environment is as follows

Here, the environment is basically built. MARIADB introduction and so on, completely can be used as MySQL learning temperature.

Objective

The above problem is originally select * From Oss_musicelves; can solve the problem.

Here you need to enter a year and a month plus some special conditions. SELECT * from%_%_%_oss_musicelves; Solve. It can be solved by simply using SQL scripts. It's very complicated. Not familiar with.

This is done first with the shell script

Touch getmouths.sh
chmod +x getmouths.sh
VI getmouths.sh

The specific script content is as follows

#!/bin/sh

#得到输入的玩家ptid
If [$#-lt 1]
then
  echo "uage: $ [Ptid]"
  exit-1
fi
ptid=$1

mouth=$ (Date +%m | sed s '/^0//')
#第一个参数是月份
If [$#-ge 2]
then
  mouth=$2
fi

#第二个参数是年
year=$ (date +%y)
if [$#-ge 3]
then
  year=$3
fi

#得到查询的随机表名
tbname= "\" ${year}_${mouth} _%_oss_musicelves\ ""

#这里得到mysql all legitimate table names
Rm-rf __tmp touch
__tmp

#开始查询数据库了, you need to start this script with root privileges
mysql-e "SELECT DISTINCT table_name from INFORMATION_SCHEMA.COLUMNS where table_name like $tbname" | awk ' nr>1 ' |  While read name
does
  mysql-e "SELECT COUNT (*) from Oss_log. $name where Specifytype = 1 and char_id = $ptid" | awk ' Nr>1 ' | While the read cut does
    echo "$name: $cut"
    echo $cut >>
    __tmp

break done Data in Statistics
sum=$ (cat __tmp | awk ' {s+=$1} end {print S} ')
rm-rf __tmp

# Final output statistic
echo ' $year-$mouth sum : $sum "

Use script screenshots

We can accomplish our needs through the shell. The shell really works on Linux. Window bat is not good.

Body

The first part: let C call mariadb run Pass

First look at the test demo MARIADB_DEMO.C

#include <stdio.h>
#include <stdlib.h>
#include <mysql.h>
 * * First MARIADB program
 */
int main (int argc, char *argv[]) {
  //Create data Connection object
  MYSQL *con = Mysql_init (NULL);
   if (con = = NULL) {
    fprintf (stderr, "%s\n", mysql_error (Con));
    Exit (exit_failure);
   }

   if (!mysql_real_connect (Con, "localhost", "root", "", NULL, 0, NULL, 0)) {
    fprintf (stderr, "%s\n", mysql_error (Con)) ;
     Mysql_close (con);
     Exit (exit_failure);
   } 

   if (mysql_query (Con, "show Databases;")) {
    fprintf (stderr, "%s\n", mysql_error (Con));
    Mysql_close (con);
    Exit (exit_failure);
   }

  Puts ("MARIADB is connect and run succesed!");
  Mysql_close (con);  

  return 0;
}

Specific compilation commands

Su root
gcc-wall-ggdb2-i/usr/include/mariadb-o mariadb_demo.out mariadb_demo.c-lmysqlclient

The results of the operation are as follows

Here the basic C call mariadb basic flow ran through. But it's very unpleasant. Can only be used by the root user.

Then we're not happy about this. Enter the second part. Extended Data C in mariadb http://stackoverflow.com/questions/17265471/using-mariadb-in-c

Part Two: Complete business requirements through ordinary users.

First create the normal user Csz, the password is 1413222, and give its select Read permission

Su root
mysql

drop user csz;
Create user ' csz ' @ '% ' identified by ' 13142222 ';
Grant SELECT on *.* to ' csz ' @ '% ';
# immediately refresh
flush privileges;

Try after Login

mysql-ucsz-p1314222-h127.0.0.1

The main mariadb is to turn off remote access by default. Let's try the Safe access mode.

su root/etc/init.d/--skip-grant-tables

One more session to open later. Re-enter the mysql-ucsz-p1314222-h127.0.0.1, and we can fix it.

#目标拼接 string content
select SUM (c) from (
select COUNT (*) as C from 2016_6_1_oss_musicelves where specifytype=1 and char_id = 30422720614402293
UNION ALL
Select COUNT (*) from 2016_6_2_oss_musicelves where specifytype=1 and char_id = 30422 720614402293
UNION ALL
Select COUNT (*) from 2016_6_3_oss_musicelves where specifytype=1 and char_id = 30422720614402293
UNION ALL
Select COUNT (*) from 2016_6_4_oss_musicelves where specifytype=1 and char_id = 30422720614402293
UNION ALL
Select COUNT (*) from 2016_6_5_oss_musicelves where specifytype=1 and char_id = 30422720614402293
UNION ALL
Select COUNT (*) from 2016_6_9_oss_musicelves where specifytype=1 and char_id = 30422720614402293
UNION ALL
Select COUNT (*) from 2016_6_10_oss_musicelves where specifytype=1 and char_id = 30422720614402293
UNION ALL
Select COUNT (*) from 2016_6_12_oss_musicelves where specifytype=1 and char_id = 30422720614402293
) as T;

Look at the contents of the GETMOUTHS.C file specifically

#include <stdio.h> #include <stdlib.h> #include <time.h> #include <mysql.h> #define _INT_BUF (40 98)//Get the contents of the query Datasheet #define _STR_SQLTABLES \ "SELECT DISTINCT table_name from INFORMATION_SCHEMA.COLUMNS where table_name l Ike '%d_%d_%%_oss_musicelves ' "#define _STR_SQLSELECT \" SELECT COUNT (*) as C from%s where specifytype=1 and char_id = %LLD//Base MARIADB error shutdown function static inline void _err_mariadb (MYSQL *con) {fprintf (stderr, "_ERR_MARIADB Error:%s\n", my
  Sql_error (Con));
  Mysql_close (con);
Exit (Exit_failure);
 * * * processing all tables for oss_musicelves one months.
  */int main (int argc, char* argv[]) {Long long ptid;
  time_t RT = Time (NULL);
  struct TM *pt = localtime (&AMP;RT);
  int year = pt->tm_year + 1900;  

  int mouth = Pt->tm_mon + 1;
    First simple detection input if (argc <= 1) {fprintf (stderr, "%s [Ptid] [mouth] [year]\n", argv[0]);
  Exit (Exit_failure);  
  ///First get Ptid data Ptid = Atoll (argv[1]); Get current month if (argc >= 3) mouth = atoi (argv[2));
  Gets the current year if (argc >= 4) years = Atoi (argv[3]); Simple test results are valid if (Ptid < 0 | | Mouth <=0 | | mouth>12 | | year<1900) {fprintf (stderr, "%s%lld%d is err
    Or!\n ", Argv[0], ptid, mouth, year);
  Exit (Exit_failure);

  //Output results printf ("%s%lld%d%d start run!\n", Argv[0], ptid, mouth, year);
  Start using MySQL to access the data we need to access results of MySQL *con = Mysql_init (NULL);
   if (con = = NULL) {fprintf (stderr, "Mysql_init Error:%s\n", mysql_error (Con));
   Exit (Exit_failure);
  
  if (!mysql_real_connect (Con, "127.0.0.1", "Csz", "1314222", "Oss_log", 0, NULL, 0)) _err_mariadb (con);
  Char Sqls[_int_buf];
  int sqlen = 0;
  sprintf (Sqls, _str_sqltables, year, mouth);
  
  if (mysql_query (Con, sqls)) _err_mariadb (con);
  Start to get results mysql_res *ret = Mysql_store_result (con);
  if (NULL = ret) _err_mariadb (con);
  Mysql_row ROW;
  int i = 0, nr = 0; while (!! (row = mysql_fetch_row (ret)) {if (i = = 0) {Sqlen = sprintf (sqls, select SUM (c) from (\ n "_str_sqlselect, row[0], ptid);"
      i = 1;
    Continue
    //back normal stitching nr = sprintf (Sqls + Sqlen, "\nunion all\n" _str_sqlselect, row[0), Ptid);
      if ((Sqlen = nr) >= _int_buf) {fprintf (stderr, "sprintf while%d too length.\n", Sqlen);
    Goto __return_free;
    } if (i = = 0) {printf ("Sum%lld%d/%d:0\n", Ptid, year, mouth);
  Goto __return_free;  
  ///here processed data NR = sprintf (Sqls + sqlen, "\ n) as T;");
    if ((Sqlen = nr) >= _int_buf) {fprintf (stderr, "sprintf End%d Too length.\n", Sqlen);
  Goto __return_free;
  ////memory is used up to release Mysql_free_result directly (ret);
  

  ret = NULL;
  printf ("sql: \n\t%s\n", SQLS);
  Start output statistic results if (mysql_query (Con, sqls)) _err_mariadb (con);
  if (ret = Mysql_store_result (con)) ==null) _err_mariadb (con); Get results directly return if (!!
    (Row=mysql_fetch_row (ret))
  printf ("Sum%lld%d/%d:%s\n", Ptid, year, mouth, row[0]);

    
Else puts ("Select is empty!"); __return_free://release of used internalDeposit Mysql_free_result (ret);
  Closes the Open database Access Object Mysql_close (Con);
return 0; }

Compile command

Gcc-wall-ggdb2-i/usr/include/mariadb-o Getmouths.out getmouths.c-lmysqlclient

The final run result is

If you want to learn more about MARIADB C-driven API use, you can refer to the foreigner wrote very good understanding.

MYSQLC Demo http://zetcode.com/db/mysqlc/

Here is the end, about C access to the database capabilities are also basically through.

Postscript

Mistakes are unavoidable, welcome to learn to progress ~ ~ ~ What the future is uncertain, you can be sure there is no future, only now still in charge.

The above C basic MARIADB processing Simple example is small series to share all the content, hope to give you a reference, but also hope that we support cloud habitat community.