56數(shù)據(jù)庫1_DCL_DDL_constrait_view

?

目錄

SQL：... 1

DCL：... 2

DDL：... 2

PRIMARY KEY：... 6

INDEX：... 6

constraint：... 6

view：... 8

數(shù)據(jù)類型：... 10

?

?

?

按照數(shù)據(jù)結(jié)構(gòu)來組織、存儲、管理數(shù)據(jù)的倉庫；

?

分類：

按數(shù)據(jù)模型分：

網(wǎng)狀數(shù)據(jù)庫；

層次數(shù)據(jù)庫；

關(guān)系型數(shù)據(jù)庫；

?

oracle；

sqlserver；

DB2；

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

cassandra、hbase，column store；

mongodb，document store；

redis，key-value；

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

hive，relational DBMS，數(shù)據(jù)倉庫用于數(shù)據(jù)分析；

?

?

MySQL：

是一種關(guān)系型數(shù)據(jù)庫管理軟件，支持網(wǎng)絡(luò)訪問，默認3306port；

通信使用mysql協(xié)議；基于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;?? #觀察連接，與權(quán)限有關(guān)

?

?

?

SQL：

structured guery language，結(jié)構(gòu)化查詢語言；

1987年被ISO組織標準化；

所有主流的關(guān)系型數(shù)據(jù)庫都支持SQL，nosql也有很大一部分支持SQL；

?

SQL語言分為：

DDL，定義，負責數(shù)據(jù)庫定義、數(shù)據(jù)庫對象定義，create、alter、drop；

DML，操作，負責對數(shù)據(jù)庫對象的操作，CRUD；

DCL，控制，負責數(shù)據(jù)庫權(quán)限訪問控制，grant、revoke；

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

?

SQL語句大小寫不敏感，末尾應(yīng)用分號結(jié)束；

?

]# mysql -uroot -p < test.sql

?

?

DCL：

mysql> grant all on test.* to 'jowin'@'%' identified by 'jowin';?? #mysql.user中無此用戶，賦權(quán)并創(chuàng)建用戶，jowin/jowin

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

?

?

DDL：

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

?

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

?

drop database IF EXISTS gogs;

?

CREATE TABLE `employees` (

? `emp_no` int(11) NOT NULL,

? `birth_date` date NOT NULL,?? #date，特殊的數(shù)值

? `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;?? #表引擎可單獨設(shè)置

創(chuàng)建表：

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

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

反引號為標準的名稱，不是關(guān)鍵字；

?

建庫、建表時，用工具建模，可轉(zhuǎn)為建表語句；

如何數(shù)據(jù)庫建模，見CMDB項目中；

?

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

?

例：

設(shè)計一張表，記錄登錄賬戶；

應(yīng)存儲用戶的姓名、登錄名、密碼；

?

使用navicat mysql工具：

表-->新建表

注：

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

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

關(guān)注的重點，是字段設(shè)計符合業(yè)務(wù)需求；

至少2張表關(guān)聯(lián)，很少有獨立的表；

?

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)

?

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

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

新增新表，遷移數(shù)據(jù)過去；

?

?

PRIMARY KEY：

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

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

主鍵往往設(shè)計為整型、長整型、且自增AUTO_INCREMENT；

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

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

?

?

INDEX：

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

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

可對一列或多列設(shè)索引；

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

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

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

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

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

?

?

constraint：

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

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

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

?

注：

FOREIGN KEY：

在B中的列關(guān)聯(lián)A中的主鍵，表B中的列就是外鍵；

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

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

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

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

?

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

?

例：

建login表

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

CASCADE級聯(lián)，危險，若在主表中刪除了主鍵的記錄，其它與之關(guān)聯(lián)的表內(nèi)容也將刪除，能不刪就不刪；

NOACTION，無操作；

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

?

當刪除reg表中的主鍵（第2行記錄）時，會級聯(lián)刪除login表中相關(guān)的第2條記錄；

?

view：

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

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

CRUD用視圖要慎用；

作用：

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

數(shù)據(jù)安全，視圖可只顯示真實表的部分列，或計算的結(jié)果，隱藏真實表的數(shù)據(jù)；

?

例：

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

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

?

例：

employees表，emp_no為PK；

salaries表，emp_no和from_date聯(lián)合PK；

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

左側(cè)，test1庫，視圖，雙擊v_salary即可看到查詢結(jié)果；

新建查詢，select * from v_salary;

?

?

數(shù)據(jù)類型：

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

?

snallint，2字節(jié)，帶符號-32768-32767，無符號0-65535；

?

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

?

bigint，長整型8字節(jié)，用作id，int足夠；

?

float，單精度，精確到大約7位小數(shù)位；

double，雙精度，精確到大約15位小數(shù)位

?

DATE、DATETIME、TIMESTAMP；

?

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

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

?

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

BLOB，大字節(jié)，65535字節(jié)的BLOG;

?

注：

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

?

LENGTH函數(shù)返回字節(jié)數(shù)；

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

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

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

?

例：

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

?

模型：

UML，統(tǒng)一建模語言；

將模型轉(zhuǎn)化為DB的table，轉(zhuǎn)化為py的class；

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

?

?

?

?

?