chapter14

MySQL

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

MySQL是一个开源数据库管理系统，由服务器和客户机组成。在将客户机连接到服务器后，用户可向服务器输入SQL命令，以便创建数据库，删除数据库，存储、组织和检索数据库中的数据。MySQL有广泛的应用。除了提供标准的数据库系统服务外，MySQL和PHP(PHP 2018)已成为大多数数据管理和在线商务网站的主干网。

Linux安装MySQL

由于我使用的是Ubuntu 20.04版本，因此这里只给出此版本的MySQL安装方式，至于其他版本的Linux安装MySQL教程可以在网上搜索。

对于Ubuntu 16.04及以后版本，通过以下操作安装MySQL：

sudo apt-get install mysql-server

mysql-server包包括一个MySQL服务器和一个客户机。在安装MySQL时，它会询问根用户密码。用户可以使用与Ubuntu相同的登录密码。安装MySQL后，通过运行脚本对其进行配置以获得更好的安全性：

mysql_secure_installation

要得到简单和标准的安全设置，用户可以按Y，然后按ENTER，接受所有问题的默认值。

MySQL的使用

具体的MySQL学习可以参考MySQL官方文档或者菜鸟教程中的MySQL教程

连接到MySQL服务器

在Linux命令行中，使用下面命令以连接到MySQL服务器。

mysql -u root -p
Enter password:******

注意：所有MySQL命令行末尾必须是分号；MySQL命令行不区分大小写。

显示数据库

SHOW DATABASES;命令可显示MySQL中的当前数据库。

新建数据库

如果数据库dbname还不存在，那么CREATE DATABASE dbname命令将创建一个名为dbname的新数据库。如果数据库已经存在，则可以使用可选的IF NOT EXISTS子句对该命令进行限定，以避免出现错误消息。

删除数据库

DROP DATABASE dbname会删除已存在的命名数据库。该命令可以用一个可选的IF EXISTS子句限定。注意，DROP操作是不可逆的。一旦数据库被删除，就无法撤销或恢复。因此，须谨慎使用。

选择数据库

假设MySQL已经有几个数据库。为了操作特定的数据库，用户必须通过USE dbname命令选择一个数据库。

创建表

CREATE TABLE table_name命令会在当前数据库中创建一个表。命令语法如下：

CREATE TABLE[IF NOT EXISTS] tableName (
    columnName columnType columnAttribute, ...
    PRIMARY KEY(columnName),
    FOREIGN KEY (columnName) REFERENCES tableName (columnName)
)

对于单个表，不需要FOREIGN KEY子句。

使用DESCRIBE或DESC命令显示表格式和列属性。

主键是一个列或一组列，可用于唯一地标识行。在默认情况下，主键是唯一的。

删除表

DROP TABLE table_name命令可删除表。

MySQL中的数据类型

MySQL中使用的基本数据类型主要有三类：数字、字符串、日期和时间。下面只显示每个类别中一些常用的数据类型。

数值类型：
INT：整数（4字节），TINYINT：（1字节），SMALLINT：（2字节）等。
FLOAT：浮动指针数。
字符串类型：
CHAR(size)：固定长度字符串，长度为1~255字符。
VARCHAR(size)：可变长度字符串，但不能使用任何空格。
TEXT：可变长度的字符串。
日期和时间类型：
DATE：日期格式为YYYY-MM-DD。 
TIME：以HH:MM:SS格式保存时间。

插入行

要在表中添加行，可使用INSERT命名，具有语法形式：

INSERT INTO table_name VALUES(columnValue1,columnValue2,....);

删除行

使用DELETE命令从表中删除行，其语法形式如下：

DELETE FROM table_name;                  # delete all rows of a table
DELETE FROM table_name WHERE condition;  # delete row by condition

更新表

UPDATE命令用于修改表中的现有记录（列）。它的语法形式如下：

UPDATE table_name SET col1 = value1, col2 = value2, ... WHERE condition;

修改表

ALTER TABLE命令用于添加、删除或修改当前表中的列。它还用于添加和删除当前表中的各种约束条件。

1.修改表名

