Oracle笔记1

1.口令管理的界面

超级管理员账户: sys-change_on_install;
普通管理员: system-manager;
[解锁]普通用户: scott-tiger; 
[解锁]大数据用户: sh-sh

2.如果要进行oracle的卸载需要三步

a.执行Oracle提供的卸载程序;
b.进入注册表(regedit.exe),然后删除掉与Oracle有关的配置项;
c.重启电脑,进入安全模式,删除所有Oracle文件

3.安装完成之后,关闭开机自启

在计算机管理中将Oracle的服务全部改成[手动]启动,共有八个,有两个是禁用的
OracleVssWriterMLDN、OracleDBConsolemldn、OracleMTSRecoveryService
OracleOraDb11g_home1ClrAgent、OracleOraDb11g_home1TNSListener、OracleServiceMLDN

4.简化输入:在dos窗口,输入sqlplus scott/tiger

如果想要知道当前的登录账户是哪一位:show user;
切换账户:conn 用户名[/密码][AS SYSDBA];
如果使用超级管理员则必须追加"AS SYSDBA"
切换到system账户:conn system/manager;
切换到sys账户:conn sys/change_on_install AS SYSDBA;
清屏:clear scr
设置每行显示的数据长度:set linesize 每行长度(300);
设置每页显示的数据长度:set pagesize 每行长度(30);
如果没有图形界面,可以这样执行SQL语句；edit xx(生成一个xx.sql的文件)
接着@xx,即可执行编写的语句.也可以这样执行@绝对路径\xx,
调用本机命令:host copy d:\a.sql d:\b.sql

5.SQL语法标准

DML(数据操作语言):数据库的具体操作,四个形式CRUD,最常用的是查询操作;
DDL(数据定义语言):定义数据库对象、表、用户、索引;
DCL(数据控制语言):用户的授权管理

6.scott用户数据表

查看数据表用select * from tab;
查看每一张表的结构desc 表名;
四张表:部门表(dept)、雇员表(emp)、工资等级表(salgrade)、工资表(bonus)
Oracle的日期是有时间的,MySQL就只有日期
数据表结构-emp(雇员表)
EMPNO     NUMBER(4)     雇员编号
ENAME     VARCHAR2(10)  雇员姓名
JOB       VARCHAR2(9)   雇员职位
MGR       NUMBER(4)     雇员的领导编号
HIREDATE  DATE          雇用日期
SAL       NUMBER(7,2)   基本工资
COMM      NUMBER(7,2)   佣金,只有销售才有佣金
DEPTNO    NUMBER(2)     所在部门编号

7.编码习惯:固定的语法用大写字母,可更换部分用小写字母.

SELECT [DISTINCT] *|列名称[别名]
DISTINCT:消除重复数据行的显示
查看员工的年薪,但这样的显示--sal*12不好看,可以设置别名
SELECT empno,ename,job,sal*15+(200+100)*12 FROM emp;
SELECT empno,ename,job,sal*12 income FROM emp;
DISTINCT:进行重复数据消除时,所有的数据列重复时才允许消除
SELECT DISTINCT job FROM emp;
常量查询:SELECT '雇员信息',10,ename FROM emp;
如果是字符串则一定要使用单引号声明;
如果是数字则可以直接编写
字符串靠左对齐,数字靠右对齐

字符串连接:||相当于加号,一旦连接,就只有一列数据返回
SELECT '雇员姓名:' || ename ||',职位:' || job FROM emp;
SELECT '雇员姓名:' || ename ||',职位:' || job info FROM emp;

简单查询:
FROM子句优先于SELECT子句执行,以确定数据的来源

8.简单查询是控制行,限定查询是控制列

WHERE子句中常用的判断符:>、>=、<、<=、=、<>、!=、in
查询工资在1000~1500范围内的非销售人员的empno,ename,job,sal
SELECT empno,ename,job,sal FROM emp WHERE sal>=1000 AND sal<=1500 AND job!='SALESMAN';

9.范围查询:BETWEEN...AND

a.带有边界值的范围查询
b.可以使用在各种数据类型上
范例:查询所有在1981年雇佣的雇员信息
1981年雇佣:1981-01-01('01-1月-1981')~1981-12-31('31-12月-1981')
SELECT * FROM emp WHERE hiredate BETWEEN '01-JAN-1981' AND '31-DEC-1981';

10.空判断:IS NULL、IS NOTNULL

在数据库中称之为一个不确定的内容,如果使用null,任何计算结果都为null
范例:查询所有不领取雇佣金的雇员信息
SELECT * FROM emp WHERE comm IS NULL;

