使用C语言访问MySQL数据库

MySQL数据库（管理CD盘）

一、数据库设计

1、数据库E/R图（实体-关系图）应该是多对多，为了简化，一对多（把artist的key——Artist_ID放到CD中作为外键）

2、关系模式（Relation Schema）

Artist(artist_ID,  names)  简称为D1表

CD(CD_ID, Title, cataloge, Notes, artist_id（外键）,track_id（磁道）, song_name)

  其中：非主属性track_id, song_name,不能完全函数依赖于键CD_ID,不符合第二范式，将其投影分解为：

CD(CD_ID,Title, cataloge, Notes, artist_id)    简称为D2表

Track(CD_ID,track_id, song_name)          简称为D3表

//甲骨文数据库可以分属性为多个属性，可以直接存图片或者MP3

二、创建数据库

1、进入MySQL数据库

%mysql –u root –p

Password:

2、生成数据库

mysql> create database ex1;

Query OK, 1 row affected (0.01 sec)

2、授权：

   用户的授权信息保存于mysql数据库中的user表中

   例：mysql>use mysql;

       mysql>select user, host, passwordfrom user;

授权语法：

 grant all on 数据库名.表名  to ‘用户名’@’客户登录计算机名（地址）’ identified by  ‘密码’;

   或

 grant allprivileges on数据库名.表名  to 用户名@客户登录计算机名（地址） identified by  ‘密码’;

   或

 grant allon  数据库名.表名 to 用户名@客户登录计算机名（地址） identified by  ‘密码’;

 

例：mysql>grant all on *.* to  test@localhost identified by ‘password’;

    mysql>use mysql;    打开MySQL数据库

    mysql>update user   更新user表

          >set password=OLD_PASSWORD('password')

          >where user='test';

     mysql>flush privileges;

           或

     mysql>grant all on ex110301.* tow12@localhost identified by ‘password’;

     mysql>use mysql;    打开MySQL数据库

     mysql>flush privileges;

           或

     mysql>grant all on ex110301.* tow12@'%' identified by 'password';

     mysql>use mysql;  打开MySQL数据库

     mysql>flush privileges;

     mysql>exit

       表示用户w12对cd110301具有所有权限，可从任何计算（‘%’）上连接该数据库

       说明：远程连接需要使用TCP端口：3306

 

3、改用你授权的用户名/密码登录

  %mysql –u 授权的用户名  -p

  Password:输入授权密码

 

4、创建表

4.1 生成表cd

create table cd (

        id INTEGER AUTO_INCREMENT NOT NULLPRIMARY KEY,

        title VARCHAR(70) NOT NULL,    

        artist_id INTEGER NOT NULL,

        catalogue VARCHAR(30) NOT NULL

);

4.2 生成表artist

create tableartist (

        id INTEGER AUTO_INCREMENT NOT NULLPRIMARY KEY,

        name VARCHAR(100) NOT NULL

);

 

4.3 生成表track

create table track(

        cd_id INTEGER NOT NULL,

        track_id INTEGER NOT NULL,

        title VARCHAR(70),

        PRIMARY KEY(cd_id, track_id)

);

 

三、插入数据

3.1 向表artist中插入数据

insert intoartist(id, name) values(1, 'Pink Floyd');

insert intoartist(id, name) values(2, 'Genesis');

insert intoartist(id, name) values(3, 'Einaudi');

insert intoartist(id, name) values(4, 'Melanie C');

 

3.2 向表cd中插入数据

insert into cd(id,title, artist_id, catalogue) values(1, 'Dark Side of the Moon', 1, 'B000024D4P');

insert into cd(id,title, artist_id, catalogue) values(2, 'Wish You Were Here', 1, 'B000024D4S');

insert into cd(id,title, artist_id, catalogue) values(3, 'A Trick of the Tail', 2, 'B000024EXM');

insert into cd(id,title, artist_id, catalogue) values(4, 'Selling England By the Pound', 2,'B000024E9M');

insert into cd(id,title, artist_id, catalogue) values(5, 'I Giorni', 3, 'B000071WEV');

