案例

一、

mysql> use test
Database changed
mysql> create table if not exists branch(
    ->  branchNo   varchar(5) primary key,
    ->  street     varchar(30) not null,
    ->  city       varchar(30) not null,
    ->  postcode   varchar(6));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into branch(branchNo, street, city, postcode) values
    -> ('B001', '22 Deer Rd', 'London', 'SW14EH'),
    -> ('BO02', '16 Argy11 St', 'Aberdeen', 'AB23SU'),
    -> ('B003', '163 Main St', 'Glasgow', 'G119QW'),
    -> ('B004', '32 Manse Rd', 'Bristol', 'BS991N'),
    -> ('B005', '56 Clover Dr', 'London', 'NW106U');
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from branch;
+----------+--------------+----------+----------+
| branchNo | street       | city     | postcode |
+----------+--------------+----------+----------+
| B001     | 22 Deer Rd   | London   | SW14EH   |
| B003     | 163 Main St  | Glasgow  | G119QW   |
| B004     | 32 Manse Rd  | Bristol  | BS991N   |
| B005     | 56 Clover Dr | London   | NW106U   |
| BO02     | 16 Argy11 St | Aberdeen | AB23SU   |
+----------+--------------+----------+----------+
5 rows in set (0.00 sec)

mysql> create table if not exists staff(
    ->  staffNo varchar(5) primary key,
    ->  fname   varchar(20) not null,
    ->  lname   varchar(20) not null,
    ->  position varchar(30) not null,
    ->  sex     enum('M','F'),
    ->  birth   date,
    ->  salary  decimal(10,2),
    ->  branchNo varchar(5),
    ->  constraint fk_staff_branch foreign key(branchNo) references branch(branchNo));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into staff(staffNo, fname, lname, position, sex, birth, salary, branchNo) values (
    ->  'SL21', 'John', 'White', 'Manager', 'M', '1945-10-1', '30000.00','B001'),
    -> ( 'SG37', 'Ann', 'Beech', 'Assistant', 'F', '1960-11-11', '12000.00','B003'),
    -> ( 'SG14', 'David', 'Ford', 'Supervisor', 'M', '1958-3-11', '18000.00','B003'),
    -> ( 'SA9', 'Mary', 'Howe', 'Assistant', 'F', '1970-9-7', '9000.00','B005'),
    -> ( 'SG5', 'Susan', 'Brand', 'Manager', 'F', '1980-6-7', '24000.00','B005'),
    -> ( 'SL41', 'Julie', 'Lee', 'Assistant', 'F', '1980-6-13', '9000.00','B003');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from staff;
+---------+-------+-------+------------+------+------------+----------+----------+
| staffNo | fname | lname | position   | sex  | birth      | salary   | branchNo |
+---------+-------+-------+------------+------+------------+----------+----------+
| SA9     | Mary  | Howe  | Assistant  | F    | 1970-09-07 |  9000.00 | B005     |
| SG14    | David | Ford  | Supervisor | M    | 1958-03-11 | 18000.00 | B003     |
| SG37    | Ann   | Beech | Assistant  | F    | 1960-11-11 | 12000.00 | B003     |
| SG5     | Susan | Brand | Manager    | F    | 1980-06-07 | 24000.00 | B005     |
| SL21    | John  | White | Manager    | M    | 1945-10-01 | 30000.00 | B001     |
| SL41    | Julie | Lee   | Assistant  | F    | 1980-06-13 |  9000.00 | B003     |
+---------+-------+-------+------------+------+------------+----------+----------+
6 rows in set (0.00 sec)