oracle初试、函数、增删改查、多表查询

安装oracle后的测试以及解锁账户                 

安装后打开命令行，输入     sqlplus
回车后会提示输入用户名，输入   sys或者system
回车后输入密码，密码为安装oracle时设置的口令

登录后命令行会出现    SQL>>    表示登录成功，接着就可以写SQL命令了
这时登录的是sys用户或者system用户，如果需要使用scott用户需要解锁，解锁命令如下：

解锁scott用户
alter user scott identified by tiger account unlock；

解锁hr用户
alter user  hr  identified by  hr  account unlock；


其中scott / tiger ，hr / hr 是用户名密码。

几个ORACLE常用的命令                              

ORACLE常用命令不需要分号

1. 不使用密码登录oracle
    CONN/AS SYSDBA

2. 给用户设置密码,将sys的密码设置成admin
    ALTER USER SYS IDENTIFIED BY ADMIN

3. 登录后，需要更换登录的用户
    CONN SYS/ADMIN AS SYSDBA
    CONN 用户名/密码

4. 显示当前登录用户
    SHOW USER

5. 账户锁定和解锁
    ALTER USER 用户名 ACCOUNT LOCK; 锁定
    ALTER USER 用户名 ACCOUNT UNLOCK; 解锁

 

函数                                                               

普通函数

首字母大写INITCAP(字段)
    SELECT INITCAP(ENAME) FROM EMP;

转大小写LOWER(),UPPER()
    SELECT LOWER('JOB') FROM EMP;    -- 转小写
    SELECT UPPER("hell o") FROM DUAL;
    SELECT UPPER(ENAME) FROM EMP;   -- 将ENAME列的内容转为大写

去掉左右空格 TRIM()
    SELECT TRIM(' h e ll o ') FROM DUAL;

字符串长度 LENGTH()
    SELECT LENGTH(ENAME) FROM EMP;

    -- 先去掉字段的空格然后计算长度
    -- 知识点：函数可以嵌套调用
    SELECT LENGTH(TRIM(' h e ll o')) FROM EMP;

左剪裁 LTRIM()
    -- 从左边裁掉HE
    -- 第一个参数是要裁剪的字段，第二个参数是要裁剪的内容
    SELECT LTRIM('HELLO', 'HE') FROM DUAL;

右剪裁RTRIM()
    SELECT RTRIM('HELLO', 'HEL') FROM DUAL;
   
替换REPLACE()
    -- 第一个参数是要替换的字段
    -- 第二个参数是被替换的内容
    -- 第三个参数是替换的内容
    SELECT REPLACE('HELLOWORD', 'O', '你好') FROM DUAL;

查找字符串的位置 INSTR()
    -- 要查找的字段， 查找的内容   没有返回0
    SELECT INSTR('HELLOWORD', 'O') FROM DUAL;

字符串截取SUBSTR()
    -- 第一个参数是要截取的字段
    -- 第二个参数是截取的位置
    -- 第三个参数是截取的长度
    
    -- 从1开始截掉两位
    SELECT SUBSTR("HELLOWORD", 2) FROM DUAL;
    -- 从2开始截取4位
    SELECT SUBSTR('HELLOWORD', 2， 4) FROM DUAL;

字符串连接CONCAT()
    SELECT CONCAT('HELLO', 'WWWW') FROM DUAL;
    -- 使用||也可以将这两个字符串连接起来
    SELECT 'HELLO'||'WWWW' FROM DUAL;

数值型函数

绝对值 ABS()
    SELECT ABS(-15) FROM DUAL;

向上取整 CELL()
    SELECT CELL(10.0001) FROM DUAL;

向下取整FLOOR()
    SELECT FLOOR(10.9999) FROM DUAL;

次方次幂 POWER()
    SELECT POWER(2, 3) FROM DUAL;

四舍五入ROUND()
    -- 参数一要操作的字段
    -- 保留的位数
    SELECR ROUND(34.785858, 5) FROM DUAL;

开平方 SQRT()
    SELECT SQRT(9) FROM DUAL;

日期型函数

系统当前时间 SYSDATE
    SELECT SYSDATE FROM DUAL;

两个日期之间的月份差 MONTHS_BETWEEN(当前系统时间， 指定日期)
    SELECT MONTHS_BETWEEN(SYSDATE, '01-1月-18') FROM DUAL;

返回指定月数后的日期 ADD_MONTHS(当前日期， 指定的月份)
    -- 当前时间开始几个月之后的日期
    SELECT ADD_MONTHS(SYSDATE, 2) FROM DUAL; 

