Oracle总结

--select语句必须与from语句联合使用

SELECT *

FROM emp;

--*与写出每一列的区别

--*的执行效率没有后者高

SELECT * FROM emp;

SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp;

--列的别名

--1.使用as

SELECT empno, ename AS haha

FROM emp;

--2.直接使用空格

SELECT empno, ename haha

FROM emp;

--算术表达式

SELECT ename, sal * 12

FROM emp;

--null值参与算术表达式计算,结果是null

SELECT ename, NVL(sal,0) * 12

FROM emp;

--字符串拼接

--显示 XXX的工资是xxxxxxxx

SELECT ename || '的工资是' || nvl(sal,0)

FROM emp;

--null值参与字符串拼接,结果还是原来的字符串

--‘aaa’|| null 结果是‘aaa’

-- null || 'bbb' 结果是 'bbb'

SELECT ename || '的工资是' || sal

FROM emp;

--distinct关键字

--distinct去掉重复的数据

--查询emp表中部门编号信息

SELECT deptno

FROM emp;

SELECT DISTINCT deptno

FROM emp;

--where语句:条件查询语句

--查询部门编号是10,并且工资大于2500的员工信息 SELECT *

FROM emp

WHERE deptno = 10 AND sal > 2500;

--查询部门编号是10,或者工资大于2500的员工信息 SELECT *

FROM emp

WHERE deptno = 10 OR sal > 2500;

--Oracle中的比较运算符

-- = 相等

-- <>不等于

-- >= 大于等于

-- <= 小于等于

-- > 大于

-- < 小于

--Oracle中特殊的比较运算符

--1.between...and...包含了两边的断点

--查询emp表中工资在2000和3000之间的员工信息 --实际上查询结果是闭区间

SELECT *

FROM emp

WHERE sal BETWEEN 2000 AND 3000;

SELECT *

FROM emp

WHERE sal >= 2000 AND sal <= 3000;

--2.in

--查询emp表中工资是1200,3000,800的员工信息 SELECT *

FROM emp

WHERE sal IN(1250,3000,800);

SELECT *

FROM emp

WHERE sal = 1250 OR sal = 3000 OR sal = 800

--3.like

--经常用于模糊查询(软件的常用功能)

--%通配符:表示0-n个字符

--_通配符:表示1个字符

--查询名字里面包含RD的员工信息

SELECT *

FROM emp

WHERE ename LIKE '%RD%';

--查询名字里倒数第二个字母是T的员工信息

SELECT *

FROM emp

WHERE ename LIKE '%T_';

--名字中包含两个T的员工信息

SELECT *

FROM emp

WHERE ename LIKE '%T%T%';

--名字中包含通配符的处理

--查询名字中带有%的员工信息

--使用\表示转义字符

--\后一位的字符被当做真是的字符处理,而不是当做通配符处理 SELECT *

FROM emp

WHERE ename LIKE '%\%%' ESCAPE '\'

--4.查询数据包含了null值

--IS NULL表示数据时的null的查询

SELECT *

FROM emp

WHERE comm = 0 OR comm IS NULL

--5.NOT表示去反

--与上述特殊比较运算符结合表示取反

--not between and

--not in

--not like

--is not null

--排序

--升序排列

--数字按照从小到大

--日期按照从早到晚

--字符按照字典顺序 啊在张的前面

--查询员工信息,按照工资从低到高进行排列

SELECT *

FROM emp

ORDER BY sal

--查询20号部门员工信息,按照工资从低到高进行排列 --asc表示升序,不写没问题

SELECT *

FROM emp

WHERE deptno = 20

ORDER BY sal ASC

----查询20号部门员工信息,按照工资从高到低进行排列 --desc表示降序,不能省略!!!

SELECT *

FROM emp

WHERE deptno = 20

ORDER BY sal DESC

--单行函数

--1.字符函数

--concat字符串拼接

--||可以拼接多个字符串

--比如'abc' || 'def' || 'ghi'

--concat只能拼两个

--concat(str1,str2)

--将字符串str1和str2进行拼接

SELECT CONCAT('abc','def')

FROM dual;

--dual表,是Oracle特有的

--表里面没有什么数据,只是为了补全SQL语句才使用的

