電工與電子技術(shù)電子商務(wù)電子課件MySQL數(shù)據(jù)庫(kù)應(yīng)用2.2 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu)1ppt

1、項(xiàng)目2：數(shù)據(jù)庫(kù)設(shè)計(jì)數(shù)據(jù)庫(kù)應(yīng)用任務(wù)2 創(chuàng)建與管理數(shù)據(jù)表 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu)(1) 教學(xué)目標(biāo) 素質(zhì)目標(biāo) 知識(shí)目標(biāo)1線上線下結(jié)合，引導(dǎo)學(xué)習(xí)方式，培養(yǎng)自主學(xué)習(xí)能力；2培養(yǎng)學(xué)生積極探索、勇于創(chuàng)新的科學(xué)素養(yǎng)；3思政內(nèi)容：本次課通過(guò)以公司招聘數(shù)據(jù)庫(kù)技術(shù)人員的要求為例，來(lái)結(jié)合講解數(shù)據(jù)庫(kù)開(kāi)發(fā)規(guī)范。1理解數(shù)據(jù)庫(kù)中使用到的常用數(shù)據(jù)類型；2掌握在命令行方式下創(chuàng)建數(shù)據(jù)表的方法；3掌握在命令行方式下設(shè)置數(shù)據(jù)表約束的方法 能力目標(biāo)1能采用命令方式建立數(shù)據(jù)表、開(kāi)發(fā)環(huán)境中建立數(shù)據(jù)表；2能實(shí)現(xiàn)表的各種約束，特別是主鍵、外鍵約束；3能采用圖形界面方式創(chuàng)建表及創(chuàng)建表的約束 教學(xué)重點(diǎn)通過(guò)命令行方式建立數(shù)據(jù)表并實(shí)現(xiàn)約束；在SQLyog圖形工

