• sql server复习重点


    一、

      1.增加,更改,删除 列属性

      alter table SC add test1 varchar(10) --增加列

      alter table SC alter column test1 int --更改列

      alter table SC drop column test1 --删除列

      sp_rename 旧名,新名

      2.创建唯一索引

      create unique index stusno on student(SNO)

      3.删除索引

      drop index student.stusno

      4.创建视图

      create view cs_student
      as
      select * from student

      5.in可以用select查询出来的结果做为取值范围

      update SC set GRADE=GRADE+2 where CNO in (select CNO from course where CNAME='数据结构' and TNO in (select TNO from teacher where TNAME='张星'))

      sco_degree > any[all] (select sco_degree from Score where cou_id='3-245')

      6.去掉重复值的关键词

      distinct

      7.sql查询语句去除重复列(行)

      参考资料:https://blog.csdn.net/danuo2011/article/details/79939385

      8.sql server的case判断语句

      case when 判断语句 then 结果 end

    select ct.cid,ct.Cname,
    SUM(case when sc.score<=100 and sc.score>85 then 1 else 0 end) as [100-85],
    SUM(case when sc.score<=85 and sc.score>70 then 1 else 0 end) as [85-70],
    SUM(case when sc.score<=70 and sc.score>60 then 1 else 0 end) as [70-60],
    SUM(case when sc.score<=60 and sc.score>0 then 1 else 0 end) as [60-0]
    from SC inner join course as ct on sc.Cid=ct.Cid
    group by ct.cid,ct.Cname
    order by ct.Cid
    View Code

      CASE
      WHEN 条件1 THEN 结果1
      WHEN 条件2 THEN 结果2
      .........
      WHEN 条件N THEN 结果N
      ELSE 结果X
      END

    select GRSDS=(
    case 
    when zggz.YFGZ<=1200 then 0 
    when zggz.YFGZ>1200 and zggz.YFGZ<=2400 then (zggz.YFGZ-1200)*0.02
    else (zggz.YFGZ-1200)*0.05
    end
    )
    from ZGGZ
    View Code
    SELECT
        s.s_id,
        s.s_name,
        s.s_sex,
        CASE s.s_sex
    WHEN '1' THEN ''
    WHEN '2' THEN ''
    ELSE '其他'
    END as sex,
     s.s_age,
     s.class_id
    FROM
        t_b_student s
    WHERE
        1 = 1
    View Code

      9.rank函数,算排名名次

      RANK() over(order by 列名 desc) as 重命名

      10.查询 1990 年出生的学生名单 

    select * from student
    where YEAR(student.Sage)='1990'
    View Code

      11.sql语句的备注说明关键词 ‘ - - ’,两个横杠

      12.时间的加减

       参考资料:https://www.cnblogs.com/kaxbk/p/3807641.html

      13.获得当前时间

      getdate() 

    create table test1(
        id int,
        name char(10),
        createTime datetime default getdate()
    )
    
    select * from test1
    
    insert into test1(id,name) values(1,'ken')
    
    insert into test1 values(1,'ken2','2000-8-9')
    
    insert into test1 values(1,'ken3',null)
    
    insert into test1 values(1,'ken4',GETDATE())
    View Code  

      

      

      14.year和month函数返回值是int

      select year(GETDATE())

      select month(GETDATE())

      15.为每行记录依次标记一个返回值(从1开始),此函数必须跟上某列的排序语句

      ROW_NUMBER() OVER(PARTITION BY COLUMN1 ORDER BY COLUMN2)

         其中PARTITION是以COLUMN1为分组

       例子1:select *,ROW_NUMBER()over(order by name) as 排序列 from test1

       

      16.数据库的备份和还原,定时备份,SQLService代理(定时作业)

      参考资料:https://www.cnblogs.com/zfylzl/p/9869409.html

      定时作业详细介绍参考资料:https://blog.csdn.net/qq_34590413/article/details/79127419

      17.语法分析器SQL Service Profiler

      参考资料:https://blog.csdn.net/qq_22698657/article/details/81671360

      18.触发器

      参考资料1:https://blog.csdn.net/suntao1995/article/details/88864575

      参考资料2:https://blog.csdn.net/legendaryhaha/article/details/80368114

        语法:

        CREATE TRIGGER  trigger_name
        ON  table|view
        FOR|AFTER|INSTEADOF  [DELETE][,INSERT][,UPDATE]
        AS
        Sql_statement[…n]

      实例资料:https://www.cnblogs.com/julinhuitianxia/p/6823011.html

      19.存储过程

      参考资料:https://jingyan.baidu.com/article/a65957f44a4c7064e77f9b61.html

      参考资料2:https://www.cnblogs.com/lihuiqi/p/10471740.html

      实例资料:https://www.jianshu.com/p/abab22348e55

        19.1 无参存储过程的创建及调用    

        create procedure getallmsg
        as
        select * from presonMSG3

        exec getallmsg   --调用存储过程

        19.2 更改及删掉

        alter procedure getallmsg
        as
        select 姓名,电话,身份证号 from presonMSG3

        exec getallmsg

        drop procedure getallmsg

        19.3 单个参数的存储过程

        create procedure searchName(@Name nvarchar(255))
        as
        select * from presonMSG where 姓名=@Name
     
        exec searchName '牛坤'

        19.4 多个参数的存储过程

    create procedure insMSG(@stu_id varchar(3),@stu_name varchar(20),@stu_sex varchar(2),
    @stu_birthday datetime,@stu_class varchar(5))
    as
        insert into student values(@stu_id,@stu_name,@stu_sex,@stu_birthday,@stu_class)
        
    exec insMSG '109','ken','','2087-08-09','2098'
    View Code

       20.事务(sql语句的操作,开始,提交,回滚)

      参考资料:https://blog.csdn.net/legendaryhaha/article/details/80550180

       

    set XACT_ABORT off --如果产生错误自动回滚
    GO
    begin tran
        insert into student values('102','ken','','2087-08-09','90022')
        insert into student values('102','ken',男,'2087-08-09','90022')
    commit tran
    View Code

       21.删除重复记录,只保留一条

      参考资料:https://www.cnblogs.com/monogem/p/11149578.html

    select * from presonMSG2 
    where 身份证号 in(
    select 身份证号 from presonMSG2 group by 身份证号 having COUNT(身份证号)>1)
    
    delete t from (
    select *,ROW_NUMBER()over(partition by 身份证号 order by 身份证号) as No from presonMSG2
    ) as t --为presonMSG2增加一列按身份证号排序的列,重命名为t,
    where No>1
    View Code

       22.跨数据库的多表链接

      参考资料:http://www.itfarmer.com.cn/743.html

      23.算周的函数

      select datepart(参数,datetime)
      示例: select datepart(week,getdate()) 判断今天为本年第几周
      参数部分如下:
      year 将返回日期的年份
      quarter 将返回日期在年内的第几季
      month 将返回日期的月份
      dayofyear 将返回日期在年内的第几天
      day 将返回日期在该月的第几天
      week 将返回日期在年内的第几周
      weekday 将返回日期在周内的第几日
      Hour 将返回日期的小时部分
      minute 将返回日期的分钟部分
      second 将返回日期的秒钟部分

      参考网址:https://www.w3school.com.cn/sql/func_datepart.asp 

      24.with  as 语句

      参考网址:https://blog.csdn.net/qq_41080850/article/details/94557852

      参考网址2:https://www.cnblogs.com/wohenxinwei/p/9649900.html 

      参考网址3:https://blog.csdn.net/liufeifeinanfeng/article/details/83006713 

      25.将数据库兼容模式设置为90

      ALTER DATABASE 数据库名字

      SET COMPATIBILITY_LEVEL = 90

      26.convert函数: 转换数据类型

      CONVERT(VARCHAR(19),GETDATE())

      参考资料:https://www.w3school.com.cn/sql/func_convert.asp 

      27.Sql Server 查询今天,昨天,近七天....数据

      参考地址:https://www.cnblogs.com/Aotum/p/10155403.html

      28.CONVERT() 函数是把日期转换为新数据类型的通用函数。

      CONVERT(data_type(length),data_to_be_converted,style)

      参数网址:https://www.w3school.com.cn/sql/func_convert.asp

      29.CAST (expression AS data_type)  

      参数说明:  

      expression:任何有效的SQLServer表达式。  

      AS:用于分隔两个参数,在AS之前的是要处理的数据,在AS之后是要转换的数据类型。    

      data_type:目标系统所提供的数据类型,包括bigint和sql_variant,不能使用用户定义的数据类型。

      

     

      

      30.decimal

      decimal(m,n)的意思是m位数中,有n位是小数,即m-n位整数。

      上述实例decimal(5,2)的意思是三位整数和两位小数

      31.isnull

      isnull(参数1,参数2),判断参数1是否为NULL,如果是,返回参数2,否则返回参数1

      32.DATEDIFF()

      DATEDIFF()函数返回两个日期之间的时间差值()

      语法:DATEDIFF(datepart,startdate,enddate)

      例子:DATEDIFF(day,'2008-12-30','2008-12-29')  相差一天

      33.通过列名找到所在的表

      参考资料:https://www.cnblogs.com/zhangchenliang/archive/2010/02/02/1662142.html

       select   a.name   表名,b.name   列名   from   sysobjects   a,syscolumns   b   where   a.id=b.id   and   b.name='字段名'   and   a.type='U'

      34.服务器A访问服务器B的数据库

      参考链接:https://blog.csdn.net/archer119/article/details/79552609    调用表

      参考链接:https://www.cnblogs.com/freeliver54/p/3517159.html  存储过程中调用 

      35.索引

        ① 基础知识:https://www.cnblogs.com/zhaoshujie/p/10262119.html

          以下是索引创建的规则:
          1、表的主键、外键必须有索引;
          2、数据量超过300的表应该有索引;
          3、经常与其他表进行连接的表,在连接字段上应该建立索引;
          4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;
          5、索引应该建在选择性高的字段上;
          6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;
          7、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:
          A、正确选择复合索引中的主列字段,一般是选择性较好的字段;
          B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;
          C、如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;
          D、如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;
          E、如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;
          8、频繁进行数据操作的表,不要建立太多的索引;
          9、删除无用的索引,避免对执行计划造成负面影响;
          以上是一些普遍的建立索引时的判断依据。一言以蔽之,索引的建立必须慎重,对每个索引的必要性都应该经过仔细分析,要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处:在表上建立的每个索引都会增加存储开销,索引对于插入、删除、更新操作也会增加处理上的开销。另外,过多的复合索引,在有单字段索引的情况下,一般都是没有存在价值的;相反,还会降低数据增加删除时的性能,特别是对频繁更新的表来说,负面影响更大。

        ② 周浩提供的课件资料学习

          5.3 图书管理数据库查询的优化(73页)

        

          L5.2 图书管理数据库视图(96页)

          L5.3 图书管理数据库查询的优化(98页)

          L7.1  存储过程、触发器的基本说明(129页,141页,142页)

          11 数据库操作自动化应用实践(221-231页)

          附录B 全局变量和常用函数(299)

      36.查看数据库的空间使用情况

        

    USE 你的库名;
    GO
    -- 数据库空间使用情况
    EXEC sp_spaceused;
    
    -- 查下文件空间使用情况
    SELECT 
        file_id, name,
        [文件大小(MB)] = size / 128.,
        [未使用空间(MB)] = (size - FILEPROPERTY(name, N'SpaceUsed')) / 128.
    FROM sys.database_files
    
    -- 表空间使用情况
    DECLARE @tb_size TABLE(
        name sysname,
        rows int,
        size varchar(100),
        data_size varchar(100),
        INDEX_size varchar(100),
        unused_size varchar(100)
    );
    INSERT @tb_size
    EXEC sp_msforeachtable '
    sp_spaceused ''?''
    '
    SELECT * FROM @tb_size
    用上面的语句检查一下你的数据库中的空间使用情况
    
    其中,文件空间情况中的未使用空间表示你的文件可以通过 DBCC SHRINKFILE 来释放的磁盘空间
    
    如果可以释放的空间很小, 那么你可以通过后面的查表空间的语句来查到是那些表占用了大的空间, 对于 unused_size 列的值很大的表, 你可以通过 REBUILD 聚焦索引(一般是主键) 的方式, 重组表的数据存储, 以便能够释放空间占用
  • 相关阅读:
    [LeetCode]Remove Duplicates from Sorted Array
    二叉树中和为某一值的路径
    机器学习基石笔记:Homework #2 Decision Stump相关习题
    机器学习基石笔记:08 Noise and Error
    机器学习基石笔记:07 The VC Dimension
    机器学习基石笔记:06 Theory of Generalization
    机器学习基石笔记:05 Training versus Testing
    正交矩阵、EVD、SVD
    win10安装ubuntu16.04及后续配置
    chmod命令相关
  • 原文地址:https://www.cnblogs.com/kentee/p/12334903.html
Copyright © 2020-2023  润新知