SQL：表的创建修改与删除

迈不过友情╰ 2022-04-08 14:43 362阅读 0赞

# 1、操作表 #

## 1.1、创建表:CREATE TABLE ##

顾客信息表

CREATE TABLE  IF NOT EXISTS  customers (  #当表名不存在时创建customers表
    	cust_id      INT       NOT NULL AUTO_INCREMENT,
    	cust_name    VARCHAR(50)  NOT NULL,
    	cust_address VARCHAR(50)  NULL,  #NULL是默认设置，如果不是为null，默认值是null
    	cust_city    VARCHAR(50)  NULL,
    	cust_state   VARCHAR(5)   NULL,
    	cust_zip     VARCHAR(10)  NULL,
    	cust_country VARCHAR(50)  NULL,  
    	cust_contact VARCHAR(50)  NULL,
    	cust_email   VARCHAR(255) NULL,
    	PRIMARY KEY(cust_id)                    #指定主键
    )ENGINE = INNODB DEFAULT CHARACTER SET = utf8mb4;

上架商品信息表：商品名prod\_id的订单编号是order\_num

CREATE TABLE orderitems(
    	order_num  INT 		NOT NULL,
    	order_item INT          NOT NULL,
    	prod_id    CHAR(10)	NOT NULL,
    	quantity   INT          NOT NULL DEFAULT 1,
    	item_price DECIMAL(8,2) NOT NULL,
    	PRIMARY KEY(order_num, order_item) #订单号和订单物品的组合是唯一的，因此设置为主键
    )ENGINE = INNODB;

顾客订单表：客户cust\_id在order\_date那天购买了订单编号为order\_num的商品

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 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 TABLE vendors
    (
      vend_id      INT      NOT NULL AUTO_INCREMENT,
      vend_name    CHAR(50) NOT NULL ,
      vend_address CHAR(50)  ,
      vend_city    CHAR(50)  ,
      vend_state   CHAR(5)   ,
      vend_zip     CHAR(10)  ,
      vend_country CHAR(50)  ,
      PRIMARY KEY (vend_id)
    ) ENGINE=INNODB;
    DESC vendors;

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;