insert into cd(id,title, artist_id, catalogue) values(6, 'Northern Star', 4, 'B00004YMST');

 

3.3 向表track中插入数据

insert intotrack(cd_id, track_id, title) values(1, 1, 'Speak to me');

insert intotrack(cd_id, track_id, title) values(1, 2, 'Breathe');

insert intotrack(cd_id, track_id, title) values(1, 3, 'On the run');

insert intotrack(cd_id, track_id, title) values(1, 4, 'Time');

insert intotrack(cd_id, track_id, title) values(1, 5, 'Great gig in the sky');

insert intotrack(cd_id, track_id, title) values(1, 6, 'Money');

insert intotrack(cd_id, track_id, title) values(1, 7, 'Us and them');

insert intotrack(cd_id, track_id, title) values(1, 8, 'Any colour you like');

insert intotrack(cd_id, track_id, title) values(1, 9, 'Brain damage');

insert intotrack(cd_id, track_id, title) values(1, 10, 'Eclipse');

 

insert intotrack(cd_id, track_id, title) values(2, 1, 'Shine on you crazy diamond');

insert intotrack(cd_id, track_id, title) values(2, 2, 'Welcome to the machine');

insert intotrack(cd_id, track_id, title) values(2, 3, 'Have a cigar');

insert intotrack(cd_id, track_id, title) values(2, 4, 'Wish you were here');

insert intotrack(cd_id, track_id, title) values(2, 5, 'Shine on you crazy diamond pt.2');

 

insert intotrack(cd_id, track_id, title) values(3, 1, 'Dance on a volcano');

insert intotrack(cd_id, track_id, title) values(3, 2, 'Entangled');

insert intotrack(cd_id, track_id, title) values(3, 3, 'Squonk');

insert intotrack(cd_id, track_id, title) values(3, 4, 'Madman moon');

insert intotrack(cd_id, track_id, title) values(3, 5, 'Robbery assault and battery');

insert intotrack(cd_id, track_id, title) values(3, 6, 'Ripples');

insert intotrack(cd_id, track_id, title) values(3, 7, 'Trick of the tail');

insert intotrack(cd_id, track_id, title) values(3, 8, 'Los Endos');

 

四、查询

4.1 获取cd表的最后一个记录号（取出游标）

Select LAST_INSERT_ID();

结果： +---------------------------+

| LAST_INSERT_ID()  |

+---------------------------+

|              0  |

+---------------------------+

因为自己定义的id所以没有自增，所以为0，若第一个属性变为null，再插入，则会变为7，即cd表中的最后一个id。

 

4.2 列出track表中第3张cd光盘中的所有曲目

   Select track_id, title from track wheretrack.cd_id=3;

结果： +-------------+-------------------------------------------+

| track_id | title                        |

+------------+--------------------------------------------+

|      1 | Dance on a volcano           |

|      2 |Entangled                   |

|     3 | Squonk                     |

|      4 | Madman moon              |

|      5 | Robbery assault and battery    |

|     6 | Ripples                     |

|     7 | Trick of the tail               |

|      8 | Los Endos                   |

+-------------+-------------------------------------------+

4.3 多表查询

   列出第二张cd中，艺术家的名字存放在artist表中的艺术家名、编号、cd名称及分类号

   Selectartist.name,  artist.id,  cd.id, cd.title, cd.catalogue from artist, cd where artist.id=cd.artist_id andcd.id=2;

结果： +--------------+----+----+--------------------------+-------------------+

| name    | id | id | title             |catalogue   |

+--------------+----+----+--------------------------+-------------------+

| Pink Floyd | 1 | 2 | Wish You Were Here | B000024D4S|

+--------------+----+----+--------------------------+-------------------+

4.4 模糊查询

   查询艺术家名字带有gene???，或cd光盘的光盘名中带有Trick的艺术家编号和cd编号

   Selectdistinct artist.id, cd.id from artist, cd where artist.name LIKE '%%gene%%' orcd.title LIKE '%%Trick%%';

       其中：distinct 为消除冗余记录，数据库术语为投影

