1.2.2 數(shù)據(jù)庫的創(chuàng)建

?create database <數(shù)據(jù)庫名稱>

1.2.3 創(chuàng)建數(shù)據(jù)表

?create table<表名>

（<列名1><數(shù)據(jù)類型><該列所需的約束>, ......

<該表的約束1>，<該表的約束2>，......）；

1.2.6 約束設(shè)置

NOT NULL 是非空約束，該列必須輸入數(shù)據(jù)

PRIMARY KEY是主鍵約束，代表該列是唯一值

1.2.7表的刪除與更新

• 刪除表的語法：

DROP TABLE < 表名 > ;

• 刪除 product 表

需要特別注意的是，刪除的表是無法恢復(fù)的，只能重新插入，請(qǐng)執(zhí)行刪除操作時(shí)要特別謹(jǐn)慎。

DROP TABLE product;

• 添加列的 ALTER TABLE 語句

ALTER TABLE < 表名 > ADD COLUMN < 列的定義 >;

• 添加一列可以存儲(chǔ)100位的可變長字符串的 product_name_pinyin 列

ALTER TABLE product ADD COLUMN product_name_pinyin VARCHAR(100);

• 刪除列的 ALTER TABLE 語句

ALTER TABLE < 表名 > DROP COLUMN < 列名 >;

• 刪除 product_name_pinyin 列

ALTER TABLE product DROP COLUMN product_name_pinyin;

• 刪除表中特定的行（語法）

-- 一定注意添加 WHERE 條件，否則將會(huì)刪除所有的數(shù)據(jù)
DELETE FROM product WHERE COLUMN_NAME='XXX';

優(yōu)點(diǎn)：相比drop / delete，truncate用來清除數(shù)據(jù)時(shí)，速度最快。

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

UPDATE <表名>
? SET <列名> = <表達(dá)式> [, <列名2>=<表達(dá)式2>...]; ?
WHERE <條件>; ?-- 可選，非常重要。
ORDER BY 子句; ?--可選
LIMIT 子句; --可選

使用 update 時(shí)要注意添加 where 條件，否則將會(huì)將所有的行按照語句修改

-- 修改所有的注冊(cè)時(shí)間
UPDATE product
? SET regist_date = '2009-10-10'; ?
-- 僅修改部分商品的單價(jià)
UPDATE product
? SET sale_price = sale_price * 10
WHERE product_type = '廚房用具'; ?

使用 UPDATE 也可以將列更新為 NULL（該更新俗稱為NULL清空）。此時(shí)只需要將賦值表達(dá)式右邊的值直接寫為 NULL 即可。

-- 將商品編號(hào)為0008的數(shù)據(jù)（圓珠筆）的登記日期更新為NULL ?
UPDATE product
? SET regist_date = NULL
WHERE product_id = '0008'; ?

和 INSERT 語句一樣， UPDATE 語句也可以將 NULL 作為一個(gè)值來使用。 但是，只有未設(shè)置 NOT NULL 約束和主鍵約束的列才可以清空為NULL。如果將設(shè)置了上述約束的列更新為 NULL，就會(huì)出錯(cuò)，這點(diǎn)與INSERT 語句相同。

• 多列更新

UPDATE 語句的 SET 子句支持同時(shí)將多個(gè)列作為更新對(duì)象。

-- 基礎(chǔ)寫法，一條UPDATE語句只更新一列
UPDATE product
? SET sale_price = sale_price * 10
WHERE product_type = '廚房用具';
UPDATE product
? SET purchase_price = purchase_price / 2
WHERE product_type = '廚房用具'; ?

該寫法可以得到正確結(jié)果，但是代碼較為繁瑣。可以采用合并的方法來簡化代碼。

-- 合并后的寫法
UPDATE product
? SET sale_price = sale_price * 10,
? ? ? purchase_price = purchase_price / 2
WHERE product_type = '廚房用具'; ?

需要明確的是，SET 子句中的列不僅可以是兩列，還可以是三列或者更多。

