数据库基础知识——MySQL连接、数据库的创建、选择、展示和表的创建

深藏阁楼爱情的钟 2022-12-03 08:56 352阅读 0赞

## 一、连接 MySQL ##

### 1. Terminal 命令行窗口 ###

# mysql -u username -p
    # 输入密码

### 2. Python 脚本 ###

import pymysql
     
    db = pymysql.connect(host='host_addresss', user='username', password='password', port=3306, db='spiders')

## 二、数据库的创建、选择和展示 ##

### 1. 展示数据库 ###

显示可用的数据库列表：

SHOW DATABASES;

![在这里插入图片描述][20200902200045344.png_pic_center]

### 2. 创建数据库 ###

CREATE DATABASE crashcourse DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

`crashcourse` 是我自己设置的数据库的名称，下面的使用示例，都是使用这个数据库。

`为了下面的展示，我们需要为 crashcourse 数据库创建一些表和往表中插入一些记录， 相关的 SQL 文件有：`

*  创建表：[create.sql][]
 *  插入数据：[populate.sql][]

### 3. 选择数据库 ###

在你能执行任意数据库操作前，需要选择一个数据库。为此，可使用 **USE** 关键字。

USE crashcourse;

![在这里插入图片描述][20200902200158351.png_pic_center]

### 4. 展示数据库中的表 ###

为了获得一个数据库内的表的列表，使用：

SHOW TABLES;

![在这里插入图片描述][20200902200213976.png_pic_center]

### 5. 展示表中的列 ###

SHOW COLUMNS FROM customers;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d1YW5tYW9uaW5n_size_16_color_FFFFFF_t_70_pic_center]

## 三、表 ##

### 1. 创建表 ###

########################
    # Create customers table
    ########################
    CREATE TABLE customers
    (
      cust_id      int       NOT NULL AUTO_INCREMENT,
      cust_name    char(50)  NOT NULL ,
      cust_address char(50)  NULL ,
      cust_city    char(50)  NULL ,
      cust_state   char(5)   NULL ,
      cust_zip     char(10)  NULL ,
      cust_country char(50)  NULL ,
      cust_contact char(50)  NULL ,
      cust_email   char(255) NULL ,
      PRIMARY KEY (cust_id)
    ) ENGINE=InnoDB;
    
    #########################
    # Create orderitems table
    #########################
    CREATE TABLE orderitems
    (
      order_num  int          NOT NULL ,
      order_item int          NOT NULL ,
      prod_id    char(10)     NOT NULL ,
      quantity   int          NOT NULL ,
      item_price decimal(8,2) NOT NULL ,
      PRIMARY KEY (order_num, order_item)
    ) ENGINE=InnoDB;
    
    
    #####################
    # Create orders table
    #####################
    CREATE TABLE orders
    (
      order_num  int      NOT NULL AUTO_INCREMENT,
      order_date datetime NOT NULL ,
      cust_id    int      NOT NULL ,
      PRIMARY KEY (order_num)
    ) ENGINE=InnoDB;
    
    #######################
    # Create products table
    #######################
    CREATE TABLE products
    (
      prod_id    char(10)      NOT NULL,
      vend_id    int           NOT NULL ,
      prod_name  char(255)     NOT NULL ,
      prod_price decimal(8,2)  NOT NULL ,
      prod_desc  text          NULL ,
      PRIMARY KEY(prod_id)
    ) ENGINE=InnoDB;
    
    ######################
    # Create vendors table
    ######################
    CREATE TABLE vendors
    (
      vend_id      int      NOT NULL AUTO_INCREMENT,
      vend_name    char(50) NOT NULL ,
      vend_address char(50) NULL ,
      vend_city    char(50) NULL ,
      vend_state   char(5)  NULL ,
      vend_zip     char(10) NULL ,
      vend_country char(50) NULL ,
      PRIMARY KEY (vend_id)
    ) ENGINE=InnoDB;
    
    ###########################
    # Create productnotes table
    ###########################
    CREATE TABLE productnotes
    (
      note_id    int           NOT NULL AUTO_INCREMENT,
      prod_id    char(10)      NOT NULL,
      note_date datetime       NOT NULL,
      note_text  text          NULL ,
      PRIMARY KEY(note_id),
      FULLTEXT(note_text)
    ) ENGINE=MyISAM;
    
    
    #####################
    # Define foreign keys
    #####################
    ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
    ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
    ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
    ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

### 2. ALTER 添加外键 ###

#####################
    # Define foreign keys
    #####################
    ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
    ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
    ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
    ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

### 3. ALTER 添加表列 ###

ALTER TABLE vendors ADD vend_phone CHAR(20);

### 4. ALTER 删除表列 ###

ALTER TABLE vendors DROP COLUMN vend_phone;

### 5. 删除表 ###

DROP TABLE customers2;

### 6. 重命名表 ###

RENAME TABLE customers2 TO customers3;

重命名多个表：  
![在这里插入图片描述][20200904204325165.png_pic_center]

[20200902200045344.png_pic_center]: /images/20221123/8b8d1308119448a8a9bdbfae7d196acc.png
[create.sql]: https://github.com/MaoningGuan/MySQL_exercises/blob/master/create.sql
[populate.sql]: https://github.com/MaoningGuan/MySQL_exercises/blob/master/populate.sql
[20200902200158351.png_pic_center]: /images/20221123/3bf93ebfb2444d91a992eb944f74d178.png
[20200902200213976.png_pic_center]: /images/20221123/e3f6c1530d3b4f2dafccb84d5451e473.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d1YW5tYW9uaW5n_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/fafde6f5865443d49fa276677d266773.png
[20200904204325165.png_pic_center]: /images/20221123/50f18db7ff3a4b4685b0eab68d42dde7.png