top 4 * from emp where sal between 1500 and 3000 order by sal desc;

• order by … desc降序排序，不写desc则默认是升序排序

null

• 零和null是不一样的，null表示空值，没有值，零表示一个确定的值
  
  • null 不能参与以下运算：<>、!=、=
  • null 可以参与如下运算：is、is not
• select * from emp;
  
  • 输出奖金为空的员工的信息
• select * from emp where comm <> null;
  
  • 输出为空，error
• select * from emp where comm != null;
  
  • 输出为空，error
• select * from emp where com = null;
  
  • 输出为空，error
• 以上的总结：null不能参与<>、!=运算
• select * from emp where comm is null;
  
  • 输出奖金为空的员工的信息
• select * from emp where comm is not null
  
  • 输出奖金不为空的员工的信息
• select empno,ename,sal*12+comm “年薪” from emp;
  
  • 输出每个员工的姓名，年薪（包含了奖金）,comm假设是一年的奖金，其中奖金可能为空（null）
  • 最后运行结果为，奖金（comm）为空，工资（sal）不为空，得出的结果为空（null）
  • 本程序证明了：null不能参与任何数据运算，否则结果永远为空

• null不能参与任何数据运算的解决方法：

  • 使用isnull(列名，0)函数

  • select enam，sal*12+isnull(comm，0) "年薪" 
    from emp;

    • isnull(comm, 0)表示，如果comm是null，则返回零，否则返回comm的具体的值

order by【排序】

• asc是升序的意思，默认省略，desc是降序，需要指定
• 如果不指定排序的标准，则默认是升序，升序使用asc表示，默认可以省略
• 为一个字段指定的排序的标准并不会对另外一个字段产生影响
• 强烈建议为每一个字段都指定排序的标准
• 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升序排序
  • 记住sal是升序而不是降序排序
  • desc仅仅对前面的一个产生作用
• select * from emp order by deptno desc ,sal;
  
  • 问题 ：desc是否会对deptno产生影响？
  • 答案：不会
  • 先按照deptno升序排序，如果deptno相同，再按照sal降序排序

模糊查询

• 格式：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的就输出

  • [a-f] 表示a到f的任意单个字符，只能是a、b、c、d、e、f中的任意一个字符

    • select * 
      from emp
      where ename like ‘_[A-F]%‘

      • ename 只要第二个字母是A到F中的任意一个就输出
  • [a, f] 表示包含a或者f
  • [^a-f] 表示不包含a到f的任意单个字符
• 注意 ：
  
  • 匹配的条件必须使用单引号括起来，不能省略也不能改用双引号
  • 通配符作为普通字符使用的问题【使用escape来表示】
    
    • select * from student where name like ‘%_%’ escape ‘\’;
      
      • 这表示把所有记录中的name列包含下划线’_’字符的记录输出
      • escape ‘\’表示把’\’字符当作转义字符的标志
      • 在sql中可以把任意的字符当作转义字符的标志，具体是把哪个字符当作转义字符，这是由* escape ‘要制定为转义字符的字符’ *来决定的

聚合函数【多行记录返回至一个值，通常用于统计分组的信息】

• 函数的分类：
  
  • 单行函数：
    
    • 每一行返回一个值
  • 多行函数：
    
    • 多行返回一个值
    • 聚合函数是多行函数
  • 例子：
    
    • select lower(ename) from emp;
      
      • lower()是单行函数
    • select max(sal) from emp;
      
      • max()是单行函数
