图片 6

50条SQL查询技巧,数据库基本知识

12)HAVING

该关键词可以帮助我们对函数产生的值来设定条件。语法为:
SELECT
“栏位1”, SUM(“栏位2”)  FROM “表格名”  GROUP BY “栏位1”  HAVING
(函数条件)  

     
3.关系模式是对关系的描述,五元组形式化表示为:R(U,D,DOM,F),其中

44、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,查询结果按人数降序排列,若人数相同,按课程号升序排列

13)ALIAS

我们可以通过ALIAS为列名称和表名称指定别名,语法为:
SELECT
“表格别名”.”栏位1″ “栏位别名”  FROM “表格名” “表格别名”  

  • *

   
  <属性列表 1>:一个或逗号分隔的多个列名。

复制代码 代码如下:

问题:

1、查询“001”课程比“002”课程成绩高的所有学生的学号;
select
a.S#
from
(select s#,score from SC where C#=’001′) a,
(select
s#,score from SC where C#=’002′) b
where
a.score>b.score and a.s#=b.s#;

2、查询平均成绩大于60分的同学的学号和平均成绩;
select
S#,avg(score)
from
sc
group
by S# having avg(score) >60;

3、查询所有同学的学号、姓名、选课数、总成绩;
select
Student.S#,Student.Sname,count(SC.C#),sum(score)
from
Student left Outer join SC on Student.S#=SC.S#
group
by Student.S#,Sname

4、查询姓“李”的老师的个数;
select
count(distinct(Tname))
from
Teacher
where
Tname like ‘李%’;

5、查询没学过“叶平”老师课的同学的学号、姓名;
select
Student.S#,Student.Sname
from
Student
where
S# not in (select distinct( SC.S#) from SC,Course,Teacher where
SC.C#=Course.C# and Teacher.T#=Course.T# and
Teacher.Tname=’叶平’);

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
select
Student.S#,Student.Sname
from
Student,SC

where
Student.S#=SC.S# and SC.C#=’001′and exists( Select * from SC as
SC_2 where SC_2.S#=SC.S# and SC_2.C#=’002′);

 

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
select
S#,Sname
from
Student
where
S# in
(select
S#
from
SC ,Course ,Teacher
where
SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’叶平’
group by S# having count(SC.C#)=(select count(C#) from Course,Teacher
where Teacher.T#=Course.T# and Tname=’叶平’));

8、查询所有课程成绩小于60分的同学的学号、姓名;
select
S#,Sname
from
Student
where
S# not in (select Student.S# from Student,SC where S.S#=SC.S# and
score>60);

9、查询没有学全所有课的同学的学号、姓名;
select
Student.S#,Student.Sname
from
Student,SC
where
Student.S#=SC.S#
group
by Student.S#,Student.Sname having count(C#) <(select count(C#)
from Course);

10、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
select
S#,Sname
from
Student,SC
where
Student.S#=SC.S# and C# in (select C# from SC where
S#=’1001’);

11、删除学习“叶平”老师课的SC表记录;
Delect
SC
from
course ,Teacher
where
Course.C#=SC.C# and Course.T#= Teacher.T# and Tname=’叶平’;

12、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
SELECT
L.C# 课程ID,L.score 最高分,R.score 最低分
FROM
SC L ,SC R
WHERE
L.C# = R.C#
and
L.score
= (SELECT MAX(IL.score)
FROM
SC IL,Student IM
WHERE
IL.C# = L.C# and IM.S#=IL.S#
GROUP
BY IL.C#)
and
R.Score
= (SELECT MIN(IR.score)
FROM
SC IR
WHERE
IR.C# = R.C#
GROUP
BY IR.C# );

13、查询学生平均成绩及其名次
SELECT
1+(SELECT COUNT( distinct 平均成绩)
FROM
(SELECT S#,AVG(score) 平均成绩
FROM
SC
GROUP
BY S# ) T1
WHERE
平均成绩 > T2.平均成绩) 名次, S# 学生学号,平均成绩
FROM
(SELECT S#,AVG(score) 平均成绩 FROM SC GROUP BY S# ) T2
ORDER
BY 平均成绩 desc;

14、查询各科成绩前三名的记录:(不考虑成绩并列情况)
SELECT
t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM
SC t1
WHERE
score IN (SELECT TOP 3 score
FROM
SC
WHERE
t1.C#= C#
ORDER
BY score DESC)
ORDER
BY t1.C#;

15、查询每门功成绩最好的前两名
SELECT
t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM
SC t1
WHERE
score IN (SELECT TOP 2 score
FROM
SC
WHERE
t1.C#= C#
ORDER
BY score DESC )

ORDER
BY t1.C#;

图片 1

24、查询学生平均成绩及其名次

9)函数

函数允许我们能够对这些数字的型态存在的行或者列做运算,包括 AVG
(平均)、COUNT (计数)、MAX (最大值)、MIN (最小值)、SUM
(总合)。语法为:
SELECT
“函数名”(“栏位名”) FROM “表格名”  

     
      
 SELECT [ALL|DISTINCT] <算术表达式列表> FROM <表名或视图名列表>

11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;

第三节

     
 3. 关系数据库: 依照关系模型建立的数据库称为关系数据库。 它是在某个应用领域的所有关系的集合。

SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS
课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩
FROM SC AS T,Course AS C ,Teacher AS Z
where T.C#=C.C# and C.T#=Z.T#
GROUP BY C.C#
ORDER BY AVG(Score) DESC

第二节

 二、重点知识点

45、检索至少选修两门课程的学生学号

4)AND OR

上例中的 WHERE
指令可以被用来由表格中有条件地选取资料。这个条件可能是简单的
(像上一页的例子),也可能是复杂的。复杂条件是由二或多个简单条件透过 AND
或是 OR 的连接而成。语法为:
SELECT
“栏位名”  FROM “表格名”  WHERE “简单条件”  {[AND|OR]
“简单条件”}+

     
 2. 外键:如果一个关系的一个或一组属性引用(参照)了另一个关系的主键,则称这个或这组属性为外码或外键(Foreign key)。

4、查询姓“李”的老师的个数;

 二、重点知识点

  1. 数据库系统由数据库、数据库管理系统、应用系统和数据库管理员构成。

     
 2. 数据模型的组成要素是:数据结构、数据操作、完整性约束条件。

     
 3. 实体型之间的联系分为一对一、一对多和多对多三种类型。

     
 4. 常见的数据模型包括:关系、层次、网状、面向对象、对象关系映射等几种。

     
 5. 关系模型的完整性约束包括:实体完整性、参照完整性和用户定义完整性。

      6. 阐述数据库三级模式、二级映象的含义及作用。

     
  数据库三级模式反映的是数据的三个抽象层次: 模式是对数据库中全体数据的逻辑结构和特征的描述内模式又称为存储模式,是对数据库物理结构和存储方式的描述。外模式又称为子模式或用户模式,是对特定数据库用户相关的局部数据的逻辑结构和特征的描述

     
  数据库三级模式通过二级映象在 DBMS 内部实现这三个抽象层次的联系和转换。外模式面向应用程序, 通过外模式/模式映象与逻辑模式建立联系, 实现数据的逻辑独立性。 模式/内模式映象建立模式与内模式之间的一对一映射, 实现数据的物理独立性

       算术表达式列表:一个或多个逗号分隔的算术表达式,表达式由常量(包括数字和字符串)、列名、函数和算术运算符构成。每个表达式后还可跟别名。也可用 *代表查询表中的所有列。

复制代码 代码如下:

SQL语句中常用关键词及其解释如下:

12)HAVING

该关键词可以帮助我们对函数产生的值来设定条件。语法为:
SELECT “栏位1”, SUM(“栏位2”)  FROM
“表格名”  GROUP BY “栏位1”  HAVING (函数条件)  

复制代码 代码如下:

2)DISTINCT

在上述 SELECT 关键词后加上一个 DISTINCT
就可以去除选择出来的栏位中的重复,从而完成求得这个表格/栏位内有哪些不同的值的功能。语法为
SELECT
DISTINCT “栏位名” FROM “表格名”。

     
      
 [ GROUP BY <属性列表 1> [ HAVING <条件表达式 2 > ] ]

39、求选了课程的学生人数

