2021-8-3 数据库课堂笔记Day01

1 Oracle

1.1 Oracle 的体系结构

1 数据库

Oracle 数据库是数据的物理存储,其中有数据文件 ORA 或 bdf

Oracle 数据库是一个抽象总称,类似于面向对象中的类

2 实例

实例由一系列的后台进程和内存结构组成, 一个数据库可以有多个实例, 类似于类的对象

3 用户

建立在实例下, 不同的实例可以创建相同名字的用户

4 表空间

是 oracle 对物理数据库上相关数据的逻辑映射，一个数据库在逻辑上可以被划分为一个或者若干个表空间，每一个表空间包含了在逻辑上相关的一组结构。每一个数据库实例至少有一个表空间（system表空间）。一个表空间可有多个用户

5 数据文件(dbf、ora)

数据文件是数据库的物理存储单位。数据库的数据存储在一个或者多个数据文件中，由一个或多个数据文件组成表空间，一个数据文件只能属于一个表空间，一旦数据文件被加入到某一个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除期所属的表空间

表的数据，是由用户放入某一个表空间的，而这个表空间会随机的把这些数据放到一个或者多个数据文件中。

oracle 数由用户和表空间对数据进行管理和存放的。表不是由表空间查询的，而是由用户去查询。

2 Oracle12c创建示例数据库

在12c前，每个Oracle在安装时，如果是指定了安装的示例数据库，则会创建一个默认用户scott/tiger.

2.1 创建用户

先使用超级管理员登录PLSQL

方式一：使用命令（要求掌握）

在新建中打开命令窗口，输入代码

-- 创建scott用户
 create user scott identified by tiger;
 --为scoot赋予权限
  Grant connect,RESOURCE,UNLIMITED TABLESPACE TO scott;
  --设置用户使用的表空间
  ALTER USER scott default tablespace users;
  --设置用户的临时表空间
  Alter user scott Temporary tablespace  temp;

方式二：可视化操作

在user文件夹下新建

创建用户

方式三：网页可视化操作
在网站https://localhost:5500/em/login中登录

在”安全“界面中新建用户

2.2 表的相关操作

2.2.1 创建表的语句：

​ 前提：该用户必须具备crate table的权限

​ 创建表必须指定 表名 列名 数据类型 大小
列名间用逗号分割

Oracle 数据类型

数据类型	描述
varchar2（size）	可变长字符串
char（size）	定长字符串
number（p，n）	可变长数值类型
date	日期型
long	可变长字符数据，最大可达2G
clob	字符数据，最大可达4G
Bob	二进制数据，最大可达4G

2.2.2 修改表

• 追加新列（追加的新列一定位于最后）

• 为新列定义默认值

-- 新增一列并且定义默认值
alter table dept add(dnum number(2) default 10)；

• 修改现有列

-- 可修改数据类型 尺寸 默认值
alter table dept modify (dnum number(3) default 100)；

• 删除一个列

alter table dept drop COLUMN dnum；

• 重命名一个列

alter table dept rename COLUMN loc to location；

• 删除表

-- 删除表
drop table department；

• 清空表中的数据

-- 清空表
delete from dept；
truncate table dept；

delete 删除表中的数据 删除的数据在未提交之前 可回滚 不释放占有的存储空间和资源

truncate 清空数据 删除表中的所有数据，删除之后 不可回滚 释放占有的存储空间和资源

• 重命名表

-- 重命名表
rename dept to department；

2.3 创建表的示例数据库和初始化表

初始化表时可以直接进入表中编辑数据，也可用语句进行数据插入，数据插入时，输入的数据依次放入表创建的列中

语句格式：

INSERT INTO tablename VALUES (参数列表)；

3 SQL SELECT

3.1 SQL 概述

SQL 是一门结构化查询语言

SQL 是最重要的关系型数据库操作语言，是所有的关系型数据库管理系统的标准语言

关系型数据库：oracle mysql sqlserver

nosql：redis mongdb Hbase

SQL 语言是一种非过程化语言，只需要提出做什么，不需要指明怎么做

SQL 语言作用：

• 对数据库的数据进行增删查改操作（CRUD）
• 对数据库的对象进行创建、修改、删除操作
• 给用户和用户关联角色赋权或取消权限
• 事务控制

3.2 SQL 分类

DML：数据操纵语言

DDL：数据定义语言

DCL：数据控制语言

3.2.1 DML

用于查询与修改数据记录，包括：

• INSERT INTO 添加数据到数据库中
• UPDATE 更新数据库中的数据
• DELETE 删除数据库中的数据
• SELECT 查询数据库中的数据

3.2.2 DDL

用于定义数据库的结构，如创建、修改或删除数据库对象，包括：

• CREATE TABLE 创建数据库表
• ALTER TABLE ：更改表结构 添加 删除 修改列
• DROP TABLE 删除表
• CRATE INDEX :在表上建立索引
• DROP INDEX 删除索引
• CREATE VIEW 创建试图
• DROP VIEW 删除试图

3.2.3 DCL

用于控制数据库的访问，包括：

• GRANT 授予权限
• REVOKE 撤销、回收权限
• COMMIT 提交事务
• Rollback 回滚事务
• SAVEPOINT 设置保存点
• lock 对数据库的特定部分进行锁定

