掌握 SQL 這些核心知識點，出去吹牛逼再也不擔心了

第一章 SQL的介紹

1.1、什么是sql

• SQL：Structure Query Language。（結(jié)構(gòu)化查詢語言）,通過sql操作數(shù)據(jù)庫(操作數(shù)據(jù)庫,操作表,操作數(shù)據(jù))
• SQL被美國國家標準局（ANSI）確定為關(guān)系型數(shù)據(jù)庫語言的美國標準，后來被國際化標準組織（ISO）采納為關(guān)系數(shù)據(jù)庫語言的國際標準
• 各數(shù)據(jù)庫廠商(MySql,oracle,sql server)都支持ISO的SQL標準。
• 各數(shù)據(jù)庫廠商在標準的基礎(chǔ)上做了自己的擴展。各個數(shù)據(jù)庫自己特定的語法

1.2、sql的分類

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

1.3、MySQL的語法規(guī)范和要求

（1）mysql的sql語法不區(qū)分大小寫

MySQL的關(guān)鍵字和函數(shù)名等不區(qū)分大小寫，但是對于數(shù)據(jù)值是否區(qū)分大小寫，和字符集與校對規(guī)則有關(guān)。

ci（大小寫不敏感），cs（大小寫敏感），_bin（二元，即比較是基于字符編碼的值而與language無關(guān)，區(qū)分大小寫）

（2）命名時：盡量使用26個英文字母大小寫，數(shù)字0-9，下劃線，不要使用其他符號user_id

（3）建議不要使用mysql的關(guān)鍵字等來作為表名、字段名等，如果不小心使用，請在SQL語句中使用`（飄號）引起來

（4）數(shù)據(jù)庫和表名、字段名等對象名中間不要包含空格

（5）同一個mysql軟件中，數(shù)據(jù)庫不能同名，同一個庫中，表不能重名，同一個表中，字段不能重名

（6）標點符號：

• 必須成對
• 必須英文狀態(tài)下半角輸入方式
• 字符串和日期類型可以使用單引號’’
• 列的別名可以使用雙引號""，給表名取別名不要使用雙引號。取別名時as可以省略
• 如果列的別名沒有包含空格，可以省略雙引號，如果有空格雙引號不能省略。

（7）SQL腳本中如何加注釋

• 單行注釋：#注釋內(nèi)容
• 單行注釋：–空格注釋內(nèi)容 其中–后面的空格必須有
• 多行注釋：/* 注釋內(nèi)容 */

#以下兩句是一樣的，不區(qū)分大小寫
show databases;
SHOW DATABASES;

#創(chuàng)建表格
#create table student info(...); #表名錯誤，因為表名有空格
create table student_info(...); 

#其中name使用``飄號，因為name和系統(tǒng)關(guān)鍵字或系統(tǒng)函數(shù)名等預(yù)定義標識符重名了。
CREATE TABLE t_stu(
    id INT,
    `name` VARCHAR(20)
);

select id as "編號", `name` as "姓名" from t_stu; #起別名時，as都可以省略
select id as 編號, `name` as 姓名 from t_stu; #如果字段別名中沒有空格，那么可以省略""
select id as 編 號, `name` as 姓 名 from t_stu; #錯誤，如果字段別名中有空格，那么不能省略""

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

2.1、創(chuàng)建數(shù)據(jù)庫(掌握)

• 語法

create database 數(shù)據(jù)庫名 [character set 字符集][collate  校對規(guī)則]     注: []意思是可選的意思

字符集(charset)：是一套符號和編碼。

• 練習(xí)

創(chuàng)建一個day01的數(shù)據(jù)庫（默認字符集)

create database day01;

創(chuàng)建一個day01_2的數(shù)據(jù)庫,指定字符集為gbk(了解)

create database day01_2 character set gbk;

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

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

語法

show databases; 

• 查看數(shù)據(jù)庫的定義結(jié)構(gòu)【了解】

語法

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

查看day01這個數(shù)據(jù)庫的定義

show create database day01; 

2.3、刪除數(shù)據(jù)庫

語法

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

刪除day01_2數(shù)據(jù)庫

drop database day01_2;

2.4、修改數(shù)據(jù)庫【了解】

語法

alter database 數(shù)據(jù)庫名 character set 字符集;

修改day01這個數(shù)據(jù)庫的字符集(gbk)

alter database day01 character set gbk;

注意：

• 是utf8，不是utf-8
• 不是修改數(shù)據(jù)庫名

2.5、其他操作

切換數(shù)據(jù)庫, 選定哪一個數(shù)據(jù)庫

use 數(shù)據(jù)庫名;     //注意: 在創(chuàng)建表之前一定要指定數(shù)據(jù)庫. use 數(shù)據(jù)庫名

練習(xí): 使用day01

use day01;

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

select database();

第三章-DDL操作表

3.1、創(chuàng)建表

• 語法

create table 表名(
 列名 類型 [約束],
 列名 類型 [約束]
 ...
  
);

• 類型

數(shù)值類型

整型系列：xxxInt

• int(M)，必須和unsigned zerofill一起使用才有意義

浮點型系列：float,double（或real)

• double(M,D)：表示最長為M位，其中小數(shù)點后D位

例如：double(5,2)表示的數(shù)據(jù)范圍[-999.99,999.99]，如果超過這個范圍會報錯。

定點型系列：decimal（底層實際上是使用字符串進行存儲）

• decimal(M,D)：表示最長為M位，其中小數(shù)點后D位

位類型：bit

• 字節(jié)范圍是：1-8，值范圍是：bit(1)~bit(64)，默認bit(1)

用來存儲二進制數(shù)。對于位字段，直接使用select命令將不會看到結(jié)果?？梢允褂胋it()或hex()函數(shù)進行讀取。插入bit類型字段時，使用bit()函數(shù)轉(zhuǎn)為二進制值再插入，因為二進制碼是“01”。常見 SQL 面試題：經(jīng)典 50 例

日期時間類型

日期時間類型：year, date, datetime, timestamp

注意一下每一種日期時間的表示范圍

timestamp和datetime的區(qū)別：

• timestamp范圍比較小
• timestamp和時區(qū)有關(guān)
• show variables like ‘time_zone’;
• set time_zone = ‘+8:00’;
• timestamp受MySQL版本和服務(wù)器的SQLMode影響很大
• 表中的第一個非空的timestamp字段如果插入和更新為NULL則會自動設(shè)置為系統(tǒng)時間。速來！整理了 25 個 PDF 電子書免費下載

字符串類型

MySQL中提供了多種對字符數(shù)據(jù)的存儲類型，不同的版本可能有所差異。常見的有：

char,varchar，xxtext，binary，varbinary，xxblob，enum，set等等

• 字符串類型char,varchar(M)
• char如果沒有指定寬度，默認為1個字符
• varchar(M)，必須指定寬度

binary和varbinary類似于char和varchar，不同的是它們包含二進制字符串，不支持模糊查詢之類的。

一般在保存少量字符串的時候，我們會選擇char和varchar；而在保存較大文本時，通常會選擇使用text或blob系列。blob和text值會引起一些性能問題，特別是在執(zhí)行了大量的刪除操作時，會在數(shù)據(jù)表中留下很大的“空洞”，為了提高性能，建議定期時候用optimize table功能對這類表進行碎片整理?？梢允褂煤铣傻?Synthetic)索引來提高大文本字段的查詢性能，如果需要對大文本字段進行模糊查詢，MySql提供了前綴索引。但是仍然要在不必要的時候避免檢索大型的blob或text值。

enum枚舉類型，它的值范圍需要在創(chuàng)建表時通過枚舉方式顯式指定，對于1~255個成員的枚舉需要1個字節(jié)存儲；對于【 255`65535】個成員需要2個字節(jié)存儲。例如：gender enum(‘男’,‘女’)。如果插入枚舉值以外的值，會按第一個值處理。一次只能從枚舉值中選擇一個。

