數(shù)據(jù)庫(kù)技術(shù)的基礎(chǔ)

什么是數(shù)據(jù)庫(kù)技術(shù)

數(shù)據(jù)庫(kù)技術(shù)就是數(shù)據(jù)管理的技術(shù)搓萧，是計(jì)算機(jī)科學(xué)與計(jì)算的重要分支杂数，是信息系統(tǒng)的核心和基礎(chǔ)宛畦；

基本概念的認(rèn)識(shí)

理清概念，才能清晰的前行揍移；

• 數(shù)據(jù)

存儲(chǔ)在存儲(chǔ)介質(zhì)上的物理符號(hào)次和，包括數(shù)字、字符那伐、圖形踏施、圖像、audio和vedio等數(shù)據(jù)類型罕邀；

• 信息

經(jīng)過加工處理后的數(shù)據(jù)畅形；

單純的數(shù)據(jù)是無價(jià)值的，經(jīng)過處理后符合特定目的的數(shù)據(jù)诉探，即信息才是有價(jià)值的日熬；

數(shù)據(jù)承載信息，信息表現(xiàn)數(shù)據(jù)內(nèi)涵肾胯；

• 數(shù)據(jù)處理

把數(shù)據(jù)變成信息的過程竖席，即

對(duì)原始數(shù)據(jù)進(jìn)行收集、整理敬肚、加工毕荐、存儲(chǔ)、傳輸和檢索等一系列操作過程帘皿；

• 數(shù)據(jù)庫(kù)

數(shù)據(jù)庫(kù)是存儲(chǔ)在計(jì)算機(jī)的有組織东跪、可共享的數(shù)據(jù)集合畸陡；

數(shù)據(jù)庫(kù)按一定的數(shù)據(jù)模型組織鹰溜、描述和存儲(chǔ)；

• 數(shù)據(jù)庫(kù)管理系統(tǒng)

對(duì)數(shù)據(jù)庫(kù)進(jìn)行管理的系統(tǒng)軟件丁恭，提供如下主要功能：

- 數(shù)據(jù)定義功能

DBMS提供數(shù)據(jù)定義語(yǔ)言DDL,對(duì)數(shù)據(jù)庫(kù)中相關(guān)內(nèi)容進(jìn)行定義曹动；

- 數(shù)據(jù)操縱功能

DBMS提供數(shù)據(jù)操作語(yǔ)言DML,對(duì)象數(shù)據(jù)庫(kù)進(jìn)行基本操作，即數(shù)據(jù)的增刪改查牲览；

- 數(shù)據(jù)庫(kù)運(yùn)行控制功能

是DBMS的核心功能墓陈，包括：
    - 并發(fā)控制，多個(gè)用戶同時(shí)使用某些數(shù)據(jù)第献；
    - 安全性檢查
    - 數(shù)據(jù)完整性條件檢查
    - 數(shù)據(jù)庫(kù)內(nèi)部維護(hù)

- 數(shù)據(jù)庫(kù)的建立和維護(hù)功能
    - 數(shù)據(jù)庫(kù)的建立贡必，是指數(shù)據(jù)的載入、轉(zhuǎn)儲(chǔ)庸毫、重組織和數(shù)據(jù)庫(kù)的恢復(fù)功能仔拟；
    - 數(shù)據(jù)庫(kù)的維護(hù)，是指數(shù)據(jù)庫(kù)結(jié)構(gòu)的修改飒赃、變更集擴(kuò)充功能利花；

【注】

作為一個(gè)前端攻城獅科侈，前期主要在數(shù)據(jù)庫(kù)的建立與維護(hù)、數(shù)據(jù)的定義炒事、數(shù)據(jù)操縱等功能理解臀栈；

• 數(shù)據(jù)庫(kù)系統(tǒng)

  擁有數(shù)據(jù)庫(kù)并利用數(shù)據(jù)庫(kù)技術(shù)進(jìn)行數(shù)據(jù)管理的計(jì)算機(jī)系統(tǒng)；

  數(shù)據(jù)庫(kù)系統(tǒng)的體系包括：

    - 計(jì)算機(jī)硬件設(shè)備
    - 數(shù)據(jù)庫(kù)及相關(guān)軟件系統(tǒng)
    - 開發(fā)及管理人員
  

