一、什么是范式？

范式是數(shù)據(jù)庫設(shè)計時遵循的一種規(guī)范，不同的規(guī)范要求遵循不同的范式。

最常用的三大范式

第一范式(1NF)：屬性不可分割，即每個屬性都是不可分割的原子項。(實體的屬性即表中的列)

第二范式(2NF)：滿足第一范式；且不存在部分依賴，即非主屬性必須完全依賴于主屬性。(主屬性即主鍵；完全依賴是針對于聯(lián)合主鍵的情況，非主鍵列不能只依賴于主鍵的一部分)

第三范式(3NF)：滿足第二范式；且不存在傳遞依賴，即非主屬性不能與非主屬性之間有依賴關(guān)系，非主屬性必須直接依賴于主屬性，不能間接依賴主屬性。（A -> B, B ->C, A -> C）

舉例說明3NF：

1NF

屬性不可再分，即表中的每個列都不可以再進行拆分。

如下學(xué)生信息表(student)：

id、name(姓名)、sex_code(性別代號)、sex_desc(性別描述)、contact(聯(lián)系方式)

primary key(id)

id name sex_code sex_desc contact 001 張三 0 男 17835201234_山西省運城市xx村 002 李四 0 男 17735204567_山西省呂梁市yy村 003 王五 1 女 18835207890_山西省太原市zz村

如果在查詢學(xué)生表時經(jīng)常用到學(xué)生的電話號，則應(yīng)該將聯(lián)系方式(contact)這一列分為電話號(phone)和地址(address)兩列，這樣才符合第一范式。

修改使表滿足1NF后：

id name sex_code sex_desc phone address 001 張三 0 男 17835201234 山西省運城市xx村 002 李四 0 男 17735204567 山西省呂梁市yy村 003 王五 1 女 18835207890 山西省太原市zz村

?判斷表是否符合第一范式，列是否可以再分，得看需求，如果將電話號和地址分開才能滿足查詢等需求時，那之前的表設(shè)計就是不滿足1NF的，如果電話號和地址拼接作為一個字段也可以滿足查詢、存儲等需求時，那它就滿足1NF。

2NF

在滿足1NF的前提下，表中不存在部分依賴，非主鍵列要完全依賴于主鍵。(主要是說在聯(lián)合主鍵的情況下，非主鍵列不能只依賴于主鍵的一部分)

如下學(xué)生成績表(score)：

stu_id(學(xué)生id)、kc_id(課程id)、score(分數(shù))、kc_name(課程名)

primary key(stu_id, kc_id)

stu_id kc_id score kc_name 001 1011 85 高數(shù)3-1 001 1022 79 計算機組成原理 002 1011 59.9 高數(shù)3-1

表中主鍵為stu_id和kc_id組成的聯(lián)合主鍵。滿足1NF；非主鍵列score完全依賴于主鍵，stu_id和kc_id兩個值才能決定score的值；而kc_name只依賴于kc_id，與stu_id沒有依賴關(guān)系，它不完全依賴于主鍵，只依賴于主鍵的一部分，不符合2NF。

修改使表滿足2NF后：

成績表(score) ??primary key(stu_id)

stu_id kc_id score 001 1011 85 001 1022 79 002 1011 59.9

課程表(kc) ? primary key(kc_id)

kc_id kc_name 1011 高數(shù)3-1 1022 計算機組成原理

將原來的成績表(score)拆分為成績表(score)和課程表(kc)，而且兩個表都符合2NF。

3NF：

在滿足2NF的前提下，不存在傳遞依賴。(A -> B, B -> C, A->C)

如下學(xué)生信息表(student)：

primary key(id)

id name sex_code sex_desc phone address 001 張三 0 男 17835201234 山西省運城市xx村 002 李四 0 男 17735204567 山西省呂梁市yy村 003 王五 1 女 18835207890 山西省太原市zz村

