第3章MySQL數(shù)據(jù)庫和表

1、第第3 3章章 MySQLMySQL數(shù)據(jù)庫和表數(shù)據(jù)庫和表MySQL數(shù)據(jù)庫數(shù)據(jù)庫3 . 13 . 2MySQL表表表記錄的操作表記錄的操作3 . 33 . 4MySQL數(shù)據(jù)庫信息顯示數(shù)據(jù)庫信息顯示3.1 MySQL3.1 MySQL數(shù)據(jù)庫數(shù)據(jù)庫3.1.1 創(chuàng)建數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫使用CREATE DATABASE或CREATE SCHEMA命令可以創(chuàng)建數(shù)據(jù)庫。語法格式：語法格式：CREATE DATABASE | SCHEMA IF NOT EXISTS 數(shù)據(jù)庫名 選項(xiàng) .選項(xiàng)：選項(xiàng)：DEFAULT CHARACTER SET 字符集| DEFAULT COLLATE 校對規(guī)則名3.1.1 3.1.

2、1 創(chuàng)建數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫【例3.1】創(chuàng)建學(xué)生成績數(shù)據(jù)庫xscj。mysqlcreate database xscj如果已經(jīng)創(chuàng)建了數(shù)據(jù)庫，（例如mytest）重復(fù)創(chuàng)建時系統(tǒng)會提示數(shù)據(jù)庫已經(jīng)存在，不能再創(chuàng)建。系統(tǒng)顯示錯誤信息。使用IF NOT EXISTS選項(xiàng)從句可不顯示錯誤信息。創(chuàng)建了數(shù)據(jù)庫之后使用USE命令可指定當(dāng)前數(shù)據(jù)庫。語法格式：語法格式：USE 數(shù)據(jù)庫名;例如：指定當(dāng)前數(shù)據(jù)庫為學(xué)生成績數(shù)據(jù)庫（xscj）。mysqluse xscj3.1.2 3.1.2 修改數(shù)據(jù)庫修改數(shù)據(jù)庫數(shù)據(jù)庫創(chuàng)建后，如果需要修改數(shù)據(jù)庫的參數(shù)，可以使用ALTER DATABASE命令。語法格式：語法格式：ALTER D

3、ATABASE | SCHEMA 數(shù)據(jù)庫名 選項(xiàng) .選項(xiàng)：選項(xiàng)：DEFAULT CHARACTER SET 字符集名 | DEFAULT COLLATE 校對規(guī)則名【例3.2】修改學(xué)生成績數(shù)據(jù)庫（xscj）默認(rèn)字符集和校對規(guī)則。3.1.3 3.1.3 刪除數(shù)據(jù)庫刪除數(shù)據(jù)庫已經(jīng)創(chuàng)建的數(shù)據(jù)庫需要刪除，使用DROP DATABASE命令。語法格式：語法格式：DROP DATABASE IF EXISTS 數(shù)據(jù)庫名還可以使用IF EXISTS子句，避免刪除不存在的數(shù)據(jù)庫時出現(xiàn)MySQL錯誤信息。3.2 MySQL3.2 MySQL表表3.2.1 創(chuàng)建表創(chuàng)建表1全新創(chuàng)建全新創(chuàng)建從頭創(chuàng)建一個全新的表，使用

4、CREATE TABLE命令。語法格式：語法格式：CREATE TEMPORARY TABLE IF NOT EXISTS 表名 ( 列定義 . | 表索引定義 ) 表選項(xiàng) select語句;說明：說明：lTEMPORARY：該關(guān)鍵字表示用CREATE命令新建的表為臨時表。lIF NOT EXISTS：在創(chuàng)建表前加上一個判斷，只有該表目前尚不存在時才執(zhí)行CREATE TABLE操作。l列定義：包括列名、數(shù)據(jù)類型，可能還有一個空值聲明和一個完整性約束。l表索引項(xiàng)定義：主要定義表的索引、主鍵、外鍵等，具體定義將在第5章中討論。lselect語句：用于在一個已有表的基礎(chǔ)上創(chuàng)建表。3.2.1 3.2.

