SQLServer 常用查询语句(根据B站 郝斌老师教程整理)

---------------------------------------------表结构如下---------------------------------------------

select * from emp;
select * from dept;
select * from SALGRADE;

-- 一、查询
-- 1、计算列
    select ename , sal*12 as "年薪",sal "月薪"  from emp;
    --*表示所有
    --from emp 表示从emp中查询
    --as 可以省略 "年薪"双引号 别名->"" 
    -- , 分隔
    select 5 from emp
    --ok  输出的行数是emp表的行数 每行只有一个字段 值是5

--2、distinct 去重
    select distinct deptno from emp ;--distinct 过滤重复的 包括null
    select distinct comm from emp;
    select distinct comm, deptno from emp ;--ok 把comm和deptno的组合进行过滤
    select  comm, distinct deptno from emp ;--error 逻辑上冲突 14条对3条

--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 <  1500 and sal >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);
    select * from emp where sal<>1500 and sal<>3000 and sal<>5000;
    --数据库中不等于有两种表示  !=  <>推荐第二种

--5、top 前面的几个
    select top 2 * from emp;
    select top 15 percent * from emp;
    select top 4 * from emp 
        where sal between 1500 and 3000
        order by sal desc ;--desc 降序 不写默认升序

--6、null 没有值
    select * from emp where comm <>null;  --输出为空 error
    select * from emp where comm != null; --输出为空 error
    select * from emp where comm  = null; --输出为空 error
    --null 不能参与<> != = 运算 可参与 is、 is not
    select * from emp where comm is null;
    select * from emp where comm is not null;

    --输出每个员工 姓名、年薪(包含奖金) 
    select ename, sal*12+comm as "年薪" from emp ;--error
    --null 不能参与任何数学运算 否则为null
    select ename, sal*12+isnull(comm, 0)  as "年薪" from emp; --正确写法
    --isnull(comm,0) 如果comm是null 就返回零否则返回comm的值

--7、order by  以某个字段排序
    select * from emp order by sal; --默认是按照升序排序的
    select * from emp order by deptno, sal; -- order by deptno, sal 是个整体 先按照deptno排序 在按照sel排序
    select * from emp order by deptno desc, sal;
    --先deptno降序排序 如果deptno相同 再按照sal升序排序
    --order by a desc ,b,c,d \ desc只对a有影响
    --升序 asc 默认不写 建议为每一个字段指定

--8、模糊查询
    --格式 select 字段的集合 from 表名 where 某个字段的名字 like 匹配的条件
    --通配符 ：
    -- % 表示任意0个或者多个字符
    select * from emp where ename like '%A%' ;--包含A的字符串
    select * from emp where ename like 'A%' ; --首字母是A的字符串
    select * from emp where ename like '%A';  --尾字母是A的字符串
    -- _[下划线] 表示任意单个字符 
    select * from emp where ename like '_A%' ; --第二个字母是A的字符串
    --[a-f] a-f中的任意单个字符
    select * from emp where ename like '_[A_C]%;' --第二个字符是a或b或c的字符串
    --[a,f] a或f
    select * from emp where ename like '_[A,C]%';
    --[^a-c] 不是a或b或c 异或
    select * from emp where ename like '_[^a-c]%';
    --匹配条件必须用单引号   ''->字符串  ""->对象的名称 别名
    --ename中含有%  escape 转义字符 把'\'当作转义字符
    select * from emp where ename like '%\%%' escape '\'; --name中有%的输出
    select * from emp where ename like '%\_%' escape '\'; --name中有_的输出

--9、聚合函数 通常用来统计分组后的信息
    --单行函数：每一行返回一个值
    select lower(ename) from emp; --每一行返回一个值 输出14行
    --多行函数：多行返回一个值
    select max(comm) from emp;   --14行只 输出一行

    --分类
    --max()
    --min()
    --avg() 平均值
    --count(*)      所有记录的个数
        --conut(字段名) 统计非空的个数
        --conut(distinct 字段名) 统计不重复字段非空的个数
    select count(*) from emp; --返回emp表所有记录的个数
    select count(deptno) from emp; --返回14 deptno重复的记录也被当做有效的记录
    select count(distinct deptno) from emp; --返回3个
    select count(comm) from emp; --返回4 说明comm为null的记录不会被当作有效值
    --单行函数和多行函数不能共用
    select max(sal)"最高工资", min(sal)"最低工资", count(*)"员工人数" from emp;--ok 
    select max(sal), lower(ename) from emp; --error 一个值 14个值 逻辑冲突

