摘要：數(shù)據(jù)庫(kù)管理系統(tǒng)的全稱是，簡(jiǎn)稱。命令的語(yǔ)法表名數(shù)據(jù)庫(kù)約束約束是表上強(qiáng)制執(zhí)行的數(shù)據(jù)校驗(yàn)規(guī)則，約束主要用于保證數(shù)據(jù)庫(kù)里數(shù)據(jù)的完整性。

SQL語(yǔ)句是對(duì)所有關(guān)系數(shù)據(jù)庫(kù)都通用的命令語(yǔ)法，而JDBC API只是執(zhí)行SQL語(yǔ)句的工具，JDBC允許對(duì)不同的平臺(tái)、不同的數(shù)據(jù)庫(kù)采用相同的編程接口來(lái)執(zhí)行SQL語(yǔ)句

數(shù)據(jù)庫(kù)僅僅是存放用戶數(shù)據(jù)的地方。當(dāng)用戶訪問(wèn)、操作數(shù)據(jù)庫(kù)中的數(shù)據(jù)時(shí)，就需要數(shù)據(jù)庫(kù)管理系統(tǒng)的幫助。數(shù)據(jù)庫(kù)管理系統(tǒng)的全稱是Database Management System，簡(jiǎn)稱DBMS。把數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)管理系統(tǒng)籠統(tǒng)地稱為數(shù)據(jù)庫(kù)，通常所說(shuō)的數(shù)據(jù)庫(kù)既包括存儲(chǔ)用戶數(shù)據(jù)的部分，也包括管理數(shù)據(jù)庫(kù)的管理系統(tǒng)

DBMS是所有數(shù)據(jù)的知識(shí)庫(kù)，它負(fù)責(zé)管理數(shù)據(jù)的存儲(chǔ)、安全、一致性、并發(fā)、恢復(fù)和訪問(wèn)等操作。DBMS有一個(gè)數(shù)據(jù)字典（也稱為系統(tǒng)表），用于存儲(chǔ)它擁有的每個(gè)事務(wù)的相關(guān)信息，例如名字、結(jié)構(gòu)、位置和類型，這種關(guān)于數(shù)據(jù)的數(shù)據(jù)也稱為元數(shù)據(jù)

在數(shù)據(jù)庫(kù)的發(fā)展歷史中，按時(shí)間順序主要出現(xiàn)了如下幾種類型的數(shù)據(jù)庫(kù)系統(tǒng)：

網(wǎng)狀型數(shù)據(jù)庫(kù)

層次型數(shù)據(jù)庫(kù)

關(guān)系數(shù)據(jù)庫(kù)

面向?qū)ο髷?shù)據(jù)庫(kù)

MySQL數(shù)據(jù)庫(kù)的一個(gè)實(shí)例可以同時(shí)包含多個(gè)數(shù)據(jù)庫(kù)，MySQL使用如下命令來(lái)查看當(dāng)前實(shí)例下包含多少數(shù)據(jù)庫(kù)

show databases;

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

create database [IF NOT EXISTS] 數(shù)據(jù)庫(kù)名;

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

delete database 數(shù)據(jù)庫(kù)名;

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

use 數(shù)據(jù)庫(kù)名;

查詢?cè)摂?shù)據(jù)庫(kù)下包含多少個(gè)數(shù)據(jù)表

show tables;

查看指定數(shù)據(jù)表的表結(jié)構(gòu)

desc 表明;

MySQL數(shù)據(jù)庫(kù)通常支持如下兩種存儲(chǔ)機(jī)制：

MyISAM：這是MySQL早期默認(rèn)的存儲(chǔ)機(jī)制，對(duì)事務(wù)支持不夠好

InnoDB：InnoDB提供事務(wù)安全的存儲(chǔ)機(jī)制。InnoDB存儲(chǔ)機(jī)制，如果不想使用InnoDB表，則可以使用skip-innodb選項(xiàng)

ENGINE = MyISAM —— 強(qiáng)制使用MyISAM

ENGINE = InnoDB —— 強(qiáng)制使用InnoDB

SQL的全稱是Sructured Query Language，結(jié)構(gòu)化查詢語(yǔ)言。SQL是操作和檢索關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言，標(biāo)準(zhǔn)的SQL語(yǔ)句可用于操作任何關(guān)系數(shù)據(jù)庫(kù)

使用SQL語(yǔ)句，程序員和數(shù)據(jù)庫(kù)管理員（DBA）可以完成如下任務(wù)：

在數(shù)據(jù)庫(kù)中檢索信息

對(duì)數(shù)據(jù)庫(kù)的信息進(jìn)行更新

改變數(shù)據(jù)庫(kù)的結(jié)構(gòu)

更改系統(tǒng)的安全設(shè)置

增加或回收用戶對(duì)數(shù)據(jù)庫(kù)、表的許可權(quán)限

標(biāo)準(zhǔn)的SQL語(yǔ)句通常可以分為如下幾種類型：

查詢語(yǔ)句：主要由select關(guān)鍵字完成，查詢語(yǔ)句是SQL語(yǔ)句中最復(fù)雜、功能最豐富的語(yǔ)句

DML（Data Manipulation Language，數(shù)據(jù)操作語(yǔ)言）語(yǔ)句：主要由inset、update、delete關(guān)鍵字完成

DDL（Data Definition Language，數(shù)據(jù)定義語(yǔ)言）語(yǔ)句：主要由create、alter、drop、truncate關(guān)鍵字完成

DCL（Data Control Language，數(shù)據(jù)控制語(yǔ)言）語(yǔ)句：主要由grant、revoke關(guān)鍵字完成

事物控制語(yǔ)句：主要由commit、rollback、savepoint關(guān)鍵字完成

標(biāo)識(shí)符的命名規(guī)則：

標(biāo)識(shí)符通常必須以字母開(kāi)頭

標(biāo)識(shí)符包括字母、數(shù)字和三個(gè)特殊字符（# _ $）

不要使用當(dāng)前數(shù)據(jù)庫(kù)系統(tǒng)的關(guān)鍵字、保留字，通常建議使用多個(gè)單詞連綴而成，單詞之間以_分隔

同一個(gè)模式下的對(duì)象不應(yīng)該同名，這里的模式指的是外模式

DDL語(yǔ)句是操作數(shù)據(jù)庫(kù)對(duì)象的語(yǔ)句，包括創(chuàng)建（create）、刪除（delete）、修改（alter）數(shù)據(jù)庫(kù)對(duì)象

數(shù)據(jù)庫(kù)里的幾種常見(jiàn)的數(shù)據(jù)庫(kù)對(duì)象

create table [模式名.] 表名
(
    # 可以定義有多個(gè)列定義
    columnName1 datatype [default expr] ,
    ...
)

每個(gè)列定義之間以英文逗號(hào)(,)隔開(kāi)，最后一個(gè)列定義不需要使用英文逗號(hào)，而是直接以括號(hào)結(jié)束
列名放在前面，列類型放在后面。如果要指定列的默認(rèn)值，則使用default關(guān)鍵字，而不是使用等號(hào)(=)

使用子查詢建表語(yǔ)句，可以在建表的同時(shí)插入數(shù)據(jù)。子查詢建表的語(yǔ)法

create table [模式名.]表名 [column[, cloumn...]]
as subquery;

#創(chuàng)建premium_info數(shù)據(jù)表，該數(shù)據(jù)表和user_info完全相同，數(shù)據(jù)也完全相同
create table premium_info
as
select * from user_info;

修改表結(jié)構(gòu)使用alter table，修改表結(jié)構(gòu)包括增加列定義、修改列定義、刪除列、重命名列等操作。SQL語(yǔ)句中的字符串值不少用雙引號(hào)引起，而是用單引號(hào)引起的

alter table 表名
add
(
    #可以有多個(gè)列定義
    column_name1 datatype [default expr] ,
    ...
)

如果只是新增一列，則可以省略圓括號(hào)，僅在add后緊跟一個(gè)列定義即可

# 為premium_info數(shù)據(jù)表增加一個(gè)pre_id字段，該字段的類型為int;
alter table premium_info
add pre_id int;
# 為premium_info數(shù)據(jù)表增加增加name、duration字段，兩個(gè)字段的類型都為varchar(255)
alter table premium_info
add
(
name varchar(255) default "Jimmy",
duration varchar(255)
)

修改列定義的語(yǔ)法

