1. 了解SQL

1.1 數(shù)據(jù)庫基礎(chǔ)

? 學(xué)習(xí)到目前這個(gè)階段白嘁，我們就需要以某種方式與數(shù)據(jù)庫打交道翔怎。在深入學(xué)習(xí)MySQL及其SQL語言之前配名，應(yīng)該先對(duì)數(shù)據(jù)庫及數(shù)據(jù)庫技術(shù)的某些基本概念有所了解鹉梨。
? 你可能還沒意識(shí)到蠢终，其實(shí)你一直在使用數(shù)據(jù)庫序攘。

• 你使用QQ和微信查找好友的時(shí)候，你就在使用數(shù)據(jù)庫寻拂。
• 你在百度上進(jìn)行搜索程奠，也是在使用數(shù)據(jù)庫。
• 你每天來學(xué)習(xí)進(jìn)行打卡的指紋驗(yàn)證兜喻，也是在使用數(shù)據(jù)庫梦染。
• 自動(dòng)取款機(jī)上使用ATM卡，還是在使用數(shù)據(jù)庫。
• 帕识。泛粹。。肮疗。晶姊。太多太多。伪货。们衙。。
  其實(shí)我們做的所有產(chǎn)品（包括客戶端和服務(wù)器端）最終都是建立在數(shù)據(jù)上碱呼。我們每一款軟件最核心蒙挑、最寶貴的就是數(shù)據(jù)。

1.1.1 數(shù)據(jù)庫

數(shù)據(jù)庫（database）是按照數(shù)據(jù)結(jié)構(gòu)來組織愚臀、存儲(chǔ)和管理數(shù)據(jù)的倉庫（通常是一個(gè)文件或一組文件）忆蚀。
? 理解數(shù)據(jù)庫的一種最簡單的辦法就是將其想象為一個(gè)文件柜，此文件柜是一個(gè)存放數(shù)據(jù)的物理位置姑裂。

• 注意：有些人通常使用數(shù)據(jù)庫這個(gè)術(shù)語來代表他們使用的數(shù)據(jù)庫軟件馋袜，這是不正確的。確切的說舶斧，數(shù)據(jù)庫軟件應(yīng)稱為DBMS（數(shù)據(jù)庫管理系統(tǒng)）欣鳖。數(shù)據(jù)庫是通過DBMS創(chuàng)建和操作的容器。數(shù)據(jù)庫可以是保存在硬件設(shè)備上的文件茴厉，但也可以不是泽台。對(duì)我們來說數(shù)據(jù)庫究竟是文件還是其他什么東西并不重要，因?yàn)槲覀儾⒉恢苯釉L問數(shù)據(jù)庫呀忧，我們使用的是DBMS师痕，它替我們?cè)L問數(shù)據(jù)庫。

1.1.2 表

表（table）是數(shù)據(jù)庫中存儲(chǔ)數(shù)據(jù)的基本單位而账。

image

1.1.3 列

列（column）表中的一個(gè)字段。所有的表都是由一個(gè)或多個(gè)列組成因篇。

1.1.4 數(shù)據(jù)類型

數(shù)據(jù)類型（datatype）每個(gè)列都有相應(yīng)的數(shù)據(jù)類型泞辐，用來限制該列存儲(chǔ)的數(shù)據(jù)。

1.1.5 行

行（row）表中的一個(gè)（行）記錄竞滓。
? 表中的數(shù)據(jù)是按行存儲(chǔ)的咐吼，所保存的每個(gè)記錄存儲(chǔ)在自己的行內(nèi)，如果將表想象為網(wǎng)格商佑，網(wǎng)格中垂直的列為表列锯茄，水平行為表行。

1.1.6 主鍵

主鍵（primary key）一列（或一組列），其值能夠唯一區(qū)分表中的每一行肌幽。
? 表中每一行都應(yīng)該有可以唯一標(biāo)識(shí)自己的一列（或一組列）晚碾。一個(gè)顧客表中可以使用顧客編碼列，而訂單表可以使用訂單ID等喂急。一個(gè)表中沒有主鍵的話格嘁，更新或刪除表中特定行很困難，因?yàn)闆]有安全的方法保證只涉及相關(guān)的行廊移。
?主鍵的規(guī)則表中的任何列都可以作為主鍵糕簿，只要它滿足一下條件：

• 任何兩行都不具有相同的主鍵值（每一行的主鍵值是唯一的）。
• 每個(gè)行都必須具有一個(gè)主鍵值（主鍵不允許null值）
  主鍵的好習(xí)慣除了強(qiáng)制的規(guī)則外狡孔，應(yīng)該堅(jiān)持的幾個(gè)普遍認(rèn)可的好習(xí)慣：
• 不更新主鍵列中的值
• 不重用主鍵列的值
• 不在主鍵列中使用可能會(huì)更改的值

MySQL數(shù)據(jù)庫操作

MySql中添加用戶,新建數(shù)據(jù)庫,用戶授權(quán),刪除用戶,修改密碼(注意每行后邊都跟個(gè);表示一個(gè)命令語句結(jié)束):

1.新建用戶

1.1 登錄MYSQL：

@>mysql -u root -p

@>密碼

1.2 創(chuàng)建用戶：

mysql> CREATE USER 'username'@'host' [IDENTIFIED BY 'PASSWORD'] 其中密碼是可選項(xiàng)懂诗；

例子：CREATE USER 'john'@'192.168.189.71' IDENTIFIED BY "123";

CREATE USER 'john'@'192.168.189.%' IDENTIFIED BY "123";

CREATE USER 'john'@' %' ;

1.3 然后登錄一下：

mysql>exit;

@>mysql -u test -p

@>輸入密碼

mysql>登錄成功

2.為用戶授權(quán)

授權(quán)格式：grant 權(quán)限 on 數(shù)據(jù)庫.* to 用戶名@登錄主機(jī) identified by "密碼";

