数据库期末复习，基础知识总结

操作表

列级的完整性约束可以定义如下约束：
Not null，default，unique，check，primary key，foreign key。
创建表的实例如下：
Create table student (
Sno char(7) primary key,
Sname char(10) not null,
Ssex char(2),
Sage tinyint,
Sdept char(20)
)
Create table course (
Cno char(6) not null,
Cname char(20) not null,
Credit tinyint,
Semester tinyint,
Primary key(cno)
)
Create table sc (
Sno char(7) not null,
Cno char(6) not null,
Grade smallint,
Primary key(Sno,Cno),
Foreign key (Sno) references student(Sno),
Foreign key (Cno) references course(Cno)
)
删除表：drop table 表名
改变表的结构：
增加一列：alter table student add spec char(10) null
修改列定义：alter table student alter column spec char(20)
删除列：alter table student drop column spec
添加约束：alter table student add [constraint<约束名>] 约束定义
alter table student add constraint PK_EMP primary key (Sno),
alter table 雇员表 add constraint UK_SID unique (电话号码)
alter table 雇员表 add constraint FK_JOB_id foreign key (工作编号) references 工作表（工作编号）
alter table 雇员表 add constraint DF_SALARY default for 工资：default 默认值for 列名
alter table 雇员表 add constraint CHK_Salary check (工资>=1000)
删除约束：alter table student drop [constraint] <约束名>

操作数据

数据查询：查询语句的基本结构如下：

Select <目标查询序列>From<数据源>
[Where<检索条件表达式>][ group by<分组依据列>][ having <组提取条件>][
order by<排序依据列>]
单表查询：select * from student
Select sname，sno，sdept from student
查询经过计算的列：select sname，2015-sage from student
起别名：select sname name 2015-sage ‘出生年月’ from student
消除取值相同的行：select distinct sno from sc ；
查询满足条件的元组：select sname from student where sdept = ‘计算机系’
Select sname，sage from student where sage<20 ;
Select distinct sno from sc where grade<60
确定范围：select sname ，sdept， sage from student where sage between 20 and 23
（包括边界）等价于 select sname，sdept，sage from student where sage>=20 and sage<=23
确定集合：select sname，ssex from student where sdept in （’信息系’，‘数学系’，‘计算机’）；
等价于：select sname，ssex from student where sdept=‘信息系’ or sdept=‘数学系’or dept=‘计算机’；
字符串匹配：select * from student where sname like ‘张%’;
Select * from student where sname like ‘[张刘李]%’；
Select * from student where sname like ‘[小大]%’；
Select * from student where sname not like ‘[王张]%’；
Select * from student where sname like ‘王’;
多重条件查询：select * from student where sdept=’计算机’ and sage <20 ;
对查询结果进行排序(asc表示升序desc表示降序)：select * from student order by sage asc；
Select * from student where cno=’c002’ order by grade desc ;
Select * from student order by sdept asc,sage desc ;
使用聚合函数汇总数据：（对一组值进行计算并返回一个单值）
提供的聚合函数有：count（）统计表中元组的个数
Count（[distinct]<列名>）：统计本列非空列值的个数
Sum（<列名>）：计算列值总和
Avg（<列名>）：计算列值平均值
Max（<列名>）：求列值最大值
Min（<列名>）：求列值最小值
Select count（）as 学生人数 from student ；
Select count （distinct sno） 选课人数 from sc ；
Select count（）as 选课门数 ，sum（grade）as 总成绩 from sc where sno = ‘1512101‘；
Select avg（grade） as 平均成绩 from student where cno=‘c001‘；
Select max（grade）as 最高分，min（grade）as 最低分 from sc where cno=‘c001‘；
Select count（）as 选课门数，count（grade） as 考试门数，max（grade）最高分，min（grade）最低分，avg（grade）平均分 from sc where sno=‘1512101‘；
对查询结果进行分组：
统计每门课程的选课人数，列出课程号和选课人数：
Select cno as 课程号，count（sno）as 选课人数 from sc group by cno ；
Select sno 学号，count（） 选课门数，avg（grade）平均成绩 from sc group by sno ；
Select sdept，count（）女生人数，avg（sage）as 平均年龄 from student
Where ssex=‘女‘ group by sdept ；（先执行where子句，再分组）
按多列分组：统计每个系的男生人数和女生人数，以及男生的最大年龄和女生的最大年龄，结果按系名升序排序：
Select sdept，ssex，count（）人数，max（sage）最大年龄 from student group by
Sdept，ssex order by sdept asc ；
使用having子句：having子句是对分组后的结果再进行筛选。
查询选修了3门以上课程的学生的学号和选修课门数：
Select sno，count（）选课门数 from sc group by sno having count（）>3 ;
统计每个系的男生人数，只列出男生人数大于等于2的系：
Select sdept，count（） 人数 from student where ssex=‘男‘ order by sdept having
count（*）>=2 ;
注意：
Where子句用来筛选from子句中指定的数据源所产生的行数据
Group by 子句用来对经过where子句筛选后的结果数据进行分组
Having子句用来对分组后的结果 数据再进行筛选。
多表查询：
内连接：在使用内连接时，如果两个表的相关字段满足连接条件，则从两个表中提取数据并合并成新的记录。From 表1 join 表2 on <连接条件>
Select student.sno ,sname,ssex,sage,sdept,cno,grade from student
Join sc on student.sno = sc.sno ;
Select student.sno ,sname,cno,grade from student join sc
On student.sno = sc.sno where sdept = ‘计算机’;
Select Sname,sdept from student s join sc on s.sno = sc.sno
Join course c on c.cno = sc.cno
Where cname = ‘java’ ;(涉及到三个表，每连接一个表就要加一个join)；
用有分组的多表连接查询：select sdept ,avg(grade) from student s join
Sc on s.sno = sc.sno group by sdept ;
外连接：在内连接中，只有满足连接条件的元组才能作为条件输出。而外连接只限制一张表中的数据满足的条件，而另一张表中的数据可以不满足连接的条件，以null的形式输出。
From 表1 left | right join 表2 on <连接条件>，左连接的含义是限制表2中的数据必须满足条件，不管表一的数据是否满足条件，均输出表一的内容。
Select student.sno,sname,cno,grade from student left outer join sc
On student.sno = sc.sno ;
使用Top限制结果集：top n [percent] [with ties],n为非负整数，percent为百分之n行的数据，with ties 表示并列的结果。
子查询：如果一个select 语句嵌套在一个select，insert，update或delete语句中，称为子查询。
Select sno,sname,sdept,from student where sdept in
( select sdept from student where sname = ‘刘晨’)；
Select sno,sname from student where sno in
(select sno from sc where grade >90);