表中sex_desc依賴于sex_code，而sex_code依賴于id(主鍵)，從而推出sex_desc依賴于id(主鍵)；sex_desc不直接依賴于主鍵，而是通過依賴于非主鍵列而依賴于主鍵，屬于傳遞依賴，不符合3NF。

修改表使?jié)M足3NF后：

學(xué)生表(student) ??primary key(id)

id name sex_code phone address 001 張三 0 17835201234 山西省運城市xx村 002 李四 0 17735204567 山西省呂梁市yy村 003 王五 1 18835207890 山西省太原市zz村

性別代碼表(sexcode) ? primary key(sex_code)

sex_code sex_desc 0 男 1

女

將原來的student表進行拆分后，兩個表都滿足3NF。

什么樣的表越容易符合3NF？

非主鍵列越少的表。(1NF強調(diào)列不可再分；2NF和3NF強調(diào)非主屬性列和主屬性列之間的關(guān)系)

如代碼表(sexcode)，非主鍵列只有一個sex_desc；

或者將學(xué)生表的主鍵設(shè)計為primary key(id,name,sex_code,phone)，這樣非主鍵列只有address，更容易符合3NF。

二、多表關(guān)系

在進行數(shù)據(jù)庫表結(jié)構(gòu)的設(shè)計時，會根據(jù)業(yè)務(wù)的需求和業(yè)務(wù)模塊之間的關(guān)系，分析設(shè)計表結(jié)構(gòu)，由于業(yè)務(wù)之間相互關(guān)聯(lián)，所以各個表結(jié)構(gòu)之間也存在各種聯(lián)系

表與表之間的聯(lián)系：

1.一對多(多對一)
2.多對多
3.一對一

一對多(多對一)

例如，一個員工對應(yīng)一個部門，一個部門可以對應(yīng)多個員工

編輯

一般在多的一方創(chuàng)建外鍵，指向一的那一方
員工與部門，在員工表上設(shè)置外鍵，指向部門表

多對多

例如，一個學(xué)生可以選修多門課程，一個課程可以被多名學(xué)生選修
一般會建立第三張表，至少包含兩個外鍵，分別指向兩張表的主鍵

編輯

一對一

例如，用戶和自己的學(xué)歷信息的關(guān)系，一個人只對應(yīng)一條學(xué)歷信息
可以在任意一方加入外鍵，關(guān)聯(lián)另一方的主鍵，并且設(shè)置外鍵為唯一(unique)

?

編輯

?注：可以放在一張表中，但是對其進行拆分，一張表放基礎(chǔ)信息，另一張表放詳情，可以提升操作效率

三、多表查詢

概述：
從多張表中查詢數(shù)據(jù)
笛卡爾積：
笛卡爾積為兩個集合(兩張表)中的每條數(shù)據(jù)進行兩兩組合的結(jié)果
在多表查詢時會產(chǎn)生笛卡爾積，要通過添加條件消除笛卡爾積

編輯

?dept表：

編輯

?emp表：

編輯

查詢產(chǎn)生笛卡爾積的結(jié)果：

select * from emp, dept ;

編輯

?消除笛卡爾積(添加條件)：

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

編輯

多表查詢的分類

1.連接查詢：

內(nèi)連接： 相當(dāng)于查詢AB的交集部分 外連接： 左外連接： 查詢A的所有數(shù)據(jù)，同時拼接上B對應(yīng)的數(shù)據(jù) 右外連接： 查詢B的所有數(shù)據(jù)，同時拼接上A中對應(yīng)的數(shù)據(jù) 自連接： 表與自身連接查詢 自連接必須給表取別名

編輯

2.子查詢

數(shù)據(jù)準備

部門表：

create table dept ( ? ?id int auto_increment primary key comment 'id', ? ?name varchar(50) not null comment '部門名稱') comment '部門表';insert into dept (id, name)values (1, '研發(fā)部'), ? ? ? (2, '市場部'), ? ? ? (3, '財務(wù)部'), ? ? ? (4, '銷售部'), ? ? ? (5, '總經(jīng)辦'), ? ? ? (6, '人事部');

