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

數(shù)據(jù)庫簡述

數(shù)據(jù)庫就是各種數(shù)據(jù)的集合漱办，一個項目可能有一個或者多個數(shù)據(jù)庫。
一個數(shù)據(jù)庫下面會有很多張表绸吸，表就是同一類相同結(jié)構(gòu)數(shù)據(jù)的集合鼻弧。
這個固定的結(jié)構(gòu)设江，就是一個個的字段
表之間有聯(lián)系，存在關(guān)系

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

1.關(guān)系型數(shù)據(jù)庫
表和表之間存在關(guān)系攘轩，通過sql語言操作方便叉存，表結(jié)構(gòu)固定
Mysql
最常用的數(shù)據(jù)庫，目前多數(shù)項目使用度帮，必須掌握基本設(shè)計歼捏，操作

2.非關(guān)系型數(shù)據(jù)庫
數(shù)據(jù)以鍵值對的形式存儲，性能較高笨篷，容易擴展
Redis
數(shù)據(jù)存在內(nèi)存中瞳秽，速度快，常用的高性能數(shù)據(jù)庫冕屯，經(jīng)常用作緩存寂诱，保存關(guān)鍵數(shù)據(jù)
Memchache

數(shù)據(jù)庫設(shè)計

幾個重要原則，設(shè)計時注意使用

引擎選擇

Mysql數(shù)據(jù)庫分為兩種引擎
一般情況可以選擇MyISAM存儲引擎安聘，通常查詢性能相比較好
如果需要事務(wù)支持必須使用InnoDB存儲引擎

事務(wù)就是多個語句同時執(zhí)行痰洒，作為一個整體操作，保證多條語句要么都成功浴韭，要么任何一個失敗都撤銷不執(zhí)行丘喻。
舉例：銀行轉(zhuǎn)賬，你給你朋友B轉(zhuǎn)賬念颈，轉(zhuǎn)了100塊錢泉粉，第一步扣你余額100，第二步是不是給B的余額加100榴芳。一共兩條語句嗡靡，沒有事務(wù)的情況下，如果第一條扣你100執(zhí)行成功了窟感，第二條給B加100執(zhí)行失敗了讨彼，那你這個錢不就是白扣了
所以事務(wù)可以保證該范圍內(nèi)的操作要么都成功，要么都失敗撤銷什么也不做柿祈，不會有任何改動哈误，常用于訂單或者支付，涉及到錢的操作

命名規(guī)則

數(shù)據(jù)庫名躏嚎，表名蜜自，字段名
請使用英文，不要出現(xiàn)拼音卢佣，盡量使用和業(yè)務(wù)相對應(yīng)的簡單易懂的單詞

存在關(guān)系的表重荠，字段，使用部分單詞作為前綴虚茶，如用戶表user戈鲁，和用戶地址表尾膊，一個用戶是不是多個收貨地址，用戶地址表可以命名為user_address荞彼。如字段，用戶地址表user_address待笑，怎么知道這個地址是哪個用戶的鸣皂，得有個用戶id吧，可以命名user_id

字段設(shè)計

字段包括暮蹂，字段名寞缝，字段類型，字段長度
就是這字段是存的什么數(shù)據(jù)仰泻，叫什么荆陆，是數(shù)字，小數(shù)還是字符串集侯，多長被啼，存5位數(shù)，還是10位數(shù)

常用字段類型

數(shù)字類

整數(shù)類
1.INT棠枉，整數(shù)型浓体，保存123456這種數(shù)
實用例子：
保存id，長度建議11辈讶，int 11命浴，每個表的主鍵id，主鍵是啥贱除，你這一個表存了很多數(shù)據(jù)吧生闲，每一個數(shù)據(jù)都是一行，怎么知道這是哪一條月幌，得有個標志碍讯，像你的身份證號碼id。又比如excel表飞醉，每一行數(shù)據(jù)開頭是不是經(jīng)常有個標號冲茸，123456這種。注意一張表主鍵只能有一個缅帘，因為是唯一的轴术。如用戶表id標志這是哪個用戶，商品表id這是哪個商品

