一、 新增帳號
grant all privileges on *.* to pcschool@localhost identified by 'phpmysql'; grant all privileges on board.* to php1@localhost identified by 'mysqlstart1'; grant select on board2.* to php3@localhost identified by 'mysqlstart3'; |
二、 PHP連結資料庫語法(db.php,資料庫為pcschool)
〈html〉〈head〉〈meta http-equiv="Content-Type" content="text/html; charset=utf-8"〉 〈title〉資料庫連線〈/title〉〈/head〉〈body〉〈? $link = @mysql_connect("localhost", "pcschool", "phpmysql"); $link_db = @mysql_select_db("pcschool123446"); if($link) { echo "主機連結OK!...."; if($link_db) { echo "資料庫連結OK!...."; } if(!$link_db) { echo "資料庫連結失敗!....."; }} if(!$link) { die( "連結主機失敗!....");} echo "Hello"; ?〉 〈/body〉〈/html〉 |
1. 問題:如果mysql_connect出錯,會如何?
2. 問題:如果mysql_selsect_db出錯,會如何?
3. 問題:在發生錯誤之後,可否在顯示訊息後跳出網頁?
三、 mysql編碼與傳輸:unicode.php
〈? mysql_query("SET NAMES utf8"); mysql_query("CHARACTER SET utf8"); mysql_query("SET CHARACTER_SET_CLIENT =utf8"); mysql_query("SET COLLATION_CONNECTION=utf8_general_ci"); mysql_query("SET CHARACTER_SET_RESULTS =utf8"); mysql_query("SET CHARACTER_SET_SERVER = utf8"); mysql_query("SET character_set_connection=utf8"); ?〉 |
四、 PHP函數--送出執行語法:mysql_query("SQL 語法")
【提供mysql_query1.php】
〈html〉〈head〉 〈meta http-equiv="Content-Type" content="text/html; charset=utf-8"〉 〈title〉mysql_query〈/title〉〈/head〉〈body〉〈? mysql_connect("localhost", "pcschool", "phpmysql") or die("無法連結主機"); mysql_select_db("pcschool") or die("無法連結資料庫"); //include("unicode.php"); //以下新增語法請做練習 //以下兩行為一行 //$sql = "insert into list (username, email, sex) values('pcschool', 'quota123@ms14.url.com.tw', '男')"; //$sql = "insert into list (email, sex) values('quota123@ms14.url.com.tw', '男')"; //$sql = "insert into list values('quota123@ms14.url.com.tw','pcschool','男')"; $sql = "insert into list values('','pcschool','男')"; mysql_query($sql) or die(mysql_error( ) ); ?〉〈/body〉〈/html〉 |
若沒有加入unicode.php,MySQL內的資料會如何呢?
若資料表名稱錯誤或儲存的欄位數量不對,系統會如何回應呢?
【提供mysql_query01.php】
〈html〉〈head〉 〈meta http-equiv="Content-Type" content="text/html; charset=UTF-8"〉 〈title〉以下的Query是否有錯?〈/title〉〈/head〉〈body〉 〈?php include("server.php"); $a='test'; $b='test2'; $sql="INSERT INTO orders(CustomerID,EmployeeID)VALUES ($a,$b);"; mysql_query($sql); //mysql_query($sql) or die(mysql_error( ) ); //echo $sql; ?〉〈/body〉〈/html〉 |
若兩個query一起執行,但其中一個有問題會如何呢?【提供mysql_query02.php】
〈html〉〈head〉 〈meta http-equiv="Content-Type" content="text/html; charset=UTF-8"〉 〈title〉兩個Query執行會如何?〈/title〉〈/head〉〈body〉 〈?php include("server.php"); $sql="INSERT INTO orders(CustomerID,EmployeeID)VALUES ('er', '23');"; mysql_query($sql); $a='test'; $b='test2'; $sql="INSERT INTO orders(CustomerID,EmployeeID)VALUES ($a,$b);"; mysql_query($sql); //mysql_query($sql) or die(mysql_error( ) ); //echo $sql;//得到與mysql_error( ) ?〉〈/body〉〈/html〉 |
上述兩個練習請修正【修改為mysql_query03.php】:
$a='test'; $b='test2'; $sql="INSERT INTO orders(CustomerID,EmployeeID)VALUES ('$a',$b);"; |
複製資料表語法
create table insertemployees select * from employees; create table insertjob select * from job; create table updateemployees1 select * from employees; create table updateemployees2 select * from employees; create table updateemployees3 select * from employees; create table updatejob select * from job; create table updateproducts select * from products; create table delemployees1 select * from employees; create table delemployees2 select * from employees; create table delemployees3 select * from employees; create table deljob select * from job; |
新增資料語法
insert into insertjob values (12); insert into insertjob values ('manager',12); select * from insertjob; insert into insertjob values (12,'manager'); select * from insertjob; insert into employees(lastname,firstname,employeeid) values ('jiannrong','yeh',2009);」 select lastname,firstname,employeeid from employees where employeeid=2009; insert into insertjob(employeeid,title) select employeeid ,concat(firstname,"-",lastname) from employees; select * from insertjob |
五、 資料查詢語法
1.基本查詢
select * from employees; select firstname, lastname from employees; select city from employees; select distinct city from employees; |
2.As的使用
select firstname as f, lastname as l from employees; select productname, unitprice ,unitsinstock ,unitprice * unitsinstock as total from products; select firstname,hiredate,curdate( ) as nowdate,(year(curdate( ) )-year(hiredate)) as years from employees; select firstname,lastname,concat(firstname, "-- ",lastname) as yourname from employees; |
3.排序
select firstname, lastname, hiredate from employees order by firstname; select firstname, lastname, hiredate from employees order by firstname desc; select firstname, lastname, hiredate from employees order by firstname asc; select firstname, lastname from employees order by hiredate desc; select firstname, lastname from employees order by firstname desc,lastname asc; select firstname, lastname from employees order by lastname asc ,firstname desc; |
六、 PHP函數-- mysql_num_rows( ) 計算查詢後的筆數【請自行撰寫】
〈html〉〈head〉 〈meta http-equiv="Content-Type" content="text/html; charset=utf-8"〉 〈title〉計算筆數〈/title〉〈/head〉〈body〉〈? @mysql_connect("localhost", "pcschool", "phpmysql") or die("無法連結主機"); @mysql_select_db(“pcschool”) or die("無法連結資料庫"); include("unicode.php"); $abc="select username from list"; echo $abc."〈br〉"; $sql = mysql_query($abc) or die(mysql_error( ) );; $rows = mysql_num_rows($sql); if($rows==""){ echo "查無資料!"; }else{ echo "有 ".$rows." 筆資料喔!";} ?〉〈/body〉〈/html〉 |
七、 回傳欄位資料【請依照講義修改mysql_query04.php】
〈html〉〈head〉 〈meta http-equiv="Content-Type" content="text/html; charset=UTF-8"〉 〈title〉顯示記錄〈/title〉〈/head〉〈body〉 〈?php include("server.php"); $sql="SELECT * FROM orders"; $sql2=mysql_query($sql) or die(mysql_error( ) ); echo $sql2; $list1= mysql_fetch_array($sql2); echo $list1['OrderID']; ?〉 〈/body〉〈/html〉 |
請修改上例,觀察顯示內容【請將mysql_query04.php另存新檔後依講義修改】
$list1= mysql_fetch_array($sql2); echo $list1['OrderID']."〈br〉"; echo $list1['OrderID']."〈br〉"; $list1= mysql_fetch_array($sql2); echo $list1['OrderID']."〈br〉"; |
八、 顯示資料【以array方式設計】 【請依照講義修改mysql_query06.php】
〈html〉〈head〉 〈meta http-equiv="Content-Type" content="text/html; charset=UTF-8"〉 〈title〉顯示所有資料〈/title〉〈/head〉〈body〉 〈? include("server.php"); $sql="SELECT * FROM orders"; $sql2=mysql_query($sql) or die(mysql_error( ) ); while($list1= mysql_fetch_array($sql2)) { echo $list1['OrderID']." ".$list1['OrderDate']."〈br〉"; } ?〉〈/body〉〈/html〉 |
九、 表單送資料至PHP後查詢資料【提供表單網頁】
1. 練習SQL語法中where語法
select employeeid,firstname from employees where (employeeid 〉= 3); select employeeid,firstname from employees where (employeeid in(2,4,9)); select employeeid,firstname from employees where (employeeid not in(2,4,9)); select country,companyname from customers where (country in('Argentina','Mexico')); select region,companyname from customers where (region is null); select region,companyname from customers where (region is not null); select firstname from employees where (firstname like 'a%'); select firstname from employees where (firstname like '%a%'); select firstname from employees where (firstname like '%a'); select companyname,contactname,country from customers where (country in('France','Germany') and companyname like 'b%'); select companyname,contactname,country from customers where (country in('France','Germany') or companyname like 'b%'); |
2. 若直接將post傳送過來的資料送至sql語法?【提供query03.php】
〈html〉〈head〉 〈meta http-equiv="Content-Type" content="text/html; charset=UTF-8"〉 〈title〉接收資料〈/title〉〈/head〉〈body〉 〈? include("server.php"); $sql="SELECT * FROM orders where CustomerID=".$_POST['text1']; $sql2=mysql_query($sql) or die(mysql_error()); echo mysql_num_rows($sql2)."〈br〉"; while($list1= mysql_fetch_row($sql2)) { echo $list1[0]." ".$list1[1]."〈br〉"; } ?〉〈/body〉〈/html〉 |
3. 上例修改【請依照講義修改query03.php】
//$sql="SELECT * FROM orders where CustomerID=".$_POST['text1']; $sql="SELECT * FROM orders where CustomerID='".$_POST['text1']."'"; |
4. 上例修改【請依照講義修改query03.php】
//$sql="SELECT * FROM orders where CustomerID=".$_POST['text1']; //$sql="SELECT * FROM orders where CustomerID='".$_POST['text1']."'"; $a=$_POST['text1']; $sql="SELECT * FROM orders where CustomerID='$a'"; |
5. 測試各種模糊查詢【提供query04.php,但請修改表單網頁路徑】
〈html〉〈head〉 〈meta http-equiv="Content-Type" content="text/html; charset=UTF-8"〉 〈title〉接收資料〈/title〉 〈/head〉〈body〉 〈?php include("server.php"); //測試各種模糊查詢 $sql="SELECT * FROM orders where CustomerID like '%$a%'"; $sql="SELECT * FROM orders where CustomerID like '$a%'"; $sql="SELECT * FROM orders where CustomerID like '%$a'"; $sql2=mysql_query($sql) or die(mysql_error()); echo mysql_num_rows($sql2)."〈br〉"; while($list1= mysql_fetch_row($sql2)) { echo $list1[0]." ".$list1[1]."〈br〉"; } ?〉〈/body〉〈/html〉 |
十、 傳遞參數給SQL語法:例如查詢customers筆數與範圍(提供表單)
【請自行撰寫】
〈html〉〈head〉 〈meta http-equiv="Content-Type" content="text/html; charset=utf-8"〉 〈title〉回傳各欄位資料〈/title〉〈/head〉〈body〉〈? @mysql_connect("localhost", "pcschool", "phpmysql") or die("無法連結主機"); mysql_select_db("pcschool") or die("無法連結資料庫"); include("unicode.php"); echo $_SERVER['HTTP_REFERER']."〈br〉"; //$sql1="select * from customers limit ".$_POST['first'].",".$_POST['total']; //$sql1="select * from customers limit $_POST['first'],$_POST['total']"; $a=$_POST['first']; $b=$_POST['total']; $sql1="select * from customers limit $a,$b"; //echo $sql1; //$sql = @mysql_query($sql1); $sql = mysql_query($sql1) or die(mysql_error()); $rows = @mysql_num_rows($sql); //echo $rows; if($rows==""){ echo "查無資料!"; }else{ echo "有 ".$rows." 筆資料喔!"; } ?〉〈/body〉〈/html〉 |
限制筆數之SQL語法:
select employeeid,firstname, lastname from employees ; select employeeid,firstname, lastname from employees limit 1,30; select employeeid,firstname, lastname from employees limit 0,5; select employeeid,firstname, lastname from employees limit 6; select productid,productname,unitprice from products order by unitprice desc limit 1; select employeeid,firstname, lastname from employees order by rand() limit 6; |
十一、 其他
沒有留言:
張貼留言