MySQL云數(shù)據(jù)庫(kù)應(yīng)用與實(shí)踐 課件 ch05-基本表與視圖管理

第四章

數(shù)據(jù)庫(kù)的管理Database西北工業(yè)大學(xué)NorthwesternPolytechnicalUniversity數(shù)據(jù)庫(kù)目錄5.1

實(shí)戰(zhàn)目標(biāo)與準(zhǔn)備5.2MySQL的存儲(chǔ)引擎5.3

表的創(chuàng)建與管理5.4表的完整性約束5.5表的索引管理5.6表的安全性控制5.7視圖的創(chuàng)建與管理5.1實(shí)戰(zhàn)目標(biāo)與準(zhǔn)備實(shí)戰(zhàn)目標(biāo)本章目標(biāo)是掌握基于MySQL的云數(shù)據(jù)庫(kù)環(huán)境中基本表和視圖的創(chuàng)建與管理等的基本操作方法，具體以華為云數(shù)據(jù)庫(kù)MySQL為例展開介紹。熟練掌握數(shù)據(jù)庫(kù)和基本表的創(chuàng)建、管理方法。熟練掌握SQL語句設(shè)置完整性約束、安全性的方法。熟練掌握SQL語句進(jìn)行索引管理的方法。熟練掌握SQL語句創(chuàng)建、插入、修改和刪除視圖的方法5.2MySQL的存儲(chǔ)引擎插件式的存儲(chǔ)引擎5.3表的創(chuàng)建與管理5.3表的創(chuàng)建與管理SQL語句CREATE/DROPTABLEALTERTABLE語法格式：CREATETABLE<表名>(<列名><數(shù)據(jù)類型>[完整性約束]

[，<列名><數(shù)據(jù)類型>[完整性約束]]…

[,<表級(jí)完整性約束>]);

<表名>：所要定義的基本表的名字<列名>：組成該表的各個(gè)屬性（列）<列級(jí)完整性約束條件>：涉及相應(yīng)屬性列的完整性約束條件<表級(jí)完整性約束條件>：涉及一個(gè)或多個(gè)屬性列的完整性約束條件5.3表的創(chuàng)建與管理[例]建立一個(gè)“學(xué)生”表Student，它由學(xué)號(hào)Sno、姓名Sname、性別Sgender、出生日期Sbirthdate、所在系Smajor五個(gè)屬性構(gòu)成。其中學(xué)號(hào)不能為空，并且姓名取值也唯一。

CREATETABLEStudent(SnoCHAR(8)NOTNULL,SnameCHAR(20)unique,SgenderCHAR(6),Sbirthdate

Date,SmajorCHAR(40),

PRIMARYKEY(Sno))5.4表的完整性約束5.4表的完整性約束在CREATETABLE中用FOREIGNKEY短語定義哪些列為外碼用REFERENCES短語指明這些外碼參照哪些表的主碼［例］定義SC中的參照完整性

CREATETABLESC(SnoCHAR(8)NOTNULL，

CnoCHAR(5)NOTNULL，

GradeSMALLINT，

SemesterCHAR(5),TeachingclassCHAR(8),

PRIMARYKEY(Sno，Cno)，/*在表級(jí)定義實(shí)體完整性*/

FOREIGNKEY(Sno)REFERENCESStudent(Sno)，

/*在表級(jí)定義參照完整性*/

FOREIGNKEY(Cno)REFERENCESCourse(Cno)

/*在表級(jí)定義參照完整性*/);5.4表的完整性約束CONSTRAINT約束定義［例］建立學(xué)生登記表Student，要求學(xué)號(hào)在10000000~29999999之間，姓名不能取空值，出生在1980年以后，性別只能是“男”或“女”。

CREATETABLEStudent(SnoCHAR(8)

CONSTRAINTC1CHECK(SnoBETWEEN‘10000000’AND’29999999’)，

SnameCHAR(20)

CONSTRAINTC2NOTNULL,

SbirthdateDATE

CONSTRAINTC3CHECK(Sbirthdate>’1980-1-1’)，

SgenderCHAR(2)CONSTRAINTC4CHECK(SgenderIN('男'，'女'))，

CONSTRAINTStudentKeyPRIMARYKEY(Sno))；在Student表上建立了5個(gè)約束條件，包括主碼約束（命名為StudentKey）以及C1、C2、C3、C4四個(gè)列級(jí)約束。mysql中替換成CHECK(snameisNOTNULL),5.4表的完整性約束CONSTRAINT約束修改［例］修改表Student中的約束條件，要求學(xué)號(hào)改為在900000~999999之間，出生日期改為1985年之后?？梢韵葎h除原來的約束條件，再增加新的約束條件

ALTERTABLEStudent

DROPCONSTRAINTC1;ALTERTABLEStudentADDCONSTRAINTC1CHECK(SnoBETWEEN‘900000’AND‘999999’);

ALTERTABLEStudent

DROPCONSTRAINTC3;

ALTERTABLEStudentADDCONSTRAINTC3CHECK(Sbirthdate>‘1985-1-1’)；5.5表的索引管理無索引的查詢：順序依次遍歷表中的每行(全表掃描)索引：加速數(shù)據(jù)查詢的輔助數(shù)據(jù)結(jié)構(gòu)5.5表的索引管理索引表數(shù)據(jù)表音序查字法，部首查字法5.5表的索引管理索引的類型功能邏輯：

-主鍵索引(唯一且非空，每張表1個(gè)，聚簇索引)-普通索引

-唯一索引

-全文索引物理實(shí)現(xiàn)方式：

-聚簇索引