2、具軟件中建立數(shù)據(jù)表及實(shí)現(xiàn)表的約束 教學(xué)難點(diǎn)通過(guò)命令行方式建立數(shù)據(jù)表及約束教學(xué)重難點(diǎn)知識(shí)儲(chǔ)備數(shù)據(jù)表是數(shù)據(jù)庫(kù)中用于存放數(shù)據(jù)的容器。作為數(shù)據(jù)庫(kù)開(kāi)發(fā)人員，需要在創(chuàng)建的用戶數(shù)據(jù)庫(kù)中創(chuàng)建系統(tǒng)所需要的數(shù)據(jù)表。創(chuàng)建數(shù)據(jù)表分為兩個(gè)階段，首先是創(chuàng)建表的結(jié)構(gòu)，然后是向表中輸入數(shù)據(jù)。1數(shù)據(jù)類型用戶在創(chuàng)建表結(jié)構(gòu)的時(shí)候，需要指定表中各種數(shù)據(jù)所屬的類型。數(shù)據(jù)類型是指用于存儲(chǔ)、檢索及解釋數(shù)據(jù)值類型而預(yù)先定義的命名方法，它決定了數(shù)據(jù)在計(jì)算機(jī)中的存儲(chǔ)格式，代表不同的信息類型。在MySQL數(shù)據(jù)庫(kù)管理系統(tǒng)中，提供了數(shù)值類型（整數(shù)類型、浮點(diǎn)數(shù)和定點(diǎn)數(shù)類型、二進(jìn)制類型）、日期和時(shí)間類型以及字符串類型。 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 4知識(shí)儲(chǔ)備(一）數(shù)

3、值類型 MySQL支持所有標(biāo)準(zhǔn)SQL數(shù)值數(shù)據(jù)類型。下表列出了每個(gè)數(shù)值類型數(shù)據(jù)的相關(guān)特性。 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 5類型 存儲(chǔ)長(zhǎng)度（字節(jié)）范圍（有符號(hào)）范圍（無(wú)符號(hào)）默認(rèn)寬度整數(shù)類型Tinyint1 -27，27-10，28-14位Smallint2 -215，215-10，216-16位Mediumint3 -223223-10224-19位Int(Interger)4 -231231-1023211位Bigint8 -263263-1026420位浮點(diǎn)與定點(diǎn)數(shù)類型Float4 單精度浮點(diǎn)數(shù)值，默認(rèn)保留實(shí)際精度Double8 雙精度浮點(diǎn)數(shù)值，默認(rèn)保留實(shí)際精度Dec(p,s)p+2依賴于p和s的值依賴

4、于p和s的值存放精確的小數(shù)值二進(jìn)制類型Bit(m)m位 64位m位Varbinary(m)可變長(zhǎng)度 0m位可變長(zhǎng)度Binary(m)m位 m位Tinyblob可變長(zhǎng)度 28-1可變長(zhǎng)度Blob可變長(zhǎng)度 216-1可變長(zhǎng)度Mediumblob可變長(zhǎng)度 224-1可變長(zhǎng)度Longblob可變長(zhǎng)度 232-1可變長(zhǎng)度知識(shí)儲(chǔ)備（二）日期和時(shí)間類型 MySQL中表示日期和時(shí)間類型有Year、Date、Time、Datetime和Timestamp。下表顯示了日期和時(shí)間類型的相關(guān)特性。 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 6類型存儲(chǔ)長(zhǎng)度(字節(jié))范圍格式用途Year119012155YYYY或YYYY年份值Date31000-

5、01-019999-12-31YYYY-MM-DD或YYYYMMDD日期值Time3-838:59:59838:59:59HH:MM:SS時(shí)間值或持續(xù)時(shí)間Datetime81000-01-01 00:00:009999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和時(shí)間值Timestamp41970-01-01 00:00:002038-01-19 03:14:17 YYYY-MM-DD HH:MM:SS混合日期和時(shí)間值，時(shí)間戳知識(shí)儲(chǔ)備（三）字符串類型 MySQL支持兩類字符串類型：文本字符串如Char、Varchar、Tinytext、Text、Mediumte

6、xt、Longtext、Enum和Set等；二進(jìn)制字符串如Bit和BLOB等。下表描述了這些字符串類型數(shù)據(jù)的相關(guān)特性。 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 7類型大小（字節(jié)）數(shù)值范圍（字節(jié)）用途文本字符串Char(n)N0255 定長(zhǎng)字符串Varchar(n)輸入字符串長(zhǎng)度+1065535 變長(zhǎng)字符串，最大為n+1Tinytext值的長(zhǎng)度+20255短文本字符串Text值的長(zhǎng)度+2065535長(zhǎng)文本數(shù)據(jù)Mediumtext值的長(zhǎng)度+3016777 215中等長(zhǎng)度文本數(shù)據(jù)Longtext值的長(zhǎng)度+404294967295二進(jìn)制形式的極大文本數(shù)據(jù)Enum1或2 枚舉類型，在表創(chuàng)建時(shí)指定列值中選擇一個(gè)Set14字節(jié)或8

7、 在表創(chuàng)建時(shí)指定列值中選擇一個(gè)或多個(gè)二進(jìn)制字符串Bit(n) (n+7)/8位字段類型Binary(n) n固定長(zhǎng)度二進(jìn)制串Varbinary(n) n+1可變長(zhǎng)度二進(jìn)制串Tinyblob(n)值的長(zhǎng)度+10256非常小的BlobBLOB值的長(zhǎng)度+2065535小的BlobMeduumblob(n)值的長(zhǎng)度+30224中等大小的BlobLongblob(n)值的長(zhǎng)度+40232非常大的Blob知識(shí)儲(chǔ)備2創(chuàng)建數(shù)據(jù)表在MySQL中，表是數(shù)據(jù)庫(kù)中最重要、最基本的操作對(duì)象，是數(shù)據(jù)存儲(chǔ)的基本單位。數(shù)據(jù)在表中的組織方式與電子表格類似，也是按行和列形式組成的集合，每一行代表一條記錄，一個(gè)記錄用于存儲(chǔ)一個(gè)對(duì)象

8、的相關(guān)屬性；每一列代表記錄的一個(gè)字段，一個(gè)字段就是一個(gè)屬性。 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 8知識(shí)儲(chǔ)備（二）在Command line client方式下定義表結(jié)構(gòu)數(shù)據(jù)表在存放數(shù)據(jù)之前，需要先定義其結(jié)構(gòu)。定義結(jié)構(gòu)就是設(shè)置表有哪些字段，以及這些字段的特性，如字段名稱、數(shù)據(jù)類型、長(zhǎng)度、精度、小數(shù)位數(shù)、是否唯一、是否定義為主鍵、是否允許為空值（NULL）、是否設(shè)置默認(rèn)值等等。在Command line client方式下定義表結(jié)構(gòu)的語(yǔ)法如下： CREATE TEMPORARY TABLE IF NOT EXISTS table_name( 屬性名 數(shù)據(jù)類型 列約束條件, 屬性名 數(shù)據(jù)類型 列約束條件, , 表約束

9、條件 )ENGINE=存儲(chǔ)引擎; 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 9知識(shí)儲(chǔ)備（三）表的約束一個(gè)數(shù)據(jù)庫(kù)往往是由多個(gè)表組成的，如何實(shí)現(xiàn)表與表之間的關(guān)聯(lián)關(guān)系、如何減少表數(shù)據(jù)在輸入時(shí)的錯(cuò)誤、如何防止非法數(shù)據(jù)的輸入等，這些都可以通過(guò)建立表約束來(lái)實(shí)現(xiàn)。表約束包括主鍵約束、外鍵約束、非空約束、唯一性約束、默認(rèn)約束和檢查約束。（1）主鍵約束主鍵是表中一列或多列的組合。主鍵用于唯一標(biāo)識(shí)數(shù)據(jù)表中的一條記錄。主鍵約束就是要求主鍵不能取空值，也不允許取重復(fù)值，主鍵約束對(duì)應(yīng)的是實(shí)體完整性。在一個(gè)數(shù)據(jù)表中，只能定義一個(gè)主鍵，并且系統(tǒng)會(huì)自動(dòng)為主鍵創(chuàng)建索引。 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 10知識(shí)儲(chǔ)備由表的一列組成的主鍵稱為單字段主鍵，由表的多列組成的主

10、鍵稱為多字段聯(lián)合主鍵。a) 單字段主鍵單字段主鍵的指定有兩種方法，一種是在定義列的同時(shí)指定主鍵，另一種是在定義完所有列之后指定主鍵。在定義列的同時(shí)指定主鍵的語(yǔ)法規(guī)則如下：字段名 數(shù)據(jù)類型 primary key 默認(rèn)值 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 11知識(shí)儲(chǔ)備【單字段主鍵示例】 在測(cè)試數(shù)據(jù)庫(kù)XSCJ_db中，以表2-4所示的XSQK1表結(jié)構(gòu)創(chuàng)建XSQK1表。表2-4 XSQK1表結(jié)構(gòu)mysql use XSCJ_db; #創(chuàng)建表前，需要先指定當(dāng)前數(shù)據(jù)庫(kù)Database changedmysql create table XSQK1( - 學(xué)號(hào) char(10) primary key, #在定義列的同時(shí)指

