学习SQL语句的经验与体会

最近在开发项目的时候, 发现应对与多张表格的查询,使用sql语句的拼接来实现多张表格的拼接查询,更加的方便与有意思.   同时基于hibernate的数据关联性的设计 也是一个非常重要的部分.在这里暂时还不是特别的熟悉,也不是很会使用.

 

现在开始进行SQL拼装语句的学习.

1,首先 SQL语言 有四个作用,   数据查询,数据定义,数据操纵, 数据控制.

2,每个作用分别对应一个特点.SQl有 嵌入式的 和 交互式(直接敲命令行). 下图表示的是sql语言的几个基本操作.

 

 

3,基本表和视图的概念

 

  

Ø 基本表 （ BASETABLE ）：是独立存在的表，不是由其它的表导出的表。一个关系对应一个基本表，一个或多个基本表对应一个存储文件。

 

Ø 视图（ VIEW ） ：是一个虚拟的表，是从一个或几个基本表导出的表。它本身不独立存在于数据库中，数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中。当基本表中的数据发生变化时，从视图中查询出来的数据也随之改变。

 

    还有 两个基本概念  ,分别是索引和数据库.

 

4,  SQL语言的数据定义功能.

     其中 视图 和 索引 是不可以 修改的.只能创建和删除.

     数据类型有很多种, 各种各样的.

     标记数据类型：有UNIQUEIDENTIFIER，TIMESTAMP两种，此数据类型通常系统自动产生，而不是用户输入

     的，TIMESTAMP记录数据更新的时间戳印，而UNIQUEIDENTIFIER用来识别每一笔数据的唯一性。

 

5,外键约束

  

FOREIGN KEY约束

ØFOREIGN KEY约束指定某一个列或一组列作为外码，其中，包含外码的表称为从表，包含外部键所引用的主键或唯一键的表称主表。

Ø系统保证从表在外码上的取值要么是主表中某一个主码值，要么取空值。以此保证两个表之间的连接，确保了实体的参照完整性。

ØFOREIGN KEY既可用于列约束，也可用于表约束，

Ø其语法格式为：

  [CONSTRAINT <约束名>] FOREIGN KEY  REFERENCES<主表名> (<列名>[{<列名>}])

 

6,

1. 按照索引记录的存放位置可分为聚集索引与非聚集索引

Ø聚集索引：按照索引的字段排列记录，并且依照排好的顺序将记录存储在表中。

Ø非聚集索引：按照索引的字段排列记录，但是排列的结果并不会存储在表中，而是另外存储。

2. 唯一索引的概念

Ø唯一索引表示表中每一个索引值只对应唯一的数据记录，

Ø这与表的PRIMARY KEY的特性类似，因此唯一性索引常用于PRIMARY KEY的字段上，以区别每一笔记录。

Ø当表中有被设置为UNIQUE的字段时，SQL SERVER会自动建立一个非聚集的唯一性索引。

Ø而当表中有PRIMARY KEY的字段时，SQL SERVER会在PRIMARY KEY字段建立一个聚集索引。

3. 复合索引的概念

Ø复合索引是将两个字段或多个字段组合起来建立的索引，而单独的字段允许有重复的值。

 

这些都是索引的概念,索引最主要的用途就是 在进行数据库的查询时,加快查询速度.  在非索引的列上,进行查询是非常恐怖的一件事情.

 

CREATE CLUSTER INDEX TI ON T(TN)

Ø 执行此命令后，为 T 表建立一个索引名为 TI 的聚集索引， T 表中的记录将按照 TN 值的升序存放。

但是索引对于更新较多的表 是一种负担.

索引数目无限制，但索引越多，更新数据的速度越慢。对于仅用

于查询的表可多建索引，对于数据更新频繁的表则应少建索引

7, 对于数据表的查询,   select语句的使用.   查询语句的嵌套非常重要.   是实现五张表格联合查询的核心.

    现在意识到了 数据库设计的必要性,  尤其是在处理各个数据的关联性的时候.   怎样设计数据库,是完全分拆成三范式,还是不分拆,有一些联合.  应对不同特点的数据表格, 更新频繁类型的,   查询频繁类型的. 不怎么更新,但是有可能更新的.   对于历史数据的 记录.   这些问题都应该是数据库设计里面应该体现出来的东西.

 

 首先进行sql语句查询的学习. 

  select 语句的 嵌套查询.

