next up previous contents
Next: 刪除資料表 Up: Tables 資料表 Previous: 資料型態   Contents

產生資料表

  1. 登入 mysql 產生資料表 dyw_tbl,共有 4 個欄位,dyw_id 是主鍵,不能是 NULL 且自動增加;dyw_title 最長 100 個字元的字串,不能是空的;dyw_author 最長 40 個字元的字串,不能是空的;submission_date 記錄寫入時間。
    [root@kvm8 html]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    .........
    mysql> use dyw;
    Database changed
    mysql> create table dyw_tbl(
        -> dyw_id int not null auto_increment,
        -> dyw_title varchar(100) not null,
        -> dyw_author varchar(40) not null,
        -> submission_date date,
        -> primary key (dyw_id)
        -> );
    Query OK, 0 rows affected (0.19 sec)
    
    mysql> exit
    Bye
    
  2. 查看 dyw_tbl 資料表欄位型態。
    mysql> show columns from dyw_tbl;
    +-----------------+--------------+------+-----+---------+----------------+
    | Field           | Type         | Null | Key | Default | Extra          |
    +-----------------+--------------+------+-----+---------+----------------+
    | dyw_id          | int(11)      | NO   | PRI | NULL    | auto_increment |
    | dyw_title       | varchar(100) | NO   |     | NULL    |                |
    | dyw_author      | varchar(40)  | NO   |     | NULL    |                |
    | submission_date | date         | YES  |     | NULL    |                |
    +-----------------+--------------+------+-----+---------+----------------+
    4 rows in set (0.00 sec)
    
  3. 查看 dyw 資料庫的資料表,出現 dyw_tbl
    [root@kvm8 html]# mysql -uroot -p123qwe -e "use dyw; show tables;"
    +---------------+
    | Tables_in_dyw |
    +---------------+
    | dyw_tbl       |
    +---------------+
    
  4. 使用 php 函數 mysql_query 產生資料表 dywphp_tbl
    [root@kvm8 html]# vim createtbl.php 
    [root@kvm8 html]# cat createtbl.php 
    <html>
    <head>
    <title>Creating MySQL Tables</title>
    </head>
    <body>
    <?php
    $dbhost = 'localhost:3306';
    $dbuser = 'root';
    $dbpass = '123qwe';
    $conn = mysql_connect($dbhost, $dbuser, $dbpass);
    if(! $conn )
    {
      die('Could not connect: ' . mysql_error());
    }
    echo 'Connected successfully<br />';
    $sql = "CREATE TABLE dywphp_tbl( ".
           "dywphp_id INT NOT NULL AUTO_INCREMENT, ".
           "dywphp_title VARCHAR(100) NOT NULL, ".
           "dywphp_author VARCHAR(40) NOT NULL, ".
           "submission_date DATE, ".
           "PRIMARY KEY ( dywphp_id )); ";
    mysql_select_db( 'dywphp' );
    $retval = mysql_query( $sql, $conn );
    if(! $retval )
    {
      die('Could not create table: ' . mysql_error());
    }
    echo "Table created successfully\n";
    mysql_close($conn);
    ?>
    </body>
    </html>
    
  5. 以 php 命令執行 createtbl.php 在 dywphp 資料庫產生資料表 dywphp_tbl,使用瀏覽器開啟 selectdb.php 也可以。
    [root@kvm8 html]# php createtbl.php 
    <html>
    <head>
    <title>Creating MySQL Tables</title>
    </head>
    <body>
    Connected successfully<br />Table created successfully
    </body>
    </html>
    
  6. 查看 dywphp 資料庫的資料表,出現 dywphp_tbl
    [root@kvm8 html]# mysql -uroot -p123qwe -e "use dywphp; show tables;"
    +------------------+
    | Tables_in_dywphp |
    +------------------+
    | dywphp_tbl       |
    +------------------+
    



2018-06-19