保存各種整數(shù)數(shù)字钦无，如商品銷量逗栽，

保存時間戳，int 長度11失暂，時間戳是什么彼宠，就是時間平常 年Y 月m 日d 小時h 分鐘m 秒s
2019.10.16鳄虱。 時間戳也是標記時間的，這個是怎么算的凭峡， 定義為從格林威治時間1970年01月01日00時00分00秒起拙已，到現(xiàn)在這個時間一共走了多少秒
2019-10-16 13:42:28，對應(yīng)時間戳1571229748摧冀，也就是2019年這個時間比1970那個時間倍踪，一共走了1571229748秒

時間戳現(xiàn)在10位，建議11索昂，好像到2048年左右這個秒數(shù)就到11位了建车，當(dāng)然估計那時候項目早沒了

2.TINYINT，保存很小的整數(shù)椒惨，最大范圍0-255或者-128到127缤至。通常設(shè)計保存狀態(tài)status之類的變化范圍很小的整數(shù)】底唬可以節(jié)省數(shù)據(jù)庫存儲空間
tinyint 長度1
保存用戶狀態(tài)领斥，你想能有幾個狀態(tài)，等于1正常秉宿，等于0不正常沒有驗證郵箱電話戒突，再或者等于2可能被封號了
以及各種狀態(tài)，如商品狀態(tài)描睦，0上架膊存，1下架〕腊龋或者訂單狀態(tài)隔崎，就是這些變化范圍很小，就幾個固定值的

保存is_deleted韵丑，是不是被刪除爵卒，一共0 1兩種選擇，1是被刪了撵彻，0沒被刪钓株。
以及保存其他邏輯類字段is_xxxx，是不是被xxx了

為什么存在is_deleted這種字段陌僵，這叫邏輯刪除轴合，比如這個商品沒有被真的刪除，還存在數(shù)據(jù)庫里面碗短，查詢的時候加上條件受葛，可以篩選掉。因為如果執(zhí)行sql語句真的刪除了，數(shù)據(jù)庫就沒有這個數(shù)據(jù)了总滩，不能恢復(fù)了纲堵。所以這種假裝刪除了

3.SMALLINT 保存大一點的整數(shù)

小數(shù)類
4.DECIMAL以及FLOAT單精度，DOUBLE雙精度闰渔，這幾個都是保存小數(shù)席函，精度就是小數(shù)的位數(shù)

常用來保存金額
decimal 12,2 小數(shù)，長度12冈涧，小數(shù)位2（小數(shù)點后面2位）
保存金額amount向挖，價格price等小數(shù)，付錢是不是有幾分幾毛這樣炕舵，所以是個小數(shù)吧，如商品價格20.99

字符串類

1.VARCHAR
保存大多數(shù)字符串的情況
用戶名username varchar 長度30
密碼password varchar 30

手機號跟畅，不要用int咽筋，看著都是數(shù)字是吧，一般建議用varchar當(dāng)作一共字符串徊件。int之類的數(shù)字類通常進行計算的奸攻，比如這個商品價格price，你買了好幾個是不是加起來算算一共多少錢虱痕。手機號這種雖然是數(shù)字但不需要計算睹耐，建議選擇varchar

以及各種
商品名稱goods_name
商品描述description varchar 255
商品圖片路徑pic_url varchar 255

內(nèi)容長度長一點的，就用varchar 200 255之類長一些

2.CHAR
char長度是固定的部翘，占固定空間硝训。varchar是可變的長度，通承滤迹可以使用varchar

3.TEXT
保存長文本窖梁，很長的數(shù)據(jù)
一段文字之類

4.LONGTEXT
特別特別長，比如一大段文字夹囚，文章的內(nèi)容

要是更長的數(shù)據(jù)纵刘，很大的，別用數(shù)據(jù)庫存了荸哟，考慮考慮別的存儲方式假哎，文件存儲

日期時間類

