第14章学习笔记

第14章：MySQL数据库系统

知识点归纳总结：

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

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

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

---

其中让我最有收获的几个部分如下：

• MySQL 简介
• 安装 MySQL
• 使用 MySQL
• C语言MySQL编程

---

---

mysql简介：

MySQL (MySQL2018 )是一个关系数据库系统(Codd 1970)c在关系数据库中，数据存储在表中。每个表由多个行和列组成。表中的数据相互关联。表也可能与其他表有关联。关系结构使得可在表上运行查询来检索信息并修改数据库中的数据。关系数据库系统的标准查询语言是SQL(结构化查询语言)，包括MySQL。

MySQL是一个开源数据库管理系统，由服务器和客户机组成。在将客户机连接到服 务器后，用户可向服务器输入SQL命令，以便创建数据库，删除数据库，存储、组织和检索数据库中的数据。MySQL有广泛的应用。除了提供标准的数据库系统服务外，MySQL和PHP(PHP 2018)已成为大多数数据管理和在线商务网站的主干网。本章介绍了MySQLo我们将介绍MySQL的基础知识，包括如何在Linux中安装/配置MySQL,如何使用MySQL创建和管理简单数据库，以及如何在C语言和PHP编程环境中与MySQL交互。

mysql显示数据库

---

C语言mysql编程：

#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);
}

// C14.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);
}

// C14.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);
}

// C14.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);
}