5、1 創(chuàng)建表創(chuàng)建表（1）“列定義列定義”格式如下：格式如下：列名 type NOT NULL | NULL DEFAULT 默認(rèn)值 AUTO_INCREMENT UNIQUE KEY | PRIMARY KEY COMMENT string 參照定義其中：其中：列名：必須符合標(biāo)識符規(guī)則，長度不能超過64個字符，而且在表中要唯一。type：列的數(shù)據(jù)類型，有的數(shù)據(jù)類型需要指明長度n，并用括號括起，MySQL支持的數(shù)據(jù)類型在附錄C中介紹。AUTO_INCREMENT：設(shè)置自增屬性，只有整型列才能設(shè)置此屬性。NOT NULL | NULL：指定該列是否允許為空。如果不指定，則默認(rèn)為NULL。DEFAUL

6、T 默認(rèn)值：為列指定默認(rèn)值，默認(rèn)值必須為一個常數(shù)。UNIQUE KEY | PRIMARY KEY：PRIMARY KEY和UNIQUE KEY都表示字段中的值是唯一的。COMMENT string：對于列的描述，string是描述的內(nèi)容。參照定義：指定參照的表和列“type”定義如下。3.2.1 3.2.1 創(chuàng)建表創(chuàng)建表（2）“表選項(xiàng)表選項(xiàng)”定義如下：定義如下：ENGINE | TYPE = engine_name/*存儲引擎*/| AUTO_INCREMENT = value/*初始值*/| AVG_ROW_LENGTH = value/*表的平均行長度*/| DEFAULT CHARAC

7、TER SET 字符集名 COLLATE 校對規(guī)則名/*默認(rèn)字符集和校對*/| CHECKSUM = 0 | 1/*設(shè)置1表示求校驗(yàn)和*/| COMMENT = string/*注釋*/| CONNECTION = connect_string/*連接字符串*/| MAX_ROWS = value/*行的最大數(shù)*/| MIN_ROWS = value/*列的最小數(shù)*/| PACK_KEYS = 0 | 1 | DEFAULT| PASSWORD = string/*對.frm文件加密*/| DELAY_KEY_WRITE = 0 | 1/*對關(guān)鍵字的更新*/| ROW_FORMAT = DEF

8、AULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT/*定義各行應(yīng)如何儲存*/| UNION = (表名,表名.)/*表示哪個表應(yīng)該合并*/| INSERT_METHOD = NO | FIRST | LAST /*是否執(zhí)行INSERT語句*/| DATA DIRECTORY = absolute path to directory/*數(shù)據(jù)文件的路徑*/| INDEX DIRECTORY = absolute path to directory/*索引的路徑*/3.2.1 3.2.1 創(chuàng)建表創(chuàng)建表【例3.3】在學(xué)生成績數(shù)據(jù)庫（xscj）中也創(chuàng)建一個學(xué)生

9、情況表，表名xs。輸入以下命令：USE xscjCREATE TABLE xs( 學(xué)號 char(6) not null primary key, 姓名 char(8) not null, 專業(yè)名 char(10) null, 性別 tinyint(1)not null default 1, 出生日期date not null, 總學(xué)分 tinyint(1) null, 照片 blob null, 備注 text null) engine = innodb;3.2.1 3.2.1 創(chuàng)建表創(chuàng)建表用show tables命令顯示xscj數(shù)據(jù)庫中產(chǎn)生了學(xué)生（xs）表，用describe xs命令可以

10、顯示xs表的結(jié)構(gòu)。如圖3.1所示。3.2.1 3.2.1 創(chuàng)建表創(chuàng)建表2復(fù)制現(xiàn)成的表復(fù)制現(xiàn)成的表除了全新創(chuàng)建，用戶也可直接復(fù)制數(shù)據(jù)庫中已有表的結(jié)構(gòu)和數(shù)據(jù)，用這種方式構(gòu)建一個表，十分方便、快捷。語法格式：語法格式：CREATE TEMPORARY TABLE IF NOT EXISTS 表名 ( ) LIKE 已有表名 | AS ( 表達(dá)式 );【例3.4】在mytest數(shù)據(jù)庫中，用復(fù)制的方式創(chuàng)建一個名為user_copy1的表，表結(jié)構(gòu)直接取自user表；另再創(chuàng)建一個名為user_copy2的表，其結(jié)構(gòu)和內(nèi)容（數(shù)據(jù)）都取自user表。（1）創(chuàng)建）創(chuàng)建user_copy1表：表：USE mytes

