数据库-第四章 SQL语句查询

2016-06-16 08:38 小米加汉堡阅读(962) 评论(0) 收藏举报

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

　　　　　　　　　　SQL查询的基本结构

　　　　　　　　　　集合运算

　　　　　　　　　　空值

　　　　　　　　　　聚集函数

　　　　　　　　　　嵌套子查询

　　　　　　　　　　数据库的修改

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL查询的基本机构

SQL查询的基本机构由三个子句构成：select，from，where

select A1，A2，A3
from r1,r2,r3
where P;

select子句

注意：SQL不允许在属性名称中，使用字符'-',使用dept_name代替dept-name

　 SQL不区分字母的大小写，因此可以使用大写字母、小写字母命令表，属性等。

SQL允许在关系以及SQL表达式结果中出现重复的元祖

若要强行去除重复，可在select后加入关键词distinct

　　例：查询instructor关系中的所有系名，并去除重复　　

select distinct dept_name
from instructor;

　　SQL也可以使用关键词all来显式指明不去除重复(SQL默认就是all)

select all dept_name
from instructor;

　星号*在select子句中，可以用来表示“所有的属性”

select * 
from instructor;

select子句中还可带含有+、-、/运算符的算术表达式，运算对象可以是常数或元组的属性。

select ID,name,salary*1.05
from instructor;

where子句

where子句允许我们只选出那些在from子句的结果关系中满足特定谓词的元祖。

例：找出在Computer Science系并且工资超过7万美元教师的姓名

select name
from instructor
where dept_name='Computer.science' and salary>70000;

上述SQL查询语句，对应的关系代数表达式为：

SQL允许在where子句中使用逻辑连词and，or和not，也可以使用between指定范围查询。逻辑连词的运算对象可以是包含比较运算符<,<=,>,>=,=和<>的表达式

例：找出工资在90000到100000教师的名字

select name
from instructor
where salary > 90000 and salary < 100000;

或

select name
from instructor
where salary between 90000 and 100000;

from子句

from子句是一个查询求值中需要访问的关系列表，通过from子句定义了一个在该子句中所列出关系上的笛卡尔积。

找出instructor和teacher的笛卡尔积　　

select *
from instructor,teacher;

更名运算

SQL提供可为关系和属性重新命名的机制，使用as子句，as语句可以出现在select子句中，也可以出现在from子句中

使用更名运算，对关系进行更名,

例：找出所有教师，以及他们所讲授课程的标识

select T.name,S.course_id
from instructor as T,teacher as S
where T.ID = S.ID;

　例：找出所有教师名，他们的工资至少比 biology系某一个教师的工资高

select T.name,S.course_id
from instructor as T,teacher as S
where T.salary > S.salary and S.dept_name = 'biology';

字符串运算

对字符串进行的最常见的操作就是使用操作符like的模式匹配，使用两个特殊的字符来描述模式：

百分号(%):匹配任意的子串

下划线(_):匹配任意一个字符

例：找出所有建筑名称中包含子串'ryan'的所有系名

select dept_name
from departname
where building like '%ryan%';

为使模式中能够包含特殊字符（即%和_）,SQL允许定义转义字符。在like运算中使用escape关键词来定义转义字符

　　例：使用反斜线（\）作为转义字符

like 'ab\%cd%' escape '\' 　　匹配所有以ab%cd开头的字符串
like ‘ab\\cd%’ escape '\' 　　匹配所有以ab\cd开头的字符串

　　还允许在字符串中有多种函数，例如串联("||")、提取子串，计算字符串长度、大小写转换（upper(s)将字符串s转换成大写；lower(s)将字符串s转换成小写）、去掉字符串后面的空格(使用trim(s))等。

排列元祖显示次序

　　SQL为用户提供一些对关系中元祖显示次序的控制。order by子句就可以让查询结果中元祖按排列顺序显示。

　　例：按字母顺序列出在Physical系的所有教师

select name
from instructor
where dept_name = ‘phsical’
order by name;

　　order by 子句默认使用升序。要说明排序顺序，desc表示降序，asc表示升序

　　例：按salary的降序列出整个instructor关系，如果有几位教师的工资相同就按升序排列

select *
from instructor
order by salary desc, name asc;

重复

　　在关系模型的形式化数学定义中，关系是一个集合。因此，重复的元祖不会出现在关系中，但在实践中，包含重复元祖的关系是有用的。

　　可以使用关系运算符多重集版本来定义SQL查询的副本定义

集合运算

　　SQL作用在关系上的union，intersect和expect运算对应于数学集合论中的∪，∩和_运算

　　union，intersect和expect运算与select子句不同，他们会自动去除重复。

　　如果要保留所有重复，必须用union all、intersect all 和 expect all

　　假设一个元祖在关系r中重复出现了m次，在关系s中重复出现了n次，那么这个元祖将会重复出现

