数据库系统笔记5：SQL

基本操作

• 创建表

create table Students (
    stu_id     int, primary key,    //声明主键
    stu_name   char(20), not null,  //声明非空
    sex        char(1),
    birth      date
);

• 删除表

drop table Students;
drop table Students if exists;

• alter更改表结构

alter table Students add addr char(50);   //增加属性
alter table Students drop column addr;    //删除属性

• 插入数据

//插入数据
insert into Students(stu_id, stu_name, sex, birth) values(1001, 'Roslin', 'f', '2001-12-25');
//不声明时按表属性的顺序插入
insert into Students values(1001, 'Roslin', 'f', '2001-12-25');
//也可以按自己想要的顺序插入
insert into Students(stu_id, sex, stu_name, birth) values(1001, 'f', 'Roslin', '2001-12-25');
//可以插入部分数据，其他的空值
insert into Students(stu_id, stu_name) values(1001, 'Roslin');

//插入多行数据
insert into Students(stu_id, stu_name, sex, birth) values
    (1001, 'Roslin', 'f', '2001-12-25'),
    (1002, 'Tina', 'f', '2002-06-01'),
    (1003, 'Matty', 'm', '2001-10-01');

• 删除数据

delete from Students where stu_id = 1003;  //删除学号为1003的记录
delete from Students;                      //删除全表

• 更新数据

update Students set stu_name = 'Henry' where stu_id = 1003;
update Students set stu_id = stu_id + 1000;

• 查找数据（详细的查询将在之后介绍）

select * from Students;    //查看Students表中的所有信息

完整性约束

主键的声明

• primary key：不允许重复，不允许出现Null，只能有一个主键
• unique：不允许重复，可以出现Null，可以有多个unique

create table Students(
    stu_id    int, primary key,
    stu_name  char(20), not null
);

create table Students(
    stu_id    int,
    stu_name  char(20), not null,
    primary key(stu_id)
);

//当主键是两个及以上的属性集合时，不能按第一种方法声明
create table Students(
    stu_id    int,
    stu_name  char(20),
    primary key(stu_id, stu_name)
);

create table Students(
    stu_id    int, unique,
    stu_name  char(20), not null
);

//下面这两种方法不同
create table Students(
    stu_id    int, unique,
    stu_name  char(20), unique
);

create table Students(
    stu_id    int,
    stu_name  char(20),
    unique(stu_id, stu_name)
);

外键的声明

//选修表中，学号和课程号都是外键
//注意，在学生表和课程表中，学号和课程号必须被声明为主键
create table Select_course (
    stu_id       int, references Students(stu_id),
    course_id    int, references Courses(course_id),
    score        float, not null,
    primary key(stu_id, course_id)
);

create table Select_course (
    stu_id       int,
    course_id    int,
    score        float, not null,
    primary key(stu_id, course_id),
    foreign key(stu_id) references Students(stu_id),
    foreign key(course_id) references Courses(course_id)
);

由于外键的约束，对于表的数据进行更新、删除操作时， 可能会被拒绝。为了解决这一问题，我们在声明外键时可以同时声明三种模式：

• Default（默认）：不允许修改外键所参照的属性
• Cascade（级联）：修改外键所参照的属性时，同时修改本表
• Set NULL：修改外键所参照的属性时，在本表中将所对应的外键设置为空

注意，可以对更新和删除设置不同的模式

//更新的同时删除该表中的相关行
foreign key(stu_id) references Students(stu_id) on delete cascade;
//更新的同时更新该表中的相关数据
foreign key(stu_id) references Students(stu_id) on update cascade;

foreign key(stu_id) references Students(stu_id) on delete set null;
foreign key(stu_id) references Students(stu_id) on update set null;

也可以通过check声明外键。check和foreign key的区别是，check只在插入或更新该表时生效，而不在参照表的元组被更新或删除时生效

//基于属性的check
create table Select_course (
    stu_id    int, check(stu_id in (select stu_id from Students)),
    ......
);