2.1 登錄MYSQL（有ROOT權(quán)限），這里以ROOT身份登錄：

@>mysql -u root -p

@>密碼

2.2 首先為用戶創(chuàng)建一個(gè)數(shù)據(jù)庫(testDB)：

mysql>CREATE DATABASEtestDBDEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

2.3 授權(quán)test用戶擁有testDB數(shù)據(jù)庫的所有權(quán)限（某個(gè)數(shù)據(jù)庫的所有權(quán)限）：

mysql>grant all privileges on testDB.* to test@localhost identified by '1234';

mysql>flush privileges;//刷新系統(tǒng)權(quán)限表

格式：grant 權(quán)限 on 數(shù)據(jù)庫.* to 用戶名@登錄主機(jī) identified by "密碼";

2.4 如果想指定部分權(quán)限給一用戶苗膝，可以這樣來寫:

mysql>grant select,update on testDB.* to test@localhost identified by '1234';

mysql>flush privileges; //刷新系統(tǒng)權(quán)限表

2.5 授權(quán)test用戶擁有所有數(shù)據(jù)庫的某些權(quán)限：

mysql>grant select,delete,update,create,drop on_._to test@"%" identified by"1234";

 //test用戶對(duì)所有數(shù)據(jù)庫都有select,delete,update,create,drop 權(quán)限殃恒。

//@"%" 表示對(duì)所有非本地主機(jī)授權(quán)，不包括localhost荚醒。（localhost地址設(shè)為127.0.0.1芋类，如果設(shè)為真實(shí)的本地地址，不知道是否可以界阁，沒有驗(yàn)證侯繁。）

//對(duì)localhost授權(quán)：加上一句grant all privileges on testDB.* to test@localhostidentified by '1234';即可。

3. 刪除用戶

@>mysql -u root -p

@>密碼

mysql>use mysql; // 進(jìn)入這個(gè)數(shù)據(jù)庫

mysql>Delete FROM user Where User='test' and Host='localhost';

mysql>flush privileges;

mysql>drop database testDB; //刪除用戶的數(shù)據(jù)庫

刪除賬戶及權(quán)限：>drop user 用戶名@'%';

>drop user 用戶名@ localhost;

4. 修改指定用戶密碼

@>mysql -u root -p

@>密碼

mysql>update mysql.user set password=password('新密碼') where User="test"and Host="localhost";

mysql>flush privileges;

5. 列出所有數(shù)據(jù)庫

mysql>show database;

6. 切換數(shù)據(jù)庫

mysql>use '數(shù)據(jù)庫名';

7. 列出所有表

mysql>show tables;

8. 顯示數(shù)據(jù)表結(jié)構(gòu)泡躯，顯示表定義

mysql>desc 表名;

mysql>show create table 表名;

9. 刪除數(shù)據(jù)庫和數(shù)據(jù)表

mysql>drop database 數(shù)據(jù)庫名;

mysql>drop table 數(shù)據(jù)表名;

MySQL 數(shù)據(jù)類型

MySQL中定義數(shù)據(jù)字段的類型對(duì)你數(shù)據(jù)庫的優(yōu)化是非常重要的贮竟。
MySQL支持多種類型，大致可以分為三類：數(shù)值较剃、日期/時(shí)間和字符串(字符)類型咕别。

數(shù)值類型

MySQL支持所有標(biāo)準(zhǔn)SQL數(shù)值數(shù)據(jù)類型。
這些類型包括嚴(yán)格數(shù)值數(shù)據(jù)類型(INTEGER写穴、SMALLINT惰拱、DECIMAL和NUMERIC)，以及近似數(shù)值數(shù)據(jù)類型(FLOAT啊送、REAL和DOUBLE PRECISION)偿短。
關(guān)鍵字INT是INTEGER的同義詞，關(guān)鍵字DEC是DECIMAL的同義詞馋没。
BIT數(shù)據(jù)類型保存位字段值昔逗，并且支持MyISAM、MEMORY篷朵、InnoDB和BDB表勾怒。
作為SQL標(biāo)準(zhǔn)的擴(kuò)展婆排，MySQL也支持整數(shù)類型TINYINT、MEDIUMINT和BIGINT笔链。下面的表顯示了需要的每個(gè)整數(shù)類型的存儲(chǔ)和范圍段只。

日期和時(shí)間類型

表示時(shí)間值的日期和時(shí)間類型為DATETIME、DATE卡乾、TIMESTAMP翼悴、TIME和YEAR。
每個(gè)時(shí)間類型有一個(gè)有效值范圍和一個(gè)"零"值幔妨，當(dāng)指定不合法的MySQL不能表示的值時(shí)使用"零"值鹦赎。
TIMESTAMP類型有專有的自動(dòng)更新特性，將在后面描述误堡。

字符串類型

字符串類型指CHAR古话、VARCHAR、BINARY锁施、VARBINARY陪踩、BLOB、TEXT悉抵、ENUM和SET肩狂。該節(jié)描述了這些類型如何工作以及如何在查詢中使用這些類型。
CHAR和VARCHAR類型類似姥饰，但它們保存和檢索的方式不同傻谁。它們的最大長度和是否尾部空格被保留等方面也不同。在存儲(chǔ)或檢索過程中不進(jìn)行大小寫轉(zhuǎn)換列粪。
BINARY和VARBINARY類類似于CHAR和VARCHAR审磁，不同的是它們包含二進(jìn)制字符串而不要非二進(jìn)制字符串。也就是說岂座，它們包含字節(jié)字符串而不是字符字符串态蒂。這說明它們沒有字符集，并且排序和比較基于列值字節(jié)的數(shù)值值费什。
BLOB是一個(gè)二進(jìn)制大對(duì)象钾恢，可以容納可變數(shù)量的數(shù)據(jù)。有4種BLOB類型：TINYBLOB鸳址、BLOB赘那、MEDIUMBLOB和LONGBLOB。它們只是可容納值的最大長度不同氯质。
有4種TEXT類型：TINYTEXT、TEXT祠斧、MEDIUMTEXT和LONGTEXT闻察。這些對(duì)應(yīng)4種BLOB類型，有相同的最大長度和存儲(chǔ)需求。

