基本操作
- 创建表
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出发,则拒绝收回权限的操作
- cascade