Chapter4：設(shè)計數(shù)據(jù)庫之內(nèi)部模式：SQL基本操作

筆記來源：
1.《漫畫數(shù)據(jù)庫》—科學出版社
2.SQL | DDL, DQL, DML, DCL and TCL Commands

設(shè)計數(shù)據(jù)庫的步驟：
概念模式

概念模式(conceptual schema)是指將現(xiàn)實世界模型化的階段進而，是確定數(shù)據(jù)庫理論結(jié)構(gòu)的階段。
概念模式的設(shè)計是通過E-R模型把握現(xiàn)實世界，進而規(guī)范化表格來實現(xiàn)的。

內(nèi)部模式

內(nèi)部模式是從計算機內(nèi)部看到的數(shù)據(jù)庫，是確定數(shù)據(jù)庫物理構(gòu)造的階段。
內(nèi)部模式的設(shè)計通過設(shè)計數(shù)據(jù)庫高速檢索方法來實現(xiàn)。

外部模式

外部模式是從用戶和應(yīng)用的角度來觀察的數(shù)據(jù)庫。
外部模式的設(shè)計是通過設(shè)計應(yīng)用程序所必要的數(shù)據(jù)來實現(xiàn)的。

我們使用SQL能干什么？

使用數(shù)據(jù)庫必須將數(shù)據(jù)錄入并調(diào)取必要數(shù)據(jù)，因此我們要學習SQL，我們使用該語言與數(shù)據(jù)庫進行對話

SQL命令分類
這些命令有些用于數(shù)據(jù)庫本身，有些數(shù)據(jù)庫中的對象，有些二者皆可（例如CREATE既可用于創(chuàng)建數(shù)據(jù)庫又可用于創(chuàng)建表）

數(shù)據(jù)定義語言（DDL：Data Definition Language）
CREATE：該命令用于創(chuàng)建數(shù)據(jù)庫或其對象(如表、索引、函數(shù)、視圖、存儲過程和觸發(fā)器)。
DROP：該命令用于刪除數(shù)據(jù)庫中的對象。
ALTER：用于修改數(shù)據(jù)庫的結(jié)構(gòu)。
TRUNCATE：用于從表中刪除所有記錄，包括為這些記錄分配的所有空間。
COMMENT：用于向數(shù)據(jù)字典中添加注釋。
RENAME：用于重命名數(shù)據(jù)庫中存在的對象。

數(shù)據(jù)查詢語言（DQL：Data Query Language）
SELECT：用于從數(shù)據(jù)庫中檢索數(shù)據(jù)。

數(shù)據(jù)操作語言（DML：Data Manipulation Language）
INSERT：用于向表中插入數(shù)據(jù)。
UPDATE：用于更新表中的現(xiàn)有數(shù)據(jù)。
DELETE：用于刪除數(shù)據(jù)庫表中的記錄。
LOCK：表控制并發(fā)性。
CALL：調(diào)用PL/SQL或JAVA子程序。
EXPLAIN PLAN：描述數(shù)據(jù)的訪問路徑。

數(shù)據(jù)控制語言（DCL：Data Control Language）
GRANT：這個命令賦予用戶訪問數(shù)據(jù)庫的權(quán)限。
REVOKE：該命令撤銷使用GRANT命令賦予用戶的訪問權(quán)限。

事務(wù)控制語言（TCL：Transaction Control Language）
BEGIN：打開一個事務(wù)。
COMMIT：提交事務(wù)。
ROLLBACK：在發(fā)生任何錯誤時回滾事務(wù)。
SAVEPOINT：在事務(wù)中設(shè)置保存點。

具體用法查詢?nèi)肟?#xff1a;SQL Tutorial

5.1 Create Database in SQL（關(guān)于數(shù)據(jù)庫）

關(guān)于數(shù)據(jù)庫的創(chuàng)建、選擇、重命名、刪除的操作

