数据库-小练习

1.根据题目要求创建数据库和相关的表，题目要求如下：

1.employees表
列1:id，整型，主键
列2:name，字符串，最大长度50，不能为空
列3:age，整型
列4:gender，字符串，最大长度10，不能为空，默认值“unknown”
列5:salary，浮点型
2.orders表
列1:id，整型，主键
列2:name，字符串，最大长度100，不能为空
列3:price，浮点型
列4:quantity，整型
列5:category，字符串，最大长度50
3.invoices表
列1:number，整型，主键自增长
列2:order_id，整型，外键关联到orders表的id列
列3:in date:日期型
列4:total_amount:浮点型，要求数据大于0

解题步骤：

//首先建立数据库mydb6_product

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

//查询数据库列表，查看是否建立了该数据库

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

//使用该数据库
mysql> use mydb6_product;
Database changed

//创建表employees
mysql> create table employees(id int primary key,name char(50) not null,age int,gender char(10) not null default 'unknow',salary float);
Query OK, 0 rows affected (0.01 sec)

//查询建立的表的属性
mysql> desc employees;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id     | int      | NO   | PRI | NULL    |       |
| name   | char(50) | NO   |     | NULL    |       |
| age    | int      | YES  |     | NULL    |       |
| gender | char(10) | NO   |     | unknow  |       |
| salary | float    | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

//创建表orders
mysql> create table orders(id int primary key,name char(100) not null, price float,quantity int,category char(50));
Query OK, 0 rows affected (0.01 sec)

//查询所建立的表的属性
mysql> desc orders;
+----------+-----------+------+-----+---------+-------+
| Field    | Type      | Null | Key | Default | Extra |
+----------+-----------+------+-----+---------+-------+
| id       | int       | NO   | PRI | NULL    |       |
| name     | char(100) | NO   |     | NULL    |       |
| price    | float     | YES  |     | NULL    |       |
| quantity | int       | YES  |     | NULL    |       |
| category | char(50)  | YES  |     | NULL    |       |
+----------+-----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

//创建表invoices
mysql> create table invoices(number int primary key,order_id int,in_date date,total_amount float,foreign key (order_id) references orders(id), 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    |       |
| order_id     | int   | YES  | MUL | NULL    |       |
| in_date      | date  | YES  |     | NULL    |       |
| total_amount | float | YES  |     | NULL    |       |
+--------------+-------+------+-----+---------+-------+
4 rows in set (0.00 sec)

//对该表设置主键自增长
mysql> alter table invoices modify number int auto_increment;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

//查询表的属性
mysql> desc invoicrs;
ERROR 1146 (42S02): Table 'mydb6_product.invoicrs' doesn't exist
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)