-非聚簇索引(二級(jí)索引或者輔助索引，需要回表)字段個(gè)數(shù)：

-單一索引

-聯(lián)合索引:最左原則，index:(a,b,c）,則支持a;a,b;

a,b,c查詢5.5表的索引管理5.5表的索引管理索引的定義CREATEINDEX

語法格式：例子：

CREATEINDEXidx_nameONs

(sname);5.5表的索引管理索引的刪除DROPINDEX

語法格式：DROPINDEX<索引名>ON<表名>[例]刪除Student表的Stusname索引。DROPINDEXStusnameONStudent

或者ALTERTABLEstudentdropindexStusname;SQL標(biāo)準(zhǔn)中沒有定義對(duì)索引的修改功能，而采用刪除后重新定義索引的方式實(shí)現(xiàn)。5.5表的索引管理Explain：查看查詢的執(zhí)行計(jì)劃（性能分析之必備!）若期望強(qiáng)制指定查詢時(shí)使用某個(gè)索引:selectsno,snamefromuserinfouseindex(idx_name)whereuser_id>005.5表的索引管理列名描述idselect查詢的序列號(hào)，表示查詢中執(zhí)行select子句或表的順序select_type查詢類型table表名partitions匹配的分區(qū)信息type針對(duì)單表的訪問方法possible_keys可能用到的索引key實(shí)際使用的索引列名描述key_len實(shí)際使用的索引長(zhǎng)度ref當(dāng)使用索引等值查詢時(shí)，與索引列進(jìn)行等值匹配的對(duì)象信息rows預(yù)估的需要讀取的記錄數(shù)filtered針對(duì)預(yù)估的需要讀取的記錄，經(jīng)過搜索條件過濾后剩余記錄條數(shù)的百分比Extra一些額外的信息5.5表的索引管理select_type：SIMPLEPRIMARYUNIONDEPENDENTUNIONUNIONRESULTSUBQUERYDEPENDENTSUBQUERYDERIVEDtype：systemconsteq_refreffulltextref_or_nullindex_mergeunique_subqueryindex_subqueryrangeindexall（全表掃描）通常按照?qǐng)?zhí)行速度：system>const>eq_ref>ref>range>index>all/doc/refman/8.0ut.html5.5表的索引管理創(chuàng)建一個(gè)使用memory存儲(chǔ)引擎的表（對(duì)比Hash索引與B樹索引）createtablememt1(id1intnotnull,c1intdefaultnull,c2intdefaultnull,primarykey(id1))engine=memorycreateindexindex_nameusinghash/btreeontable_name(...)查看索引：showkeysfromtablename;5.6表的安全性控制5.6表的安全性控制授權(quán)GRANT<權(quán)限>[，權(quán)限]…

[ON<對(duì)象類型><對(duì)象名>]TO<用戶>[，<用戶>]…

[WITHGRANTOPTION]1.DBA擁有數(shù)據(jù)庫(kù)操作的所有權(quán)限，他可以將權(quán)限賦予其他用戶。2.建立數(shù)據(jù)庫(kù)對(duì)象用戶稱為該對(duì)象的屬主(OWNER)，他擁有該對(duì)象的所有操作權(quán)限。3.接受權(quán)限的用戶可以是一個(gè)或多個(gè)具體用戶，也可以是全體用戶(PUBLIC)。4.WITHGRANTOPTION：決定是否有傳播權(quán)限的權(quán)利不允許循環(huán)傳播5.6表的安全性控制收回權(quán)限REVOKE語句的一般格式為：

REVOKE<權(quán)限>[,<權(quán)限>]...[ON<對(duì)象類型><對(duì)象名>]

FROM<用戶>[,<用戶>]...

[CASCADE|RESTRICT][例]把用戶U4修改學(xué)生學(xué)號(hào)的權(quán)限收回。

REVOKEUPDATE(Sno)ONTABLEStudentFROMU4；5.6表的安全性控制MySQL中的權(quán)限相關(guān)概念用戶名（user）：訪問數(shù)據(jù)庫(kù)用戶名角色（role）：用戶權(quán)限的組合（主體集合）相關(guān)SQL語句：createuseru1identifiedby'123';

createroler1;grantselectonstudent.*to'r1'@'%';grant'r1'@'%'to'u1'@'%';flushprivileges;查看所有用戶:selectuser,hostfrommysql.user;查看某個(gè)用戶權(quán)限：showgrantsfor'user1'@'localhost'1.MySQL設(shè)置權(quán)限后需要刷新：flushprivileges;2.ROLE需要激活后權(quán)限才生效：SETDEFAULTROLE命令或者SETglobalactivate_all_roles_on_login=ON5.6表的安全性控制在MySQL中的應(yīng)用：

例：對(duì)于表s：授予角色r2具有所有權(quán)限，u1具有查詢、插入和修改學(xué)生系別的權(quán)限，u1的查詢權(quán)限可以傳播。給角色r1授予對(duì)所有表的查詢權(quán)限 grantallonstor2;grantupdateonsto'u1'@'%'withgrantoption;grantupdateonsto'u1'@'localhost'withgrantoption;grantupdateonsto'u1'@'08'withgrantoption;grantinsert,update(sdept)onstou1;grantselecton*.*tor1;例：對(duì)于表s：收回用戶r2的所有權(quán)限，收回u1的查詢權(quán)限。

revokeallonsfromr2revokeselectonstudentfromu1@%5.7視圖的創(chuàng)建與管理5.7視圖的創(chuàng)建與管理視圖定義格式：CREATEVIEW<視圖名>[(<列名>[,<列