Linux數據庫：【表的約束】【表的基本查詢】

一.表的約束

1.1空屬性 not null

1.2默認值 default

?空屬性和默認值一起使用？

1.3列描述 comment

1.4 zerofill

1.5 主鍵

1.6 自增長

1.7 唯一鍵

1.8 外鍵

二. 表的基本查詢

2.1 Create

2.1.1單行數據 + 全列插入

2.1.2多行數據 + 指定列插入

2.1.3 插入否則更新

2.1.4 替換

2.2?Retrieve

2.2.1 select 列

2.2.1.1 全列查詢

2.2.1.2指定列查詢

2.2.1.3 查詢字段為表達式

2.2.1.4為查詢結果指定別名

2.2.1.5結果去重 distinct

2.2.2where條件

2.2.2.1英語不及格的同學及英語成績 ( < 60 )

2.2.2.2語文成績在 [80, 90] 分的同學及語文成績

2.2.2.3 數學成績是 58 或者 59 或者 98 或者 99 分的同學及數學成績

2.2.2.4?姓孫的同學及孫某同學

2.2.2.5語文成績好于英語成績的同學

2.2.2.6 總分在 200 分以下的同學

2.2.2.7?語文成績 > 80 并且不姓孫的同學

2.2.2.8孫某同學，否則要求總成績 > 200 并且語文成績 < 數學成績并且英語成績 > 80

2.2.2.9 NULL 的查詢

2.2.3結果排序 order by

2.2.3.1?同學及數學成績，按數學成績升序顯示

2.2.3.2同學及 qq 號，按 qq 號排序顯示

2.2.3.3查詢同學各門成績，依次按數學降序，英語升序，語文升序的方式顯示

2.2.3.4查詢同學及總分，由高到低

2.2.4 篩選分頁結果 limit

2.3 Update

2.3.1將孫悟空同學的數學成績變更為 80 分

2.3.2將曹孟德同學的數學成績變更為 60 分，語文成績變更為 70 分

2.3.3將總成績倒數前三的 3 位同學的數學成績加上 30 分

2.3.4將所有同學的語文成績更新為原來的 2 倍

2.4 Delete

2.4.1 刪除數據 delete

2.4.1.1 刪除孫悟空

2.4.1.2 刪除整張表

2.4.2截斷表 truncate

2.5插入查詢結果 rename

2.6 聚合函數

2.6.1?統計班級共有多少同學 count

2.6.2 統計qq號有多少個

2.6.3統計本次考試的數學成績分數個數

2.6.4統計總分 sum

2.6.5統計平均總分

2.6.6返回英語最高分

2.6.7返回 > 70 分以上的數學最低分

2.7group by子句的使用

一.表的約束

真正約束字段的是數據類型，但是數據類型約束很單一，需要有一些額外的約束，更好的保證數據的合法性，從業務邏輯角度保證數據的正確性。比如有一個字段是email，要求是唯一的。

1.1空屬性 not null

兩個值：null（默認的）和not null(不為空)

數據庫默認字段基本都是字段為空，但是實際開發時，盡可能保證字段不為空，因為數據為空沒辦

法參與運算。

比如我在創建一個班級表的時候就可以，設置一下不為空的屬性（not null）：?

?表格里的Null的那一列就變成了NO。

當我們在插入數據的時候這兩行數據就不可以為null。如下圖：

下面的那個我們就不可以進行插入。

1.2默認值 default

默認值實際上就是缺省值，當我們不添加某一個值的時候，mysql會使用默認值。如果我們主動的添加了值就用我們自己的值。

這里面我設置了age的默認值和sex的默認值：

?空屬性和默認值一起使用？

這里還是上面的表，我修改了t1的sex：

一種是我不傳sex，會使用默認值。另一種是我傳null，這里會報錯。看現象：?

1.3列描述 comment

這個很簡單，就充當備注的意思：

使用desc無法看到?

show查看詳細信息：?

1.4 zerofill

剛開始學習數據庫時，很多人對數字類型后面的長度很迷茫。

可以看到int(11),這個代表什么意思呢？整型不是4字節碼？這個10又代表什么呢？其實沒有zerofill這個屬性，括號內的數字是毫無意義的。