數(shù)據(jù)管理的發(fā)展過程

數(shù)據(jù)管理發(fā)展大致經(jīng)歷人工管理階段挠乳、文件系統(tǒng)管理階段和數(shù)據(jù)庫(kù)系統(tǒng)管理階段权薯；

• 人工管理階段

20世紀(jì)50年代：

硬件方面存儲(chǔ)介質(zhì)只有磁帶、紙帶等睡扬，沒有磁盤崭闲，軟件方面沒有操作系統(tǒng)和數(shù)據(jù)管理軟件；

此時(shí)威蕉，數(shù)據(jù)和程序集合在一起刁俭，每個(gè)程序有自己的數(shù)據(jù)集，彼此獨(dú)立韧涨，數(shù)據(jù)無法共享牍戚；

• 文件系統(tǒng)管理階段

20世紀(jì)60年代：

硬件方面出現(xiàn)了磁帶、磁盤等大容量存儲(chǔ)介質(zhì)虑粥，軟件方面有操作系統(tǒng)和用文件系統(tǒng)處理數(shù)據(jù)的技術(shù)如孝；

文件系統(tǒng)是專門處理數(shù)據(jù)的系統(tǒng)軟件，系統(tǒng)中數(shù)據(jù)以文件形式存儲(chǔ)在外存娩贷，應(yīng)用程序通過文件系統(tǒng)對(duì)文件數(shù)據(jù)進(jìn)行存取第晰，文件系統(tǒng)是應(yīng)用程序和數(shù)據(jù)之間的一個(gè)接口，實(shí)現(xiàn)對(duì)數(shù)據(jù)的增刪改查功能彬祖；

這段期間茁瘦，數(shù)據(jù)管理有如下特點(diǎn)：

- 數(shù)據(jù)以文件形式長(zhǎng)期存儲(chǔ)在外存，文件系統(tǒng)提供接口對(duì)文件進(jìn)行操作储笑；

- 程序和數(shù)據(jù)之間分開甜熔，數(shù)據(jù)的物理結(jié)構(gòu)和邏輯結(jié)構(gòu)有所區(qū)別；

- 但文件結(jié)構(gòu)的設(shè)計(jì)仍基于特定應(yīng)用突倍，程序與數(shù)據(jù)結(jié)構(gòu)之間的依賴關(guān)系未根本改變腔稀；

- 存在數(shù)據(jù)冗余，同樣的數(shù)據(jù)可能存在多個(gè)文件中羽历；

• 數(shù)據(jù)庫(kù)系統(tǒng)管理階段

硬件方面出現(xiàn)大容量磁盤焊虏；

軟件方面出現(xiàn)為多用戶、多應(yīng)用程序共享數(shù)據(jù)的數(shù)據(jù)庫(kù)管理技術(shù)秕磷；

此時(shí)的數(shù)據(jù)管理有如下特點(diǎn)：
- 數(shù)據(jù)不在針對(duì)特定應(yīng)用诵闭，是面向全組織;
- 具有整體的結(jié)構(gòu)性、共享性高和冗余度小
- 實(shí)現(xiàn)程序與數(shù)據(jù)的解耦和對(duì)數(shù)據(jù)的統(tǒng)一控制跳夭；

database-history

概念模型和數(shù)據(jù)模型

概念模型： 對(duì)信息世界的管理對(duì)象涂圆、屬性和聯(lián)系等信息的抽象和形式化描述们镜，概念模型不依賴與DBMS；

數(shù)據(jù)模型：
按計(jì)算機(jī)系統(tǒng)的觀點(diǎn)對(duì)數(shù)據(jù)建模润歉，是數(shù)據(jù)庫(kù)管理系統(tǒng)的實(shí)現(xiàn)模狭；