*  MySQL引擎\[ENGINE = INNODB\]：
    
     *  MySQL有一个具体管理和处理数据的内部引擎。当使用CREATE TABLE时该引擎创建表，当进行数据库处理时，该引擎在内部处理请求。
     *  MySQL服务器具有多种引擎，具备不同的功能和特性：
        
         *  InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索
         *  Memory在功能等同于MyISAM，但是由于数据存储在内存中，速度很快，可以用来创建临时表
         *  MyISAM是一个性能很高的引擎，它支持全文本搜索但是不支持事务处理
     *  引擎类型可以混用
     *  外键不能跨引擎：使用一个引擎的表不能引用具有不同引擎的外键
 *  语法：
    
     *  create table 表名(  
        列名 列的类型【(长度) 约束】,  
        列名 列的类型【(长度) 约束】,  
        列名 列的类型【(长度) 约束】,  
        …  
        列名 列的类型【(长度) 约束】

### ① 指定表名 ###

* 命名规则：数据库名、表名、列名：英文字母、数字、下划线(_)，<font color=red>必须以英文开头开头</font>，以数字为开头是不规范的。
    * 在同一个数据库中不能创建两个相同名称的表。在同一个表中不能创建相同名称的列。否则会报错。
    * 建议数据库的首字母大写。我没有大写的原因是笔记进行了更新，为了避免代码出现错误，就没有对原先已经验证的代码进行修改。

### ② 指定数据类型 ###

所有列必须指定数据类型。每一列都不能存储与该列数据类型不相同的数据。基本的数据类型有：

*  INTERGER型
    
     *  存储整数不能存储小数
 *  CHAR型
    
     *  存储定长字符串。所谓定长字符串，就是当列中存储的长度达不到最大长度时，使用半角空格不足；当字符串超过最大长度的部分是无法存储到该列中的。比如CHAR(8)，指定存储8个字符的字符串
     *  表中存储的字符串是区分大小写的
 *  VARCHAR()型。
    
     *  存储变长字符串。当字符串未达到最大长度，不会用半角空格补足。
     *  Oracle中使用VARCHAR2型，不推荐使用VARCHAR()型
 *  DATE型：
    
     *  用来存储日期

### ③ 约束 ###

约束就是除了数据类型之外，对列中存储的数据进行显示或者追缴条件。

*  NOT NULL约束
    
     *  必须输入数据，如果什么也不输入会报错。
 *  主键约束  
    成为主键的条件\[PRIMARY KEY\]：主键必须唯一
    
     *  如果主键是单个列，值必须唯一
     *  如果主键是多个列，这些列的组合值必须唯一
        
        注：主键必须唯一，因此主键不能为null值
 *  默认值约束\[DEFAULT\]:
    
     *  如果没有设置DEFAULT默认值就是NULL
    
     *  如果设置了DEFAULT,当执行INSERT子句是没有指定值时使用DEFAULT设置的值
        
        注：与大多数DBMS不一样，MySQL不允许使用函数作为默认值，它只支持常量  
        最好使用默认值而不是Null值，特别对于计算或者数据分组的列

alter table 表名 alter column 字段名 drop default; (若本身存在默认值，则先删除)
    
    alter table 表名 alter column 字段名 set default 默认值;(若本身不存在则可以直接设定)

*  自动增量\[AUTO\_INCREMENT\]：
    
     *  每执行insert子句时，该列自动增量。
        
        注：每个表只允许一个AUTO\_INCREMENT列，而且它必须被索引。  
        SELECT LAST\_INSERT\_ID(); 返回最后一个AUTO\_INCREMENT的值

### 补充 ###

CREATE TABLE table_new like table_old;

整个SQL语句会模仿table\_old的数据结果创建一个一模一样的数据表table\_new，但是并没有table\_old的数据。

## 1.2、查看表结构 ##

DESC `products`;
    DESC `orderitems`;
    DESC `orders`;
    DESC `customers`;

*  查看表中所有的数据
    
    > SELECT \* FROM `表名`

## 1.3、修改表结构\[不常用\]：ALTER TABLE ##

### ①修改列名 ###

> ALTER TABLE `表名` CHANGE COLUMN `列名` `新列名` 数据类型;

### ②修改列的数据类型 ###

> ALTER TABLE `表名` MODIFY COLUMN `列名` 新数据类型;

### ③添加新列 ###

MySQL、DB2、PostgreSQL

> \#插入新列vend\_phone，数据类型是VARCHAR(20)  
> ALTER TABLE `vendors` ADD COLUMN `vend_phone` VARCHAR(20);

Oracle：

> ALTER TABLE `vendors` ADD (`vend_phone` VARCHAR(20));

SQL Server：

> ALTER TABLE `vendors` ADD `vend_phone` VARCHAR(20);

### ④删除列 ###

SQL Server、DB2、PostgreSQL、MySQL

> \#删除旧列vend\_phone。  
> ALTER TABLE `vendors` DROP COLUMN `vend_phone` ;

Oracle

> ALTER TABLE `vendors` DROP (`vend_phone`) ;

### ⑤ 定义外键 ###

>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);
    >products 只存储产品信息，它除了存储供应商ID[vendors的主键]外不愁吃其他vendors的信息。vendors表的主键又叫做products的外键，它将vendors表与products表关联，利用供应商ID能从vendors表中找出其他相应供应商的信息

修改表名

> ALTER TABLE `vendors` RENAME TO `新表名`;

*  外键：外键为某个表的一列，它包含另一个表的主键值，定义了两个表之间的关系。
    
     *  供应商信息不重复，从而不浪费时间和空间。处理数据也更简单
     *  如果供应商信息变动，可以只更新verdos表中的单个记录，相关表中的数据不用改动
 *  可伸缩性：能够适应不断增加的工作量而不失败。
    
        每个表只允许一个主键(PRI)[一列或者多个列组成唯一值]，但是可以拥有多个外键(MUL)
 *  语法

*  alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
    
    注： 当设计表的时候要考虑好表的结构，更新表结构并不是一个好事  
    数据库的更改不可撤销，因此在改动之前最好做一个备份，因为改动表是不可以撤销的

## 1.4、修改表名 ##

Mysql：

> RENAME TABLE `表名` TO `新表名`, `表名`TO `新表名`;

DB2：

> RENAME TABLE `表名`TO `新表名`;

Oracle PostgreSQL：

> ALTER TABLE `表名` RENAME TO `新表名`;

SQL Server：

> sp\_rename ‘ `表名`’, ‘`新表名`’;

## 1.5、删除表DROP TABLE \[慎用\] ##

