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

• DB：專門存儲和維護(hù)信息的容器（軟件）
• 數(shù)據(jù)庫管理系統(tǒng)：管理數(shù)據(jù)庫的軟件。具有存儲核偿、安全、一致性顽染、并發(fā) 漾岳、恢復(fù)和訪問功能。
• 數(shù)據(jù)字典：系統(tǒng)目錄(表頭)存放各種對象的定義信息和其他一些輔助管理信息（元數(shù)據(jù)）家乘。
• 數(shù)據(jù)庫特征：數(shù)據(jù)結(jié)構(gòu)化蝗羊、數(shù)據(jù)共享、減少數(shù)據(jù)冗余仁锯、數(shù)據(jù)獨(dú)立耀找。
• 關(guān)系型數(shù)據(jù)庫：用表進(jìn)行存儲，表與表之間存在特定關(guān)系业崖。
• 字段：列名野芒，列需要定義數(shù)據(jù)類型。
• 記錄：行
• 字段值： 格
• 字符：最小的表義符號双炕；
• 字符集：所有字符和編碼對組成的集合就是字符集狞悲。
• 約束：非空、唯一妇斤、主鍵摇锋、外鍵、檢查性約束站超。
• 復(fù)合主鍵：由多個字段構(gòu)成表的主鍵荸恕，每個字段不能為空。

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

• 數(shù)字類型：
  小數(shù)類型：精確小數(shù)類型死相、浮點(diǎn)數(shù)類型
  整數(shù)類型
• 字符串類型：
  定長字符串類型char融求、變長字符串類型varchar(長度)、text
• 日期類型

常見數(shù)據(jù)類型

bit：位字段類型算撮，1-64生宛，默認(rèn)為1。
bool(boolean):布爾類型肮柜，zero值為假陷舅，非zero為真。
int：整型素挽。
float蔑赘，double：浮點(diǎn)類型。
set：集合類型，存放多選數(shù)據(jù)缩赛。
date：日期耙箍，年月日。
datetime：日期和時間酥馍，年月日時分秒辩昆。
timestamp：時間戳類型，用于記錄修改操作的日期和時間旨袒。
time：時間類型汁针。
year：兩位或四位年份類型，默認(rèn)四位砚尽。
char(m):定長字符串施无，m為存儲長度。
varchar(m):可變長字符串必孤，m為最大存儲長度猾骡。
blob：二進(jìn)制大對象。
text:大文本類型敷搪。
enum：枚舉類型兴想。

常見約束類型：

primary key：主鍵。
auto_increment:指定字段為自動增加字段赡勘。
index：為字段創(chuàng)建索引嫂便。
not null:字段值不允許為空。
null：允許為空闸与。
comment：設(shè)置字段的注釋信息毙替。
default：設(shè)置字段的默認(rèn)值。

事務(wù)處理：

• 概念：

也叫做工作單元践樱，由一個或多個sql語句組成的操作序列蔚龙，該序列作為一個完成的工作單元，全部執(zhí)行成功或全部失敗映胁，保證了數(shù)據(jù)的一致性。

• 事務(wù)處理語言：tpl

對組成事務(wù)的dml語句的操作結(jié)果進(jìn)行確認(rèn)甲雅，成功commit; 失敗rollback;

• 事務(wù)組成：

一組dml(insert/update/delete)或select語句解孙，加上
一個tpl語句組成 或者
一個ddl(create/alter/drop/truncate)語句 或者
一個dcl(grant/revoke)語句 組成。

• 事務(wù)特征：ACID

原子性：不能分割抛人。
一致性：數(shù)據(jù)一直弛姜，不論成功或失敗。
隔離性：不會被其他事務(wù)干擾妖枚。
持久性：數(shù)據(jù)一旦提交不能回滾廷臼。

• 事務(wù)處理主要方法：

1.begin、rollback、commit
2.用set改變MYSQL的自動提交模式荠商，默認(rèn)是自動提交的寂恬。
set autocommit = 0; 禁止自動提交
set autocommit = 1; 開啟自動提交

• 事務(wù)的隱式結(jié)束：

隱式提交：
執(zhí)行一個ddl(create/alter/drop/truncate/rename)語句 或者
一個dcl(grant/revoke)語句 組成。
隱式回滾：
1.客戶端強(qiáng)行退出莱没；
2.客戶端連接到服務(wù)器端異常中斷初肉；
3.系統(tǒng)崩潰。

