L3 SQL 入门

3-1 SQL 概述

资料操作语言(DML):提供资料修改删除等操作指令完整性(intergrity):SQL DDL 包含设令完整性限制等指令，确保资料的完整及可靠性检视表定义(view drfinition):包含定义检视表的命令交易控制(transaction control):包含交易流程的命令嵌入式 SQL 与动态 SQL(embedded SQL and dynamic SQL):定义 SQL 如何嵌入通用的程式设计语言授权(authorization):包含设定权限等指令

3-2 SQL 定义

3.2.1 基本类型

char(n):存放固定长度 n 的字串资料

varchar(n):存放字串大小为 n 的字串

int:存放整数，最大长度为 4 位元

smallint:存放整数，最大整数为 2 位元

numeric(p,d):存放小数的资料，p 表示精準度，d 表示 d 位数的小数

  EX:numeric(3,1)为三位数，包含2位整数与1位小数

real,double,precision:存近似浮点数，精準值依机器定

float(n):存放浮点数值，精準度为 n

null:表示不存在或不确定资料

3.2.2 基本架构

建立资料表(creat table):

    create table instructor(        ID      char(5),        name    varchar(20) not null,        dept_name varchar(20),        salary numeric(8,2)

主键(primary key):限制唯一的值，主要用于区分资料表中的每一笔资料外来键参考(foreign key):限制属性的值必须参考到关联中主键属性的值非空值(not null):限制属性中不得为 null 值

    create table instructor(        ID          char(5),        name        varchar(20) not null,        dept_name   varchar(20),        salary      numeric(8,2),        primary key (ID),        foreign key (dept_name) references department)

插入资料表(insert):

    insert into instructor values (‘10211’, null, ’Biology’, 66000);

删除关联中的原组(delete):

    delete from student;

删除资料表(drop table):

    drop table r;//(delete from r;)

修改资料表(alter)

    alter table r add A D    //A为新增的属性名称，D为类型

    alter table r drop A 

3-3 SQL查询的基本结构

3.3.1 单一关联的查询

from:欲查询的关联

select(选择):显示查询栏位的最终结果

  可在后加上运算，但不会改变原本的资料

select distinst:查询结果不含重複值select all(*):显示所有查询结果

    select distinct dept_name    from instructo

where:可用来设定查询的条件

    select name    from instructor    where dept_name= ‘Comp. Sci.'and salary > 80000

    资讯系薪水大于80000的教授

3.3.2 多关联查询

资料不会存在同一资料表，需要多关联的查询方式执行顺序:from,where,select查询有开课的教师数量:

    select name, course_id    from instructor, teaches    where  instructor.ID = teaches.ID

3.3.3 自然关联

自然关联(natual join):只考虑双方资料表中有相同属性值的栏位

select name, titlefrom instructor natural join teaches 

列出教师的名称以及他们教导课程的职称

select name, titlefrom instructor natural join teaches, coursewhere teaches.course_id= course.course_id;

3.4其他运算

3.4.1 重命名操作

old-name as new-name

select ID, name, salary/12 as monthly_salaryfrom instructor

3.4.2 字符串操作

(%):任何子字符串匹配(_): 任何字符匹配。

查找名称包含子字符串"dar"的所有教师的名称

select namefrom instructorwhere name like '%dar%' 

3.4.3 Select中的选择子句

表示所有栏位的资料

select instructor *

3.4.4 查询结果排序

按字母顺序列出所有教师的名称

select distinct namefrom    instructororder by name

默认值为升序(ASC)可以为每个属性指定DESCFOR降序

order by name desc

对多个属性进行排序

order by dept_name, name

3.4.5 Where子句

between:指定值可在某数据之间

  EX：查找所有教师的名称  薪水在90,000美元到100,000美元之间

select namefrom instructor where salary between 90000 and 100000

比较元组

select name, course_idfrom instructor, teacheswhere (instructor.ID, dept_name) = (teaches.ID, ’Biology’);

3.5 集合运算

操作联合，交叉，除外，上述每个操作都会自动消除重複值保留重複值:union all, intersect all,except all.

3.6 空值

null表示一个未知值或不存在值

涉及null的任何算术表达式结果都是null

  5 + null returns null

is null:检查空值

  Example: Find all instructors whose salary is null.

select namefrom instructorwhere salary is null

NULL的三维逻辑

OR: (unknown or true) = true,
(unknown or false) = unknown
(unknown or unknown) = unknownAND:(true and unknown) = unknown,
(false and unknown) = false,
(unknown and unknown) = unknownNOT: (notunknown) = unknown

3.7聚集函数

聚集函数为多个值输入后返回为单一值的函数

3.7.1 基本聚集

查找在2010年春季学期教授课程的教师总数

  select count (distinct ID)from teacheswhere semester = ’Spring’ and year = 2010

3.7.2 聚集的分组

找到每个部门的教练的平均工资

  select dept_name, avg (salary)  from instructor  group by dept_name;

select中唯一能出现的非聚集属性函数，必须存在于group by子句中

3.7.3 Having子句

找到所有部门平均工资大于42000的姓名

  select dept_name, avg (salary)  from instructor   group by dept_name  having avg (salary) > 42000;

having和where的差别在于前者能使用聚集函数，后者不行

3.7.4 null值和聚合

全部薪水

select sum(salary )from instructor

以上语句忽略NULL，如有为空集合结果为null

3.8 子查询

3.8.1 集合成员

在查询中使用查询结果

select count (distinct ID)from takeswhere (course_id, sec_id, semester, year) in (select course_id, sec_id, semester, year  from teaches  where teaches.ID= 10101);

3.8.2 集合比较

some:至少符合一个条件all:必须符合所有条件找出有薪水的教师的名称大于生物部中的（至少一个）教练的薪资

select namefrom instructorwhere salary > some (select salary    from instructor    where dept name = ’Biology’);

找出薪水大于生物部门所有教师的所有教师姓名。

select namefrom instructorwhere salary > all (select salary    from instructor    where dept name = ’Biology’);

3.8.3 新关联测试

exists:若子查询结果为空集合回传flase，有资料则回传tureexists:r ⇔ r ≠Ønot exists:r ⇔ r = Ø“查找秋季2009年学期和2010年春季学期所教授的所有课程”

select course_idfrom section as Swhere 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);