大多數(shù)情況可以使用int 11時間戳保存的，int類型計算起來比較快鞍历，只是展示的時候需要轉(zhuǎn)換成正常形式 年月日舵抹，總不能給用戶看時間戳多少秒

1.DATETIME
YYYY-MM-DD HH:MM:SS

2.TIMESTAMP
YYYYMMDD HHMMSS

索引

索引就是某一列或者多列的值，相當(dāng)于書的目錄堰燎，用索引可以加快查詢速度
就好比你買了本書掏父，是不是有個目錄，你想看那一章比著目錄找是不是比你一頁頁翻快多了

通常經(jīng)常查詢的字段需要加索引index秆剪，加快查詢速度赊淑。

比如爵政，商品表，category_id陶缺，商品分類id
作為用戶钾挟，你買東西是不是經(jīng)常會選擇一個分類，你想買電腦饱岸，選中電腦分類掺出。

又或者根據(jù)訂單狀態(tài)，status苫费√老牵看淘寶里面你那些訂單，未付款的百框，已付款等發(fā)貨的闲礼，待收貨的，數(shù)據(jù)庫查詢是不是得根據(jù)這個status查

商城數(shù)據(jù)庫設(shè)計例子

舉例子：
商城項目基本功能為例铐维，采用Mysql數(shù)據(jù)庫
1.用戶登錄注冊
2.商品購買下單

先分析這個功能
大概涉及到哪些信息柬泽，分成幾個表
這一個數(shù)據(jù)是什么結(jié)構(gòu)，每一個表哪些字段

分析：
1.用戶登錄注冊
用戶表user
要保存用戶信息吧嫁蛇，這個得有
保存的什么信息锨并，用戶是不是得有用戶名密碼，可能還有頭像睬棚，個性簽名第煮。
想不全沒事，后面可以再修改表的結(jié)構(gòu)抑党，加字段空盼。盡量想豐富

主鍵id，標識這個身份的int 11新荤，代表用戶id
用戶名username varchar 30
密碼password varchar 50揽趾， 通常密碼經(jīng)過md5加密

頭像avatar varchar 255，保存頭像圖片的url地址
個性簽名description varchar 50

狀態(tài)status tinyint 1苛骨， 倆情況0被禁用 1正常能登錄

創(chuàng)建時間create_time int 11 什么時候創(chuàng)建的
更新時間update_time int 11篱瞎，什么時候改過，比如哪天改了密碼了

建議每張表必備字段
id主鍵 int 11,自增 id代表當(dāng)前表的身份痒芝，用戶id俐筋，商品id等，自增代表id自動增加严衬，插入一條數(shù)據(jù)id加一
status狀態(tài) tinyint 1
is_deleted tinyint 1 是否邏輯刪除
create_time int 11 創(chuàng)建時間
update_time int 11 更新時間

2.分析商品購買下單
比較復(fù)雜澄者，買東西的過程，一點點分析

商品表goods
先知道是個什么商品，這個商品有什么商品

首先必備字段粱挡，這里id代表商品id赠幕，
商品名稱name varchar 50
商品分類category_id int 11， 對應(yīng)商品分類表
商品簡介description varchar 200
價格decimal 12,2 金額用小數(shù)
銷量volume int 11 用于計算的整數(shù)
等等等

訂單表order
你買的啥該付多少錢询筏，該給你發(fā)到什么地方去榕堰，發(fā)給誰，是不是得有個
必備字段嫌套，id標識訂單id
用戶身份user_id int 11逆屡， 標識用戶，是哪個用戶下的單
訂單狀態(tài)status tinyint 1踱讨， 各種狀態(tài)魏蔗，0未付款，1付款了痹筛，2發(fā)貨了沫勿，3確認收貨了等

收貨地址相關(guān)
聯(lián)系人username varchar 30
聯(lián)系電話telephone varchar 20
地址address varchar 100， 地址通常會拆分味混，省province市city縣county+具體街道門牌號address，其中省市縣會用code代碼標識诫惭，123這種翁锡，一個標識代表一個地名

