您现在的位置:首页 >> 综合经典 >> 电脑网络 >> 内容

SQL Server 2005学习笔记(大纲式) v1.0

时间:2011/8/31 22:27:50 点击:

  核心提示:第一章.数据库技术基础(略)第二章.SQL Sever 2005系统概述(略)第三章.数据库及其管理 1.通过企业管理器建立用户库 2.通过命令行创建用户库 CREATE DATABASE book ...

第一章.数据库技术基础(略)
第二章.SQL Sever 2005系统概述(略)
第三章.数据库及其管理
    1.通过企业管理器建立用户库
    2.通过命令行创建用户库
    CREATE DATABASE book
    ON PRIMARY
    (NAME=book_data,FILENAME='d:\book.mdf',SIZE=55,
    MAXSIZE=120,FILEGROWTH=10%)
    LOG ON
    (NAME=book_log,FILENAME='d:\book.ldf',SIZE=12,
    MAXSIZE=30,FILEGROWTH=3
    )
    3.事务日志
    事务日志包含用于恢复数据库的事务日志信息。应用事务日志时将出现前滚或回滚动作。
    4.查看数据库信息
    (1)通过企业管理器查看
    (2)通过存储过程sp_helpdb查看
    exec sp_helpdb pubs
    (3)查看数据库空间信息
    exec sp_spaceused pubs
    5.管理数据库
    (1)打开数据库
    USE database_name
    (2)修改、增加和缩减数据库容量
    ALERT DATABASE database_naem
    MODIFY_FILE
    (NAME=file_name,
    SIZE=newsize)
    例子:
    ALERT DATABASE book
    MODIFY_FILE
    (NAME='d:\book.mdf',
    SIZE=60)
    缩减数据库
    DBCC SHRINKDATABASE (UserDB, 10) #使得userDB数据库中有10%的可用空间,不指定size则缩减到最小。
    查看目前数据库选项设定及修改
    EXEC sp_dboption 'book'
    例子:
    EXEC sp_dboption 'book','read only',True
    数据库更名
    EXEC sp_renamedb 'book','shu'   #需在单用户模式
    删除数据库
    DROP database 'book'
第四章.数据库中表的基本操作
    1.创建和管理表
    例子:
    USE book
    go
    CREATE TABLE book1
    (
        编号 char(8) NOT NULL,
        ISBN号 char(13) NOT NULL,
        书名 NVARCHAR(255),
        定价 MONEY,
        出版社 NVARCHAR(255),
        出版日期 DATETIME
    )
    2.添加、删除和修改列
    ALTER TABLE products ADD column_test VARCHAR(20) NULL
    ALTER TABLE products ALTER COLUMN column_test int NULL
    ALTER TABLE products DROP COLUMN column_test
    3.查看表结构、插入、更新和删除表数据
    (1)查看
    select * from book1
    (2)插入
    insert into book1
    values('1111','2222','sql数据库','35','中山大学','03-12-2005')
    (3)更新
    update book1
    set 出版社 = '华南师大'
    where ISBN号 = '2222'
    (4)删除
    delete from book1 where 定价=100
    drop table book1
    4.定义约束
    check约束 default约束 primary key约束 foreigh key约束 unique约束
    (1)创建check约束
    use book
    alter table teacher
    add constraint CK_teacher CHECK(性别='男' or 性别='女')
    (2)删除check约束
    alter table teacher
    drop constraint CK_teacher
    (3)default约束的创建,删除和查看
    创建:alter table teacher
         add constraint 性别 default '男' for 性别
    删除:drop constraint de_性别
    (4)primary key约束的创建、查看和删除
    创建:add constraint PK_作者编号 primary key clustered(作者编号)
    删除:drop constratin PK_作者编号
    (5)foreigh key约束
    创建:add constraint FK_编号 foreign key(编号)
    references book1(编号)
    删除:drop constratin FK_编号
    (6)unique约束
    创建:ADD CONSTRAINT uc_编号 UNIQUE (编号,性别)
    删除:DROP CONSTRAINT uc_编号
    5.默认和规则
    (1)建立默认约束
    Create Default MR_定价
    AS '100'
    (2)绑定默认约束
    EXEC sp_bindefault MR_定价,'book1.定价'
    (3)建立规则
    Create rule GZ_定价
    AS @定价>=0 and @定价<=10000
    (4)绑定规则
    EXEC sp_bindrule GZ_定价,'book2.定价'