结果+----+------+

| id | id  |

+----+------+

|  2 |  1|

|  2 |  2|

|  1 |  3|

|  2 |  3|

|  3 |  3|

|  4 |  3|

|  2 |  4|

|  2 |  5|

|  2 |  6|

+-----+------+

4.5 子查询

   列出与第二张光盘对应的艺术家id(artist_id)

   Selectartist_id from cd where artist_id=(select artist_id from cd where id=2);

结果：+-----------+

| artist_id |

+-----------+

|      1 |

|      1 |

+-----------+

  使用C语言访问创建的数据库

1.     头文件---app_mysql.h

定义数据结构体和函数原型

1.1   表示当前CD的数据结构（不考虑track曲目信息）

structcurrent_cd_st {

  int artist_id;

  int cd_id;

  char artist_name[100];

  char title[100];

  char catalogue[100];

};

 

1.2   一张CD盘中的曲目数据结构

structcurrent_tracks_st {

  int cd_id;

  char track[20][100];

};

 

#defineMAX_CD_RESULT 10

structcd_search_st {

  int cd_id[MAX_CD_RESULT];

};

 

1.3 数据库操作函数原型

1.3.1 打开/关闭数据库函数原型

int  database_start(char *name, char *password);

voiddatabase_end();

 

1.4 增加CD和曲目的函数原型

int add_cd(char*artist, char *title, char *catalogue, int *cd_id);

intadd_tracks(struct current_tracks_st *tracks);

 

1.5 查询和返回CD盘信息的函数原型

int find_cds(char*search_str, struct cd_search_st *results);

int get_cd(intcd_id, struct current_cd_st *dest);

intget_cd_tracks(int cd_id, struct current_tracks_st *dest);

 

1.6 从数据库中删除CD的函数原型

int delete_cd(intcd_id);

 

 

2、主程序---app_test.c

    用于测试函数实现的是否正确的函数原型

#include<stdlib.h>

#include<stdio.h>

#include<string.h>

 

#include"app_mysql.h"

 

int main() {

  struct current_cd_st cd;

  struct cd_search_st cd_res;

  struct current_tracks_st ct; //曲目的数据结构

  int cd_id;

  int res, i;

 

  database_start("n1417", "密码");

 

  res = add_cd("Mahler","Symphony No 1", "4596102", &cd_id); 

  printf("Result of adding a cd was %d,cd_id is %d\n", res, cd_id);

 

  memset(&ct, 0, sizeof(ct));

  ct.cd_id = cd_id;

  strcpy(ct.track[0], "LansamSchleppend");

  strcpy(ct.track[1], "Kraftigbewegt");

  strcpy(ct.track[2], "Feierlich undgemesen");

  strcpy(ct.track[3], "Sturmischbewegt");

  add_tracks(&ct);

 

  res =find_cds("Symphony", &cd_res);

//以symphony为关键字查找相关的CD，找到的结果给cd_res

  printf("Found %d cds, first has ID%d\n", res, cd_res.cd_id[0]);

 

  res = get_cd(cd_res.cd_id[0], &cd);

  //找到的CD中第一张CD的相关信息

  printf("get_cd returned %d\n",res);

 

  memset(&ct, 0, sizeof(ct)); //置0

  res = get_cd_tracks(cd_res.cd_id[0],&ct); //列出这张CD中的曲目

  printf("get_cd_tracks returned%d\n", res); 

  printf("Title: %s\n", cd.title);

  i = 0;

  while (i < res) {

   printf("\ttrack %d is %s\n", i, ct.track[i]); //输出曲目名

              i++;

  }

 

  res = delete_cd(cd_res.cd_id[0]); //删除CD表上这张CD并删除track表中这张CD上的所有曲目

  printf("Delete_cd returned %d\n",res);

 

 

  database_end();

 

  return EXIT_SUCCESS;

 

}

 

 

 

 

3、函数实现---app_mysql.c

    用于实现app_mysql.h头文件中定义的函数原型

#include<stdlib.h>

#include<stdio.h>

