[TOC]

第一章：MySQL介紹

1.1-什么是SQL

Structured Query Language 結(jié)構(gòu)化查詢語言

1.2-SQL的作用

1. 是一種所有關(guān)系型數(shù)據(jù)庫的查詢規(guī)范阱冶，不同的數(shù)據(jù)庫都支持弃酌。
2. 通用的數(shù)據(jù)庫操作語言艾扮，可以用在不同的數(shù)據(jù)庫中。
3. 不同的數(shù)據(jù)庫 SQL 語句有一些區(qū)別

1.3-SQL語句分類

1. Data Definition Language (DDL 數(shù)據(jù)定義語言) 如：建庫，建表
2. Data Manipulation Language(DML 數(shù)據(jù)操縱語言)，如：對表中的記錄操作增刪改
3. Data Query Language(DQL 數(shù)據(jù)查詢語言)悍汛，如：對表中的查詢操作
4. Data Control Language(DCL 數(shù)據(jù)控制語言)，如：對用戶權(quán)限的設(shè)置

1.4-SQL語法注意事項

1. 每條語句以分號結(jié)尾至会，如果在 SQLyog 中不是必須加的离咐。

2. SQL 中不區(qū)分大小寫，關(guān)鍵字中認為大寫和小寫是一樣的 奉件。

3. 3 種注釋：

   注釋語法	說明
   --空格	單行注釋
   /**/	多行注釋
   #	這是 mysql 特有的注釋方式

第二章：DDL操作數(shù)據(jù)庫

2.1-創(chuàng)建數(shù)據(jù)庫

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

CREATE DATABASE 數(shù)據(jù)庫名;

判斷數(shù)據(jù)庫是否已經(jīng)存在宵蛀，不存在則創(chuàng)建數(shù)據(jù)庫

CREATE DATABASE IF NOT EXISTS 數(shù)據(jù)庫名;

創(chuàng)建數(shù)據(jù)庫并指定字符集

CREATE DATABASE 數(shù)據(jù)庫名 CHARACTER SET 字符集;

演示：

--創(chuàng)建一個名為db1的數(shù)據(jù)庫
create database db1;
--若沒有存在，則創(chuàng)建一個名為db2的數(shù)據(jù)庫
create database if not exists db2;
--創(chuàng)建要給字符集為gbk的數(shù)據(jù)庫db3
create database db3 character set gbk;

2.2-查看數(shù)據(jù)庫

查看所有數(shù)據(jù)庫

show databases;

查看指定的數(shù)據(jù)庫

show create database 數(shù)據(jù)庫名

演示：

--查看所有數(shù)據(jù)庫
show databases;
--查看名為db1的數(shù)據(jù)庫
show create database db1;

2.3-修改數(shù)據(jù)庫

修改數(shù)據(jù)庫的字符集

ALTER DATABASE 數(shù)據(jù)庫名 DEFAULT CHARACTER SET 字符集;

演示：

ALTER DATABASE db1 character set utf8;

2.4-刪除數(shù)據(jù)庫

語法

DROP DATABASE 數(shù)據(jù)庫名;

演示：

DROP DATABASE db1;

2.5-使用數(shù)據(jù)庫

查看使用的數(shù)據(jù)庫

SELECT DATABASE();

切換使用的數(shù)據(jù)庫

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

演示

select database();

第三章：DDL操作表結(jié)構(gòu)

前提先使用某個數(shù)據(jù)庫 县貌。

3.1-創(chuàng)建表

格式

CREATE TABLE 表名 (
    字段名 1 字段類型 1,
    字段名 2 字段類型 2
);

關(guān)鍵字說明

• CREATE 創(chuàng)建
• TABLE 表

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

類型

日期：TIMESTAMP 時間戳

演示

create table student (
    id int, -- 整數(shù)
    name varchar(20), -- 字符串
    birthday date -- 生日术陶，最后沒有逗號
);

3.3-查看表

查看某個數(shù)據(jù)庫的所有表

SHOW TABLES;

查看表結(jié)構(gòu)

DESC 表名

查看創(chuàng)建表的SQL語句

SHOW CREATE TABLE 表名

3.4-快速創(chuàng)建一個與表結(jié)構(gòu)相同的表

語法

CREATE TABLE 新表名 LIKE 舊表名;

演示

CREATE TABLE student LINKE stu;

3.5-刪除表

直接刪除表

DROP TABLE 表名;

判斷表是否存在，如果存在則刪除表

DROP TABLE IF EXISTS 表名

演示

DROP TABLE student;
DROP TABLE IF EXISTS student

3.6-修改表結(jié)構(gòu)

添加一個列

alter table 表名 add 列名 類型

修改列的類型

ALTER TABLE 表名 MODIFY 列名 新的類型

修改列名

ALTER TABLE 表名 CHANGE 舊列名 新列名 類型;

刪除列

ALTER TABLE 表名 DROP 列名;

修改表名

RENAME TABLE 表名 TO 新表名;

修改表名字符集

ALTER TABLE 表名 character set 字符集;

第四章：DML操作表中的數(shù)據(jù)

4.1-插入記錄

所有的字段名

INSERT INTO 表名 (字段名 1, 字段名 2, 字段名 3…) VALUES (值 1, 值 2, 值 3...);

不寫字段名

INSERT INTO 表名 VALUES (值 1, 值 2, 值 3…);

插入部分數(shù)據(jù)

INSERT INTO 表名 (字段名 1, 字段名 2, ...) VALUES (值 1, 值 2, ...);

注意事項

1. 插入的數(shù)據(jù)應(yīng)與字段的數(shù)據(jù)類型相同
2. 數(shù)據(jù)的大小應(yīng)在列的規(guī)定范圍內(nèi)煤痕，例如：不能將一個長度為 80 的字符串加入到長度為 40 的列中梧宫。
3. 在 values 中列出的數(shù)據(jù)位置必須與被加入的列的排列位置相對應(yīng)。
4. 在 mysql 中可以使用 value摆碉，但不建議使 用塘匣，功能與 values 相同。
5. 字符和日期型數(shù)據(jù)應(yīng)包含在單引號中巷帝。MySQL 中也可以使用雙引號做為分隔符忌卤。
6. 不指定列或使用 null，表示插入空值楞泼。

4.2-更新記錄

UPDATE 表名 SET 列名=值 [WHERE 條件表達式]
#UPDATE: 需要更新的表名
#SET: 修改的列值
#WHERE: 符合條件的記錄才更新
#你可以同時更新一個或多個字段埠巨。
#你可以在 WHERE 子句中指定任何條件历谍。

不帶條件修改數(shù)據(jù)

UPDATE 表名 SET 字段名=值; -- 修改所有的行

帶條件修改數(shù)據(jù)

UPDATE 表名 SET 字段名=值 WHERE 字段名=值;

演示

-- 不帶條件修改數(shù)據(jù)现拒，將所有的性別改成女
update student set sex = '女';
-- 帶條件修改數(shù)據(jù)辣垒，將 id 號為 2 的學(xué)生性別改成男
update student set sex='男' where id=2;
-- 一次修改多個列，把 id 為 3 的學(xué)生印蔬，年齡改成 26 歲勋桶，address 改成北京
update student set age=26, address='北京' where id=3;

4.3-刪除記錄

DELETE FROM 表名 [WHERE 條件表達式]
# 如果沒有指定 WHERE 子句，MySQL 表中的所有記錄將被刪除侥猬。
# 你可以在 WHERE 子句中指定任何條件

不帶條件刪除數(shù)據(jù)

DELETE FROM 表名

帶條件刪除數(shù)據(jù)

DELETE FROM 表名 WHERE 字段名=值;

使用 truncate 刪除表中所有記錄

TRUNCATE TABLE 表名;

truncate 和 delete 的區(qū)別

truncate 相當(dāng)于刪除表的結(jié)構(gòu)例驹，再創(chuàng)建一張表。

演示

-- 帶條件刪除數(shù)據(jù)退唠，刪除 id 為 1 的記錄
delete from student where id=1;
-- 不帶條件刪除數(shù)據(jù),刪除表中的所有數(shù)據(jù)
delete from student;

第五章：DQL查詢表中的數(shù)據(jù)

SELECT 列名 FROM 表名 [WHERE 條件表達式];

5.1-簡單查詢

查詢表所有行和列的數(shù)據(jù)

SELECT * FROM 表名;

查詢指定的列

SELECT 字段名 1, 字段名 2, 字段名 3, ... FROM 表名;

演示：

