发布于 由kailjava

book3 – sql

title: book3 – sql
toc: true
comments: true
categories:
top:
abbrlink: 90843d6a
date: 2021-02-09 09:52:07
tags: Java

这里记录的是Oracle数据库和运维的学习 !

(一)关系型数据库(Oracle)

1. 数据库概述

2. 数据库系统发展史

2.1 数据库设计的三大范式

  1. 第一范式: 确保每一列的原子性, 不可拆分; 当两列属性相近或相似时, 尽量合并为一列
  2. 第二范式: 一条数据做一件事, 每一行数据只能与其中一列相关
  3. 第三范式: 表中的每一列都只能依赖主键, 数据不能存在传递关系, 通过主键可以唯一识别其他数据, 没有相互关联性

3. 主流数据库简介

3.1 常用数据库默认端口号

  • SQL Server – 1433
  • MyServer – 3306
  • MySQL – 3306
  • Oracle – 1521
  • DB2 – 5000
  • PostgreSQL – 5432

3.2 数据库, 数据库管理系统和数据库系统的差异

  1. 数据库(DB): 数据的仓库
  2. 数据库管理系统(DBMS): 管理数据库的软件
  3. 数据库系统(DBS): 计算机硬件 + 软件 + DB + DBMS + DBA(数据库管理员))

4. 结构化查询语言

5. Oracle安装

6. Oracle初使用

6.1 要使用PL/SQL Developer操作管理Oracle数据库

  • 端口权限
  • 端口配置

(二)查询与排序

1. 数据库表简介

1.1 创建数据表

  • 语法
    CRATE TABLE 表名
    (
        字段名1 字段类型,
        字段名2 字段类型,
        字段名3 字段类型，
        …………
        约束定义 1,
        约束定义 2,
        …………
    )
  • 示例(Oracle下)
    CRATE TABLE T_Person
    (
    FName VARCHAR2(20),
    FAGE NUMBER (10)
)

2. 数据查询

2.1 Oracle常用数据类型

类型 含义
CHAR(size) 存储固定长度的字符串。参数size指定了长度，如果存储的字符串长度小于size，用空格填充。默认长度为1，最长不超过2000字节
VARCHAR2(size) 存储可变长度的字符串。参数size指定了该字符串的最大长度。默认长度为1，最长不超过4000字节
NUMBER(p, s) 存储可变长的数字。既可以存储浮点数，也可以存储整数，p表示数字的最大位数（如果是小数包括整数部分、小数部分和小数点，p默认是38位），s表示小数位数
DATE 存储定长的日期或时间。存储世纪、年、月、日、时、分、秒，存储范围从公元前4712年1月1日到公元后9999年12月31日
TIMESTAMP 和DATE类型大致相同，不过TIMESTAMP精确到了秒后6位小数
CLOB 存储单字节字符大数据。和VARCHAR2数据类型相似，可存储的数据最大可以达到4GB，可以用来存储非结构化的XML文档
BLOB 存储无结构的二进制大数据。可存储的数据最大可以达到4GB，可以用来存储图像、视频、音频等信息
ROWID 表中行的存储地址，该地址可以唯一标识数据库中的一行，可以使用ROWID伪列快速定位表中的一行
ROWNUM 查询返回的结果集中行的序号，可以使用它来限制查询返回的行数

2.2 SELECT语句基本语法

    SELECT *|字段名|表达式 FROM 表名 [WHERE 条件 [ORDER BY 字段名]]

在最简单的形式中，SELECT语句必须包含一个SELECT子句，指定需要显示的字段；还需要一个FORM子句，指定从哪个表获取数据

语法解释：

  • *：选择表中的所有字段
  • 字段名：选择表中的字段名称，可以选择多个字段，各个字段间用逗号隔开
  • 表达式：由字段、函数等组成
  • 表名：指定包含字段的表
  • WHERE条件：查询的条件，可以通过该条件进行行选择
  • ORDER BY字段名：要求在查询的结果中进行排序，默认是升序ASC，若希望是降序则使用关键字DESC。

假设要通过SQL语句查询DEPARTMENTS表中所有列、所有行的数据，可以使用下面的语句：

    SELECT * FROM DEPARTMENTS

注：SQL语句不区分大小写，并以 “;” 结尾，同时，为了方便阅读，SQL关键字采用大写，非SQL关键字采用小写，SQL语句可以写成一行或多行，为了易于阅读和编辑，子句同常放在不同的行，且使用缩进

2.3 算术表达式