11、定主鍵 - 姓名 varchar(10), - 性別 char(2);Query OK, 0 rows affected (0.02 sec) 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 12列名數(shù)據(jù)類型長(zhǎng)度（字節(jié)）約束學(xué)號(hào)char10主鍵姓名varchar10 性別char2知識(shí)儲(chǔ)備【互動(dòng)】 在測(cè)試數(shù)據(jù)庫(kù)XSCJ_db中，以表2-4所示的XSQK1表結(jié)構(gòu)創(chuàng)建XSQK1表。表2-4 XSQK1表結(jié)構(gòu) 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 13列名數(shù)據(jù)類型長(zhǎng)度（字節(jié)）約束學(xué)號(hào)char10主鍵姓名varchar10 性別char2知識(shí)儲(chǔ)備在定義完所有列之后指定主鍵的語(yǔ)法規(guī)則如下：CONSTRAINT PRIMARY KEY 字段名mysql dr

12、op table if exists XSQK1; #刪除前面創(chuàng)建的CJ1表Query OK, 0 rows affected (0.27 sec)mysql create table XSQK1( - 學(xué)號(hào) char(10), - 姓名 varchar(10), - 性別 char(2), - constraint primary key(學(xué)號(hào)); #在定義完所有列之后指定主鍵Query OK, 0 rows affected (0.02 sec) 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 14知識(shí)儲(chǔ)備（b)多字段聯(lián)合主鍵定義多字段聯(lián)合主鍵的語(yǔ)法規(guī)則：PRIMARY KEY 字段1，字段2，字段n【多字段主鍵示例】參