--可以使用嵌套形式完成多个字符串的拼接

SELECT CONCAT(CONCAT('abc','def'),'ghi')

FROM dual;

--substr字符串截取

--substr(str1,num1,num2)

--截取str1字符串,从num1位置开始,截取num2的长度 SELECT SUBSTR('abcdefhij',2,3)

FROM dual;

--instr字符串的查找

--instr(str1,str2,num1,num2)

--在str1中的num1位置起,找到str2第num2次出现的位置 SELECT INSTR('I LIKE GIRL','I',2,2)

FROM dual;

--length字符长度

--length(str1)

--返回str1的长度

SELECT LENGTH('haha')

FROM dual;

--lpad/rpad 左/右补齐

--lpad/rpad('str1',num1,'str2')

--使用str2将str1左/右到num1的长度

SELECT LPAD('haha',10,'*')

FROM dual;

--replace字符串替换

--replace(str1,str2,str3)

--在str1中将str2替换为str3

--将haha的a替换为e

SELECT REPLACE('haha','a','e')

FROM dual;

--数字函数

--round四舍五入

--round(num1,num2)

--将num1四舍五入,num2表示精确的位

--num2为0时表示精确到个位

--num2大于0时表示小数点后几位

--num2小于0时表示小数点前几位

SELECT ROUND(12.5,0)

FROM dual;

SELECT ROUND(12.5,1)

FROM dual;

SELECT ROUND(12.5,-1)

FROM dual;

SELECT ROUND(12.5,-2)

FROM dual;

--trunc(num1,num2)

--截取数字,用法与round相似

SELECT TRUNC(12.5,0)

FROM dual;

SELECT TRUNC(12.5,1)

FROM dual;

SELECT TRUNC(12.5,-1)

FROM dual;

SELECT TRUNC(12.5,-2)

FROM dual;

--mod(num1,num2)

--num1与num2进行取余运算

--取余结果的符号是跟被余数一致

SELECT MOD(5,3)

FROM dual;

SELECT MOD(5,-3)

FROM dual;

SELECT MOD(-5,-3)

FROM dual;

SELECT MOD(-5,3)

FROM dual;

--日期函数

--1.sysdate

--表示系统当前时间

SELECT SYSDATE

FROM dual;

--2.months_between

--months_between(d1,d2)

--表示时间d1和时间d2之间的月数

--d2的时间晚于d1,结果负数

--相当于使用d1减d2

--经常用于计算员工的工龄和司龄

SELECT months_between('01-1月-2013',SYSDATE) FROM dual;

--3.add_months(d1,num1)

-- 在日期d1的基础上,追加num1个月后的时间 SELECT add_months(SYSDATE,1)

FROM dual;

SELECT add_months(SYSDATE,-1)

FROM dual;

--4.last_day(d1)

--返回日期所在月的最后一天

SELECT last_day(SYSDATE)

FROM dual;

SELECT last_day('01-2月-2013')

FROM dual;

SELECT last_day('01-2月-2012')

FROM dual;

--5.其他:next_day,round,trunc

--转换函数

--1.to_date(str1,fmt)

--将str1按照fmt的格式转换成日期类型

SELECT to_date('2013-01-01 21:12:22','YYYY-MM-DD HH24:MI:SS')

FROM dual;

SELECT to_date('20xx年01月01日 21:12:22','YYYY"年"MM"月"DD"日" HH24:MI:SS') FROM dual;

SELECT to_char(SYSDATE,'YYYY"年"MM"月"DD"日" HH24"时"MI"分"SS"秒"') FROM dual;

SELECT to_char(hiredate,'YYYY"年"MM"月"DD"日" HH24"时"MI"分"SS"秒"') FROM emp;

--等值连接

SELECT *

from emp e, dept d

WHERE e.deptno = d.deptno

--左外连接

SELECT *

from emp e, dept d

WHERE e.deptno = d.deptno(+)

SELECT *

from emp e LEFT JOIN dept d

ON(e.deptno = d.deptno)

--右外连接

SELECT *

from emp e, dept d

WHERE e.deptno(+) = d.deptno

SELECT *

from emp e RIGHT JOIN dept d

ON(e.deptno = d.deptno)

--分组函数

--max 最大 min 最小

