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) 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. 查看数据库已创建的表

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

三、表的基本操作

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

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

2. 将表employees的birth字段改名为employee_birth

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

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