但是對列添加了zerofill屬性后，顯示的結果就有所不同了。修改表的屬性：

可以發現，除了10剩下的全都補0了。這就是zerofill屬性的作用，如果寬度小于設定的寬度（這里設置的是5），自動填充0。要注意的是，這只是最后顯示的結果，在MySQL中實際存儲的還是1。為什么是這樣呢？我們可以用hex函數來證明。

可以看出數據庫內部存儲的還是A,00010只是設置了zerofill屬性后的一種格式化輸出而已。

1.5 主鍵

主鍵：primary key用來唯一的約束該字段里面的數據，不能重復，不能為空，一張表中最多只能有一個主鍵；主鍵所在的列通常是整數類型。

主鍵約束：主鍵對應的字段中不能重復，一旦重復，操作失敗。

當表創建好以后但是沒有主鍵的時候，可以再次追加主鍵

alter table 表名 add primary key(字段列表)

刪除主鍵

alter table 表名 drop primary key;

復合主鍵

在創建表的時候，在所有字段之后，使用primary key(主鍵字段列表)來創建主鍵，如果有多個字段作為主鍵，可以使用復合主鍵。

這個是直接添加：

插入兩次的話就會失敗：

這里還可以創建表成功后，在去添加主鍵，運用一下上面的命令：

1.6 自增長

auto_increment：當對應的字段，不給值，會自動的被系統觸發，系統會從當前字段中已經有的最大值 +1操作，得到一個新的不同的值。通常和主鍵搭配使用，作為邏輯主鍵。

自增長的特點:

任何一個字段要做自增長，前提是本身是一個索引（key一欄有值）

自增長字段必須是整數

一張表最多只能有一個自增長

此時再去添加字段的話：

索引：

????????在關系數據庫中，索引是一種單獨的、物理的對數據庫表中一列或多列的值進行排序的一種存儲結構，它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的數據頁的邏輯指針清單。索引的作用相當于圖書的目錄，可以根據目錄中的頁碼快速找到所需的內容。

????????索引提供指向存儲在表的指定列中的數據值的指針，然后根據您指定的排序順序對這些指針排序。數據庫使用索引以找到特定值，然后順指針找到包含該值的行。這樣可以使對應于表的SQL語句執行得更快，可快速訪問數據庫表中的特定信息。

1.7 唯一鍵

一張表中有往往有很多字段需要唯一性，數據不能重復，但是一張表中只能有一個主鍵：唯一鍵就可以解決表中有多個字段需要唯一性約束的問題。

唯一鍵的本質和主鍵差不多，唯一鍵允許為空，而且可以多個為空，空字段不做唯一性比較。

關于唯一鍵和主鍵的區別：我們可以簡單理解成，主鍵更多的是標識唯一性的。而唯一鍵更多的是保證在業務上，不要和別的信息出現重復。乍一聽好像沒啥區別，我們舉一個例子

假設一個場景(當然，具體可能并不是這樣，僅僅為了幫助大家理解) 比如在公司，我們需要一個員工管理系統，系統中有一個員工表，員工表中有兩列信息，一個身份證號碼，一個是員工工號，我們可以選擇身份號碼作為主鍵。而我們設計員工工號的時候，需要一種約束：而所有的員工工號都不能重復。具體指的是在公司的業務上不能重復，我們設計表的時候，需要這個約束，那么就可以將員工工號設計成為唯一鍵。一般而言，我們建議將主鍵設計成為和當前業務無關的字段，這樣，當業務調整的時候，我們可以盡量不會對主鍵做過大的調整

1.8 外鍵

外鍵用于定義主表和從表之間的關系：外鍵約束主要定義在從表上，主表則必須是有主鍵約束或unique 約束。當定義外鍵后，要求外鍵列數據必須在主表的主鍵列存在或為null。

外鍵：

foreign key (字段名) references 主表(列)

先創建主表，再創建從表：

給主表插入數據：

給從表插入數據：

插入班級id為null，比如來了一個學生，目前還沒有分配班級

理解外鍵約束

首先我們承認，這個世界是數據很多都是相關性的。

