数据库—应付考试—期末复习

系统的学一下几个常用查询

1、select … from A, B 的用法

产生一个临时表，行数是A，B行数之积，列数是A，B列数之和
（笛卡尔积，A，B表的每一条记录都连接在一起）

2、select … from A，B where … 的用法

对select … from A, B产生的结果用where中的条件进行过滤

3、select … from A join B on … 的用法

• 在表名后面跟一个大写字母，可以给表起个别名
• on不能省，有join必须有on
• A join B是连接两个表，on后面是连接的条件，用左边的表 去连接右边的表
• 查询的多个表中有同一个字段，需要在前面写 ： 表名.

4、select … from A join on B … where … 的用法

先连接，连接之后再过滤。先看要把哪几个表连接起来，然后再看如何过滤。

简答题

1、简述计算机数据管理技术发展的三个阶段

• 人工管理
• 文件系统管理
• 数据库管理

2、数据管理系统有哪些主要功能

• 数据定义
• 数据操纵增删改查
• 数据库的事务管理和运行管理

编程题

删除id大于50的记录
delete
from Student
Where id>50
删除：delete 

将Student表中所有符合id>50的记录的name字段改成"test"。
Update Student
set name='test'
where id>50
修改表：
	update ...
	set ...
	where ... 

统计订单表（orders）中顾客的订单数量，并插入results表中
insert into results
select CustomerID, count(*) as OrderCount
from orders
group by CustomerID
取别名：... as ...

//1、按照学号的降序对学生表排序后，显示前 4 条记录。
Select Top 4 *
from Student
Order by s_id Desc 
//用Top 4显示前4条记录

查询平均分高于80分的学生姓名，分数和姓名不在一张表里
Select sname 
from stu 
where sno in (
	Select sno 
	from sc 
	group by sno 
	having  avg(grade)>80 )
!!!要先按学号分组才能算每个人的平均成绩 

Select  '浙江'  As name,  Sum(population) As population 
from city 
Where name in ('杭州','宁波','温州')
Union
Select  '江苏'  As name,  Sum(population) As population 
from city 
Where name in ('苏州','南京','无锡')

Union：对多个select的结果集进行并操作，自动跳过重复行 
语法：  SELECT 列名 
		FROM 表名
		UNION
		SELECT 列名 
		FROM 表名

查询sh_goods表中用户评分score在前20%的商品名称
select top 20 percent name
from sh_goods
order by score desc
输出前百分之几：select top ... percent ... 

查询goods表中价格为NULL的商品信息
select id,name,price
from goods
where price is NULL
注意NULL的表达： ... is NULL

//2、往课程表插入一条记录。 该表的前三列是字符类型，最后两列是数值
//类型。 插入的数据： 6，大学计算机基础， NULL， 2， 32
Insert into Course 
Values( '6' , '大学计算机基础' , NULL , 2 , 32 )
//别忘记写into

//4、 将学生表中， 计算机科学专业的 1992 年 1 月 1 日以后出生的学生检
//索出来，以此创建一个视图 vwXuanJu， 代表年满 18 岁有选举权的学生。
Create view vwXuanJu As
Select *
from Student
where sMajor = '计算机科学' and sBirthday > '1992-01-01'
//大于某年月日：直接 > '****-**-**'
//创建视图：Create view ... As

//5、利用多表连接技术查询选修了“大学英语”课程的学生的学号、姓名、
//课程号、课程名和分数。
Select Student.s_id, Student.sname, Course.c_id, Course.cname, Grade.grade
from Student, Course, Grade
Where Course.cname='大学英语' and Student.s_id=Grade.s_id and Grade.c_id=Course.c_id
多表查询 一定 别忘记写最后面的约束条件

//6、查询与赵青在同一个院系的所有学生的信息，要求使用嵌套查询，结
//果显示学号、姓名、 院系。 注意， 表中的赵青有可能有重名情况。
Select s_id, sname, sdepartment
from Student
Where sdepartment In(
		Select sdepartment
		from Student
		Where sname='赵青')
理解一下嵌套查询，限定sdepartment的值

//7、 编写一个存储过程 prcFindByName， 接收学生的姓名作为参数，查询
//输出学生的学号、姓名、课程号、课程名、分数。 已知姓名在表中定义为
//varchar(10)。
Create Proc prcFindByName @StuName varchar(10)
As
	Select Student.s_id, Student.sname, Course.c_id, Course.cname, Grade.grade
	from Student, Course, Grade
	Where Grade.s_id = Student.s_id and Grade.c_id=Course.c_id and Student.sname = @StuName

重点理解创建存储过程的格式：
Create Proc（Proc是创建存储格式的关键字）
@StuName整体是形参名，其后必须跟这他的参数类型varchar(10)
As别忘了写