SQL CREATE DATABASE Statement（創(chuàng)建數(shù)據(jù)庫語句）

CREATE DATABASE database_name;

SQL Drop Database（刪除數(shù)據(jù)庫）

DROP DATABASE database_name;

SQL Rename Database（重命名數(shù)據(jù)庫）

Rename database

SQL Select database（選擇數(shù)據(jù)庫）

USE DATABASE_NAME;

5.2 Tables in SQL（關(guān)于表）

關(guān)于表的創(chuàng)建、刪除、重命名、清空的操作

SQL CREATE TABLE（創(chuàng)建表）

CREATE TABLE table_name

SQL Drop Table Statement（刪除表語句）

DROP TABLE table_name;

SQL DELETE Statement（刪除語句）

DELETE FROM table_name WHERE some_condition;
注：Some_condition: condition to choose a particular record.（記錄，即一行）

ALTER(RENAME) in SQL（重命名）

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
注：僅在MySQL, Oracle中適用

DROP and TRUNCATE in SQL（刪除和清空表）

DROP TABLE table_name;
TRUNCATE TABLE table_name;

SQL Query to Copy,Duplicate or Backup Table（復制備份表）
通過備份表來追蹤數(shù)據(jù)變換

選擇幾列來備份
CREATE TABLE Table_Name AS SELECT col_1, col_2, … FROM Source_Table_Name;
Table_Name: The name of the backup table.
AS: Aliasing
col: Required columns from source table

SQL ALTER TABLE-ADD,DROP,MODIFY（添加、刪除、修改表）

添加一個記錄（一行）
ALTER TABLE table_name ADD (Columnname_1 datatype,Columnname_2 datatype, …Columnname_n datatype);

5.3 SQL Queries（查詢）

SQL中的查詢語句用于從數(shù)據(jù)庫中檢索或獲取數(shù)據(jù)。

SQL SELECT Query（選擇查詢）
SOL-TOP,LIMIT,FETCH FIRST Clause（限制查詢返回的行數(shù)）
SQL-SELECT FIRST（只選擇第一行）
SQL-SELECT LAST（只選擇最后一行）
SQL-SELECT RANDOM（返回任意一行）
SQL-SELECT IN（返回與列表或子查詢（嵌套查詢）中的值匹配的值）
SQL-SELECT DATE（從與日期相關(guān)的表中獲取數(shù)據(jù)）
SQL Query to Insert Multiple Rows（插入多行）
SQL INSERT INTO Statement（插入）
SQL UPDATE Statement（更新）
SQL DELETE Statement（刪除）
SQL Query to Delete Duplicate Rows（刪除重復行）

5.4 SQL Clauses（條件）

SQL CLAUSE通過為 SQL 查詢提供條件來幫助限制結(jié)果集。 CLAUSE有助于從整個記錄集中過濾行。

SQL WHERE Clause（根據(jù)特定條件獲取數(shù)據(jù)）
SQL WITH clause（給子查詢塊一個名稱）
SQL HAVING Clause （基于聚合函數(shù)和分組的查詢結(jié)果過濾）
ORDER BY in SQL（對獲取的數(shù)據(jù)進行排序）
SQL GROUP BY（在一些函數(shù)的幫助下，將相同的數(shù)據(jù)分組）
SQL LIMIT Clause（若有大量元組滿足查詢條件，一次只查看少數(shù)元組）

5.5 SQL Operators（操作符）

提供算術(shù)運算符、比較運算符、邏輯運算符、位運算符

算術(shù)運算符可對表中數(shù)據(jù)進行運算
比較運算符可以判斷表中的哪些記錄是符合條件的。
邏輯運算符用來判斷表達式的真假。
位運算符是在二進制數(shù)上進行計算的運算符。位運算會先將操作數(shù)變成二進制數(shù)，進行位運算。然后再將計算結(jié)果從二進制數(shù)變回十進制數(shù)。