二、细说数据库三范式

  2.1 第一范式(1NF)无重复的列

       第一范式(1NF)中数据库表的每一列都是不可分割的基本数据项

     
 同一列中不能有多个值

     
 即实体中的某个属性不能有多个值或者不能有重复的属性

     
 简而言之,第一范式就是无重复的列。

     
 在任何一个关系数据库中,第一范式(1NF)是对关系模式的基本要求,不满足第一范式(1NF)的数据库就不是关系数据库

  

  2.2 第二范式(2NF)属性完全依赖于主键[消除部分子函数依赖]     

  满足第二范式(2NF)必须先满足第一范式(1NF)。   
 

  第二范式(2NF)要求数据库表中的每个实例或行必须可以被惟一地区分。     

  为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。 

  第二范式(2NF)要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性,如果存在,那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体,新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。简而言之,第二范式就是属性完全依赖于主键。

  2.3 第三范式(3NF)属性不依赖于其它非主属性[消除传递依赖]

     
 满足第三范式(3NF)必须先满足第二范式(2NF)。

     
 简而言之,第三范式(3NF)要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

     
 例如,存在一个部门信息表,其中每个部门有部门编号(dept_id)、部门名称、部门简介等信息。那么在的员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表,则根据第三范式(3NF)也应该构建它,否则就会有大量的数据冗余。简而言之,第三范式就是属性不依赖于其它非主属性。

  

  2.4
具体实例剖析

  下面列举一个学校的学生系统的实例,以示几个范式的应用。

  在设计数据库表结构之前,我们先确定一下要设计的内容包括那些。学号、学生姓名、年龄、性别、课程、课程学分、系别、学科成绩,系办地址、系办电话等信息。为了简单我们暂时只考虑这些字段信息。我们对于这些信息,说关心的问题有如下几个方面。

     
 1)学生有那些基本信息 
     
 2)学生选了那些课,成绩是什么 
     
 3)每个课的学分是多少 
     
 4)学生属于那个系,系的基本信息是什么。

     
 首先第一范式(1NF):数据库表中的字段都是单一属性的,不可再分。这个单一属性由基本类型构成,包括整型、实数、字符型、逻辑型、日期型等。在当前的任何关系数据库管理系统(DBMS)中,不允许你把数据库表的一列再分成二列或多列,因此做出的都是符合第一范式的数据库。 

     
 我们再考虑第二范式,把所有这些信息放到一个表中(学号,学生姓名、年龄、性别、课程、课程学分、系别、学科成绩,系办地址、系办电话)下面存在如下的依赖关系。 
     
 1)(学号)→ (姓名, 年龄,性别,系别,系办地址、系办电话) 
     
 2) (课程名称) → (学分) 
     
 3)(学号,课程)→ (学科成绩)

  根据依赖关系我们可以把选课关系表SelectCourse改为如下三个表: 

     
 学生:Student(学号,姓名, 年龄,性别,系别,系办地址、系办电话); 
     
 课程:Course(课程名称, 学分); 
     
 选课关系:SelectCourse(学号, 课程名称, 成绩)。

     
 事实上,对照第二范式的要求,这就是满足第二范式的数据库表,若不满足第二范式,会产生如下问题

  (1)数据冗余: 同一门课程由n个学生选修,”学分”就重复n-1次;同一个学生选修了m门课程,姓名和年龄就重复了m-1次。

  (2)更新异常:1)若调整了某门课程的学分,数据表中所有行的”学分”值都要更新,否则会出现同一门课程学分不同的情况。 
     
         
 2)假设要开设一门新的课程,暂时还没有人选修。这样,由于还没有”学号”关键字,课程名称和学分也无法记录入数据库。

  (3)删除异常 : 假设一批学生已经完成课程的选修,这些选修记录就应该从数据库表中删除。但是,与此同时,课程名称和学分信息也被删除了。很显然,这也会导致插入异常。

  我们再考虑如何将其改成满足第三范式的数据库表,接着看上面的学生表Student(学号,姓名, 年龄,性别,系别,系办地址、系办电话),关键字为单一关键字”学号”,因为存在如下决定关系:

     
(学号)→ (姓名, 年龄,性别,系别,系办地址、系办电话) 
  但是还存在下面的决定关系 
     
 (学号) → (所在学院)→(学院地点, 学院电话) 
     
 
即存在非关键字段”学院地点”、”学院电话”对关键字段”学号”的传递函数依赖。 
     
 它也会存在数据冗余、更新异常、插入异常和删除异常的情况(这里就不具体分析了,参照第二范式中的分析)。根据第三范式把学生关系表分为如下两个表就可以满足第三范式了:

     
 学生:(学号, 姓名, 年龄, 性别,系别); 
     
 系别:(系别, 系办地址、系办电话)。

SQL语句总结

     
 6.SQL 创建表语句的一般格式为

SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0)
AS 平均成绩
,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0
END)/COUNT(*) AS 及格百分数
FROM SC T,Course
where t.C#=course.C#
GROUP BY t.C#
ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0
END)/COUNT(*) DESC

5)IN

在 SQL 中,在两个情况下会用到 IN  这个指令;这一页将介绍其中之一:与
WHERE
有关的那一个情况。在这个用法下,我们事先已知道至少一个我们需要的值,而我们将这些知道的值都放入
IN  这个子句。语法为:
SELECT
“栏位名”  FROM “表格名”  WHERE “栏位名” IN (‘值一’, ‘值二’, …)
 

 
   
关于SQL语句的知识这里先作如上简略介绍,具体写法下次将专门拿出一篇来叙述。

SELECT distinct student.S#,student.Sname,SC.C#,SC.score
FROM student,Sc
WHERE SC.score>=70 AND SC.S#=student.S#;

一、相关概念和知识

     
 1.触发器是用户定义在基本表上的一类由事件驱动的特殊过程。由服务器自动激活, 能执行更为复杂的检查和操作,具有更精细和更强大的数据控制能力。使用 CREATE TRIGGER 命令建立触发器。

     
 2.计算机系统存在技术安全、管理安全和政策法律三类安全性问题。

     
 3. TCSEC/TDI 标准由安全策略、责任、保证和文档四个方面内容构成。

     
 4. 常用存取控制方法包括自主存取控制(DAC)和强制存取控制(MAC)两种。

     
 5. 自主存取控制(DAC)的 SQL 语句包括 GRANT 和 REVOKE 两个。 用户权限由数据对象和操作类型两部分构成。

图片 2

     
 6. 常见SQL 自主权限控制命令和例子。

     
   1) 把对 Student 和 Course 表的全部权限授予所有用户。

     
        GRANT ALL PRIVILIGES ON TABLE Student,Course TO PUBLIC

     
   2) 把对 Student 表的查询权和姓名修改权授予用户 U4。

     
        GRANT SELECT,UPDATE(Sname) ON TABLE Student TO U4 ;

     
   3) 把对 SC 表的插入权限授予 U5 用户,并允许他传播该权限。

     
        GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION ;

     
   4) 把用户 U5 对 SC 表的 INSERT
权限收回,同时收回被他传播出去的授权。

     
        REVOKE INSERT ON TABLE SC FROM U5 CASCADE ;

     
   5) 创建一个角色 R1,并使其对 Student
表具有数据查询和更新权限。

     
        CREATE ROLE R1;

     
        GRANT SELECT,UPDATE ON TABLE Student TO R1;

     
   6) 对修改 Student 表结构的操作进行审计。

     
        AUDIT ALTER ON Student ;

       6. 常见SQL 自主权限控制命令和例子。
         1) 把对 Student 和 Course 表的全部权限授予所有用户。
            
 GRANT ALL PRIVILIGES ON TABLE Student,Course TO PUBLIC ;
       
 2) 把对 Student 表的查询权和姓名修改权授予用户 U4。
            
 GRANT SELECT,UPDATE(Sname) ON TABLE Student TO U4 ;
       
 3) 把对 SC 表的插入权限授予 U5 用户,并允许他传播该权限。
            
 GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION ;
       
 4) 把用户 U5 对 SC 表的 INSERT
权限收回,同时收回被他传播出去的授权。
            
 REVOKE INSERT ON TABLE SC FROM U5 CASCADE ;
       
 5) 创建一个角色 R1,并使其对 Student
表具有数据查询和更新权限。
            
 CREATE ROLE R1;
            
 GRANT SELECT,UPDATE ON TABLE Student TO R1;
       
 6) 对修改 Student 表结构的操作进行审计。
            
 AUDIT ALTER ON Student ;

select count(distinct(Tname))
from Teacher
where Tname like ‘李%’;

第四节

13)ALIAS

我们可以通过ALIAS为列名称和表名称指定别名,语法为:
SELECT “表格别名”.”栏位1″
“栏位别名”  FROM “表格名” “表格别名”  