• 保存點(diǎn)：

savepoint 保存點(diǎn)名;定義一個保存點(diǎn)語句饰躲。
rollback to 保存點(diǎn)名; 回滾到指定保存點(diǎn)牙咏。
不結(jié)束事務(wù)的執(zhí)行。

SQL語句：

• 數(shù)據(jù)庫：

create database [if not exists] 數(shù)據(jù)庫名;
使用if not exists,會在數(shù)據(jù)庫已存在的情況下不進(jìn)行創(chuàng)建嘹裂；不使用則會產(chǎn)生錯誤妄壶。
show create database 數(shù)據(jù)庫名;
查看數(shù)據(jù)庫的相關(guān)信息。
drop database 數(shù)據(jù)庫名寄狼；
刪除數(shù)據(jù)庫丁寄。

• ddl：對表結(jié)構(gòu)操作：

create table 表名(
字段名 數(shù)據(jù)類型 [約束條件],
.
.
[其他約束條件],
[其他約束條件]
)其他選項(xiàng)(例如存儲引擎，字符集選項(xiàng))
create table 新表名 like 源表;
復(fù)制源表的表結(jié)構(gòu)到新表中例嘱，無數(shù)據(jù)狡逢。
create table 新表名 select * from 源表;
復(fù)制源表的表結(jié)構(gòu)和數(shù)據(jù)到新表中。
drop table 表名;
刪除表拼卵。
alter table 表名 add column 列名 數(shù)據(jù)類型和長度 列屬性;
alter table 表名 modify column 列名 數(shù)據(jù)類型和長度 新列屬性;
alter table 表名 drop column 列名;
修改表奢浑。如果此時有數(shù)據(jù)怎么辦？數(shù)據(jù)類型不符合會報(bào)錯腋腮。
alter table 表名 add constraint 約束名 約束類型(字段名);
alter table 表名 drop primary key;
alter table 表名 drop foreign key 約束名;
alter table 表名 drop index 索引名; 刪除唯一性約束
修改約束
alter table 表名 engine=新的存儲引擎類型
alter table 表名 default charset=新的字符集
alter table 表名 auto_increment=新的初始值
alter table 表名 pack_keys=新的壓縮類型
rename table 舊表名 to 新表名 = alter table 舊表名 rename 新表名
desc[ribe] 表名;
顯示表的結(jié)構(gòu)雀彼。

• dml：對表內(nèi)容操作：

insert into 表名 [列名[,列名...]]
values (value[,value...]);
插入數(shù)據(jù)，一次一條數(shù)據(jù)即寡，沒有列名的就按順序插入徊哑，其順序可以用sql語句：desc 表名; 查看。有列名需要保持列名列表和值列表在個數(shù)和數(shù)據(jù)類型上保持一致聪富。字符和日期型數(shù)據(jù)需要用單引號括起來莺丑。插入空值時，可以在列名列表中忽略該列或者指定 null關(guān)鍵字或'' 墩蔓。
insert into 表名 [列名列表]
values (值列表1),(值列表2)...(值列表n);
批量插入數(shù)據(jù)梢莽。
insert into 表名 [列名列表] 子查詢;
不用寫values子句，將通過子查詢找到的記錄直接插入到表中奸披，需要保持列名列表和子查詢返回的各個字段在個數(shù)和數(shù)據(jù)類型上保持一致昏名。
update 表名 set 列名=值[,列名=值..] [where 條件];
按照指定條件修改表中某些行的列數(shù)據(jù)。
delete from 表名 [where 條件];
刪除記錄阵面。如果數(shù)據(jù)行的主鍵作為外鍵被其他表引用轻局，不能刪除該記錄洪鸭。
truncate table 表名;
截?cái)啾恚?br> 與delete區(qū)別：
truncate是ddl語句，刪除表中所有記錄仑扑，釋放存儲空間览爵，使用rollback不可以回滾。
delete是dml語句夫壁，刪除表中指定記錄拾枣，不釋放存儲空間，使用rollback可以回滾盒让。

• dql：查詢語句：

select [distinct]{*|列名|表達(dá)式 [[as] 列別名]} from 表名 [where 條件] [group by 列名] [having] [order by {列名|表達(dá)式|列別名|結(jié)果集的列序號} [asc（默認(rèn)升序）|desc],...] [limit];
select語句執(zhí)行過程：
from>where>group by>having>select>order by

