您的位置:首页 > 教育 > 锐评 > MySQL第一次作业

MySQL第一次作业

2024/10/4 1:55:20 来源:https://blog.csdn.net/nixiaoge/article/details/140500192  浏览:    关键词:MySQL第一次作业

一、库的建立

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb2_stuinfo      |
| mydb3_employee     |
| mydb4_product      |
| mydbl_test         |
| mysql              |
| performance_schema |
| sys                |
| temp2              |
+--------------------+
9 rows in set (0.00 sec)mysql> create database mydb6_product;
Query OK, 1 row affected (0.01 sec)mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb2_stuinfo      |
| mydb3_employee     |
| mydb4_product      |
| mydb6_product      |
| mydbl_test         |
| mysql              |
| performance_schema |
| sys                |
| temp2              |
+--------------------+
10 rows in set (0.00 sec)

二、建立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.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.00 sec)

三、建立 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.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)​

四、建立invoices表 (有主键自增长,外键关联,数值范围的要求)

mysql> create table invoices(-> number int primary key,-> 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.04 sec)mysql> alter table invoices modify number int auto_increment;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> 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)

版权声明:

本网仅为发布的内容提供存储空间,不对发表、转载的内容提供任何形式的保证。凡本网注明“来源:XXX网络”的作品,均转载自其它媒体,著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

我们尊重并感谢每一位作者,均已注明文章来源和作者。如因作品内容、版权或其它问题,请及时与我们联系,联系邮箱:809451989@qq.com,投稿邮箱:809451989@qq.com