数据库基本知识

?????????????????????????????????????????????????????????????????? 数据库基本知识

1.数据模型：

?????? 层次模型（IMS是层次模型数据库系统的代表）、网状模型（DBTG是网状数据库系统代表）和关系模型是三种重要的数据模型。这三种模型是按其数据结构而命名的。前两种采用格式化的结构。在这类结构中实体用记录型表示，而记录型抽象为图的顶点。记录型之间的联系抽象为顶点间的连接弧。整个数据结构与图相对应。对应于树形图的数据模型为层次模型；对应于网状图的数据模型为网状模型。关系模型为非格式化的结构，用单一的二维表的结构表示实体及实体之间的联系。满足一定条件的二维表，称为一个关系（见关系数据库）。

2.关系数据库：

??????? 在关系模型中，关系用来指代表，元组用来指代行，属性代表列。用关系实例来表示一个关系的特定实例（实际表）。

???????? 超码：一个或多个属性的集合，在关系中唯一的标识一个元组（类似于表中主键的作用），最小的超码叫候选码。

???????? 主码：数据库设计者选中的用来区分不同元组的候选码（表中的主键）。它的值从不或极少的发生变化。

???????? 外码：一个关系模式（r1）中的外码是另一个关系模式（r2）的主码（类似与表中外键）。r1称为外码依赖的参照关系，r2叫做外码的被

?????????????????? 参照关系。

3.基本数据类型：

???????? char,varchar,int,smallint,double,float。

4.基本SQL:

?

关系：

department(dept_name(主键),building,budget)

course(course_id(主键),title,dept_name(references department),credits)

instructor(ID(主键),name,dept_name(references department),salary)

section(sec_id,course_id(references course),semester,year,building,room_number,time_slot_id)? course_id,sec_id,semester,year一起做主键

teaches(ID(references instructor),course_id(references course),sec_id(references section),semester(references section),year(references section)) ID,course_id,sec_id,semester,year一起做主键

?

创建关系：create table 关系名(属性1 数据类型，属性2 数据类型，...，约束1，约束2，...);?

???????????????? create table department(dept_name varchar(20),building varchar(15),primarykey(dept_name));

alert:为已有关系添加（属性值为空）或删除属性；??? alert 关系名 add 属性 数据类型；???? alert 关系名 drop 属性；

插入：insert into 关系名(属性1，属性2)关系 values(值1，值2...)?? 当没有属性时默认插入所有属性

更新：1.update 关系名 set 属性1= 值1，属性2= 值2... where 属性约束

??????????? 2.case结构：update instructor set salary = case when salary <= 10000 then salary*1.5 else salary *2 end

????????? ? 3.子查询用于更新中

???????????????? update 关系名? S (一个变量，用来指代某一个元组) set 属性 = （select sum(credicts) from takes natural join couse where S.ID = takes.ID and takes.grade <>? ‘F‘ and takes.grade is not null）;???

???????????????? 默认没有完成课程(既不是空也不是F)该属性设为空，如果要设为0 的话可以把select sum(credits) 替换为：

???????????????? select case when sum(credits) is not null then sum(credits)

??????????????????????????? else 0

????????????????????????????? end

删除： delete from 关系名 where 约束???????????? 删除符合条件的元组

??????????? delete from 关系名????????????????????????????????? 删除所有元组，关系本身仍存在

??????????? delete from instructor where salary <(select avg(salary) from instructor);

查询：

单关系查询：select 属性 from 关系 where 属性约束 order by 排序；

多关系查询：select 关系1.属性，关系2.属性 from 关系1，关系2 where 关系1.属性 = 关系2.属性；? 属性唯一时可省略 关系1. 与 关系2.

自然连接：将两个关系中的非重复属性按照相同属性（一般是外键和另一个关系的主键）进行匹配连接。natural join;join...using...

??????????????? select name,courseid from instructor natural join teaches = select name,couseid from instructor,teaches where instructor.ID = teaches.ID

