数据库与SQL基本知识

                                                                   数据库基本知识

1.数据模型：

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

2.关系数据库：

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

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

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

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

3.关系数据库中的几种范式：

第一范式（1NF）:第一范式是关系数据库的最基本要求，在第一范式中关系的每个属性不能重复也不能分割

第二范式（2NF）:在第一范式的基础上要求关系中的所有属性完全依赖于主码，而不能依赖与主码的子集。且每一个元组可以被区分开来

第三范式（3NF）:在第二范式的基础上要求属性不依赖于其他非主属性（不能有A->B->C的情况）。即在该关系上的属性不会重复出现在其他关系上。

BCNF:在第三范式的基础上不存在关键字段决定关键字段的情况

第四范式：在BCNF的基础上，属性之间不允许有非平凡且非函数依赖的多值依赖

 

4.基本数据类型：

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

5.基本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 instructor where salary <(select avg(salary) from instructor);

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

          

查询：select 要查的结果，最后显示出来   from查询的范围    where约束条件，但其中属性值要在from中存在

单关系查询：select (top num) 属性 from 关系 where 属性约束 order by 排序；可以加上top num关键字截取排序的前num个数据

多关系查询：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;

                根据定义我们会发现有时候关系中的某个元组与另一个关系所有元组都不能配对这时在自然连接是元组就会发生丢失，为了使元组不丢失我么可以使用外连接将该元组在另一个关系中的所有属性都设为空。根据具体情况分为左外连接，右外连接和全外连接。使用natural left/right/full outer join。相应的普通连接就为内连接，默认时是省略了inner的。

并运算：(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可以将不满足条件的分组舍弃                  对于在2009年讲授的每个课程段，如果至少有两名学生这找出选秀该课程段的所有学生的总分和平均值

                 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

              

                 除了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     

空关系测试：用来测试一个子查询的结果中是否存在元组

找出在09年秋和10年春同时开设的所有课程（不用交运算）

select coure_id from section as S where semester = 'fall' and year = 2009 and exists(select * from section as T where semester = 'spring' and year =2010 and S.course_id = T.course_id);   and S.course_id = T.course_id可以省略，加上可以缩小检查范围。另外我们发现外层的相关名称S用在了where子句的子查询中，这种查询叫相关子查询。S的作用域范围类似函数中变量的作用范围。同理我们可以使用not exsist来检测查询结果中不再子查询的结果中的结果  A not exsists B即 A-B

找出选修了Biology系开设的所有课程的学生：

Select S.ID,S.name from student as S where not exists((select course_id from course where dept_name = 'biology')except(select T.course_id from takes as T where S.ID = T.ID));

 

重复元组存在性测试：where unique(结果集中只有一个的符合where约束)与where not unicque(结果集中不止一个的符合where约束)  还未广泛使用

from子句中的子查询：查询范围在子查询结果中

找出系平均工资超过42000元的系中教师的平均工资

select dept_name,avg_salary from(select dept_name,avg(salary) from instructor group by dept_name) as dept_avg(detp_name,avg_salary) where avg_salary >42000;

 我们把放在having里的语句，放在了外层where中。注意：有些SQL要求必须用as对关系命名，即使用不到，所以我们最好写上；Oracle中可以对结果关系命名，但不允许对关系中属性重命名。而且from子句的子句查询中不能使用from子句其他关系的相关变量。但在IBM DB2中可以加上嵌套子查询前lateral关键字，就可以使用来自from子句其他关系的相关变量

with子句：用来定义临时关系的方法，只在该子句中有用

找出具有最大预算值的系：定义一个最大预算值，然后在from和where中使用

with max_budget(value) as (select max(budget) from department)

select budget from department,max_budget where department.budget = max_budget.value

找出所有工资大于所有系平均工资的系

with dept_total(dept_name,value) as(select dept_name,sum(salary) from instructor group by dept_name), dept_total_avg(value) as(select avg(value) from dept_total)

select dept_name from dept_total,dept_total_avg where dept_total.value>=dept_total_avg.value;

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

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

对字符串操作的常用数： substring（截取字符）,charindex（字符下标）函数，一般使用两者组合能对字符串进行大部分操作。                                                                                                                                                                                                                                                                                  

ps:一些关键词的作用

distinct:用来去除重复

all不去除重复，默认

*：查询所有属性

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

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

trim:去除空格

upper:将字符串转为大写

lower:将字符串转为小写

%：匹配任意字符串

_：陪陪任意字符

desc:降序

asc：升序，默认

between...and:在什么之间

<>:!= 不等于

        此外，基于sql的基础有Microsoft SQL Server的加强版T_sql和针对Oracle的Pl/sql。它们提供了其他一些特有的（如游标）等操作。