SQL數(shù)據(jù)庫標(biāo)準(zhǔn)語言, since 2021-12-11

(2021.12.11 Sat)
關(guān)系型數(shù)據(jù)庫中路星，一個表格稱為關(guān)系，每一行稱為一個記錄切蟋，每個列是一個屬性秸妥。SQL中大小寫不敏感(case-insensitive)滚停。

定義語句

建立表語句

CREATE TABLE table_name (field_name1 type [NOT NULL [UNIQUE]], field_name2 type [NOT NULL [UNIQUE]], ...);

說明：

  1. SQL列的數(shù)據(jù)類型包括
    • CHAR(n): 長度為n的字符串型
    • DECIMAL[(m[, n])]: 共m位(不包含小數(shù)點)且有n位小數(shù)的數(shù)。當(dāng)m和n省略時粥惧，其含義由系統(tǒng)自定義键畴。該類型為精確數(shù)值類型，還可寫成DEC(m[, n])或NUMERIC(m[, n])
    • INTEGER或INT: 四字節(jié)或二字節(jié)整數(shù)
    • SMALLINT: 二字節(jié)整數(shù)
    • FLOAT(n): n位有效數(shù)字的實型數(shù)
    • REAL: 單精度實型數(shù)
    • DOUBLE PRECISION: 雙精度實型數(shù)
CREATE TABEL student (sid CHAR(2) NOT NULL UNIQUE, name CHAR(10), age SMALLINT, gender CHAR(2));

從其他表復(fù)制創(chuàng)建

CREATE TABLE table2 LIKE table1;
INSERT INTO table2 SELECT * FROM table1;

擴充表(添加列)

ALTER TABLE <table_name> ADD (field_name1 type [NOT NULL], field_name2 ...);

ALTER TABLE sudent ADD (birthplace CHAR(10)); //添加列birthplace
ALTER TABLE stu_score_bk ADD (sid INT AUTO_INCREMENT, PRIMARY KEY (sid)); 
// 添加一列sid突雪，并將其設(shè)置為自加和primary key

修改表

ALTER TABLE <table_name> MODIFY (field_name1 type [NOT NULL], field_name2 type ...);

該語句可以修改表中列的類型和長度起惕，但不能修改列名本身。若一個列要修改數(shù)據(jù)類型咏删，則該數(shù)據(jù)必須全為空值惹想。

刪表

DROP TABLE <table_name>;

增刪改UPDATE/DELETE/INSERT

增刪改操作是對數(shù)據(jù)的基本操作，屬于數(shù)據(jù)操作語言(Data Manipulation Language)督函。不同于前面的創(chuàng)建勺馆、添加、刪表等操作侨核，這些被成為數(shù)據(jù)定義語言(Data Definition Language)草穆，其實現(xiàn)了對數(shù)據(jù)庫結(jié)構(gòu)和操作的定義。DDL指令在執(zhí)行之后會隱式執(zhí)行commit操作搓译，即對數(shù)據(jù)庫做了對應(yīng)的動作悲柱。而DML語言并不隱式含有commit操作，需要手動執(zhí)行commit指令才能實現(xiàn)對數(shù)據(jù)的修改些己。在執(zhí)行DML操作后豌鸡，手動加入commit指令。

START TRANSACTION;
...
INSERT INTO Student (name,department) values ("xiaoming","computer science");
COMMIT;

刪除記錄

DELETE FROM student_table where name = 'xiaoming';

此外段标，執(zhí)行操作后是否需要commit也需要考慮數(shù)據(jù)引擎涯冠。對于不支持事務(wù)的引擎，如myisam逼庞，commit指令無效蛇更。而支持事務(wù)的引擎，如innodb，支持自動提交commit派任。

查看數(shù)據(jù)庫是否支持commit砸逊，可用如下指令查看，如果返回結(jié)果為on掌逛，則支持查看师逸。

mysql > show variables like '%autocommit%';

約束

(2021.12.15 Wed)

  • DEFAULT: 用于向列中插入默認(rèn)值。如果沒有其他的規(guī)定豆混，默認(rèn)值會被添加到所有新記錄篓像。在創(chuàng)建表和修改表時都可以使用。