select * from student;
select name,age,sex from student;

5.2-指定列的別名

使用別名的好處： 顯示的時候使用新的名字鹃锈，并不修改表的結(jié)構(gòu)。

表使用別名的原因：用于多表查詢操作 瞧预。

對列指定別名

SELECT 字段名 1 AS 別名, 字段名 2 AS 別名... FROM 表名;

對列和表名指定別名

SELECT 字段名 1 AS 別名, 字段名 2 AS 別名... FROM 表名 AS 表別名;

演示

-- 使用別名
select name as 姓名,age as 年齡 from student;
-- 表使用別名
select st.name as 姓名,age as 年齡 from student as st

5.3-清除重復(fù)的值

**查詢指定列并且結(jié)果不出現(xiàn)重復(fù)數(shù)據(jù) **

SELECT DISTINCT 字段名 FROM 表名;

演示

-- 查詢學(xué)生來至于哪些地方
select address from student;
-- 去掉重復(fù)的記錄
select distinct address from student;

5.4-查詢結(jié)果參與運算

注意: 參與運算的必須是數(shù)值類型

**某列數(shù)據(jù)和固定值運算 **

SELECT 列名 1 + 固定值 FROM 表名;

**某列數(shù)據(jù)和其他列數(shù)據(jù)參與運算 **

SELECT 列名 1 + 列名 2 FROM 表名;

演示

需求-準(zhǔn)備數(shù)據(jù)：添加數(shù)學(xué)屎债，英語成績列,給每條記錄添加對應(yīng)的數(shù)學(xué)和英語成績，查詢的時候?qū)?shù)學(xué)和英語的成績相 加

select * from student;
-- 給所有的數(shù)學(xué)加 5 分
select math+5 from student;
-- 查詢 math + english 的和
select * from student;
select *,(math+english) as 總成績 from student;
-- as 可以省略
select *,(math+english) 總成績 from student;

5.5-條件查詢

為什么要條件查詢垢油？

如果沒有查詢條件盆驹，則每次查詢所有的行。實際應(yīng)用中滩愁，一般要指定查詢的條件躯喇。對記錄進行過濾

語法

SELECT 字段名 FROM 表名 WHERE 條件;

流程：取出表中的每條數(shù)據(jù)，滿足條件的記錄就返回硝枉，不滿足條件的記錄不返回

準(zhǔn)備數(shù)據(jù)

CREATE TABLE student3 (
 id int, -- 編號
 name varchar(20), -- 姓名
 age int, -- 年齡
 sex varchar(5), -- 性別
 address varchar(100), -- 地址
 math int, -- 數(shù)學(xué)
 english int -- 英語
);
INSERT INTO student3(id,NAME,age,sex,address,math,english) 
VALUES 
    (1,'馬云',55,'男','杭州',66,78),
    (2,'馬化騰',45,'女','深圳',98,87),
    (3,'馬景濤',55,'男','香港',56,77),
    (4,'柳巖',20,'女','湖南',76,65),
    (5,'柳青',20,'男','湖南',86,NULL),
    (6,'劉德華',57,'男','香港',99,99),
    (7,'馬德',22,'女','香港',99,99),
    (8,'德瑪西亞',18,'男','南京',56,65);

比較運算符

-- 查詢 math 分數(shù)大于 80 分的學(xué)生
select * from student3 where math>80;
-- 查詢 english 分數(shù)小于或等于 80 分的學(xué)生
select * from student3 where english <=80;
-- 查詢 age 等于 20 歲的學(xué)生
select * from student3 where age = 20;
-- 查詢 age 不等于 20 歲的學(xué)生，注：不等于有兩種寫法
select * from student3 where age <> 20;
select * from student3 where age != 20;

邏輯運算符

-- 查詢 age 大于 35 且性別為男的學(xué)生(兩個條件同時滿足)
select * from student3 where age>35 and sex='男';
-- 查詢 age 大于 35 或性別為男的學(xué)生(兩個條件其中一個滿足)
select * from student3 where age>35 or sex='男';
-- 查詢 id 是 1 或 3 或 5 的學(xué)生
select * from student3 where id=1 or id=3 or id=5;

in關(guān)鍵字

SELECT 字段名 FROM 表名 WHERE 字段 in (數(shù)據(jù) 1, 數(shù)據(jù) 2...);

in 里面的每個數(shù)據(jù)都會作為一次條件笛匙，只要滿足條件的就會顯示 侨把。

-- 查詢 id 是 1 或 3 或 5 的學(xué)生
select * from student3 where id in(1,3,5);
-- 查詢 id 不是 1 或 3 或 5 的學(xué)生
select * from student3 where id not in(1,3,5);

范圍查詢

BETWEEN 值 1 AND 值 2 表示從值 1 到值 2 范圍犀变，包頭又包尾

比如：age BETWEEN 80 AND 100相當(dāng)于： age>=80 && age<=100

查詢 english 成績大于等于 75，且小于等于 90 的學(xué)生

select * from student3 where english between 75 and 90;

like關(guān)鍵字

SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';

LIKE 表示模糊查詢

MySQL通配符

-- 查詢姓馬的學(xué)生
select * from student3 where name like '馬%';
select * from student3 where name like '馬';
-- 查詢姓名中包含'德'字的學(xué)生
select * from student3 where name like '%德%';
-- 查詢姓馬秋柄，且姓名有兩個字的學(xué)生
select * from student3 where name like '馬_';

5.6-排序

通過 ORDER BY 子句获枝，可以將查詢出的結(jié)果進行排序(排序只是顯示方式，不會影響數(shù)據(jù)庫中數(shù)據(jù)的順序)

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];

ASC: 升序骇笔，默認值 DESC: 降序

單列排序

只按某一個字段進行排序省店，單列排序。

-- 查詢所有數(shù)據(jù),使用年齡降序排序
select * from student order by age desc;

組合排序

同時對多個字段進行排序笨触，如果第 1 個字段相等懦傍，則按第 2 個字段排序，依次類推芦劣。

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 1 [ASC|DESC], 字段名 2 [ASC|DESC];

-- 查詢所有數(shù)據(jù),在年齡降序排序的基礎(chǔ)上粗俱，如果年齡相同再以數(shù)學(xué)成績升序排序
select * from student order by age desc, math asc;

5.7-聚合函數(shù)

之前我們做的查詢都是橫向查詢，它們都是根據(jù)條件一行一行的進行判斷虚吟，而使用聚合函數(shù)查詢是縱向查詢寸认， 它是對一列的值進行計算，然后返回一個結(jié)果值稍味。聚合函數(shù)會忽略空值 NULL废麻。

語法：SELECT 聚合函數(shù)(列名) FROM 表名;

-- 查詢學(xué)生總數(shù)
select count(id) as 總?cè)藬?shù) from student;
select count(*) as 總?cè)藬?shù) from student;

對于 NULL 的記錄不會統(tǒng)計，建議如果統(tǒng)計個數(shù)則不要使用有可能為 null 的列模庐，但如果需要把 NULL 也統(tǒng)計進去呢烛愧？

IFNULL(列名，默認值) 如果列名不為空掂碱，返回這列的值怜姿。如果為 NULL，則返回默認值疼燥。

-- 查詢 id 字段沧卢，如果為 null，則使用 0 代替
select ifnull(id,0) from student;

我們可以利用 IFNULL()函數(shù)醉者，如果記錄為 NULL但狭，給個默認值，這樣統(tǒng)計的數(shù)據(jù)就不會遺漏 撬即。

select count(ifnull(id,0)) from student;
-- 查詢年齡大于 20 的總數(shù)
select count(*) from student where age>20;
-- 查詢數(shù)學(xué)成績總分
select sum(math) 總分 from student;
-- 查詢數(shù)學(xué)成績平均分
select avg(math) 平均分 from student;
-- 查詢數(shù)學(xué)成績最高分
select max(math) 最高分 from student;
-- 查詢數(shù)學(xué)成績最低分
select min(math) 最低分 from student;

5.8-分組

分組查詢是指使用 GROUP BY 語句對查詢信息進行分組立磁，相同數(shù)據(jù)作為一組

SELECT 字段 1,字段 2... FROM 表名 GROUP BY 分組字段 [HAVING 條件];

GROUP BY 怎么分組的

將分組字段結(jié)果中相同內(nèi)容作為一組，如按性別將學(xué)生分成 2 組剥槐。