員工表：

create table emp( ? ?id int auto_increment primary key , ? ?name varchar(50) not null , ? ?age int, ? ?job varchar(20) comment '職位', ? ?salary int , ? ?entrydate date comment '入職時間', ? ?managerid int comment '直屬領(lǐng)導(dǎo)id', ? ?dept_id int comment '所在部門id') comment '員工表';insert into empvalues ( 1, '金庸', 66, '總裁', 20000, '2000-01-01', null, 5 ), ? ? ? ( 2, '張無忌', 20, '項目經(jīng)理', 12500, '2005-12-05', 1, 1 ), ? ? ? ( 3, '楊曉', 33, '開發(fā)', 8400, '2000-11-03', 2, 1 ), ? ? ? ( 4, '韋一笑', 48, '開發(fā)', 11000, '2002-02-05', 2, 1 ), ? ? ? ( 5, '陳玉存', 43, '開發(fā)', 10500, '2004-09-07', 3, 1 ), ? ? ? ( 6, '小昭', 19, '程序員鼓勵師', 6600, '2004-10-12', 2, 1 ), ? ? ? ( 7, '滅絕', 60, '財務(wù)總監(jiān)', 8500, '2002-09-12', 1, 3 ), ? ? ? ( 8, '周芷若', 19, '會計', 48000, '2006-06-02', 7, 3 ), ? ? ? ( 9, '丁敏君', 23, '出納', 5250, '2009-05-13', 7, 3 ), ? ? ? ( 10, '趙敏', 20, '市場部總監(jiān)', 12500, '2004-10-12', 1, 2 ), ? ? ? ( 11, '鹿杖客', 56, '職員', 3750, '2006-10-03', 10, 2 ), ? ? ? ( 12, '何碧文', 19, '職員', 3750, '2007-05-09', 10, 2 ), ? ? ? ( 13, '東方白', 19, '職員', 5500, '2009-02-12', 10, 2 ), ? ? ? ( 14, '張三豐', 88, '銷售總監(jiān)', 14000, '2004-10-12', 1, 4 ), ? ? ? ( 15, '魚梁洲', 38, '銷售', 4600, '2004-10-12', 14, 4 ), ? ? ? ( 16, '宋遠橋', 40, '銷售', 4600, '2004-10-12', 14, 4 ), ? ? ? ( 17, '陳友諒', 42, null, 2000, '2011-10-12', 1, null );

內(nèi)連接

語法：

# 隱式內(nèi)連接select 字段列表 from 表1,表2 where 條件; # 顯示內(nèi)連接select 字段列表 from 表1 [inner] join 表2 on 連接條件;

內(nèi)連接查詢的是兩張表交集的部分

# 查詢每一個員工的姓名及關(guān)聯(lián)的部門的名稱select emp.name, dept.name from emp, dept where emp.dept_id=dept.id;select emp.name, dept.name from emp inner join dept on emp.dept_id = dept.id;

外連接

語法：

# 左外連接select 字段列表 from 表1 left [outer] join 表2 on 條件; # 右外連接select 字段列表 from 表1 right [outer] join 表2 on 條件;

左外連接相當(dāng)于查詢表1的所有數(shù)據(jù)包含表1和表2交集的部分數(shù)據(jù)
右外連接相當(dāng)于查詢表2的所有數(shù)據(jù)包含表1和表2交集部分的數(shù)據(jù)

# 查詢emp表的所有數(shù)據(jù)，和應(yīng)于的部門信息(左)select emp.*, dept.* from emp left outer join dept on emp.dept_id = dept.id; # 查詢dept表的所有數(shù)據(jù)，和對于的員工信息(右)select dept.*, emp.* from emp right outer join dept on emp.dept_id = dept.id;

左外連接和右外連接可以進行相互轉(zhuǎn)化

自連接

語法：

select 字段列表 from 表a 別名a join 表a 別名b on 條件;

自鏈接查詢可以是內(nèi)連接查詢也可以是外連接查詢