CREATE TABLE some_table
(
PID INT NOT NULL,
...
city VARCHAR(255) DEFAULT 'HONGKONG',
OrderDate DATE DEFAULT GETDATE()
)

ALTER TABLE some_table
ALTER city SET DEFAULT 'London';

撤銷DEFAULT約束

ALTER TABLE some_table
ALTER city DROP DEFAULT

CHECK約束
用于限制列中的值的范圍皿伺。如果對單個列定義 CHECK 約束遗淳，那么該列只允許特定的值。如果對一個表定義 CHECK 約束心傀，那么此約束會在特定的列中對值進行限制屈暗。

CREATE TABLE Persons
(
Id_P int NOT NULL,
...
City varchar(255),
CHECK (Id_P>0)
)

如果命名 CHECK 約束，以及為多個列定義 CHECK 約束脂男，采用如下方法

CREATE TABLE Persons
(
Id_P int NOT NULL,
City varchar(255),
CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
)

在修改表的過程中

ALTER TABLE Persons
ADD CHECK (Id_P>0)

如果命名 CHECK 約束养叛，以及為多個列定義 CHECK 約束，采用如下方法

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')

撤銷約束

ALTER TABLE Persons
DROP CHECK chk_Person

自增auto_increment
每次插入新紀(jì)錄時宰翅，自動的創(chuàng)建主鍵字段的值弃甥。
下面這個例子中，創(chuàng)建表Persons汁讼，設(shè)置字段PId為自增字段淆攻，將其設(shè)置為主鍵。

CREATE TABLE Persons
(
PId int NOT NULL AUTO_INCREMENT,
...
City varchar(255),
PRIMARY KEY (PId)
)

默認(rèn)的auto_increment字段的初值為1嘿架，每條新記錄自增加1.
要讓auto_increment字段的初始值從其他值開始瓶珊，需要使用如下指令

ALTER TABLE Persons AUTO_INCREMENT=99;

向這個表中添加數(shù)據(jù)時，不需要指定PId的值耸彪，自動加1.

INSERT INTO Persons (FirstName,LastName)
VALUES ('Bill','Gates')

其他SQL服務(wù)器的命令和MySQL不同伞芹。

SQL簡單查詢 select...from...where

(2021.12.12 Sun)
基本格式如下

SELECT * FROM table_name

該格式選擇表中所有屬性返回。對表屬性做投影蝉娜，可直接寫出所選屬性

SELECT field_name1, field_name2, ... FROM table_name

選擇的屬性可以重命名唱较，用AS關(guān)鍵字，或不用關(guān)鍵字召川，只用空格隔離南缓；選擇的屬性可以重新賦值；可重新定義屬性的值

SELECT field_name1 AS field_name_edited,
field_name2 field_name_edited_2,
field_name3 * 0.5 field_name_edited_3,
'hrb' AS field_name_new
FROM table_name

關(guān)系運算符
除了常見的加減乘除等荧呐，還有對字符的連接操作||汉形，如'foo' || 'bar'的運算結(jié)果是'foobar'纸镊。對字符串的比較操作，實際上是比較字符串在字典中的順序(假想字符串出現(xiàn)在字典中)获雕。哪個字符串在前薄腻，哪個字符串的值比較小收捣。比如'fodder' < 'foo'届案，'bar'<'bargin'。

另外罢艾，需要注意的是楣颠，盡管SQL語言本身大小寫不敏感，但是涉及字符串的操作咐蚯，或?qū)ψ址牟僮魍觯谴笮懨舾械摹?/p>

模式匹配

s LIKE p

其中的p表示模式，該指令用于匹配(字符串)屬性s與模式p春锋。模式中的%表示匹配任意多個(含0)字符矫膨，_表示任意一個字符。

SELECT title FROM movies
WHERE title LIKE 'Star _ _ _ _';

該指令返回的可能結(jié)果是Star Trek和Star Wars期奔。

符號%可匹配0或任意多個字符侧馅。

SELECT title
FROM Movies 
WHERE title LIKE '% ' ' s%';

SQL約定，字符串中兩個連續(xù)的單引號表示一個單引號呐萌，而不作為字符串的結(jié)束符馁痴。該代碼表示是查詢名字中含有's的所有電影。

日期和時間
日期由DATE關(guān)鍵字加上表示日期的特定形式字符串組成

DATE'YYYY-MM-DD'

