数据库基础

关系数据库基础

计算机对数据进行管理经历了文件管理 和数据库管理两个发展阶段。

文件管理数据缺点：

1.编写应用程序不方便；

2.数据冗余不可避免；

3.应用程序依赖性；

4.不支持对文件的并发访问；

5.数据间关系弱；

6.难以按不同用户的愿望表示数据；

7.无安全控制功能；

 

数据库管理系统的好处：

1.将相互关联的数据集成在一起；

2.数据冗余小；

3.程序与数据相互独立；（1指当数据的存储方式发生变化；2指当数据的逻辑结构发生变化时；）

4.保证数据的安全和可靠；

5.最大限度地保证数据的正确性；

6.数据可以共享并能保证数据的一致性。

 

数据库系统（DBs）和数据库管理系统（DBMS）

数据库系统组成：数据库（DB）；数据库管理系统（DBMS）；应用程序（n）；数据库系统管理员（DBA）。

数据和数据模型

数据模型这个工具来抽象、表示和处理现实世界中的数据和信息。

数据模型分类：概念层数据模型（也称概念模型或信息模型）；组织层数据模型（也称组织模型）；

组织模型（也称组织方式）包括：层次模型（用树形结构组织数据）、网状模型（用图形结构组织数据）、关系模型（用简单二维表结构组织数据）、对象-关系模型（用复杂的表格以及其他结构组织数据）。

先有概念层数据模型，然后再到组织层数据模型。

数据模型一般由数据结构、数据操作、数据完整性约束三部分组成，这三部分成为数据模型三要素。

概念层数据模型

实体-关系模型（E-R模型、工具是E-R图）。

关系数据模型三要素：1关系模型的数据结构 2关系表中的每一列都是不可再分的基本属性 3 表中的每一行数据称为一个元组。

关系模型中的数据完整性约束：实体完整性、参照完整性、用户定义完整性。

E-R模型向关系模型的转换

一个实体转换为一个关系模式。

1.对于1:1联系可以转换为一个独立的关系模式，也可以与任意一端所对应的关系模式合并。

2.对于1：n联系可以转换为一个独立的关系模式，也可以与n端所对应的关系模式合并。

3.一个m：n联系转换为一个独立的关系模式，与该联系相连的各实体的码以及联系本身的属性均转换为该关系模式的属性。

例如：

对于1:1联系，有两种转换方法；

1 将联系与某一实体的关系模式合并。

①经理（经理号，经理名，电话）

 部门（部门号，部门名，经理号）

或者

②经理（经理号，经理名，电话，部门号）

部门（部门号，部门名）

 

2 将联系转换为一个独立的关系模式（查询效率低不建议）

经理（经理号，经理名，电话）

部门（部门号，部门名）

部门_经理（经理号，部门号）

  

对于1:n联系，有两种转换方法；

1 将联系与n端实体的关系模式合并。

①部门（部门号，部门名）

职工（职工号，部门号，职工名，性别）

 

 

2 将联系转换为一个独立的关系模式（查询效率低不建议）

部门（部门号，部门名）

职工（职工号，职工名，性别）

部门_职工（部门号，职工号）

对于m：n联系，必须将联系转换为一个独立的关系模式。

教师（教师号，教师名，职称）

课程（课程号，课程名，学分）

授课（教师号，课程号，授课时数）

Student 表结构

列名

含义

数据类型

约束

Sno

学号

Char(7)

主键

Sname

姓名

Nchar(5)

非空

SID

身份证号

Char(18)

取值不重复

Ssex

性别

Nchar(1)

默认值为”男”

Sage

年龄

tinyint

取值范围15~45

Sdept

所在系

Nvarchar(20)

 

 

Course表结构

列名

含义

数据类型

约束

Cno

课程号

Char(6)

主键

Cname

课程名

Nvarchar(20)

非空

Credit

学分

Numeric(3,1)

大于0

Semeter

学期

tinyint

 

 

SC表结构

列名

含义

数据类型

约束

Sno

学号

Char(7)

主键，引用student的外键

Cno

课程名

Char(6)

主键，引用Course的外键

Grade

成绩

tinyint

 

 

 

创建数据库和表格：

Create table student(

Sno char(7) primary key,

Sname nchar(5) not null,

Sid char(18) unique,

Ssex nchar(1) defaullt’男’,

Sage tinyint check(Sage>=15 and Sage<=45),

Sdept nvarchar(20))

)

