MYSQL常用语句

 
#批量快速插入
INSERT INTO users2 SELECT * FROM users
INSERT INTO users2 (user_id,email) SELECT user_id,email FROM users
 
#替换
SELECT REPLACE(email ,',','.') emails FROM users 
 
#替换，拼接更新
UPDATE users SET email = CONCAT('start',REPLACE ( email, ',', '.' ), 'end')
UPDATE users SET email = 11 WHERE EXISTS ( SELECT 1 FROM users2 WHERE users.user_id = users2.user_id AND users2.user_name = 'nn')
 
#时间
SELECT user_id,email,DATE_FORMAT(FROM_UNIXTIME(change_time),'%Y-%m-%d %H:%i:%s') times FROM users WHERE start_time >= UNIX_TIMESTAMP( '2000-01-01' ) 
 
#去重
SELECT email,count(DISTINCT user_id) num FROM users 
 
#格式化金额 CAST(value AS datatype)
SELECT CAST(sum(vote) AS DECIMAL(20,6)) total FROM users  
 
#null to 0
select COALESCE(sum(g1.goods_number*g1.goods_price), 0) m from order_goods g1 where g1.id=10