• 概念模型包括以下概念：

  • 實(shí)體和實(shí)體集

    • 實(shí)體是客觀存在并且相互區(qū)別的事物，包括具體的事物和事物之間的聯(lián)系踩衩；
      如嚼鹉，每一個(gè)學(xué)生以及班級(jí)中學(xué)生與學(xué)生的關(guān)系是一個(gè)實(shí)體

    • 實(shí)體集是具有相同屬性的實(shí)體的集合，如若干學(xué)生組成的學(xué)生實(shí)體集驱富；

  • 屬性和屬性值

    • 屬性是描述實(shí)體的特性锚赤，如學(xué)生的名字和學(xué)號(hào)；

    • 屬性值是屬性的具體指褐鸥，如學(xué)生的學(xué)號(hào)為1线脚；

  • 實(shí)體集間的聯(lián)系
    實(shí)體級(jí)之間的對(duì)應(yīng)關(guān)系成為聯(lián)系，根據(jù)一個(gè)實(shí)體集中每個(gè)實(shí)體與另一個(gè)實(shí)體集中的實(shí)體可能出現(xiàn)的數(shù)目對(duì)應(yīng)關(guān)系叫榕，兩個(gè)實(shí)體集之間的關(guān)系可分為3個(gè)類型：

    • 1對(duì)1聯(lián)系
      實(shí)體集A與實(shí)體集B的1對(duì)1聯(lián)系浑侥，記做1：1，實(shí)體集A中每個(gè)實(shí)體在實(shí)體集B中至多有一個(gè)實(shí)體與之聯(lián)系晰绎；
      如寓落，班級(jí)實(shí)體集和班長(zhǎng)實(shí)體集，班級(jí)實(shí)體集中的每一班在班長(zhǎng)實(shí)體集中至多有一個(gè)班長(zhǎng)實(shí)體與之聯(lián)系荞下；

    • 1對(duì)n聯(lián)系
      實(shí)體集A中的每一個(gè)實(shí)體在實(shí)體集B中與之對(duì)應(yīng)的實(shí)體至少1個(gè)伶选，記做1：n；
      如尖昏，學(xué)校實(shí)體集中在學(xué)生實(shí)體集中的學(xué)生實(shí)體不止一個(gè)仰税，但每一個(gè)學(xué)生只能屬于一個(gè)學(xué)校；

    • n對(duì)n聯(lián)系
      實(shí)體集A中每個(gè)實(shí)體在實(shí)體集B中對(duì)于對(duì)象的實(shí)體至少1個(gè)会宪，反之亦然肖卧，記做n：n;
      如蚯窥，教師實(shí)體集中每個(gè)教師對(duì)應(yīng)課程實(shí)體集中的多門課程實(shí)體掸鹅，課程實(shí)體集中的每個(gè)課程實(shí)體也可對(duì)應(yīng)教師實(shí)體集中的多個(gè)教師；

    • E-R模型
      E-R模型是用來描述現(xiàn)實(shí)世界的概念模型

      • 實(shí)體用矩形表示
      • 屬性用橢圓表示
      • 聯(lián)系用菱形表示實(shí)體集之間的聯(lián)系
      • 連線用線段表示實(shí)體及其屬性之間的聯(lián)系

concept-model

1對(duì)1聯(lián)系可看做特殊的1對(duì)n聯(lián)系拦赠，1對(duì)n聯(lián)系可以看做特殊的n對(duì)n聯(lián)系

