MYSQL数据库库表操作

新建产品库mydb6_product，新建3张表如下:

 

1. employees表

• 列1: id，整型，主键
• 列2: name，字符串，最大长度50，不能为空
• 列3: age，整型
• 列4: gender，字符串，最大长度10，不能为空，默认值"unknown"
• 列5: salary，浮点型

mysql> create table employees(id int 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         | NO   | PRI | NULL    |       |
| name   | varchar(50) | NO   |     | NULL    |       |
| age    | int         | YES  |     | NULL    |       |
| gender | varchar(10) | NO   |     | unknown |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

 

2. orders表

• 列1: id，整型，主键
• 列2: name，字符串，最大长度100，不能为空
• 列3: price，浮点型
• 列4: quantity，整型
• 列5: category，字符串，最大长度50

mysql> create table orders(id int primary key,
    -> name varchar(100) not null,
    -> price float,
    -> quantity int,
    -> category varchar(50));
Query OK, 0 rows affected (0.03 sec)
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.00 sec)

 

3.invoices表

• 列1: number，整型，主键自增长
• 列2: order_id，整型，外键关联到orders表的id列
• 列3: in_date:日期型
• 列4: total_amount:浮点型，要求数据大于0

 

mysql> create table invoices(number int primary key auto_increment,
    -> order_id int,
    -> in_date date,
    -> total_amount float check(total_amount > 0),
    -> foreign key (order_id) references orders(id));
Query OK, 0 rows affected (0.08 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 | YES  |     | NULL    |                |
+--------------+-------+------+-----+---------+----------------+
4 rows in set (0.00 sec)