下面为一个例子,通过它我们应该能很好地掌握以上关键词的使用方法。

Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表

问题:
1、查询“001”课程比“002”课程成绩高的所有学生的学号;
select a.S#
from (select s#,score from SC where C#=’001′) a,
(select s#,score from SC where C#=’002′) b
where a.score>b.score and a.s#=b.s#;

2、查询平均成绩大于60分的同学的学号和平均成绩;
select S#,avg(score)
from sc
group by S# having avg(score) >60;

3、查询所有同学的学号、姓名、选课数、总成绩;
select Student.S#,Student.Sname,count(SC.C#),sum(score)
from Student left Outer join SC on Student.S#=SC.S#
group by Student.S#,Sname

4、查询姓“李”的老师的个数;
select count(distinct(Tname))
from Teacher
where Tname like ‘李%’;

5、查询没学过“叶平”老师课的同学的学号、姓名;
select Student.S#,Student.Sname
from Student
where S# not in (select distinct( SC.S#) from SC,Course,Teacher where
SC.C#=Course.C# and Teacher.T#=Course.T# and
Teacher.Tname=’叶平’);

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
select Student.S#,Student.Sname
from Student,SC

where
Student.S#=SC.S# and SC.C#=’001′and exists( Select * from SC as
SC_2 where SC_2.S#=SC.S# and SC_2.C#=’002′);

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
select S#,Sname
from Student
where S# in
(select S#
from SC ,Course ,Teacher
where SC.C#=Course.C# and Teacher.T#=Course.T# and
Teacher.Tname=’叶平’ group by S# having count(SC.C#)=(select
count(C#) from Course,Teacher where Teacher.T#=Course.T# and
Tname=’叶平’));

8、查询所有课程成绩小于60分的同学的学号、姓名;
select S#,Sname
from Student
where S# not in (select Student.S# from Student,SC where S.S#=SC.S#
and score>60);

