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)容，希望文章能夠幫你解決所遇到的問題。