SET FOREIGN_KEY_CHECKS = 0;    
    DROP TABLE IF EXISTS  `customers`;
    SET FOREIGN_KEY_CHECKS = 1;

*  当设置了表的关联时执行DROP TABLE customers;报错：
    
    ERROR: Cannot delete or update a parent row: a foreign key constraint fails：
    
     *  原因：MySQL在InnoDB中设置了foreign key关联，造成无法更新或删除数据。可以通过设置FOREIGN\_KEY\_CHECKS变量来避免这种情况。
     *  解决：
        
        SET FOREIGN\_KEY\_CHECKS = 0;  
        删除完成后设置  
        SET FOREIGN\_KEY\_CHECKS = 1;  
        注：删除customers外键依旧存在

## 1.6、显示当前数据库可用的表 ##

> SHOW TABLES;

*  总结：通用的写法：

*  DROP DATABASE IF EXISTS 旧库名;  
    CREATE DATABASE 新库名;
 *  DROP TABLE IF EXISTS 旧表名;  
    CREATE TABLE 表名();

# 2、修改表数据 #

## 2.1、插入INSERT ##

> [官方文档][Link 1]

*  可以针对每个表或者每个用户，利用MySQL的安全机制禁止使用INSERT语句
 *  insert语句一般不会产生输出

### ①插入完整的行 ###

> INSERT INTO `customers`  
> VALUES(NULL, ‘Pep E.LaPew’, ‘100 Main Street’, ‘Los Angles’, ‘CA’, ‘90046’, ‘USA’, NULL, NULL);

*  注意：
    
     *  主键\[当主键设置了自动增长\]或者不想插入值将值设置为NULL；
     *  不建议这种语法，因为它高度依赖表的结构，如果值设置的顺序不对或者回来表的结构变动就不好了
     *  INTO可以省略
     *  如果想要插入NULL值，直接写成NULL就可以了。

> INSERT `customers`(`cust_name`, `cust_address`, `cust_city`, `cust_state`, `cust_zip`, `cust_country`)  
> VALUES(‘Pep E.LaPew’, ‘100 Main Street’, ‘Los Angles’, ‘CA’, ‘90046’, ‘USA’); 【效果和上面的一样】

*  推荐使用,优点：
    
     *  不担心表的结构变动
     *  可以省略不想设置的值\[前提是这些值必须是允许NULL值或者已经设置了默认值\]
 *  注意：
    
     *  列的顺序可以改变但是值与列一一对应。
     *  通过降低优先级提高整体性能：一般数据检索是最重要的，所以我们可以通过在insert和info之间添加关键字LOW\_PRIORITY以降低INSERT的优先级。注意：UPDATE和DELETE同理
        
        > INSERT LOW\_PRIORITY `table_name`(row\_name1,row\_name2,row\_name3) VALUES(new\_value1, new\_value2, new\_value3);

如果想要给有了默认约束的值插入默认值，有两种方法

> * INSERT INTO `customers` 
    > VALUES(NULL,DEFAULT '100 Main Street', 'Los Angles', 'CA', '90046', 'USA', NULL, NULL);  # 用DEFAULT代替【推荐】
    > * INSERT INTO `customers` 
    > VALUES(NULL,  '100 Main Street', 'Los Angles', 'CA', '90046', 'USA', NULL, NULL); #省略
    > * 如果省略了没有设置默认值的列，该列的值会被设定为NULL。如果省略了设置了NOT NULL约束的列，会报错。

### ② 一次插入多行 ###

MySQL

> INSERT `customers`(`cust_name`,  `cust_address`, `cust_city`,`cust_state`, `cust_zip`,`cust_country`) 
    >VALUES('Pep E.LaPew', '100 Main Street', 'Los Angles', 'CA', '90046', 'USA'), 
    >('M. Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA');

*  注：
    
     *  只要值得次序相同，可以一次插入多行。
     *  MYSQL用单挑INSERT语句处理多个插入比使用多条语句INSERT快。

Mysql方法二：

>START TRANSACTION;         
    INSERT INTO Product VALUES ('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20');
    INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
    INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
    INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
    INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
    INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
    INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
    INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');
    COMMIT;
    
    * START TRANSACTION;   开始插入；COMMIT：确认插入
    
    Oracle DB2
    >INSERT INTO Product VALUES ('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20');
    INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
    INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
    INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
    INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
    INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
    INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
    INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');
    COMMIT;

