数据库第四章总结

-- 第四章：
--     SQL查询基本结构
--     集合运算
--     空值
--     聚集函数
--     嵌套子查询
--     数据库修该
-- 
SQL不区分大小写

SQL查询基本结构：
    SQL的查询由3部分构成：
        SELECT A1, A2, ... , An
        FROM r1, r2, rm
        WHERE P
查询的代数表达式

    eg：
        找出所有教师的名字
        SELECT name
        FROM instructor;
    
    SELECT 子句：
        SQL允许出现重复元组想要强行去除，可在select后加入关键词distinct
            eg：
                找到关系终中的所有系名， 并去除重复
                    SELECT distinct dept_name
                    From instructor;
            可以使用关键词 all 来显示指明不去除重复（SQL默认状态）
            eg：
                SELECT ALL dept_name
                FROM instructor;

        "*"在SQL中可以用来表示所有属性
            eg:
                select *
                from instructor

        select 句子可以含有 +、-、*、/ 运算符的表达式，对象可以是元组或者是常数
            eg：
                select ID, name, salary * 1.05
                from instructor;
    
    WHERE 子句：
        允许选出 在 from 句子中结果满足特定谓词 P 的元组
            eg：
                找出所有在Computer Science系 and 工资超过 70 000 美元的教师姓名
                    select name
                    from instructor
                    where dept_name = 'Comp.Sci.' and salary > 70000;
        
        SQL允许在where子句中使用逻辑连词 and, or, not, 也可以使用 between
        指定范围查询寻。还有可以使用 < , <=, > ,>= ,= , <> 的表达式 
            eg：
                找出工资在 90 000 到 100 000 的教师的姓名 
                    select name
                    from instructor
                    where salary <= 100000 and salary >= 90000;
                或者：
                    select name
                    from instructor
                    where salary between 90000 and 100000;

    FROM 子句：
        查询值中需要访问的列表，通过from子句定义了该子句中所列出关系的笛卡尔积
            eg：
                找出 关系 instructor 和 teacher 的 笛卡尔积
                    select *
                    from instructor, teacher;
            eg：
                找出所有在Computer Science系的教师名和课程标识
                    select name, course_id
                    from instructor, teachers
                    where instructor.ID = teachers.ID 
                        and instructor.dept_name = 'Comp. Sci.';

    as子句 {更名运算}：
        SQL提供可为关系和属性重命名的机制
        Form： old_name as new_name
        ps: as 可以出现在select子句中，也可以出现在from子句中
        eg：
            找出所有在Computer Science系的教师名和课程标识
            并重命名为instructor为instructor_name
                select name as instructor_name, course_id
                from instructor, teachers
                where instructor.dept_name = 'Comp. Sci.' 
                    and instructor.ID = teachers.ID;
        使用更名运算对关系重命名
            eg：
                找出所有教师，以及他们所受课程的标识
                    select T.name, S.course_id
                    from instructor as T, teachers as S
                    where T.ID = S.ID
            eg:
                找出所有教师名，他们的工资至少比Biology某一个教师工资要高
                    select distinct T.name
                    from instructor as T, instructor as S
                    where T.salary > S.salary and S.dept_name = 'Biology';

    like 操作符：{字符串操作}
        like 通过 '%' 和 '_' 进行模式匹配
            '%'：匹配任意字符串
            '_'：匹配任意一个字符
        eg：
            找出所在建筑名称中含有‘Waston’的所有系名
                select dept_name 
                from department
                where building like '%Waston%';
            ps:需要提取含“%”或“_”或“\”的字符可以使用 
                escape 关键词'\' 进行转义
                eg：
                    like 'ab\%cd%' escape '\' 匹配所有以“ab%cd”开头的字符串
                SQL提供了大量的string 函数
    
    order by 子句：{默认升序}
        可以让查询结果中元组安排列序显示
        eg：
            按字母排序列出在 Physics 系的所有教师
                select name
                from instructor 
                where dept_name = 'Physics'
                order by name;
        排序：{desc:降序; asc:升序}
            eg：
                按salary降序列出整个instructor的关系，存在相同则按字典排序
                    select * 
                    from instructor 
                    order by salary desc, name asc;

    SQL重复的定义：
        

集合运算：
    union：　　　　并集
     intersect:　　交集
　   except:　　　　差集

　　由于符号关系上图
　　

eg：

    ps:这三个操作会去重，如果想不去重需要加上关键词all
    
聚集函数：
    平均值：    avg
    最小值：    min
    最大值：    max
    值的综合：  sum
    值的数量：  count
    ps: sum 和 avg 必须用在数字集

    group by:{分组聚集}
        在 group by 句子中给出一个或多个属性用来构造分组，
        group by 子句中的所有属性都会被分到一个分组
　　　　