• 數(shù)據(jù)模型

  數(shù)據(jù)模型由模型結(jié)構(gòu)巍沙、數(shù)據(jù)操作和完整性約束組成，下面主要講解模型結(jié)構(gòu)和數(shù)據(jù)操作荷鼠；

  • 模型結(jié)構(gòu)
    模型結(jié)構(gòu)經(jīng)歷層次模型句携、網(wǎng)狀模型、關(guān)系模型和面向?qū)ο竽Ｐ驮世郑@里重點(diǎn)講解關(guān)系模型矮嫉，稍微提及面向?qū)ο竽Ｐ停?/p>

    關(guān)系模型是數(shù)據(jù)模型中最紅要的額模型結(jié)構(gòu)削咆，應(yīng)用最為廣泛，常見的數(shù)據(jù)庫(kù)管理系統(tǒng)MySQL蠢笋、Oracle拨齐、SQL Server都是采用關(guān)系模型對(duì)數(shù)據(jù)進(jìn)行組織；

    關(guān)系模型包括以下概念：

    • 關(guān)系
      就是一個(gè)二維表代表的數(shù)據(jù)集合昨寞，講一個(gè)實(shí)體集放入一個(gè)二維表瞻惋；
      理解：關(guān)系型數(shù)據(jù)庫(kù)是以“關(guān)系”作為數(shù)據(jù)的基本單元，所以一個(gè)關(guān)系就是一個(gè)數(shù)據(jù)單元援岩；

    • 關(guān)系結(jié)構(gòu)
      關(guān)系結(jié)構(gòu)描述關(guān)系中數(shù)據(jù)的意義歼狼，如student(學(xué)號(hào)，姓名享怀，入學(xué)成績(jī))

    • 記錄
      二維表中的一行成為一條記錄或元組

    • 屬性
      二維表中的每一列是的名稱羽峰，每一列還有許多屬性值，組成屬性值集

    • 關(guān)鍵字
      唯一表示記錄的屬性添瓷，成為關(guān)鍵字限寞，關(guān)鍵字包含一個(gè)以上時(shí)要選定主鍵；

    【面向?qū)ο竽Ｐ汀?br> 面向?qū)ο竽Ｐ蛯F(xiàn)實(shí)世界的數(shù)據(jù)抽象為對(duì)象和類仰坦，代表的NoSQL數(shù)據(jù)庫(kù)有MongoDB
    - 對(duì)象
    現(xiàn)實(shí)世界中實(shí)體的模型化履植，每個(gè)對(duì)象有唯一標(biāo)識(shí)符、狀態(tài)和行為悄晃；
    - 狀態(tài)是對(duì)象屬性值得集合
    - 行為是對(duì)對(duì)象狀態(tài)的操作方法集

      - 類
      共享同一屬性集合方法集的所有對(duì)象構(gòu)成一個(gè)類
    

  數(shù)據(jù)操作
  數(shù)據(jù)操作至少支持選擇玫霎、投影和聯(lián)接三種基本關(guān)系運(yùn)算

    - 選擇
        在關(guān)系中選擇滿足條件的元組，相當(dāng)于查找一行的記錄
    - 投影
        在關(guān)系中選擇滿足條件的屬性列妈橄，相當(dāng)于查找一列的屬性
    - 聯(lián)接
        將兩個(gè)關(guān)系的屬性拼接成一個(gè)新的關(guān)系
  

SQL基本操作

SQL庶近，指結(jié)構(gòu)化查詢語(yǔ)言，全稱是 Structured Query Language是用于處理數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)計(jì)算機(jī)語(yǔ)言眷蚓；

這里使用SQL去訪問處理MySQL數(shù)據(jù)庫(kù)鼻种，需要事先安裝MySQL

• MySQL的安裝

具體教程網(wǎng)上有，這里小羊就沒贅述了沙热；
安裝完后叉钥，輸入：

$ mysql -V
mysql  Ver 14.14 Distrib 5.7.17, for osx10.12 (x86_64) using  EditLine wrapper

查看是否安裝成功；

• 開啟數(shù)據(jù)庫(kù)服務(wù)

$ mysql.server start

• 進(jìn)入數(shù)據(jù)庫(kù)

$ mysql -u root -p
# 輸入密碼
Enter password:

• 退出mysql

mysql> exit
Bye

• 顯示數(shù)據(jù)庫(kù)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| test               |
+--------------------+
1 rows in set (0.00 sec)

• 創(chuàng)建數(shù)據(jù)庫(kù)

mysql> create database demo;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| demo               |
| test               |
+--------------------+
2 rows in set (0.00 sec)

• 選擇數(shù)據(jù)庫(kù)

mysql> use demo;
Database changed

• 創(chuàng)建數(shù)據(jù)庫(kù)的表

peole(...)代表前面的關(guān)系模型中的關(guān)系結(jié)構(gòu)篙贸，定義一張表的結(jié)構(gòu)投队；

括號(hào)的參數(shù)表示屬性名，屬性名后面是屬性值得類型描述爵川；

mysql> create table people(id int, lastname varchar(20), firstname varchar(20), age int);
Query OK, 0 rows affected (0.03 sec)

