第一次作业题目
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)