having 子句：
        作用于分组，(where 作用于元组限定条件)
        having 子句中的谓词在形成分组后才起作用

    eg:
        找出 Computer Science系教师的平均工资
            select avg(salary) as avg_salary
            from instructor
            where dept_name = 'Comp. Sci.';
    eg：
        找出每个系的平均工资：
            select dept_name avg(salary) as avg_salary
            from instructor
            group by department;
        ps: 任何没有出现在 group by 子句中的属性 
            出现在 select 中，只能出现在聚集函数内部
        
    eg：
        找出教师平均工资高于42 000的系
            sele dept_name avg(salary) as avg_salary
            from instructor
            group by dept_name
            having avg(salary) > 42000;
        ps：任何没有出现在 having 子句中没有被聚集的属性 
            必须出现在 group by 中

空值：
    可以作用于属性的值，表示属性值缺失
    可以使用 is null 对谓词的值进行 空检测
    或者使用 is not null ，进行非空检测

    ps：count(*) 比较奇葩,它会统计所有的属性包括空值

    eg：
        找出 instructor 中元组在属性salary上取空值的教师名
            select name
            from instructor where salary is null
        
    聚集函数会忽略空值
        
嵌套子查询：
    集合成员资格：
        in:
            用于测试 元组 是否 是 select 产生的集合 的成员
        not in：
            用于测试 元组 是否 不是 select 产生的集合 的成员
        eg：
            找出在 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)
        eg：
            找出(不同的)学生总数， 他们选修了ID为10101的教师所讲授的课程
                select count(distinct ID)
                from takes
                where (course_id, sec_id, semester, year)
                            in (select course_id, sec_id, semester, year
                                    from teachers
                                    where teachers.ID = 10101);
        
   集合的比较：
     some 子句的定义：
            

        eg:
            找出所有教师名，他们的工资至少比Biology某一个教师工资要高
                select distinct T.name
                from instructor as T, instructor as S
                where T.salary > S.salary and S.dept_name = 'Biology';
            思考这个例子的新写法
                select distinct name
                from instructor
                where salary > some (select salary 
                                        from instructor
                                        where dept_name = 'Biology');
                ps:some类似于或关系，由于整个语法左边是值
                    右边是集合，some还有内部应该实现了迭代器

        all 子句的定义：
            

        eg：
            找出所有教师名，他们的工资比Biology每一个教师工资要高
                select name
                from instructor 
                where salary > all (select salary 
                                    from instructor
                                    where dept_name = 'Biology');
        
    空关系测试：
        exists: 非空测试
        not exists: 空测试
        eg：
            改写"找出在 2009 年秋季 和 2010 年春季同时开课的所有课程"
                select course_id
                from section as S
                where semester = 'Fall' and year = 2009 and
                    exists (select *
                    from section as T
                    where semester = 'Spring' and year = 2010 and
                            S.course_id = T.course_id);
        PS:该查找效率极低，每次查询都会执行一次内部子查询，
            原因：引用了外部变量

        eg：
            找出选修了Biology系开设的所有课程的学生
                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));
    
    unique：{重复元组存在测试}
        作为参数的子查询中没有重复元组unique将返回true
        eg：
            找出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)
        PS: unique 可以被换位 1 >= 

    
    from 子句中的嵌套子查询：
        说白了就是把基本查询套入到from中，相当于局部视图

        eg：
            找出所有系中总工资额最大的系
                select max(tot_salary)
                from (select dept_name, sum(salary)
                        from instructor 
                        group by dept_name) as dept_name (dept_name, tot_salary);

    with 子句：
        提供定义临时 关系 的方法              --注意是关系
        生命周期：
            在 with 查询语句内有效    
        eg：
            找出工资总额大于平均值的系
                with dept_total(dept_name, value) as 
                    (select dept_name, sum(salary)
                    from instructor
                    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;

数据库的修改：
    删除：
        delete from r
        where P;
        ps:其中P代表一个谓词，r代表一个关系、
        eg：
            删除位于Watson大楼的老师
                delete from instructor
                where dept_name in (select department
                                    from department
                                    where building = 'Watson');
        eg:
            删除工资低于平均工资的教师记录
                delete from instructor
                where salary < (select avg(salary)
                                    from instructor)
    插入：
        insert into r[(c1,c2,...)]
            value (e1, e2)
        insert into r[(c1,c2,...)]
            select e1, e2, ..., from ...
        eg：
            在 Computer Science系 里插入 Database systems 的 课程CS——437，学分为 4
                insert into course
                    value('CS_437', 'Database systems', 'Comp. Sci.', '4');
            或者
                insert into course(course_id, title, dept_name, credits)
                    value('CS_437', 'Database systems', 'Comp. Sci.', '4');
　　　　　

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


        
SQL查询语句顺序：
    from -> where -> group (aggregate) -> having -> select -> order by