第五章.SQL Server的数据查询
    1.SELECT语句
    (1)使用数据库
    use book
    go
    (2)select语句
    select 选择列表
    from 表的列
    where 查询的条件
    例子:
    use book
    select 编号,书名,ISBN号
    from book1
    where 编号='XH5468'
    go
    (3)*表示所有
    select * from book1
    (4)使用DISTINCT消除重复值
    例子:
    Select DISTINCT 出版社
    from book1
    (5)使用TOP n[precent]仅返回N行
    返回前5行
    例子:
    use book 
    go
    select top 5 *
    from book1
    返回前5%
    use book
    go
    select top 5 percent *
    from book1
    go
    (6)在查询结果中显示字符串
    例子:
    use book
    go
    select 书名,'图书定价为:';定价
    from book1
    go
    (7)使用where子句给出查询的条件
    例子:
    use book
    go
    select 出版社
    from book1
    where 书名='红楼梦图咏(3册)'
    go
    (8)列名为表达式
    例子:
    use book
    go
    select min(定价) as 最小定价,max(定价) as 最大定价,avg(定价) as 平均定价
    (9)使用order by子句重新排列查询结果
    例子:
    降序排列
    use book
    go
    select 书名,定价
    from book1
    order by 定价 desc
    例子:
    先按书名降序排列,若书名相同则按编号升序排列
    use book
    go
    select 书名,编号,出版社
    from book1
    order by 书名 desc,编号 asc
    go
    (10)使用IN关键字
    例子:
    use book
    go
    select 书名
    from book1
    where 编号 in('xh5960','yb2222','ybzt8824')
    go
    (11)使用like关键字进行查询
    例子:
    查询以"中"字开头的书名
    use book
    go
    select *
    from book1
    where 书名 like '中%'
    以"大学"两字结尾的书名的所有书
    use book
    go
    select *
    from book1
    where 书名 like '%大学'
    查询第二个字为"人"的所有图书
    use book
    go
    select *
    from book1
    where 书名 like '_人%'
    go
    查询第一个不是"半"的所有图书
    use book
    go
    select *
    from book1
    where 书名 like '[^半]%'(where 书名 not like '半%')
    go
    (12)使用is null 关键字查询没有赋值的行
    use book
    go
    select 书名,出版社
    from book1
    where 出版社 is null
    go
    (13)
    查询某一范围内的信息
    use book
    go
    select *
    from book1
    where 定价 < 25
    go
    use book
    go
    select *
    from book1
    where 定价 > 1000 and 定价 < 2000
    (14)
    使用between关键字
    use book
    go
    select 书名,定价
    from book1
    where 定价 betweed 1000 and 2000
    order by 定价 asc
    go
    (15)使用compute子句来计算总计
    use book
    go
    select 编号,书名,ISBN号,定价,出版社
    from book1
    where 出版社 = '中国长安'
    compute avg(定价)
    go
    (16)使用compute by进行分组计算
    use book
    go
    select 编号,书名,定价,出版社
    from book1
    order by 出版社
    compute avg(定价) by 出版社
    go
    (17)使用group by子句
    use book
    go
    select 出版社,count(出版社) as '本数'
    from book1
    group by 出版社
    go
    (18)使用嵌套查询
    use book
    go
    select 编号,书名,定价
    from book1
    where 定价 > (select avg(定价) from book1)
    go
    (19)使用union查询
    使用UNION运算符
    use book
    go
    select 书名 from book1
    union
    select 编号 from book2
    go
    (20)多表查询
    use book
    go
    select book1.编号,书名,定价,作者姓名,职称
    from book1,teacher
    where book1.编号 = teacher.编号 and book1.编号='XH5468'
    go
    (21)自然连接(消除相等连接产生的冗余)
    use book
    go
    select book1.*,作者姓名,职称
    from book1,teacher
    where book1.编号=teahcer.编号
    go
    (22)使用exists关键字
    测试子查询中的行是否存在
    use book
    go
    select 书名,定价
    from book1
    where exists(select 编号 from teacher)
    go
第六章.索引
    索引分类
    UNIQUE 唯一索引
    CLUSTERED 聚集索引
    NONCLUSTERED 非聚集索引
    1.建立索引
    基于"编号"的聚集,唯一,简单索引
    use book
    go
    create unique clusterd
    index IX_book1 on book1(编号)
    go
    2.删除索引
    use book
    go
    drop index book1.IX_book1
    go
    删除索引时应该先删除primary key约束或unique约束创建的索引
    3.显示索引信息
    use book
    go
    exec sp_helpindex book1
    go
    4.重新命名索引
    use book
    go
    exec sp_rename 'book1.IX_book1','IX_book1new'
    go
   
