MySQL操作命令

-- 查找订单详情
select * from order_summary where order_id=10214839442;
select * from order_voyage_info where order_id='10214839442';
select * from order_trailer_overview where order_id='10214839442';
select * from order_fee_details where order_id=10214839442;

-- 查询字段重复的
SELECT * FROM ship_period where ship_product_id in
(select ship_product_id from ship_period group by ship_product_id having count(ship_product_id)>1);

-- 查询字段为NULL的数据
SELECT * FROM ship_period WHERE departure_port_id is NULL;

-- 删除destination_port_id为空的关联数据
DELETE ship_period,container_info FROM ship_period INNER JOIN container_info ON
ship_period.ship_product_id = container_info.ship_product_id WHERE ship_period.destination_port_id IS NULL;

-- 删除departure_port_id为空的字段
DELETE ship_period,container_info FROM ship_period INNER JOIN container_info ON
ship_period.ship_product_id = container_info.ship_product_id WHERE ship_period.departure_port_id IS NULL;

-- 根据类型为20GP更新价钱-150
UPDATE container_info SET universal_container_price=universal_container_price-150,platform_container_price=platform_container_price-150,
total_fee=total_fee-150 WHERE container_type='20GP';

-- 根据类型为40HQ更新价钱-300
UPDATE container_info SET universal_container_price=universal_container_price-300,platform_container_price=platform_container_price-150,
total_fee=total_fee-300 WHERE container_type='40HQ';

-- 根据船公司id更新所属供应商
UPDATE container_info SET supplier_id=1 WHERE ship_company_id=995926352600588288 OR ship_company_id=2 OR ship_company_id=1;

-- 根据is_single_pair更新显示双边
UPDATE container_info SET instructions='双边' WHERE is_single_pair=2 AND ship_company_id=2;

-- 根据is_single_pair更新显示单边
UPDATE container_info SET instructions='单边' WHERE is_single_pair=1 AND ship_company_id=2;

-- 更新起始码头港口或者目的码头港口名字
UPDATE ship_period SET start_place='四会永泰' WHERE start_place='永泰';

-- 查询拖车价钱四级地址
SELECT * FROM truck_price WHERE door_address='广东省广州市花都区狮岭镇';
SELECT * FROM truck_price WHERE tab_id=4566398647;
select * FROM sys_area WHERE parent_id='440305' order BY region_code ASC;
select * from sys_area where region_name='南山区';

-- 根据港口名字更新港口拼音
UPDATE ship_period SET start_place_spell=pinyin(start_place),destination_place_spell=pinyin(destination_place);

-- 查询九月份注册量
SELECT count( 1 ) '注册人数' FROM member WHERE (identity !=1 OR identity is NULL) AND create_date BETWEEN '2018-9-1 0:0:0' AND '2018-10-1 0:0:0' ;

-- 查询船期表ship_product_id不在价钱表的数据
SELECT * FROM ship_period WHERE ship_product_id NOT IN ( SELECT ship_product_id FROM container_info);

-- 根据类型为20GP更新价钱+150
UPDATE container_info SET universal_container_price=universal_container_price-150,platform_container_price=platform_container_price-150,
total_fee=total_fee-150 WHERE container_info.ship_product_id in (SELECT ship_product_id FROM ship_period WHERE ship_company_id=2 AND start_place='江阴'AND remarks=1) AND container_type='20GP';

-- 根据类型为40HQ更新价钱+300
UPDATE container_info SET universal_container_price=universal_container_price-300,platform_container_price=platform_container_price-300,
total_fee=total_fee-300 WHERE container_info.ship_product_id in (SELECT ship_product_id FROM ship_period WHERE ship_company_id=2 AND start_place='江阴'AND remarks=1) AND container_type='40HQ';

-- 查询container_info中ship_product_id不在ship_product_id中ship_product_id
SELECT * FROM container_info where ship_product_id NOT in (select ship_product_id from ship_period);
-- 查询船期表ship_product_id不在价钱表的数据
SELECT * FROM ship_period WHERE ship_product_id NOT IN ( SELECT ship_product_id FROM container_info);