SQL Server 2005基础知识详细整理

程序员文章站 2023-10-31 10:09:22

1. acid：指数据库事务正确执行的四个基本要素缩写：1.原子性2.一致性3.隔离性4.持久性 2.数据库对象：表（table）视图（view）存储过程（store...

1. acid：指数据库事务正确执行的四个基本要素缩写：
1.原子性2.一致性3.隔离性4.持久性

2.数据库对象：表（table）视图（view）存储过程（stored procedure）函数（function）索引（index）

3.sql server 2005中包含master、model、msdb、tempdb四个系统数据库。

4.使用t-sql语句创建数据库：
create database [apressfinacial] on primary
( name = n'apressfinacial', filename = n'i:\program files\microsoft sql server\mssql.1\mssql\data\apressfinacial.mdf' , size = 3072kb , filegrowth = 1024kb )
log on
( name = n'apressfinacial_log', filename = n'i:\program files\microsoft sql server\mssql.1\mssql\data\apressfinacial_log.ldf' , size = 1024kb , filegrowth = 10%)

5.t-sql语句删除数据库：drop database[数据库名]

6.常见的约束类型：主键约束、唯一约束、检查约束、默认约束、外键约束。

7.添加约束的语法：
alter table 表名
add constraint 约束名约束类型具体的约束说明

8.删除约束：alter table 表名
drop constraint 约束名

9.授权语法： grant 权限[on 表名] to 数据库用户
例：grant select insert update
on students to zhangsan

10.t-sql语句创建登录名：
           create login[yuan]
            with password='130125'
            go

11.数据库完整性分为：实体完整性、域完整性、参照完整性、用户自定义完整性。

12.t-sql 中的运算符：
运算符
含义

=
等于

>
大于

<
小于

>=
大于等于

<=
小于等于

<>
不等于

!
非

通配符
解释
示例

_
一个字符
a like 'c_'

%
任意长度的字符
b like 'co_%'

[]
括号中所指定范围内的一个字符
c like '9w0[1-2]'

[^]
不在括号中所指定范围内的一个字符
d like '%[a-z][^1-2]'

13.查询语句：select 列名 from 表名 where 条件如：select* from students where no=1

14.插入数据行：insert [into]<表名> [列名]values<值列表>
       例：intset into students(name,address,grade,email,sex)
           values('长青菜','上海松江',5,'zqc@souhu.com','男')
      注意：（不能为标识列指定值，因为它的数字是自动增长的）

15.更新数据行：update <表名> set<列名=更新值>    [where<更新条件>]
      例：update students
          set address='石家庄'
          where   address='保定'

16.删除数据行： delete from<表名>[where<删除条件>]
例：delete from studentinfo where no='180108225'

17.问题：如果标识列a的初始值为1，增长量为2，则输入三行数据以后，再删除两行，下次再输入数据行的时候，标识值从多少开始？ ( 7 )

18.删除列： alter table student drop column address

19.exists： if exists (select*from?where name='yuandb') drop database studb

20.变量：   局部变量（必须以标记@作为前缀）
            全局变量（必须以标记@@作为前缀）
       全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值。

21.局部变量声明：例：decalre @name vachar(8)      decare @seat int
   赋值：set @变量名=值或select@变量名=值 set @name='张三'
           select @name=sname from students where no='1'

22.逻辑控制语句： if-else语句
            if(条件)
              begin
              语句1
              语句2...........
              end
            else
            begin
            语句1
            语句2.........
            end        注：else是可选的。

例： decalre @myavg float
     select @myavg =avg(course) from score
     print'平均分'+convert(varchar(5),@myavg)
     if(@myavg>70)
     begin
     print '本班成绩优秀，前三名成绩为：'
      select top 3 * from score order by score desc
      end
      else
        begin
        print '本班成绩较差，后三名成绩为：'
        select top 3 *from score order by score
       end

23.while循环语句：   while（条件）
                     begin
                     语句1
                     语句2..........
                     break
                     end
       例：declare @n int
           while(1=1) --条件永远成立
           begin
           select @n=count(*) from score
           where score<60    --统计不及格人数
           if (@n>0)
           update score     --每人加2分
            set score=score+2
          else
          break    --退出循环
          end
          print '加分后的成绩如下：'
          select * from score