• 聚合函数的分类：
  
  • max()
  • min()
  • avg() 平均值
  • count()
    
    • count(*) 返回表中所有的记录的个数
    • count(字段名) 返回字段值非空的记录的个数，重复的记录也会被当作有有效的记录
    • count(distinct 字段名) 返回字段值非空的而且不重复的记录的个数
    • select count(*) from emp；
      
      • 返回emp表的所有记录的个数
    • select count(deptno) from emp;
      
      • 这里deptno是部门数，但是在返回结果的时候，没有排除重复，致使的到的答案是错误的
    • select count(distinct deptno) from emp;
      
      • deptno重复的记录会被忽略
      • 统计deptno不重复的记录的个数
    • select count(comm) from emp；
      
      • 这里comm是奖金，奖金可以为空
      • 这里返回结果中不包含null的记录，说明了count()不会记录记录为null的行数
• 注意的问题：
  
  • 判断下面的sql语句是否正确
    
    • select max(sal),min(sal),count(*) from emp;
      
      • okay
    • select max(sal) “最高工资”,min(sal) “最低工资”,count(*) “员工人数” from emp;
      
      • okay
    • select max(sal),lower(ename) from emp;
      
      • error
      • 单行函数和多行函数不能混用
    • select max(sal) from emp;
      
      • 默认把所有记录看成一个分组

group by

• 格式：
  
  • group by 字段的集合
• 功能：
  
  • 把表中的记录按照字段分成不同的组
• 注意：
  
  • group by a,b,c的用法
    
    • 先按照a分组，如果a相同，再按b分组，如果b相同，再按照c分组，最终统计的是最小分组的信息
• 输出每个部门deptno的编号和该部门的平均工资avg(sal)
  
  • select deptno, avg(sal) from emp group by deptno;
• 判断下面的语句是否正确
  
  • select deptno, avg(sal), ename from emp group by deptno;
    
    • 这里是错误的，因为已经被group by进行分组了，仅有的是部门deptno，没有了ename，所以会报错
• 总结：使用了group by之后select 中只能出现分组后的整体信息，不能出现组内的详细信息

having【对分组信息进行过滤】

• having子句是用来对分组之后的数据进行过滤，因此使用having的时候通常都会使用group by
• 如果没有使用group by的话，则意味着having把所有的记录当作一组来过滤
  
  • select cout(*) from emp having avg(sal) > 1000;
• having 子句出现的字段必须是分组之后的组的整体信息，having子句不允许出现组内的详细信息
• 尽管select字段可以出现别名，但是having中不能出现字段的别名，只能够使用字段最原始的字段名
• having和where的异同：
  
  • 相同：
    
    • 都是对数据过滤，只保留有效的数据
    • where和having一样，都不允许出现字段的别名，只允许出现字段最原始的字段名
  • 不同：
    
    • where是对原始的记录进行过滤，having是对分组之后的记录进行过滤
    • where必须写在having的前面，顺序不可以颠倒，否则运行出错

• 例子： 输出部门平均工资大于2000的部门的部门编号，和平均工资

  • select deptno ,avg(sal) 
    from emp 
    group by deptno 
    having avg(sal) > 2000；

  • 注意：having只能出现分组后的整体信息，不能出现组内的详细信息

• 把姓名不包含A的所有的员工按照部门的编号分组

• 统计输出部门平均工资大于2000的部门的部门的编号，部门的平均工资

  • select deptno,avg(sal) "平均工资" 
    from emp 
    where ename not like ‘%A%‘ 
    group by deptno 
    having avg(sal) > 2000;

    • 其中where是对原始的记录进行了过滤
    • having是对分组之后的记录进行了过滤

  • select deptno,avg(sal) "平均工资" 
    from emp g
    roup by deptno 
    having avg(sal) > 2000 
    where ename not like ‘%A%‘;

    • error
• 总结：所有select的参数的顺序是不允许变化的，否则编译的时候会出错

连接查询

---

定义：

• 将两个表或者两个以上的表以一定的连接条件连接起来
• 从中检索出满足条件的数据

分类：

内连接【重点中的重点，也是难点中的难点】

• select … from A,B的用法【笛卡尔集】

  • select * 
    from emp,dept

    • 假如emp是14行8列，dept是5行3列
    • 那么最终的结果是有14*5=70行8+3=11列
    • 总结 ：
      
      • 行数是A和B之积
      • 列数是A和B之和
      • 或者说：把A表的每一条记录都和B表的每一条记录组合在一起，形成的就是个笛卡尔集