返回当前日期的下一周某一天的日期 NEXT_DAY(当前日期， 下一周的星期数)
    SELECT NEXT_DAY(SYSDATE, '星期五') FROM DUAL;

返回指定月份的最后一天 LAST_DAY(SYSDATE)
    SELECT LAST_DAY(SYSDATE) FROM DUAL;

转换函数

将日期转为字符串 TO_CHAR
    SELECT TO_CHAR(SYSDATE, 'yyyy-MM-DD') FROM DAUL;

将数字转为字符串
    -- 9代表一位数字，如果该位没有数字不显示，但是小数点后面的仍会强制显示
    -- 100.1000
    SELECT TO_CHAR(100.10, '9999,9999.9999') FROM DUAL;
    -- 0代表一位数字，没有也会显示
    -- 00000100.1000
    SELECT TO_CHAR(100.10， '0000,0000.0000') FROM DUAL;

将字符串转为日期型 TO_DATE
    SELECT TO_DATE('2018-01-01', 'yyyy-MM-DD') FROM DUAL;

将字符串转为数值型 TO_NUMBER
    -- 前后数字位数要一致
    SELECT TO_NUMBER('1234.99', '9999.99') FROM DUAL;

多行函数

顾名思义，多行函数就是一个函数能够操作多行数据的函数，一般是操作数据表的某一列数值

 -- 对一组数据进行运算，针对一组数据只返回一个结果，也成分组函数，聚合函数

/*
    SUM: 求和   
    AVG(): 求平均值   
    MAX：求最大值   
    MIN: 求最小值   
    COUNT: 求总个数
*/
 
SELECT SUM(SAL) AS 工资总和，
AVG(SAL) AS 平均工资,
MAX(SAL) 最高工资,
MIN(SAL) 最低工资,
COUNT(*) 总人数
FROM EMP;

/*
    SUM:求和  AVG:平均值   适用于数值型

    MAX MIN COUNT适用于任何数据类型
 
    COUNT不会对空值进行计算

*/

其他函数

-- 相当于if..else..

NVL(EXP1, EXP2)  如果exp1的值为null,则返回exp2的值，否则返回exp1
    -- exp1 与 exp2类型要一致
    SELECT EMPON,ENAME,SAL+NVL(COMM, 0) FROM DUAL;

分组

-- group by: 分组， 将表中的数据分成若干小组

/*
    语法

    SELECT 列名， 分组函数(要分的列)

    FROM 表名

    [WHERE 条件]

    [GROUP BY 被分组项]

    [ORDER BY 排序项]
 
*/
-- 统计每个DEPTNO的人数
-- 统计每个部门的人数
    SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO;   

-- 每个DEPTNO的不同JOB分组，计算平均值
-- 每个部门的不同工种分组，计算平均值
　　 SELECT JOB, DEPTNO, AVG(SAL) FROM EMP GROUP BY JOB, DEPTNO;


-- 对分组之后的结果进行条件筛选使用having句子
-- 分组 排序 WHERE HAVING出现的顺序
    SELECT ... FROM ... [WHERE] ... [GROUP BY] ... [HAVING] ... [ORDER BY]

-- 按照不同的部门的不同职位分组
-- 求平均值
-- 将平均值1500的选出来（这里不能用WHERE选，因为WHERE不能在GROUP BY后面出现）
-- 并对平均值排序
    SELECT JOB, DEPTNO, AVG(SAL)
    FROM EMP
    GROUP BY JOB, DEPTNO
    HAVING AVG(SAL) > 1500
    ORDER BY AVG(SAL);

 ORACLE数据库之增删改查                 

增

单行数据操作

-- INSERT INTO 表名(列名1, 列名2....) VALUES (值1, 值2);

-- 一次插入一行（条）数据
    INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES (50, '教育部', '北京');
    
    /*
        注意事项：
          要求数值类型完全一致，结果为受影响的行数
          在oracle中对表中数据的和进行操作后，需要回滚(rollback)或者提交(commit)
    */
    ROLLBACK;  // 执行回滚会回退到上一步
    COMMIT;    // 执行提交会将数据保存到数据库

-- 向表中全部列插入数据， 列名可省略，但要求值的顺序必须和表中列一致
    INSERT INTO DEPT VALUES (60, '城管', '上海');

-- 向表中部分插入数据，要求非空列必须插入值，注意部分列的时候，列名必须写
    INSERT INTO DEPT(DNAME, DEPTNO) VALUES('aaa', 'bbb');

对整个表操作

　　新表不存在

-- 备份一个表
    CREATE TABLE EMP1 
    AS 
    SELECT * FROM EMP; 

