SQL之数据库查询

SQL之数据库查询

• 简单查询
• 连接查询
• 子查询
• 其他查询
• 对查询结果进行排序

• 分组与汇总

  对数据库中表的查询是数据库操作中最重要基本的操作，查询操作的一般情况：
  select column_list1 [into new_table]
  from table_list
  [where seach_conditions]
  [group by order_columnlist]
  [having hav_conditions]
  [order by column_list2]
  [compute com_expr]
  上述的[]表示可省略，new table为选择出的列组合组成的表，column_list1表示选择的表中的列，table_list表示查询操作的源表，search_conditions 表示进行选择查询时需要满足的条件，order_columnlist表示进行排序时所依据的属性列组合，hav_conditions表示在进行group分组后还需进行的条件过滤条件，column_list2表示对表进行按列排序的列组合，com_expr表示对查询结果进行汇总的表达式。

1.简单查询

 表的简单查询分为投影、选择、聚合函数查询。
 1.1.投影查询
 投影查询主要是选择表中的列组合，等价于于表关系运算中的投影运算
 语法：
 select [all|distinct|top  
 [n|percent]|*|column_list|expression|col as alias_col]
 from table_name
 参数含义：
 1.all：选择的结果包括重复行在内的所有行
 2.distinct:结果不包括重复行
 3.top n|percent:选择表中的前n行或前百分之percent行
 4.*：选择所有属性的行
 5.column_list:选择指定列的行
 6.expression：包含计算列
 7.as  alias_col:可以为选择列名别名
 8.table_name:选择的源表

例子：
1.查询student表中所有学生信息。
注：表中已经事先写入了数据。
代码：

select * from student

效果：

  2.查询student表中前3的人员的姓名，并把他们的年龄都加10岁，将department列命名为别名“系别”。
  代码：

select top 3 name,age+10,department as '系别'
from student

效果：

  3.查询student表中的专业（不重复）
    代码：

select distinct department
from student

效果：

 1.2.选择查询
 选择查询等同于关系运算中的选择运算，为从表中选择满足设定条件的行集合。
 语法:
 select column_list 
 from table_list
 where select_conditions
 select_conditions代表选择条件，可以比较、范围、集合、字符串匹配、空值判断条件。
 下面依次对这些条件进行介绍：
 1.比较条件：
   指通过>、<、=、<>连接的运算
   例子：
   在student表中选择年龄小于22的人员
   代码：

select * 
from student
where age<22

效果：

  2.范围条件
    范围条件主要是用between and作为运算符的条件，凡是其值在范围之内，都是符合选择要求的。
    例子：
    选择student表中年龄大小在18--23岁之间的人员信息。
    代码：

select * 
from student
where age between 18 and 23

效果:

   3.集合条件
     集合条件主要使用[not]in来判断值是否在集合内。
     例子：
     查询studnet表中计算机系或者自动化系的学生姓名
     代码：

select name
from student
where department not in ('计算机系','自动化系')

结果：

    4.字符匹配条件
      字符匹配条件是用[not]like 字符串 来作为判断条件的
      判断的时候常用字符通配符%，[]、[^]、_作为判断条件
      %：表示任意个若干字符序列，比如'%abc%'表示字符中包含abc的若干个字符组成的字符串，'abc%'表示以abc开头的字符串。
      []:表示[]中规定字符或规定字符范围的任意单个字符，如[abc]表示abc任意一个字符，[a-c]表示a到c的任意一个字符。
      [^]:表示没在[]范围之内的单个字符，[^a]非a的字符
      _:表示单个字符的占位符，比如:abc_以abc开头的4个字符组成的字符串。
      例子：
      查询student表中名字以'马'开头的人员信息。

      代码：

select * 
from student
where name like '马%'

效果:

    5.空值判断条件
      以is [not]null对属性列进行判断的判断条件，判断该属性列是否为空。
      例子：
      向class表中插入('b4',null,3.5),再在class表中查询classname为null的课程号classid。
      代码：

