1 概要

数据查询 select
数据定义 create drop alter
数据操纵 insert update delete
数据控制 grant revoke

三级模式

视图是由一个或几个基本表（视图）导出的表，由create view命令创建。

存储文件：数据库文件，由若干基本表组成。

数据定义：

操作对象	创建	删除	修改
表	create table	drop table	alter table
视图	create view	drop view
索引	create index	drop index

2 修改基本表

一、修改表名

alter table [旧表名] rename to [新表名]

二、修改列名

alter table [表名] rename column [旧列名] to [新列名]

三、修改列的数据类型

alter table [表名] modify [列名] [新数据类型]

四、插入列

alter table [表名] add [列名] [数据类型]

五、删除列

alter table [表名] drop [列名]

删除基本表：提示删除失败，有外键约束，则先删除外键约束，再删除表。
具体步骤：
1.先查出外键约束名：

select name 
from sys,foreign_key_columns f join sys.objects o on f.constraint_object_id = o.object_id
where f.parent_object_id = object_id('表名')

2.然后删除外键约束：

alter table [表名] drop constraint [外键约束名]

3 建立与删除索引

create [unique] [clustered] index [索引名] on [表名] ([列名], [排序方式])

1.排序方式有ASC升序和DESC降序，缺省值为ASC
2.unique表明每一个索引值只对应唯一的一个元组，即索引值相同的元组只索引一次
3.clustered表明要建立的索引是聚簇索引，聚簇索引是指索引项的顺序与表中元组的物理顺序一致

删除索引

drop index [表名] [索引名1], ... , [索引名n]

注：删除基本表后，基本表的定义、表中数据、索引，以及由此表导出的视图的定义都被删除。

4 数据查询

select [all/distinct] [目标列名或表达式] from [表名或视图名]
[where [条件表达式] [group by [列名] [having [条件表达式] ] ] ]
[order by [列名] [ASC/DESC] ]

all与不填相同，可以查询出有重复的元组
distinct是只能查询不重复的元组
group子句要求将结果按列名的值进行分组
group子句带having短语时，输出满足指定条件的组

例：

select Customer, sum(orderPrice) from Orders group by Customer

会将同一个人的账单金额相加

一、简单的选择与投影查询
1.无条件查询：不使用where，查询全部值
2.条件查询：
(1) 比较条件：

where Sdept = '数学'
where Sage > 18 AND Sage <= 22

(2) 谓词条件：

between ... and ... 包含等于
not between ... and ... 不包含等于
where Sdept in ('自动化', '数学', '计算机')
[not] like '匹配串' [escape '换码字符（转义用）']

① %：代表任意长度的字符串，a%b 表示以a开头以b结尾
② _：代表任意单个字符或一个汉字

空值检查：is null、is not null
如：

where Grade is null

注：is不能用等号代替，Grade = null 是错误的。
3.查询结果排序：

select * from Students order by Sdept, Sage DESC

4.使用集函数：
min() 最小值、max() 最大值、sum() 总和、count() 计数、avg() 求平均值
例：求C01号课程的学生平均成绩

select avg(Grade) from Reports where Cno = 'C01'

5.查询结果分组：
例：查询选修了3门或3门以上课程的学生学号Sno

select Sno from Reports
group by Sno having count(Cno) >= 3

二、连接查询
1.不同表之间的连接查询

select Student.*, Reports.* from Students, Reports
where Students.Sno = Report.Sno

2.自身连接查询
例：求间接先修课

|Cno|Cname|Cpno|Ccredit
-|-|-|-|-
1|A|DB|B|4
2|B|OS|C|4
3|C|Data Structure|D|4
4|D|CM|E|6

select C1.Cno, C2.Pno from Courses C1, Courses C2 where C1.Cpno = C2.Cno

结果：

|Cno|Cpno
-|-|-
1|A|C
2|B|D
3|C|E
3.外连接

select * from Students left join Reports
on Students.Sno = Reports.Sno

以Reports表为基准，即使Students表没有与之匹配的记录，其结果表中也要求包括表Reports的所有元组，老Students表中没有与之匹配的记录，结果表中涉及表Students的属性列全部取空值。
三、嵌套查询
1.带谓词in的嵌套查询
例：查询选修编号为"C02"的课程的学生姓名(Sname)和所在系(Sdept)

select Sname, Sdept from Students
where Sno in (select Sno from Reports where Cno = 'C02')

例：查询与“李伟”在同一个系学习的学生学号(Sno)、姓名(Sname)和系名(Sdept)

select Sno, Sname, Sdept from Students
where Sdept in (select Sdept from Reports where Sname = '李伟')

例：查询选修课程名为“数据结构”的学生学号(Sno)和姓名(Sname)

select Sno, Sname from where Sno in
(select Sno from Reports where Cno in
(select Cno from Courses where Cname = '数据结构') )

2.带有比较运算符的嵌套查询
例：查询与“李伟”在同一个系学习的学生学号(Sno)、姓名(Sname)和系名(Sdept)

select Sno, Sname, Sdept from Students
where Sdept = (select Sdept from Students where Sname = '李伟')