alter table 表名
modify column_name datatype [default expr] [first|after col_name];

first或者after col_name指定需要將目標(biāo)修改到指定位置，該修改語(yǔ)句每次只能修改一個(gè)列定義

#將premium_info數(shù)據(jù)表的pre_id列修改成varchar(255)類型
alter table premium_info
modify pre_id varchar(255);
#將premium_info的duration列修改成int類型
alter table premium_info
modify duration int;

如果需要讓MySQL支持一次修改多個(gè)列定義，則可以在alter table后使用多個(gè)modify命令

刪除列的語(yǔ)法

alter table 表名
drop column_name   

# 刪除premium_info表中的name字段
alter talbe premium_info
drop name;

從數(shù)據(jù)庫(kù)中刪除列定義通常總是可以成功，刪除列定義時(shí)將從每行中刪除該列的數(shù)據(jù)，并釋放該列在數(shù)據(jù)塊中占用的空間。所以刪除大表中的字段時(shí)需要比較長(zhǎng)的時(shí)間，因?yàn)檫€需要回收空間

MySQL的兩種特殊語(yǔ)法：重命名數(shù)據(jù)表和完全改變列定義

重命名數(shù)據(jù)表的語(yǔ)法

alter table 表名
rename to 新表名

alter table premium_info
rename to premium;

change選項(xiàng)的語(yǔ)法

alter table 表名
change old_column_name new_column_name type [default expr] [first|after col_name];

alter table premium_info
change duration time int;

drop table 表名;

# 刪除數(shù)據(jù)表
drop table premium_info;

刪除數(shù)據(jù)表的效果如下

表結(jié)構(gòu)被刪除，表對(duì)象不再存在

表里的所有數(shù)據(jù)也被刪除

該表所有相關(guān)的索引、約束也被刪除

truncate被稱為“截?cái)唷蹦硞€(gè)表——作用是刪除該表里的全部數(shù)據(jù)，但保留表結(jié)構(gòu)。相對(duì)于DML里的delete命令而言，truncate的速度要快得多，而且truncate不像delete可以刪除指定的記錄，truncate只能一次性刪除整個(gè)表的全部記錄。truncate命令的語(yǔ)法：

truncate 表名

約束是表上強(qiáng)制執(zhí)行的數(shù)據(jù)校驗(yàn)規(guī)則，約束主要用于保證數(shù)據(jù)庫(kù)里數(shù)據(jù)的完整性。除此之外，當(dāng)表中數(shù)據(jù)存在相互依賴性時(shí)，可以保護(hù)相關(guān)的數(shù)據(jù)不被刪除

5種完整性約束
大部分?jǐn)?shù)據(jù)庫(kù)支持下面五類完整性約束；

NOT NULL：非空約束，指定某列不能為空

UNIQUE：唯一約束，指定某列或者幾列組合不能重復(fù)

PRIMARY KEY：主鍵，指定該列的值可以唯一地標(biāo)識(shí)該條記錄

FOREIGN KEY：外鍵，指定該行記錄從屬于主表的一條記錄，主要用于保證參照完整性

CHECK：檢查，指定一個(gè)布爾型表達(dá)式，用于指定對(duì)應(yīng)列的值必須滿足該表達(dá)式

MySQL不支持CHECK約束

根據(jù)約束對(duì)數(shù)據(jù)列的限制，可以分為如下兩類：

單行約束：每個(gè)約束只約束一列

多行約束：每個(gè)約束約束多個(gè)數(shù)據(jù)列

為數(shù)據(jù)表指定約束有兩個(gè)時(shí)機(jī)：

在建表的同時(shí)為相應(yīng)的數(shù)據(jù)列指定約束

建表后創(chuàng)建，以修改表的方式來(lái)增加約束

非空約束用于確保指定列不允許為空，非空約束是比較特殊的約束，只能作為列級(jí)使用

SQL中的NULL值特征：

所有的數(shù)據(jù)類型的值都可以為null，包括int、float、boolean等數(shù)據(jù)類型

與java類似的是，空字符串不等于null，0也不等于null

建表示指定非空約束：

create table null_test
(
    # 建立了非空約束，這意味著user_id不可以為null
    user_id int not null,
    # MySQL的非空約束不能指定名字
    user_name varchar(255) default "lin" not null,
    # 下面列可以為空，默認(rèn)就是可以為空
    user_location varchar(255) null
)

也可以在使用alter table修改表時(shí)增加或者刪除非空約束：

# 增加非空約束
alter table null_test
modify user_location varchar(255) not null;
# 取消非空約束
alter table null_test
modify user_name varchar(255) null;
# 取消非空約束，并指定默認(rèn)值
alter table null_test
modify user_location varchar(255) default "Nantes" null;

唯一性約束用于保證指定列或指定列的組合不允許出現(xiàn)重復(fù)值，但可以出現(xiàn)多個(gè)null值（因?yàn)樵跀?shù)據(jù)庫(kù)中null不等于null）

唯一約束既可以使用列級(jí)約束語(yǔ)法建立，也可以使用表級(jí)約束的語(yǔ)法建立。如果需要為多列建組合約束，或者需要為唯一約束指定約束名，則只能用表級(jí)約束語(yǔ)法

當(dāng)建立唯一約束時(shí)，MySQL在唯一約束所在列或列組合上建立對(duì)應(yīng)的唯一索引。如果不給唯一約束起碼，該唯一約束默認(rèn)與列名相同

使用列級(jí)約束語(yǔ)法來(lái)建立唯一約束非常簡(jiǎn)單，只要簡(jiǎn)單的在列定義后增加unique關(guān)鍵字即可

test_name varchar(255) unique

如果需要為多列組合建立唯一約束，或者想自行指定約束名，則需要使用表級(jí)約束語(yǔ)法，表級(jí)約束語(yǔ)法如下：

[constraint 約束名] 約束定義

# 建表時(shí)創(chuàng)建唯一約束，使用表級(jí)的約束語(yǔ)法建約束
create table unique_test2
(
    # 建立了非空約束，著意味著test_id不可以為null
    test_id int not null,
    test_name varchar(255),
    test_pass varchar(255),
    # 使用表及約束語(yǔ)法建唯一約束
    unique (test_name),
    # 使用表級(jí)約束語(yǔ)法建唯一約束，而且指定約束名
    constraint test_uk unique(test_pass)}
);
    

上面建表語(yǔ)句為test_name、test_pass分別建立唯一約束，這意味著兩列不能為空，除此之外還可以為這兩列組合建立唯一約束

# 建表時(shí)創(chuàng)建唯一約束，使用表級(jí)約束語(yǔ)法建約束
create table unique_test3
(
    # 建立了非空約束，著意味著test_id不可以為NULL
    test_id int not null,
    test_name varchar(255),
    test_pass varchar(255),
    # 使用表級(jí)約束語(yǔ)法建唯一約束，指定兩列組合不能為空
    constraint test3_uk unique(test_name, test_pass)
);

unique_test2要求test_name、test_pass都不能出現(xiàn)重復(fù)值，而unique_test3只要求test_name、test_pass兩列值的組合不能重復(fù)

可以修改表結(jié)構(gòu)增加唯一約束

# 添加唯一約束
alter table unique_test3
add unique(test_name, test_pass);

可以在修改表時(shí)使用modify關(guān)鍵字，為單列采用列級(jí)約束語(yǔ)來(lái)增加唯一約束

# 為null_test表的user_name列增加唯一約束
alter table null_test
modify user_name varchar(255) unique;

對(duì)于大多數(shù)數(shù)據(jù)庫(kù)而言，刪除約束都是在alter table語(yǔ)句后使用drop constraint約束名的語(yǔ)法刪除約束，但MySQL并不使用這種方式，而是使用drop index、約束名的方式來(lái)刪除約束

# 刪除unique_test3表上的test3_uk
alter table unique_test3
drop index test3_uk;

主鍵約束相當(dāng)于非空約束和唯一約束，即主鍵約束的列既不允許出現(xiàn)重復(fù)值，也不允許出現(xiàn)null值；如果對(duì)多列組合建立主鍵約束，則多列里包含的每一列都不能為空，但只要求這些列組合不能重復(fù)

每個(gè)表中最多允許一個(gè)主鍵，但這個(gè)主鍵約束可由多個(gè)數(shù)據(jù)列組合而成。主鍵是表中能唯一確定一行記錄的字段或字段組合

