数据库自我快速学习笔记

工具： 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是面向集合的，查询出来的是多个记录，不利于处理，于是就设计一个”游标”，它指向结果集中的某一记录，这样人们 就可以对这条记录进行处理，然后再移动一下游标，使之指向下一条记录，再进行处理，，，就这样逐条地就可以把结果集处理完（循环）