如需修改表名，可使用以下命令：

ALTER TABLE table_name RENAME TO new_name;

2.添加列

要在表中添加列,可使用以下命令：

ALTER TABLE table_name ADD column_name datatype;

3.删除行

可使用以下命令删除列：

ALTER TABLE table_name DROP column_name datatype;

4.更改/修改行

可使用以下命令修改表中某列的数据类型：

ALTER TABLE table_name ALTER COLUMN column_name datatype;

关联表

一个真正的数据库可能包含多个相互关联的表。在MySQL中，使用主键-外键约束条件来定义表关系。在两个表之间创建链接，其中一个表的主键与另一个表的外键相关联。在MySQL中，表可能以几种方式相关联，包括：

1.一对一（1-1）关系

一对一（1-1）关系是指两个表仅基于一个匹配行相互关联的关系。可以使用主键–外键约束条件创建这类关系。

使用FOREIGN KEY (key) REFERENCES dbname(key)子句来创建一对一关系。

2.一对多（1-M）关系

在数据块中，一对多或1-M关系比1-1关系更常见，也更有用。一对多关系是指一个表中的一行在另一个表中有多个匹配行的关系。可以使用主键–外键关系来创建这种关系。

使用ALTER TABLE table1 ADD FOREIGN KEY (key) REFERENCES table2(key);来达到一对多关系的目的。

3.多对多（M-M）关系

如果一个表中的多条记录与另一个表中的多条记录相关，则两个表具有多对多（M-M）关系。例如，每名学生可以上几门课，每门课通常有多名学生。所以，students表与课程注册表之间是M-M关系。处理M-M关系的标准方法是在两个表之间创建一个连接表。连接表使用外键来引用两个表中的主键，从而在两个表之间创建连接。

4.自引用关系

表可以通过某些列自我关联。

连接操作

在MySQL 中，可使用连接操作在多个表中检索数据。连接操作有4种不同的类型。

• (INNER) JOIN table1, table2：检索两个表中共有的项。
• LEFT JOIN table1, table2：检索表1中的项以及两个表中共有的项。
• RIGHT JOIN table1, table2：检索表2中的项以及两个表中共有的项。
• OUTER JOIN tabel1, table2：检索两个表中非共有以及没有用的项。

对于正则集运算，MySQL中的连接操作可以解释如下。＋表示两个集合的并集，^表示两个集合的交集。则有

(INNER) JOIN t1, t2 = t1 ^ t2;
LEFT JOIN t1, t2 = t1 + (t1 ^ t2);
RIGHT JOIN t1, t2 = t2 + (t1 ^ t2);
OUTER JOIN t1, t2 = t1 + t2;

MySQL数据库关系图

在MySQL和所有关系数据库系统中，用数据库关系图来描述表之间的关系非常有用。这类关系图通常称为ERD（实体关系图）或EERD（增强/扩展ERD）。它们可以直观地表示数据库中的各个组件及其关系。对于教材中使用的数据库，可以用如下所示的数据库关系图表示。

在数据库关系图中，箭头线通过将一个表中的外键与另一个表中引用的主键连接来描述表之间的关系。两端都有一个箭头标记的线表示1-1关系，一端有多个箭头标记的线表示1-M关系。

MySQL脚本

与普通Unix/Linux sh一样，MySQL shell也可以接受和执行脚本文件。MySQL脚本文件的后缀是.sql。它们包含MySQL服务器要执行的MySQL命令。我们可以使用MySQL脚本来创建数据库，在数据库中创建表，插入表条目和修改表内容，而不是手动输入命令行。

下面是一个简单的mysql脚本。

/* mysql.sql */
drop database if exists testdb;

create database if not exists testdb;
show databases;
use testdb;
show tables;

/* your own sql codes */

使用sql脚本运行mysql客户机有两种方法。

第一种方法是使用SOURCE命令让mysql接受脚本文件的输入。

source mysql.sql

第二种方法是使用sql脚本作为输入以批处理模式运行mysql。

mysql -u root -p < mysql.sql
Enter password:

C语言MySQL编程