13、照下表所示的CJ1表結(jié)構(gòu)創(chuàng)建CJ1表，將“學(xué)號(hào)”“課程號(hào)”兩列組成聯(lián)合主鍵。 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 15列名數(shù)據(jù)類型長(zhǎng)度（字節(jié)）主鍵學(xué)號(hào)Char10是課程號(hào)Char3是成績(jī)Tinyint1 學(xué)分Tinyint1知識(shí)儲(chǔ)備mysql drop table if exists CJ1;Query OK, 0 rows affected (0.01 sec)mysql create table CJ1( - 學(xué)號(hào) char(10), - 課程號(hào) char(3), - 成績(jī) tinyint, - 學(xué)分 tinyint, - constraint primary key(學(xué)號(hào),課程號(hào));Query OK, 0

14、rows affected (0.03 sec)注意：多字段聯(lián)合主鍵只能在定義完所有列之后用“constraint primary key”指定。 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 16知識(shí)儲(chǔ)備（b)多字段聯(lián)合主鍵【互動(dòng)】參照下表所示的CJ1表結(jié)構(gòu)創(chuàng)建CJ1表，將“學(xué)號(hào)”“課程號(hào)”兩列組成聯(lián)合主鍵。 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 17列名數(shù)據(jù)類型長(zhǎng)度（字節(jié)）主鍵學(xué)號(hào)Char10是課程號(hào)Char3是成績(jī)Tinyint1 學(xué)分Tinyint1知識(shí)儲(chǔ)備（2）非空約束非空約束是指字段的值不能為空。對(duì)于指定了非空約束的字段，如果用戶在添加數(shù)據(jù)時(shí)沒(méi)有指定值，則數(shù)據(jù)庫(kù)會(huì)報(bào)錯(cuò)?？諏傩允锹暶髟摿械闹翟诒碇休斎霐?shù)據(jù)時(shí)可以不填，用NULL表示，如

15、果不允許為空，則需要聲明為NOT NULL。NULL屬性表示數(shù)值未知，不是零長(zhǎng)度字符串，也不是數(shù)字0，只表示沒(méi)有輸入其內(nèi)容，可用于該列的值暫時(shí)未定，或不需要輸入值的情況。如果某列被定義為NOT NULL屬性，但在表中插入數(shù)據(jù)時(shí)沒(méi)有輸入任何數(shù)據(jù)，則會(huì)彈出如下所示的錯(cuò)誤。 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 18知識(shí)儲(chǔ)備定義非空約束的語(yǔ)法規(guī)則：字段名 數(shù)據(jù)類型 NOT NULL【非空約束示例】在表2-4 XSQK1表結(jié)構(gòu)的基礎(chǔ)上，設(shè)置XSQK1表中的姓名列不能取空值。mysql drop table if exists XSQK1;Query OK, 0 rows affected (0.03 sec)mysql c