表操作

創(chuàng)建表

MySQL不僅用于表數(shù)據(jù)操縱辕漂，而且還可以用來執(zhí)行數(shù)據(jù)庫和表的所有操作呢灶，包括表本身的創(chuàng)建和處理。
通用語法：CREATE TABLE table_name (column_name column_type);
create table test_table(test_id INT NOT NULL AUTO_INCREMENT,test_title VARCHAR(100) NOT NULL,test_author VARCHAR(40) NOT NULL DEFAULT 'larry',test_date DATE,PRIMARY KEY ( test_id ));
在這里钉嘹，一些數(shù)據(jù)項(xiàng)需要解釋：

• 字段使用NOT NULL屬性鸯乃，是因?yàn)槲覀儾幌Ｍ@個(gè)字段的值為NULL。 因此跋涣，如果用戶將嘗試創(chuàng)建具有NULL值的記錄缨睡，那么MySQL會(huì)產(chǎn)生錯(cuò)誤。
• 字段的AUTO_INCREMENT屬性告訴MySQL自動(dòng)增加id字段下一個(gè)可用編號(hào)陈辱。
• DEFAULT 設(shè)置默認(rèn)值奖年。
• 關(guān)鍵字PRIMARY KEY用于定義此列作為主鍵∨嫣埃可以使用逗號(hào)分隔多個(gè)列來定義主鍵陋守。

刪除表

語法：DROP TABLE IF EXISTS table_name;
刪除表（刪除整個(gè)表而不只是其內(nèi)容）非常簡單，刪除表沒有確認(rèn)利赋，也不能撤銷水评，執(zhí)行刪除語句后將永久刪除該表。

更新表

為更新表定義媚送，可使用ALTER TABLE 語句中燥，但是理想狀態(tài)下，當(dāng)表中存儲(chǔ)數(shù)據(jù)以后季希，該表就不應(yīng)該再被更新褪那。在表的設(shè)計(jì)過程中需要花費(fèi)大量時(shí)間來考慮，以便后期不對(duì)該表進(jìn)行大的改動(dòng)式塌。
為了使用ALTER TABLE更改表結(jié)構(gòu)博敬，必須給出下面的信息：

• 在ALTER TABLE之后給出要更改的表名（該表必須存在，否則將出錯(cuò)）* 所做更改的列表
  給s_dept表添加一個(gè)名字為dept_name的列峰尝，必須明確其數(shù)據(jù)類型偏窝。
  alter table s_dept add dept_name varchar(20);
  刪除剛剛添加的列，可以這樣做：
  ALTER TABLE s_dept DROP COLUMN dept_name;
  修改某個(gè)列的名字：
  ALTER TABLE s_dept CHANGE dept_name dept_name2 varchar(30);

重命名表

RENAME TABLE s_dept TO s_dept2;

SQL

全拼：STRUCTURED QUERY LANGUAGE

是一種數(shù)據(jù)庫查詢和程序設(shè)計(jì)語言武学，用于存取數(shù)據(jù)以及查詢祭往、更新和管理關(guān)系數(shù)據(jù)庫系統(tǒng)；同時(shí)也是數(shù)據(jù)庫腳本文件的擴(kuò)展名火窒。是一種用于數(shù)據(jù)庫操作的語言硼补。

包含6個(gè)部分

數(shù)據(jù)查詢語言（DQL）

select ，where熏矿，order by ,group by已骇，having

數(shù)據(jù)操作語言（DML）

insert离钝，update，delete

事務(wù)處理語言（TPL）

BEGIN TRANSACTION褪储，COMMIT和ROLLBACK卵渴。

數(shù)據(jù)控制語言（DCL）

GRANT或REVOKE獲得許可，確定單個(gè)用戶和用戶組對(duì)數(shù)據(jù)庫對(duì)象的訪問

數(shù)據(jù)定義語言（DDL）

CREATE和DROP鲤竹，ALTER

指針控制語言 ****(****CCL****)

DECLARE CURSOR浪读，F(xiàn)ETCH INTO和UPDATE WHERE CURRENT

檢索語句（SELECT語句

通用語法：SELECT column_name FROM table_name

為了使用SELECT檢索表數(shù)據(jù)，必須至少給出兩條信息- 想要選擇什么辛藻，以及從什么地方去選擇碘橘。

檢索單個(gè)列

Q：查詢所有員工的名字

select first_name from s_emp;

檢索多個(gè)列

Q：查詢所有員工的id和名字

select id, first_name from s_emp;

檢索所有列

Q：查詢所有員工的每個(gè)字段

select * from s_emp;

檢索不同的行（DISTINCT）

使用DISTINCT關(guān)鍵字，注意必須放在列名的最前面揩尸，不能部分使用DISTINCT蛹屿，DISTINCT關(guān)鍵字應(yīng)用于所有列而不僅是前置它的列。如果給出SELECT DISTINCT column1, column2岩榆，除非指定的兩個(gè)列都不同错负。否則所有行都將被檢索出來。

Q：查詢所有員工的所在部門

select dept_id from s_emp;

Q：查詢所有員工的所在部門（去掉重復(fù)）

select distinct dept_id from s_emp;

限制結(jié)果（LIMIT)

LIMIT 5 和 LIMIT 5, 5 勇边，MySQL5.0以后 是 LIMIT 4 OFFSET 3 從行3開始取4行犹撒， 注意：兩個(gè)參數(shù)的時(shí)候，索引是從0開始

Q：查詢前5個(gè)員工的名字

select first_name from s_emp limit 5;

Q：查詢從第3行開始后的4個(gè)員工名字