C语言程序与MySQL之间的接口由mysqlclient库中的一系列MySQL C API函数（C API 2018a,b）支持。

使用C语言构建MySQL客户机程序

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

若要编译程序，可输入

gcc client.c -I/usr/include/mysql/ -lmysqlclient

注意，-I选项将包含文件路径指定为/usr/include/mysql，-l选项指定mysqlclient库。然后，运行a.out，它会打印你的Mysql client版本信息。

使用C语言连接到MySQL服务器

//connection.c file:connect to MySQL server
#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)){   //return object address printf("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", "root_password", "yourdatabasename", 3306, NULL, 0)){
        printf("Connection to remote MySQL server OK\n");
    } else {
        printf("Connection to remote MySQL failed\n");
        exit(1);
    }
    //4.Close the connection when done
    mysql_close(&con);
}

使用上面的程序就可以实现连接到MySQL服务器了。

使用C语言构建MySQL数据库

在C文件中，可以通过使用函数mysql_query(con, "your SQL codes");来进行MySQL数据库的操作，但是如果参数包含二进制值或null字节，就不能用于命令。要执行可能包含二进制数据的命令，程序必须使用mysql_real_query()函数。

使用C语言检索MySQL查询结果

MySQL C API提供了两种从MySQL服务器获取结果集的方法：一次性获取所有行或逐行获取。

函数mysql_store_result()用于一次性从MySQL服务器获取一组行并将其存储在本地内存中。它的用法是：

MYSQL_RES *mysql_store_result(MYSQL *mysql);

函数mysql_use_result()用于启动逐行结果集检索。

MYSQL_RES *mysql_use_result(MYSQL *mysql);

每次使用返回结果的MySQL语句调用mysql_query()或mysql_real_query()之后，可调用其中一个函数来检索结果集。这两个函数都会返回用MYSQL_RES类型的对象表示的结果集。其他API 函数使用返回的结果集对象来获取列和行集。当不再需要结果集时，使用mysql_free_result()函数来释放结果对象资源。

除了mysql_store_result()函数，还会使用以下函数。

• MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result)L：将结果集的一列定义为MYSQL_FIELD结构。重复调用该函数以检索结果集中所有列的信息。当没有其他字段时，它会返回NULL。
• unsigned int mysql_num_fields(MYSQL_RES *result)：返回结果集中的列数。
• MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)：检索结果集的下一行。当没有更多要检索的行时，会返回NULL。

PHP MySQL编程

可以通过MySQL PHP 语法来了解在PHP中如何使用MySQL操作。

使用PHP连接到MySQL服务器

使用下面的脚本来连接到MySQL服务器。

//mysql.php:PHP script, connect to a MySQL Server
<html>
    <head>
        <title>Creating MySQL Database</title>
    </head>
    <body>
        <?php
            $dbhost = 'localhost:3306';
            $dbuser = 'root';
            $dbpass = 'changeme';
            $con = mysql_connect($dbhost, $dbuser, $dbpsaa);
            if(!$con){
                die('Can not connect: ' . mysql_error());
            }
            echo 'Connected successfully<br />';

            $sql = 'DROP DATABASE IF EXISTS testdb';
            $retval = mysql_query($sql, $con);
            if($retval)
                echo 'dropped database testdb OK<br>';

            $sql = 'CREATE DATABASE testdb';
            $retval = mysql_query($sql, $con);
            if(!$retval){
                die('Could not create database: ' . mysql_error());
            }
            echo 'Database testdb created successfully\n';
            mysql_close($con);
        ?>
    </body>
</html>

使用PHP创建数据库表

可以在上面脚本创建了数据库后添加下面的代码段实现创建数据库表。

mysql_select_db( 'testdb' );            //use testdb created earlier

$sql = "DROP TABLE IF EXISTS students";
$retval = mysql_query($sql, $con);

echo "create table in testdb<br>";
$sql = "CREATE TABLE students( ".
    "student_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, ".
    "name CHAR(20) NOT NULL, ".
    "score INT, ".
    "grade CHAR(2)); ";