24.case--end多分支语句
      语法：    case
                when 条件1 then 结果1
                when 条件2 then 结果2
                ..............
                 else 其他结果
                 end
       例：print 'abcde五级显示成绩如下：'
           select studentid,
               成绩=case
                        when score<60 then'e'
                        when   score between 60 and 69 then 'd'
                        when   score between 70 and 79 then 'c'
                         else 'a'
                      end
                     from score

25.批处理语句示例：
                   select * from stuinfo
                   select * from stumarks
                   update stumarks
                      set writtenexam=writtenexam+2
                   go

26.习题及答案：
        习题内容：则根据如下规则对成绩进行反复加分，直到平均分超过85分为止。请编写t-sql语句实现。
90分以上：   不加分
80－89分：   加1分
70－79分：   加2分
60－69分：   加3分
60分以下：   加5分
习题答案：
select * from score --原始成绩
declare @labavg int
while(1=1)
begin
    update score
      set score=
        case
            when score<60 then score+5
            when score between 60 and 69 then score+3
            when score between 70 and 79 then score+2
            when score between 80 and 89 then score+1
            else score
        end
     select @labavg=avg(score) from score
       if @labavg>=85
           break
end
select * from score --加分后的成绩

27.把一个表中的数据复制到另一个表中： insert into <表名>（列名）
select <列名>
from <源表名>

28.把查询结果放在新表中同时插入新的标识列：
           select 列名 identity（数据类型，标识种子，标识增长量）as
            列名 into 新表    from 原始表
例：   select   students.sname, students.saddress, students.semail, identity(int,1,1) as studentid    into tongxunlu    from students

29.更新数据行：
      语法： update<表名>
              set<列名=更新值>
              where <更新条件>
       例： update students
             set   address='软件学校'
              where address='软件学院'

30.问题：按多列排序：例：select no,name,address,grade
                   from   students
                   where   sex in(1,0)
                   order by   no,grade

