2020年6月4日

    1. <?php
    2. try{
    3. $dsn="mysql:host=127.0.0.1;port=3306;dbname=demo;charset=utf8";
    4. $pdo=new PDO($dsn,"root","root"); 
    5. $pdo->setAttribute(PDO::ATTR_CASE,PDO::CASE_LOWER);
    6. $sql="SELECT * FROM a";
    7. $zhi=$pdo->query($sql);
    9. if($zhi){
    10. echo "执行成功";
    11. echo "<pre>";
    13.  $fetch_data=[];
    14.   while ($row=$zhi->fetch(PDO::FETCH_ASSOC)){
    15.   $fetch_data[] = $row;
    16.   }
    17.   var_dump($fetch_data);
    18.   echo"</pre>";
    19. }else{
    20. echo"执行失败";
    21. }
    22. }catch(PDOException $e){
    23. var_dump($e->getMessage());
    24. }
    27. //设置错误报告
    28. try{
    29. $dsn="mysql:host=127.0.0.1;port=3306;dbname=demo;charset=utf8";
    30. $pdo=new PDO($dsn,"root","root"); 
    31.   $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    32.   $sql="SELECT * FROM a";
    33.   $zhi=$pdo->query($sql);
    34.   if($zhi){
    35.   echo"执行成功";
    36.   }else{
    37.   var_dump($pdo->errorInfo()[2]);
    38.   }
    39. }catch(PDOException $e){
    40. var_dump($e->getMessage());
    41. }
    44. try{
    45. $dsn="mysql:host=127.0.0.1;port=3306;dbname=demo;charset=utf8";
    46. $pdo=new PDO($dsn,"root","root"); 
    47. //余额表
    48. //CREATE TABLE a(
    49. //  n VARCHAR(15) NOT NULL,
    50. //  r INT(10)
    51. //  )ENGINE=innodb;
    52.  //INSERT INTO a VALUES('夏亚','1000'),('阿姆罗','1000'); 
    53. $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    54. $pdo->beginTransaction();
    55. $sql="UPDATE a SET rmb=rmb-200 WHERE name='夏亚'";
    56. $pdo->exec($sql);
    57.   $sql="UPDATE a SET rmb=rmb+200 WHERE name='阿姆罗'";
    58. $pdo->exec($sql);
    59. $pdo->commit();
    60. }catch(PDOException $e){
    61. var_dump($e->getMessage());
    62. }
    64. //预处理流程
    65. try{
    66. $dsn="mysql:host=127.0.0.1;port=3306;dbname=demo1;charset=utf8";
    67. $pdo=new PDO($dsn,"root","root"); 
    68. $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    69.   $sql="SELECT * FROM a";
    70.   $zhi=$pdo->prepare($sql);
    71.   $res=$zhi->execute();
    72.   if($res){
    73.   $tl=$zhi->fetchAll(PDO::FETCH_ASSOC);
    74.     var_dump($tl);
    75.   }else{
    76.   echo"执行失败";
    77.   }
    78.   }catch(PDOException $e){
    79.   $pdo->rollback();
    80. var_dump($e->getMessage());
    81. }
    84. try{
    85. $dsn="mysql:host=127.0.0.1;port=3306;dbname=demo1;charset=utf8";
    86. $pdo=new PDO($dsn,"root","root"); 
    87. $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    88. $sql="SELECT * FROM a WHERE name LIKE ? AND rmb=?;";
    89.   $zhi=$pdo->prepare($sql);
    90.   $s="%红%";
    91.   $r="1000";
    93.   $zhi->bindParam(1,$s);
    94.   $zhi->bindParam(2,$r);
    96.    $zhi->bindValue(1,"%子%");
    97.    $zhi->bindValue(2,3200);
    99.   $res=$zhi->execute();
    100.   if($res){
    101.   var_dump($zhi->fetchAll(2));
    103.   }
    105. }catch(PDOException $e){
    106.   $pdo->rollback();
    107. var_dump($e->getMessage());
    108. }
    111. try{
    112. $dsn="mysql:host=127.0.0.1;port=3306;dbname=demo1;charset=utf8";
    113. $pdo=new PDO($dsn,"root","root"); 
    114. $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    115. $sql="SELECT * FROM a WHERE name LIKE :search AAND rmb = :qian;";
    116.   $zhi=$pdo->prepare($sql);
    118. $s="%子%";
    119. $qian=3200;
    120.  $zhi->bindParam('s',$s);
    121.    $zhi->bindValue('qian',3200);
    122.   $res=$zhi->execute();
    124.   if($res){
    125.   var_dump($zhi->fetchAll(2));
    127.   }catch(PDOException $e){
    128. var_dump($e->getMessage());
    129. }
    133. <?php
    134. try{
    135. $dsn="mysql:host=127.0.0.1;port=3306;dbname=demo;charset=utf8";
    136. $pdo=new PDO($dsn,"root","root"); 
    137. $pdo->setAttribute(PDO::ATTR_CASE,PDO::CASE_LOWER);
    138. $sql="SELECT * FROM a";
    139. $zhi=$pdo->query($sql);
    141. if($zhi){
    142. echo "执行成功";
    143. echo "<pre>";
    145.  $fetch_data=[];
    146.   while ($row=$zhi->fetch(PDO::FETCH_ASSOC)){
    147.   $fetch_data[] = $row;
    148.   }
    149.   var_dump($fetch_data);
    150.   echo"</pre>";
    151. }else{
    152. echo"执行失败";
    153. }
    154. }catch(PDOException $e){
    155. var_dump($e->getMessage());
    156. }
    159. //设置错误报告
    160. try{
    161. $dsn="mysql:host=127.0.0.1;port=3306;dbname=demo;charset=utf8";
    162. $pdo=new PDO($dsn,"root","root"); 
    163.   $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    164.   $sql="SELECT * FROM a";
    165.   $zhi=$pdo->query($sql);
    166.   if($zhi){
    167.   echo"执行成功";
    168.   }else{
    169.   var_dump($pdo->errorInfo()[2]);
    170.   }
    171. }catch(PDOException $e){
    172. var_dump($e->getMessage());
    173. }
    176. try{
    177. $dsn="mysql:host=127.0.0.1;port=3306;dbname=demo;charset=utf8";
    178. $pdo=new PDO($dsn,"root","root"); 
    179. //余额表
    180. //CREATE TABLE a(
    181. //  n VARCHAR(15) NOT NULL,
    182. //  r INT(10)
    183. //  )ENGINE=innodb;
    184.  //INSERT INTO a VALUES('夏亚','1000'),('阿姆罗','1000'); 
    185. $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    186. $pdo->beginTransaction();
    187. $sql="UPDATE a SET rmb=rmb-200 WHERE name='夏亚'";
    188. $pdo->exec($sql);
    189.   $sql="UPDATE a SET rmb=rmb+200 WHERE name='阿姆罗'";
    190. $pdo->exec($sql);
    191. $pdo->commit();
    192. }catch(PDOException $e){
    193. var_dump($e->getMessage());
    194. }
    196. //预处理流程
    197. try{
    198. $dsn="mysql:host=127.0.0.1;port=3306;dbname=demo1;charset=utf8";
    199. $pdo=new PDO($dsn,"root","root"); 
    200. $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    201.   $sql="SELECT * FROM a";
    202.   $zhi=$pdo->prepare($sql);
    203.   $res=$zhi->execute();
    204.   if($res){
    205.   $tl=$zhi->fetchAll(PDO::FETCH_ASSOC);
    206.     var_dump($tl);
    207.   }else{
    208.   echo"执行失败";
    209.   }
    210.   }catch(PDOException $e){
    211.   $pdo->rollback();
    212. var_dump($e->getMessage());
    213. }
    216. try{
    217. $dsn="mysql:host=127.0.0.1;port=3306;dbname=demo1;charset=utf8";
    218. $pdo=new PDO($dsn,"root","root"); 
    219. $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    220. $sql="SELECT * FROM a WHERE name LIKE ? AND rmb=?;";
    221.   $zhi=$pdo->prepare($sql);
    222.   $s="%红%";
    223.   $r="1000";
    225.   $zhi->bindParam(1,$s);
    226.   $zhi->bindParam(2,$r);
    228.    $zhi->bindValue(1,"%子%");
    229.    $zhi->bindValue(2,3200);
    231.   $res=$zhi->execute();
    232.   if($res){
    233.   var_dump($zhi->fetchAll(2));
    235.   }
    237. }catch(PDOException $e){
    238.   $pdo->rollback();
    239. var_dump($e->getMessage());
    240. }
    243. try{
    244. $dsn="mysql:host=127.0.0.1;port=3306;dbname=demo1;charset=utf8";
    245. $pdo=new PDO($dsn,"root","root"); 
    246. $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    247. $sql="SELECT * FROM a WHERE name LIKE :search AAND rmb = :qian;";
    248.   $zhi=$pdo->prepare($sql);
    250. $s="%子%";
    251. $qian=3200;
    252.  $zhi->bindParam('s',$s);
    253.    $zhi->bindValue('qian',3200);
    254.   $res=$zhi->execute();
    256.   if($res){
    257.   var_dump($zhi->fetchAll(2));
    259.   }catch(PDOException $e){
    260. var_dump($e->getMessage());
    261. }
    265.   <?php
    266. try{
    267. $dsn="mysql:host=127.0.0.1;port=3306;dbname=demo;charset=utf8";
    268. $pdo=new PDO($dsn,"root","root"); 
    269. $pdo->setAttribute(PDO::ATTR_CASE,PDO::CASE_LOWER);
    270. $sql="SELECT * FROM a";
    271. $zhi=$pdo->query($sql);
    273. if($zhi){
    274. echo "执行成功";
    275. echo "<pre>";
    277.  $fetch_data=[];
    278.   while ($row=$zhi->fetch(PDO::FETCH_ASSOC)){
    279.   $fetch_data[] = $row;
    280.   }
    281.   var_dump($fetch_data);
    282.   echo"</pre>";
    283. }else{
    284. echo"执行失败";
    285. }
    286. }catch(PDOException $e){
    287. var_dump($e->getMessage());
    288. }
    291. //设置错误报告
    292. try{
    293. $dsn="mysql:host=127.0.0.1;port=3306;dbname=demo;charset=utf8";
    294. $pdo=new PDO($dsn,"root","root"); 
    295.   $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    296.   $sql="SELECT * FROM a";
    297.   $zhi=$pdo->query($sql);
    298.   if($zhi){
    299.   echo"执行成功";
    300.   }else{
    301.   var_dump($pdo->errorInfo()[2]);
    302.   }
    303. }catch(PDOException $e){
    304. var_dump($e->getMessage());
    305. }
    308. try{
    309. $dsn="mysql:host=127.0.0.1;port=3306;dbname=demo;charset=utf8";
    310. $pdo=new PDO($dsn,"root","root"); 
    311. //余额表
    312. //CREATE TABLE a(
    313. //  n VARCHAR(15) NOT NULL,
    314. //  r INT(10)
    315. //  )ENGINE=innodb;
    316.  //INSERT INTO a VALUES('夏亚','1000'),('阿姆罗','1000'); 
    317. $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    318. $pdo->beginTransaction();
    319. $sql="UPDATE a SET rmb=rmb-200 WHERE name='夏亚'";
    320. $pdo->exec($sql);
    321.   $sql="UPDATE a SET rmb=rmb+200 WHERE name='阿姆罗'";
    322. $pdo->exec($sql);
    323. $pdo->commit();
    324. }catch(PDOException $e){
    325. var_dump($e->getMessage());
    326. }
    328. //预处理流程
    329. try{
    330. $dsn="mysql:host=127.0.0.1;port=3306;dbname=demo1;charset=utf8";
    331. $pdo=new PDO($dsn,"root","root"); 
    332. $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    333.   $sql="SELECT * FROM a";
    334.   $zhi=$pdo->prepare($sql);
    335.   $res=$zhi->execute();
    336.   if($res){
    337.   $tl=$zhi->fetchAll(PDO::FETCH_ASSOC);
    338.     var_dump($tl);
    339.   }else{
    340.   echo"执行失败";
    341.   }
    342.   }catch(PDOException $e){
    343.   $pdo->rollback();
    344. var_dump($e->getMessage());
    345. }
    348. try{
    349. $dsn="mysql:host=127.0.0.1;port=3306;dbname=demo1;charset=utf8";
    350. $pdo=new PDO($dsn,"root","root"); 
    351. $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    352. $sql="SELECT * FROM a WHERE name LIKE ? AND rmb=?;";
    353.   $zhi=$pdo->prepare($sql);
    354.   $s="%红%";
    355.   $r="1000";
    357.   $zhi->bindParam(1,$s);
    358.   $zhi->bindParam(2,$r);
    360.    $zhi->bindValue(1,"%子%");
    361.    $zhi->bindValue(2,3200);
    363.   $res=$zhi->execute();
    364.   if($res){
    365.   var_dump($zhi->fetchAll(2));
    367.   }
    369. }catch(PDOException $e){
    370.   $pdo->rollback();
    371. var_dump($e->getMessage());
    372. }
    375. try{
    376. $dsn="mysql:host=127.0.0.1;port=3306;dbname=demo1;charset=utf8";
    377. $pdo=new PDO($dsn,"root","root"); 
    378. $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    379. $sql="SELECT * FROM a WHERE name LIKE :search AAND rmb = :qian;";
    380.   $zhi=$pdo->prepare($sql);
    382. $s="%子%";
    383. $qian=3200;
    384.  $zhi->bindParam('s',$s);
    385.    $zhi->bindValue('qian',3200);
    386.   $res=$zhi->execute();
    388.   if($res){
    389.   var_dump($zhi->fetchAll(2));
    391.   }catch(PDOException $e){
    392. var_dump($e->getMessage());
    393. }

    笔记：
    设置PDO的选项：setAttribute()

    设置列名强制大写：pdo->setAttribute(PDO::ATTR_CASE,PDO::CASE_UPPER);
    数据库里面设置的字段是大写php获取到就是大写pdo->setAttribute(PDO::ATTR_CASE,PDO::CASE_NATURAL);
    设置强制列名小写：pdo->setAttribute(PDO::ATTR_CASE,PDO::CASE_LOWER);

    结果类别
    PDO::FETCH_ASSOC 结果设置未关联数组
    PDO::FETCH_BOTH 结果只为混合数组
    PDO::FETCH_NUM 结果只为索引数组
    fetch(结果类别)；获取一条数据；类似于mysql的写法

    事物处理：事物处理是用来保证数据操作的原子性（成功或失败）
    保证多个sql语句处理全部成功则数据修改生效，如果其中任何一个语句出差那所有的语句修改都不生效

    事物处理对数据存储引擎有要求
    MyISAM 不支持事物

    innodb 支持事物

    PDO预处理 预先处理数据步骤：
    1.发送sql语句给预处理2.执行预处理的sql语句

    参数绑定
    sql语句在开发中，比如搜索功能；通常搜索值都是由外传递进来的；
    sql注入：如果用户是黑客，输入一些特殊的代码，服务器如果没处理直接丢到sql语句发送给mysql执行可能会被黑掉