# 查詢員工及其所屬領(lǐng)導(dǎo)的名字 # 自連接可以看成兩張一樣的表進行連接查詢select a.name, b.name from emp a join emp b on a.managerid=b.id;

聯(lián)合查詢

union、union all
對于聯(lián)合查詢就是把多次查詢的結(jié)果合并起來，形成一個新的查詢結(jié)果集
語法：

select 字段列表 from 表aunion [all]select 字段列表 from 表b

# 將薪資低于5000的員工和年齡大于50的員工查詢出來select * from emp where salary>5000union allselect * from emp where age>50;

# 沒有all重復(fù)滿足條件的只出現(xiàn)一次 # 將薪資低于5000的員工和年齡大于50的員工查詢出來select * from emp where salary>5000unionselect * from emp where age>50;

對于聯(lián)合查詢的多張表的列數(shù)必須保持一致，字段類型也要保持一致
union all會將全部的數(shù)據(jù)直接合并在一起，union會對合并之后的數(shù)據(jù)去重

子查詢

概念：SQL語句中嵌套select語句為嵌套查詢，又稱子查詢
select * from 表1 where 字段=(select 字段 from 表2);
子查詢外的語句可以是insert、update、delete、select中的一個
根據(jù)子查詢的結(jié)構(gòu)不同，分為：

標量子查詢：子查詢的結(jié)果為單個值 列子查詢：子查詢的結(jié)果為一列 行子查詢：子查詢的結(jié)果為一行 表子查詢：子查詢的結(jié)果為多行多列

根據(jù)子查詢的位置，分為：

where之后from之后select之后

標量子查詢

子查詢返回的結(jié)果是單個值(數(shù)字、字符串、日期等)，最簡單的形式，這種子查詢稱為標量子查詢
常用符號：=、<>、>、>=、<、<=

# 根據(jù)銷售部門的id查詢員工信息 # 先分開查詢 # 查詢銷售部門的idselect id from dept where name='銷售部'; #id為4# 查詢銷售部門中員工的信息select * from emp where dept_id=4; # 合并為一個查詢select * from emp where dept_id=(select dept.id from dept where dept.name='銷售部' );

列子查詢

子查詢的結(jié)果為一列(可以是多行)的，這種子查詢?yōu)榱凶硬樵?br>常用操作符：

編輯

# 列子查詢 # 查詢銷售部和市場部的所有員工信息 # 查詢銷售部和市場部的idselect id from dept where name='銷售部' or name='市場部'; #id為2 4# 查詢兩個部門的所有員工select * from emp where dept_id in (2,4); # 合并select * from emp where dept_id in (select id from dept where name='銷售部' or name='市場部');

行子查詢

子查詢返回的結(jié)果是一行(可以是多列)，這種子查詢?yōu)樾凶硬樵?br>常用操作符：=、<>、in、not in

# 查詢與張無忌的薪資及直屬領(lǐng)導(dǎo)相同的員工信息 # 查詢張無忌的薪資和直屬領(lǐng)導(dǎo)select salary, managerid from emp where name='張無忌'; # 查詢與張無忌的薪資及直屬領(lǐng)導(dǎo)相同的員工信息select * from emp where (salary,managerid)=(select salary, managerid from emp where name='張無忌');

表子查詢

子查詢的結(jié)果是多行多列這種查詢?yōu)楸碜硬樵?br>常用操作符：in

# 查詢與鹿杖客和宋遠橋的職位和薪資相同的員工信息select * from emp where (job, salary) in ( select job, salary from emp where name in ('鹿杖客', '宋遠橋'));

表子查詢的子表作為臨時表

# 查詢?nèi)肼毴掌谑恰?006-01-01‘之后的員工信息和部門信息 # 先查詢出入職在’2006-01-01‘之后員工的所有信息 # 與部門表左連接select e.*, dept.* from (select * from emp where entrydate>'2006-01-01') e left outer join dept on e.dept_id=dept.id;

四、函數(shù)sql語法

