20201331 黄文刚 学习笔记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);
}
原文地址：http://www.cnblogs.com/killerqueen4/p/16908967.html
