【凤凰社】18Oracle入门

1 Oracle的服务

Oracle的监听服务:OralceOraDB12Home1TNSListener 需要通过程序链接数据库进行开发的时候,此服务必须打开,如果只是在本机使用,此服务可不启动

Oracle 的实例服务:OracleServiceORCL,默认启动

1.1数据库的链接访问

运行SQL Plus,输入用户名和密码。

如果需要解锁账户,打开cmd,输入sqlplus / as sysdba。在SQL提示符后面输入alter user system identified by root;alter user system account unlock;

再次输入用户名和密码,显示成功。

访问oracle的web端:https://localhost:5500/em/login

如果需要登录sys用户,用户名输入sys /as sysdba。

这样,就建立了虚拟机上的oracle连接。

2.Oracle数据库介绍

oracle文件中,后缀名为ora或dbf的表示数据文件,为ctl的是控制文件,为log的

是日志文件。

Oracle数据库指的是一个库。

一个oracle实例有一系列的后台进程和内存结构组成。一个Oracle数据库可以有多个实例。

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

表空间是oracle对物理数据库上相关数据的逻辑映射,一个数据库在逻辑上可以被划分为一个或者若干个表空间,每一个表空间包含了在逻辑上相关的一组结构。

每一个数据库实例至少有一个表空间(system表空间),每一个表空间有同一磁盘上的一个或多个文件组成,这些文件叫数据文件,一个数据文件只可以属于一个表空间。

数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中,实际是在一个或者多个数据文件中。一旦数据文件被加入到某一个表空间后,就不可以删除这个文件。

如果要删除某个数据文件,只可以删除其所属的表空间才可以删除表的数据,是由用户放入某一个表空间的,而这个表空间会随机的把这些数据放到一个或者多个数据文件中。

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

oracle数据库对实例:一对多

表空间和用户隶属于实例。

用户对表空间:多对一

表空间对数据文件:一对多

表对数据文件:多对多

3.Oracle12C创建实例数据库

3.1创建用户并分配权限

登录客户端,新建命令窗口。

-- 创建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;

 

create user 用户名 identified by 密码;

grant xxx to 用户名;xxx可以是连接权限,资源权限,无限制的表空间权限。如果没有分配无限制表空间,则必须制定一个值。

alter user 用户名 default tablespacce 默认表空间名。

alter user 用户名 temporary tablespacce 暂时表空间名。

3.2创建实例库和增加示例数据

前提:该用户必须具备创建表的权限。

oracle的数据类型

varchar2(size) 可变长字符串

char(size) 定长字符串

number(p,n) 可变长数值类型

date 日期型

long 可变长字符数据,最大可达2G

clob 字符数据,最大可达4G

blob 二进制数据,最大可达4G

3.3DDL语句

修改表:alter table

追加列:add(列名 列类型 限定条件);

修改列:modify(列名 列类型 限定条件);

删除列:drop column列名;

重命名列:rename column 原来列名 to 新列名;

删除表:

drop table 表名;

清空表:

delete from 表名;

truncate table 表名;

delete和truncate的区别:delete删除的数据在未提交之前,可以回滚,不释放占有的空间和资源;truncate删除的数据,删除之后不可回滚,释放占有的存储空间和资源。

重命名表:

rename 表名 to 新表名;

4 SQL Select

4.1 SQL概述

SQL是一门语言 :结构化查询语言

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

关系型数据库: oracle mysql sqlserver

nosql : redis mongdb Hbase

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

SQL语言的作用:

1 可以对数据库的数据进行增删改查操作(CRUD)

2 数据库的对象进行创建 修改 删除操作。

3 用户赋权限 取消权限 角色赋权限 取消权限 给用户关联角色

4 事务控制

4.2 SQL的分类

DML:数据操纵语言

DDL:数据定义语言

DCL:数据控制语言

4.2.1 DML

DML 用于查询与修改数据记录 包含:

  • insert 添加数据到数据库中

  • update 更新数据库中的数据

  • delete 删除数据库中的数据

  • select 查询数据库中的数据

4.2.2.DDL

用于定义数据看的结构 比如创建 修改或删除数据库对象。包含:

  • Create Table 创建数据库表

  • ALTER TABLE :更改表结构 添加 删除 修改列

  • DROP Table 删除表

  • CRATE INDEX :在表上建立索引

  • drop index 删除索引

  • CREATE VIEW 创建试图

  • drop view 删除试图

4.2.3 DCL

用来控制数据库的访问,包含:

  • GRANT 授予权限

  • REVOKE 撤销、回收权限

  • COMMIT 提交事务

  • Rollback 回滚事务

  • SAVEPOINT 设置保存点

  • lock:对数据库的特定部分进行锁定

4.3 SQL语句的书写规范

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

  • 在名称中仅使用字母 数字 下划线

  • 列明 参数 变量等标量小写

  • 模式对象名首字母大写:表 试图 存储过程 函数 触发器

  • 保留关键字大写

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

等号的两边要使用空格

在逗号的后面使用空格

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

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

5 注释:

单行注释:--

多行注释:/* */

mysql中的注释是:#

5 基本的Select 语句

select *| { [ditinct] columnname|expression [alias],..} from  tablename;

 

SELECT 标识选择的列

FROM 标识从那个表查询

5.1选择全部列

SELECT * FROM dept;--* 表示查询所有的列
SELECT deptno,dname,loc from dept;

 

5.2 选择特定的列

SELECT empno,ename,job,sal from emp;

 

注意:

  • SQL语言大小写不敏感

  • SQL可以写在一行或者多行

  • 关键字不可以被缩写也不可以分行

  • 各个子句一般要分行写

  • 使用缩进来提供代码的可读性

5.3 使用运算法

5.3.1 数学运算符:

数字和日期使用的算术运算符: + - * /

 -- 查询所有的员工的姓名  职位 薪资  为每个员工涨薪1000元
 SELECT ename, job, sal, sal + 1000 FROM emp;

 

5.4 操作符的优先级

+   -   *  /
  • 先乘除后加减

  • 同一优先级运算符从左往右依次运算

  • 括号内的运算先Run

 --查询所有的员工的姓名  职位 薪资  为每个员工涨薪1000元  并计算涨薪后的年薪
 SELECT ename,job ,sal, (sal + 1000) * 12 FROM emp;

 

5.5 定义空值

  • 空值指的是null

  • 空值是无效的 ,如果一个列没有指定值,或者是未知的 或者是不可预知的值

  • 空值不是空格也不是0

  -- 查询所有的员工的姓名  职位 薪资 和奖金
  SELECT ename, job, sal,comm FROM emp;

 

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

-- 查询所有的员工的姓名  职位 薪资 和奖金  以及年收入(薪资 * 12 ) + 奖金
SELECT ename,job,sal,comm, sal * 12 + comm FROM emp;

 

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

5.6 列的别名

别名紧跟列明之后,之间需要保留一个空格。也可以在别名和列明之间加入关键字 AS 别名可以使用双引号,以便在别名中包含空格或特殊的字符 并区分大小写

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

 

5.7. 连接符 ||

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

 

拼接后的值成为一列

5.8 字符串

  • 字符串可以是SELECT语句中的一个字符 数字 日期

  • 日期 和字符只可以出现在单引号中

  • 每当返回一行时,字符串被输出一次

5.9 重复行

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

 

6.SQL 和SQL plus

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

SQL *PLUS 一种环境,提供了sql语句Run的一个环境

6.2 字符和日期

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

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

默认的日期格式 :DD-MON月-RR

--查询20部门的所有雇员信息

select * from emp where deptno=20;
-- 查询姓名为SMITH的雇员的基本信息  此时的字符串的匹配 是严格匹配大写小的
select * from emp where ename='SMITH';
-- 查询入职日期为17-12月-80的雇员信息
SELECT * FROM emp WHERE hiredate='17-12月-80';

 

6.3 比较运算

与java中的关系运算符类型,注意相等判断是=。

6.4 其他比较运算符

between ... and 介于...之间

in(值1,值2,值3,...) 等于其中的任意一个

like 模糊查询 匹配查询

is null 值为空

6.5 逻辑运算

and

or

not

sql语法中没有短路与和短路或

6.6运算符的优先级:

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

7排序

ORDER BY子句

ASC:升序 从小到大

DESC: 降序 从大到小

order by 子句在select 语句的结尾

8 SQL函数

函数就是java中的方法 :函数有输入有输出 输入就是参数 输出就是结果 而且输入可以是多个 输出的结果仅有一个

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

8.1单行函数

单行函数:只可以对一行进行变换,每行返回一个结果。可以转换数据类型 在使用的时候 还可以嵌套。参数可以是一列或一个值。

单行函数: 字符 数值 日期 转换 通用