数据库中存储的数据可能并不是最终要显示的数据，会存在需要修改数据显示内容已达到显示所需数据的目的，这样就可能会用到算术表达式。一个算术表达式可以包含字段名、固定的数字值和算术运算符。

Oracle中算术运算符只有”+”, “-“, “*”, “/”四个，其中，”/”运算的结果是浮点数。求余运算只能借助函数MOD(x, y)来完成，这个函数的作用就是返回x除以y的余数。

更特别说明一点，在Oracle中，可以对DATE和TIMESTAMP类型进行加、减操作，具体加、减的含义和用法，在后面会说到。

2.4 practice01

  • 操作employees表, 将last_name和job_id字段连接, 中间用逗号隔开, 使用Name and Job作为字段标题
select e.last_name || ',' || e.job_id Name and Job
from employees e

2.5 practice02

  • 操作employees表, 选定查询department_id和job_id字段, 并去掉重复的行

2.6 SQL语句中有哪几个通配符, 含义分别是

  • “%”: 替代0个或多个字符
  • “_”: 替代一个字符
  • “[charlist]”: 字符列中的任何单一字符
  • [^charlist]或[!charlist]: 不在字符列中的任何单一字符

3. 限定返回行

4. 排序

4.1 顺序排序: order by

4.2 逆序排序: desc

(三)单行函数与多表查询

1. SQL函数

1.1 fm, fx, th, sp四个元素的作用

  • fx: 删除填补的空或前导0
  • th和sp: 指定后缀来影响数字的显示, th加在数字后面表示序数, sp加在数字后面让数字以英文形式显示, 两个元素也可以结合起来使用
  • fx: 指示对需要转换的字符类型和TO_DATE函数的格式或模板必须精确匹配, 包括标点符号和空格

2. 字符数字,日期函数

2.1 修改Oracle中日期显示结果为: “2013/6/23,22-7-16”

“YYYY/MM/DD, HH24-MIMI-SS”

3. 转换函数

4. 通用函数

5. 多表查询

5.1 什么是左连接, 右连接和全连接

  • 左连接: 不仅查询出符合连接条件的数据, 还查询出left join 左边不符合连接条件的数据
  • 右连接: 不仅查询出符合连接条件的数据, 还查询出right join 右边不符合连接条件的数据
  • 全连接: 查询出full join 两边不符合连接条件的数据

6. 外连接

6.1 什么是内连接和外连接

  • 内连接: 内查询出符合连接条件的数据
  • 外连接: 不仅查询出符合条件的数据, 还查询出不符合条件的数据

(四)子查询与组函数

1. 子查询

1.1 使用GROUP BY进行分组后, select子句或ORDER BY 子句在选择字段时需要注意

  • select或order by后面只能是组函数或group by后面出现过的字段, 且不能使用别名

1.2 使用group by 进行分组的sql语句中, where子句和having子句的作用的区别

  • where: 用在group by之前; 作用于单个记录
  • having: 用在group by之后; 作用于单个组(1行或多行)

2. 组函数

2.1 COUNT组函数的参数”*”和表达式之间的区别

参数是”*”, 则计算所有行, 包括空值; 如果参数是表达式, 如列名, 则忽略空值且考虑重复值

2.2 practice01

有如下表:

  • S(SNO, SNAME)学生表, SNO为学号, SNAME为学生姓名;
  • C(CNO, CNAME)课程表, CNO为课程号, CNAME为课程名
  • SC(SNO, CNO, SCGRADE)选课成绩表, SCGRATE为成绩

使用SQL语句查询下面条件给出的信息:

Q1: 有两门以上(含两门)不及格课程的学生姓名及其平均成绩

Q2: 既学过”语文”又学过”英语”的所有学生姓名

(五)操作数据和管理表

1. 数据操纵语言DML

2. 事务处理

2.1 什么是事务, 其最大的特点是什么?并说明一个事务在什么情况下会结束

  • 事务: 一个完整的DML操作
  • 最大特点: 原子性, 一致性, 隔离性, 永久性
  • 结束, DM了操作结束时就结束了

3. 表

3.1 用子查询创建一个表时, 定义新表的子段属性, 只能定义子段名和默认值

3.2 使用SQL命令查询一个表的表结构

4. 内置约束

4.1 数据库中有哪些常用内置约束

  • 主键约束primary key
  • 唯一键约束unique
  • 非空约束not null
  • 检查约束check (age in( , , ))
  • 外键约束foreign key(tno) references tea(tno)

(六)视图,序列和索引

1. 视图

