20191330雷清逸 学习笔记12

20191330 雷清逸 学习笔记12（第十四章）

一、知识点归纳以及自己最有收获的内容

知识点归纳

摘要

本章讨论了MySQL关系数据库系统；介绍了MySQL并指出了它的重要性；

展示了如何在Linux机器上安装和运行MySQL;演示了如何使用MySQL在命令模式和批处理模式下使用SQL脚本创建和管理数据库；

说明了如何将MySQL与C编程相结合；演示了如何将MySQL与PHP集成，通过动态Web页面创建和管理数据库。

最有收获的部分

• MySQL 简介
• MySQL 使用
• MySQL的C语言实现

MySQL 简介

MySQL是一个关系数据库系统在关系数据库中，数据存储在表中。每个表由多个行和列组成。表中的数据相互关联。表也可能与其他表有关联。关系结构使得可在表上运行查询来检索信息并修改数据库中的数据。MySQL是一个开源数据库管理系统，由服务器和客户机组成。在将客户机连接到服务器后，用户可向服务器输入SQL命令，以便创建数据库，删除数据库，存储、组织和检索数据库中的数据。MySQL有广泛的应用。除了提供标准的数据库系统服务外，MySQL 和PHP已成为大多数数据管理和在线商务网站的主干网。

MySQL 使用

• 显示数据库：SHOW DATABASES命令可显示MySQL中的当前数据库
• 新建数据库：CREATE DATABASE dbname 创建一个名为dbname的数据库，如果数据库已经存在，则可以使用IF NOT EXISTS子句对命令限定。
• 删除数据库：DROP DATABASE dbname 删除已存在的命名数据库，该命令可以用一个可选的IF EXISTS 子句限定。
• 选择数据库：USE dbname命令选择一个数据库
• 创建表：CREATE TABLE table_name 命令回在当前数据库中创建一个表；DESCRIBE 命令显示表格式和列属性。
• 删除表：DROP TABLE table_name 删除表

MySQL的C语言实现

#include <stdio.h>
#include <my_global.h>
#include <mysql.h>
int main(int argc, char *argc[])
{
printf("MySQL client version is : %s\n", mysql_get_client_info());
}

#include <stdio.h>
#include <stdlib.h>
#include <my_global.h>
#include <mysql.h>
int main(int argc, char *argv[ ])
{
    // 1. define a connection object
    MYSQL con;
    // 2. Initialize the connection object
    if (mysql_init(&con)) 
    {
        ("Connection handle initialized\n");
    } 
    else 
    {
        printf("Connection handle initialization failed\n");
        exit(1);
    }
    // 3. Connect to MySQL server on localhost
    if (mysql_real_connect(&con, "localhost","root",NULL,"filetransfer",3306, NULL, 0)) 
    {
        printf("Connection to remote MySQL server OK\n");
    }
    else 
    {
        printf("Connection to remote MySQL failed\n");
        exit(1);
    }

    mysql_close(&con);
}

14.1.c: build MySQL database in C

#include <stdio.h>
#include <stdlib.h>
#include <my_global.h>
#include <mysql.h>
MYSQL *con; // connection object pointer
void error()
{
    printf("errno = %d %s\n", mysql_errno(con), mysql_error(con));
    mysql_close(con);
    exit(1);
}
int main(int argc, char *argv[ ])
{
    con = mysql_init(NULL); // will allocate and initialize it
    if (con == NULL)
        error();
    printf("connect to mySQL server on localhost using database cs360\n");
    
    if (mysql_real_connect(con, "localhost", "root", NULL,"cs360", 0, NULL, 0) == NULL)
        error();
    
    printf("connection to server OK\n");
    printf("drop students table if exists\n");
    
    if (mysql_query(con, "DROP TABLE IF EXISTS students"))
        error();
    printf("create students tables in cs360\n");
    
    if (mysql_query(con, "CREATE TABLE students(Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name CHAR(20) NOT NULL, score INT)"))
        error();

    printf("insert student records into students table\n");
    
    if (mysql_query(con, "INSERT INTO students VALUES(1001,’Baker’,50)"))
        error();
    
    if (mysql_query(con, "INSERT INTO students VALUES(1002,’Miller’,65)"))
        error();
    
    if (mysql_query(con, "INSERT INTO students VALUES(2001,’Miller’,75)"))
        error();
    
    if (mysql_query(con, "INSERT INTO students VALUES(2002,’Smith’,85)"))
        error();
    
    printf("all done\n");
    mysql_close(con);
}

