数据库第一次作业

Windows中选择一种方式安装mysql8.0
新建产品库mydb6_product，新建4张表如下:
o employees表
列1:id，整型，主键
列2:name，字符串，最大长度50，不能为空
列3:age，整型
列4:gender，字符串，最大长度10，不能为空，默认值“unknown”
列5:salary，浮点型
o orders表
列1:id，整型，主键
。列2:name，字符串，最大长度100，不能为空
列3:price，浮点型
列4:quantity，整型
:列5:category，字符串，最大长度50
o invoices表
o列1:number，整型，主键自增长
。列2:order_id，整型，外键关联到orders表的id列
o 列3:in date:日期型
o列4:total_amount:浮点型，要求数据大于0

代码如下：

mysql> create database mydb6_product;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb2_stuinfo      |
| mydb3_employee     |
| mydb4_product      |
| mydb6_product      |
| mydbl_test         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
9 rows in set (0.00 sec)

mysql> use mydb6_product;
Database changed
mysql> create table employees(id int, name varchar(50) not null, age int, gender varchar(10) not null default 'unknown', salary float);
Query OK, 0 rows affected (0.02 sec)

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

mysql> alter table employees add primary key(id);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

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.00 sec)

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