数据表数据结构

  1. CREATE TABLE `user` (
  2. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  3. `name` varchar(25) NOT NULL DEFAULT '',
  4. `gender` tinyint(1) NOT NULL DEFAULT '1',
  5. `age` int(11) NOT NULL DEFAULT '0',
  6. `flag` tinyint(1) NOT NULL DEFAULT '1',
  7. PRIMARY KEY (`id`)
  8. ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
  9. /*Data for the table `user` */
  10. insert into `user`(`id`,`name`,`gender`,`age`,`flag`) values (1,'allen',1,20,1),(2,'alice',2,18,1),(3,'bob',1,21,1),(4,'dave',1,25,1),(5,'eve',2,20,1),(6,'joy',1,21,1),(7,'june',1,23,1),(8,'linda',2,22,1),(9,'lisa',2,22,1),(10,'liz',2,23,1);
  1. <?php
  2. $db = array(
  3. 'host' => '127.0.0.1', //设置服务器地址
  4. 'port' => '3306', //设端口
  5. 'dbname' => 'test', //设置数据库名
  6. 'username' => 'root', //设置账号
  7. 'password' => 'yangji0321', //设置密码
  8. 'charset' => 'utf8', //设置编码格式
  9. 'dsn' => 'mysql:host=127.0.0.1;dbname=test;port=3306;charset=utf8', //这里不知道为什么,也需要这样再写一遍。
  10. );
  11. //连接
  12. $options = array(
  13. PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, //默认是PDO::ERRMODE_SILENT, 0, (忽略错误模式)
  14. PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // 默认是PDO::FETCH_BOTH, 4
  15. );
  16. try{
  17. $pdo = new PDO($db['dsn'], $db['username'], $db['password'], $options);
  18. }catch(PDOException $e){
  19. die('数据库连接失败:' . $e->getMessage());
  20. }
  21. //或者更通用的设置属性方式:
  22. //$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //设置异常处理方式
  23. //$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); //设置默认关联索引遍历
  24. echo '<pre/>';
  25. //1 查询
  26. //1)使用query
  27. $stmt = $pdo->query('select * from user limit 2'); //返回一个PDOStatement对象
  28. //$row = $stmt->fetch(); //从结果集中获取下一行,用于while循环
  29. $rows = $stmt->fetchAll(); //获取所有
  30. $row_count = $stmt->rowCount(); //记录数,2
  31. print_r($rows);
  32. echo '<br>';
  33. //2)使用prepare 推荐!
  34. $stmt = $pdo->prepare("select * from user where name = ? and age = ? ");
  35. $stmt->bindValue(1,'allen');
  36. $stmt->bindValue(2,20);
  37. $stmt->execute(); //执行一条预处理语句 .成功时返回 TRUE, 失败时返回 FALSE
  38. $rows = $stmt->fetchAll();
  39. $row_count = $stmt->rowCount(); //记录数,2
  40. print_r($rows);
  41. print_r($row_count);
  42. echo '<br>';
  43. //2 新增、更新、删除
  44. //A.1)普通操作
  45. //$count = $pdo->exec("insert into user(name,gender,age)values('test',2,23)"); //返回受影响的行数
  46. //echo $pdo->lastInsertId();
  47. //$count = $pdo->exec("update user set name='test2' where id = 15"); //返回受影响的行数
  48. //$count = $pdo->exec("delete from user where id = 15"); //返回受影响的行数
  49. //A.2)使用prepare 推荐!
  50. $stmt = $pdo->prepare("insert into user(name,gender,age)values(?,?,?)");
  51. $stmt->bindValue(1, 'test');
  52. $stmt->bindValue(2, 2);
  53. $stmt->bindValue(3, 23);
  54. $stmt->execute();
  55. $count = $stmt->rowCount();//受影响行数
  56. echo 'prepare方法影响行数:'.$count;
  57. echo '<br>';
  58. //A.3)使用prepare 批量新增
  59. $stmt = $pdo->prepare("insert into user(name,gender,age)values(?,?,?)");
  60. $stmt->bindParam(1, $name);
  61. $stmt->bindParam(2, $gender);
  62. $stmt->bindParam(3, $age);
  63. $data = array(
  64. array('t1', 1, 22),
  65. array('t2', 2, 23),
  66. );
  67. foreach ($data as $vo){
  68. list($name, $gender, $age) = $vo;
  69. $stmt->execute();
  70. }
  71. //B)更新操作
  72. echo '<br>';
  73. $stmt = $pdo->prepare("UPDATE `user` SET `age`=? WHERE (`name`= ? )");
  74. $stmt->bindValue(1, '20');
  75. $stmt->bindValue(2, 'allen');
  76. $num = $stmt->execute();
  77. $count = $stmt->rowCount();//受影响行数
  78. echo '更新操作影响行数:'.$count;
  79. //删除操作
  80. $stmt = $pdo->prepare("DELETE FROM `user` WHERE (`name`= ? )");
  81. $stmt->bindValue(1, 't1');
  82. $num = $stmt->execute();
  83. $count = $stmt->rowCount();//受影响行数
  84. echo '删除操作影响行数:'.$count;
  85. # 【示例5:统计数据】:统计company表有多少条数据
  86. echo '<br>';
  87. $num = $pdo->query("select count(*) from user");
  88. $count = $num->fetchColumn();
  89. echo '共有数据:【'.$count.'】条';
  90. ?>

分类: PHP

标签:   php