• select … from A,B where … 的用法

  • select * 
    from emp, dept 
    where empno = 7369

    • 产生后的笛卡儿集后用where中的条件进行过滤
  • select … from A join B on .. 的用法

  • select "E".ename "员工名称","D".dname "部门名称" 
    join dept "D" 
    on "E".deptno = "D".deptno;

    • join是连接
    • on是连接条件
  • on不能省略，有join就必须有on

• select … from A,B where 与 select … from A join B on … 的比较

  • 前者是sql92标准，后者是sql99标准，输出结果是一样的，推荐使用SQL99标准

    • SQL99更容易理解
    • 在SQL99标准中，on和where可以做不同的分工：on指定连接条件，where对连接后临时表的数据进行过滤

  • select "E".ename, "D".dname, "S".grade 
    where "E".sal > 2000 
    join "D" 
    on "E".deptno = "D".deptno 
    join salgrade "S" 
    on "E".sal >= "S".local and "E".sal <= "S".hisal;

  • select、from、where、join、on、group、order、top

    select * 
    from emp 
    join dept 
    on emp.deptno = dept.deptno 
    having dept.deptno = 10;

    • error，因为having是对分组之后的进行过滤，这里没有分组，肯定出错

内连接例子

1.求出每个员工的姓名，部门编号，薪水和薪水的等级

select "E".ename,"E".sal,"S".grade
from emp "E"
join salgrade "S"
on "E".sal >= "S".losal and "E".sal <= "S".hisal;

2.查找每个部门的编号，该部门所有员工的平均工资，平均工资的等级

select "T".deptno,"T"."avg_sal" "部门平均工资","S".grade "工资等级"
from (
    select deptno,avg(sal) "avg_sal"
    from emp
    group by deptno
) "T"
join salgrade "S"
on "T".avg_sal between "S".losal and "S".hisal;

或者：

select "T".deptno,"T"."avg_sal" "部门平均工资","S".grade "工资等级"
from salgrade "S"
join (
    select deptno,avg(sal) "avg_sal"
    from emp
    group by deptno
) "T"
on "T".avg_sal between "S".losal and "S".hisal;

3.查找每个部门的编号，部门名称，该部门所有员工的平均工资，平均工资的等级

select "T".deptno,"T"."avg_sal" "部门平均工资","S".grade "工资等级","D".dname "部门名称"
from (
    select deptno,avg(sal) "avg_sal"
    from emp
    group by deptno
) "T"
join salgrade "S"
on "T".avg_sal between "S".losal and "S".hisal
join dept "D"
on "T".deptno = "D".deptno;

4.考虑下面语句的实际含义 以及这样写是否恰当?

select * from emp
join dept
on emp.deptno = dept.deptno and emp.sal>2000  

on中既可以写连接条件，也可以写过滤条件，但是不推荐，应该分开写。on中只写连接条件，where中写过滤条件。
等价于

select * from emp
join dept
on emp.deptno = dept.deptno 
where emp.sal>2000

等价于

select * from dept
join emp
on dept.deptno  = emp.deptno
where emp.sal>2000

等价于

select *
from dept,emp
where dept.deptno  = emp.deptno and emp.sal>2000

下面例子：error 有join就必须的有on

select * 
from emp
join dept

5.求出emp表中所有领导的信息

select * from emp
where empno in (select mgr from emp)

思考题
求出emp表中所有非领导的信息

select * from emp
where empno not in (select mgr from emp)

in与null的组合所带来的问题
6.求出平均薪水最高的部门的编号和部门的平均工资
第一种写法

select top 1 deptno "部门的编号",avg(sal)"平均工资"
    from emp
    group by deptno
    order by avg(sal) desc

第二种写法