Create table Course(

Cno char(6) primary key,

Cname nvarchar(20) not null,

Credit numeric(3,1) check(credit>0),

Semester tinyint)

)

Create table SC(

Sno char(7) not null,

Cno char(6) not null,

Grade tinyint,

Prumary key (Sno,Cno),

Foreign key (sno)references Student(sno),

Foreign key(cno) references course(Cno))

)

 

查询语句的基本结构

Select <目标列名序列>

From <表名>

Where <行选择条件>

Group by <分组依据列>

Having <组选择条件>

Order by <排序依据列>

 

All ：表示在结果集中可以包含重复行，All是默认值。

Distinct ：指定在结果集中只能包含唯一行。（消除重复的行）

单表查询：

查询指定的列：

查询全体学生的学号和姓名

select sno,sname,from student;

查询全体学生的信息。

Select * from student;

查询经过计算的列：

Select sname,year(getdate()) - year(birthdate) from student;

查询全体学生的姓名和出生年份，在出生年份前加一列“出生年份”。

Select sname ,’出生年份’,year(birthdate()) from student;

Select sname , year(getdate()) - year(birthdate) as 年龄 from student;

选择标准的若干元组：

Select sno from sc;

Select distinct sno from sc;

比较运算符：

=、>、>=、<=、<、<> (不等于)、!=、!>、!<

查询计算机系所有学生的姓名：

Select sname from student where dept=’计算机系’;

 

查询有所年龄20岁以下的学生姓名和年龄：

Select sname,year(getdate()) - year(birthdate) as 年龄from student where year(getdate)) - year(birthdate) < 20;

 

查询所有考试成绩不及格学生的学号;

Select distinct sno from sc where grade<60;

 

查询考试成绩在80~90之间的学生的学号、课程号、成绩；

Select sno,cno,grade from sc where grade beteeen 80 and 90;

Select sno,cno,grade from sc where grade>=80 and grade<=90;

 

查询考试成绩不在80~90的学生的学号、课程号、成绩；

Select sno,cno,grade from sc where grade not between 80 and 90;

Select sno,cno,gradefrom sc where grade<80 or grade>90;

 

查询1991年6~8月出生的学生的姓名和出生日期；

Select sname,birthdate from student where birthdate between ‘1991/6/1’ and ‘1991/8/31’;

 

查询信息管理系，通信工程系，和计算机系的学生的姓名和性别；

Select sname,sex from student where dept in (‘信息管理系’,’通信工程系’,’计算机系’);

Select sname,sex from student where dept=’信息管理系’ or dept=’通信工程系’ or dept=’计算机

系’;

Select sname,sex from student where dept =’信息’ or ‘通信’ or ’计算机’;

 

查询信息管理系，通信工程系，和计算机系3个系之外的其他系的学生的姓名和性别；

Select sname,sex from student where dept not in (‘信息管理系’,’通信工程系’,’计算机系’);

Select sname,sex from student where dept != ‘信息’ and dept !=’通信’ and dept !=’计算机’;

 

Like运算符一般用法：

列名 [not ] like <匹配串>

匹配串可以包含如下4种通配符：

_(下划线)：匹配任意一个字符；__(两个下划线)：匹配两个字符;

%(百分号)：匹配0个或多个字符；

[]:匹配[  ]中的任意一个字符，如 [afcd] 表示匹配a、f、c、d中的任何一个，若比较字符是连续的，例如要匹配c、d、e、f中的任意一个，则可以写成  [c-f] 。

[^ ]：不匹配 [ ] 的任意一个字符，如 [^afcd] 表示不匹配a、f、c、d中的任何一个，同样若比较字符是连续的，例如[^c-f] 表示不匹配c、d、e、f中的任意一个 。

 

查询姓“张”的同学的详细信息：

Select * from student where sname like ‘张%’;

查询姓“张”，“李”，“刘”学生的详细信息:

Select * from student where sname like ’[张李刘]’;

查询名字的第二个字为“小”或“大”的学生的详细信息。

Select sno,sname  from student where sname like ‘_[小大]%’;

查询所有不姓刘的学生的姓名：

Select sname from student where sname not like ‘刘%’;

查询学号最后一位不是2,3,5的学生得详细信息:

Select * from student where sno like ’*[^235]’;

 

通配符：escape （特殊符号的查询0

例如：在field 字段中查找包含字符串“30%”的记录，可在where自居中指定：

Where field like ‘%30 !%%’ escape ‘!’ ;         !%表示%

在field 字段中查找包含下划线（_）的记录，

Where field like ‘%!_%’ escape ‘!’ ;         !_ 表示_

 

涉及空值的查询：

判断列取值为空的子句：is null

判断列取值不为空的子句：is not null

查询还没有考试的学生的学号和相应的课程号：

Select sno,cno, from sc where grade is null;

 

多重条件查询：

查询计算机系男生的姓名：

Select sname from student where dept = ‘计算机系’ and sex=’男’;

查询c002和c003课程中考试成绩在80~90的学生的学号、课程号和成绩:

Select sno,cno,grade from sc where cno in (‘c002’,’c003’)and grade between 80 and 90;

Select sno,cno,grade from sc where (cno = ‘c002’ or cno=’c003’) and grade between 80 and 90;

Or 运算符的优先级小于and 因此可以通过（）来改变运算顺序。

对查询结果进行排序：order by <列名> [ASC （升序默认）  DESC（降序）]

查询全体学生的详细信息，结果按系名升序排列，同一个系的学生按出生日期降序排列。

Select * from student order by dept asc ,birthdate desc;

使用聚合函数统计数据：聚合函数不能出现在where子句中。

Count(*):统计表中元组的个数

Count(dictinct <列名>)：统计本列的非空列值个数

Sum(<列名>)：计算列植的和值

Avg(<列名>):计算列值的平均值

Max(<列名>)：计算列值的最大值

Min(<列名>):得到列值的最小值

 

统计学生总人数：

Select count(*) from student;

统计选修了课程的学生人数：

Select count(distinct sno) from sc;

 

统计“08114”号学生的考试平均成绩：

Select avg(grade) from sc where sno=’081114’;

查询“c001”号课程考试成绩的最高分和最低分：

Select max(grade) 最高分,min(grade) 最低分 from sc where cno=’c001’;

 

对数据进行分组统计：group by

统计每门课程的选课人数：

Select cno as 课程号 ,count (sno) as 选课人数 from sc group by cno;

统计每个学生的选课门数和平均成绩：

Select sno 学号,COUNT(*) 选课门数,avg(grade) 平均成绩 from sc group by sno;

统计每个系的女生人数：

Select dept , count(*) 女生人数 from student where sex=’女’ group by dept;

统计每个系的男生人数和女生人数，结果按系名的升序排序：

Select dept 系名, sex 性别, count(*) 人数 from student  group by dept , sex order by dept;

 

使用 having 子句：

HAVING 子句用于对分组的统计结果再进行筛选，他的功能与where子句类似，但他用于组而不是单个记录。他通常和group by 子句一起使用。

查询选课门数超过三门的学生的学号和选课门数。

Select sno ,count(*) 选课门数 from sc group by sno having count(*)>3;

查询选课门数大于或者等于4,门的学生的平均成绩和选课门数。

Select sno,avg(grade) 平均成绩,count(*) 选课门数 from sc group by sno having count(*)>=4;

 

查询计算机系和信息管理系每个系的学生人数：

Select dept, count(*) from student group bu dept having dept in (’计算机系’,’信息管理系’);

Selelct dept,count(*) from student where dept in(‘计算机系’,’信息管理系’) group by dept;（效率高）

查询每个系的男生人数：

Select dept ,count(*) from student where sex=’男’ group by dept;

 

多表连接查询：

内连接： from 表1 [inner] join 表2 on <连接条件>

查询学生及其选课的详细信息：

Select * from student  inner  join  sc  on  student.sno  = sc.sno;

查询计算机系学生的修课情况，要求列出学生的姓名、所修课的课程号和成绩；

Select sname,cno,grade from student join sc on student,sno = sc.sno where dept=’计算机系’;

 

三张表：查询“信息管理系”选修了“计算机文化学”课程的学生信息，要求列出学生的姓名和考试成绩：

Select sname,grade from student s join sc on s.sno=sc.sno join course c on c.cno = sc.cno

Where dept = ‘信息管理系’ and cname=’计算机文化学’;

