56數據庫1_DCL_DDL_constrait_view

?

目錄

SQL：... 1

DCL：... 2

DDL：... 2

PRIMARY KEY：... 6

INDEX：... 6

constraint：... 6

view：... 8

數據類型：... 10

?

?

?

按照數據結構來組織、存儲、管理數據的倉庫；

?

分類：

按數據模型分：

網狀數據庫；

層次數據庫；

關系型數據庫；

?

oracle；

sqlserver；

DB2；

sqlite，c++，手機中的存儲；

cassandra、hbase，column store；

mongodb，document store；

redis，key-value；

elasticsearch（大型分布式）、solr（單機玩）、splunk，search engine；

hive，relational DBMS，數據倉庫用于數據分析；

?

?

MySQL：

是一種關系型數據庫管理軟件，支持網絡訪問，默認3306port；

通信使用mysql協議；基于TCP；

?

MySQL5.0，里程碑;

5.5起默認的SE為InnoDB，行級鎖；

?

連接字符串：

"server=127.0.0.1;uid=root;pwd=123456;database=test"

?

row，行，record，元組；

column，列，field；

?

mysql> show processlist;?? #觀察連接，與權限有關

?

?

?

SQL：

structured guery language，結構化查詢語言；

1987年被ISO組織標準化；

所有主流的關系型數據庫都支持SQL，nosql也有很大一部分支持SQL；

?

SQL語言分為：

DDL，定義，負責數據庫定義、數據庫對象定義，create、alter、drop；

DML，操作，負責對數據庫對象的操作，CRUD；

DCL，控制，負責數據庫權限訪問控制，grant、revoke；

TCL，事務控制語言，負責處理ACID事務，commit、rollback；

?

SQL語句大小寫不敏感，末尾應用分號結束；

?

]# mysql -uroot -p < test.sql

?

?

DCL：

mysql> grant all on test.* to 'jowin'@'%' identified by 'jowin';?? #mysql.user中無此用戶，賦權并創建用戶，jowin/jowin

mysql> revoke all on test.* from 'jowin';

?

?

DDL：

mysql> drop user 'jowin';?? #刪除用戶，慎用；開發設計中，一般是在邏輯上刪除（假刪）

?

CREATE DATABASE IF NOT EXISTS gogs CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;?? #CHARACTER SET指定字符集，表中字符集可與庫中不一樣，表中不寫字符集則默認用庫中定義的；utf8mb4，5.5.3以上版本支持，utf8的擴展，支持4byte utf8；COLLATE，指定字符集排序規則，用來作字符串比較的

?

drop database IF EXISTS gogs;

?

CREATE TABLE `employees` (

? `emp_no` int(11) NOT NULL,

? `birth_date` date NOT NULL,?? #date，特殊的數值

? `first_name` varchar(14) NOT NULL,

? `last_name` varchar(16) NOT NULL,

? `gender` enum('M','F') NOT NULL,

? `hire_date` date NOT NULL,

? PRIMARY KEY (`emp_no`)?? #約束，另UNIQUE KEY ' 'ln' ('emp_no')

) ENGINE=InnoDB DEFAULT CHARSET=utf8;?? #表引擎可單獨設置

創建表：

定義field、定義約束、定義SE，storage engine存儲引擎；

NOT NULL，插入記錄后，不允許字符為空；

反引號為標準的名稱，不是關鍵字；

?

建庫、建表時，用工具建模，可轉為建表語句；

如何數據庫建模，見CMDB項目中；

?

mysql> desc employees;?? #查看列信息

?

例：

設計一張表，記錄登錄賬戶；

應存儲用戶的姓名、登錄名、密碼；

?

使用navicat mysql工具：

表-->新建表

注：

password，單身加密，不可逆，雙向加密很少用，建議128，散列越長越好，單身散列值；

id，點“主鍵”按鈕，可上移到第1行，少用聯合主鍵；

關注的重點，是字段設計符合業務需求；

至少2張表關聯，很少有獨立的表；

?

id，下方勾選，自動遞增和無符號；

id，選項，自動遞增：1；

?

登錄名不允許重復：

點“索引”，名：ln，欄位：loginname，索引類型：Unique，索引方法：BTREE；

唯一鍵約束；

?

左邊，右鍵"reg"表-->對象信息-->DDL，可查看建表語句：

?

mysql> desc reg;

+-----------+------------------+------+-----+---------+----------------+

| Field???? | Type???????????? | Null | Key | Default | Extra????????? |

+-----------+------------------+------+-----+---------+----------------+

| id??????? | int(11) unsigned | NO?? | PRI | NULL??? | auto_increment |

| loginname | varchar(48)????? | NO?? | UNI | NULL??? |??????????????? |

| name????? | varchar(64)????? | YES ?|???? | NULL??? |??????????????? |

| password? | varchar(128)???? | NO?? |???? | NULL??? |??????????????? |

+-----------+------------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

?

如果表里已有數據，再添字段，事就大了；若幾十條記錄還很快，若幾百萬行記錄，一晚上估計都好不了，解決：

設計時提前預留2個字段，空間換時間，如reversed1 varchar,reversed2 varchar；

新增新表，遷移數據過去；

?

?