select "E".*
    from (  
            select deptno, avg(sal) "avg_sal" 
                from emp 
                group by deptno 
    ) "E"
    where "E"."avg_sal" = (
        select max("avg_sal") 
        from (
            select deptno, avg(sal) "avg_sal" 
            from emp 
            group by deptno
        ) "T"
    )

7.有一个人工资最低，把这个人排除掉；剩下的人中工资最低的前3个人的姓名、工资、部门编号、部门名称、工资等级输出

select top 3 "T".ename, "T".sal, "T".deptno, "D".dname, "S".grade
    from (
        select *
            from emp "E"  --153行
            where sal > ( 
                select min(sal) 
                from emp
            )
    ) "T"  --这里的"E"与153行的"E"是没有冲突的，因为范围不一样
    join dept "D"
    on "T".deptno = "D".deptno
    join salgrade "S"
    on "T".sal between "S".losal and "S".hisal
    order by "T".sal asc

8.查询的顺序

select top ...
from A 
join B 
on ...
join C
on ...
where ...
group by ...
having ...
order by ...

外连接

• 分为左外连接和右外连接

定义

• 不仅返回满足连接条件的所有记录，而且会返回部分不满足条件的记录

分类

• 左外连接：不仅仅返回满足条件的所有记录，而且会返回左表不满足连接条件的记录
  
  • 帮助文档：左外向连接的结果集包括left outer子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有列表均为空值
• 右外连接：不仅仅返回满足条件的所有记录，而且会返回右表不满足连接条件的记录
  
  • 帮助文档：右外向连接的结果集包括right outer子句中指定的右表的所有行，而不仅仅是连接列所匹配的行。如果右表的某行在左表中没有匹配行，则在相关联的结果集行中左表的所有列表均为空值

完全连接

• 完全连接相当于左外连接加上右外连接
• 在结果集和包含三部分内容
  
  • 两个表中匹配的所有行的记录
  • 左表中那些在右表中找不到匹配的行的记录，这些记录的右边都为null
  • 右表中那些在左表中找不到匹配的行的记录，这些记录的左边都为null

交叉连接

select * from emp 
cross join dept

等价于

select *
from emp,dept

自连接

定义

• 一张表自己和自己连接起来查询数据

例子

• 不准用聚合函数，求薪水最高的员工的信息

select * 
from emp 
where empno not in (
    select dintinct "E1".empno
    from emp "E1"
    join emp "E2"
    on "E1".sal < "E2".sal
)

联合

定义

• 表和表之间的数据以纵向的方式连接在一起

注意：

• 我们以前将的连接都是以横向的方式连接在一起的

例子

输出所有员工信息，及其老板的名字，如果员工没有上司。则输出已经是最大老板

select * ,‘已经是最大老板了‘ 
from employee
where eId = 0
union -- 联合改变的是行
select E1.*,E2.ename ‘老板名字‘
from employee E1,employee E2 
where E1.boss = E.eid 

注意：

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

identity

定义

表示该字段的值会自动更新，不需要我们维护，通常情况下我们不可以直接给identity修饰的字符赋值，否则编译时会报错。

语法格式

• identity[(m, n)]
  
  • m表示的是初始值，n表示的是每次自动增加的值
  • 要么同时指定m和n的值，要么m和n的值都不指定，不能只写其中一个值
  • 如果m和n的值都没有指定的话，则去默认值（1，1）
• 数据类型是整型的列才能被定义成表示列
  
  • int，bigint,smallint列都可以被定义成identity
  • 不含有小数位的decimal和numeric也可以被标记为identity
    
    • 如decimal，decimal(6,0)字段都可以被定义为identity，但是decimal(6,2)字段就不可以被定义为identity
• 标识列通常于primary key约束一起用作表的唯一行标识符
  
  • 非主键也是可以被定义为identity的，但是不推荐

如何向identity字段插入数据示例

