新途官网欢迎你访问长沙北大青鸟新途校区,支持你成为一个受人尊重的专业人才!
当前位置: 首页 > 青鸟知识 > 网络技术

SQL语句入门:SQL插入语句

来源:长沙it培训|发布时间:2017-05-02|浏览量:

学IT,好工作

就读北大青鸟长沙新途

20万年薪,等的就是你

求学热线: 0731-8309 3002

  SQL最常见的语句便是SQL插入语句了,常用的SQL插入语句有哪些?我们在插入数据到数据库中的时候,常用的SQL插入语句如下:

  INSERT INTO table1(id, name, address) VALUES(1, ygl, 'beijing')——适用于T-sql和PL/SQL;

  SELECT id, name, address INTO table2 FROM table1——自动创建table2,T-sql用法;

  INSERT INTO table2(id, name, address) SELECT id, name, address FROM table1

  这里简单说一下第三句SQL插入语句,由于可以指定插入到talbe2中的列,以及可以通过相对较复杂的查询语句进行数据源获取,可能使用起来会更加的灵活一些,但我们也必须注意,我们在指定目标表的列时,一定要将所有非空列都填上,否则将无法进行数据插入,还有一点比较容易出错的地方就是,当我们写成如下简写格式:

  INSERT INTO table2 SELECT id, name, address FROM table1

  此时,我们如果略掉了目标表的列的话,则默认会对目标表的全部列进行数据插入,且SELECT后面的列的顺序 必须和目标表中的列的定义顺序完全一致 才能完成正确的数据插入,这是一个很容易被忽略的地方。

  插入一行或多行到目标表中

-- single row
INSERT INTO Sales.MyOrders(custid, empid, orderdate, shipcountry, freight)
  VALUES(2, 19, '20120620', N'USA', 30.00);

-- relying on defaults
INSERT INTO Sales.MyOrders(custid, empid, shipcountry, freight)
  VALUES(3, 11, N'USA', 10.00);

INSERT INTO Sales.MyOrders(custid, empid, orderdate, shipcountry, freight)
  VALUES(3, 17, DEFAULT, N'USA', 30.00);

-- multiple rows
INSERT INTO Sales.MyOrders(custid, empid, orderdate, shipcountry, freight) VALUES
  (2, 11, '20120620', N'USA', 50.00),
  (5, 13, '20120620', N'USA', 40.00),
  (7, 17, '20120620', N'USA', 45.00);

  注:如果要更新自增字段需要把IDENTITY_INSERT选项开启,用完后记得关闭

SET IDENTITY_INSERT <table> ON;
 

INSERT SELECT

  把select 语句的查询结果插入到表中,这个中方法要比上面的INSERT VALUES 效率高


SET IDENTITY_INSERT Sales.MyOrders ON;

INSERT INTO Sales.MyOrders(orderid, custid, empid, orderdate, shipcountry, freight)
  SELECT orderid, custid, empid, orderdate, shipcountry, freight
  FROM Sales.Orders
  WHERE shipcountry = N'Norway';

SET IDENTITY_INSERT Sales.MyOrders OFF;

 

INSERT EXEC

  该语句可以让你把动态语句或者存储过程的结果插入表格。
-- create procedure
IF OBJECT_ID(N'Sales.OrdersForCountry', N'P') IS NOT NULL
  DROP PROC Sales.OrdersForCountry;
GO

CREATE PROC Sales.OrdersForCountry
  @country AS NVARCHAR(15)
AS

SELECT orderid, custid, empid, orderdate, shipcountry, freight
FROM Sales.Orders
WHERE shipcountry = @country;
GO

-- insert the result of the procedure
SET IDENTITY_INSERT Sales.MyOrders ON;

INSERT INTO Sales.MyOrders(orderid, custid, empid, orderdate, shipcountry, freight)
  EXEC Sales.OrdersForCountry
    @country = N'Portugal';

SET IDENTITY_INSERT Sales.MyOrders OFF;

  对于动态SQL,或者类似DBCC这种非常规的SQL语句,都可以通过这种方式来保存结果集。


CREATE TABLE test_dbcc
    (
      TraceFlag VARCHAR(100) ,
      Status TINYINT ,
      Global TINYINT ,
      Session TINYINT
    )

INSERT  INTO test_dbcc
        EXEC ( 'DBCC TRACESTATUS'
            )

  注:不能嵌套使用insert exec语句 ,可以用以下方式变动(这里直接拷贝参考文章中的内容。我没试验过)

  1.首先到打开服务器选项Ad Hoc Distributed Queries
exec sp_configure 'show advanced options',1
RECONFIGURE
GO
exec sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE
GO

  2. 通过OPENROWSET连接到本机,运行存储过程,取得结果集
--使用Windows认证
SELECT  *
INTO    #JobInfo_S1
FROM    OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes',
                   'exec msdb.dbo.sp_help_job')

--使用SQL Server认证
SELECT  *
INTO    #JobInfo_S2
FROM    OPENROWSET('SQLOLEDB', '127.0.0.1'; 'sa'; 'sa_password',
                   'exec msdb.dbo.sp_help_job')

  这样的写法,既免去了手动建表的麻烦,也可以避免insert exec 无法嵌套的问题。几乎所有SQL语句都可以使用。
