Mysql学习Mac OS下PHP环境搭建及PHP操作MySQL常用方法小结

brew tap josegonzalez/homebrew-php
brew install php54

  >SHOWDATABASES; 

  >USEguest; 

  >SELECTDATABASE(); 

  >SELECT*FROMguest; //可以先通过SHOWTABLES;来查看有若干张表 

  >SET NAMESgbk; //set names utf8; 

  >CREATEDATABASEbook; 

  >CREATETABLEusers ( 
  >username VARCHAR(20),//NOT NULL 设置不允许为空
  >sex CHAR(1),
  >birth DATETIME);

  >DESCIRBEusers;

  >INSERT INTO users (username,sex,birth) VALUES('jack','male',NOW());

<?php
  header('COntent-Type:text/html;charset=utf-8');//设置页面编码,如果文件是gbk编码,则charset也应用gbk
  //@表示如果出错了,不要报错,直接忽略
  //参数：服务器地址,用户名和暗码
  echo (!!@mysql_connect('localhost','root','*****'));//1
?> 

<meta charset="utf-8">
<?php
  //定义常量参数
  define('DB_HOST','localhost');
  define('DB_USER','root');
  define('DB_PWD','345823');//暗码
  $connect = mysql_connect(DB_HOST,DB_USER,DB_PWD) or die('数据库连接失败,错误信息：'.mysql_error());
  echo $connect;//Resource id #2 
?> 

<?php
  define('DB_HOST','localhost');
  define('DB_USER','root');
  define('DB_PWD','345823');//暗码
  define('DB_NAME','trigkit');//在phpmyadmin创建一个名为trigkit的数据库
  //连接数据库
  $connect = mysql_connect(DB_HOST,DB_USER,DB_PWD) or die('数据库连接失败,错误信息：'.mysql_error());
  //选择指定数据库
  mysql_select_db(DB_NAME,$connect) or die('数据库连接错误,错误信息：'.mysql_error());//将表名字故意写错,提示的错误信息：数据库连接错误,错误信息：Unknown database 'trigkt'
?> 

<meta charset="utf-8">
<?php
  define('DB_HOST','localhost');
  define('DB_USER','root');
  define('DB_PWD','345823');//暗码
  define('DB_NAME','trigkit');
  //连接数据库
  $connect = mysql_connect(DB_HOST,DB_USER,DB_PWD) or die('数据库连接失败,错误信息：'.mysql_error());
  //选择指定数据库
  mysql_select_db(DB_NAME,$connect) or die('数据表连接错误,错误信息：'.mysql_error());
  //从数据库里把表的数据提出来(获取记录集)
  $query = "SELECT * FROM class";//在trigkit数据库中新建一张'表'
  $result = mysql_query($query) or die('SQL错误,错误信息：'.mysql_error());//故意将表名写错：SQL错误,错误信息：Table 'trigkit.clas' doesn't exist
?> 

<meta charset="utf-8">
<?php
  define('DB_HOST','localhost');
  define('DB_USER','root');
  define('DB_PWD','345823');//暗码
  define('DB_NAME','trigkit');
  //连接数据库
  $connect = mysql_connect(DB_HOST,DB_USER,DB_PWD) or die('数据库连接失败,错误信息：'.mysql_error());
  //选择指定数据库,设置字符集
  mysql_select_db(DB_NAME,$connect) or die('数据表连接错误,错误信息：'.mysql_error());
  mysql_query('SET NAMES UTF8') or die('字符集设置出错'.mysql_error());
  //从数据库里把表的数据提出来(获取记录集)
  $query = "SELECT * FROM class";
  $result = mysql_query($query) or die('SQL错误,错误信息：'.mysql_error());
  print_r(mysql_fetch_array($result,MYSQL_ASSOC));
?> 

<?php
  mysql_free_result($result); 
?>

<?php
  require 'index.php';
  //新增数据
  $query = "INSERT INTO CLASS(
       name,
       email,
       point,
       regdate)
    VALUES (
    '小明',
    'xiaoming@163.com',
    100,
    NOW()
    )";
  @mysql_query($query) or die('新增错误：'.mysql_error());
?>

<?php
  require 'index.php';
  //改动数据
  $query = 'UPDATE class SET point=80 WHERE id=2';
  @mysql_query($query);
?>

<?php
  require 'index.php';
  //删除数据
  $query = "DELETE FROM class WHERE id=2";
  @mysql_query($query);
  mysql_close();
?>

<?php
  require 'index.php';
  //显示数据
  $query = "SELECT id,name,email,regdate FROM class";
  $result = mysql_query($query) or die('sql语句差错：'.mysql_error());
  print_r(mysql_fetch_array($result));
  mysql_close();
?>

$data = mysql_fetch_array($result);
echo $data['email'];//显示email
echo $data['name'];//显示name