mysql中创建基本表

关注

原创 2025-01-15 19:38:18 171 阅读 AI 写同款· GEO 优化 ›

数据库课程大作业——概念模式设计、数据库模式设计优化及Web框架实现本文详细介绍数据库从零到一的完整设计，因基于课程报告衍生，因此文章内容仅供举例参考，与商业应用有较大差距，适合学习数据库的同学参考学习使用。阅读详情

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb1_test         |
| mydb2_stuinfo      |
| mydb3_employee     |
| mydb4_product      |
| mydb5_sales        |
| mydb6_product      |
| mydb_temp1         |
| mysql              |
| performance_schema |
| sys                |
| temp1              |
+--------------------+
12 rows in set (0.00 sec)

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

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

mysql> create table orders(id int primary key, name varchar(100) not null, price float not null, quantity int not null,category varchar(50));
Query OK, 0 rows affected (0.04 sec)

mysql> desc orders;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | NO   | PRI | NULL    |       |
| name     | varchar(100) | NO   |     | NULL    |       |
| price    | float        | NO   |     | NULL    |       |
| quantity | int          | NO   |     | 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,int_date date, total_amount float check (total_amount>0),foreign key (order_id) references orders(id));
Query OK, 0 rows affected (0.05 sec)

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

mysql> show tables;
+-------------------------+
| Tables_in_mydb6_product |
+-------------------------+
| employees               |
| invoices                |
| orders                  |
+-------------------------+
3 rows in set (0.00 sec)