必知的知识点
第一部分——基础知识
-
数据库:长期储存在计算机中、有组织的、可共享的大量数据的集合。
-
数据库系统特点:
(1)数据结构化
(2)数据的共享性高,冗余度低,易扩充
(3)数据独立性高
(4)数据由DBMS统一管理和控制。 -
数据库管理系统的主要功能:
(1)数据定义功能;
(2)数据组织、存储和管理;
(3)数据操纵功能;
(4)数据库的事物管理和运行管理;
(5)数据库的建立和维护功能;
(6)其他功能。 -
数据库系统:数据库、数据库管理系统、数据库管理员、应用程序组成。
-
数据模型:
(1)概念模型:了解实体之间的联系:一对多、多对一、多对多
掌握E-R图的画法:
确定实体;
画出属性;
找到实体之间的联系。
*例题:
设有商品和顾客两个实体,商品实体包括商品编号、名称、单价等属性,顾客实体包括
顾客编号、姓名、联系电话等属性,两个实体存在如下的关系:
一个顾客可购买多种任意商品
每次可购买一定数量的商品(一天只限一次购物)
将E-R图转成关系模式: *注意:* _1对1不需要再建一个表,只需通过外键就可以连接,多对多需要导入一个基本表_
(2)数据模型(逻辑模型和数据模型):
三个组成要素:数据结构、数据操作、数据的完整性约束条件。
数据模型:层次模型、网状模型、关系模型、面向对象模型。
-
三级模式:内模式(只有一个)、模式、外模式。
-
知道关系的基本概念,掌握关系代数的方法
- 例题:
- 现有图书借阅关系数据库模式如下:
图书(图书号,书名,作者,单价,库存量)
读者(读者号,姓名,工作单位,地址)
借阅(图书号,读者号,借期,还期,备注)
其中,还期为 NULL 表示该书未还。
用关系代数表示如下查询:
(1) 查询读者号为“R016”的读者姓名、工作单位
(2) 查询图书号为“B5”的读者姓名
(3) 查询读者“李林”所借阅图书的书名
(4) 查询读者“李林”所借阅图书中未还的书名
*图书借阅关系查询
- 关系完整性约束:
实体完整性:(主键约束) primary key 主键不能为空,且唯一。
参照完整性:(外键约束) foreign key 可以为空或是参照表的主键值
用户定义完整性:check语句 ;not null;unique。
数据库安全性:保护数据库防止不合法使用所造成的数据泄露、更改、破坏。
方法:1.用户标识和鉴别;
2.存取控制
*自主存取控制:
授权:
GRANT <权限>[,<权限>]……
ON <对象类型><对象名>[,<对象类型><对象名>]……
TO <用户>[,<用户>]……
[WITH GRANT OPTION]
授予王明权限
GRANT SELECT ON Student TO 王明
收回权限
REVOKE <权限>[,<权限>]……
ON <对象类型><对象名>[,<对象类型><对象名>]……
FROM <用户>[,<用户>]……[CASCADE|RESTRICT]
*强制存取控制
3.视图
4.审计
5.数据加密
数据库安全性:数据的正确性和相容性
规范化:
掌握通过函数依赖确定码。
数据库设计步骤:
1.需求分析
2.概念结构设计
3.逻辑结构设计
4.物理结构设计
5.数据库实施
6.数据库运行和维护
数据字典:数据流、数据结构、数据项、数据存储、处理过程。
第二部分— SQL语言(结构化查询语言)
一、SQL语言特点:
综合统一
高度非过程化
面向集合的操作方式
以同一种语法结构提供多种使用方式
语言简洁,易学易用
对于数据统计方便直观
二、数据定义:
模式:
1.创建模式
CREATE SCHEMA<模式名>AUTHORIZATION<用户名>
2.删除模式:
DROP SCHEMA<模式名><CASCADE|RESTRICT>
CASCADE(级联):删除模式同时把模式中所有的数据库对象全部删除
RESTRICT(限制):如果已经定义了数据库对象(表,视图 等)。则拒绝删除语句 ,当没有下属对象时,才能执行此语句
表:
1.创建表:
CREATE TABLE <表名>(<列名> <数据类型> [完整性约束条件],
...............................................
<列名> <数据类型> [完整性约束条件]
);
2.修改表:
ALTER TABLE<表名>
[ADD [COLUME] <新列名><数据类型>[完整性约束]]
[ADD <表级完整性约束>]
[DROP [COLUMN] <列名> [CASCADE|RESTRICT]]
[DROP CONSTRAINT<完整性约束名> [RESTRICT|CASCADE]]
[ALTER COLUMN <列名><数据类型>];
3.删除表:
DROP TABLE<表名>[RESTRICT|CASCADE]
索引:
1.建立索引:
CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
ON <表名> (<列名> [<次序>],......);
2.修改索引:
ALTER INDEX<旧索引名> RENAME TO <新索引名>
3.删除索引:
DROP INDEX <索引名>;
视图:
1.视图定义
CREATE VIEW 视图名
AS 子查询
WITH CHECK OPTION //可以省略
2.视图查询、更新
对于查询更新要转到对基本表上的操作。所以和基本表的查询语句类似,只是把表名的位置换成视图名就可以
3.视图删除
DROP VIEW 视图名 【CASCADE】//CASCADE为级联删除,可以省略
三、数据查询
一般格式如下:
SELECT [ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]……
FORM <表名或视图名>[,<表名或视图名>……]|(<SELECT 语句>) <别名>
[WHERE <条件表达式>]
[GROUP BY <列名1>[HAVING <条件表达式>]]
[ORDER BY <列名2>[ASC|DESC]
1.查询全部列
select * from 表名;
2.取消取值重复的行
select distinct 列名 from 表名 //去掉表中重复的行必须制定短语distinct,如果没有指定,缺省值为all
条件查询必知:
1.WHERE 子句常用的查询条件:
查询条件 | 谓词 |
---|---|
比较 | =,>,<,>=,<=,!=,<>,!>,!<,not+比较运算符 |
确定范围 | BETWEEN AND,NOT BETWEEN AND |
确定集合 | IN,NOT IN |
字符匹配 | LIKE,NOT LIKE |
空值 | IS NULL,IS NOT NULL |
多重条件 | AND,OR,NOT |
WHERE子句后不能跟聚合函数,若需要使用聚合函数作为条件出现可以通过使用GROUP BY 语句后的having语句进行使用
2.聚集函数:
聚集函数 | 作用 |
---|---|
COUNT (*) | 统计元组的个数 |
COUNT([DISTINCT/ALL]<列名>) | 统计一列中值得个数 |
SUM([DISTINCT/ALL]<列名>) | 计算一列中值的总和 |
MAX([DISTINCT/ALL]<列名>) | 求一列中值最大值 |
MIN([DISTINCT/ALL]<列名>) | 求一列值中最小值 |
AVG([DISTINCT/ALL]<列名>) | 计算一列中值的平均值 |
当聚合函数遇到空值时,除count(*)以外都跳过空值只处理非空值
3. ORDER BY 语句必须位于查询语句的最后
连接查询
1.等值连接(有重复列):
使用where子句进行多个表的连接。
2.自然连接没有重复列
3.自身连接:通过给表起别名实现。
4.外连接:
SELECT 查询属性
FORM 表名1
LEFT |RIGHT OUTER JION 表名2 ON 条件;
嵌套查询—子查询
1.带有IN谓词的子查询
查询与“刘晨”在同一个系学习的学生
SELECT Sdept,Sno,Sname
FORM Student
WHERE Sdept IN (
SELECT Sdept FROM Student WHERE Sname='刘晨')
2.带有比较运算符的子查询
与IN的集合查询相似,只需把IN改为比较运算符即可
3.带有ANY(SOME)或ALL谓词的子查询
方法与以上一致
4.带有EXISTS谓词的子查询
**不返回任何数据,只产生逻辑真值或逻辑假值
查询所有选修了1号课程的学生姓名
SELECT Sname FROM Student
WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= '1')
集合查询
(1)集合并
查询计算机科学系的学生及年龄不大于19岁的学生
SELECT * FROM Student WHERE Sdept= 'CS'UNION
SELECT * FROM Student WHERE Sage<=19
(2)集合交
查询计算机科学系且年龄不大于 19 岁的学生
SELECT * FROM Student WHERE Sdept='CS'
INTERSECT
SELECT * FROM Student WHERE Sage<=19
(3)集合差
查询计算机科学系且年龄大于19岁的学生
SELECT * FROM Student WHERE Sdept='CS'
EXCEPT
SELECT * FROM Student WHERE Sage <=19
四、数据更新——增删改表中的数据
-
添加数据:
语法:
insert into 表名(列名1,列名2,……列名n)values(值1,值2,……值n);
注意:a.列名与对象一一对应。
b.如果表名后不定义列名,则默认给所有列添加值
insert into 表名 values(值1,值2,……值n);
c.除了数据类型,其他都要用引号(单、双都可)。
-
删除数据:
语法:
delete from 表名[where 条件];
若不加条件则删除表中所有数据。
若删除所有记录:
delete from 表名;
truncate table 表名;**效率高,推荐使用。**
- 修改数据:
update 表名 set 列名 [where 条件];
*例题:
- 仓库管理系统
在某仓库管理系统中,有两个表:KC 表和 CKMX 表,其结构和部分数据如下:
表 1、KC 表结构和数据
材料代码 材料名称 单位 单价 库存数量
01 计算机 台 5000.00 10
02 电视机 台 2000.00 20
… … … … …
表 2、CKMX 表结构和数据
编号 材料代码 出库日期 出库数量
1 01 2009-10-10 4
2 01 2009-11-20 3
3 02 2009-11-20 5
… … … …
其中,KC 表保存当前仓库库存材料的信息,CKMX 表存放库存材料的出库名细。
例如,KC 表第一行表示该仓库中现有计算机 10 台。CKMX 表第二行表示 2009 年 11
月 20 日从仓库中出库 3 台计算机。
(1) 试写出创建 KC 表和 CKMX 表的 SQL 语句,要求定义实体和参照完整性。
(2) 写出在 KC 表上按材料名称建立唯一索引的 SQL 语句。
(3) 创建一个视图,用于查询计算机的出库明细。
(4) 写出将表中数据插入到相应表中的语句
(1) 创建表
CREATE TABLE KC
(
材料代码 char(2) PRIMARY KEY,
材料名称 char(20),
单位 char(10),
单价 numeric(10,2),
库存数量 int
)
CREATE TABLE CKMX
(
编号 char(4) PRIMARY KEY,
材料代码 char(2) REFERENCES KC(材料代码),
出库日期 date,
出库数量 int
)
(2) 创建索引
CREATE UNIQUE INDEX index1 ON KC(材料名称)
(3) 创建视图
CREATE VIEW viewComputer
AS
SELECT * FROM CKMX WHERE 材料代码 = '01'
(4) 插入数据
INSERT INTO KC VALUES('01','计算机','台',5000.00,10)
INSERT INTO KC VALUES('02','电视机','台',2000.00,20)
INSERT INTO CKMX VALUES('1','01','2009-10-10',4)
INSERT INTO CKMX VALUES('2','01','2009-10-20',3)
INSERT INTO CKMX VALUES('3','02','2009-10-20',5)
(5) 统计
SELECT 材料代码,COUNT(*) as 次数, SUM(出库数量) as 出库数量
from CKMX
group by 材料代码
(6) 查询
SELECT ckmx.编号,出库日期,材料名称,单价,出库数量,出库数量*单价 as 金额
FROM KC,CKMX
WHERE KC.材料代码 = CKMX.材料代码
更多推荐
数据库概论知识点总结
发布评论