Ø SELECT 语句的一般格式为：

SELECT〈列名〉[{，〈列名〉}]

FROM〈表名或视图名〉[{，〈表名或视图名〉}]

[WHERE〈检索条件〉]

[GROUP BY <列名1>[HAVING <条件表达式>]]

[ORDER BY <列名2>[ASC|DESC]];

 

另一种格式

Ø SELECT 语句的格式：

SELECT  [ALL|DISTINCT][TOPN [PERCENT][WITH TIES]]

列名1 [AS 别名1]

[, 列名2 [ AS别名2]…]

[INTO 新表名]

FROM 表名 1[[AS]表1别名]

[INNER|RIGHT|FULL|OUTER][OUTER]JOIN

    表名2 [[AS]表2别名]

ON 条件

 

SQL语句执行的过程.

Ø查询的结果是仍是一个表。

ØSELECT语句的执行过程是:

Ø根据WHERE子句的检索条件，从FROM子句指定的基本表或视图中选取满足条件的元组，再按照SELECT子句中指定的列，投影得到结果表。

Ø如果有GROUP子句，则将查询结果按照<列名1>相同的值进行分组。

Ø如果GROUP子句后有HAVING短语，则只输出满足HAVING条件的元组。

Ø如果有ORDER子句，查询结果还要按照<列名2>的值进行排序。

 

则个很重要,要深刻的理解.  尤其是 select 出来的结果是一张表.

 

例3.23查询选修了课程的学生号。

  SELECTDISTINCT SNOFROM SC

Ø 查询结果中的重复行被去掉

 

可将 查询结果的其中一列的重复行去掉.  注意 结果表格中 完全相同的行.

 

Ø 另外，利用投影查询可控制列名的顺序，并可通过指定 别名 改变查询结果的列标题的名字。

例3.24查询全体学生的姓名、学号和年龄。

  SELECT SNAME NAME, SNO, AGEFROM S

Ø 其中， NAME 为 SNAME 的别名 

 

 

上图表示了 基本的条件查询的条件.

例3.34查询没有考试成绩的学生的学号和相应的课程号。

SELECT SNO, CNO

FROM SC

WHERE SCORE ISNULL

Ø 注意：这里的空值条件为 IS NULL ，不能写成 SCORE=NULL 。

 

并且 in 这个筛选条件 要慎用.   in 与 exist 的效率问题, 同时尽量少用 not.   这个属于 sql语句的效率问题.

下面图中是 sql 提供的库函数. 主要是针对一列. 结果是一个数值.

 

 

例3.35求学号为S1学生的总分和平均分。

SELECT SUM(SCORE) AS TotalScore, AVG(SCORE) ASAveScore

FROM SC

WHERE (SNO = 'S1')

Ø

Ø注意：函数SUM和AVG只能对数值型字段进行计算。

 

 

 

8,  下面开始,最为复杂的 group by的 使用.

        

    继续向下写, having group by   这些关键字,一直都不是很清晰.

 

ØGROUP BY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值。

Ø

例3.42查询各位教师的教师号及其任课的门数。

SELECT TNO,COUNT(*) ASC_NUM

FROM TC

GROUP BY TNO

ØGROUP BY子句按TNO的值分组，所有具有相同TNO的元组为一组，对每一组使用函数COUNT进行计算，统计出各位教师任课的门数。

 

 

Ø若在分组后还要按照一定的条件进行筛选，则需使用HAVING子句。

 

ØWHERE与HAVING子句的根本区别在于作用对象不同。

ØWHERE子句作用于基本表或视图，从中选择满足条件的元组；

ØHAVING子句作用于组，选择满足条件的组，必须用于GROUP BY子句之后，但GROUP BY子句可没有HAVING子句。

  having 必须 跟在 group by 语句后面.  这一点非常重要.

 

9,   order by 的使用, 这个比较简单.

 

 

3.3.5查询的排序

Ø当需要对查询结果排序时，应该使用ORDER BY子句

ØORDER BY子句必须出现在其他子句之后

Ø排序方式可以指定，DESC为降序，ASC为升序，缺省时为升序

 