DATE'1960-01-01'

時間和時間戳類似于日期肺孤，其關(guān)鍵字分別是TIME和TIMESTAMP罗晕。

TIME'15:00:00.5'
TIMESTAMP'2021-12-12 09:46:35'

空值和涉及空值的比較
空值NULL有幾種常見的解釋

  • value unknown未知值，知道其有值但不知道其值
  • value inapplicable不適用的值赠堵，任何值在這里都沒有意義小渊，如MovieStar關(guān)系中，如果該演員單身茫叭，則spouse屬性為空粤铭，因為沒配偶
  • value withheld保留的值，屬于某對象但無權(quán)知道的值杂靶，比如未公布的電話號碼屬于空值

空值運算的規(guī)則

  1. 對NULL與任何值(包括一個空值)進行算術(shù)運算梆惯，如加減乘除，其結(jié)果仍然是空值NULL
  2. 對空值做比較運算吗垮，包括與另一個NULL值做比較垛吗，結(jié)果都是UNKNOWN值，而UNKNOWN值時另外一個與TRUE和FALSE相同的布爾值

注意烁登，NULL可出現(xiàn)在元組中怯屉，但它不是一個常量蔚舀，因此可對空值表達式進行運算，但不可以直接將NULL作為一個操作數(shù)锨络。

正確判斷x的值時否為NULL的方式

x IS NULL

關(guān)于UNKNOWN有下面關(guān)系

NOT UNKNOWN --> UNKNOWN
TRUE AND UNKNOWN --> UNKNOWN
FALSE AND UNKNOWN --> FALSE
TRUE OR UNKNOWN --> TRUE
FALSE OR UNKNOWN --> UNKNOWN

排序ORDER
排序指令ORDER BY對輸出結(jié)果進行排序赌躺，默認(rèn)是升序ASC，也可進行降序DESC排列羡儿。除了對某個屬性的值按升序或降序排列礼患，也可以對多個屬性值的運算做排序。

SELECT * 
FROM Movies
WHERE studioName = 'Disney' AND year = 1990
ORDER BY A+B DESC;

多關(guān)系查詢

SQL用簡單的方式在一個查詢中處理多個關(guān)系：在FROM子句中列出每個關(guān)系掠归，用逗號隔開缅叠，在SELECT子句和WHERE子句中引用任何出現(xiàn)在FROM子句中關(guān)系的屬性。

為避免引用屬性歧義虏冻，可以在FROM子句中給關(guān)系賦縮略名肤粱，或者在調(diào)用關(guān)系中的屬性時直接加入關(guān)系的名字。

SELECT a.name, b.length
FROM Movies a, MovieExec b
WHERE a.name = b.name;

子查詢subquery

在SQL中厨相，一個查詢可以通過不同的方式被用來計算另一個查詢领曼，當(dāng)某個查詢是另一個查詢的一部分是，稱之為子查詢subquery蛮穿。

子查詢可以返回單個常量庶骄，這個常量能再WHERE子句中和另一個常量做比較。子查詢能返回關(guān)系绪撵，該關(guān)系可以在WHERE子句中以不同的方式使用瓢姻。子查詢形成的關(guān)系也能出現(xiàn)在FROM子句中，并且后面緊跟該關(guān)系的元組變量音诈。

SELECT name
FROM MovieExec
WHERE cert IN
           (SELECT producerC
            FROM Movies
            WHERE (title, yer) IN
                      (SELECT movieTitle, movieYear
                       FROM StarsIn
                       WHERE starName = 'Harrison Ford'
                      )
           );

該查詢返回Harrison Ford演過的電影的制片人幻碱。

SELECT name
FROM MovieExec, (SELECT producerC,
                                 FROM Movies, StarsIn
                                 WHERE title = movieTitle AND 
                                       year = movieYear AND
                                       starName = 'Harrison Ford'
                                 ) Prod
WHERE cert  = Prod.producerC;

使用FROM子句子查詢找出Ford出演的電影的制片人琴许。

子查詢也可產(chǎn)生標(biāo)量鸭丛。

SELECT name
FROM MovieExec
WHERE cert = 
           (SELECT producerC
            FROM Movies
            WHERE title = 'Star Wars'
           );

