Sql Server的藝術（七） SQL 數據插入操作

--用INSERT插入單行數據在SQL中，可以通過INSERT...VALUES語句直接向資料庫表中插入數據。可以整行，也可以部分列。基本語法： INSERT INTO table_name [column1,column2...] VALUES (values1,values2...) 如果t ...

--用INSERT插入單行數據

　　 在SQL中，可以通過INSERT...VALUES語句直接向資料庫表中插入數據。可以整行，也可以部分列。
    基本語法：
    INSERT INTO table_name
                [column1,column2...]
    VALUES      (values1,values2...)
    如果table_name後面沒有指定列，預設是整行插入。使用語句時，要遵守以下準則：
    1、插入數據類型應與加入該欄位的數據類型相同。
    2、數據的大小應在列規定的範圍內。
    3、在VALUES中，列出的數據位置必須與欄位的排列位置相對應。

    創建一個數據表
    ID（ID），INT類型，要求是主鍵，且非空唯一，自增長
    姓名（PName），CHAR類型，要求是非空且唯一
    性別（Sex），CHAR類型，非空
    生日（Birthday），SMALLDATETIME類型，可以為空
    類別（Type），用INT型數表示：0代表朋友，1同學 ，2同事，3親戚，4其他關係。預設值為0
    家庭地址（Address），VARCHAR類型，可以為空
    行動電話（MovePhone），CHAR類型，可以為空
    家庭電話（HomePhone），CHAR類型，可以為空
    電子郵件（Email），VARCHAR類型，可以為空

    CREATE TABLE ComInfo
    (
        ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
        PName CHAR(12) NOT NULL UNIQUE,
        Sex CHAR(2) NOT NULL,
        Birthday SMALLDATETIME,
        Type INT NOT NULL DEFAULT 0,
        Address VARCHAR(16),
        MovePhone CHAR(11),
        HomePhone CHAR(11),
        Email VARCHAR(35)
    )

    --DROP TABLE dbo.ComInfo
    --DELETE FROM dbo.ComInfo WHERE ID=6
    SELECT * FROM dbo.ComInfo

    --向ComInfo插入兩行數據
    INSERT INTO dbo.ComInfo           
    VALUES  ( '張三' , -- PName - char(12)
              '男' , -- Sex - char(2)
              '1979/9/4' , -- Birthday - smalldatetime
              1 , -- Type - int
              '山東煙臺' , -- Address - varchar(16)
              '13576273872' , -- MovePhone - char(11)
              NULL , -- HomePhone - char(11)
              '[email protected]'  -- Email - varchar(35)
            ),
            ( '李二花' , -- PName - char(12)
              '女' , -- Sex - char(2)
              '1999/3/4' , -- Birthday - smalldatetime
              1 , -- Type - int
              NULL , -- Address - varchar(16)
              '13576756872' , -- MovePhone - char(11)
              '02755642454' , -- HomePhone - char(11)
              '[email protected]'  -- Email - varchar(35)
            )


    　　註意：
        　　1、如果插入的某個欄位為空，則該欄位對應的values值為"NULL"，不允許什麼都不填。
        　　2、如果插入的欄位定義為NOT NULL，插入數據時寫入NULL，則違背了欄位的非空約束，會報錯，如果不確定值，是可以用空格代替的。
        　　3、許多資料庫管理系統建立了UNIQUE屬性，意味著這個欄位必須唯一，並且只能出現一次。例如：PName欄位就設有此屬性，如果再次插入相同的名字，則會報錯。
        　　4、一個規範化的表應有一個唯一欄位或關鍵欄位，這個欄位在表之間連接數據時是有用的，它通常能夠在使用索引時提高查詢速度。
        　　5、插入多條數據時，只需要用","隔開，然後繼續寫下一條數據即可。

    --特定欄位數據插入
        在插入數據時，只指定插入其中幾個欄位會怎麼樣呢？看看以下3種情況：
        1、如果該欄位有一個預設值，該值會被使用。前面在介紹表創建的時候我們已經講過，允許通過DEFAULT關鍵字為列創建預設預設值。例如，假設插入新記錄時沒有給欄位new_column提供數據，而這個欄位有一個預設值"some value"。
        2、如果該欄位可以接受空值，而且沒有預設值，則會被插入空值。
        3、如果該欄位不能接受空值，而且沒有預設值，則就會出現錯誤。

        INSERT INTO dbo.ComInfo(PName,Sex,Email)     
        VALUES  ( '李彤彤' , -- PName - char(12)
                  '女' , -- Sex - char(2)
                  '[email protected]'  -- Email - varchar(35)
                 )
        SELECT * FROM dbo.ComInfo