等等比如用戶備注，快遞信息夕土，快遞名馆衔，單號

訂單商品關(guān)聯(lián)表order_goods
你這個一個訂單，可能同時買了好幾個商品怨绣，一個訂單包含多個商品
這是不是要保存多行角溃，一行是一個訂單對應(yīng)的商品
必備字段，id標識id
訂單order_id int 11篮撑， 標識訂單减细，買的這個商品是哪個訂單里面的
用戶身份user_id int 11， 標識用戶赢笨，是哪個用戶下的單
商品goods_id int 11未蝌， 標識商品，這一個商品是個具體什么商品

通過這么個關(guān)聯(lián)關(guān)系茧妒，是不是把訂單表order和商品goods連接到了一起萧吠。

關(guān)聯(lián)和外鍵

涉及到關(guān)聯(lián)和外鍵的知識

關(guān)聯(lián)
關(guān)聯(lián)就是存在一定關(guān)系
主要是三種關(guān)系，一對一桐筏，一對多纸型，多對多
1.一對一是什么，就是唯一一個與之對應(yīng)。好比一個學(xué)生student只能是一個班級class的狰腌，這個班級是唯一的除破。舉例子，用戶user和簡介資料Profile癌别，一個用戶只能有一個簡介皂岔，一個簡介也只能對應(yīng)一個用戶。商品goods和供應(yīng)商supplier展姐，一個商品只有一個供應(yīng)商躁垛，商品對供應(yīng)商，一對一關(guān)系圾笨。商品分類教馆，一個商品goods屬于一個分類category。
2.一對多擂达，就是一個對應(yīng)多個土铺，好比一個班級class有很多學(xué)生student，班級和學(xué)生板鬓，一對多悲敷。舉例一個供應(yīng)商supplier能供應(yīng)很多商品goods，供應(yīng)商-商品也是一對多關(guān)系俭令。
3.多對多后德，學(xué)生student和課程course，一個學(xué)生能上好幾節(jié)課抄腔，一節(jié)課也有很多學(xué)生上瓢湃，通常采用一個中間表，student_course表保存學(xué)生和課程的對應(yīng)關(guān)系

外鍵
關(guān)聯(lián)關(guān)系之中赫蛇，一個表里面存的绵患，代表另一個表信息id的叫做外鍵。是一個約束條件悟耘。比如訂單表order落蝙，里面是不是有個user_id，這個user_id就是外鍵暂幼，因為這個user_id代表的是另一個user表的信息掘殴。簡單說就表和表之間通過這個字段關(guān)聯(lián)。

付款表payment
你的付款記錄是不是保存一下粟誓，以后方便對賬算錢奏寨。
必備字段，id標識支付id
用戶身份user_id int 11鹰服， 標識用戶病瞳，是哪個用戶付的錢
付款方式type tinyint 1揽咕，是余額付款，還是微信套菜，還是支付寶
金額amount decimal 12,2 付了多少錢

以上完成了一個基本的數(shù)據(jù)庫設(shè)計亲善，從功能分析，到設(shè)計
想功能有哪些信息逗柴，這個信息結(jié)構(gòu)是什么蛹头。當(dāng)然一個完整的商城還包含很多功能，很多其他表戏溺。比如用戶地址表user_address渣蜗，用戶收藏表user_collect等等

基本使用

掌握基本的sql語句，sql語句是什么旷祸，就是和數(shù)據(jù)庫打交道的語言耕拷，一種指令，告訴數(shù)據(jù)庫做什么托享。
雖然有各種圖形化工具Navicat骚烧，并且框架都封裝了增刪改查，但是基本知識還是要掌握

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

創(chuàng)建數(shù)據(jù)庫
create database mydb;

刪除數(shù)據(jù)庫
drop database mydb;

使用數(shù)據(jù)庫
use mydb;

注意
sql語句以分號; 結(jié)尾

表操作