8.2字符函数

  • 控制大小写: LOWER UPPER INITCAP

    • 分别返回全小写、全大写、首字母大写的值

  • 字符的控制函数: CONCAT SUBSTR LENGTH TRIM LTRIM RTRIM LENGTHB

    • 拼接、子串、长度、修剪空格

    • SUBSTR(c1,n1[,n2]) 取c1字符串的子串,从n1位置Start取,取n2个,没有n2参数为取全部。取到n1+n2-1下标的位置。

    • LENGTH:全角、汉字按1个字符算 LENGTHB:全角、汉字按2个字符算

    • SUBSTR和SUBSTRB同理

    • NLS_LOWER、NLS_UPPER 、NLS_INITCAP,第二个参数是Nls_param

    • Length函数返回字符的个数,使用定义是给定的字符集来计算字符的个数;LENGTHB给出该字符串的byte;LENGTHC使用纯Unicode;LENGTH2使用UCS2;LENGTH4使用UCS4

  • INSTR、INSTRB、LPAD、RPAD REPLACE

    • INSTR(C1,C2[,I[,J]]):查找c2在c1中出现的位置,i代表从第几个下标Start查,j代表出现第几次 i和j默认为1

    • LPAD(c1,n[,c2]):在左边用c2填充c1,使其长度达到n。c2默认为空格

    • RPAD(c1,n[,c2])同理

    • REPLACE(c1,c2[,c3]):在c1中,将c2替换为c3,默认c3为空即删除c2

  • SOUNDX、TRANSLATE

    • SOUNDEX(c1)返回字符串的语音表示形式

    • TRANSLATE(c1,c2,c3):在c1中,将c2顺序换成对应的c3顺序。是部分的替换。

  • ASCII CHR

    • ASCII(x1):返回x1的ASCII码,是数值型。如果最左端是汉字,只取汉字最左半边字符的ASCII 码

    • CHR(n1):返回n1对应的字符,是字符型。n1属于[0,255]

8.3数值函数

  • ABS SIGN MOD

    • abs(x):返回绝对值

    • sign(x):符号函数,返回1,-1,0。

    • mod(x,y):返回x mod y

  • ROUND TRUNC CEIL FLOOR

    • round(x,[y]):返回四舍五入的值。如果y为正数,四舍五入为y位小数。如果y为负数,整数部分低y位四舍五入。

    • trunc(x,[y]):返回x按精度y截取后的值,But不四舍五入

    • ceil(x):返回大于等于x的最小整数值

    • floor(x):返回小于等于x的最大整数值

  • POWER EXP LOG LN SQRT

    • power(x,y):返回x的y次幂

    • exp(y):返回e的y次幂

    • log(x,y):返回以x为底的y的对数

    • ln(y):返回以e为底的y的对数

    • sqrt(x):返回x的平方根

  • 三角函数

    • 返回一个数字的正弦sin、双曲正弦sigh、余弦cos、反余弦cosh、正切tan、双曲正切tanh、反正弦asin、反余弦acos、反正切值atan

8.4日期函数

  • add_months last_day months_between next_day

    • add_months(d1,n1):返回日期d1加上n1个月后的日期

    • last_day(d1):返回日期d1所在月份最后一天的日期

    • months_between(d1,d2):返回d1日期和d2日期之间的月数

    • next_day(d1[,c1]):返回d1日期下周,星期是c1字符串的日期。

  • new_time round trunc extract localtimestamp

    • new_time(dt1,c1,c2):给出时间c1时区的dt1时间,对应c2时区的日期和时间

    • round():给出日期d1按期间(参数c1)四舍五入后的期间的第一天日期

    • trunc(d1[,c1]):返回日期d1所在期间(参数c1)的第一天日期,c1可以是day,month,q,year等等。

    • localtimestamp:返回会话中的日期和时间

    • extract(c1 from d1):日期/时间d1中,参数c1的值。比如

      select 
      extract(hour from timestamp '2001-2-16 2:38:40 ' ) 小时 from dual;

       

  • current_time current_date sysdate

    • sysdate 返回当前日期

    • current_time :以timestamp with time zone数据类型返回当前会话时区中的当前日期

    • current_date:返回当前日期

  • dbtimezone sessiontimezone interval

    • dbtimezone:返回时区

    • sessiontimezone:返回会话时区

    • INTERVAL c1 set1:变动时期时间数值。c1 ”量“,set1起作用的时间单位

