內(nèi)容提綱
-
DQL:查詢語(yǔ)句
- 排序查詢
- 聚合函數(shù)
- 分組查詢
- 分頁(yè)查詢
約束
多表之間的關(guān)系
范式
數(shù)據(jù)庫(kù)的備份和還原
DQL:查詢語(yǔ)句
-
排序查詢
- 語(yǔ)法：order by 子句
- 例：order by 排序字段1 排序方式1 惠啄， 排序字段2 排序方式2...
- 排序方式：
- ASC：升序烫止，默認(rèn)的肝谭。
- DESC：降序。
- 注意：
- 如果有多個(gè)排序條件燕差，則當(dāng)前邊的條件值一樣時(shí)，才會(huì)判斷第二條件辞槐。
- 語(yǔ)法：order by 子句
-
聚合函數(shù)：將一列數(shù)據(jù)作為一個(gè)整體刹勃，進(jìn)行縱向的計(jì)算。
- count：計(jì)算個(gè)數(shù)
- 一般選擇非空的列：主鍵
- count(*)
- max：計(jì)算最大值
- SELECT MAX(math) FROM student;
- min：計(jì)算最小值
- SELECT MIN(math) FROM student;
- sum：計(jì)算和
- SELECT SUM(english) FROM student;
- avg：計(jì)算平均值
- SELECT AVG(math) FROM student;
- 注意：聚合函數(shù)的計(jì)算埃碱，排除null值猖辫。
- 解決方案：
- 選擇不包含非空的列進(jìn)行計(jì)算，一般為主鍵（這里*的意思是砚殿，只要那一條記錄有一列的值非空啃憎，就不會(huì)進(jìn)行排除）
- 例：SELECT COUNT(*) FROM student;
- IFNULL函數(shù)
- 例：SELECT COUNT(IFNULL(english,0)) FROM student;
- 選擇不包含非空的列進(jìn)行計(jì)算，一般為主鍵（這里*的意思是砚殿，只要那一條記錄有一列的值非空啃憎，就不會(huì)進(jìn)行排除）
- 解決方案：
- count：計(jì)算個(gè)數(shù)
-
分組查詢:
- 語(yǔ)法：group by 分組字段；
- 注意：
- 分組之后查詢的字段：分組字段似炎、聚合函數(shù)
- where 和 having 的區(qū)別辛萍？
- where 在分組之前進(jìn)行限定悯姊，如果不滿足條件，則不參與分組贩毕。having在分組之后進(jìn)行限定悯许，如果不滿足結(jié)果，則不會(huì)被查詢出來(lái)辉阶。
- where 后不可以跟聚合函數(shù)先壕，having可以進(jìn)行聚合函數(shù)的判斷。
例子
-- 按照性別分組谆甜，分別查詢男垃僚、女同學(xué)的數(shù)學(xué)成績(jī)平均分
-- 注意，分組之后规辱，查詢的字段谆棺，要么是分組字段，要么是聚合函數(shù)罕袋，不然沒(méi)有任何意義改淑，應(yīng)該是分組中共通的字段！
SELECT sex, AVG(math) FROM student GROUP BY sex;
-- 除了數(shù)學(xué)平均分浴讯，還要查詢出男朵夏、女的人數(shù)
SELECT sex, AVG(math), COUNT(id) FROM student GROUP BY sex;
-- 按照性別分組，分別查詢男兰珍、女同學(xué)的數(shù)學(xué)成績(jī)平均分侍郭。要求：分?jǐn)?shù)低于70分的人，不參與分組
SELECT sex, AVG(math), COUNT(id) FROM student WHERE math > 70 GROUP BY sex;
-- 按照性別分組掠河，分別查詢男亮元、女同學(xué)的數(shù)學(xué)成績(jī)平均分。要求：分?jǐn)?shù)低于70分的人唠摹，不參與分組（分組前的前置條件）爆捞，分組之后，人數(shù)要大于2個(gè)人（分組后的條件）
SELECT sex, AVG(math), COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
-- 人數(shù)我們可以會(huì)用別名來(lái)簡(jiǎn)化語(yǔ)句（這里直接用中文勾拉，方便訓(xùn)練煮甥，實(shí)際開(kāi)發(fā)中會(huì)用英文）
SELECT sex, AVG(math), COUNT(id) 人數(shù) FROM student WHERE math > 70 GROUP BY sex HAVING 人數(shù) > 2;
-
分頁(yè)查詢
- 語(yǔ)法：limit 開(kāi)始的索引,每頁(yè)查詢的條數(shù);
- 公式：開(kāi)始的索引 = （當(dāng)前頁(yè)碼 - 1） * 每頁(yè)顯示的條數(shù)
-- 分頁(yè)查詢，第一個(gè)參數(shù)是開(kāi)始的索引藕赞，第二參數(shù)是每頁(yè)的條數(shù) SELECT * FROM student LIMIT 0,3; -- 第1頁(yè) SELECT * FROM student LIMIT 3,3; -- 第2頁(yè) SELECT * FROM student LIMIT 6,3; -- 第3頁(yè)- limit 是一個(gè)MySQL"方言"成肘，其他數(shù)據(jù)則有特有的分頁(yè)語(yǔ)法。
約束
概念： 對(duì)表中的數(shù)據(jù)進(jìn)行限定斧蜕，保證數(shù)據(jù)的正確性双霍、有效性和完整性。
分類
主鍵約束：primary key
非空約束：not null
唯一約束：unique
外鍵約束：foreign key
-
非空約束：not null，值不能為null
- 創(chuàng)建表時(shí)添加約束
CREATE TABLE stu( id INT, NAME VARCHAR(20) NOT NULL -- name為非空 );- 創(chuàng)建表完后洒闸，再添加非空約束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;- 刪除字段的非空約束
ALTER TABLE stu MODIFY NAME VARCHAR(20); -
唯一約束：unique染坯，值不能重復(fù)
- 創(chuàng)建表時(shí)，添加唯一約束
CREATE TABLE stu( id INT, phone_number VARCHAR(20) UNIQUE -- 添加了唯一約束 ); * 注意mysql中丘逸，唯一約束限定的列的值可以有多個(gè)null- 刪除唯一約束
ALTER TABLE stu DROP INDEX phone_number;- 在創(chuàng)建表后单鹿，添加唯一約束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE; -
主鍵約束：primary key
-
注意：
- 含義：非空且唯一
- 一張表只能有一個(gè)字段為主鍵
- 主鍵就是表中記錄的唯一標(biāo)識(shí)
在創(chuàng)建表時(shí)，添加主鍵約束
create table stu( id int primary key,-- 給id添加主鍵約束 name varchar(20) );- 刪除主鍵
ALTER TABLE stu DROP PRIMARY KEY;- 創(chuàng)建完表后深纲，再添加主鍵
ALTER TABLE stu MODIFY id INT PRIMARY KEY; -
-
自動(dòng)增長(zhǎng)
概念：如果某一列是數(shù)值類型的仲锄，使用 auto_increment 可以來(lái)完成值得自動(dòng)增長(zhǎng)
在創(chuàng)建表時(shí)，添加主鍵約束囤萤，并且完成主鍵自增長(zhǎng)
create table stu( id int primary key auto_increment,-- 給id添加主鍵約束 name varchar(20) );- 刪除自動(dòng)增長(zhǎng)
ALTER TABLE stu MODIFY id INT;- 添加自動(dòng)增長(zhǎng)
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT; -
外鍵約束：foreign key,讓表于表產(chǎn)生關(guān)系昼窗，從而保證數(shù)據(jù)的正確性是趴。
- 在創(chuàng)建表時(shí)涛舍，可以添加外鍵
create table 表名( .... 外鍵列 constraint 外鍵名稱 foreign key (外鍵列名稱) references 主表名稱(主表列名稱) );- 刪除外鍵
ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名稱;- 創(chuàng)建表之后，添加外鍵
ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段名稱) REFERENCES 主表名稱(主表列名稱); -
級(jí)聯(lián)操作
- 添加級(jí)聯(lián)操作
ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段名稱) REFERENCES 主表名稱(主表列名稱)- 級(jí)聯(lián)更新
- ON UPDATE CASCADE
- 級(jí)聯(lián)刪除
- ON DELETE CASCADE
數(shù)據(jù)庫(kù)的設(shè)計(jì)
- 多表之間的關(guān)系
- 分類
- 一對(duì)一(了解)：
- 如：人和身份證
- 分析：一個(gè)人只有一個(gè)身份證唆途，一個(gè)身份證只能對(duì)應(yīng)一個(gè)人
- 一對(duì)多(多對(duì)一)：
- 如：部門(mén)和員工
- 分析：一個(gè)部門(mén)有多個(gè)員工富雅，一個(gè)員工只能對(duì)應(yīng)一個(gè)部門(mén)
- 多對(duì)多：
- 如：學(xué)生和課程
- 分析：一個(gè)學(xué)生可以選擇很多門(mén)課程，一個(gè)課程也可以被很多學(xué)生選擇
- 一對(duì)一(了解)：
- 實(shí)現(xiàn)關(guān)系
- 一對(duì)多(多對(duì)一)：
- 如：部門(mén)和員工
- 實(shí)現(xiàn)方式：在多的一方建立外鍵肛搬，指向一的一方的主鍵没佑。
- 多對(duì)多：
- 如：學(xué)生和課程
- 實(shí)現(xiàn)方式：多對(duì)多關(guān)系實(shí)現(xiàn)需要借助第三張中間表。中間表至少包含兩個(gè)字段温赔，這兩個(gè)字段作為第三張表的外鍵蛤奢，分別指向兩張表的主鍵
- 一對(duì)一(了解)：
- 如：人和身份證
- 實(shí)現(xiàn)方式：一對(duì)一關(guān)系實(shí)現(xiàn)，可以在任意一方添加唯一外鍵指向另一方的主鍵陶贼。
- 一對(duì)多(多對(duì)一)：
- 分類
案例
旅游路線分類下啤贩，有一批旅游路線，用戶可以收藏多個(gè)旅游路線拜秧。
-
表分類
- 旅游路線分類表：tab_category
- 旅游路線表：tab_route
- 用戶表：tab_user
- 收藏表：tab_favorite
-
表關(guān)系
- 一對(duì)多關(guān)系：旅游路線分類表tab_category和旅游路線表：tab_route
- 多對(duì)多關(guān)系：用戶表tab_user和旅游路線表tab_route痹屹，需要一張中間表：收藏表tab_favorite
表結(jié)構(gòu)
-- 創(chuàng)建旅游線路分類表 tab_category
-- cid 旅游線路分類主鍵，自動(dòng)增長(zhǎng)
-- cname 旅游線路分類名稱非空枉氮，唯一志衍，字符串 100
CREATE TABLE tab_category (
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(100) NOT NULL UNIQUE
);
-- 創(chuàng)建旅游線路表 tab_route
/*
rid 旅游線路主鍵，自動(dòng)增長(zhǎng)
rname 旅游線路名稱非空聊替，唯一楼肪，字符串 100
price 價(jià)格
rdate 上架時(shí)間，日期類型
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)
);
/*創(chuàng)建用戶表 tab_user
uid 用戶主鍵春叫，自增長(zhǎng)
username 用戶名長(zhǎng)度 100，唯一，非空
password 密碼長(zhǎng)度 30象缀，非空
name 真實(shí)姓名長(zhǎng)度 100
birthday 生日
sex 性別蔬将，定長(zhǎng)字符串 1
telephone 手機(jī)號(hào)，字符串 11
email 郵箱央星，字符串長(zhǎng)度 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)
);
/*
創(chuàng)建收藏表 tab_favorite
rid 旅游線路 id霞怀，外鍵
date 收藏時(shí)間
uid 用戶 id，外鍵
rid 和 uid 不能重復(fù)莉给，設(shè)置復(fù)合主鍵毙石，同一個(gè)用戶不能收藏同一個(gè)線路兩次
*/
CREATE TABLE tab_favorite (
rid INT, -- 線路id
DATE DATETIME,
uid INT, -- 用戶id
-- 創(chuàng)建復(fù)合主鍵
PRIMARY KEY(rid,uid), -- 聯(lián)合主鍵
FOREIGN KEY (rid) REFERENCES tab_route(rid),
FOREIGN KEY(uid) REFERENCES tab_user(uid)
);
數(shù)據(jù)庫(kù)設(shè)計(jì)的范式
-
概念：
- 設(shè)計(jì)數(shù)據(jù)庫(kù)時(shí)，需要遵循的一些規(guī)范颓遏。要遵循后邊的范式要求徐矩，必須先遵循前邊的所有范式要求。
- 設(shè)計(jì)關(guān)系數(shù)據(jù)庫(kù)時(shí)叁幢，遵從不同的規(guī)范要求滤灯，設(shè)計(jì)出合理的關(guān)系型數(shù)據(jù)庫(kù)，這些不同的規(guī)范要求被稱為不同的范式曼玩，各種范式呈遞次規(guī)范鳞骤，越高的范式數(shù)據(jù)庫(kù)冗余越小。
- 目前關(guān)系數(shù)據(jù)庫(kù)有六種范式：第一范式（1NF）黍判、第二范式（2NF）豫尽、第三范式（3NF）、巴斯-科德范式（BCNF）顷帖、第四范式(4NF）和第五范式（5NF美旧，又稱完美范式）。
-
分類：
- 第一范式（1NF）：每一列都是不可分割的原子數(shù)據(jù)項(xiàng)
- 第二范式（2NF）：在1NF的基礎(chǔ)上贬墩，非碼屬性必須完全依賴于碼（在1NF基礎(chǔ)上消除非主屬性對(duì)主碼的部分函數(shù)依賴）
- 幾個(gè)概念：
- 函數(shù)依賴：A-->B,如果通過(guò)A屬性(屬性組)的值榴嗅，可以確定唯一B屬性的值。則稱B依賴于A
- 例如：學(xué)號(hào)-->姓名震糖。 （學(xué)號(hào)录肯，課程名稱） --> 分?jǐn)?shù)
- 完全函數(shù)依賴：A-->B， 如果A是一個(gè)屬性組吊说，則B屬性值得確定需要依賴于A屬性組中所有的屬性值论咏。
- 例如：（學(xué)號(hào)，課程名稱） --> 分?jǐn)?shù)
- 部分函數(shù)依賴：A-->B颁井， 如果A是一個(gè)屬性組厅贪，則B屬性值得確定只需要依賴于A屬性組中某一些值即可。
- 例如：（學(xué)號(hào)雅宾，課程名稱） -- > 姓名
- 傳遞函數(shù)依賴：A-->B, B -- >C . 如果通過(guò)A屬性(屬性組)的值养涮，可以確定唯一B屬性的值，在通過(guò)B屬性（屬性組）的值可以確定唯一C屬性的值，則稱 C 傳遞函數(shù)依賴于A
- 例如：學(xué)號(hào)-->系名贯吓，系名-->系主任
- 碼：如果在一張表中懈凹，一個(gè)屬性或?qū)傩越M，被其他所有屬性所完全依賴悄谐，則稱這個(gè)屬性(屬性組)為該表的碼
- 例如：該表中碼為：（學(xué)號(hào)介评，課程名稱）
- 主屬性：碼屬性組中的所有屬性
- 非主屬性：除過(guò)碼屬性組的屬性
- 例如：該表中碼為：（學(xué)號(hào)介评，課程名稱）
- 函數(shù)依賴：A-->B,如果通過(guò)A屬性(屬性組)的值榴嗅，可以確定唯一B屬性的值。則稱B依賴于A
- 幾個(gè)概念：
- 第三范式（3NF）：在2NF基礎(chǔ)上，任何非主屬性不依賴于其它非主屬性（在2NF基礎(chǔ)上消除傳遞依賴）
數(shù)據(jù)庫(kù)的備份和還原
- 數(shù)據(jù)庫(kù)備份
- 命令語(yǔ)法：mysqldump -u用戶名 -p密碼 數(shù)據(jù)庫(kù)名稱 > 保存的路徑
- 數(shù)據(jù)庫(kù)還原
- 登錄數(shù)據(jù)庫(kù)
- 創(chuàng)建數(shù)據(jù)庫(kù)
- 使用數(shù)據(jù)庫(kù)
- 命令命令：source 文件路徑
