php 3 -> MySQL

Thanks Tutorial！

CREATE TABLE users (
	id INT(11) NOT NULL AUTO_INCREMENT,
    username VARCHAR(30) NOT NULL,
    pwd VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL,
    createTime DATETIME NOT NULL DEFAULT CURRENT,
    PRIMARY KEY (id)
);

CREATE TABLE comments (
	id INT(11) NOT NULL AUTO_INCREMENT,
    username VARCHAR(30) NOT NULL,
    commentText TEXT NOT NULL,
    createTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    userId INT(11),
    PRIMARY KEY (id),
    // FOREIGN KEY (userId) REFERENCES users (id) ON DELETE NO ACTION,
    // FOREIGN KEY (userId) REFERENCES users (id) ON DELETE CASCADE,
    FOREIGN KEY (userId) REFERENCES users (id) ON DELETE SET NULL,

);

INSERT INTO users (username,pwd,email) VALUES ("dingfei","733","dingfeiuniverse@gmail.com");

UPDATE users SET username = 'Dxj' , pwd = '57733' WHERE username = 'DXJ' AND pwd = "577" OR username = "73341298231" ;
  // 可以使用 AND , OR 来连接多个条件

DELETE FROM users WHERE id = 1;
  // 可能使 id 不从 1 开始连续，但是不要手动修改 id ，会引起外键问题

SELECT username, email, createTime FROM users;
  // ',' 要加上不然出问题

SELECT users.username FROM users;
  // 这也可以

SELECT * FROM comments WHERE userId = 4;
  // * 代表所有列

SELECT * FROM users INNER JOIN comments ON users.id = comments.userid;
  // 连接两个表的行，ON 后面是连接条件

SELECT users.username, comments.commentText FROM users INNER JOIN comments ON users.id = comments.userid; 
  // 只选择需要的列

SELECT users.username, comments.commentText FROM users LEFT JOIN comments ON users.id = comments.userid;   
  // 左连接，强制显示左表的所有行，左表若没有匹配的右表则右表显示 NULL

SELECT users.username, comments.commentText FROM users RIGHT JOIN comments ON users.id = comments.userid;   
  // 右连接，强制显示右表的所有行，右表若没有匹配的左表则左表显示 NULL
  // 但是在显示顺序上，左表的列还是在前面
  // MySQL 不支持 FULL JOIN

php 与 mysql 交互 dbh.php

<?php

/* communicate to the database  */

$dsn = "mysql:host=localhost;dbname=hm2ns";
// dsn: data source name
$username = "root";
$password = "";
// in mac ,password is 'root';

try { // 错误处理

  $pdo = new PDO($dsn, $username, $password);
  // pdo: php data object
  // PDO 把链接变成了一个对象，现在把一个实例化的对象赋值给 $pdo 变量
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  // 错误处理模式，设置为抛出异常

} catch (PDOException $e) {// 捕获异常
  echo "Connection failed: " . $e->getMessage();
}