建立主鍵約束時(shí)既可以使用列級(jí)約束語(yǔ)，也可使用表級(jí)約束語(yǔ)。如需要對(duì)多個(gè)字段建立組合主鍵約束時(shí)，則只能使用表級(jí)約束語(yǔ)法。使用表級(jí)約束語(yǔ)法來(lái)建立約束時(shí)，可以為該約束指定約束名。MySql總是將所有主鍵約束命名為PRIMARY

建立主鍵約束使用primary key

建表時(shí)創(chuàng)建主鍵約束，使用的是列級(jí)約束語(yǔ)法：

create table primary_test
(
    # 建立主鍵約束
    test_id int primary key,
    test_name varchar(255)
);

建表時(shí)創(chuàng)建主鍵約束，使用表級(jí)約束語(yǔ)法

create table primary_test2
(
    test_id int not null,
    test_name varchar(255),
    test_pass varchar(255),
    # 指定主鍵名為test2_pk，對(duì)大多數(shù)數(shù)據(jù)庫(kù)有效，但對(duì)mysql無(wú)效
    # MySQL數(shù)據(jù)庫(kù)中該主鍵約束依然是primary
    constraint test2_pk primary key(test_id)
);

建表時(shí)創(chuàng)建主鍵約束，以多列建立組合主鍵，只能使用表級(jí)約束語(yǔ)法

create table primary_test3
(
    test_name varchar(255),
    test_pass varchar(255),
    # 使用表級(jí)約束建立多列組合主鍵約束
    primary key(test_name, test_pass)
);

如果需要?jiǎng)h除指定表的主鍵約束，則在alter table語(yǔ)句后使用drop primary_key字句即可

#刪除主鍵約束
alter table primary_test3
drop primary key;

如果需要為指定表增加主鍵約束，即可通過(guò)modify修改列定義來(lái)增加主鍵約束，這將采用列級(jí)約束語(yǔ)法增加主鍵約束
也可通過(guò)add來(lái)增加主鍵約束，這將采用表級(jí)約束語(yǔ)法來(lái)增加主鍵約束

# 使用列級(jí)約束語(yǔ)法增加主鍵約束
alter table_primary_test3
modify test_name varchar(255) primary key;

#使用表級(jí)約束語(yǔ)法增加主鍵約束
alter table primary_test3
add primary key(test_name,test_pass);

MySQL只用auto_increment來(lái)設(shè)置自增長(zhǎng)

create table primary_test4
(
    # 建立主鍵約束，使用自增長(zhǎng)
    test_id int auto_increment primary key,
    test_name varchar(255),
    test_pass varchar(255)
);

外鍵約束主要保證一個(gè)或兩個(gè)數(shù)據(jù)表之間的參照完整性，外鍵是構(gòu)建于一個(gè)表的兩個(gè)字段或者兩個(gè)表的兩個(gè)字段之間的參照關(guān)系。外鍵確保了相關(guān)的兩個(gè)字段的參照關(guān)系：子(從)表外鍵列的值必須在主表被參照列的值范圍之內(nèi)，或者為空。

當(dāng)主表的記錄被從表的記錄參照時(shí)，主表記錄不允許被刪除，必須先把從表里參照該記錄的所有記錄全部刪除后，才能可以刪除主表

從表外鍵參照的只能是主表主鍵列或者唯一鍵列，這樣才可保證從表記錄可以準(zhǔn)確定位到被參照的主表記錄。同一個(gè)表內(nèi)可以擁有多個(gè)外鍵

外鍵約束通常用于定義兩個(gè)實(shí)體之間的一對(duì)多，一對(duì)一的關(guān)聯(lián)關(guān)系。對(duì)于一對(duì)多的關(guān)聯(lián)關(guān)系，通常在多的一端增加外鍵列。對(duì)于一對(duì)一的關(guān)聯(lián)關(guān)系，則可以選擇任意一方增加外鍵列，增加外鍵列的表被稱為從表。對(duì)于多對(duì)多的關(guān)聯(lián)關(guān)系，則需要額外增加一個(gè)連接表來(lái)記錄他們的關(guān)聯(lián)關(guān)系。

建立外鍵約束同樣可以采用列級(jí)約束語(yǔ)法和表級(jí)約束語(yǔ)法。如果僅對(duì)多帶帶的數(shù)據(jù)列建立外鍵約束，則可以使用列級(jí)約束語(yǔ)法即可；如果需要對(duì)多列組合創(chuàng)建外鍵約束，或者需要為外鍵指定名字，則必須使用表級(jí)約束語(yǔ)

采用列級(jí)約束語(yǔ)法建立外鍵約束直接使用references關(guān)鍵字，references指定該列參照哪個(gè)表，以及參照主表的那一列

# 為了保證從表參照的主表存在，通常應(yīng)該先創(chuàng)建主表
create table teacher_table1
(
    #auto_increment：代表數(shù)據(jù)庫(kù)的自動(dòng)編號(hào)策略，通常用作數(shù)據(jù)庫(kù)的邏輯主鍵
    teacher_id int auto_increment,
    teacher_name varchar(255),
    primary key(teacher_id)
);

create table student_table1
(
student_id int auto_increment primary key,
student_name varchar(255),
# 指定java_teacher參照到teacher_table的teacher_id列
java_teacher int references teacher_table1(teacher_id)
);

但值得指出的是，雖然mysql支持使用列級(jí)約束語(yǔ)法來(lái)建立外鍵約束，但這種列級(jí)約束語(yǔ)法建立的外鍵約束不會(huì)生效，mysql提供這種列級(jí)約束語(yǔ)法僅僅是為了和標(biāo)準(zhǔn)的SQL保持良好的兼容性，如果要使用mysql中的外鍵約束生效，則應(yīng)該使用表級(jí)約束語(yǔ)法

#為了保證從表參照的的主表存在，通常應(yīng)該先建主表
create table teacher_table
(
    #auto_increment：代表數(shù)據(jù)庫(kù)的自動(dòng)編碼策略，通常用作數(shù)據(jù)表的邏輯主鍵
    teacher_id int auto_increment,
    teacher_name varchar(255),
    primary key(teacher_id)
);

create table student_table
(
    student_id int auto_increment primary key,
    studnet_name varchar(255),
    # 指定java_teacher參照到teacher_table的teacher_id列
    java_teacher int,
    foreign key(java_teacher) references teacher_table(teacher_id)
);

如果使用表級(jí)約束語(yǔ)，則需要使用foreign key來(lái)指定本表的外鍵列，并使用references來(lái)指定參照到那個(gè)表，以及參照到主表的哪個(gè)數(shù)據(jù)列。使用表級(jí)約束語(yǔ)法可以為外鍵指定約束名，如果創(chuàng)建外鍵約束時(shí)沒(méi)有指定約束名，則MySQL會(huì)為該外鍵約束名為table_name_ibfk_n，其中table_name是從表的表名，而n是從1開(kāi)始的整數(shù)

如果需要顯式指定外鍵約束的名字，則可以使用constraint來(lái)指定名字

create table teacher_table2
(
    #auto_increment：代表數(shù)據(jù)庫(kù)的自動(dòng)編碼策略，通常用作數(shù)據(jù)表的邏輯主鍵
    teacher_id int auto_increment,
    teacher_name varchar(255),
    primary key(teacher_id)
);

create table student_table2
(
    student_id int auto_increment primary key,
    studnet_name varchar(255),
    # 指定java_teacher參照到teacher_table的teacher_id列
    java_teacher int,
    # 使用表級(jí)約束語(yǔ)法建立外鍵約束，指定外鍵約束的約束名為student_teacher_fk
    constraint student_teacher_fk foreign key(java_teacher) 
    references teacher_table(teacher_id)
);

如果需要建立多列組合的外鍵約束，則必須使用表級(jí)約束語(yǔ)法

create table teacher_table
(
    teacher_name varchar(255),
    teacher_pass varchar(255),
    # 以兩列建立組合主鍵
    primary key(teacher_name, teacher_pass)
);

create table student_table
(
    # 為本表建立主鍵約束
    student_id int auto_increment primary key,
    student_name varchar(255),
    java_teacher_name varchar(255),
    java_teacher_pass varchar(255),
    # 使用表級(jí)約束語(yǔ)法建立外鍵約束，指定兩列的聯(lián)合外鍵
    foreign key(java_teacher_name, java_teacher_pass)
    references teacher_table(teacher_name, teacher_pass)
);