31.字符串函数：
      replace:    select replace('莫勒可切.杨可'，'可'，‘兰')
          返回结果：莫勒兰切.杨兰
      upper:    select upper('sql server 课程')
       返回结果：sql server 课程
      getdate：   select getdate（）获取当前时间。
       dateadd：   select dateadd（mm,4,'01/01/2009'）
        返回结果：05/01/2009
      datename:     select datename(dw,'01/15/2000')
       返回结果：   saturday

32.聚合函数：
max-min:     select avg(score) as 平均成绩,max(score) as 最高分,min(score) as最低分       from score          where score>=60
count：    select count（*）as 及格人数      from score
                      where   score>=60

33. 分组查询：

34.习题：
假如成绩单中记录了学生的几次考试成绩，要查询每位学生的每门课的总成绩，怎么查？

35.分组查询--having
having：指定了组或聚合的搜索条件，常常与group by子句一起使用。
例：

36.多表查询——内联结
例：

37.多表联结——三表联结
例：

38.多表联结查询——右外联结
例：

39.银行案例——多表联结——左外联结：
例：

40.子查询：
例：

41.例：内联结（等值联结）

42.局部变量：
例：将姓名为里斯的学生的学号，出生日期和平均成绩分别赋给局部变量
@no_str @birthday_str @avgrade_num （用select语句来实现）

43.自连接查询：
例：（注：student2为不存在，是创建的）

44．声明局部变量：declare @变量名数据类型

45.if语句例：查询学号为457865的学生，如果成绩及格，则打印其姓名和成绩。

46.if-else语句：对于给定学号的查询，如果平均成绩不及格则打印姓名和平均成绩，否则打印学号。

47.多分支的if语句查询并实现分等级打印学生成绩

48.case 语句   使用select语句查询学生的学号，然后用case函数对学生的姓名和爱好进行简要说明。    select 学号=sno，姓名及爱好=
              case sno
              when ‘123' then ‘李好，游泳'
              。。。。。。。
              else ‘没有这人'
              end
              from student

49.while语句
      （如果学生平均成绩低于85分，则循环执行对每位学生成绩加0.5%，在循环过程中，如果最高成绩超过95分则退出循环；在加分过程中，当成绩的平均成绩大于或等于80分时打印出当前成绩的平均成绩）

50.子查询：查询成绩刚好通过60分的学生
              select sname from students
                  inner join score                ---内连接
                   on students.scode=score.studentid
                   where score=60
                    go

51.事务的特性：原子性、一致性、隔离性、永久性。
事务分类：显示事务、隐性事务、自动提交事务。

52.系统存储过程的名称都以“sp_”开头或“xp_”开头。
exec sp_databases 列出当前系统的数据库
exec sp_help students 查看表students的信息

53.定义存储过程的语法：
             create proc[edure] 存储过程名
              @参数1 数据类型=默认值 output，
               ...................
              @参数n 数据类型=默认值 output
              as
              sql语句
              go （注：参数可选，参数分输入参数、输出参数，输入参数允许有默认值）

54.创建不带参数的存储过程：
例：

55.调用存储过程：语法： exec 过程名[参数] 例：exec proc_stu

56.输入参数的默认值:
例：

57.调用带参数默认值的存储过程：
             exec proc_stu    -------采用默认值
             exec proc_stu 55    ------不采用默认值
             exec proc_stu @scorepass=55

58.带输出参数的存储过程：

59.调用带输出参数的存储过程：（注：调用时必须带output关键字，返回结果将存放在变量@sum 中）

60.错误处理：
使用raiserror语句：

61.使用raiserror语句

62.调用存储过程：
例：

               declare @s int
               exec proc_stuinfo '张三' ，@s ， 30    ———错误！应为（exec proc_stuinfo '张三'，@s output，30）
                exec proc_stuinfo '张三' ，@s     ————错误！应为（exec proc_stuinfo '张三'，@s output）
                exec proc_stuinfo @sage=22，@stuname='李四'，@m=@s output（正确！）

64.创建触发器语法：

65.insert 触发器
例：

66. 插入一条语句
例：

67. delete触发器示例：
            create trigger tri_delete_transinfo
            on transinfo
            for delete
            as
                print '开始备份数据，请稍候......'
                if not exists（select * from sysobjects where name='backuptable'）
                select * into backuptable from deleted        ------------从deleted表中获取被删除的交易记录。
                 else
                    insert into backuptable select * from deleted
                    print '备份数据成功，备份表中的数据为：'
                    select * from backuptable
                    select * from transinfo
                go

68. update 触发器：

69. 列级update触发器

70.数据库阶段数据管理的主要特点：1.采用数据模型表示复杂的数据结构；2.有较高的数据独立性；3.为用户提供了方便的用户接口；4.提供数据控制功能；5.增加系统的灵活性。

71. db(数据库)：存储在计算机内有组织的、统一管理的相关数据的集合。
dbms(数据库管理系统)：位于用户与操作系统之间的数据管理软件。
dbs(数据库系统)：是实现有组织的、动态的存储大量关联数据，以方便用户访问计算就硬件、软件和株距资源组成的计算机系统。

72.物理数据独立性和逻辑数据独立性：
物理数据独立性：内模式的修改，只需对模式/内模式映象做相应修改，而不需修改概念模式。逻辑数据独立性：概念模式的修改，只需对外模式/模式映象做相应修改，而不
需修改外模式。

73. dbms的主要功能：
数据定义（ddl）：三级结构、两级映象、完整性约束等。数据操纵（dml）：检索、更新。数据保护：数据恢复、并发控制、完整性控制、安全性控制等。数据维护：数据载入、转换、存储、、性能监控等。数据字典（dd）：存储三级结构定义的数据库称为dd，即系统数据库。dbms主要功能：数据库定义功能、数据存取功能、数据库运行管理功能、数据库的建立和维护功能。

74.--1、创建存储过程--

75.

76.代码：

76.导出sql server数据库中数据：

打赏

微信扫一扫

SQL Server2005

Sql实现行列转换方便了我们存储数据和呈现数据

从ms sql server 2005微软就推出了pivot和unpivot实现行列转换，这极大的方便了我们存储数据和呈现数据。今天... [阅读全文]
基于SQL2005 SQL2008 表结构信息查询升级版的详解(含外键信息)

select 表名=case when a.colorder = 1 then d.name else '' end, 表说明=ca... [阅读全文]
sql2005数据导出方法(使用存储过程导出数据为脚本)

执行语句：exec keleyioutputsqldata thetablename 以下是创建存储过程脚本：复制代码代码如下:... [阅读全文]
sqlserver2005利用临时表和@@RowCount提高分页查询存储过程性能示例分享

最近发现现有框架的通用查询存储过程的性能慢，于是仔细研究了下代码：复制代码代码如下:alter procedure [dbo]... [阅读全文]
SQL2005CLR函数扩展-深入环比计算的详解

此类问题还可以延伸到类似进销存的批次计算中，这也要关注其他历史记录来决定当前某条记录的状态。sql语句无法简单实现mdx语句的类似功... [阅读全文]