一、oracle基础知识点
1、什么是数据库(DBMS)
数据库:根据数据类型存放数据的一个软件。 全称:数据库管理系统
Data Base Management System(DBMS)
2、为什么要使用数据库?
与普通文件存储相比,
1>普通文件存储没有数据类型
2>数据量大查询不方便
3>不安全
4>共享性
3、数据库的相关名词
表:存储数据的基本单位 ---- 表格 java 类
|- 行 代表一条记录 一个对象
|- 列 属性名对应的值 属性名
列名 : 属性名
主键:唯一标识一条记录
外键:建立两张表的联系,它来自于别的表的主键
5、常见的关系型数据库有?
Oracle oracle公司【收费】
mysql oracle公司
SQLServer ms微软
DB2 IBM公司
sql语言(Structed query language)
6、数据库的工作模式?
客户端和服务器端 === 共享
8、常用命令【重点】
用户登录: conn 用户名/密码;
查看当前用户所有的表: select *from user_tables;
查看用户的表结构 desc 表名;
查询表中的数据: select * from表名;
清屏: clear
二、select查询语句相关知识点
1、基本语法
select * from 表名;
*代表所有的列 select * from emp;
查询单个列:
select 列名 from 表名;
select job from emp;
去重:DISTINCT 去重,多列去重 DISTINCT 前面不能有列:
多列去重,是指的是列的组合没有重复的就行;
SQL> SELECT DISTINCT name FROM employee;
NAME
--------------------
JOBO
jek
fack
SQL> SELECT DISTINCT name,deptno FROM employee;
NAME DEPTNO
-------------------- ------
fack 0
LAOxue 6
张三 5
jek 6
默默 6
2、where条件
select * from 表名 where条件
-- 查询10号部门的员工
select * from emp where deptno = 10;
select * from emp where job = "SALESMAN" 单引号!
ORA-00904: "SALESMAN":标识符无效
比较运算符
> < >= <=
不等于!= <>
-- 查询工资大于1250
select * from emp where sal > 1250 and sal <= 3000;
-- 工资不为1250的员工信息
select * from emp where sal != 1250;
select * from emp where sal <> 1250; 数据库公认
逻辑运算符
and 并且
-- 查询 job SALESMAN 工资= 1250
select * from emp where job = 'SALESMAN' and sal = 1250;
or 或者
-- 查询job为SALESMAN或者 CLERK的员工信息
select * from emp where job = 'SALESMAN' OR JOB = 'CLERK';
like 像
not like
select * from emp where ename like '%A'; --以A结尾的名字
select * from emp where ename like '%A%';
select * from emp where ename like '%E_';
select * from emp where ename like '%E__';
select * from emp where ename like '_A%';
-- not like
select * from emp where ename not like '%A%';
注意:_代表一个字符
% 代表 0到任意的字符
in(范围)
-- 查询工资为1250和3000的员工信息
select * from emp where sal = 1250 or sal = 3000;
select * from emp where sal in (1250,3000);
-- 查询10号部门和30号部门的员工
select * from emp where deptno in (10,30);
select * from emp where job in ('SALESMAN','CLERK');
between A and B 在A和B之间,包括A和B
-- 查询工资在1000 - 3000 之间的员工信息[包括1000和3000]
select * from emp where sal between 1000 and 3000;
字符串拼接
方法1: 字符串1 ||字符串2 oracle中独有的
方法2:concat(字符串1,字符串2)
-- @qq.com
select ename || '@qq.com' from emp;
-- concat(字符串1,字符串2)
select concat(ename,'@qq.com') from emp;
别名:
数据库中的一个伪表:dual
3、select语句中的函数
3.1 运算函数
+ - * /
-- 每位员工sal+1
select sal+1 from emp;
select sal+0.01 from emp;
mod(值1,值2) 求余数
abs() 求绝对值
3.2 字符串函数
concat(字符串1,字符串2) 进行字符串拼接
lower(字符串|列名) 字符串转小写
upper(字符串|列名) 字符串转大写
instr(字符串1,字符串2,下标)在字符串1中找字符串2,
如果找到返回位置,如果找不到返回0
lenght(str):获取字符串的长度
3.3 日期函数【重点】
特殊的日期:sysdate 系统当前时间
ADD_MONTHS(日期,数字) 加一月 自动进位
select add_months(hiredate,1) from emp where deptno =10;
last_day(日期)获取当月最后一天
例如:select last_day(日期) from dual;
to_char() 把日期格式转换成字符串
select to_char(hiredate,转换格式) from表名
转换格式:yyyy年 mm月dd日hh小时mi分钟ss秒
例如:
12小时制:
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
24小时制:
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
获取Oracle 的时间
SQL> SELECT SYSDATE FROM dual;
SYSDATE
-----------
2018/3/30 1
SQL> SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
--------------------------------------------------------------------------------
30-3月 -18 03.57.32.861000 下午 +08:00
SQL> SELECT TO_DATE ('2008年08月08日 20:08:08','YYYY"年"MM"月"DD"日"HH24:MI:SS') FROM dual;
TO_DATE('2008年08月08日20:08:0
-----------------------------
2008/8/8 20:08:08
SQL> SELECT TO_DATE ('2018-08-08 20:08:08','YYYY-MM-DD HH24:MI:SS') FROM dual;
TO_DATE('2018-08-0820:08:08','
------------------------------
2018/8/8 20:08:08
计算员工的入职时间:
SQL> SELECT name,TRUNC(SYSDATE-birth,0) FROM employee;
NAME TRUNC(SYSDATE-BIRTH,0)
-------------------- ----------------------
jack 662
TO_CHAR关键字:
SQL> SELECT TO_CHAR (SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE,'YYYY"年"MM"月
------------------------------
2018年03月30日 16:46:13
LAST_DAY 当前时间的月底的最后一天
SQL> SELECT LAST_DAY(SYSDATE) FROM dual;
LAST_DAY(SYSDATE)
-----------------
2018/3/31 17:13:0
ADD_MONTHS(DATE,1)按月份的加减,负数为减
SQL> SELECT name,ADD_MONTHS(birth,12*20) FROM employee;
NAME ADD_MONTHS(BIRTH,12*20)
-------------------- -----------------------
jack 2036/6/6
MONTHS_BETWEEN 关键字:
SQL> SELECT name,TRUNC(MONTHS_BETWEEN(SYSDATE,birth),0) FROM employee;
NAME TRUNC(MONTHS_BETWEEN(SYSDATE,B
-------------------- ------------------------------
jack 21
现在时间 3-30 17:50
SQL> SELECT NEXT_DAY(SYSDATE,3) FROM dual;
NEXT_DAY(SYSDATE,3)
-------------------
2018/4/3 17:50:49
SQL> SELECT
LEAST(SYSDATE,TO_DATE('2008-08-07','YYYY-MM-DD')) FROM DUAL;
LEAST(SYSDATE,TO_DATE('2008-08
------------------------------
2008/8/7
SQL> SELECT name,birth FROM employee WHERE birth> TO_DATE('2016-06-09','YYYY-MM-DD');
NAME BIRTH
-------------------- -----------
fack 2017/7/7
3.4 null函数
select nvl(comm,0) from emp;
如果comm为null,则修改成0
如果comm不为null,则为原来的值
判断是否为null
为空:is null
不为空:is not null
SELECT name,birth FROM employee WHERE birth is null;
AS 定义别名,可以省略: 单引号在SQL中指的是字符串,所以用单引号
SELECT name,(salary*12) AS "年薪" FROM employee;
SELECT name,(salary*12) "年薪" FROM employee;
SELECT name,job FROM employee WHERE job ='manager';
3.5 case when
成绩:1-60 c
61-80 b
81-100 a
语法:
case
when 条件1 then 结果1
when 条件2 then 结果2
..
else 结果
end 别名
工资区间 工资级别
800 - 1250 C
1300 - 2975 B
3000 - 5000 A
第一步:先写case when 语句:
case
when sal >= 800 and sal <=1250 then 'C'
when sal >=1300 and sal <= 2975 then 'B'
else 'A'
end 工资级别
第二步:嵌套
select e.*,
case
when sal >= 800 and sal <=1250 then 'C'
when sal >=1300 and sal <= 2975 then 'B'
else 'A'
end 工资级别
from emp e;
4、排序
order by
select * from 表名 order by列名 asc(升序,默认)|desc降序
按工资升序进行查询:
select * from emp order by sal (asc);
降序:
select * from emp order by sal desc;
5、组函数 (聚合函数)
sum() 求和
avg() 求平均值
count() 数量,总数
特殊:count(*)
max() 最大值
min() 最小值
注意:他们都是针对多行记录进行计算的
SQL> SELECT
MAX(salary) AS "最大值",
MIN(salary) AS "最小值",
AVG(salary) "平均值",
SUM(salary) "工资总和"
FROM employee;
最大值 最小值 平均值 工资总和
---------- ---------- ---------- ----------
5400 450 3035 30350
COUNT 统计某列的记录数
SQL> SELECT COUNT(birth) FROM employee;
COUNT(BIRTH)
------------
7
AVG、 COUNT最明显,聚合函数都忽视 null;
所以在查看表的记录数的时候,防止有null的存在,也可以用
SELECT COUNT(*) FROM employee;
三、oracle中的分组
1、分组的定义
根据一个条件分成不同的组,分别进行处理。
2、分组的语法
group by
语法:
select * from 表名 group by 列名1,列名2
--统计每个部门的人数
思路:先根据部门分组,分别统计人数
select count(*),deptno from emp group by deptno;
select count(*),deptno,job from emp group by deptno
ORA-00979: 不是 GROUP BY 表达式
【注意】组函数后面相加别的列,只能加group by后的列名
3、分组的原理
4、分组条件
语法:
select 列1,... ,列n from 表名where 条件group by 列名order by 列名;
where
-- 查询每个部门工资大于2000的人数
select count(*),deptno from emp where sal > 2000 group by deptno ;
-- 查询每个部门中员工名字长度为5的并且含有A的
select count(*),deptno from emp where length(ename) = 5 and ename like '%A%' group by deptno;
select count(sal) 部门人数,deptno 部门编号from emp where length(ename) = 5 and ename like '%A%' group by deptno;
having 条件
语法:
select 列1,... , 列n from 表名where 条件group by 列名 having条件 order by列名;
1、where和having通用的情况:
例如:求每个部门的工资大于3000的员工人数
方式1:select count(*),deptno from emp where sal>3000 group by deptno;
select count(*),deptno,sal from emp group by deptno,sal having sal>3000;
-- 从每个部门的员工人数中查找10号部门的员工人数:
select deptno,count(*) from emp where deptno = 10 group by deptno;
select deptno,count(*) from emp group by deptno having deptno = 10;
2、只能用having而不能有where的情况:
select 列1,... , 列n from 表名where 条件group by 列名 having条件 order by列名;
例如:求每个部门人数大于3的部门编号和部门人数
思路:根据部门编号分组
求出每个部门的人数 人数>3
使用having:
select deptno,count(*) from emp group by deptno having count(*) > 3;
使用where:
select deptno,max(sal) from emp where max(sal)>3000 group by deptno
!报错:ORA-00934:此处不允许使用分组函数
原因:
!select执行顺序:
from ... where ... group by ... having ... select ... order by ...
[注意]
1》执行顺序不是绝对的,如果进行sql优化,执行顺序不一定
2》where和having区别:
where效率高,having效率低,
如果两种都可以使用,推荐使用where。
3》错误用法:where max(sal)>1000
where不能与组函数连用
5、多列分组
-- 求每个部门的工资大于3000的员工总数:
select count(*),deptno,sal from emp group by deptno,sal having sal>3000;
四、oracle 中的伪列
1、伪列的定义
假的列 关键字:rownum 行号
2、伪列的特点
特点:在查询到结果后,对数据编号(行号)
从1开始,每次递增1
3、伪列的生成过程
先查询到结果,后生成rownum
4、另外一个伪列
rowid: 唯一标识表中的一条记录 -- 类似于主键
rownum和rowid的区别
rownum,伪列,行号,从1开始,每次递增1
rowid:类似于主键,唯一标识表中的一条记录。(有规律并且不重复的字符串)
五、oracle中的子查询
1、子查询的语法以及应用
语法:
方式1:当做条件使用
select ... from 表名 where 列名= (select 列名from.....)
-- 找与“刘德华”年龄相同的其他学生的信息
select * from m_student where age = (select age from m_student where name = '刘德华');
方式2:当做临时表
-- 查询从年龄18的学生中查询名字长度为2的学生信息
select * from (select * from m_student where age = 18) where length(name) = 2;
2、分页查询
-- 查询年龄18的前3条记录
select rownum,m.* from m_student m where age = 18 and rownum < =3;
注意: rownum与where连用,只能用< <= 不能使用> >=
select b.*,rn from (
select rownum rn,a.* from
(select * from m_student)a
where rownum <= 11
) b where rn >=5;
简单的分页查询: btween ... and ...
第一步,加rownum
select rownnum rn,a.* from m_student a
第二步,直接用between ... and...
select b.* from (select rownnum rn,a.* from m_student a)b where rn between 5 and 11;
分页的页数:
每页记录数m
总记录数count(*) c
页数: c/m 整除 页数= c/m
不整除 页数 = c/m +1
between and
假设页数为x,每页记录数为y
select b.* from (select rownnum rn,a.* from m_student a)b where rn between x*y+1 and x*y;【第一页,最后一页除外除外】
// (x-1)y+1 x*y
3.1 内连
语法:select... from表1 inner join 表2on 连接条件
--查询员工编号为7369的员工信息以及所在部门信息
方式1:select e.*,d.* from emp e inner join dept d on e.deptno = d.deptno where e.empno = 7369;
方式2:
select e.*,d.* from emp e inner join dept d on e.deptno = d.deptno and e.empno = 7369;
特点:两张表对应的记录都会显示【常用】
3.2 左(外)连接
语法:select... from表1 left (outer) join 表2on 连接条件
-- 查询所有员工以及他的部门信息
select e.*,d.* from dept e left outer join emp d on e.deptno = d.deptno;
特点:以左表记录为主,左表与右表没有对应的记录时,左表的记录也会显示
(左表的记录都会显示)
3.3 右(外)连接
语法:select... from表1 right (outer) join 表2on 连接条件
-- 查询所有员工以及他的部门信息
select e.*,d.* from dept e right outer join emp d on e.deptno = d.deptno;
特点:以右表记录为主,右表与左表没有对应的记录时,右表的记录也会显示
(右表的记录都会显示)
3.4 自连接(特殊的内连接)
自己连接自己
语法:select... from表1 inner join 表2on 连接条件
-- 查询员工编号为:7369的信息以及他的部门主管的信息
select e1.*,e2.* from emp e1 inner join emp e2 on e1.mgr = e2.empno where e1.empno = 7369;
4、多表连接【了解】
语法:select... from表1 inner join 表2on 连接条件
inner join表3 on 连接条件
hr用户下:
员工表:employees job_id department_id
职位表:jobs job_id
部门表:departments department_id
select e1.*,e2.*,e3.* from employees e1
inner join jobs e2 on e1.job_id = e2.job_id
inner join departments e3 on e1.department_id = e3.department_id
where e1.employee_id = 250;
5、补充:集合运算符【了解】
并集
union 去重
union all 不去重
select * from emp where deptno >=10
union
select * from emp where deptno = 20;
select * from emp where deptno >=10
union all
select * from emp where deptno = 20;
差集 minus
select * from emp where deptno >=10
minus
select * from emp where deptno = 20;
交集 intersect
select * from emp where deptno >=10
intersect
select * from emp where deptno = 20;
1、表的创建
1.1 创建方式
语句创建
图形化界面创建
1.2 创建语法
create table 表名(
列名 列的数据类型 约束类型 --可以有多个
);
-- 创建一张明星表 star (不能与关键字冲突,可以用_)
create table star(
s_id number(3) primary key, --明星编号,主键
s_name varchar2(30) not null, --姓名 ,不能为空
s_phoneNumber number(11) unique, --电话号码,唯一
s_age number(3) check (s_age > 18) --年龄,age>18
);
注意:一个列可以加多个约束,约束之间用空格隔开
1.3 数据类型
oracle java
number 基本数据类型 boolean 0男 1女
number(参数1,参数2)
参数数字: number(3) 整数位:0-999
number(3,2) 整数位:0-999 2:代表小数位是两位
char() String
varchar2() String
char(10) -- 10个英文字母
varchar2(10) -- 10个英文字母
'zhangsan' 8个字节
char 'zhangsan__' 定长字符串,实际长度不足,使用空格补齐!
varchar2 'zhangsan' 长度可变字符串
varchar2(30) gbk 一个汉字 2个字节
utf-8 一个汉字占3个字节
date/timestamp 日期
date 年月日时分秒
timestamp 精确到毫秒
1.3 约束类型
主键约束:唯一标识一条一条记录 关键字:primary key
外键约束:引用自别的表的主键 关键字:foreign key
唯一约束:该列的值是唯一的 关键字:unique
非空约束:检查该列是否可以为null 关键字(not) null
检查约束:根据一个条件检查该列的值 关键字:check
注意:一个列可以加多个约束,多个约束之间用空格隔开!
外键约束:
-- 创建班级表
create table h_class(
id number(3) primary key,
name varchar2(30)
);
-- 插入测试数据
insert into h_class values (101,'兴学1班');
insert into h_class values (102,'兴学2班');
commit;
-- 创建学生表
create table h_student(
id number(3) primary key,
name varchar2(30),
c_id references h_class(id) -- 创建外键
);
-- 插入测试数据
insert into h_student values (1,'张三',101);
insert into h_student values (2,'张三',102);
commit;
-- 如果插入班级表中不存在的班级,报错
insert into h_student values (3,'张三',103);
ORA-02291: 违反完整约束条件 (HR.SYS_C004406) - 未找到父项关键字
2、表的删除
语法: drop table 表名
drop table star;
!删除表的同时,会删除表中的所有数据
谨慎使用!
3、表的修改
对表结构的修改:
语法:alter table 表名 操作
1》给表中添加列
alter table animal add color varchar2(30);
2》修改列名
alter table animal rename column color to color_one;
3》修改长度
alter table animal modify color_one varchar2(50);
4、表中数据的增删改
4.1插入数据:
语法:
insert into 表名 (列1,列2 ...列n) values (值1,值2,...值n);
注意:插入的值对应数据类型
例如:向star表中插入数据
insert into star (s_id,s_name,s_phoneNumber,s_age) values (1,'张乐',120.20);
语法2:
insert into 表名 values (值1,值2,...值n); 注意:插入的值对应数据类型
insert into star values (5,'小暖暖',150,19);
================================================================================
4.2 修改数据:
语法:update表名 set 列名1 = 值1,列2 =值2, ...列n =值n where条件
-- 修改s_id = 2的名字为 小辣椒
update star set s_name = '小辣椒' where s_id = 2;
================================================================================
4.3 删除数据
语法:delete from表名 where 条件
-- 删除s_id = 5的记录
delete from star where s_id = 5;
【注意】删除是一个危险的操作!!!
数据的备份
5、提交和回滚【重点】
提交:commit 只针对增删改有效
对于增删改一系列操作,要么同时成功,要么同时失败
回滚:rollback
要么同时失败
对增删该操作的确认
DML (data manipulation language)数据库操作语言 --- 增删改
DDL (Data Definition language)数据库定义语言 --表的创建,其他命令
七、事务transaction
1、事务的定
多条执行的sql要么同时成功(commit),要么同时失败(rollback)。
2、事务的开始和结束
开始:执行增删改语句代表事务的开始
结束:rollback/commit
3、并发访问【锁】
多个用户同时访问同一条记录(修改)
oracle会给数据加锁,一个用户没有提交或者回滚,另外一个用户无法对同一条记录进行修改(小闪电变亮)
确认命令执行一个有效,再次执行无效
锁:数据库的一种保护机制
当一个用户对一条记录修改时,数据库会对该记录上锁
此时如果另外一个用户也同时修改该条记录,就无法成功操作,
只有等第一个用户执行commit/rollback时,此时才会释放锁,
另外一个用户才可以执行对该记录的操作
锁:
行级锁: 对一行记录加锁
表级锁: 对表加锁
4、事务的特性
ACID
A 原子性 : 多条sql的操作是一个整体
C 一致性 : 多个客户端看到结果是一致
I 隔离性 : 事务之间影响级别
D 持久性 : 做的增删改操作永久有效
八、表的其他概念
1、序列
1.1 序列的创建
create sequence 序列名;
-- 创建序列
create sequence m_seq;
1.2 序列的使用
序列的使用 序列名.nextval --- 代表序列的下一个值
序列名.currval --- 代表序列的当前值
insert into moon values (m_seq.nextval,'张三',to_date('2017-7-17','yyyy-mm-dd'));
1.3 序列的作用
作用:自动生成主键
1.4 序列的删除
drop sequence 序列名;
drop sequence m_seq;
select m_seq.currval from dual
ORA-02289: 序列不存在
2、视图
2.1 视图的创建
create view 视图名 as select....
-- 视图
create view emp_view as
select e.*,
case
when sal >= 800 and sal <=1250 then 'C'
when sal >=1300 and sal <= 2975 then 'B'
else 'A'
end 工资级别
from emp e;
使用:
select * from emp_view;
2.2 视图的作用
作用:隐藏基表结构,方便查询
2.3 视图的删除
drop view 视图名;
drop view emp_view;
注意:
select * from user_tables;
user_tables 是一个视图
3、索引
3.1 索引的创建
create index 索引名 on 表(列);
create index emp_no on emp(ename);
使用:(与普通查询一样)
select * from emp where ename = 'KING';
注意:索引不是越多越好,本质:用空间换取时间。
3.2 索引的作用
作用:提高查询速度
Oracle MySql 都是DBMS 数据库管理系统,Linux 是开源的, 所以相对于 windows 的系统安全
查询表结构:
DESC employee;
设置某个字段的默认值:
ALTER TABLE employee
ADD( hiredate DATE DEFAULT SYSDATE);
建表:
CREATE TABLE employee(
id NUMBER(4),
name VARCHAR2(20),
gender CHAR(1) DEFAULT 'M',
birth DATE, salary NUMBER(6,2),
job VARCHAR2(30),
deptno NUMBER(2)
);
ALTER TABLE employee MODIFY(
job VARCHAR2(40) DEFAULT 'CLERK'
);
添加数据:
INSERT INTO employee (
id,name,salary,deptno
) VALUES (
1,'jack',3600,5
);
事物提交:
COMMIT;
INSERT INTO employee
(id,name,job,birth,deptno)
VALUES
(3,'dido','MANAGER',
在添加数据的时候,使用TO_DATE(时间的字符串,要转换的格式)
TO_DATE('2018-08-08','YYYY-MM-DD'),5
);
UPDATE employee
在SQL中单引号表示字符串,双引号用于别名;
SET gender = 'N',birth =
TO_DATE('2016-06-06','YYYY-MM-DD')
where id =1;
DELETE FROM employee 会清空表中的所有数据
DELETE FROM employee WHERE id = 3;
Java 中解决的转换问题 不放在成 Oracle 数据库中解决
SELECT name,salary/1000.00 FROM employee;
SELECT name,salary/1000.00*12 FROM employee;
将两个字段拼接在一起,形成一个字符串:
SELECT CONCAT(name,salary)FROM employee;
SELECT CONCAT(CONCAT(name,', '),salary) FROM employee;
SELECT name||','||salary FROM employee;
查看某个属性的长度,对定长的列(varchar2)有效:
SELECT name,LENGTH(name) FROM employee;伪表的用法:
查询的内容不和任何表中数据有关系时,可以使用伪表,伪表只会查询出一条记录;
SELECT UPPER('helloworld') FROM dual;
在Oracle 中获取当前系统时间:
SELECT sysdate FROM dual;
SQL> SELECT TRIM('E' FROM 'EEHOLLEWORLDEEE') FROM dual;
SELECT LTRIM('serreessserhelloworld','ser') FROM dual;
SUBSTR关键字,在ORALCE中的字符串的下标从1开始
Thinking 下标是从1到8
SELECT SUBSTR('thinking in java',13,4) FROM dual;SELECT SUBSTR('thinking in java',10)FROM dual;
SELECT SUBSTR('thinking in java',-4,4) FROM dual;
INSTR关键字
SELECT INSTR ('thinking in java','in') as result FROM dual;
SELECT INSTR ('thinking in java','in',1,2) as result FROM dual;
SELECT INSTR ('thinking in java','in',1,3) as result FROM dual;
ROUND关键字:
SQL> SELECT ROUND(45.678,2) FROM dual;
ROUND(45.678,2)
---------------
45.68
SQL> SELECT ROUND(45.678,0) FROM dual;
---------------
46
SQL> SELECT ROUND(45.678,-1) FROM dual;
----------------
50
SQL> SELECT ROUND(45.678,-2) FROM dual;
----------------
0
TRUNC 关键字:
SQL> SELECT TRUNC (1234.567,0) FROM dual;
TRUNC(1234.567,0)
-----------------
1234
SQL> SELECT TRUNC (12345.678,-1) FROM dual;
TRUNC(12345.678,-1)
-------------------
12340
SQL> SELECT TRUNC (12345.678,2) FROM dual;
TRUNC(12345.678,2)
------------------
12345.67
SQL> SELECT TRUNC (12345.678,-3) FROM dual;
TRUNC(12345.678,-3)
-------------------
12000
MOD关键字求余数:
SQL> SELECT name,salary, MOD(salary,1000) FROM employee;
NAME SALARY MOD(SALARY,1000)
-------------------- -------- ----------------
jack 3600.00 600
张三 3400.00 400
JOBO 450.00 450
dido 600.00 600
SQL> SELECT name,salary, MOD(salary,100) FROM employee;
NAME SALARY MOD(SALARY,100)
-------------------- -------- ---------------
jack 3600.00 0
张三 3400.00 0
JOBO 450.00 50
dido 600.00 0
SQL中的CEIL 、FLOOR 关键字:
SQL> SELECT CEIL(456.78) FROM dual;
CEIL(456.78)
------------
457
SQL> SELECT FLOOR(456.78) FROM dual;
FLOOR(456.78)
-------------
456
函数在where条件中使用:
SQL> SELECT name,salary FROM employee where name=UPPER('jobo');
NAME SALARY
-------------------- --------
JOBO 450.00
SQL> SELECT name,deptno FROM employee where length(name)=4;
NAME DEPTNO
-------------------- ------
jack 5
JOBO 1
dido 1
SQL> SELECT name,deptno FROM employee where length(name)=2;
NAME DEPTNO
-------------------- -----
张三 5
SQL> SELECT name,salary,deptno FROM employee WHERE INSTR(name,'a',1)=2;
NAME SALARY DEPTNO
-------------------- -------- ------
jack 3600.00 5
EXTRACT 关键字:
SQL> SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;
EXTRACT(YEARFROMSYSDATE)
------------------------
2018
SQL> SELECT
name,birth
FROM employee WHERE
EXTRACT(YEAR FROM birth)=2016;
NAME BIRTH
-------------------- -----------
jack 2016/6/6
SQL> INSERT INTO employee
(id,name,birth,salary,job,hiredate,deptno)
VALUES
(5,'fack', TO_DATE('2017-07-07','YYYY-MM-DD'), 5300,'manager',SYSDATE,0);
SQL> SELECT name,salary,deptno
FROM employee
WHERE salary>3000 OR deptno =5;
NAME SALARY DEPTNO
-------------------- -------- ------
jack 3600.00 5
张三 3400.00 5
fack 5300.00 0
SQL> SELECT name,salary,deptno
FROM employee
WHERE salary >3000 AND deptno =5;
NAME SALARY DEPTNO
-------------------- -------- ------
jack 3600.00 5
张三 3400.00 5
BETWEEN 小时间 and 大时间
SQL> SELECT name,birth salary
FROM employee
WHERE birth
BETWEEN
TO_DATE('2016-06-09','YYYY-MM-DD')
AND
TO_DATE('2018-01-10','YYYY-MM-DD');
NAME SALARY
-------------------- -----------
fack 2017/7/7
默默 2017/7/5
LAOxue 2017/9/9
LAOxue 2017/9/9
ORDER BY排序 默认升序、DESC 降序;
Null 值在排序中被认为最大值
SQL> SELECT name,deptno FROM employee ORDER BY deptno DESC;
NAME DEPTNO
-------------------- ------
jek 6
张三 5
dido 1
fack 0
多字段ORDER BY 排序,它是有优先级的,deptno、 salary
SQL> SELECT name,deptno,salary FROM employee ORDER BY deptno ASC ,salary ASC;
NAME DEPTNO SALARY
-------------------- ------ --------
fack 0 5300.00
JOBO 1 450.00
dido 1 600.00
张三 5 3400.00
jack 5 3600.00
LAOxue 6 2400.00
LAOxue 6 2400.00
LAOxue 6 2400.00
默默 6 4400.00
jek 6 5400.00
各个关键字的执行顺序?
GROUP BY 分组:
SQL> SELECT AVG(salary),deptno FROM employee GROUP BY deptno;
AVG(SALARY) DEPTNO
----------- ------
525 1
3400 6
3500 5
5300 0
WHERE 和HAVING 的区别?
SQL> SELECT AVG(salary),deptno FROM employee GROUP BY deptno HAVING AVG(salary)>2000;
AVG(SALARY) DEPTNO
----------- ------
3400 6
3500 5
5300 0
N 张表要有至少 N-1 个链接条件;
关联查询 和 内连接的效率问题?
关联查询在数据库中还先转换成内连接的形式查询;
不满足连接条件的记录是不会在关联查询中被查询出来的!
树状结构的信息,用自连接尝试,换句话说:就是存到一张表中。
JOIN ... ON ...
LEFT/RIGHT/FULL OUTER JOIN ...ON...
单行子查询返回多个行:不能用 =
ROWNUM :
怎么随机生成表中的数据?
UNION 和UNION ALL的区别,一个不重复,一个重复。
INTERSECT 交集!
INSERT INTO sales_tab
借助DBMS(数据库管理系统 随机创建数据):
SELECT TRUNC(DBMS_RANDOM.value(2010,2012)) AS year_id,
TRUNC(DBMS_RANDOM.value(1,13))AS month_id, TRUNC(DBMS_RANDOM.VALUE(1,32))AS day_id,
ROUND(DBMS_RANDOM.value(1,100),2)AS sales_value
FROM dual
CONNECT BY LEVEL<=1000;
把销售表按照时间的顺序排序:
SELECT year_id,month_id,day_id,sales_value
FROM sales_tab
ORDER BY year_id,month_id,day_id;
查询每月的营业额:
SELECT year_id,month_id,SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id
ORDER BY year_id,month_id ;
总营业额:
SQL> SELECT SUM(SALES_value) FROM sales_tab;
SUM(SALES_VALUE)
----------------
51244.11
用一个SQL查询每年、每月、每天以及总营业额:(可以利用高级分组函数)
SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab
GROUP BY ROLLUP(year_id,month_id,day_id)
ORDER BY year_id,month_id,day_id ;
CUBE(2^n 次方种情况):
SQL> SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab
GROUP BY CUBE(year_id,month_id,day_id)
ORDER BY year_id,month_id,day_id ;
更灵活的高级分组函数:FROUPING SETS
SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab
GROUP BY GROUPING SETS “自己定义需求”
((year_id,month_id,day_id),(year_id,month_id))
ORDER BY year_id,month_id,day_id ;
视图 也可以称为:虚拟的表,不过不是表,简化SQL语句复杂度。
所有的视图、表明不能重复,所有命名的时候: v_...
查看视图:
SELECT * FROM V_EMP_10
对视图进行操作,其实是对生产该视图对应的表进行操作,但是不能违反人家表的基本结构要求。所有说视图的操作不当会造成对基本的污染。但是删除没有污染!
删除不能删除看不见的信息???所有有检查选项,
但是检查选项不能万能不,他不能限制,在进行视图可见的操作,但是不否合基本的结构要求,他限制不了,所以 操作的时候也会有这方面的意外。
删除视图不会影响表中的信息,但是删除视图中的信息回影响基表中的信息 !
序列:
序列缓存,和序列断裂现象、数据字典
Like 不使用索引。
复合索引
在修改唯一约的时候, 不能在表中存在重复的数据, null除外;
主键是非空合二为一, 不能是null 做主键
数据字典
该文章知识点比较乱,看到的人别介意,我这个是给自己平时总结!