sql语句使用

1.需求：按照查询顺序条件的排列顺序返回

要点：where......in

SELECT * FROM t_device WHERE guid IN ('guid1','guid2') order by field(guid,'guid1','guid2')

---

2.需求：替换数据表中的字段中相同数据

要点：replace函数

语法：replace(object,search,replace)
语义：把object对象中出现的的search全部替换成replace。

UPDATE t_device set 'dev_code' = replace('dev_code','oldstr','newstr')

---

3.需求：实现有条件的联合查询

要点：条件约束的位置

正确例子：【在联合查询之后做整体条件约束】

SELECT 
t_user.user_owner AS first_id,
t_user_1.user_name AS first_name,
v_cash_record.uid AS second_id,
v_cash_record.user_name AS second_name,
v_cash_record.* from v_cash_record 
LEFT JOIN t_user ON t_user.uid=v_cash_record.uid 
LEFT JOIN t_user AS t_user_1 ON t_user_1.uid=t_user.user_owner 
where v_cash_record.rid=3 

错误的例子：注意【v_cash_record.rid=3】位置

SELECT 
t_user.user_owner AS first_id,
t_user_1.user_name AS first_name,
v_cash_record.uid AS second_id,
v_cash_record.user_name AS second_name,
v_cash_record.* from v_cash_record 
LEFT JOIN t_user ON (t_user.uid=v_cash_record.uid and v_cash_record.rid=3) 
LEFT JOIN t_user AS t_user_1 ON t_user_1.uid=t_user.user_owner  

错误原因是：(t_user.uid=v_cash_record.uid and v_cash_record.rid=3) 恒为true，整体条件表达式的真值取决于and前面的条件【t_user.uid=v_cash_record.uid】，恒为真；and后面的条件【v_cash_record.rid=3】只是陪跑，并不影响整体表达式的值。

结论：联合查询时，不管是外联，左联，右联，on后面都只能跟着一个条件。

另一种正确写法的例子：【在MySQL数据库中实测，低版本创建视图时可能会报错】

SELECT 
t_user.user_owner AS first_id,
t_user_1.user_name AS first_name,
v_cash_record_1.uid AS second_id,
v_cash_record_1.user_name AS second_name,
v_cash_record_1.* FROM (SELECT * FROM v_cash_record WHERE rid=3) AS v_cash_record_1
LEFT JOIN t_user ON t_user.uid=v_cash_record_1.uid 
LEFT JOIN t_user AS t_user_1 ON t_user_1.uid=t_user.user_owner

---

4.需求：将字段数量不同的两张表（t_table1,t_table2）连接，其中 t_table1 字段包含 t_table2 的字段

要点：union，lack_col_1和lack_col_2为t_table1独有字段。

select * from t_table1 union SELECT *,NULL AS lack_col_1,NULL AS lack_col_2 from t_table2