2017-10-16 | php

数据库操作

數據庫分類

層次數據庫：類似xml（html）的文本結構
網狀數據庫
關係數據庫：SQL；Struct Query Language（結構化查詢語言），其實是一個“國際化”標準
- 各關係數據庫，其實都是實現（賓擴充）該標準的一樣產品
- 本質：將大量的“數據值”通過數據管理系統進行某種複雜但是邏輯結構上清晰有序的存儲
- 但在數據庫文件表現上，其實只是非常有限的幾個文件或文件夾。新建一個數據庫會新建一個對應文件夾，新建一個表會新建一個或多個文件
- 關係數據庫的常用基本術語
  - 數據data
  - 數據庫database
  - 數據庫管理系統dbms
  - 表（數據表）table
  - 字段field，列column
  - 行row，紀錄record

mysql數據庫應用系統的基本結構

數據庫客戶端（軟體）
數據庫管理系統（軟體）
數據（文件）

mysql數據庫操作的基本模式（流程）

建立連接（認證身份）
客戶端向服務器端發送sql命令
服務器端執行命令，賓返回執行的結果
客戶端接收結果（賓顯示）
斷開連接

mysql系統級操作及基本語法規定

mysql數據庫的安裝與配置
啟用/停止mysql數據庫服務
- 命令行模式：net start/stop mysql（window端）
- 服務模式：控制面板 > 管理工具 > 服務 > mysql > 啟動/停止（window端）
登錄/退出mysql數據庫系統
- 登錄：mysql -h服務器地址 -u登錄名 -P端口號 -p
- 或登錄：mysql –host=服務器地址 –user=用戶名 –port=端口 –password
- 退出：quit；或exit；
- 注意：登錄數據庫系統後，需要使用“set names 編碼名；”來設定當前連接數據庫的“環境變量”，即當前跟數據庫打交道的“客戶端”本身的編碼。通常說，cmd客戶端中是固定的gbk編碼，而php網頁中，是該網頁文件的編碼（現在主流都是utf-8）
備份/恢復數據
- 備份：mysql dump -h服務器地址 -u登錄名 -P端口號 -p數據庫名 > 文件名
- 恢復：mysql -h服務器地址 -u登錄名 -P端口號 -p數據庫名> 文件名
基本語法規定
- 註釋：
  - 單行註釋：#註釋內容
  - 多行註釋：– 註釋內容（注意，兩個“–”之後有一個空格）
  - 多行註釋：/註釋內容/
- 語句行：
  - 一條語句也成為一條命令，通常用一個分號（；）結束，也可以通過“delimiter 新結束符”命令來設定新的結束符
  - 語句的執行是以一條語句為單位進行，一次執行一條語句
- 大小寫：
  - mysql中各種系統關鍵字和命令行本身是不區分大小寫的
  - mysql中自定義的名稱（標識符）的大小寫問題，有的區分（跟操作系統有關），有的不區分，詳見如下“明明規則”
- 命名（標識符）規則：
  - 可以自己命名的名字，稱為標識符，包括：數據庫名，表名，字段名，視圖名，函數名，變量名，用戶名…等等。
  - 可以命名標識符的字符比常規的語言多，但特別建議只用：字母數字和下劃線，並不用數字開頭。
  - 非常規自負或系統關鍵字雖然可以作為標識符使用，但最好要包在反引號（數字1嘴邊那個反撇）中，並且不推薦。
  - 對數據庫名，表名，和視圖名，在window系統中不區分大小寫，而其他系統中區分，建議全使用小寫，並採用下劃線分割法。
  - 對其他自己命名的標識符（字段名，函數名，過程名），不區分大小寫，但也建議全使用小寫，並採用下劃線分割法

PHP中才做數據庫的基本代碼和流程

