MySQL Tips and Tricks

未经许可请勿转载。
Please do not reprint this article without permission.

Useful Docs

• MySQL Documentation
• left join，on与where区别，参考关于 MySQL LEFT JOIN 你可能需要了解的三点
• 数据筛选，参考Mysql关键字之Group By
• 查询，参考MYSQL limit用法
• 修改密码，参考Ubuntu下修改MySQL密码
• 时间戳字段自动更新时间，参考Mysql 如何设置字段自动获取当前时间
• 更新数据库字段，参考mysql alter命令
• 查看字段，参考mysql 查看字段详情
• 数据统计，参考MySQL查询count(*)、count(1)、count(field)的区别收集
• 数据库优化，参考Mysql优化之explain详解，MySQL Explain详解
• MySQL-Workbench安装，参考ubuntu16.04安装MySQL Workbench 6.3.10
• 循环插入数据，参考Mysql 循环插入10000条数据
• 远程访问，参考远程访问mysql出现Access denied for user 'root'@'的解决方法

MySQLdb - python2

• cursor()
• execute()
• fetchall()/fetchmany()/fetchone()
• commit()

pymysql - python3

Common Usage

• Select 查 (select, as, left join ... on, where, group by, order by, limit, count, sum)

  SELECT task.id, task.name, task.image_num, task.status, task.create_time,
  IFNULL(SUM(subtask.image_label), 0),
  category.name, category.feature_model, category.id, IFNULL(SUM(subtask.template_label), 0)
  from t_template_task as task 
  left join t_template_subtask as subtask on task.id = subtask.task_id
  left join t_template_category as category on task.category_id = category.id
  WHERE task.label_type = 1 and task.status in (%s) group by task.id 
  order by task.id desc LIMIT %s, %s;
  
  select acc.name, subtask.id, sum(detect.bndbox_num), count(1)
  from 
  t_label_account as acc, 
  t_template_task as task, 
  t_template_subtask as subtask, 
  t_template_image as image, 
  t_template_detect as detect 
  where 
  subtask.owner_id = acc.id 
  and task.label_type = 2 
  and task.id = subtask.task_id 
  and subtask.id = image.subtask_id 
  and image.id = detect.image_id 
  and detect.label_time > '2019-09-09 00:00:00' 
  and acc.name in ('user1', 'user2') 
  group by acc.id, subtask.id;
  

• Insert 增 (insert into, values)

  INSERT INTO t_template_detect(image_id, label_data) values(%s, %s);
  

  Insert data iteratively:

  DROP PROCEDURE IF EXISTS proc_initData;
  DELIMITER $
  CREATE PROCEDURE proc_initData()
  BEGIN
      DECLARE i INT DEFAULT 6;
      WHILE i<=40 DO
          INSERT INTO t_label_account(name, password, level) VALUES(concat('user', i), '123456', 0);
          SET i = i+1;
      END WHILE;
  END $
  CALL proc_initData();
  

• Update 改 (update, set, where)

  UPDATE t_template_detect SET label_data = %s, label_time = %s WHERE image_id = %s;
  

• Delete 删 (delete from, where)

  DELETE FROM t_template_repertory where id = %s;
  

• Alter 修改表（alter, add, drop, modify, change, rename...）

  alter table t_label_image
  modify column label_time timestamp NULL DEFAULT ON UPDATE CURRENT_TIMESTAMP;
  

• Show 查看表

  show create table tb_test01;       #查看表结构
  show table status like "tb_test01" #查看表创建信息
  show columns from tb_test01;       #详细查看表各字段 类型约束设置