理論上，上面的例子，我們不創建外鍵約束，就正常建立學生表，以及班級表，該有的字段我們都有。此時，在實際使用的時候，可能會出現什么問題？有沒有可能插入的學生信息中有具體的班級，但是該班級卻沒有在班級表中？這很明顯是有問題的。因為此時兩張表在業務上是有相關性的，但是在業務上沒有建立約束關系，那么就可能出現問題。解決方案就是通過外鍵完成的。建立外鍵的本質其實就是把相關性交給mysql去審核了，提前告訴mysql 表之間的約束關系，那么當用戶插入不符合業務邏輯的數據的時候，mysql不允許你插入。

?插入一個班級號為30的學生，因為沒有這個班級，所以插入不成功，這就是外鍵約束

二. 表的基本查詢

2.1 Create

-- 創建一張學生表
CREATE TABLE students (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,sn INT NOT NULL UNIQUE COMMENT '學號',name VARCHAR(20) NOT NULL,qq VARCHAR(20)
);

2.1.1單行數據 + 全列插入

下面我插入三條數據：

2.1.2多行數據 + 指定列插入

加上逗號就行了：

2.1.3 插入否則更新

由于主鍵或者唯一鍵對應的值已經存在而導致插入失敗

-- 主鍵沖突
INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大師');
ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'
-- 唯一鍵沖突
INSERT INTO students (sn, name) VALUES (20001, '曹阿瞞');
ERROR 1062 (23000): Duplicate entry '20001' for key 'sn'

可以選擇性的進行同步更新操作語法：

INSERT ... ON DUPLICATE KEY UPDATE column = value [, column = value] ...

這里我就故意寫一個與主鍵重復的人，并且修改。

相當于是如果主鍵沖突了，就把插入操作變為update。這里是2 row affected。

2.1.4 替換

-- 主鍵 或者 唯一鍵 沒有沖突，則直接插入；
-- 主鍵 或者 唯一鍵 如果沖突，則刪除后再插入
REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞞');
Query OK, 2 rows affected (0.00 sec)
-- 1 row affected: 表中沒有沖突數據，數據被插入
-- 2 row affected: 表中有沖突數據，刪除后重新插入

下面就可以看出，一行被影響和兩行被影響，且成功把nihao替換為ok：

2.2?Retrieve

先準備好表：

-- 創建表結構 
CREATE TABLE exam_result ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL COMMENT '同學姓名', chinese float DEFAULT 0.0 COMMENT '語文成績', math float DEFAULT 0.0 COMMENT '數學成績', english float DEFAULT 0.0 COMMENT '英語成績' 
); -- 插入測試數據 
INSERT INTO exam_result (name, chinese, math, english) VALUES ('唐三藏', 67, 98, 56), ('孫悟空', 87, 78, 77), ('豬悟能', 88, 98, 90), ('曹孟德', 82, 84, 67), ('劉玄德', 55, 85, 45), ('孫權', 70, 73, 78), ('宋公明', 75, 65, 30); 
Query OK, 7 rows affected (0.00 sec) 
Records: 7 Duplicates: 0 Warnings: 0

2.2.1 select 列

2.2.1.1 全列查詢

- - 通常情況下不建議使用 * 進行全列查詢

- - 1 . 查詢的列越多，意味著需要傳輸的數據量越大；

- - 2 . 可能會影響到索引的使用。

2.2.1.2指定列查詢

2.2.1.3 查詢字段為表達式

甚至是：

2.2.1.4為查詢結果指定別名

as去掉也可以：

2.2.1.5結果去重 distinct

去重后：

2.2.2where條件

比較運算符：

邏輯運算符：

2.2.2.1英語不及格的同學及英語成績 ( < 60 )

2.2.2.2語文成績在 [80, 90] 分的同學及語文成績

或者不使用比較運算符，使用between? and：

2.2.2.3 數學成績是 58 或者 59 或者 98 或者 99 分的同學及數學成績

或者是使用in加上一段：

2.2.2.4?姓孫的同學及孫某同學

-- % 匹配任意多個（包括 0 個）任意字符 
-- _ 匹配嚴格的一個任意字符

2.2.2.5語文成績好于英語成績的同學

2.2.2.6 總分在 200 分以下的同學

-- WHERE 條件中使用表達式

-- 別名不能用在 WHERE 條件中

