MySQL必知必会-插入、增加、修改、删除

oneNeko 于 2022-02-21 发布

本篇主要内容为增删改

增加数据

基本语法简单,但是并不安全,因为这种语句高度依赖次序。应该尽量使用明确给定列名的写法,即使表结构发生变化,也能继续发挥作用

#基本语句
$ INSERT INTO Customers VALUES(
    NULL,
    'Pep E. LaPew',
    '100 Main Street'
    )

#指定列名
$ INSERT INTO customers (
    cust_name,
    cust_address
    )
    VALUES(
        'Pep E. LaPew',
        '100 Main Street'
    );

# 使用单条INSERT语句处理多个插入比使用多条INSERT语句快
$ INSERT INTO customers (
    cust_name,
    cust_address
    )
    VALUES(
        'Pep E. LaPew',
        '100 Main Street'
    ),
    (
        'M. Martian',
        '42 Galaxy Way'
    );

# 插入查询(MySQL并不要求列名匹配,使用的是列的位置)
$ INSERT INTO customers(
    cust_name,
    cust_address
    )
    SELECT cust_name,cust_address
    FROM custnew;

更新数据

UPDATE可以更新特定行或是所有行

不要忽略WHERE子句,因为稍不注意就会更新表中所有行

$ UPDATE customers
    SET cust_email='elmer@fudd.com',
        cust_name='The Fudds'
    WHERE cust_id=10005;

UPDATE语句中可以使用子查询,使得能用SELECT语句检索出的数据更新列数据

如果使用UPDATE更新多行,并且在更新这些行中的一行或多行出现一个错误,则整个UPDATE操作会被取消(已更新的行会恢复到原来的值)。使用IGNORE关键字,即使发生错误,也继续更新,用法UPDATE IGNORE customers ...

删除数据

$ DELETE FROM customers WHERE cust_id=10006;

不要忽略WHERE子句,因为稍不注意就会删除表中所有行 如果想从表中删除所有行,使用TRUNCATE TABLE语句速度更快(TRUNCATE实际上是删除原来的表并重新创建空表)

更新和删除的指导原则

创建和操纵表

# 创建表 单主键(值是唯一的)
$ CREATE TABLE customers(
    cust_id         int         NOT NULL AUTO_INCREMENT,
    cust_name       char(50)    NOT NULL,
    cust_address    char(50)    NULL,
    PRIMARY KEY(cust_id)
)ENGINE=InnoDB;

# 创建表 组合主键(组合是唯一的)
$ CREATE TABLE customers(
    cust_id         int         NOT NULL AUTO_INCREMENT,
    cust_name       char(50)    NOT NULL,
    cust_address    char(50)    NULL,
    PRIMARY KEY(cust_id,cust_name)
)ENGINE=InnoDB;

如果仅想在一个表不存在时创建它,应该在表名后给出IF NOT EXISTS

注意NULL和空串是不一样的,NULL是没有值,''是有效的值

AUTO_INCREMENT

AUTO_INCREMENT告诉MySQL,本列每当增加一行时自动增量。每个表只允许一个AUTO_INCREMENT列,而且它必须被索引

使用SELECT last_insert_id()获取最后一个AUTO_INCREMENT

指定默认值

如果在插入行时没有给出值,MySQL允许指定此时使用的默认值,使用DEFAULT关键字指定

$ CREATE TABLE orderitems(
    order_num       int     NOT NULL,
    order_item      int     NOT NULL,
    quantity        int     NOT NULL DEFAULT 1 
);

与大多数DBMS不一样,MySQL不允许使用函数作为默认值

更新表

为更新表定义,可使用ALTER TABLE语句。但是理想状态下,党表中存储数据以后,此表就不应该再被更新,在表的设计过程中需要花费大量时间来考虑,一边后期不会该表进行大的改动

# 增加
$ ALTER TABLE vendors
    ADD vend_phone CHAR(20);

# 删除
$ ALTER TABLE vendors
    DROP COLUMN vend_phone;

# 定义外键
$ ALTER TABLE orderitems
    ADD CONSTRAINT fk_orderitems_orders
    FOREGN KEY(order_num) REFERENCES orders(order_num);

$ ALTER TABLE orderitems
    ADD CONSTRAINT fk_orderitems_products
    FOREGN KEY(prod_id) REFERENCE products(prod_id);

使用ALTER TABLE要极为小心,应该在进行改动前做一个完整的备份(模式和数据的备份)

删除表

删除表没有确认,也不能撤销,执行这条语句将永远删除该表

DROP TABLE customers2;

重命名表

使用RENAME TABLE语句重命名一个表

$ RENAME TABLE customers2 TO customers;

$ RENAME TABLE backup_customers TO customers
               backup_vendors TO vendors;

相关

MySQL必知必会-查询

MySQL必知必会-插入、增加、修改、删除

MySQL必知必会-视图、存储过程

MySQL必知必会-游标、触发器、事务管理

MySQL必知必会-字符集、校对