mysql聚合函数伪列_Oracle学习

自己一些关于Oracle练习整理

一、Oracle体系结构

1、概念关系

数据库与实例：Oracle只有一个数据库，但可以有多个实例

Oracle数据库与实例一对多，一般情况下只有一个实例，我们平常说的Oracle数据库名，其实是Oracle的实例名

实例与用户一对多

实例与表空间一对多

表空间与用户一对多

2、Oracle结构

物理结构：数据文件(xxx.dbf、xxx.ora)

逻辑结构：数据库 表空间 段 区 Oracle数据块

二、Oracle实用命令及语句

在物理机的dos窗口输入：sqlplus system/password@192.168.25.128:1521/orcl

查询所有表空间：select tablespace_name from dba_tablespaces;

查询所有角色：select * from dba_sys_privs;

查看所有用户：select * from all_users;

查看当前用户：show user

通过scott登录可以做测试：sqlplus scott/tiger@192.168.25.128:1521/orcl

账号锁定报错：ERROR:ORA-28000: the account is locked

解决：重新用system登录，执行alter user scott account unlock;给scott用户解锁

密码过期报错：ERROR:ORA-28001: the password has expired

解决：重新输入密码tiger即可

查看当前用户下的所有表：select table_name from user_tables;

查看表结构：desc emp

清屏：host cls

设置行宽：set linesize 150

显示行宽：show linesize

设置列宽：col ename for a8(8个字符的宽度)

设置每页多少条数据：set pagesize 20

显示每页多少条数据：show pagesize

将命令行的语句写入到指定的目下的指定的文件中：

指定写到哪个目录的哪个文件上：spool d:\1.text

写入完毕：spool off

编辑之前输入过的命令：

输入ed，然后回车

该命令主要用在当前一个sql语句写错时，又不想重新写，这个时候可以用ed命令调出记事本

对先前的命令进行编辑(如果该命令用在Linux系统中，调出来的是vi编辑器)

执行之前缓存过的命令：/

三、创建表空间+创建用户+系统管理员授权

1、创建表空间tablespace

create tablespace waterbos

datafile 'c:\waterbos.dbf'

size 100m

autoextend on

next 10m;

2、创建用户user

create user xuming

identified by itcast

default tablespace waterbos;

3、给创建的新用户授权grant

ORA-01045: user lacks CREATE SESSION privilege; logon denied

解决办法：系统管理员给新用户授权：

grant create session,resource to 用户名; 等同于grant connect,resource to 用户名;

grant dba to 用户名;

张三 超级管理员 对A资源的增加

李四 普通管理员 对A资源的删除

王五 项目经理 对A资源的修改

赵六 产品经理 对A资源的查询

钱七 技术顾问 对B资源的查询

四、创建表+维护表结构

DDL：Data Definition Language 数据定义语言 如：create、alter、drop、truncate

DML：Data Manipulation Language 数据操纵语言 如：select、update、delete、insert

DCL:Data Control Language 数据控制语言 如：grant、revoke

1、oracle常用数据类型

char,varchar2,long,number,date,clob,blob

2、创建表+约束

1)基本创建表语句

create table myemp3(

id number(10),

name varchar2(10) constraint myemp3_name_nn not null,--非空约束

gender varchar2(4) default '男',--默认值

deptno NUMBER(2),

email varchar2(20),

constraint myemp3_id_pk primary key(id), --主键约束

constraint myemp3_gender_ck check(gender in('男','女')), --检查约束

constraint myemp3_email_uk unique(email), --唯一约束

constraint myemp3_deptno_fk foreign key(deptno) references dept(deptno) on delete set null --外键约束

-- 没有这种写法constraint myemp3_name_nn not null

);

2)使用子查询创建表

create table emp2 as select * from emp where 1=2;

由于1=2为假，该语句只拷贝了结构，没有拷贝数据

3、修改表

1)修改表中列

