MySQL数据库应用实战教程（慕课版）（第2版）实训指导-3-3 表操作

实训案例名称：表操作一、任务介绍MySQL表操作是使用频率最高的操作。用户可以创建数据库表，查看数据库表，向数据库表里插入数据、更新数据、删除数据等，同时可以修改表结构、复制表、使用临时表。二、实现步骤1.创建表#使用用户名root和相应密码，连接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#创建shop数据库mysql>CREATEDATABASEshop;QueryOK,1rowaffected(0.00sec)#使用shop数据库mysql>USEshop;Databasechanged#创建用户表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.查看表结构#使用用户名root和相应密码，连接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop数据库mysql>USEshop;Databasechanged#查看用户表user的表结构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.修改表结构#使用用户名root和相应密码，连接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop数据库mysql>USEshop;Databasechanged#为用户表user增加一列phonemysql>ALTERTABLEuserADDphonechar(11);QueryOK,0rowsaffected(0.03sec)Records:0Duplicates:0Warnings:0#查看用户表user的表结构，其中多出来一列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.复制表（1）复制表结构、数据、主键、索引①基于user表的结构和数据进行复制，user表有主键和索引，如图3.1和图3.2所示。图3.1user表主键图3.2user表索引②在shop数据库的user表里添加一条记录，命令操作如下所示。#使用用户名root和相应密码，连接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop数据库mysql>USEshop;Databasechanged#向用户表user中插入数据mysql>INSERTINTOuserVALUES(1,'kevin','男',20,'123456'),(2,'tom','男','30','123456');QueryOK,1rowaffected(0.07sec)#查询用户表user中的数据mysql>SELECT*FROMuser;+----+-------+------+------+----------+|id

|name

|sex

|age

|password

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

|kevin

|男

|20

|123456

||2

|tom

|男

|30

|123456

|+----+-------+------+------+----------+2rowsinset(0.00sec)mysql>③将user表的表结构、索引、主键复制到新的user_new表里，命令操作如下所示。#使用用户名root和相应密码，连接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop数据库mysql>USEshop;Databasechanged#使用like将用户表user的表结构、索引、主键复制到user_new表中mysql>CREATETABLEuser_newlikeuser;QueryOK,0rowsaffected(0.48sec)#查询user_new表数据，mysql>SELECT*FROMuser_new;Emptyset(0.12sec)mysql>=4\*GB3④创建新的user_new表，只复制了表结构、主键和索引，并没有数据，把旧的表user的数据复制到新的表user_new，命令操作如下所示。#使用用户名root和相应密码，连接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop数据库mysql>USEshop;Databasechanged#把用户表user的数据复制到user_new表中mysql>INSERTuser_newSELECT*FROMuser;QueryOK,2rowsaffected(0.13sec)#查询user_new表中的数据mysql>SELECT*FROMuser_new;+----+-------+------+------+----------+|id

|name

|sex

|age

|password

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

|kevin

|男

|20

|123456

||2

|tom

|男

|30

|123456

|+----+-------+------+------+----------+2rowsinset(0.00sec)mysql>（2）复制表结构、数据，不能复制主键、索引①将user表的表结构、索引、主键复制到新的user_new2表里，可以查看到user_new2表里已经复制进来数据了，命令操作如下所示。#使用用户名root和相应密码，连接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop数据库mysql>USEshop;Databasechanged#将用户表user的表结构、数据复制到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表的表结构、索引、主键复制到新的user_new3表里，可以查看到user_new3表里没有数据，命令操作如下所示。#使用用户名root和相应密码，连接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop数据库mysql>USEshop;Databasechanged#复制表结构，不复制数据mysql>CREATETABLEuser_new3SELECT*FROMuserWHERE0;QueryOK,0rowsaffected(0.67sec)Records:0Duplicates:0Warnings:0#查询user_new3表中的数据，数据为空mysql>SELECT*FROMuser_new3;Emptyset(0.00sec)mysql>4.临时表和内存表#使用用户名root和相应密码，连接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop数据库mysql>USEshop;Databasechanged#创建临时表mysql>CREATEtemporaryTABLEtmp1(idintnotnull);QueryOK,0rowsaffected(0.26sec)#查看创建的表mysql>SHOWCREATETABLEtmp1;+-------+------------------------------------------------------------------+|+-------+------------------------------------------------------------------+|tmp1

|CREATETEMPORARYTABL