set集合類型，可以包含0~64個成員。一次可以從集合中選擇多個成員。如果選擇了1-8個成員的集合，占1個字節(jié)，依次占2個，3個。。8個字節(jié)。例如：hoppy set(‘吃飯’,‘睡覺’,‘玩游戲’,‘旅游’)，選擇時’吃飯,睡覺’或’睡覺,玩游戲,旅游’

示例

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| eid            | int(11)      | NO   | PRI | NULL    | auto_increment |
| ename          | varchar(20)  | NO   |     | NULL    |                |
| tel            | char(11)     | NO   |     | NULL    |                |
| gender         | char(1)      | YES  |     | 男        |                |
| salary         | double       | YES  |     | NULL    |                |
| commission_pct | double(3,2)  | YES  |     | NULL    |                |
| birthday       | date         | YES  |     | NULL    |                |
| hiredate       | date         | YES  |     | NULL    |                |
| job_id         | int(11)      | YES  |     | NULL    |                |
| email          | varchar(32)  | YES  |     | NULL    |                |
| mid            | int(11)      | YES  |     | NULL    |                |
| address        | varchar(150) | YES  |     | NULL    |                |
| native_place   | varchar(10)  | YES  |     | NULL    |                |
| did            | int(11)      | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

約束

• 即規(guī)則,規(guī)矩 限制;
• 作用：保證用戶插入的數(shù)據(jù)保存到數(shù)據(jù)庫中是符合規(guī)范的

約束種類:

• not null: 非空 ; eg: username varchar(40) not null username這個列不能有null值
• unique:唯一約束, 后面的數(shù)據(jù)不能和前面重復(fù); eg: cardNo char(18) unique; cardNo 列里面不可以有重復(fù)數(shù)據(jù)
• primary key；主鍵約束(非空+唯一); 一般用在表的id列上面. 一張表基本上都有id列的, id列作為唯一標識的
• auto_increment: 自動增長,必須是設(shè)置了primary key之后,才可以使用auto_increment
• id int primary key auto_increment; id不需要我們自己維護了, 插入數(shù)據(jù)的時候直接插入null, 自動的增長進行填充進去, 避免重復(fù)了.

注意:

• 先設(shè)置了primary key 再能設(shè)置auto_increment
• 只有當設(shè)置了auto_increment 才可以插入null , 否則插入null會報錯

id列:

• 給id設(shè)置為int類型, 添加主鍵約束, 自動增長
• 或者給id設(shè)置為字符串類型,添加主鍵約束, 不能設(shè)置自動增長

練習(xí)

創(chuàng)建一張學(xué)生表(含有id字段,姓名字段不能重復(fù),性別字段不能為空默認值為男. id為主鍵自動增長)

CREATE TABLE student(
 id INT PRIMARY KEY AUTO_INCREMENT, -- 主鍵自增長
 NAME VARCHAR(30) UNIQUE, -- 唯一約束
 gender CHAR(1) NOT NULL DEFAULT '男'
);

3.2、查看表【了解】

查看所有的表

show tables;

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

語法

desc 表名；

練習(xí): 查看student表的定義結(jié)構(gòu)

desc student;

3.3、修改表【掌握，但是不要記憶】

語法

• 增加一列

alter table 【數(shù)據(jù)庫名.]表名稱 add 【column】 字段名 數(shù)據(jù)類型;
alter table 【數(shù)據(jù)庫名.]表名稱 add 【column】 字段名 數(shù)據(jù)類型 first;
alter table 【數(shù)據(jù)庫名.]表名稱 add 【column】 字段名 數(shù)據(jù)類型 after 另一個字段;

• 修改列的類型約束:alter table 表名 modify 字段 類型 約束 ；
• 修改列的名稱，類型，約束: alter table 表名 change 舊列 新列 類型 約束;
• 刪除一列: alter table 表名 drop 列名;
• 修改表名 : rename table 舊表名 to 新表名;

練習(xí)

給學(xué)生表增加一個grade字段,類型為varchar(20),不能為空

ALTER TABLE student ADD grade VARCHAR(20) NOT NULL;

給學(xué)生表的gender字段改成int類型，不能為空，默認值為1

alter table student modify gender varchar(20);

給學(xué)生表的grade字段修改成class字段