PRIMARY KEY：

表中一列或多列（多列少做），組成唯一的key，即通過這一個或者多個列能唯一的標識一條記錄；

主鍵的列，不能包含空值NULL；

主鍵往往設計為整型、長整型、且自增AUTO_INCREMENT；

表中可以沒有主鍵，但一般設計表中都會有主鍵；

字符串作為主鍵沒整型效率高；

?

?

INDEX：

可看作是大字典的目錄，為了快速檢索用；

用空間換時間，顯著提高查詢效率；

可對一列或多列設索引；

主鍵索引，主鍵會自動建立主鍵索引，主鍵本身就是為了快速定位唯一記錄的；

唯一索引，表中的索引列組成的索引必須唯一，但可為NULL空，非空值必須唯一；

普通索引，沒有唯一性要求，就是建了一個字典的目錄而已；

索引有副作用，增、刪、改都會效率低下，可能系統慢的原因就是索引；

適用于r多w少，不適用于w多r少，OS大量IO在等待寫；

?

?

constraint：

UNIQUE唯一鍵約束，定義了唯一鍵索引就定義了唯一鍵約束；

PRIMARY KEY主鍵約束，定義了主鍵，就定義了主鍵約束；

FOREIGN KEY外鍵約束，為保證數據完整性、一致性、杜絕數據冗余、數據訛誤，能少用就少用，不宜過多濫用；

?

注：

FOREIGN KEY：

在B中的列關聯A中的主鍵，表B中的列就是外鍵；

如果在表B插入一條數據，B的外鍵列插入了一個值，這個值必須是表A中存在的主鍵值；

修改表B的外鍵值也是這樣，外鍵值同樣要在表A中存在；

如果表A要刪除一條記錄，那么就等于刪除這個主鍵，如果表B中引用到了這個主鍵，就必須先刪除表B中引用的這個主鍵的記錄，然后才能刪除表A的記錄，否則刪除失效；

修改表A的主鍵，由于主鍵的唯一性，修改的主鍵相當于插入新主鍵，那么表B引用過這個主鍵，就阻止表A的主鍵修改，必須刪除表B的相關記錄后，才可修改表A的主鍵；

?

set foreign_key_checks=0;?? #禁用外鍵約束

?

例：

建login表

在login表上建外鍵，fk_login_reg，udi，test，reg，id，刪除時CASCADE級聯；

CASCADE級聯，危險，若在主表中刪除了主鍵的記錄，其它與之關聯的表內容也將刪除，能不刪就不刪；

NOACTION，無操作；

login表的uid必須要和reg表的id對應，如類型、長度、無符號等，否則創建不成功；

?

當刪除reg表中的主鍵（第2行記錄）時，會級聯刪除login表中相關的第2條記錄；

?

view：

視圖，也稱虛表，看起來像表，它是由查詢語句生成的，可通過視圖進行CRUD操作；

最好用視圖作查詢，select語句過濾形成視圖，select * from VIEW_NAME；

CRUD用視圖要慎用；

作用：

簡化操作，將復雜查詢語句定義為視圖，可簡化查詢；

數據安全，視圖可只顯示真實表的部分列，或計算的結果，隱藏真實表的數據；

?

例：

工資表，只查當月工資，其它列不允許看；

登錄，只看登錄的用戶名，其它列不允許看；

?

例：

employees表，emp_no為PK；

salaries表，emp_no和from_date聯合PK；

視圖，新建視圖，視圖創建工具，將指定表托入右側空白處，勾選要顯示的字段，下方可修改語句，鍵入視圖名v_salary；

左側，test1庫，視圖，雙擊v_salary即可看到查詢結果；

新建查詢，select * from v_salary;

?

?

數據類型：

tinyint，1字節，bool或boolean就是tinyint，0假，非0真（包括負數），帶符號范圍-128-127，無符號范圍0-255；

?

snallint，2字節，帶符號-32768-32767，無符號0-65535；

?

int，整型，4字節，帶符號-2147483648-2147483647，無符號0-4294967295-42億；

?

bigint，長整型8字節，用作id，int足夠；

?

float，單精度，精確到大約7位小數位；

double，雙精度，精確到大約15位小數位

?

DATE、DATETIME、TIMESTAMP；

?

char(M)，固定長度，M為長度即字符個數，范圍0-255；

varchar(M)，變長字符串，M為最大列長度，不能突破65535最大字節數；

?

text，大文本，最大長度65535個字符；

BLOB，大字節，65535字節的BLOG;

?

注：

char(M)、varchar(M)、text、BLOB，設計表中講；

?

LENGTH函數返回字節數；

而char()、varchar()中的M是字符數限制；

char()可將字符串變成等長的，空間換時間，效率略高；

varchar()，變長，省了空間，存后參差不齊，當表中已有幾百萬行記錄，這時要對前面某長記錄作修改，恰好引起了長度的變化，會產生大量IO，修改的行之后的記錄都要挪動，將導致表不對外工作；

?

例：

在連接上，右鍵“運行SQL文件”，選擇文件，開始

?

模型：

UML，統一建模語言；

將模型轉化為DB的table，轉化為py的class；

建模工具：powerdesigner、rose（IBM）；

?

?

?

?

?