select first_name from s_emp limit 4 offset 3;

或者

select first_name from s_emp limit 3, 4;

使用完全限定的表名字（跨庫訪問）

Q：查詢其他數(shù)據(jù)庫的某個(gè)表數(shù)據(jù) 注意：events是information_schema這個(gè)庫中的表

select ends from information_schema.events;

排序檢索數(shù)據(jù)（ORDER BY子句)

單列排序粒褒、多列排序识颊、降序、找出最大值或最小值（和limit組合使用）

Q: 按照工資從少到多查詢員工的名字和工資

select first_name, salary from s_emp order by salary asc; 默認(rèn)就是升序奕坟，asc可以省略

Q：按照工資從多到少查詢員工的名字和工資

select first_name, salary from s_emp order by salary desc;

Q：按照工資從多到少祥款，如果有相同工資的則按照部門大小查詢員工的名字、工資和部門

select first_name, salary, dept_id from s_emp order by salary desc, dept_id desc;

Q：按照工資從多到少查詢員工的名字和工資月杉，顯示6-10條

select first_name, salary, dept_id from s_emp order by salary desc limit 5, 5;

過濾數(shù)據(jù)（WHERE子句）

在SELECT語句中刃跛，數(shù)據(jù)根據(jù)WHER子句中指定的搜索條件進(jìn)行過濾WHERE子句在表名（FROM子句）之后給出。

WHERE子句支持的操作符

• = 等于
• <> 不等于
• != 不等于
• < 小于
• > 大于
• >= 大于等于
• <= 小于等于
• BETWEEN 苛萎。桨昙。。AND 在指定的兩個(gè)值之間

檢查單個(gè)值(不一定是單個(gè)值腌歉，這里的單個(gè)值是指單個(gè)值過濾)

Q：查詢名字叫'Garmen'的員工

select first_name, salary from s_emp where first_name = 'Garmen';

不匹配檢查

Q：查詢不是部門3的員工信息

select first_name, salary, dept_id from s_emp where dept_id <> 3 order by dept_id;

范圍值檢查

Q：查詢工資在1000-1500之間的員工信息

select first_name, salary from s_emp where salary >= 1000 and salary

或者

select first_name, salary from s_emp where salary between 1000 and 1500 order by salary desc;

空值檢查（IS NULL）

Q：查詢xxx為NULL的員工

select first_name, last_name from s_emp where last_name is null;

組合WHERE子句

AND操作符

Q：查詢部門1并且工資大于1300的員工信息

select first_name, dept_id, salary from s_emp where dept_id = 1 and salary >1300 order by salary desc;

OR操作符

Q：查詢部門2和部門3的員工信息

select first_name, dept_id from s_emp where dept_id = 2 or dept_id = 3;

AND 和 OR組合操作的問題

Q：列出3或4部門下工資大于1000以上的員工

select first_name, dept_id, salary from s_emp where (dept_id = 3 or dept_id =4) and salary >= 1000;

或者

select first_name, dept_id, salary from s_emp where dept_id in(3, 4) and salary>= 1000 order by dept_id;

IN操作符

Q：查詢部門2和部門3和部門5的員工信息

select first_name, dept_id from s_emp where dept_id in (2, 3, 5);

NOT操作符

Q：查詢不在部門2和部門3的員工信息

select first_name, dept_id from s_emp where dept_id not in (2, 3);

通配符過濾（like操作符蛙酪，%通配符，_通配符)

LIKE是謂詞翘盖，%是通配符桂塞，表示任何字符出現(xiàn)任意次數(shù)。_是通配符馍驯，表示只能匹配一個(gè)藐俺。是否區(qū)分大小寫和MySQL配置有關(guān)系

Q：查詢名字以C開頭的員工信息

select first_name, dept_id from s_emp where first_name like 'C%';

Q：查詢部門1名字中包含e的員工信息

select first_name, dept_id from s_emp where dept_id = 1 and first_name like'%e%';

Q：查詢包含這種格式的員工信息

計(jì)算字段

存儲(chǔ)在數(shù)據(jù)庫表中的數(shù)據(jù)一般不是應(yīng)用程序最后所需要的格式炊甲。

例如，在一個(gè)字段中既要顯示公司名字欲芹，又要顯示公司的地址。

這種情況下就要用到計(jì)算字段了吟吝。解決辦法是把兩個(gè)列（字段）拼接起來菱父。可以使用Concat()函數(shù)來拼接兩個(gè)列剑逃，這里要注意：其他DBMS使用 + 或者 || 來進(jìn)行拼接浙宜，MySQL中就是使用Concat()函數(shù)。

Q：查詢部門5員工的信息蛹磺，要求只顯示一個(gè)字段粟瞬，字段格式為 員工姓名（工資）

select Concat(first_name, '(工資：', salary, ')'), dept_id from s_emp where dept_id = 5;

別名

使用AS關(guān)鍵字來設(shè)置別名

Q：同上面的問題一樣，只是顯示的時(shí)候名字叫做員工名字和工資

select Concat(first_name, '(工資：', salary, ')') as 員工和工資, dept_id as 部門IDfrom s_emp where dept_id = 5;

執(zhí)行算術(shù)計(jì)算

MySQL算術(shù)操作符：

• 加 +
• ? 減
• 乘 *
• 除 /

Q：顯示所有員工的名字和年薪

select first_name as 姓名, (salary * 12) as 年薪 from s_emp;

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

MySQL提供了很多內(nèi)置的數(shù)據(jù)處理函數(shù)

例如Lower()萤捆、Upper()裙品、Trim()等

select Upper(first_name) as 姓名, (salary * 12) as 年薪 from s_emp;

聚集函數(shù)

AVG() 表示某列的平均值

Q：查詢員工的平均薪資

select avg(salary) as 平均工資 from s_emp;

COUNT() 表示某列的行數(shù)

Q：查詢有多少個(gè)員工

select count(*) from s_emp;

