insert + select

insert into product_type_commission select id,5,1,1,0,0,0,0,0,0 from product_type where title='notebook' and is_physical=0;

update table1,table2

begin;ALTER TABLE `customer` ADD COLUMN `cutoff_time` TIMESTAMP NOT NULL default '0000-00-00 00:00:00';update customer,agent set customer.cutoff_time = agent.cutoff_time where customer.id = agent.id;ALTER TABLE `agent` DROP COLUMN `cutoff_time`;commit;

update table1 set field1 = (select value from table2)

UPDATE	transactionSET	transaction.total_sold_price = (		SELECT			SUM(transaction_item.price)	FROM			transaction_item		WHERE transaction_item.transaction_id = 100	)WHERE	transaction.id = 100

update table1, (select * from other) as table2 set table1.field1 = table2.field1

UPDATE	transaction,(	SELECT						SUM(product_item.bought_price) AS total_bought_price, transaction_item.transaction_id					FROM						transaction_item			    	WHERE						transaction_item.transaction_id IN ( '123','456' )			     ) as totalSET	transaction.total_bought_price = total.total_bought_priceWHERE	transaction.id = total.transaction_id

join + subquery

select u.*,t.category,t.items,t.[property] from tb_sysregchkusers as u left join (select a.items as category, b.* from (select id, items from tb_sysregchktask where categoryid=0) as a left join tb_sysregchktask as b on b.categoryid=a.id ) as t on u.taskID=t.idselect * from tb_sysregchklog where CONVERT(datetime,CONVERT(varchar(10),checkTime,120)) between convert(datetime,'2007-12-12') and convert(datetime,'2007-12-12')

select DISTINCT user_point_history.user_id,user.username,(select count(id) from transaction where id = user_point_history.transaction_id) as transactions,(SELECT SUM(u_p_h.points) FROM user_point_history as u_p_h WHERE u_p_h.type != 'RDMP' AND u_p_h.status IN('pr','ac') AND u_p_h.user_id = user_point_history.user_id) as total_points_earned,(SELECT SUM(u_p_h.points) FROM user_point_history as u_p_h WHERE u_p_h.type = 'RDMP' AND u_p_h.status IN('pr','ac') AND u_p_h.user_id = user_point_history.user_id) as total_points_redeemedfrom user_point_history,user where user_point_history.user_id = user.id;

(total_points_earned - total_points_redeemed) as current_balance_points

select user_id, username, transactions, total_points_earned, total_points_redeemed, (total_points_earned - total_points_redeemed) as current_balance_pointsfrom (select DISTINCT user_point_history.user_id,user.username,(select count(id) from transaction where id = user_point_history.transaction_id) as transactions,(SELECT SUM(u_p_h.points) FROM user_point_history as u_p_h WHERE u_p_h.type != 'RDMP' AND u_p_h.status IN('pr','ac') AND u_p_h.user_id = user_point_history.user_id) as total_points_earned,(SELECT SUM(u_p_h.points) FROM user_point_history as u_p_h WHERE u_p_h.type = 'RDMP' AND u_p_h.status IN('pr','ac') AND u_p_h.user_id = user_point_history.user_id) as total_points_redeemedfrom user_point_history,user where user_point_history.user_id = user.id) as user_performance;

subquery作为一个字段使用

select product_type_attribute.*,(select 'selected' from product_type_attribute_set where product_type_attribute_set.product_type_attribute_id = product_type_attribute.id and product_type_attribute_set.product_type_id = 26) as selectedfrom product_type_attribute;