--10、group by 分组
    --输出每个部门的编号 和 该部门的平均工资
    select deptno, avg(sal) as "部门平均工资"
        from emp
        group by deptno;
    --group by 后 select中 只能出现分组后的整体信息，不能出现组内的详细信息

    select deptno, job, avg(sal) as "小组平均工资", count(*) as "小组人数", max(sal) as "最高工资"
        from emp
        group by deptno,job
        order by deptno;

--11、having 对分组之后的信息进行过滤
    --(1)因此使用having时通常都先使用group by
    --(2)如果没有使用group by,而使用了where，则having 把所有记录当成一组 
    --(3)having子句出现的字段必须时分组之后的组的整体信息
    -----having子句不允许出现组内的详细信息
    --(4)尽管select字段可以出现别名  但是having子句不能出现别名
    -----只能使用字段最原始的名字
    --(5)having和where的异同
    --相同的：
    --都是对数据过滤，只保留有效的数据,都不允许出现别名
    --不同的：
    --where是对原始的记录过滤，having是对分组之后的记录过滤
    --where必须写在having的前面，顺序不可颠倒
    select ename, sal as "工资"
        from emp
        where "工资">2000

    --输出部门平均工资大于2000的部门的部门编号 部门的平均工资
    select deptno, avg(sal) as "平均工资"
        from emp
        group by deptno
        having avg(sal) >2000;--ok
        
    select deptno, avg(sal) as "平均工资"
        from emp
        group by deptno
        having deptno >10;--ok

    select deptno, avg(sal) as "平均工资"
        from emp
        group by deptno
        having "平均工资" >2000;--error having不能对别名进行分组

    select deptno, avg(sal) as "平均工资"
        from emp
        group by deptno
        having count(*) >1;--ok

    --把名字不包含A的所有的员工按照部门编号分组
    select deptno, avg(sal) "平均工资",count(*) "部门人数",max(sal) "部门最高工资"
        from emp
        where sal>2000
        group by deptno
        having avg(sal) >3000

    --参数的顺序
    --select 参数的顺序是不允许变化的 