MAX() 查詢某列的最大值

Q：查詢工資最高的員工

select max(salary) from s_emp;

MIN() 查詢某列的最小值

Q：查詢工資最低的員工

select min(salary) from s_emp;

SUM() 查詢某列的和

Q：查詢員工的工資總數(shù)

select sum(salary) from s_emp;

DISTINCT 和 AVG() 組合使用

Q：查詢員工的平均薪資（去掉重復(fù)）

select avg(distinct salary) from s_emp;

分組

分組是在SELECT語句的GROUP BY子句中建立的。一般配合聚合函數(shù)來使用俗或。一般看到每個(gè)部門xxx 市怎，每個(gè)地區(qū)xxx，每個(gè)班級(jí)xxx等需求的時(shí)候辛慰，我們要首先想到使用分組

Q：總共有多少員工

select count(*) from s_emp;

select Concat(count(*), '人') as 員工總數(shù) from s_emp;

Q：每個(gè)部門分別有多少員工

select dept_id, Concat(count(*), '人') as 員工總數(shù) from s_emp group by dept_id;

Q：每個(gè)部門的平均薪資

select dept_id, avg(salary) as 平均薪資 from s_emp group by dept_id;

如果要過濾分組的話使用 HAVING子句区匠，要記住在過濾數(shù)據(jù)普通行記錄的話使用WHERE，在過濾分組的時(shí)候使用HAVING

Q：部門3帅腌、4總共有多少員工

select count(*) from s_emp where dept_id in (3, 4);

Q：部門3驰弄、4分別有多少員工

select dept_id as 部門, count(*) as 員工數(shù)量 from s_emp group by dept_id having dept_id in (3, 4);

Q：員工數(shù)大于5人的部門有哪些

select dept_id as 部門, count(id) as 員工數(shù)量 from s_emp group by dept_idhaving count(id) > 5;

總結(jié)：select子句極其順序

1、select 要返回的列或表達(dá)式 必須使用

2速客、from 從中檢索數(shù)據(jù)的表 僅在選擇數(shù)據(jù)的時(shí)候使用

3戚篙、where 行級(jí)過濾 不必須使用

4、group by 分組說明 僅在按組計(jì)算聚集的時(shí)候使用

5挽封、having 組級(jí)過濾 不必須使用

6已球、order by 排序 不必須使用

7、limit 檢索行數(shù) 不必須使用

多表查詢

我們平時(shí)檢索出來的數(shù)據(jù)辅愿，大多數(shù)情況下都是多表聯(lián)合查詢出來的智亮。先來看下面的反例：

select *from s_emp, s_dept;

-- 125 rows in set (0.00 sec) 生成的結(jié)果是125條數(shù)據(jù)，我們知道員工表中有25條記錄点待，部門表中有5條記錄阔蛉。之所以產(chǎn)生125條記錄，是因?yàn)槲覀兪褂昧硕啾聿樵凂海瑳]有給任何關(guān)聯(lián)條件状原，會(huì)計(jì)算兩個(gè)表的行數(shù)乘積聋呢。形成 **笛卡爾積， **很明顯這125條數(shù)據(jù)并不是我們想要的結(jié)果颠区。

在來看一個(gè)問題：

select first_name, id from s_emp, s_dept;

-- 以上代碼運(yùn)行會(huì)報(bào)錯(cuò)削锰，先不考慮笛卡爾積問題，因?yàn)閕d在員工表和部門表里都存在毕莱，所以這里會(huì)有問題器贩。

我可以給表起一個(gè)別名，可以像下面這樣使用

select e.first_name, e.id, d.id from s_emp e, s_dept d;

內(nèi)連接 (inner join)

內(nèi)連接會(huì)匹配兩個(gè)表或者多個(gè)表內(nèi)相同的值的結(jié)果集朋截。

內(nèi)連接原理：

t1表和t2表做內(nèi)連接蛹稍，連接條件為 on t1.c1 = t2.c2，分兩種情況得到結(jié)果集

1部服、t1做驅(qū)動(dòng)表唆姐，t2做匹配表

2、t2做驅(qū)動(dòng)表廓八，t1做匹配表

無論哪種方式奉芦，所得到的結(jié)果集都是相同的，只是效率不同而已瘫想。

內(nèi)連接的核心就是任何一張表的記錄一定要在另外一張表中找到匹配的記錄仗阅，否則不能出現(xiàn)在結(jié)果集中

內(nèi)連接匹配流程：on t1.c1 = t2.c2

假設(shè)t1表做驅(qū)動(dòng)表，t2表做匹配表国夜，記錄的匹配過程：

1减噪、從t1表中讀取一條記錄r1，若他的值是c1

2车吹、根據(jù)該值到t2表中查找匹配的記錄筹裕，既需要遍歷t2表，從t2表中的第一條記錄開始尋找窄驹，若該記錄c2列的值等于c1朝卒，我們就說這兩條記錄可以匹配上，那么t1中的結(jié)果和t2中的結(jié)果組合起來乐埠，作為最后結(jié)果集的一條記錄抗斤。否則檢測t2表中的下一條記錄。

3丈咐、按照步驟2一次將t2表中的所有記錄匹配完畢瑞眼，只要可以匹配的就放到最后的結(jié)果集中。

4棵逊、從t1中讀取第二條記錄伤疙，一次重復(fù)步驟2和步驟3，產(chǎn)生最后的結(jié)果集。

select e.first_name, e.id, d.id

from s_emp e

inner join s_dept d

on e.dept_id = d.id

nner join 徒像。黍特。on 。锯蛀。 語法：內(nèi)連接員工表和部門表灭衷。員工表的部門id 等于 部門表的id

Q：'Garmen'所在的部門名稱是什么

select e.first_name, d.name from s_emp e inner join s_dept d on e.dept_id = d.id where e.first_name = 'Garmen';

Q：每個(gè)部門所在地區(qū)的名稱是什么