SQL IN Operator（基于特定值的列表篩選數(shù)據(jù)）
SQL NOT Operator（查詢時排除）
SQL NOT EQUAL Operator（檢查兩個表達式是否不同/不相等）
SQL IS NULL Operator（檢查列中缺省值）
SQL UNION Operator（查找結(jié)果集或兩個或多個表的組合）
SQL UNION ALL（合并兩個或多個SELECT語句的結(jié)果）
SQL Except Clause（第一個SELECT操作返回，而第二個SELECT操作不返回）
SQL Between（測試表達式是否在值范圍內(nèi)）
SQL ALL and ANY（選擇select語句的所有元組）
SQL INTERSECT Clause（只返回兩個SELECT語句共有的行）
SQL EXISTS（檢查相關(guān)嵌套查詢的結(jié)果是否為空(不包含元組)）
SQL CASE Statement（根據(jù)不同的條件有條件地賦值）

5.6 SQL Aggregate Functions（聚合函數(shù)）

聚合函數(shù)就是將一組數(shù)據(jù)進行統(tǒng)一計算

COUNT() 符合指定條件的行數(shù)
AVG() 數(shù)值列的平均值
SUM()返回所有或不同值的總和
MIN()返回所選列的最小值
MAX()返回所選列的最大值

5.7 SQL Data Constraints（數(shù)據(jù)約束）

數(shù)據(jù)約束是用來為表中的某一列或多列設(shè)置規(guī)則的，以確保數(shù)據(jù)的完整性和準確性。這些規(guī)則確保了數(shù)據(jù)的一致性、有效性和可靠性。如果嘗試插入、更新或刪除違反這些規(guī)則的數(shù)據(jù)，數(shù)據(jù)庫系統(tǒng)將拋出錯誤并拒絕執(zhí)行這些操作。

SQL NOT NULL Constraint（強制特定的值或記錄不應(yīng)該保留空值）
SQL UNIQUE Constraint（檢查子查詢的結(jié)果中是否有重復的元組）
Primary key constraint in SQL（唯一標識表中的每條記錄）
Foreign Key constraint in SQL（防止會破壞表間鏈接的操作）
Composite Key in SQL（多個主鍵稱為組合鍵）
SQL-ALTERNATE KEY（候選鍵）
SQL CHECK Constraint（限制了列在關(guān)系中可以包含的值）
SQL DEFAULT Constraint（用默認值和固定值填充列）

5.8 SQL Joining Data（連接數(shù)據(jù)）

將兩個或多個表連接到一個表中

以相同意義的列為媒介，連接成表格，稱作同等連接(equi join)
我們稱將重復的列整合成一列的連接叫做自然連接(natural join)

僅選擇數(shù)值相同的行進行連接，我們稱之為內(nèi)部連接(inner join)
全部保留任何一方表格的所有行，將一方中沒有的行設(shè)置為空值的連接方法稱為外部連接(outer join)
兩個表格在SQL命令中分左右時，根據(jù)保留全部表格行的情況，分為左外部連接(left outer join)和右外部連接(right outer join)

內(nèi)部連接：兩張表取交集	外部連接：表A與表B的并集
右外部連接：表B+表A與B共有部分	左外部連接：表A+表A與B共有部分

SQL Outer Join（左外部連接+右外部連接+全外部連接）
SQL Left Join（左外部連接）
SQL Right Join（右外部連接）
SQL FULL JOIN（全外部連接）

SQL CROSS JOIN（笛卡爾連接）

SQL Self Join（兩張表結(jié)構(gòu)和數(shù)據(jù)內(nèi)容完全一樣的表，在做數(shù)據(jù)處理的時候，我們通常會給它們分別重命名來加以區(qū)分，然后進行關(guān)聯(lián)）
SQL UPDATE with JOIN（使用另一個表和連接條件更新一個表）
SQL DELETE JOIN（允許基于涉及另一個表的條件刪除表的行）
Recursive Join in SQL（允許我們查詢分層數(shù)據(jù)）

