数据库系统概论笔记

参考视频数据库系统概论

---

绪论

几个概念

数据库的四个基本概念：
数据：描述事物的符号记录。

数据库：概括地讲，数据库数据具有永久存储、有组织和可共享的三个基本特点。
严格地讲：数据库是⻓期储存在计算机内、有组织的、可共享的⼤量数据的集合。数据库中的数据按⼀定的数据模型组织、描述和储存，具有较⼩的冗余度、较⾼的数据独⽴性和易扩展性，并可为各种⽤户共享。

数据库管理系统：是位于⽤户和操作系统之间的⼀层数据管理软件，和操作系统⼀样是计算机的基础软件。
主要功能：
1、数据定义功能
2、数据组织、存储和管理
3、数据操纵功能
4、数据库的事务管理和运⾏管理
5、数据库的建⽴和维护功能
6、其他功能（通信功能，数据转换功能，互访和互操作功能等）
数据库系统：是由数据库、数据库管理系统（及其应⽤开发⼯具）、应⽤程序和数据库管理员（DBA）组成的存储、管理、处理

数据库发展阶段

数据模型

ER图

常用的数据模型

1.层次模型

2.网状模型

3.关系模型

数据库系统结构

关系型数据库

关系模式

分类

关系代数运算符

投影

连接

自然连接

外连接&左外连接&右外连接

sql语法

数据类型

char(n),character(n)				长度为n的定长字符串
varchar(n),charactervarying(n)		最大长度为n的变长字符串
clob								字符串大对象
blob								二进制大对象
int,integer							4字节长整数
smallint							2字节短整数
bigint								8字节大整数
numeric(p,d)		 				定点数,由p位数字组成,小数点后面有d位数字
decimal(p,d),dec(p,d)				同numeric
real								取决于机器精度的单精度浮点数
double precision					取决于机器精度的双精度浮点数
float(n)							可选精度的浮点数,精度至少位n位数字
boolean								逻辑布尔值
data								日期
time								时间
timestamp							时间戳类型
interval							时间间隔类型

表操作

create table表名(字段名 类型 字段约束，字段名 类型 字段约束，字段名 类型 字段约束）//创建表
drop table <表名> <CASCADE|RESTRICT>;
alter table <表名> [add [column] <新列名><数据类型>[完整性约束]][add <表级完整性约束>]
alter table test add email VARCHAR(255);

索引操作

create [unique][cluster] index <索引名> on <表名>(<列名>[<次序>]...);

ALTER INDEX<旧索引名>RENAME TO<新索引名>;
例子：将SC表的SCno索引名改为SCSno。
ALTER INDEX SCno RENAME TO SCSno;

DROP INDEX<索引名>;
[例]删除Student表的Stusname索引。
DROP INDEX Stusname;

查询

select * from Student;
 
等价于select Sno,Sname,Ssex,Sage,Sdept from Student;

给列起别名
select username 名字 from user;
等价于
select username as 名字 from user;
 
 
给表起别名
select username from user u;
等价于
select username from user as u;

select distinct title from edu_course;
查询   不重复    标题   从   表中

查询条件

聚合函数

AVG() - 返回集合的平均值。
COUNT() - 返回集合中的项目数。
MAX() - 返回集合中的最大值。
MIN() - 返回集合中的最小值
SUM() - 返回集合中所有或不同值的总和。

select count(title)       from  edu_course;
查询   title列中的属性个数 来自   表
包括重复，不含空NULL

select count(distinct title)               from  edu_course;
查询   title列中不重复的属性个数 来自   表
不包括重复，不含空NULL,加上DISTINCT是去除列⾥⾯重复的  来计算 

select avg(price) from  edu_course;
价格平均值

select max(price) from  edu_course;
价格最大值

select min(price) from  edu_course;
价格最小值

分组查询

select count(title)       from  edu_course;
查询   title列中的属性个数 来自   表
包括重复，不含空NULL

select count(distinct title)               from  edu_course;
查询   title列中不重复的属性个数 来自   表
不包括重复，不含空NULL,加上DISTINCT是去除列⾥⾯重复的  来计算 

select avg(price) from  edu_course;
价格平均值

select max(price) from  edu_course;
价格最大值

select min(price) from  edu_course;
价格最小值

连接

select t.* ,c.*         from edu_teacher   t        ,edu_course c  where t.id=c.teacher_id;
查询   t表全部和c表全部  从    edu_teacher表 别名t   edu_course表 别名c 连接  t的id和c的tid同

Select c1.version,c2.view_count from edu_course c1 ,edu_course c2 where c1.version=c2.view_count;

select * from edu_course ec left outer join edu_course_description ecd on ec.id=ecd.id;
 
select * from edu_course ec right outer join edu_course_description ecd on ec.id=ecd.id;
 
select * from edu_course ec inner join edu_course_description ecd on ec.id=ecd.id;

多表联查

select c.id ID ,c.title 课程名,cp.title 章节,cd.description 课程措述 from edu_course c ,edu_chapter cp,edu_course_description cd where c.id=cp.course_id and c.id=cd.id
查询   c表中id 别名ID，c表中title 别名课程名，cp表中title别名章节，cd表中的description别名课程措述    从edu_course表，别名c ,edu_chapter表，别名cp,edu_course_description表别名cd，连接 c.id=cp.course_id 和 c.id=cd.id

嵌套查询

select c.title 课程名,c.price 价格 from edu_course c where c.title in (select c2.title from edu_course c2 where c2.title="mysql速成");
 
select c2.title from edu_course c2 where c2.title="mysql速成"
在c2表中查出title="mysql速成"的表
 
select c.title 课程名,c.price 价格 from edu_course c
从c表中查出课程名和价格
 
where c.title in()
要求从c表中查出课程名在c2表中查出title="mysql速成"的表里面

ANY ALL子查询

select c.title课程名,c.price价格 from edu_course c where c.price >ALL(select c2.price from edu_course c2 where c.price between 1 and 20) ;
 
1.在edu_course表中查出价格在1到20之间的价格，组成一张表
2.edu_course中查询价格高于（）表中所有价格的行，组成一张表
3.2中表只保留title和price


select c.title 课程名,c.price价格 from edu_course c where c.price >ANY (select c2.price from edu_course c2 where c2.price between 22 and 24);
 
1.在edu_course表中查出价格在22到24之间的价格，组成一张表
2.edu_course中查询价格高于（）表中最低价格的行，组成一张表
3.2中表只保留title和price

exists子查询

select Sname from Student where NOT EXISTS 
(select * from course where NOT EXISTS 
(select * from SC where Sno=Student.Sno and Cno=Course.Cno));
 
查询他选择的所有的课程门数SC        select * from SC 。。。
查询他没有选择的所有的课程门数SC    NOT EXISTS (select * from SC 。。。
查询所有课程                      (select * from course where 。。。o));
查询没选的所有课程                 NOT EXISTS (select * from course 。。。no));