此外，也可使用LEFT/RIGHT/INNER/OUTER JOIN的命令實現(xiàn)關(guān)系的連接宋雏。(略)

與子查詢經(jīng)常一同出現(xiàn)的謂詞包括IN, ALL, ANY, EXISTS喇聊。

SELECT snum, sscore
FROM SCORE
WHERE course_num = 'c2' AND sscore > ALL
                                        (SELECT sscore
                                         FROM SCORE
                                         WHERE course_num = 'c5');

該查詢返回c2成績高于c5最高成績的學(xué)生號和成績恍风。如果其中的關(guān)鍵詞換成ANY，則返回c2成績高于c5最低成績的學(xué)生號和成績誓篱。

函數(shù)查詢和聚集

常見的函數(shù)查詢包括

  • AVG(field_name)： 求指定數(shù)值列秩的算數(shù)平均值
  • COUNT(field_name): 求指定列中朋贬，值的個數(shù)
  • MAX(field_name)/MIN: 值的最大或最小值
  • SUM(field_name): 值的總和

注意，COUNT(*)用于查詢有多少條記錄窜骄，其前不能使用DISTINCT關(guān)鍵字锦募。而如果求某屬性有多少個不重合的值，需使用COUNT(DISTINCT field_name)邻遏。如果只是使用指令COUNT(field_name)糠亩，則返回A屬性非空的元組個數(shù)之和虐骑。AVG(field_name+5)表示對field_name屬性的值都加5再求均值。在使用SUM(field_name)時赎线，如果field_name屬性中含有NULL值廷没，則返回的和應(yīng)為NULL。

GROUP分組和HAVING條件
分組操作往往與函數(shù)查詢聯(lián)合使用垂寥。

SELECT A, COUNT(B)
FROM R
GROUP BY A;

HAVING子句有這兩個規(guī)則

  • HAVING子句中的聚集只應(yīng)用到正在檢測的分組上
  • 所有FROM子句中關(guān)系的屬性都可以在HAVING子句中用聚集運算颠黎，但是只有出現(xiàn)在GROUP BY子句中的屬性，才可能以不聚集的方式出現(xiàn)在HAVING子句中矫废。

(2022.07.22 Fri)
GROUP BY命令有另外兩個OLAP修飾符聯(lián)合使用盏缤，ROLLUP和CUBE砰蠢。這兩個修飾符的作用如下

  • ROLLUP：對分組后各組的聚合結(jié)果進行匯總蓖扑，在結(jié)果中輸出，提供更高一級的聚合操作台舱。即如果聚合函數(shù)是AVG律杠，則加了WITH ROLLUP提示符后，額外輸出一行竞惋，記錄所有分組的AVG柜去。這個操作相當(dāng)于做分組操作的結(jié)果與對全體數(shù)據(jù)做相同操作的結(jié)果的UNION ALL。比如求一個班級里面各科目的平均分拆宛，并在此基礎(chǔ)上計算所有科目的平均分
SELECT AVG(score) as avgscore, subject FROM score_table
GROUP BY subject WITH ROLLUP;
// or
SELECT AVG(score) AS avgscore, subject FROM score_table
GROUP BY subject 
UNION ALL
SELECT 'total', AVG(score) AS avgscore FROM score_table
  • CUBE：生成的結(jié)果集顯示了所選列中值的所有組合的聚合嗓奢，而ROLLUP生成的結(jié)果集顯示了所選列中值的某一層次結(jié)構(gòu)的聚合。CUBE案例：在MySQL 8.0上暫時運行失敗浑厚，placeholder股耽。

其他函數(shù)

(2021.12.15 Wed)

  • FIRST/LAST(field_name): 返回該列的第一個/最后一個元素，該命令僅適用于MS Access钳幅。
SELECT FIRST(field_name) FROM table;
SELECT LAST(field_name) FROM table;

在MySQL中物蝙，返回該列的第一個/最后一個元素用如下方式

SELECT field_name
FROM table
ORDER BY field_name ASC/DESC (?)
LIMIT 1;

(2022.07.31 Sun)
注意這里的LIMIT是一個分頁函數(shù)，常規(guī)的用法是

LIMIT <starting_line>, <row_counter>;

