MySQL数据表的基本操作三：综合示例

发布时间：2020-12-12 02:18:03 所属栏目：MySql教程来源：网络整理

导读：一、创建数据库 mysql create database company;mysql use company; 二、创建表 1. 创建表offices mysql create table offices - ( - officeCode int(10) NOT NULL UNIQUE,- city varchar(50) NOT NULL,- address varchar(50) NOT NULL,- country varchar(50

一、创建数据库

mysql> create database company; mysql> use company;

二、创建表

1. 创建表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) -> ); 2. 创建表employees

mysql> create table employees -> ( -> employeeNumber int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,-> lastName 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,-> note VARCHAR(255),-> sex VARCHAR(5),-> CONSTRAINT office_fk FOREIGN KEY (officeCode) REFERENCES offices(officeCode) -> ); 3. 查看数据库已创建的表

三、表的基本操作

1. 将表employees的mobile字段修改到officeCode字段后面

1) 创建表时设置了表的外键，所以不能直接删除

mysql> drop table offices; ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
2) 删除employees表的外键约束

mysql> alter table employees drop foreign key office_fk;
3) 删除offices表

mysql> drop table offices; Query OK,0 rows affected (0.03 sec)mysql> show tables; +-------------------+ | Tables_in_company | +-------------------+ | employees | +-------------------+
7. 修改employees表的存储引擎为MyISAM

mysql> alter table employees ENGINE=MyISAM; Query OK,0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table employeesG; *************************** 1. row *************************** Table: employees Create Table: CREATE TABLE `employees` ( `employeeNumber` int(11) NOT NULL AUTO_INCREMENT,`lastName` 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=latin1 1 row in set (0.01 sec)
8. 将表employees表名改为employees_info

mysql> alter table employees rename employees_info; Query OK,0 rows affected (0.00 sec) mysql> show tables; +-------------------+ | Tables_in_company | +-------------------+ | employees_info | +-------------------+ 1 row in set (0.00 sec)

如果您们在尝试的过程中遇到什么问题或者我的代码有错误的地方，请给予指正，非常感谢！

联系方式：david.louis.tian@outlook.com

版权＠：转载请标明出处！

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!