oracle: alter table myemp3 modify ename vachar2(20); 不能带column

mysql : alter table myemp3 modify (column) ename varchar(20);

2)增加表中列

oracle: alter table myemp3 add gender varchar2(4);

mysql : alter table myemp3 add (column) gender varchar(4);

3)删除表中列

oracle: alter table myemp3 drop column gender; 必须带column

mysql : alter table myemp3 drop (column) gender;

4)重新命名表中列名

oracle: alter table myemp3 rename column ename to myname; 必须带column

mysql : alter table myemp3 change (column) ename myname varchar(20);

5)重新命名表名

oracle: rename myemp3 to myemp4;

mysql : rename table mysqltname3 to mysqltname4; 必须带table

4、删除表

1)彻底删除表

drop table myemp3 purge; 不加purge是暂时放到回收站中了

2)drop之后的数据放到了recyclebin回收站中

查看回收站：show recyclebin;

彻底清除回收站：purge recyclebin;

彻底删除表：drop table myemp3 purge;

查看回收站表数据： select * from "BIN$cd/KYgV5RY6/RxGTjn2Skg==$0";

3)闪回

flashback table myemp3 to before drop;

五、对表数据操作(insert,update,delete)

1)向表中插入数据(insert into values)

insert into t_owners values(2,'张三',1,'3-3','7895',sysdate,1);

insert into t_owners2 select * from t_owners;

insert into 表名(id,name) values(xx,yy),(xx1,yy1) (mysql中可以这样写,oracle不能这样写)

向多张表中插入数据：insert all into 表名(id,name) values(xx1,yy1) into 表名(id,name) values(xx2,yy2);

2)修改表中数据(update set)

update 表名 set dateddd=dateadd-3,col1=col1+1;

3)删除表中数据

使用delete删除数据 delete from myemp where empno=7369;

使用truncate 删除整张表数据 truncate table myemp;功能上与delete from myemp; 相同

delete和truncate 删除数据的区别：

(1)delete可以回滚，truncate不可以

(2)truncate比delete效率要高

实际企业开发中删除数据库中数据注意点：

(1)先把要删除的数据备份

(2)确认用于删除的sql语句无误

(3)尽量选择能够回滚数据的方式delete

(4)drop > truncate > delete

六、导入和导出

1)整库导入导出

exp system/itcast full=y

exp system/itcast full=y file=abc.dmp

imp system/itcast full=y

imp system/itcast full=y file=abc.dmp

2)按用户导入与导出

exp system/itcast owner=xuming file=abc.dmp

imp system/itcast file=abc.dmp fromuser=xuming

3)按表导入导出

exp xuming/itcast file=a.dmp tables=t_owners;

imp xuming/itcast file=a.dmp tables=t_owners;

4)mysql导入导出

第一种导入数据：

mysql -uroot -proot

mysql>create database crm;

mysql>use crm;

mysql>source d:/crm.sql

第二种导入数据：

先创建好数据库crm，执行

mysql -uroot -proot crm < d:\crm.sql

mysql导出数据

mysqldump -uroot -proot crm > d:\crm1.sql

七、总结oracle与mysql在项目中使用区别

1、导入jar包不同

oracle：ojdbc14.jar 版本可能会变

mysql：mysql-connector-java-5.1.7-bin.jar 版本可能会变

注意：不同项目，jar包版本会有区别

2、jdbc获取连接写法

oracle：Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.25.128:1521:orcl", "xuming","itcast");

mysql：Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8", "root", "root");

3、配置文件

# oracle jdbc properties

jdbc.url = jdbc:oracle:thin:@localhost:1521:xe

jdbc.driver= oracle.jdbc.driver.OracleDriver

jdbc.user = bos

jdbc.password = bos

#mysql jdbc properties

jdbc.driver=com.mysql.jdbc.Driver

jdbc.url=jdbc:mysql://localhost:3306/bos?characterEncoding=utf-8