創(chuàng)建表
CREATE TABLE user (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
level int(11) unsigned NOT NULL DEFAULT '0' COMMENT '用戶等級',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意
AUTO_INCREMENT自增闰围，COMMENT是注釋
ENGINE 引擎
DEFAULT 默認值
utf8是字符編碼格式赃绊，常用utf8 general ci

刪除表
drop table user;

修改表結(jié)構(gòu)alter
增加一個username字段
alter table user add username varchar(20);

修改username字段，使其長度為30羡榴。
alter table user modify username varchar(30);

刪除username字段
alter table user drop username;

增刪改查

增加

insert into user values(1,'test');

insert into user(id,username) values(1,'test');

刪除

刪除username為test的數(shù)據(jù)
delete from user where username='test';

清空表數(shù)據(jù)
truncate table user;

修改

update user set password=123456 where username='test';

查詢

查詢表中所有user的信息
select * from user;

查詢表中所有用戶的姓名和密碼
select username,password from user;

查詢余額大于10000的所有用戶
select * from user where balance>200;

聚合查詢
統(tǒng)計個數(shù)
select count(* ) from user where balance>100;

統(tǒng)計總數(shù)
select sum(balance) from user;

此外
統(tǒng)計某字段平均值avg
統(tǒng)計某字段最大值max
統(tǒng)計某字段最小值min

查詢條件
where是表示查詢的條件碧查，數(shù)據(jù)庫 存的數(shù)據(jù)很多，你要篩選炕矮，加條件選擇你想要的。就像你買東西者冤，你篩選價格低于200的肤视，where price < 200。除了 >大于 <小于 =等于

between兩者之間涉枫，常用統(tǒng)計日期時間范圍內(nèi)的數(shù)據(jù)
WHERE create_time BETWEEN xxxxx AND xxxxxx;

like邢滑，常用于 名稱搜索， 搜索商品名稱關(guān)鍵詞愿汰，返回包含關(guān)鍵詞的商品 %like%,百分號%字符來匹配
WHERE name LIKE '%xxx%';

in,查詢在某個范圍內(nèi)也常用
WHERE id IN (1,2,3); 查詢id是1困后，2，3里的

與或非
and 條件都滿足
or 條件滿足一個
not 條件取反

其他常用
as臨時更改替代名稱衬廷，某字段 a as b摇予，本來字段叫做a，輸出結(jié)果名稱改成b
group by分組查詢吗跋，返回的結(jié)果按照某特定字段列出侧戴，GROUP BY
limit限制查詢數(shù)目宁昭，LIMIT 2;查兩條數(shù)據(jù)。常用來分頁
order by按照某順序排序酗宋，ORDER BY price DESC按照商品價格desc降序积仗，asc升序

數(shù)據(jù)庫優(yōu)化

查詢緩存

對于經(jīng)常查詢的數(shù)據(jù)，可以增加查詢緩存蜕猫，先存到緩存里面寂曹，用到直接取。比起你從數(shù)據(jù)庫再查一遍是不是快多了回右。
一般后端如php隆圆，各種框架都有查詢緩存的設(shè)計，檢測到使用相同sql語句自然是相同的查詢條件楣黍，自動從緩存中取出數(shù)據(jù)并返回匾灶，一定時間段緩存時間如5分鐘之內(nèi)，返回緩存中數(shù)據(jù)租漂，超出時間后阶女，重新查詢并且緩存該查詢結(jié)果，周而復(fù)始哩治。

善用索引

查詢頻繁的字段秃踩，一個或多個，使用索引index

Not Null

數(shù)據(jù)庫盡量不要保存Null空數(shù)據(jù)业筏，就算這個值沒有憔杨，可以設(shè)置一個初始值default值

避免Select *

建議指定字段，查詢只返回指定的字段
1減少性能浪費
2數(shù)據(jù)安全蒜胖，只返回相關(guān)字段

拆分

分庫分表
水平拆分
縱向拆分
小項目不用考慮消别，幾萬數(shù)據(jù)，對于Mysql性能影響不大
十幾萬台谢，幾十萬數(shù)據(jù)寻狂，通過索引之類的基本優(yōu)化操作也能優(yōu)化