0005 SQL 手册

复（学）习一下 SQL

MySQL 实例

Index 目录

• …
• like
• …
• MySQL 更新某一行
• MySQL 已有字段更新（包括重命名 Rename field）
• MySQL 添加字段 Add field
• MySQL 利用已有数据创建新一行数据
• …
• MongoDB select * from table
• MongoDB describe table(collection)
• 复制行

like 用法

SELECT endAt FROM `lives` WHERE endAt like '2717%'

MySQL 已有字段更新（包括重命名 Rename field）

update settings set guard_amount = 333 where id = 1;

MySQL 已有字段更新（包括重命名 Rename field）

alter table rates change column deleted isDeleted tinyint(1) default 0;

MySQL 添加字段 Add field

alter table rates add isVisible tinyint(1) default 1;

MySQL 利用已有数据创建新一行数据

insert into rates (liveId, userId, rateNum, text, visible, createdAt, deleted, audited) select liveId, userId, rateNum, text, visible, createdAt, deleted, audited from rates where id = 1;

MongoDB select * from table

db.tableName.findOne();

MongoDB describe table(collection)

var col_list = db.tableName.findOne();
for (var col in col_list) { print (col); }
for (var col in col_list) { print (col, typeof col_list[col]); }

复制行

unique 字段可以直接指定值，多行且不重复可以通过写脚本，console.log 到文本中再复制粘贴

insert into lives (name, subtitle, liveId, type, startAt, endAt, price, originalPrice, intro, banner, anchorId, categoryId, course, assistant, messageVisible, isActive, isShowSection, exerciseIntro, extra, isHidden, isVipFree)
select name, subtitle, 1111, type, startAt, endAt, price, originalPrice, intro, banner, anchorId, categoryId, course, assistant, messageVisible, isActive, isShowSection, exerciseIntro, extra, isHidden, isVipFree
from lives
where id = 1;

s = (id) => {
  return `
insert into lives (name, subtitle, liveId, type, startAt, endAt, price, originalPrice, intro, banner, anchorId, categoryId, course, assistant, messageVisible, isActive, isShowSection, exerciseIntro, extra, isHidden, isVipFree)
select name, subtitle, ${id}, type, startAt, endAt, price, originalPrice, intro, banner, anchorId, categoryId, course, assistant, messageVisible, isActive, isShowSection, exerciseIntro, extra, isHidden, isVipFree
from lives
where id = 1;
`;
};
for (let i = 100000; i < 100040; i++) {
  console.log(s(i));
}