《數據庫原理及應用》數據庫虛擬教研室制作數據庫目錄01

SQL概述02存儲引擎03字符集與字符編碼04MySQL數據庫管理05MySQL數據類型06MySQL數據表管理07數據表的數據操作第4章數據庫及表基本操作SQL概述SQL：StructuredQueryLanguage，結構化查詢語言。其功能包括數據查詢、定義、操縱和控制等方面。SQL有SQL89、SQL92和SQL99多個版本，并向下兼容。注意：SQL是關系數據庫的標準語言，在語法上，不同的數據庫產品略有不同，MySQL的語法與其他工具差別較大。SQL組成數據庫存儲引擎：是數據庫底層軟件組織，是對數據庫文件的一種存取機制，為存儲的數據建立索引以及如何實現更新、查詢數據等技術的方法。存儲引擎為什么要使用不同的存儲引擎：現實生活中，由于不同業(yè)務產生了不同的數據，這些數據有的可能偏重于被頻繁查詢，有的可能偏重于修改速度，有的則對事務、索引、外鍵有特殊規(guī)定。這樣一來，就對存儲數據要求使用不同的數據組織結構，使用不同的存儲引擎可以將數據以不同的結構進行存儲。查看MySQL支持的存儲引擎：

showengines存儲引擎存儲引擎的類型服務器是否支持該類型引擎注釋事務分布式交易處理的XA規(guī)范事務保存點通過系統(tǒng)變量default-storage-engine來查看存儲引擎，查看方式：showvariableslike‘default-storage-engine’；存儲引擎MySQL存儲引擎可在定義表的時候設置，也可以通過修改表的方式來設置。服務器在運行期間可修改數據庫服務器默認存儲引擎方式：setdefault-storage-engine=存儲引擎名；數據庫服務在啟動時會讀取配置文件my.ini，配置文件中有默認存儲引擎的設置。(C:\ProgramData\MySQL\MySQL

Server

8.0\my.ini)[mysqld]default-storage-engine=innodb字符集（Characterset）：是字符的集合，是一套符號以及編碼規(guī)則，以二進制形式存儲。常用的字符集如下表：字符集與字符編碼字符編碼：將字符變成字節(jié)的過程。在符號集合與數字系統(tǒng)之間建立對應關系。與字符集相對應，不同的字符集有不同的字符編碼。解碼：將二進制字節(jié)轉換成對應字符的過程。字符集與字符編碼轉碼utf8gbk查看當前MSQL支持的字符集及其默認的排序方式：showcharset;/showcharacterset;使用系統(tǒng)變量查看當前MySQL使用的字符集:showvariableslike'character%';字符集與字符編碼數據庫服務在啟動時會讀取配置文件my.ini，配置文件中有默認字符集的設置。[client]default-character-set=utf8[mysql]default-character-set=utf8[mysqld]character-set-server=utf8字符序：是指在同一字符集內字符之間的比較規(guī)則。字符集與字符編碼如何比較兩個英文字符的大小？最簡單的方式是比較兩個字符二進制編碼的大小，如ASCII碼字符集中的’A’的編號為65，’a’的編碼為97。如何比較兩個漢字字符的大小？UTF8中文采用康熙字典中定義的偏旁索引順序排序,GBK中文采用新華字典中定義的字母順序排序。二都可以轉碼。例：將student表的sname字段由utf8字符集轉為gbk排序。SELECT*FROMstudentORDERBYCONVERT(snameUSINGgbk);創(chuàng)建數據庫創(chuàng)建數據庫的語法格式CREATEDATABASE|SCHEMA[IFNOTEXISTS]db_name[[DEFAULT]CHARACTERSETcharset_name][[DEFAULT]COLLATEcollation_name];MySQL數據庫管理創(chuàng)建數據庫命令早期數據庫稱謂待創(chuàng)建數據庫名已經存在時，給出錯誤信息待創(chuàng)建數據庫名設置默認字符集設置字符集默認校對規(guī)則在創(chuàng)建數據庫時省略了字符集和校對規(guī)則的設置，MySQL將采用默認字符集和默認校對規(guī)則，默認字符集是在哪里設置的？【例4-1】創(chuàng)建名稱為teaching_manage的數據庫，字符集和排序規(guī)則為采用系統(tǒng)默認。CREATEDATABASEteaching_manage;MySQL數據庫管理說明：創(chuàng)建數據庫后，會在存儲數據文件夾data下生成一個與數據庫同名的目錄，將來創(chuàng)建的表數據保存在此文件夾中?！纠?-2】創(chuàng)建名稱為mytest的數據庫，字符集為utf8mb4和排序規(guī)則為采用系統(tǒng)默認。CREATEDATABASEmytestcharsetutf8mb4;查看數據庫查看所有的數據庫SHOWDATABASES;MySQL數據庫管理查看數據庫查看某個數據庫的創(chuàng)建語句SHOWCREATEDATABASEteaching_manage;使用數據庫MySQL服務器中可以同時存在多個數據庫，在做數據處理前，需要切換到指定數據庫。語法如下：USE數據庫名;MySQL數據庫管理【例】將teaching_manage切換為當前數據庫useteaching_manage;修改數據庫更改數據庫的字符集和排序規(guī)則ALTERDATABASEdb_name[[DEFAULT]CHARACTERSETcharset_name][[DEFAULT]COLLATEcollation_name];MySQL數據庫管理【例4-3】修改已建數據庫mytest，將其字符集改為utf8。ALTERDATABASEmytestCHARACTERSETutf8;刪除數據庫系統(tǒng)收回已創(chuàng)建數據庫所占用的存儲空間，清除其在數據字典中的定義，數據庫中所包含數據也將被清除。DROPDATABASE[IFEXISTS]數據庫名;MySQL數據庫管理【例4-4】刪除mytest數據庫，刪除前判斷數據庫是否存在。DROPDATABASEIFEXISTSmytest;數據類型:是指數據所代表信息的類型,不同的數據類型在使用范圍、存儲方式都不一樣。MySQL數據類型數字類型日期和時間類型字符型數據庫通用SET類型ENUM類型JSON數據類型MySQL使用(自學)數字類型MySQL數據類型類型字節(jié)取值范圍（有符號）取值范圍（無符號）TINYINT1（-128,127）（0,255）SAMLLINT2（-32768,32767）（0,65535）MEDIUMINT3（-8388608,8388607）（0，16777215）INT4（-2147483648,2147483647）（0,4294967295）BIGINT8（-263,263-1）（0,264-1）DECIMAL[(P[，S])]如果P>S,為P+2字節(jié)，否則為S+2字節(jié)和P、S的值有關和P、S的值有關關于數字類型的相關說明如下：MySQL數據類型（1）在選擇數據類型時，若一個數據將來可能參與數學計算，推薦使用整數或定點數類型：若只用來顯示，則推薦使用字符串類型。例如，商品庫存保存為整數類型；用戶的身份證、電話號碼可以保存為字符串型。（2）表的主鍵推薦使用整數類型，與字符串相比，整數類型的處理效率更高，查詢速度更快。（3）定義無符號數據類型，需要在數據類型右邊加UNSIGNED關鍵字，例如INTUNSIGNED；字符型MySQL數據類型數據類型說明限制CHAR(n)固定長度字符串0~255字符VARCHAR(n)可變長度字符串與字符集相關TEXT長文本數據0~28-1字符BLOB二進制長文本數據0~216-1字符關于字符類型的相關說明如下：MySQL數據類型（1）CHAR(n)存儲存儲大小為n個字符。如果實際輸入不足n個字符，系統(tǒng)會自動在后面添加空格來填滿。（2）VARCHAR(n)存儲大小為輸入數據的字符的實際長度，其存儲的最大長度取決于字符集，gbk和utf8對應的最大值分別為32766和21488。（3）BLOB類型用于保存數據量較大的二進制數據，如圖片、PDF文檔等，BLOB與TEXT類型的主要區(qū)別為：BLOB類型根據二進制數據進行比較與排序，區(qū)分字符大小寫；TEXT類型根據字符進行比較與排序，不區(qū)分字符大小寫。日期和時間類型MySQL數據類型類型字節(jié)取值范圍格式YEAR11901到2155YYYYTIME3-838:59到838:59:59HH:MM:SSDATE31000-01-01到9999-12-31YYYY-MM-DDDATETIME81000-01-0100:00:00到9999-12-3123:59:59YYYY-MM-DDHH:MM:SSTIMESTAMP41970-01-0100:00:00到2038-01-1903:14:07YYYY-MM-DDHH:MM:SS關于日期時間類型的相關說明如下：MySQL數據類型（1）在輸入YEAR類型的值時，既可以使用四位字符和數字，也可以使用2位字符和數字。使用兩位字符表示為'00'~'99'，其中，'00'~'69'會被轉化為2000~2069，'70'~'99'的值會被轉換為1970~1999。（2）日期型DATE的常量有多種表示方式：'20221025'、'2022-10-25'、'22-10-25'、'221025'都表示2022年10月25日。日期型的變量CURRENT_DATE返回系統(tǒng)當前日期，日期函數NOW()返回系統(tǒng)當前日期和日間。（3）時間型的常量以'HHMMSS'或HHMMSS格式表示，如輸入'121314'或121314都表示12:13:14（12小時13分14秒）。時間型的變量CURRENT_TIME返回系統(tǒng)當前時間。創(chuàng)建數據表在已存在的數據庫中建立新表語法格式：CREATETABLE[IFNOTEXISTS]<表名>(<字段名>

<字段類型>[字段屬性]…[索引定義])[表選項];MySQL數據表管理【例4-5】在teaching_manage數據庫中創(chuàng)建一個名稱為student的數據表，保存學生信息。CREATETABLEIFNOTEXISTSstudent( snoCHAR(6)PRIMARYKEYCOMMENT'學生編號’, snameVARCHAR(20)NOTNULL, deptVARCHAR(20), sexCHAR(1), birthdayDATE, totalcreditDECIMAL(4,1)DEFAULT0, remarksVARCHAR(100) );MySQL數據表管理【例4-6】在teaching_manage數據庫中創(chuàng)建一個名稱為course的數據表，保存課程信息。

CREATETABLEcourse( cnoCHAR(3)PRIMARYKEY cnameVARCHAR(30)NOTNULLUNIQUE termTINYINT, ctimeTINYINTUNSIGNED, creditDECIMAL(3,1));MySQL數據表管理【例4-7】在teaching_manage數據庫中創(chuàng)建一個名稱為score的數據表，保存成績信息。CREATETABLEscore( score_idINTUNSIGNEDAUTO_INCREMENTsnoCHAR(6), cnoCHAR(3), gradeDECIMAL(4,1)CHECK(grade>=0) PRIMARYKEY(score_id),CONSTRAINTfk_score_snoFOREIGNKEY(sno)REFERENCESstudent(sno),CONSTRAINTfk_score_cnoFOREIGNKEY(cno)REFERENCEScourse(cno));MySQL數據表管理【例4-8】在teaching_manage數據庫中利用score創(chuàng)建一個名稱為sc的數據表，保存成績信息。CREATETABLEscASSELECT*FROMscore;MySQL數據表管理注意：使用CREATETABLE+子查詢方式創(chuàng)建表，只復制表結構和內容，原表中的約束和索引通常不會被復制。查看數據表1.查看庫中數據表SHOWTABLES[LIKE匹配模式];MySQL數據表管理【例4-9】查看當前數據庫中所有數據表。SHOWTABLES;2.查看數據表的詳細信息SHOWTABLESTATUS[FROM數據庫名][LIKE匹配模式];MySQL數據表管理【例4-10】查看當前數據庫中所有數據表的詳細信息。SHOWTABLESTATUS;3.查看表結構DESC<數據表名>;MySQL數據表管理【例4-11】查看當前數據庫中student表的結構。DESCstudent;4.查看表的創(chuàng)建語句SHOWCREATETABLE<數據表名>;MySQL數據表管理【例4-12】查看當前數據庫中student表的創(chuàng)建語句。SHOWCREATETABLEstudent;修改數據表1.修改數據表名稱ALTERTABLE<原數據表名>RENAME[TO|AS]<新數據表名>;MySQL數據表管理【例4-13】修改數據表student為student1。ALTERTABLEstudentRENAMETOstudent1;2.修改數據表選項ALTERTABLE<數據表名><表選項>[=]<表選項>;MySQL數據表管理【例4-14】修改數據表student的字符集為gbk。ALTERTABLEstudentCHARSET=gbk;修改表結構1.新增字段ALTERTABLE<數據表名>ADD[COLUMN]<字段名類型>[FIRST|AFTER字段名];MySQL數據表管理【例4-15】修改數據表student增加1個新列nativeplace

。ALTERTABLEstudentADDnativeplaceVARCHAR(50);2.修改字段（1）修改字段名ALTERTABLE<數據表名>CHANGE<舊字段名><新字段名類型>MySQL數據表管理【例4-16】修改數據表student將nativeplace改名為native。ALTERTABLEstudentCHANGEnativeplacenativeVARCHAR(50);（2）修改字段類型和位置ALTERTABLE<數據表名>MODIFY<字段名類型>[FIRST|AFTER字段名2]MySQL數據表管理【例4-17】修改數據表student將nativeplace類型改為VARCHAR(30)。ALTERTABLEstudentMODIFYnativeplaceVARCHAR(30);3.刪除字段ALTERTABLE<數據表名>DROP[COLUMN]<字段名>;MySQL數據表管理【例4-18】修改數據表student刪除列nativeplaceALTERTABLEstudentDROPnativeplace;4.添加和刪除約束ALTERTABLE<數據表名>[ADDCONSTRAINT<完整性約束名><完整性約束>][DROPCONSTRAINT<完整性約束名>]MySQL數據表管理【例4-19】修改數據表score，在sno、cno兩字段上添加唯一約束uk_sno_cno

ALTERTABLEscoreADDCONSTRAINTuk_sno_cnoUNIQUE(sno,cno);刪除數據表DROPTABLE<數據表名>;表被刪除后，表在數據字典中的定義也被刪除，此表上建立的索引和視圖也被自動刪除。MySQL數據表管理【例4-20】刪除學生表DROPTABLEstudent;SQL提供了數據更新功能INSERT插入DELETE刪除UPDATE更新數據表的數據操作插入數據1.插入單個記錄語句格式

INSERT INTO<表名>[(<屬性列1>[，<屬性列2>…)] VALUES(<常量1>[，<常量2>]…)數據表的數據操作注意：

（1）INTO子句屬性列的順序可與表定義中的順序不一致

（2）VALUES子句提供的值必須與INTO子句匹配1.插入單個記錄數據表的數據操作【例4-21】向student表中插入一條記錄，所有字段都有值INSERTINTOstudentVALUES('001242','張青','計算機','女','04-01-22',50,'三好生');【例4-22】

向student表中插入一條記錄，部分字段有值INSERTINTOstudent(sno,sname,dept)VALUES('001243','李四','計算機');2.插入多個記錄INSERT[INTO]<表名>[(字段名,…)][AS]子查詢數據表的數據操作【例4-23】新建一個stu表，并向stu表中插入查詢結果CREATETABLEstu(snoCHAR(6),snameVARCHAR(10),deptVARCHAR(20),sexVARCHAR(1));INSERTINTOstuSELECTsno,sname,dept,sexFROMstudentWHEREdept='計算機';注意：stu表定義的字段的類型與長度最好與SELECT后面字段的類型和長度相同，否則拒絕插入。修改數據UPDATE<表名>SET列名={表達式|(子查詢)}[,…][WHERE<條件表達式>];注意：

（1）如果不選WHERE子句，則表中所有的行全被更新；

（2）如果選擇了WHERE子句，則使WHERE中條件表達式為真的行被更新。數據表的數據操作【例4-24】把student表中的總學分加10。UPDATEstudentSETtotalcredit=totalcredit+10;數據表的數據操作【例4-25】把student表中姓名為“羅林琳”的學生的專業(yè)改為“計算機”，備注改為“三好學生”。UPDATEstudentSETdept='計算機',remarks='三好學生'WHEREsname='羅林琳';刪除數據DELETEFROM<表名>[WHERE<條件表達式>];WHERE子句指定要刪除的元組缺省表示要刪除表中的全部元組，表的定義仍在字典中數據表的數據操作數據表的數據操作【例4-26】刪除student表中計算機系全體學生的記錄。DELETEFROMstudentWHEREdept='計算機';【例4-27】刪除student表中的所有記錄。。DELETEFROMstudent;TRUNCATETABLE語句截斷基本表，從表中刪除所有的行，并且釋放該表所使用的存儲空間。在使用TRUNCATETABLE語句時，不能回退已刪除的行。數據表的數據操作【例4-28】將表STUDENT刪除，并釋放其存儲空間。TRUNCATETABLESTUDENT;為什么要定義自增型的字段？自增型字段（1）業(yè)務需求（2）空間效率：相同長度的字符串和數值所能表示的范圍有較大差別。

如4個字節(jié)的數值型字符所表示的范圍為：‘0000’—‘9999’共10000種不同的值，而4個字節(jié)的int類型所能表示的范圍為：0——232-1共42億種不同的值。（3）時間效率：相同長度的字符串和數值進行比較時，數值的比較速度要高于字符的比較速度。AUTO_INCREMENT類型AUTO_INCREMENT定義自增長字段，查看參數下：SHOWVARIABLESLIKE'auto_increment%';自增型字段參數名稱默認值取值范圍作用auto_increment_increment11~65535控制增量的幅度。auto_increment_offset11~65535增量開始的位置（開始的偏移量）。--設置AUTO_INCREMENT類型的初始值為2SET@@auto_increment_offset=2;--設置AUTO_INCREMENT類型的增量為2SET@@auto_increment_increment=2;自增型字段的數據插入AUTO_INCREMENT類型定義的字段默認值和步長均為1，每插入一條記錄，該字段的值自動加1。插入操作中，被AUTO_INCREMENT修飾的字段可以給空值或具體值。自增型字段【例4-28】創(chuàng)建stu表，設置自增型字段，插入記錄。CREATETABLEstu(stu_idINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,snameVARCHAR(10));INSERTINTOstuVALUES(NULL,'張三'),(NULL,'李四'),(10,'王五');2.自增型字段的數據修改在定義表結構時，可以設置自增型字段的初始值，也可以通過修改表結構來修改初始值。當修改表結構，重新定義初始值時，原表中的數據不會發(fā)生變化，只對新插入的數據起作用。自增型字段【例4.30】創(chuàng)建數據表stu，定義stu_id為自增型字段，設置初始值為200001，并插入兩條記錄。CREATETABLEstu(stu_idINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,snameVARCHAR(10))AUTO_INCREMENT=200001;INSERTINTOstu2VALUES(NULL,'張三'),(NULL,'李四');自增型字段【例4.31】修改stu表的stu_id字段的初始值為202201，并插入兩條記錄。

ALTERTABLEstuAUTO_INCREMENT=202201;INSERTINTOstu2VALUES(NULL,'張三'),(NULL,'李四');3.自增型字段的數據刪除執(zhí)行DELETE命令刪除表記錄后，再執(zhí)行插入操作時，自增型字段的值在原來序列最大值的基礎上自動增加。此時如想從初始值開始自增，必須使用TRUNCATE命令刪除全表的記錄。自增型字段自增型字段【例4.32】用DELETE命令刪除stu中所有記錄，重新插入新記錄DELETEFROMstu；INSERTINTOstuVALUES(NULL,'張三'),(NULL,'李四');【例4.33】用TRUNCATE命令刪除stu中所有記錄，插入新記錄TRUNCATETABLEstu;INSERTINTOstuVALUES(NULL,'張三'),(NULL,'李四');數據庫的完整性約束數據庫的完整性是指保證數據庫中數據的正確性和相容性。數據的正確性是指數據庫數據符合現實世界語義且反映當前的實際狀況。數據的相容性是指數據庫同一對象在不同關系表中的數據是一致的。在定義數據表時可以進一步定義與此表有關的完整性約束條件，如主碼、外碼、空值、唯一值和默認值等約束。當數據庫用戶對數據庫進行更新操作時，數據庫管理系統(tǒng)會自動檢測操作數據是否符合相關完整性約束，并做出相應的處理。實體完整性約束保證一個表的每行取值是唯一的。要實現實體完整性，必須指定一個表中的一列或多列作為表的主鍵（

PRIMARYKEY）。定義方式有：列級和表級。（1）實體完整性定義【例】：列級定義主碼

CREATETABLEXS

(學號CHAR(9)PRIMARYKEY，

姓名CHAR(8)NOTNULL);【例】：表級定義主碼

CREATETABLEscore(snoCHAR(6)，

cnoCHAR(3)，

gradeDECIMAL(5,1)

PRIMARYKEY（sno,cno）

//表級定義

);實體完整性約束對主碼列進行插入或更新操作時，DBMS按照實體完整性規(guī)則自動進行檢查。包括：檢查主碼值是否唯一，如果不唯一則拒絕執(zhí)行；檢查主碼的各個屬性是否為空，如果為空就拒絕執(zhí)行。（2）實體完整性檢查實體完整性違約處理：拒絕(RESTRICT)執(zhí)行。（3）實體完整性違約處理參照完整性約束通過聲明一個FOREIGNKEY在表中定義一個或多個外鍵并實現參照完整性，用REFERENCES定義外鍵來自的表名，即主表或父表名。（1）參照完整性定義通過在定義外鍵時加參照完整性選擇項ONUPDATE/ONDELETE來實現當主表被引用的主鍵被修改/刪除時，為了不破壞參照完整性約束所有可能的處理辦法：

RESTRICT：缺省選擇項，稱為限制引用操作。凡是被子表所引用的主鍵，不得進行任何操作；

CASCADE：稱為級聯引用操作。凡是對主表的主鍵進行的操作，子表中引用此主鍵的行也隨之自動被修改/刪除SETNULL:置空操作。凡是對主表的主鍵進行的操作，造成外鍵值與主鍵不一致，子表中引用此主鍵的值被設置為NULL。參照完整性約束（2）參照完整性檢查和違約處理被參照表（父表）參照表（子表）違約處理可能破壞參照完整性插入記錄拒絕可能破壞參照完整性修改外碼值拒絕刪除記錄可能破壞參照完整性拒絕/級聯刪除/設置為空值修改主碼值可能破壞參照完整性拒絕/級聯刪除/設置為空值參照完整性約束【例4-34】：創(chuàng)建dept和emp表，設置dno字段的參照完整性關系，并設置更新和刪除違約處理方式CREATETABLEdept(dnoCHAR(3)PRIMARYKEY,dnameVARCHAR(50));CREATETABLEemp(enoCHAR(3)PRIMARYKEY,enameVARCHAR(50),dnoCHAR(2),CONSTRAINTfk_dnoFOREIGNKEY(dno)REFERENCESdept(dno)

ONDELETESETNULLONUPDATECASCADE);用戶自定義完整性約束就某一個體應用涉及的數據必須滿足的語義要求。（1）用戶自定義完整性定義定義類型：列值非空（NOTNULL）列值唯一（UNIQUE）默認值約束（DEFAULT）檢查列值是否滿足一個布爾表達式（CHECK）對插入或更新操作時，