jdbc.user=root

jdbc.password=root

======================================================================================================================================================================================================

一、单表查询

完整语句：select from where group by having order by

1、简单条件查询

=,!=,>,>=,

like

and 和or 逻辑运算，and的优先级大于or

between and:小值在前，大值在后；左右两边都包括边界

is null/is not null：在mysql和oracle中没有=null和!=null

not like,not between and,not in

2、去重：select distinct deptno,mgr保证deptno和mgr两个列组成的值的唯一性

3、排序

order by sal,deptno desc;

desc只能对deptno进行降序排列

底层实现的顺序：首先按照sal升序排列，然后如果有相同的sal，再按照deptno进行降序排列。

4、伪列rowid和rownum

rowid:每一行的物理地址 rownum:每一行排序的序号

select rowid,rownum,empno,ename from emp;

ROWID ROWNUM EMPNO ENAME

------------------ ---------- ---------- ------

AAAMfPAAEAAAAAgAAA 1 7369 SMITH

AAAMfPAAEAAAAAgAAB 2 7499 ALLEN

AAAMfPAAEAAAAAgAAC 3 7521 WARD

AAAMfPAAEAAAAAgAAD 4 7566 JONES

AAAMfPAAEAAAAAgAAE 5 7654 MARTIN

AAAMfPAAEAAAAAgAAF 6 7698 BLAKE

AAAMfPAAEAAAAAgAAG 7 7782 CLARK

AAAMfPAAEAAAAAgAAH 8 7788 SCOTT

AAAMfPAAEAAAAAgAAI 9 7839 KING

AAAMfPAAEAAAAAgAAJ 10 7844 TURNER

AAAMfPAAEAAAAAgAAK 11 7876 ADAMS

5、聚合函数：min(),max(),avg(),sum(),count(),其中count(对于null值行直接滤过)

group by 分组

select deptno,max(sal) from emp group by deptno;//这里的deptno为非聚合函数的列，所有需要参与到分组中去

select deptno,job,max(sal) from emp group by deptno,job;//多列分组，首先按照deptno分组，相同的组再按照job分组

select empno,deptno,sum(sal) from emp group by empno,deptno;

聚合函数group by使用having来过滤分组后的结果，

普通的列使用where过滤

6、别名：select ename as "员工姓名",sal 工资 from emp;

as可以加，也可以省略，如果别名包含了特殊的关键词必须加上""；否则可以不用加""

oracle中字符串和日期都是单引号，只有别名才是双引号。

二、多表查询

只要两个表能够建立关联关系，两个表都能够联合查询

1、内连接：在笛卡尔集上选择了满足on条件的记录行，连接的是两个相同的列的值

(笛卡尔集：记录条数是多个表记录乘积，列数是多个表的列之和)

显式内连接：select * from emp e inner join dept d on e.deptno=d.deptno;

隐式内连接：select * from emp e , dept d where e.deptno=d.deptno;

2、外连接：左外连接left join on/right join on

遵循的SQL99语法 select * from emp left join dept on emp.deptno=dept.deptno;

oracle中(+) select * from emp where emp.deptno=dept.deptno(+);

select d.deptno 部门编号,d.dname 部门名称,count(e.empno) 部门总人数 from emp e, dept d where e.deptno(+)=d.deptno

group by d.deptno,d.dname order by d.deptno;

三、子查询

1、子查询写法

where后的子查询：先查子查询，子查询的结果作为主查询的过滤条件

select ename from emp e where e.deptno in

(select dept.deptno from dept where deptno>10);

from后的子查询：先查子查询，子查询的结果，可以看成一张表，被主查询查

select t.部门总人数 from (select d.deptno 部门编号,d.dname 部门名称,

count(e.empno) 部门总人数 from emp e, dept d

where e.deptno(+)=d.deptno

group by d.deptno,d.dname order by d.deptno) t;

