實(shí)訓(xùn)案例名稱：表操作一、任務(wù)介紹MySQL表操作是使用頻率最高的操作。用戶可以創(chuàng)建數(shù)據(jù)庫(kù)表，查看數(shù)據(jù)庫(kù)表，向數(shù)據(jù)庫(kù)表里插入數(shù)據(jù)、更新數(shù)據(jù)、刪除數(shù)據(jù)等，同時(shí)可以修改表結(jié)構(gòu)、復(fù)制表、使用臨時(shí)表。二、實(shí)現(xiàn)步驟1.創(chuàng)建表#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#創(chuàng)建shop數(shù)據(jù)庫(kù)mysql>CREATEDATABASEshop;QueryOK,1rowaffected(0.00sec)#使用shop數(shù)據(jù)庫(kù)mysql>USEshop;Databasechanged#創(chuàng)建用戶表usermysql>CREATETABLEuser(idint(10)unsignednotnullauto_increment,namevarchar(25),sexvarchar(5),ageint(10),passwordvarchar(25),primarykey(id))engine=InnoDB;QueryOK,0rowsaffected(0.90sec)#查看表mysql>SHOWtables;+-----------------------+|Tables_in_shop

|+-----------------------+|user

|+-----------------------+1rowinset(0.04sec)mysql>2.查看表結(jié)構(gòu)#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫(kù)mysql>USEshop;Databasechanged#查看用戶表user的表結(jié)構(gòu)mysql>DESCuser;+----------+--------------+------+-----+---------+----------------+|Field |Type

|Null

|Key

|Default

|Extra |+----------+--------------+------+-----+---------+----------------+|id |intunsigned

|NO

|PRI

|NULL

|auto_increment ||name |varchar(25)

|YES

|

|NULL

| ||sex |varchar(5)

|YES

|

|NULL

| ||age |int

|YES

|

|NULL

| ||password|varchar(25)

|YES

|

|NULL

| |+----------+--------------+------+-----+---------+----------------+5rowsinset(0.02sec)mysql>3.修改表結(jié)構(gòu)#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫(kù)mysql>USEshop;Databasechanged#為用戶表user增加一列phonemysql>ALTERTABLEuserADDphonechar(11);QueryOK,0rowsaffected(0.03sec)Records:0Duplicates:0Warnings:0#查看用戶表user的表結(jié)構(gòu)，其中多出來一列phonemysql>DESCuser;+----------+--------------+------+-----+---------+----------------+|Field

|Type

|Null

|Key

|Default

|Extra |+----------+--------------+------+-----+---------+----------------+|id

|intunsigned

|NO

|PRI

|NULL

|auto_increment ||name

|varchar(25)

|YES

|

|NULL

| ||sex

|varchar(5)

|YES

|

|NULL

| ||age

|int

|YES

|

|NULL

| ||password

|varchar(25)

|YES

|

|NULL

| ||phone

|char(11)

|YES

|

|NULL

| |+----------+--------------+------+-----+---------+----------------+6rowsinset(0.01sec)#為用戶表user刪除一列phonemysql>ALTERTABLEuserDROPphone;QueryOK,0rowsaffected(0.06sec)Records:0Duplicates:0Warnings:0mysql>DESCuser;+----------+--------------+------+-----+---------+----------------+|Field

|Type

|Null

|Key|Default

|Extra |+----------+--------------+------+-----+---------+----------------+|id

|intunsigned

|NO

|PRI|NULL

|auto_increment||name

|varchar(25)

|YES

||NULL

| ||sex

|varchar(5)

|YES

||NULL

| ||age

|int

|YES

||NULL

| ||password

|varchar(25)

|YES

||NULL

| |+----------+--------------+------+-----+---------+----------------+5rowsinset(0.00sec)4.復(fù)制表（1）復(fù)制表結(jié)構(gòu)、數(shù)據(jù)、主鍵、索引①基于user表的結(jié)構(gòu)和數(shù)據(jù)進(jìn)行復(fù)制，user表有主鍵和索引，如圖3.1和圖3.2所示。圖3.1user表主鍵圖3.2user表索引②在shop數(shù)據(jù)庫(kù)的user表里添加一條記錄，命令操作如下所示。#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫(kù)mysql>USEshop;Databasechanged#向用戶表user中插入數(shù)據(jù)mysql>INSERTINTOuserVALUES(1,'kevin','男',20,'123456'),(2,'tom','男','30','123456');QueryOK,1rowaffected(0.07sec)#查詢用戶表user中的數(shù)據(jù)mysql>SELECT*FROMuser;+----+-------+------+------+----------+|id