該指令用于讀取特定行(starting_line)開始的row_counter行數(shù)據(jù)敢艰。如果不指定開始行如LIMIT 20诬乞，則從0行開始讀取row_counter行數(shù)據(jù)，即前20行數(shù)據(jù)钠导。

分頁函數(shù)的另一種語法是

LIMIT <row_counter> OFFSET <starting_line>;

  • UCASE/LCASE(field_name): 把所選字段的值都改為大寫/小寫震嫉。在SQL server中是UPPER/LOWER。
SELECT UCASE(city_name) AS city_name_upper FROM city_table;
  • MID(field_name, start[, length])/SUBSTR(field_name, start[, length]): 從文本字段中提取字符牡属。其中的start只能從1開始票堵，length表示提取的長度，不標(biāo)記則為返回剩余文本湃望。MID和SUBSTR的動作完全相同换衬。
SELECT MID(company_name, 1, 3) AS shorten_name FROM table;

如果table表中的這個字段第一個數(shù)據(jù)是Google痰驱，則返回Goo。

  • LEN(field_name): 返回文本字段中值的長度瞳浦。MySQL中該指令為LENGTH(field_name)担映。
SELECT LENGTH(company_name) FROM table;

該字段中Google返回6.

  • ROUND(field_name[, digit]): 把數(shù)值字段舍入為指定位數(shù)的數(shù)字，默認(rèn)的digit=0叫潦，即沒有小數(shù)部分蝇完。返回值轉(zhuǎn)換為BIGINT型。
SELECT ROUND(field_name, 0) FROM table;
  • NOW(): 返回系統(tǒng)當(dāng)前時間矗蕊，格式為YYYY-MM-YY HH:MM:SS 
SELECT city_name, NOW() AS current_time FROM table;

返回Harbin, 2021-12-15 16:53:30

  • FORMAT(field_name, format): 對字段做格式轉(zhuǎn)換短蜕。多用于時間格式轉(zhuǎn)換。
SELECT FORMAT(NOW(), 'YYYY-MM-DD') AS nowtime, product_id FROM table;

一個用到局部變量的例子傻咖，在SQL server中朋魔，來自microsoft docs頁面。

DECLARE @d DATE = GETDATE();  
SELECT 
FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS 'Date',
FORMAT(123456789,'###-##-####') AS 'Custom Number';
  • CAST(field_name AS type_name(para)): 對字段類型做轉(zhuǎn)換卿操。注意CAST沒有舍入截斷操作警检，將小數(shù)轉(zhuǎn)換為整數(shù)會產(chǎn)生錯誤。
SELECT CAST(number_field AS INT) AS number_field_int FROM table;
SELECT CAST(number_field AS decimal(9, 2)) AS decimal_field FROM table; # 表示最大位數(shù)9害淤，2位小數(shù)扇雕，即9999999.00
SELECT CAST(some_id AS CHAR(4)) as id_char FROM table;

UNION操作符

(2021.12.16 Thur)
MySQL的UNION操作符用于將連接兩個以上的SELECT語句的結(jié)果組合到一個語句中】悖可根據(jù)關(guān)鍵字ALL/DISTINCT來決定是選擇全部結(jié)果還是刪除重復(fù)結(jié)果镶奉。

SELECT field1, field2, field3 
FROM table1
UNION 
SELECT field1, field2, field3
FROM table2

上面表達中，UNION表示默認(rèn)情況崭放，即刪除重復(fù)結(jié)果哨苛，等效于UNION DISTINCT。如果想保留所有結(jié)果莹菱，含重復(fù)結(jié)果移国，使用UNION ALL指令。
(2022.07.15 Fri)
此外道伟，UNION命令默認(rèn)對結(jié)果排序迹缀，而UNION ALL不排序。從速度來看蜜徽，顯然UNION ALL更快祝懂。

視圖view

視圖是SQL中除了關(guān)系以外另一種基本數(shù)據(jù)結(jié)構(gòu)。視圖是數(shù)據(jù)庫中滿足一定約束條件的數(shù)據(jù)組成的表拘鞋，但其本身不占用實際存儲器砚蓬。視圖可看做是一個虛表。從用戶角度看盆色，關(guān)系和視圖沒有任何區(qū)別灰蛙。

視圖可用作某個用戶的專用數(shù)據(jù)部分祟剔，便于使用，又提高了數(shù)據(jù)的獨立性摩梧，也可以把表中數(shù)據(jù)隱藏起來物延，加強數(shù)據(jù)的保密性。