select d.name, r.name from s_dept d inner join s_region r on d.region_id = r.id;

Q：亞洲地區(qū)都有哪些部門

select d.name, r.name from s_dept d inner join s_region r on d.region_id = r.id where r.name = 'Asia';

Q：亞洲地區(qū)有哪些員工

select e.first_name, d.name, r.name from s_emp e inner join s_dept d on e.dept_id = d.id inner join s_region r on d.region_id = r.id;

Q：'Garmen'在哪個(gè)地區(qū)上班

select e.first_name, d.name, r.name from s_emp e inner join s_dept d on e.dept_id = d.id and e.first_name = 'Garmen' inner joins_region r on d.region_id = r.id;

內(nèi)連接的三種形式

1、等值連接（上面我們描述的就是等值連接）

兩張表具有描述共同屬性的列谬墙，常見的寫法是父表的主鍵和子表的外鍵相等今布。

2、非等值連接

兩張表不具有描述共同屬性的列拭抬，可以使用between and 這樣的非等值運(yùn)算符將兩張表中的列寫成一個(gè)表達(dá)式。所謂表之間的關(guān)系侵蒙，實(shí)際是指表中的行（記錄）之間的關(guān)系造虎。該關(guān)系將通過表中的列寫成表達(dá)式來體現(xiàn)。

Q：員工對(duì)應(yīng)的工資等級(jí)纷闺。

select e.first_name, e.salary, s.grade from s_emp e inner join salgrade s on e.salary between s.losal and s.hisal;

Q：'Garmen'的工資級(jí)別是多少算凿。

select e.first_name, e.salary, s.grade from s_emp e inner join salgrade s on e.salary between s.losal and s.hisal and e.first_name ='Garmen';

Q：在部門1中，工資等級(jí)為3犁功、5的員工有哪些氓轰。

select e.first_name, e.salary, s.grade from s_emp e inner join salgrade s on e.salary between s.losal and s.hisal and e.dept_id = 1and s.grade in (3, 5);

3、自連接

Q：查詢每個(gè)員工和這個(gè)員工對(duì)應(yīng)的領(lǐng)導(dǎo)

select e1.first_name as 員工, e2.first_name as 領(lǐng)導(dǎo) from s_emp e1 inner join s_emp e2 on e1.manager_id = e2.id;

Q：哪些人是領(lǐng)導(dǎo)

select first_name, ifnull((select first_name from s_emp i where o.manager_id = i.id), 'Boss') as 領(lǐng)導(dǎo) from s_emp o;

外鏈接（outer join)

如果結(jié)果集沒有匹配記錄的時(shí)候浸卦，就要使用外鏈接署鸡。 外鏈接的核心是將匹配不到的記錄找回來，即一個(gè)都不能少限嫌，外鏈接的結(jié)果集 = 內(nèi)連接的結(jié)果集 + 驅(qū)動(dòng)表中匹配不上的記錄和一條null記錄組合靴庆。

Q：每個(gè)員工對(duì)應(yīng)的領(lǐng)導(dǎo)

左連接寫法

select e1.first_name as 員工, e2.first_name as 領(lǐng)導(dǎo)

from s_emp e1 left outer join s_emp e2

on e1.manager_id = e2.id;

右連接寫法

select e1.first_name as 員工, e2.first_name as 領(lǐng)導(dǎo)

from s_emp e2 right outer join s_emp e1

on e1.manager_id = e2.id;

Q：每個(gè)員工對(duì)應(yīng)的領(lǐng)導(dǎo)（'Garmen'如果沒有領(lǐng)導(dǎo)，則換成BOSS）

擴(kuò)展：ifnull(字段, '替換內(nèi)容') 這個(gè)函數(shù)的意思是如果現(xiàn)實(shí)的某個(gè)字段值為null怒医，則替換成我們提供的值

select e1.first_name as 員工,ifnull(e2.first_name, 'Boss') as 領(lǐng)導(dǎo) from s_emp e2 right outer join s_emp e1 on e1.manager_id = e2.id;

Q：哪個(gè)部門沒有員工

select e.first_name, d.name from s_emp e right outer join s_dept d on e.dept_id = d.id where e.first_name is null;

Q：哪些人是員工（相當(dāng)于問哪些人不是領(lǐng)導(dǎo)）

select e2.first_name as 普通員工 from s_emp e2 left outer join s_emp e1 on e1.manager_id = e2.id where e1.first_name is null;

子查詢

子查詢邏輯：

先執(zhí)行子查詢炉抒，子查詢的返回結(jié)果作為主查詢的查詢條件，在執(zhí)行主查詢稚叹。

子查詢只執(zhí)行一遍焰薄。

若子查詢的返回結(jié)果有多個(gè)值，則會(huì)去重在講結(jié)果返回給主查詢

Q：找出工資最少的那個(gè)人

select e.first_name, e.salary from s_emp e where e.salary = (select min(salary) from s_emp);

Q：誰和'Mark'的工資是一樣的 （如果不能保證結(jié)果集唯一扒袖，應(yīng)該使用any）

select e.first_name, e.salary from s_emp e where e.salary = (select salary from s_emp where first_name = 'Mark') and e.first_name<> 'Mark';

或者 （注意：子查詢?nèi)绻鼙ＷC結(jié)果是唯一（根據(jù)主鍵查詢）的塞茅，可以不用any，要不就會(huì)有多個(gè)結(jié)果的風(fēng)險(xiǎn)）

select e.first_name, e.salary from s_emp e where e.salary = any(select salary from s_emp where first_name = 'Mark') and e.first_name <> 'Mark';

Q：哪些部門的平均工資比部門3的平均工資高

select e.dept_id, avg(e.salary) from s_emp e group by e.dept_id having avg(e.salary) > (select avg(salary) from s_emp where dept_id = 3);

Q：哪些人是領(lǐng)導(dǎo)

