数据库原理与应用(复习)
2021-07-21 11:07:17 31 举报AI智能生成
数据库原理与应用是一门研究如何有效地组织、存储和管理数据的学科。它涉及到数据库的设计、实现、优化和维护等方面。在复习这门课程时，我们需要掌握数据库的基本概念，如数据模型、关系模型、SQL语言等。此外，我们还需要了解数据库的体系结构，包括内模式、外模式和模式。在实际应用中，我们需要学会如何使用数据库管理系统（DBMS）来创建、查询、更新和删除数据。同时，我们还需要关注数据库的安全性和完整性问题，以确保数据的安全和准确。总之，数据库原理与应用是一门实用性很强的学科，通过学习这门课程，我们可以更好地理解和应用数据库技术。
作者其他创作
大纲/内容
第一章 数据库概述
基本概念
数据和信息的区别
数据库(DB)
数据库管理系统(DBMS)
数据库系统(DBS)
技术发展
手工管理
文件管理
数据库管理
数据模型
层次模型
网状模型
关系模型
关系数据库
要素
关系
表
元组
一行记录
属性
字段（列）
候选码（或关键字）
能唯一标识元组，一个关系中可有多个候选码
主码(或主键)
从候选码中选择一个作为主码,一个主码可以是一个，也可以是多个字段
约束
实体完整性
如：主键值不能为空，不能重复
参照完整性
如：员工表，部门表。员工表中的“所属部门编号”列受到部门表的约束
用户自定义完整性
如：规定“手机号”列的长度必须是多少位
操作
增加/删除/修改/查询
结构化查询语言(SQL)
作用
分支主题
特点
第二章 认识MySQL
数据库管理系统(DBMS)
Oracle
DB2
SQL Server
Access
MySQL
SQLite
MySQL概述
历史
1985年创立，2009年被Oracle收购
特点
性能高效、跨平台、简单易用、开源、支持多用户
运行机制(P31页)
分支主题
核心：配置文件
my.ini文件
MySQL安装配置
安装
msi版本：双击安装下一步即可，但一些系统易出错
zip版本：解压缩无需安装，需进行配置，不会出错
启动与停止服务
通过服务窗口
开始-运行-services.msc
右键我的电脑-管理-服务和应用程序
通过命令行：开始-运行-以管理员运行cmd：net start mysql ; net stop mysql
通过任务管理器：停止mysqld.exe进程
客户机连接服务器
通过CMD命令行窗口
通过MySQL自带命令行
若安装zip版本的没有此工具
通过WEB浏览器
如phpMyAdmin
第三方客户机程序
Navicat
Workbench
SQLYog
字符集
查看
show variables like 'character%';
分支主题
修改
通过更改my.ini配置文件永久性修改
通过命令临时修改
通过SQL脚本文件临时修改
第三章 数据库管理
创建数据库
常用命令
显示当前服务器版本
SELECT VERSION();
显示当前日期时间
SELECT NOW();
显示当前用户
SELECT USER();
显示当前数据库
SELECT DATABASE();
创建数据库
create database choose;
数据库命名规范
由任意字母、阿拉伯数字、下划线（_）和“$”组成
不能是纯数字，不能与其他数据库重名
不能使用MySQL关键字，名称最长可为64个字符
创建时判断是否重名
create database if not exists choose;
若重名可查看警告
show warnings;
创建数据库时设置字符集
create database choose character set gbk;
操作数据库
显示当前服务器下所有数据库
show databases;
显示数据库信息(字符集)
show create database choose;
修改字符集
alter database choose default character set utf8;
（其中character set 可缩写为charset）
选定当前数据库
use choose;
删除数据库
drop database choose;
避免重复删除
drop database if exists choose;
若重复删除会出现警告
show warnings;
数据库备份
1 准备工作(限制更新)
法1：停止MySQL服务
法2：通过命令：flush tables with read lock；加锁
备份后使用unlock tables；解锁
2 复制文件(如U盘)
若数据库中都是MyISAM存储引擎的表，
则只复制data文件夹下对应的数据库文件夹即可
若数据库中有InnoDB存储引擎的表，
则还需备份data文件夹下ib开头的日志文件
3 文件还原
关闭MySQL，将复制的文件粘贴到对应位置即可
第四章 数据表管理
基础知识
存储引擎
基本概念
MySQL中为了适应不同存储需求，设计了多种存储引擎
存储引擎是基于表的，不同表可以有不同引擎
显示MySQL支持的存储引擎
show engines;
其中InnoDB为默认存储引擎
MySQL常用引擎
InnoDB
支持事务安全（提交、回滚和崩溃恢复能力）、支持外键
适合于大量的增(insert)删(delete)改(update)日常操作
MyISAM
不支持事务安全、不支持外键
适合于执行大量查询(select)语句进行决策分析
修改默认存储引擎
临时修改
set default_storage_engine = MyISAM
永久修改
my.ini配置文件中：[mysqld]项下修改 default-storage-engine= MyISAM
数据类型
数值类型
整数
tinyint（1字节）
smallint（1字节）
mediumint（3字节）
int（4字节）
bigint（8字节）
小数
精确小数
decimal
浮点数
float
double
字符串类型
定长
char
变长
varchar
text
日期类型
date
time
year
datatime
timestamp
复合类型
enum
set
二进制类型
binary
varbinary
bit
blob
创建数据表
简单例子
创建表
查看当前数据库下所有表
show tables;
显示表信息
show create table student;
查看表结构
desc student;(或show columns from student;)
插入数据并查看
设置约束
非空约束(not null)
默认值约束(default)
检查约束(check)：MySQL不支持
通常通过Enum和Set数据类型，或者触发器实现检查约束
主键约束（primary key)
主键自动为设置为not null，可通过desc 表名字; 命令查看
一张表只能设置一个主键，但一个主键里面可以包含多个字段
设置自增字段(auto_increment)
默认从整数1开始自动编号，依次递增
必须和主键一起使用，否则创建表会失败
主键也可以不和自增一起使用
唯一约束(unique)
和主键的区别
一个表里可以有多个唯一约束
约束字段可以为空值
外键约束(foreign key)
外键列所在的表为子表，参照列所在的表为父表
父表和子表的存储引擎必须为InnoDB
子表中的外键列需要与父表的参照列数据类型相同
级联选项
cascade
父表记录删除(修改)后，子表自动删除(修改)
set null
父表记录删除(修改)后，子表自动设置为null
restrict
为默认值 父表记录删除(修改)操作时提示失败
no action
是SQL的标准写法,在MySQL中与 restrict 功能相同
设置存储引擎
在创建表得最后面加上engine = InnoDB
设置字符集
在创建表的最后面加上default charset = gbk
复制表结构
只复制结构
复制结构及内容
操作数据表
修改数据表
修改表名
法1：alter table student rename stu;
法2：rename table student to stu;
修改字段
添加新字段
删除字段
修改字段名(可同时修改字段类型、位置、设置默认值、非空)
仅修改字段类型
修改约束
添加约束
主键约束
唯一约束
外键约束
删除约束
主键约束
唯一约束
外键约束
修改存储引擎
alter table student engine = InnoDB;
修改字符集
alter table student default charset = gbk;
删除表
drop table student;
可一次性删除多个表，表之间用逗号隔开
删除有外键约束的父表，要先删除约束再删除表(或者先删除子表，再删除父表)
第五章 操作表记录
插入记录(insert)
1：insert into values 结构
向指定字段插入数据
字符串、日期、enum、set类型的字段需用单引号括起来
字段书写顺序可以和创建表时不一样
向表中所有字段插入数据
自增字段可用null代替，默认值字段可用default代替
注意：删除记录再添加，会造成自增字段会不连续
注意：因唯一约束插入失败，会造成自增字段会不连续
一次性插入多条
2：insert into select结构
3：插入时替换原纪录(replace)
若新纪录的主键或唯一约束的字段与旧记录相同，则先删旧记录再插新纪录
如果影响行数为1，说明表中没有重复记录
如果影响行数大于1，说明有重复记录被删除
修改记录(update)
修改单表记录
删除记录
delete
truncate（清空表）
相当于delete from 表名
当有外键约束时，任何情况下无法清空父表
清空表后会重置自增字段
特殊字符
当字符串中存在8个特殊字符序列时
字符序列被转义成对应的字符
分支主题
第六章 检索表记录(上)
准备数据
简单查询
查询列
查询某一列
查询student表的学号（某一列）
查询多列
查询student表姓名、性别和入学时间 (部分列)
查询全部列
查询student表的所有学生信息(全部列)
查询计算列
将course表中的学时period列和学分credit列进行除法运算
将grade表中的Grade列加10分显示
添加说明列
自定义列标题
条件查询
限定条件
比较运算
查询课程号为 Dp010001 的成绩情况
查询所有不及格的成绩情况
逻辑运算
优先级顺序：NOT> AND >OR
查询分数大于等于60且小于等于80的成绩信息
查询学分为 2 或者为 6 的课程信息
范围判断
查询分数大于等于60且小于等于80的成绩信息
查询出生日期不在1990年1月1日至1990年12月31日之间的学生信息
集合判断
查询学分为 2 或者为 6 的课程信息
空值判断
查询班主任为空的班级信息
模糊查询
_ 或 %
查询教师编号以03结尾的教师信息
查询学生姓名中第二字为“丽”的学生信息
正则表达式
查询家庭住址以“成都”开头的学生信息
查询家庭住址以“号”结尾的学生信息
查询学号出现“21”的学生信息
查询以'St010901'或'St010902'开头，且后面跟着4位数字的学生信息
第七章 检索表记录(中)
数据排序
排序
查询教师信息，以教师职称为依据排序
查询成绩信息，以学号升序，成绩降序排序显示
注意：对汉字来说，排序结果不对怎么办？
注意：对于空值，升序排列，最先显示；降序排列，最后显示
限制返回行数
查询课程号为 Dp010004 且成绩在前三名的成绩信息
查询成绩位于5-10名的成绩信息
删除重复项
查询学生表中不同的班级信息
查询学生表中学号和班号的组合信息
聚合函数
计数(COUNT)
查询班级表中班级的数量
查询班级表中班主任的数量
查询学生表中班级的数量
最大值(MAX)
查询课程号为Dp010001中的最高分和最低分
最小值(MIN)
查询教师出生年月中的最大值和最小值
平均值(AVG)
查询课程号为Dp010001中的总分和平均分
求和(SUM)
分类汇总
统计各个班的学生人数
统计不同职称的教师人数
统计每门课程的考试人数、最高分、最低分、平均分及总分
统计每门课程考试及格的人数
查询课程平均分超过70分的课程信息
统计班级名称不以01结尾的课程中，课程平均分大于70的课程信息
查询班级人数超过5人的班级信息
统计各个班级男生和女生人数
统计各个班的学生人数并计算合计数
条件转换
IF函数
判断Cs010901班级人数是否超过5
判断所有班级的人数是否超过5
将class表中的monitor列中空值替换为'待定'
将成绩表中的分数分为及格和不及格
统计各个班的学生人数并计算合计数
IFNULL 函数
判断Cs011104班级的班主任是否为空
判断所有班级的班主任是否为空
统计各个班的学生人数并计算合计数
简单CASE函数
查询教师姓名及职称，职称以高级（副教授以上）、中级（讲师）和初级（助教）的形式显示
统计各个班的学生人数并计算合计数
无法匹配空值但可以匹配长度为0的字符串(即空白值)
搜索CASE函数
查询成绩以“优”（>=90）、“良”(70-89)、“及格”(60-69)和“不及格”(<60)的方式显示
统计各个班的学生人数并计算合计数
第八章 检索表记录(下)
多表连接
内连接(inner join)
两种代码格式
注意：当使用别名时，代码中必须使用别名，不能使用原来的名字
等值连接
不同表之间连接
查询学生及其所在班级的信息
查询学生编号、学生姓名、班级编号及班级名称
查询教师姓名及其所在系部名称
查询学生及其所在班级和系部的信息
查询学生名、课程名和对应的成绩
查询课程名，教师姓名，班级名
查找‘张丽’的所有课程的成绩信息，包括姓名、课程号、成绩
自连接
查询和“张丽”处于同一班级的学生信息
查询和“朱于龙”职称相同的教师信息
查询同时选修了‘dp010001’和‘dp010003’的学生学号
非等值连接
不同表之间连接
没有实际意义，一般不用
自连接
查询比“数据库原理与应用”学分高的课程信息
查询和“王一平”不在同一个系的教师信息
外连接(outer join)
代码格式
左外连接
查询所有学生的成绩情况（包括未选修课程的学生）
右外连接
查询所有选修过课程的学生成绩情况
交叉连接(cross join)
代码格式
即左表中的每一行与右表中的每一行进行连接，结果的行数是左表行数乘以右表行数
交叉连接没有实际意思，通常用于测试所有可能的情况
查询学生和课程的全部可能性
合并查询
代码格式
将学生姓名、性别、出生日期和教师的相关信息合并显示
查询学生表中的全部男生和Cs010901班的全部学生并去除重复行
嵌套查询
不相关子查询
比较运算符
查询和“张丽”处于同一班级的学生信息
查询班主任林静所带的学生信息
查询年龄比“雷燕”大的教师信息
查询Dp010003课程中成绩高于平均分的成绩信息
查询课程Dp010001中比Dp020001最高分数都要高的成绩信息
查询同时选修了‘dp010001’和‘dp010003’的学生学号
[NOT]IN谓词
查询在dp01系的全部学生
查询教师dep01001所上课程的名称
查询09软件技术1班学生的成绩
查询没有选修Dp010001课程的学生姓名
ALL谓词
查询课程Dp010001中比Dp020001最高分数都要高的成绩信息
ANY谓词
查询学生St0109010001比St0109010002某个成绩要高的成绩信息
相关子查询
[NOT]EXISTS谓词
查询和“张丽”处于同一班级的学生信息
查询没有选修Dp010001课程的学生姓名
查询总结
查询的完整结构
查询语句的完整结构如下：
SELECT 子句1
FROM 子句2
[WHERE 表达式1]
[GROUP BY 子句3
[HAVING 表达式2] ]
[ORDER BY 子句4]
[LIMIT [M,]N] ;
第九章 索引和视图
索引概述
概念
根据表中一列或若干列按照某种存储方式建立的列值与记录行之间的对应关系表
存储方式：
B-TREE索引
MySQL默认的索引方式
HASH索引
优点：
一般在经常使用到的列上添加索引，可以增加查询效率，跑车和拖拉机的区别
缺点:
降低更新速度(需要根据数据的变动来动态维护),需要额外消耗空间
以下情况尽量不要创建索引
记录较少的基本表
查询中很少涉及的列
包含太多重复值的列
分类：
普通索引(index)
可以给任意列添加索引
唯一索引(unique index)
列里面的值只可以出现一次
全文索引(fulltext index)
一般应用在长文中对关键字进行索引
管理索引
以普通索引为例
查看
#创建表时 系统会自动对 主键、唯一建、外键 添加索引
查看student表中的全部索引
创建
根据student表的HomeAddr列上的前3个字符建立一个单列索引
在grade表的StudentID和CourseID两列上建立一个复合索引
修改
一般通过先删除再创建的方式进行修改
删除
删除索引
视图概述
概念
仅保存查询一张或多张表中某些列的定义，即一条select语句，具体内容来源于数据表
优点
简化查询和结构，保障安全，保障逻辑独立
管理视图
创建
普通视图
创建视图记录全部男生的姓名和班级号
通过视图查询信息
创建视图记录1990年出生的学生的姓名，性别和出生日期
通过视图查询信息
创建视图记录成绩表中未及格的成绩信息
通过视图修改表内容
创建一个视图记录不同课程成绩的最高分，最低分，平均分,并按平均分升序排列
创建一个视图记录教师姓名及所带课程名称（要包括未带课教师）
在上面视图的基础上创建一个视图记录未代课教师信息
检查视图
创建视图记录成绩表中未及格的成绩信息
检查视图和普通视图的区别
查看
查看视图里面的内容
select * from grade_course_view
查看结构
desc grade_course_view
查看定义
show create view grade_course_view
可通过“show tables”命令查看所有的表和视图
修改
修改视图grade_fail_view为成绩表中及格的成绩信息
删除
删除一个
drop view grade_course_view
删除多个
drop view grade_fail_view,student_info_view
第十章 数据库编程(上)
常量
字符型
select '1+2',select 'China',"Output X is:";
数值型
整数
select 1234,654,+2008,-123;
小数
select 897.1,-123.03,19E24,-83E2;
日期型
select '2009-01-03','2008/01/09';
布尔型
select true,false;#以字符串0或1显示
变量
系统变量
全局系统变量(global)
查看所有
show global variables;
查看某些
show global variables like 'version%';
会话系统变量(session)
查看所有
show session variables;
查看某些
show session variables like 'pseudo%';#其中session 可以省略
既是 全局系统变量 又是 会话系统变量
查看某些
show global variables like 'character%';
show session variables like 'character%';#其中session 可以省略
用户变量
用户会话变量
创建
法1：SET
创建用户变量name并赋值为“王林”
SET @name='王林';
创建用户变量user1并赋值为1，user2赋值为2，user3赋值为3
SET @user1=1, @user2=2, @user3=3;
创建用户变量user4，值为user3加1
SET @user4=@user3+1;
使用查询结果给变量赋值
SET @classid=(SELECT classid FROM student WHERE studentname='张丽');
法2：SELECT
SELECT max(grade) into @mg FROM grade;
SELECT max(grade) FROM grade into @mg;
查询
查询用户会话变量
SELECT @name, @user1,@user2,@user3,@user4;
使用
将用户变量用于查询
SELECT * FROM student WHERE classid=@classid;
局部变量
定义在函数中(和用户会话变量的使用方法一样，只是不加@符号)
系统函数
数学函数
计算半径为5的圆形面积
SELECT POWER(5,2)*PI()
计算5的平方根并保留2位小数
SELECT ROUND(SQRT(5),2)
产生50-100的随机整数
SELECT ROUND(RAND()*50+50)
字符串函数
查找学生表家庭住址的前三个字符
SELECT LEFT(HomeAddr,3)FROM student
将教师的姓名，性别和职称连接
SELECT CONCAT(Teachername,Sex,Profession)FROM teacher
查询学生姓名的长度
SELECT LENGTH(StudentName)FROM student
日期函数
查询学生年龄
SELECT 2018-year(birth) FROM student
查询学生入学日期是星期几
SELECT DAYOFWEEK(EntranceTime) FROM student
查询2年后的今天是星期几
select DAYOFWEEK(date_add(curdate(),interval 2 year))
其他
返回最后一个SELECT查询进行检索的总行数
SELECT FOUND_ROWS()
将一个值转换为指定的数据类型
SELECT CAST(NOW() AS CHAR)
自定义函数(一)
创建并调用
格式
根据自己的特殊需求创建的用来补充和扩展系统内置函数
创建一个名为add_fn()的函数，函数的功能是实现两个数相加
创建一个名为get_name_fn()的函数，函数的功能是根据学生学号查询学生姓名
查看
查看所有自定义函数
show function status;
查看名称以get开头的自定义函数
show function status like 'get%';
查看自定义函数的定义
show create function get_name_fn;
修改
建议先删除再创建
删除
drop function get_name_fn;
第十一章 数据库编程(中、下)
自定义函数（二）
条件空值
IF ELSE 语句块
代码格式
创建函数compar_fn比较输入的两个参数的大小关系
创建函数judge_grade_fn判断分数等级
CASE语句块
代码格式
创建函数judge_profession_fn判断教师职称等级
创建函数compar2_fn比较输入的两个参数的大小关系
循环控制
MySQL提供了三种循环语句，分别是WHILE、REPEAT以及LOOP
还提供了 ITERATE 语句以及 LEAVE 语句用于循环的内部控制
WHILE语句
代码格式
创建函数get_sum实现1到n的累加运算
创建函数get_sum2实现1到n的累加运算(LEAVE进行内部控制)
创建函数get_sum3实现1到n的奇数累加运算(ITERATE进行内部控制)
REPEAT
代码格式
创建函数get_sum4实现1到n的累加运算
LOOP
代码格式
创建函数get_sum5实现1到n的累加运算
存储过程
创建并调用
格式(注意参数类型)
创建名为count_dsn_proc的存储过程，统计不同系的学生人数
创建名为count_sc_proc的存储过程，统计某位同学的选课数
创建一个名为proc_get_max的存储过程，根据课程号查该课程所有学生的最高成绩
查看
查看所有存储过程
show procedure status;
查看名称以count开头的自定义函数
show procedure status like 'count%';
查看存储过程的定义
show create procedure count_sc_proc;
修改
建议先删除再创建
删除
drop procedure count_sc_proc;
触发器
定义
一个数据操作行为（增、删、改）的发生会激活另一个动作时，需要使用触发器。
对表进行操作时自动执行的存储过程，属于一种特殊的存储过程
只能被动触发，不能主动激活
格式
创建并触发
为grade表创建前触发器，在插入数据前检查分数是否在0-100之间，超过分别设置为0和100
为teacher表创建后触发器，在删除教师数据后同步删除schedule表中该教师的数据
查看
show triggers;
修改
show create trigger grade_insert_before_trigger;
删除
drop trigger grade_insert_before_trigger;
游标
使用游标的过程
分支主题
声明游标
declare 游标名 cursor
打开游标
open 游标名
提取数据
fetch 游标名 into 变量名1，变量名2....
关闭游标
close 游标名
一般和存储过程一起使用
创建存储过程用于统计教师所上课程的总学时
第十二章 数据库设计
设计步骤
需求分析
有多少数据，数据的来源在哪里，必须保存哪些数据？
数据是字符、数字或日期？
数据是否经常修改，如何修改和什么时候修改？
某个数据是否依赖于另一个数据或被其他数据引用？某个信息是否要唯一？
谁使用数据，如何使用？
概念设计
根据E-R模型画E-R图
确定实体
找出具有相同性质的某种对象的集合
确定实体间的联系
1∶1
1∶n
m∶n
确定实体和联系的属性
在唯一标识符属性下划横线
逻辑设计
将E-R图转换为关系模型
为E-R图中的每个实体建立一张表，属性作为表的字段
为每张表定义一个主键
联系的转换
1∶1
将一个表的主键作为另一个表的外键
1∶n
将一个表的主键作为另一个表的外键
m∶n
将联系单独建表，并将两个表得主键作为自己的联合主键
为字段选择合适的数据类型
定义约束条件
物理设计
确定数据的存放位置和存储结构
确定系统配置
数据库的实施和维护
设计规范化
设计的不规范表会有各种问题
数据冗余太大
插入异常
删除异常
更新异常
对表进行规范化
范式理论
1NF
如果一张表内同类字段不可再分且不重复出现，该表就满足第一范式的要求
保证了数据的原子性和唯一性
2NF
在满足第一范式的基础上，如果每个“非关键字”字段“完全”函数依赖于主键，那么该表满足第二范式的要求
3NF
在满足第二范式的基础上，并且不存在“非关键字”字段函数依赖于任何其他“非关键字”字段，那么该表满足第三范式的要求。
是一个可用的关系模式应满足的最低范式
自由主题
自由主题
0 条评论
下一页