1.2.9索引

• 索引的作用

MySQL索引的建立對(duì)于MySQL的高效運(yùn)行是很重要的，索引可以大大提高M(jìn)ySQL的檢索速度。

打個(gè)比方，如果合理的設(shè)計(jì)且使用索引的 MySQL 是一輛蘭博基尼的話，那么沒有設(shè)計(jì)和使用索引的 MySQL 就是一個(gè)人力三輪車。

拿漢語字典的目錄頁（索引）打比方，我們可以按拼音、筆畫、偏旁部首等排序的目錄（索引）快速查找到需要的字。

索引創(chuàng)建了一種有序的數(shù)據(jù)結(jié)構(gòu)，采用二分法搜索數(shù)據(jù)時(shí)，其復(fù)雜度為

，1000多萬的數(shù)據(jù)只要搜索23次，其效率是非常高效的。

• 索引分類

  建立在主鍵上的索引被稱為主鍵索引，一張數(shù)據(jù)表只能有一個(gè)主鍵索引，索引列值不允許有空值，通常在創(chuàng)建表時(shí)一起創(chuàng)建。

  建立在UNIQUE字段上的索引被稱為唯一索引，一張表可以有多個(gè)唯一索引，索引列值允許為空，列值中出現(xiàn)多個(gè)空值不會(huì)發(fā)生重復(fù)沖突。

  建立在普通字段上的索引被稱為普通索引。

  前綴索引是指對(duì)字符類型字段的前幾個(gè)字符或?qū)ΧM(jìn)制類型字段的前幾個(gè)bytes建立的索引，而不是在整個(gè)字段上建索引。前綴索引可以建立在類型為char、varchar、binary、varbinary的列上，可以大大減少索引占用的存儲(chǔ)空間，也能提升索引的查詢效率。

  利用“分詞技術(shù)”實(shí)現(xiàn)在長文本中搜索關(guān)鍵字的一種索引。

  語法：SELECT * FROM article WHERE MATCH (col1，col2，...) AGAINST (expr [ search _ modifier ])

  1、MySQL 5.6 以前的版本，只有 MyISAM 存儲(chǔ)引擎支持全文索引；

  2、MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存儲(chǔ)引擎均支持全文索引;

  3、只有字段的數(shù)據(jù)類型為 char、varchar、text 及其系列才可以建全文索引。

  4、如果可能，請(qǐng)盡量先創(chuàng)建表并插入所有數(shù)據(jù)后再創(chuàng)建全文索引，而不要在創(chuàng)建表時(shí)就直接創(chuàng)建全文索引，因?yàn)榍罢弑群笳叩娜乃饕室摺?/p>

  建立在單個(gè)列上的索引被稱為單列索引。

  建立在多個(gè)列上的索引被稱為聯(lián)合索引，又叫復(fù)合索引、組合索引。

  二 基礎(chǔ)查詢與排序

  2.1.2從表中選取符合條件的數(shù)據(jù)

  WHERE語句

  當(dāng)不需要取出全部數(shù)據(jù)，而是選取出滿足“商品種類為衣服”“銷售單價(jià)在1000日元以上”等某些條件的數(shù)據(jù)時(shí)，使用WHERE語句。

  SELECT 語句通過WHERE子句來指定查詢數(shù)據(jù)的條件。在WHERE 子句中可以指定“某一列的值和這個(gè)字符串相等”或者“某一列的值大于這個(gè)數(shù)字”等條件。執(zhí)行含有這些條件的SELECT語句，就可以查詢出只符合該條件的記錄了。

  SELECT <列名>, ……
  ?FROM <表名>
  WHERE <條件表達(dá)式>;

  比較下面兩者輸出結(jié)果的不同：

  -- 用來選取product type列為衣服的記錄的SELECT語句
  SELECT product_name, product_type
  ?FROM product
  WHERE product_type = '衣服';
  -- 也可以選取出不是查詢條件的列（條件列與輸出列不同）
  SELECT product_name
  ?FROM product
  WHERE product_type = '衣服';

  2.1.3 相關(guān)法則

  -- 想要查詢出全部列時(shí)，可以使用代表所有列的星號(hào)（*）。
  SELECT *
  ?FROM <表名>；
  -- SQL語句可以使用AS關(guān)鍵字為列設(shè)定別名（用中文時(shí)需要雙引號(hào)（“”））。
  SELECT product_id ? ? As id,
  ? ? ? product_name ? As name,
  ? ? ? purchase_price AS "進(jìn)貨單價(jià)"
  ?FROM product;
  -- 使用DISTINCT刪除product_type列中重復(fù)的數(shù)據(jù)
  SELECT DISTINCT product_type
  ?FROM product;