8. 查询 Teacher 表中有哪几种职称，要求职称不能有重复数据。
Select Distinct Tport
from Teacher
或
Select Tport
from Teacher
Group by tport

9、查询学生的政治面貌分布(也就是有哪些政治面貌， 以及对应的人数)。
Select spoliticalStatus, Count(s_id)
from Student
Group by spoliticalStatus

Group by能把后面的表头按照不同进行分组，不同的值会单独列出一个表，此时刚好可以用count统计数量

10、查询 Student 表中 EMAIL 满足在@左侧有且仅有 1 个字符的学生信息，结果显
示姓名、院系、邮箱地址 3 列。
Select Sname, Sdept, EMAIL
from Student
Where EMAIL Like '_@%'

select * from emp where ename like '_[A-F]%'
--把ename中第二个字母是A或B或C或D或E或F的记录输出

select * from emp where ename like '_[^A-F]%'
--把ename中第二个字母不是A或B或C或D或E或F的记录输出

select * from emp where ename like '_[A,F]%'
--把ename中第二个字母是A或F的记录输出

select * from emp where ename like '_[^A,F]%'
--把ename中第二个字母不是A或F的记录输出

模糊查询：

• "_"号表示任意单个字符,该字符号只能匹配一个字符

• "%"符号是字符匹配符,能匹配0个或更多字符的任意长度的字符串

• “[]”能查询一定范围的数据，也能输出一些特殊字符

11、写出将新生记录（201920123，张大荣，男，法学院， 19 岁）插入到 Student
表中的 SQL 语句。
Insert into Student
Values( ‘201920123’, ‘张大荣’, ‘男’, ‘法学院’, 19 )
或
Insert into Student
Values( ‘201920123’, ‘张大荣’, ‘男’, ‘法学院’, 19 , NULL ,NULL )
注：
别忘记加双引号！！！！！！！！！！！！！

创建名为Zhangsan的登录名，使用的sql语句是：
Create login ZhangSan with password='123456'
语法：create login ... whth password='...'

将登录名ZhangSan映射到当前数据库中，映射后的数据库用户名也是张三
CREATE user ZhangSan for login ZhangSan
语法：create user ... for login ...

--登录账户改密码
alter login ZhangSan with password='aabb'

--登录账户改名
alter login ZhangSan with name=jiar
语法：alter login ... whth ...=... 

--删除数据库用户
drop user ZhangSan

--删除登录账户
drop login ZhangSan
语法：deop ... ...

4.写出授予用户 Rose 具有对 Teacher 表的插入和删除权。
Grant Insert, Delete ON Teacher TO Rose
Grant：授予权限，Grant ... on ... to ... 

3、从数据库用户 ZhangSan 收回创建表的权限。
Revoke Create Table
from ZhangSan
Revoke：收回权限，revoke ... from ...

从ZhangSan用户收回一张表的检索权限
revoke Select On MyTable 
from ZhangSan
revoke ... on ... from ...

5.创建视图 smajor_num，统计每个专业的学生人数，要求列出专业和人数。
Create View smajor_num as
select Smajor, count(*)nums
from student
Group by smajor
Group by：分组，分组后只能输出 分组形成的表的总体信息，如总体的和，总体的平均值

8. 创建存储过程，用于返回指定课程名的课程平均成绩， 课程名在执行该存储过程时给出），
并写出运行该存储过程查询“数据库原理及应用”课程平均成绩的指令。
Create Proc find_avg @Cname char(30)
as
	begin
	Select AVG(Grade)
	from SC
	Where Cno=(
		Select Cno
		from Course
		Where Cname='@Cname')
	end

execute find_abg '数据库原理及应用'
begin end：流程控制语句，划出一个代码块
execute：执行存储过程，execute 存储过程名 参数

4. 将没有学生选修的课程信息删除。
delete from Course
Where c_id not in(
		Select c_id
		from grade)
delete：delete from ... where ... 
* 先选择要从哪个表里面删东西，然后限制删除的条件

5. 为用户 John 授予 Student 表的查询和修改权。
Grant Select, Update on Student to John
查询权： Select
修改权： Update

create table Score
(	//这里的cNo是一个外键，其他表里面已经有了，'cNo char(12) Not NULL' 这里要严格和已有的相同 
	cNo char(12) Not NULL foreign Key references Student(Cno),//foreign Key references ...() 
	cCourseNo char(12) Not NULL foreign Key references Course(cCourseNo)  //是引用外键的格式
	iGrade int check(iGrade>=0 and iGrade<=100)			//限制语句紧跟在定义的后面 
	sbirthday datetime,
)
引用外键格式：... foreign Key references ...(...)
主键：... Primary Key
限制语句：check( ... )
日期类型：datetime

Drop table Score   //删除表

在Grade表中添加约主键束
例： 
Alter Table Grade
Add Constraint PK_Grade 
Primary Key(s_id,c_id)