　　　　※ 在r union all s中，重复出现m+n次

　　　　※ 在r intersect all s中，重复出现min(m,n)次

　　　　※ 在r expect all s中，重复出现max(0,|m-n|)次

　　例：找出2009年秋季开课，或者2010年春季开课或两个学期都开课的所有课程　　　　

      (select course_id
　　　　from section
　　　　where semester = 'Fall' and year = 2009)
　　　　union
　　　 (select course_id
　　　　from section
　　　　where semester = 'Spring' and year = 2010);

　　例：找出在2009年秋季和2010年春季同事开课所有课程

(select course_id
 from setion
 where semester = 'Fall' and year = 2009)
 intersect
(select course_id
 from section
 where semester = 'Spring' and year = 2010);

　　例：找出在2009年秋季开课但不在2010年春季开课所有课程

(select course_id
 from section
 where semester = 'Fall' and year = 2009)
 except
(select course_id
 from section
 where semester = 'Spring' and year = 2010);

聚集函数

聚集函数是以值的一个集合（集或多重集）为输入，返回单个值的函数。

   SQL提供五个固有的聚集函数：
       平均值：avg
       最小值：min
       最大值：max
       总和：sum
       计数：count
   其中，sum和avg的输入必须是数字集，但其他运算符还可作用在非数字数据类型的集合上，如字符串

   分组聚集（group by）：group by 子句中给出的一个或多个属性是用来构造分组，group by 子句中的所有属性上取值相同的元组被分在一个组中。
   having子句类似与where子句，但其是对分组限定条件，而不是对元组限定条件。having子句中的谓词在形成后才起作用。

   例：找出computer science系教师的平均工资

   select avg(salary) as avg_salary
   from instructor
   where dept_name='Comp_sci';

例：找出每个系的平均工资

select dept_name avg(salary) as avg_salary
from instrauctor
group by dept_name;

    注意：任何没有出现在group by子句中的属性，如果出现在select子句中的话，他只能出现在聚集函数内部，否则这样的查询就是错误的！

   例：找出教师平均工资超过42000美元的系和工资

select dept_name avg(salaty) as avg_salary
from instructor
group by dept_name
having avg(salaty) > 42000;

空值
   SQL允许使用null值表示属性值信息缺失，我们在谓词中可以使用特殊的关键词null测试空值，也可以使用is not null 测试非空值

   例：找出instructor关系中元组在属性salary上取空值的教师名

select name
from instructor
where salary is not null;

    聚集函数根据以下原则处理空值：
       除了count(*)外所有的聚集函数都忽略输入集合中的空值
       规定：空集的count运算值为0，其他所有聚集运算在输入为空集的情况下返回一个空值。