• 顯示所有數(shù)據(jù)表

mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| people          |
+----------------+
1 row in set (0.00 sec)

• 插入記錄（元祖）

mysql> insert into people values (1, 'teren', 'yeung', 18);
Query OK, 1 row affected (0.01 sec)

• 查詢數(shù)據(jù)表

查詢所有記錄

mysql> select * from people;
+------+----------+-----------+------+
| id   | lastname | firstname | age  |
+------+----------+-----------+------+
|    1 | teren    | yeung     |   18 |
+------+----------+-----------+------+
1 row in set (0.00 sec)

查詢特定屬性的記錄

mysql> select lastname, age from people;
+----------+------+
| lastname | age  |
+----------+------+
| teren    |   18 |
+----------+------+
1 row in set (0.00 sec)

查詢唯一記錄

mysql> select * from people;
+------+-------------+-----------+------+
| id   | lastname    | firstname | age  |
+------+-------------+-----------+------+
|    1 | teren       | yeung     |   18 |
|    2 | totora      | cat       |    2 |
|    2 | hitachi     | dog       |    5 |
|    2 | hello_kitty | cat       |    5 |
+------+-------------+-----------+------+
4 rows in set (0.00 sec)

mysql> select distinct firstname from people;
+-----------+
| firstname |
+-----------+
| yeung     |
| cat       |
| dog       |
+-----------+
3 rows in set (0.00 sec)

條件查詢

mysql> insert into people values (3,"kobe", "bryant", 18);
Query OK, 1 row affected (0.00 sec)

mysql> select * from people where lastname="kobe";
+------+----------+-----------+------+
| id   | lastname | firstname | age  |
+------+----------+-----------+------+
|    3 | kobe     | bryant    |   18 |
+------+----------+-----------+------+
1 row in set (0.00 sec)

mysql> select * from people where age<18;
+------+-------------+-----------+------+
| id   | lastname    | firstname | age  |
+------+-------------+-----------+------+
|    2 | totora      | cat       |    2 |
|    2 | hitachi     | dog       |    5 |
|    2 | hello_kitty | cat       |    5 |
+------+-------------+-----------+------+
3 rows in set (0.00 sec)

mysql> select * from people;
+------+-------------+-----------+------+
| id   | lastname    | firstname | age  |
+------+-------------+-----------+------+
|    1 | teren       | yeung     |   18 |
|    2 | totora      | cat       |    2 |
|    2 | hitachi     | dog       |    5 |
|    2 | hello_kitty | cat       |    5 |
|    3 | kobe        | bryant    |   18 |
+------+-------------+-----------+------+
5 rows in set (0.00 sec)

mysql> select * from people where age between 2 and  4;
+------+----------+-----------+------+
| id   | lastname | firstname | age  |
+------+----------+-----------+------+
|    2 | totora   | cat       |    2 |
+------+----------+-----------+------+
1 row in set (0.00 sec)

LIKE用于匹配符合特定模式的數(shù)據(jù)敷鸦；

mysql> select * from people where lastname like 'teren';
+------+----------+-----------+------+
| id   | lastname | firstname | age  |
+------+----------+-----------+------+
|    1 | teren    | yeung     |   18 |
+------+----------+-----------+------+
1 row in set (0.00 sec)

mysql> select * from people where lastname not like 'teren';
+------+-------------+-----------+------+
| id   | lastname    | firstname | age  |
+------+-------------+-----------+------+
|    2 | totora      | cat       |    2 |
|    2 | hitachi     | dog       |    5 |
|    2 | hello_kitty | cat       |    5 |
|    3 | kobe        | bryant    |   18 |
+------+-------------+-----------+------+
4 rows in set (0.00 sec)

mysql> select * from people where lastname like '%ren';
+------+----------+-----------+------+
| id   | lastname | firstname | age  |
+------+----------+-----------+------+
|    1 | teren    | yeung     |   18 |
+------+----------+-----------+------+
1 row in set (0.00 sec)

IN用于符合查詢符合IN集合后的數(shù)據(jù)