第七章.视图及其用途
    1.创建视图
    use book
    go
    create view v_book1
    as
    select *
    from book1
    where 出版社 = '中国长安'
    2.视图的修改和删除
    (1)修改视图
    use book
    go
    alter view v_book1
    with encryption  ##加密视图
    as
    select 出版社,count(*) 出版总数,sum(定价) 出版总价
    From book1
    group by 出版社
    (2)删除视图
    use book
    go
    drop view v_book1
    4.重命名视图及显示视图的信息
    (1)查看视图定义信息
    use book
    go
    exec sp_helptext 'v_book1'
   
    use book
    go
    exec sp_depends 'v_book1'
    5.视图的应用
    use book
    go
    select 书名,作者姓名
    from v_book1
    where 定价=59.8
    通过视图更新数据
    (1)插入
    use book
    go
    insert into v_book1
    values('硬件测试',50)
    (2)修改和删除
    update delete
    例子是错误的
    use book
    go
    insert into v_book1
    values('硬件测试',50,'周奇奇')
第八章.存储过程
    1.存储过程与视图的比较
    (1)单个存储过程可执行一系列语句,而在视图中只能是select语句
    (2)视图不能接受参数,只能返回结果。而存储过程可以接受参数,
       包括输入、输出参数,并能返回多个结果。
    (3)多表联查可用视图,存储过程完成复杂处理。
    (4)存储过程中可以用视图
    2.创建和执行存储过程
    (1)创建存储过程
    with encryption 对存储过程加密
    with recompile 对存储过程重新编译
    use book
    go
    create procedure p_book1
    as
    select * from book1 where 出版社='中国长安'
    (2)执行存储过程
    use book
    go
    exec p_book1
    (3)带参数的存储过程
    create procedure p_book1
    @出版社 varchar(20)
    as
    select * from book1 where 出版社 = @出版社
    执行带有输入参数的存储过程
    exec p_book1 @出版社 = '中国长安'
    go
    exec p_book1 @出版社 = '安徽人民'
    go
    (4)带输出参数的存储过程
    create procedure p_book1
    @出版社 varchar(20), @booklnum smallint output
    as
    set @booklnum=
    (
    select count(*) from book1
    where 出版社=@出版社
    )
    print @booklnum
    执行
    declare @出版社 varchar(20),@booklnum smallint
    set @出版社='中国长安'
    exec p_book1 @出版社,@booklnum
    (5)
    修改、删除、重命名存储过程
    修改存储过程:
    alter procedure p_book1
    @出版社 varchar(20)
    with enctyption
    as
    select 出版社,ISBN号,定价,作者姓名
    from book1,teacher
    where book1.编号=teacher.编号 and 出版社 like '%@出版社'
    删除存储过程:
    use book
    go
    drop procedure p_book1
    3.存储过程的重编译处理
    (1)创建存储过程时用with recompile
    use book
    go
    create procedure p_book1
    @出版社 varchar(20)
    with recompile
    as
    select * from book1 where 出版社=@出版社
    (2)执行存储过程时指定重编译选项
    use book
    go
    exec p_book '中国长安' with recompile
    (3)通过系统存储过程设定重新编译选项
    use book
    go
    exec sp_recompile p_book1
    4.触发器的创建和管理
    (1)insert触发器
    use book
    go
    create trigger book1_tri
    on book1
    for insert
    as
    print '数据插入成功'
    go
    delete触发器
    use book
    go
    create trigger book1_tri
    on book1
    instead of delete
    as
    print '数据删除不成功'
    go
    (2)update触发器
    use book
    create trigger book1_tri
    on book1
    if update(定价)
    begin
    rollback transaction  ##恢复原来数据,防止数据被更新
    end
    (3)delete触发器
    use book
    go
    create trigger book1_tri
    on book1
    for delete
    as
    begin
    raiserror('Unauthorized',10,1)
    rollback transaction
    end
    (4)管理触发器
    查看和表有关的所有触发器
    use book
    go
    exec sp_helptrigger book1
    go
    使用系统表sysobjects得到触发器相关信息
    use book
    go
    select name
    from sysobjects
    where type = 'tr'
    go
    修改触发器名字
    sp_rename book1_tr1 book1_trnew
    修改触发器内容
    use book
    go
    alter trigger book1_tr1
    on book1
    instead of delete,insert,update
    as
    print '傻逼!你执行的删除、增加、修改都无效'
    删除触发器
    drop trigger book1_tr1
    禁止和启动触发器
    alter table book disable trigger all
    alter table book enable trigger all
