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

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

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

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

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

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

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

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

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

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

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

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

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

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

15、，xs表的結構如圖3.4所示。3.2.2 3.2.2 修改表修改表為了在后面演示表記錄操作的方便，此處完成后要及時地將xs表改回原樣，語句如下：alter table xs add 姓名 char(8) not null after 學號, drop column 獎學金等級;3.2.2 3.2.2 修改表修改表2更改表名更改表名除了上面的ALTER TABLE命令，還可以直接用RENAME TABLE語句來更改表的名字。語法格式：語法格式：RENAME TABLE 老表名 TO 新表名 . 【例3.6】將mytest數據庫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 表名 .這個命令將表的描述、表的完整性約束、索引及和表相關的權限等一并刪除。【例3.7】刪除表uesra。drop table if exists usera;3.2.4 MySQL3.2.4 MySQL表結構的特點表結構的特點1空值（空值（NULL）的概念）的概念空值通常表示未知、不可用或將在以后添加的數據。若一個列允許為空值，

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

18、型（1）長度小于4的varchar被改變為char。（2）如果在一個表中的任何列有可變長度，結果使整個行是變長的。因此，如果一張表包含任何變長的列（varchar、text或Blob），所有大于3個字符的char列被改變為varchar列。這在任何方面都不影響用戶如何使用列。（3）timestamp的顯示尺寸必須是偶數且在214的范圍內。如果指定0顯示尺寸或比14大，尺寸被強制為14。從113范圍內的奇數值尺寸被強制為下一個更大的偶數。（4）不能在一個timestamp列里面存儲一個NULL，將它設為NULL默認為當前的日期和時間。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列名：需要插入數據的列名。lVALUES子句：包含各列需要插入的數據清單，數據的順序要與列的順序相對應。lLOW_PRIORITY：可以使用在I

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

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

22、、備注默認值為NULL，插入上例那行數據也可以使用如下命令：insert into xs (學號, 姓名, 性別, 出生日期, 總學分) values(081101, 王林, 1, 1994-02-10, 50);也與下面這個命令的效果相同：insert into xs values(081101, 王林, default, 1, 1994-02-10, 50, null, null);當然，也可以使用SET子句來實現：insert into xs set 學號=081101, 姓名=王林, 專業=default, 性別=1, 出生日期=1994-02-10, 總學分=50;執行結果如圖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數據庫user表記錄插入到user1表中。user mytestinsert into user1 select * from us

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

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

26、 into xs values(081102, 程明, 計算機, 1, 1995-02-01, 50, load_file( D: IMAGEpicture.jpg), null);執行結果為：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】將學生成績數據庫（xscj）的學生（xs）表中的所有學生的總學分都增加10。將姓名為

27、“劉華”的同學的備注填寫為“輔修計算機專業”，學號改為“081250”。update xs set 總學分 = 總學分 + 10;update xs set 學號 = 081250 , 備注 = 輔修計算機專業 where 姓名 = 劉華;select 學號, 姓名, 總學分, 備注 from xs;執行結果為：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數據庫表user和表user2中都有兩個字段id int(11)，password varchar(10)，其中id為主鍵。當表user中id值與user2中id值相同時，將表user中對應的password值修改為“11111111”，將表user2中對應的password值改為“22222222”。user mytestupdate user, user2 set user.password = 11111111 , user2.password = 22222222 where user.id = user2.id;修改后的結果如圖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子句：用于說明從何處刪除數據，表名為要刪除數據的表名。lWHERE子句：指定的刪除記錄條件。lORDER BY子句：各行按照子句中指定的順序進行刪除，此子句只在與LIMIT聯用時才起作用。lLIMIT子句：用于告知服務器，在控制命令被返回到客戶端前，被刪除的行的最大

30、值。3.3.3 3.3.3 刪除記錄刪除記錄【例3.14】刪除mytest數據庫中user2表“周何駿”記錄刪除。use mytestdelete from person where username = 周何駿;或者：delete from xs where id=2;3.3.3 3.3.3 刪除記錄刪除記錄2從多個表中刪除行從多個表中刪除行刪除操作若要在多個表中進行，其語法格式如下：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;命令執行結果如圖3.8所示。3.3.3 3.3.3 刪除記錄刪除記錄3清除表數據清除表數據使用TRUNCATE TABLE語句將刪除指定表中的所有數據，因此也稱其為清除表數據語句，其語法格式如下：TRUNCATE TABLE 表名3.4 MySQL3.4 MySQL數據庫信息顯示數據庫信息顯示1SHOW語句語句lSHOW tables或SHOW tables from database_name：顯示當前數據庫中所有表的名稱。lSHOW databases：顯示MySQL中所有數據庫的名稱。lSHOW columns from table_name from databas