• 單行函數(shù)：

形式：
函數(shù)名[(參數(shù)1梅肤，參數(shù)2...)] 參數(shù)可以是變量 、 列名 邑茄、 表達(dá)式
特征：
對單行操作
每行返回一個結(jié)果
返回值與原參的數(shù)據(jù)類型可能不一致
可以寫在select 姨蝴、 where 、 order by 子句中肺缕。
函數(shù)可以嵌套
參數(shù)個數(shù)不一定左医。

• 常用函數(shù)：

• 數(shù)字函數(shù)：
  abs(x)
  sqrt(x) 返回非負(fù)數(shù)x的平方根
  pi()
  mod(x,y)或% 返回x除以y的余數(shù)
  ceil(x)
  floor(x)
  round(x,y) 返回保留y位小數(shù)，四舍五入后的數(shù)同木。
  truncate(x,y) 返回保留y位小數(shù)浮梢，截取后的數(shù)字。
  rand()
  sign(x) 返回值為1彤路，-1秕硝，0。
  pow(x,y) 返回x的y次方的結(jié)果值
  exp(x) 返回e的x次方的結(jié)果值
  log(x)
  log10(x)

• 字符串函數(shù)：
  char_length(str) 返回包含字符的長度
  length(str) 返回字節(jié)長度
  concat(s1,s2,..) 連接 如果有參數(shù)為null 則返回null
  concat(x,s1,s2,..) 以x作為分隔符連接
  insert(s1,x,len,s2) 返回字符串s1洲尊，在位置x起始的子串且len個字符長的子串由字符串s2代替远豺，如果子串超過s1長度返回原串，若有參數(shù)為null坞嘀，則返回null躯护。ppt第五章22頁？
  lower(str)
  upper(str)
  left(s,n) 返回左邊指定長度的字符串
  lpad(s1,len,s2) 返回s1丽涩，其左邊由s2填充到指定長度
  trim(s)
  repeat(s,n) 返回n個s組成的新字符串棺滞，n<=0,返回空字符串，s或n為null矢渊，返回null检眯。
  space(n) 返回n個空格組成的字符串。
  replace(s,s1,s2) 將s中的s1替換成s2
  strcmp(s1,s2) 比較字符串大小昆淡，若相同返回0，s1小于s2刽严，返回-1昂灵，其他情況返回1.
  substring(s,n,len) 從s的n位置截取len長度的子串避凝。
  locate(str1,str) position(str1 in str) instr(str,str1) 返回str1在str中的開始位置。
  reverse(s) 反轉(zhuǎn)
  elt(n,s1,s2,s3...) 返回第n個字符串眨补，若n小于1或大于參數(shù)個數(shù)返回null管削。

• 日期和時間函數(shù)：
  curdate() 獲取當(dāng)前日期 返回格式根據(jù)數(shù)據(jù)類型而定“yyyy-mm-dd”，“yyyymmdd”
  now() 返回服務(wù)器當(dāng)前日期和時間 “yyyy-mm-dd hh：mm：ss”撑螺，“yyyymmddhhmmss”
  curtime() 返回時間含思，時分秒
  utc_date() 返回世界標(biāo)準(zhǔn)時間日期
  utc_time() 返回世界標(biāo)準(zhǔn)時間
  timediff(e1,e2) 返回兩個時間相減e1-e2相差的時間數(shù) 參數(shù)的類型必須相同
  datediff(e1,e2) 返回相差的天數(shù)
  date_add(date,interval expr unit) 日期加上一個時間間隔 interval是關(guān)鍵字，expr是表達(dá)式甘晤，unit是間隔單位含潘。
  date_sub(date,interval expr unit) 日期減去一個時間間隔
  date(date)/time(date)/year(date) 選取日期時間的各個部分
  extract(unit from date) 從日期中抽取某個單獨(dú)的部分或組合。
  dayofweek(date)/dayofmonth(date)/dayofyear(date)
  dayname/ monthname 返回值是中文或英文由lc_time_names控制线婚，默認(rèn)是en_US, sql: show variables like 'lc_time_names'; set lc_time_names='zh_CN'; set names gbk;
  date_formate(date,format) 格式 '%Y-%m-%d %H:%i:%s'
  time_formate(time,format)