14.2 Program: build MySQL database with mysql_real_query()

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <my_global.h>
#include <mysql.h>
#define N 5
int id[ ] = { 1001, 1002, 2001, 2002, 2003};
char name[ ][20] = { "Baker", "Miller", "Smith", "Walton", "Zach"};
int score[ ] = { 65, 50, 75, 95, 85};
MYSQL *con;
void error()
{
    printf("errno = %d %s\n", mysql_errno(con), mysql_error(con));
    mysql_close(con);
    exit(1);
}
int main(int argc, char *argv[ ])
{
    int i;
    char buf[1024]; // used to create commands for MySQL
    con = mysql_init(NULL); // MySQL server will allocate and init con
    if (con == NULL)
        error();

    printf("connect to mySQL server on localhost using database cs360\n");
    
    if (mysql_real_connect(con, "localhost", "root", NULL,"cs360", 0, NULL, 0) == NULL)
        error();
    
    printf("connected to server OK\n");

    printf("drop students table if exists\n");
    
    if (mysql_query(con, "DROP TABLE IF EXISTS students"))
        error();

    printf("create students tables in cs360\n");
    
    if (mysql_query(con, "CREATE TABLE students(Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name CHAR(20) NOT NULL, score INT)"))
    error();
    
    printf("insert student records into students table\n");
    
    for (i=0; i<N; i++){
        printf("id=%8d name=%10s, score=%4d\n",id[i],name[i],score[i]);
        sprintf(buf, "INSERT INTO students VALUES (%d, ’%s’, %d);",id[i], name[i], score[i]);
        if (mysql_real_query(con, buf, strlen(buf)))
            error();
    }
    printf("all done\n");
    mysql_close(con);
}

14.3 file: Retrieve MySQL query results

#include <my_global.h>
#include <mysql.h>
#include <string.h>
#define N 5
int id[ ] = { 1001, 1002, 2001, 2002, 2003};
char name[ ][20] = {"baker", "Miller", "Smith", "Walton", "Zach"};
int score[ ] = { 65, 50, 75, 95, 85};
int grade[ ] = { 'D', 'F', 'C', 'A', 'B'};
MYSQL *con;
void error()
{
    printf("errno = %d %s\n", mysql_errno(con), mysql_error(con));
    mysql_close(con);
    exit(1);
}
int main(int argc, char **argv)
{
    int i, ncols;
    MYSQL_ROW row;
    MYSQL_RES *result;
    MYSQL_FIELD *column;
    char buf[1024];
    con = mysql_init(NULL);
    
    if (con == NULL)
        error();
    
    printf("connect to mySQL server on localhost using database cs360\n");
    
    if (mysql_real_connect(con, "localhost", "root", NULL,"cs360", 0, NULL, 0) == NULL)
        error();

    printf("connected to server OK\n");
    printf("drop students table if exists\n");

    if (mysql_query(con, "DROP TABLE IF EXISTS students"))
        error();
    
    printf("create students tables in cs360\n");
    
    if (mysql_query(con, "CREATE TABLE students (Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name CHAR(20) NOT NULL, score INT, grade CHAR(2))"))
        error();

    printf("insert student records into students table\n");
    
    for (i=0; i<N; i++){
        printf("id =%4d name =%-8s score =%4d %c\n",id[i], name[i], score[i], grade[i]);
        sprintf(buf, "INSERT INTO students VALUES (%d, '%s', %d, '%c');",id[i], name[i], score[i], grade[i]);
        if (mysql_real_query(con, buf, strlen(buf)))
            error();
    }

    printf("retrieve query results\n");
    mysql_query(con, "SELECT * FROM students");
    result = mysql_store_result(con); // retrieve result
    ncols = mysql_num_fields(result); // get number of columns in row
    printf("number of columns = %d\n", ncols);
    
    for (i=0; i<ncols; i++){
        column = mysql_fetch_field(result); // get each column
        printf("column no.%d name = %s\n", i+1, column->name);
    }

    mysql_query(con, "SELECT * FROM students");
    result = mysql_store_result(con);
    ncols = mysql_num_fields(result);
    printf("columns numbers = %d\n", ncols);

    while( row = mysql_fetch_row(result) ){
        for (i=0; i<ncols; i++)
            printf("%-8s ", row[i]);
        printf("\n");
    }
    printf("all done\n");
    mysql_close(con);
}