数据库第一次作业（建库建表）

1.登录数据账号

PS C:\WINDOWS\system32> mysql -uroot -p

Enter password: ******

2.创建数据库并启用

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

mysql> use mydb6_product;
Database changed

3.创建employees表

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

ERROR 1146 (42S02): Table 'mydb6_product.enployees' doesn't exist
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)

4.创建orders张表

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.01 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)

5.创建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, check(total_amount>0));
Query OK, 0 rows affected (0.02 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)