郝斌老师-sql-server-2005数据库大纲

声明：这是郝斌先生的笔记，属于郝斌先生，不是我的原创(只有三个选项，不是转载，不是翻译，我只好选择原创了)。我在博客中展示，只是为了方便查阅。我在此感谢郝斌先生。（郝斌先生自己说不得用于商业用途，而我在网上想找这份资料的时候却发现这份资料被好多大的网站用来归属自己的利益，最后我是在百度文库花了一个下载券下载下来的，将之记录在这里，同时方便其他和我一样有需要的人）
百度网盘：链接：http://pan.baidu.com/s/1hs8upDE 密码：p5c6

数据库学习大纲
什么是数据库
狭义:
存储数据的仓库
广义:
可以对数据进行存储和管理的软件以及数据本身统称为数据库

数据库是由表、关系、操作组成

为什么需要数据库
几乎所有的应用软件的后台都需要数据库
数据库存储数据占用空间小容易持久保存
存储比较安全
容易维护和升级
数据库移植比较容易
简化对数据的操作
为将来学习Oracle做准备
B/S架构里面包含数据库

数据库的安装和卸载

sg12000
解决挂起的问题

sq12005
参见视频

预各知识
学习数据库必须的学习数据库原理么
我的视频中会讲一些数据库原理的知识

学习SglServer 2005必须的先学一门编程语言么
不需要，但是懂一门编程语言的话会有助于学SQLServer 2005的TL-SQL

数据结构和数据库的区别
数据库是在应用软件级别研究数据的存储和操作
数据结构是在系统软件级别研究数据的存储和操作

什么是连接【重点】

有了编程语言为什么还需耍数据库
对内存数据操作是编程语言的强项，但是对硬盘数据操作却是编程语言的弱项
对硬盘数据操作是数据库的强项，是数据库研究的核心问题

建议初学者从三个方面学习数据库
1 数据库是如何存储数据的
字段 记录 表 约束 (主键 外键 唯一键 非空 check default 触发器)

2 数据库是如何操作数据的
insert update delete T-SQL 存储过程 函数 触发器

3 数据库是如何显示数据的
Select (重点的重点)
必备的一些操作
如何建数据库
如何删除数据库
如何附加和分离数据库
设置登录用户名和密码
如何创建用户

数据库是如何解决数据存储问题的【最基础内容，必须掌握】
1.表的相关数据
字段
一个事物的某一个特征
记录
字段的组合表示的是一个具体的事物
表
记录的组合表示的是同一类型事物的集合
表和字段、记录的关系
字段是事物的属性
记录是事物本身
表是事物的集合

列
字段的另一种称谓
属性
字段的另一种称谓
元组
记录的另一种称谓
2. create table命令
通过图形化界面建表
create table最后一个字段的后面建议不要写逗号
说明:简单掌握后面我们会再详细的介绍

3.什么是约束
定义
对一个表中属性操作的限制叫做约束
分类
主键约束
不允许重复元素避免了数据的冗余
外键约束
通过外键约束从语法上保证了本事物所
关联的其他事物一定是存在的

事物和事物之间的关系是通过外键来体现的

check约束
保证事物属性的取值在合法的范围之内
default约束
保证事物的属性一定会有一个值
唯一约束
保证了事物属性的取值不允许重复，
但允许其中有一列且只能有一列为空

问题:
    unique键是否允许多列为空?
    答案:
      SqlServer2005只允许一个unique列为空
      Oracle11G允许多个unique列为空

not null
要求用户必须的为该属性斌一个值，否则语法出错!

如果一个字段不写null 也不写not null
则默认是null即默认允许用户可以不给该字段斌值
如果用户没有为该字段赋值，则该字段的值默认是null

要注意null和default的区别
相同点:
都允许用户不赋值
不同点:
null修饰的字段如果用户不赋值则默认是null
default修饰的字段如果用户不斌值则默认是default指定的那个值

4.表和约束的异同
数据库是通过表来解决事物的存储问题的
数据库是通过约束来解决事物取值的有效性和合法性的问题
建表的过程就是指定事物属性及其事物属性各种约束的过程

5.什么是关系
定义: 表和表之间的联系
实现方式
通过设置不同形式的外键来体现表和表的不同关系

分类(假设是A表和B表)
一对一
既可以把表A的主键充当表B的外键
也可以把表B的主键充当表A的外键

一对多【重点】
把表A的主键充当表B的外键
或者讲:把A表的主键添加到B表来充当B表的外键