2.2.2.7?語文成績 > 80 并且不姓孫的同學

2.2.2.8孫某同學，否則要求總成績 > 200 并且語文成績 < 數學成績并且英語成績 > 80

2.2.2.9 NULL 的查詢

查詢class_id 不為null的

NULL 和 NULL 的比較，= 和 <=>的區別

2.2.3結果排序 order by

-- ASC 為升序（從小到大） 
-- DESC 為降序（從大到小） 
-- 默認為 ASC SELECT ... FROM table_name [WHERE ...] ORDER BY column [ASC|DESC], [...];

注意：沒有 ORDER BY 子句的查詢，返回的順序是未定義的，永遠不要依賴這個順序

2.2.3.1?同學及數學成績，按數學成績升序顯示

2.2.3.2同學及 qq 號，按 qq 號排序顯示

2.2.3.3查詢同學各門成績，依次按數學降序，英語升序，語文升序的方式顯示

2.2.3.4查詢同學及總分，由高到低

2.2.4 篩選分頁結果 limit

建議：對未知表進行查詢時，最好加一條 LIMIT 1，避免因為表中數據過大，查詢全表數據導致數據庫卡死按 id 進行分頁，每頁 3 條記錄，分別顯示第 1、2、3 頁

例如：

2.3 Update

UPDATE table_name SET column = expr [, column = expr ...] [WHERE ...] [ORDER BY ...] [LIMIT ...]

2.3.1將孫悟空同學的數學成績變更為 80 分

2.3.2將曹孟德同學的數學成績變更為 60 分，語文成績變更為 70 分

2.3.3將總成績倒數前三的 3 位同學的數學成績加上 30 分

2.3.4將所有同學的語文成績更新為原來的 2 倍

不用where就是更新全部的：

2.4 Delete

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

2.4.1 刪除數據 delete

2.4.1.1 刪除孫悟空

2.4.1.2 刪除整張表

這是很危險的操作！

2.4.2截斷表 truncate

TRUNCATE [TABLE] table_name

注意：這個操作慎用

1. 只能對整表操作，不能像 DELETE 一樣針對部分數據操作；

2. 實際上 MySQL 不對數據操作，所以比 DELETE 更快，但是TRUNCATE在刪除數據的時候，并不經過真正的事物，所以無法回滾

3. 會重置 AUTO_INCREMENT 項

刪除之后，重新查看表里面的數據：

2.5插入查詢結果 rename

INSERT INTO table_name [(column [, column ...])] SELECT ...

先創建原始數據表：

-- 創建一張空表 no_duplicate_table，結構和 duplicate_table 一樣

-- 將 duplicate_table 的去重數據插入到 no_duplicate_table

-- 通過重命名表，實現原子的去重操作

2.6 聚合函數

2.6.1?統計班級共有多少同學 count

使用 * 做統計，不受 NULL 影響

當然重命名也是可以的：

使用表達式可以：

2.6.2 統計qq號有多少個

注意null不作數

2.6.3統計本次考試的數學成績分數個數

2.6.4統計總分 sum

2.6.5統計平均總分

2.6.6返回英語最高分

運用之前的也可以找：

2.6.7返回 > 70 分以上的數學最低分

2.7group by子句的使用

在select中使用group by 子句可以對指定列進行分組查詢

select column1, column2, .. from table group by column;

準備工作，

創建一個雇員信息表（來自oracle 9i的經典測試表）

EMP員工表

DEPT部門表

SALGRADE工資等級表

隨后插入數據：

如何顯示每個部門的平均工資和最高工資，總共三個組，group by指定列名，實際分組，是用該列的不同行數據來進行分組的，分組的條件deptno，組內一定是相同的，也就是說，可以被聚合壓縮：

顯示每個部門的每種崗位的平均工資和最低工資

如果我在加一個ename呢？

名字人人都有，無法分組，分組其實就是把相同的東西放到一起。

顯示平均工資低于2000的部門和它的平均工資

統計各個部門的平均工資

having和group by配合使用，對group by結果進行過濾

--having經常和group by搭配使用，作用是對分組進行篩選，作用有些像where。

where（純粹的對表篩選）與having（分組之后的篩選）對比：