　　　　註意：
            1、沒有涉及到代碼插入的列，值都為NULL，前提是這個列的屬性是可空的，如果該列為NOT NULL，但是又沒有插入數據，則會報錯。
            2、如果該列有預設值，而又沒有插入數據，則結果採用預設的值。

--通過視圖插入行
    除了通過視圖顯示數據以外，我們還可以在INSERT語句中通過視圖向視圖底層的表（視圖本身不存儲數據，它所顯示的數據都是其底層表的數據）中插入數據。通過視圖向表中插入數據的語法與直接向表中插入數據的語法是一樣的，只是在INSERT子句中使用的是視圖名而不是表名。
     語法：
     INSERT INTO view_name
                 [column1,column2...]
     VALUES      (values1,values2...)

   創建視圖並查詢
    CREATE VIEW View1_ComInfo
    AS 
    SELECT PName,Sex,Address,MovePhone FROM dbo.ComInfo WHERE Address IS NOT NULL

    SELECT * FROM View1_ComInfo

　　--向視圖插入數據
    　　INSERT INTO View1_ComInfo VALUES('趙六','男','河南安陽','13909342343')
    　　SELECT * FROM dbo.ComInfo
    　　SELECT * FROM View1_ComInfo


    　　註意：
         1、跟前面一樣，如果不指定添加哪幾列，則預設添加整行。
         2、跟直接向表中插入一樣，DBMS將對未指定值得任何列使用預設值，如Type列。如果沒有指定預設值，則插入時將為NULL值。如果此列屬性為NOT NULL，則會報錯。
         3、視圖最大的缺點是，如果表中某列並不包含在視圖中，那麼就不能給列添加數據。

    --使用SELECT語句插入值
      INSERT...SELECT語句實際上包含兩部分：INSERT插入和SELECT查詢，語法如下：
        INSERT INTO table_name
                    [column1,column2...]
        SELECT     column1,column2...
        FROM          table_name2
        WHERE       search_condition
        實際上，該語句是將查詢語句的輸出結果插入到表中。適用於INSERT...VALUES語句的規則同樣也適用於INSERT...SELECT語句。另外，使用INSERT...SELECT語句，應遵循以下幾點新準則。
        　　1、SELECT語句不能從正在被插入的表中選擇數據，即table_name與table_name2不能為同一個表。
        　　2、在INSERT INTO語句中，列的數目必須等於從SELECT語句返回的列的數據。
        　　3、在INSERT INTO語句中，列的數據類型必須與從SELECT語句返回的列的數據類型相同。
        INSERT...SELECT語句通常用於創建查找表，提高檢索性能。查找表可以包含分佈在多個資料庫的多個表中的數據。因為多個表連接處理起來比簡單的查詢更慢，我們對一個查找表執行SELECT查詢，則明顯快於執行又長又複雜的連接查詢。
        註意：查找表通常存儲在客戶/伺服器環境中的客戶機中，用來降低網路信息流量。

        另外，INSERT...SELECT語句的另一個用途是備份表，備份將要刪除、截斷數據或重新裝入數據的表。這個過程需要建立一個臨時表，並且通過從原始表上選擇任何內容，將包含在原始表的數據插入臨時表。
        註意：許多資料庫系統支持臨時表。臨時表的存在是相對資料庫連接的壽命而言的，當連接終止時，它就被刪除。

    --用INSERT...SELECT語句複製數據到表
      --創建一個新表MyFriend，包含FName，Sex，Age，然後將ComInfo中的朋友（Type值為0）和同學（Type值為1）相關信息複製到MyFriend中。
        CREATE TABLE MyFriend
        (
            ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
            FName CHAR(12) NOT NULL,
            Sex CHAR(2),
            Age INT
        )
        INSERT INTO dbo.MyFriend
                ( FName, Sex, Age )
        SELECT PName,Sex,DATEDIFF(YEAR,Birthday,GETDATE()) 
        FROM dbo.ComInfo WHERE Type IN(0,1)

        SELECT * FROM dbo.MyFriend
        SELECT * FROM dbo.ComInfo