16、reate table XSQK1( - 學(xué)號(hào) char(10) primary key, - 姓名 varchar(10) NOT NULL, #設(shè)置姓名不能為空值 - 性別 char(2);Query OK, 0 rows affected (0.03 sec) 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 19知識(shí)儲(chǔ)備【互動(dòng)】在下表結(jié)構(gòu)的基礎(chǔ)上，設(shè)置XSQK1表中的姓名列不能取空值。 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 20列名數(shù)據(jù)類型長(zhǎng)度（字節(jié)）約束學(xué)號(hào)char10主鍵姓名varchar10 性別char2知識(shí)儲(chǔ)備（3）默認(rèn)值約束默認(rèn)值約束用于指定某列的默認(rèn)值。定義默認(rèn)值約束的語(yǔ)法規(guī)則：字段名 數(shù)據(jù)類型 DEFAULT 默認(rèn)值【默認(rèn)值

17、約束示例】以表2-4 XSQK1表結(jié)構(gòu)為例，設(shè)置XSQK1表中的性別列的默認(rèn)值為“男”。mysql drop table if exists XSQK1;Query OK, 0 rows affected (0.03 sec)mysql create table XSQK1( - 學(xué)號(hào) char(10) primary key, - 姓名 varchar(10) NOT NULL, - 性別 char(2) default 男); #設(shè)置性別的默認(rèn)值為男Query OK, 0 rows affected (0.04 sec) 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 21知識(shí)儲(chǔ)備（3）默認(rèn)值約束【互動(dòng)】以下表結(jié)構(gòu)為例，

18、設(shè)置XSQK1表中的性別列的默認(rèn)值為“男”。 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 22列名數(shù)據(jù)類型長(zhǎng)度（字節(jié)）約束學(xué)號(hào)char10主鍵姓名varchar10 性別char2默認(rèn)值：“男”知識(shí)儲(chǔ)備（4）唯一鍵約束唯一性約束又稱唯一鍵約束，它用于保證列中不會(huì)出現(xiàn)重復(fù)的數(shù)據(jù)，在一個(gè)數(shù)據(jù)表上可以定義多個(gè)唯一性約束，定義了唯一性約束的列可以取空值。唯一性約束實(shí)現(xiàn)了實(shí)體完整性規(guī)則。指定唯一約束的語(yǔ)法規(guī)則：字段名 數(shù)據(jù)類型 UNIQUE 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 23知識(shí)儲(chǔ)備【唯一性約束示例】以表2-4 XSQK1表結(jié)構(gòu)為例，定義數(shù)據(jù)表XSQK1，將姓名列指定為非空，并取唯一值。mysql drop table if exists XS

19、QK1;Query OK, 0 rows affected (0.03 sec)mysql create table XSQK1( - 學(xué)號(hào) char(10) primary key, - 姓名 varchar(10) NOT NULL UNIQUE, #設(shè)置姓名不能取空值，不能有重復(fù)值 - 性別 char(2) default 男);Query OK, 0 rows affected (0.05 sec) 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 24知識(shí)儲(chǔ)備（5）外鍵約束外鍵是指某個(gè)屬性對(duì)本表來(lái)說(shuō)，不是本表的主鍵或只是本表主鍵的一部分（本表主鍵是多字段聯(lián)合主鍵的情況），但卻是另外一個(gè)表的主鍵。外鍵是用來(lái)在兩個(gè)表的數(shù)

20、據(jù)之間建立鏈接的一列或多列。一個(gè)表可以有一個(gè)或多個(gè)外鍵。外鍵對(duì)應(yīng)的是參照完整性，用于保持?jǐn)?shù)據(jù)的一致性和完整性。定義了外鍵之后，不允許刪除另一個(gè)表中具有關(guān)聯(lián)關(guān)系的行。一個(gè)表的外鍵可以為空值，如不為空值，則必須與另一個(gè)表中某個(gè)主鍵的值相同。 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 25知識(shí)儲(chǔ)備（5）外鍵約束外鍵是指某個(gè)屬性對(duì)本表來(lái)說(shuō)，不是本表的主鍵或只是本表主鍵的一部分（本表主鍵是多字段聯(lián)合主鍵的情況），但卻是另外一個(gè)表的主鍵。外鍵是用來(lái)在兩個(gè)表的數(shù)據(jù)之間建立鏈接的一列或多列。一個(gè)表可以有一個(gè)或多個(gè)外鍵。外鍵對(duì)應(yīng)的是參照完整性，用于保持?jǐn)?shù)據(jù)的一致性和完整性。定義了外鍵之后，不允許刪除另一個(gè)表中具有關(guān)聯(lián)關(guān)系的行。一個(gè)表的外

