MySQL基础作业一

#建立数据库mydb6_product
mysql> create database mydb6_product;
Query OK, 1 row affected (0.02 sec)

mysql> use mydb6_product;
Database changed

#建立employees表
mysql> create table employees(id int unsigned primary key,
    -> name varchar(50) not null,
    -> age int ,
    -> gender varchar(10) not null default 'unknown',
    -> salary float);
Query OK, 0 rows affected (0.03 sec)

mysql> desc employees;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int unsigned | NO   | PRI | NULL    |       |
| name   | varchar(50)  | NO   |     | NULL    |       |
| age    | int          | YES  |     | NULL    |       |
| gender | varchar(10)  | NO   |     | unknow  |       |
| salary | float        | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

#建立orders表
mysql> create table orders(id int primary key,
    -> name varchar(100) not null,
    -> price float,
    -> quantity int ,
    -> category varchar(50));
mysql> desc orders;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | NO   | PRI | NULL    |       |
| name     | varchar(100) | NO   |     | NULL    |       |
| price    | float        | YES  |     | NULL    |       |
| quantity | int          | YES  |     | NULL    |       |
| category | varchar(50)  | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

#建立invoices表
mysql> create table invoices(number int primary key auto_increment,order_id int ,foreign key(order_id) references orders(id),in_date date,total_amount float unsigned);
Query OK, 0 rows affected, 1 warning (0.34 sec)

mysql> desc invoices;
+--------------+----------------+------+-----+---------+----------------+
| Field        | Type           | Null | Key | Default | Extra          |
+--------------+----------------+------+-----+---------+----------------+
| number       | int            | NO   | PRI | NULL    | auto_increment |
| order_id     | int            | YES  | MUL | NULL    |                |
| in_date      | date           | YES  |     | NULL    |                |
| total_amount | float unsigned | YES  |     | NULL    |                |
+--------------+----------------+------+-----+---------+----------------+
4 rows in set (0.03 sec)