　　　　 --因為在INSERT INTO語句中，列的數據類型必須與從SELECT語句返回的列的類型相同，MyFriend表中的Age列是INT，而ComInfo是SMALLDATETIME，所以要用DATEDIFF函數與當年年份比較，獲取年齡。

　　--利用子查詢向表中插入數據
        SELECT * FROM dbo.TEACHER
        SELECT * FROM dbo.COURSE
　　　　　數據表sql下載地址：https://files.cnblogs.com/files/liuchenxing/sql.rar
　　　　　--將TEACHER中教"設計理論","資料庫設計"課程的教師相關信息插入到MyFriend中。
        INSERT INTO dbo.MyFriend
                ( FName, Sex, Age )
        SELECT TNAME,TSEX,AGE FROM dbo.TEACHER WHERE CNO IN(SELECT CNO FROM dbo.COURSE WHERE CNAME IN('電腦軟體基礎','電腦硬體基礎'))

        SELECT * FROM dbo.MyFriend

      --如果要頻繁對多個表的數據進行查詢，我們可以將多個表需要的數據合併到一個臨時表中，作為新表，在執行SQL語句，則查詢較為簡單而且執行更快。

--將多個表中需要的數據合併到一個臨時表中
      --創建Temp_Teacher表，存儲所有教師的姓名，所在系，所教課程，學時以及這門課程的最高成績的學生，其中教師姓名來自TEACHER，課程名，學時來自COURSE，最高成績來自STUDENT。
        CREATE TABLE Temp_Teacher
        (
            ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
            TName CHAR(8) NOT NULL,
            Dname VARCHAR(20),
            Cname VARCHAR(20),
            CTime INT,
            Sname CHAR(8)
        )

        INSERT INTO dbo.Temp_Teacher
                ( TName, Dname, Cname, CTime, Sname )
        SELECT t.TNAME,t.DNAME,c.CNAME,c.CTIME,SNAME 
        FROM dbo.TEACHER AS t LEFT OUTER JOIN dbo.COURSE AS c ON c.CNO = t.CNO LEFT OUTER JOIN dbo.STUDENT AS s ON s.CNO = t.CNO 
        AND s.MARK=(SELECT AVG(MARK) FROM dbo.STUDENT WHERE CNO=s.CNO)

        SELECT * FROM dbo.Temp_Teacher

　　　　 註意：
            1、因為要求Temp_Teacher表哦要有所有教師信息，所以採用左連接(LEFT OUTER JOIN)的方式。
            2、其實，不難發現，這個可以通過視圖來實現，但是視圖本身不存儲數據，只是在資料庫中個存儲一個定義，其操作的任然是各底層的數據，因此並不能提高查詢速度。

    --表中數據的複製
      通過INSERT...SELECT語句，我們可以從其他表選擇記錄，插入到新表，但是前提是該新表在插入前已經創建完成。而在SQL中，可以通過INSERT...INTO語句直接將表中的數據複製到新表，而且該新表不需要預先創建。
        INSERT...INTO語法：
        SELECT    column1,column2...
        INTO    new_table
        FROM    table_name
        WHERE    search_condition
        INSERT...INTO語句首先創建表new_table，而後將表table_name中滿足search_condition條件的列column1,column2...中的欄位複製到表new_table中。
　　　　　關於INSERT...INTO，說明以下幾點：
            1、創建的新表包含的欄位與SELECT語句選擇的欄位相同。
            2、前面介紹的各種SELECT查詢語句，包含HAVING、ORDER BY子句、各種函數、通配符、聚合函數等等，這裡都可以用。
            3、它可以連接多表，將多個資料庫表中的數據一次性複製到新表。
            4、不管有多少數據、來自多少個表，所有數據最終都將被覆制到一個新表中，即語句中新建的表。

        --用INSERT...INTO語句複製表
            --將ComInfo中的數據複製到新表NEW1_ComInfo中。
            SELECT PName,Sex,Type,Email
            INTO NEW1_ComInfo
            FROM dbo.ComInfo

            SELECT * FROM dbo.NEW1_ComInfo