mysql数据库练习1

目录

新建数据库----查看所有数据库----使用选定数据库---查看当前所使用数据库

创建employees表（根据给定结构和约束）---查看表结构

创建orders表

创建invoices表

查看mydb6_product 数据库中的所有表

要求1：

新建数据库----查看所有数据库----使用选定数据库---查看当前所使用数据库

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

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

mysql> use  mydb6_product;
Database changed
mysql> select database();
+---------------+
| database()    |
+---------------+
| mydb6_product |
+---------------+

使用命令：

create database 数据库名;    用于新建数据库

show databases;    查看所有数据库

use 数据库名；    选定数据库进行使用

select database();    使用函数查看当前所使用的数据库

要求2：

创建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.02 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)

使用命令：

create table 表名(字段名 字段类型 字段约束)；   创建表

desc 表名；  查看表结构

要求3：

创建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.01 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)

要求4：

创建invoices表

mysql> create table invoices(
    -> number int primary key auto_increment,
    -> order_id int,
    -> in_date date,
    -> total_amount float,
    -> foreign key (order_id) references orders(id),
    -> check(total_amount>0));
Query OK, 0 rows affected (0.03 sec)

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)

查看mydb6_product 数据库中的所有表

mysql> select database();
+---------------+
| database()    |
+---------------+
| mydb6_product |
+---------------+
1 row in set (0.00 sec)

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