//基于元组的check
create table Select_course (
    stu_id    int, 
    ......
    check(stu_id in (select stu_id from Students))
);

alter table Select_course add check(stu_id in (select stu_id from Students));

当然，check也可以用来声明其他的约束

create table Products (
    ......
    price     float, check(price <= 5.00),
    ......
);

查询操作

基础查询操作

select * from Students;    //查看学生表中的所有信息
select stu_name from Students where stu_id = 1001;   //查找学生表中学号为1001的学生姓名
select stu_name as name from Students where stu_id = 1001;    //可以换成自己想要的属性名
select stu_name from Students where stu_id >= 1002 and sex = 'm';    //多个条件用AND连接

 包含字符的查询

select * from Students where stu_name like '林%';    //姓林的学生
select * from Students where stu_name like '%林%';   //名字中有林的学生
select * from Students where stu_name like '林_';    //姓林且名字只有两个字的学生
select * from Students where stu_name like '_林_';   //名字有三个字且中间为林的学生

排序

order by stu_id asc;    //升序（默认）
order by stu_id desc;   //降序

多表查询

//两表的笛卡尔积
select * from Students, Select_course;

//只有属性完全相同的两条记录会被并成一条（更符合逻辑）
//注意，两表中的这两个属性必须名字、数据类型一模一样
select * from Students natural join Select_course;  

表的副本

//查找选修了同一门课程的两个学生学号
select s1.stu_id, s2.stu_id
from Select_course s1, Select_course s2    //表的副本，我们可以随意给它们命名
where s1.course_id = s2.course_id          //课号相同
    AND s1.stu_id < s2.stu_id;             //学号不同，否则会查找到自己和自己

子查询

//查找选择课号为1234的学生姓名
//=只会有一个值，即只有一个学生选择课号为1234的课程
select stu_name
from Students
where stu_id = (
    select stu_id
    from Select_course
    where course_id = 1234
    );

//IN是一个集合，即可能有很多学生选择课号为1234的课程
select stu_name
from Students
where stu_id in (
    select stu_id
    from Select_course
    where course_id = 1234
    );

//查找只选修了一门课的学生学号
//EXISTS返回的是真/假
select stu_id
from Select_course s1
where not exists (
    select *
    from Select_course s2
    where s1.course_id = s2.course_id
        and s1.stu_id <> s2.stu_id
    );

//查找学号最大的学生姓名
select stu_name
from Students
where stu_id >= all(
    select stu_id
    from Students
    );

//查找学号不是最小的学生姓名
select stu_name
from Students
where stu_id > any(    //ANY是至少一个的意思
    select stu_id
    from Students
    );

聚合查询

//注意，集合中不会有重复的元素
union        //并集
intersect    //交集
(select * from Students) union (Select * from Students)
(select * from Students) intersect (Select * from Students)

distinct     //查找不重复的
//查找只选修了一门课的学生学号
select distinct stu_id
from Select_course;

sum()        //括号中属性的和
avg()        //平均值
min()        //最小值
max()        //最大值
count()      //计数
count(*)     //包括NULL

//仅使用上面的操作会对括号内属性的每一行生效
//使用GROUP BY即可根据某一属性分组计算
//如下面这个例子，查找每个学生所有课程的平均分
select stu_id, avg(score)
from Select_course
group by stu_id;

//HAVING对分组后的数据进行操作
//查找选修2门课以上的学生和对应的课程数目
select stu_id, count(*)
from Select_course
group by stu_id
having count(*) > 2;

特殊操作

Assertion 断言

在每次更改的时候都check，如果不满足条件，则无法修改数据，即永远保持check后面为真