11.模糊查询:LIKE

使用LIKE可以实现关键字的检索,而后提供两个运算匹配符:
"_":表示匹配任意一位字符;
"%":表示匹配任意的0位、1位、多位字符.
范例:查询雇员姓名以A开头的所有员工信息
SELECT * FROM emp WHERE ename LIKE 'A%';
范例:查询雇员姓名中第二个字母为A的员工信息
SELECT * FROM emp WHERE ename LIKE '_A%';
取代之前的BETWEEN...AND
SELECT * FROM emp WHERE hiredate LIKE '%-81';

12.范围查询:IN、NOT IN

范例:查询出雇员编号为7369、7566、7788、9999的雇员信息
SELECT * FROM emp WHERE empno IN (7369,7566,7839,9999);
如果NOT IN中出现有null肯定是程序问题,如果发现有null,就不进行查询了,也不返回任何数据
SELECT * FROM emp WHERE empno NOT IN (7369,7566,7839,NULL);

13.查询排序

③SELECT [DISTINCT] * | 列名称 [别名] , 列名称 [别名] ,... | 数据计算
①FROM 数据表 [别名] 
②[WHERE 限定条件(s)]
④[ORDER BY 排序字段 [ASC | DESC],排序字段 [ASC | DESC],.....];
SELECT子句在WHERE子句之后执行,所以WHERE子句无法使用SELECT子句定义的别名.
ASC:升序,默认为此模式;      DESC:降序
SELECT * FROM emp ORDER BY sal DESC;
范例:查询所有雇员的信息,要求按照工资由高到低排序,如果工资相同则按照雇用日期由早到晚排序
SELECT * FROM emp ORDER BY sal DESC , hiredate ASC;
查询雇员部门是10和20的信息,...
SELECT * FROM emp WHERE deptno IN (10,20) ORDER BY sal DESC,hiredate ASC;
ORDER BY用SELECT的别名
SELECT empno,ename,sal*12 income FROM emp ORDER BY income DESC ;

14.单行函数

字符串函数:转大写UPPER、转小写LOWER
SELECT * FROM emp WHERE ename=UPPER('&inputname');
SELECT LOWER(ename) FROM emp;
首字母大写:字符串 INITCAP(数据|列)
SELECT INITCAP(ename) FROM emp;
计算字符串长度:数字 LENTH(数据|列)
SELECT * FROM emp WHERE LENGTH(ename)=5;
字符串截取:字符串 SUBSTR(数据|列)
设置指定索引和截取长度
SELECT SUBSTR('helloworld',6,2) FROM dual;
索引设置为0,跟默认索引从1开始,是一样的;也可以设置为负数
范例:要求截取每一个雇员姓名的前三个和后三个字母
SELECT ename,SUBSTR(ename,1,3),SUBSTR(ename,-3) FROM emp;
字符串替换:字符串 REPLACE(数据|列,要替换的数据,替换数据)
SELECT REPLACE('helloworld','l','_') FROM dual;
直接使用一张实体表进行查询没有意义,可以使用虚拟表--dual

数值函数
ROUND(数据|列[,保留小数位]):四舍五入
TRUNC(数据|列[,保留小数位]):数据截取,不四舍五入,直接将小数截取掉
SELECT ename,job,TRUNC(sal/30) FROM emp;
MOD(数据|列):求余数
日期函数(避免闰年问题)、转换函数、通用函数
SELECT SYSDATE FROM dual;
SELECT ADD_MONTHS(SYSDATE,4) FROM dual;
用于计算date1和date2之间有几个月,如果date1比date2晚,那么就返回一个正数.
MONTHS_BETWEEN (date1, date2)
转换日期格式
SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd') FROM dual;
NVL():第一个参数为空那么显示第二个参数的值,如果第一个参数的值不为空,则显示第一个参数本来的值.
SELECT ename,sal,NVL(comm,0),(sal+NVL(comm,0))*12 income FROM emp;
DECODE():将查询结果翻译成其他值

15.多表查询慎用,得消除笛卡尔积

本质:写好条件之后,去另一张表找只与自己有关的数据.
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;
SELECT * FROM emp e, dept d WHERE e.deptno=d.deptno;
使用这两张表sales、costs进行多表查询,由于数据量大,导致时间很长
范例:查询每个雇员的编号、姓名、工资、部门名称
SELECT e.empno,e.ename,e.job,e.sal,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno;
范例:查询每个雇员的编号、姓名、工资、工资等级
SELECT e.empno,e.ename,e.sal,s.grade
FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;
范例:查询每个雇员的编号、姓名、工资、工资等级、部门名称
SELECT e.empno,e.ename,e.job,e.sal,s.grade,d.dname,d.loc
FROM emp e , salgrade s,dept d
WHERE e.sal BETWEEN s.losal AND s.hisal AND e.deptno=d.deptno;