11、tCREATE TABLE user_copy1 LIKE user;（2）創(chuàng)建）創(chuàng)建user_copy2表：表：CREATE TABLE user_copy2 AS (select * from user);3.2.1 3.2.1 創(chuàng)建表創(chuàng)建表執(zhí)行過程及結(jié)果，如圖3.2所示。3.2.1 3.2.1 創(chuàng)建表創(chuàng)建表查詢user_copy1表中沒有記錄，而user_copy2表中包含user表中所有記錄。如圖3.3所示。3.2.2 3.2.2 修改表修改表1修改表結(jié)構(gòu)修改表結(jié)構(gòu)ALTER TABLE用于更改原有表的結(jié)構(gòu)。例如，可以增加（刪減）列、創(chuàng)建（取消）索引、更改原有列的類型、重新命名列或表，

12、還可以更改表的評注和表的類型。語法格式：語法格式：ALTER IGNORE TABLE 表名 選項(xiàng) .選項(xiàng)=： ADD COLUMN 列定義 FIRST | AFTER 列名 /*添加列*/ | ALTER COLUMN 列名SET DEFAULT literal | DROP DEFAULT /*修改默認(rèn)值*/ | CHANGE COLUMN 列名 原列名 FIRST|AFTER 列名 /*列名重定義*/ | MODIFY COLUMN 列定義 FIRST | AFTER 列名 /*修改列數(shù)據(jù)類型*/ | DROP COLUMN 列名/*刪除列*/ | RENAME TO 新表名/*重命名該

13、表*/ | ORDER BY 列名/*排序*/ | CONVERT TO CHARACTER SET 字符集名 COLLATE 校對規(guī)則名 /*將字符集轉(zhuǎn)換為二進(jìn)制*/ | DEFAULT CHARACTER SET 字符集名 COLLATE 校對規(guī)則名 /*修改默認(rèn)字符集*/ | 表選項(xiàng) | 列或表中索引項(xiàng)的增、刪、改（詳細(xì)見索引一章詳細(xì)見索引一章）3.2.2 3.2.2 修改表修改表說明：說明：lIGNORE：是MySQL相對于標(biāo)準(zhǔn)SQL的擴(kuò)展。lADDCOLUMN子句：向表中增加新列。lFIRST | AFTER 列名：表示在某列的前或后添加，不指定則添加到最后。lALTER COLUM

14、N子句：修改表中指定列的默認(rèn)值。lCHANGE COLUMN子句：修改列的名稱。lMODIFY COLUMN子句：修改指定列的數(shù)據(jù)類型。lDROP子句：從表中刪除列或約束。lRENAME子句：修改該表的表名。lORDER BY子句：用于在創(chuàng)建新表時，讓各行按一定的順序排列。l表選項(xiàng)：修改表選項(xiàng)，具體定義與CREATE TABLE語句中一樣。3.2.2 3.2.2 修改表修改表【例3.5】在xscj數(shù)據(jù)庫的xs表中，增加“獎學(xué)金等級”一列，并將表中的“姓名”列刪除。user xscjalter table xs add 獎學(xué)金等級 tinyint null, drop column 姓名;執(zhí)行后

15、，xs表的結(jié)構(gòu)如圖3.4所示。3.2.2 3.2.2 修改表修改表為了在后面演示表記錄操作的方便，此處完成后要及時地將xs表改回原樣，語句如下：alter table xs add 姓名 char(8) not null after 學(xué)號, drop column 獎學(xué)金等級;3.2.2 3.2.2 修改表修改表2更改表名更改表名除了上面的ALTER TABLE命令，還可以直接用RENAME TABLE語句來更改表的名字。語法格式：語法格式：RENAME TABLE 老表名 TO 新表名 . 【例3.6】將mytest數(shù)據(jù)庫usera表重命名為user1，user_copy2表重命名為user

