mysql数据库第一天

创建库，创建employees表

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

 实现内容

mysql> create table employees(id int  primary key not null,name varchar(50) comment '不能为空' not null,age int , gender char(10) commet'不能为空' default 'unknown'not null,salary float);
Query OK, 0 rows affected (0.04 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 | char(10)     | NO   |     | unknown |       |
| salary | float        | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

创建表二

mysql> create table orders(id int primary key not null ,name varchar(100) comment '不能为空',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) | YES  |     | NULL    |       |
| price    | float        | YES  |     | NULL    |       |
| quantity | int          | YES  |     | NULL    |       |
| category | varchar(50)  | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

删除quantity列

mysql> alter table orders drop quantity;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc orders;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | NO   | PRI | NULL    |       |
| name     | varchar(100) | YES  |     | NULL    |       |
| price    | float        | YES  |     | NULL    |       |
| category | varchar(50)  | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

创建表三

mysql> create table invoices(number int primary key not null,order_id int ,foreign key(order_id) references orders(id),in_date date,total_amount float);
Query OK, 0 rows affected (0.05 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.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

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)