这篇文章主要介绍mysql中数据表基本操作的示例，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

案例:创建数据库company，按照下面两个表给出的表结构在company数据库中创建两个数据表offices和employees，按照操作过程完成数据表的基本操作。

操作过程如下：

(1)：登录MySQL。

mysql-hlocalhost-uroot-p

打开windows命令行，输入登录用户名和密码：

C:\Users\Hudie>mysql-hlocalhost-uroot-pEnterpassword:********WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis19Serverversion:8.0.16MySQLCommunityServer-GPLCopyright(c)2000,2019,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>_

登录成功，可以输入SQL语句进行操作。

(2)：创建数据库company。

createdatabasecompany;

mysql>createdatabasecompany;QueryOK,1rowaffected(0.06sec)

创建成功后，在company数据库中创建数据表，必须先选择该数据库。SQL语句如下：

mysql>usecompany;Databasechanged

(3):创建表offices。

createtableoffices

mysql>createtableoffices->(->officeCodeint(10)notnullunique,->cityvarchar(50)notnull,->addressvarchar(50)notnull,->countryvarchar(50)notnull,->postalCodevarchar(15)notnull,->primarykey(officeCode)->);QueryOK,0rowsaffected(0.14sec)mysql>showtables;+-------------------+|Tables_in_company|+-------------------+|offices|+-------------------+1rowinset(0.00sec)

(4)：创建表enployees。

createtableemployees

mysql>createtableemployees->(->employeeNumberint(11)notnullprimarykeyauto_increment,->lastNameevarchar(50)notnull,->firstNamevarchar(50)notnull,->mobilevarchar(25)notnull,->officeCodeint(10)notnull,->jobTitlevarchar(50)notnull,->birthdatetime,->nothvarchar(25),->sexvarchar(5),->constraintoffice_fkforeignkey(officeCode)referencesoffices(officeCode)->);QueryOK,0rowsaffected(0.14sec)mysql>showtables;+-------------------+|Tables_in_company|+-------------------+|employees||offices|+-------------------+2rowsinset(0.01sec)

创建成功，查看两个表的结构:

mysql>descoffices;+------------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+------------+-------------+------+-----+---------+-------+|officeCode|int(10)|NO|PRI|NULL|||city|varchar(50)|NO||NULL|||address|varchar(50)|NO||NULL|||country|varchar(50)|NO||NULL|||postalCode|varchar(15)|NO||NULL||+------------+-------------+------+-----+---------+-------+5rowsinset(0.06sec)mysql>descemployees;+----------------+-------------+------+-----+---------+----------------+|Field|Type|Null|Key|Default|Extra|+----------------+-------------+------+-----+---------+----------------+|employeeNumber|int(11)|NO|PRI|NULL|auto_increment||lastNamee|varchar(50)|NO||NULL|||firstName|varchar(50)|NO||NULL|||mobile|varchar(25)|NO||NULL|||officeCode|int(10)|NO|MUL|NULL|||jobTitle|varchar(50)|NO||NULL|||birth|datetime|YES||NULL|||noth|varchar(25)|YES||NULL|||sex|varchar(5)|YES||NULL||+----------------+-------------+------+-----+---------+----------------+9rowsinset(0.01sec)

(5)：将表employees的mobile字段修改到officeCode字段后面。

altertableemployeesmodifymobilevarchar(25)afterofficeCode;

mysql>altertableemployeesmodifymobilevarchar(25)afterofficeCode;QueryOK,0rowsaffected(0.18sec)Records:0Duplicates:0Warnings:0mysql>descemployees;+----------------+-------------+------+-----+---------+----------------+|Field|Type|Null|Key|Default|Extra|+----------------+-------------+------+-----+---------+----------------+|employeeNumber|int(11)|NO|PRI|NULL|auto_increment||lastNamee|varchar(50)|NO||NULL|||firstName|varchar(50)|NO||NULL|||officeCode|int(10)|NO|MUL|NULL|||mobile|varchar(25)|YES||NULL|||jobTitle|varchar(50)|NO||NULL|||birth|datetime|YES||NULL|||noth|varchar(25)|YES||NULL|||sex|varchar(5)|YES||NULL||+----------------+-------------+------+-----+---------+----------------+9rowsinset(0.01sec)

(6)：将表employees的birth字段改名为employee_birth。

altertableemployeeschangebirthemployee_birthdatetime;

mysql>altertableemployeeschangebirthemployee_birthdatetime;QueryOK,0rowsaffected(0.03sec)Records:0Duplicates:0Warnings:0mysql>descemployees;+----------------+-------------+------+-----+---------+----------------+|Field|Type|Null|Key|Default|Extra|+----------------+-------------+------+-----+---------+----------------+|employeeNumber|int(11)|NO|PRI|NULL|auto_increment||lastNamee|varchar(50)|NO||NULL|||firstName|varchar(50)|NO||NULL|||officeCode|int(10)|NO|MUL|NULL|||mobile|varchar(25)|YES||NULL|||jobTitle|varchar(50)|NO||NULL|||employee_birth|datetime|YES||NULL|||noth|varchar(25)|YES||NULL|||sex|varchar(5)|YES||NULL||+----------------+-------------+------+-----+---------+----------------+9rowsinset(0.00sec)