16、2。rename table usera to user1,user_copy2 to user2;3.2.3 3.2.3 刪除表刪除表需要刪除一個表時可以使用DROP TABLE語句。語法格式：語法格式：DROP TEMPORARY TABLE IF EXISTS 表名 .這個命令將表的描述、表的完整性約束、索引及和表相關(guān)的權(quán)限等一并刪除?！纠?.7】刪除表uesra。drop table if exists usera;3.2.4 MySQL3.2.4 MySQL表結(jié)構(gòu)的特點(diǎn)表結(jié)構(gòu)的特點(diǎn)1空值（空值（NULL）的概念）的概念空值通常表示未知、不可用或?qū)⒃谝院筇砑拥臄?shù)據(jù)。若一個列允許為空值，

17、則向表中輸入記錄值時可不為該列給出具體值；而一個列若不允許為空值，則在輸入時必須給出該列的具體值。2列的標(biāo)志（列的標(biāo)志（IDENTITY）屬性）屬性對任何表都可創(chuàng)建包含系統(tǒng)所生成序號值的一個標(biāo)志列，該序號值唯一標(biāo)志表中的一列，可以作為鍵值。每個表只能有一個列設(shè)置為標(biāo)志屬性，該列只能是decimal、int、numeric、smallint、bigint 或 tinyint 數(shù)據(jù)類型。定義標(biāo)志屬性時，可指定其種子（即起始）值、增量值，二者的默認(rèn)值均為 1。系統(tǒng)自動更新標(biāo)志列值，標(biāo)志列值不允許空值。3.2.4 MySQL3.2.4 MySQL表結(jié)構(gòu)的特點(diǎn)表結(jié)構(gòu)的特點(diǎn)3隱含地改變列類型隱含地改變列類

18、型（1）長度小于4的varchar被改變?yōu)閏har。（2）如果在一個表中的任何列有可變長度，結(jié)果使整個行是變長的。因此，如果一張表包含任何變長的列（varchar、text或Blob），所有大于3個字符的char列被改變?yōu)関archar列。這在任何方面都不影響用戶如何使用列。（3）timestamp的顯示尺寸必須是偶數(shù)且在214的范圍內(nèi)。如果指定0顯示尺寸或比14大，尺寸被強(qiáng)制為14。從113范圍內(nèi)的奇數(shù)值尺寸被強(qiáng)制為下一個更大的偶數(shù)。（4）不能在一個timestamp列里面存儲一個NULL，將它設(shè)為NULL默認(rèn)為當(dāng)前的日期和時間。3.3 3.3 表記錄的操作表記錄的操作3.3.1 插入記錄插

19、入記錄1插入新記錄插入新記錄向表中插入全新的記錄用INSERT語句，語法格式：INSERT LOW_PRIORITY | DELAYED | HIGH_PRIORITY IGNORE INTO 表名 (列名,.) VALUES (expr | DEFAULT,.),(.),. | SET 列名=expr | DEFAULT, . ON DUPLICATE KEY UPDATE 列名=expr, . 3.3.1 3.3.1 插入記錄插入記錄說明：說明：l列名：需要插入數(shù)據(jù)的列名。lVALUES子句：包含各列需要插入的數(shù)據(jù)清單，數(shù)據(jù)的順序要與列的順序相對應(yīng)。lLOW_PRIORITY：可以使用在I

20、NSERT、DELETE和UPDATE等操作中，當(dāng)原有客戶端正在讀取數(shù)據(jù)時，延遲操作的執(zhí)行，直到?jīng)]有其他客戶端從表中讀取為止。lDELAYED：若使用此關(guān)鍵字，則服務(wù)器會把待插入的行放到一個緩沖器中，而發(fā)送INSERT DELAYED語句的客戶端會繼續(xù)運(yùn)行。lHIGH_PRIORITY：可以使用在SELECT和INSERT操作中，使操作優(yōu)先執(zhí)行。lIGNORE：使用此關(guān)鍵字，在執(zhí)行語句時出現(xiàn)的錯誤就會被當(dāng)作警告處理。lON DUPLICATE KEY UPDATE：使用此選項(xiàng)插入行后，若導(dǎo)致UNIQUE KEY或PRIMARY KEY出現(xiàn)重復(fù)值，則根據(jù)UPDATE后的語句修改舊行（使用此選項(xiàng)時