SQL Server PostgreSQL

>BEGIN TRANSACTION;
    INSERT INTO Product VALUES ('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20');
    INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
    INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
    INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
    INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
    INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
    INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
    INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');
    COMMIT;

Mysql完整的.sql文本。

##########################
    # Populate customers table
    ##########################
    INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
    VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
    INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
    VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
    INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
    VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
    INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
    VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
    INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
    VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');
    
    
    ########################
    # Populate vendors table
    ########################
    INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
    VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
    INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
    VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');
    INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
    VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
    INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
    VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
    INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
    VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
    INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
    VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');
    
    
    #########################
    # Populate products table
    #########################
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');
    
    
    
    #######################
    # Populate orders table
    #######################
    INSERT INTO orders(order_num, order_date, cust_id)
    VALUES(20005, '2005-09-01', 10001);
    INSERT INTO orders(order_num, order_date, cust_id)
    VALUES(20006, '2005-09-12', 10003);
    INSERT INTO orders(order_num, order_date, cust_id)
    VALUES(20007, '2005-09-30', 10004);
    INSERT INTO orders(order_num, order_date, cust_id)
    VALUES(20008, '2005-10-03', 10005);
    INSERT INTO orders(order_num, order_date, cust_id)
    VALUES(20009, '2005-10-08', 10001);
    
    
    ###########################
    # Populate orderitems table
    ###########################
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20005, 1, 'ANV01', 10, 5.99);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20005, 2, 'ANV02', 3, 9.99);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20005, 3, 'TNT2', 5, 10);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20005, 4, 'FB', 1, 10);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20006, 1, 'JP2000', 1, 55);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20007, 1, 'TNT2', 100, 10);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20008, 1, 'FC', 50, 2.50);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20009, 1, 'FB', 1, 10);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20009, 2, 'OL1', 1, 8.99);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20009, 3, 'SLING', 1, 4.49);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20009, 4, 'ANV03', 1, 14.99);
    
    #############################
    # Populate productnotes table
    #############################
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(101, 'TNT2', '2005-08-17',
    'Customer complaint:
    Sticks not individually wrapped, too easy to mistakenly detonate all at once.
    Recommend individual wrapping.'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(102, 'OL1', '2005-08-18',
    'Can shipped full, refills not available.
    Need to order new can if refill needed.'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(103, 'SAFE', '2005-08-18',
    'Safe is combination locked, combination not provided with safe.
    This is rarely a problem as safes are typically blown up or dropped by customers.'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(104, 'FC', '2005-08-19',
    'Quantity varies, sold by the sack load.
    All guaranteed to be bright and orange, and suitable for use as rabbit bait.'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(105, 'TNT2', '2005-08-20',
    'Included fuses are short and have been known to detonate too quickly for some customers.
    Longer fuses are available (item FU1) and should be recommended.'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(106, 'TNT2', '2005-08-22',
    'Matches not included, recommend purchase of matches or detonator (item DTNTR).'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(107, 'SAFE', '2005-08-23',
    'Please note that no returns will be accepted if safe opened using explosives.'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(108, 'ANV01', '2005-08-25',
    'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(109, 'ANV03', '2005-09-01',
    'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(110, 'FC', '2005-09-01',
    'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(111, 'SLING', '2005-09-02',
    'Shipped unassembled, requires common tools (including oversized hammer).'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(112, 'SAFE', '2005-09-02',
    'Customer complaint:
    Circular hole in safe floor can apparently be easily cut with handsaw.'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(113, 'ANV01', '2005-09-05',
    'Customer complaint:
    Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(114, 'SAFE', '2005-09-07',
    'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
    Comment forwarded to vendor.'
    );

### ③从其他表中复制数据 ###

新创建表：

CREATE TABLE concust(
    	con_id INT NOT NULL AUTO_INCREMENT,
    	con_name VARCHAR(50),
    	con_city VARCHAR(20),
    	con_email VARCHAR(255),
    	PRIMARY KEY(con_id)
    )ENGINE=INNODB;;

从customers表中查询数据，然后将结果插入concust表中：

> INSERT `concust`(`con_name`, `con_city`, `con_email`)  
> SELECT `cust_name`, `cust_city`, `cust_email` FROM `customers`;

查看数据：

> SELECT \* FROM `concust`;

*  注意：
    
     *  可以利用这个完成对数据的迁移
     *  INSERT语句的SELECT语句中，可以使用WHERE子句汇总GROUP BY子句等任何SQL语法，除了ORDER BY

## 2.2、更新UPDATE ##

> [官方文档][Link 2]

> #查询
    >SELECT * FROM `customers` WHERE `cust_id` = 10005;

### ①更新单列 ###

>UPDATE `customers` SET `cust_email` = 'elmer@163.com' WHERE `cust_id` = 10005;
    
    >UPDATE `customers` SET `cust_email` = NULL WHERE `cust_id` = 10005;

### ②更新多列 ###

>UPDATE `customers` SET `cust_name` = 'The Fudds',`cust_email` = 'elmer@fudd.com' WHERE `cust_id` = 10005; 【推荐】

只能在PostgreSQL和DB2中可以使用

>UPDATE `customers` SET (`cust_name` ,`cust_email`) = ('The Fudds', 'elmer@fudd.com') WHERE `cust_id` = 10005;

### ③删除单列 ###

> #删除某列:将值设置为null[前提是该值可以设置为null]
    UPDATE `customers` SET `cust_email` = NULL WHERE `cust_id` = 10005;

*  注意：
    
     *  在使用不带where的update时一定要特别注意，一不小心就会更新表中所有行
     *  update可以使用子查询

备注：关联后更新

--
    -- 关联后更新
    --
    mysql> select * from t1;
    +------+
    | a    |
    +------+
    |   10 |
    |    4 |  -- 和t2中的4相等
    |    1 |
    |    2 |
    |    3 |  -- 和t2中的3相等
    |   -1 |
    |    8 |
    +------+
    7 rows in set (0.00 sec)
    
    mysql> select * from t2;
    +------+
    | a    |
    +------+
    |    5 |
    |    4 |  -- 和t1中的4相等
    |    3 |  -- 和t1中的3相等
    +------+
    3 rows in set (0.00 sec)
    
    mysql> update t1 join t2 on t1.a = t2.a set t1.a=100;  -- 先得到t1.a=t2.a的结果集
                                                           -- 然后将结果集中的t1.a设置为100
    Query OK, 2 rows affected (0.03 sec)
    Rows matched: 2  Changed: 2  Warnings: 0
    
    mysql> select * from t1;
    +------+
    | a    |
    +------+
    |   10 |
    |  100 |  -- 该行被更新成100
    |    1 |
    |    2 |
    |  100 |  -- 该行被更新成100
    |   -1 |
    |    8 |
    +------+
    7 rows in set (0.00 sec)

*  **更新有关系的值**

mysql> create table t5 (a int, b int);
    
    mysql>  insert into t5 values(1,1);
    
    mysql> select * from t5;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 |    1 |
    +------+------+
    
    mysql>  update t5 set a=a+1, b=a where a=1;
    Query OK, 1 row affected (0.11 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from t5;
    +------+------+
    | a    | b    |
    +------+------+
    |    2 |    2 |
    +------+------+

## 2.3、删除delete ##

> [官方文档][Link 3]

### ①删除一行数据 ###

>   #查询
    >SELECT * FROM `customers` WHERE `cust_id` = 10006;  
    >   ****
    >   #删除`cust_id` = 10006这一整行
    DELETE FROM `customers` WHERE `cust_id` = 10006;

### ②删除全部行 ###

> #查询
    SELECT * FROM `customers` WHERE `cust_id` = 10006; 
    > ***
    > #删除表的所有行
    TRUNCATE TABLE `concust`; 【推荐】
    DELETE TABLE `concust`; 【不推荐】
    > ***
    >#查询
    SELECT * FROM `concust`;

*  注意：
    
     *  在使用不带where的delete时一定要特别注意，一不小心就会删除表中所有行
        
         *  delete是删除整行而不是删除某一列，如果需要删除指定的列，请用update。
         *  delete子句删除整行但是不删除表本身。delete子句不带where可以删除表的所有行
         *  如果想要删除表的所有行,不要用delete，用TRUNCATE TABLE更快\[TRUNCATE 实际是删除原来的表并重新创建一个同样结构的表，并不是逐行删除表中的数据\]
     *  可以限制和控制delete子句的使用
     *  与SELECT子句不同的是，DELETE语句中不能使用GROUP BY、HAVING和GROUP BY三列子句，而只能使用WHERE子句。因为GROUP BY和HAVING子句是从表中选取数据时用来改变抽取数据的，而ORDER BY是用来指定取得结果显示顺序的，因此，在表中数据它们都起不到什么作用

### 规则 ###

*  绝对不要使用不带where子句的update或者delete语句，除非确定想要更新和删除表的每一行
 *  确保每个表都有主键，并且在搜索中尽可能使用主键
 *  再对update或者delete语句使用where之前，请用select测试确保操作正确
 *  使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行

## 2.4、REPLACE ##

> [官方文档][Link 4]

mysql> create table t4(a int primary key auto_increment, b int);
    
    mysql> insert into t4 values(NULL, 10),(NULL,11),(NULL,12);
    
    mysql> select * from t4;
    +---+------+
    | a | b    |
    +---+------+
    | 1 |   10 |
    | 2 |   11 |
    | 3 |   12 |
    +---+------+
    
    mysql> insert into t4 values(1, 100);  -- 报错，说存在重复的主键记录 "1"
    ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
    
    mysql> replace into t4 values(1, 100); -- 替换该主键对应的值  
    Query OK, 2 rows affected (0.03 sec)   -- 两行记录受到影响
    
    mysql> select * from t4;
    +---+------+
    | a | b    |
    +---+------+
    | 1 |  100 |  -- 已经被替换
    | 2 |   11 |
    | 3 |   12 |
    +---+------+
    3 rows in set (0.00 sec)
    -----
    -- replace的原理是：先delete，在insert
    -----
    
    mysql> replace into t4 values(5, 50);  -- 没有替换对象时，类似插入效果
    Query OK, 1 row affected (0.03 sec)    -- 只影响1行
    
    mysql> select * from t4;
    +---+------+
    | a | b    |
    +---+------+
    | 1 |  100 |
    | 2 |   11 |
    | 3 |   12 |
    | 5 |   50 |  -- 插入了1行
    +---+------+
    4 rows in set (0.00 sec)

replace原理更明显的例子

mysql> create table t6(a int primary key, b int auto_increment, c int, key(b));
    
    mysql> insert into t6 values(10, NULL, 100),(20,NULL,200);  -- b自增长
    
    mysql> select * from t6;
    +----+---+------+
    | a  | b | c    |
    +----+---+------+
    | 10 | 1 |  100 |  -- b为1
    | 20 | 2 |  200 |  -- b为2
    +----+---+------+
    
    mysql> replace into t6 values(10,NULL,150);  -- 将a=10的替换掉
    
    mysql> select * from t6;
    +----+---+------+
    | a  | b | c    |
    +----+---+------+
    | 10 | 3 |  150 |  -- 替换后b从1变成了3，说明是先删除，再插入
    | 20 | 2 |  200 |
    +----+---+------+

insert on duplicate 效果和 replace类似

mysql>  select * from t4;
    +---+------+
    | a | b    |
    +---+------+
    | 1 |  100 |
    | 2 |   11 |
    | 3 |   12 |
    | 5 |   50 |
    +---+------+
    
    mysql> insert into t4 values(1,1);  -- 插入报错，存在key为1的记录
    ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
    
    mysql> insert into t4 values(1,1) on duplicate key update b=1;  -- 带上on duplicate参数
                                                                   -- 非SQL标准，不推荐
    Query OK, 2 rows affected (0.03 sec)
    
    mysql> select * from t4;
    +---+------+
    | a | b    |
    +---+------+
    | 1 |    1 |  -- 该行的b列从100被替换成1
    | 2 |   11 |
    | 3 |   12 |
    | 5 |   50 |
    +---+------+

insert ignore

mysql> insert ignore into t4 values(1,1);   -- 忽略重复的错误
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> show warnings;
    +---------+------+---------------------------------------+
    | Level   | Code | Message                               |
    +---------+------+---------------------------------------+
    | Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
    +---------+------+---------------------------------------+
    1 row in set (0.00 sec)

[Link 1]: https://dev.mysql.com/doc/refman/8.0/en/insert.html
[Link 2]: http://dev.mysql.com/doc/refman/5.7/en/update.html
[Link 3]: https://dev.mysql.com/doc/refman/8.0/en/delete.html
[Link 4]: http://dev.mysql.com/doc/refman/5.7/en/replace.html