刪除外鍵約束的語(yǔ)法也很簡(jiǎn)單，在alter table后增加"drop foreign key 約束名"字句即可

# 刪除student_table3表上名為student_talbe_ibkf_1的外鍵約束
alter table student_table
drop foreign key student_table_ibkf_1;

增加外鍵約束通常使用add foreign key命令，如下

# 修改student_table的數(shù)據(jù)表，增加外鍵約束
alter table student_table
add foreign key(java_teacher_name, java_teacher_pass)
references teacher_table(teacher_name, teacher_pass);

值得指出的是，外鍵約束不僅可以參照其他表，而且可以參照自身，這種參照自身的情況通常被稱為自關(guān)聯(lián)

# 使用表級(jí)約束語(yǔ)法建立外約束鍵，且直接參照自身
create table foreign_test
(
    foreign_id int auto_increment primary key,
    foreign_name varchar(255),
    # 使用該表的refer_id參照到本表的foreign_id列
    refer_id int,
    foreign key(refer_id) references foreign_test(foreign_id)
);

如果想定義當(dāng)刪除主表紀(jì)錄時(shí)，從表的記錄也會(huì)隨之刪除，則需要在建立外鍵約束后添加on delete cascade或添加 on delete set null。第一種是刪除主表紀(jì)錄時(shí)，把參照該主表記錄的從表記錄全部級(jí)聯(lián)刪除；第二種是指定當(dāng)刪除主表紀(jì)錄時(shí)，把參照該主表記錄的從表記錄外鍵設(shè)為NULL

create table teacher_table
(
    teacher_id int auto_increment,
    teacher_name varchar(255),
    primary key(teacher_id)
);

create table studnet_table
(
    # 為本表建立主鍵約束
    student_id int auto_increment primary key,
    studnet_name varchar(255),
    java_teacher int,
    # 使用表級(jí)約束語(yǔ)法建立外鍵約束，定義級(jí)聯(lián)刪除
    foreign key(java_teacher) references teacher_table(teacher_id)
    on delete cascade 
    # 也可以使用on delete set null
);

建立CHECK約束的語(yǔ)法很簡(jiǎn)單，只要在建表的列定義后增加check即可

create table check_test
(
    emp_id int auto_increment,
    emp_name varchar(255),
    emp_salary decimal,
    # 創(chuàng)建CHECK約束
    check(emp_salary>0)
);

索引是存放在模式（scheme）中的一個(gè)數(shù)據(jù)庫(kù)對(duì)象，雖然索引總是從屬于數(shù)據(jù)表，但它也和數(shù)據(jù)表一樣屬于數(shù)據(jù)庫(kù)對(duì)象。創(chuàng)建索引的唯一作用就是加速對(duì)表的查詢，索引通過(guò)使用快速訪問(wèn)方法來(lái)快速定位數(shù)據(jù)，從而減少磁盤(pán)的I/O

索引作為數(shù)據(jù)庫(kù)對(duì)象，在數(shù)據(jù)字典里獨(dú)立存放，但不能獨(dú)立存在，必須屬于某個(gè)表

創(chuàng)建索引有兩種方式

自動(dòng)：當(dāng)在表上定義主鍵約束、唯一約束和外鍵約束時(shí)，系統(tǒng)會(huì)為該數(shù)據(jù)列自動(dòng)創(chuàng)建對(duì)應(yīng)的索引

手動(dòng)：用戶可以通過(guò)create index...語(yǔ)句來(lái)創(chuàng)建索引

刪除索引也有兩種方式

自動(dòng)：數(shù)據(jù)表被刪除時(shí)，該表上的索引自動(dòng)被刪除

手動(dòng)：用戶可以通過(guò)drop index...語(yǔ)句來(lái)刪除指定數(shù)據(jù)表上的指定索引

創(chuàng)建索引的語(yǔ)法格式如下：

create index index_name
on table_name (column[,column]...);

下面的索引將會(huì)提高對(duì)employees表基于last_name字段的查詢速度

create index emp_last_name_index
on employees(last_name);

同時(shí)對(duì)多列建立索引如

# 下面語(yǔ)句為employees的first_name和last_name兩列同時(shí)建立索引
create index emp_last_name_index
on employees(first_name, last_name);

MySQL中刪除索引需要指定表，采用如下語(yǔ)法格式

drop index 索引名 on 表名

如下SQL語(yǔ)句刪除了employees表中的emp_last_name_idx的索引

drop index emp_last_index on employees;

索引的好處是加速查詢，但索引也有如下壞處：

與書(shū)的目錄相似，當(dāng)數(shù)據(jù)表中的記錄被添加、刪除、修改時(shí)，數(shù)據(jù)庫(kù)系統(tǒng)需要維護(hù)索引，因此有一定的系統(tǒng)開(kāi)銷

存儲(chǔ)索引信息需要一定的磁盤(pán)空間

視圖看上去非常像一個(gè)數(shù)據(jù)表，但它不是數(shù)據(jù)表，因?yàn)樗⒉荒艽鎯?chǔ)數(shù)據(jù)，視圖只是一個(gè)或多個(gè)數(shù)據(jù)表中數(shù)據(jù)的邏輯顯示

使用試圖的優(yōu)點(diǎn)：

可以限制對(duì)數(shù)據(jù)的訪問(wèn)

可以使復(fù)雜的查詢變的簡(jiǎn)單

提供了數(shù)據(jù)的獨(dú)立性

提供了對(duì)相同數(shù)據(jù)的不同顯示

因?yàn)橐晥D只是數(shù)據(jù)表中數(shù)據(jù)的邏輯顯示--也就是一個(gè)查詢結(jié)果，所以創(chuàng)建視圖就是建立視圖名和查詢語(yǔ)句的關(guān)聯(lián)。如下：

create or replace view 視圖名
as
subquery

從上面的語(yǔ)法可以看出，創(chuàng)建、修改視圖都可使用上面語(yǔ)法。上面語(yǔ)法的含義是，如果該視圖不存在，則創(chuàng)建視圖；如果指定的視圖名的視圖已經(jīng)存在，則使用新視圖替換原有的視圖。后面的subquery就是一個(gè)查詢語(yǔ)句，這個(gè)查詢可以非常復(fù)雜。

一旦建立了視圖以后，使用該視圖語(yǔ)使用數(shù)據(jù)表就沒(méi)有上面區(qū)別了，但通常只是查詢視圖數(shù)據(jù)，不會(huì)修改視圖里的數(shù)據(jù)，因?yàn)橐晥D本身就沒(méi)有存儲(chǔ)數(shù)據(jù)

create or replace view view_test
as
select teacher_name, teacher_pass from teacher_table;

大部分時(shí)候，我們不推薦直接改變視圖的數(shù)據(jù)，因?yàn)橐晥D并不存儲(chǔ)數(shù)據(jù)，它只是相當(dāng)于一條命名的查詢語(yǔ)句而已。為了強(qiáng)制不允許改變視圖的數(shù)據(jù)，MySQL允許在創(chuàng)建視圖時(shí)使用with check option字句，使用該字句創(chuàng)建視圖不允許修改如下：

create or replace view view_test
as
select teacher_name form teacher_table
# 指定不允許修改視圖的數(shù)據(jù)
with check option;

刪除視圖使用如下語(yǔ)句：

drop view 視圖名

如下SQL語(yǔ)句刪除了前面剛剛創(chuàng)建的視圖名

drop view view_test;

與DDL操作數(shù)據(jù)庫(kù)對(duì)象不同，DML主要操作數(shù)據(jù)表里的數(shù)據(jù)，使用DML可以完成以下3中任務(wù)：

插入新數(shù)據(jù)

修改已有的數(shù)據(jù)

刪除不需要的數(shù)據(jù)

DML語(yǔ)句由insert into、update、和delete from 3個(gè)命令組成。

insert into用于向數(shù)據(jù)表中插入數(shù)據(jù)。對(duì)于標(biāo)準(zhǔn)的SQL語(yǔ)句而言，每次只能插入一條記錄。insert into語(yǔ)法格式如下：

insert into table_name [(column[,column..])]
values(value,[,vlaue...]);

執(zhí)行插入操作時(shí)，表名后可以用括號(hào)列出所有需要插入值的列名，而value后用括號(hào)列出對(duì)應(yīng)需要插入的值。
例如：

insert into teacher_table2 value ("Vincent");