21、DELAYED被忽略）。lSET子句：SET子句用于給列指定值，使用SET子句時表名的后面省略列名。3.3.1 3.3.1 插入記錄插入記錄【例3.8】向?qū)W生成績數(shù)據(jù)庫（xscj）的表xs（表中列包括學(xué)號、姓名、專業(yè)名、性別、出生日期、總學(xué)分、照片、備注）中插入如下一行：081101，王林，計(jì)算機(jī)，1，1994-02-10，50，NULL，NULL使用下列語句：use xscjinsert into xs values(081101, 王林, 計(jì)算機(jī), 1, 1994-02-10, 50, null, null);3.3.1 3.3.1 插入記錄插入記錄若表xs中專業(yè)的默認(rèn)值為“計(jì)算機(jī)”，照片

22、、備注默認(rèn)值為NULL，插入上例那行數(shù)據(jù)也可以使用如下命令：insert into xs (學(xué)號, 姓名, 性別, 出生日期, 總學(xué)分) values(081101, 王林, 1, 1994-02-10, 50);也與下面這個命令的效果相同：insert into xs values(081101, 王林, default, 1, 1994-02-10, 50, null, null);當(dāng)然，也可以使用SET子句來實(shí)現(xiàn)：insert into xs set 學(xué)號=081101, 姓名=王林, 專業(yè)=default, 性別=1, 出生日期=1994-02-10, 總學(xué)分=50;執(zhí)行結(jié)果如圖3.5

23、所示。3.3.1 3.3.1 插入記錄插入記錄2. 從已有表中插入新記錄從已有表中插入新記錄使用INSERT INTOSELECT，可以快速地從一個或多個已有的表記錄向表中插入多個行。語法格式如下：INSERT LOW_PRIORITY | HIGH_PRIORITY IGNORE INTO 表名 (列名,.) SELECT語句 ON DUPLICATE KEY UPDATE 列名=expr, . 3.3.1 3.3.1 插入記錄插入記錄【例3.9】將mytest數(shù)據(jù)庫user表記錄插入到user1表中。user mytestinsert into user1 select * from us

24、er;命令執(zhí)行前后的效果如圖3.6所示。 3.3.1 3.3.1 插入記錄插入記錄2替換舊記錄替換舊記錄REPLACE語句可以在插入數(shù)據(jù)之前將與新記錄沖突的舊記錄刪除，從而使新記錄能夠替換舊記錄，正常插入。REPLACE語句格式與INSERT相同?！纠?.10】若上例中的記錄行已經(jīng)插入，其中學(xué)號為主鍵（PRIMARY KEY），現(xiàn)在想再插入下列一行記錄：081101，劉華，通信工程，1，1995-03-08，48，NULL，NULL若直接使用INSERT語句，會產(chǎn)生如下錯誤：使用REPLACE語句，則可以成功插入：3.3.1 3.3.1 插入記錄插入記錄3插入圖片插入圖片MySQL還支持圖片的

25、插入，圖片一般可以以路徑的形式來存儲，即插入圖片可以采用插入圖片的存儲路徑的方式。當(dāng)然也可以直接插入圖片本身，只要用LOAD_FILE函數(shù)即可。3.3.1 3.3.1 插入記錄插入記錄【例3.11】向xs表中插入一行記錄：081102，程明，計(jì)算機(jī)，1，1995-02-01，50，picture.jpg，NULL其中，照片路徑為D: IMAGEpicture.jpg。使用如下語句：insert into xs values(081102, 程明, 計(jì)算機(jī), 1, 1995-02-01, 50, D:IMAGE picture.jpg, null);也可使用這個語句直接存儲圖片本身：insert

26、 into xs values(081102, 程明, 計(jì)算機(jī), 1, 1995-02-01, 50, load_file( D: IMAGEpicture.jpg), null);執(zhí)行結(jié)果為：3.3.2 3.3.2 修改記錄修改記錄1修改單個表修改單個表語法格式：語法格式：UPDATE LOW_PRIORITY IGNORE 表名 SET 列名1=expr1 , 列名2=expr2 . WHERE 條件 ORDER BY . LIMIT row_count3.3.2 3.3.2 修改記錄修改記錄【例3.12】將學(xué)生成績數(shù)據(jù)庫（xscj）的學(xué)生（xs）表中的所有學(xué)生的總學(xué)分都增加10。將姓名為