查询所有选修了java课程的学生情况，列出学生的姓名和他们所在的系：

Select sname,dept from student s join s.sno= sc.sno join course c on c.cno = sc.cno where cname=’java’;

统计每个系的平均考试成绩：

Select dept ,avg(grade) as 平均成绩 from student s join sc on s.sno = sc.sno group by dept;

统计计算机系每个学生的选课门数，平均成绩，最高成绩和最低成绩：

Select s.sno,count(*) as 总数,avg(grade) 平均成绩,max(grade) as 最高成绩,min(grade) ac 最低成绩 from student s join sc on s.sno = sc.sno where dept=’计算机系’ group by s.sno;

查询没有人选的课程的课程名：

Select cname from course c left join sc on c.cno = sc.cno where sc.cno is null;

查询计算机系没选课的学生的姓名和性别：

Select sname,sex from student s left join sc on s.sno = sc.sno where dept=’计算机系’and sc.sno is null;

统计计算机系每个学生的选课门数，包括没选课的学生：

Select s.sno as 学号, count(sc.cno) as 选课门数 from student s left join sc on s.sno = sc.sno where dept=’计算机系’ group by s.sno;

使用top限制结果集行数：

查询考试成绩最高的3个成绩，列出学号，课程号，成绩。

Select top 3 sno,cno,grade from sc order by grade desc;

若要包括并列第三名的成绩，可写成：使用with ties 必须使用出现order bySelect top 3 with ties sno,cno,grade from sc order by grade desc;

将查询结果保存在新表中：

Select 查询列表序列 into<新表名>

From 数据源.........

将计算机系的学生信息保存在#computerStudent 局部临时表中：

Select sno ,sname,ssex,sage into #computerStudent from student where dept=’计算机系’

将选了java课程的学生的学号及成绩存入永久表java_Grade：

Select sno,grade into java_Grade from sc join Course c on c.cno  = sc.cno where cname=’java’;

插入数据：

Insert into sc values(‘0811’,’c001’,95),(‘0812’,’c002’,96),(‘0813,’c003’,’98)

更新数据：

将所有学生的成绩加十分

Update sc set grade =  grade+10;

基于本表的更新，将c001号课程的学分改为5：

Update course set credit = 5 where sno=’c001’;

基于其他表条件的更新：将计算机全体学生的成绩加5分：

Update sc set grade = grade+5 from sc join student on sc.sno = student.sno where dept=’计算机系’;

删除数据：

delete删除数据或记录；drop：删除表结构，永久删除。

删除employee表中的全部数据

Delete  from  employee;

基于本表删除:有条件删除：删除所有考试成绩不及格的学生的选课记录。

Delete from sc where grade<60;

基于其他表的删除：删除计算机系考试成绩不及格的学生的选课记录：

Delete from sc from sc join student on sc.sno where dept=’计算机系’ and grade<60;

删除employee表中2.5%的行数据：

Delete top(2.5) percent from employee;

 

高级查询：

 

查询选了java课程的学生的学号、姓名、所在系和成绩，并对所在系进行如下处理：

 当所在系为”计算机系”时，在查询结果中显示“CS”；

当所在系为“信息管理系”时，在查询结果中显示“IM”；

当所在系为“通信工程系”时，在查询结果中显示“COM”；

Select s.sno 学号,sname 姓名，

Case dept

 When ‘计算机系’   then  ’CS’

 When ’信息管理系’ then  ‘IM’

 When ‘通信工程系’ then  ‘COM’

 End  as所在系 ,grade 成绩

From student s join sc on s.sno = sc.sno join course c on c.cno=sc.cno where cname=’java’;

 

 

Select sno,

Case

When grade>=90 then ’优’

When grade between 80 and 89 then ’良’

When grade between 70 and 79 then ‘中’

When grade between 60 and 69 then ‘及格’

When grade<60 then’不及格’

End as 成绩

From sc

Where cno=’c001 ’

 

管理权限：

Grant：用于授予权限

Revoke:用户收回或撤销权限

Deny:用于拒绝权限

为用户user1授予student 表的查询权限

Grant select on student to user1;

为用户user1授予sc表的查询和插入权限。

Grant select,insert on sc to user1;

收回用户user1对student表的查询权限。

Revoke select on student from user1;

拒绝用户user1具有SC表的更改权限：

Deny update on sc to user1;