select first_name from s_emp where id in (select manager_id from s_emp);

插入數(shù)據(jù)
INSERT是用來插入（添加）行到數(shù)據(jù)庫的僚稿。插入可以用幾種方式使用：

注意：可以針對(duì)每個(gè)表或者每個(gè)用戶凡桥，利用MySQL的安全機(jī)制禁止使用INSERT語句。

插入完整的行
insert into s_dept values ( NULL, '部門1', 2);
INSERT語句一般不會(huì)產(chǎn)生輸出蚀同。
以上語句會(huì)對(duì)s_dept表缅刽，存儲(chǔ)到每一個(gè)表列中的數(shù)據(jù)在values子句中給出啊掏。對(duì)每個(gè)列必須提供一個(gè)值。如果某個(gè)列沒有值衰猛，應(yīng)該使用NULL值迟蜜。各個(gè)列必須以它們?cè)诒矶x中出現(xiàn)的次序填充。第一列為NULL啡省。是因?yàn)槊看尾迦胍粋€(gè)新行時(shí)娜睛，該列由MySQL自動(dòng)增量。
雖然上面的語法很簡單卦睹，但并不安全畦戒，應(yīng)該盡量避免使用。上面的語句高度依賴于表中列的定義次序结序。
insert into s_dept ( name, region_id) values ('部門1', 3);
以上會(huì)發(fā)現(xiàn)障斋，我們可以設(shè)定只給某些字段設(shè)置值，然后沒有設(shè)置的字段我們不用設(shè)置為NULL徐鹤，沒有出現(xiàn)的字段垃环，默認(rèn)就會(huì)變成NULL。
insert into s_dept (region_id, name) values (3, '部門2');
這個(gè)例子給的列名字和建立表的時(shí)候順序不一樣返敬，這樣也是可以的遂庄。

更新數(shù)據(jù)
更新（修改）表中的數(shù)據(jù)，可以使用UPDATE語句劲赠，可以采用兩種方式使用UPDATE：

注意：同INSERT語句一樣涛目，利用MySQL的安全機(jī)制可以限制使用UPDATE語句
UPDATE語句非常容易使用，基本的UPDATE語句由3部分組成经磅，分別是：
要更新的表泌绣、 列名和它們的新值*、確定要更新行的過濾條件
例如：給某個(gè)用戶表预厌，用戶ID為1001的用戶修改新密碼為123456
update user表 set user_pwd = '123456' where user_id = 1001;

注意：上面的語句中的where不要省略阿迈，如果省略就會(huì)更新這個(gè)表的所有行的密碼。
更新多個(gè)列轧叽，在set子句后面使用逗號(hào)給開

update user表 set user_pwd = '新密碼', user_email = 'xx@qq.com' where user_id = 1001;

刪除數(shù)據(jù)
從某個(gè)表中刪除數(shù)據(jù)苗沧，使用DELETE語句。有兩種方式使用DELETE：
從表中刪除特定的行
注意：同以上一樣炭晒，利用MySQL的安全機(jī)制可以限制使用DELETE語句
DELETE語句非常容易待逞，刪除某一行
delete from user表 where user_id = 1001;

刪除某個(gè)表中的所有行

delete from user表;
注意：在使用delete語句的時(shí)候，刪除的是表中的數(shù)據(jù)网严，而不是表
如果想刪除所有行识樱，還可以有另外一種方式刪除
TRUNCATE TABLE table_name;
這個(gè)可以刪除所有行，其實(shí)內(nèi)部實(shí)現(xiàn)是刪除了某個(gè)表，然后在創(chuàng)建該表怜庸。從性能上講它更快当犯，但要注意，它會(huì)把和表相關(guān)聯(lián)的自增序列一并刪除割疾。

事務(wù)處理

事務(wù)處理（transaction processing）可以用來維護(hù)數(shù)據(jù)的完整性嚎卫，它保證成批的MySQL操作要么完全執(zhí)行，要么完全不執(zhí)行宏榕。
在使用事務(wù)處理時(shí)拓诸，有幾個(gè)關(guān)鍵術(shù)語：

• 事務(wù)（transaction）指一組SQL語句* 回退（rollback）指撤銷指定SQL語句的過程* 提交（commit）指將未存儲(chǔ)的SQL語句結(jié)果寫入數(shù)據(jù)庫表
  MySQL使用下面的語句來標(biāo)識(shí)事物的開始：
  START TRANSACTION

使用ROLLBACK

MySQL使用ROLLBACK命令來回退（撤銷）MySQL語句。
先查詢某個(gè)表麻昼，看下之前的數(shù)據(jù)
select * from xx表;
開啟事務(wù)管理
start transaction;
刪除所有數(shù)據(jù)
delete from xx表;
查詢看是否刪除（結(jié)果已經(jīng)沒有數(shù)據(jù)）
select * from xx表;
回滾
rollback;
再次查詢數(shù)據(jù)（結(jié)果顯示數(shù)據(jù)回到了最初刪除之前的樣子）
select * from xx表;
顯然奠支，ROLLBACK只能在一個(gè)事務(wù)處理內(nèi)使用（在執(zhí)行一一條START TRANSACTION命令之后）
事務(wù)處理用來管理INSERT、UPDATE和DELETE語句抚芦，不能回退SELECT語句（這樣做也沒有意義）胚宦，不能回退CREATE和DROP。

使用COMMIT

一般的MySQL語句都是直接針對(duì)數(shù)據(jù)庫執(zhí)行和編寫的燕垃。這就是所謂的隱含提交（implicit commit），即提交（寫或保存）操作都是自動(dòng)的。
但是在事務(wù)處理中，提交不會(huì)隱含的進(jìn)行趴久。為進(jìn)行明確的提交脂崔，需要使用COMMIT語句。
START TRANSACTION;
DELETE FROM xx表 where user_id = 1001;
DELETE FROM xx表 where manager_id = 1001;
COMMIT;
以上語句保證不會(huì)因?yàn)槟撤N原因憋他，只執(zhí)行了第一條，沒有執(zhí)行第二條刪除。會(huì)保持刪除的完整性侦副，如果其中一個(gè)有問題，則不會(huì)提交驼鞭，會(huì)被自動(dòng)撤銷秦驯。