連接數據庫：
- $mylink = mysql connect("localhost", "root", "root");
設定連接編碼（通常是utf）
- mysql_set_charset("utf8"); // 也可以使用：mysql_query("set names utf8");
選擇數據庫（如有需要）
- mysql_select_db("數據庫名"); // 也可以使用：mysql_query("use 數據庫名");
執行sql命令
- $resule = mysql_query("幾乎所有sql語句");
- 返回結果通常需要分兩種情形進行處理；
  - 如果是無返回結果數據的語句：
    - 如果$result為true，表示執行成功
    - 如果$result為false，表示執行失敗
  - 如果是有返回數據的語句：
    - 如果$result為false，表示執行失敗
    - 否則，執行成功，需要繼續從結果中去處數據並顯示出來

mysql數據定義語言DDL（Data Definition Language）

用來定義資料庫、資料表、檢視表、索引、預存程序、觸發程序、函數等資料庫物件
可以用來建立、更新、刪除 table, schema, domin, index, view
常見的指令有：
- create：建立資料庫的物件
- alter：變更資料庫的物件
- drop：刪除資料庫的物件

mysql數據操作語言DML（Data Manipulation Language）

用來處理資料表裡的資料
常見的指令有：
- insert：新增資料到資料表中
- update：更改資料表中的資料
- delete：刪除資料表中的資料

mysql數據控制語言DCL（Data Control Language）

用來控制資料表、檢視表之存取權限，提供資料庫的安全性
常見的指令有：
- grant：賦予使用者使用權限
- revoke：取消使用者的使用權限
- commit：完成教育作業
- rollback：交易作業異常，將已變動的資料回復到交易開始的狀態

mysql數據查詢語言DQL（Data Query language）

負責進行資料查詢，不會對資料本身進行修改的語句
用來查詢資料表中的資料
指令只有一個：
- select：選取資料庫中的資料
各類輔助指令：select, from, where, group by, order by

mysql數據事務語言DTL（Data Transaction language）

事務：講一系列的sql語句當作“一句”來執行的一種機制，該系列語句要麼全部執行成功，要麼一個耶不執行。
事務特點：
- 原子性（要麼全部執行成功，要麼一個都不執行）
- 一致性（數據保持邏輯上的合理性）
- 隔離性（多個事務同時併發執行，每個事務獨立執行）
- 持久性（硬盤數據的更改）
事務模式：在cmd命令行模式中，是否開啟了“一條語句就是一個事務”這個功能
- 默認情況下，這個模式時開啟的，稱為自動提交模式；
- 關閉後，就是“非自動提交模式”——需人工提交（在執行sql語句後，需要人為提交，即執行語句commit;）
- 關閉該模式：set autocommit=0;

事務執行的基本流程：（針對增刪改語句）

