mysql第二章综合应用

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current

mysql> create database bms character set utf8mb4;
Query OK, 1 row affected (0.00 sec)
//查看数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bms                |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
6 rows in set (0.00 sec)

mysql> use bms;
Database changed
//auto_increment为自动增长，他会继承最大值往后增长
//primary key为主键
//comment为注释
mysql> create  table user (id int primary key auto_increment c

    -> name varchar(20) not null unique comment '用户名称',
    -> state char(1) not null default 0 comment '用户状态');
Query OK, 0 rows affected (0.14 sec)
//full是可选项 \G为换行符区分大小写
mysql> show full columns from user \G;
*************************** 1. row ***************************
     Field: id
      Type: int(11)
 Collation: NULL
      Null: NO
       Key: PRI
   Default: NULL
     Extra: auto_increment
Privileges: select,insert,update,references
   Comment: 用户编号
*************************** 2. row ***************************
     Field: name
      Type: varchar(20)
 Collation: utf8mb4_general_ci
      Null: NO
       Key: UNI
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment: 用户名称
*************************** 3. row ***************************
     Field: state
      Type: char(1)
 Collation: utf8mb4_general_ci
      Null: NO
       Key:
   Default: 0
     Extra:
Privileges: select,insert,update,references
   Comment: 用户状态
3 rows in set (0.00 sec)

ERROR:
No query specified

mysql> create table book (id int primary key auto_increment co
    -> name varchar(20) not null unique comment '图书名称',
    -> price decimal(6,2) not null comment '图书价格',
    -> upload_time datetime not null comment '上架时间',
    -> borrower_id int comment '借阅人编号',
    -> borrow_time datetime comment '借阅时间',
    -> state char(1) not null comment '图书状态');
Query OK, 0 rows affected (0.11 sec)

mysql> show full columns from book \g;
+-------------+--------------+--------------------+------+----
----------+---------------------------------+-----------------
| Field       | Type         | Collation          | Null | Key
          | Privileges                      | Comment
+-------------+--------------+--------------------+------+----
----------+---------------------------------+-----------------
| id          | int(11)      | NULL               | NO   | PRI
increment | select,insert,update,references | 图书编号
| name        | varchar(20)  | utf8mb4_general_ci | NO   | UNI
          | select,insert,update,references | 图书名称
| price       | decimal(6,2) | NULL               | NO   |
          | select,insert,update,references | 图书价格
| upload_time | datetime     | NULL               | NO   |
          | select,insert,update,references | 上架时间
| borrower_id | int(11)      | NULL               | YES  |
          | select,insert,update,references | 借阅人编号
| borrow_time | datetime     | NULL               | YES  |
          | select,insert,update,references | 借阅时间
| state       | char(1)      | utf8mb4_general_ci | NO   |
          | select,insert,update,references | 图书状态
+-------------+--------------+--------------------+------+----
----------+---------------------------------+-----------------
7 rows in set (0.00 sec)

ERROR:
No query specified

mysql> show full columns from book \G;
*************************** 1. row ***************************
     Field: id
      Type: int(11)
 Collation: NULL
      Null: NO
       Key: PRI
   Default: NULL
     Extra: auto_increment
Privileges: select,insert,update,references
   Comment: 图书编号
*************************** 2. row ***************************
     Field: name
      Type: varchar(20)
 Collation: utf8mb4_general_ci
      Null: NO
       Key: UNI
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment: 图书名称
*************************** 3. row ***************************
     Field: price
      Type: decimal(6,2)
 Collation: NULL
      Null: NO
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment: 图书价格
*************************** 4. row ***************************
     Field: upload_time
      Type: datetime
 Collation: NULL
      Null: NO
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment: 上架时间
*************************** 5. row ***************************
     Field: borrower_id
      Type: int(11)
 Collation: NULL
      Null: YES
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment: 借阅人编号
*************************** 6. row ***************************
     Field: borrow_time
      Type: datetime
 Collation: NULL
      Null: YES
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment: 借阅时间
*************************** 7. row ***************************
     Field: state
      Type: char(1)
 Collation: utf8mb4_general_ci
      Null: NO
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment: 图书状态
7 rows in set (0.00 sec)

ERROR:
No query specified

mysql
mysql> create table record (id int primary key auto_increment
,
    -> book_id int not null comment '图书编号',
    -> borrower_id int not null comment '借阅人编号',
    -> borrow_time datetime not null comment '借阅时间',
    -> remand_time datetime not null comment '归还时间');
Query OK, 0 rows affected (0.10 sec)

mysql> show full columns from record \G;
*************************** 1. row ***************************
     Field: id
      Type: int(11)
 Collation: NULL
      Null: NO
       Key: PRI
   Default: NULL
     Extra: auto_increment
Privileges: select,insert,update,references
   Comment: 借阅编号
*************************** 2. row ***************************
     Field: book_id
      Type: int(11)
 Collation: NULL
      Null: NO
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment: 图书编号
*************************** 3. row ***************************
     Field: borrower_id
      Type: int(11)
 Collation: NULL
      Null: NO
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment: 借阅人编号
*************************** 4. row ***************************
     Field: borrow_time
      Type: datetime
 Collation: NULL
      Null: NO
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment: 借阅时间
*************************** 5. row ***************************
     Field: remand_time
      Type: datetime
 Collation: NULL
      Null: NO
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment: 归还时间
5 rows in set (0.00 sec)

ERROR:
No query specified

第二章相关PPT：大数据Mysql第二章ppt.pptx https://www.alipan.com/s/MKf6fi446EW 点击链接保存，或者复制本段内容，打开「阿里云盘」APP ，无需下载极速在线查看，视频原画倍速播放。