8.5转换函数

  • chatorowid rowidtochar convert hextoraw

    • chatorowid(c1): 转换varchar2类型为rowid类型。每一条记录都有一个rowid,rowid在整个数据库中是唯一的,rowid确定了每条记录是在Oracle中的哪一个数据文件、块、行上。

    • rowidtochar(rowid):转换rowid值为varchar2类型

    • CONVERT(c1,set1,set2):将字符串c1从字符集set2转换到另一个字符集set1。

    • hextoraw(c1):讲一个十六进制构成的字符串转换为二进制

  • to_char to_date to_number to_multi_byte to_single_byte

    • to_char(x[[,c2],C3]):将日期或数据转换为char类型。x是日期或数据,c2是格式参数,c3是nls设置参数。

    • to_date(X[,c2[,c3]]):将字符串x根据c2的格式转换为日期类型

    • to_number(X[[,c2],c3]):将字符串x根据c2的格式转换为数字类型

    • to_multi_byte(c1):单字节转换为多字节(半角转换为全角)

    • to_single_byte(c1):多字节转换为单字节(全角转换为半角)

  • nls_charset_id nls_charset_name

    • nls_charset_id(c1):返回字符集名称对应的id

    • nls_charset_name(id):返回字符集id对应的名称

8.5.1 to_char详细使用

to_char函数对日期的转换:to_char(date,'format_model')

必须包含单引号 其中的format_model是大小写敏感

可以包含任意的日期格式

数值型转换为字符型 to_char:to_char(123.45,'$999,999.00')->¥123.45

9 表示数字 0 表示0 $ 表示美元 L 表示本地货币 .表示小数点

8.6聚组函数(多行函数)

  • avg sum stddev variance count max min

    • avg([distinct|all]x):返回平均值。all表示对所有的值求平均值,distinct只对不同的值求平均值,默认为all。如果有参数distinct或all,需有空格与x(列)隔开。

    • sum([distinct|all]x):返回总和

    • stddev([distinct|all]x):返回标准误差(标准差)

    • variance([distinct|all]x):返回方差

    • count(*|[distinct|all]x):返回个数

    • max([distinct|all]x):返回最大值

    • min([distinct|all]x):返回最小值

  • 注意事项

    • group by语句中:如果查询结果中有列,那么列要么是以聚组函数的参数出现,要么是以group by后面的列名存在。

    • where后面不可以出现group by和聚组函数 Beacuse违反了先分组后过滤的原则

    • 分组过滤:having having的过滤条件出现在group by后

  • 在组函数中使用nvl函数 count(nvl(comm,0)) ,会将被忽略的null值转化为数值参与运算

  • 去重统计count(distinct deptno)

     

8.7分析函数

  1. 值域窗(RANGE WINDOW) range n preceding 仅对数值或日期类型有效,选定窗为排序后当前行之前,某列(即排序列)值大于/小于(当前行该列值 –/+ N)的所有行,因而与ORDER BY子句有关系。

  2. 行窗(ROW WINDOW) rows n preceding 选定窗为当前行及之前N行。 还可以加上BETWEEN AND 形式,例如row between m preceding and n following

  3. 使用函数分析窗中的内容

  4. 分组求和:group by

    1. grouping sets(x,xx,xxx):等效于分别按照x,xx,xxx分组得出结果后,再并起来的结果

    2. rollup(x,xx,xxx):等效于分别按照(x,xx,xxx),(x,xx),(x),null分组得出结果后,再并起来的结果

    3. cube(x,xx,xxx):等效于grouping sets(),参数是它的所有子集

    4. grouping :用于测试该行是不是前三种方法添加出来的,是则值为1,不是值为0。

8.7.1 sum(...) over(....)

sum(exp)over(分区/排序):连续求和,仅有最后一行的值才是sum()。第二行就是第一行+第二行。

8.7.2 rank() dense_rank()

rank()over(条件/排序/分区)

dense_rank()over(条件/排序/分区)

dense_rank在并列关系是,相关等级不会跳过。rank则跳过。 rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内) dense_rank()l是连续排序,有两个第二名时仍然跟着第三名。

8.7.3 row_number()

row_number()over(排序):先Run排序,再返回排序后的行号

select name,seqno,description 
from(select name,seqno,description,row_number() over (partition by name order by seqno) id
from table_name) where id<=3;

 

按照名字分区,再按照seqno升序排序得到排名id,只返回id前2的结果,最终返回过滤id后的结果

8.7.4 lag() lead()

lag(expr,offset,default) lead(expr,offset,default)

lag(列):取上一个该列值作为值

lead(列):取下一个该列值作为值

OFFSET是缺省为1 的正数,表示相对行数。希望检索的当前行分区的偏移量

