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;  --

 SELECT * FROM EMP CROSS JOIN DEPT;  --

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

 SELECT E.ENAME, E.EMPNO, D.DNAME

 FEOM EMP E, DEPT D

 WHERE E.DEPTNO = D.DEPTNO;   --

 -- 不需要写E.DEPTNO = D.DEPTNO，会自动寻找同名列

 SELECT E.ENAME, E.EMPNO, D.DNAME

 FEOM EMP E NATURAL JOIN DEPT D;   --

 -- 99中使用WHERE条件

 SELECT E.ENAME, E.EMPNO, D.DNAME

 FEOM EMP E NATURAL JOIN DEPT D

 WHERE DEPTNO = 10;                 --

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

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;

巴特西