最里面的子查询是这个人没选一门课程
外面的子查询是没有这样的课程

表数据增删改

insert into 表名 [( 字段列表 )] values( 值列表 ...);
 
 //标准添加（指定所有字段，给定所有的值）
 insert into stu(id,name,age,sex,classid) values
 ( 1 , 'zhangsan' , 20 , 'm' , 'lamp138' );
 //指定 部分 字段添加值
 insert into stu(name,classid) value( 'lisi' , 'lamp138' );
 //不指定 字段添加值
 insert into stu value( null , 'wangwu' , 21 , 'w' , 'lamp138' );
 //批量 添加值
 insert into stu values
( null , 'zhaoliu' , 25 , 'w' , 'lamp94' ),
( null , 'uu01' , 26 , 'm' , 'lamp94' ),
( null , 'uu02' , 28 , 'w' , 'lamp92' ),
( null , 'qq02' , 24 , 'm' , 'lamp92' ),
( null , 'uu03' , 32 , 'm' , 'lamp138' ),
( null , 'qq03' , 23 , 'w' , 'lamp94' );

update 表名 set 字段1= 值1, 字段2= 值2, 字段n= 值n... where 条件
-- //将 id 为 11 的 age 改为 35 ， sex 改为 m 值
update stu set age=35 ,sex='m' where id= 11 ;

格式： delete from 表名 [where 条件 ]
//删除stu表中id值为100的数据
delete from stu where id=100;

视图

create view <视图名> [(<列名>...)] as <子查询> [with check option];\
例如:
create view IS_student as select sno,sname,sage from student where sdept ='IS' with check option;

drop view <视图名>[cascade]; 


select sno,sage from is_student where sage<20;


update is_student set sname ='liu' where sno = '111';
转换后的更新语句为:
update student set sname = 'liu' where sno = '111' and sdept = 'IS';

数据库安全性

概述

安全控制

权限授予

数据库角色创建

视图

审计和数据加密

数据库完整性

三大完整性

实体完整性

参照完整性

用户定义完整性

断言

触发器

删除触发器