常用容易忘记的mysql命令积累（2023-06-28）

[yaogengzhu]

操作表的常用sql

drop table table_name # 删除表

delete from table_name # 删除表数据但是保留表结构

show create table table_name # 查看create 语句

desc table_name # 查看表结构

show index from table_name # 查看表的索引

alter table table_name convert to character set charset_name;  # 更新表的字符集 
alter table goods convert to character set utf8;  # demo

[yaogengzhu]

数据分组

# 按mode 分组，并且找出mode的数量
select mode, count(*) as count from a group by mode
# 按mode 分组，并且找出mode的数量并且排序
select mode, count(*) as count from a group by mode order by count desc
# 按mode 分组，过滤部分分组，并且找出mode的数量并且排序
select mode, count(*) as count from a group by mode having mode != 'xx' order by count desc

[yaogengzhu]

如何向库里一次插入巨大的数据

knexjs 方式

复制代码

const data = [
  { name: 'John', email: 'john@example.com' },
  { name: 'Jane', email: 'jane@example.com' },
  // ...more rows
];

knex.batchInsert('users', data, 1000) // 1000 rows at a time
  .then(() => {
    console.log('Data inserted successfully');
  })
  .catch((err) => {
    console.error(err);
  });

SQL

const data = [
  { name: 'John', email: 'john@example.com' },
  { name: 'Jane', email: 'jane@example.com' },
  // ...more rows
];

const values = data.map(row => `('${row.name}', '${row.email}')`).join(',');
const query = `INSERT INTO users (name, email) VALUES ${values}`;

knex.raw(query)
  .then(() => {
    console.log('Data inserted successfully');
  })
  .catch((err) => {
    console.error(err);
  });

[yaogengzhu]

如何查询当前表的大小

去掉AND的条件，查出整个数据的表的大小

SELECT table_name AS `Table`,
ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)`
FROM information_schema.TABLES
WHERE table_schema = "test2" AND table_name = 't';