[MySQL] MySQL CRUD C로 구현하기

MySQL CRUD C로 구현하기

#include<stdio.h>
#include<stdlib.h>
#include<mysql.h>
#include<errno.h>

void print_menu(){
    printf("==================\n");
    printf("1. Display all Tables\n");
    printf("2. Select Tables\n");
    printf("3. Add\n");
    printf("4. change\n");
    printf("5. Delete\n");
    printf("6. Exit\n");
    printf("==================\n");
    printf("Enter number : ");
}

void main(int argc, char **argv){
    MYSQL *conn;
    MYSQL_RES *result;
    MYSQL_ROW row;

    char query_buffer[2048];

    conn = mysql_init(NULL);
    int n=0;
    int i;
    char table_name[100];
    MYSQL_FIELD *field;

    if(!mysql_real_connect(conn, "127.0.0.1", "root", "PASSWORD", NULL, 0, NULL, 0)){
        printf("Cannot connect");
        exit(1);
    }
    else {
        if (mysql_select_db(conn, "khnoh")){
            printf("cannot use database");
            exit(1);
        }
    }

    while(n!=6){
        print_menu();
        scanf("%d", &n);
        if(n==1){                                           // 1. show tables;
            sprintf(query_buffer, "%s", "show tables;");
            if(mysql_query(conn, query_buffer)){
                printf("query faild : %s\n", query_buffer);
                exit(1);
            }

            result = mysql_use_result(conn);
            while((row = mysql_fetch_row(result)) != NULL)
                printf("%s \n", row[0]);
        }
        if(n==2){                                         // 2. select * from table;
            printf("Enter table name : ");
            scanf("%s", &table_name);
            sprintf(query_buffer, "%s %s", "select * from", table_name);
            if(mysql_query(conn, query_buffer)){
                printf("query faild : %s\n", query_buffer);
                exit(1);
     }

            result = mysql_use_result(conn);
            while((row = mysql_fetch_row(result)) != NULL){
                for(i=0; i<mysql_num_fields(result); i++){
                    if(i==0){
                        while(field = mysql_fetch_field(result))
                        {
                            printf("%-25s", field->name);
                        }
                        printf("\n");
                    }
                    printf("%-25s", row[i]);
                }
            }
            printf("\n");
        }
        if(n==3){                                         // 3. Insert into                             

        }
        if(n==4){                                         // 4. Update                             

        }
        if(n==5){                                         // 5. Delete                              

        }
    }

    mysql_free_result(result);
    mysql_close(conn);
}

1번

• Show tables; 결과화면
  
  

2번

• select * from table; 결과화면
  
  

3번

4번

5번

MySQL JOIN

• rename table topic to topic_backup
  
  

테이블 분리

• Table structure for table author

  CREATE TABLE `author` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `profile` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
  );

• Dumping data for table author

INSERT INTO author VALUES (1,'khNoh','developer');
INSERT INTO author VALUES (2,'hsLee','database administrator');
INSERT INTO author VALUES (3,'jsPark','data scientist, developer');

- Table structure for table `topic`
```mysql
CREATE TABLE `topic` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) NOT NULL,
  `description` text,
  `created` datetime NOT NULL,
  `author_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

• Dumping data for table topic테이블 JOIN
• 
• INSERT INTO `topic` VALUES (1,'MySQL','MySQL is...', NOW(),1); INSERT INTO `topic` VALUES (2,'Oracle','Oracle is ...','2018-01-03 13:01:10',1); INSERT INTO `topic` VALUES (3,'SQL Server','SQL Server is ...','2018-01-20 11:01:10',2); INSERT INTO `topic` VALUES (4,'PostgreSQL','PostgreSQL is ...','2018-01-23 01:03:03',3); INSERT INTO `topic` VALUES (5,'MongoDB','MongoDB is ...','2018-01-30 12:31:03',1);
• SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.id
  
  
• 중복을 제거하기 위해서 테이블을 분리하며, JOIN 연산자를 이용해서 테이블을 효과적으로 조회 가능