定義視圖

CREATE VIEW <view_name> [(view_list)]
AS SELECT ...
[WITH CHECK OPTION];

結(jié)尾的WITH CHECK OPTION語句仅父，如果省略叛薯，則視圖是一個只讀表，不限制插入到該視圖的數(shù)據(jù)值笙纤；否則是一個可更新表耗溜，插入到該視圖的數(shù)據(jù)值必須滿足在該視圖定義時SELECT語句指定的條件。
下面例子生成一個平均成績視圖省容。

CREATE VIEW ave_score (snum, name, gender, avg_s) 
          AS SELECT s.snum, s.name, s.gender, avg(sc.score)
          FROM student s, score sc
          WHERE s.snum = sc.snum
          GROUP BY snum;

視圖的查詢指令與關(guān)系相同抖拴。

視圖的更新指令與基本表相同。但是一個視圖要進行更新操作蓉冈，應(yīng)滿足下面基本條件

  • 視圖只由一個基本表導(dǎo)出
  • SELECT語句中不含有GROUP子句城舞、組函數(shù)和DISTINCT
  • 視圖中的列不是由表達式定義的
  • 基本表中全部帶有NOT NULL的列都屬于該視圖

刪除視圖

DROP VIEW view_name

存儲過程PROCEDURE

(2021.12.16 Thur)

SQL數(shù)據(jù)控制功能

控制功能指的是控制用戶對數(shù)據(jù)的存儲權(quán)力轩触。
授權(quán)語句

GRANT grant_list ON table_name TO user_name/PUBLIC [WITH GRANT OPTION]

其中的grant_list是權(quán)力表寞酿，如果授予所有權(quán)利，則用ALL代替grant_list.
權(quán)力表包括

  • ALTER 修改表結(jié)構(gòu)
  • SELECT 查詢權(quán)
  • DELETE 刪除記錄
  • INSERT 插入新紀(jì)錄權(quán)
  • UPDATE 對指定列或全體列的值修改的權(quán)利

而ALL指代上面所有的權(quán)利脱柱。而如果所授予的不是數(shù)據(jù)表而是視圖伐弹，則只能授予SELECT, DELETE, INSERT, UPDATE.

用戶名可以是個別用戶，也可以是全部用戶榨为，比如PUBLIC惨好。

如果出現(xiàn)WITH GRANT OPTION，則得到授權(quán)的用戶可以向其他人授權(quán)随闺。

取消授權(quán)

REVOKE ALL/grant_list ON table_name FROM user_name/public

SQL事務(wù)Transaction

目前日川，數(shù)據(jù)庫上的操作模型是用戶查詢或更新數(shù)據(jù)庫。數(shù)據(jù)庫上的操作一次只執(zhí)行一個矩乐，一個操作留下的數(shù)據(jù)正是下一個操作所要起作用的龄句。操作中軟硬件都不會出錯，不會留下操作的結(jié)果不能解釋的數(shù)據(jù)庫狀態(tài)散罕。

可串行化

銀行行業(yè)或機票預(yù)定中分歇，往往出現(xiàn)多個人同時進行同一個業(yè)務(wù)，也就是同時修改數(shù)據(jù)庫的情況欧漱。比如訂機票职抡，兩個人同時看到有一個空位，并且(幾乎)同時執(zhí)行訂票操作误甚，到底誰頂?shù)狡蹦兀?/p>

為避免上面說的情況引起數(shù)據(jù)庫系統(tǒng)的錯誤和崩潰缚甩，引入了串行化概念谱净。SQL允許程序員規(guī)定一個特定的事務(wù)必須對于別的事物可以串行化(serializabel)，即這些事物必須表現(xiàn)得好像他們是串行的(serially)執(zhí)行擅威，也就是一個時刻只有一個事物岳遥，相互之間沒有重疊。

DBMS中一個普通的方式是鎖定(lock)數(shù)據(jù)庫的元素防止被兩個函數(shù)同時訪問裕寨。

前面訂票的案例浩蓉，如果兩個用戶對訂票的操作是串行執(zhí)行的，那么就不會出現(xiàn)前面提到的尷尬情況宾袜。

原子性atomic