9、查询没有学全所有课的同学的学号、姓名;
select Student.S#,Student.Sname
from Student,SC
where Student.S#=SC.S#
group by Student.S#,Student.Sname having count(C#) <(select
count(C#) from Course);

10、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
select S#,Sname
from Student,SC
where Student.S#=SC.S# and C# in (select C# from SC where
S#=’1001’);

11、删除学习“叶平”老师课的SC表记录;
Delect SC
from course ,Teacher
where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname=’叶平’;

12、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
SELECT L.C# 课程ID,L.score 最高分,R.score 最低分
FROM SC L ,SC R
WHERE L.C# = R.C#
and
L.score = (SELECT MAX(IL.score)
FROM SC IL,Student IM
WHERE IL.C# = L.C# and IM.S#=IL.S#
GROUP BY IL.C#)
and
R.Score = (SELECT MIN(IR.score)
FROM SC IR
WHERE IR.C# = R.C#
GROUP BY IR.C# );

13、查询学生平均成绩及其名次
SELECT 1+(SELECT COUNT( distinct 平均成绩)
FROM (SELECT S#,AVG(score) 平均成绩
FROM SC
GROUP BY S# ) T1
WHERE 平均成绩 > T2.平均成绩) 名次, S# 学生学号,平均成绩
FROM (SELECT S#,AVG(score) 平均成绩 FROM SC GROUP BY S# ) T2
ORDER BY 平均成绩 desc;

14、查询各科成绩前三名的记录:(不考虑成绩并列情况)
SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM SC t1
WHERE score IN (SELECT TOP 3 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC)
ORDER BY t1.C#;

15、查询每门功成绩最好的前两名
SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM SC t1
WHERE score IN (SELECT TOP 2 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC )

ORDER
BY t1.C#;

=

复制代码 代码如下:

10)COUNT

这个关键词能够帮我我们统计有多少笔资料被选出来,语法为:
SELECT
COUNT(“栏位名”) FROM “表格名”

第二节

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序

 一、相关概念    

  1.
Data:数据,是数据库中存储的基本对象,是描述事物的符号记录。

     
 2.
Database:数据库,是长期储存在计算机内、有组织的、可共享的大量数据的集合

     
 3.
DBMS:数据库管理系统,是位于用户与操作系统之间的一层数据管理软件,用于科学地组织、存储和管理数据、高效地获取和维护数据。

     
 4.
DBS:数据库系统,指在计算机系统中引入数据库后的系统,一般由数据库、数据库管理系统、应用系统、数据库管理员(DBA)构成。

     
 5.
数据模型:是用来抽象、表示和处理现实世界中的数据和信息的工具,是对现实世界的模拟,是数据库系统的核心和基础;其组成元素有数据结构、数据操作和完整性约束

     
 6.
概念模型:也称信息模型,是按用户的观点来对数据和信息建模,主要用于数据库设计。

     
 7. 逻辑模型:是按计算机系统的观点对数据建模,用于DBMS实现。

     
 8.
物理模型:是对数据最底层的抽象,描述数据在系统内部的表示方式和存取方法,在磁盘或磁带上的存储方式和存取方法,是面向计算机系统的。

     
 9.
实体和属性:客观存在并可相互区别的事物称为实体。实体所具有的某一特性称为属性。

     
 10.E-R图:即实体-关系图,用于描述现实世界的事物及其相互关系,是数据库概念模型设计的主要工具。

     
 11.关系模式:从用户观点看,关系模式是由一组关系组成,每个关系的数据结构是一张规范化的二维表。

     
 12.型/值:型是对某一类数据的结构和属性的说明;值是型的一个具体赋值,是型的实例。

     
 13.数据库模式:是对数据库中全体数据的逻辑结构(数据项的名字、类型、取值范围等)和特征(数据之间的联系以及数据有关的安全性、完整性要求)的描述。

     
 14.数据库的三级系统结构:外模式、模式和内模式

     
 15.数据库内模式:又称为存储模式,是对数据库物理结构和存储方式的描述,是数据在数据库内部的表示方式。一个数据库只有一个内模式

     
 16.数据库外模式:又称为子模式或用户模式,它是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述,是数据库用户的数据视图。通常是模式的子集。一个数据库可有多个外模式

     
 17.数据库的二级映像:外模式/模式映像、模式/内模式映像。

            
 [,<列名> <数据类型>[ <列级完整性约束>] ] …

复制代码 代码如下:

第一节

      <列名列表>:一个或逗号分隔的多个列名,每个列名后可跟 ASC 或 DESC,表示升/降序,缺省为升序。多列时则按为多级排序。

复制代码 代码如下:

6)BETWEEN

IN 这个指令可以让我们依照一或数个不连续
(discrete)的值的限制之内抓出资料库中的值,而 BETWEEN
则是让我们可以运用一个范围 (range)
 内抓出资料库中的值,语法为:
SELECT
“栏位名”  FROM “表格名” WHERE “栏位名” BETWEEN ‘值一’ AND
‘值二’ 

     
 3. 嵌套查询:指将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询。

复制代码 代码如下:

 一、相关概念和知识点

     
 1.数据依赖:反映一个关系内部属性与属性之间的约束关系,是现实世界属性间相互联系的抽象,属于数据内在的性质和语义的体现。

     
 2. 规范化理论:是用来设计良好的关系模式的基本理论。它通过分解关系模式来消除其中不合适的数据依赖,以解决插入异常、删除异常、更新异常和数据冗余问题。

     
 3. 函数依赖:简单地说,对于关系模式的两个属性子集X和Y,若X的任一取值能唯一确定Y的值,则称Y函数依赖于X,记作X→Y。

     
 4. 非平凡函数依赖:对于关系模式的两个属性子集X和Y,如果X→Y,但Y!⊆X,则称X→Y为非平凡函数依赖;如果X→Y,但Y⊆X,则称X→Y为非平凡函数依赖。

     
 5. 完全函数依赖:对于关系模式的两个属性子集X和Y,如果X→Y,并且对于X的任何一个真子集X’,都没有X’→Y,则称Y对X完全函数依赖。

     
 6. 范式:指符合某一种级别的关系模式的集合。在设计关系数据库时,根据满足依赖关系要求的不同定义为不同的范式。

     
 7. 规范化:指将一个低一级范式的关系模式,通过模式分解转换为若干个高一级范式的关系模式的集合的过程。

     
 8. 1NF:若关系模式的所有属性都是不可分的基本数据项,则该关系模式属于1NF。

     
 9. 2NF:1NF关系模式如果同时满足每一个非主属性完全函数依赖于码,则该关系模式属于2NF。

     
 10. 3NF:若关系模式的每一个非主属性既不部分依赖于码也不传递依赖于码,则该关系模式属于3NF。

     
 11. BCNF:若一个关系模式的每一个决定因素都包含码,则该关系模式属于BCNF。

     
 12. 数据库设计:是指对于一个给定的应用环境,构造优化的数据库逻辑模式和物理结构,并据此建立数据库及其应用系统,使之能够有效地存储和管理数据,满足各种用户的应用需求,包括信息管理要求和数据操作要求。

     
 13.
数据库设计的6个基本步骤:需求分析,概念结构设计,逻辑结构设计,物理结构设计,数据库实施,数据库运行和维护。

     
 14. 概念结构设计:指将需求分析得到的用户需求抽象为信息结构即概念模型的过程。也就是通过对用户需求进行综合、归纳与抽象,形成一个独立于具体DBMS的概念模型。

     
 15. 逻辑结构设计:将概念结构模型(基本E-R图)转换为某个DBMS产品所支持的数据模型相符合的逻辑结构,并对其进行优化。

     
 16. 物理结构设计:指为一个给定的逻辑数据模型选取一个最适合应用环境的物理结构的过程。包括设计数据库的存储结构与存取方法。

     
 17. 抽象:指对实际的人、物、事和概念进行人为处理,抽取所关心的共同特性,忽略非本质的细节,并把这些特性用各种概念精确地加以描述,这些概念组成了某种模型。     
 18. 数据库设计必须遵循结构设计和行为设计相结合的原则。     
 19. 数据字典主要包括数据项、数据结构、数据流、数据存储和处理过程五个部分。

     
 20. 三种常用抽象方法是分类、聚集和概括。

     
 21. 局部 E-R
图之间的冲突主要表现在属性冲突、命名冲突和结构冲突三个方面。     
 22. 数据库常用的存取方法包括索引方法、聚簇方法和 HASH方法三种。

     
 23. 确定数据存放位置和存储结构需要考虑的因素主要有: 存取时间、
存储空间利用率和维护代价等。

            DOM —— 属性向域的映象集合

38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;

11)GROUP BY

GROUP BY
语句用于结合合计函数,根据一个或多个列对结果集进行分组。语法为:
SELECT
“栏位1”, SUM(“栏位2”)  FROM “表格名”  GROUP BY “栏位1” 

      CLUSTER:表示创建聚簇索引,缺省为非聚簇索引;

复制代码 代码如下:

二、重要知识点

     
1. 关系数据库语言分为关系代数、关系演算和结构化查询语言三大类。

     
2. 关系的5种基本操作是选择、投影、并、差、笛卡尔积

     
3.关系模式是对关系的描述,五元组形式化表示为:R(U,D,DOM,F),其中

     
      R —— 关系名

     
      U —— 组成该关系的属性名集合

     
      D —— 属性组 U 中属性所来自的域

     
      DOM —— 属性向域的映象集合

     
      F —— 属性间的数据依赖关系集合

     
4.笛卡尔乘积,选择和投影运算如下

图片 3

6)BETWEEN

IN
这个指令可以让我们依照一或数个不连续
(discrete)的值的限制之内抓出资料库中的值,而 BETWEEN
则是让我们可以运用一个范围 (range)  内抓出资料库中的值,语法为:
SELECT “栏位名”  FROM “表格名”
WHERE “栏位名” BETWEEN ‘值一’ AND ‘值二’ 

您可能感兴趣的文章:

  • MySQL学习笔记3:表的基本操作介绍
  • 单个select语句实现MySQL查询统计次数
  • sql查询出各科成绩最好的学生信息
  • mysql查询昨天 一周前 一月前
    一年前的数据
  • mysql查询今天、昨天、近7天、近30天、本月、上一月的SQL语句
  • MySql查询时间段的方法
  • MySQL查询和修改auto_increment的方法
  • 一个优化MySQL查询操作的具体案例分析
  • MySQL查询倒数第二条记录实现方法
  • SQL查询出表、存储过程、触发器的创建时间和最后修改时间示例
  • 大幅优化MySQL查询性能的奇技淫巧
  • SQL大量数据查询的优化及非用like不可时的处理方案
  • 如何使用MySQL查询某个列中相同值的数量统计
  • SQL如何实现MYSQL的递归查询
  • 数据库表的查询操作(实验二)
  • 数据库表的查询操作实践演练(实验三)

一、相关概念

     
 1. 主键: 能够唯一地标识一个元组的属性或属性组称为关系的键或候选键。 若一个关系有多个候选键则可选其一作为主键(Primary key)。

     
 2. 外键:如果一个关系的一个或一组属性引用(参照)了另一个关系的主键,则称这个或这组属性为外码或外键(Foreign key)。

     
 3. 关系数据库: 依照关系模型建立的数据库称为关系数据库。 它是在某个应用领域的所有关系的集合

     
 4. 关系模式: 简单地说,关系模式就是对关系的型的定义, 包括关系的属性构成、各属性的数据类型、 属性间的依赖、 元组语义及完整性约束等。 关系是关系模式在某一时刻的状态或内容, 关系模型是型, 关系是值, 关系模型是静态的、 稳定的, 而关系是动态的、随时间不断变化的,因为关系操作在不断地更新着数据库中的数据

     
 5.
实体完整性:用于标识实体的唯一性。它要求基本关系必须要有一个能够标识元组唯一性的主键,主键不能为空,也不可取重复值。

     
 6. 参照完整性: 用于维护实体之间的引用关系。 它要求一个关系的外键要么为空, 要么取与被参照关系对应的主键值,即外键值必须是主键中已存在的值

     
 7. 用户定义的完整性:就是针对某一具体应用的数据必须满足的语义约束。包括非空、 唯一和布尔条件约束三种情况。

            
 ( <列名> <数据类型>[ <列级完整性约束> ]

select count(*) from sc group by C#;

8)ORDER BY

我们经常需要能够将抓出的资料做一个有系统的显示。这可能是由小往大
(ascending)  或是由大往小(descending)。在这种情况下,我们就可以运用
ORDER BY 这个指令来达到我们的目的。语法为:
SELECT
“栏位名”  FROM “表格名 [WHERE “条件”] ORDER BY “栏位名” [ASC,
DESC] 

图片 4

复制代码 代码如下:

一、相关概念

     
 1. SQL:结构化查询语言的简称, 是关系数据库的标准语言。SQL 是一种通用的、 功能极强的关系数据库语言, 是对关系数据存取的标准接口, 也是不同数据库系统之间互操作的基础。集数据查询、数据操作、数据定义、和数据控制功能于一体。

     
 2. 数据定义:数据定义功能包括模式定义、表定义、视图和索引的定义。

     
 3. 嵌套查询:指将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询。

     
      
 CREATE [UNIQUE] [CLUSTER] INDEX <索引名>

复制代码 代码如下:

3)WHERE

这个关键词可以帮助我们选择性地抓资料,而不是全取出来。语法为
SELECT
“栏位名” FROM “表格名” WHERE “条件” 

     
一、相关概念

49、检索“004”课程分数小于60,按分数降序排列的同学学号

数据库也是计算机类笔试面试中不可避免会遇到的考点,尤其是银行和部分传统软件类公司。这里根据整理的资料,对数据库的相关知识也做个总结吧。希望学过数据库但长时间不用的同学根据这些知识能够回忆和重拾,没学过的同学能掌握一些数据库的基础知识。

     
 4. 关系模式: 简单地说,关系模式就是对关系的型的定义, 包括关系的属性构成、各属性的数据类型、 属性间的依赖、 元组语义及完整性约束等。 关系是关系模式在某一时刻的状态或内容, 关系模型是型, 关系是值, 关系模型是静态的、 稳定的, 而关系是动态的、随时间不断变化的,因为关系操作在不断地更新着数据库中的数据。

select S#,avg(score)
from sc
group by S# having avg(score) >60;

1)SELECT

将资料从数据库中的表格内选出,两个关键字:从 (FROM) 数据库中的表格内选出
(SELECT)。语法为
SELECT
“栏位名” FROM “表格名”。

 
    二、重要知识点

select Student.S#,Student.Sname,count(SC.C#),sum(score)
from Student left Outer join SC on Student.S#=SC.S#
group by Student.S#,Sname

数据库知识总结(2)范式

      二、重要知识点

SELECT Sname FROM Student WHERE Sname like ‘张%’;

