未經(jīng)許可請(qǐng)勿轉(zhuǎn)載。
Please do not reprint this article without permission.

Useful Docs

• MySQL Documentation
• left join渣刷，on與where區(qū)別鹦肿，參考關(guān)于 MySQL LEFT JOIN 你可能需要了解的三點(diǎn)
• 數(shù)據(jù)篩選，參考Mysql關(guān)鍵字之Group By
• 查詢辅柴，參考MYSQL limit用法
• 修改密碼箩溃，參考Ubuntu下修改MySQL密碼
• 時(shí)間戳字段自動(dòng)更新時(shí)間，參考Mysql 如何設(shè)置字段自動(dòng)獲取當(dāng)前時(shí)間
• 更新數(shù)據(jù)庫(kù)字段碌嘀，參考mysql alter命令
• 查看字段涣旨，參考mysql 查看字段詳情
• 數(shù)據(jù)統(tǒng)計(jì)，參考MySQL查詢count(*)股冗、count(1)霹陡、count(field)的區(qū)別收集
• 數(shù)據(jù)庫(kù)優(yōu)化，參考Mysql優(yōu)化之explain詳解止状，MySQL Explain詳解
• MySQL-Workbench安裝烹棉，參考ubuntu16.04安裝MySQL Workbench 6.3.10
• 循環(huán)插入數(shù)據(jù)，參考Mysql 循環(huán)插入10000條數(shù)據(jù)
• 遠(yuǎn)程訪問怯疤，參考遠(yuǎn)程訪問mysql出現(xiàn)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;       #查看表結(jié)構(gòu)
  show table status like "tb_test01" #查看表創(chuàng)建信息
  show columns from tb_test01;       #詳細(xì)查看表各字段 類型約束設(shè)置