• SQL 基础技能提升


    此篇涉及的 SQL 全部在SQL SERVER 2005 EXPRESS 通过验证
    -- 数据库备份
    USE MASTER
    EXEC SP_ADDUMPDEVICE 'DISK' , 'TESTBACKDB1' , 'E:\T.bak';
    BACKUP DATABASE DB TO TESTBACKDB1

    EXEC SP_DROPDEVICE 'TESTBACKDB1'

    -- 依据旧表创建新表
    SELECT * INTO NEW_TABLE_NAME FROM OLE_TABLE_NAME WHERE 1 = 2  -- 只创建与旧表结构相同表结构
    SELECT * INTO NEW_TABLE_NAME FROM OLE_TABLE_NAME  -- 复制旧表资料到新表
    Eg:
    SELECT *  INTO TEST2 FROM TEST

    -- 添加/删除列 COLUMN
    ALTER TABLE TABLE_NAME ADD  COLUMN_NAME INT IDENTITY | UNIQUE -- 添加
    ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME                -- 删除
    Eg:
    ALTER TABLE TEST ADD AGE INT
    ALTER TABLE TEST DROP COLUMN AGE

    -- 添加/删除约束 CONSTRAINT
    ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_NAME UNIQUE (COLUMN1_NAME,COLUMN2_NAME)
    ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_NAME PRIMARY KEY(COLUMN_NAME,COLUMN_NAME2)
    ALTER TABLE TABLE_NAME ADD  PRIMARY KEY(ID) -- 此方法添加的主键名称无法控制,建议使用上面的方法
    ALTER TABLE TABLE_NAME DROP  CONSTRAINT_NAME
    ALTER TABLE TABLE_NAME DROP CONSTRAINT CONSTRAINT_NAME
    Eg:
    ALTER TABLE TEST ADD CONSTRAINT CK CHECK(AGE > 18)
    INSERT INTO TEST(ID,AGE) VALUES (1,19)


    -- 索引的创建和删除
    CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN1_NAME,COLUMN2_NAME) -- 创建非唯一,非聚集索引
    CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME(COLUMN1_NAME,COLUMN2_NAME) -- 创建唯一,非聚集索引
    CREATE UNIQUE CLUSTERED INDEX  INDEX_NAME ON TEST(COLUMN1_NAME,COLUMN2_NAME) -- 创建唯一,聚集索引
    DROP INDEX TABLE_NAME.INDEX_NAME

    -- 复杂的运算符

    -- UNION : 合并表查询结果
    SELECT GETDATE() UNION SELECT GETDATE() - 1 UNION  SELECT GETDATE() - 2
    SELECT GETDATE() UNION SELECT GETDATE() - 1 UNION  SELECT GETDATE() - 1
    SELECT GETDATE() UNION ALL SELECT GETDATE()  UNION ALL SELECT GETDATE()

    -- EXCEPT : 获取表1中存在且不存在表2中是数据
    SELECT GETDATE()  EXCEPT SELECT GETDATE()
    SELECT GETDATE() UNION ALL SELECT GETDATE() UNION ALL SELECT GETDATE() - 1  EXCEPT SELECT GETDATE() - 1

    --INTERSECT : 获取存在与表1且表2中也有同样记录的数据
    SELECT 1 A INTERSECT SELECT 1 A
    SELECT 1 A INTERSECT SELECT 2 A

    -- 分离/附加数据库
    EXEC SP_DETACH_DB DATABASE_NAME
    EXEC SP_ATTACH_DB DATABASE_NAME, FILE_FULL_PATH
    Eg:
    EXEC SP_ATTACH_DB 'DB', 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\DB.MDF'

    -- 数据库重新命名
    EXEC SP_RENAMEDB 'OLD_NAME' , 'NEW_NAME'
    EXEC SP_RENAME 'OLD_NAME' , 'NEW_NAME', 'DATABASE'
    Eg;
    EXEC SP_RENAMEDB 'DB' , 'DB2'
    EXEC SP_RENAME 'DB2' , 'DB' , 'DATABASE'

    -- 重命名表和约束(CHECK,FOREIGN KEY,PRIMARY/UNIQUE KEY)
    EXEC SP_RENAME 'OLD_NAME' , 'NEW_NAME', 'OBJECT'
    Eg;
    EXEC SP_RENAME 'TEST' , 'TEST1' , 'OBJECT'

    -- 索引/列 重命名
    EXEC SP_RENAME 'TABLE_NAME.OLD_NAME' , 'NEW_NAME', 'INDEX'
    EXEC SP_RENAME 'TABLE_NAME.OLD_NAME' , 'NEW_NAME', 'COLUMN'
    Eg;
    EXEC SP_RENAME 'TEST.IX_ID1' , 'IX_ID', 'INDEX'

    -- SQL 语句的循环结构
    DECLARE @COUNT INT;
    SET @COUNT = 0;
    WHILE @COUNT <= 6
    BEGIN
       SET @COUNT = @COUNT + 1;
       IF @COUNT = 2  PRINT  'CHINA';
       ELSE IF @COUNT = 3
           BEGIN
              CONTINUE; -- 退出本次循环执行下次循环,
              PRINT 'JANPAN'
           END;
       ELSE IF @COUNT = 4 PRINT 'AMERICA';
       ELSE IF @COUNT = 5
            BEGIN
                BREAK; -- 退出当前的循环结构,不在执行此循环
                PRINT 'GERMAN';
            END;
       ELSE PRINT 'ENGLAND'
    END;

    -- CASE 语句结构

    DECLARE @COUNT INT;
    SET @COUNT = 3;
    --WHILE @COUNT < 5
         SELECT @COUNT  ID,
          CASE @COUNT  WHEN 1 THEN 'CHINA' 
                       WHEN 2 THEN 'AMERICA'
                       WHEN 3 THEN 'JANPAN'
                       WHEN 4 THEN NULL
           END AS [NAME];
    -- CASE 语句也可以嵌套的在WHERE条件中

    -- 创建表详细说明JOIN的用法
    create table T1(ID int, [NAME] nvarchar(10));
    go
    create table T2(ID int, AGE int);

    insert into t1(ID,[NAME]) values(1,'LILY');
    insert into t1 values(2,'LUCY');
    insert into t1 values(3,'JIM');

    insert into t2 values(1,18);
    insert into t2 values(2,19);
    insert into t2 values(4,20);
    -- TABLE: T1                TABLE: T2
    --     ID      NAME         ID      AGE
    --     1       LILY         1        18
    --     2       LUCY         2        19
    --     3       JIM          4        20
    SELECT T1.ID,T1.NAME,T2.AGE FROM T1 JOIN T2 ON T1.ID = T2.ID ORDER BY T1.ID -- 内连接
    -- 结果
    --  ID       NAME     AGE
    --  1         LILY    18
    --  2         LUCY    19
    SELECT T1.ID,T1.NAME,T2.AGE FROM T1 LEFT JOIN T2 ON T1.ID = T2.ID ORDER BY T1.ID -- 左连接(外连接之一)
    -- 结果
    --  ID       NAME     AGE
    --  1        LILY     18
    --  2        LUCY     19
    --  3        JIM      NULL
    SELECT T1.ID,T1.NAME,T2.AGE FROM T1 RIGHT JOIN T2 ON T1.ID = T2.ID ORDER BY T2.AGE -- 右连接(外连接之一)
    -- 结果
    --  ID       NAME     AGE
    --  1        LILY     18
    --  2        LUCY     19
    --  NULL     NULL     20
    SELECT T1.ID,T1.NAME,T2.AGE FROM T1 FULL  JOIN T2 ON T1.ID = T2.ID ORDER BY T1.ID,T2.AGE -- 全连接(外连接之一)
    -- 结果
    --  ID       NAME     AGE
    --  NULL     NULL     20
    --  1        LILY     18
    --  2        LUCY     19
    --  3        JIM      NULL
    SELECT T1.ID,T1.NAME,T2.AGE FROM T1 CROSS  JOIN T2  ORDER BY T1.ID,T2.AGE -- 交叉连接
    -- 结果: 笛卡尔积形式
    --  ID       NAME     AGE
    --  1        LILY     18
    --  1        LILY     19
    --  2        LUCY     18
    --  2        LUCY     19
    -- 多张表的连接
    SELECT T1.ID,T2.ID,T3.ID FROM T1 JOIN T2 ON T1.ID = T2.ID JOIN T3 ON T2.ID = T3.ID

    -- GROUP BY 与 HAVING 组合
    -- 如果group by ,having 基本和where条件结果没什么区别,只是过滤的结果集的时间延迟到where条件之后执行,
    -- having只有与group by组合使用,它的用途才可以展现的淋漓尽致
    -- 1. 获取表中的重复记录(假设表中只有NAME一列)
    SELECT [NAME] FROM T1 GROUP BY [NAME] HAVING COUNT([NAME]) > 1
    -- 2. 获取平均分数大于85的学科的名称
    SELECT [NAME] FROM T1 GROUP BY [NAME] HAVING AVG(SCORE) > 85


    -- ROW_NUMBER() 函数的使用
    SELECT ROW_NUMBER() OVER( ORDER BY ITEMID) ,* FROM MSPETSHOP4..ITEM  GO
    -- 先按分区ID PRODUCTID分组,再按ITEMID排序
    SELECT ROW_NUMBER() OVER( PARTITION BY PRODUCTID ORDER BY ITEMID) ,* FROM MSPETSHOP4..ITEM
    SELECT SUM(ITEMID) OVER( PARTITION BY PRODUCTID ) ,* FROM MSPETSHOP4..ITEM

  • 相关阅读:
    L255 Learning to say no brings a thrill of freedom
    2019-02-25 EST 科技文翻译
    L253 Work and Pleasure
    2019.2.22 初级英语口语班 结课
    L252 小组作业
    2019-02-22 L231
    leetcode 67 Add Binary ----- java
    leetcode 66 Plus One ----- java
    leetcode 65 Valid Number ----- java
    leetcode 64 Minimum Path Sum ----- java
  • 原文地址:https://www.cnblogs.com/niuww/p/SQL.html
Copyright © 2020-2023  润新知