16.两种表连接

内连接:等值连接
示例:在emp中创建一个没有部门的员工
INSERT INTO emp(empno,ename) VALUES (9999,'HELLO');
此时进行连接查询
SELECT e.empno,e.ename,d.deptno,d.dname FROM emp e,dept d
WHERE e.deptno=d.deptno;
e.deptno=d.deptno(内连接)凡是不满足于此条件的,都不会显示

外连接:数据表记录的全部显示,左外连接、右外连接、全外连接
左外连接(字段=字段(+)):左表的数据要求全部显示
右外连接(字段(+)=字段):右表的数据要求全部显示
自身连接:数据表自己关联自己
范例:查询每个雇员的编号、姓名、职位、领导姓名
SELECT e.ename,e.job,m.ename FROM emp e, emp m WHERE e.mgr=m.empno;
显示左表全部内容
SELECT e.ename,e.job,m.ename FROM emp e, emp m WHERE e.mgr=m.empno(+);

17.SQL1999语法

交叉连接:CROSS JOIN,简单的将数据表关联在一起,产生笛卡尔积.
SELECT * FROM emp CROSS JOIN dept ;
自然连接:NATURAL JOIN,内连接,自动找到同名的字段采用内连接消除笛卡尔积.
SELECT * FROM emp NATURAL JOIN dept ;
USING子句:设置要进行关联的字段
SELECT * FROM emp JOIN dept USING(deptno);
ON子句:设置关联条件
SELECT * FROM emp e JOIN dept d ON(e.deptno=d.deptno);
左外连接、右外连接、全外连接(用得少)
SELECT * FROM emp e LEFT OUTER JOIN dept d ON(e.deptno=d.deptno);
SELECT * FROM emp e RIGHT OUTER JOIN dept d ON(e.deptno=d.deptno);
SELECT * FROM emp e FULL OUTER JOIN dept d ON(e.deptno=d.deptno);

18.集合处理

[UNION | UNION ALL | MINUS | INTERSECT]
UNION:将集合结果整合在一起,但相同的部分不显示
SELECT empno,ename,job FROM emp WHERE deptno=10 
UNION SELECT empno,ename,job FROM emp ;
UNION ALL:重复的部分也显示
MINUS:差集运算,大集合减小集合
INTERSECT:交集运算,返回相同部分

19.统计

COUNT():统计表的行数,如果没有数据则返回0,而不是空
COUNT(*)、COUNT(字段)、COUNT(DISTINCT)
如果某一个列上的数据没有null,则COUNT(*)和COUNT(字段)返回的结果一样;
如果有null,则COUNT(字段)不统计空数据.
SELECT COUNT(*),COUNT(mgr) FROM emp;
如果某一列上有重复的数据,统计的时候不想重复,则使用COUNT(DISTINCT)
SELECT COUNT(*),COUNT(DISTINCT job) FROM emp;
SUM()、AVG()、MIN()、MAX()

20.分组查询

根据某字段分完组之后,SELECT子句中只能用一些统计函数
SELECT deptno,COUNT(*),AVG(sal) FROM emp GROUP BY deptno;
# 范例:查询每个部门的名称、人数、平均工资
SELECT d.dname,COUNT(empno),AVG(sal) FROM dept d,emp e
WHERE d.deptno=e.deptno(+) GROUP BY d.dname ;
# 范例:查询雇员的工资等级、编号、工资
SELECT s.grade,e.empno,e.sal
FROM salgrade s,emp e WHERE e.sal BETWEEN s.losal AND s.hisal;
# 范例:在列上有重复时,可以进行分组,查询每个工资等级有多少人,最高工资
SELECT s.grade,COUNT(e.empno),MAX(e.sal) FROM salgrade s,emp e 
WHERE e.sal BETWEEN s.losal AND s.hisal GROUP BY s.grade;
# 范例:查询部门编号、名字、位置、雇员编号、工资
SELECT d.deptno,d.dname,d.loc,e.empno,e.sal
FROM dept d,emp e WHERE d.deptno=e.deptno(+) ;
现在是三列一起重复,可以进行三个列的统一分组操作,
只有GROUP BY中出现的字段才能在SELECT中出现
# 范例:多表查询和分组统计,查询部门编号、名字、位置、人数、平均工资
SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),AVG(e.sal)
FROM dept d,emp e WHERE d.deptno=e.deptno(+)
GROUP BY d.deptno,d.dname,d.loc;

