oracle的groupby聚合函数效率,Oracle内容概述

Oracle--01

1.数据管理的发展史

手工管理阶段:一个应用程序对应一组数据，也就是说把数据在程序中固定好

文件管理阶段:一个应用程序对应一组或一个文件，文件中保存的数据。

数据管理阶段:就是通过专门的数据管理软件进行数据的管理。

2.关系型数据库就是说由行和列组成的表格。

3.数据库相关的概念

数据：指的就是交流过程中产生的信息

数据库:指的是存储数据的仓库

数据库管理系统(DBMS):一套能够管理数据的软件

4.oracle数据库简介

5.oracle数据库安装

见安装文档

sys 超级管理员

scott 示例用户

6.使用客户端登录Oralce数据库

在DOS命令下登录Oralce数据库的语法:

sqlplus 用户名/密码

注意:如果是超级管理员需要加上as sysdba

在win系统中DOS命令行一定是以管理员身份打开

sqlplus scott/oracle

sqlplus sys/oracle as sysdba

还可以使用Oracle自带的客户端来登录

作业:

1.安装oracle数据库

2.测试登录是否成功

3.写一个java程序验证用户输入的用户名是否合法，合法的条件:用户名只能以字母开头，必须有字母和数字组成，不能有特殊字符，而且长度一定是6-18位，

如果不符合条件，则提示相关的信息，并让用户重新输入。

Oracle--02

1.基础查询之基本语法：

SELECT *[|列名[[AS]别名],列名2...] FROM表名[[AS]别名];

select * from emp;

2.使用||做连接：

Oracle中的字符串用单引号。

例：SELECT '姓名:'||ename||'月薪'||sal FROM emp;

3. 使用DISTINCT可以消除重复内容

在查询单列时才更有效。

select ename,job from emp;

select distinct job from emp;

4.WHERE子句：

使用WHERE指定的条件对数据进行筛选，返回符合条件的记录(行)。

SELECT *[|列名[[AS]别名],列名2...]

FROM 表名[[AS]别名]

WHERE 条件;

1) WHERE 子句可以包括运算符，使用()，常量，列，函数，SQL操作符。

5. 运算符：

1) 算术：+ - * /

2) 比较：> >= )，

ANY(值1,值2...) ALL(值1,值2...)不能单独使用，要配合>,>=,

3) 逻辑：AND OR NOT

6. SQL操作符：

1) IN(值1,值2...)

select * from emp where job in('CLERK','MANAGER');

2) LIKE '模式字符串'：_代表任意一个字符，%代表0到n个字符

select * from emp where job like('%M%');

3) BETWEEN 数值1 AND数值2：不仅可用于数值，还可以用于日期时间

select * from emp where sal between 1000 and 3000;

4) IS NULL,IS NOT NULL：匹配空值，非空值。对于空值一定不能用=或!=

select * from emp where comm is not null;

5) IS NAN：非数字。IS NOT NAN：数字。Oralce特有

select * from emp where sal is not nan;

select * from emp where comm is not nan;

7. Oracle特有的伪列：

1) ROWID: Oracle内部对每个表的每一行都有一个唯一的标识。物理存储所有地址

select rowid from emp;

★2) ROWNUM: Oracle对每次查询结果集的每一行记录都有一个行号。

select rownum from emp;

select * from emp where rownum<5;

对结果集的数据进行标识。不能对ROWNUM直接使用>,>=,!=,between..and

怎么来获取第5条到第10条的记录呢？子查询，连接查询

8.对结果进行排序：

ORDER BY 排序列名[别名] [ASC|DESC],排序列名[别名] [ASC|DESC]...

select * from emp order by hiredate asc;

select * from emp order by hiredate;

select * from emp order by hiredate desc;

9.SQL函数：数据库管理系统(DBMS)提供的一些功能封装。

不同的数据库产品所提供的函数不尽相同。

1) 单行函数：同时只能对一行进行操作，且对于每一行返回一个值。

聚合函数：同时对多行进行操作，这多行只返回一个值。

2) 单行函数：

a) 字符串：lower(char),upper(char),length(char),initcap(x)

select lower('HELLO') from dual;

b) 数字：round(number,n)

select round(23.435,2) from dual;

c) 日期时间：sysdate返回数据库所在操作系统的当前日期时间。

select sysdate from dual;

systimestamp 返回数据库所在操作系统的当前时间戳。

select systimestamp from dual;

last_day(d) 返回指定日期当月的最后一天的日期时间

select last_day(sysdate) from dual;

trunc(d[,unit]) 截断日期时间，unit: 'year', 'month', 'day'

select trunc(sysdate,'year') from dual;

select trunc(sysdate,'month') from dual;

select trunc(sysdate,'day') from dual;

截断日期时间:当年，月，日的第一天

d) dual表是Oracle提供给任何用户的一个表，常用在没有目标表的SELECT语句中。

Oracle--03

1.高级查询之聚合函数：

对多行(组)进行操作，多行返回一个结果。分组函数。

2. 常用的：