外键添加原则：在多的一方添加外键

多对多
多对多必须的通过单独的一张表来表示
例子
班级和教师
班级是一张表
教师是一张表
班级和教师的关系也是一张表

6.主键
定义：
能够唯一标示一个事物的一个字段或者多个字段的组合

主键的特点【重点】
含有主键的表叫做主键表
主键通常都是整数不建议使用字符串当主键(如果主键是用于集群式服务，才可以考虑用字符串当主键)
主键的值通常都不允许修改，除非本记录被删除
主键不要定义成id，而要定义成表名id或者表名_id
要用代理主键，不要用业务主键
任何一张表，强烈建议不要使用有业务含义的字段充当主键
我们通常都是在表中单独添加一个整型的编号充当主键字段
主键是否连续增长不是十分重要

7.外键
定义:
如果一个表中的若干个字段是来自另外若千个表的主键或唯一键
则这若干个字段就是外键
注意:
外键通常是来自另外表的主键而不是唯一键，因为唯一键可能为null
外键不一定是来自另外的表，也可能来自本表的主键
含有外键的表叫外建表，外键字段来自的那一张表叫做主键表
问题:
先删主键表还是外建表?

答案:先删外建表
如果先删主键表，会报错，因为这会导致外建表中的数据引用失败

查询【最重要难度最大，强烈建议所有的学生都要熟练掌握查询的内容】
1.计算列
select * from emp; —*表示所有的 from表示从emp表查询
select ename , sal from emp;
select ename, sal, sal * 12 as “年薪” from emp;——-as可省略
select 888 from emp;—-ok，输出行数是emp行数，值是888
select 5；—-ok，一行，值是5
注意：
在oracle中字段的别名不允许被单引号括起来
但是在sql2005中允许，
因此为了兼容性 最好字段别名用双引号括起来
2.distinct【不允许重复的】
select distinct deptno from emp;
—-会过滤重复的deptno
select distinct comm from emp；
—会过滤掉重复的null，或者所：有多个null，最终也只会输出一个
select distinct deptno, comm from emp;
—对deptno和comm组合进行过滤
select deptno, distinct comm from emp；
—error 逻辑上有冲突

3． between 【在某个范围】
—-查找工资在1500到3000之间（包括和）的所有员工信息
select * from emp
where sal >=1500 and sal <=3000
等价于
select * from emp
where sal between 1500 and 3000；

—查找工资小于1500或者大于3000之间的所有员工信息
select * from emp
where sal not between 1500 and 3000；
4. in
select * from emp where sal in (1500, 3000, 5000)
等价于
Select * from emp
where sal=1500 or sal=3000 or sal=5000

select * from emp where sal not in (1500, 3000,5000)
一把sal不是 也不是 也不是的记录输出
等价于
select*from emp
where sal<>1500 and sal<>3000 and sal<>5000
一数据库中不等于有两种表示:!二 <> 推荐使用第二种
一对或取反是并且 对并且取反是或

1. top【最前面的若干个记录 专属于Sql的语法，不可移植】
   select top 5 * from emp;
   select top 15 percent * from emp;一输出的是3个，不是2个
   select top 5 from emp;-error
   分页查询后面会讲
2. null 【没有值 空值】
   零和null是不一样的，null表示空值，没有值，零表示一个确定的值
   null不能参与如下运算: < > != =
   null可以参与如下运算:is not is
   select * from emp where comm is null;一输出奖金为空的员工的信息
   select * from emp where comet is not null:一输出奖金不为空的信息
   select * from emp where comm < > null;–输出为空 error
   select * from emp where comm ！= null;-偏出为空error
   select * from emp where come = null:一输出为空error

任何类型的数据都允许为null
create table tl (name nvarchar(20),cnt int, riqi datetime);
insert into tl values (null, null, null)；—OK

任何数字与null参与数学运算的结果永远是null
一输出每个员工的姓名年薪（包含了奖金) comm假设是一年的奖金
Select empno, ename, sal*12 + comm “年薪” from emp;
一本程序证明了:null不能参与任何数据运算否则结果永远为空

一正确的写法是:
select ename, sal*12+isnull(comm, 0)”年薪” from emp;
—isnull (comm, 0)含意思：如果comm是null 就返回零 否则返回comm的值