mysql> select * from people where lastname in ('hitachi', 'totora');
+------+----------+-----------+------+
| id   | lastname | firstname | age  |
+------+----------+-----------+------+
|    2 | totora   | cat       |    2 |
|    2 | hitachi  | dog       |    5 |
+------+----------+-----------+------+
2 rows in set (0.01 sec)

通配符

mysql> select * from people where lastname like '%to%';
+------+----------+-----------+------+
| id   | lastname | firstname | age  |
+------+----------+-----------+------+
|    2 | totora   | cat       |    2 |
+------+----------+-----------+------+
1 row in set (0.00 sec)

mysql> select * from people where lastname like '_o%';
+------+----------+-----------+------+
| id   | lastname | firstname | age  |
+------+----------+-----------+------+
|    2 | totora   | cat       |    2 |
|    3 | kobe     | bryant    |   18 |
+------+----------+-----------+------+
2 rows in set (0.00 sec)

mysql> select * from people where lastname regexp '^[t]';
+------+----------+-----------+------+
| id   | lastname | firstname | age  |
+------+----------+-----------+------+
|    1 | teren    | yeung     |   18 |
|    2 | totora   | cat       |    2 |
+------+----------+-----------+------+
2 rows in set (0.00 sec)

AND && OR 運(yùn)算符

mysql> select * from people where id=2 and age !=2;
+------+-------------+-----------+------+
| id   | lastname    | firstname | age  |
+------+-------------+-----------+------+
|    2 | hitachi     | dog       |    5 |
|    2 | hello_kitty | cat       |    5 |
+------+-------------+-----------+------+
2 rows in set (0.00 sec)

mysql> select * from people where id=1 or age =2;
+------+----------+-----------+------+
| id   | lastname | firstname | age  |
+------+----------+-----------+------+
|    1 | teren    | yeung     |   18 |
|    2 | totora   | cat       |    2 |
+------+----------+-----------+------+
2 rows in set (0.00 sec)

ODBER BY關(guān)鍵字
對(duì)結(jié)果進(jìn)行排序

mysql> select * from people order by lastname;
+------+-------------+-----------+------+
| id   | lastname    | firstname | age  |
+------+-------------+-----------+------+
|    2 | hello_kitty | cat       |    5 |
|    2 | hitachi     | dog       |    5 |
|    3 | kobe        | bryant    |   18 |
|    1 | teren       | yeung     |   18 |
|    2 | totora      | cat       |    2 |
+------+-------------+-----------+------+
5 rows in set (0.00 sec)

mysql> select * from people order by age desc, firstname;
+------+-------------+-----------+------+
| id   | lastname    | firstname | age  |
+------+-------------+-----------+------+
|    3 | kobe        | bryant    |   18 |
|    1 | teren       | yeung     |   18 |
|    2 | hello_kitty | cat       |    5 |
|    2 | hitachi     | dog       |    5 |
|    2 | totora      | cat       |    2 |
+------+-------------+-----------+------+
5 rows in set (0.00 sec)

SELEC TOP
返回指定數(shù)量記錄

mysql> select * from people limit 3;
+------+----------+-----------+------+
| id   | lastname | firstname | age  |
+------+----------+-----------+------+
|    1 | teren    | yeung     |   20 |
|    2 | totora   | cat       |    2 |
|    2 | hitachi  | dog       |    5 |
+------+----------+-----------+------+
3 rows in set (0.00 sec)

別名查詢
為查詢結(jié)構(gòu)的屬性名或是表指定別名

mysql> select id as object_id, lastname as lname from people;
+-----------+-------------+
| object_id | lname       |
+-----------+-------------+
|         1 | teren       |
|         2 | totora      |
|         2 | hitachi     |
|         2 | hello_kitty |
+-----------+-------------+
4 rows in set (0.00 sec)

mysql> select * from people as c where c.id=2;
+------+-------------+-----------+------+
| id   | lastname    | firstname | age  |
+------+-------------+-----------+------+
|    2 | totora      | cat       |    2 |
|    2 | hitachi     | dog       |    5 |
|    2 | hello_kitty | cat       |    5 |
+------+-------------+-----------+------+
3 rows in set (0.00 sec)

