【资料库系统】 L3 SQL 入门

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, title
from instructor natural join teaches

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

select name, title
from instructor natural join teaches, course
where teaches.course_id= course.course_id;

3.4其他运算

3.4.1 重命名操作

old-name as new-name

select ID, name, salary/12 as monthly_salary
from instructor

3.4.2 字符串操作

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

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

select name
from instructor
where name like '%dar%'

3.4.3 Select中的选择子句

表示所有栏位的资料

select instructor *

3.4.4 查询结果排序

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

select distinct name
from    instructor
order by name

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

order by name desc

对多个属性进行排序

order by dept_name, name

3.4.5 Where子句

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

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

select name
from instructor 
where salary between 90000 and 100000

比较元组

select name, course_id
from instructor, teaches
where (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 name
from instructor
where salary is null

NULL的三维逻辑
- OR: (unknown or true) = true,
  (unknown or false) = unknown
  (unknown or unknown) = unknown
- AND:(true and unknown) = unknown,
  (false and unknown) = false,
  (unknown and unknown) = unknown
- NOT: (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 takes
where (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 name
from instructor
where salary > some (select salary
    from instructor
    where dept name = ’Biology’);

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

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

3.8.3 新关联测试

exists:若子查询结果为空集合回传flase，有资料则回传ture
exists:r ⇔ r ≠Ø
not exists:r ⇔ r = Ø
“查找秋季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);

3.8.4 重复值测试

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

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);

3.8.5 from的子查询

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

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

lateral:查询外部查询的属性

select name, salary, avg_salary
from 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 budget
from department, max_budget
where department.budget= max_budget.value;

3.8.7 标量子查询

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

3.9 修改资料库

3.9.1 删除

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

delete from instructor
where 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);

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, 0
from 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);

<<: CMoney第八届菁英软件工程师战斗营满一个月_Week 4

>>: 企业培训及顾问服务 Corporate Training & Consultancy