ALTER TABLE student CHANGE grade class VARCHAR(20) NOT NULL;

把class字段刪除

ALTER TABLE student DROP class;

把學(xué)生表修改成老師表(了解)

RENAME TABLE student TO teacher;

3.4、刪除表【掌握】

語法

drop table 表名；

把teacher表刪除

drop table teacher;

第四章-DML操作表記錄-增刪改【重點】

準備工作: 創(chuàng)建一張商品表(商品id,商品名稱,商品價格,商品數(shù)量.)

create table product(
 pid int primary key auto_increment,
 pname varchar(40),
 price double,
 num int
);

4.1、插入記錄

語法

• 方式一: 插入指定列, 如果沒有把這個列進行列出來, 以null進行自動賦值了.

eg: 只想插入pname, price , insert into t_product(pname, price) values(‘mac’,18000);

insert into 表名(列,列..) values(值,值..);

注意: 如果沒有插入了列設(shè)置了非空約束, 會報錯的

• 方式二: 插入所有的列,如果哪列不想插入值,則需要賦值為null

insert into 表名 values(值,值....);

eg:

insert into product values(null,'蘋果電腦',18000.0,10);
insert into product values(null,'華為5G手機',30000,20);
insert into product values(null,'小米手機',1800,30);
insert into product values(null,'iPhonex',8000,10);
insert into product values(null,'iPhone7',6000,200);
insert into product values(null,'iPhone6s',4000,1000);
insert into product values(null,'iPhone6',3500,100);
insert into product values(null,'iPhone5s',3000,100);

insert into product values(null,'方便面',4.5,1000);
insert into product values(null,'咖啡',11,200); 
insert into product values(null,'礦泉水',3,500);

4.2、更新記錄

• 語法

update 表名 set 列 =值, 列 =值 [where 條件]

• 練習(xí)

將所有商品的價格修改為5000元

update product set price = 5000;

將商品名是蘋果電腦的價格修改為18000元

UPDATE product set price = 18000 WHERE pname = '蘋果電腦';

將商品名是蘋果電腦的價格修改為17000,數(shù)量修改為5

UPDATE product set price = 17000,num = 5 WHERE pname = '蘋果電腦';

將商品名是方便面的商品的價格在原有基礎(chǔ)上增加2元

UPDATE product set price = price+2 WHERE pname = '方便面';

4.3、刪除記錄

• delete

根據(jù)條件，一條一條數(shù)據(jù)進行刪除

語法

delete from 表名 [where 條件]    注意: 刪除數(shù)據(jù)用delete,不用truncate

類型

刪除表中名稱為’蘋果電腦’的記錄

delete from product where pname = '蘋果電腦';

刪除價格小于5001的商品記錄

delete from product where price < 5001;

刪除表中的所有記錄(要刪除一般不建議使用delete語句，delete語句是一行一行執(zhí)行，速度過慢)

delete from product;

• truncate 把表直接DROP掉，然后再創(chuàng)建一個同樣的新表。刪除的數(shù)據(jù)不能找回。執(zhí)行速度比DELETE快

truncate table 表;

• 工作中刪除數(shù)據(jù)
• 物理刪除: 真正的刪除了, 數(shù)據(jù)不在, 使用delete就屬于物理刪除
• 邏輯刪除: 沒有真正的刪除, 數(shù)據(jù)還在. 搞一個標記, 其實邏輯刪除是更新 eg: state 1 啟用 0禁用

第五章-DQL操作表記錄-查詢【重點】

5.1、基本查詢語法

select 要查詢的字段名 from 表名 [where 條件] 

5.2、簡單查詢

查詢所有行和所有列的記錄

• 語法

select * form 表

查詢商品表里面的所有的列

select * from product;

查詢某張表特定列的記錄

• 語法

select 列名,列名,列名... from 表

查詢商品名字和價格

select pname, price from product;

去重查詢 distinct

• 語法

SELECT DISTINCT 字段名 FROM 表名;   //要數(shù)據(jù)一模一樣才能去重

去重查詢商品的名字

SELECT DISTINCT pname,price FROM product

注意點: 去重針對某列, distinct前面不能先出現(xiàn)列名

別名查詢

• 語法

select 列名 as 別名 ,列名  from 表   //列別名  as可以不寫
select 別名.* from 表 as 別名      //表別名(多表查詢, 明天會具體講)

查詢商品信息，使用別名

SELECT pid ,pname AS '商品名',price AS '商品價格',num AS '商品庫存' FROM product

運算查詢(+,-,*,/,%等)

把商品名，和商品價格+10查詢出來:我們既可以將某個字段加上一個固定值，又可以對多個字段進行運算查詢

select pname ,price+10 as 'price' from product;
select name,chinese+math+english as total from student

注意

• 運算查詢字段,字段之間是可以的
• 字符串等類型可以做運算查詢，但結(jié)果沒有意義

5.3、條件查詢(很重要)

• 語法

select ... from 表 where 條件 
//取出表中的每條數(shù)據(jù)，滿足條件的記錄就返回，不滿足條件的記錄不返回

運算符

1、比較運算符

大于：>
小于：<
大于等于：>=
小于等于：<=
等于：=   不能用于null判斷
不等于：!=  或 <>
安全等于: <=>  可以用于null值判斷

2、邏輯運算符（建議用單詞，可讀性來說）

邏輯與：&& 或 and
邏輯或：|| 或 or
邏輯非：! 或 not
邏輯異或：^ 或 xor

3、范圍

區(qū)間范圍：between x  and  y
     not between x  and  y
集合范圍：in (x,x,x) 
     not  in (x,x,x)

4、模糊查詢和正則匹配（只針對字符串類型，日期類型）

like 'xxx'  模糊查詢是處理字符串的時候進行部分匹配
如果想要表示0~n個字符，用%
如果想要表示確定的1個字符，用_

regexp '正則'

5、特殊的null值處理

#（1）判斷時
xx is null
xx is not null
xx <=> null
#（2）計算時
ifnull(xx,代替值) 當xx是null時，用代替值計算

• 練習(xí)

查詢商品價格>3000的商品

select * from product where price > 3000;

查詢pid=1的商品

select * from product where pid = 1;

查詢pid<>1的商品（！=）

select * from product where pid <> 1;

查詢價格在3000到6000之間的商品

select * from product where price between 3000 and 6000;

查詢pid在1，5，7，15范圍內(nèi)的商品

select * from product where id = 1;
select * from product where id = 5;
select * from product where id = 7;
select * from product where id = 15;

select * from product where id in (1,5,7,15);

查詢商品名以iPho開頭的商品(iPhone系列)

select * from product where pname like 'iPho%';

查詢商品價格大于3000并且數(shù)量大于20的商品 (條件 and 條件 and…)

select * from product where price > 3000 and num > 20;

查詢id=1或者價格小于3000的商品

select * from product where pid = 1 or price < 3000;

5.4、排序查詢

排序是寫在查詢的后面，代表把數(shù)據(jù)查詢出來之后再排序

• 環(huán)境的準備

# 創(chuàng)建學(xué)生表(有sid,學(xué)生姓名,學(xué)生性別,學(xué)生年齡,分數(shù)列,其中sid為主鍵自動增長)
CREATE TABLE student(
 sid INT PRIMARY KEY auto_increment,
 sname VARCHAR(40),
 sex VARCHAR(10),
 age INT,
    score DOUBLE
);

INSERT INTO student VALUES(null,'zs','男',18,98.5);
INSERT INTO student VALUES(null,'ls','女',18,96.5);
INSERT INTO student VALUES(null,'ww','男',15,50.5);
INSERT INTO student VALUES(null,'zl','女',20,98.5);
INSERT INTO student VALUES(null,'tq','男',18,60.5);
INSERT INTO student VALUES(null,'wb','男',38,98.5);
INSERT INTO student VALUES(null,'小麗','男',18,100);
INSERT INTO student VALUES(null,'小紅','女',28,28);
INSERT INTO student VALUES(null,'小強','男',21,95);

• 單列排序

語法: 只按某一個字段進行排序，單列排序

SELECT 字段名 FROM 表名 [WHERE 條件] ORDER BY 字段名 [ASC|DESC];  //ASC: 升序，默認值; DESC: 降序

案例: 以分數(shù)降序查詢所有的學(xué)生

SELECT * FROM student ORDER BY score DESC

• 組合排序

語法: 同時對多個字段進行排序，如果第1個字段相等，則按第2個字段排序，依次類推

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

練習(xí): 以分數(shù)降序查詢所有的學(xué)生, 如果分數(shù)一致,再以age降序

SELECT * FROM student ORDER BY score DESC, age DESC

5.5、聚合函數(shù)

聚合函數(shù)用于統(tǒng)計，通常會和分組查詢一起使用，用于統(tǒng)計每組的數(shù)據(jù)

• 聚合函數(shù)列表

語法

SELECT 聚合函數(shù)(列名) FROM 表名 [where 條件];

案例

-- 求出學(xué)生表里面的最高分數(shù)
SELECT MAX(score) FROM student
-- 求出學(xué)生表里面的最低分數(shù)
SELECT MIN(score) FROM student
-- 求出學(xué)生表里面的分數(shù)的總和(忽略null值)
SELECT SUM(score) FROM student
-- 求出學(xué)生表里面的平均分
SELECT AVG(score) FROM student
-- 求出學(xué)生表里面的平均分（缺考了當成0分處理）
SELECT AVG(IFNULL(score,0)) FROM student
-- 統(tǒng)計學(xué)生的總?cè)藬?shù) (忽略null) 
SELECT COUNT(sid) FROM student
SELECT COUNT(*) FROM student

注意: 聚合函數(shù)會忽略空值NULL

我們發(fā)現(xiàn)對于NULL的記錄不會統(tǒng)計，建議如果統(tǒng)計個數(shù)則不要使用有可能為null的列，但如果需要把NULL也統(tǒng)計進去呢？我們可以通過 IFNULL(列名，默認值) 函數(shù)來解決這個問題. 如果列不為空，返回這列的值。如果為NULL，則返回默認值。SQL查找是否"存在"，別再count了！

-- 求出學(xué)生表里面的平均分（缺考了當成0分處理）
SELECT AVG(IFNULL(score,0)) FROM student;

5.6、分組查詢

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

• 分組

語法

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

案例

-- 根據(jù)性別分組, 統(tǒng)計每一組學(xué)生的總?cè)藬?shù)
SELECT sex '性別',COUNT(sid) '總?cè)藬?shù)' FROM student GROUP BY sex

-- 根據(jù)性別分組，統(tǒng)計每組學(xué)生的平均分
SELECT sex '性別',AVG(score) '平均分' FROM student GROUP BY sex

-- 根據(jù)性別分組，統(tǒng)計每組學(xué)生的總分
SELECT sex '性別',SUM(score) '總分' FROM student GROUP BY sex

• 分組后篩選 having

分組后的條件，不能寫在where之后，where關(guān)鍵字要寫在group by之前

根據(jù)性別分組, 統(tǒng)計每一組學(xué)生的總?cè)藬?shù)> 5的(分組后篩選)

SELECT sex, count(*) FROM student GROUP BY sex HAVING count(sid) > 5

根據(jù)性別分組，只統(tǒng)計年齡大于等于18的，并且要求組里的人數(shù)大于4

SELECT sex '性別',COUNT(sid) '總?cè)藬?shù)' FROM student WHERE age >= 18 GROUP BY sex HAVING COUNT(sid) > 4

• where和having的區(qū)別【面試】

where 子句作用

• 1)對查詢結(jié)果進行分組前，將不符合where條件的行去掉，即在分組之前過濾數(shù)據(jù)，即先過濾再分組。
• 2)where后面不可以使用聚合函數(shù)

having字句作用

• 
  
1. having 子句的作用是篩選滿足條件的組，即在分組之后過濾數(shù)據(jù)，即先分組再過濾。
• 
  
2. having后面可以使用聚合函數(shù)

5.7、分頁查詢

• 語法

select ... from .... limit a ,b

• 案例