考慮一個例子捻艳，從A賬戶到B賬戶轉(zhuǎn)賬100盧比。其中包含兩個動作庆猫，一個是A賬戶減少100盧比认轨，第二個是B賬戶增加100盧比。

在執(zhí)行轉(zhuǎn)賬的過程月培，一旦硬件或網(wǎng)絡(luò)出現(xiàn)問題嘁字，會無法完成這兩個看似分開，但實則應(yīng)為一體的動作杉畜。

該案例說明數(shù)據(jù)庫的操作的某些組合需要原子地atomically執(zhí)行纪蜒，即他們要么都執(zhí)行要么都不執(zhí)行。一個簡單的解決方案是將對數(shù)據(jù)庫的所有修改都在一個本地工作區(qū)中執(zhí)行此叠，而且只有在所有工作都完成后才能修改提交(commit)到數(shù)據(jù)庫纯续，于是所有改變成為數(shù)據(jù)庫的一部分，對其他操作可見灭袁。

事務(wù)Transaction

對可串行化和原子性問題的解決方案將把數(shù)據(jù)庫操作分組為事務(wù)猬错。事務(wù)是必須原子地執(zhí)行一個或多個數(shù)據(jù)庫操作的集合，即要么所有操作都執(zhí)行要么所有操作都不執(zhí)行茸歧。SQL要求默認(rèn)事務(wù)以可串行化方式執(zhí)行倦炒。

在SQL中，每條語句自身就是一個事務(wù)软瞎。不過SQL允許程序員將幾條語句組成一個事務(wù)逢唤。用命令START TRANSACTION來標(biāo)記事務(wù)的開始。有兩種方式結(jié)束事務(wù)：

  1. SQL的COMMIT語句使得事務(wù)成功結(jié)束铜涉，即被提交了智玻。在COMMIT被執(zhí)行之前，改變是試探性的芙代，對其他事務(wù)可不可見均有可能吊奢。
  2. SQL的ROLLBACK語句使得事務(wù)夭折abort或不成功結(jié)束。任何由該事務(wù)的SQL語句所引起的修改都被撤銷，即被回滾rolled back页滚。所以他們不會持久的出現(xiàn)在數(shù)據(jù)庫中召边。

只讀事務(wù)read-only

如果告知SQL執(zhí)行系統(tǒng)當(dāng)前的事務(wù)是只讀事務(wù)，即它不會修改數(shù)據(jù)庫裹驰，那么SQL系統(tǒng)很可能能夠充分利用這一點隧熙。通常，多個訪問同一個數(shù)據(jù)的只讀事務(wù)可以并行執(zhí)行幻林，但是多個寫同一個數(shù)據(jù)的事務(wù)并不能并行執(zhí)行贞盯。
告知SQL系統(tǒng)下一個事務(wù)是只讀事務(wù)的語句是

SET TRANSACTION READ ONLY;

這條語句必須在事務(wù)開始前執(zhí)行。
可以通過如下語句通知SQL下一個事務(wù)可以寫數(shù)據(jù)

SET TRANSATION READ WRITE;

不過這個選項是默認(rèn)選項沪饺。

讀臟數(shù)據(jù)dirty data(待補充)

臟數(shù)據(jù)表示還沒有提交的事務(wù)所寫的數(shù)據(jù)的通用術(shù)語躏敢。藏獨dirty read是對臟數(shù)據(jù)的讀取。讀臟數(shù)據(jù)的風(fēng)險是寫數(shù)據(jù)的事務(wù)可能最終夭折整葡。

有時可能需要冒險偶爾臟讀一次件余，從而避免

  1. DBMS用啦防止臟讀所作的耗時的工作
  2. 為了等到不可能出現(xiàn)臟讀而造成的并發(fā)性的損失

使用SET TRANSACTION語句，SQL允許指定一個給定的事務(wù)是否可以臟讀遭居，比如

SET TRANSACTION READ WRITE
        ISOLATION LEVEL READ UNCOMMITED;

這個語句做了兩件事啼器，一是聲明可以寫數(shù)據(jù)，二是聲明事務(wù)用讀未提交read-uncommited的隔離層次進行俱萍。即允許事務(wù)讀臟數(shù)據(jù)端壳。

隔離層次