第九章.SQL Server2005安全管理
    1.sql server2005的安全认证模式
    (1)身份验证阶段
    (2)权限验证阶段
    2.身份验证
    (1)windows身份验证模式
    (2)混合身份验证模式
    3.权限认证
    当用户身份通过验证连接到sql server2005服务器后,在用户可以访问的每个数据库
    中都要求单独的用户账号,对于没有账户的数据库,将无法访问。
    4.创建sql server登陆账户
    (1)使用对象资源管理器创建登陆账户(略)
    (2)使用存储过程创建登陆账户
    use book
    go
    exec sp_addlogin 'book_login','book'
    exec sp_adduser 'book_login'
   
    exec sp_addlogin 'my_login','123456','book'
    5.创建数据库的用户
    (1)使用对象资源管理器创建数据库用户(略)
    (2)使用存储过程创建数据库用户
    use book
    go
    exec sp_grantdbaccess 'b_login','b_user1',db_owner
    go
    (3)使用sp_adduser
    use book
    go
    exec sp_addlogin 'b_login','book'
    go
    exec sp_adduser 'b_login','b_user1',db_owner
    6.安全管理账户
    (1)使用对象资源管理器查看登陆账户(略)
    (2)使用sp_helplogins可查看登陆账户
    (3)修改登陆账户属性
    用sp_password改变登陆账号的密码
    exec sp_password 'zhouqi','zhouqiqi','b_login'
                     {旧密码}  {新密码}
    用sp_addsrvrolemember将登陆账号加入服务器角色
    use book
    go
    exec sp_addsrvrolemember 'b_login','dbcreator'
    修改数据库中用户与登陆的关系
    格式//sp_change_users_login 'action','user','login'
    use book
    go
    exec sp_addlogin 'book_newlogin'
    go
    exec sp_change_users_login 'update_one','zhouqi','book_newlogin'##将当前登陆用户
    改为新增加的用户"book_newlogin"
    (4)查看数据库的用户
    使用对象资源管理器查看数据库用户(略)
    用存储过程查看数据库的用户
    use book
    go
    exec sp_helpuser
    7.删除登陆和用户账号
    (1)使用对象资源管理器删除登陆账户(略)
    (2)用存储过程删除登陆账号
    use book
    go
    exec sp_droplogin 'b_login'
    (3)删除用户账号
    使用对象资源管理器删除用户账号(略)
    使用sp_revokedbaccess可删除用户账号
    use book
    go
    exec sp_revokedbaccess 'b_user1'
    8.管理数据库用户和角色
    (1)创建新角色
    使用对象资源管理器创建角色(略)
    (2)使用存储过程创建角色
    use book
    go
    exec sp_addrole 'myrole','dbo'
    创建应用程序角色
    use book
    go
    exec sp_addapprole 'approle','123456'
    (3)删除角色
    使用对象资源管理器删除角色(略)
    使用存储过程删除角色
    use book
    go
    exec sp_droprole 'myrole'
    9.设置数据库用户账号的权限
    (1)授予权限
    use master
    go
    grant create database,create table
    to book_user1,book_user2
   
    use book
    go
    grant select on book1 to public
    go
    grant insert,update,delete, on book1 to book_user1,book_user2
    (2)拒绝权限
    use master
    gp
    deny create database, create table
    to book_user1,book_user2
   
    use book
    go
    revoke select on book1 to public
    go
    deny insert,update,delete, on book1 to book_user1,book_user2
    (3)撤销权限
    revoke create table from book_user1
   
    revoke create table,create default
    from book_user1,book_user2
第十章.数据复制
    实践步骤:
    1.建立分发服务器
    2.创建分发数据库
    3.为分发服务器指定分发数据库
    4.指定要发布的数据所在的数据库
    5.创建快照发布
    6.创建强制订阅
第十一章.数据库的日常维护与管理
    1.数据库备份方式
    (1)数据库完全备份
    (2)差异备份或称增量备份
    (3)事务日志备份
    (4)数据库文件和文件组备份
    2.数据库还原方式
    (1)完全还原
    (2)简单还原
    (3)大容量日志记录还原
    3.备份操作
    (1)在对象资源管理器中使用界面备份数据库(略)
    (2)命令行备份
    use master
    go
    exec sp_addumpdevice 'disk','book_bak2','d:\book_bak2'
    backup database book to book_bak2
    (3)使用对象资源管理器备份事务日志(略)
    (4)
    use master
    go
    exec sp_addumpdevice 'disk','book_log_bak2','d:\book_log_bak2'
    backup log book to book_bak2
    4.还原数据库
    (1)使用对象资源管理器还原数据库(略)
    (2)命令行还原
    use master
    go
    restore database book from disk='d:\book_bak2'
    5.附加数据库
    使用对象资源管理器附加数据库(略)

==============此文转自【穴居狂人】博客

作者:不详 来源:网络
相关评论
发表我的评论
  • 大名:
  • 内容:
  • 阿发美文网(www.afa008.cn) © 2020 版权所有 All Rights Reserved.
  • Email:afashanxi@126.com 站长QQ:360179349 ,感谢老Y
    本站只为学习交流用,如果侵犯了您的版权,请及时联系我们,24小时内删除。

    全国涉黑涉恶违法犯罪线索举报

    陕ICP备0105016号
  • Powered by laoy! V4.0.6