--sum 求和 avg 求平均

--count 求总数

--求得emp表中的平均工资

SELECT AVG(sal)

FROM emp;

--按照部门求得emp表中的平均工资

SELECT deptno,AVG(sal)

FROM emp

GROUP BY deptno;

--在分组函数SQL语句中,在select语句中出现的非分组函数的列 --必须出现在group by中

SELECT d.deptno,dname,AVG(sal)

FROM emp e, dept d

WHERE e.deptno = d.deptno

GROUP BY d.deptno, dname;

--查询平均工资大于1500的部门编号和平均工资

--分组函数的比较运算不能写在where语句中

SELECT deptno,AVG(sal)

FROM emp

GROUP BY deptno

HAVING AVG(sal) > 2000

--SQL语句的执行顺序

--1.from 从表里取得数据

--2.where 根据条件筛选

--3.group by计算分组函数 分组并进行分组函数的计算 --4.having 根据分组函数进行条件筛选

--5.select 计算表达式,选择要显示的列

--6.order by 排序

--子查询

--1.单行子查询

--找到比SCOTT工资高的员工信息

SELECT *

FROM emp

WHERE sal > (SELECT sal FROM emp ep WHERE ep.ename = 'SCOTT')

--2.多行子查询

--找到与SCOTT和KING工资一样的员工信息

SELECT *

FROM emp

WHERE sal IN (SELECT sal FROM emp ep WHERE ep.ename = 'SCOTT' OR ep.ename = 'KING')

--3.多列子查询

--4.相关子查询

--找到工资高于自身岗位平均工资的员工信息

SELECT *

FROM emp e1

WHERE e1.sal > (SELECT AVG(e2.sal) FROM emp e2 WHERE e2.job = e1.job);

--子查询可以使用的语句

--1.where 2.select 3.from 4.having

--在from语句里写的子查询,查询结果可以当做视图来使用

SELECT

(SELECT sal FROM emp WHERE ename = 'TIM' );

FROM dual;

--DML语句和事务

--1.增加

INSERT INTO emp VALUES(2,'haha','hehe',1,SYSDATE,1200,1200,10);

INSERT INTO emp(empno, ename, job, mgr) VALUES(3,'aaa','aaa',1);

--2.更新

UPDATE emp SET ename = '哈哈' WHERE empno = 2;

UPDATE emp SET ename = '哈哈',sal = 50000 WHERE empno = 2;

--如果没有指定具体的where语句,将更新所有的行

--3.删除

DELETE emp WHERE empno = 3;

--如果没有指定具体的where语句,将删除所有的行

--事务

--原子性:事务是一个不可分割的整体

--一致性:要么都成功要么都失败

--隔离性:事务之间互不影响

--持久性:事务所做出的更改一旦确定,影响的结果是确实的

--DDL

--表和约束

--1.建表语句

CREATE TABLE haha(

hahaid NUMBER,

hahaname VARCHAR2(16),

hahadate DATE

)

--常用的数据类型

--数字型:number

--字符型:char char是固定长度字符串

-- varchar2 varchar2是可变长度字符串

--日期型:date

--char(10) 存abc,后面7位,系统使用空格自动补齐

--varchar2(10) 存abc,就是abc,长度就是3位

--2.修改表

--(1)增加一列

ALTER TABLE haha ADD(hahasal NUMBER);

--(2)修改列的类型

ALTER TABLE haha MODIFY(hahasal VARCHAR2(16));

--(3)删除一列

ALTER TABLE haha DROP(hahasal);

--3.删除表

DROP TABLE haha;

--4.截断表

--将表中的数据清空

TRUNCATE TABLE haha;

--截断表(truncate)与delete有什么区别

--1.truncate表效率高,只记录一条日志 delete表效率低,记录删除条目的日志 --2.truncate表数据不可恢复,delete可以恢复

--truncate与drop有什么区别

--1.truncate表清空数据,但是保留表结构

--2.drop表删除表,表结构都不存在了

--约束

--1.主键约束 primary key 用于确定数据的惟一标识,一般来说,不使用有具体意义的列担任主键

--2.外键约束 foreign key 关联其他表的主键或唯一标识,保证数据输入的准确性(相当于保证数据关系的准确)