select后的子查询：先查主查询，主查询的结果作为子查询的参数，最后再查主查询

select ename 员工编号,

(select dname from dept where dept.deptno=emp.deptno) 部门名称 from emp;

注意：select后面的子查询，一定是单行子查询(只返回一条结果记录)

2、子查询运算符

单行子查询使用(=,<>,>,>=,

四、分页查询

1、rownum

一张表不经过任何操作默认带有rownum行号,经过排序操作之后，该行号也随着排序了，但不是从1-2-3排序的

为了重新按照1-2-3排序，我们把rownum当成列来使用而不是行号

2、可以提炼成公式：

页码pageNo=1 每页条数pageSize=5

select * from

(select rownum r,t.* from

(select * from emp order by sal desc) t

where rownum <=pageNopageSize)

where r>(pageNo-1)pageSize;

select * from emp order by sal desc limit 0,5;

select * from emp order by sal desc limit (pageNo-1)*pageSize,pageSize;

五、函数：任何一个函数都有返回值 (只作了解，把握可以处理哪几种数据，以及处理数据功能)

参考"oracle函数大全(分类显示).chm"

字符函数

length(),concat()和||,substr()

数值函数

round() trunc()

select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三,round(45.926,-1) 四,round(45.926,-2) 五 from dual;

select trunc(45.926,2) 一,trunc(45.926,1) 二,trunc(45.926,0) 三,trunc(45.926,-1) 四,trunc(45.926,-2) 五 from dual;

日期函数

sysdate systimestamp

next_day(,'星期一')

last_day()

trunc()

months_between()

add_months()

转换函数

to_char() 可以转换成日期或者指定格式

to_number() 字符串转换成数字

to_date() 字符串转换日期

其他函数

关于null的函数：

nvl(a,b) a为null，则返回b

nvl2(a,b,c) a为null,则返回c,否则返回b

在mysql中nullif(a,b) a=b，则返回null,否则返回a

在mysql中使用ifnull(a,b) 如果a为null ,则返回b

条件语句函数(重要)

case when then else end

decode(ename,'',,,,)

分析排名函数()

rank() over(order by xxx ) 相同的值排名相同，排名跳跃

DENSE_RANK() over(order by xxx) 相同的值排名相同，排名连续

ROW_NUMBER() over(order by xxx) 连续的排名，无论值是否相等

六、集合操作

union 去重,union all不去重

集合参与运算(并集，交集，差集)，笔记中图案的黄色部分为查询结果

差集：a minus b a-(a和b的交集)

集合操作必须满足如下原则：

1、两个集合的列数要一致

2、两个集合中的列要顺序相同，对应类型相同

order by放在最后；

select ename,sal from emp

union all

select sal,ename from emp; 会报错

======================================================================================================================================================================================================

一、视图

1、普通视图(复杂查询结果放到一张虚拟表中,对视图的操作其实是构成视图基表操作)

创建视图：create or replace view myview_view as select * from myemp3;

删除视图：drop view myview;

理解：视图一般不推荐做插入、修改操作，推荐做查询，

因为如果遇到一些特殊语句，insert,update 操作就不行，

企业开发中用的最多的是with read only视图

2、物化视图(能够说出来，物化视图是怎么一个原理)

创建视图时是否生成数据：BUILD IMMEDIATE(默认，创建即生成),BUILD DEFERRED

刷新方法有三种： FAST(增量刷新),COMPLETE(全量刷新) ,FORCE(默认强制刷新)

刷新的模式有两种：ON DEMAND(默认，手动刷新) 和 ON COMMIT(自动刷新)

理解：物化视图存储基于基表的数据，也可以称为快照，可以理解成一种特殊的表。

删除物化视图：drop materialized view myemp3_materialized;

3、 普通视图与物化视图的异同

1)相同：都指向一段sql语句

2)不同：普通视图相当于虚拟表；物化视图会真正生成一张特殊的表