$retval = mysql_query($sql, $con);
if(!$retval){
    die('Could not create table: ' . mysql_error());
)
echo "Table created successfully\n";

使用PHP将记录插入表中

使用下面的代码将记录插入表中。

<html>
    <head>
        <title>Creating MySQL Database</title>
    </head>
    <body>
        <?php
        if(isset($_POST['submit'])){        //if user has submitted data
            $dbhost = 'localhost:3306';
            $dbuser = 'root';
            $dbpass = 'changeme';
            $con = mysql_connect($dbhost, $dbuser, $dbpsaa);

            if(!$con){
                die('Can not connect: ' . mysql_error());
            }
            echo 'Connected successfully<br />';
            mysql_select_db('testdb');      //use testdb database

            $student_id = $_POST['student_id'];
            $name = $_POST['name'];
            $score = $_POST['score'];
            echo "ID=" . $student_id . " name=" . $name . " score=".$score."<br>";
            if($student_id == NULL || $name == NULL || $score == NULL){
                echo "ID or name or score can not be NULL<br>";
            }
            else{
                $sql = "INSERT INTO students "."(student_id,name,score) "."VALUES "."('$student_id','$name','$score')";
                $retval = mysql_query($sql, $con);
                if(!$retval){
                    echo "Error " . mysql_error() . "<br>";
                }
                else{
                    echo "Entered data OK\n";
                    mysql_close($con);
                }
            }
        }
        ?>

        <br>
        //a FORM for user to enter and submit data
        <form method = "post" action = "<?php $_PHP_SELF ?>">
            ID number : <input name="student_id" type="text" id="student_id"><br>
            name &emsp;&emsp; : <input name="name" type="text" id="name"><br>
            score &emsp;&emsp;: <input name="score" type="text" id="score"><br><br>
            <input name="submit" type="submit" id="submit" value="Submit">
        </form>
    </body>
</html>

使用PHP删除行

使用下面的代码可以删除MySQL表中的行。

<?php
    $dbhost = 'localhost:3306';
    $dbuser = 'root';
    $dbpass = 'changeme';
    $con = mysql_connect($dbhost, $dbuser, $dbpsaa);

    if(!$con){
        die('Can not connect: ' . mysql_error());
    }
    mysql_select_db('testdb');

    $sql = 'DELETE FROM students WHERE name=\'Zach\";
    $retval = mysql_query($sql, $con);

    if(!$retval){
        die('Could not delete data: ' . mysql_error());
    }
    echo "Delete data OK\n";
    mysql_close($con);
?>

GPT提问环节

C语言操作MySQL数据库

php语言操作MySQL数据库

在学习中遇到的一些问题

问题1：虚拟机中下载了MySQL后登录不上去，如下图所示。

询问GPT后给出了如下的解释和方法。

查看回答后发现需要用管理员身份才能登录数据库，命令前加上sudo就解决问题了。

问题2：使用C语言链接MySQL数据库链接不上，如下图。

询问GPT后它给出了一些方法，如下图所示。

在了解了方法3后我明白了是因为权限不够所以导致无法连接到数据库，和问题1一样，使用管理员身份就可以连接上数据库了，如下图。

代码实践

1.使用C语言连接到MySQL服务器

2.使用C语言构建MySQL数据库

3.使用C语言实现MySQL查询

4.使用C语言检索MySQL查询结果

本栏目推荐文章
• 2024/1/13 算法笔记
• Python学习笔记
• spark 3.x idea linux远程开发环境搭建
• linux环境htop安装
• 【Shell基础】Bash基础与Linux三剑客
• HDP 笔记
• VMware虚拟机安装银河麒麟桌面版V10系统
• OpenHarmony 上跑CV 应用 - Windows上搭建 Linux 桌面系统
• 学习进度笔记2
• 微软企业库Unity学习笔记(一)

教材 笔记 系统 Linux Unix教材 笔记 系统linux 笔记 系统linux unix 系统linux unix 教材 笔记linux 教材 文件 笔记 系统 教材 笔记 系统 信息 网络编程 教材 笔记linux 教材 笔记 问题linux 知识 系统linux unix 教材 第三章 笔记 系统