SQL语言方方面面
1 數(shù)據(jù)庫和SQL
1.1 數(shù)據(jù)庫
DB, DBMS
DBMS的種類: 層次性數(shù)據(jù)庫, 關(guān)系型數(shù)據(jù)庫, 非關(guān)系型數(shù)據(jù)庫
RDBMS, 關(guān)系數(shù)據(jù)庫管理系統(tǒng)
1.2 數(shù)據(jù)庫的結(jié)構(gòu)
RDBMS常見的系統(tǒng)結(jié)構(gòu)是 客戶端服務(wù)器結(jié)構(gòu)
表: 在關(guān)系數(shù)據(jù)庫中管理數(shù)據(jù)的二維表
關(guān)系數(shù)據(jù)庫必須以航為單位進(jìn)行數(shù)據(jù)的讀寫
1.3 SQL
標(biāo)準(zhǔn)SQL和特定的SQL
SQL語句的分類
1) DDL 數(shù)據(jù)定義語言: create, drop, alter
2) DML 數(shù)據(jù)操作語言(90%的使用率): select, instert, update, delete
3) DCL 數(shù)據(jù)控制語言: commit, rollback, grant, revoke
SQL的基本原則
1) 以分號(hào)結(jié)尾
2) 不區(qū)分大小寫, 一般關(guān)鍵字大寫, 表首字母大寫, 列小寫
3) 常數(shù)的書寫方式是固定的, 字符串日期常數(shù)用單引號(hào), 數(shù)字直接寫就行
4) 空格要是半角的
5) 注釋有兩種寫法 單行用-- 多行用/**/
1.4 表的創(chuàng)建
數(shù)據(jù)庫的創(chuàng)建
create database 數(shù)據(jù)庫名稱;表的創(chuàng)建
create table 表名(列名 數(shù)據(jù)類型 該列的約束...表的約束 );命名規(guī)則
名字可以是: 半角英文字母, 數(shù)字, 下劃線,?
必須是以英文開頭
名稱不能重復(fù)
數(shù)據(jù)類型
integer, char(定長的字符串), varchar(不定長的字符串), date
主鍵約束
primary key
1.5 表的刪除和更新
刪除表
drop table 表名;添加行
alter table 表名 add column 列的定義;插入數(shù)據(jù)
insert into 表 values (值, 值...);2 基礎(chǔ)查詢
2.1 select語句基礎(chǔ)
1) 基本格式
select 列名, 列名... from 表名,.. ;2) 其中在列名去中輸入* 表示顯示所有列, 而且是按照表定義的列的順序進(jìn)行顯示
3) 為列設(shè)置別名
select 列名 as 別名, 列名 as 別名, ... from 表名,.. ;設(shè)置別名之后, 查詢得到的結(jié)果的行首就顯示的是別名
如果設(shè)置成中文的話需要使用雙引號(hào)來定義
4) 設(shè)置常數(shù)列
select 列名 as 別名, 列名 as 別名, 常數(shù) as 別名... from 表名,.. ;這個(gè)常數(shù)可以是數(shù)字也可是字符串
這么設(shè)置之后這個(gè)列的值就是這個(gè)常數(shù)值, 且表頭顯示的是別名, 如果不設(shè)置別名, 那也顯示成該常數(shù)
5) 去除重復(fù)行
在select后面加上關(guān)鍵字distinct
注意distinct的位置, 只能在第一個(gè)列名之前
去除是根據(jù)后面選取行的組合來判斷的重復(fù)
6) where限定行
在from之后加上where語句限定查詢出來的結(jié)果
2.2 算數(shù)運(yùn)算符和比較運(yùn)算符
算數(shù)運(yùn)算符: + - * /
關(guān)于NULL的運(yùn)算結(jié)果都是NULL
比較運(yùn)算符: = <> >= <= > <
數(shù)字的比較是按照值比較的
字符串的比較是按照字典順序進(jìn)行比較的
NULL不會(huì)和別的結(jié)果進(jìn)行比較
判斷是NULL用IS NULL
判斷不是NULL用 IS NOT NULL
2.3 邏輯運(yùn)算符
NOT 表示否定
AND OR 與或者非
AND的優(yōu)先級(jí)是高于OR的
要想按照指定的順序執(zhí)行需要使用括號(hào)
邏輯運(yùn)算中NULL需要單獨(dú)討論
對NULL使用比較運(yùn)算符得到的結(jié)果是unkonwn(不確定)
除了true, false還有unknown
在SQL中的邏輯運(yùn)算有三值邏輯
判斷值的原則是 真>unknown>假, AND取等級(jí)低的, OR取等級(jí)高的
因此在AND中, 真 AND unknown = unknown, unknown AND false = false
在OR中,?真 OR unknown = 真, unknown OR false =?unknown
3 聚合和排序
3.1 對表進(jìn)行聚合查詢
常用的聚合函數(shù)
count: 計(jì)算個(gè)數(shù)(行數(shù)), 可以指定參數(shù), 當(dāng)指定列值為NULL時(shí), 不做統(tǒng)計(jì)
sum: 求和
avg: 求平均值
max: 求最大值
min: 求最小值
關(guān)于NULL
指定列時(shí), 如果值為NULL, 會(huì)忽略
對于AVG, 如果有NULL值, 同樣也是忽略, 也就是分子沒有那個(gè)值, 分母也不算拿一行
在聚合函數(shù)中, 可以傳入?yún)?shù), 可以傳入distinct來刪除重復(fù)的值
3.2 對表進(jìn)行分組
使用group by來分組
group by后面指定的列為聚合鍵或者分組列
分組NULL同樣會(huì)單獨(dú)形成一個(gè)分組
有where會(huì)先執(zhí)行where, 得到篩選的結(jié)果之后再進(jìn)行分組
注意事項(xiàng):
1) select 不能選取除group by選定的列的其他列
原因是: 既然已經(jīng)分組了, 那么一個(gè)分組對應(yīng)的記錄就有可能是多條, 在當(dāng)前分組下, group by后面的列的值是一致的并沒有什么關(guān)系, 但是如果選取別的列, 那么就有可能是不同的值, 那么此時(shí)就不能夠確定顯示哪一個(gè)值了, 這樣就造成了歧義, 在mysql中會(huì)排序然后得到第一個(gè)值
2) group by后面不要寫別名
原因是: select語句是在group by之后執(zhí)行的, 因此使用了別名那個(gè)時(shí)候是找不到的
3) 不要在where中使用聚集函數(shù)
實(shí)際上能夠使用聚集函數(shù)的就有: select子句, having, order by, group by
3.3 為聚合結(jié)果指定條件
使用having在分組的基礎(chǔ)上進(jìn)行過濾篩選
having的組成部分
常數(shù)
聚合函數(shù)(聚合函數(shù)類的參數(shù)可以是非聚合鍵)
聚合鍵
因此在having中使用別的鍵是錯(cuò)誤的
having和where的區(qū)別
要將having和where區(qū)分開來
having: 指定組的條件
where: 指定行的條件
能使用where就使用where, 因?yàn)閣here的執(zhí)行效率更高
3.4 對查詢結(jié)果進(jìn)行排序
排序使用order by
一般地, 查詢的記錄的順序是隨機(jī)的(或者有的數(shù)據(jù)庫會(huì)有默認(rèn)的排序)
由于進(jìn)行數(shù)據(jù)的排序是在返回結(jié)果的時(shí)候執(zhí)行, 因此一般地, order by語句放在查詢語句的末尾
ASC表示升序, DESC表示降序
指定多個(gè)排序鍵
可以指定多個(gè)排序鍵, 這樣在第一個(gè)鍵相同的時(shí)候判斷第二個(gè)鍵, 進(jìn)一步確定排序的順序
NULL值會(huì)固定放在最后, 不管是升序還是降序
查詢語句的執(zhí)行順序:
from -> where?-> group by?-> having?-> select?-> order by
order by可以使用的列
表中的列
select中選取的列的序號(hào), 從1開始
聚合函數(shù)
4 數(shù)據(jù)更新
4.1 數(shù)據(jù)的插入
insert的基本語法
insert into 表名(列1, 列2, ...) values (值1, 值2,...)... ;原則上, 一次insert語句只會(huì)插入一行數(shù)據(jù)
但是有的數(shù)據(jù)庫是支持多行插入的, 用括號(hào)括起來表示一行, 用逗號(hào)間隔多行的數(shù)據(jù)
列清單可以省略, 省略之后按照順序排序數(shù)據(jù)
值可以是NULL
插入默認(rèn)值
1) 通過顯式的方式
在設(shè)置了默認(rèn)值的位置用default代替
2) 通過隱式的方式
列清單不寫該列
從其他表中賦值數(shù)據(jù)
insert into 表名(列1, 列2, ...) select... ;4.2 數(shù)據(jù)的刪除
刪除表
drop table 表名;全部刪除數(shù)據(jù)
delete from 表名;刪除指定記錄
delete from 表名 where 條件;清空表, 還原表為初始狀態(tài)
truncate 表名;不能加入where語句指定某記錄
處理速度比delete快很多
4.3 數(shù)據(jù)的更新
update的基本語法
update 表名set 列名 = 表達(dá)式...where 條件;
表達(dá)式可以是一個(gè)固定的值, 也可以是一個(gè)運(yùn)算
NULL清空
將值設(shè)置為NULL, 但是該列必須是允許空的
其中列名=表達(dá)式可以有多個(gè), 這樣可以方便進(jìn)行多行更新
4.4 事務(wù)
事務(wù): 需要在同一個(gè)處理單元中執(zhí)行的一系列更新處理的集合
創(chuàng)建事務(wù)
事務(wù)開始語句;DML語句;...... 事務(wù)結(jié)束語句;一般地, 事務(wù)開始語句分?jǐn)?shù)據(jù)庫, SQL server的是begin transaction, MySQL是start?transaction, Oracle沒有開始語句
因此一定要注意事務(wù)的開始起點(diǎn), 在標(biāo)準(zhǔn)SQL中已經(jīng)規(guī)定了一種悄悄處理事務(wù)的方法
結(jié)束語句一般是commit表示提交
一定要記得在事務(wù)結(jié)束之后仔細(xì)確認(rèn)
結(jié)束語句還可以是rollback, 表示取消事務(wù)包含的全部更新處理
一般的數(shù)據(jù)庫產(chǎn)品已經(jīng)不需要事務(wù)的開始語句了, 很多數(shù)據(jù)庫在執(zhí)行第一個(gè)SQL語句的時(shí)候就已經(jīng)悄悄開始了事務(wù)
一般有兩種情況
1) 每一條SQL語句都是一個(gè)事務(wù)(自動(dòng)提交模式): SQL Server, PostgreSQL, MySQL
2) 知道用戶執(zhí)行了commit或者rollback為止算作一個(gè)事務(wù): Oracle
因此在大多數(shù)情況下, 采用自動(dòng)提交模式的數(shù)據(jù)庫, 你一旦誤刪了某個(gè)表, 就再也找不回來了
酸性性質(zhì)
ACID性質(zhì), 這是所有DBMS都必須遵守的規(guī)則
A: 原子性, 事務(wù)在結(jié)束時(shí), 其中包含的更新處理要么全部執(zhí)行, 要么完全不執(zhí)行, 這樣就防止了中途停止的情況, 不會(huì)對業(yè)務(wù)造成不好的影響
C: 一致性(完整性), 事務(wù)中執(zhí)行的處理, 必須要滿足數(shù)據(jù)庫數(shù)據(jù)表設(shè)置好的約束, 如果不遵守約束, 就會(huì)報(bào)錯(cuò), 然后事務(wù)回滾
I: 隔離性, 保證事務(wù)時(shí)間互不干擾的特性, 該事務(wù)之間不會(huì)相互嵌套, 當(dāng)前事務(wù)如果沒有提交, 所做的修改是對別的事務(wù)是不可見的, 直到事務(wù)完成
D: 持久性(耐久性), 事務(wù)一旦結(jié)束, 數(shù)據(jù)庫會(huì)保證數(shù)據(jù)的狀態(tài)得以保存, 常見的保證持久性的方法是將執(zhí)行記錄保存到存儲(chǔ)介質(zhì)上, 也就是一般意義的保存日志
5 復(fù)雜查詢
5.1 視圖
視圖基本上與表差不多, 只是說表實(shí)際存儲(chǔ)數(shù)據(jù), 而視圖是虛表, 不會(huì)存儲(chǔ)實(shí)際的數(shù)據(jù)
一般的, 對表的修改更新等操作會(huì)實(shí)際的寫到存儲(chǔ)介質(zhì)上進(jìn)行保存, 但是對視圖的處理, 不會(huì)將數(shù)據(jù)保存到任何地方, 實(shí)際上視圖保存的就是select語句, 當(dāng)需要從視圖中讀取數(shù)據(jù)的時(shí)候, 就是根據(jù)這個(gè)select語句來創(chuàng)建一個(gè)臨時(shí)表
視圖的優(yōu)點(diǎn)
1) 無需保存數(shù)據(jù), 節(jié)省存儲(chǔ)空間
2) 可以將常用的select語句保存成視圖, 這樣就不會(huì)每次重寫查詢語句, 且會(huì)得到數(shù)據(jù)的 最新狀態(tài)
創(chuàng)建視圖
create view 視圖名稱 (視圖列名, 視圖列名, ...) as select語句 ;創(chuàng)建視圖的時(shí)候, 視圖名稱后面的名稱是可以省略的, 省略的時(shí)候默認(rèn)就是查詢出來的列名; 如果指定了列名, 就相當(dāng)于是重新命名的列名, 但是要注意此時(shí)的列名要和查詢得到的列相同數(shù)量
視圖的注意事項(xiàng):
1) 可以在視圖的基礎(chǔ)上再創(chuàng)建視圖, 也就是形成了多重視圖, 但是這樣會(huì)降低SQL的性能, 一般杜絕這樣的操作
2) 一般定義視圖的時(shí)候不使用order by, 因?yàn)閿?shù)據(jù)行是沒有順序的, 盡管很多數(shù)據(jù)庫支持這樣的操作
3) 可以對視圖進(jìn)行更新, 但是有條件限制, 一般視圖是無法進(jìn)行更新的, 因?yàn)橹T如你創(chuàng)建的視圖是基于聚合函數(shù)的, 聚合函數(shù)是針對一個(gè)分組的, 如果對視圖進(jìn)行添加數(shù)據(jù)了, 那么對應(yīng)這個(gè)數(shù)據(jù)反映到原表上是一組數(shù)據(jù), 數(shù)據(jù)庫是沒有辦法處理這一組數(shù)據(jù)僅僅只有一個(gè)聚合的結(jié)果
可以進(jìn)行更新的條件
1) select子句中沒有使用distinct
2) from后面就只有一張表
3) 沒有使用group by, 也沒有使用having
視圖的使用
一般對視圖的操作就是查詢操作, 可以將視圖名稱當(dāng)成一般的表使用就好了
視圖的刪除
drop view 視圖名稱;在刪除的時(shí)候, 如果有其他視圖是在當(dāng)前視圖的基礎(chǔ)上創(chuàng)建的, 那么這個(gè)視圖是無法被刪除的, 此時(shí)還要?jiǎng)h除需要加上級(jí)聯(lián)刪除
drop view 視圖名稱 cascade;5.2 子查詢
子查詢就是在select ..from的后面再加上select語句查詢結(jié)果, 并用as關(guān)鍵字命名, 但不是所有的數(shù)據(jù)庫都支持as, 直接寫名字就行
select .. from (select語句) as 名字 .. ;可以在子查詢中嵌套子查詢
標(biāo)量子查詢: 只返回一行一列數(shù)據(jù)的子查詢
標(biāo)量子查詢相當(dāng)于就是一個(gè)標(biāo)量值, 因此可以應(yīng)用到標(biāo)量值應(yīng)用的所有位置, selet后面, where后面, having后面等
使用標(biāo)量子查詢需要注意的是, 一定要注意查詢的結(jié)果是一行一列的
5.3 關(guān)聯(lián)子查詢
現(xiàn)在有個(gè)商品表
現(xiàn)在需要根據(jù)篩選出單價(jià)大于該分類的平均值的商品
如果僅僅使用子查詢, 得到的SQL是錯(cuò)誤的
SELECT * FROM sp WHERE sp_prise > (SELECT AVG( sp_prise) FROM sp GROUP BY sp_class );但是這樣會(huì)報(bào)錯(cuò), 因?yàn)樽硬樵兊闹挡晃ㄒ? 那如何將當(dāng)前商品和當(dāng)前商品類別的平均價(jià)格對應(yīng)上呢, 就是關(guān)聯(lián)子查詢
關(guān)聯(lián)子查詢就是在子查詢中, 關(guān)聯(lián)表的操作, 應(yīng)用關(guān)聯(lián)子查詢得到的SQL是這樣的
SELECT * FROM sp as s1 WHERE s1.sp_prise > (SELECT AVG( s2.sp_prise) FROM sp as s2WHERE s1.sp_class = s2.sp_classGROUP BY s2.sp_class );6 函數(shù), 謂詞和case表達(dá)式
6.1 函數(shù)
函數(shù): 給定一些輸入, 輸出特定的輸出
輸入值就是參數(shù)
輸出值就是返回值
函數(shù)分為
1) 算術(shù)函數(shù): abs(絕對值), mod(求余, 傳入兩個(gè)參數(shù)), round(四舍五入),?
2) 字符串函數(shù)
拼接:
-- 一般數(shù)據(jù)庫使用||拼接字符串 SELECT sp_name, sp_class, sp_name || sp_class as my_str FROM sp;-- SQL Server使用+拼接字符串 SELECT sp_name, sp_class, sp_name + sp_class as my_str FROM sp;-- MySQL使用concat()函數(shù)拼接字符串 SELECT sp_name, sp_class, concat(sp_name,sp_class) as my_str FROM sp;字符串長度: length(字符串) SQL Server使用的是len()
轉(zhuǎn)成小寫: lower()
轉(zhuǎn)成大寫: upper()
字符串替換: replace(字符串對象, 要替換的字符串, 替換成的字符串)
字符串截取: substring(字符串對象 from 起點(diǎn) for 截取的字符數(shù)) 例如: substring("ABCDE" from 2 for 3) 得到的字符串是"BCD"
3) 日期函數(shù)
當(dāng)前日期: current_date
當(dāng)前時(shí)間: current_time
當(dāng)前日期和時(shí)間點(diǎn): current_timestamp
截取日期元素: extract(日期元素 from 日期)
SELECT CURRENT_TIMESTAMP,EXTRACT(YEAR FROM CURRENT_TIMESTAMP),EXTRACT(MONTH FROM CURRENT_TIMESTAMP),EXTRACT(DAY FROM CURRENT_TIMESTAMP),EXTRACT(HOUR FROM CURRENT_TIMESTAMP),EXTRACT(MINUTE FROM CURRENT_TIMESTAMP),EXTRACT(SECOND FROM CURRENT_TIMESTAMP);4) 聚合函數(shù): sum, avg, max, min, count
5) 轉(zhuǎn)換函數(shù): 轉(zhuǎn)換函數(shù)是進(jìn)行類型的轉(zhuǎn)換或者值的轉(zhuǎn)換
類型轉(zhuǎn)換
cast(轉(zhuǎn)換前的值 as 想轉(zhuǎn)換的數(shù)據(jù)類型)
值轉(zhuǎn)換, 將NULL轉(zhuǎn)化為其他值
coalesce(數(shù)據(jù)1, 數(shù)據(jù)2, ...)
6.2 謂詞
常見的謂詞就是一些比較運(yùn)算符(= < >等)
具體來說就是, 返回值是真值(true/false/unknown)
常見的謂詞有l(wèi)ike betwween, is null, is not null, in, exists
(1) like
涉及到前方一致, 中間一致, 后方一致, 分別是: 字符串%, %字符串%, %字符串
%表示任意個(gè)字符, _表示任意一個(gè)字符
(2) between A and B 范圍查找
取值范圍為?[A, B]?
(3) is null, is not null
判斷是不是null不能用 值 = null 來判定, 這樣始終返回為false
判斷是否為null
(4) in
in/not in 可以用or來實(shí)現(xiàn), 但是使用in更加簡潔
(5) exist
判斷記錄是否存在某種條件, 返回值是false或者true
6.3 case表達(dá)式
case表達(dá)式主要完成條件分歧
具體格式為
case when 判斷表達(dá)式 then 表達(dá)式when 判斷表達(dá)式 then 表達(dá)式when 判斷表達(dá)式 then 表達(dá)式...else 表達(dá)式 end課件不管有多么復(fù)雜的case語句, 最終的結(jié)果始終是一個(gè)簡單的值
基于這樣的特點(diǎn), case語句也可以放在任何單值的位置
7 集合運(yùn)算
7.1 表的加減法
表的加減法是處理增減或者減少數(shù)據(jù)行
(1) 表的加法 union
union是對表進(jìn)行并集運(yùn)算, 條件是列必須相同(一般地, 列對應(yīng)是一樣的union才有意義), 得到的結(jié)果會(huì)去掉重復(fù)
注意:
列要相同
列的類型必須一致
可以使用任何select大但是order by只能在最后使用且使用一次, 也就是說只能對union之后的表進(jìn)行排序
如果要包含重復(fù)的行要使用union all
(2) 表的公共部分 intersect
intersect取得的是表的交集
(3) 表的減法 except
求的在A表中不在B表中的記錄
7.2 聯(lián)結(jié)
聯(lián)結(jié)的作用是增加列, 是在列的方向進(jìn)行一定的操作
(1) 內(nèi)聯(lián)結(jié) inner join
格式為
select 列 from 表A inner join 表B on 聯(lián)結(jié)條件 其他操作;得到的結(jié)果是同時(shí)滿足聯(lián)結(jié)條件的記錄, 不存在補(bǔ)記錄的現(xiàn)象
(2) 外聯(lián)結(jié) outer join
與內(nèi)聯(lián)結(jié)相比, 外聯(lián)結(jié)存在補(bǔ)齊的情況
(3) 交叉聯(lián)結(jié) cross join
得到的就是笛卡爾積?
?
轉(zhuǎn)載于:https://www.cnblogs.com/weihuchao/p/6958942.html
《新程序員》：云原生和全面數(shù)字化實(shí)踐50位技術(shù)專家共同創(chuàng)作，文字、視頻、音頻交互閱讀總結(jié)
- 上一篇： python第四十一天---作业:简单F
- 下一篇： npm API文档