隱含事務(wù)關(guān)閉：當(dāng)COMMIT或ROLLBACK語句執(zhí)行后，事務(wù)會(huì)自動(dòng)關(guān)閉

約束

約束是一種限制挣棕，它通過對(duì)表的行或列的數(shù)據(jù)做出限制译隘，來確保表的數(shù)據(jù)的完整性、唯一性洛心。
MYSQL中固耘，常用的幾種約束：
| 約束類型： | 主鍵 | 默認(rèn)值 | 唯一 | 外鍵 | 非空

關(guān)鍵字： | PRIMARY KEY | DEFAULT | UNIQUE | FOREIGN KEY | NOT NULL |

主鍵

主鍵(PRIMARY KEY)是用于約束表中的一行，作為這一行的標(biāo)識(shí)符词身，在一張表中通過主鍵就能準(zhǔn)確定位到一行厅目，因此主鍵十分重要。主鍵要求這一行的數(shù)據(jù)不能有重復(fù)且不能為空。
還有一種特殊的主鍵——復(fù)合主鍵损敷。主鍵不僅可以是表中的一列葫笼，也可以由表中的兩列或多列來共同標(biāo)識(shí)

默認(rèn)值

默認(rèn)值約束(DEFAULT)規(guī)定，當(dāng)有DEFAULT約束的列嗤锉，插入數(shù)據(jù)為空時(shí)該怎么辦渔欢。
DEFAULT約束只會(huì)在使用INSERT語句時(shí)體現(xiàn)出來，INSERT語句中瘟忱，如果被DEFAULT約束的位置沒有值奥额，那么這個(gè)位置將會(huì)被DEFAULT的值填充

唯一性

唯一約束(UNIQUE)比較簡單，它規(guī)定一張表中指定的一列的值必須不能有重復(fù)值访诱，即這一列每個(gè)值都是唯一的垫挨。
當(dāng)INSERT語句新插入的數(shù)據(jù)和已有數(shù)據(jù)重復(fù)的時(shí)候，如果有UNIQUE約束触菜，則INSERT失敗.

外鍵

外鍵(FOREIGN KEY)既能確保數(shù)據(jù)完整性九榔，也能表現(xiàn)表之間的關(guān)系。
一個(gè)表可以有多個(gè)外鍵涡相，每個(gè)外鍵必須REFERENCES(參考)另一個(gè)表的主鍵哲泊，被外鍵約束的列，取值必須在它參考的列中有對(duì)應(yīng)值催蝗。
如果子表試圖創(chuàng)建一個(gè)在父表中不存在的外鍵值切威，InnoDB會(huì)拒絕任何INSERT或UPDATE操作。如果父表試圖UPDATE或者DELETE任何子表中存在或匹配的外鍵值丙号，最終動(dòng)作取決于外鍵約束定義中的ON UPDATE和ON DELETE選項(xiàng)先朦。InnoDB支持5種不同的動(dòng)作，如果沒有指定ON DELETE或者ON UPDATE犬缨，默認(rèn)的動(dòng)作為RESTRICT:

1. CASCADE: 從父表中刪除或更新對(duì)應(yīng)的行喳魏，同時(shí)自動(dòng)的刪除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都被InnoDB所支持怀薛。
2. SET NULL: 從父表中刪除或更新對(duì)應(yīng)的行刺彩，同時(shí)將子表中的外鍵列設(shè)為空。注意乾戏，這些在外鍵列沒有被設(shè)為NOT NULL時(shí)才有效迂苛。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。
3. NO ACTION: InnoDB拒絕刪除或者更新父表鼓择。
4. RESTRICT: 拒絕刪除或者更新父表三幻。指定RESTRICT（或者NO ACTION）和忽略O(shè)N DELETE或者ON UPDATE選項(xiàng)的效果是一樣的。
5. SET DEFAULT: InnoDB目前不支持呐能。
   外鍵約束使用最多的兩種情況：
   1）父表更新時(shí)子表也更新念搬，父表刪除時(shí)如果子表有匹配的項(xiàng)抑堡，刪除失敗朗徊；
   2）父表更新時(shí)子表也更新首妖，父表刪除時(shí)子表匹配的項(xiàng)也刪除。
   前一種情況爷恳，在外鍵定義中有缆，我們使用ON UPDATE CASCADE ON DELETE RESTRICT；
   后一種情況温亲，可以使用ON UPDATE CASCADE ON DELETE CASCADE。

非空

非空約束(NOT NULL),聽名字就能理解袖外，被非空約束的列曼验，在插入值時(shí)必須非空。
在MySQL中違反非空約束孤紧，不會(huì)報(bào)錯(cuò)，只會(huì)有警告.

數(shù)據(jù)庫設(shè)計(jì)三范式

• 1NF：要求一張表只描述一件事咙轩。比如學(xué)生表只能保存學(xué)生信息活喊，和學(xué)生無關(guān)的信息不得保存钾菊；
• 2NF：要求保證表中每一行數(shù)據(jù)的原子性煞烫。即表中不得存在完全相同的數(shù)據(jù)行凛俱，存在完全相同的數(shù)據(jù)行叫數(shù)據(jù)冗余（或叫數(shù)據(jù)副本）蒲犬，這是一種錯(cuò)誤原叮，要盡量的減少和避免奋隶，無法完全完全消除达布。解決方案主鍵或標(biāo)識(shí)，一般的是主鍵配合標(biāo)識(shí)去用的产还。
• 3NF：（外鍵）表中其他的（外）鍵要依賴于主（鍵表）（主鍵）鍵而存在脐区。