• 星號(hào)（*）代表全部列的意思。

• SQL中可以隨意使用換行符，不影響語句執(zhí)行（但不可插入空行）。

• 設(shè)定漢語別名時(shí)需要使用雙引號(hào)（"）括起來。

• 在SELECT語句中使用DISTINCT可以刪除重復(fù)行。

• 注釋是SQL語句中用來標(biāo)識(shí)說明或者注意事項(xiàng)的部分。分為1行注釋"-- "和多行注釋兩種"/* */"。

• 聯(lián)合索引（復(fù)合索引、多列索引）

• 單列索引

• 全文索引

• 前綴索引

• 普通索引

• 唯一索引

• 主鍵索引

2.5 對(duì)表進(jìn)行分組

2.5.1 GROUP BY語句

2.5.2 常見錯(cuò)誤

在使用聚合函數(shù)及GROUP BY子句時(shí)，經(jīng)常出現(xiàn)的錯(cuò)誤有：

1. 在聚合函數(shù)的SELECT子句中寫了聚合健以外的列使用COUNT等聚合函數(shù)時(shí)，SELECT子句中如果出現(xiàn)列名，只能是GROUP BY子句中指定的列名（也就是聚合鍵）。

2. 在GROUP BY子句中使用列的別名SELECT子句中可以通過AS來指定別名，但在GROUP BY中不能使用別名。因?yàn)樵贒BMS中 ,SELECT子句在GROUP BY子句后執(zhí)行。

3. 在WHERE中使用聚合函數(shù)原因是聚合函數(shù)的使用前提是結(jié)果集已經(jīng)確定，而WHERE還處于確定結(jié)果集的過程中，所以相互矛盾會(huì)引發(fā)錯(cuò)誤。 如果想指定條件，可以在SELECT，HAVING（下面馬上會(huì)講）以及ORDER BY子句中使用聚合函數(shù)。

4. 在group by 存在的情況下，select后的所有非聚合字段都會(huì)被視為分組字段，需要在group by語句中出現(xiàn)，否則就會(huì)報(bào)錯(cuò)；反之，在group by后出現(xiàn)的字段，不在select語句中出現(xiàn)，不會(huì)報(bào)錯(cuò)，但會(huì)有歧義。所以要保證select列字段和group by字段一一對(duì)應(yīng)。

2.6 為聚合結(jié)果指定條件

2.6.1 用HAVING得到特定分組

HAVING子句用于對(duì)分組進(jìn)行過濾，可以使用數(shù)字、聚合函數(shù)和GROUP BY中指定的列名（聚合鍵）。

-- 數(shù)字
SELECT product_type, COUNT(*)
?FROM product
GROUP BY product_type
HAVING COUNT(*) = 2;
-- 錯(cuò)誤形式（因?yàn)閜roduct_name不包含在GROUP BY聚合鍵中）
SELECT product_type, COUNT(*)
?FROM product
GROUP BY product_type
HAVING product_name = '圓珠筆';

2.7 對(duì)查詢結(jié)果進(jìn)行排序

2.7.1 ORDER BY

2.7.2 ORDER BY中列名可使用別名