create assertion NoFailExam
check(
    not exists(
        select score
        from Select_course
        where score < 60);

Trigger 触发器

当某个操作发生后，如果满足条件，就触发某个行为。可以用触发器来解决外键约束的问题，如下面这个例子：

//delimiter用于改变SQL语句中的分隔符
delimiter //
create trigger ScoreTrig
before insert on Select_course for each row    #在对选修表进行插入之前
begin
if new.stu_id not in (select stu_id from Students) then    #如果新插入的学号不在学生表李
insert into Students(stu_id, stu_name) values(new.stu_id, 'A');    #在学生表中插入新学号
end if;
end; //
delimiter ;
//把分隔符改回成;

Store 存储程序

Procedure

//打印是否有多于10个选修课号为1234的同学成绩在90分以上
create table print(printInfo char(50));

delimiter \\
create procedure printProc(inout printInfo char(50))
#inout参数为输入、输出，in为输入，out为输出
begin
if (
    select count(*)
    from Select_course
    where course_id = 1234 and score > 90) >10 then
    set printInfo = 'There are more than 10 people who get 90+ point';
else set printInfo = 'There are less than 10 people who get 90+ point';
end if;
end; \\
delimiter ;

call printProc(@a);
select(@a);

Function

create function hello(s char(20))
returns char(50) deterministic
return concat('Hello, I am', s, '!');
select hello('Roslin');

View 视图 

• 视图是从一个或几个基本表（或视图）导出的虚拟表，可以简化用户查询操作，对数据提供安全保护
• 执行create view语句时只是定义了视图，并没有实际执行select语句
• 对视图进行查询时，才按照视图的定义从基本表中查出数据
• 删除视图时，不影响基本表；删除基本表时，其视图也不可用

create view Database
as  select stu_id, stu_name, score
    from Students s1, Select_course s2
    where s1.stu_id = s2.stu_id
        and s2.course_id = 1234;

select *
from Database
where score > 90;

更新视图的限制

• 如果视图由一个基本关系的简单查询生成，而且它还包含了基本关系中的主关键字或是候选关键字，则可以通过这个视图进行更新操作
• 不允许对涉及到多个基本关系的视图进行更新
• 如果视图的生成中涉及到聚集或是分组操作，则不允许通过这个视图进行更新

Index 索引

• DBMS系统自动选择合适的索引结构，索引创建后，也由系统负责使用和维护
• 如果经常查询的条件包含多个属性，最好创建一个多属性索引

create index i_stu_id on Students(stu_id);
create index i_course_id, i_score on Select_course(course_id, score);

drop index i_stu_id on Students;

 Authorization 授权

• 可以对增删改查操作的全部或部分进行授权

//Roslin可以在学生表上进行修改操作
grant update on Students to Roslin;

//Roslin可以在学生表上进行查询操作，并且可以把这个权限授权给别人
grant select on Students to Roslin with grant option;

• 收回权限只收回本人对A的相应权限，别人对A的授权没有影响。那么如果A把本人授权给他的权限又授权给了别人呢？
  • cascade：A授权给别人，以及这之后的授权都要收回
  • restrict：如果A授权给了别人，本人收回A的权限的操作失败

//Roslin在学生表上进行修改操作的权限被收回了
//但Roslin可能还可以在学生表上进行修改操作，因为别人给她授权了
revoke update on Students from Roslin;

有向图 

我们引入有向图来解决授权的问题

• 每一个节点代表某人拥有某项权限，注意，这项权限有一点点不同，都要重新画一个节点，例如（A拥有P权限）和（A拥有P权限并可以授权给他人）是不同的
• A拥有P权限，表示为AP；A拥有P权限并可以授权给他人，表示为AP*；A是P权限的最早来源者（管理员），则表示为AP**
• A将P权限授权给B，则在两节点中间加一条有向边
• 如果A拥有P权限，那么必须有一条从XP**到AP的有向路径
• A将P权限从B收回
  • cascade
    • 删除AP到BP的边
    • 检查每个节点是否有一条从**到它的有向路径
    • 如果没有，那这些节点也要被删除
  • restrict：如果B不再有P的权限，且有边从BP出发，则拒绝收回权限的操作