insert into class
values('b4',null,3.5)
select classid
from class
where classname is null

    1.3.聚合函数查询
        聚合函数查询主要利用聚合函数对源表的某一属性列进行聚合运算从而得到选择结果。
        聚合函数：
        sum、avg、min、max、count这些函数的参数都只有一个，且为列名，count(*)表示计算表中总共的行数。
        例子：
        查询student表中的最大年龄。
        代码：

select MAX(age) as 最大年龄 
from student

效果：

3.连接查询

  连接查询分为：内连接、自连接、外链接、交叉连接查询。
  1.内连接查询
    内连接查询：将两个表连接成第三个表，新表中不包含不满足连接条件的行。
    2种语法：
     1.1.select  column_list 
       from table1 inner join table2 
       on table1.属性=table2.属性
       [where ....]
       ...
      例子：
      查询学号为1002的学生信息及所选课程信息。
      代码：

select *
from student inner join class
on student.classid=class.classid
where no='1002'

效果：

      1.2.select column_list 
        from table1,table2
        where  连接条件
        例子：
        查询所有学生的信息及其所选课程信息。
        代码：

select *
from student,class
where student.classid=class.classid

效果:

  2.自连接
    不同表之间可以进行连接，同一个表之间也可以进行连接，称之为内连接，自连接不同于内连接以及外链接，前者属于横向的连接，即列的连接，后者为纵向的连接，为行的连接，即将表内不同的行连接起来。为了进行内连接，需对同一个表命名不同的别名，使之成为逻辑上的不同表，方便进行操作。
    例子：
  向student表中插入('1006','周星驰',28,'自动化系','7555','b2')
  再查询student表中自动化系的学生信息。
  代码：

insert into student
values('1006','周星驰',28,'自动化系','7555','b2')
select *
from student a inner join student b
on a.department=b.department and a.no<>b.no

效果：

注：结果中出现重复行是因为内连接属于交叉连接，类似关系笛卡尔积

 3.外链接
   外链接将两个不同的表按照连接条件连接起来，连接的结果中包括不满足连接条件的数据。
   外链接按类型分为：
   左外连接（left outer join）：对连接左边的表不加限制
   右外连接（right outer join）：对连接右边的表不加限制
   全外连接（full outer join）：对两个表都不加限制
   1.3左外连接（其他两类连接类似）
       语法：
       select  list
       from table1 left outer join table2
       on link_conditions
       例子：
       向student表中插入('1007','周杰伦',30,'数学系','8999',null)
       并查询所有学生信息及课程情况

insert into student
values('1007','周杰伦',30,'数学系','8999',null)
select *
from student left outer join class 
on student.classid=class.classid

效果：

   4.交叉连接
     交叉连接（cross join）等价于关系运算中的笛卡尔运算，详细过程请参考关系数据库理论。
     例子：
     对student、class表进行交叉连接，查询连接后的表信息。
     代码：

select *
from student cross join class

效果：

3.子查询

  子查询是将select查询的结果作为外部查询语句的判断条件。
  语法：
  select ...
  from ...
  where  表名.属性 满足关系(select ...)
  子查询分为相关子查询、无关子查询。

  3.1无关子查询：内部查询的过程不依赖于外部查询，两个过程是相对独立的
  例子：
  查询student表中年龄大于表中人员平均年龄的人员信息。
  代码：  

select *
from student
where age>(select AVG(age) from student)

效果：

 3.2相关子查询
      相关子查询的运算过程依赖于外部查询，具体过程为：先在外部查询所用列中选择内部查询要用的属性列，将该属性列送入到内部查询中进行查询，这个过程一直持续到对外部查询中所有源表都查询完毕为止。
      相关子查询分为：
      3.2.1比较子查询
      例子：
      查询student表中大于等于每个专业最小年龄的学生信息
      代码：

use student 
go
select no,name,age,department
from student a
where age>=(select min(age) from student b where a.department=b.department)

效果：