1) count(x) 建议使用count(rowid)效率更高

2) sum(x)

3) avg(x)

4) max(x)

5) min(x)

3. 分组统计：

SELECT * FROM 表名

WHERE 过滤条件

GROUP BY 分组条件

ORDER BY 列名,..;

1) SELECT的列没有出现在聚合函数中，就必须出现在GROUP BY子句中。

2) 聚合函数不能出现在WHERE子句中。

4. 分组过滤：

SELECT * FROM tablename

WHERE condition --过滤条件

GROUP BY condition --分组条件

HAVING codition -- 组过滤条件

ORDER BY colName; -- 排序条件

5.多表查询：

使用单个SELECT语句从多个表中取出相关的数据。(外键引用关系)

1) 内连接:列出所有连接表中与连接条件相匹配的数据行。(等值连接，非等值连接)

SELECT ... FROM 主表名 别名

[INNER] JOIN 从表名 别名ON连接条件

WHERE 条件...;

[SELECT ... FROM 表名1,表名2 WHERE连接条件and过滤条件]

特点:只查询出多个表中符合连接条件的数据。

2) 外连接：

a) 左外连接：不仅列出所有连接表中与连接条件相匹配的数据行，还列出左表中符合WHERE过滤条件的数据行。

SELECT ... FROM 主表名 别名

LEFT [OUTER] JOIN 从表名 别名

ON 连接条件

WHERE 条件...;

特点:

会把主表中所有的数据都查询出来，从表中只查询符合连接条件的数据。

b) 右外连接：RIGHT JOIN ... ON ...

特点:

会把从表中所有的数据都查询出来，主表中只查询符合连接条件的数据。

c) 全外连接：FULL JOIN ... ON ...

特点:

会把两个表中所有的数据都查询出来。

3) 交叉连接：产生笛卡尔积。

SELECT ... FROM 主表CROSS JOIN从表;

[SELECT ... FROM 表名1,表名2]

特点:

多个表中记录数相乘即为最后查询的结果。

4) 自连接：参与连接的表都是同一张表。(通过给表取别名虚拟出)

Oracle--04

1.多表查询92标准

内连接

SELECT [DISTINCT] *|[列名 别名,…]

FROM 主表名 别名,从表名 别名

WHERE 主表名.列名=从表名.列名AND WHERE过滤条件;

左连接

SELECT [DISTINCT] *|[列名 别名,…]

FROM 主表名 别名,从表名 别名

WHERE 主表名.列名=从表名.列名(+) AND WHERE过滤条件;

右连接

SELECT [DISTINCT] *|[列名 别名,…]

FROM 主表名 别名,从表名 别名

WHERE 主表名.列名(+)=从表名.列名AND WHERE过滤条件;

交叉连接(产生笛卡积)

SELECT * FROM 主表,从表;

2.子查询：

把SELECT的结果当作另一个查询语句的一部分。

1) 子查询可以出现在主查询语句的任意位置。

2) 子查询效率比表连接查询低。

3) 表连接查询语句都可以替换成子查询语句，但反之不成立。

4) 单行单列子查询中可以使用的比较操作符：= !=  >=

多行单列子查询中可以使用的比较操作符：IN, ANY,ALL

多行多列子查询中可以当作一张表来使用。

3. 集合操作符

1) UNION : 并集，两个结果集合并，没有重复行

2) UNION ALL : 合集，两个结果集合并，包含重复行

3) INTERSECT : 交集，返回两个查询结果集中共有的行

4) MINUS : 差集，返回第一个结果集中减去第二个结果集中相同的行之后的结果。

4. ROWNUM实现分页查询：在有结果集后的行号。

1) 不能直接作为：> >= != between ..and..的条件

2) 不能以基表名或别名作为前缀引用。

3) 示例：

SELECT t.* FROM (SELECT ROWNUM r,e.* FROM emp e WHERE ROWNUM<=10) t WHERE t.r >=6;

1.DML包括INSERT INTO, UPDATE, DELTE

1) 新增：

INSERT INTO 表名[(列名[别名],列名2...)] VALUES(值1,值2...);

如果是字符串值一定要加单引号;如果是日期值，一定要用日期类型，可以使用to_date()函数进行转换;如果是空值使用null;

2) 更新：

UPDATE 表名SET列名=新值,列名2=新值2... [WHERE条件];

3) 删除：一行一行的删除。

DELETE FROM 表名[WHERE条件];

TRUNCATE TABLE 表名; --截断表，清空表中的数据，同时重置表空间

2.TCL：事务的ACID属性

1) SHOW AUTOCOMMIT; -- 查看Oracle数据库是否自动提交事务，默认不自动提交事务

SET AUTOCOMMIT ON|OFF;

2) 显示事务控制：

a) COMMIT; -- 提交事务

b) ROLLBACK [TO 回滚点]; --回滚事务

c) SAVE POINT 名;

3) 隐式事务控制：

a) 自动提交事务：DDL、DCL、SQL*Plus正常退出，JDBC正常关闭数据库连接

b) 自动回滚事务: SQL*Plus异常退出，连接异常中断