3.8.4 重複值测试

unique:检查子查询结果是否有重複值，有则回传flase,否为true

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

3.8.5 from的子查询

找到那些平均工资大于42,000美元部门的平均教师薪水

select dept_name, avg_salaryfrom (select dept_name, avg (salary) as avg_salaryfrom instructorgroup by dept_name)where avg_salary> 42000;

lateral:查询外部查询的属性

select name, salary, avg_salaryfrom instructor I1, lateral (select avg(salary) as avg_salary                from instructor I2                where I2.dept_name= I1.dept_name);

3.8.6 with子句

with提供了暂存资料集的方法，其定义仅可使用一次查出最大预算的所有部门

with max_budget(value) as (select max(budget)from department)select budgetfrom department, max_budgetwhere department.budget= max_budget.value;

3.8.7 标量子查询

标量子查询:子查询查询结果只回传单一属性的情况

3.9 修改资料库

3.9.1 删除

删除薪水低于教师平均工资的所有教师

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

先计算Avg(Salary)并找到要删除的所有元组之后删除上面找到的所有元组

3.9.2 增加

为课程添加新元组

insert into course (course_id, title, dept_name, credits)values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);

or

insert into course (course_id, title, dept_name, credits)values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);

将所有教师添加到Studet关係与Tot_CredS到0

insert into studentselect ID, name, dept_name, 0from instructor

3.9.3 更新

工资超过100,000美元的教练的工资增加3%，其他人为5%

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

可以使用case语句更好

update instructor    set salary = case            when salary <= 100000 then salary * 1.05            else salary * 1.03            end

可搭配子查询重新计算和更新所有学生的Tot_creds值

update student S     set tot_cred= ( select sum(credits)    from takes natural join course    where S.ID= takes.ID and         takes.grade<> ’F’ and        takes.grade is not null);