--12、连接查询
    --定义 ： 将两个或者两个以上的表以一定的连接条件连接起来
    --        从中检索出满足条件的数据
    --分类
    --（1）内连接
    --1）select 。。。 from A,B 的用法
    --产生结果：行数是AB的乘积、列数是AB之和 
    --或者说 把A表的每一条记录和B表的每一条记录组合在一起
    --emp 14行8列  dept 5行3列 笛卡尔积 14*5 8+5=》70行11列 临时表
    select * from emp, dept 
    --2）select 。。。 from A,B where 。。。 的用法
    --对产生的笛卡尔积用where中的条件进行过滤
    select * from emp, dept  where empno= 7369--5行 11列 
    --3）select 。。。from A join B on 。。。的用法
    select "E".ename as "员工姓名", "D".dname as "部门名称"
        from emp "E"
        join dept "D" --join 是连接
        on "E".deptno = "D".deptno --on是连接条件 
    select "E".ename as "员工姓名", "D".dname as "部门名称"
        from emp "E"
        join dept "D"  
        on 1=1--70行 2列
    select deptno 
        from  emp "E"
        join dept "D"
        on 1=1 --error 列名 'deptno' 不明确。
    --4）SQL92 标准和SQL99标准的区别
    select * from emp, dept where emp.deptno = dept.deptno -- 92
    select * from emp join dept on emp.deptno = dept.deptno --99
    --推荐99 
    --on 指定连接条件
    --where 对连接之后的临时表的数据进行过滤 

    --把工资大于2000的员工的姓名和部门名称进行输出
    select "E".ename, "D".dname
        from emp "E", dept "D"
        where "E".sal > 2000 and "E".deptno = "D".deptno

    select "E".ename, "D".dname
        from emp "E"
        join dept"D"
        on "E".deptno = "D".deptno
        where "E".sal>2000
    --5）
    select *
        from emp "E"
        join dept "D"
        on 1=1
        join SALGRADE "S"
        on 1=1

    --把工资大于2000的员工的姓名和部门名称 和工资登记 进行输出
    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;
    --6）练习
    --求出每个员工的姓名 部门编号 薪水 和薪水等级
    select "E".ename "员工姓名", "E".deptno "部门名称","E".sal "薪水","S".GRADE "薪水等级"
        from emp "E"
        join SALGRADE "S"
        on "E".sal >= "S".LOSAL and "E".sal<="S".HISAL; 
    --查找每个部门的编号 该部门所有员工的平均工资 平均工资的等级
    select "T".deptno,"T"."avg_sal" "部门平均工资","S".GRADE "工资等级"
        from (
            select "E".deptno, avg("E".sal) "avg_sal"
                from emp "E"
                group by "E".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 "E".deptno, avg("E".sal) "avg_sal"
                from emp "E"
                group by "E".deptno
        )"T"
        on "T"."avg_sal" between "S".LOSAL and "S".HISAL;

    select "T".deptno, "T"."avg_sal" "部门平均工资", "S".GRADE "工资等级"
        from SALGRADE "S" ,(
                            select deptno ,AVG(sal) "avg_sal"
                                from emp
                                group by deptno
                            )"T"
        where "T"."avg_sal" between "S".LOSAL and "S".HISAL;
    --查找每个部门的编号 部门名称 该部门所有员工的平均工资 平均工资的等级
    select "T".deptno "部门编号","D".dname "部门名称","T"."avg_sal" "部门平均工资","S".GRADE "工资等级"
        from (
            select "E".deptno, avg("E".sal) "avg_sal"
                from emp "E"
                group by "E".deptno
        ) "T"
        join SALGRADE "S"
        on "T"."avg_sal" between "S".LOSAL and "S".HISAL
        join dept "D"
        on "T".deptno = "D".deptno
    --求出emp表中所有领导的姓名 
    select * from emp 
        where EMPNO in (select mgr from emp);
    --求出emp表中所非有领导的姓名 in 与 null的组合 带来的问题
    select * from emp 
        where EMPNO not in (select mgr from emp); --error
    select * from emp 
        where EMPNO not in (select mgr from emp where mgr is not null); 
    --求出平均薪水最高的部门的编号和部门的平均工资 先分组在排序
    select top 1 deptno "部门编号", avg(sal) "平均工资"
        from emp 
        group by deptno
        order by avg(sal) desc        
    select "T".deptno "部门编号", "T".avg_sal "平均工资"
        from (select deptno, AVG(sal) "avg_sal" from emp group by deptno) "T"
        where "T"."avg_sal" = 
        (select MAX("E".avg_sal) from (select deptno, AVG(sal) "avg_sal" from emp group by deptno)"E")
    --把工资大于所有员工中工资最低的人中(排除工资最低，剩下的人中工资最低的3个人的。。。)
    -- 前3个人的姓名、工资、部门编号、部门名称、工资等级输出
    select top 3 "E".ename,"E".sal,"E".deptno, "D".dname,"S".GRADE
        from (
                select *
                    from emp "E"
                    where sal > (select min(sal) from emp)
        ) "E"--与()里的“E”没有冲突 因为作用域不一样
        join dept "D"
        on "E".deptno = "D".deptno
        join SALGRADE "S"
        on "E".sal between "S".LOSAL and "S".HISAL
        order by "E".sal

    --查询顺序:
     select top ...
        from A
        join B
        on ...
        join C
        on ...
        where ...
        group by ...
        having ...
        order by..;

    --（2）外连接
    --用左表的第一行分别和右表的所有行进行联接、
    --如果有匹配的行，则一起输出，如果右表有多行匹配，则结果集输出多行
    --如果没有匹配行，则结果集中只输出一行，该输出行左边为左表第一行内容，右边全部输出null
    select * from emp E
        left join dept "D"
        on E.deptno = "D".deptno

    select * from dept "D"
        left join emp "E"
        on E.deptno = "D".deptno

    --（3）完全连接和交叉连接
    --两个表中匹配的所有行记录
    --左表中那些在右表中找不到的匹配行的记录，这些记录的右边全为null
    --右表中那些在左表中找不到的匹配行的记录，这些记录的左边全为null
    select * from dept "D"
        full join emp "E"
        on D.deptno = E.deptno

    select * from emp cross join dept --笛卡尔积
    等价于
    select * from emp, dept

    --（4）自链接
    select  E1.EMPNO
        from emp "E1"
        join emp "E2"
        on E1.sal < E2.sal
    --用聚合函数 求薪水最高的员工的信息
    select * from emp where sal =(select max(sal) from emp)
    --不使用聚合函数
    select * from emp
        where empno not in (
            select distinct E1.EMPNO
                from emp "E1"
                join emp "E2"
                on E1.sal < E2.sal 
        )

    --（5）联合链接
    --纵向的连接表中的数据、或者表和表之间的数据以纵向的方式连接在一起
    --注意：之前的所有连接是以横向的方式连接在一起的
    --输出每个员工的姓名、工资、上司的名字
    select E1.ename,E1.sal,E2.ename
        from emp "E1"
        join emp "E2"
        on E1.mgr = E2.EMPNO
    union --联合 纵向
    select ename, sal,'boss' from emp where mgr is null
    --注意：若干个select子句联合成功的话，必须满足的两个条件
    --1、这若干个select子句输出的列数必须是相等的
    --2、这若干个select子句输出列的数据类型至少是相同的