1. order by【以某个字段排序】
   order by a, b –a和b都是升序
   order by a, b des -a升序b降序
   order by a desc, b -a降序b升序
   order by a desc, b desc 一a和b都是降序
   文字描述:
   如果不指定排序的标准，则默认是升序 升序 :asc 默认可以不写
   为一个字段指定的排序标准并不会对另一个字段产生影响
   强烈建议为每一个字段都指定排序的标准

例子:
—asc是升序的意思默认可以不写 desc是降序
select * from emp order by sal ;一默认是按照升序排序

select * from emp order by deptno, sal;
一先按照deptno升序排序，如果deptno相同，再按照sal升序排序

Select * from emp order by deptno desc, sal;
一先按deptno降序排序 如果deptno相同,再按照sal升序排序(是升序不是降序)
–order by a desc, b, c, d desc只对a产生影响不会对后面的 b c d产生影响

select * from emp order by deptno, sal desc
一问题:desc是否会对deptno产生影响?
一答案:不会，先按deptno升序，如果deptno相同，再按sal降序

8.模糊查询【搜索时经常使用】
格式:
select 字段的集合from 表名where 某个字段的名字like 匹配的条件
匹配的条件通常含有通配符
通配符:
%
表示任意0个或多个字符
select * from emp where ename like ‘%A%’ –ename只要含有字母A就输出
select * from emp where ename like ‘A%’ –ename只要首字母是A的就输出
select * from emp where ename like ‘%A’ –ename只要尾字母是A的就输出

_ [这是下划线 不是减号]
表示任意单个字符
select *from emp where ename like ‘_A%’ -ename只要第二个字母是A的就输出

[a-f]
a到f中的任意单个字符 只能是a b c d e f中的任意一个字符
select * from emp where ename like ‘_[A-F]%’
一把ename中第二个字符是A或B或C或D或E或F的记录输出

[a, f]
a或f

[^a-c]
不是a也不是b也不是c的任意单个字符
例子：
select * from emp where ename like ‘_[“A-F]%’
一把ename中第二个字符不是A也不是B也不是C也不是D也不是E也不是F的记录输出
注意:
匹配的条件必须的用单引号括起来 不能省略 也不能改用双引号
通配符作为不同字符使用的问题

预备操作
create table student (
name varchar(20) null,
age int
)

insert into student values(‘张三’,88);
insert into student values(‘Tom’,66);
insert into student values(‘a_b’,22);
insert into student values(‘c%d’,44);
insert into student values(‘abces fe’,56);
insert into student values(‘c%’,66);
insert into student values(‘long”S’,100)

select * from student;
select * from student where name like ‘%\%%’ escape ‘\’;
—把name中含有%的输出
select * from student where name like ‘%_%’ escape ‘\’;
—-把name中含有_的输出；
9.聚合函数【多行记录返回至一个值 通常用于统计分组的信息】
函数的分类
单行函数
每一行返回一个值
多行函数
多行返回一个值
聚合函数是多行函数
例子:
select lower(ename) from emp;一最终返回的是行lower()是单行函数
select max(sal) from emp;一返回行max()是多行函数
聚合函数的分类
max0
min()
avg ()平均值
count ()求个数

1  count(*)
       返回表中所有的记录的个数
       select count(*) from emp;一返回emp表所有记录的个数

2 count(字段名)
返回字段值非空的记录的个数，重复的记录也会被当做有效的记录
select count(deptno) from emp;
一返回值是14 这说明deptno重复的记录也被当做有效的记录
select count(comm) from emp;
一返回值是这说明comm为null的记录不会被当做有效的记录

3 count(distinct 字段名)
返回字段不重复并且非空的记录的个数
select count(distinct deptno) from emp;
—-返回值是3,说明统计的是deptno不重复的记录个数

注意的问题
判断如下sql语句是否正确
select max(sal), min(sal), count(*) from emp; -ok
select max (sal) “最高工资”, min(sal)”最低工资”,count (*)”员工人数” from emp; -ok
select max(sal), lower(ename) from emp; -error单行函数和多行函数不能混用
select max(sal) from emP; -ok默认把所有的信息当做一组

1. group by【分组 难点】
   格式:
   group by 字段的集合
   功能:
   把表中的记录按照字段分成不同的组
   例子
   查询不同部门的平均工资
   select deptno, avg(sal) as “部门平均工资，from emp group by deptno
   注意
   理解: group by a, b，c的用法
   先按a分组，如果a相同，再按b分组，如果b相同，再按c分组， 最终统计的是最小分组的信息

   一定要明白下列语句为什么是错误的
   select deptno, avg(sal) as”部门平均工资”, ename
   from emp
   group by deptno

