程式研究筆記

本次環境使用的是MySQL5.7版 1.查看資料表的欄位與類型 SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tableName'; mysql> SELECT COLUMN_NAME, DATA_TYPE -> FROM INFORMATION_SCHEMA.COLUMNS -> WHERE TABLE_NAME = 'Persons_copy'; +-------------+-----------+ | COLUMN_NAME | DATA_TYPE | +-------------+-----------+ | PersonID | int | | LastName | varchar | | FirstName | varchar | | Address | varchar | | City | varchar | +-------------+-----------+ 5 rows in set (0.00 sec)

本次環境使用的是MySQL5.7版 1. 根據表名稱刪除表 DROP TABLE tableName mysql> show tables; +-------------------+ | Tables_in_test | +-------------------+ | Persons | | Persons_copy | | Persons_copy_copy | +-------------------+ 3 rows in set (0.00 sec) mysql> DROP TABLE Persons_copy_copy; Query OK, 0 rows affected (0.11 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | Persons | | Persons_copy | +----------------+ 2 rows in set (0.00 sec)

本次環境使用的是MySQL5.7版 1. 新增一個欄位至該表 ALTER TABLE tableName ADD column_name datatype; mysql> SELECT * FROM Persons_copy; +----------+----------+-----------+---------+------+ | PersonID | LastName | FirstName | Address | City | +----------+----------+-----------+---------+------+ | 11 | Lester | Mark | No | No | +----------+----------+-----------+---------+------+ 1 row in set (0.00 sec) mysql> ALTER TABLE Persons_copy ADD column_name varchar(255); Query OK, 0 rows affected (0.42 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM Persons_copy; +----------+----------+-----------+---------+------+-------------+ | PersonID | LastName | FirstName | Address | City | column_name | +----------+----------+-----------+---------+------+-------------+ | 11 | Lester | Mark | No | No | NULL | +----------+----------+-----------+---------+------+-------------+ 1 row in set (0.00 sec) 2. 刪除一個欄位至該表 ALTER TABLE tableName DROP column

本次環境使用的是MySQL5.7版 1. 清空該表中的所有數據 TRUNCATE TABLE tableName mysql> SELECT * FROM Persons_copy; +----------+----------+-----------+---------+------+ | PersonID | LastName | FirstName | Address | City | +----------+----------+-----------+---------+------+ | 11 | Lester | Mark | No | No | +----------+----------+-----------+---------+------+ 1 row in set (0.00 sec) mysql> TRUNCATE TABLE Persons_copy; Query OK, 0 rows affected (0.14 sec) mysql> SELECT * FROM Persons_copy; Empty set (0.00 sec)

本次環境使用的是MySQL5.7版 1. 根據括弧內所輸入的欄位與資料類型建立表(table) CREATE TABLE table_name(     column1 datatype,     column2 datatype,     column3 datatype,     ... ); mysql> CREATE TABLE Persons( PersonID int, -> LastName varchar(255), -> FirstName varchar(255), -> Address varchar(255), -> City varchar(255) -> ); Query OK, 0 rows affected (0.17 sec) 2.根據舊表欄位新增新表 CREATE TABLE newTableName As SELECT column1,column2 ... FROM  oldTableName WHERE ...; mysql> CREATE TABLE Persons_copy As -> SELECT * -> FROM Persons -> Where PersonID=11; Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> INSERT INTO Persons_copy VALUES (11,"Lester","Mark","No","No"); Query OK, 1 row affected (0.03 sec) mysql> SELECT * FROM Persons_copy; +----------+----------+-----------+---------+------+ | PersonID | LastName | FirstName | Address | City | +----------+----------+--

本次環境使用的是MySQL5.7版 1. 搜尋這個table中所有欄位、資料。 SELECT * FROM tableName mysql> SELECT * FROM Persons; +----------+----------+-----------+---------+------+ | PersonID | LastName | FirstName | Address | City | +----------+----------+-----------+---------+------+ | 11 | Lester | Mark | No | No | | 12 | Stenson | Jared | No | No | | 13 | Walker | Aidan | No | No | +----------+----------+-----------+---------+------+ 3 rows in set (0.00 sec) 2. 根據填入的欄位(cloumn1,cloumn2...)搜尋資料。 SELECT cloumn1,cloumn2... FROM tableName mysql> SELECT PersonID,LastName,FirstName FROM Persons; +----------+----------+-----------+ | PersonID | LastName | FirstName | +----------+----------+-----------+ | 11 | Lester | Mark | | 12 | Stenson | Jared | | 13 | Walker | Aidan | +----------+----------+-----------+ 3 rows in set (0.00 sec)

<?php $mysqli = new mysqli("localhost","root","","practice",3306); if($mysqli->connect_errno){ echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ")" . $mysqli->connect_error; } echo $mysqli->host_info . "\n"; localhost via TCP/IP localhost via UNIX socket localhost via TCP/IP ▲在windows主機上會看到這個 使用TCP/IP代表連接是透過網路卡 意謂著會受到網絡防火墻和網卡相關的限制 localhost via UNIX socket ▲在linux主機上會看到這個 使用socket代表本地直連，未透過網路卡 則不受網絡防火墻和網卡相關的的限制 會透過mysql.sock檔案 可以透過以下方式找到你的mysql.sock