3.事务隔离级别:

多个事务同时操作同一数据行时,它们之间的隔离程度。

1) 并发数据库事务可能引起的问题：

a) 第一类丢失更新

b) 脏读:一个事务读到了另一个事务未提交的数据。

c) 不可重复读：一个事务两次读取同一行数据，两次的状态不同。

d) 第二类丢失更新

e) 幻读：一个事务中同一个SQL语句前后两次执行的结果不同。

2) SQL标准中有四种隔离级别：在隔离程度和并发性能两方面权衡考虑

READ UNCOMMITED: 读未提交 。可以避免第一类丢失更新。

READ COMMITED: 读已提交。可以避免第一类丢失更新和脏读。(Oracle,SQL Server默认)

REPEATABLE READ: 可重复读。可以避免第一类丢失更新，脏读，不可重复读，第二类丢失更新。(MySQL默认)

SERIALIZABLE: 串行化。可以避免所有的并发问题。 在执行财务数据更新时采用。

4 Oracle中的事务隔离级别管理：

只支持READ COMMITED和SERIALIZABLE。

set transactionisolation level SERIALIZABLE; -- 更改全局的事务隔离级别

alter session set isolation_level=SERIALIZABLE; --更改当前会话的事务隔离级别

Oracle--05

DCL语句

1.用户(user)管理。

以下操作者必须有相应的权限，本课程以sys登录。

1) 创建用户：CREATE USER用户名IDENTIFIED BY密码;

创建一个用户，默认也创建了一个缺省Schema(方案(数据库对象的集合)名与用户名相同)。

用户可以直接访问缺省方案中的对象。

用户要访问其它方案中的对象，必须有访问权限，且要使用“方案名.对象名”的形式。

2) 修改密码：ALTER USER用户名IDENTIFIED BY新密码;

锁定或解锁用户：ALTER USER用户名ACCOUNT [UN]LOCK;

3) 删除用户：DROP USER用户名;

2. 权限(privilege)管理。

1) 系统权限：DCL、DDL

授权：GRANT权限名,.. TO用户名;

回收：REVOKE权限名,.. FROM用户名;

2) 对象权限：对数据库对象的操作权力

授权：GRANT权限名,.. ON方案名.对象名TO用户名;

回收：REVOKE权限名,.. ON方案名.对象名FROM用户名;

3.角色(role)管理：一组权限的集合。

常用角色：CONNECT,RESOURCE

1) 授权：GRANT角色名,... TO用户名;

回收：REVOKE角色名,... FROM用户名;

4.使用Oracle 10g以上版本

提供的web方式的企业管理器(em)进行管理。

5. create user website identified by website;

grant connect,resource to website;

Oracle--06

DDL语句：数据库对象，表、约束、序列、视图、索引

1. 表管理

CREATE TABLE 表名(

列名 类型 [default默认值] [列约束],

列名 类型 [default默认值] [列约束],

[表级约束]

);

ALTER TABLE 表名ADD(列名 列类型[default默认值]);

ALTER TABLE 表名MODIFY(列名 列类型[default默认值]);

ALTER TABLE 表名DROP COLUMN列名;

DROP TABLE 表名;

TRUNCATE TABLE 表名;

2. 约束管理

a) NOT NULL 非空。列约束

b) UNIQUE 唯一。列约束,表级约束

C) CHECK() 检查。列约束,表级约束

d) PRIMARY KEY 主键。列约束,表级约束

e) FOREIGN KEY 外键。列约束,表级约束。表示表之间的关联关系

ALTER TABLE 表名ADD [CONSTRAINT约束名]约束类型(列名);

ALTER TABLE 表名ADD [CONSTRAINT约束名] FOREIGN KEY(列名) REFERENCES主表名(列名);

ALTER TABLE 表名DROP CONSTRAINT约束名;

3.序列管理：

序列是Oracle中特有的数据库对象，专门用来产生递增的数字。

CREATE SEQUENCE 序列名;

DROP SEUQENCE 序列名;

伪列：NEXTVAL(取下一个序列值), CURRVAL(查看当前的序列值)

insert into 表名(id) values(序列名.nextval);

4.索引管理:

用来提高查询效率的一个数据库对象

CREATE INDEX 索引名ON表名(列名); -- b树索引

DROP INDEX 索引名;

Oracle数据库会为表中指定为primary key或unique约束的列自动创建索引。

5、视图管理：虚拟表。

它的数据来源于其它真实表。为查询方便。更新视图的数据也会更新源表的数据。

CREATE OR REPLACE VIEW 视图名AS select查询语句WITH READY ONLY;

DROP VIEW 视图;

Oracle--07

1. E-R图：实体关系图

2. 映射基数：一对一(OneToOne)，一对多(OneToMany)，多对一(ManyToOne)，多对多(ManyToMany)

3. powerdisgner 创建cdm --转成-->pdm

4. 数据库设计范式

1NF:确保每列的原子性。

2NF:确保每列都和主键相关。

3NF:确保每列都和主键列直接相关。