-- 只要表结构，不要内容
    CREATE TABLE EMP2
    AS
    SELECT * FROM EMP
    WHERE 1 > 2;
    -- 当条件不成立的时候，只复制表结构

-- 创建表emp3,只需要两个列，列名从EMP表中来
    CREATE TABLE EMP3(编号, 姓名)
    AS
    SELECT EMPTNO, ENAME FROM EMP;

 　　新表存在

-- 新表存在的情况下直接向已经存在的表EMP2中插入数据就可以了

INSERT INTO EMP2 SELECT * FROM EMP;  -- 可以执行多次，数据叠加

删

删分为对表进行删除和对表内数据进行删除

　　对表内数据进行删除

-- DELETE [FROM]  表名...[WHERE];

DELETE EMP2;   -- 无条件的删除


DELETE DEPT
WHERE DEPTNO=50;   -- 带条件的删除

-- 注意 delete后面只能跟表名，不许跟列名，一次删除一整行数据，而不是某一列



-- TRUNCAT

TRUNCAT TABLE EMP;  -- 不能回退的删除表中数据

　　对表进行删除

-- DROP TABLE 表名

DROP TABLE EMP1;

DROP TABLE EMP2;

改

-- UPDATE 表名 set 列名1='值', 列名2='值2'.........[WHERE]

UPDATE DEPT SET LOC='北京';  -- 无条件修改


UPDATE DEPT SET DNAME='教育部', LOC='上海'
WHERE DEPTNO = 50;          -- 有条件的更改

查

-- 在查询结果中使用空值   is null
-- 查询COMM是空的列
    SELECT * FROM TABLE_NAME WHERE COMM IS NULL;
-- 查询COMM不是空的列
    SELECT * FROM TABLE_NAME WHERE COMM IS NOT NULL;

-- 在查询结果中使用常量
    SELECT ENAME, SAL, '潭州' 工作单位 FROM EMP;
    -- 工作单位是列名
    -- '潭州'是值

-- 查询限制行数  rownum叫做伪列
    -- 拿取表中前5条数据
    SELECT * FROM EMP WHERE ROWNUM <= 5;

-- 给表命名   使用空格(省略了AS)
    -- E就是给EMP重新赋值的名字
    SELECT ROWNUM, E.EMPNO FROM EMP E WHERE ROWNUM<=5;

-- 查询工资大于1500的员工的信息，对他们年龄进行排序
    -- 判断条件必须加在order by前面
    SELECT * FROM EMP WHERE SAL>1500 ORDER BY AGE DESC;

-- 使用别名进行排序
    SELECT EMPNO, SAL*12 年薪 FROM EMP ORDER BY 年薪;

/*
    模糊查询，like  between in (查询结果不确定)

        通配符：%   代表任意多个字符，可以是0个可以是多个

            _    下划线代表一个任意字符
*/
    
    -- like通常是和字符型一起使用
        -- 查询以S开头的员工
        SELECT * FROM EMP WHERE ENAME LIKE 'S%';  
      
        -- between ... and代表一个范围，和数值型一起使用(>=and<=)
        --  使用(>= <=)
        SELECT * FROM EMP WHERE SAL >=1500 AND SAL<=3000;
        -- 使用between and，效果同上 
        SELECT * FROM EMP WHERE SAL BETWEEN 1500 AND 3000;
        
        -- 01-1月-81    日-月-年
        SELECT * 
        FROM EMP 
        WHERE DATE BETWEEN '01-1月-81' AND '31-1月-87';   

-- 连接运算符 ||    效果同CONCAT()函数
    SELECT EMPNO || "的员工的姓名是" || ENAME 
    AS 员工 
    FROM EMP;

-- 去掉重复项  Distinct
    SELECT DISTINCT DEPTNO FROM EMP;

 补：模糊查询IN

-- in 在指定的值中进行匹配  相当于或者
    SELECT * FROM EMP WHERE SAL IN(1266, 1500, 3000, 3600);
    -- SAL 将会在1266 1500 3000 3600这四个值中进行匹配

 多表查询                            

  笛卡尔积现象                 

SELECT * FROM EMP;   -- EMP中有4条数据
SELECT * FROM DEPT;  -- DEPT中有14条数据

-- 将EMP和DEPT联合起来查会出现56条数据
    SELECT * FROM EMP, DEPT; -- 56条数据  14*4

-- 这就是笛卡尔积现象

  92标准下的多表查询    

等值查询      