select deptno,ename
from emp
group by deptno

select deptno,job, sal
from emp
group by deptno, job
记住:使用了group by 之后select中只能出现分组后的整体信息，
不能出现组内的详细信息

1. having【对分组之后的信息进行过滤 难点】
   1.
   having子句是用来对分组之后的数据进行过滤
   因此使用having时通常都会先使用group by

   2.
   如果没使用group by但使用了having
   则意味着having把所有的记录当做一组来进行过滤
   极少用
   select count(*)
   from emp
   having avg(sal)>1000

3.
having子句出现的字段必须的是分组之后的组的整体信息
having子句不允许出现组内的详细信息

4.
尽管select字段中可以出现别名
但是having子句中不能出现字段的别名，只能使用字段最原始的名字

5．
having和where的异同
相同的:
都是对数据过滤，只保留有效的数据
where和having一样，都不允许出现字段的别名，
只允许出现最原始的字段的名字，此结论在SQL和Oracle都成立
不同:
where是对原始的记录过滤having是对分组之后的记录过滤
where必须的写在having的前面，顺序不可顺倒 否则运行出错

例子：
—-统计 输出部门平均工资大的部门的部门编号和部门的平均工资
select deptno, avg(sal) “平均工资”,count (*)”部门人数”,
max(sal) as “部门的最高工资”
from emp
where sal>2000 –where是对原始的记录过滤
group by deptno
having avg(sal)>3000 –一对分组之后的记录过

一判断入选语句是否正确
select deptno, avg(sal)”平均工资”,count (*)”部门人数”,
max(sal)”部门的最高工资”
from emp
group by deptno
having avg(sal)>300。一对分组之后的记录过滤
where sal>2000 -where写在Thaving的后面 error

12.连接查询
定义
将两个表或者两个以上的表以一定的连接条件连接起来,
从中检索出满足条件的数据
分类
内连接【重点的重点也是难点的难点】
1. select…from A, B的用法
产生的结果:
行数是a和B的乘积
列数是A和B之和
或者说
把A表的每一条记录都和B表的每一条记录组合在一起,
形成的是一个笛卡尔积

注意:
    select * from A, B输出结果和select * from B, A 是一模一样的
例子
    一输出行11列
    select * from emp, dept

1. select…from A, B where…的用法
   select…from A, B -A和B可以互换
   产生的笛卡尔积，用where中的条件进行过滤
   例子:
   一物出行11列
   select *
   from emp, dept –dept和emp互换输出结果不变
   where empno=7369

2. select…from A join B on…的用法
   select…from A Join B on ..—-A和B互换输出结果不变

3. SQL92标准和SQL99标准的区别
   select…from A, B where…是sq192标准
   select…from A Join B on…是sq199标准
   输出结果是一样
   推荐使用SQL99标准

   1. sq199更容易理解
      2.在sq199标准中，one和where可以做不同的分工
      on指定连接条件
      where对连接之后临时表的数据进行过滤
      示例:
      一把工资大于2000的员工的姓名和部门的名称输出和工资的等级
      —–sg199标准明显的优于sq192
      select “E”.ename,”D”.dname,”S”.grade
      from emp “E”
      join dept “D”
      on “E”.deptno=”D”.deptno
      join salgrade “S”
      on “E”.sal>=”S”.losal and “E”.sal<=”S”.hisal
      where “E”.sal>2000
      —-sg192标准
      select “E”.ename,”D”.dname,”S”.grade
      from emp “E”, dept “D”, salgrade “S”
      where “E”. sal>2000 and “E”.deptno=”D”.deptno
      and (“E”.sal>=”S”.losal and “E”.sal<=”S”.hisal)
4. select, from, where, join, on, group, order, top, having的混合使用
   查询的顺序： select top …
   from A
   Join B
   on……
   join C
   on…
   where……
   group by …
   having …
   order by…
   例子:
   一把工资大于1500的所有的员工按部门分组 并把部门平均工资大于2000得前两个最高的部门的编号部门的名称部门平均工资的等级
   一第一种写法
   select “D”.deptno, “D”.dname,”T”.avg_sal
   from dept “D” join
   (
   select top 2 “E”.deptno,avg(sal)”avg_sal”
   from emp”E” join dept”D”
   on “E”.deptno = “D”.deptno
   join SALGRADE”S”
   on “E”.sal between “S”.losal and “S”.hisal
   where “E”.sal>1500
   group by “E”.deptno
   order by avg(“E”.sal) desc
   )”T”
   on “T”.deptno = “D”.deptno
   inner join
   SALGRADE as “S”
   on “T”.”avg_sal” between “S”.losal and “S”.hisal