-- 分頁查詢
-- limit 關(guān)鍵字是使用在查詢的后邊，如果有排序的話則使用在排序的后邊
-- limit的語法: limit offset,length  其中offset表示跳過多少條數(shù)據(jù)，length表示查詢多少條數(shù)據(jù)
SELECT * FROM product LIMIT 0,3
-- 查詢product表中的前三條數(shù)據(jù)(0表示跳過0條，3表示查詢3條)

SELECT * FROM product LIMIT 3,3
-- 查詢product表的第四到六條數(shù)據(jù)(3表示跳過3條，3表示查詢3條)
-- 分頁的時候，只會告訴你我需要第幾頁的數(shù)據(jù)，并且每頁有多少條數(shù)據(jù)
-- 假如，每頁需要3條數(shù)據(jù)，我想要第一頁數(shù)據(jù): limit 0,3
-- 假如,每頁需要3條數(shù)據(jù)，我想要第二頁數(shù)據(jù): limit 3,3
-- 假如，每頁需要3條數(shù)據(jù)，我想要第三頁數(shù)據(jù): limit 6,3
-- 結(jié)論: length = 每頁的數(shù)據(jù)條數(shù)，offset = (當前頁數(shù) - 1)*每頁數(shù)據(jù)條數(shù)
-- limit (當前頁數(shù) - 1)*每頁數(shù)據(jù)條數(shù), 每頁數(shù)據(jù)條數(shù)

5.8、查詢的語法小結(jié)

select...from...where...group by...order by...limit

select...from...where...
select...from...where...order by...
select...from...where...limit...
select...from...where...order by...imit

第六章 數(shù)據(jù)庫三范式

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

6.1、第一范式: 確保每列保持原子性

第一范式是最基本的范式。如果數(shù)據(jù)庫表中的所有字段值都是不可分解的原子值，就說明該數(shù)據(jù)庫表滿足了第一范式。

第一范式的合理遵循需要根據(jù)系統(tǒng)的實際需求來定。比如某些數(shù)據(jù)庫系統(tǒng)中需要用到“地址”這個屬性，本來直接將“地址”屬性設(shè)計成一個數(shù)據(jù)庫表的字段就行。但是如果系統(tǒng)經(jīng)常會訪問“地址”屬性中的“城市”部分，那么就非要將“地址”這個屬性重新拆分為省份、城市、詳細地址等多個部分進行存儲，這樣在對地址中某一部分操作的時候?qū)⒎浅７奖?。這樣設(shè)計才算滿足了數(shù)據(jù)庫的第一范式，如下表所示。

如果不遵守第一范式，查詢出數(shù)據(jù)還需要進一步處理（查詢不方便）。遵守第一范式，需要什么字段的數(shù)據(jù)就查詢什么數(shù)據(jù)（方便查詢）

6.2、第二范式: 確保表中的每列都和主鍵相關(guān)

第二范式在第一范式的基礎(chǔ)之上更進一層。第二范式需要確保數(shù)據(jù)庫表中的每一列都和主鍵相關(guān)，而不能只與主鍵的某一部分相關(guān)（主要針對聯(lián)合主鍵而言）。也就是說在一個數(shù)據(jù)庫表中，一個表中只能保存一種數(shù)據(jù)，不可以把多種數(shù)據(jù)保存在同一張數(shù)據(jù)庫表中。

比如要設(shè)計一個訂單信息表，因為訂單中可能會有多種商品，所以要將訂單編號和商品編號作為數(shù)據(jù)庫表的聯(lián)合主鍵，如下表所示

這樣就產(chǎn)生一個問題：這個表中是以訂單編號和商品編號作為聯(lián)合主鍵。這樣在該表中商品名稱、單位、商品價格等信息不與該表的主鍵相關(guān)，而僅僅是與商品編號相關(guān)。所以在這里違反了第二范式的設(shè)計原則。

而如果把這個訂單信息表進行拆分，把商品信息分離到另一個表中，把訂單項目表也分離到另一個表中，就非常完美了。如下所示

<img src="imgs/tu_13.png" style="zoom: 67%;" />

這樣設(shè)計，在很大程度上減小了數(shù)據(jù)庫的冗余。如果要獲取訂單的商品信息，使用商品編號到商品信息表中查詢即可

6.3、第三范式: 確保每列都和主鍵列直接相關(guān),而不是間接相關(guān)

第三范式需要確保數(shù)據(jù)表中的每一列數(shù)據(jù)都和主鍵直接相關(guān)，而不能間接相關(guān)。

比如在設(shè)計一個訂單數(shù)據(jù)表的時候，可以將客戶編號作為一個外鍵和訂單表建立相應(yīng)的關(guān)系。而不可以在訂單表中添加關(guān)于客戶其它信息（比如姓名、所屬公司等）的字段。如下面這兩個表所示的設(shè)計就是一個滿足第三范式的數(shù)據(jù)庫表。

<img src="imgs/tu_14.png" style="zoom:67%;" />

這樣在查詢訂單信息的時候，就可以使用客戶編號來引用客戶信息表中的記錄，也不必在訂單信息表中多次輸入客戶信息的內(nèi)容，減小了數(shù)據(jù)冗余

第七章 外鍵約束

7.1、外鍵約束的概念

在遵循三范式的前提下，很多時候我們必須要進行拆表，將數(shù)據(jù)分別存放在多張表中，以減少冗余數(shù)據(jù)。但是拆分出來的表與表之間是有著關(guān)聯(lián)關(guān)系的，我們必須得通過一種約束來約定表與表之間的關(guān)系，這種約束就是外鍵約束

7.2、外鍵約束的作用

外鍵約束是保證一個或兩個表之間的參照完整性,外鍵是構(gòu)建于一個表的兩個字段或是兩個表的兩個字段之間的參照關(guān)系。

7.3、創(chuàng)建外鍵約束的語法

• 在建表時指定外鍵約束

