實作練習

我們希望可以完成如下的表單
 

(1) 先建立新的資料庫 (紅色部分依你的需求更改)
建立資料庫,名稱為newdbname
並且使用字元集為 utf8,排序規則是 utf8_general_ci (不分大小寫)。

參考資料:
如何使用指令知道資料庫的字元集和排序規則?
MySQL CHARACTER SET 與 COLLATION

create database newdbname
character set utf8
collate utf8_general_ci;

(2) 開始使用newdbname這個資料庫。

use newdbname;

(3)  建立資料表單 customer

CREATE TABLE customer (
cus_id int NOT NULL,
cus_name varchar(255) NOT NULL,
cus_address varchar(255),
cus_no char(3),
PRIMARY KEY (cus_id) );

參考資料(Create):
https://www.mysql.tw/2013/03/sql-commands-of-ddl-data-definition.html
https://www.mysql.tw/2014/05/mysql-ddl-data-definition-language.html
參考資料(Data type):
https://www.mysql.tw/2017/03/mysql-data-types.html
https://www.mysql.tw/2013/04/mysqltime-datetime-timestamp.html
參考資料(Keys):
https://www.mysql.tw/2013/03/primaryuniqueindex.html
參考資料(Other SQL):
https://www.mysql.tw/2014/05/sql-select.html
https://www.mysql.tw/2013/03/sqlddldmldcltcl.html

(4)  建立資料表單 product

CREATE TABLE product (
prod_id int NOT NULL,
prod_name varchar(255) NOT NULL,
prod_price int,
prod_inventory int, 
prod_no char(3),
PRIMARY KEY (prod_id) );

(5)  建立資料表單 order_head

CREATE TABLE order_head (
ord_id int NOT NULL,
ord_cus_id int,
ord_date char(10),
ord_date_delivery char(10),
PRIMARY KEY (ord_id) );

(6)  建立資料表單 order_body

CREATE TABLE order_body (
ordb_id int NOT NULL,
ordb_ord_id int,
ordb_prod_id int,
ordb_amount int,
PRIMARY KEY (ordb_id) );

(7) 測試與PHP連接,建立一個檔案存成test_connect.php,內容如下:

以下是只適用於EasyPHP 14.1VC9的版本

<?php
//接上資料庫
$link = mysql_connect("localhost", "root", "") or die("Could not connect : " . mysql_error());

//選擇資料庫
mysql_select_db("mysql") or die("Could not select database");

//處理SQL
$query = "SELECT * FROM user";
$result = mysql_query($query) or die("Query failed : " . mysql_error());

//關閉連線
mysql_close($link);
?>

以下是適用於EasyPHP 14.1VC9 及14.1VC11的版本


<?php
//接上資料庫
$link = mysqli_connect("localhost", "root", "", "newdbname");
//檢查連線
if (!$link) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
//關閉連線
mysqli_close($link);

?>

(8) 如果上面沒有錯誤訊息,再建立一個 cus.html,內容如下:

<html>
<head>
<meta content="text/html;charset=UTF-8"
http-equiv="Content-Type">
<title>Test Customer</title>
</head>
<body>
<form action="cus.php" method="post" name="form1">
Cus_id <input name="cus_id" type="text" value=""><BR>
Cus_name <input name="cus_name" type="text" value=""><BR>
Cus_address <input name="cus_address" type="text" value=""><BR>
<input type="submit" value="send">
</form>
</body></html>

(9) 再建立一個 cus.php,內容如下:

以下是只適用於EasyPHP 14.1VC9的版本

<html>
<head>
<meta content="text/html;charset=UTF-8"
http-equiv="Content-Type">
<title>Process SQL - Customer INPUT</title>
</head>
<body>

<?php
if(count($_POST)>0){
$cus_id=$_POST["cus_id"];
$cus_name=$_POST["cus_name"];
$cus_address=$_POST["cus_address"];

//接上資料庫
$link = mysql_connect("localhost", "root", "") or die("Could not connect : " . mysql_error());

//選擇資料庫
mysql_select_db("newdbname") or die("Could not select database");

//處理SQL
$query = "INSERT INTO customer (cus_id, cus_name,cus_address) VALUES ($cus_id, '$cus_name','$cus_address')";
$result = mysql_query($query) or die("Query failed : " . mysql_error());

echo "SQL: ".$query;
//關閉連線
mysql_close($link);

} else {
echo "no data";
}
?>

</body>
</html>

以下是適用於EasyPHP 14.1VC9 及14.1VC11的版本


<html>
<head>
<meta content="text/html;charset=UTF-8"
http-equiv="Content-Type">
<title>Process SQL - Customer INPUT</title>
</head>
<body>
<?php
if(count($_POST)>0){
$cus_id=$_POST["cus_id"];
$cus_name=$_POST["cus_name"];
$cus_address=$_POST["cus_address"];
$con=mysqli_connect("localhost","root","","newdbname");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
// Perform queries 
$query = "INSERT INTO customer (cus_id, cus_name,cus_address) VALUES ($cus_id, '$cus_name','$cus_address')";
mysqli_query($con,$query) or die(mysqli_error($con));

echo "SQL: ".$query;
mysqli_close($con);
} else {
echo "no data";
}
?>
</body>

</html>

(10) 執行cus.html,然後試著填寫資料,看是否會出現錯誤。

(11) 以下是showcus.php,用來執行SELECT

以下是只適用於EasyPHP 14.1VC9的版本

<html>
<head>
<meta content="text/html;charset=UTF-8"
http-equiv="Content-Type">
<title>Process SQL - Customer show</title>
</head>
<body>

<?php

//接上資料庫
$link = mysql_connect("localhost", "root", "") or die("Could not connect : " . mysql_error());

//選擇資料庫
mysql_select_db("newdbname") or die("Could not select database");

//處理SQL
$query = "SELECT * FROM customer";
$result = mysql_query($query) or die("Query failed : " . mysql_error());
if(!$result) {
    die("Database query failed: " . mysql_error());
}
while ($row = mysql_fetch_array($result)) {
    echo $row["cus_name"]."<BR>";
}
echo "<P>SQL: ".$query;

//關閉連線
mysql_close($link);
?>

</body>
</html>

以下是適用於EasyPHP 14.1VC9 及14.1VC11的版本


<html>
<head>
<meta content="text/html;charset=UTF-8"
http-equiv="Content-Type">
<title>Process SQL - Customer show</title>
</head>
<body>

<?php
$link = mysqli_connect("localhost", "root", "", "newdbname");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query = "SELECT * FROM customer";

if ($result = mysqli_query($link, $query)) {

    /* fetch associative array */
    while ($row = mysqli_fetch_row($result)) {
        printf ("%s ~ %s \n", $row[0], $row[1]);
    }

    /* free result set */
    mysqli_free_result($result);
}

/* close connection */
mysqli_close($link);
?>

</body>

</html>

留言

這個網誌中的熱門文章

如何使用EXCEL連接MYSQL

關聯模式的五大鍵 Super key、Candidate Key、Primary Key、Alternate Key、Foreign Key

SELECT SQL語法總整理