#include<string.h>

 

#include"mysql.h"

#include"app_mysql.h"

 

static MYSQLmy_connection;

static intdbconnected = 0;          用于判断是否已经建立连接的变量 0未建立，其它：建立

 

static intget_artist_id(char *artist);

 

3.1 建立数据库连接的函数实现

intdatabase_start(char *name, char *pwd) {

 

        if (dbconnected) return 1;

____ mysql_init(&my_connection);

                 if(!mysql_real_connect(&my_connection, "localhost","root",NULL,"labn1417", 0, NULL, 0)) {

                         fprintf(stderr,"Database connection failure: %d, %s\n",mysql_errno(&my_connection), mysql_error(&my_connection));

                         return 0;

        }___________________________________________________

        dbconnected = 1;

        return 1;

} /* databasestart */

 

3.2 关闭数据库连接的函数实现

voiddatabase_end() {

        if (dbconnected)mysql_close(&my_connection);

        dbconnected = 0;

} /* database_end*/

 

 

3.3 向数据库中添加记录的函数实现

int add_cd(char*artist, char *title, char *catalogue, int *cd_id) {

 

  MYSQL_RES *res_ptr;

  MYSQL_ROW mysqlrow;

 

  int res;

  char is[250];

  char es[250];

  int artist_id = -1;

  int new_cd_id = -1;

 

  if (!dbconnected) return 0;

  artist_id = get_artist_id(artist);

 

  mysql_escape_string(es, title,strlen(title));

  sprintf(is, "INSERT INTO cd(title,artist_id, catalogue) VALUES('%s', %d, '%s')", es, artist_id, catalogue);

  res = mysql_query(&my_connection, is);

  if (res) {

    fprintf(stderr, "Insert error %d:%s\n", mysql_errno(&my_connection), mysql_error(&my_connection));

    return 0;

  }

  res = mysql_query(&my_connection,"SELECT LAST_INSERT_ID()");

  if (res) {

    printf("SELECT error: %s\n",mysql_error(&my_connection));

    return 0;

  } else {

    res_ptr =mysql_use_result(&my_connection);

    if (res_ptr) {

      if ((mysqlrow =mysql_fetch_row(res_ptr))) {

        sscanf(mysqlrow[0], "%d",&new_cd_id);

      }

      mysql_free_result(res_ptr);

    }

    *cd_id = new_cd_id;

   

    if (new_cd_id != -1) return 1;

    return 0;

  }

} /* add_cd */

 

3.4  向数据库中添加artist的函数实现

/* Find or createan artist_id for the given string */

static intget_artist_id(char *artist) {

  MYSQL_RES *res_ptr;

  MYSQL_ROW mysqlrow;

 

  int res;

  char qs[250];

  char is[250];

  char es[250];

  int artist_id = -1;

 

  /* Does it already exist? */

  mysql_escape_string(es, artist,strlen(artist));

  sprintf(qs, "SELECT id FROM artist WHEREname = '%s'", es);

 

  res = mysql_query(&my_connection, qs);

  if (res) {

    fprintf(stderr, "SELECT error:%s\n", mysql_error(&my_connection));

  } else {

    res_ptr =mysql_store_result(&my_connection);

    if (res_ptr) {

      if (mysql_num_rows(res_ptr) > 0) {

        if (mysqlrow = mysql_fetch_row(res_ptr)){

         sscanf(mysqlrow[0], "%d", &artist_id);

        }

      }

      mysql_free_result(res_ptr);

    }

  }

  if (artist_id != -1) return artist_id;

 

  sprintf(is, "INSERT INTO artist(name)VALUES('%s')", es);

  res = mysql_query(&my_connection, is);

  if (res) {

    fprintf(stderr, "Insert error %d:%s\n", mysql_errno(&my_connection), mysql_error(&my_connection));

    return 0;

  }

  res = mysql_query(&my_connection,"SELECT LAST_INSERT_ID()");

  if (res) {

    printf("SELECT error: %s\n",mysql_error(&my_connection));

    return 0;

  } else {

    res_ptr =mysql_use_result(&my_connection);

    if (res_ptr) {

      if ((mysqlrow =mysql_fetch_row(res_ptr))) {

        sscanf(mysqlrow[0], "%d",&artist_id);

      }

      mysql_free_result(res_ptr);

    }

  }

  return artist_id;

} /* get_artist_id*/

 