create table [數(shù)據(jù)名.]從表名(
 字段名1 數(shù)據(jù)類型  primary key ,
 字段名2 數(shù)據(jù)類型 ,
 ....,
    [constraint 外鍵約束名] foreign key (從表字段) references 主表名(主表字段) [on update 外鍵約束等級][on delete 外鍵約束等級]
    #外鍵只能在所有字段列表后面單獨指定
    #如果要自己命名外鍵約束名，建議 主表名_從表名_關(guān)聯(lián)字段名_fk
);

• 在建表后指定外鍵約束

alter table 從表名稱 add [constraint 外鍵約束名] foreign key (從表字段名) references 主表名(主表被參照字段名) [on update xx][on delete xx];

7.4、刪除外鍵約束的語法

ALTER TABLE 表名稱 DROP FOREIGN KEY 外鍵約束名;
#查看約束名 SELECT * FROM information_schema.table_constraints WHERE table_name = '表名稱';
#刪除外鍵約束不會刪除對應(yīng)的索引，如果需要刪除索引，需要用ALTER TABLE 表名稱 DROP INDEX 索引名;
#查看索引名 show index from 表名稱;

7.5、外鍵約束的要求

• 在從表上建立外鍵，而且主表要先存在。
• 一個表可以建立多個外鍵約束
• 通常情況下，從表的外鍵列一定要指向主表的主鍵列
• 從表的外鍵列與主表被參照的列名字可以不相同，但是數(shù)據(jù)類型必須一樣

7.6、外鍵約束等級

• Cascade方式：在主表上update/delete記錄時，同步update/delete掉從表的匹配記錄
• Set null方式：在主表上update/delete記錄時，將從表上匹配記錄的列設(shè)為null，但是要注意子表的外鍵列不能為not null
• No action方式：如果子表中有匹配的記錄,則不允許對父表對應(yīng)候選鍵進行update/delete操作
• Restrict方式：同no action, 都是立即檢查外鍵約束
• Set default方式（在可視化工具SQLyog中可能顯示空白）：父表有變更時,子表將外鍵列設(shè)置成一個默認的值，但Innodb不能識別
• 如果沒有指定等級，就相當于Restrict方式

7.7、外鍵約束練習(xí)

-- 部門表
create table dept( 
 id int primary key,
 dept_name varchar(50),
 dept_location varchar(50)
);
-- 員工表
CREATE TABLE emp(
 eid int primary key,
 name varchar(50) not null,
 sex varchar(10),
    dept_id int
);
-- 給員工表表的dept_id添加外鍵指向部門表的主鍵
alter table emp add foreign key(dept_id) references dept(id)

第八章 多表間關(guān)系

8.1、一對多關(guān)系

• 概念

一對多的關(guān)系是指: 主表的一行數(shù)據(jù)可以同時對應(yīng)從表的多行數(shù)據(jù)，反過來就是從表的多行數(shù)據(jù)指向主表的同一行數(shù)據(jù)。

• 應(yīng)用場景

分類表和商品表、班級表和學(xué)生表、用戶表和訂單表等等

• 建表原則

將一的一方作為主表，多的一方作為從表，在從表中指定一個字段作為外鍵，指向主表的主鍵

• 建表語句練習(xí)

-- 創(chuàng)建分類表
CREATE TABLE category(
 cid INT PRIMARY KEY AUTO_INCREMENT,
 cname VARCHAR(50)
);

-- 創(chuàng)建商品表
CREATE TABLE product(
 pid INT PRIMARY KEY AUTO_INCREMENT,
 pname VARCHAR(50),
 price DOUBLE,
 cid INT
)
-- 給商品表添加一個外鍵
alter table product add foreign key(cid) references  category(cid)

8.2、多對多關(guān)系

• 概念

兩張表都是多的一方，A表的一行數(shù)據(jù)可以同時對應(yīng)B表的多行數(shù)據(jù)，反之B表的一行數(shù)據(jù)也可以同時對應(yīng)A表的多行數(shù)據(jù)

• 應(yīng)用場景

訂單表和商品表、學(xué)生表和課程表等等

• 建表原則

因為兩張表都是多的一方，所以在兩張表中都無法創(chuàng)建外鍵，所以需要新創(chuàng)建一張中間表，在中間表中定義兩個字段，這倆字段分別作為外鍵指向兩張表各自的主鍵

• 建表語句練習(xí)

-- 創(chuàng)建學(xué)生表
CREATE TABLE student(
 sid INT PRIMARY KEY AUTO_INCREMENT,
 sname VARCHAR(50)
);

-- 創(chuàng)建課程表
CREATE TABLE course(
 cid INT PRIMARY KEY AUTO_INCREMENT,
 cname VARCHAR(20)
);

-- 創(chuàng)建中間表
CREATE TABLE s_c_table(
 sno INT,
 cno INT
);
-- 給sno字段添加外鍵指向student表的sid主鍵
ALTER TABLE s_c_table ADD CONSTRAINT fkey01 FOREIGN KEY(sno) REFERENCES student(sid);
-- 給cno字段添加外鍵指向course表的cid主鍵
ALTER TABLE s_c_table ADD CONSTRAINT fkey03 FOREIGN KEY(cno) REFERENCES course(cid);

8.3、一對一關(guān)系(了解)

• 第一種一對一關(guān)系

我們之前學(xué)習(xí)過一對多關(guān)系，在一對多關(guān)系中主表的一行數(shù)據(jù)可以對應(yīng)從表的多行數(shù)據(jù)，反之從表的一行數(shù)據(jù)則只能對應(yīng)主表的一行數(shù)據(jù)。這種一行數(shù)據(jù)對應(yīng)一行數(shù)據(jù)的關(guān)系，我們可以將其看作一對一關(guān)系

• 第二種一對一關(guān)系

A表中的一行數(shù)據(jù)對應(yīng)B表中的一行數(shù)據(jù)，反之B表中的一行數(shù)據(jù)也對應(yīng)A表中的一行數(shù)據(jù)，此時我們可以將A表當做主表B表當做從表，或者是將B表當做主表A表當做從表

• 建表原則

在從表中指定一個字段創(chuàng)建外鍵并指向主表的主鍵，然后給從表的外鍵字段添加唯一約束

第九章 多表關(guān)聯(lián)查詢