???????????????? 自然连接与where组合使用

??????????????????? select name,title from instructor natural join teaches,course where teaches.course_id = course_id;?????

??????????????? 为了避免多个关系在自然连接是有多个属性相同我们使用using来明确指示

??????????????? select name,title from(instructor natural join teches) join course using (course_id)? 避免了course与前两个关系的连接既要course_id相同还要dept_name相同。等同于

??????????????? select name,title from instructor natural join teaches,course where teaches.course_id = course_id;

并运算：(select...09年秋课程 ) union(select... 10年春课程 )?????? 将两个查询的结果做并运算，union all保留重复(同一个课程一个学期可能开多个，但由不同老师交，这时就有重复)。

交运算： (select...09年秋课程 ) intersect(select... 10年春课程 )?? 将两个查询结果做交运算，intersect all保留重复。

差运算：(select... 09年秋课程 ) exceptt(select...10年春课程)???? 在第一个查询结果中但不再第二个查询结果中，except all 保留重复

基本聚集函数:(avg,min,max,sum,count(计算元组个数，不能和distinct一起使用))

分组聚集：group by...having...?? 对查询的结果进行分组统计，并排除某个

????????????? 没有出现在group by 字句中的属性如果出现在select子句中只能出现在聚集函数内部

?? select dept_name,ID,avg(salary) from instruct group by dept_name;这是错误的要去掉ID,因为每个教师都要不同的ID,而每个分组（部门）只能出现一个元组/行（dept_name,ID,avg(salary)），那就无法确定选哪个ID进行输出，但平均薪水只有一个。

??????????? having可以将不满足条件的分组舍弃

??? select course_id,semester,year,sec_id,avg(tot_cred)from takes natural join student where year = 2009 group by course_id,semester,year,sec_id having count(ID) >= 2

对于在2009年讲授的每个课程段，如果至少有两名学生这找出选秀该课程段的所有学生的总分和平均值

?????? 除了count(*)外所有的聚集函数都忽略空值

嵌套子查询：

集合成员资格：在2010年春开的课中查询2009年秋开的课

???????? select distinct course_id from section where semester = ‘fall‘ and year = 2009 and course_id in (

?????????????????? select course_id from section where semester=‘spring‘ and year =2010);? 同理可以使用not in 在2010年春开的课中查询2009年秋没有开的课

?not in可以用于枚举中： select distinct name from instructor where name not in(‘mozart‘,‘einstein‘)查询工资至少比biology系的某一个教师工资高的教师名

集合的比较：select name from instructor where salary >some(select salary from instructor where dept_name =‘biology‘)?? 等价于select distict T.name from instructor as T,instructor as S where T.salary >S.salary and S.dept_name = ‘‘biology;

?????????? 找出平均工资最高的系：(先要找出所有系的平均工资)

select dept_name from instructor group by dept_name having avg(salary) >=all(select avg(salary) from instructor group by dept_name)

?

? =some等价与in,而<>some并不等价于not in，同理<>all等价于not in,但=all并不等价于in ????

标量子查询：返回单个值的表达式能够出现的任何地方都可以有子查询，只要该子查询只返回包含单个属性的元组；这样的子查询叫标量子查询 ??

select dept_name(select count(*) from instructor where department.dept_name = instructor.dept_name) as num_instructors from department ????? 查询各系名字及拥有的教师数

?

ps:distinct:用来去除重复

all不去除重复，默认

*：查询所有属性

as：用于select子句中把查询的属性改变名字显示，用于from语句中把复杂的长的属性名进行更改简化该语句的查询

like:符合某种模式，大小写敏感

trim:去除空格

upper:将字符串转为大写

lower:将字符串转为小写

%：匹配任意字符串

_：陪陪任意字符

desc:降序

asc：升序，默认

between...and:在什么之间

<>:!= 不等于

??????????????????

????????????????

?

?????????????

?

　　?

????????

?