这里进行比较子查询时，都是先从源表student中逐一取出一个行，再用行中的department属性赋值给子查询，子查询利用department进行相应查询后返回结果作为父查询的查询条件，如此循环往复，直至对整个表都查询完毕为止。

       3.2.2存在性子查询
          存在性子查询用exits进行子查询，exits中的参数为select查询语句，该语句返回逻辑值作为父查询的where句子条件。由于exits只返回逻辑值，因此exits中的select中columnlist通常为*。
          例子：
          查询student表中选修了'b2'课程的学生姓名和年龄
          代码：

select name,age
from student a
where exists(
             select * from student b 
             where a.no=b.no and b.classid='b2')

效果：

4.其他查询

 包括union查询、except查询、intersect查询
 4.1.union查询
 union查询可以将多个不同的查询连接到一起，形成一个整体。
 select_states1
 union
 select_states2
 ....
 但要求连接的各个select语句中列顺序相同，类型兼容
 例子：
 从student表中查询自动化系和计算机系的学生
 代码：

select name,age,department
from student
where department='自动化系' 
union 
select name,age,department
from student
where department='计算机系'

效果：

  4.2.except连接查询
 except查询结果中只保留第一个中有而第二个中没有的行，常用于表间的查询，即两个select_states为两个不同表的查询语句。
 select_states1
 except
 select_states2
 但要求连接的各个select语句中列顺序相同，类型兼容
 例子：
 创建sc表，包含no(学号)、classid(课程号)、score(成绩)
 向表中插入student表中成员的相应学号、课程号、成绩
 查询没有考试成绩的学生
 代码：

use student
go 
create table sc      ---建立sc表
(
  no       char(10),
  classid  varchar(10) not null,
  score    tinyint,
  constraint pk_sc_1 primary key(no,classid),
  constraint fk_sc_1 foreign key(no) references student(no),
  constraint  fk_sc_2 foreign key(classid) references class(classid)    
) 
insert into sc        ---插入考试成绩
values('1001','a1',99),
       ('1002','b1',85)
select *             ---查询未考试学生信息
from student
where no in(
select no
from student
except
select no
from sc
)       

效果：

       4.3.intersect查询
           操作结果为表1和表2都有的数据。
           select_states1
           except
           select_states2
           要求连接的各个select语句中列顺序相同，类型兼容
           例子：
           查询有考试成绩的学生信息
           代码：

select *             ---查询考试学生信息
from student
where no in(
select no
from student
intersect
select no
from sc
)      

效果：

5.对查询结果进行排序

  sql通过order by对查询结果进行排序
  语法
  select  column_list
  from table_list
  where ...
  order by column1 desc|asc ,column2 desc|asc...
  上述句子的意思是对查询结果按照column1属性的值进行排序，在column1属性值相同的情况下再按照column2进行排序...
  例子：
  对student表中的学生按照年龄降序排序，在年龄相同的情况下按照学号升序排序。
  代码：

select *
from student
order by age desc,no asc

效果：

6.分组和汇总

  6.1分组
     在进行查询操作时，会希望能够对查询结果进行分组查询，比如查询人物表中的男女比例等。
     sql分组查询使用group  by 属性列的方式
     这里的属性列必须是select中除表达式列、聚合函数列之外的列组合的全集，且不能使用列的别名
    语法：
    select column_list
    from table_list
    where ...
    group by column_list
    例子：
    查询student表中都有哪些系，每个系有多少人
    代码：

select department as '系别',COUNT(*) as '人数'
from student
group by department

系别：

   在分组的基础上可以用having子句对分组后的数据进行进一步的筛选。
   例子：
   查询student表中系别人数大于等于2的系别名称及人数
   代码：

select department as '系别',COUNT(*) as '人数'
from student
group by department
having COUNT(*)>=2

效果：

  6.2汇总
     sql的汇总操作是对查询结果的汇总，这个汇总结果在显示结果时单独另起一行，sql使用compute(expr)进行汇总操作，放于select语句之后，expr中的列均为select列集的子集
     例子：
    选择student表中人员的姓名，年龄，系别，并汇总年龄
    代码：

select name,department,age
from student
compute avg(age) 

效果：