如果不想在表后用括號(hào)列出所有列，則需要為所有列指定值；如果某列的值不能確定，則為該列分配一個(gè)null值

insert into teacher_table2 
# 使用null代替主鍵列的值
values(null, "Pigeau");

然而此時(shí)，Pigeau記錄的主鍵列的值是2，而不是SQL語(yǔ)句插入的null，因?yàn)樵撝麈I列是自增長(zhǎng)，系統(tǒng)會(huì)自動(dòng)為該列分配值

根據(jù)外鍵約束規(guī)則：外鍵列里的值必須是被參照列里已有的值，所以向從表中插入記錄之前，通常應(yīng)該先向主表中插入記錄，否則從表記錄的外鍵列只能為null。現(xiàn)向從表student_table2中插入記錄

insert into student_table2
# 當(dāng)向外鍵列里插值時(shí)，外鍵列的值必須是被參照列里已有的值
values (null, "Mars", 2)；

在一些特殊的情況下，我們可以使用帶子查詢的插入語(yǔ)句，帶子查詢的插入語(yǔ)句可以一次插入多條記錄

insert into student_table2(student_name)
#　使用子查詢的值來(lái)插入
select teacher_name from teacher_table2;

MySQL允許在values后使用多個(gè)括號(hào)包含多條記錄，表示多條記錄的多個(gè)括號(hào)之間以英文逗號(hào)(,)隔開(kāi)

insert into teacher_table2
# 同時(shí)插入多個(gè)值
values (null, "Paris"),
(null, "Nantes");

update語(yǔ)句用于修改數(shù)據(jù)表的記錄，每次可以修改多條記錄，通過(guò)使用where子句限定修改哪些記錄。沒(méi)有where子句則意味著where表達(dá)式的值總是true，即該表的所有記錄都會(huì)被修改，update語(yǔ)句的語(yǔ)法格式如下:

update teacher_table 
set column1 = value1[,column=value2]...
[WHERE condition];

使用update不僅可以一次修改多條記錄，也可以一次修改多列。修改多列都是通過(guò)在set關(guān)鍵字后使用column1=value1,column2=value2...來(lái)實(shí)現(xiàn)的，修改多列的值之間以英文逗號(hào)(,)隔開(kāi)

update teacher_table2 
set teacher_name = "王";

也可以通過(guò)添加where條件來(lái)指定只修改特定記錄，如下

update teacher_table
set teacher_name = "林"
where teacher_id > 1;

delete from語(yǔ)句用于刪除指定數(shù)據(jù)表的記錄。使用delete from語(yǔ)句刪除時(shí)不需要指定列名，因?yàn)榭偸钦械貏h除。使用delete from語(yǔ)句可以一次刪除多行，刪除哪些行采用where字句限定，只刪除滿足where條件的記錄。沒(méi)有where字句限定將會(huì)把表里的全部記錄刪除

delete from語(yǔ)句的語(yǔ)法格式如下：

delete from table_name
[WHERE condition];

如下SQL語(yǔ)句將會(huì)把student_table2表中的全部記錄全部刪除:

delete from studnet_table2;

也可以使用where條件來(lái)限定只刪除指定記錄，如下SQL語(yǔ)句所示：

delete form teacher_table2 
where teacher_id > 2;

當(dāng)主表記錄被從表記錄參照時(shí)，主表記錄不能被刪除，只有先將從表中參照主表記錄的所有記錄全部刪除后，才可刪除主表記錄。還有一種情況，定義外鍵約束時(shí)定義了主表記錄和從表記錄之間的聯(lián)級(jí)刪除on delete cascade，或者使用on delete null用于指定當(dāng)主表記錄被刪除時(shí)，從表中參照該記錄的從表記錄把外鍵列的值設(shè)為null

select語(yǔ)句的功能就是查詢數(shù)據(jù)。select語(yǔ)句也是SQL語(yǔ)句中功能最豐富的語(yǔ)句，select語(yǔ)句不僅可以執(zhí)行單表查詢，而且可以執(zhí)行多表連接查詢，還可以進(jìn)行子查詢，select語(yǔ)句用于從一個(gè)或多個(gè)數(shù)據(jù)表中選出特定行、特定列的交集

select語(yǔ)句最簡(jiǎn)單的功能如圖所示

單表查詢的select語(yǔ)句的語(yǔ)法如下:

select colimn1 colimn2 ... 
form 數(shù)據(jù)源 
[WHERE condition]

上面的語(yǔ)法格式中的數(shù)據(jù)源可以是表、視圖等。從上面的語(yǔ)法格式中可以看出，select后的列表用于選擇哪些列，where條件用于確定選擇哪些行，只有滿足where條件的記錄才會(huì)被選擇出來(lái)；如果沒(méi)有where條件，則默認(rèn)選出所有行。如果想選擇所有列，則可使用星號(hào)(*)代表所有列

下面的SQL語(yǔ)句將會(huì)選擇出teacher_table表中的所有行、所有列的數(shù)據(jù)。

select * 
from teacher_table;

如果增加where條件，則只選擇符合where條件的記錄，如下SQL語(yǔ)句將選擇出student_table表中java_teacher值大于3的記錄student_name列的值

select student_name 
from student_table 
where java_teacher > 3;

當(dāng)使用select語(yǔ)句進(jìn)行查詢時(shí)，還可以在select語(yǔ)句中使用算術(shù)運(yùn)算符(+、-、*、/)，從而形成算術(shù)表達(dá)式：使用算術(shù)表達(dá)式的規(guī)則如下

對(duì)數(shù)值型數(shù)據(jù)列、變量、常量可以使用算術(shù)運(yùn)算符(+、-、*、/) 創(chuàng)建表達(dá)式

對(duì)日期型數(shù)據(jù)列、變量、常量可以使用部分算術(shù)運(yùn)算符(+、-、)創(chuàng)建表達(dá)式，兩個(gè)日期之間可以進(jìn)行減法運(yùn)算，日期和數(shù)值之間可以進(jìn)行加、減運(yùn)算

運(yùn)算符不僅可以在列和常量、變量之間進(jìn)行運(yùn)算，也可以在兩列之間進(jìn)行運(yùn)算

下面的select語(yǔ)句中使用了算術(shù)運(yùn)算符

# 數(shù)據(jù)列實(shí)際上可當(dāng)成一個(gè)變量
select teacher_id + 5 
from teacher_table;
# 查詢出teacher_table表中teacher_id * 3 大于4的記錄
select * 
from teacher_table 
where teacher_id * 3 > 4;

需要指出的是，select后的不僅可以是數(shù)據(jù)列，也可以是表達(dá)式，還可以是變量、常量等

# 數(shù)據(jù)列實(shí)際上可當(dāng)成一個(gè)變量
select 3*5, 20 
from teacher_table;

SQL語(yǔ)言中算術(shù)符的優(yōu)先級(jí)與java語(yǔ)言的運(yùn)算符優(yōu)先級(jí)完全相同，MySQL使用concat函數(shù)來(lái)進(jìn)行字符串連接運(yùn)算。

# 選擇出teacher_name和"xx"字符串連接后的結(jié)果
select concat(teacher_name, "xx") 
form teacher_table;

對(duì)于MySQL而言，如果在算術(shù)表達(dá)式中使用null，將會(huì)導(dǎo)致整個(gè)算術(shù)表達(dá)式的返回值為null；如果在字符串連接運(yùn)算符中出現(xiàn)null，將會(huì)導(dǎo)致連接后的結(jié)果也是null

select concat(teacher_name, null) 
from teacher_table;

如果不希望直接使用列名作為列標(biāo)題，則可以為數(shù)據(jù)列或表達(dá)式起一個(gè)別名，為數(shù)據(jù)列或表達(dá)式起別名時(shí)，別名緊跟數(shù)據(jù)列，中間以空格隔開(kāi)，或者使用as關(guān)鍵字隔開(kāi)

select teacher_id + 5 as MY_ID 
from teacher_table;

如果列別名中使用特殊字符（例如空格），或者需要強(qiáng)制大小寫(xiě)敏感，都可以通過(guò)為別名添加雙引號(hào)來(lái)實(shí)現(xiàn)

# 可以為選出的列起別名，別名中包括單引號(hào)字符，所以把別名用雙引號(hào)引起來(lái)
select teacher_id + 5 as "MY"id"
from teacher_table;