前文講GROUP BY中提到，GROUP BY 子句中不能使用SELECT 子句中定義的別名，但是在 ORDER BY 子句中卻可以使用別名。為什么在GROUP BY中不可以而在ORDER BY中可以呢？

這是因?yàn)镾QL在使用 HAVING 子句時(shí) SELECT 語句的順序?yàn)椋?/p>

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY。

其中SELECT的執(zhí)行順序在 GROUP BY 子句之后，ORDER BY 子句之前。也就是說，當(dāng)在ORDER BY中使用別名時(shí)，已經(jīng)知道了SELECT設(shè)置的別名存在，但是在GROUP BY中使用別名時(shí)還不知道別名的存在，所以不能在ORDER BY中可以使用別名，但是在GROUP BY中不能使用別名

第三章 復(fù)雜一點(diǎn)的查詢

之前介紹了sql基本的查詢用法，接下來介紹一些相對(duì)復(fù)雜的用法。

3.1 視圖

創(chuàng)建視圖基本語法：create view<視圖名稱>(<列名1>，<列名2>,...) AS <select語句>

• 注意事項(xiàng)

需要注意的是在一般的DBMS中定義視圖時(shí)不能使用ORDER BY語句。下面這樣定義視圖是錯(cuò)誤的。

為什么不能使用 ORDER BY 子句呢？這是因?yàn)橐晥D和表一樣，數(shù)據(jù)行都是沒有順序的。

在 MySQL中視圖的定義是允許使用 ORDER BY 語句的，但是若從特定視圖進(jìn)行選擇，而該視圖使用了自己的 ORDER BY 語句，則視圖定義中的 ORDER BY 將被忽略。

3.1.6 如何更新視圖內(nèi)容

因?yàn)橐晥D是一個(gè)虛擬表，所以對(duì)視圖的操作就是對(duì)底層基礎(chǔ)表的操作，所以在修改時(shí)只有滿足底層基本表的定義才能成功修改。

對(duì)于一個(gè)視圖來說，如果包含以下結(jié)構(gòu)的任意一種都是不可以被更新的：

• 聚合函數(shù) SUM()、MIN()、MAX()、COUNT() 等。

• DISTINCT 關(guān)鍵字。

• GROUP BY 子句。

• HAVING 子句。

• UNION 或 UNION ALL 運(yùn)算符。

• FROM 子句中包含多個(gè)表。

視圖歸根結(jié)底還是從表派生出來的，因此，如果原表可以更新，那么 視圖中的數(shù)據(jù)也可以更新。反之亦然，如果視圖發(fā)生了改變，而原表沒有進(jìn)行相應(yīng)更新的話，就無法保證數(shù)據(jù)的一致性了。

• 更新視圖

因?yàn)槲覀儎倓傂薷牡膒roductSum視圖不包括以上的限制條件，我們來嘗試更新一下視圖

UPDATE productsum
? SET sale_price = '5000'
WHERE product_type = '辦公用品';

3.2 子查詢

3.2.1 什么是子查詢

子查詢指一個(gè)查詢語句嵌套在另一個(gè)查詢語句內(nèi)部的查詢，這個(gè)特性從 MySQL 4.1 開始引入，在 SELECT 子句中先計(jì)算子查詢，子查詢結(jié)果作為外層另一個(gè)查詢的過濾條件，查詢可以基于一個(gè)表或者多個(gè)表。

3.2.2 子查詢和視圖的關(guān)系

子查詢就是將用來定義視圖的 SELECT 語句直接用于 FROM 子句當(dāng)中。其中AS studentSum可以看作是子查詢的名稱，而且由于子查詢是一次性的，所以子查詢不會(huì)像視圖那樣保存在存儲(chǔ)介質(zhì)中， 而是在 SELECT 語句執(zhí)行之后就消失了。

3.1 create view ?ViewPractice5_1

? ? ? AS

? ? ? select product_name,sale_price,regist_date

? ? ? from product

? ? ? where sale_price>1000 and regist_date = '2009-09-20'