标准格式：
Alter table 表名
add constraint PK_ID  //命名一个主键名称 
Primary Key(ID)

数据库设计题-画E-R图

1、基本知识：

• 实体：矩形表示，里面写实体名

• 属性：椭圆表示，里面写属性名，无向边与实体连接

• 联系：菱形表示，里面写联系名，无向边与实体连接（标注1:1、1:n、m:n）

2、注：联系 也可以有属性

3、画图步骤：

• 确定实体

• 确定联系名 和 联系类型

• 确定实体属性 和 联系属性

E-R图 转 关系模式

1、什么是关系模式：

• 关系名（属性1，属性2， …,属性n）

例：职工（职工号，职工名，部门号，工资级别）

• 一般就是把实体写前面，后面跟括号，写它的属性

• 还要考虑主键和外键

2、主键 外键 外键参考关系

• 主键：能唯一标识一条记录的属性，很多属性可以做主键，但之间只能是其中一个

• 外键：一个关系的主键在别的关系里叫外键，外键要么空，要么对应其主键的一个值

• 外键参考关系：一定指向其他关系的主键，基本都是由n指向1

        联系的属性的关系中：箭头指向该联系连接的实体的主键

例题讲解

1：n的关系，n的那边要有1的那边的主键，在自己这里作为外键，然后连个箭头到1的那边。

从选择题学起

1、数据库 包含 数据库系统 和 数据库管理系统

• 数据库管理系统是在操作系统支持下的系统软件
• 数据库物理设计与具体的 DBMS密切相关
• 数据库系统的特点是（数据共享）、数据独立、减少数据冗余、避免数据不一致和加强了数据保护
• （数据库操作系统）不是数据库的组成成员

2、安全管理

2.1 登录名

• 登录名用于连接服务器
• 登录名是一个账号密码，可以登录并使用SQL Server软件
• 系统默认一个sa，有所有权限
• 新创建的登录名只能进去看，没权限，干不了事

2.2 用户

• 现有登录名 ，后又用户，用户在登录名之下
• 用户可以具体访问某个数据库，用户在数据库之下
• 我们 通过 用户来使用数据库
• 新建的登录名没有用户，用不了数据库
• 用户没有密码
• 登录名可以和用户名一样

3、NoSQL（非关系型数据库）

• 包含四大类型：键-值对存储、列存储数据库、文档存储数据库、图形数据库
• 数据库中不存在关系
• 不限于固定的结构，如：不用创建表、字段，可以直接存储数据、和自定义数据
• 几乎没有限制，扩展能力强（很容易扩展）
• 应用场景：

NoSQL：MongoDB、HBase、Redis

4、数据库系统结构（三级模式结构）

4.1 模式（概念模式）

• 全体数据的逻辑结构
• 一个数据库只有一个模式
• 处于三层的中间层

4.2 外模式（又名：子模式、用户模式、用户视图、视图）

• 是应用程序使用的 局部 数据逻辑 结构
• 一个应用程序对应一个外模式（视图），但一个外模式可被多个应用使用
• 介于模式与应用之间
• 多个外模式对应一个模式，他们都是模式的子集，可以重叠

4.3 内模式（存储模式、物理模式）

• 一个数据库只一个内模式

4.4 外模式/模式映像

• 定义了他们之间的映射关系，保证数据的逻辑独立性

4.5 模式/内模式映像

• 定义了数据的逻辑结构到存储结构的对应关系，保证数据的物理独立性
  

5、关系代数

5.1 关系运算符

5.1.1 选择 σ

其他符号

5.1.2 投影 Π（select）

5.1.3 连接（连接两个表）

• 自然连接：共同属性等值连接，若有多个属性，则这几个属性都相等才链接
• 外连接：在自然连接基础上，保留两边的悬浮元组
• 左外连接：保留左边的悬浮元组
• 右外连接：保留右边的悬浮元组

5.1.4 除法

保留包含除数的

例题：

5.2 笛卡尔积

两个相同的要舍弃，顺序颠倒的舍弃一组

5.3 并相容性

5.3.1

• 参与运算的两个关系及其相关属性之间有一定的对应性、可比性或意义关联性
• 并、差、交必须满足并相容性

6、范式判断

6.1 步骤

• 求候选键
• 非键属性是否部分依赖于候选键
• 若是，则为 1NF
• 若否，至少是2NF
• 非键属性是否有传递依赖于候选键
• 若是，则为 2NF
• 若否，则至少是3NF
• 判断左边是否全为候选键
• 若是，则为BCNF
• 若否，则为3NF
• 
  

如何求候选键

先找出来，L、LR、R；在L和LR中看谁能把全部都推出来，那就是候选键
视频例题讲解

部份依赖

传递依赖

例题讲解，9分钟处

7、数据模型

8、杂