基本就是直接指定一列,来进行order by 的排序. 

ORDER BY SNO,SCORE  DESC        有两个列并行的时候,就依次按照 列来进行并行.

 

Ø 此语句为 分组排序 ，执行过程如下：

1.（FROM）取出整个SC

2.（WHERE）筛选SCORE>=60的元组

3.（GROUP BY）将选出的元组按SNO分组

4.（HAVING）筛选选课三门以上的分组

5.（SELECT）以剩下的组中提取学号和总成绩

6.（ORDER BY）将选取结果排序

 

这个执行的过程,非常的清晰明白.   基本可以按照这个来理解 sql语句的执行状态.  后面的涉及多表查询的部分,应该也是按照这个方法来执行的.

 

下面开始进行多表的查询.

 

10 多表查询

数据表连接及连接查询

Ø数据表之间的联系是通过表的字段值来体现的，这种字段称为连接字段。

Ø连接操作的目的就是通过加在连接字段的条件将多个表连接起来，以便从多个表中查询数据。

Ø前面的查询都是针对一个表进行的，当查询同时涉及两个以上的表时，称为连接查询。

Ø表的连接方法有两种：

Ø方法1：表之间满足一定的条件的行进行连接，此时FROM子句中指明进行连接的表名，WHERE子句指明连接的列名及其连接条件。

Ø方法2：利用关键字JOIN进行连接。

具体分为以下几种：

Ø INNER JOIN ：显示符合条件的记录，此为 默认值 ；

Ø LEFT （ OUTER ） JOIN ： 显示符合条件的数据行以及左边表中不符合条件的数据行，此时右边数据行会以 NULL 来显示，此称为 左连接 ；

Ø RIGHT （ OUTER ） JOIN ： 显示符合条件的数据行以及右边表中不符合条件的数据行，此时左边数据行会以 NULL 来显示，此称为 右连接；

Ø FULL （ OUTER ） JOIN ： 显示符合条件的数据行以及左边表和右边表中不符合条件的数据行，此时缺乏数据的数据行会以 NULL 来显示；

Ø CROSS JOIN ： 会将一个表的每一笔数据和另一表的每笔数据匹配成新的数据行。

Ø

Ø当将JOIN关键词放于FROM子句中时，应有关键词ON与之相对应，以表明连接的条件。 这个很重要.

 

下面开始  一个例子,一个例子的讲解 SQl语句的 使用.  看完以后,去了解 hibrate的具体特点.

 

 

等值连接与非等值连接

Ø 例 3.47 查询刘伟老师所讲授的课程。

Ø 方法 1 ：

SELECT T.TNO ,TN,CNO

FROM T,TC

WHERE(T.TNO = TC. TNO) AND (TN=‘刘伟’)

Ø 这里， TN=‘ 刘伟’为 查询条件 ，而 T.TNO = TC.TNO 为 连接条件 ， TNO 为 连接字段 。连接条件的一般格式为：

  [<表名1>.] <列名1> <比较运算符>[<表名2>.] <列名2>

Ø 其中 , 比较运算符主要有：＝、＞、＜、＞＝、＜＝、！＝。

Ø 当比较运算符为“＝“时，称为 等值连接 ，其他情况为非等值连接。

 

初步理解 就是两张表格中,相同的 字段进行连接为一行,   遇到3个 对应 2个的情况,则直接连接为 2X3=6 行的数据.  然后后面 就是对这张表格进行投影,也就是筛选.

 

from   where  group_by  having select order_by

 

所谓的  left join    right join 只不过是 连接完以后 2X3=6条数据中, 加上 左边表的其余数据罢了.

 

Ø 方法 3 ：

SELECT R2.TNO,R2.TN,R1.CNO

FROM                                                              (注意:只有在 from中 的as 可以在 sql语句中使用,   其余地方写的只是表现在结果中)

(SELECT TNO,CNO FROM TC) AS R1

INNER JOIN

(SELECT TNO ,TN FROM T

WHERE TN='刘伟') AS R2

ON R1.TNO=R2.TNO

 

