第一次作業題目
1,windows中選擇一種方式安裝mysql
2,新建產品庫(名字自擬)db_mysql_3
3,使用產品庫新建三張表
如下:
表1:employees
?? ?列1:id,整型,主鍵
?? ?列2:name,字符串,最大長度50,不能為空
?? ?列3:age,整型
?? ?列4:gender,字符串,最大長度10,不能為空,默認值"unknow"
?? ?列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
作業內容:

準備工作

--登錄命令 mysql -u root -p
--查看數據庫
mysql> show databases;
--創建數據庫
mysql> create database db_mysql_3;
--使用數據庫
mysql> use db_mysql_3;
--查看正在使用的數據庫
mysql> select database();

開始建表

--新建表1
--單行結構
mysql> create table emoloyees ( id int primary key,name varchar(50) not null,age int,gender varchar(10) not null default 'unknown',salary float);
--多行結構
mysql> create table emoloyees (-> id int primary key,-> name varchar(50) not null,-> age int,-> gender varchar(10) not null default 'unknown',-> salary float-> );
--查看表結構(完整describe)
mysql> desc emoloyees;
+--------+-------------+------+-----+---------+-------+
| 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)--新建表2
--單行結構
mysql> create table oders ( id int primary key,name varchar(100) not null,price float,quantity int,category varchar(50));
--多行結構
mysql> create table oders (-> id int primary key,-> name varchar(100) not null,-> price float,-> quantity int,-> category varchar(50)-> );
--查看表結構(完整describe)
mysql> desc oders;
+----------+--------------+------+-----+---------+-------+
| 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)--新建表3
--單行結構
mysql> create table invoices ( number int auto_increment primary key,order_id int,in_date date,total_amount float check (total_amount > 0 ),foreign key (order_id) references oders(id));
--多行結構
mysql> create table invoices (-> number int auto_increment primary key,-> order_id int,-> in_date date,-> total_amount float check (total_amount > 0 ),-> foreign key (order_id) references oders(id)-> );
--查看表結構(完整describe)
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.01 sec)