一、插入資料
(1)INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')
(2)INSERT INTO Persons (LastName, Address)
VALUES ('Wilson', 'Champs-Elysees'),('Wilson2', 'Champs-Elysees2')
(3)INSERT INTO Persons select * from Persons2
(4)SELECT LastName,FirstName INTO Persons_backup FROM Persons
二、删除資料
(1)DELETE FROM Person WHERE LastName = 'Wilson'
(2)drop Person
三、修改資料
(1)UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'
WHERE LastName = 'Wilson'
(2)update p set p.Address = 'Zhongshan 23'
from Person p inner join City c on p.cityId = c.id where c.name = '北京'
四、查詢語句
1、普通查詢:
(1)SELECT LastName,FirstName FROM Persons WHERE City='Beijing'
ORDER BY Company DESC, OrderNumber ASC
(2)SELECT DISTINCT Company FROM Orders
(3)SELECT * FROM Persons WHERE (FirstName='Thomas' OR FirstName='William')
AND LastName='Carter'
(4)SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')
(5)SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Adams' AND 'Carter'
(6)SELECT TOP 2 * FROM Persons (隻sqlserver)
(7)SELECT * FROM Persons LIMIT 5 (隻mysql)
(8)SELECT po.OrderID, p.LastName, p.FirstName FROM Persons AS p,
Product_Orders AS po WHERE p.LastName='Adams' AND p.FirstName='John'(别名)
(9)SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2(合并結果集,union all是直接連接配接,取到得是所有值,
記錄可能有重複;union 是取唯一值,記錄沒有重複)
2、like查詢
(1)SELECT * FROM Persons WHERE City LIKE '%' + 'N' + '%'
(2)SELECT * FROM Persons WHERE City LIKE 'N%'(這種方式可能用到索引)
(3)SELECT * FROM user where userName like '發_1_2'(_僅替代一個字元)
(4)SELECT top 10 * FROM user where userId like '[23]%'
([charlist]字元列中的任何單一字元,隻sqlserver有用)
(5)SELECT top 10 * FROM user where userId like '[^23]0%'
([^charlist]不在字元列中的任何單一字元,隻sqlserver有用)
3、關聯查詢
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons
INNER JOIN Orders ON Persons.Id_P = Orders.Id_P ORDER BY Persons.LastName
(1)JOIN: 如果表中有至少一個比對,則傳回行
(2)LEFT JOIN: 即使右表中沒有比對,也從左表傳回所有的行
(3)RIGHT JOIN: 即使左表中沒有比對,也從右表傳回所有的行
(4)FULL JOIN: 隻要其中一個表中存在比對,就傳回行
4、分頁查詢
(1)SELECT * FROM ceshi limit 0,10(隻mysql,從第1個資料開始,取10條資料)
(2)select top 10 o.* from (select row_number() over(order by userId)as rowNumber,* from user) as o where rowNumber>0(隻sqlserver,從第1個資料開始,取10條資料)
(3)SELECT * FROM user order by userId OFFSET 0 ROW FETCH NEXT 10 ROWS ONLY
(隻sqlserver,新版的sqlserver可用,比上一種高效)
四、java寫資料庫語句需要特别注意的問題
1、SqlServer 對語句的條數和參數的數量都有限制,分别是 1000 和 2100;
2、Mysql 對語句的長度有限制,預設是 4M;
3、where條件中,=與!=兩邊都不能有null值,否則判斷會出錯。需要對有null值的字段作比較時, sqlserver需要用isnull(null,'default_value')把null值轉化為預設值再比較;mysql需要用ifnull(null,'default_value')把null值轉化為預設值再比較。
本文轉自 獨孤環宇 51CTO部落格,原文連結:http://blog.51cto.com/snowtiger/1928756