PHP 之 访问 php访问mysql
1. php访问mysql流程
mysql> select*from customers; +------------+-----------------+--------------------+--------------+ | customerid | name | address | city | +------------+-----------------+--------------------+--------------+ | 3 | Julie Smith | 25 Oak Street | Airport West | | 4 | Alan Wong | 长沙 | Box Hill | | 5 | Michelle Arthur | 357 North Road | Yarraville | | 6 | 张三 | 碧石东街三号 | 鄂州 | +------------+-----------------+--------------------+--------------+
对数据库操作
1.查询数据 (select)
2.显示数据 (select)
3.插入数据 (insert)
4.更新数据 (update)
5.删除数据 (delete)
2.mysql面向对象
<?php /* php 操作 mysql (面向对象) */ // 1.链接数据库服务器 $connID = mysql_connect('localhost','root','root123'); // 返回连接符 // 2.mysql_select_db()选择数据库文件 mysql_select_db('dbname',$connID); // 3.使用mysql_query()函数执行SQL语句 $result = mysql_query('select*from books',$connID); // 执行查询,返回结果集 /* 从上述结果集中获取信息,这里有两种路径 * 1.用mysql_fetch_array()函数从数组结果集中获取信息; * 2.用mysql_fetch_object()函数从结果集中获取一行作为对象 * 它们的区别在于mysql_fetch_object()返回值是一个对象,而不是数组,也就是该函数只能通过字段名来访问数组 */ while($row = mysql_fetch_array($result)){ echo "<div style=\"height:24px; line-height:24px; font-weight:bold;\">"; //排版代码 echo $row['isbn']."</td>".$row['author']."</td>".$row['title']."<br/>"; echo "</div>"; //排版代码 } // 4. 关闭结果集 释放内存 mysql_free_result($result); // 5. 断开服务器连接 mysql_close($connID);
3. mysqli 面向对象
(1)创建连接,创建数据库,创建表,插入数据库
1 <?php 2 /* mysqli 面向对象 3 */ 5 // 创建连接 6 $conn = new mysqli('localhost','root','root123'); 7 9 // 检测连接 10 if(mysqli_connect_error()){ 11 die("Connection failed: ".mysqli_connect_error()); 12 exit; 13 } 14 15 //创建数据库 16 $sql = "create database dbdemo";//create database 17 18 if($conn->query($sql) == TRUE){ 19 echo "Database created successfully"."<br>"; 20 }else{ 21 echo "Error creating database :".$conn->error."<br>"; 22 } 23 24 // use database 25 $sql = "use dbdemo"; 26 if($conn->query($sql) == TRUE){ 27 echo "use Database successfully"."<br>"; 28 }else{ 29 echo "Error use database :".$conn->error."<br>"; 30 } 31 32 //创建表 33 $sql = "create table customers 34 (customerid int unsigned not null auto_increment primary key, 35 name char(50) not null, 36 address char(100) not null, 37 city char(30) not null 38 )"; 39 40 if($conn->query($sql) == TRUE){ 41 echo "Table customers created successfully"."<br>"; 42 }else{ 43 echo "Error creating table customers:".$conn->error."<br>"; 44 } 45 46 //插入数据库 47 $sql = "insert into customers values 48 (1,'Julie Smith','25 Oak Street','Airport West'), 49 (2,'王五','汉口大道23号','武汉市')"; 50 51 if($conn->query($sql) == TRUE){ 52 echo "insert into tables successfully"."<br>"; 53 }else{ 54 echo "insert into tables customers:".$conn->error."<br>"; 55 } 56 57 //关闭连接 58 $conn->close();
(2) 预处理
优点:预处理语句大大减少了分析时间，只做了一次查询. 绑定参数减少了服务器带宽，你只需要发送查询的参数，而不是整个语句.
<?php /* mysqli 面向对象 */ // 创建连接 $conn = new mysqli('localhost','root','root123','dbdemo'); // 检测连接 if(mysqli_connect_error()){ die("Connection failed: ".mysqli_connect_error()); exit; } // prepare and bind $stmt = $conn->prepare("INSERT INTO MyGuests(name,address,city) VALUES(?, ?, ?)"); $stmt->bind_param("sss", $name, $address, $city); $name = "jon"; $address = "aaa"; $city = "bbb"; $stmt->execute(); $stmt->close(); //关闭连接 $conn->close();
(3)从数据库中读取数据
order by 关键词 : select*from customers order by age (默认asc,升序)
select*from column_name from table_name order by column_name asc|desc
根据两列排序 : select*from column1=value column2=value2 from table_name order by column1,column2
<?php /* mysqli 面向对象 */ // 创建连接 $conn = new mysqli('localhost','root','root123','dbdemo'); // 检测连接 if(mysqli_connect_error()){ die("Connection failed: ".mysqli_connect_error()); exit; } $sql = "select*from customers"; $result = $conn->query($sql); if($result->num_rows > 0){ // 输出每行数据 fetch_assoc() 返回一个关联数组 while($row = $result->fetch_assoc()){ echo $row["customerid"]. "-name: ". $row["name"]." address: ".$row["address"]." city: ".$row['city']."<br>"; } }else{ echo "0 results"; } // where 关键字子句 $sql = "select*from customers where customerid=7"; $result = $conn->query($sql); while($row = mysqli_fetch_array($result)){ echo $row['name'] . "<br>"; } //关闭连接 $conn->close();
(4) update(更新数据)
update customers set name='张三' where customerid=1;
(5) delete(删除表记录)
delete customers where customerid = 1;
4.mysqli 面向过程
<?php /* mysqli 面向过程 */ // 创建连接 $conn = mysqli_connect('localhost','root','root123'); // 检测连接 if(!$conn){ die("Connection failed: ".mysqli_connect_error())."<br>"; exit; } // create database $sql = "create database db1"; if(mysqli_query($conn,$sql)){ echo "Database created successfully"."<br>"; }else{ echo "Error creating database:".mysqli_error($conn)."<br>"; } $sql = "use db1"; mysqli_query($conn,$sql); //create table $sql = "create table person( id int(6) unsigned not null auto_increment primary key, name varchar(20) not null, age int(6) not null)"; if(mysqli_query($conn,$sql)){ echo "table created successfully"."<br>"; }else{ echo "Error creating table:".mysqli_error($conn)."<br>"; } //insert data $sql = "insert into person values(1,'李明',32)"; if (mysqli_query($conn, $sql)){ echo "New record created successfully"; }else{ echo "Error: " . $sql . "<br>" . mysqli_error($conn); } //关闭连接 mysqli_close($conn);
预处理
<?php /* mysqli 面向过程 */ // 创建连接 $conn = new mysqli('localhost','root','root123','dbdemo'); // 检测连接 if(mysqli_connect_error()){ die("Connection failed: ".mysqli_connect_error()); exit; }else{ /* 预处理语句以及绑定参数 * 1.预处理:创建sql语句模板并发送到数据库,预留的值使用参数"?"标记 例如:insert into customers values(?, ?, ?); * 2.数据库解析,编译,对SQL语句模板执行查询优化,并存储结果不输出 * 3.执行:将应用绑定的值传递给参数("?" 标记),数据库执行语句.应用可以多次执行语句,如果参数的值不一样 */ $sql = "insert into customers(name,address,city) values(?,?,?)"; // 为mysqli_stmt_prepare()初始化statement对象 $stmt = mysqli_stmt_init($conn); //预处理 if(mysqli_stmt_prepare($stmt,$sql)){ // 绑定参数 mysqli_stmt_bind_param($stmt,'sss',$name,$address,$city); // 设置参数并执行 $name = '张三'; $address = '南山大道99号'; $city = '深圳'; mysqli_stmt_execute($stmt); }else{ echo "1111111111"; } } //关闭连接 $conn->close();
5. ODBC (application programming interface,api)使我们有能力连接到某个数据源