別名的使用場(chǎng)景，在設(shè)計(jì)復(fù)雜的查詢時(shí)，可以簡(jiǎn)化查詢語(yǔ)句扒披；

數(shù)據(jù)更新

mysql> select * from people;
+------+-------------+-----------+------+
| id   | lastname    | firstname | age  |
+------+-------------+-----------+------+
|    1 | teren       | yeung     |   18 |
|    2 | totora      | cat       |    2 |
|    2 | hitachi     | dog       |    5 |
|    2 | hello_kitty | cat       |    5 |
|    3 | kobe        | bryant    |   18 |
+------+-------------+-----------+------+
5 rows in set (0.00 sec)

mysql> update people set age=20 where lastname='teren';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from people;
+------+-------------+-----------+------+
| id   | lastname    | firstname | age  |
+------+-------------+-----------+------+
|    1 | teren       | yeung     |   20 |
|    2 | totora      | cat       |    2 |
|    2 | hitachi     | dog       |    5 |
|    2 | hello_kitty | cat       |    5 |
|    3 | kobe        | bryant    |   18 |
+------+-------------+-----------+------+
5 rows in set (0.00 sec)

添加值依、刪除或修改列

• alter table table_name add column_name type

mysql> alter table book add date date;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from book;
+----+------------+------------------+-------+------+
| id | bookname   | author           | price | date |
+----+------------+------------------+-------+------+
|  1 | MySQL      | Monty Widenius   |    20 | NULL |
|  2 | Python     | Guido van Rossum |    30 | NULL |
|  3 | JavaScript | Brendan Eich     |    30 | NULL |
+----+------------+------------------+-------+------+
3 rows in set (0.00 sec)

mysql> alter table book add remark varchar(20);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from book;
+----+------------+------------------+-------+------+--------+
| id | bookname   | author           | price | date | remark |
+----+------------+------------------+-------+------+--------+
|  1 | MySQL      | Monty Widenius   |    20 | NULL | NULL   |
|  2 | Python     | Guido van Rossum |    30 | NULL | NULL   |
|  3 | JavaScript | Brendan Eich     |    30 | NULL | NULL   |
+----+------------+------------------+-------+------+--------+
3 rows in set (0.00 sec)

• alter table table_name drop column column_name;

mysql> alter table book drop column remark;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from book;
+----+------------+------------------+-------+------+
| id | bookname   | author           | price | date |
+----+------------+------------------+-------+------+
|  1 | MySQL      | Monty Widenius   |    20 | NULL |
|  2 | Python     | Guido van Rossum |    30 | NULL |
|  3 | JavaScript | Brendan Eich     |    30 | NULL |
+----+------------+------------------+-------+------+
3 rows in set (0.00 sec)

• 修改字段的數(shù)據(jù)類型

mysql> alter table book modify column date int;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

刪除特定記錄

mysql> delete from people where lastname='kobe';
Query OK, 1 row affected (0.01 sec)

mysql> select * from people;
+------+-------------+-----------+------+
| id   | lastname    | firstname | age  |
+------+-------------+-----------+------+
|    1 | teren       | yeung     |   20 |
|    2 | totora      | cat       |    2 |
|    2 | hitachi     | dog       |    5 |
|    2 | hello_kitty | cat       |    5 |
+------+-------------+-----------+------+
4 rows in set (0.00 sec)

復(fù)制數(shù)據(jù)表

mysql> create table animals select * from people;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| animals        |
| people         |
+----------------+
2 rows in set (0.00 sec)

mysql> select * from animals;
+------+-------------+-----------+------+
| id   | lastname    | firstname | age  |
+------+-------------+-----------+------+
|    1 | teren       | yeung     |   20 |
|    2 | totora      | cat       |    2 |
|    2 | hitachi     | dog       |    5 |
|    2 | hello_kitty | cat       |    5 |
+------+-------------+-----------+------+
4 rows in set (0.00 sec)

SQL約束用于定義特定字段（屬性）的規(guī)則

• PRIMARY KEY

唯一標(biāo)識(shí)數(shù)據(jù)庫(kù)表中的每條記錄；