GROUP BY 將分組字段結(jié)果中相同內(nèi)容作為一組唱歧，并且返回每組的第一條數(shù)據(jù)，所以單獨分組沒什么用處。 分組的目的就是為了統(tǒng)計颅崩，一般分組會跟聚合函數(shù)一起使用几于。

-- 按性別進行分組，求男生和女生數(shù)學(xué)的平均分
select sex, avg(math) from student3 group by sex;

效果如下

實際上是將每組的 math 求了平均,返回每組統(tǒng)計的結(jié)果

注意：當(dāng)我們使用某個字段分組,在查詢的時候也需要將這個字段查詢出來,否則看不到數(shù)據(jù)屬于哪組的

查詢男女各多少人

查詢所有數(shù)據(jù),

1. 按性別分組沿后。
2. 統(tǒng)計每組人數(shù)

select sex, count(*) from student3 group by sex;

查詢年齡大于 25 歲的人,按性別分組,統(tǒng)計每組的人數(shù)

1. 先過濾掉年齡小于 25 歲的人沿彭。
2. 再分組。
3. 最后統(tǒng)計每組的人數(shù)

select sex, count(*) from student3 where age > 25 group by sex ;

查詢年齡大于 25 歲的人得运，按性別分組膝蜈，統(tǒng)計每組的人數(shù)，并只顯示性別人數(shù)大于 2 的數(shù)據(jù)

-- 對分組查詢的結(jié)果再進行過濾
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2;

having 與 where 的區(qū)別

5.9-limit語句

準(zhǔn)備數(shù)據(jù)

INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES
(9,'唐僧',25,'男','長安',87,78),
(10,'孫悟空',18,'男','花果山',100,66),
(11,'豬八戒',22,'男','高老莊',58,78),
(12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎嶺',66,66),
(14,'蜘蛛精',23,'女','盤絲洞',88,88);

作用

LIMIT 是限制的意思，所以 LIMIT 的作用就是限制查詢記錄的條數(shù)肺素。

SELECT *|字段列表 [as 別名] FROM 表名 [WHERE 子句][GROUP BY 子句][HAVING 子句][ORDER BY 子 句][LIMIT 子句];

語法格式

LIMIT offset,length;

offset：起始行數(shù)恨锚，從 0 開始計數(shù)，如果省略倍靡，默認就是 0

length： 返回的行數(shù)

-- 查詢學(xué)生表中數(shù)據(jù)猴伶，從第 3 條開始顯示，顯示 6 條塌西。
select * from student3 limit 2,6;

使用場景

分頁：比如我們登錄京東他挎，淘寶，返回的商品信息可能有幾萬條捡需，不是一次全部顯示出來办桨。是一頁顯示固定的 條數(shù)。 假設(shè)我們每頁顯示 5 條記錄的方式來分頁

-- 如果第一個參數(shù)是 0 可以省略寫：
select * from student3 limit 5;
-- 最后如果不夠 5 條站辉，有多少顯示多少
select * from student3 limit 10,5;

第六章：數(shù)據(jù)庫表的約束

6.1-數(shù)據(jù)庫約束概述

約束的作用

對表中的數(shù)據(jù)進行限制呢撞，保證數(shù)據(jù)的正確性、有效性和完整性饰剥。一個表如果添加了約束殊霞，不正確的數(shù)據(jù)將無 法插入到表中。約束在創(chuàng)建表的時候添加比較合適捐川。

約束的種類

6.2-主鍵約束

主鍵的作用

用來唯一標(biāo)識數(shù)據(jù)庫中的每一條記錄

哪個字段應(yīng)該作為表的主鍵

通常不用業(yè)務(wù)字段作為主鍵脓鹃，單獨給每張表設(shè)計一個 id 的字段，把 id 作為主鍵古沥。主鍵是給數(shù)據(jù)庫和程序使用 的瘸右，不是給最終的客戶使用的娇跟。所以主鍵有沒有含義沒有關(guān)系，只要不重復(fù)太颤，非空就行苞俘。
如：身份證，學(xué)號不建議做成主鍵 龄章。

創(chuàng)建主鍵

1. 主鍵關(guān)鍵字： primary key
2. 主鍵的特點：
   • 非空 not null
   • 唯一
3. 創(chuàng)建主鍵的方式
   1. 在創(chuàng)建表的時候給字段添加主鍵 ：字段名 字段類型 PRIMARY KEY ;
   2. 在已有表中添加主鍵 ：ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

-- 創(chuàng)建表學(xué)生表 st5, 包含字段(id, name, age)將 id 做為主鍵
create table st5 (
 id int primary key, -- id 為主鍵
 name varchar(20),
 age int
)
desc st5;

刪除主鍵

-- 刪除 st5 表的主鍵
alter table st5 drop primary key;

添加主鍵

alter table st5 add primary key(id);

主鍵自增

主鍵如果讓我們自己添加很有可能重復(fù),我們通常希望在每次插入新記錄時,數(shù)據(jù)庫自動生成主鍵字段的值

AUTO_INCREMENT 表示自動增長(字段類型必須是整數(shù)類型)

-- 插入數(shù)據(jù)
insert into st6 (name,age) values ('小喬',18);
insert into st6 (name,age) values ('大喬',20);
-- 另一種寫法
insert into st6 values(null,'周瑜',35);
select * from st6;

修改自增長的默認值起始值

默認地 AUTO_INCREMENT 的開始值是 1吃谣，如果希望修改起始值,請使用下列 SQL 語法 。

創(chuàng)建時指定起始值

CREATE TABLE 表名(
列名 int primary key AUTO_INCREMENT
) AUTO_INCREMENT=起始值;

-- 指定起始值為 1000
create table st4 (
 id int primary key auto_increment,
 name varchar(20)
) auto_increment = 1000;
insert into st4 values (null, '孔明');

修改起始值

ALTER TABLE 表名 AUTO_INCREMENT=起始值;

alter table st4 auto_increment = 2000;

6.3-唯一約束

什么是唯一約束： 表中某一列不能出現(xiàn)重復(fù)的值

基本格式

字段名 字段類型 UNIQUE

演示

-- 創(chuàng)建學(xué)生表 st7, 包含字段(id, name),name 這一列設(shè)置唯一約束,不能出現(xiàn)同名的學(xué)生
create table st7 (
 id int,
 name varchar(20) unique
)
-- 添加一個同名的學(xué)生
insert into st7 values (1, '張三');
select * from st7;
-- Duplicate entry '張三' for key 'name'
insert into st7 values (2, '張三');
-- 重復(fù)插入多個 null 會怎樣做裙？
insert into st7 values (2, null);
insert into st7 values (3, null);
-- null 沒有數(shù)據(jù)岗憋，不存在重復(fù)的問題

6.4-非空約束

什么是非空約束：某一列不能為 null。

基本格式

字段名 字段類型 NOT NULL

-- 創(chuàng)建表學(xué)生表 st8, 包含字段(id,name,gender)其中 name 不能為 NULL
create table st8 (
    id int,
    name varchar(20) not null,
    gender char(1)
)

默認值

字段名 字段類型 DEFAULT 默認值

-- 創(chuàng)建一個學(xué)生表 st9锚贱，包含字段(id,name,address)仔戈， 地址默認值是廣州
create table st9 (
 id int,
 name varchar(20),
 address varchar(20) default '廣州'
)
-- 添加一條記錄,使用默認地址
insert into st9 values (1, '李四', default);
select * from st9;
insert into st9 (id,name) values (2, '李白');
-- 添加一條記錄,不使用默認地址
insert into st9 values (3, '李四光', '深圳');

注意

疑問:如果一個字段設(shè)置了非空與唯一約束，該字段與主鍵的區(qū)別?

1. 主鍵數(shù)在一個表中拧廊，只能有一個监徘。不能出現(xiàn)多個主鍵。主鍵可以單列吧碾，也可以是多列凰盔。
2. 自增長只能用在主鍵上。

6.5-外鍵約束

單表的缺點

創(chuàng)建一個員工表包含如下列(id, name, age, dep_name, dep_location),id 主鍵并自動增長,添加 5 條數(shù)據(jù)

CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_name VARCHAR(30),
dep_location VARCHAR(30)
);
-- 添加數(shù)據(jù)
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('張三', 20, '研發(fā)部', '廣州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研發(fā)部', '廣州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研發(fā)部', '廣州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '銷售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '銷售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '銷售部', '深圳');

以上表的缺點：

1. 數(shù)據(jù)冗余
2. 后期還會出現(xiàn)增刪改的問題

解決方案

-- 解決方案：分成 2 張表
-- 創(chuàng)建部門表(id,dep_name,dep_location)
-- 一方倦春，主表
create table department(
id int primary key auto_increment,
dep_name varchar(20),
dep_location varchar(20)
);
-- 創(chuàng)建員工表(id,name,age,dep_id)
-- 多方户敬，從表
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int -- 外鍵對應(yīng)主表的主鍵
)
-- 添加 2 個部門
insert into department values(null, '研發(fā)部','廣州'),(null, '銷售部', '深圳');
select * from department;
-- 添加員工,dep_id 表示員工所在的部門
INSERT INTO employee (NAME, age, dep_id) VALUES ('張三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
select * from employee;

新的問題：當(dāng)我們在 employee 的 dep_id 里面輸入不存在的部門,數(shù)據(jù)依然可以添加.但是并沒有對應(yīng)的部門， 實際應(yīng)用中不能出現(xiàn)這種情況溅漾。employee 的 dep_id 中的數(shù)據(jù)只能是 department 表中存在的 id

目標(biāo): 需要約束 dep_id 只能是 department 表中已經(jīng)存在 id

解決方式: 使用外鍵約束

什么是外鍵約束

什么是外鍵：在從表中與主表主鍵對應(yīng)的那一列山叮，如：員工表中的 dep_id

主表： 一方，用來約束別人的表

從表： 多方添履，被別人約束的表

創(chuàng)建約束的語法

• 新建表時增加外鍵： [CONSTRAINT][外鍵約束名稱] FOREIGN KEY(外鍵字段名) REFERENCES 主表名(主鍵字段名)
• 已有表增加外鍵：ALTER TABLE 從表 ADD [CONSTRAINT][外鍵約束名稱] FOREIGN KEY (外鍵字段名) REFERENCES 主表(主 鍵字段名);

-- 1) 刪除副表/從表 employee
drop table employee;
18 / 26
-- 2) 創(chuàng)建從表 employee 并添加外鍵約束 emp_depid_fk
-- 多方屁倔，從表
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- 外鍵對應(yīng)主表的主鍵
-- 創(chuàng)建外鍵約束
constraint emp_depid_fk foreign key (dep_id) references department(id)
)
-- 3) 正常添加數(shù)據(jù)
INSERT INTO employee (NAME, age, dep_id) VALUES ('張三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
select * from employee;

刪除外鍵

ALTER TABLE 從表 drop foreign key 外鍵名稱;

-- 刪除 employee 表的 emp_depid_fk 外鍵
alter table employee drop foreign key emp_depid_fk;

新的問題

select * from employee;
select * from department;
-- 要把部門表中的 id 值 2，改成 5暮胧，能不能直接更新呢锐借？
-- Cannot delete or update a parent row: a foreign key constraint fails
update department set id=5 where id=2;
-- 要刪除部門 id 等于 1 的部門, 能不能直接刪除呢？
-- Cannot delete or update a parent row: a foreign key constraint fails
delete from department where id=1;

級聯(lián)操作：在修改和刪除主表的主鍵時往衷，同時更新或刪除副表的外鍵值钞翔，稱為級聯(lián)操作

-- 刪除 employee 表，重新創(chuàng)建 employee 表，添加級聯(lián)更新和級聯(lián)刪除
drop table employee;
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- 外鍵對應(yīng)主表的主鍵
-- 創(chuàng)建外鍵約束
constraint emp_depid_fk foreign key (dep_id) references
 department(id) on update cascade on delete cascade
)
-- 再次添加數(shù)據(jù)到員工表和部門表
INSERT INTO employee (NAME, age, dep_id) VALUES ('張三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
-- 刪除部門表汰扭？能不能直接刪除稠肘？
drop table department;
-- 把部門表中 id 等于 1 的部門改成 id 等于 10
update department set id=10 where id=1;
select * from employee;
select * from department;
-- 刪除部門號是 2 的部門
delete from department where id=2;

6.6-數(shù)據(jù)約束小結(jié)

第七章：表與表之間的關(guān)系

7.1-概述

現(xiàn)實生活中，實體與實體之間肯定是有關(guān)系的笆包，比如：老公和老婆环揽，部門和員工，老師和學(xué)生等庵佣。那么我們 在設(shè)計表的時候歉胶，就應(yīng)該體現(xiàn)出表與表之間的這種關(guān)系！

表與表之間的三種關(guān)系

1. 一對多：最常用的關(guān)系 部門和員工
2. 多對多：學(xué)生選課表 和 學(xué)生表秧了， 一門課程可以有多個學(xué)生選擇跨扮，一個學(xué)生選擇多門課程
3. 一對一：相對使用比較少。員工表 簡歷表验毡， 公民表 護照表

7.2-一對多

一對多（1:n） 例如：班級和學(xué)生，部門和員工帝嗡，客戶和訂單晶通，分類和商品 一對多建表原則: 在從表(多方)創(chuàng)建一個字段,字段作為外鍵指向主表(一方)的主鍵 。

7.3-多對多

多對多（m:n） 例如：老師和學(xué)生哟玷，學(xué)生和課程狮辽，用戶和角色 多對多關(guān)系建表原則: 需要創(chuàng)建第三張表，中間表中至少兩個字段巢寡，這兩個字段分別作為外鍵指向各自一方的 主鍵喉脖。

7.4-一對一

一對一（1:1） 在實際的開發(fā)中應(yīng)用不多.因為一對一可以創(chuàng)建成一張表。 兩種建表原則：

1. 外鍵唯一 ：主表的主鍵和從表的外鍵（唯一）抑月，形成主外鍵關(guān)系树叽，外鍵唯一 UNIQUE
2. 外鍵是主鍵 ：主表的主鍵和從表的主鍵，形成主外鍵關(guān)系

7.5-一對多關(guān)系案例

需求：一個旅游線路分類中有多個旅游線路

界面

表與表的關(guān)系

演示

-- 創(chuàng)建旅游線路分類表 tab_category
-- cid 旅游線路分類主鍵谦絮，自動增長
-- cname 旅游線路分類名稱非空题诵，唯一，字符串 100
create table tab_category (
 cid int primary key auto_increment,
 cname varchar(100) not null unique
)
-- 添加旅游線路分類數(shù)據(jù)：
insert into tab_category (cname) values ('周邊游'), ('出境游'), ('國內(nèi)游'), ('港澳游');
select * from tab_category;
-- 創(chuàng)建旅游線路表 tab_route
/*
rid 旅游線路主鍵层皱，自動增長
rname 旅游線路名稱非空性锭，唯一，字符串 100
price 價格
rdate 上架時間叫胖，日期類型
cid 外鍵草冈，所屬分類
*/
create table tab_route(
 rid int primary key auto_increment,
 rname varchar(100) not null unique,
 price double,
 rdate date,
 cid int,
 foreign key (cid) references tab_category(cid)
)
-- 添加旅游線路數(shù)據(jù)
INSERT INTO tab_route VALUES
(NULL, '【廈門+鼓浪嶼+南普陀寺+曾厝垵 高鐵 3 天 惠貴團】嘗味友鴨面線 住 1 晚鼓浪嶼', 1499,
'2018-01-27', 1),
(NULL, '【浪漫桂林 陽朔西街高鐵 3 天純玩 高級團】城徽象鼻山 興坪漓江 西山公園', 699, '2018-02-
22', 3),
(NULL, '【爆款￥1699 秒殺】泰國 曼谷 芭堤雅 金沙島 杜拉拉水上市場 雙飛六天【含送簽費 泰風(fēng)情 廣州
往返 特價團】', 1699, '2018-01-27', 2),
23 / 26
(NULL, '【經(jīng)典?獅航 ￥2399 秒殺】巴厘島雙飛五天 抵玩【廣州往返 特價團】', 2399, '2017-12-23',
2),
(NULL, '香港迪士尼樂園自由行 2 天【永東跨境巴士廣東至迪士尼去程交通+迪士尼一日門票+香港如心海景酒店
暨會議中心標(biāo)準(zhǔn)房 1 晚住宿】', 799, '2018-04-10', 4);

7.6-多對多關(guān)系案例

案例描述

一個用戶收藏多個線路，一個線路被多個用戶收藏 。

演示

/*
創(chuàng)建用戶表 tab_user
uid 用戶主鍵怎棱，自增長
username 用戶名長度 100哩俭，唯一，非空
password 密碼長度 30蹄殃，非空
name 真實姓名長度 100
birthday 生日
sex 性別携茂，定長字符串 1
telephone 手機號，字符串 11
email 郵箱诅岩，字符串長度 100
*/
create table tab_user (
 uid int primary key auto_increment,
 username varchar(100) unique not null,
 password varchar(30) not null,
 name varchar(100),
 birthday date,
 sex char(1) default '男',
 telephone varchar(11),
 email varchar(100)
)
-- 添加用戶數(shù)據(jù)
INSERT INTO tab_user VALUES
(NULL, 'cz110', 123456, '老王', '1977-07-07', '男', '13888888888', '66666@qq.com'),
(NULL, 'cz119', 654321, '小王', '1999-09-09', '男', '13999999999', '99999@qq.com');
select * from tab_user;
/*
創(chuàng)建收藏表 tab_favorite
rid 旅游線路 id讳苦，外鍵
date 收藏時間
uid 用戶 id，外鍵
rid 和 uid 不能重復(fù)吩谦，設(shè)置復(fù)合主鍵鸳谜，同一個用戶不能收藏同一個線路兩次
*/
create table tab_favorite (
 rid int,
 date datetime,
 uid int,
 -- 創(chuàng)建復(fù)合主鍵
 primary key(rid,uid),
 foreign key (rid) references tab_route(rid),
 foreign key(uid) references tab_user(uid)
)
-- 增加收藏表數(shù)據(jù)
INSERT INTO tab_favorite VALUES
(1, '2018-01-01', 1), -- 老王選擇廈門
(2, '2018-02-11', 1), -- 老王選擇桂林
(3, '2018-03-21', 1), -- 老王選擇泰國
(2, '2018-04-21', 2), -- 小王選擇桂林
(3, '2018-05-08', 2), -- 小王選擇泰國
(5, '2018-06-02', 2); -- 小王選擇迪士尼
select * from tab_favorite;

7.7-總結(jié)

第八章：數(shù)據(jù)庫設(shè)計

8.1-數(shù)據(jù)規(guī)范化

什么是范式

好的數(shù)據(jù)庫設(shè)計對數(shù)據(jù)的存儲性能和后期的程序開發(fā)咐扭，都會產(chǎn)生重要的影響。建立科學(xué)的滑废，規(guī)范的數(shù)據(jù)庫就需 要滿足一些規(guī)則來優(yōu)化數(shù)據(jù)的設(shè)計和存儲蝗肪，這些規(guī)則就稱為范式。

三大范式

目前關(guān)系數(shù)據(jù)庫有六種范式：第一范式（1NF）蠕趁、第二范式（2NF）薛闪、第三范式（3NF）、巴斯-科德范式（BCNF）俺陋、 第四范式(4NF）和第五范式（5NF豁延，又稱完美范式）。 滿足最低要求的范式是第一范式（1NF）腊状。在第一范式的基礎(chǔ)上進一步滿足更多規(guī)范要求的稱為第二范式（2NF）诱咏， 其余范式以次類推。一般說來缴挖，數(shù)據(jù)庫只需滿足第三范式(3NF）就行了袋狞。

8.2-1NF

概念

數(shù)據(jù)庫表的每一列都是不可分割的原子數(shù)據(jù)項，不能是集合醇疼、數(shù)組等非原子數(shù)據(jù)項硕并。即表中的某個列有多個值 時，必須拆分為不同的列秧荆。簡而言之倔毙，第一范式每一列不可再拆分，稱為原子性乙濒。

班級表

8.3-2NF

概念

在滿足第一范式的前提下陕赃，表中的每一個字段都完全依賴于主鍵卵蛉。 所謂完全依賴是指不能存在僅依賴主鍵一部分的列。簡而言之么库，第二范式就是在第一范式的基礎(chǔ)上所有列完全 依賴于主鍵列傻丝。當(dāng)存在一個復(fù)合主鍵包含多個主鍵列的時候，才會發(fā)生不符合第二范式的情況诉儒。比如有一個主鍵有 兩個列葡缰，不能存在這樣的屬性，它只依賴于其中一個列忱反，這就是不符合第二范式泛释。

第二范式的特點：

1. 一張表只描述一件事情。
2. 表中的每一列都完全依賴于主鍵

示例

借書證表

分成兩張表

8.4-3NF

概念

在滿足第二范式的前提下温算，表中的每一列都直接依賴于主鍵怜校，而不是通過其它的列來間接依賴于主鍵。 簡而言之注竿，第三范式就是所有列不依賴于其它非主鍵列茄茁，也就是在滿足 2NF 的基礎(chǔ)上，任何非主列不得傳遞 依賴于主鍵巩割。所謂傳遞依賴裙顽，指的是如果存在"A → B → C"的決定關(guān)系，則 C 傳遞依賴于 A宣谈。因此锦庸，滿足第三范 式的數(shù)據(jù)庫表應(yīng)該不存在如下依賴關(guān)系：主鍵列 → 非主鍵列 x → 非主鍵列 y

示例

學(xué)生信息表

存在傳遞的決定關(guān)系： 學(xué)號→所在學(xué)院 →學(xué)院地點

拆分成兩張表

8.5-三大范式小結(jié)

第九章：數(shù)據(jù)庫備份和還原

9.1-備份的應(yīng)用場景

在服務(wù)器進行數(shù)據(jù)傳輸、數(shù)據(jù)存儲和數(shù)據(jù)交換牙言，就有可能產(chǎn)生數(shù)據(jù)故障酸钦。比如發(fā)生意外停機或存儲介質(zhì)損壞。 這時咱枉，如果沒有采取數(shù)據(jù)備份和數(shù)據(jù)恢復(fù)手段與措施卑硫，就會導(dǎo)致數(shù)據(jù)的丟失，造成的損失是無法彌補與估量的蚕断。

9.2-備份與還原的語句

備份格式： DOS 下欢伏，未登錄的時候。這是一個可執(zhí)行文件 exe亿乳，在 bin 文件夾

mysqldump -u 用戶名 -p 密碼 數(shù)據(jù)庫 > 文件的路徑

導(dǎo)出結(jié)果：數(shù)據(jù)庫中的所有表和數(shù)據(jù)都會導(dǎo)出成 SQL 語句

-- 備份 day21 數(shù)據(jù)庫中的數(shù)據(jù)到 d:\day21.sql 文件中
mysqldump -uroot -proot day21 > d:/day21.sql

還原格式：mysql 中的命令硝拧，需要登錄后才可以操作

USE 數(shù)據(jù)庫;

SOURCE 導(dǎo)入文件的路徑;

還原 day21 數(shù)據(jù)庫中的數(shù)據(jù)径筏，注意：還原的時候需要先登錄 MySQL,并選中對應(yīng)的數(shù)據(jù)庫。

1. 刪除 day21 數(shù)據(jù)庫中的所有表
2. 登錄 MySQL
3. 選中數(shù)據(jù)庫
4. 使用 SOURCE 命令還原數(shù)據(jù)
5. 查看還原結(jié)果

use day21;
source d:/day21.sql;

9.3-圖形化界面?zhèn)浞菖c還原

備份數(shù)據(jù)庫中的數(shù)據(jù)

1. 選中數(shù)據(jù)庫障陶，右鍵 ”備份/導(dǎo)出”
2. 指定導(dǎo)出路徑滋恬，保存成.sql 文件即可。

還原數(shù)據(jù)庫中的數(shù)據(jù)

1. 刪除數(shù)據(jù)庫
2. 數(shù)據(jù)庫列表區(qū)域右鍵“執(zhí)行 SQL 腳本”抱究， 指定要執(zhí)行的 SQL 文件恢氯，執(zhí)行即可

第十章：多表查詢

10.1-多表查詢介紹

數(shù)據(jù)準(zhǔn)備

# 創(chuàng)建部門表
create table dept(
 id int primary key auto_increment,
 name varchar(20)
)
insert into dept (name) values ('開發(fā)部'),('市場部'),('財務(wù)部');
# 創(chuàng)建員工表
create table emp (
 id int primary key auto_increment,
 name varchar(10),
 gender char(1), -- 性別
 salary double, -- 工資
 join_date date, -- 入職日期
 dept_id int,
 foreign key (dept_id) references dept(id) -- 外鍵，關(guān)聯(lián)部門表(部門表的主鍵)
)
insert into emp(name,gender,salary,join_date,dept_id) values('孫悟空','男
',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('豬八戒','男
',3600,'2010-12-02',2);
insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-
08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女
',5000,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女
',4500,'2011-03-14',1);

為什么要多表查詢鼓寺？

比如：我們想查詢孫悟空的名字和他所在的部門的名字勋拟，則需要使用多表查詢。

如果一條 SQL 語句查詢多張表侄刽，因為查詢結(jié)果在多張不同的表中指黎。每張表取 1 列或多列。

多表查詢的分類

10.2-笛卡爾積現(xiàn)象

什么是笛卡爾積現(xiàn)象州丹？

-- 需求：查詢所有的員工和所有的部門
select * from emp,dept;

結(jié)果分析

如何清除笛卡爾積無用的數(shù)據(jù)影響醋安？

我們發(fā)現(xiàn)不是所有的數(shù)據(jù)組合都是有用的，只有員工表.dept_id = 部門表.id 的數(shù)據(jù)才是有用的墓毒。所以需要 通過條件過濾掉沒用的數(shù)據(jù)吓揪。

-- 設(shè)置過濾條件 Column 'id' in where clause is ambiguous
select * from emp,dept where id=5;
select * from emp,dept where emp.`dept_id` = dept.`id`;
-- 查詢員工和部門的名字
select emp.`name`, dept.`name` from emp,dept where emp.`dept_id` = dept.`id`;

10.3-內(nèi)連接

用左邊表的記錄去匹配右邊表的記錄，如果符合條件的則顯示所计。如：從表.外鍵=主表.主鍵

隱式內(nèi)連接

隱式內(nèi)連接：看不到 JOIN 關(guān)鍵字柠辞，條件使用 WHERE 指定

SELECT 字段名 FROM 左表, 右表 WHERE 條件

select * from emp,dept where emp.`dept_id` = dept.`id`;

顯示內(nèi)連接

顯示內(nèi)連接：使用 INNER JOIN ... ON 語句, 可以省略 INNER

SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 條件

需求： 查詢唐僧的信息，顯示員工 id主胧，姓名吹埠，性別干茉，工資和所在的部門名稱，我們發(fā)現(xiàn)需要聯(lián)合 2 張表同時才能 查詢出需要的數(shù)據(jù)，使用內(nèi)連接 堆生。

1.確定查詢哪些表

select * from emp inner join dept;

2.確定表連接條件硫狞，員工表.dept_id = 部門表.id 的數(shù)據(jù)才是有效的

select * from emp e inner join dept d on e.`dept_id` = d.`id`;

3.確定查詢條件产镐，我們查詢的是唐僧的信息尤莺，員工表.name='唐僧'

select * from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';

4.確定查詢字段，查詢唐僧的信息囤官，顯示員工 id冬阳，姓名，性別党饮，工資和所在的部門名稱

select e.`id`,e.`name`,e.`gender`,e.`salary`,d.`name` from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';

5.我們發(fā)現(xiàn)寫表名有點長肝陪，可以給表取別名，顯示的字段名也使用別名

select e.`id` 編號,e.`name` 姓名,e.`gender` 性別,e.`salary` 工資,d.`name` 部門名字 from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';

內(nèi)連接總結(jié)

1. 確定查詢哪些表
2. 確定表連接的條件
3. 確定查詢的條件
4. 確定查詢的字段

10.4-左外連接

左外連接：使用 LEFT OUTER JOIN ... ON劫谅，OUTER 可以省略

SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 條件

用左邊表的記錄去匹配右邊表的記錄见坑，如果符合條件的則顯示嚷掠；否則，顯示 NULL 可以理解為：在內(nèi)連接的基礎(chǔ)上保證左表的數(shù)據(jù)全部顯示(左表是部門荞驴，右表員工)

-- 在部門表中增加一個銷售部
insert into dept (name) values ('銷售部');
select * from dept;
-- 使用內(nèi)連接查詢
select * from dept d inner join emp e on d.`id` = e.`dept_id`;
-- 使用左外連接查詢
select * from dept d left join emp e on d.`id` = e.`dept_id`;

10.5-右外連接

右外連接：使用 RIGHT OUTER JOIN ... ON不皆，OUTER 可以省略

SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 條件

用右邊表的記錄去匹配左邊表的記錄，如果符合條件的則顯示熊楼；否則霹娄，顯示 NULL 可以理解為：在內(nèi)連接的基礎(chǔ)上保證右表的數(shù)據(jù)全部顯示

-- 在員工表中增加一個員工
insert into emp values (null, '沙僧','男',6666,'2013-12-05',null);
select * from emp;
-- 使用內(nèi)連接查詢
select * from dept inner join emp on dept.`id` = emp.`dept_id`;
-- 使用右外連接查詢
select * from dept right join emp on dept.`id` = emp.`dept_id`;

第十一章：子查詢

11.1 什么是子查詢

子查詢的概念

1. 一個查詢的結(jié)果做為另一個查詢的條件
2. 有查詢的嵌套，內(nèi)部的查詢稱為子查詢
3. 子查詢要使用括號

演示

-- 需求：查詢開發(fā)部中有哪些員工
select * from emp;
-- 通過兩條語句查詢
select id from dept where name='開發(fā)部' ;
select * from emp where dept_id = 1;
-- 使用子查詢
select * from emp where dept_id = (select id from dept where name='市場部');

11.2-子查詢的三種情況

子查詢的結(jié)果是單行單列

子查詢的結(jié)果是多行單列

**子查詢的結(jié)果是多行多列 **

11.3-子查詢結(jié)果是一個值

子查詢結(jié)果只要是單行單列鲫骗，肯定在 WHERE 后面作為條件犬耻，父查詢使用：比較運算符，如：> 执泰、<枕磁、<>、= 等

SELECT 查詢字段 FROM 表 WHERE 字段=（子查詢）;

**案例：查詢工資最高的員工是誰术吝？ **

-- 1) 查詢最高工資是多少
select max(salary) from emp;
-- 2) 根據(jù)最高工資到員工表查詢到對應(yīng)的員工信息
select * from emp where salary = (select max(salary) from emp);

查詢工資小于平均工資的員工有哪些计济？

-- 1) 查詢平均工資是多少
select avg(salary) from emp;
-- 2) 到員工表查詢小于平均的員工信息
select * from emp where salary < (select avg(salary) from emp);

11.4-子查詢結(jié)果是多行單列

子查詢結(jié)果是單例多行，結(jié)果集類似于一個數(shù)組排苍，父查詢使用 IN 運算符

SELECT 查詢字段 FROM 表 WHERE 字段 IN （子查詢）;

**查詢工資大于 5000 的員工沦寂，來自于哪些部門的名字 **

-- 先查詢大于 5000 的員工所在的部門 id
select dept_id from emp where salary > 5000;
-- 再查詢在這些部門 id 中部門的名字 Subquery returns more than 1 row
select name from dept where id = (select dept_id from emp where salary > 5000);
select name from dept where id in (select dept_id from emp where salary > 5000);

查詢開發(fā)部與財務(wù)部所有的員工信息

-- 先查詢開發(fā)部與財務(wù)部的 id
select id from dept where name in('開發(fā)部','財務(wù)部');
-- 再查詢在這些部門 id 中有哪些員工
select * from emp where dept_id in (select id from dept where name in('開發(fā)部','財務(wù)部'));

11.5-子查詢的結(jié)果是多行多列

子查詢結(jié)果只要是多列，肯定在 FROM 后面作為表

SELECT 查詢字段 FROM （子查詢） 表別名 WHERE 條件;

子查詢作為表需要取別名淘衙，否則這張表沒有名稱則無法訪問表中的字段

查詢出 2011 年以后入職的員工信息传藏，包括部門名稱

-- 查詢出 2011 年以后入職的員工信息，包括部門名稱
-- 在員工表中查詢 2011-1-1 以后入職的員工
select * from emp where join_date >='2011-1-1';
-- 查詢所有的部門信息彤守，與上面的虛擬表中的信息組合毯侦，找出所有部門 id 等于的 dept_id
select * from dept d, (select * from emp where join_date >='2011-1-1') e where d.`id`= e.dept_id ;

也可以使用表連接

select * from emp inner join dept on emp.`dept_id` = dept.`id` where join_date >='2011-1-1';
select * from emp inner join dept on emp.`dept_id` = dept.`id` and join_date >='2011-1-1';

11.6-總結(jié)

1. 子查詢結(jié)果只要是單列，則在 WHERE 后面作為條件
2. 子查詢結(jié)果只要是多列具垫，則在 FROM 后面作為表進行二次查詢

第十二章：事務(wù)

12.1-事務(wù)概述

事務(wù)的應(yīng)用場景

什么是事務(wù)： 在實際的開發(fā)過程中叫惊，一個業(yè)務(wù)操作如：轉(zhuǎn)賬，往往是要多次訪問數(shù)據(jù)庫才能完成的做修。轉(zhuǎn) 賬是一個用戶扣錢，另一個用戶加錢抡草。如果其中有一條 SQL 語句出現(xiàn)異常饰及，這條 SQL 就可能執(zhí)行失敗。

事務(wù)執(zhí)行是一個整體康震，所有的 SQL 語句都必須執(zhí)行成功燎含。如果其中有 1 條 SQL 語句出現(xiàn)異常，則所有的 SQL 語句都要回滾腿短，整個業(yè)務(wù)執(zhí)行失敗屏箍。

轉(zhuǎn)賬操作

模擬張三給李四轉(zhuǎn) 500 元錢绘梦，一個轉(zhuǎn)賬的業(yè)務(wù)操作最少要執(zhí)行下面的 2 條語句： 張三賬號-500 李四賬號+500

-- 創(chuàng)建數(shù)據(jù)表
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
-- 添加數(shù)據(jù)
INSERT INTO account (NAME, balance) VALUES ('張三', 1000), ('李四', 1000);
-- 張三賬號-500
update account set balance = balance - 500 where name='張三';
-- 李四賬號+500
update account set balance = balance + 500 where name='李四';

假設(shè)當(dāng)張三賬號上-500 元,服務(wù)器崩潰了。李四的賬號并沒有+500 元赴魁，數(shù)據(jù)就出現(xiàn)問題了卸奉。我們需要保證其中 一條 SQL 語句出現(xiàn)問題，整個轉(zhuǎn)賬就算失敗颖御。只有兩條 SQL 都成功了轉(zhuǎn)賬才算成功榄棵。這個時候就需要用到事務(wù)。

12.2-手動提交事務(wù)

MYSQL 中可以有兩種方式進行事務(wù)的操作：

1. 手動提交事務(wù)
2. 自動提交事務(wù)

手動提交事務(wù)的SQL

手動提交事務(wù)的執(zhí)行過程

1. 執(zhí)行成功的情況： 開啟事務(wù) → 執(zhí)行多條 SQL 語句 → 成功提交事務(wù)
2. 執(zhí)行失敗的情況： 開啟事務(wù) → 執(zhí)行多條 SQL 語句 → 事務(wù)的回滾

案例1：模擬張三給李四轉(zhuǎn) 500 元錢（成功） 目前數(shù)據(jù)庫數(shù)據(jù)如下

1- 使用 DOS 控制臺進入 MySQL

2- 執(zhí)行以下 SQL 語句： 1.開啟事務(wù)潘拱， 2.張三賬號-500疹鳄， 3.李四賬號+500

3- 使用 SQLYog 查看數(shù)據(jù)庫：發(fā)現(xiàn)數(shù)據(jù)并沒有改變

4- 在控制臺執(zhí)行 commit 提交事務(wù)：

5- 使用 SQLYog 查看數(shù)據(jù)庫：發(fā)現(xiàn)數(shù)據(jù)改變

**案例演示 2：事務(wù)回滾 **

模擬張三給李四轉(zhuǎn) 500 元錢（失敗） 目前數(shù)據(jù)庫數(shù)據(jù)如下：

1- 在控制臺執(zhí)行以下 SQL 語句：1.開啟事務(wù)芦岂， 2.張三賬號-500

2- 使用 SQLYog 查看數(shù)據(jù)庫：發(fā)現(xiàn)數(shù)據(jù)并沒有改變

3- 在控制臺執(zhí)行 rollback 回滾事務(wù)：

4- 使用 SQLYog 查看數(shù)據(jù)庫：發(fā)現(xiàn)數(shù)據(jù)沒有改變

總結(jié): 如果事務(wù)中 SQL 語句沒有問題瘪弓，commit 提交事務(wù)，會對數(shù)據(jù)庫數(shù)據(jù)的數(shù)據(jù)進行改變禽最。 如果事務(wù)中 SQL 語句有問題腺怯，rollback 回滾事務(wù)，會回退到開啟事務(wù)時的狀態(tài)弛随。

12.3-自動提交事務(wù)

概述

MySQL 默認每一條 DML(增刪改)語句都是一個單獨的事務(wù)瓢喉，每條語句都會自動開啟一個事務(wù)，語句執(zhí)行完畢 自動提交事務(wù)舀透，MySQL 默認開始自動提交事務(wù)

案例：自動提交事務(wù)

1. 將金額重置為 1000
2. 更新其中某一個賬戶
3. 使用 SQLYog 查看數(shù)據(jù)庫：發(fā)現(xiàn)數(shù)據(jù)已經(jīng)改變

取消自動提交

查看 MySQL 是否開啟自動提交事務(wù)

@@表示全局變量栓票，1 表示開啟，0 表示關(guān)閉

**取消自動提交事務(wù) **

執(zhí)行更新語句愕够，使用 SQLYog 查看數(shù)據(jù)庫走贪，發(fā)現(xiàn)數(shù)據(jù)并沒有改變 ,在控制臺執(zhí)行 commit 提交任務(wù)

12.4-事務(wù)原理

事務(wù)開啟之后, 所有的操作都會臨時保存到事務(wù)日志中, 事務(wù)日志只有在得到 commit 命令才會同步到數(shù)據(jù)表 中，其他任何情況都會清空事務(wù)日志(rollback惑芭，斷開連接)

原理圖

事務(wù)的步驟

1. 客戶端連接數(shù)據(jù)庫服務(wù)器坠狡，創(chuàng)建連接時創(chuàng)建此用戶臨時日志文件
2. 開啟事務(wù)以后，所有的操作都會先寫入到臨時日志文件中
3. 所有的查詢操作從表中查詢遂跟，但會經(jīng)過日志文件加工后才返回
4. 如果事務(wù)提交則將日志文件中的數(shù)據(jù)寫到表中逃沿，否則清空日志文件

12.5-回滾點

什么是回滾點

在某些成功的操作完成之后，后續(xù)的操作有可能成功有可能失敗幻锁，但是不管成功還是失敗凯亮，前面操作都已經(jīng)成 功，可以在當(dāng)前成功的位置設(shè)置一個回滾點哄尔〖傧可以供后續(xù)失敗操作返回到該位置，而不是返回所有操作岭接，這個點稱 之為回滾點富拗。

回滾點的操作語句

具體操作

1. 將數(shù)據(jù)還原到 1000

2. 開啟事務(wù)

3. 讓張三賬號減 3 次錢臼予，每次 10 塊

4. 設(shè)置回滾點：savepoint three_times;

5. 讓張三賬號減 4 次錢，每次 10 塊

6. 回到回滾點：rollback to three_times;

7. 分析執(zhí)行過程

設(shè)置回滾點可以讓我們在失敗的時候回到回滾點啃沪，而不是回到事務(wù)開啟的時候粘拾。

12.6-事務(wù)的四大特性

12.7-事務(wù)的隔離級別

隔離級別介紹

概述

事務(wù)在操作時的理想狀態(tài)： 所有的事務(wù)之間保持隔離，互不影響河泳。因為并發(fā)操作沃呢，多個用戶同時訪問同一個 數(shù)據(jù)〔鸹樱可能引發(fā)并發(fā)訪問的問題：

MySQL 數(shù)據(jù)庫有四種隔離級別

上面的級別最低，下面的級別最高汉矿∑榉唬“是”表示會出現(xiàn)這種問題，“否”表示不會出現(xiàn)這種問題洲拇。

隔離級別越高奈揍，性能越差，安全性越高赋续。

MySQL 事務(wù)隔離級別相關(guān)的命令

1. 查詢隔離級別 select @@tx_isolation;
2. 設(shè)置隔離級別 set global transaction isolation level 級別字符串;

設(shè)置事務(wù)隔離級別打月，需要退出 MySQL 再重新登錄才能看到隔離級別的變化

臟讀問題及解決方案演示

演示

將數(shù)據(jù)進行恢復(fù)：UPDATE account SET balance = 1000;

【01-打開 A 窗口登錄 MySQL，設(shè)置全局的隔離級別為最低】

mysql -uroot -proot
set global transaction isolation level read uncommitted;

【02-打開 B 窗口,AB 窗口都開啟事務(wù)】

【03-A 窗口更新 2 個人的賬戶數(shù)據(jù)蚕捉，未提交】

update account set balance=balance-500 where id=1;
update account set balance=balance+500 where id=2;

【04-B 窗口查詢賬戶】

【05-A 窗口回滾 】

【06-B 窗口查詢賬戶，錢沒了 】

臟讀非常危險的柴淘，比如張三向李四購買商品迫淹，張三開啟事務(wù)秘通，向李四賬號轉(zhuǎn)入 500 塊，然后打電話給李四說錢 已經(jīng)轉(zhuǎn)了敛熬。李四一查詢錢到賬了肺稀，發(fā)貨給張三。張三收到貨后回滾事務(wù)应民，李四的再查看錢沒了话原。

解決臟讀的問題：將全局的隔離級別進行提升

將數(shù)據(jù)進行恢復(fù)：

UPDATE account SET balance = 1000;

【01-在 A 窗口設(shè)置全局的隔離級別為 read committed 】

set global transaction isolation level read committed;

【02-B 窗口退出 MySQL，B 窗口再進入 MySQL 】

【03-AB 窗口同時開啟事務(wù) 】

【04-A 更新 2 個人的賬戶诲锹，未提交 】

update account set balance=balance-500 where id=1;
update account set balance=balance+500 where id=2;

【05-B 窗口查詢賬戶】

【06-A 窗口 commit 提交事務(wù) 】

【08-B 窗口查看賬戶 】

結(jié)論：read committed 的方式可以避免臟讀的發(fā)生

不可重復(fù)讀問題及解決方案

問題演示

將數(shù)據(jù)進行恢復(fù)：

UPDATE account SET balance = 1000;

【01-開啟 A 窗口 】

set global transaction isolation level read committed;

【02-開啟 B 窗口繁仁，在 B 窗口開啟事務(wù) 】

start transaction;
select * from account;

【03-在 A 窗口開啟事務(wù)，并更新數(shù)據(jù) 】

start transaction;
update account set balance=balance+500 where id=1;
commit;

【04-B 窗口查詢 】

兩次查詢輸出的結(jié)果不同归园，到底哪次是對的黄虱？不知道以哪次為準(zhǔn)。 很多人認為這種情況就對了庸诱，無須困惑捻浦， 當(dāng)然是后面的為準(zhǔn)。我們可以考慮這樣一種情況桥爽，比如銀行程序需要將查詢結(jié)果分別輸出到電腦屏幕和發(fā)短信給客 戶朱灿，結(jié)果在一個事務(wù)中針對不同的輸出目的地進行的兩次查詢不一致，導(dǎo)致文件和屏幕中的結(jié)果不一致钠四，銀行工作 人員就不知道以哪個為準(zhǔn)了盗扒。

解決不可重復(fù)讀的問題

將全局的隔離級別進行提升為：repeatable read 將數(shù)據(jù)進行恢復(fù)：

UPDATE account SET balance = 1000;

【01-A 窗口設(shè)置隔離級別為：repeatable read 】

set global transaction isolation level repeatable read;

【02-B 窗口退出 MySQL，B 窗口再進入 MySQL 形导，開啟事務(wù)环疼，查詢數(shù)據(jù)】

start transaction;
select * from account

【03-A 窗口更新數(shù)據(jù) 】

start transaction;
update account set balance=balance+500 where id=1;
commit;

【04-B 窗口查詢 】

結(jié)論：同一個事務(wù)中為了保證多次查詢數(shù)據(jù)一致，必須使用 repeatable read 隔離級別 朵耕。

幻讀的演示

在 MySQL 中無法看到幻讀的效果炫隶。

但我們可以將事務(wù)隔離級別設(shè)置到最高，以擋住幻讀的發(fā)生 將數(shù)據(jù)進行恢復(fù)：

UPDATE account SET balance = 1000;

【01-開啟 A 窗口 】

set global transaction isolation level serializable; -- 設(shè)置隔離級別為最高

【02-A 窗口退出 MySQL阎曹，A 窗口重新登錄 MySQL 】

start transaction;
select count(*) from account;

【03-再開啟 B 窗口伪阶，登錄 MySQL 】

【04-在 B 窗口中開啟事務(wù)，添加一條記錄 】

start transaction; -- 開啟事務(wù)
insert into account (name,balance) values ('LaoWang', 500);

【05-在 A 窗口中 commit 提交事務(wù)处嫌，B 窗口中 insert 語句會在 A 窗口事務(wù)提交后立馬運行 】

【06-在 A 窗口中接著查詢栅贴，發(fā)現(xiàn)數(shù)據(jù)不變 】

【07-B 窗口中 commit 提交當(dāng)前事務(wù) 】

【08-A 窗口就能看到最新的數(shù)據(jù) 】

結(jié)論：使用 serializable 隔離級別，一個事務(wù)沒有執(zhí)行完熏迹，其他事務(wù)的 SQL 執(zhí)行不了檐薯，可以擋住幻讀

第十三章：DCL

我們現(xiàn)在默認使用的都是 root 用戶，超級管理員，擁有全部的權(quán)限坛缕。但是墓猎，一個公司里面的數(shù)據(jù)庫服務(wù)器上面 可能同時運行著很多個項目的數(shù)據(jù)庫。所以赚楚，我們應(yīng)該可以根據(jù)不同的項目建立不同的用戶毙沾，分配不同的權(quán)限來管 理和維護數(shù)據(jù)庫。 注：mysqld 是 MySQL 的主程序宠页，服務(wù)器端左胞。mysql 是 MySQL 的命令行工具，客戶端举户。

13.1-創(chuàng)建用戶

語法

CREATE USER '用戶名'@'主機名' IDENTIFIED BY '密碼';

關(guān)鍵字說明

具體操作

-- 創(chuàng)建 user1 用戶兄淫，只能在 localhost 這個服務(wù)器登錄 mysql 服務(wù)器屯远，密碼為 123 
create user 'user1'@'localhost' identified by '123';
-- 創(chuàng)建 user2 用戶可以在任何電腦上登錄 mysql 服務(wù)器，密碼為 123
create user 'user2'@'%' identified by '123';

注：創(chuàng)建的用戶名都在 mysql 數(shù)據(jù)庫中的 user 表中可以查看到捕虽，密碼經(jīng)過了加密慨丐。

13.2-給用戶授權(quán)

用戶創(chuàng)建之后，沒什么權(quán)限泄私！需要給用戶授權(quán)

語法

GRANT 權(quán)限 1, 權(quán)限 2... ON 數(shù)據(jù)庫名.表名 TO '用戶名'@'主機名';

關(guān)鍵字說明

具體操作

-- 給 user1 用戶分配對 test 這個數(shù)據(jù)庫操作的權(quán)限：創(chuàng)建表梧奢，修改表，插入記錄演痒，更新記錄亲轨，查詢
grant create,alter,insert,update,select on test.* to 'user1'@'localhost';
-- 給 user2 用戶分配所有權(quán)限，對所有數(shù)據(jù)庫的所有表
grant all on *.* to 'user2'@'%';
-- 注：用戶名和主機名要與上面創(chuàng)建的相同鸟顺，要加單引號惦蚊。

13.3-撤銷授權(quán)

語法

REVOKE 權(quán)限 1, 權(quán)限 2... ON 數(shù)據(jù)庫.表名 revoke all on test.* from 'user1'@'localhost'; '用戶名'@'主機 名';

具體操作

-- 撤銷 user1 用戶對 test 數(shù)據(jù)庫所有表的操作的權(quán)限
revoke all on test.* from 'user1'@'localhost';

13.4-查看權(quán)限

語法

SHOW GRANTS FOR '用戶名'@'主機名';

具體操作

注：usage 是指連接（登陸）權(quán)限巫湘，建立一個用戶，就會自動授予其 usage 權(quán)限（默認授予）昏鹃。

13.5-刪除用戶

語法

DROP USER '用戶名'@'主機名';

具體操作

-- 刪除 user2
drop user 'user2'@'%';

13.6-修改管理員密碼

語法

mysqladmin -uroot -p password 新密碼

注意：需要在未登陸 MySQL 的情況下操作尚氛，新密碼不需要加上引號。

具體操作

1. 將 root 管理員的新密碼改成 123456

2. 要求輸入舊密碼

3. 使用新密碼登錄

13.7-修改普通用戶密碼

語法

set password for '用戶名'@'主機名' = password('新密碼');

注意：需要在登陸 MySQL 的情況下操作洞渤，新密碼要加單引號阅嘶。

具體操作

1. 將'user1'@'localhost'的密碼改成'666666'

2. 使用新密碼登錄，老密碼登錄不了