如果需要選擇多列，并為多列起別名，則多列與列之間以逗號(hào)隔開(kāi)，但列和列名之間以空格隔開(kāi)

select teacher_id + 5 MY_ID, teacher_name 老師名
from teacher_table;

不僅可以為列或表達(dá)式起別名，也可以為表起別名，為表起別名的語(yǔ)法和為列或表達(dá)式起別名的語(yǔ)法完全一樣

select teacher_id + 5 MY_ID, teacher_name 老師名
# 為teacher_table起別名t
from teacher_table t;

列名可以當(dāng)成變量處理，所以運(yùn)算符也可以在多列之間進(jìn)行運(yùn)算

select teacher_id + 5 MY_ID, concat(teacher_name, teacher_id) teacher_name
from teacher_table
where teacher_id * 2 > 3;    

select默認(rèn)會(huì)把所有符合條件的記錄全部選出來(lái)，即使兩行記錄完全一樣。如果想去除重復(fù)行，則可以使用distinct關(guān)鍵字從查詢結(jié)果中清除重復(fù)行，比較下面兩條SQL語(yǔ)句的執(zhí)行結(jié)果：

# 選出所有記錄，包括重復(fù)行
select student_name, java_teacher 
from student_table;

# 去除重復(fù)行
select distinct student_name, java_teacher 
from student_table;

注：使用distinct去除重復(fù)行時(shí)，distinct緊跟select關(guān)鍵字，它的作用是去除后面字段組合的重復(fù)值，而不管對(duì)應(yīng)對(duì)應(yīng)記錄在數(shù)據(jù)庫(kù)是否重復(fù)

前面已經(jīng)看到了where字句的作用：可以控制只選擇指定的行。因?yàn)閣here字句里包含的是一個(gè)條件表達(dá)式，所以可以使用>、>=、<、<=、=和<>等基本的比較運(yùn)算符。SQL中的比較運(yùn)算符不僅可以比較數(shù)值之間的大小，也可以比較字符串、日期之間的大小

SQL判斷兩個(gè)值是否相等的比較運(yùn)算符是單等號(hào)=，判斷不等的運(yùn)算符是<>；SQL中的賦值運(yùn)算符不是等號(hào)，而是冒號(hào)等號(hào)(:=)

SQL支持的特殊比較運(yùn)算符

下面的SQL語(yǔ)句選出student_id大于等于2，且小于等于4的所有記錄.

select * 
from student_table 
where student_id between 2 and 4;

# 選出java_teacher小于等于2，student_id大于等于2的所有記錄
select * 
from student_table 
where 2 between java_teacher and student_id;

使用in比較運(yùn)算時(shí)，必須在in后的括號(hào)里列出一個(gè)或多個(gè)值，它要求指定列必須與in括號(hào)里任意一個(gè)值相等

# 選出student_id、java_teacher列的值為2或4的所有記錄
select * 
from student_table 
where student_id in(2,4);

與之類似的是，in括號(hào)里的值既可以是常量，也可以是變量或者列名

# 選出student_id、java_teacher列的值為2的所有記錄
select * 
from student_table 
where 2 in(student_id,java_teacher);

like運(yùn)算符主要用于進(jìn)行模糊查詢，例如，若要查詢名字以“孫”開(kāi)頭的所有記錄，這就需要用到迷糊查詢，在模糊查詢中需要使用like關(guān)鍵字。SQL語(yǔ)句中可以使用兩個(gè)通配符：下劃線(_)和百分號(hào)(%)，其中下劃線可以代表一個(gè)任意的字符，百分號(hào)可以代表任意多個(gè)字符。如下SQL語(yǔ)句將查詢出所有學(xué)生中名字以"孫"開(kāi)頭的學(xué)生

select * 
from student_table
where student_name like "孫%";

下面的SQL語(yǔ)句將查出名字為兩個(gè)字符的所有學(xué)生

select * 
from student_table
# 下面使用兩個(gè)下劃線代表來(lái)個(gè)字符
where student_name like "__";

在某些特殊情況下，查詢的條件里需要使用下劃線或百分號(hào)，不希望SQL把下劃線和百分號(hào)當(dāng)成通配符使用，這就需要使用轉(zhuǎn)義字符，MySQL使用反斜線(/)作為轉(zhuǎn)義字符

# 選出所有名字以下劃線開(kāi)頭的學(xué)生
select 8
from student_table
where student_name like "\_%";

is null 用于判斷某些值是否為空，判斷是否為空不能用=null來(lái)判斷，因?yàn)镾QL中null=null返回null。如下SQL語(yǔ)句將選擇出student_table表中student_name為null的所有記錄

select * 
from student_table
where student_name is null;

如果where字句后面有多個(gè)條件需要組合，SQL提供了and和or邏輯運(yùn)算符來(lái)組合2個(gè)條件，并提供了not來(lái)對(duì)邏輯表達(dá)式求否，如下SQL語(yǔ)句將選出學(xué)生名字為2個(gè)字符，且student_id 大于3的所有記錄。

select * 
from student_table
where student_name like "__" and studnent_id > 3;

下面的SQL語(yǔ)句將選出student_table表中姓名不以下劃線開(kāi)頭的所有記錄。

select * 
from student_table
# 使用not對(duì)where條件取否
where not student_name like "/_%"; 

SQL中比較運(yùn)算符、邏輯運(yùn)算符的優(yōu)先級(jí)

執(zhí)行查詢后的結(jié)果默認(rèn)按插入順序排序；如果需要在查詢結(jié)果按某列值的大小進(jìn)行排序，則可以使用order by字句

ORDER BY 語(yǔ)句用于根據(jù)指定的列對(duì)結(jié)果集進(jìn)行排序。ORDER BY 語(yǔ)句默認(rèn)按照升序?qū)τ涗涍M(jìn)行排序。如果您希望按照降序?qū)τ涗涍M(jìn)行排序，可以使用 DESC 關(guān)鍵字

order by字句的語(yǔ)法如下：

order by column_name1 [desc], column_name...

進(jìn)行排序時(shí)默認(rèn)按升序排序排列，如果強(qiáng)制按降序排序，則需要在列后使用desc關(guān)鍵字(與之對(duì)應(yīng)的是asc關(guān)鍵字，用不用該關(guān)鍵字的效果完全一樣，因?yàn)槟J(rèn)是按升序排列)。上面語(yǔ)法中設(shè)定排序列時(shí)可采用列名、序列名和列別名。如下SQL語(yǔ)句選出student_table表中的所有記錄，選出后按java_teacher列的升序排列、

select * 
from student_table
order by java_teacher;

如果需要按多列排序，則每列的asc、desc必須多帶帶設(shè)定。如果指定了多個(gè)排序列，則第一個(gè)排序列是首要排序列，只有當(dāng)?shù)谝涣兄写嬖诙鄠€(gè)相同值時(shí)，第二個(gè)排序才會(huì)起作用。如果SQL語(yǔ)句先按java_teacher列的降序排序,當(dāng)java_teacher列的值相同按student_name列的升序排列

select * 
from student_table
order by java_teacher desc, student_name;

每個(gè)數(shù)據(jù)庫(kù)都會(huì)在標(biāo)準(zhǔn)的SQL基礎(chǔ)上擴(kuò)展一些函數(shù)，這些函數(shù)用于進(jìn)行數(shù)據(jù)處理或復(fù)雜計(jì)算，他們通常對(duì)一組數(shù)據(jù)進(jìn)行計(jì)算，得到最終需要的輸出結(jié)果。函數(shù)一般都會(huì)有一個(gè)或者多個(gè)輸入，這些輸入被稱為函數(shù)的參數(shù)，函數(shù)內(nèi)部會(huì)對(duì)這些參數(shù)進(jìn)行判斷和計(jì)算，最終只有一個(gè)值作為返回值。函數(shù)可以出現(xiàn)在SQL語(yǔ)句中的各個(gè)位置，比較常用的位置是select之后的where子句中

根據(jù)函數(shù)對(duì)多行數(shù)據(jù)的處理方式，函數(shù)被分為單行函數(shù)和多行函數(shù)，單行函數(shù)對(duì)每行輸入值多帶帶計(jì)算，每行得到一個(gè)計(jì)算結(jié)果返回給用戶；多行函數(shù)對(duì)多行輸入值整體計(jì)算，最后只會(huì)得到一個(gè)結(jié)果

SQL中的函數(shù)和java語(yǔ)言中的方法有點(diǎn)相似，但SQL中的函數(shù)是獨(dú)立的程序單元，也就是說(shuō)，調(diào)用函數(shù)時(shí)無(wú)需使用任何類、對(duì)象作為調(diào)用者，而是直接執(zhí)行函數(shù)。如下：

function_name(arg1,arg2...)

多行函數(shù)也稱為聚集函數(shù)、分組函數(shù)，主要用于完成一些統(tǒng)計(jì)功能，在大部分?jǐn)?shù)據(jù)庫(kù)中基本相同。但不同數(shù)據(jù)庫(kù)中的單行函數(shù)差別非常大，MySQL中的單行函數(shù)具有如下特征

單行函數(shù)的參數(shù)可以是變量、常數(shù)或數(shù)據(jù)列。單行函數(shù)可以接收多個(gè)參數(shù)，但只返回一個(gè)值

單行函數(shù)會(huì)對(duì)每行多帶帶起作用，每行(可能包括多個(gè)參數(shù))返回一個(gè)結(jié)果

使用單行函數(shù)可以改變參數(shù)的數(shù)據(jù)類型。單行函數(shù)支持嵌套使用，即內(nèi)層函數(shù)的返回值是外層函數(shù)的參數(shù)

MySQL的單行函數(shù)分類如圖所示

MySQ數(shù)據(jù)庫(kù)的數(shù)據(jù)類型大致分為數(shù)值型、字符型、和日期時(shí)間型。所以mysql分別提供了對(duì)應(yīng)的函數(shù)。轉(zhuǎn)換函數(shù)主要負(fù)責(zé)完成類型轉(zhuǎn)換，其他函數(shù)又大致分為如下幾類

位函數(shù)

流程控制函數(shù)

加密解密函數(shù)

信息函數(shù)

# 選出teacher_table表中teacher_name列的字符長(zhǎng)度
select char_length(teacher_name) 
from teacher_table;

# 計(jì)算teacher_name列的字符長(zhǎng)度的sin值
select sin(char_length(teacher_name)) 
from teacher_table;

# 為指定日期添加一定的時(shí)間，在這種用法下interval是關(guān)鍵字，需要一個(gè)數(shù)值還有一個(gè)單位
select DATE_ADD("1998-01-02", interval 2 MONTH);

# 獲取當(dāng)前日期
select CURDATE();

# 獲取當(dāng)前時(shí)間
select curtime();

# 下面的MD5是MD5加密函數(shù)
select MD5("testing");

MySQL提供了如下幾個(gè)處理null的函數(shù)

ifnull(expr1, expr2)：如果expr1為null，則返回expr2，否則返回expr1

nullif(expr1, expr2)：如果expr1和expr2相等，則返回null，否則返回expr1

if(expr1, expr2, expr3)：有點(diǎn)類似于?:三目運(yùn)算符，如果expr1為true，不等于0，且不等于null，則返回expr2，否則返回expr3

isnull(expr1)：判斷expr1是否為null，如果為null則返回true，否則返回false

# 如果student_name列為null，則返回"沒(méi)有名字"
select ifnull(student_name, "沒(méi)有名字")
from student_table;

# 如果CTO_name列為"吳局"，則返回null
select nullif(CTO_name, "吳局")
from CTO_table;

# 如果student_name列為null，則返回"沒(méi)有名字"，否則返回"有名字"
select if(isnull(student_name), "沒(méi)有名字", "有名字")
from student_table; 

case函數(shù)，流程控制函數(shù)。case函數(shù)有兩個(gè)用法

case value
when compare_value1 then result1
when compare_value2 then result2
...
else result
end

case函數(shù)用value和后面的compare_value1、compare_value2、...依次進(jìn)行比較，如果value和指定的compare_value1相等，則返回對(duì)應(yīng)的result1，否則返回else后的result

# 如果java_teacher為1，則返回"Java老師"，為2返回"Spring老師"，否則返回"其他老師"
select student_name, case java_teacher
when 1 then "Java老師"
when 2 then "Spring老師"
else "其他老師"
end
from student_table;

case
when condition1 then result1
when condition2 then result2
...
else result
end   

condition返回boolean值的條件表達(dá)式

# id小于3的為初級(jí)工程師，3~6為中級(jí)工程師，其他為高級(jí)工程師
select employees_name, case
when employees_id <= 3 then "初級(jí)工程師"
when employees_id <= 6 then "中級(jí)工程師"
else "高級(jí)工程師"
end
from employees_table;

組函數(shù)也就是前面提到的多行函數(shù)，組函數(shù)是將一組作為整體計(jì)算，每組記錄返回一個(gè)結(jié)果，而不是每條記錄返回一個(gè)結(jié)果

avg([distinct|all]expr)：計(jì)算多行expr平均值，其中expr可以是變量、常量或者數(shù)據(jù)列，但其數(shù)據(jù)類型必須是數(shù)值型。使用distinct表明不計(jì)算重復(fù)值；all表明需要計(jì)算重復(fù)值

count({*|[distinct|all] expr})：計(jì)算多行expr的總條數(shù)，其中expr可以是變量、常量或者數(shù)據(jù)列，但其數(shù)據(jù)類型必須是數(shù)值型。用星號(hào)（*）表示統(tǒng)計(jì)該表內(nèi)的記錄行數(shù)

max(expr)：計(jì)算多行expr的最大值

min(expr)：計(jì)算多行expr的最小值

sum([distanct|all]expr)：計(jì)算多行expr的總和

# 計(jì)算student_table表中的記錄條數(shù)
select count(*)

# 計(jì)算java_teacher列總共有多少個(gè)值
select count(distinct java_teacher)

# 統(tǒng)計(jì)所有student_id 的總和
select sum(student_id)

# 計(jì)算的結(jié)果是20 * 記錄的行數(shù)
select sum(20)

# 選出student_table表中student_id最大的值
select max(student_id)

# 選出student_table表中student_id最小的值
select min(student_id)

# 因?yàn)閟um里的expr是常量23，所以每行的值都相同
# 使用distinct強(qiáng)制不計(jì)算重復(fù)值，所以下面計(jì)算結(jié)果為23
select sum(distinct 23)

# 使用count統(tǒng)計(jì)記錄行數(shù)，null不會(huì)被計(jì)算在內(nèi)
select count(student_name)

# 對(duì)于可能出現(xiàn)null的列，可以使用ifnull函數(shù)來(lái)處理該列
# 計(jì)算java_teacher列所有記錄的平均值
select avg(ifnull(java_teacher, 0))

from student_table;

# distinct和*不可同時(shí)使用

組函數(shù)會(huì)把所有記錄當(dāng)成一組，為了對(duì)記錄進(jìn)行顯式分組，可以在select語(yǔ)句后使用group by子句后通常跟一個(gè)或多個(gè)列名，表明查詢結(jié)果根據(jù)一列或多列進(jìn)行分組——當(dāng)一列或多列組合的值完全相同時(shí)，系統(tǒng)會(huì)把這些記錄當(dāng)成一組

SQL GROUP BY 語(yǔ)法

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

# count(*)將會(huì)對(duì)每組得到一個(gè)結(jié)果
select count(*)
from student_table
# 將java_teacher列值相同的記錄當(dāng)成一組
group by java_teacher;

如果對(duì)多列進(jìn)行分組，則要求多列的值完全相同才會(huì)被當(dāng)成一組

# count(*)將會(huì)對(duì)每組得到一個(gè)結(jié)果
select count(*)
from student_table
# 將java_teacher、student_name兩列的值完全相同時(shí)才會(huì)被當(dāng)成一組
group by java_teacher, student_name;

如果需要對(duì)分組進(jìn)行過(guò)濾，則應(yīng)該使用having子句，having子句后面也是一個(gè)條件表達(dá)式，只有滿足該條件表達(dá)式的分組才會(huì)被選出來(lái)。having子句和where子句非常容易混淆，它們都有過(guò)濾功能，但它們有如下區(qū)別

不能在where子句中過(guò)濾組，where子句僅用于過(guò)濾行。過(guò)濾組必須使用having子句

不能在where子句中使用組函數(shù)，having子句才可使用組函數(shù)

在SQL中增加HAVING子句原因是，WHERE關(guān)鍵字無(wú)法與合計(jì)函數(shù)一起使用
SQL HAVING 語(yǔ)法

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