二、重要知识点

     
 1. SQL 数据定义语句的操作对象有:模式、表、视图和索引。

     
 2. SQL 数据定义语句的命令动词是:CREATE、DROP 和 ALTER。

     
 3. RDBMS 中索引一般采用 B+树或 HASH 来实现

     
 4. 索引可以分为唯一索引、非唯一索引和聚簇索引三种类型。

图片 5

  6.SQL 创建表语句的一般格式为

     
        CREATE TABLE <表名>

     
      
 ( <列名> <数据类型>[ <列级完整性约束> ]

     
      
 [,<列名> <数据类型>[ <列级完整性约束>] ] …

     
        [,<表级完整性约束> ] ) ;

其中<数据类型>可以是数据库系统支持的各种数据类型,包括长度和精度。 

   
列级完整性约束为针对单个列(本列)的完整性约束, 包括 PRIMARY KEY、 REFERENCES表名(列名)、UNIQUE、NOT NULL 等。 

   
表级完整性约束可以是基于表中多列的约束,包括 PRIMARY KEY ( 列名列表) 、FOREIGN KEY REFERENCES 表名(列名) 等。

     
 7. SQL 创建索引语句的一般格式为

     
        CREATE [UNIQUE] [CLUSTER] INDEX <索引名>

     
        ON <表名> (<列名列表> ) ;

其中UNIQUE:表示创建唯一索引,缺省为非唯一索引;

     
CLUSTER:表示创建聚簇索引,缺省为非聚簇索引;

     
<列名列表>:一个或逗号分隔的多个列名,每个列名后可跟 ASC 或 DESC,表示升/降序,缺省为升序。多列时则按为多级排序。 
  

   8. SQL 查询语句的一般格式为

     
      
 SELECT [ALL|DISTINCT] <算术表达式列表> FROM <表名或视图名列表>

     
        [ WHERE <条件表达式 1> ]

     
      
 [ GROUP BY <属性列表 1> [ HAVING <条件表达式 2 > ] ]

     
        [ ORDER BY <属性列表 2> [ ASC|DESC ] ] ;

其中

   
  ALL/DISTINCT: 缺省为 ALL, 即列出所有查询结果记录, 包括重复记录。 DISTINCT则对重复记录只列出一条

   
   算术表达式列表:一个或多个逗号分隔的算术表达式,表达式由常量(包括数字和字符串)、列名、函数和算术运算符构成。每个表达式后还可跟别名。也可用 *代表查询表中的所有列。

   
  <表名或视图名列表>: 一个或多个逗号分隔的表或视图名。 表或视图名后可跟别名。

   
  条件表达式 1:包含关系或逻辑运算符的表达式,代表查询条件。

   
  条件表达式 2:包含关系或逻辑运算符的表达式,代表分组条件。

   
  <属性列表 1>:一个或逗号分隔的多个列名。

   
  <属性列表 2>: 一个或逗号分隔的多个列名, 每个列名后可跟 ASC 或 DESC, 表示升/降序,缺省为升序。

     
 1. 主键: 能够唯一地标识一个元组的属性或属性组称为关系的键或候选键。 若一个关系有多个候选键则可选其一作为主键(Primary key)。

delete from Sc where S#=’001’and C#=’001′;

7)LIKE

LIKE 是另一个在 WHERE  子句中会用到的指令。基本上, LIKE
 能让我们依据一个模式(pattern) 来找出我们要的资料。语法为:
SELECT
“栏位名”  FROM “表格名”  WHERE “栏位名” LIKE {模式} 

     
 7. 用户定义的完整性:就是针对某一具体应用的数据必须满足的语义约束。包括非空、 唯一和布尔条件约束三种情况。

Delect SC
from course ,Teacher
where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname=’叶平’;

5)IN


SQL 中,在两个情况下会用到 IN  这个指令;这一页将介绍其中之一:与 WHERE
有关的那一个情况。在这个用法下,我们事先已知道至少一个我们需要的值,而我们将这些知道的值都放入
IN  这个子句。语法为:
SELECT “栏位名”  FROM “表格名”
 WHERE “栏位名” IN (‘值一’, ‘值二’, …)  

Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC
;

     
 3. 实体型之间的联系分为一对一、一对多多对多三种类型。

SELECT S# as 学生ID
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=’004′) AS 数据库
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=’001′) AS 企业管理
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=’006′) AS 英语
,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩
FROM SC AS t
GROUP BY S#
ORDER BY avg(t.score)

            U —— 组成该关系的属性名集合

复制代码 代码如下:

     
 1. 数据库系统由数据库、数据库管理系统、应用系统数据库管理员构成。

复制代码 代码如下:

   
  <表名或视图名列表>: 一个或多个逗号分隔的表或视图名。 表或视图名后可跟别名。

select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and
Score>80 and C#=’003′;

     
 5. 关系模型的完整性约束包括:实体完整性、参照完整性和用户定义完整性

18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分

4)AND OR

上例中的
WHERE 指令可以被用来由表格中有条件地选取资料。这个条件可能是简单的
(像上一页的例子),也可能是复杂的。复杂条件是由二或多个简单条件透过 AND
或是 OR 的连接而成。语法为:
SELECT “栏位名”  FROM “表格名”
 WHERE “简单条件”  {[AND|OR] “简单条件”}+

复制代码 代码如下:

其中

复制代码 代码如下:

     
 1.触发器是用户定义在基本表上的一类由事件驱动的特殊过程。由服务器自动激活, 能执行更为复杂的检查和操作,具有更精细和更强大的数据控制能力。使用 CREATE TRIGGER 命令建立触发器。

2、查询平均成绩大于60分的同学的学号和平均成绩;

      2. 关系的 5 种基本操作是选择、投影、并、差、笛卡尔积。

复制代码 代码如下:

一、相关概念

8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;

10)COUNT

这个关键词能够帮我我们统计有多少笔资料被选出来,语法为:
SELECT COUNT(“栏位名”) FROM
“表格名”

复制代码 代码如下:

            R —— 关系名

27、查询出只选修了一门课程的全部学生的学号和姓名

数据库也是计算机类笔试面试中不可避免会遇到的考点,尤其是银行和部分传统软件类公司。这里根据整理的资料,对数据库的相关知识也做个总结吧。希望学过数据库但长时间不用的同学根据这些知识能够回忆和重拾,没学过的同学能掌握一些数据库的基础知识。

12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名;

     