3)查询物化视图和查询表的效率一样

4、增量刷新的物化视图

1)增量刷新跟全量刷新的区别？提示：下载app

2)创建物化视图日志，记录基表数据的变化：insert、update、delete

3)创建物化视图的sql语句里面(as后面)一定要有rowid，

物化视图日志表的rowid和物化视图表的rowid做比较

二、序列(主键自增使用)

创建序列：create sequence myemp3_seq;

删除序列：drop sequence myemp3_seq;

使用序列：在insert into myemp3 values(myemp3_seq.nextval,'张三','男',10,'101001@qq.com');

获取当前值使用myemp3_seq.currval

注意：myemp3_seq.nextval 每调用一次nextval 指针向后移动一位，也就是该序列增加一次；

create sequence seq_test999

increment by 10

start with 1

minvalue 0

maxvalue 190

cycle;

会报错：CACHE值必须小于CYCLE值(因为一次CACHE的值有重复的会出错)

-- cache值：默认20 指20个数

-- cycle值：ceil((maxvalue-minvalue)/abs(increment))

-- CACHE值必须小于等于CYCLE值

需要满足一个公式：cache <= ceil((maxvalue-minvalue)/abs(increment))

1 11 21 31 ... 191 20个数

cache 1 11 21 31 ... 191 1 11 21 ...191

场景：发消息 1、2、3、4、5、6、7

20 <= ceil((190-0)/10)

改成

create sequence seq_test999

increment by 10

start with 1

minvalue 0

maxvalue 191

cycle;

三、同义词(给其他对象取一个别名，方便其他用户调用和缩写对象名称)

创建同义词：create synonym mysys_synonym for myemp3; for 可以是其他对象

删除同义词：drop synonym mysys_synonym;

四、索引(提高检索速度)

创建索引：create index myemp3_index on myemp3(name,email) 联合(复合)索引

删除索引：drop index myemp3_index;

创建索引的场景：

该列值很多(覆盖很多条记录)，经常被查询，例如where ename="",where A.xx=B.xx

如果该列值经常做update操作不适合建索引

======================================================================================================================================================================================================

一、plsql的基本结构和变量声明

1、基本结构

declare

--声明变量

begin

--plsql体执行其他操作

exception --有异常捕获处理可以加上该关键字

--异常处理

end;

2、变量声明：(常量、普通变量、引用普通类型变量、引用行类型变量、异常类型变量、游标类型变量)

set serveroutput on --注意这里使用sqldeveloper 工具需要将控制台输出打开一次就行

DECLARE

id constant number(2):=1;--使用constant 关键词定义常量

name VARCHAR2(10):='悟空';--定义指定类型变量

mysal emp.sal%type;--定义引用存在表的列类型

myrow emp%rowtype;--定义引用存在表行类型，相当于java中对象，该行类型包含所有列 select * into v_account from xx

no_data exception;--异常定义

cursor c1 is select sal from emp;--游标定义，该c1中相当于java中集合，给

cursor c2(dno number) is select sal from emp where deptno=dno;---带参游标定义，该c2相当于java中带泛型集合

BEGIN

raise no_data;

exception

when no_data then xx;

when others then xx;

--常量就不能再赋值了 id:=2 错误

SELECT sal INTO mysal FROM emp WHERE empno=7369;

SELECT * INTO myrow FROM emp WHERE empno=7369;

dbms_output.put_line(id);

dbms_output.put_line(name);

dbms_output.put_line(mysal);

dbms_output.put_line(myrow.ename || ' ' || myrow.sal);

END;

二、条件判断语句

1、select case when then when then else end

2、select decode()

3、if then elsif then else end if;

if xx then xx

elsif xx then xx --注意不能写成elseif

elsif xx then xx

else xx

end if;---注意加上分号

三、循环语句

第1种：

for i in 1..100 --退出循环是根据循环次数来定的

loop

end loop;

游标使用for in格式更简单