多表關(guān)聯(lián)查詢是使用一條SQL語句，將關(guān)聯(lián)的多張表的數(shù)據(jù)查詢出來

9.1、環(huán)境準備

-- 創(chuàng)建一張分類表(類別id,類別名稱.備注:類別id為主鍵并且自動增長)
CREATE TABLE t_category(
  cid INT PRIMARY KEY auto_increment,
  cname VARCHAR(40)
);
INSERT INTO t_category values(null,'手機數(shù)碼');
INSERT INTO t_category values(null,'食物');
INSERT INTO t_category values(null,'鞋靴箱包');


-- 創(chuàng)建一張商品表(商品id,商品名稱,商品價格,商品數(shù)量,類別.備注:商品id為主鍵并且自動增長)

CREATE TABLE t_product(
  pid INT PRIMARY KEY auto_increment,
  pname VARCHAR(40),
  price DOUBLE,
  num INT,
  cno INT
);

insert into t_product values(null,'蘋果電腦',18000,10,1);
insert into t_product values(null,'iPhone8s',5500,100,1);
insert into t_product values(null,'iPhone7',5000,100,1);
insert into t_product values(null,'iPhone6s',4500,1000,1);
insert into t_product values(null,'iPhone6',3800,200,1);
insert into t_product values(null,'iPhone5s',2000,10,1);
insert into t_product values(null,'iPhone4s',18000,1,1);

insert into t_product values(null,'方便面',4.5,1000,2);
insert into t_product values(null,'咖啡',10,100,2);
insert into t_product values(null,'礦泉水',2.5,100,2);

insert into t_product values(null,'法拉利',3000000,50,null);

-- 給 商品表添加外鍵
ALTER TABLE t_product ADD FOREIGN KEY(cno) REFERENCES t_category(cid);

9.2、交叉查詢【了解】

交叉查詢其實就是將多張表的數(shù)據(jù)沒有條件地連接在一起進行展示

• 語法

select a.列,a.列,b.列,b.列 from a,b ;  

select a.*,b.* from a,b ;  
--或者 
select * from a,b;

• 練習(xí)

使用交叉查詢類別和商品

select * from t_category,t_product;

通過查詢結(jié)果我們可以看到，交叉查詢其實是一種錯誤的做法，在查詢到的結(jié)果集中有大量的錯誤數(shù)據(jù)，我們稱交叉查詢到的結(jié)果集是笛卡爾積

• 笛卡爾積

假設(shè)集合A={a,b}，集合B={0,1,2}，則兩個集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以擴展到多個集合的情況。

9.3、內(nèi)連接查詢

交叉查詢產(chǎn)生這樣的結(jié)果并不是我們想要的，那么怎么去除錯誤的、不想要的記錄呢，當然是通過條件過濾。通常要查詢的多個表之間都存在關(guān)聯(lián)關(guān)系，那么就通過**關(guān)聯(lián)關(guān)系(主外鍵關(guān)系)**去除笛卡爾積。這種通過條件過濾去除笛卡爾積的查詢，我們稱之為連接查詢。連接查詢又可以分為內(nèi)連接查詢和外連接查詢，我們先學(xué)習(xí)內(nèi)連接查詢

• 隱式內(nèi)連接查詢

隱式內(nèi)連接查詢里面是沒有inner join關(guān)鍵字

select [字段,字段,字段] from a,b where 連接條件 (b表里面的外鍵 = a表里面的主鍵 ) 

• 顯式內(nèi)連接查詢

顯式內(nèi)連接查詢里面是有inner join關(guān)鍵字

select [字段,字段,字段] from a [inner] join b on 連接條件 [ where 其它條件]

• 內(nèi)連接查詢練習(xí)

查詢所有類別下的商品信息,如果該類別下沒有商品則不展示

-- 1 隱式內(nèi)連接方式 
select *from t_category  c, t_product  p WHERE c.cid = p.cno;

-- 2 顯示內(nèi)連接方式
-- 查詢手機數(shù)碼這個分類下的所有商品的信息以及分類信息
SELECT * FROM t_product tp INNER JOIN t_category tc ON tp.cno = tc.cid WHERE tc.cname = '手機數(shù)碼';

SELECT * from t_category c INNER JOIN t_product p ON c.cid = p.cno

• 內(nèi)連接查詢的特點

主表和從表中的數(shù)據(jù)都是滿足連接條件則能夠查詢出來，不滿足連接條件則不會查詢出來

9.4、外連接查詢

我們發(fā)現(xiàn)內(nèi)連接查詢出來的是滿足連接條件的公共部分， 如果要保證查詢出某張表的全部數(shù)據(jù)情況下進行連接查詢. 那么就要使用外連接查詢了. 外連接分為左外連接和右外連接

• 左外連接查詢

概念

以join左邊的表為主表,展示主表的所有數(shù)據(jù),根據(jù)條件查詢連接右邊表的數(shù)據(jù),若滿足條件則展示,若不滿足則以null顯示。可以理解為：在內(nèi)連接的基礎(chǔ)上保證左邊表的數(shù)據(jù)全部顯示

語法

select 字段 from a left [outer] join b on 條件

練習(xí)

查詢所有類別下的商品信息，就算該類別下沒有商品也需要將該類別的信息展示出來

SELECT * FROM t_category c LEFT OUTER JOIN t_product p ON c.cid = p.cno

• 右外連接查詢

概念

以join右邊的表為主表,展示右邊表的所有數(shù)據(jù),根據(jù)條件查詢join左邊表的數(shù)據(jù),若滿足則展示,若不滿足則以null顯示?？梢岳斫鉃椋涸趦?nèi)連接的基礎(chǔ)上保證右邊表的數(shù)據(jù)全部顯示

語法

select 字段 from a right [outer] join b on 條件

練習(xí)

查詢所有商品所對應(yīng)的類別信息

SELECT * FROM t_category c RIGHT  OUTER JOIN t_product p ON c.cid = p.cno

9.5、union聯(lián)合查詢實現(xiàn)全外連接查詢