--dbcc不能直接运行
SELECT  a.*
INTO    #t
FROM    OPENROWSET('SQLOLEDB', '127.0.0.1'; 'sa'; 'sa_password',
                   'dbcc log(''master'',3)') AS a

--可以变通一下
SELECT  a.*
INTO    #t
FROM    OPENROWSET('SQLOLEDB', '127.0.0.1'; 'sa'; 'sa_password',
                   'exec(''DBCC LOG(''''master'''',3)'')') AS a

SELECT INTO

  该语句不需要我们事先建立目标表,而是直接拷贝数据源或者查询结果数据定义:比如列名,类型,是否为空,自增等来建立目标表。
-- simple SELECT INTO
IF OBJECT_ID(N'Sales.MyOrders', N'U') IS NOT NULL DROP TABLE Sales.MyOrders;

SELECT orderid, custid, orderdate, shipcountry, freight
INTO Sales.MyOrders
FROM Sales.Orders
WHERE shipcountry = N'Norway';

  通过以下存储可以查看查询返回字段的结构,注意name,system_type_name,is_nullable.

EXEC sp_describe_first_result_set N'SELECT * FROM Sales.Customers;';
利用select into 生成一个空表

select * into #temp from sysobjects where 1=2

  实际插入案例

  1) 学生表插入


CREATE PROCEDURE [dbo].[Students_Insert]
    (
      @ID INT ,
      @LASTNAME VARCHAR(50) ,
      @FIRSTNAME VARCHAR(50) ,
      @STATE VARCHAR(50) ,
      @PHONE VARCHAR(50) ,
      @EMAIL VARCHAR(50) ,
      @GRADYEAR INT ,
      @GPA DECIMAL(20, 10) ,
      @PROGRAM VARCHAR(50) ,
      @NEWSLETTER BIT
    )
AS
    BEGIN
    --Check to make sure the ID does not already exist
    --If it does, return error
        DECLARE @existing AS INT = 0
        SELECT  @existing = COUNT(ID)
        FROM    Students
        WHERE   ID = @ID
   
        IF @existing > 0
            BEGIN
                RAISERROR ('ID already exists', 1, 1)
                RETURN 0
            END
    --Format GPA as 2 decimal places
        DECLARE @TwoDecimalGPA AS DECIMAL(3, 2)
        SELECT  @TwoDecimalGPA = CAST(@GPA AS NUMERIC(3, 2))
    --Make sure GPA is within range
        IF ( ( @TwoDecimalGPA > 4 )
             OR ( @TwoDecimalGPA < 0 )
           )
            BEGIN
                RAISERROR ('GPA value is invalid', 1, 1)
                RETURN 0
            END    
    --Attempt insert
        INSERT  INTO [dbo].[Students]
                ( [ID] ,
                  [LASTNAME] ,
                  [FIRSTNAME] ,
                  [STATE] ,
                  [PHONE] ,
                  [EMAIL] ,
                  [GRADYEAR] ,
                  [GPA] ,
                  [PROGRAM] ,
                  [NEWSLETTER]
                )
        VALUES  ( @ID ,
                  @LASTNAME ,
                  @FIRSTNAME ,
                  @STATE ,
                  @PHONE ,
                  @EMAIL ,
                  @GRADYEAR ,
                  @TwoDecimalGPA ,
                  @PROGRAM ,
                  @NEWSLETTER
                )
           --check to see if insert occured
           --and return status
        IF @@ROWCOUNT = 1
            RETURN 1
        ELSE
            RETURN 0
    END
GO

  2) 循环插入
-----SQL SERVER中直接循环写入数据-----
DECLARE @i INT
SET @i = 1
WHILE @i < 30
    BEGIN
        INSERT  INTO test
                ( userid )
        VALUES  ( @i )
        SET @i = @i + 1
    END
--案例:
--有如下表,要求就裱中所有沒有及格的成績,在每次增長0.1的基礎上,使他們剛好及格:
--    Name     score
--    Zhangshan   80
--    Lishi       59
--    Wangwu      50
--    Songquan    69

WHILE ( ( SELECT    MIN(score)
          FROM      tb_table
        ) < 60 )
    BEGIN
        UPDATE  tb_table
        SET     score = score * 1.01
        WHERE   score < 60
        IF ( SELECT MIN(score)
             FROM   tb_table
           ) > 60
            BREAK
        ELSE
            CONTINUE
    END

上一篇:ASP技巧:如何用.ashx文件排除重复内容

下一篇:DevOps未来发展的9大趋势

扫码关注微信公众号了解更多详情

跟技术大咖,专业导师一起交流学习

姓名
电话
Q Q

在线留言

请您把问题留下,我们为您提供专业化的解答!

QQ咨询
  1. 招生问答
  2. 热门点击
  3. 最新更新
  4. 推荐文章

关于我们

学校成就

就业保障

联系方式

联系电话:0731-8309 3002

在线报名

预约报名

备案号:湘ICP备16017604号-1
地址:长沙高新区麓谷麓松路679号

在线咨询
课程咨询 学费咨询 学费分期 入学测试 免费预约 来校路线
初中生 高中生 待业者
0731-8309 3002

在线客服