Head First PHP&MySQL 学习笔记（二） —— Connect MySQL

本文链接：https://blog.youkuaiyun.com/swgsunhj/article/details/25098573

本文详细介绍了如何通过命令行登录MySQL数据库，并演示了创建数据库、表及数据的全过程。同时，展示了如何在PHP中利用mysqli系列函数进行数据库操作，包括连接、执行查询和关闭连接。文章还提供了一个PHP实例，说明了如何将用户输入的数据插入到MySQL数据库中。

MySQL

登录MySQL

$ mysql -h localhost -u root -p

-h : 服务器地址，如果在装在本机，在本机登录可直接使用localhost。

-u : 指定用户，MySQL在安装时会提示你输入管理员用户名和密码，使用这个用户名就可以。

-p : 需要输入密码，输入该命令后，会提示输入该用户的密码。

sunny@sunny-virtualbox:~$ mysql -h localhost -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 48

Server version: 5.5.37-0ubuntu0.14.04.1 (Ubuntu)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| aliendatabase |

| mysql |

| performance_schema |

| phpmyadmin |

+--------------------+

5 rows in set (0.08 sec)

mysql> use aliendatabase;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from aliens_abduction;

+------------+-----------+------------------+----------+----------+--------------------------+----------------------------------------+--------------+--------------------------------------+-----------------------+

2 rows in set (0.00 sec)

mysql>

创建数据库

mysql> CREATE DATABASE aliendatabase;

mysql> USE aliendatabase;

创建表

mysql> CREATE TABLE aliens_abduction (

first_name varchar(30),

last_name varchar(30),

when_it_happened varchar(30),

how_long varchar(30),

how_many varchar(30),

alien_description varchar(100),

what_they_did varchar(100),

fang_spotted varchar(10),

other varchar(100),

email varchar(50)

);

插入数据

mysql> INSERT INTO aliens_abduction(first_name, last_name, when_it_happened, how_long, how_many, alien_description, what_they_did, fang_spotted, other, email) VALUES('Salley', 'Jones', '3 days ago', '1 day', 'four', 'green with six tentacles', 'We just talked and palyed with the dog', 'yes', 'I may have seen your dog, Contact me', 'salley@gregs-list.net')

MySQL in PHP

mysqli_connect() 连接MySQL数据库

mysqli_query() 执行一个查询（这里的查询不是指增、删、改、查中的“查”，而是泛指所有的操作）

mysqli_close() 关闭MySQL数据库连接

Tips：所有与MySQL交互的PHP函数都是以"mysqli_"开头的。（较早的一组与MySQL通信的PHP函数都是以"mysql_"开头的，没有"i"。这个"i"代表改进"improved"。现在更倾向于使用mysqli系列函数）

<?php
  $first_name = $_POST['firstname'];
  $last_name = $_POST['lastname'];
  $when_it_happened = $_POST['whenithappened'];
  $how_long = $_POST['howlong'];
  $how_many = $_POST['howmany'];
  $alien_description = $_POST['aliendescription'];
  $what_they_did = $_POST['whattheydid'];
  $fang_spotted = $_POST['fangspotted'];
  $email = $_POST['email'];
  $other = $_POST['other'];

  $dbc = mysqli_connect('data.aliensabductedme.com', 'owen', 'aliensrool', 'aliendatabase')
    or die('Error connecting to MySQL server.');

  $query = "INSERT INTO aliens_abduction (first_name, last_name, when_it_happened, how_long, " .
    "how_many, alien_description, what_they_did, fang_spotted, other, email) " .
    "VALUES ('$first_name', '$last_name', '$when_it_happened', '$how_long', '$how_many', " .
    "'$alien_description', '$what_they_did', '$fang_spotted', '$other', '$email')";

  $result = mysqli_query($dbc, $query)
    or die('Error querying database.');

  mysqli_close($dbc);
?>

Note:

PHP中的SQL语句不以分号结束，这一点与PHP脚本语句不同。

PHP 支持 C，C++ 和 Unix Shell 风格（Perl 风格）的注释。

<?php
    echo "This is a test"; // This is a one-line c++ style comment
    /* This is a multi line comment
       yet another line of comment */
    echo "This is yet another test";
    echo 'One Final Test'; # This is a one-line shell-style comment
?>