default是超出范围时自动返回的值,默认是null

8.8其它函数

  • DUMP greatest least nvl nvl2

    • dump(w[,x[,y[,z]]]):返回数据类型、字节长度和在内部的存储位置.

    • greatest(exp1,exp2,exp3,……,expn):返回表达式中最大的一个。如果表达式类型不相同, 会隐含转换为第一个表达式的类型

    • least(exp1,exp2,exp3,……,expn):返回表达式中最小的一个。如果表达式类型不相同, 会隐含转换为第一个表达式的类型

    • nvl(exp1,exp2):返回不为null的值

    • nvl2(expr1, expr2, expr3) :expr1不为NULL,返回expr2;expr2为NULL,返回expr3。expr2和expr3类型不同的话,expr3会转换为expr2的类型

  • user uid userenv

    • user:返回当前会话的数据库用户名

    • uid:返回当前会话的数据库用户id

    • userenv(param):返回当前会话上下文属性的值

  • nullif coalesce

    • nullif(expr1, expr2):expr1和expr2相等返回null,不相等返回expr1。

    • coalesce(c1, c2, ...,cn):返回列表中第一个非空的表达式,如果所有表达式都为空值则返回1个空值

  • rownum bfilename vsize

    • rownum:返回当前行号

    • bfilename:函数返回一个空的BFILE位置值指示符,函数用于初始化BFILE变量或者是BFILE列。

    • vsize:返回x的大小(字节)数

  • decode case...when...then...end

    • decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

      decode (条件,

      值1,翻译值1,

      值2,翻译值2,...

      值n,翻译值n,

      缺省值)

    • case [<表达式>] when <表达式条件值1> then <满足条件时返回值1> [when <表达式条件值2> then <满足条件时返回值2> …… [else <不满足上述条件时返回值>]] end

    • case有两种形式,一种是提前列名,case 列名,when后面仅有值。另一种是case后面接when when后面是表达式 列名=值

  • sys_guid sys_context dbms_random

    • sys_guid():生产32位,十六进制的随机数字(A-F大写)

    • sys_context(c1,c2):返回系统c1对应的c2的值

    • dbms_random包

      • dbms_random.value:无参方法,这种用法没有参数,会返回一个具有38位精度的数值,范围从0.0到1.0,但不包含1.0。第二种value带有两个参数,第一个指下限,第二个指上限,将会生成下限到上限之间的数字,但不包含上限

      • dbms_random.string(c1,len):第一个参数有'u'-大写,'l'-小写,'a'-混合大小写,‘x’-大写字母和数字字符,'p'-返回的都是可读字符

      • dbms_random.random:返回一个随机大小的,binary_integer值数据

      • dbms_random.normal:返回符合正态分布的数。这个函数返回的数值中有68%是介于-1与+1之间,95%介于-2与+2之间,99%介于-3与+3之间。

      • dbms_random.send:用于生成一个随机数种子,设置种子的目的是可以重复生成随机数,用于调试。否则每次不同,难以调度。

  • 获取主机名和IP地址 utl_inaddr包:

    • utl_inaddr.get_host_address:获取IP

    • utl_inaddr.get_host_name:获取主机名

    • utl_inaddr包需要给用户分配ACL权限并创建ACL文件。

9多表查询

多表查询要克服笛卡尔积的问题,需要使用where设置关联条件。

等值连接:where后面的逻辑运算符是=。

非等值连接:where后面使用其他逻辑运算符。

内连接:结果集中只包含匹配且关联列不为null的行。

外连接:结果集中也可以匹配关联列为null的行。

外链接的实现:(1)关键字left outer join /right outer join/full outer join

(2)在where子句的列后面加上'(+)',表示这个表可以有null,另一个表全部显示

全部显示的表是主表,另一个表是从表。

(+)在左边是右外连接。

(3)join 关键字后面用on不用where。(+)前面只可以用where

叉集:cross join 得到笛卡尔积

自然连接:natural join 以表中具有相同名字的列作为条件,创建等值连接。

使用using子句创建连接:using(列名),using与join连用,不需要给表取别名

使用on子句创建连接:on与join连用,可以进行多表连接

10子查询

子查询:where后面的子句是另一个查询的结果,称为子查询

子查询优先于父查询Run

多行子查询、嵌套子查询

子查询修饰符:

  • in等于列表中的任意一个

  • any和子查询返回的某一个值比较

  • all和子查询返回的所有值比较


0 评论

回复