21.HAVING子句

分组后的过滤统计要求在HAVING子句中实现
SELECT job,COUNT(*),AVG(sal)
FROM emp GROUP BY job HAVING AVG(sal)>1200;
HAVING与WHERE区别
WHERE:在GROUP BY分组前使用,对要进行分组的数据进行筛选,不允许使用统计函数
HAVING:在GROUP BY分组后使用,允许使用统计函数,针对分组后的数据进行筛选

⑤SELECT [DISTINCT] 分组字段 | 统计函数
①FROM 数据表1 [别名] ,数据表2 [别名] , ...
②[WHERE 限定条件(s)]
③[GROUP BY 分组字段,分组字段,...]
④[HAVING 分组后过滤]
⑥[ORDER BY 排序字段 [ASC | DESC],排序字段 [ASC | DESC],.....];

22.分组统计案例

# 案例一:查询所有非销售人员的工资总和,并且要求同一工作的雇员的工资总和大于5000,
显示的结果按照工资的总和由高到低排序
SELECT job,SUM(sal) sum
FROM emp WHERE job!='SALESMAN'
GROUP BY job HAVING SUM(sal)>5000 ORDER BY sum DESC;

# 案例二:统计出所有领取佣金和不领取佣金的雇员人数、平均工资
SELECT '领取佣金' info, COUNT(*),AVG(sal) FROM emp WHERE comm IS NOT NULL 
	UNION
SELECT '不领取佣金' info, COUNT(*),AVG(sal) FROM emp WHERE comm IS NULL ;

23.子查询

子查询指的是在一个完整查询语句中嵌入N个内部查询得来的查询结果

常用:子查询返回单行单列、单行多列、多行单列时,一般出现在WHERE子句
范例:查询工资最低的雇员信息(单行单列)
SELECT * FROM emp WHERE sal=(SELECT MIN(sal) FROM emp);
范例:查询和scott职位、工资一样的雇员信息(单行多列)
SELECT * FROM emp
WHERE (job,sal)=(
	SELECT job,sal FROM emp WHERE ename='SCOTT') 
	AND ename<>'SCOTT';
子查询返回多行单列的数据,返回的内容就是一个范围,有三种操作形式:IN、ANY、ALL
SELECT * FROM emp 
WHERE sal IN (SELECT sal FROM emp WHERE job='MANAGER') ;

ANY操作有三种使用形式:
=ANY:与IN操作一样
>ANY:比最小的值大的数据
<ANY:比最大的值小的数据

ALL操作有两种使用形式:
>ALL:比最大的值大的数据
<ALL:比最小的值小的数据

常用:子查询返回多行多列数据(表)可以在FROM子句出现,按照表的方式处理
案例:查询出每个部门的编号、名称、人数、平均工资
第一步:出现了一个多行多列的数据,可以将其当做一个临时表
SELECT deptno,COUNT(empno),AVG(sal) FROM emp GROUP BY deptno;
第二步:dept表与temp临时表进行关联,
SELECT d.deptno,d.dname,d.loc,temp.count,temp.avg
FROM dept d , (
	SELECT deptno dno,COUNT(empno) count,AVG(sal) avg
	FROM emp
	GROUP BY deptno) temp
WHERE d.deptno=temp.dno(+) ;
# FROM子查询的效率比多表查询高,因为参与的数据量少了很多

不常用:子查询返回单行单列并且需要进行统计操作时,一般出现在HAVING子句
范例:查询出所有高于公司平均工资的职位名称、职位人数、平均工资
SELECT job,COUNT(empno),AVG(sal)
FROM emp GROUP BY job
HAVING AVG(sal)>(SELECT AVG(sal) FROM emp) ;

不常用:子查询返回单行单列可以出现在SELECT子句
SELECT e.empno,e.ename,e.job,
	(SELECT d.dname FROM dept d WHERE d.deptno=e.deptno) dname,
	(SELECT s.grade FROM salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal) grade
FROM emp e;

EXISTS:根据子查询是否有数据来判断条件是否成立的运算符
IN与EXISTS的区别
IN需要明确的进行数据的判断,子查询返回的数据需要参与运算;
EXISTS不需要参考具体的返回内容,根据是否有数据返回,来确定条件是否成立.