首先要明確，聯(lián)合查詢不是多表連接查詢的一種方式。聯(lián)合查詢是將多條查詢語句的查詢結(jié)果合并成一個結(jié)果并去掉重復(fù)數(shù)據(jù)。

全外連接查詢的意思就是將左表和右表的數(shù)據(jù)都查詢出來，然后按照連接條件連接

• union的語法

查詢語句1 union 查詢語句2 union 查詢語句3 ...

• 練習(xí)

# 用左外的A union 右外的B
SELECT * FROM t_category c LEFT OUTER JOIN t_product p ON c.cid = p.cno
union
SELECT * FROM t_category c RIGHT  OUTER JOIN t_product p ON c.cid = p.cno

9.6、自連接查詢

自連接查詢是一種特殊的多表連接查詢，因為兩個關(guān)聯(lián)查詢的表是同一張表，通過取別名的方式來虛擬成兩張表，然后進行兩張表的連接查詢

• 準備工作

-- 員工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 員工id
  ename VARCHAR(50), -- 員工姓名
  mgr INT , -- 上級領(lǐng)導(dǎo)
  joindate DATE, -- 入職日期
  salary DECIMAL(7,2) -- 工資
);
-- 添加員工
INSERT INTO emp(id,ename,mgr,joindate,salary) VALUES 
(1001,'孫悟空',1004,'2000-12-17','8000.00'),
(1002,'盧俊義',1006,'2001-02-20','16000.00'),
(1003,'林沖',1006,'2001-02-22','12500.00'),
(1004,'唐僧',1009,'2001-04-02','29750.00'),
(1005,'李逵',1006,'2001-09-28','12500.00'),
(1006,'宋江',1009,'2001-05-01','28500.00'),
(1007,'劉備',1009,'2001-09-01','24500.00'),
(1008,'豬八戒',1004,'2007-04-19','30000.00'),
(1009,'羅貫中',NULL,'2001-11-17','50000.00'),
(1010,'吳用',1006,'2001-09-08','15000.00'),
(1011,'沙僧',1004,'2007-05-23','11000.00'),
(1012,'李逵',1006,'2001-12-03','9500.00'),
(1013,'小白龍',1004,'2001-12-03','30000.00'),
(1014,'關(guān)羽',1007,'2002-01-23','13000.00');
#查詢孫悟空的上級
SELECT employee.*,manager.ename mgrname FROM emp employee,emp manager where employee.mgr=manager.id AND employee.ename='孫悟空'

• 自連接查詢練習(xí)

查詢員工的編號，姓名，薪資和他領(lǐng)導(dǎo)的編號，姓名，薪資

#這些數(shù)據(jù)全部在員工表中
#把t_employee表，即當做員工表，又當做領(lǐng)導(dǎo)表
#領(lǐng)導(dǎo)表是虛擬的概念，我們可以通過取別名的方式虛擬
SELECT employee.id "員工的編號",emp.ename "員工的姓名" ,emp.salary "員工的薪資",
 manager.id "領(lǐng)導(dǎo)的編號" ,manager.ename "領(lǐng)導(dǎo)的姓名",manager.salary "領(lǐng)導(dǎo)的薪資"
FROM emp employee INNER JOIN emp manager
#emp employee：employee.，表示的是員工表的
#emp manager：如果用manager.，表示的是領(lǐng)導(dǎo)表的
ON employee.mgr = manager.id  # 員工的mgr指向上級的id

#表的別名不要加""，給列取別名，可以用""，列的別名不使用""也可以，但是要避免包含空格等特殊符號。

第十章 子查詢

如果一個查詢語句嵌套在另一個查詢語句里面，那么這個查詢語句就稱之為子查詢，根據(jù)位置不同，分為：where型，from型，exists型。注意：不管子查詢在哪里，子查詢必須使用()括起來。

10.1、where型

①子查詢是單值結(jié)果(單行單列)，那么可以對其使用（=，>等比較運算符）

# 查詢價格最高的商品信息
select * from t_product where price = (select max(price) from t_product)

②子查詢是多值結(jié)果，那么可對其使用（【not】in(子查詢結(jié)果)，或 >all(子查詢結(jié)果)，或>=all(子查詢結(jié)果)，<all(子查詢結(jié)果)，<=all（子查詢結(jié)果)，或 >any(子查詢結(jié)果)，或>=any(子查詢結(jié)果)，<any(子查詢結(jié)果)，<=any（子查詢結(jié)果)）

# 查詢價格最高的商品信息
SELECT * FROM t_product WHERE price >=ALL(SELECT price FROM t_product)
select * from t_product order by price desc limit 0,1

10.2、from型

子查詢的結(jié)果是多行多列的結(jié)果，類似于一張表格。

必須給子查詢?nèi)e名，即臨時表名，表的別名不要加“”和空格。

-- 思路一: 使用連接查詢
-- 使用外連接，查詢出分類表的所有數(shù)據(jù)
SELECT tc.cname,COUNT(tp.pid) FROM t_category tc LEFT JOIN t_product tp ON tp.cno = tc.cid GROUP BY tc.cname

-- 思路二: 使用子查詢
-- 第一步:對t_product根據(jù)cno進行分組查詢，統(tǒng)計每個分類的商品數(shù)量
SELECT cno,COUNT(pid) FROM t_product GROUP BY cno
-- 第二步: 用t_category表去連接第一步查詢出來的結(jié)果，進行連接查詢,此時要求查詢出所有的分類
SELECT tc.cname,IFNULL(tn.total,0) '總數(shù)量' FROM t_category tc LEFT JOIN (SELECT cno,COUNT(pid) total FROM t_product GROUP BY cno) tn ON tn.cno=tc.cid

10.3、exists型

# 查詢那些有商品的分類
SELECT cid,cname FROM t_category tc WHERE EXISTS (SELECT * FROM t_product tp WHERE tp.cno = tc.cid);

版權(quán)聲明：本文為CSDN博主「寧靜與致遠」的原創(chuàng)文章，遵循CC 4.0 BY-SA版權(quán)協(xié)議，轉(zhuǎn)載請附上原文出處鏈接及本聲明。鏈接：blog.csdn.net/qq_42076902/article/details/121701974