27、“劉華”的同學(xué)的備注填寫為“輔修計(jì)算機(jī)專業(yè)”，學(xué)號改為“081250”。update xs set 總學(xué)分 = 總學(xué)分 + 10;update xs set 學(xué)號 = 081250 , 備注 = 輔修計(jì)算機(jī)專業(yè) where 姓名 = 劉華;select 學(xué)號, 姓名, 總學(xué)分, 備注 from xs;執(zhí)行結(jié)果為：3.3.2 3.3.2 修改記錄修改記錄2修改多個表修改多個表語法格式：語法格式：UPDATE LOW_PRIORITY IGNORE 表名,表名. SET 列名1=expr1 , 列名2=expr2 . WHERE 條件3.3.2 3.3.2 修改記錄修改記錄【例3.13】 myte

28、st數(shù)據(jù)庫表user和表user2中都有兩個字段id int(11)，password varchar(10)，其中id為主鍵。當(dāng)表user中id值與user2中id值相同時，將表user中對應(yīng)的password值修改為“11111111”，將表user2中對應(yīng)的password值改為“22222222”。user mytestupdate user, user2 set user.password = 11111111 , user2.password = 22222222 where user.id = user2.id;修改后的結(jié)果如圖3.7所示。3.3.3 3.3.3 刪除記錄刪除記錄

29、1刪除滿足條件的行刪除滿足條件的行使用DELETE語句刪除表中滿足條件的記錄行。從單個表中刪除，語法格式：DELETE LOW_PRIORITY QUICK IGNORE FROM 表名 WHERE 條件 ORDER BY . LIMIT row_count說明：說明：lQUICK修飾符：可以加快部分種類的刪除操作的速度。lFROM子句：用于說明從何處刪除數(shù)據(jù)，表名為要刪除數(shù)據(jù)的表名。lWHERE子句：指定的刪除記錄條件。lORDER BY子句：各行按照子句中指定的順序進(jìn)行刪除，此子句只在與LIMIT聯(lián)用時才起作用。lLIMIT子句：用于告知服務(wù)器，在控制命令被返回到客戶端前，被刪除的行的最大

30、值。3.3.3 3.3.3 刪除記錄刪除記錄【例3.14】刪除mytest數(shù)據(jù)庫中user2表“周何駿”記錄刪除。use mytestdelete from person where username = 周何駿;或者：delete from xs where id=2;3.3.3 3.3.3 刪除記錄刪除記錄2從多個表中刪除行從多個表中刪除行刪除操作若要在多個表中進(jìn)行，其語法格式如下：DELETE LOW_PRIORITY QUICK IGNORE 表名.* , 表名.* . FROM table_references WHERE where_definition或：DELETE LOW_P

31、RIORITY QUICK IGNORE FROM 表名.* , 表名.* . USING table_references WHERE where_definition3.3.3 3.3.3 刪除記錄刪除記錄【例3.15】 刪除user1中id值等于user的id值的所有行和user2中id值等于user的id值的所有行，使用如下語句：DELETE user1, user2 FROM user1, user2, user WHERE user1.id=user.id AND user2.id=user.id;命令執(zhí)行結(jié)果如圖3.8所示。3.3.3 3.3.3 刪除記錄刪除記錄3清除表數(shù)據(jù)清除表數(shù)據(jù)使用TRUNCATE TABLE語句將刪除指定表中的所有數(shù)據(jù)，因此也稱其為清除表數(shù)據(jù)語句，其語法格式如下：TRUNCATE TABLE 表名3.4 MySQL3.4 MySQL數(shù)據(jù)庫信息顯示數(shù)據(jù)庫信息顯示1SHOW語句語句lSHOW tables或SHOW tables from database_name：顯示當(dāng)前數(shù)據(jù)庫中所有表的名稱。lSHOW databases：顯示MySQL中所有數(shù)據(jù)庫的名稱。lSHOW columns from table_name from databas