(7)：修改sex字段，设置数据类型为char(1),非空约束。

altertableemployeesmodifysexchar(1)notnull;

mysql>altertableemployeesmodifysexchar(1)notnull;QueryOK,0rowsaffected(0.20sec)Records:0Duplicates:0Warnings:0mysql>descemployees;+----------------+-------------+------+-----+---------+----------------+|Field|Type|Null|Key|Default|Extra|+----------------+-------------+------+-----+---------+----------------+|employeeNumber|int(11)|NO|PRI|NULL|auto_increment||lastNamee|varchar(50)|NO||NULL|||firstName|varchar(50)|NO||NULL|||officeCode|int(10)|NO|MUL|NULL|||mobile|varchar(25)|YES||NULL|||jobTitle|varchar(50)|NO||NULL|||employee_birth|datetime|YES||NULL|||noth|varchar(25)|YES||NULL|||sex|char(1)|NO||NULL||+----------------+-------------+------+-----+---------+----------------+9rowsinset(0.01sec)

(8)：删除字段noth。

altertableemployeesdropnoth;

mysql>altertableemployeesdropnoth;QueryOK,0rowsaffected(0.15sec)Records:0Duplicates:0Warnings:0mysql>descemployees;+----------------+-------------+------+-----+---------+----------------+|Field|Type|Null|Key|Default|Extra|+----------------+-------------+------+-----+---------+----------------+|employeeNumber|int(11)|NO|PRI|NULL|auto_increment||lastNamee|varchar(50)|NO||NULL|||firstName|varchar(50)|NO||NULL|||officeCode|int(10)|NO|MUL|NULL|||mobile|varchar(25)|YES||NULL|||jobTitle|varchar(50)|NO||NULL|||employee_birth|datetime|YES||NULL|||sex|char(1)|NO||NULL||+----------------+-------------+------+-----+---------+----------------+8rowsinset(0.01sec)

(9):增加字段名favoriate_activity,数据类型为varchar(100)

altertableemployeesaddfavoriate_activityvarchar(100);

mysql>altertableemployeesaddfavoriate_activityvarchar(100);QueryOK,0rowsaffected(0.09sec)Records:0Duplicates:0Warnings:0mysql>descemployees;+--------------------+--------------+------+-----+---------+----------------+|Field|Type|Null|Key|Default|Extra|+--------------------+--------------+------+-----+---------+----------------+|employeeNumber|int(11)|NO|PRI|NULL|auto_increment||lastNamee|varchar(50)|NO||NULL|||firstName|varchar(50)|NO||NULL|||officeCode|int(10)|NO|MUL|NULL|||mobile|varchar(25)|YES||NULL|||jobTitle|varchar(50)|NO||NULL|||employee_birth|datetime|YES||NULL|||sex|char(1)|NO||NULL|||favoriate_activity|varchar(100)|YES||NULL||+--------------------+--------------+------+-----+---------+----------------+9rowsinset(0.00sec)

(10):删除主表offices

①删除表的外键约束:alter table employees drop foreign key office_fk;
②删除表offices:drop table offices;

mysql>altertableemployeesdropforeignkeyoffice_fk;QueryOK,0rowsaffected(0.03sec)Records:0Duplicates:0Warnings:0mysql>droptableoffices;QueryOK,0rowsaffected(0.03sec)mysql>showtables;+-------------------+|Tables_in_company|+-------------------+|employees|+-------------------+1rowinset(0.06sec)

(11):修改表employees存储引擎为MyISAM。

altertableemployeesENGINE=MyISAM;

mysql>altertableemployeesENGINE=MyISAM;QueryOK,0rowsaffected(0.17sec)Records:0Duplicates:0Warnings:0mysql>showcreatetableemployees\G***************************1.row***************************Table:employeesCreateTable:CREATETABLE`employees`(`employeeNumber`int(11)NOTNULLAUTO_INCREMENT,`lastNamee`varchar(50)NOTNULL,`firstName`varchar(50)NOTNULL,`officeCode`int(10)NOTNULL,`mobile`varchar(25)DEFAULTNULL,`jobTitle`varchar(50)NOTNULL,`employee_birth`datetimeDEFAULTNULL,`sex`char(1)NOTNULL,`favoriate_activity`varchar(100)DEFAULTNULL,PRIMARYKEY(`employeeNumber`),KEY`office_fk`(`officeCode`))ENGINE=MyISAMDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci1rowinset(0.00sec)

(12)将表employees名称修改为employees_info。

altertableemployeesrenameemployees_info;

mysql>altertableemployeesrenameemployees_info;QueryOK,0rowsaffected(0.07sec)mysql>showtables;+-------------------+|Tables_in_company|+-------------------+|employees_info|+-------------------+1rowinset(0.00sec)

以上是“mysql中数据表基本操作的示例”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注亿速云行业资讯频道！