1.1 什么是视图, 使用视图可以带来什么好处

  • 定义: 视图是一张逻辑表(基于表或其他视图), 其提供了另外一个视角查看或改变表中的数据
  • 好处: 隐藏隐私字段, 使数据更加安全; 简化查询

2. 视图的DML操作

2.1 对什么样的视图可以执行DML操作

对于简单视图可以, 复杂视图不可以; 复杂视图指的是子查询中涉及表的数目超过一个, 或包含函数, 或包含数据分组的视图

3. 序列

3.1 什么是序列, 主要作用

  • 序列是在数据中一个自动增加的列
  • 主要作用
      1. 自动提供唯一的数值
      2. 共享对象
      3. 主要用于提供主键值
      4. 代替应用代码

4. 索引

4.1 什么是索引? 索引的好处, 在什么情况下创建索引?

  • 索引: 能在表中快速查找一个行的数据库结构
  • 好处: 提高数据的检索效率
  • 创建索引的情况:
      1. 表的数据比较大, 则表适合创建索引, 反之小表不适合
      2. 频繁做查询, 而较少做增删则适合创建索引; 反之增删频繁查询较少的不适合
      3. where条件中出现的字段多表连接字段

4.2 索引会被自动创建的情况

  • order会为unique或primary key声明创建一个索引

(七)访问控制和子程序

1. 访问控制

1.1 作为程序开发人员, 常被授予的系统权限包括

  • create session
  • create table
  • create view
  • create sequence
  • create procedure

1.2 授予和撤销权限的关键字分别是:

  • grant
  • revoke

2. 子程序

2.1 什么是存储过程和触发器, 使用存储过程的好处

  • 存储过程: 有名称的无返回值的程序块
  • 触发器: 类似于过程, 不同的是, 触发器无需调用, 当满足一定条件时会自动调用
  • 好处:
      1. 存储过程允许标准组件式编程;
      2. 能够实现较快的存储速度
      3. 能减少网络流量
      4. 可被作为一种安全机制来充分利用

2.2 一个触发器包括哪几个部分?语句触发器和行触发器有什么区别?

  1. 触发器组成部分: 触发时间(触发器体执行的时机); 触发事件(哪类具体的数据操纵); 触发器类型(触发器体被执行次数); 触发器体(触发器执行的具体操作)
  2. 语句触发器和行触发器的区别:
    • 行触发器有for each row 子句语句, 语句触发器没有
    • 行触发器可以有when作为触发限制, 可以使用new/old, 语句触发器不能有when作为触发限制
    • 行触发器所对应DML语句所影响到的表中的每一行, 触发器都要执行一遍; 语句触发器对应的DML语句所影响到的表中的所有行, 触发器只执行一遍

(八)备份,恢复与SQL优化

1. 数据库备份与恢复概述

1.1 备份有哪几种不同的方式

  • 逻辑备份
  • 物理备份(冷备份, 热备份)

1.2 进行冷备份的具体操作步骤和需要注意的事项

冷备份具体步骤:

  • 使用SYS登录数据库
SQLPLUS sys/change_on_install AS SYSDBA
  • 查询所有控制文件的位置
select * from v$controlfile;
  • 查询所有日志文件的位置
select * from v$logfile;
  • 查询所有数据文件的位置 
    select * from v$datafile;
  • 查询pfile文件的位置
show parameter pfile
  • 记录以上所有文件的位置, 之后关闭数据库实例 
    shutdown immediate
  • 根据记录的位置, 将以上所有文件复制到备份目录中
  • 重新启动数据库实例
startup

冷备份注意事项

  • 冷备份完成后, 若要进行恢复操作, 只需先关闭数据库实例, 然后将之前备份的文件再复制回原目录中, 重启数据库实例

1.3 使用EXP工具进行导出操作时, 有哪几种不同的方式

  • 表方式
  • 用户方式
  • 全库方式

2. SQL优化

2.1 SQL优化目的

    1. 查询性能低
    1. 执行时间过长
    1. 等待时间过长
    1. SQL写得太差(尤其体现在多表查询上)
    1. 索引失效
    1. 服务器参数(缓存,线程数)设置不合理
    1. 项目需求不合理

2.2 UNION ALL与UNION在执行过程中有什么区别

  • UNION ALL对两个结果集进行并集操作, 包括重复行, 不进行排序
  • UNION对两个结果集进行并集操作, 不包括重复行, 同时进行默认规则的排序

发表评论

电子邮件地址不会被公开。 必填项已用*标注