for 不需要定义任意变量名称 in c1;

loop

end loop;

第2种：

while --此处while后面声明退出循环条件

loop

end loop;

第3种：

open c1; ---此处打开游标

loop

fetch c1 into xx;

exit when c1%notfound; --此处exit when 声明退出循环条件

end loop;

close c1; --此处关闭游标

四、存储过程

第1种创建方式：不带参数

create or replace procedure mypro1 as

--这里可以声明变量

begin

end;

第2种创建方式：带in和out参数，参数个数不限制

create or replace procedure mypro2(eno in emp.empno%type,name out varchar2) as

--这里可以声明变量

myrow emp%rowtype;

begin

select * into myrow from emp where empno=eno;

name:=myrow.ename;

end;

plsql调用存储过程：

execute mypro1;---对于无参数存储过程可以这样调用

或者

call mypro1(xxx)--对于只有in 参数可以这样调用

或者

declare --存储函数和存储过程都可以这样调用

name varchar2;

begin

mypro2(7369,name);---此处声明一个变量用于接收out参数输出

end;

五、存储函数

1、创建存储函数：

create or replace function myfn1(eno in emp.empno%type) return varchar2 as

--实际return name 的name类型一定要和定义的return varchar2要一致

--这里可以声明变量

myrow emp%rowtype;

name varchar2;

begin

select * into myrow from emp where empno=eno;

name:=myrow.ename;

return name;

end;

2、plsql调用存储函数：

select myfn1(7369) from dual;

或者

declare

name varchar2;--此处要声明一个变量用于接收返回的结果

begin

name:=mypro2(7369);

dbms_output.put_line(ename);

end;

六、java api 调用存储过程和存储函数

DriverManager.getConncetion() -> Connection ->CallableStatement prepareCall

-> 设置传入参数直接通过设置类型setInt() ->

设置传出参数，需要registerOutParameter

CallableStatement execute();

CallableStatement调用存储过程和存储函数的语句

{call mypro1(?,?)} 调用存储过程

{?=call myfn1(?)} 调用存储函数

七、触发器

1、前置与后置

前置触发器：sql语句commit之前

后置触发器：sql语句commit之后

2、从影响行数来看

insert into emp() values() 插入了3条记录

行级触发器：触发3次

表级触发器(或者说语句触发器)：触发了1次

3、语法

create or replace trigger 触发器名

before|after

delete|update|insert

on 表名

for each row--行级触发器

begin

pl/sql语句

end;

====================================================================================================================================

oracle总结：

一、数据库基本功底(ddl+dml(insert/update/delete)+单表+多表)

基本语句：select from where group by having order by

多表：where连接内连接+on连接的外连接

jdbc操作相关：

Statement/PreparedStatement/CallableStatement

con.prepareCall("存储函数和存储过程")

mysql连接语法：jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8

oracle连接语法：jdbc:oracle:thin:@192.168.25.128:1521:orcl

二、数据库优化

1、sql语句优化

1)查询的时候尽量使用列名；如：select empno,所有列名写出来 from emp;

2)分组查询有聚合函数,如果使用过滤，使用having

普通查询带条件，使用where

3)在子查询和多表查询之间选择，尽量使用多表查询

4)在集合运算中，如果union 和union all都可以，考虑使用union all因为union需要去掉重复的

5)能够不要使用集合运算就不要使用集合运算

6)对于多个条件连接，可以将false的放在and最右边，可以将true的条件放在or的最右边

2、索引优化

建立索引：单个列建立索引以及多个列上建立索引(复杂或者联合索引)

怎么建立索引：建立索引的两个场景需要斟酌

1)建立索引：在某一个列上建立索引，必须考虑到该列的值是否覆盖更广，并且查询很频繁

2)不建立索引：某一个列经常被改变，就不要建立索引

三、实际中常用的：

DML insert+update+delete+select

DDL create alter drop truncate