3.带谓词any或all的嵌套查询
例：查询非自动化系的不超过自动化系所有学生的年龄的学生姓名(Sname)和年龄(Sage)

select Sname, Sage from Students
where Sdept <> '自动化' and Sage <= all
(select Sage from Students where Sdept = '自动化')

4.带谓词exists的嵌套查询
例：查询了所有选修了编号为“C01”课程的学生姓名(Sname)和所在系(Sdept)

select Sname, Sdept from Students where exists
(select * from Reports where Sno = Students.Sno and Cno = 'C01')

exists子查询只产生逻辑值：true or false
例：查询选修所有课程的学生姓名(Sname)和所在系(Dname)

select Sname, Dname from Students where not exists
(select * from Courses where not exists
(select * from Reports where Sno = Students.Sno and Cno = Courses.Cno) )

四、集合查询
集合并 union 集合交 intersect 集合差 except
注：集合操作自动去除重复元组，如果要保留重复元组的话，必须用all关键词指明。

5 数据更新

一、插入数据
例：有一个表History_Student，与Students完全一样，试将关系Students中所有元组插入到关系History_Student中去

insert into History_Student select * from Students

二、修改数据
例：将学号为“S03”的学生年龄改为22岁，即要修改满足条件的一个元组的属性值

update Students set Sage = 22 where Sno = 'S03'

例：将所有学生的年龄增加1岁，即要修改多个元组的值

update Students set Sage = Sage + 1

三、删除数据
住：增删改操作每次只能对一个表进行，且必须注意基本表之间的参照完整性和操作顺序

delete from Reports where Sno = 'S04' and Cno = 'C02'

6 视图操作

视图是从一个或几个基本表或视图导出的一个虚拟表，视图可以和基本表一样被查询、被删除。
一、定义视图

create view [视图名] [列名]
as [select子查询语句] [with check opinion]

说明：
1.select语句可以是任意复杂的语句，但通常不允许含有order by子句。
2.with check opinion表示用视图进行update、insert、delete操作时要保证更新，插入或删除的元组满足视图中定义的谓词条件。
3.组成视图的属性列名要么全部省略，要么全部都明确指定，在下列三种情况必须明确指定列名。
①某个目标列不是单纯的属性名，而是集函数或表达式
②多表连接导出的视图中有几个同名列作为该视图的属性列名
③需要在视图中为某个列使用新的更合适的名字

例：简历数学系学生的视图，且在进行修改和插入操作时仍需保证该视图只对教学系的学生，视图的属性名为Sno，Sname，Sage，Sdept

create view C_Student as
select Sno, Sname, Sage, Sdept from Students where Sdept = '数学'
with check opinion

例：建立学生的学号Sno，姓名Sname，选修课程名Cname及成绩Grade的视图

create view Student_CR as
select Students.Sno, Students.Sname, Cname, Grade
where Students.Sno = Reports.Sno and Reports.Cno = Courses.Cno

二、删除视图

drop view [视图名]

说明：由该视图导出的其他视图在用户使用时会出错，需要一一删除。
三、查询视图
与基本表的查询相同。

四、更新视图
1.更新视图的含义：指通过视图来insert、delete、update
由于视图是虚拟表，并不实际存储数据，因此对视图的更新，系统将自动转换为对基本表的更新。
2.更新视图的限制：由于有些视图的更新操作不能唯一地转换成对基本表有意义的更新操作，所以有如下限制规则：
①不允许的情况：如果一个视图是从多个表通过连接操作导出的
②不允许的情况：如果在导出视图的过程中，使用了分组和集函数
③允许的情况：如果一个视图是从单个基本表使用选择和投影操作导出，且视图的属性集包含了基本表的一个候选键（这种视图称为“行列子集视图”）

7 SQL的数据控制

一、授权

grant [权限] [on [对象类型] [对象名] ] to [用户1] ... [用户n]
[with grant option]

不同类型的操作对象的操作权限

对象	对象类型	权限
属性列 视图	table	select, insert, update, delete, all privileges
基本表	table	select, insert, update, delete, alter, index, all privileges
数据库	database	createtab

注：接受权限的用户可以是一个或多个具体用户，也可以是public，全体用户有with grant option子句的授权，使获得这个权限的用户可以授权给别人。
例：把对基本表Students的查询权限授权所有用户

grant select on Students to all

例：把查询Students表和修改学生学号Sno的权限授给用户User3

grant select, update(Sno) on Students to User3

二、收回权限

revoke 权限 [on [对象类型] [对象名] ] from [用户]
revoke all privileges on Students from User1

超键：在关系中能唯一标识元组的属性集称为关系模式的超键。
候选键：不含有多余属性的超键称为候选键。
主键：用户选作元组标识的一个候选键称为主键。

8 概念数据模型——E-R模型

三类数据模型在数据库设计过程中的地位和关系

E-R图：实体+属性=实体型
实体集：若干同型实体的集合称为实体集

9 常用的结构数据模型

一、层次模型：只能表示1:n；m:n也能，但是不好表示。

层次模型

二、网状模型：复杂

网状模型

三、关系模型

10 三级模式

三级模式
外模式	view	用户可见 模式的子集
模式	table	用户不可见
内模式	数据库物理结构和存储方式的描述	存储文件