已经学习了mysql的各种操作，如创建表、添加各种约束、产看表结构、以及修改和删除表。给出一个实战演练，全面复习一下数据表的基本操作基础。
案例:创建数据库company，按照下面两个表给出的表结构在company数据库中创建两个数据表offices和employees，按照操作过程完成数据表的基本操作。
（免费学习推荐：mysql视频教程）
操作过程如下：
(1)：登录mysql。
mysql -h localhost -u root -p
打开windows命令行，输入登录用户名和密码：
c:\users\hudie>mysql -h localhost -u root -penter password: ********welcome to the mysql monitor. commands end with ; or \g.your mysql connection id is 19server version: 8.0.16 mysql community server - gplcopyright (c) 2000, 2019, oracle and/or its affiliates. all rights reserved.oracle is a registered trademark of oracle corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners.type 'help;' or '\h' for help. type '\c' to clear the current input statement.mysql>_
登录成功，可以输入sql语句进行操作。
(2)：创建数据库company。
create database company;
mysql> create database company;query ok, 1 row affected (0.06 sec)
创建成功后，在company数据库中创建数据表，必须先选择该数据库。sql语句如下：
mysql> use company;database changed
(3):创建表offices。
create table offices
mysql> create table offices -> ( -> officecode int(10) not null unique, -> city varchar(50) not null, -> address varchar(50) not null, -> country varchar(50) not null, -> postalcode varchar(15) not null, -> primary key (officecode) -> );query ok, 0 rows affected (0.14 sec)mysql> show tables;+-------------------+| tables_in_company |+-------------------+| offices |+-------------------+1 row in set (0.00 sec)
(4)：创建表enployees。
create table employees
mysql> create table employees -> ( -> employeenumber int(11) not null primary key auto_increment, -> lastnamee varchar(50) not null, -> firstname varchar(50) not null, -> mobile varchar(25) not null, -> officecode int (10) not null, -> jobtitle varchar(50) not null, -> birth datetime, -> noth varchar(25), -> sex varchar(5), -> constraint office_fk foreign key(officecode) references offices(officecode) -> );query ok, 0 rows affected (0.14 sec)mysql> show tables;+-------------------+| tables_in_company |+-------------------+| employees || offices |+-------------------+2 rows in set (0.01 sec)
创建成功，查看两个表的结构:
mysql> desc offices;+------------+-------------+------+-----+---------+-------+| 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 | |+------------+-------------+------+-----+---------+-------+5 rows in set (0.06 sec)mysql> desc employees;+----------------+-------------+------+-----+---------+----------------+| 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 | |+----------------+-------------+------+-----+---------+----------------+9 rows in set (0.01 sec)
(5)：将表employees的mobile字段修改到officecode字段后面。
alter table employees modify mobile varchar(25) after officecode;
mysql> alter table employees modify mobile varchar(25) after officecode;query ok, 0 rows affected (0.18 sec)records: 0 duplicates: 0 warnings: 0mysql> desc employees;+----------------+-------------+------+-----+---------+----------------+| 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 | |+----------------+-------------+------+-----+---------+----------------+9 rows in set (0.01 sec)
(6)：将表employees的birth字段改名为employee_birth。
alter table employees change birth employee_birth datetime;
mysql> alter table employees change birth employee_birth datetime;query ok, 0 rows affected (0.03 sec)records: 0 duplicates: 0 warnings: 0mysql> desc employees;+----------------+-------------+------+-----+---------+----------------+| 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 | |+----------------+-------------+------+-----+---------+----------------+9 rows in set (0.00 sec)
(7)：修改sex字段，设置数据类型为char(1),非空约束。
alter table employees modify sex char(1) not null;
mysql> alter table employees modify sex char(1) not null;query ok, 0 rows affected (0.20 sec)records: 0 duplicates: 0 warnings: 0mysql> desc employees;+----------------+-------------+------+-----+---------+----------------+| 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 | |+----------------+-------------+------+-----+---------+----------------+9 rows in set (0.01 sec)
(8)：删除字段noth。
alter table employees drop noth;
mysql> alter table employees drop noth;query ok, 0 rows affected (0.15 sec)records: 0 duplicates: 0 warnings: 0mysql> desc employees;+----------------+-------------+------+-----+---------+----------------+| 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 | |+----------------+-------------+------+-----+---------+----------------+8 rows in set (0.01 sec)
(9):增加字段名favoriate_activity,数据类型为varchar(100)
alter table employees add favoriate_activity varchar(100);
mysql> alter table employees add favoriate_activity varchar(100);query ok, 0 rows affected (0.09 sec)records: 0 duplicates: 0 warnings: 0mysql> desc employees;+--------------------+--------------+------+-----+---------+----------------+| 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 | |+--------------------+--------------+------+-----+---------+----------------+9 rows in set (0.00 sec)
(10):删除主表offices
①删除表的外键约束:alter table employees drop foreign key office_fk;
②删除表offices:drop table offices;
mysql> alter table employees drop foreign key office_fk;query ok, 0 rows affected (0.03 sec)records: 0 duplicates: 0 warnings: 0mysql> drop table offices;query ok, 0 rows affected (0.03 sec)mysql> show tables;+-------------------+| tables_in_company |+-------------------+| employees |+-------------------+1 row in set (0.06 sec)
(11):修改表employees存储引擎为myisam。
alter table employees engine=myisam;
mysql> alter table employees engine=myisam;query ok, 0 rows affected (0.17 sec)records: 0 duplicates: 0 warnings: 0mysql> show create table employees \g*************************** 1. row *************************** table: employeescreate table: create table `employees` ( `employeenumber` int(11) not null auto_increment, `lastnamee` varchar(50) not null, `firstname` varchar(50) not null, `officecode` int(10) not null, `mobile` varchar(25) default null, `jobtitle` varchar(50) not null, `employee_birth` datetime default null, `sex` char(1) not null, `favoriate_activity` varchar(100) default null, primary key (`employeenumber`), key `office_fk` (`officecode`)) engine=myisam default charset=utf8mb4 collate=utf8mb4_0900_ai_ci1 row in set (0.00 sec)
(12)将表employees名称修改为employees_info。
alter table employees rename employees_info;
mysql> alter table employees rename employees_info;query ok, 0 rows affected (0.07 sec)mysql> show tables;+-------------------+| tables_in_company |+-------------------+| employees_info |+-------------------+1 row in set (0.00 sec)
相关免费学习推荐：mysql数据库(视频)
以上就是mysql练习之一：数据表的基本操作的详细内容。