--3.唯一性约束 unique 用于保证数据的唯一性

--4.非空约束 not null 用于保证数据不能为空,只能在列级创建

--5.检查性约束 check 用于保证的数据的准确性

--1.直接在列上创建

CREATE TABLE haha(

hid NUMBER PRIMARY KEY,

hname VARCHAR2(15) NOT NULL

)

DROP TABLE haha;

--2.constraint关键字

CREATE TABLE haha(

hid NUMBER,

hname VARCHAR2(16),

CONSTRAINT haha_pk_hid PRIMARY KEY(hid),

CONSTRAINT haha_un_hname UNIQUE(hname)

)

--主键约束与唯一性约束的区别

--(1)主键不能为null,唯一性约束可以为null

--(2)一张表里只能有一个主键约束(一个主键约束可以加在多个列上,被称为联合主键) -- 一张表中可以有多个唯一性约束

--(3)主键是聚集索引,唯一性约束是非聚集索引

--3.not null

--非空约束只能通过列级创建

--4.foreign key

--外键是另外一张表中的主键或唯一约束

--保证数据输入的准确性

CREATE TABLE haha(

hid NUMBER,

hname VARCHAR2(16),

hdid NUMBER,

CONSTRAINT haha_pk_hid PRIMARY KEY(hid),

CONSTRAINT haha_un_hname UNIQUE(hname),

CONSTRAINT haha_fk_hdid FOREIGN KEY(hdid) REFERENCES dept(deptno)

)

INSERT INTO haha VALUES(1,'haha', 20);

--5.check

--检查性约束,保证数据输入的准确性

CREATE TABLE haha(

hid NUMBER,

hname VARCHAR2(16),

hsal NUMBER,

hdid NUMBER,

CONSTRAINT haha_pk_hid PRIMARY KEY(hid),

CONSTRAINT haha_un_hname UNIQUE(hname),

CONSTRAINT haha_ck_hsal CHECK(hsal > 0),

CONSTRAINT haha_fk_hdid FOREIGN KEY(hdid) REFERENCES dept(deptno) )

INSERT INTO haha VALUES(1,'haha',2000,10);

INSERT INTO haha VALUES(2,'hehe',-500,10);

--禁用约束

ALTER TABLE haha DISABLE CONSTRAINT haha_ck_hsal;

--启用约束

ALTER TABLE haha ENABLE CONSTRAINT haha_ck_hsal;

--视图

--不是表,与表的性质很像。

--是数据的透视窗口

--作用:

--(1)将复杂的查询通过存储成一个视图,方便后续多次调用 --(2)隐藏数据细节,将不方便提供的数据隐藏,保证数据安全

--查询员工编号,员工姓名,职位,工资,所在部门名称

SELECT e.empno,e.ename,e.job,e.sal,d.dname

FROM emp e, dept d

WHERE e.deptno = d.deptno(+)

--创建视图

CREATE OR REPLACE VIEW empinfomation

AS

SELECT e.empno,e.ename,e.job,e.sal,d.dname,d.loc

FROM emp e, dept d

WHERE e.deptno = d.deptno(+)

SELECT * FROM empinfomation WHERE sal > 1200 ORDER BY sal ASC;

--删除视图

DROP VIEW empinfomation;

--rownum分页

--查找emp表中,工资从高到低,排在第2位到第6位的员工信息

SELECT * FROM

(SELECT ROWNUM AS rindex,t.* FROM

(SELECT empno,ename,job,mgr,hiredate,nvl(sal,0) AS ssal,comm,deptno FROM emp

ORDER BY ssal DESC) t

) s

WHERE s.rindex BETWEEN 2 AND 6

--索引

--1.null值比较多的列

--2.经常被做为where条件中的列

--3.经常查询的结果是占总数2%-4%的列

--4.表的记录非常多

DROP TABLE haha;

CREATE TABLE haha AS SELECT * FROM emp;

INSERT INTO haha SELECT * FROM haha;

UPDATE haha SET empno = ROWNUM;

SELECT * FROM haha ORDER BY empno;

--创建索引

CREATE INDEX haha_idx_empno ON haha(empno);

--删除索引

DROP INDEX haha_idx_empno;

相关推荐