mySql常用sql語句

SELECT * FROM table1SELECT * FROM table1 WHERE id>=1 AND id<=20 AND id IN(1,2,3,4,5,6,7,8,9) OR (id BETWEEN 1 AND 10)SELECT * FROM table3 WHERE t3Date >= '2011-08-10'SELECT * FROM table1 WHERE id IS NOT NULL GROUP BY id,t1Name ORDER BY id,t1NameSELECT SUM(id) FROM table1SELECT COUNT(*) FROM table1SELECT * FROM (SELECT a.*,b.t2Value FROM table1 a LEFT JOIN table2 b ON a.id = b.t2Table1Id) aSELECT a.*,b.* FROM table1 a LEFT JOIN table2 b ON a.id = b.t2Table1Id/*顯示a表中所有資料與b表中所有符合關聯條件的資料，b表中有多條滿足a表條件的行，則b表所有滿足的行都顯示，結果中對應a表顯示部分的內容相同。如果a表中某條資料在b表中沒有關聯資料，則查詢結果該條記錄對應b表列為null空*/SELECT a.*,b.* FROM table1 a RIGHT JOIN table2 b ON a.id = b.t2Table1Id/*顯示b表中所有資料與a表中所有符合關聯條件的資料。如果b表中某條資料在a表中沒有關聯資料，則查詢結果該條記錄對應a表列為null空*/SELECT a.*,b.* FROM table1 a INNER JOIN table2 b ON a.id = b.t2Table1Id/*只顯示a表b表中所有與對方有對應的資料*/-- 返回資料庫最後插入的id值SELECT @@IDENTITY AS id;SELECT LAST_INSERT_ID() AS id;UPDATE table4 a LEFT JOIN table5 b ON a.table5_id = b.id SET a.name = CONCAT(a.name, '.html') WHERE b.id = 1;-- CONCAT(a.name, '.html')CONCAT()將兩字串串連後返回-- 批插入INSERT INTO table1(id, t1Name) VALUES(1, 'name1'), (2, 'name2'), (3, 'name3');CREATE TABLE table6 AS  SELECT a.id,b.name  FROM table4 a LEFT JOIN table5 b ON a.table5_id = b.id WHERE b.id = 1;-- 用查詢結果建立新表，可用來複製表