• 流程控制函數(shù)：
  case
  case value when [compare-value] then result [when [compare-value] then result...] [else result] end
  case when [condition] then result [when [condition] then result...] [else result] end
  第二種方式?jīng)]有else返回null
  if
  if(e1,e2,e3) 類似(?:)
  ifnull遏弱、nullif
  ifnull(s1,s2) 加入s1不為null，則返回s1塞弊，否則返回s2漱逸，返回值數(shù)據(jù)類型取決使用的語境。

• 其他函數(shù)：
  database() 返回使用utf8字符集默認(rèn)(當(dāng)前)數(shù)據(jù)庫名
  version() 返回指示mysql服務(wù)器版本的字符串
  user() 返回當(dāng)前mysql用戶名和機(jī)主名
  inet_aton(ip) 根據(jù)網(wǎng)絡(luò)地址游沿，返回一個表示該地點(diǎn)的整數(shù)
  inet_ntoa(num) 上面取反饰抒。
  password(str) 根據(jù)str計(jì)算并返回密碼字符串，參數(shù)為null時返回null诀黍。
  md5()

• 多行函數(shù)

概述
對一組記錄進(jìn)行操作袋坑，返回一個結(jié)果，分組時可能是整個表分為一組蔗草，也可能是根據(jù)條件分為多組咒彤。
常用函數(shù)
min、
max咒精、
sum镶柱、
avg、
count 計(jì)算非空記錄的個數(shù)模叙，其他函數(shù)會忽略空值歇拆。

• 子查詢

概述
括號內(nèi)的查詢叫做子查詢，也叫內(nèi)部查詢范咨，先于主查詢執(zhí)行
使用位置
where故觅、having、from
使用指導(dǎo)
子查詢要用括號括起來
將子查詢放在比較運(yùn)算符的右邊
對于單行子查詢要使用單行運(yùn)算符（<渠啊、>输吏、<=、>=替蛉、=贯溅、!=）
對于多行子查詢要使用多行運(yùn)算符（in拄氯、any、all）
分類
單行 返回一條記錄它浅，比如具體條件和多行函數(shù)
多行 使用多行操作符in译柏、any（>any:大于最小 <any:小于最大）、all（>all：大于最大 <all:小于最薪慊簟）
多列

• 多表連接：

等值連接鄙麦、非等值連接
外部鏈接，內(nèi)部鏈接

標(biāo)準(zhǔn)連接語法：
cross join 代替 多表連接的“,”镊折，會產(chǎn)生笛卡爾積胯府。
natural join
直接進(jìn)行等值連接。用所有名稱和數(shù)據(jù)類型相匹配的列作為連接條件腌乡。如果兩個表之間相同名稱的列的數(shù)據(jù)類型不同盟劫，則會產(chǎn)生錯誤,應(yīng)使用using子句避免錯誤。
using子句
指定相同名字和數(shù)據(jù)類型的列作為連接條件与纽。 select * from emp join dept using (deptno) where deptno=20; NATURAL JOIN子句和USING子句是相互排斥的侣签，不能同時使用
on
select * from emp e join emp m on (e.mgr=m.empno);
left/right outer join
沒有員工的部門也要顯示出來：select * from emp right outer join dept on (emp.deptno=dept.deptno);
基于左/右邊表為基表查詢，無論右/左是否有與之匹配的急迂，都按照左/右顯示影所。

• 運(yùn)算優(yōu)先級：

算術(shù)運(yùn)算符*/-+ ， ||(連接運(yùn)算符) 僚碎， 比較運(yùn)算符 猴娩， 特殊比較運(yùn)算(like ,in ) ， not , and , or

• 函數(shù)及部分運(yùn)算符：

between...and...
in (集合列表)
like : %任意 一個
is null
escape 轉(zhuǎn)譯字符：使用方式：
例 select * from emp where ename like 'MAN@%' escape '@';
查找名字以“MAN_”開頭的員工信息勺阐，將“@”作為聲明標(biāo)志卷中。
and、or渊抽、not
sysdate() 函數(shù)記錄當(dāng)前日期和時間蟆豫。
coalesce(，分隔) 返回列表中第一個非null的值懒闷。
greatest(十减，分隔) 返回其中最大的值。
least(愤估，分隔) 返回其中最小的值

• 視圖

• 概述
  邏輯上帮辟，來自一個或多個表的集合。
• 作用
  限制其他用戶對表的訪問玩焰，因?yàn)橐晥D可以有選擇性的顯示表的一部分由驹，對于相同數(shù)據(jù)可以產(chǎn)生不同的視圖。
  分類：根據(jù)能否進(jìn)行dml操作
