常用增刪改查sql示例

一、插入數(shù)據(jù)

    （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

二、刪除數(shù)據(jù)

    （1）DELETE FROM Person WHERE LastName = 'Wilson'

    （2）drop Person

三、修改數(shù)據(jù)

    （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（合并結(jié)果集，union all是直接連接，取到得是所有值，

                記錄可能有重復(fù)；union 是取唯一值，記錄沒有重復(fù)）

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 '發(fā)_1_2'（_僅替代一個(gè)字符）

    （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、關(guān)聯(lián)查詢

    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: 如果表中有至少一個(gè)匹配，則返回行

    （2）LEFT JOIN: 即使右表中沒有匹配，也從左表返回所有的行

    （3）RIGHT JOIN: 即使左表中沒有匹配，也從右表返回所有的行

    （4）FULL JOIN: 只要其中一個(gè)表中存在匹配，就返回行

4、分頁查詢

    （1）SELECT * FROM ceshi limit 0,10(只mysql，從第1個(gè)數(shù)據(jù)開始，取10條數(shù)據(jù))

    （2）select top 10 o.* from (select row_number() over(order by userId)as rowNumber,* from             user) as o where rowNumber>0（只sqlserver，從第1個(gè)數(shù)據(jù)開始，取10條數(shù)據(jù)）

    （3）SELECT * FROM user order by userId  OFFSET 0 ROW FETCH NEXT 10 ROWS ONLY

        （只sqlserver，新版的sqlserver可用，比上一種高效）

四、java寫數(shù)據(jù)庫語句需要特別注意的問題

1、SqlServer 對(duì)語句的條數(shù)和參數(shù)的數(shù)量都有限制,分別是 1000 和 2100；

2、Mysql 對(duì)語句的長(zhǎng)度有限制,默認(rèn)是 4M；

3、where條件中，=與!=兩邊都不能有null值，否則判斷會(huì)出錯(cuò)。需要對(duì)有null值的字段作比較時(shí)，       sqlserver需要用isnull(null,'default_value')把null值轉(zhuǎn)化為默認(rèn)值再比較；mysql需要用ifnull(null,'default_value')把null值轉(zhuǎn)化為默認(rèn)值再比較。