数据库之创建表

一、题目

二、步骤

1、先创建银行库

mysql> CREATE DATABASE bank;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| Myschool_db        |
| bank               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

2 、进入并查看是否有表

mysql> USE bank;
Database changed
mysql> SHOW TABLES;
Empty set (0.00 sec)

3、创建客户表

mysql> CREATE TABLE userInfo(
    ->     customerID INT NOT NULL AUTO_INCREMENT COMMENT '客户编号',
    ->     customerName CHAR(50) NOT NULL COMMENT '开户名',
    ->     PID CHAR(18) CHECK (LENGTH(PID) =15 OR LENGTH(PID) =18) NOT NULL COMMENT '身分证号',
    ->     telephone CHAR(20) CHECK (LENGTH(telephone) = 11) NOT NULL COMMENT '联系电话',
    ->     address VARCHAR(255) NULL DEFAULT NULL COMMENT '居住地址',
    ->     PRIMARY KEY (customerID) USING BTREE,
    ->     UNIQUE KEY (PID) USING BTREE
    ->     )ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET =utf8mb4 COLLATE = utf8mb4_general_ci;
Query OK, 0 rows affected (0.01 sec)

 4、创建银行卡信息表

mysql> CREATE TABLE cardInfo(
    ->     cardID CHAR(50) NOT NULL COMMENT '卡号',
    ->     curlID VARCHAR(50) NOT NULL DEFAULT'RMB' COMMENT '货币种类',
    ->     savingID INT NOT NULL COMMENT '存款类型',
    ->     openDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '开户 日期',
    ->     openMONEY DECIMAL CHECK(openMONEY>=1) NOT NULL COMMENT '开户金额',
    ->     balance DECIMAL CHECK(balance>=1) NOT NULL COMMENT '余额',
    ->     password VARCHAR(6) CHECK(LENGTH(password)=6) NOT NULL DEFAULT'888888' COMMENT '密码',
    ->     IsReportLoss BIT NOT NULL DEFAULT 0 COMMENT '是否挂失',
    ->     customerID INT  NOT NULL COMMENT '客户编号',
    ->     PRIMARY KEY (cardID) USING BTREE,
    ->     FOREIGN KEY (savingID) REFERENCES deposit(savingID),
    ->     FOREIGN KEY (customerID) REFERENCES userInfo(customerID)
    ->     )ENGINE = InnoDB CHARACTER SET =utf8mb4 COLLATE = utf8mb4_general_ci;
Query OK, 0 rows affected (0.08 sec)

5、创建 交易信息表

mysql> CREATE TABLE tradeInfo(
    ->     transDate TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '交易日期',
    ->     cardID CHAR(50) NOT NULL COMMENT '卡号',
    ->     transType CHAR(50) NOT NULL COMMENT '交易类型',
    ->     transMoney VARCHAR(50) NOT NULL COMMENT '交易金额',
    ->     remark TEXT(500) NULL DEFAULT NULL COMMENT '备注',
    ->     FOREIGN KEY (cardID) REFERENCES cardInfo(cardID)
    ->     )ENGINE = InnoDB CHARACTER SET =utf8mb4 COLLATE = utf8mb4_general_ci;
Query OK, 0 rows affected (0.07 sec)

 6、创建存款类型表

mysql> CREATE TABLE deposit(
    ->     savingID INT NOT NULL AUTO_INCREMENT COMMENT '存款类型号',
    ->     savingName VARCHAR(50) NOT NULL COMMENT '存款类型名称',
    ->     descrip VARCHAR(50) NULL DEFAULT NULL COMMENT '描述',
    ->     PRIMARY KEY (savingID) USING BTREE
    ->     )ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET =utf8mb4 COLLATE = utf8mb4_general_ci;
Query OK, 0 rows affected (0.01 sec)

建表完成！