1. 常見聚合函數(shù)

count（列名） max（列名）//求列中的最大值； min （列名）//求列中的最小值； avg（列名） //平均分 sum（列名） //總分

使用格式：

select count（列名），不統(tǒng)計為null的值； select count ( id ) 總?cè)藬?shù) from stu1

2. 其他函數(shù)

字符串函數(shù)、數(shù)學(xué)函數(shù)、日期函數(shù)

字符串函數(shù)

1，字符串函數(shù)length select length（‘字符串’）[ from dual ] //虛表，為讓語法更加清晰；

2，字符個數(shù)char_length select length（‘字符串’），char_length（‘哈哈’）

3，大小寫轉(zhuǎn)換upper&lower select upper（‘HelloMysql’）,lower (‘HelloMysql’)

4，截取字符串subString //沒有第0位； select subString(‘你看看從哪里截取，從后面截取’，8)

5，部分截取字符串subString subString (字符串，開始位置，結(jié)束位置)

6，替換replace select replace(‘我看看傻帽’，‘傻帽’，‘**’)

數(shù)學(xué)函數(shù)

1，四舍五入round select round( 888.25 ), round( 888.78 )

2，保留小數(shù)位round select round（888.235 ，2） //參數(shù)2是小數(shù)位個數(shù)；

3，天花板函數(shù)ceil ceil( 88.52 ) //比數(shù)字大的 最小整數(shù)；

4，地板函數(shù)floor floor（88.53） //比數(shù)字小的最大整數(shù)；

5，取余 select mod( 10,3 )

日期函數(shù)

1，將字符串轉(zhuǎn)日期格式 select STR_TO_DATE( ‘2023-02-02’，‘%Y-%m-%d’ )

2，將日期格式轉(zhuǎn)換成字符串 select now( ) //獲取當(dāng)前時間； select date_format( now(),‘%Y-%m-%d’) //獲取當(dāng)前日期；

字符串的拼接concat

eg: select concat( name,address ) from stu3 //合并字符串；

select math+English 總分 from stu3 //做相加運算；

select 100+‘100’ //數(shù)字+內(nèi)容是數(shù)字的字符串；

select ‘a(chǎn)bc’+ 'aa' //字符串使用+，得到0； select ‘a(chǎn)bc’+null //字符串+null ,得到null;

相加運算

//數(shù)字的話，是數(shù)字的和； //數(shù)字+內(nèi)容是數(shù)字的字符串=數(shù)字；反過來也可以相加； //字符串+字符串=0； //字符串+null =null; select math+english 總分 from stu3

分組查詢group by

對一列數(shù)據(jù)進行分組，相同的內(nèi)容分為一組，通常與聚合函數(shù)一起使用，完成統(tǒng)計工作；

1，語法格式

select 字段1，字段2 from 表名 [條件] group by 分組字段 [having 條件(對于分組結(jié)果的篩選) ] [ order by ];

2，案例

查詢男女各多少人 select count（*/id） from stu3 group by sex;

查詢年齡大于25歲的人，按照性別分組，統(tǒng)計每組的人數(shù) 有條件限制時，where在group by前面； select sex 性別，count(*) from stu3 where age>25 group by sex;

查詢年齡大于25歲的人，按照性別分組，統(tǒng)計每組的人數(shù)，并只顯示性別大于2的數(shù)據(jù)； select sex 性別,count（） from stu3 where age>25 group by sex having count()>2;

分組原理： 對原始表進行抽取，抽取到新的表（臨時表），created_tem_tables；

分頁查詢limit

limit 起始行數(shù)從0開始，顯示的條數(shù)； 顯示前三條數(shù)據(jù)：select * from stu3 limit 0,3； 顯示第二頁的三條數(shù)據(jù)：select from stu3 limit 3,3；

總結(jié)規(guī)律： 第pageSize頁數(shù)，顯示的條數(shù)num; limit （pageSize-1）num+num；

博主總結(jié)不易，點個贊不過分吧，好耶！