3.5 向数据库中添加曲目的函数实现

intadd_tracks(struct current_tracks_st *tracks) {

 

  int res;

  char is[250];   CD盘名称

  char es[250];   一张CD盘上的曲目

  int i;

 

  if (!dbconnected) return 0;

 

  i = 0;

  while (tracks->track[i][0]) {

______ mysql_escape_string(es,tracks->track[i], strlen(tracks->track[i])); //用于对特殊字符进行转义处理

                sprintf(is,"INSERT INTO track(cd_id, track_id, title) VALUES(%d, %d, '%s')",tracks->cd_id, i + 1, es); //向track表插入cd_id为cd_id、track_id为i+1, title为es的曲目

                    res= mysql_query(&my_connection, is);

                      if (res) {

                         fprintf(stderr,"Insert error %d: %s\n", mysql_errno(&my_connection),mysql_error(&my_connection));

                            return 0;

                    }

           i++;

        }  

return 1;

} /* add_tracks */

                      

 

3.6 查询CD盘信息的函数实现

int get_cd(intcd_id, struct current_cd_st *dest) {

  MYSQL_RES *res_ptr;

  MYSQL_ROW mysqlrow;

 

  int res;

  char qs[250];

 

  if (!dbconnected) return 0;

  memset(dest, 0, sizeof(*dest));

  dest->artist_id = -1;

 

  sprintf(qs, "SELECT artist.id, cd.id,artist.name, cd.title, cd.catalogue FROM artist, cd WHERE artist.id =cd.artist_id and cd.id = %d", cd_id);

 

  res = mysql_query(&my_connection, qs);

  if (res) {

    fprintf(stderr, "SELECT error:%s\n", mysql_error(&my_connection));

  } else {

    res_ptr =mysql_store_result(&my_connection);

    if (res_ptr) {

      if (mysql_num_rows(res_ptr) > 0) {

        if (mysqlrow = mysql_fetch_row(res_ptr)){

         sscanf(mysqlrow[0], "%d", &dest->artist_id);

         sscanf(mysqlrow[1], "%d", &dest->cd_id);

         strcpy(dest->artist_name, mysqlrow[2]);

         strcpy(dest->title, mysqlrow[3]);

         strcpy(dest->catalogue, mysqlrow[4]);

        }

      }

      mysql_free_result(res_ptr);

    }

  }

  if (dest->artist_id != -1) return 1;

  return 0;

} /* get_cd */

 

 

3.7 查询一张盘中曲目信息的函数实现

int get_cd_tracks(intcd_id, struct current_tracks_st *dest) {

  MYSQL_RES *res_ptr;

  MYSQL_ROW mysqlrow;

 

  int res;

  char qs[250];

  int i = 0, num_tracks = 0;

 

  if (!dbconnected) return 0;

  memset(dest, 0, sizeof(*dest));

  dest->cd_id = -1;

 

  sprintf(qs, "SELECT track_id, title FROMtrack WHERE track.cd_id = %d ORDER BY track_id", cd_id);

 

  res = mysql_query(&my_connection, qs);

  if (res) {

    fprintf(stderr, "SELECT error:%s\n", mysql_error(&my_connection));

  } else {

res_ptr = mysql_store_result(&my_connection);

if (res_ptr) {

                   if ((num_tracks = mysql_num_rows(res_ptr))> 0) {//个数不为空

                                 while(mysqlrow = mysql_fetch_row(res_ptr)) {//循环获得结果的每一行

                                         strcpy(dest->track[i], mysqlrow[1]);

//取出一个记录的第二属性值 ，并将其赋值给dest。

                                         i++;

                                 }

                                 dest->cd_id= cd_id;

                      }

                   mysql_free_result(res_ptr);    

}

  }

  return num_tracks;

} /* get_cd_tracks*/

 

 

