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

mysql> create database company; mysql> use company;

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) -> );

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) -> );

mysql> show tables; +-------------------+ | Tables_in_company | +-------------------+ | employees | | offices | +-------------------+

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 | | +------------+-------------+------+-----+---------+-------+

mysql> desc employees; +----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+----------------+ | employeeNumber | int(11) | NO | PRI | NULL | auto_increment | | lastName | 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 | | | note | varchar(255) | YES | | NULL | | | sex | varchar(5) | YES | | NULL | | +----------------+--------------+------+-----+---------+----------------+

mysql> alter table employees MODIFY mobile varchar(25) after officeCode; mysql> desc employees; +----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+----------------+ | employeeNumber | int(11) | NO | PRI | NULL | auto_increment | | lastName | 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 | | | note | varchar(255) | YES | | NULL | | | sex | varchar(5) | YES | | NULL | | +----------------+--------------+------+-----+---------+----------------+

mysql> alter table employees CHANGE birth employee_birth DATETIME; mysql> desc employees; +----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+----------------+ | employeeNumber | int(11) | NO | PRI | NULL | auto_increment | | lastName | 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 | | | note | varchar(255) | YES | | NULL | | | sex | varchar(5) | YES | | NULL | | +----------------+--------------+------+-----+---------+----------------+

mysql> alter table employees MODIFY sex CHAR(1) NOT NULL; mysql> desc employees; +----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+----------------+ | employeeNumber | int(11) | NO | PRI | NULL | auto_increment | | lastName | 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 | | | note | varchar(255) | YES | | NULL | | | sex | char(1) | NO | | NULL | | +----------------+--------------+------+-----+---------+----------------+

mysql> alter table employees DROP note; mysql> desc employees; +----------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------+------+-----+---------+----------------+ | employeeNumber | int(11) | NO | PRI | NULL | auto_increment | | lastName | 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 | | +----------------+-------------+------+-----+---------+----------------+

mysql> alter table employees ADD favoriate_activity varchar(100); mysql> desc employees; +--------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------------+------+-----+---------+----------------+ | employeeNumber | int(11) | NO | PRI | NULL | auto_increment | | lastName | 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 | | +--------------------+--------------+------+-----+---------+----------------+

mysql> drop table offices; ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

mysql> alter table employees drop foreign key office_fk;

mysql> drop table offices; Query OK, 0 rows affected (0.03 sec)

mysql> show tables; +-------------------+ | Tables_in_company | +-------------------+ | employees | +-------------------+

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)

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)