😂😂😂MySQL语法练习

数据库语法练习

连接两个表内容，插入表temporary（注意字段...）
-- INSERT INTO temporary(PosID,CommodityID,ShelfID,ShelfSide,Amount,HoldAmount)
      -- SELECT tescr.PosID, `is`.CommodityID, `is`.ShelfID, `is`.ShelfSide, `is`.Amount, `is`.HoldAmount
      -- FROM initialize_shelves `is`
      -- JOIN tm_eim_storage_capacity_right tescr
      -- ON `is`.CommodityID = tescr.CommodityID
      
插入表tesd，忽略重复字段（不更新...）
-- INSERT ignore INTO tt_eim_storage_detail(PosID,CommodityID,ShelfID,ShelfSide,Amount,HoldAmount) SELECT * FROM    temporary

清空表的内容
-- DELETE FROM tt_eim_order
-- DELETE FROM temporary

对BatchID不重复计数
-- SELECT COUNT(DISTINCT BatchID) FROM batch_shelf bs

对ShelfID计数
-- SELECT count(ShelfID) FROM batch_shelf bs

略
-- SELECT count(DISTINCT OrderID) FROM tt_eim_order teo

查询左表有，右表无的数据
-- SELECT teo.* FROM tt_eim_order teo LEFT JOIN batch_order bo ON (teo.OrderID = bo.OrderID) WHERE bo.OrderID IS NULL

查询两表相同数据
-- SELECT * FROM tt_eim_order teo INNER JOIN batch_order bo ON teo.OrderID = bo.OrderID

查询BatchID出现次数大于2的数据（待更新...）
-- SELECT * FROM batch_shelf WHERE BatchID IN (SELECT bs.BatchID FROM batch_shelf bs GROUP BY bs.BatchID HAVING COUNT(BatchID)>2)

更新（赋值）表的字段
-- UPDATE tt_eim_storage_detail tesd set tesd.HoldAmount = 0