--13、分页查询
    -- 14行 分页 每页 3行
    --输出工资最高的前三个员工的所有信息
        select top 3 * from emp order by sal desc --从输出结果看 先执行order by 后执行top
    --工资从低到高 第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
    --工资从低到高 第10-12的员工信息
        select top 3 * from emp
            where EMPNO not in (select top 9 EMPNO from emp order by sal desc)
            order by sal desc
    --工资从低到高 第13-14的员工信息
        select top 3 * from emp
            where EMPNO not in (select top 12 EMPNO from emp order by sal desc)
            order by sal desc

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

 
--identity 主键的自动增长，用户不是需要为indentity修饰的主键赋值
    create table student
    (
        student_id int primary key identity,
        student_name nvarchar(200) not null
    )

    insert into student values(1, '张大');
    insert into student values(2, '张二');
    insert into student(student_name) values('张三')
    insert into student values( '张四'); --ok？    
    delete from student where student_name='张大';
    insert into student(student_name) values('张五')--主键不连续增长

    --'张三' checkident('student',reseed ,0)

    --identity(100,5)
    --表中删除数据又插入数据会导致主键不连续递增 怎么办？
    --主键是否连续增长不是十分重要

A（why-what-how）
1、为什么需要A
2、什么是A
3、怎么使用A
4、使用A时注意的问题
5、A的应用领域
6、A的优缺点

--视图
    --求出平均工资最高的部门的编号的部门的平均工资
    select * 
        from (
            select deptno, avg(sal) "avg_sal"
            from emp
            group by deptno) "T"
        where "T"."avg_sal" =(
            select max("E"."avg_sal") from(
            select deptno, avg(sal) "avg_sal"
            from emp
            group by deptno)"E")
        

        create view v$_emp_1
        as
            select deptno, avg(sal) "avg_sal"
                from emp
                group by deptno
        select * from v$_emp_1 where avg_sal = (select max(avg_sal) from v$_emp_1)
    --总结 简化查询 避免代码冗余
    --视图代码上看是个select语句
    --逻辑上是一张虚拟表
    create view 视图的名字
    as
        select语句
        -- select前面不能添加 begin
        -- select后面不能添加 end
    --优点 1、简化查询 2、增加数据的保密性
    --缺点 1、增加数据库维护得成本
       --  2、只是简化查询 并不能加快查询的速度
    --注意：
    --创建视图的select语句必须的为所有的计算列指定别名
    create view v$_a
    as 
        select avg(sal) "avg_sal" from emp;
    --视图不是物理表 是虚拟表
    --不建议通过视图更新视图所依附的原始表的数据或结果

--事务
    --事务主要用来保证数据的合理性和并发处理的能力
    --事务可以保证数据处于一种不合理的中间状态
    --利用事务可以实现多个用户对共享资源的同时访问

    --一系列操作要么全部执行成功 要么全部执行失败 这就是事务
    --开始事务 begin transaction
    --提交事务 commit transaction
    --回滚事务 rollback transaction
    --事务的特性
    --原子性：事务是一个完整的操作，事务的各步操作是不可分的 要么成功 要么失败
    --一致性：当事务完成时，数据必须处于一致状态，要么处于开始状态，要么结束状态
    --隔离性：指当前的事务与其他未完成的事务是隔离的
    --持久性：事务完成后，对数据库的修改被永久保持
    create table bank
    (
        customerEname nvarchar(200),
        currentMoney money
    )

    insert into bank values('张三', 1000)
    insert into bank values('李四',1)

    update bank set currentMoney = currentMoney -1000 where customerEname = '张三'
    update bank set currentMoney = currentMoney +1000 where customerEname = '李四'

    begin transaction
    declare @errorSum int
    set @errorSum = 0
    update bank set currentMoney = currentMoney -1000 where customerEname = '张三'
    set @errorSum = @errorSum+@@ERROR
    update bank set currentMoney = currentMoney +1000 where customerEname = '李四'
    set @errorSum = @errorSum+@@ERROR
    if(@errorSum <> 0)
        begin 
            print '转账失败！'
            rollback transaction
        end
    else 
        begin
            print '转账成功！'
            commit transaction
        end