共計 9382 個字符，預計需要花費 24 分鐘才能閱讀完成。
自動寫代碼機器人，免費開通
這篇文章主要介紹 mysql 中數據表基本操作的示例，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！
案例: 創建數據庫 company，按照下面兩個表給出的表結構在 company 數據庫中創建兩個數據表 offices 和 employees，按照操作過程完成數據表的基本操作。
操作過程如下：(1)：登錄 MySQL。
mysql -h localhost -u root -p打開 windows 命令行，輸入登錄用戶名和密碼：
C:\Users\Hudiemysql -h localhost -u root -p
Enter password: ********Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 19Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.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 officesmysql 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 employeesmysql 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;mysqlalter table employees ENGINE=MyISAM;Query OK, 0 rows affected (0.17 sec)Records: 0 Duplicates: 0 Warnings: 0mysql showcreate 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 中數據表基本操作的示例”這篇文章的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注丸趣 TV 行業資訊頻道！
向 AI 問一下細節
正文完
