PHP的基礎語法教學已經在上回告一段落了,接下來要進入連接資料庫的部份。
不過在這之前,要先熟悉資料庫的使用方法。
所以今天要來教大家如何透過基本語法,將資料存到資料庫裡。
打開terminal,輸入:
$mysql -uroot -p
接著輸入mySQL的密碼,就會看到這個畫面:
首先新增一個 MySQL 使用者
GRANT ALL ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
把 username, password 換成要新增的username和密碼即可. (密碼必須符合你所設定的密碼規則)
說明:
ALL: 授權的權限(SELECT, INSERT .... etc)
.: Table Name, Db_name.*, 指定可以存取哪些Db/Table
username: 要新增的 username
localhost: 可從哪邊來存取, 可用 % (% 代表全部)
接下來我們要先建立一個database(資料庫),語法如下:
create database 資料庫名稱;
MySQL語法和PHP的陳述句一樣,每句結尾都要加上';'表示結束。
看到這行就表示建立成功:
Query OK, 1 row affected (0.01 sec)
然後,我們要在這個database中建立tables(資料表):
use 資料庫名稱;
建立一個table
格式如下:
create table 資料表名稱 (欄位1名稱 資料型態(資料長度), 欄位2名稱 資料型態(資料長度), 欄位3名稱 資料型態(資料長度), 欄位4名稱 資料型態(資料長度));
例如:
create table students(id integer auto_increment primary key, name char(20), score integer(3));
常用資料庫資料型態
- integer (整數)
- char (1~255字元字串)
- varchar (不超過255字元不定長度字串)
- text (不定長度字串最多65535字元)
auto_increment為自動增加,每次新增一筆資料進去,使用者不必自己輸入這欄,MySQL會自動幫你新增。
primary key則是table中具有唯一性與識別性的欄位,每個table中都只能有一個欄位是primary key。
儲存一筆資料進入table,格式如下:
insert into 資料表名稱(欄位1,欄位2,欄位3,欄位4)
values('值1','值2','值3','值4');
假設我們要新增四筆學生的資料:
insert into students(name, score) values('Rose', 87);
insert into students(name, score) values('Lisa', 88);
insert into students(name, score) values('Jisoo', 89);
insert into students(name, score) values('Jenny', 90);
由於id是auto_increment的欄位,因此不需要另外輸入。
接著我們想看看所有學生的資料,查看table所有欄位的格式如下:
select*from students;
就可以看到這個畫面了:
mysql> select* from students;
+----+-------+-------+
| id | name | score |
+----+-------+-------+
| 1 | Rose | 87 |
| 2 | Lisa | 88 |
| 3 | Jisoo | 89 |
| 4 | Jenny | 90 |
+----+-------+-------+
若想要修改其中一筆資料的內容,則格式如下:
update 資料表名稱 set 欄位1='值1',欄位2='值2',欄位3='值3',... 欄位N='值N'
where (欄位='值');
例如:
update students set score=86 where (name='Jenny');
再次查看table所有欄位,就能看到更新過的內容了
mysql> select* from students;
+----+-------+-------+
| id | name | score |
+----+-------+-------+
| 1 | Rose | 87 |
| 2 | Lisa | 88 |
| 3 | Jisoo | 89 |
| 4 | Jenny | 86 |
+----+-------+-------+
刪除一筆資料:
delete from 資料表名稱 where(id='4');
MySQL 亂碼解決方法:
show variables like 'collation%';
show variables like 'char%'; 查看設定狀態
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
若不是utf-8,vim /etc/mysql/my.cnf (或/etc/my.cnf) 在最下面加上
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
權限不足的話,:w !sudo tee %
然後terminal輸入:
$service mysql restart
(mariadb則是)
$service mariadb restart
db跟table、columns也要設定為utf8
show create database databaseName; = SHOW CREATE SCHEMA databaseName;
查看資料表建表資訊(包含編碼)
show create table tableName;
因為character_set_database是latin1,因此table建立時預設也是latin1
+-------+----------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------+
| tests | CREATE TABLE `tests` (
`name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------+
查看資料表欄位屬性
show full columns from tableName;
+-------+----------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+----------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| name | char(20) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
+-------+----------+-------------------+------+-----+---------+-------+---------------------------------+---------+
SELECT default_character_set_name FROM information_schema.SCHEMATA
WHERE schema_name = "databaseName";
SELECT CCSA.character_set_name FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "databaseName" AND T.table_name = "tableName";
SELECT CCSA.character_set_name FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "board" AND T.table_name = "databaseName";
SELECT CCSA.character_set_name FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "schemaname";
另外也可以將mysqli_set_charset($db,"utf8");
放在php script 裡試試看(但我還不確定有沒有作用)。
把之前有輸入過中文且原本呈現亂碼的資料清空,再輸入新的資料應該就能看到中文了
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
2021/4/15補充:
建議儘量用utf8mb4,如此一來儲存emoji的時候才不會爆炸。
但要注意的是,utf8是把3個byte當一個字元,utf8mb4則是把一個字元當4個byte,
以innodb作為資料庫引擎來說,字串長度的最大限制為767個字元;myisam則是1000 byte。
因此當編碼為utf84,varchar長度預設為255時,244*4 > 767,在innodb就會報錯Specified key was too long; max key length is 767 bytes
這時有幾種做法可以解決這個問題: