- MySQLi (object-oriented)
- MySQLi (procedural) <----這個筆記
- PDO
- 下載 XAMPP 套件
- 安裝 APACHE MySQL PHP
- HTTP APACHE Homepage
- HTTP + APACHE 運作
- 由MySQL官方提供的範例資料庫
- 新加坡大學教學網頁:內容參考資料
- MySQL官方資料庫官方所有.sql檔載點
- 計有World世界資料庫(4000筆)、Sakila電影光碟租借資料庫、airportdb機場資料庫、menageriei資料庫(5000萬筆,大型DB)
- MySQL官方World世界資料庫 world.sql下載,國家200+、城市4000+、語言900+
- MySQL官方Sakila電影光碟租借資料庫 sakila.sql,共有23個表格:電影、電影類別、類別、電影演員表、演員...等等
- MySQL官方airportdb機場資料庫,官方稱大型DB共5000萬筆
- MySQL官方menageriei資料庫
C:\xampp\mysql\bin>mysql -u root -p
Enter password:
...中間略
MariaDB [(none)]>source world-db\world.sql
...中間略
MariaDB [world]>
MariaDB [world]> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.001 sec)
MariaDB [world]>
MariaDB [world]> describe city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.021 sec)
MariaDB [world]> select * from city;
+------+-----------------------------------+-------------+----------------------+------------+
| ID | Name | CountryCode | District | Population |
+------+-----------------------------------+-------------+----------------------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
...中間略
| 4079 | Rafah | PSE | Rafah | 92020 |
+------+-----------------------------------+-------------+----------------------+------------+
4079 rows in set (0.007 sec)
MariaDB [world]>
MariaDB [world]> describe country;
MariaDB [world]> select * from country;
MariaDB [world]>
表格countrylanguage的欄位及984筆語言資料
MariaDB [world]> describe countrylanguage;
MariaDB [world]> select * from countrylanguage;
MariaDB [world]>
MariaDB [none]> use world;
MariaDB [world]>
依人口數排序:顯示中國、美國、俄國人口大於1百萬的城市
- ZIP檔解壓到適當的地方,例如 C:\xampp\mysql\bin>,可以直接叫
- 解壓後有2個 .sql 檔,sakila-schema.sql建立資料庫及其23個表格,sakila-data.sql建立記錄
C:\xampp\mysql\bin>mysql -u root -p
Enter password:
...中間略...
MariaDB [none]>source saila-schema.sql
...中間略...
MariaDB [none]>source saila-data.sql
...中間略...
MariaDB [sakila]>
MariaDB [sakila]> show tables;
+----------------------------+
| Tables_in_sakila |
+----------------------------+
| actor |
| actor_info |
...中間略...
| staff_list |
| store |
+----------------------------+
23 rows in set (0.001 sec)
MariaDB [sakila]>
MariaDB [sakila]>
MariaDB [sakila]> describe actor;
+-------------+----------------------+------+-----+---------------------+-------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------------------+-------------------------------+
| actor_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | NO | MUL | NULL | |
| last_update | timestamp | NO | | current_timestamp() | on update current_timestamp() |
+-------------+----------------------+------+-----+---------------------+-------------------------------+
4 rows in set (0.018 sec)
MariaDB [sakila]>
MariaDB [sakila]>
MariaDB [sakila]> select * from actor;
+----------+-------------+--------------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+-------------+--------------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
| 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 |
...中間略...
| 199 | JULIA | FAWCETT | 2006-02-15 04:34:33 |
| 200 | THORA | TEMPLE | 2006-02-15 04:34:33 |
+----------+-------------+--------------+---------------------+
200 rows in set (0.001 sec)
MariaDB [sakila]>
MariaDB [sakila]>
MariaDB [sakila]> describe actor_info;
+------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------------------+------+-----+---------+-------+
| actor_id | smallint(5) unsigned | NO | | 0 | |
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | NO | | NULL | |
| film_info | mediumtext | YES | | NULL | |
+------------+----------------------+------+-----+---------+-------+
4 rows in set (0.027 sec)
MariaDB [sakila]>
- 共有200個演員資訊
- 猜測:猜不出來第4個欄位是什麼?actor和actor_info關聯?
- 使用 SAMPLE Database,新增記錄
- [ex007.php] 以表單新增一筆記錄