嵌套子查询
   SQL提供嵌套子查询机制。子查询是嵌套在另一个查询中的select-from-where表达式。
   子查询嵌套在where子句中，通常用于集合的成员资格、集合的比较以及集合的基数进行检查。主要用于：
       集合成员资格
       集合的比较
       空关系测试
       重复元组存在性测试
       from子句中的子查询
       with子句

   集合成员资格：SQL允许测试元组在关系中的成员资格。连接词in测试元组是否是集合中的成员，集合是由select子句产生的一组值构成的，对应的还有not in
       例：找出在2009年秋季和2010年春季学期同时开课的所有课程

        select distinct course_id
        from section
        where semester = 'Fall' and year = 2009 and
            course_id in (select course_id
                          from section
                          where semester = 'Spring' and year = 2010);

   集合的比较
       SQL中短语”至少比某一个要大“在SQL中用>some表示
       例：找出满足下面条件的教师名，他们的工资至少比Biology系某一个教师的工资要高

            select name
            from instructor
            where salary > some (select salary
                                 from instructor
                                 where dept_name = 'Biology');

               (5 < some(0,5,6)) = true
               (5 < some(0,5)) = false
               (5 = some(0,5)) = true
               (5 ≠ some(0,5)) = true
       例：找出满足下面条件的教师名，他们的工资至少比Biology系所有教师的工资要高

            select name
            from instructor
            where salary > all (select salary
                                from instructor
                                where dept_name = 'Biology');

               (5 < all(0,5,6)) = false
               (5 < all(6,10)) = true
               (5 = all(4,5)) = false
               (5 ≠ all(4,6) = true

       例：找出平均工资最高的系

            select dept_name
            from instructor
            group by dept_name
            having avg(salary) >= all (select avg(salary)
                                       from instructor
                                       group by dept_name);

空关系测试
   SQL可以测试子查询的结果中是否存在元组。exist结构在作为参数的子查询为空时返回true值
    使用not exists结构模拟集合包含操作：可将“关系A包含关系B”写成“not exists(B except A)”

　例：找出2009年秋季学习和2010年春季学期通识开课的所有课程

        select course_id
        from section as S
        where semester = 'Fall' and year = 2009
            exists (select *
                    from section as T
                    where semester = 'Spring' and year = 2010 and
                          S.course_id = T.course_id);

   例：找出选修了Biology系开设的所有课程的学生
       使用except结构，写该查询：

        select distinct S.ID,S.name
        from student as S
        where not exists((select course_id
                          from course
                          where dept_name = 'Biology')
                          except
　　　　　　　　　　　　　　  (select T.course_id
                          from takes as T
                          where S.ID = T.ID));

重复元组存在性测试

　 SQL提供一个bool函数，用于测试在一个子查询的结果中，是否存在重复元组。
如果作为参数的子查询结果中，没有重复的元组unique结构将返回true值。
例:找出所有在2009年最多开设一次的课程

    select T.course_id
    from course as T
    where unique (select R.course_id
                  from section as R
                  where T.course_id = R.course_id and R.year = 2009);

例：找出所有在2009年至少开设两次的课程

    select T.course_id
    from course as T
    where not unique (select R.course_id
                      from section as R
                      where T.course_id = R.course_id and R.year = 2009)

FROM子句中的子查询
       SQL允许from子句中使用子查询表达式。任何select-from-where表达式返回的结果都是关系，
       因而可以插入到另一个select-from-where中任何关系可以出现的位置。
       例：找出系平均工资超过42000美元的那些系中教师的平均工资

        select dept_name,avg_salary
        from(（select dept_name,avg(salary)
             from instructor
             group by dept_name）
             as dept_avg(dept_name, avg_salary)
        where avg_salary > 42000);

例：找出在所有系中工资总额最大的系

      select max(tot_salary)
      from ((select dept_name,sum(salary)
                  from instructor
                  group by dept_name) as dept_total(dept_name,tot_salary)
                  );

with子句
   with子句提供定义临时关系的方法，这种定义只对含有with子句的查询有效

   例：找出具有最大预算的系

      //定义临时关系
      with max_budget(value) as   
           (select max(budget)
            from department)
      //使用临时关系
      select budget
      from department,max_budget
      where department.budget = max_budget.value;

例：找出工资总额大于平均值的系

　　　　　//定义临时关系
         with dept_total(dept_name,value) as
                  (select dept_name,sum(salary)
                   from instrutor
                   group by dept_name),
                  dept_total_avg(value) as
                  (select avg(value)
                   from dept_total)
         select dept_name
         from dept_total A,dept_total_avg B
         where A.value >= B.value;

数据库的修改-删除
   删除不能删除某个属性的值，而是整个元组
   SQL删除语法：
       delete from r
       where P;
   P代表谓词，r代表一个关系

   例：从instructor关系中所有这样的教师元组，他们在位于Watson大楼的系工作

delete from instructor
    where dept_name in(select dept_name
                       from department
                       where building = 'Watson'
                       );

例：删除工资低于大学平均工资的教师记录

        delete from instructor
        where salary < (select avg(salary)
        from instructor);

数据库的修改-插入
SQL使用insert语句，向关系中插入元组，形式如下：

      insert into r[(c1,c2,...)]
            value(e1,e2,...);                                                
      insert into r[(c1,c2,...)]
            select e1,e2,...from...;

例：假设我们要插入的信息是Computer Science系开设的“Database System”的课程CS-437,它有4个学分

            insert into course
            　　value('CS-437','Database System','Comp.Sci',4);

SQL允许insert语句中指定属性，所以上述语句还可以写为：

             insert into course(course_id,title,dept_name,credits)
             　　value('CD-437','Database System','Comp.Sci',4);

若上例学分未知，插入语句还可以写为：

           insert into course
               value('CS-437','Database System','Comp.Sci',null);
           insert into course
               value('CS-437','Database System','Comp.Sci');

例：Music系每个修满144学分的学生成为Music系的教师，其工资为18000美元

            insert into instructor
            select ID,name,dept_name,18000
            from student
            where dept_name='Music' and tot_cred>144;

数据库的修改-更新
SQL允许使用update语句，在不改变整个元组的情况下，改变其部分属性的值，

        update r
        set <c1=e1,[c2=e2,...]>
        [where <condition>];

SQL提供case结构，避免更新次序引发的问题，形式如下：

        case
        when pred1 then result1
        when pred2 then result2
        ...
        when predn then reslutn
        else result0
        end

例:给工资超过100000美元的教师涨3%的工资，其余教师涨5%的工资

        update instructor
        set salary = salary*1.03
        where salary > 100000;
        update instructor
        set salary = salary*1.05
        where salary <=100000;

上述结构顺序不能乱，否则出现工资低于100000涨工资8%的情况

      update istructor
      set salary=case
      where salary <= 10000 then salary*1.03
      salary > 10000 then salary*1.05

刷新页面返回顶部

小米加汉堡

数据库-第四章 SQL语句查询

About