3.3 SQL 语句的书写规范

1.使用大小写规范提供词义的识别能力

• 在名称中仅使用字母、数字、下划线
• 列名、参数、变量等标量小写
• 模式对象名首字母大写：表、试图、存储过程、函数、触发器
• 保留关键字大写

2.使用空格提供良好的语言标记区分

• 等号两边要使用空格
• 逗号后面要使用空格

3.使用缩进提高语句的逻辑层次表达能力

4.使用垂直空白提供关键字和参数的区分能力

5.注释

• 单行注释 –
• 多行注释 /* */
• mysql中的注释 #

4 基本的 SELECT 语句

SELECT * | { [ditinct] columnname|expression [alias],..} FROM tablename；

SELECT 标识选择的列

FROM 标识从哪个表查询

4.1 选择全部列

SELECT * FROM tablename;--* 表示查询所有的列

4.2 选择特定列

SELECT 列名 FROM tablename;

注意：

• SQL语言大小写不敏感
• SQL可以写在一行或者多行
• 关键字不能被缩写也不能分行
• 各个子句一般要分行写
• 使用缩进来提供代码的可读性

4.3 运算符

4.3.1 数学运算符：+ - * /

直接在 SELECT 语句中对要操作的数据或列进行运算，结果会返回一个新的数据或列

4.4 操作符的优先级

• 先乘除后加减
• 同一优先级从左往右依次运算
• 括号优先运算

4.5 定义空值

• 空值为 null
• 空值是无效的，如果一个可选列没有指定值，或值是未知的，就会用 null 值保存
• 空值不是空格也不是0，无法和 0 比较

4.5.1 空值在数学运算中的使用

包含空值的数学表达式的值为空值

4.6 列的别名

定义方式：

• 跟在列名之后，两者之间保留一个空格
• 在别名和列名之间加入关键字 AS
• 别名可以使用双引号，以便在别名中包含空格或特殊的字符 并区分大小写

--查询所有的员工的姓名  职位 薪资 和奖金  以及年收入（薪资 * 12 ） + 奖金
SELECT ename AS name, job,sal salary,comm, sal *12  AS "year salary" FROM emp;

4.7 链接符 ||

--查询所有员工的姓名 职位 薪资 作为员工的个人基本信息 拼接在一起
SELECT ename || '-' ||job || '-' ||sal AS "Employee info" FROM emp;

4.8 字符串

• 字符串可以是SELECT语句中的一个字符 数字 日期
• 日期 和字符只能出现在单引号中
• 每当返回一行时，字符串被输出一次

4.9 重复行

--查询EMP表中所有雇员的部门编号   distinct
SELECT distinct deptno FROM emp;

5 SQL 和 SQL plus

SQL：一种语言 控制数据库中的数据和结构的定义

SQL plus：一种环境 提供了SQL语句执行的一个环境

6 过滤和排序

6.1 过滤

关键字：WHERE

格式：

-- 查询 30号部门的所有的员工信息
/*
SELECT * |column FROM tablename
[where conditions];
*/
SELECT * FROM emp WHERE deptno = 30;

6.2 字符和日期

字符和日期 要包含在单引号中

字符大小写敏感 日期格式敏感

默认的日期格式 ：DD-MON月-YY

SQL中在用户界面可以自定义日期、时间、数字格式

6.3 比较运算

符号	意义
=	等于
>	大于
<	小于
>=	大于等于
<=	小于等于
<>、!=	不等于

6.4 其他比较运算符

符号	意义
between……and	介于两个值之间
in（值1，值2，…）	等于其中的任意一个
like	模糊查询
is null/is not null	值为空/值不为空

like 的使用会用到通配符 %（代表零个或多个字符）、_（代表一个字符）

6.5 逻辑运算

符号	意义
AND	逻辑与
OR	逻辑或
NOT	取反

没有短路与和短路或

运算符优先级：

算术运算符 < 连接符 < 比较符 < is null、like、in< BETWEEN AND < NOT < AND < OR

可以通过括号改变优先级

7 排序

ORDER　BY 子句

ASC : 升序 从小到大

DESC : 降序 从大到小

ORDER BY 子句在 SELECT 语句结尾，且默认为升序排序

7.1 单列排序

格式：

SELECT *|列名  FROM tablename ORDER BY 列名

7.2 多列排序

格式：

SELECT *|列名  FROM tablename ORDER BY 列名1，列名2，……

列名可以使用别名

查询：过滤查询 排序

8 SQL 函数

函数就是 java 中的方法,函数有输入有输出,输入就是参数,输出就是结果,输入可以有多个,输出只有一个

SQL 函数分为 单行函数和多行函数

8.1 单行函数

只能一对一进行变化,每行返回一个结果 可以转换数据类型,在使用时可以嵌套. 参数可以是一列或一个值

单行函数可分:

• 字符函数
• 数值函数
• 日期函数
• 转换函数
• 通用函数

8.1.1 字符函数（作用于Java相似）

大小写控制函数：LOWER　UPPER　INITCAP（首字母大写）

字符控制函数：CONCAT　SUNSTR　LENGTH　TRIM

dual 表: Oracle 提供的一张虚拟表,不存在但可以使用,主要用于测试函数