此种方法的特点是   在from中重新造出了两张表格.  并将造出的这两张表格进行连接.  自我估计,这样做 很可能是因为 原来的两张表格的数据 过多,而这样可以精简数据. 说白了 就是 把原来的两张表,用现在比较小的两张表进行替代.

     进一步, 这样做, 可以优先的 对表格进行 筛取,即先将刘伟这一条数据 拿出来, 避免对 每一个教师都进行一次数据的叠加匹配.

 

  这种方式,充分体现了 SQL编写的功力.

 

 

11.   继续学习  三表以及三张表以上的 SQL语句查询.

 

select *  from stu_t,student, teacher 

 在三张表格 进行联合查询的时候,   三招表格的行数,直接变成了 三张表每张表格的行数进行想乘.

   然后   剩余的 就是 筛选的条件而已.

 

select   C.字段一 ,  count(A.主键)
from  Table  A
inner join Table  B  on A.B的主键  =  B.主键
inner join Table  C  on B.C的主键  =  C.主键
group   C.字段一

  这是一种查询的方式.   join可以进行多次的叠加.

 

12.自连接.

    听着名字还是相当的高大上的.

    由此可以看出基本的自连接的特点.

  

 

自身连接

Ø 当一个表与其自已进行连接操作时，称为表的 自身连接 。

例3.49查询所有比刘伟工资高的教师姓名、性别、工资和刘伟的工资。

Ø 要查询的内容均在同一表 T 中，可以将表 T 分别取两个 别名 ，一个是 X ，一个是 Y 。将 X, Y 中满足比刘伟工资高的行连接起来。这实际上是同一表 T 的自身连接。

Ø

Ø 方法 1 ：

SELECT X.TN,X.SAL AS SAL_a,Y.SAL ASSAL_b

FROM T AS X ,T AS Y

WHERE X.SAL>Y.SAL AND Y.TN=' 刘伟 '

 

12 进行select 语句的训练

 

 

 

 

 

检索所有学生姓名，年龄和选课名称。

Ø 方法 1 ：

SELECT SN,AGE,CN

FROM S,C,SC

WHERE S.SNO=SC.SNO ANDSC.CNO=C.CNO

Ø 方法 2 ：

SELECTR3.SNO,R3.SN,R3.AGE,R4.CN

FROM

(SELECT SNO,SN,AGE FROM S) AS R3

INNER JOIN

(SELECT R2.SNO,R1.CN

FROM

(SELECT CNO,CN FROM C)AS R1

INNER JOIN

(SELECT SNO,CNO FROM SC)AS R2

ON R1.CNO=R2.CNO) AS R4

ON R3.SNO=R4.SNO

 

外连接

外连接

Ø 在上面的连接操作中，不满足连接条件的元组不能作为查询结果输出。

Ø 如例 3.48 的查询结果只包括有选课记录的学生，而不会有吴丽同学的信息。若将例 3.48 改成：

例3.51查询所有学生的学号、姓名、选课名称及成绩。（没有选课的同学的选课信息显示为空）则应写成如下的SQL语句。

SELECT S.SNO,SN,CN,SCORE

FROM S

LEFT OUTER JOIN SC

ON S.SNO=SC.SNO

LEFT OUTER JOIN C

ON C.CNO=SC.CNO

则查询结果只包括所有的学生，没有选课的吴丽同学的选课信息显示为空。

 

 

13  嵌套查询

 

Ø 嵌套查询 可以将一系列简单查询构成复杂查询，增强查询能力。

Ø 子查询的嵌套层次最多可达到 255 层，以层层嵌套的方式构造查询充分体现了 SQL “ 结构化 ” 的特点。

Ø 嵌套查询在执行时 由里向外 处理，每个子查询是在上一级外部查询处理之前完成，父查询要用到子查询的结果。

 

 

返回一个值的子查询

Ø 当子查询的返回值只有一个时，可以使用比较运算符（ =, >, <, >=, <=, != ）将父查询和子查询连接起来。

例3.52查询与刘伟教师职称相同的教师号、姓名。

SELECT TNO,TN

FROM T

WHERE PROF=(SELECT PROF

FROM T

WHERE TN='刘伟')

 

返回一组值的子查询

Ø 如果子查询的返回值不止一个，而是一个集合时，则不能直接使用比较运算符，可以在比较运算符和子查询之间插入 ANY 或 ALL 。其具体含义详见以下各例。

1. 使用ANY