select SC.S#,Student.Sname,count(C#) AS 选课数
from SC ,Student
where SC.S#=Student.S# group by SC.S# ,Student.Sname having
count(C#)=1;

数据库知识总结(2)范式

 一、相关概念和知识点

       1.数据依赖:反映一个关系内部属性与属性之间的约束关系,是现实世界属性间相互联系的抽象,属于数据内在的性质和语义的体现。
       2. 规范化理论:是用来设计良好的关系模式的基本理论。它通过分解关系模式来消除其中不合适的数据依赖,以解决插入异常、删除异常、更新异常和数据冗余问题。
       3. 函数依赖:简单地说,对于关系模式的两个属性子集X和Y,若X的任一取值能唯一确定Y的值,则称Y函数依赖于X,记作X→Y。
       4. 非平凡函数依赖:对于关系模式的两个属性子集X和Y,如果X→Y,但Y!⊆X,则称X→Y为非平凡函数依赖;如果X→Y,但Y⊆X,则称X→Y为非平凡函数依赖。
       5. 完全函数依赖:对于关系模式的两个属性子集X和Y,如果X→Y,并且对于X的任何一个真子集X’,都没有X’→Y,则称Y对X完全函数依赖。
       6. 范式:指符合某一种级别的关系模式的集合。在设计关系数据库时,根据满足依赖关系要求的不同定义为不同的范式。
       7. 规范化:指将一个低一级范式的关系模式,通过模式分解转换为若干个高一级范式的关系模式的集合的过程。
       8. 1NF:若关系模式的所有属性都是不可分的基本数据项,则该关系模式属于1NF。
       9. 2NF:1NF关系模式如果同时满足每一个非主属性完全函数依赖于码,则该关系模式属于2NF。
       10. 3NF:若关系模式的每一个非主属性既不部分依赖于码也不传递依赖于码,则该关系模式属于3NF。
       11. BCNF:若一个关系模式的每一个决定因素都包含码,则该关系模式属于BCNF。
       12. 数据库设计:是指对于一个给定的应用环境,构造优化的数据库逻辑模式和物理结构,并据此建立数据库及其应用系统,使之能够有效地存储和管理数据,满足各种用户的应用需求,包括信息管理要求和数据操作要求。
       13. 数据库设计的6个基本步骤:需求分析,概念结构设计,逻辑结构设计,物理结构设计,数据库实施,数据库运行和维护。
       14. 概念结构设计:指将需求分析得到的用户需求抽象为信息结构即概念模型的过程。也就是通过对用户需求进行综合、归纳与抽象,形成一个独立于具体DBMS的概念模型。
       15. 逻辑结构设计:将概念结构模型(基本E-R图)转换为某个DBMS产品所支持的数据模型相符合的逻辑结构,并对其进行优化。
       16. 物理结构设计:指为一个给定的逻辑数据模型选取一个最适合应用环境的物理结构的过程。包括设计数据库的存储结构与存取方法。
       17. 抽象:指对实际的人、物、事和概念进行人为处理,抽取所关心的共同特性,忽略非本质的细节,并把这些特性用各种概念精确地加以描述,这些概念组成了某种模型。

     
 18. 数据库设计必须遵循结构设计和行为设计相结合的原则。

     
 19. 数据字典主要包括数据项、数据结构、数据流、数据存储和处理过程五个部分。
       20. 三种常用抽象方法是分类、聚集和概括。
       21. 局部 E-R 图之间的冲突主要表现在属性冲突、命名冲突和结构冲突三个方面。

       22.
数据库常用的存取方法包括索引方法、聚簇方法和
HASH方法三种。
       23. 确定数据存放位置和存储结构需要考虑的因素主要有: 存取时间、
存储空间利用率和维护代价等。

二、细说数据库三范式

2.1 第一范式(1NF)无重复的列

       第一范式(1NF)中数据库表的每一列都是不可分割的基本数据项
       同一列中不能有多个值
     
 即实体中的某个属性不能有多个值或者不能有重复的属性。
     
 简而言之,第一范式就是无重复的列。

     
 在任何一个关系数据库中,第一范式(1NF)是对关系模式的基本要求,不满足第一范式(1NF)的数据库就不是关系数据库。

2.2 第二范式(2NF)属性完全依赖于主键[消除部分子函数依赖]

     
满足第二范式(2NF)必须先满足第一范式(1NF)。

     
第二范式(2NF)要求数据库表中的每个实例或行必须可以被惟一地区分。

     
为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。 
     
第二范式(2NF)要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性,如果存在,那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体,新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。简而言之,第二范式就是属性完全依赖于主键。
2.3 第三范式(3NF)属性不依赖于其它非主属性[消除传递依赖]

     
满足第三范式(3NF)必须先满足第二范式(2NF)。

     
简而言之,第三范式(3NF)要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

     
例如,存在一个部门信息表,其中每个部门有部门编号(dept_id)、部门名称、部门简介等信息。那么在的员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表,则根据第三范式(3NF)也应该构建它,否则就会有大量的数据冗余。简而言之,第三范式就是属性不依赖于其它非主属性。

2.4
具体实例剖析

     
下面列举一个学校的学生系统的实例,以示几个范式的应用。

     
 在设计数据库表结构之前,我们先确定一下要设计的内容包括那些。学号、学生姓名、年龄、性别、课程、课程学分、系别、学科成绩,系办地址、系办电话等信息。为了简单我们暂时只考虑这些字段信息。我们对于这些信息,说关心的问题有如下几个方面。

     
 1)学生有那些基本信息 
       2)学生选了那些课,成绩是什么 
       3)每个课的学分是多少 
       4)学生属于那个系,系的基本信息是什么。

       首先第一范式(1NF):数据库表中的字段都是单一属性的,不可再分。这个单一属性由基本类型构成,包括整型、实数、字符型、逻辑型、日期型等。在当前的任何关系数据库管理系统(DBMS)中,不允许你把数据库表的一列再分成二列或多列,因此做出的都是符合第一范式的数据库。 

       我们再考虑第二范式,把所有这些信息放到一个表中(学号,学生姓名、年龄、性别、课程、课程学分、系别、学科成绩,系办地址、系办电话)下面存在如下的依赖关系。 
       1)(学号)→ (姓名, 年龄,性别,系别,系办地址、系办电话) 
       2) (课程名称) → (学分) 
       3)(学号,课程)→ (学科成绩)

根据依赖关系我们可以把选课关系表SelectCourse改为如下三个表: 

     
 学生:Student(学号,姓名, 年龄,性别,系别,系办地址、系办电话); 
       课程:Course(课程名称, 学分); 
       选课关系:SelectCourse(学号, 课程名称, 成绩)。

     
 事实上,对照第二范式的要求,这就是满足第二范式的数据库表,若不满足第二范式,会产生如下问题 
数据冗余: 同一门课程由n个学生选修,”学分”就重复n-1次;同一个学生选修了m门课程,姓名和年龄就重复了m-1次。

更新异常: 1)若调整了某门课程的学分,数据表中所有行的”学分”值都要更新,否则会出现同一门课程学分不同的情况。 
               
 2)假设要开设一门新的课程,暂时还没有人选修。这样,由于还没有”学号”关键字,课程名称和学分也无法记录入数据库。

删除异常 : 假设一批学生已经完成课程的选修,这些选修记录就应该从数据库表中删除。但是,与此同时,课程名称和学分信息也被删除了。很显然,这也会导致插入异常。

       我们再考虑如何将其改成满足第三范式的数据库表,接着看上面的学生表Student(学号,姓名, 年龄,性别,系别,系办地址、系办电话),关键字为单一关键字”学号”,因为存在如下决定关系:

     
(学号)→ (姓名, 年龄,性别,系别,系办地址、系办电话) 
但是还存在下面的决定关系 
       (学号) → (所在学院)→(学院地点, 学院电话) 
       
即存在非关键字段”学院地点”、”学院电话”对关键字段”学号”的传递函数依赖。 
     
 它也会存在数据冗余、更新异常、插入异常和删除异常的情况(这里就不具体分析了,参照第二范式中的分析)。根据第三范式把学生关系表分为如下两个表就可以满足第三范式了:

     
 学生:(学号, 姓名, 年龄, 性别,系别); 
       系别:(系别, 系办地址、系办电话)。

SQL语句总结

SQL语句中常用关键词及其解释如下:

select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC
where score <60 group by S# having count(*)>2)group by S#;

第一节

复制代码 代码如下:

     
 2. 数据模型的组成要素是:数据结构、数据操作、完整性约束条件

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;

     
 1. SQL:结构化查询语言的简称, 是关系数据库的标准语言。SQL 是一种通用的、 功能极强的关系数据库语言, 是对关系数据存取的标准接口, 也是不同数据库系统之间互操作的基础。集数据查询、数据操作、数据定义、和数据控制功能于一体。

Select S#,Sname from (select Student.S#,Student.Sname,score ,(select
score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#=’002′)
score2
from Student,SC where Student.S#=SC.S# and C#=’001′) S_2 where
score2 <score;

第四节

40、查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩

     
 5. 自主存取控制(DAC)的 SQL 语句包括 GRANT 和 REVOKE 两个。 用户权限由数据对象和操作类型两部分构成。

select c# from sc where scor e <60 order by C# ;

   
  条件表达式 1:包含关系或逻辑运算符的表达式,代表查询条件。

17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示:
学生ID,,数据库,企业管理,英语,有效课程数,有效平均分

     
      
 ON <表名> (<列名列表> ) ;

31、1981年出生的学生名单(注:Student表中Sage列的类型是datetime)

其中UNIQUE:表示创建唯一索引,缺省为非唯一索引;

47、查询没学过“叶平”老师讲授的任一门课程的学生姓名

11)GROUP BY

GROUP
BY 语句用于结合合计函数,根据一个或多个列对结果集进行分组。语法为:
SELECT “栏位1”, SUM(“栏位2”)  FROM
“表格名”  GROUP BY “栏位1” 

复制代码 代码如下:

一、相关概念

3、查询所有同学的学号、姓名、选课数、总成绩;

9)函数

函数允许我们能够对这些数字的型态存在的行或者列做运算,包括
AVG (平均)、COUNT (计数)、MAX (最大值)、MIN (最小值)、SUM
(总合)。语法为:
SELECT “函数名”(“栏位名”) FROM
“表格名”  

复制代码 代码如下:

8)ORDER BY

我们经常需要能够将抓出的资料做一个有系统的显示。这可能是由小往大
(ascending)  或是由大往小(descending)。在这种情况下,我们就可以运用
ORDER BY 这个指令来达到我们的目的。语法为:
SELECT “栏位名”  FROM “表格名
[WHERE “条件”] ORDER BY “栏位名” [ASC, DESC] 

