工具: 1、sql server 2012 标准版
2、数据库示例:Scott(有三个表,员工表,部门表,工资等级表)
下载地址:http://download.csdn.net/detail/ahutahut/4201942
1、三个模式:外模式、模式、内模式
外模式对外(用户):比如表中只有“部分”的数据是可以用户查看的,通过外模式就可以限制用户可以查看或操作哪些部门,其它部分就“看不见了”。
内模式对内:对内就是对于物理存储,把设计的库通过“怎么样进行组织存储“到外设(硬盘)。一个数据库只有一个内模式,多个外模式(多用户各自权限不同)。
模式:在外模式与内模式之间的这个模式,它可以查看所有数据(表)及结构,通过管理员来创建修改删除它,并限制哪些用户来查看哪些部分(即外模式)
连接是外模式。SQL2012既是DBMS也是用户。
外模式通过外模式映像把数据提供给用户,内模式通过内模式映像把管理员定义的结构等存储到外设中去。
2、主键表、外键表:它们是相对的。
表A中的某些字段来自于另外某些表(如表B),那么表A就称主键表,表B称就外键表。
表A依赖表B,表B关联表A。一张表在作主键表时,也可能是外键表。
关系模型的完整性:
1、实体完整性:主键不能为空
2、参照完整性:表A中某字段来自于表B中的主键 ,则A表中这个字段必须在B表中”存在“
即表A中这字段依赖于表B,只能是Null或非空值,不能是不存在这个记录。
3、用户定义完整性:用户自己定义的合理值。
如表A中”学生年龄“,它必须在合理范围,不能说是1000,有1000岁的人么?神仙!
又如表A中”学生性别“,只能是”男“或”女“,有第三个值么?人妖:)
3、约束:
主键约束:不允许重复
外键约束:必须是存在的
check约束:取值合法
default约束:字段一定有一个值(默认值)
唯一约束:不允许重复 unique ,与主键约束区别:可允许为空。SQL只允许一个为空,Oracle允许多个为空
Not Null:不空
Null: 可为空。与default区别:两者可不赋值,但default不赋值时会用默认值代替,Null则默认是空。
4、关系
关系用外键来实现。在一对多关系中,一表为外键表,多表为主键表。
5、查询
列的运算: select empno,sal*12 as "年薪" from emp;
1、 为了和Orcale兼容,别名用双引号(SQL可用单引号)
2、一个语句后,可以加分号以示结束。
3、关键字 用户 密码 字段名 表名 序列名 触发器名等是不区分大小写。
4、as可以省略
5、执行顺序,先进“表”,从表中所有记录中选择对应列,若有计算再计算,最后以别名输出。
select ename,* from emp; --注意,SQL中是正确的,在Oracle中是错误的。(以后SQL指SQL server)
--并且SQL增加了一行,原来是8字段,成了9字段(多了一个ename)
distinct 区分的,不可重复的
select distinct deptno from emp; --表示选出不可重复的部门号。
两个减号,表示注释
select distinct comm,deptno from emp; --选择两列的“组合”不是重复的,注意,不是第一个,是组合!!
select comm,distinct deptno from emp; --错误,没这种写法,只能写在开始!!!!
注意:distinct是单纯的进行比较值,若有多个NULL,则只会选择出“一个“!
between 之间,范围内
select * from emp where sal>=3000 and sal<=1500;
select * from emp where sal between 3000 and 1500;--两句等效
表emp中对于sal在3000和1500之间的所有记录中,选择出来后,再按所有字段进行列出。
select * from emp where sal<3000 or sal>1500;
select * from emp where sal not between 3000 and 1500; --小于3000或者大于1500的所有记录。
in 在各孤立的值中选取。
select * from emp where sal in(3000,5000); --只在3000和5000两个值中选取,与between是不同的。
select * from emp where sal not in (3000,5000); --不在3000和5000中进行选取。
可以看到not 可以直接在between和in前面放置。
后一名相当于:
select * from emp where sal<>3000 and sal<>5000; --不等于可以用!=和<>,推荐用<>
top 前几项
select top 5 * from emp; -- 查询结果的前5项
select top 15 percent * from emp; --查询结果的前15%项,总项14,14X15%=2.1,
直接进位为3,故显示前3项。
注意:SQL用top,Oracle 用rownum来指定(但只能用小于号)
如:select * from emp where rownum<5; --Oracle中结果的前4项
注意分页机制,因Oracle若按分页的话,每页的前4项结果不一样。
一定把执行顺序理解:
select top 5 * --4、结果中的前5项
from emp --1、表emp所有记录
where sal between 800 and 3000 --2、所有记录中sal在800与3000间的
order by sal desc; --3、过滤后记录排序(降序),默认是升序
故:意思是表中工资800与3000间前5项工资最高的。
NULL(空值)
select * from emp where comm<>NULL; --NULL参与比较将为空,所以结果是没有一个记录(空)
select empno, sal*12+comm from emp; --NULL参与运算结果将为空,利用这一特点,可将一列置NULL
null只能用is,not is来进行比较
select * from emp where comm is Null;
order by(排序) 默认为升序asc, 降序为desc
select * from emp order by ename,sal desc; --各字段的排序互不影响。先对指定的第一个字段排序,
相同时再对第二字段按指定顺序排序。
模糊查询:like 类似
select * from emp where ename like '_A_[B-G]_[^C-E]%' ;
通配符含义:
% 0个或多个字符
_ 1个任意字符
[B-F] B到F五个字符中任意一个
[^B-F] 不在B到F五个字符中的任意一个字符
注意:因为上面几个特殊字符被占用了,但有时字段中含有这些字符,于是用到了“转义符”(类似C/C++)
escap '\' 指定转义符为\。转义符可根据自己指定,如:escap 'm' 这时m就成了转义符
select * from emp where ename like '%\%%' escap '\' ; --含有%号的,经转义符后\%就变成了普通字符%
三个关键:
1、对象或字段别名用双引号,字段名或通配会用单引号(请在日常中验证)
2、from前用双引号,from后用单引号(今后中验证)
3、not这个护身符,同样可以用like的前面。
聚合函数:
函数分类:
单行函数:每一行返回一个值;(每个记录可看作一个单选函数:)
多行函数:多行返回一个值。
聚合函数是多行函数
select lower(ename) from emp; --单行函数,每行的ename小写。lower()小写,upper()大写
select max(sal) from emp; --多行函数(聚合函数),返回多行的最大一个。max()最大,min()最小
聚合函数如:max(),min(),avg(),count()
count()求个数
count(*) 返回表中记录个数
count(字段)返回字段值非空的记录个数,重复记录将被统计
count(distinct 字段) 返回字段值非空的记录个数,不统计重复个数
显然,如果是空,肯定不存在,当然不在count的统计范围内。同时distinct限制了重复。
注意:单行函数与聚合函数在一起会出错
select max(ename),min(sal),count(*) from emp;--正确,全为聚合函数
select max(ename),upper(mgr) from emp; --错误,单行与聚合在一起了,
group by 分组: 关键点,只能出现分组的组信息,而不是每个记录的信息。
select deptno,sum(sal) from emp group by deptno; --按部门分组进行统计每组的总工资
select deptno,job,avg(sal) from emp group by deptno,job; --按部门分组,部门相同时再按职称分组,
再统计最小组的平均工资。
注意:1、group by a,b,c 先按a分组,a相同时再按b分组,b相同时再按c分组,类似于order by的排序安排
2、最后统计的肯定是最小的组,即c组。所以聚合函数只能是最小组的统计。
3、本例与“单行函数与聚合函数在一起会出错”不冲突,本质上group by使用的组信息
(不能出现每记录信息),即出现的是聚合函数,但特殊情况时,对于group by后面的字段,
可以出现在select后面,除此外其它字段不能出现。
select deptno,max(sal),ename from emp group by deptno;--错误,deptno外的单行字段ename不能出现。
所以会提示:选择列表中的列 'emp.ename' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
说明单行与聚合出现在一起时,它必须出现在group by中,否则出错。
总结:除了聚合函数外,其它单行字段只能是group by中出现的字段,否则出错!
select deptno,job,mgr from emp group by deptno,job; --出错,mgr字段没有出现在group by deptno,job中。
having 分组信息的过滤
一般配合group by使用,对组信息进行过滤。
select deptno,avg(sal) --5、最后的结果按部门编号,平均工资输出
from emp --1、首先在emp表中选择所有记录
where ename not like '%A%' --2、然后选择那些不含A的原始记录
group by deptno --3、对过滤的原始记录进行分组
having avg(sal)>2000 --4、分组后,组信息再过滤
注意:1、必须注意上面的执行顺序进行理解,所以having同where一样是不能用别名的(因别名在最后select中定义)
由此还可推出:where中不能用聚合函数,因为它是对原始记录过滤,分组是在它之后。
2、having是组信息的过滤,针对group而言。有两个含义
一是having中只能出现聚合函数或者是group by出现的单行记录(函数),否则出错。
二是无group by时,则把每一个记录看作是一个组,等同于where(当然还是可以用聚合函数),只不过
要注意“单行与聚合函数是不能同时出现在select"这个原则将生效进行查错。
3、where与having的异同
同:都是对数据的过滤,都不能用别名(不用记,就上面的顺序原则判断)
异:where是对原记录(每条)进行过滤,having是对组信息进行过滤
当然按上面顺序原则,只能先where再having,都两者位置不能乱放。
6、查询:连接查询(难点)
连接查询:两个或以上,多表连接起来进行查询,选出满足条件的数据。
内连接:
select * from emp ”E“,dept "D"; --E表14行,D表5行。结果是70行,由E表和D表的笛卡尔积组成
即,行是两表的积,14X5=70,E表一条记录与D表每条记录组成5条,共14个去组合即70行,反之思考亦然。
列是两表的和,即8+3=11,E表8列与D表3列,之和组成 一个11列,70行的临时表。
select * --4、过滤后的结果进行输出
from emp "E" --1、表E的所有记录
join dept "D" --2、连接到D表,形成笛卡尔积
on E.deptno=D.deptno --3、连接条件,进行过滤
此表等效于:select * form emp "E",dept "D" where E.deptno=D.deptno;--SQl92标准
前面join on是99标准,推荐用99标准,这样更规范阅读清晰
注意: select * from emp join dept on 1=1; --过滤条件1=1永远为真,相当于没有过滤,与前面的笛卡尔积等效。
总结:99标准更规范:where只针对最后结果,join on针对表的连接。
输出工资大于2000的员工姓名、部门名称及工资等级
select E.ename,D.dname,S.grade --7、输出结果
from emp "E" --1、E表所有记录
join dept "D" --2、连接到D表(笛卡乐积)
on E.deptno=D.deptno --3、连接条件进行过滤(结果14行)
join salgrade "S" --4、上面的临时表再连接到表S(笛卡积)
on E.sal>=S.losal and E.sal<=S.hisal --5、连接条件进行过滤,最终结果:三表连接在一起,14行
where E.sal>2000 --6、对所有记录过滤
外连接:
说白了,内连接就是两个集合的交集,表A和表B中某些值相等的情况,即:
select * from emp join dept on 1=1;
这个查询实际上就是内连接,只是省略了inner,原型如下
select * from emp inner join dept on 1=1; --可以看出内连接是原两表的交集
外连接:分为左连接、右连接、全连接
前面说过笛卡尔积,那就是交叉连接,可以看出交叉连接是没有比较条件(where)的,记录是两表列之积。
交叉连接与内连接、外连接合称三大连接
仔细看一下:
左连接:(left join或left out join ,out常常 省略)
select * from emp "E" left out join dept "D" on E.deptno=D.deptno;
关键:左连接就是表E的第一条记录根据on条件,分别去和表D每条记录进行连接。
只要符合条件就组成一条记录,如果有两条符合条件就组成两条记录,以此类推。。。
如果最终在表D中没有一条符合,也会组成一条记录,只不过后面的就字段全为NULL
然后,表E的第二条记录再根据on去和表D每条记录去连接,按上面方法进行比较,组成记录。
就这样,表E中每条记录逐个与表D的每一条记录去比较连接。
最终,因为表E中每条记录至少会有一条和表D组成一个记录(因为最差就是NULL)
故,最后的临时结果集至少应该是大于或等于表E的记录个数,即大于或等于14。
如果上面的连接条件on改为:
select * from emp "E" left out join dept "D" on 1=1;
就是笛卡尔积了,也就是“交叉连接"了,故结果记录数就是70,等效于:
select * from emp,dept;
右连接:
select * from emp "E" right out join dept "D" on E.deptno=D.deptno;
关键:与左连接类似,只不过是表D的第一条与表E的每一条根据条件进行连接。
同样的道理:表D是记录是5,表E记录是14,故最终的结果应该是大于或等于表D
同样:条件on改为on 2=2(即永远为真,相当于没有过滤条件),也是交叉连接。
全连接:
select * from emp "E" full out join dept "D" on E.deptno=D.deptno;
即左连接与右连接结果的并集,就是全连接。
7、事务: 又称作业
通俗地说,就是批处理过程,只不过比批处理更严谨,它要求:要么全部成功,否则全部失败。不能成功一半,失败一半。 比如,通过银行给小三转帐5000元,银行会对我的帐户减5000,对小三的帐户加5000.。这个过程只能是全部成功或全部失败。
如果中间出了问题,比如我减5000成功,小三没有加上5000,那小三还会问我要,
如果我减5000失败,结果小三加上5000成功,我倒乐意和小三做生意 ,把银行掏空。
语法格式:begin transaction --开始
。。。。。。 --要执行的一些操作
commit transaction --提交事件(全部成功时)
rollback transaction --恢复 (若有失败,全部恢复)
因此,事务具有:
1、原子性:整个事务不能拆开执行
2、一致性:要么全部成功,要么全部失败
3、隔离性:事务与事务互不侵犯主权,不相互干扰
4、持久性:修改的结果永久保存,包括日志。要不你骗了银行,银行还傻瓜的不知道?
8、存储过程
实际上它也是批处理过程,就是一系列的SQL语句进行集中执行。与事务类似
只不过事务要求更严格,相当于存储过程就是普通的陆军,而存储过程也是军队,只不过是特种部队,要求更严格些。
9、游标:能够指向某个位置的标志。
由于select是面向集合的,查询出来的是多个记录,不利于处理,于是就设计一个”游标”,它指向结果集中的某一记录,这样人们 就可以对这条记录进行处理,然后再移动一下游标,使之指向下一条记录,再进行处理,,,就这样逐条地就可以把结果集处理完(循环)