SQL基础

 
create table department
    (dept_name varchar(20),
    name varchar(20) not null,
    budget numeric(12,2),
    semester varchar(8),
    primary key (dept_name),
    foreign key (name) references instructor
        on delete cascade
        on update cascade,
    unique (budget),
    check (semester in ('Fall','Winter','Spring','Summer')));

创建一个名为department的关系: 1、dept_name:最长20个字符的不定长字符串; 2、name:最长20个字符的不定长字符串,不能为null; 3、budget:长度12的数字,小数点后有2位; 4、主码为dept_name属性; 5、外码为name属性,被参照关系为instructor;当instructor删除或更新元组而破坏了完整性约束,则会删除department中违反约束的元组; 6、budget属性取值具有唯一性; 7、插入的数据必须保证semester属性的取值是’Fall’,’Winter’,’Spring’,’Summer’中的一个;

drop table department;

删除department关系;

alter table department add age numeric(2);

为department关系添加age属性,原先存在的元组的age属性都被赋值为null;

alter table department drop age;

为department关系删除age属性;

select distinct name, salary*1.1
from instructor
where dept_name = 'Comp.Sci' and salary > 70000;

查询结果为: 1、来自instructor关系; 2、只输出dept_name属性值为“Comp.Sci”,且salary属性值大于70000的元组; 3、结果关系包含name, salary两个属性; 4、salary的值为1.1倍; 5、去重;

select name as new_name, instruct.dept_name, building
from instructor, department
where instructor.dept_name = department.dept_name;

查询结果为: 1、来自instructor, department的笛卡尔积;同属保留2列; 2、只输出instructor.dept_name = department.dept_name的元组; 3、结果关系包含name(重命名为new_name), instruct.dept_name, building三个属性;

select name, title
from instructor natural join teaches;

查询结果为: 1、结果关系包含的属性为instructor与teaches的并集; 2、instructor与teaches的同属同值的2个元组合成1个元组,插入结果关系; 3、结果关系只保留1个相同属性;

select name, title
from instructor join teaches using (ID);

select name, title
from instructor join teaches on instructor.ID = teaches.ID;

查询结果为: 1、结果关系包含的属性为instructor与teaches的并集; 2、instructor与teaches的ID属性同值的2个元组合成1个元组,插入结果关系; 3、结果关系只保留2个ID属性;

select name
from instructor natural left outer join teaches;
where course_id is null;

查询结果为: 1、结果关系包含的属性为instructor与teaches的并集; 2、instructor与teaches的同属同值的2个元组合成1个元组,插入结果关系; 3、instructor中无法匹配的元组也插入结果关系,它的来自instructor的属性保留,来自teaches的属性赋值为null; 4、结果关系只保留1个相同属性;

select dept_name
from department
where building like '%Waston%'

查询结果为: 只输出building属性为包含Waston的字符串的元组;

like 'ab\%cd%' escape '\'

匹配所有以“ab%cd”开头的字符串;

like '___'

匹配只含3个字符的字符串;

like '___%'

匹配至少含3个字符的字符串;

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

查询结果为: 1、先按salary降序,salary相同的再按name升序; 2、结果关系包含instructor所有属性;

select course_id, semeter, year, sec_id, avg(tot_cred)
from takes natural join student
where year = 2009
group by course_id, semeter, year, sec_id
having count (ID) >= 2;

查询结果为: 1、首先takes与student自然连接; 2、只输出year属性值为2009的元组; 3、按course_id, semeter, year, sec_id都相同的为一组; 4、只输出一组中至少有2个ID属性值(如果ID不为null,等价于至少2个元组)的那些组; 5、结果关系包含course_id, semeter, year, sec_id, tot_cred,其中tot_cred为每组的平均值;

集合操作: union,union all;intersect,intersect all;escape,escape all;

嵌套子查询: 1、集合成员资格:in,not in; 2、集合比较:some, all; 3、空关系测试:exist, not exist; 4、重复测试:unique, not unique; 5、from子句中嵌套; 6、with子句构造临时关系;

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

从instructor关系中删除工资少于平均工资的元组;

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

1、从student中找出dept_name = ‘Music’ 且tot_cred > 144的元组; 2、只保留ID, name属性; 3、把这些元组集合插入course;

update student S
set tot_cred = (
    select case
        when sum(credits) is not null then sum(credits)
        else 0
        end
    from takes natural join course
    where S.ID = takes.ID and takes.grade <> 'F';

更新student中的tot_cred属性,赋值为: 1、takes与course自然连接; 2、只输出S.ID = takes.ID 且 takes.grade 不为 ‘F’的元组; 3、计算这些元组的creditss属性的累加和,若结果为null则返回0;

create view physics as
    select course, course_id, building, room_number
    from course, section
    where course.course_id = section.course_id and course.name = 'Physics';
    
create view physics_fall_2009_watson as
    select course_id, room_number
    from physics_fall_2009
    where building = 'Watson';

创建视图和使用视图的例子;

日期时间: 1、date:‘2001-04-25’; 2、time:‘09:30:00’; 3、timestamp:‘2001-04-25 10:29:01.45’ 4、字符串转换成日期时间:cast s as t; 5、提取:extract(year/month/day/hour/minute/second from t); 6、获取日期时间:current_date、current_time、localtime、current_timestamp、localtimestamp; 7、间隔interval:date1 - date2;

create index ID_index on student(ID);

在student关系的ID属性上建立索引,名为ID_index;

create type Dollars as numeric(12,2) final;

创建自定义类型;

create table temp like instructor;

创建一个与instructorb模式相同的空关系;

create table temp as
    (select *
    from instructor
    where dept_name = 'Music')
with data;

创建一个与instructorb模式相同,且含数据的关系;