13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;

一、相关概念和知识

复制代码 代码如下:

   
  ALL/DISTINCT: 缺省为 ALL, 即列出所有查询结果记录, 包括重复记录。 DISTINCT则对重复记录只列出一条。

复制代码 代码如下:

       1. SQL
数据定义语句的操作对象有:模式、表、视图和索引。
       2. SQL 数据定义语句的命令动词是:CREATE、DROP 和 ALTER。
       3. RDBMS 中索引一般采用 B+树或 HASH 来实现。
       4. 索引可以分为唯一索引、非唯一索引和聚簇索引三种类型。

复制代码 代码如下:

     
 3. TCSEC/TDI 标准由安全策略、责任、保证和文档四个方面内容构成。

20、查询如下课程平均成绩和及格率的百分数(用”1行”显示):
企业管理(001),马克思(002),OO&UML (003),数据库(004)

2)DISTINCT

在上述
SELECT 关键词后加上一个 DISTINCT
就可以去除选择出来的栏位中的重复,从而完成求得这个表格/栏位内有哪些不同的值的功能。语法为
SELECT DISTINCT “栏位名” FROM
“表格名”。

SELECT DISTINCT top 3
SC.S# As 学生学号,
Student.Sname AS 学生姓名 ,
T1.score AS 企业管理,
T2.score AS 马克思,
T3.score AS UML,
T4.score AS 数据库,
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) +
ISNULL(T4.score,0) as 总分
FROM Student,SC LEFT JOIN SC AS T1
ON SC.S# = T1.S# AND T1.C# = ‘001’
LEFT JOIN SC AS T2
ON SC.S# = T2.S# AND T2.C# = ‘002’
LEFT JOIN SC AS T3
ON SC.S# = T3.S# AND T3.C# = ‘003’
LEFT JOIN SC AS T4
ON SC.S# = T4.S# AND T4.C# = ‘004’
WHERE student.S#=SC.S# and
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) +
ISNULL(T4.score,0)
NOT IN
(SELECT
DISTINCT
TOP 15 WITH TIES
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) +
ISNULL(T4.score,0)
FROM sc
LEFT JOIN sc AS T1
ON sc.S# = T1.S# AND T1.C# = ‘k1’
LEFT JOIN sc AS T2
ON sc.S# = T2.S# AND T2.C# = ‘k2’
LEFT JOIN sc AS T3
ON sc.S# = T3.S# AND T3.C# = ‘k3’
LEFT JOIN sc AS T4
ON sc.S# = T4.S# AND T4.C# = ‘k4’
ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) +
ISNULL(T4.score,0) DESC);

   
列级完整性约束
为针对单个列(本列)的完整性约束, 包括 PRIMARY KEY、 REFERENCES表名(列名)、UNIQUE、NOT NULL 等。 

select c#,count(S#) from sc group by C#;

7)LIKE

LIKE
是另一个在 WHERE  子句中会用到的指令。基本上, LIKE
 能让我们依据一个模式(pattern) 来找出我们要的资料。语法为:
SELECT “栏位名”  FROM “表格名”
 WHERE “栏位名” LIKE {模式} 

23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[
<60]

            
 [,<表级完整性约束> ] ) ;

25、查询各科成绩前三名的记录:(不考虑成绩并列情况)

     
 6. 参照完整性: 用于维护实体之间的引用关系。 它要求一个关系的外键要么为空, 要么取与被参照关系对应的主键值,即外键值必须是主键中已存在的值

select S# from SC where C#=’004’and score <60 order by score
desc;

            
 CREATE TABLE <表名>

复制代码 代码如下:

图片 6

select a.S# from (select s#,score from SC where C#=’001′) a,(select
s#,score
from SC where C#=’002′) b
where a.score>b.score and a.s#=b.s#;

     
      
 [ WHERE <条件表达式 1> ]

复制代码 代码如下:

     
 4. 常见的数据模型包括:关系、层次、网状、面向对象、对象关系映射等几种。

SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM SC t1
WHERE score IN (SELECT TOP 2 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#;

       1.
Data:数据,是数据库中存储的基本对象,是描述事物的符号记录。
       2.
Database:数据库,是长期储存在计算机内、有组织的、可共享的大量数据的集合。
       3.
DBMS:数据库管理系统,是位于用户与操作系统之间的一层数据管理软件,用于科学地组织、存储和管理数据、高效地获取和维护数据。
       4.
DBS:数据库系统,指在计算机系统中引入数据库后的系统,一般由数据库、数据库管理系统、应用系统、数据库管理员(DBA)构成。
       5.
数据模型:是用来抽象、表示和处理现实世界中的数据和信息的工具,是对现实世界的模拟,是数据库系统的核心和基础;其组成元素有数据结构、数据操作和完整性约束。
       6.
概念模型:也称信息模型,是按用户的观点来对数据和信息建模,主要用于数据库设计。
       7.
逻辑模型:是按计算机系统的观点对数据建模,用于DBMS实现。
       8.
物理模型:是对数据最底层的抽象,描述数据在系统内部的表示方式和存取方法,在磁盘或磁带上的存储方式和存取方法,是面向计算机系统的。
       9.
实体和属性:客观存在并可相互区别的事物称为实体。实体所具有的某一特性称为属性。
     
 10.E-R图:即实体-关系图,用于描述现实世界的事物及其相互关系,是数据库概念模型设计的主要工具。
     
 11.关系模式:从用户观点看,关系模式是由一组关系组成,每个关系的数据结构是一张规范化的二维表。
     
 12.型/值:型是对某一类数据的结构和属性的说明;值是型的一个具体赋值,是型的实例。
     
 13.数据库模式:是对数据库中全体数据的逻辑结构(数据项的名字、类型、取值范围等)和特征(数据之间的联系以及数据有关的安全性、完整性要求)的描述。
     
 14.数据库的三级系统结构:外模式、模式和内模式。
     
 15.数据库内模式:又称为存储模式,是对数据库物理结构和存储方式的描述,是数据在数据库内部的表示方式。一个数据库只有一个内模式。
     
 16.数据库外模式:又称为子模式或用户模式,它是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述,是数据库用户的数据视图。通常是模式的子集。一个数据库可有多个外模式。
     
 17.数据库的二级映像:外模式/模式映像、模式/内模式映像。

复制代码 代码如下:

 
    6. 阐述数据库三级模式、二级映象的含义及作用。
        数据库三级模式反映的是数据的三个抽象层次: 模式是对数据库中全体数据的逻辑结构和特征的描述。内模式又称为存储模式,是对数据库物理结构和存储方式的描述。外模式又称为子模式或用户模式,是对特定数据库用户相关的局部数据的逻辑结构和特征的描述。

SELECT SC.C# as 课程ID, Cname as 课程名称
,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 –
85]
,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 –
70]
,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 –
60]
,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]
FROM SC,Course
where SC.C#=Course.C#
GROUP BY SC.C#,Cname;

其中<数据类型>可以是数据库系统支持的各种数据类型,包括长度和精度。 

select Sname,count(*) from Student group by Sname having
count(*)>1;

   
  <属性列表 2>: 一个或逗号分隔的多个列名, 每个列名后可跟 ASC 或 DESC, 表示升/降序,缺省为升序。

复制代码 代码如下:

 
     7. SQL 创建索引语句的一般格式为

9、查询所有课程成绩小于60分的同学的学号、姓名;

     
 5. . 实体完整性:用于标识实体的唯一性。它要求基本关系必须要有一个能够标识元组唯一性的主键,主键不能为空,也不可取重复值

复制代码 代码如下:

     
  数据库三级模式通过二级映象在 DBMS 内部实现这三个抽象层次的联系和转换。外模式面向应用程序, 通过外模式/模式映象与逻辑模式建立联系, 实现数据的逻辑独立性。 模式/内模式映象建立模式与内模式之间的一对一映射, 实现数据的物理独立性。

复制代码 代码如下:

1)SELECT

将资料从数据库中的表格内选出,两个关键字:从
(FROM) 数据库中的表格内选出 (SELECT)。语法为
SELECT “栏位名” FROM “表格名”。