3.8 模糊查找CD盘

int find_cds(char*search_str, struct cd_search_st *dest) {

  MYSQL_RES *res_ptr;

  MYSQL_ROW mysqlrow;

 

  int res;

  char qs[500];

  int i = 0;

  char ss[250];

  int num_rows = 0;

 

  if (!dbconnected) return 0;

  memset(dest, -1, sizeof(*dest));

  mysql_escape_string(ss, search_str,strlen(search_str));

 

  sprintf(qs, "SELECT DISTINCT artist.id,cd.id FROM artist, cd WHERE artist.id = cd.artist_id and (artist.name LIKE'%%%s%%' OR cd.title LIKE '%%%s%%' OR cd.catalogue LIKE '%%%s%%')", ss,ss, ss);

 

  res = mysql_query(&my_connection, qs);

  if (res) {

    fprintf(stderr, "SELECT error:%s\n", mysql_error(&my_connection));

  } else {

    res_ptr =mysql_store_result(&my_connection);

    if (res_ptr) {

      num_rows = mysql_num_rows(res_ptr);

      if ( num_rows > 0) {

        while ((mysqlrow =mysql_fetch_row(res_ptr)) && i < MAX_CD_RESULT) {

         sscanf(mysqlrow[1], "%d", &dest->cd_id[i]);

         i++;

        }

      }

      mysql_free_result(res_ptr);

    }

  }

  return num_rows;

} /* find_cds */

 

 

3.9 删除一张CD盘中的相关信息

int delete_cd(intcd_id) {

 

  int res;

  char qs[250];

  int artist_id, num_rows;

  MYSQL_RES *res_ptr;

  MYSQL_ROW mysqlrow;

 

  if (!dbconnected) return 0;

 

  artist_id = -1;

  sprintf(qs, "SELECT artist_id FROM cdWHERE artist_id = (SELECT artist_id FROM cd WHERE id = '%d')", cd_id);

  res = mysql_query(&my_connection, qs);

  if (res) {

    fprintf(stderr, "SELECT error:%s\n", mysql_error(&my_connection));

  } else {

    res_ptr =mysql_store_result(&my_connection);

    if (res_ptr) {

      num_rows = mysql_num_rows(res_ptr);

      if (num_rows == 1) {

        /* Artist not used by any other CDs */

        mysqlrow = mysql_fetch_row(res_ptr);

        sscanf(mysqlrow[0], "%d",&artist_id);

      }

      mysql_free_result(res_ptr);

    }

  }

 

 

  sprintf(qs, "DELETE FROM track WHEREcd_id = '%d'", cd_id);

  res = mysql_query(&my_connection, qs);

  if (res) {

    fprintf(stderr, "Delete error (track)%d: %s\n", mysql_errno(&my_connection),mysql_error(&my_connection));

    return 0;

  }

 

  sprintf(qs, "DELETE FROM cd WHERE id ='%d'", cd_id);

  res = mysql_query(&my_connection, qs);

  if (res) {

    fprintf(stderr, "Delete error (cd) %d:%s\n", mysql_errno(&my_connection), mysql_error(&my_connection));

    return 0;

  }

 

  if (artist_id != -1) {

    /* artist entry is now unrelated to anyCDs, delete it */

    sprintf(qs, "DELETE FROM artist WHEREid = '%d'", artist_id);

    res = mysql_query(&my_connection, qs);

    if (res) {

      fprintf(stderr, "Delete error(artist) %d: %s\n", mysql_errno(&my_connection),mysql_error(&my_connection));

    }

  }

 

  return 1;

 

} /* delete_cd */

结果：

Result of adding acd was 1, cd_id is 7

Found 1 cds, firsthas ID 7

get_cd returned 1

get_cd_tracksreturned 4

Title: Symphony No1

        track 0 is Lansam Schleppend

        track 1 is Kraftig bewegt

        track 2 is Feierlich und gemesen

        track 3 is Sturmisch bewegt

Delete_cd returned1