具有NOT NULL和UNIQU的約束規(guī)則碟案；

每個(gè)表都有且只有一個(gè)主鍵

mysql> create table book(id int primary key, bookname varchar(30) not null, author varchar(20) unique, price int);

mysql> insert into book values(1, 'MySQL','teren', 20);
Query OK, 1 row affected (0.01 sec)

mysql> insert into book values(1, 'Python','Guido van Rossum', 30);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into book values(2, 'Python','Guido van Rossum', 30);
Query OK, 1 row affected (0.00 sec)

mysql> select * from book;
+----+----------+------------------+-------+
| id | bookname | author           | price |
+----+----------+------------------+-------+
|  1 | MySQL    | Monty Widenius   |    20 |
|  2 | Python   | Guido van Rossum |    30 |
+----+----------+------------------+-------+
2 rows in set (0.00 sec)

當(dāng)表已經(jīng)創(chuàng)建鳞滨，需要后續(xù)進(jìn)行字段約束，可使用 ALTER ADD || ALTER DROP INDEX

mysql> select * from book;
+----+----------+------------------+-------+
| id | bookname | author           | price |
+----+----------+------------------+-------+
|  1 | MySQL    | Monty Widenius   |    20 |
|  2 | Python   | Guido van Rossum |    30 |
+----+----------+------------------+-------+
2 rows in set (0.00 sec)

mysql> alter table book add unique(price);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into book values(3, 'JavaScript', 'Brendan Eich', 30);
ERROR 1062 (23000): Duplicate entry '30' for key 'price'

mysql> alter table book drop index price;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into book values(3, 'JavaScript', 'Brendan Eich', 30);
Query OK, 1 row affected (0.00 sec)

mysql> select * from book;
+----+------------+------------------+-------+
| id | bookname   | author           | price |
+----+------------+------------------+-------+
|  1 | MySQL      | Monty Widenius   |    20 |
|  2 | Python     | Guido van Rossum |    30 |
|  3 | JavaScript | Brendan Eich     |    30 |
+----+------------+------------------+-------+
3 rows in set (0.00 sec)

• FOREIGN KEY

一個(gè)表中的FOREIGN KEY指向另一個(gè)表中的PRIMARY KEY

FOREIGN KEY技能表示表之間的聯(lián)系蟆淀，又能防止這種連接被破壞拯啦；

mysql> create table book_detail(id int,book_id int, field varchar(20),primary key(id), foreign key(book_id) references book(id));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into book_detail values (1,1, 'DBMS');
Query OK, 1 row affected (0.00 sec)

mysql> select * from book_detail;
+----+---------+-------+
| id | book_id | field |
+----+---------+-------+
|  1 |       1 | DBMS  |
+----+---------+-------+
1 row in set (0.00 sec)

刪除表

mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| animals        |
| book           |
| book_detail    |
| people         |
+----------------+
4 rows in set (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| book           |
| book_detail    |
| people         |
+----------------+
3 rows in set (0.00 sec)

清空數(shù)據(jù)庫(kù)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| demo               |
| test               |
+--------------------+
2 rows in set (0.00 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from test;
+------+----------+-----------+---------+
| id   | lastname | firstname | address |
+------+----------+-----------+---------+
|    1 | teren    | yeung     | canton  |
|    2 | kobe     | bryant    | America |
+------+----------+-----------+---------+
2 rows in set (0.00 sec)

mysql> truncate table test;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test;
Empty set (0.00 sec)

刪除數(shù)據(jù)庫(kù)

mysql> drop database test;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| demo               |
+--------------------+
1 rows in set (0.00 sec)

SQL函數(shù)

SQL還有許多內(nèi)建函數(shù)，用于查詢結(jié)果的簡(jiǎn)單計(jì)算熔任，如有需要可以查找相關(guān)資料褒链，這里不詳細(xì)講解；

一般前端工程師只要做到認(rèn)識(shí)數(shù)據(jù)庫(kù)到以上基本操作就行了疑苔，SQL那些查找的數(shù)據(jù)操作一般不用內(nèi)建函數(shù)甫匹，而是采用特定高級(jí)語(yǔ)言的模塊進(jìn)行處理；