update SC set score=(select avg(SC_2.score)
from SC SC_2
where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and
Course.T#=Teacher.T# and Teacher.Tname=’叶平’);

     
 2.计算机系统存在技术安全、管理安全和政策法律三类安全性问题。

select C#,Cname
from Course
where C# in (select c# from sc group by c#)

      1. 关系数据库语言分为关系代数、关系演算和结构化查询语言三大类。

select Student.S#,Student.Sname
from Student,SC
where Student.S#=SC.S# group by Student.S#,Student.Sname having
count(C#) <(select count(C#) from Course);

     
 4. 常用存取控制方法包括自主存取控制(DAC)和强制存取控制(MAC)两种。

30、查询同名同性学生名单,并统计同名人数

   
  条件表达式 **2**:包含关系或逻辑运算符的表达式,代表分组条件。

复制代码 代码如下:

       4.笛卡尔乘积,选择和投影运算如下

SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分
FROM SC L ,SC AS R
WHERE L.C# = R.C# and
L.score = (SELECT MAX(IL.score)
FROM SC AS IL,Student AS IM
WHERE L.C# = IL.C# and IM.S#=IL.S#
GROUP BY IL.C#)
AND
R.Score = (SELECT MIN(IR.score)
FROM SC AS IR
WHERE R.C# = IR.C#
GROUP BY IR.C#
);

   
表级完整性约束
可以是基于表中多列的约束,包括 PRIMARY KEY ( 列名列表) 、FOREIGN KEY REFERENCES 表名(列名) 等。

select Sname, CONVERT(char (11),DATEPART(year,Sage)) as age
from student
where CONVERT(char(11),DATEPART(year,Sage))=’1981′;

3)WHERE

这个关键词可以帮助我们选择性地抓资料,而不是全取出来。语法为
SELECT “栏位名” FROM “表格名”
WHERE “条件” 

select Student.S#,Student.Sname from Student,SC where
Student.S#=SC.S# and SC.C#=’001’and exists( Select * from SC as
SC_2 where SC_2.S#=SC.S# and SC_2.C#=’002′);

     
  8. SQL 查询语句的一般格式为

复制代码 代码如下:

     
 2. 数据定义:数据定义功能包括模式定义、表定义、视图和索引的定义。

42、查询不同课程成绩相同的学生的学号、课程号、学生成绩

第三节

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;

     
      
 [ ORDER BY <属性列表 2> [ ASC|DESC ] ] ;

复制代码 代码如下:

            D —— 属性组 U 中属性所来自的域

34、查询课程名称为“数据库”,且分数低于60的学生姓名和分数

            F —— 属性间的数据依赖关系集合

复制代码 代码如下:

=====================================

Select Sname,isnull(score,0)
from Student,SC,Course
where SC.S#=Student.S# and SC.C#=Course.C# and
Course.Cname=’数据库’and score <60;

28、查询男生、女生人数

复制代码 代码如下:

复制代码 代码如下:

select S#,Sname
from Student
where S# not in (select Student.S# from Student,SC where S.S#=SC.S#
and score>60);

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;

33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

48、查询两门以上不及格课程的同学的学号及其平均成绩

43、查询每门功成绩最好的前两名

select Student.S#,Student.Sname
from Student
where S# not in (select distinct( SC.S#) from SC,Course,Teacher where
SC.C#=Course.C# and Teacher.T#=Course.T# and
Teacher.Tname=’叶平’);

复制代码 代码如下:

select distinct A.S#,B.score from SC A ,SC B where A.Score=B.Score and
A.C# <>B.C# ;

复制代码 代码如下:

5、查询没学过“叶平”老师课的同学的学号、姓名;

35、查询所有学生的选课情况;

复制代码 代码如下:

复制代码 代码如下:

select distinct SC.S#,Sname
from Student,SC
where Student.S#=SC.S# and C# in (select C# from SC where
S#=’001′);

select C# as 课程号,count(*) as 人数
from sc
group by C#
order by count(*) desc,c#

select Sname from Student where S# not in (select S# from
Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and
Tname=’叶平’);

37、查询不及格的课程,并按课程号从大到小排列

SELECT 1+(SELECT COUNT( distinct 平均成绩)
FROM (SELECT S#,AVG(score) AS 平均成绩
FROM SC
GROUP BY S#
) AS T1
WHERE 平均成绩 > T2.平均成绩) as 名次,
S# as 学生学号,平均成绩
FROM (SELECT S#,AVG(score) 平均成绩
FROM SC
GROUP BY S#
) AS T2
ORDER BY 平均成绩 desc;

复制代码 代码如下:

Select count(Ssex) as 男生人数 from Student group by Ssex having
Ssex=’男’;
Select count(Ssex) as 女生人数 from Student group by Ssex having
Ssex=’女’;

SELECT SC.S#,SC.C#,Sname,Cname
FROM SC,Student,Course
where SC.S#=Student.S# and SC.C#=Course.C# ;

50、删除“002”同学的“001”课程的成绩

16、向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“003”课程的同学学号、2、
号课的平均成绩;

14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;

复制代码 代码如下:

复制代码 代码如下:

26、查询每门课程被选修的学生数

复制代码 代码如下:

复制代码 代码如下:

41、查询各个课程及相应的选修人数

10、查询没有学全所有课的同学的学号、姓名;

复制代码 代码如下:

select S#
from sc
group by s#
having count(*) > = 2

select count(*) from sc;

SELECT SUM(CASE WHEN C# =’001′ THEN score ELSE 0 END)/SUM(CASE C# WHEN
‘001’ THEN 1 ELSE 0 END) AS 企业管理平均分
,100 * SUM(CASE WHEN C# = ‘001’ AND score >= 60 THEN 1 ELSE 0
END)/SUM(CASE WHEN C# = ‘001’ THEN 1 ELSE 0 END) AS
企业管理及格百分数
,SUM(CASE WHEN C# = ‘002’ THEN score ELSE 0 END)/SUM(CASE C# WHEN
‘002’ THEN 1 ELSE 0 END) AS 马克思平均分
,100 * SUM(CASE WHEN C# = ‘002’ AND score >= 60 THEN 1 ELSE 0
END)/SUM(CASE WHEN C# = ‘002’ THEN 1 ELSE 0 END) AS 马克思及格百分数
,SUM(CASE WHEN C# = ‘003’ THEN score ELSE 0 END)/SUM(CASE C# WHEN
‘003’ THEN 1 ELSE 0 END) AS UML平均分
,100 * SUM(CASE WHEN C# = ‘003’ AND score >= 60 THEN 1 ELSE 0
END)/SUM(CASE WHEN C# = ‘003’ THEN 1 ELSE 0 END) AS UML及格百分数
,SUM(CASE WHEN C# = ‘004’ THEN score ELSE 0 END)/SUM(CASE C# WHEN
‘004’ THEN 1 ELSE 0 END) AS 数据库平均分
,100 * SUM(CASE WHEN C# = ‘004’ AND score >= 60 THEN 1 ELSE 0
END)/SUM(CASE WHEN C# = ‘004’ THEN 1 ELSE 0 END) AS 数据库及格百分数
FROM SC

21、查询不同老师所教不同课程平均分从高到低显示

select S#,Sname from Student,SC where Student.S#=SC.S# and C# in
select C# from SC where S#=’1001′;

复制代码 代码如下:

复制代码 代码如下:

29、查询姓“张”的学生名单

SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM SC t1
WHERE score IN (SELECT TOP 3 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#;

复制代码 代码如下:

复制代码 代码如下:

复制代码 代码如下:

select S# from SC where C# in (select C# from SC where S#=’1002′)
group by S# having count(*)=(select count(*) from SC where
S#=’1002′);

Insert SC select S#,’002′,(Select avg(score)
from SC where C#=’002′) from Student where S# not in (Select S# from
SC where C#=’002′);

select S#,Sname
from Student
where S# in (select S# from SC ,Course ,Teacher where
SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’叶平’
group by S# having count(SC.C#)=(select count(C#) from Course,Teacher
where Teacher.T#=Course.T# and Tname=’叶平’));

select Sname,SC.S# ,avg(score)
from Student,SC
where Student.S#=SC.S# group by SC.S#,Sname having
avg(score)>85;

复制代码 代码如下:

46、查询全部学生都选修的课程的课程号和课程名

select Student.Sname,score
from Student,SC,Course C,Teacher
where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and
Teacher.Tname=’叶平’ and SC.score=(select max(score)from SC where
C#=C.C# );

15、删除学习“叶平”老师课的SC表记录;

22、查询如下课程成绩第 3 名到第 6
名的学生成绩单:企业管理(001),马克思(002),UML
(003),数据库(004)
[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩

32、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列

Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
 
问题:
1、查询“001”课程比“002”课程成绩高的所有学生的学号;