5.9 SQL Functions（函數(shù)）

函數(shù)提供許多常用功能

SQL Date functions（NOW() 返回當前日期和時間 ）
SQL String functions（對輸入字符串執(zhí)行操作并返回輸出字符串）
SQL Numeric Functions（對數(shù)字進行操作并返回數(shù)字）
SQL-Statistical Functions（對數(shù)字數(shù)據(jù)進行統(tǒng)計計算）
Working With JSON in SQL（存儲和傳輸數(shù)據(jù)）
Conversion Function in SQL（當您定義表達式和局部變量時，您應(yīng)該指定將在這些對象中存儲什么類型的數(shù)據(jù)）
SQL Data Types（數(shù)據(jù)類型）
SQL LTRIM()Function（一個字符串函數(shù)，用于從字符串或字符串表達式的左側(cè)刪除前導空格）
SQL RTRIM()Function（從字符或字符串右側(cè)刪除空格的字符串函數(shù)）
SQL UPPER()Function（將表中的所有字符串轉(zhuǎn)換為大寫的字符串函數(shù)）

5.10 SQL Views（視圖）

當用戶對數(shù)據(jù)庫中的一張或者多張表的某些字段的組合感興趣，而又不想每次鍵入這些查詢時，用戶就可以定義一個視圖，以便解決這個問題。視圖中列可以來自于表里的不同列，這些列都是用戶所感興趣的數(shù)據(jù)列

視圖與表不同，它在物理上不是真實存在的，而是一個虛表。
在數(shù)據(jù)庫里僅存放視圖的定義，而不存放視圖對應(yīng)的數(shù)據(jù)。
視圖中的這些數(shù)據(jù)存放在其對應(yīng)的表中，如果表中的數(shù)據(jù)發(fā)生了變化，從視圖中查詢出的數(shù)據(jù)也會隨之發(fā)生改變。從這個意義來看，視圖就像一個窗口，透過它可以看到數(shù)據(jù)庫中用戶感興趣的數(shù)據(jù)及變化?！?#xff1a;數(shù)據(jù)庫中的視圖有什么用，我們?yōu)槭裁匆靡晥D？

CREATE VIEW view_name AS
SELECT column1, column2…
FROM table_name
WHERE condition;
備注：view_name: Name for the View
table_name: Name of the table
condition: Condition to select rows

5.11 SQL Indexes（索引）

索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個組成部分)，它們包含著對數(shù)據(jù)表里所有記錄的引用指針。
索引是一種數(shù)據(jù)結(jié)構(gòu)。數(shù)據(jù)庫索引，是數(shù)據(jù)庫管理系統(tǒng)中一個排序的數(shù)據(jù)結(jié)構(gòu)，以協(xié)助快速查詢、更新數(shù)據(jù)庫表中數(shù)據(jù)。索引的實現(xiàn)通常使用B樹及其變種B+樹。
更通俗的說，索引就相當于目錄。為了方便查找書中的內(nèi)容，通過對內(nèi)容建立索引形成目錄。索引是一個文件，它是要占據(jù)物理空間的?！?#xff1a;sql數(shù)據(jù)庫中什么情況該加索引index？

SQL CREATE INDEX Statement（創(chuàng)建索引）
SQL DROP INDEX Statement（刪除索引）
Difference Between Clustered and Non-Clustered Index（聚集索引根據(jù)數(shù)據(jù)行的鍵值在表或視圖中排序和存儲這些數(shù)據(jù)行。 這些鍵值是索引定義中包含的列。 每個表只能有一個聚集索引，因為數(shù)據(jù)行本身只能按一個順序存儲。 只有當表包含聚集索引時，表中的數(shù)據(jù)行才按排序順序存儲。 如果表具有聚集索引，則該表稱為聚集表。 如果表沒有聚集索引，則其數(shù)據(jù)行存儲在一個稱為堆的無序結(jié)構(gòu)中）