• 用户如何手动给被identity修饰的主键赋值
  
  • 不重要
    -用户删除数据又插入数据会导致主键不连续递增，怎么办？
  • 不重要
  • 主键即便不连续递增，也是可以的

怎样学习一个知识？

• 对于一个知识A
  
  • 为什么需要A
  • 什么是A
  • 怎么使用A【最简单的】
  • 使用A的注意的问题
  • A的应用领域
  • A的优缺点

视图

为什么需要视图？

• 简化查询
  
  • 避免了代码的荣誉
  • 避免了大量重复的sql语句

什么是视图？

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

视图的格式

create view 视图的名字
as
– select的前面不能添加begin
select语句
– select的后面不能添加end

视图的优点

• 简化查询
• 增加视图的保密性

视图的缺点

• 增加了数据库维护的成本
• 在2005中删除主表并不会删除视图
• 视图只是简化了查询，但是不能加快查询的速度，这也是视图不足的地方

事务【重要】

定义

事务主要用来保证数据的合理性和并发处理的能力

• 一系列操作要么全部执行成功，要么全部执行失败

通俗的说

• 事物可以保证避免数据处于一中不合理的中间状态
• 利用事物可以实现多个用户对共享资源的同时访问

事务是用来研究什么的

• 避免数据处于一中不合理的中间状态
• 怎么避免多用户同时访问的时候呈现给用户的数据的时候的合理性？

事物和线程的关系

• 事物也是通过锁来解决很多问题的
• 线程同步就是通过锁来解决的 synchronized

事物和第三方插件的关系

• 直接使用事物库技术难度很大，很多人是借助第三方插件去实现的
  
  • 因此我们一帮人是不需要细细的研究数据库中事物的语法细节的
• 第三方插件药箱完成预期的功能，一般必须的借助数据库中的事物机制

如何创建事务

• 开始事物
  
  • begin transaction
• 结束事务
  
  • commit transaction
• 回滚（撤销事务）
  
  • rollback transaction

一旦事物提交或者回滚，则事务结束

• 判断某条语句执行是否出错
  
  • 使用全局变量@@ERROR
  • @ERROE只能判断当前的一条T-SQL语句执行是否有错，为了判断事务中所有T-SQL语句是否有错，我们需要对错误进行累计
    
    • 如：SET @errorSUM=@errorSUM+@@ERROR

总结事务的四大特性

事物必须具备以下四个属性，简称为ACID属性

• 原子性：事物是一个完整的操作。事物的各部操作是不可分的（原子性）；要么都执行，要么不执行。
• 一致性：当事物完成是，数据必须处于一致的状态，要么处于开始状态，要么处于结束状态，不允许出现中间状态
• 隔离性：指当前事物与其他未完成的事物是隔离的。在不同的隔离级别下，事物的读取操作，可以得到的结果是不同的。
• 持久性：事物完成后，它对数据库的修改被永久保持，事物日志能够保持事物的永久性

索引

存储过程

游标

TL_SQL

分页查询

显示工资最高的三个员工的信息

select top 3 * 
from emp
order by sal desc

显示工资第4到6个的三个员工信息

select top 3 * 
from emp 
where empno not in (
    select top 3 empno
    from emp
    order by sal desc 
)
order by sal desc

显示工资第7到9个的三个员工信息

select top 3 * 
from emp 
where empno not in (
    select top 6 empno
    from emp
    order by sal desc 
)
order by sal desc

总结

假设每页显示n条记录，当前要显示的是第m页，表名是A，主键是AId

select top n * 
from A 
where AId not in (
    select top (m-1)*n AId
    from emp
)

附录

说实在，郝斌老师算是我程序猿道路上的启蒙恩师，老师讲的课都很好，在这里，附上老师的视频地址：

视频地址

郝斌SqlServer_2005自学视频_全集

Sql Service笔记

标签：共享资源   block   err   outer   找不到   lint   net   设置   推荐