共四種，可串行化鼠次、讀未提交(允許臟讀)更哄、讀提交(read-commited)和可重復(fù)讀(repeatable-read)。
他們可以通過如下語句指定

SET TRANSATION ISOLATION LEVEL READ COMMITED;
SET TRANSATION ISOLATION LEVEL REPEATABLE READ;

對每條語句腥寇，默認(rèn)事務(wù)是讀寫的，所以在適當(dāng)?shù)那闆r下觅捆，可在每條語句后面加上READ ONLY赦役。有一個指定選項

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

但這是SQL的默認(rèn)情況，不必顯式指定栅炒。

Reference

1 匙彥斌等主編掂摔，計算機軟件技術(shù)基礎(chǔ)教程，天津大學(xué)出版社
2 Jeffery U.等著赢赊，岳麗華等譯乙漓，數(shù)據(jù)庫系統(tǒng)基礎(chǔ)教程，機械工業(yè)出版社

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末释移，一起剝皮案震驚了整個濱河市叭披，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌玩讳，老刑警劉巖涩蜘，帶你破解...
    沈念sama閱讀 218,386評論 6贊 506
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件嚼贡，死亡現(xiàn)場離奇詭異，居然都是意外死亡同诫，警方通過查閱死者的電腦和手機粤策，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 93,142評論 3贊 394
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來误窖，“玉大人叮盘，你說我怎么就攤上這事∨常” “怎么了熊户？”我有些...
    開封第一講書人閱讀 164,704評論 0贊 353
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵，是天一觀的道長吭服。 經(jīng)常有香客問我嚷堡，道長，這世上最難降的妖魔是什么艇棕？ 我笑而不...
    開封第一講書人閱讀 58,702評論 1贊 294
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任蝌戒，我火速辦了婚禮，結(jié)果婚禮上沼琉，老公的妹妹穿的比我還像新娘北苟。我一直安慰自己，他們只是感情好打瘪，可當(dāng)我...
    茶點故事閱讀 67,716評論 6贊 392
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布友鼻。 她就那樣靜靜地躺著，像睡著了一般闺骚。 火紅的嫁衣襯著肌膚如雪彩扔。 梳的紋絲不亂的頭發(fā)上，一...
    開封第一講書人閱讀 51,573評論 1贊 305
  • 城市分裂傳說
    那天僻爽，我揣著相機與錄音虫碉，去河邊找鬼。 笑死胸梆，一個胖子當(dāng)著我的面吹牛敦捧，可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播碰镜，決...
    沈念sama閱讀 40,314評論 3贊 418
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼兢卵，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了绪颖？” 一聲冷哼從身側(cè)響起秽荤，我...
    開封第一講書人閱讀 39,230評論 0贊 276
  • 萬榮殺人案實錄
    序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎，沒想到半個月后王滤，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體贺嫂，經(jīng)...
    沈念sama閱讀 45,680評論 1贊 314
  • ?護林員之死
    正文 獨居荒郊野嶺守林人離奇死亡，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 37,873評論 3贊 336
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年雁乡，在試婚紗的時候發(fā)現(xiàn)自己被綠了第喳。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點故事閱讀 39,991評論 1贊 348
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡踱稍，死狀恐怖曲饱，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情珠月，我是刑警寧澤扩淀，帶...
    沈念sama閱讀 35,706評論 5贊 346
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布，位于F島的核電站啤挎，受9級特大地震影響驻谆，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜庆聘，卻給世界環(huán)境...
    茶點故事閱讀 41,329評論 3贊 330
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一胜臊、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧伙判，春花似錦象对、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,910評論 0贊 22
  • 一樁弒父案勒魔，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至菇曲，卻和暖如春冠绢，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背羊娃。 一陣腳步聲響...
    開封第一講書人閱讀 33,038評論 1贊 270
  • 情欲美人皮
    我被黑心中介騙來泰國打工唐全， 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留，地道東北人蕊玷。 一個月前我還...
    沈念sama閱讀 48,158評論 3贊 370
  • 代替公主和親
    正文 我出身青樓，卻偏偏與公主長得像弥雹，于是被迫代替她去往敵國和親垃帅。 傳聞我的和親對象是個殘疾皇子，可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 44,941評論 2贊 355

推薦閱讀更多精彩內(nèi)容