SQL基础操作_8_基础概念
目錄
?
3 基礎概念
3.1 數(shù)據(jù)庫基礎概念
3.2 集合相關(guān)概念
3.3 常見數(shù)據(jù)庫對象
3.3.1 表
3.3.2 模式
3.3.3 視圖
3.3.4 觸發(fā)器
3.3.5 函數(shù)
3.3.6 存儲過程
3.4結(jié)構(gòu)化查詢語言
3.4.1 數(shù)據(jù)定義語言
3.4.2 數(shù)據(jù)操作語言
3.4.3 數(shù)據(jù)控制語言
3.4.4 事務控制語言
3 基礎概念
3.1 數(shù)據(jù)庫基礎概念
?
數(shù)據(jù)庫本質(zhì)上是一款面向數(shù)據(jù)管理軟件。我們也可以把它理解為存放數(shù)據(jù)的倉庫,這個倉庫是按照一定的存儲結(jié)構(gòu)來組織、存儲數(shù)據(jù)的。按照數(shù)據(jù)庫的數(shù)據(jù)組織形式一般分為三種:層次式數(shù)據(jù)庫、網(wǎng)絡式數(shù)據(jù)庫和關(guān)系型數(shù)據(jù)庫。一般數(shù)據(jù)庫指的都是關(guān)系型數(shù)據(jù)庫,即以簡單的二元關(guān)系(二維表格形式)形式組織數(shù)據(jù)的。而二元關(guān)系的基礎就是關(guān)系代數(shù),它是
基于集合,提供了一系列的關(guān)系代數(shù)操作:并、差、笛卡爾積、選擇、投影、交、連接和關(guān)系等擴展操作,是一種集合思維的操作語言。關(guān)系代數(shù)操作以一個或多個關(guān)系為輸入,結(jié)果是有一個新的關(guān)系。
?
3.2 集合相關(guān)概念
集合即SET,是個無序的元素的集。集合里常見概念有交、并、差。即假定有個集合叫A,它有元素 1,2,有個集合叫做B,它有元素叫做1,3.那么A和B的交為 1,即兩個集合的共有元素的集合。A和B的并,為1,2,3,即兩個集合的共有元素的集合1,2,3。A和B的差則為2,即在返回在A集合同時又不在B集合的元素的集合。
?
注:以下以mysql的語法演示了部分集合操作的特點。
?
笛卡爾積
笛卡爾積(Cartesian product,CROSS JOIN),數(shù)學里的表達式為A×B = {(x,y)|x∈A∧y∈B},即生成一個新的集合,它里的子元素都同時屬于A和B。
假設集合A={c,d},集合B={3,4,5},則兩個集合的笛卡爾積為{(c, 3), (c, 4), (c, 5), (d, 3), (d, 4), (d, 5)}。
?
?
左外連接
左外鏈接(LEFT OUTER JOIN又簡稱LEFT JOIN)即以左邊的表為主表來關(guān)聯(lián)右邊的表。它的特點是關(guān)聯(lián)出來的記錄數(shù)與前表一致如果匹配不到記錄,右邊的表以NULL填充。
CREATE TABLE tb_a(id int); INSERT INTO tb_a VALUES(1); INSERT INTO tb_a VALUES(2); INSERT INTO tb_a VALUES(3);CREATE TABLE tb_b(id int); INSERT INTO tb_b VALUES(2); INSERT INTO tb_b VALUES(3); INSERT INTO tb_b VALUES(4);SELECT A.id,B.id FROM tb_a A LEFT JOIN tb_b B ON A.id = B.id?
--結(jié)果
?
| id | id1 |
| 3 | 3 |
| 2 | 2 |
| 1 | ? |
?
?
右外連接
右外鏈接(RIGHT OUTER JOIN又簡稱RIGHT JOIN)即以右邊的表為主表來關(guān)聯(lián)右邊的表。它的特點是關(guān)聯(lián)出來的記錄數(shù)與后表一致如果匹配不到記錄,左邊的表以NULL填充。
?
| id | id1 |
| 2 | 2 |
| 3 | 3 |
| ? | 4 |
?
全連接
全連接(FULL OUTER JOIN)結(jié)合LEFT OUTER JOIN和RIGHT OUTER JOIN的特點,即前表匹配不到后表時后表補充為NULL,后表匹配不到前表是前表補充為NULL。
SELECT A.id,B.id FROM tb_a A LEFT JOIN tb_b B ON A.id = B.id UNION SELECT A.id,B.id FROM tb_a A RIGHT JOIN tb_b B ON A.id = B.id| id | id1 |
| 3 | 3 |
| 2 | 2 |
| 1 | ? |
| ? | 4 |
?
內(nèi)連接
內(nèi)連接(INNER JOIN)是兩個表按照定義的條件去匹配,僅顯示能匹配上的記錄。記錄數(shù)小于等于兩個表里記錄數(shù)最小的表的記錄。
| id | id1 |
| 2 | 2 |
| 3 | 3 |
合并
合并(UNION ALL)即兩個表里對應字段的數(shù)據(jù)整合在一起,這里不會剔除重復的數(shù)據(jù)。
SELECT id FROM tb_a UNION ALL SELECT id FROM tb_b| id |
| 1 |
| 2 |
| 3 |
| 2 |
| 3 |
| 4 |
去重合并
合并(UNION)即兩個表里對應字段的數(shù)據(jù)整合在一起,這里如果會剔除重復的數(shù)據(jù)。
SELECT id FROM tb_a UNION SELECT id FROM tb_b| id |
| 1 |
| 2 |
| 3 |
| 4 |
?
3.3 常見數(shù)據(jù)庫對象
3.3.1 表
表是存儲數(shù)據(jù)的邏輯單位,這里的表即是我們在EXCEL里看的表格,是個二維的表格,有行(row)和列(column)組成.
行是是表里的一個組成部分,即對表的橫向描述。一個表可能有成千上萬行,也可能有1行或者為空的行即只有表的定義而沒有一條記錄。同理列也可以這樣。
字段是表的基本單位是,這里又叫做屬性或者特征,即每一列的統(tǒng)一名字。如name,id_card對應的含義為名字、身份證號。
關(guān)系型數(shù)據(jù)是先模式語言,即需要先定義好表的模式,就像無規(guī)矩不成方圓一樣,需要先給每列的數(shù)據(jù)名稱、類型、每列的關(guān)系,誰是唯一標識、有沒有外鍵引用標識做好定義。下面我們來講講模式。
3.3.2 模式
模式(Schema)即數(shù)據(jù)庫對象的集合的統(tǒng)稱,一般包括表、視圖、索引、函數(shù)、存儲過程、同義詞等。一個Schema對應一個用戶,用戶相當于Schema的管理員,它負責創(chuàng)建、更新、刪除里面的對象。
3.3.3 視圖
視圖是對若干張按照一定業(yè)務含義關(guān)聯(lián)在一起的表(也可以是一張表)的引用,可以看做是一張?zhí)摂M的表。
視圖一般用作對底層數(shù)據(jù)的封裝,如果你不想用戶看到底層關(guān)聯(lián)的表或者用戶只是想訪問某些表的部分字段也可以創(chuàng)建個視圖。
-- 創(chuàng)建個視圖,以部門表和員工表關(guān)聯(lián),返回部門名和員工名
CREATE VIEW V_VIEW AS SELECT dname,ename FROM dept A JOIN emp B ON A.deptno = B.deptno3.3.4 觸發(fā)器
觸發(fā)器顧名思義即因為觸發(fā)而做的動作,這里主要應用到表這種數(shù)據(jù)對象上。常見的觸發(fā)器分別應用在INSERT、UPDATE、DELETE這三類場景下。
-- step1 創(chuàng)建個記錄插入的日志表 CREATE TABLE insertlog( id int(10) NOT NULL AUTO_INCREMENT, name varchar(100), primary key(id) );-- step2 創(chuàng)建觸發(fā)器,處罰行為基于INSERT CREATE OR REPLACE TRIGGER tri_insertdemo AFTER INSERT ON dept FOR EACH ROW INSERT INTO insertlog(name) SELECT dname from DEPT;-- step3 驗證觸發(fā)器效果 INSERT INTO dept()VALUES(100,'demo','demo'); SELECT* FROM insertlog;/* id? name 1?? ACCOUNTING 2?? RESEARCH 3?? SALES 4?? OPERATIONS 5?? demo */?
3.3.5 函數(shù)
函數(shù)是為了為了解決某個特定問題的程序代碼的封裝。一般分為入?yún)⒑统鰠?#xff0c;即傳入?yún)?shù)和輸出參數(shù)。函數(shù)按照用途可分為:
基本函數(shù)(length、replace、concat、abs、power、case、ifnull等)
聚合函數(shù)(max、min、sum、avg等)
分析函數(shù)(row_number、rank等)
自定義函數(shù)(自己定義的函數(shù)如:myFunc)
CREATE FUNCTION func_deptno(name VARCHAR(15)) RETURNS INT BEGINDECLARE r INT;SELECT DEPTNO FROM DEPT WHERE DNAME=name INTO r;RETURN r; END;SELECT func_deptno('SALES'); -- 返回部門編號為303.3.6 存儲過程
一組按照業(yè)務需求編寫的SQL指令集合,只經(jīng)過一次在數(shù)據(jù)庫里編譯后不需要再編譯。存儲過程按照用途可以分:系統(tǒng)存儲過程、自定義存儲過程。
--創(chuàng)建一個按照輸入的參數(shù)插入數(shù)據(jù)到dept的存儲過程CREATE procedure sp_insertdept(IN no int,IN name VARCHAR(10),IN loc VARCHAR(10)) BEGIN INSERT INTO dept VALUES(no,name,loc); END --調(diào)用存儲過程后會發(fā)現(xiàn)該記錄會被插入到dept表里。 call sp_insertdept(300,'demoinsert','where');?
3.4結(jié)構(gòu)化查詢語言
SQL,結(jié)構(gòu)化查詢語言,即對結(jié)構(gòu)化數(shù)據(jù)進行定義、操作、事務操作等類的語言統(tǒng)稱。一般包括DDL(數(shù)據(jù)定義語言)、DML(數(shù)據(jù)操作語言)、DCL(數(shù)據(jù)控制語言)、DTL(數(shù)據(jù)事務語言)
?
3.4.1 數(shù)據(jù)定義語言
數(shù)據(jù)定義語言,DDL用于定義、修改、刪除數(shù)據(jù)庫里的對象。(如表、索引、觸發(fā)器等)。常見的有
CREATE
-- 創(chuàng)建個部門表字段有部門編號、部門名稱、部門所在位置,并定義部門編號為主鍵。
create table dept(deptno int unsigned auto_increment COMMENT '部門編號',dname varchar(15) COMMENT '部門名稱',loc varchar(50)? COMMENT '部門所在位置', primary key(deptno) ) COMMENT='部門表';
ALTER
?
-- 修改部門表新增字段部門規(guī)模deptscale ALTER TABLE dept ADD COLUMN deptscale int comment '部門規(guī)模';
RENAME/MODIFY
DROP
?
TRUNCATE
TRUNCATE TABLE dept;3.4.2 數(shù)據(jù)操作語言
DML 數(shù)據(jù)操作語言,主要是數(shù)據(jù)庫里的對象進行增、刪、改、查操作。
?
INSERT
--往部門表里插入4條記錄。 INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO dept VALUES (20,'RESEARCH','DALLAS'); INSERT INTO dept VALUES (30,'SALES','CHICAGO'); INSERT INTO dept(deptno, dname,loc)values(40,'OPERATIONS','BOSTON')SELECT
-
--查詢部門表的部門編號的所有記錄 SELECT deptno from dept;UPDATE
--將部門表里的所有部門標號都更新為50 UPDATE dept SET deptno=50; --將部門編號為40的更新為50 UPDATE dept SET deptno=50 WHERE deptno=40;DELETE
--刪除部門表部門編號為50的記錄 DELETE FROM dept WHERE deptno=50; --刪除部門表所有記錄 DELETE FROM dept?
3.4.3 數(shù)據(jù)控制語言
dcl數(shù)據(jù)控制語言,這里主要是針對用權(quán)限的管理。常見關(guān)鍵字有g(shù)rant(賦權(quán))、revoke(收回).
?
grant
--賦予test用戶能訪問localhost(本地服務器)里的demodb里的對象。 grant select on demodb.* to test@'localhost'revoke
--收回用戶test在localhost里的所有的數(shù)據(jù)庫訪問權(quán)限 revoke all on *.* from test@localhost;?
3.4.4 事務控制語言
DTL是事務控制語言。什么是事務(Transaction),事務一個最小的不可再分的工作單元。現(xiàn)實生活中最容易聯(lián)想到到的例子就是銀行轉(zhuǎn)賬的這個場景。比如初始時A總共有1000塊,B總共有500塊。
在10:10時A轉(zhuǎn)賬給B ?500塊。
在10:20 時B 轉(zhuǎn)賬給A 200塊。
那么最終A卡里還有700塊,B用戶卡里又800塊,簡單說A用戶和B用戶的賬在整個事務里是對的上的,不存在A、B用戶的錢變的不合理的情況。兩個用戶的總額仍是1500.
這是轉(zhuǎn)賬正常的情況,也是事務所保障的情況。如果把初始狀態(tài)到10點20之前作為一個事務,如果用戶想回滾,那該怎么還原當時的情況呢?顯然我們要把500塊還給A,那就是A又是1000塊了,而B還是500塊。
如果第一個事務不回滾,比如在10點15時提交了事務,你們A用戶的余額是500,B的余額是1000.而且一旦在第二個時間點提交了,A、B的余額是在沒有轉(zhuǎn)賬行為的情況下是固定的了,不在變化了。所有事務一般有如下的特點:
原子性
原子性是指事務包含的所有操作要么全部成功,要么全部失敗回滾,這和前面兩篇博客介紹事務的功能是一樣的概念,因此事務的操作如果成功就必須要完全應用到數(shù)據(jù)庫,如果操作失敗則不能對數(shù)據(jù)庫有任何影響。
?
原子性(Atomicity):即事務的操作要么成功要么回滾,不會有其它的狀態(tài)。
一致性(Consistency);即事務之前和之后的都處于可核對的一致狀態(tài),比如總額相等等。
隔離性(Isolation):當一個事務在執(zhí)行時,其它的事務不能對它進行打斷,除非當前事務結(jié)束了才能開始新的事務。
持久性(Durability):即事務一旦提交了,數(shù)據(jù)都被保存為修改的狀態(tài),即使極端的情況,如正好掉電了或者數(shù)據(jù)庫掛了,數(shù)據(jù)也能在來電后或者數(shù)據(jù)庫起來之后恢復為事務提交后的值。
COMMIT
-- 插入一條記錄并提交后提交 INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'); COMMIT;ROLLBACK
-- 插入一條記錄并提交后回滾 INSERT INTO dept VALUES (20,'RESEARCH','DALLAS'); ROLLBACK;?
總結(jié)
以上是生活随笔為你收集整理的SQL基础操作_8_基础概念的全部內(nèi)容,希望文章能夠幫你解決所遇到的問題。