21、鍵可以為空值，如不為空值，則必須與另一個(gè)表中某個(gè)主鍵的值相同。 創(chuàng)建外鍵約束的語(yǔ)法規(guī)則： CONSTRAINT FOREIGN KEY 字段名1，字段名2，REFERENCES 主鍵列1，主鍵列2 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 26知識(shí)儲(chǔ)備【外鍵約束示例】在數(shù)據(jù)庫(kù)XSCJ_db2中，在表2-5所示的結(jié)構(gòu)基礎(chǔ)上，要求將CJ1表的“學(xué)號(hào)”列定義為外鍵，關(guān)聯(lián)到XSQK1表的 “學(xué)號(hào)”列。mysql drop table if exists CJ1; #刪除前面定義CJ1表Query OK, 0 rows affected (0.03 sec)mysql create table CJ1( - 學(xué)號(hào) char(10

22、), - 課程號(hào) char(3), - 成績(jī) tinyint, - 學(xué)分 tinyint, - constraint primary key(學(xué)號(hào),課程號(hào)), - constraint FK_XSQK1_XH foreign key(學(xué)號(hào)) references XSQK1(學(xué)號(hào)); #定義外鍵Query OK, 0 rows affected (0.07 sec) 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 27知識(shí)儲(chǔ)備【互動(dòng)】在數(shù)據(jù)庫(kù)XSCJ_db2中，在下表所示的結(jié)構(gòu)基礎(chǔ)上，要求將CJ1表的“學(xué)號(hào)”列定義為外鍵，關(guān)聯(lián)到XSQK1表的 “學(xué)號(hào)”列。 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 28列名數(shù)據(jù)類型長(zhǎng)度（字節(jié)）主鍵學(xué)號(hào)Char10是課

23、程號(hào)Char3是成績(jī)Tinyint1 學(xué)分Tinyint1知識(shí)儲(chǔ)備（6）檢查約束檢查約束是用來(lái)檢查輸入的數(shù)據(jù)是否滿足約束條件，它實(shí)現(xiàn)了關(guān)系完整性規(guī)則中的“域完整性”規(guī)則。例如，我們可以通過(guò)設(shè)置檢查約束，使在學(xué)生成績(jī)管理系統(tǒng)中要求錄入的學(xué)生成績(jī)?cè)?到100之間。定義檢查約束的語(yǔ)法規(guī)則：字段名 數(shù)據(jù)類型 CHECK(約束條件) 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu) 29知識(shí)儲(chǔ)備【檢查約束示例】參照表2-5所示的CJ1表結(jié)構(gòu)創(chuàng)建CJ1表，并為成績(jī)列增加檢查約束，要求其取值在0到100之間。mysql drop table if exists CJ1;Query OK, 0 rows affected (0.03 sec)mysql create table CJ1( - 學(xué)號(hào) char(10), - 課程號(hào) char(3), - 成績(jī) tinyint check(成績(jī)=0 and 成績(jī) 學(xué)分 tinyint, - constraint primary key(學(xué)號(hào),課程號(hào));Query OK, 0 rows affected (0.04 sec) 創(chuàng)建數(shù)