数据更改：

插入数据：insert into student values（‘1521104‘，’陈东‘，’男‘，18，’‘信息系）；
更新数据：update student set sage=21 where sno = ‘1512101‘；
Update sc set grade = grade+5 where sno in
（select sno from student where sdept = ‘计算机‘）；
删除数据：delete from sc ；
Delete from sc where grade<60;
Delete from sc where grade <60 and sno in
(select sno from student where sdept = ‘计算机系’);

视图

定义视图：create view <视图名> as select 语句；
定义单源表视图：
Create view IS_Student
As select sno,sname,sage from student where sdept = ‘信息系’ ；
定义多源表视图：
Create view V_IS_S1 (Sno,sname,grade) as
select student.sno,sname,grade from student
join sc On student.sno = sc.sno
Where sdept=’信息系’ and sc.cno=’c001’ ;
在已有的视图上定义新视图：
Create View v_IS_s2 as
Select sno ,sname,grade
From V_IS_S1 where grade >= 90 ;
再查询语句的前面加上 create view 视图名 as 即为视图。
修改视图：alter View 视图名 as select语句；
删除视图：drop view 视图名；

数据库范式理论

假设有关系模式SC（Sno，sname，cno，credit，grade）个属性分别为学号，姓名，课程号，学分，成绩。主码为（Sno，Cno），则有函数依赖：
Snosname 姓名函数依赖学号
（Sno，cno）Sname 姓名部分函数依赖与学号和课程号
（sno，cno）grade 成绩完全函数依赖与学号和课程号
还有传递依赖。

关系模式中的码：

有关系模式：学生（学号，姓名，性别，身份证号，年龄，所在系）
候选码：学号，身份证号
主码：学号或者身份证号
主属性：学号，身份证号
非主属性：姓名，性别，年龄，所在系
外码：用于建立关系表中关联关系的属性（组）称为外码。
范式：
第一范式：不包含重复组的关系是第一范式的关系。
第二范式：R中的每个非主属性都完全函数依赖于主码，则为第二范式。存在部分函数依赖的关系是造成不是第二范式的最重要的原因。
第三范式：所有的非主属性都不传递依赖与主属性，如果存在非主属性对主码的传递依赖，则不是第三范式。

事务

事务的四个特征：原子性，一致性，隔离性，持久性。
事务的结束标记为，正常结束：commit。异常结束：rollback
Begin transaction
Update 支付表 set 账户余额 = 账户余额-n where 账户号=‘A’
Update 支付表 set 账户余额 = 账户余额+n where 账户号=‘B’
Commit
锁
锁的基本类型有两种：排它锁也成为X锁，“写锁”。共享锁，也称为S锁，“读锁”。
共享锁：若事务T给数据对象A加了S锁，则事务T可以读A，但不能修改A.其他事务可以再给A加S锁，但不能加X锁，直到T释放了A上的S锁为止。
排它锁：若事务T给数据对象A加了X锁，则允许T读取和修改A，但不允许其他事务再给A加任何类型的锁和进行任何类型的操作。