• 簡單視圖：基表數(shù)量 只能一個昔园，包含函數(shù) 無荔棉，包含數(shù)據(jù)組 無闹炉，通過視圖實(shí)現(xiàn)dml操作 可以
• 復(fù)雜視圖：基表數(shù)量 一或多個，包含函數(shù) 有润樱，包含數(shù)據(jù)組 有，通過視圖實(shí)現(xiàn)dml操作 不可
• 創(chuàng)建視圖語法
  create [or replace] [algorithm={merge|temptable|undefined}]
  view 視圖名 [(列別名...)]
  as select語句
  [with [cascaded|local]約束條件];
  or replace：如果所創(chuàng)建的視圖已經(jīng)存在羡棵，該選項(xiàng)表示修改原視圖的定義壹若；
  algorithm表示使用何種算法來處理視圖，是MySQL對標(biāo)準(zhǔn)SQL進(jìn)行的功能擴(kuò)展皂冰，默認(rèn)為undefined
  create view子句中別名的順序必須和內(nèi)部查詢中的列的順序一一對應(yīng)
  在“簡單視圖”上進(jìn)行dml操作的條件
  刪除
  無Group 函數(shù)店展；
  無GROUP BY 子句；
  無DISTINCT 關(guān)鍵字秃流；
  修改
  無Group 函數(shù)赂蕴；
  無GROUP BY 子句；
  無DISTINCT 關(guān)鍵字舶胀；
  使用表達(dá)式定義的列
  插入
  無Group 函數(shù)概说；
  無GROUP BY 子句；
  無DISTINCT 關(guān)鍵字嚣伐；
  使用表達(dá)式定義的列
  基表中未在視圖中選擇的其它列定義為非空并且沒有默認(rèn)值糖赔？？
  WITH CHECK OPTION子句
  CREATE OR REPLACE VIEW empvu20 AS SELECT * FROM emp WHERE deptno = 20 WITH CHECK OPTION;
  任何修改部門編號的操作都會失敗轩端，因?yàn)檫@違反了 WITH CHECK OPTION約束放典。
  刪除視圖
  刪除視圖并不會刪除數(shù)據(jù)，因?yàn)橐晥D是基于數(shù)據(jù)庫中的基表產(chǎn)生的虛表

索引

• 概述

由數(shù)據(jù)庫表中一列或多列組合而成基茵，其作用是提高對表中數(shù)據(jù)的查詢速度奋构。創(chuàng)建在表上，是對數(shù)據(jù)庫表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu)拱层。索引可以提高查詢的速度弥臼。通過索引，查詢數(shù)據(jù)時可以不必讀完記錄的所有信息舱呻，而只是查詢索引列醋火。

小細(xì)節(jié)

1.數(shù)據(jù)庫中長度指的是字符長度，中文每個字占兩個字符箱吕。
2.create table 最后一個字段不寫“,”;
3.日期格式： '2018-12-12'
4.任何包含空值的算術(shù)表達(dá)式運(yùn)算后的結(jié)果都為空值芥驳。
5.當(dāng)遇到以下3種情況需要在列別名兩側(cè)添加雙引號：包含空格；要求區(qū)分大小寫茬高；包含特殊字符兆旬。
6.where 子句中，字符型數(shù)據(jù)和日期型數(shù)據(jù)作為被比較的值時怎栽，必須用單引號引起來丽猬，字符型數(shù)值區(qū)分大小寫宿饱。
7.order by ：升序：數(shù)字：小的在前；日期：早的在前脚祟；字符：A-Z排列谬以；中文：字典順序；空值：最前由桌。
8.limit [n,]m :從第n行開始为黎，m條記錄，第一條記錄的n為0行您。當(dāng)偏移量(n)很大時效率不高铭乾，可通過語句改善
9.distinct 消除重復(fù)行之后再使用分組函數(shù)。
10.在SELECT列表中除了分組函數(shù)那些項(xiàng)娃循，所有列都必須包含在group by 子句中炕檩。
11.不能在 WHERE子句中限制組，可以通過 HAVING 子句限制組
12.注意多行子查詢前的運(yùn)算符是否匹配０聘５阎省！
13.所有的條件和空值比較結(jié)果都是空值骤星，無論什么時候只要空值有可能成為子查詢結(jié)果集合中的一部分经瓷，就不能使用NOT IN 運(yùn)算符