---------------------------------------------表结构如下---------------------------------------------
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