-- 查询名称为SMITH的员工编号，姓名，部门名称
    SELECT EMPNO,ENAME,DNAME
    FROM EMP, DEPT
    WHERE ENAME = 'SMITH'
    AND EMP.DEPTNO = DEPT.DEPTNO;

-- 查询部门编号为10的员工的姓名，员工的工资，部门所在地并对工资排序
    SELECT E.ENAME, E.SAL, D.LOC
    FROM EMP E, DEPT D
    WHERE E.DEPTNO = 10
    AND E.DEPTNO = D.DEPTNO
    ORDER BY E.SAL DESC;

非等值查询  

-- 查询SMITH的工资等级
    SELECT E.EMPNO, E.ENAME, E.SAL, S.LOSAL, S.HISAL
    FROM EMP E, SALGRADE S
    WHERE E.ENAME = 'SMITH'
    AND E.SAL >= S.LOSAL
    AND E.SAL <= S.HISAL;
-- 通过区间查询

-- 等值查询与非等值查询中的两张表是平级关系

外连查询     

左外连接

-- 以'='左边的表为主表  将显示左边表的全部信息（包括等值的，不等值的），哪个表后面跟了(+)谁就是辅助表
    SELECT E.EMONO, E.ENAME, D.DNAME, D.DEPTNO
    FROM EMP E, DEPT D
    WHERE E.DEPTNO(+) = D.DEPTNO;

右外连接

SELECT E.EMONO, E.ENAME, D.DNAME, D.DEPTNO
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO(+);

自连接(一个表当两个表用， 自己连接自己)

SELECT E.EMPNO, E,ENAME, M.ENAME
FROM EMP E, EMP M
WHERE E.MGR = M.EMPNO;

  92标准与99标准                                   

92语法规则缺点   

　　语句的过滤条件和连接条件都放到了where语句中

　　当条件过多时，连接条件多，过滤条件多，就容易造成混淆

sql99标准           

　　修正了整个缺点，把连接条件和过滤条件分开，连接使用on，过滤条件使用where

99语法                

交叉连接(CROSS JOIN)

SELECT * FROM EMP, DEPT;  -- 92
SELECT * FROM EMP CROSS JOIN DEPT;  -- 99

自然连接(NATURAL JOIN)(类似等值连接)

SELECT E.ENAME, E.EMPNO, D.DNAME
FEOM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;   -- 92
  
-- 不需要写E.DEPTNO = D.DEPTNO，会自动寻找同名列
SELECT E.ENAME, E.EMPNO, D.DNAME
FEOM EMP E NATURAL JOIN DEPT D;   -- 99


-- 99中使用WHERE条件
SELECT E.ENAME, E.EMPNO, D.DNAME
FEOM EMP E NATURAL JOIN DEPT D   
WHERE DEPTNO = 10;                 -- 99

当两个表中出现多个同名列后，自然连接无法满足要求，因为它不知道究竟应该让两个表的哪一个同名列进行关联，于是可以用一下方式解决

  using子句：当相连的表中出现很多同名列，自然连接无法满足要求，可以在连接时使用using子句来设置用于等值连接名  

SELECT E.ENAME, E.EMPNO, D.DNAME
FEOM EMP E NATURAL JOIN DEPT D
USING(DEPTNO)    -- 指定要使用哪一个同名列进行连接
WHERE DEPTNO = 10;

  内连接INNER JOIN配合on子句使用(INNER可以省略)  

-- 查询部门编号为10的员工编号，姓名，部门名称
SELECT E.EMPNO, E.ENAME, D.DNAME
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO   -- 做连接条件
WHERE D.DEPTNO = 10;

使用内连接还可以进行多表连接

-- 三表连接
    SELECT E.ENAME, D.DNAME, C.CNAME
    FROM EMP E 
    INNER JOIN DMP D               -- 连接第一、第二张表
    ON E.ENPTNO = D.ENPTNO   -- 连接条件
    INNER JOIN CMP C               -- 继续连接第三张表
    ON D.DEPTNO = C.CEPTNO   -- 连接条件
    WHERE E.NAME = 'ALEX';

  外连接，也需要配合ON子句使用                                        

左外连接  LEFT JOIN  以左边的表为准

SELECT E.EMPNO, E.ENAME, D.DNAME, D.DEPTNO
FROM EMP E LEFT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;

右外连接  以右边的表为准

SELECT E.EMPNO, E.ENAME, D.DNAME, D.DEPTNO
FROM EMP E RIGHT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;

全外连接  不分主次，会将两个表中的数据全部拿出来

SELECT E.EMPNO, E.ENAME, D.DNAME, D.DEPTNO
FROM EMP E FULL JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;