select *
from student_table
group by java_teacher
# 對(duì)組進(jìn)行過(guò)濾
having count(*) >2;

以下book與student數(shù)據(jù)表：

交叉連接無(wú)須任何連接條件。返回左表中的所有行，左表中的每一行與右表中的所有行組合。交叉聯(lián)接也稱作笛卡爾積

select * 
from book as a 
# cross join交叉連接，相當(dāng)于廣義笛卡爾積
cross join stu as b 
order by a.id

自然連接會(huì)以兩個(gè)表中的同名列作為連接條件；如果兩個(gè)表沒(méi)有同名列，則自然連接與交叉連接效果完全一樣——因?yàn)闆](méi)有連接條件。

select s.*, teacher_name
from student_table s
# natural join 自然連接使用兩個(gè)表中的同名列作為連接條件
natural join teacher_table t;

在連接條件中使用等于(=)運(yùn)算符比較被連接列的列值，但它使用選擇列表指出查詢結(jié)果集合中所包括的列，并刪除連接表中的重復(fù)列

using子句可以指定一列或多列，用于顯示指定兩個(gè)表中的同名列作為連接條件。假設(shè)兩個(gè)表中有超過(guò)一列的同名列，如果使用natural join，則會(huì)把所有的同名列當(dāng)成連接條件；使用using子句，就可顯示指定使用哪些同名列作為連接條件

select s.*, teacher_name
from student_table s
# join連接另一個(gè)表
join teacher_table t
using(teacher id);

最常用的的連接方式，而且每個(gè)on子句只指定一個(gè)連接條件。這意味著：如果需要進(jìn)行N表連接，則需要有N-1個(gè)join...on對(duì)

select s.*, teacher_name
from student_table s
# join連接另一個(gè)表
join teacher_table t
# 使用on來(lái)指定連接條件
on s.java_teacher = t.teacher_id;

on子句的連接條件除了等值條件之外，也可以是非等值條件

select s.*, teacher_name
from student_table s
# join連接另一個(gè)表
join teacher_table t
# 使用on來(lái)指定連接條件
on s.java_teacher > t.teacher_id;

等值連接：在連接條件中使用等于號(hào)(=)運(yùn)算符比較被連接列的列值，其查詢結(jié)果中列出被連接表中的所有列，包括其中的重復(fù)列

不等值連接：在連接條件使用除等于運(yùn)算符以外的其它比較運(yùn)算符比較被連接的列的列值。這些運(yùn)算符包括>、>=、<=、<、!>、!<和<>

這三種外連接分別使用left[outer]join、right[outer]join和full[outer]join，這三種外連接的連接條件一樣通過(guò)on子句來(lái)指定，既可以是等值連接條件，也可以是非等值連接條件

是以左表為基準(zhǔn)，將a.stuid = b.stuid的數(shù)據(jù)進(jìn)行連接，然后將左表沒(méi)有的對(duì)應(yīng)項(xiàng)顯示，右表的列為null

select * 
from book as a 
left join stu as b 
on a.sutid = b.stuid

是以右表為基準(zhǔn)，將a.stuid = b.stuid的數(shù)據(jù)進(jìn)行連接，然以將右表沒(méi)有的對(duì)應(yīng)項(xiàng)顯示，左表的列為null

select * 
from book as a 
right join stu as b 
on a.sutid = b.stuid  

完整外部聯(lián)接返回左表和右表中的所有行。當(dāng)某行在另一個(gè)表中沒(méi)有匹配行時(shí)，則另一個(gè)表的選擇列表列包含空值。如果表之間有匹配行，則整個(gè)結(jié)果集行包含基表的數(shù)據(jù)值

子查詢就是在查詢語(yǔ)句中嵌套另一個(gè)查詢，子查詢可以支持多層嵌套。對(duì)于一個(gè)普通的查詢語(yǔ)句而言，子查詢可以出現(xiàn)在兩個(gè)位置

form語(yǔ)句后當(dāng)成數(shù)據(jù)表，這種用法也被稱為行內(nèi)視圖，因?yàn)樵撟硬樵兊膶?shí)質(zhì)就是一個(gè)臨時(shí)視圖

where條件后作為過(guò)濾條件的值

使用子查詢時(shí)的注意點(diǎn)

子查詢要用括號(hào)括起來(lái)

把子查詢作為數(shù)據(jù)表時(shí)（出現(xiàn)在from后），可為其起別名，作為前綴來(lái)限定數(shù)據(jù)列時(shí)，必須給子查詢起別名

把子查詢作為過(guò)濾條件時(shí)，將子查詢放在比較運(yùn)算符的右邊，可增強(qiáng)查詢的可讀性

把子查詢作為過(guò)濾條件時(shí)，單行子查詢使用單行運(yùn)算符，多行子查詢使用多行運(yùn)算符

select *
# 把子查詢當(dāng)成數(shù)據(jù)表
from (select * from student_table) t
where t.java_teacher > 1;

把子查詢當(dāng)成where條件中的值，如果子查詢返回單行、單列值，則被當(dāng)成一個(gè)標(biāo)量值使用，也就可以使用單行記錄比較運(yùn)算符

select *
from student_table
where java_teacher > 
# 返回單行、單列的子查詢可以當(dāng)成標(biāo)量值使用
(select teacher_id 
from teacher_table 
where teacher_name = "Pigeau");

如果子查詢返回多個(gè)值，則需要使用in、any和all等關(guān)鍵字

in可以多帶帶使用，此時(shí)可以把子查詢返回的多個(gè)值當(dāng)成一個(gè)值列表

select *
from student_table
where student_id in
(select teacher_id
from teacher_table);

any、all可與>、>=、<、<=、<>、=等運(yùn)算符結(jié)合使用。與any結(jié)合表示大于、大于等于、小于、小于等于、不等于、等于其中任意一個(gè)值；與all結(jié)合表示大于、大于等于、小于、小于等于、不等于、等于全部值

=any與in的作用相同

select *
from student_table
where student_id =
any(select teacher_id
from teacher_table);

ANY只要大于值列表中的最小值即可。ALL要求大于值列表中的最大值

# 選出student_table表中student_id大于teacher_table表中所有teacher_id的記錄
select *
from student_table
where student_id >
all(select teacher_id
from teacher_table);

還有一種子查詢可以返回多行、多列，此時(shí)where子句中應(yīng)該有對(duì)應(yīng)的數(shù)據(jù)列，并使用圓括號(hào)將多個(gè)數(shù)據(jù)列組合起來(lái)

select *
from student_table
where (student_id, student_name)
=any(select teacher_id, teacher_name
from teacher_table);

為了對(duì)兩個(gè)結(jié)果集進(jìn)行集合運(yùn)算，這兩個(gè)結(jié)果集必須滿足如下條件

兩個(gè)結(jié)果集所包含的數(shù)據(jù)列的數(shù)量必須相等

兩個(gè)結(jié)果集所包含的數(shù)據(jù)列的數(shù)據(jù)類型也必須一一對(duì)應(yīng)

union運(yùn)算的語(yǔ)法格式

select 語(yǔ)句 union select 語(yǔ)句

查詢所有教師的信息和主鍵小于4的學(xué)生信息

# 查詢結(jié)果包含兩列，第一列為int類型，第二列為varchar類型
select * from teacher_table
union
select student_id , student_name from student_table;

minus運(yùn)算的語(yǔ)法格式,MySQL實(shí)則不支持這種運(yùn)算

select 語(yǔ)句 minus select 語(yǔ)句

從所有學(xué)生記錄中“減去”老師記錄的ID相同、姓名相同的記錄，則可進(jìn)行如下的minus運(yùn)算

select student_id, student_name from student_table
minus
# 兩個(gè)結(jié)果集的數(shù)據(jù)列的數(shù)量相等，數(shù)據(jù)類型一一對(duì)應(yīng)，可以進(jìn)行minus運(yùn)算
select teacher_id, teacher_name from teacher_table;

intersect運(yùn)算的語(yǔ)法格式

select 語(yǔ)句 intersect select 語(yǔ)句

找出學(xué)生記錄中與老師記錄中的ID相同、姓名相同的記錄

select student_id, student_name from student_table
intersect
# 兩個(gè)結(jié)果集的數(shù)據(jù)列的數(shù)量相等，數(shù)據(jù)類型一一對(duì)應(yīng)，可以進(jìn)行intersect運(yùn)算
select teacher_id, teacher_name from teacher_table;