【MySQL第一次作业】MySQL的安装以及建表基础操作

MySQL的安装以及建表基础操作

安装MySQL

下载地址：https://dev.mysql.com/downloads/installer/

1. 下载后点击后缀为exe的文件进行安装。 注意：安装路径一定要为英文。一般无特殊情况默认即可。
2. 在系统设置——此电脑右击——属性——高级系统设置——环境变量——系统变量找到path点击后编辑——存入MySQL的安装路径（要到bin为止）
3. 快捷键win+r弹出窗口输入cmd确定。（注意：用户名不要为英文）
4. 输入mysql -uroot -p后输入密码（建议重启后再进行3、4操作）

建表基础操作

首先明确建立的数据库以及数据表的情况：

我们先建立数据库：

create database mydb6_product;                                                                                   

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

查看现有数据库：

show databases;                                                                                   

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

使用数据库mydb6_product：

mysql> use mydb6_product;                                                                                               

mysql> use mydb6_product;                                                                                               
Database changed   

创建employeees表

#创建一个employees表
CREATE TABLE employees (
    id INT PRIMARY KEY, #列名为id 整型 主键
    name VARCHAR(50) NOT NULL, #列名为name 字符串最大长度50 非空
    age INT, #列名为age 整型
    gender VARCHAR(10) NOT NULL DEFAULT 'unknown', #列名为gender 字符串最大长度为10 非空 默认值为unkown
    salary FLOAT #列名为salary 浮点型
);

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)

查看库中所有的表：

show tables;

mysql> show tables;                                                                                                     
+-------------------------+                                                                                             
| Tables_in_mydb6_product |                                                                                             
+-------------------------+                                                                                             
| employees               |                                                                                             
+-------------------------+                                                                                             
1 row in set (0.00 sec)  

查看表字段:

desc employees;                                                                                                  

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表：

CREATE TABLE orders (
    id INT PRIMARY KEY, 
    name VARCHAR(100) NOT NULL,
    price FLOAT,
    quantity INT,
    category VARCHAR(50)
);

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.01 sec)

查看库中所有表：

show tables;

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

查看表字段：

desc orders; 

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) 

创建invoice表：

CREATE TABLE invoices (
    number INT PRIMARY KEY AUTO_INCREMENT, #列名为number 整型 主键 自增长
    order_id INT,
    in_date DATE,
    total_amount FLOAT CHECK (total_amount > 0), #列名为total_amount 浮点型 检查数据是否大于0
    FOREIGN KEY (order_id) REFERENCES orders(id) # #外键约束，将本表中的order_id列关联到order表中的id列
);

mysql> CREATE TABLE invoices (
    ->     number INT PRIMARY KEY AUTO_INCREMENT,
    ->     order_id INT,
    ->     in_date DATE,
    ->     total_amount FLOAT CHECK (total_amount > 0),
    ->     FOREIGN KEY (order_id) REFERENCES orders(id)
    -> );
Query OK, 0 rows affected (0.02 sec)

查看库中所有的表：

show tables;

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

查看表字段：

desc invoices;

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)