Mysql 简明教程
MySQL - Connection
在使用 MySQL 数据库时，我们使用客户端程序来与数据库服务器通信。为此，我们必须首先在它们之间建立连接。
While working with MySQL database, we use a client program to communicate with the database server. To do so, we must first establish a connection between them.
要将客户端程序与 MySQL 服务器连接，我们必须确保正确使用所有连接参数。这些参数的功能就像其他任何登录参数一样：由用户名和密码组成。其中，用户名是服务器运行的主机的名称，密码需要根据用户进行设置。
To connect a client program with MySQL server, we must ensure all the connection parameters are properly used. These parameters work just like any other login parameters: consisting of a username and a password. Where, a username is the name of the host where the server is running and a password needs to be set according to the user.
本教程只使用 mysql 客户端程序来演示连接，但这些原理也适用于其他客户端，如 mysqldump、mysqladmin 或 mysqlshow。
This tutorial only uses the mysql client program to demonstrate the connection, but these principles also apply to other clients such as mysqldump, mysqladmin, or mysqlshow.
Set Password to MySQL Root
通常，在安装 MySQL 服务器时，系统会要求我们为 root 设置初始密码。除此之外，我们还可以使用以下命令设置初始密码 −
Usually, during the installation of MySQL server, we will be asked to set an initial password to the root. Other than that, we can also set the initial password using the following command −
mysql -u root password "new_password";其中，new_password 是最初设置的密码。
Where, new_password is the password set initially.
Reset Password
我们还可以使用 SET PASSWORD 语句更改现有密码。不过，我们只能在使用现有密码登录用户帐户后才能这样做。请看下面的查询 −
We can also change the existing password using the SET PASSWORD statement. However, we can only do so after logging in to the user account using the existing password. Look at the query below −
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password_name');
FLUSH PRIVILEGES;每次需要建立连接的时候, 都必须输入此密码。
Every time a connection is needed to be established, this password must be entered.
MySQL Connection Using MySQL Binary
我们可以在命令提示符下使用 mysql 二进制文件来建立 MySQL 数据库。
We can establish the MySQL database using the mysql binary at the command prompt.
Example
下面是一个从命令提示符连接到 MySQL 服务器的简单示例 −
Here is a simple example to connect to the MySQL server from the command prompt −
[root@host]# mysql -u root -p
Enter password:******这将给我们一个 'mysql>' 命令提示, 我们可以在其中执行任何 SQL 查询。以下是以上命令的结果 −
This will give us the 'mysql>' command prompt where we will be able to execute any SQL query. Following is the result of above command −
以下代码块显示了以上代码的结果 −
The following code block shows the result of above code −
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2854760 to server version: 5.0.9
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.在以上示例中, 我们使用了 root 作为用户, 但我们也可以使用其他用户。任何用户都可以执行所有被允许对其执行的 SQL 操作。
In the above example, we have used root as a user but we can use any other user as well. Any user will be able to perform all the SQL operations, which are allowed to that user.
我们可以使用 mysql> 提示中的 exit 命令随时断开与 MySQL 数据库的连接。
We can disconnect from the MySQL database any time using the exit command at mysql> prompt.
mysql> exit
ByeMySQL Connection Using PHP Script
我们可以使用 PHP mysqli() 构造函数或 mysqli_connect() 函数打开/建立与 MySQL 数据库的连接。此函数接收六个参数, 并且在成功时返回 MySQL 链接标识符, 或在失败时返回 FALSE。
We can open/establish connection to MySQL database using the PHP mysqli() constructor or, mysqli_connect() function. This function takes six parameters and returns a MySQL link identifier on success or FALSE on failure.
Syntax
以下是使用构造函数 mysqli() 打开 MySQL 连接的语法 −
Following is the syntax to open a MySQL connection using the constructor mysqli() −
$mysqli = new mysqli($host, $username, $passwd, $dbName, $port, $socket);Closing the Connection
我们可以随时使用另一个 PHP 函数 close() 与 MySQL 数据库断开连接。以下是语法 −
We can disconnect from the MySQL database anytime using another PHP function close(). Following is the syntax −
$mysqli->close();Example
尝试以下示例来连接到 MySQL 服务器。将文件保存为 mysql_example.php −
Try the following example to connect to a MySQL server. Save the file as mysql_example.php −
<html>
<head>
<title>Connecting MySQL Server</title>
</head>
<body>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);
if($mysqli->connect_errno ) {
printf("Connect failed: %s<br />", $mysqli->connect_error);
exit();
}
printf('Connected successfully.<br />');
$mysqli->close();
?>
</body>
</html>