前言

简单记录insert命令的使用

用法

  • insert into

1
INSERT INTO student(NAME, salary, created_at) VALUES('xing', 900, CURDATE());

  • insert into values

  1. 需要添加所有列的值
    1
    INSERT INTO student VALUES(DEFAULT, 'xing', 900, CURDATE());

  • insert into select from

  1. 通常用于处理数据后插入已存在的表
  2. insert into中的列数量必须和select的保持一致，否则会报错的
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    INSERT INTO GoodsUrl (
            id,
            url,
            created_at,
            updated_at
    ) SELECT
            Goods.id AS id,
            CONCAT_WS(
                    '/',
                    Domains.`name`,
                    Goods.sitedir
            ) AS url,
            Goods.created_at AS created_at,
            Goods.updated_at AS updated_at
    FROM
            Goods,
            Domains
    WHERE
            Goods.domain_id = Domains.id
    AND Goods.is_domain = 0

  • create table newtb (selct from)

  1. 通常用于复制表数据
  2. 新表newtb必须是不存在的表，否则会报错
  3. 该语法解释时会报错，但不影响执行
    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE student3 (
    	SELECT
    		NAME,
    		salary
    	FROM
    		student
    );