SQL(Oracle)学习20170916

一.准备工作


1.oracle四大用户密码
sys/change_on_install
system/manager
scott/tiger
sh/sh






2.问题
oracle用SQL Plus输入命令为什么只显示2
错误原因：
一般是刚接触Oracle时遇到的问题。
可能是使用sql server的习惯，sql server中句末可以不用输入分号就可以执行。
解决方法：而使用Oracle的SQL Plus或者是在命令行下使用时，如果没有在语句的后面加一个分号，就按回车键后，是不会执行的，而是光标跳到下一行，2表示行号，可以继续输入 语句，直到输入分号后按回车才执行：






3.初始化数据
由于Oracle 12C的CDB与PDB问题，所以在书上使用的是一套自定义的c##scott用户，此脚本程序如下。
    PS：即使没有此脚本，书上与视频中均已经给出了如何进行CDB与PDB切换方法，也不影响学习，所以在此特别希望所有读者一定要认真读书或者仔细看视频，不要只出现一点停顿就开始着急，切记，耐心是学习的最大成功因素。


-- 使用超级管理员登录
CONN sys/change_on_install AS SYSDBA ;


-- 创建c##scott用户
CREATE USER c##scott IDENTIFIED BY tiger ;


-- 为用户授权
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO c##scott CONTAINER=ALL ;


-- 设置用户使用的表空间
ALTER USER c##scott DEFAULT TABLESPACE USERS;
ALTER USER c##scott TEMPORARY TABLESPACE TEMP;


-- 使用c##scott用户登录
CONNECT c##scott/tiger


-- 删除数据表
DROP TABLE emp  PURGE ;
DROP TABLE dept PURGE ;
DROP TABLE bonus PURGE ;
DROP TABLE salgrade PURGE ;


-- 创建数据表
CREATE TABLE dept (
deptno NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
dname VARCHAR2(14) ,
loc VARCHAR2(13) ) ;
CREATE TABLE emp (
empno NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT );
CREATE TABLE bonus (
enamE VARCHAR2(10) ,
job VARCHAR2(9)  ,
sal NUMBER,
comm NUMBER ) ;
CREATE TABLE salgrade ( 
grade NUMBER,
losal NUMBER,
hisal NUMBER );


-- 插入测试数据 —— dept
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
-- 插入测试数据 —— emp
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('19-04-1987','dd-mm-yyyy')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('23-05-1987','dd-mm-yyyy')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
-- 插入测试数据 —— salgrade
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);


-- 事务提交
COMMIT;






4.常用命令
--格式化命令
set linesize 300;
set pagesize 30;


--调用记事本
ed midn
@midn


--登陆
conn sys/change_on_install as SYSDBA;
show user;
select * from c##scott.emp;


--连接普通用户
conn c##scott/tiger;
select * from emp;


--格式化
select * from tab;
COL TNAME FOR A20;
COL TABTYPE FOR A20;
COL CLUSTERID FOR A20;
SELECT * FROM tab;


--表结构，建文件夹
DESC emp;
HOST dir;