例3.53查询讲授课程号为C5的教师姓名。

SELECT TN

FROM T

WHERE TNO=ANY

         (SELECT TNO

       FROM TC

          WHERE CNO='C5')

 

使用IN

Ø 可以使用 IN 代替 “ =ANY ” 。

Ø 例 3.55 （题目同 3.53 ）

SELECT TN

FROM T

WHERE TNO IN

         (SELECT TNO

       FROM TC

          WHERE CNO='C5')

使用ALL

Ø ALL 的含义为 全部 。

例3.56查询其他系中比计算机系所有教师工资都高的教师的姓名和工资。

SELECT TN,SAL

FROM T

WHERE SAL>ALL

        (SELECT SAL

       FROM T

         WHERE DEPT='计算机')

       AND DEPT!= ‘计算机’

 

Ø 此查询也可以写成：

SELECT TN,SAL

FROM T

WHERE SAL>

         (SELECT MAX(SAL )

FROM T

          WHERE DEPT='计算机')

       AND DEPT!= ‘计算机’

Ø 库函数 MAX 的作用是找到计算机系中所有教师的最高工资 1500 。

 

 

 

 

查询不讲授课程号为C5的教师姓名。

SELECT DISTINCT TN

FROM T

WHERE 'C5' !=ALL

         (SELECT CNO

FROM TC

          WHERE TNO=T.TNO)

Ø !=ALL 的含义为不等于子查询结果中的任何一个值，也可使用 NOTIN 代替 !=ALL 。

Ø 子查询包含 普通子查询 和 相关子查询 。

Ø 前面所讲的子查询均为普通子查询，而本例中子查询的查询条件引用了父查询表中的属性值（ T 表的 TNO 值），我们把这类查询称为 相关子查询 。

 

Ø 二者的执行方式不同：

Ø 普通子查询的执行顺序是：

• 首先执行子查询，然后把子查询的结果作为父查询的查询条件的值。

• 普通子 查询只 执行一 次，而父查询所涉及的所有记录行都与其查询结果进行比较以确定查询结果集合。

•

Ø 相关子查询的执行顺序是：

• 首先选取父查询表中的第一行记录，内部的子查询利用此行中相关的属性值进行查询，

• 然后父查询根据子查询返回的结果判断此行是否满足查询条件。如果满足条件，则把该行放入父查询的查询结果集合中。重复执行这一过程，直到处理完父查询表中的每一行数据。

•

Ø 由此可以看出，相关子查询的执行次数是由父查询表的行数决定的。

 

 

 

14 视图的定义

 

 

Ø 视图 删除后，只会删除该视图在数据字典中的定义，而与该视图有关的基本表中的数据不会受任何影响，由此视图导出的其他视图的定义不会删除，但已无任何意义。用户应该把这些视图删除。

 

Ø 视图定义后，对视图的查询操作如同对基本表的查询操作一样。

 

视图的作用 基本上  就是 进行几张基本表格的连接来用的.  还有就是 屏蔽一些表格的字段,以达到安全的目的.

 

 

Ø由上例可以看出，当对一个基本表进行复杂的查询时，可以先对基本表建立一个视图，然后只需对此视图进行查询，这样就不必再键入复杂的查询语句，而将一个复杂的查询转换成一个简单的查询，从而简化了查询操作。

 

这基本上 就是视图的根本用法了.

 

 

Ø 使用视图有如下几个优点：

1. 利于数据保密，对不同的用户定义不同的视图，使用户只能看到与自己有关的数据。

例如,对教师表创建了计算机系视图，本系教师只能使用此视图，而无法访问其他系教师的数据。

2. 简化查询操作，为复杂的查询建立一个视图，用户不必键入复杂的查询语句，只需针对此视图做简单的查询即可。如例3.75。

3. 保证数据的逻辑独立性。对于视图的操作，比如查询，只依赖于视图的定义。当构成视图的基本表要修改时，只需修改视图定义中的子查询部分。而基于视图的查询不用改变。这就是第一章介绍过的外模式与模式之间的独立性，即数据的逻辑独立性

 

15 数据控制

基本上就是 数据库层面的 权限控制.  现在做系统的时候,使用的情况不是很多.  一般都是在代码中进行权限的控制, 比如比较出名的Rbac 组件.