Sql Server的艺术（七） SQL 数据插入操作

--用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)
              '[e-mail]'  -- 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)
              '[e-mail]'  -- 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)
                  '[e-mail]'  -- 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