|name

|sex

|age

|password

|+----+-------+------+------+----------+|1

|kevin

|男

|20

|123456

||2

|tom

|男

|30

|123456

|+----+-------+------+------+----------+2rowsinset(0.00sec)mysql>③將user表的表結(jié)構(gòu)、索引、主鍵復(fù)制到新的user_new表里，命令操作如下所示。#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫(kù)mysql>USEshop;Databasechanged#使用like將用戶表user的表結(jié)構(gòu)、索引、主鍵復(fù)制到user_new表中mysql>CREATETABLEuser_newlikeuser;QueryOK,0rowsaffected(0.48sec)#查詢user_new表數(shù)據(jù)，mysql>SELECT*FROMuser_new;Emptyset(0.12sec)mysql>=4\*GB3④創(chuàng)建新的user_new表，只復(fù)制了表結(jié)構(gòu)、主鍵和索引，并沒有數(shù)據(jù)，把舊的表user的數(shù)據(jù)復(fù)制到新的表user_new，命令操作如下所示。#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫(kù)mysql>USEshop;Databasechanged#把用戶表user的數(shù)據(jù)復(fù)制到user_new表中mysql>INSERTuser_newSELECT*FROMuser;QueryOK,2rowsaffected(0.13sec)#查詢user_new表中的數(shù)據(jù)mysql>SELECT*FROMuser_new;+----+-------+------+------+----------+|id

|name

|sex

|age

|password

|+----+-------+------+------+----------+|1

|kevin

|男

|20

|123456

||2

|tom

|男

|30

|123456

|+----+-------+------+------+----------+2rowsinset(0.00sec)mysql>（2）復(fù)制表結(jié)構(gòu)、數(shù)據(jù)，不能復(fù)制主鍵、索引①將user表的表結(jié)構(gòu)、索引、主鍵復(fù)制到新的user_new2表里，可以查看到user_new2表里已經(jīng)復(fù)制進(jìn)來數(shù)據(jù)了，命令操作如下所示。#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫(kù)mysql>USEshop;Databasechanged#將用戶表user的表結(jié)構(gòu)、數(shù)據(jù)復(fù)制到user_new2表中mysql>CREATETABLEuser_new2SELECT*FROMuser;QueryOK,2rowsaffected(0.68sec)Records:2Duplicates:0Warnings:0#查詢user_new2表mysql>SELECT*FROMuser_new2;+----+-------+------+------+----------+|id

|name

|sex

|age

|password

|+----+-------+------+------+----------+|1|kevin

|男

|20

|123456

||2|tom

|男

|30

|123456

|+----+-------+------+------+----------+2rowsinset(0.00sec)mysql>②將user表的表結(jié)構(gòu)、索引、主鍵復(fù)制到新的user_new3表里，可以查看到user_new3表里沒有數(shù)據(jù)，命令操作如下所示。#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫(kù)mysql>USEshop;Databasechanged#復(fù)制表結(jié)構(gòu)，不復(fù)制數(shù)據(jù)mysql>CREATETABLEuser_new3SELECT*FROMuserWHERE0;QueryOK,0rowsaffected(0.67sec)Records:0Duplicates:0Warnings:0#查詢user_new3表中的數(shù)據(jù)，數(shù)據(jù)為空mysql>SELECT*FROMuser_new3;Emptyset(0.00sec)mysql>4.臨時(shí)表和內(nèi)存表#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫(kù)mysql>USEshop;Databasechanged#創(chuàng)建臨時(shí)表mysql>CREATEtemporaryTABLEtmp1(idintnotnull);QueryOK,0rowsaffected(0.26sec)#查看創(chuàng)建的表mysql>SHOWCREATETABLEtmp1;+-------+------------------------------------------------------------------+|+-------+------------------------------------------------------------------+|tmp1

|CREATETEMPORARYTABL