—–第二种写法
select “D”.deptno, “D”.dname,”T”.avg_sal
from (
select top 2 deptno,avg(sal)”avg_sal”
from emp
where sal>1500
group by deptno
order by avg(sal) desc
)”T”
join dept as “D”
on “T”.deptno = “D”.deptno
join SALGRADE as”S”
on “T”.avg_sal between “S”.losa l and “S”.hisal

6 习题
判断以下语句输出的是几行
select * from emp, dept where emp.deptno=10
select * from emp, dept where dept.deptno=10
一过滤条件不是连接条件

考虑如何把 select * from emp, dept where dept.deptno=10
以sg199标准来输出

1>求出每个员工的姓名 部门编号 薪水和薪水的等级
2>查找每个部门的编号 该部门所有员工的平均工资 平均工资的等级
3>查找每个部门的编号 部门名称 该部门所有员工的平均工资 平均工资的等级
4>求出emp表中所有领导的信息
5>求出平均薪水最高的部门的编号和部门的平均工资
6>把工资大于所有员工中工资最低的前3个人的姓名 工资 部门编号 部门名称 工资等级输出

外连接〔参见ppt]

完全连接〔参见ppt]

交叉连接〔参见ppt]

自连接
定义
一张表自己和自己连接起来查询数据
例子
不准用聚合函数求薪水最高的员工的信息

联合
定义
表和表之间的数据以纵向的方式连接在一起
注意:我们以前讲的所有的连接是以横向的方式连接在一起的

例子:
   输出每个员工的姓名 工资 上司的姓名

Select “El”. ename, “E1”. sal, “E2”.ename “上司的姓名”
from emp “El”
join emp “E2”
on “E1”. mgr=”E2”.empno
union
select enamel sal ‘已是最大老板’ from emp where mgr is null

注意:
若干个select子句要联合成功的话，必须的满足两个条件
1.这若干个select子句输出的列数必须是相等的
2.这若干个select子句输山列的数据类型至少是兼容的

identity【主键自动增长，用户不需要为identity修饰的主键斌值】
用户如何手动给被identity修饰的主键斌值
—-不重要 具体解决办法参见ppt
表中删除数据后又插入数据会导致主键不连续递增 怎么办?
主键是否连续增长不十分重要
具体解决办法参见ppt

视图
为什么需要视图
示例
求出平均工资最高的部门的编号和部门的平均工资
总结:
简化查询
避免了代码的冗余
避免了书写大盆重复的sql语句

什么是视图
视图从代码上看是一个select语句
视图从逻辑上看被当做一个虚拟表看待

如何创建视图
create view 视图的名字
as
-select的前面不能添加begin
select语句
-select的后面不能添加end

注意的问题
创建视图的select语句必须为所有的计算列指定别名
—error
create view v$_a as select avg(sal) from emp —ok create view v$_a
as
select avg(sal) as “avg_sal” from emp;
视图不是物理表，是虚拟表
不建议通过视图更新视图所依附的原始表的数据或结构

视图的优点
简化查询
增加数据的保密性
视图的缺点
增加了数据库维护的成本
视图只是简化了查询，但是并不能加快查询的速度
事务【重要 参见ppt】
初学者必须要理解的三个概念
事务是用来研究什么的
1. 避免数据处于不合理的中间状态
转账
2.怎样保证多用户同时访问同一个数据时呈现给用户的数据是合理的
很复杂，现在人类仍然没有设计出很好的解决办法!

事务和线程的关系
      事务是通过锁来解决并发访问的
      线程同步也是通过锁来解决并发访问的  synchronized

所谓并发访问是指:多用户同时访问同一个数据

事务和第三方插件的关系
直接使用事务库技术难度很大很多人是借助第三放插件来实现
因此我们一般人不需要细细的研究数据库中事务的语法细节

第三方插件要想完成预期的功能，一般必须的借助数据库中的事物机制来实现

索引

存储过程

游标

TL-SQL

触发器

分页查询
总结
假设每页显示n条记录，当前要显示的是第m页
表名是A 主键是A_id
Select top n *
From A
where A_id not in (select top (m-1)*n A_id from emp)