1. 開啟一個事務：`starttransaction; //也可以寫成begin;
1. 執行多條增刪改語句; //也就是希望這多條語句要作為一個“不可分割”的整體取執行的任務

判斷這些語句執行的結果情況；

if (no error) {
    commit;     // 提交事務，此時就是一次性完成;
}else {
    rollback;   // 回滾事務，不執行
}

具體分為兩種情況：

在cmd中，執行語句沒有錯誤，直接commit，有錯誤，執行rollback;

在php中：

$link = mysql_connect("localhost", "root", "000");
Mysql_query("set names utf8");
Mysql_query("use php39");
$sql = "start transaction;";
Mysql_query($sql);

$sql1 = "insert into tab_int(f1, f2, f3)values(15, 25, 35);";
$result1 = Mysql_query($sql1);
$sql2 = "insert into tab_int(f1, f2, f3)values(16, 26, 36);";
$result2 = Mysql_query($sql2);

if($result1 && $result2) {
    mysql_query("commit;");
    echo "事務執行成功！所有任務都已完成";
}else {
    mysql_query("rollback;");
    echo "事務執行失敗！數據沒有被修改;";
}

數據庫的庫操作

新增數據庫

基本語法

create database 數據庫名稱[庫選項]；
// 庫選項：用來約束數據庫，分兩個選項
    // 字符集設定：charset/characterset 具體字符集（數據存儲的編碼格式）；常用字符集GBK和UTF8
    // 校對集設定：collate 具體校對集（數據比較的規則）
create database mydatabase charset utf8;

查看數據庫

1	show databases; // 查看所有數據庫

show databases like 'pattern';
//--pattern是匹配模式  %：表示匹配多個字符  _：表示匹配單個字符
show databases like 'mydatabase\_%' // 查看以mydatabases_開始的數據庫：_需要被轉義
show databases like 'mydatabase_%'  // 相當於mydatabases%

查看數據庫的創建語句

1	show creat database mydatabase;

更新數據庫

數據庫名字不可以修改

數據庫的修改僅限庫選項：字符集和校對集（校對集依賴字符集）

1
2
3

alter database 數據庫名稱 [庫選項];
charset/character set [字符集];    // 也可以寫作 charset/character set = [字符集]
collate [校對集];    // 也可以寫作 collate = [校對集]

刪除數據庫

所有的操作中：刪除是最簡單

1 2	drop database 數據庫名字; // 注意，數據的刪除是不可逆的，所以在執行數據庫刪除的時候，先進行備份操作

數據庫的表操作

表與子段是密不可分的

新建數據表

創建數據表有兩種方法：

// 方法一
create table [if not exists] 數據庫名.表名(
    子段名字 數據類型，
    子段名字 數據類型   // 最後一行不需要逗號
)[表選項];
// if not esists: 如果表名不存在，那麼就創建，否則不執行創建代碼：檢查功能
// 表選項：控制表的表現
    // 字符集：charset/character set 具體字符集; // 保証表中數據存儲的字符集
    // 校對集：collate 具體校對集;
    // 存儲引擎：engine 具體的存儲引擎(innodb和myisam)

.eg

create table if not exists mydatabase.student(
name varchar(10),
gender varchar(10),
number varchar(10),
age int
)charset utf8;

// 方法二
use mydatabase; // 進入數據庫環境: use 數據庫名字
create table [if not exists]  表名(
    子段名字 數據類型，
    子段名字 數據類型
)[表選項];

.eg

use mydatabase
create table if not exists student(
    name varchar(10),
    gender varchar(10),
    number varchar(10),
    age int
)charset utf8;

查看數據表

數據庫能查看的方式，表都可以查看
- 查看所有表：show tables;
- 查看部分表：模糊匹配；show tables like 'pattern';
  1
  show tables like '%s';
- 查看表創建語句：show creat table mytable;
  1
  2
  show create table mytable\g; // \g === ;
  show create table mytable\G; // \G：講查到的結構旋轉90度變成縱向
- 查看表結構：查看表中的字段信息desc/describe/show columns from 表名;
  - 列類型：子段類型；數據類型
  - 列屬性：是否允許為NULL
  - 索引：索引類型（PRI主鍵），UNI唯一鍵等
  - 列屬性：默認值，大部分字段默認為NULL
  - 列屬性：擴充（額外的），描述不下了
  修改數據表
- 表本身存在，還包含子段：表的修改分為兩個部分；修改表本身和修改字段
- 修改表本身：
- 表本身可以修改：表名和表選項
  - 修改表名：rename table 老表名 to 新表名;
    1
    2
    # 重命名表：student表 -> my_student（取數據庫名字前兩個字母）
    rename table student to my_student;
  - 修改表選項：字符集，校對集和存儲引擎
    1
    2
    # 修改表選項：字符集
    alter table my_student charset = GBK;
- 修改子段
- 字段操作很多：新增，修改，重命名，刪除
- 新增字段：alter table 表名 add [column] 子段名數據類型 [列屬性][位置];
  - 位置：字段名可以存放表中的任意位置
    - First : 第一個位置
    - After : 在哪個字段之後；after 字段名；默認的是在最後一個字段之後
      1
      2
      3
      4
      # 給學生表新增一個ID放到第一個位置
      alter table my_student
      add column id int
      first; -- mysql會自動尋找分號；（語句結束符）
- 修改字段：修改通常是修改屬性或者數據類型；alter table 表名 modify 字段名 [屬性][位置];
  1
  2
  3
  # 將學生表中的number學號字段變成固定長度，且放到第二位(id之後)
  alter table my_student
  modify number char(10) after id;
- 重命名字段：alter table 表名 change 旧字段新字段名數據類型 [屬性][位置];
  1
  2
  3
  # 修改學生表中的name字段為fullname
  alter table my_student
  change name fullname varchar(10);
- 刪除字段：alter table 表名 drop 字段名;
  1
  2
  # 刪除學生表中的年齡字段（age）
  alter table my_student drop age;
- 小心：如果表中已經存在數據，那麼刪除字段會清空該字段的所有數據(不可逆)

刪除數據表

drop table 表名1, 表名2...; –可以一次性刪除多張表

數據操作

新增數據

兩種方案
- 方案一：給全表字段插入數據，不需要制定字段列表：要求數據的值出現的順序必須與表中設計的字段出現的順序一致；凡是非數值數據，都需要使用引號（建議單引號）包裹
- insert into 表名 values(值列表)[, (值列表)]; – 可以一次性插入多條紀錄
  1
  2
  3
  # 插入數據
  insert into my_student values(1, 'it20140001', 'Vincent', 'male'),
  (2, 'it20140002', 'Eli', 'male');
- 方案二：給部分字段插入數據，需要選定字段列表：字段列表出現的順序與字段的順序無關；但是值列表的順序與選定的字段的順序一致
- insert into 表名 (字段列表) values(值列表)[, (值列表)];
  1
  2
  3
  4
  # 插入數據：制定字段列表
  insert into my_student(number, gender, fullname, id) values
  ('it20140004', 'male', 'Jim', 4),
  ('it20140005', 'female', 'Lin', 5),

查看數據

select */字段列表 from 表名[where 條件];
查看所有數據
1
select * from my_student;

查看指定之段，指定條件的數據

1	select id, number, gender, name from my_student where id = 1; -- 查看滿足ID為1的學生信息

更新數據

update 表名 set 字段 = 值 [where條件]; – 建議都有where，要不然是更新全部
1
update my_student set age = '27' where fullname = 'Jim';
更新不一定會成功：如果沒有真正要更新的數據（是否對數據有影響）

刪除數據（謹慎操作）

delete from 表名 [where條件];

1	delete from my_student where fullname = 'Jim';

中文數據問題

中文數據問題本質是字符集問題

計算機只識別二進制：人類更多是識別字符：需要有個二進制與字符的對應關係（字符集）

1
2
3

# 錯誤示範
insert into my_student values(7, 'it20140007','張越', '男');
-- 客戶端向服務器插入中文數據：沒有成功

原因：\xD5\xC5\xD4\xBD 代表的是”張越”在當前編碼(字符集)下對應的二進制編碼轉換成的十六進制：兩個漢字 => 四個字節(GBK)
報錯：服務器沒有識別對應的四個字節：服務器認為數據是UTF8，一個漢字有三個字節：讀取三個字節轉化成漢字（失敗），剩餘的再讀三個字節（不夠）；最終失敗

所有的數據庫服務器認為（表現）的一些特性都是通過服務器端變量來保存：服務器先讀取自己的變量，看看應該怎麼表現

# 查看服務器到底識別那些字符集
show character set;
-- 基本上服務器什麼字符集都支持

# 查看服務器默認的對外處理的字符集
show variables like 'character_set%';

問題根源：客戶端數據只能是GBK，而服務器認為是UTF8；產生矛盾

解決方案：改變服務器，默認的接收字符集為GBK；

1 2	# 修改服務器認為的客戶端數據的字符集gbk set character_set_client = gbk;

查看數據效果：依然是亂碼
原因：數據來源是服務器，解析數據是客戶端（客戶端只識別GBK，只會兩個字節一個漢字）；但是事實服務器給的數據卻是UTF8，三個字節一個漢字：亂碼

解決辦法：修改服務器給客戶端的數據字符集為GBK

1 2	# 修改服務器給定數據的字符集為GBK set character_set_results = gbk;

顯示正常，插入中文成功
注意：set 變量 = 值 修改只是會話級別（當前客戶端，檔次連接有效，關閉失效）
一般設置服務器對客戶端的字符集的認識，可以使用快捷方式：set name 字符集;
- set names gbk; =====> character_set_client, character_set_results, character_set_connection;
- connection 連接層：是字符集轉變的中間者，如果統一了效率更高，不統一也沒關係
  1
  2
  # 快捷設置字符集
  set names gbk;

校對集

校對集：數據比較的方式
校對集有三種格式：
- _bin: binary,二進制比較，取出二進制位，一位一位的比較，區分大小寫
- _cs: case sensitive,大小寫敏感，區分大小寫
- _ci: case insensitice,大小寫不敏感，不區分大小寫
  1
  2
  # 查看所有校對集
  show collation;

校對集應用

只有當數據產生比較的時候，校對集才會生效

對比：使用utf8個 _bin 和 _ci 來驗證不同的校對集的效果

# 1.創建表使用不同的校對集
create table my_collate_bin(
    name char(1)
)charset utf8 collate utf8_bin;

create table my_collate_ci(
    name char(1)
)charset utf8 collate utf8_general_ci;

# 2.插入數據
insert into my_collate_bin values('a'), ('A'), ('b'), ('B');

insert into my_collate_ci values('a'), ('A'), ('b'), ('B');

# 3.比較：根據某個字段進行排序：order by 字段名[asc|desc]; asc 升序， desc 降序，默認是升序
select * from my_collate_bin order by name;
select * from my_collate_ci order by name;

校對集必須在沒有數據之前聲明好，如果有了數據，那麼再進行校對集修改將無效

WEB亂碼問題

動態網站由三部分構成：瀏覽器，apacche服務器(PHP)，數據庫服務器，三個部分都有自己的字符集（特別對中文），數據需要再三個部分之間來回傳遞，很容易產生亂碼
如何解決亂碼問題：統一編碼（三碼合一）
但是事實上不可能：瀏覽器是用戶管理（根本不可能控制）
但是必須要解決這些問題：主要靠PHP來做

小練習（站點統計）

1.將用戶的訪問信息紀錄到文件中：獨佔一行，紀錄IP

// 獲取用戶信息
$ip = $_SERVER['REMOTE_ADDR'];

// 寫入文件（追加）
file_put_content('record.txt', $ip."\r\n", FILE_APPEND);

2.所有的數據都是通過文件中紀錄的內容來計算出來：讀取整個文件，一行代表訪問一次：使用file函數讀取
1
2
// 讀取數據：以行為單位
$info = file('record.txt');

3.總的訪問次數出來：數據的總行數，就是讀取數據數組的總紀錄數

// 求出網站的總訪問量
$visits = count($info);

// 求出當前用戶（IP）出現的次數
$ip_visits = 0;
$unique_ips = array();
foreach($info as $each_ip) {
    // 統計當前數組中擁有的獨立IP數
    if (!in_array($each_ip, $unique_ips)) {
        // 將當前新的用戶加入到獨立ip數組中
        $unique_ips[] = $each_ip;
        
        // 判斷當前新家的IP($each_ip)是否是當前用戶的IP
        if ($ip == trim($each_ip)) $user_visit = count($unique_ips);
    }
    
    // 比較：從文件中讀出來的是一行，需要去除換行符才能比較
    if (trim($each_ip) == $ip) $ip_visits++;
}
// 統計$unique_ips中的元素個數：就是獨立IP數：總用戶
$users = count($unique_ips);


// 輸出
echo "歡迎訪問‘小白文 · Vincent‘的部落格，你是第{$user_visit}個用戶，當前網站一共有{$users}個用戶，網頁義工被訪問了{$visits}次，你當前是第{$ip_visits}次訪問！";