5.重点：
oracle数据库四个主要用户：
1.超级管理员：sys/change_on_install
普通管理员：system/manager
普通用户：scoot/tiger(Oracle 12c之后改为c##scott)
海量数据用户（大数据用户）：sh/sh


Oracle安装中最重要的两个服务是监听和数据库实例服务:
监听服务：OracleOraDB12Home1TNSListener
数据库实服务：OracleServiceMIDN












二.基础命令和SQL


1.简单查询：主要特征就是查询表中的所有行，利用select字句控制列输出
字符串、列数据的连接:||
SELECT empno||ename FROM emp;
SELECT '编号是：'||empno||'的雇员姓名是:'||ename||',基本工资是'||sal 雇员信息 FROM emp;






2.限定查询：限定查询主要的运算符包含BETWEEN..ADN、IN、IS NULL、LIKE
Oracle是区分大小写的
查询名字第二位是M的雇员信息（下划线代表任意字符）
SELECT * FROM emp WHERE ename LIKE '_M%'
查询雇员姓名超过6个字符的雇员
SELECT * FROM emp WHERE ename LIKE '______%'


ORDER BY
执行顺序：FROM、WHERE、SELECT、ORDER BY，所以ORDER BY可以使用SELECT中的别名






3.函数
（1）字符函数:UPPER\LOWER
SELECT UPPER('asdasd') FROM DUAL;
SELECT LOWER('ASDASD') FROM DUAL;


（2）字符函数：INITCAP
SELECT ename 原始姓名,INITCAP(ename) 首字母大写 FROM emp;


（3）字符函数：REPLACE
SELECT ename 原始姓名,REPLACE(ename,'A','_') FROM emp;


（4）字符函数：LENGTH
SELECT * FROM emp WHERE LENGTH(ename)=5;


（5）字符函数：SUBSTR(截取开始点0和1是一样的)
从指定位置截取到结尾：SUBSTR(列|字符串,截取开始点)
截取部分的字符串:SUBSTR(列|字符串,截取开始点,截取个数)
SELECT * FROM emp WHERE SUBSTR(ename,0,3)='JAM';
SELECT ename 原始姓名,SUBSTR(ename,3) 截取后 FROM emp WHERE deptno=10;
SELECT ename 原始姓名,SUBSTR(ename,-3) 截取后 FROM emp WHERE deptno=10;
SELECT ename 原始姓名,SUBSTR(ename,LENGTH(ename)-2) 截取后 FROM emp WHERE deptno=10;


面试题：Oracle函数substr下标从0开始还是从1开始
回答：可以设为0也可以设为1，即使使用了0，最终结果
也会将其定义为1。


（6）字符函数：ASCII/CHR
SELECT ASCII('A'),ASCII('L') FROM dual;
SELECT CHR('100') FROM dual;


（7）字符函数：LTRIM/RTRIM/TRIM
SELECT 
'  SADA  ASD lasd  ' 原字符串,LTRIM('  SADA  ASD lasd  ') 去掉左边空格,
'  SADA  ASD lasd  ' 原字符串,RTRIM('  SADA  ASD lasd  ') 去掉右边空格,
'  SADA  ASD lasd  ' 原字符串,TRIM('  SADA  ASD lasd  ') 去掉左右边空格
FROM dual;


（8）字符函数：LPAD/RPAD
SELECT
LPAD('MLID',10，'*') 字符串左填充,
RPAD('MLID',10，'*') 字符串右填充,
LPAD(RPAD('MLID',10,'*'),16,'*') 左右填充
FROM dual;


（9）字符函数：INSTR
如果查找到要找的内容，那么此函数返回位置，找不到返回0
SELECT
INSTR('ABCDEF','AB') 找的到,
INSTR('ABCDEF','F') 找的到,
INSTR('ABCDEF','G') 找不到
FROM dual;


（10）数值函数：ROUND/TRUNC
小数进位
SELECT 
ROUND(789.458) 不保留小数,
ROUND(789.458,2) 保留2位小数,
ROUND(789.458,-1) 处理整数进位 
FROM dual;


截取小数，不进位
SELECT
TRUNC(789.458) 截取小数,
TRUNC(789.458,2) 截取2位小数,
TRUNC(789.458,-2) 取整
FROM dual;


（11）数值函数：MOD
取模(余数)
SELECT
MOD(10，3)
FROM dual;




（12）日期函数：
当前日期:直接利用SYSDATE伪列取得当前日期时间，所谓伪列就是不是表中的列但是可以使用的列
SELECT SYSDATE FROM dual;


SELECT
SYSDATE + 3 三天之后的日期,
SYSDATE - 3 三天前的日期
FROM dual;


SELECT
empno 雇员编号,
ename 雇员姓名,
TRUNC(SYSDATE - hiredate) 雇佣天数
FROM EMP;




ADD_MONTHS:
SELECT
SYSDATE,
ADD_MONTHS(SYSDATE , 3) 三个月之后的日期,
ADD_MONTHS(SYSDATE , -3) 三个月之前的日期,
ADD_MONTHS(SYSDATE , 60) 六十个月之后的日期
FROM dual;


NEXT_DAY:
SELECT
SYSDATE,
NEXT_DAY(SYSDATE , '星期日') 下一个星期日,
NEXT_DAY(SYSDATE , '星期一') 下一个星期一
FROM dual;


LAST_DAY:日期所在月份的最后一天
SELECT
SYSDATE,
LAST_DAY(SYSDATE)
FROM dual;


EXTRACT:
SELECT
EXTRACT(YEAR FROM DATE '2001-1-19') years,
EXTRACT(MONTH FROM DATE '2001-1-19') months,
EXTRACT(DAY FROM DATE '2001-1-19') days
FROM dual;


SELECT
SYSTIMESTAMP,
EXTRACT(YEAR FROM SYSTIMESTAMP) years,
EXTRACT(MONTH FROM SYSTIMESTAMP) months,
EXTRACT(DAY FROM SYSTIMESTAMP) days,
EXTRACT(HOUR FROM SYSTIMESTAMP) hours,
EXTRACT(MINUTE FROM SYSTIMESTAMP) minutes,
EXTRACT(SECOND FROM SYSTIMESTAMP) seconds
FROM dual;




（13）转换函数：
TO_CHAR
SELECT
SYSDATE,
TO_CHAR(SYSDATE , 'YYYY-MM-DD'),
TO_CHAR(SYSDATE , 'YYYY-MM-DD HH24:MI:SS'),
TO_CHAR(SYSDATE , 'FMYYYY-MM-DD HH24:MI:SS'),
TO_CHAR(SYSDATE , 'MM')
FROM dual;


TO_CHAR还可以将数字格式化
SELECT
TO_CHAR(987654321,'999,999,999.99999'),
TO_CHAR(987654321,'999999,999.99999')
FROM dual;




TO_DATE
SELECT
TO_DATE('1990-05-22','YYYY-MM-DD')
FROM dual;


--时间戳
SELECT
TO_TIMESTAMP('1990-05-22 15:25:45','YYYY-MM-DD HH24:MI:SS')
FROM dual;




(14)通用函数
NVL函数：将空置为0
SELECT
NVL(null,0),NVL(3,0)
FROM dual;


DECODE函数：相当于if...else
SELECT
DECODE(2,1,'内容为1',2,'内容为2' ,'无条件满足'),
DECODE(2,1,'内容为1' ,'无条件满足')
FROM DUAL;


SELECT
ename,sal,
DECODE(job,'CLERK','业务员',
'SALESMAN','销售人员',
'MANAGER','经理',
'ANALYST','分析员',
'PRESIDENT','经理'
) FROM emp;




4.多表查询
会产生笛卡尔积，性能低