mysql之insert

前言

简单记录insert命令的使用

用法

  • insert into

INSERT INTO student(NAME, salary, created_at) VALUES(‘xing‘, 900, CURDATE());
  • insert into values

  1. 需要添加所有列的值
INSERT INTO student VALUES(1, ‘xing‘, 900, CURDATE());
  • insert into select from

  1. 通常用于处理数据后插入已存在的表
  2. insert into中的列数量必须和select的保持一致,否则会报错的
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. 该语法解释时会报错,但不影响执行
CREATE TABLE student3 (
	SELECT
		NAME,
		salary
	FROM
		student
);

相关推荐