2018-12-18

作者: twoapes | 来源:发表于2019-01-05 21:47 被阅读9次

ORACLE视频学习之SQL篇

视频观看地址
抖音号:huyingzhao

scott用户对应表字段备注

-- Add comments to the table 
comment on table SCOTT.EMP
  is '雇员表';
-- Add comments to the columns 
comment on column SCOTT.EMP.empno
  is '部门编号';
comment on column SCOTT.EMP.ename
  is '雇员姓名';
comment on column SCOTT.EMP.job
  is '雇员职位';
comment on column SCOTT.EMP.mgr
  is '雇员的经理';
comment on column SCOTT.EMP.hiredate
  is '雇员的雇佣日期';
comment on column SCOTT.EMP.sal
  is '雇员基本工资';
comment on column SCOTT.EMP.comm
  is '雇员的奖金';
comment on column SCOTT.EMP.deptno
  is '雇员所在部门的编号';
    
-- Add comments to the table 
comment on table SCOTT.DEPT
  is '部门表';
-- Add comments to the columns 
comment on column SCOTT.DEPT.DEPTNO
  is '部门编号';
comment on column SCOTT.DEPT.DNAME
  is '部门名称';
comment on column SCOTT.DEPT.LOC
  is '部门所在位置';  
    
-- Add comments to the table 
comment on table SCOTT.BONUS
  is '工资表';
-- Add comments to the columns 
comment on column SCOTT.BONUS.ENAME
  is '雇员姓名';
comment on column SCOTT.BONUS.SAL
  is '雇员基本工资';
comment on column SCOTT.BONUS.JOB
  is '雇员职位';
comment on column SCOTT.BONUS.COMM
  is '雇员的奖金';
    
    
-- Add comments to the table 
comment on table SCOTT.SALGRADE
is '工资等级表';
-- Add comments to the columns 
comment on column SCOTT.SALGRADE.GRADE
is '工资的等级';
comment on column SCOTT.SALGRADE.LOSAL
is '此等级的最低工资';
comment on column SCOTT.SALGRADE.HISAL
is '此等级的最高工资';

一次性向多个表插入记录

--一次性向多个表插入记录
SELECT s.dname, s.loc FROM scott.dept s WHERE s.deptno IN (10, 20, 30);
CREATE TABLE dept10 AS
  SELECT s.dname, s.loc FROM scott.dept s WHERE 1 = 0;
CREATE TABLE dept20 AS
  SELECT s.dname, s.loc FROM scott.dept s WHERE 1 = 0;
CREATE TABLE dept30 AS
  SELECT s.dname, s.loc FROM scott.dept s WHERE 1 = 0;
INSERT ALL WHEN deptno = 10 THEN INTO dept10
  (dname, loc)
VALUES
  (dname, loc) WHEN deptno = 20 THEN INTO dept20
  (dname, loc)
VALUES
  (dname, loc) WHEN deptno = 30 THEN INTO dept30
  (dname, loc)
VALUES
  (dname, loc)
  SELECT deptno, dname, loc FROM scott.dept;
COMMIT;
SELECT * FROM dept10; --ACCOUNTING  NEW YORK
SELECT * FROM dept20; --RESEARCH    DALLAS
SELECT * FROM dept30; --SALES   CHICAGO

--drop
drop table dept10;
drop table dept20;
drop table dept30;

两个表中查找共同行

--两个表中查找共同行
SELECT * FROM scott.emp s WHERE s.deptno = 10;
SELECT * FROM scott.emp o WHERE o.ename = 'KING';
--使用INTERSECT取交集
SELECT *
  FROM scott.emp s
 WHERE s.deptno = 10
INTERSECT
SELECT *
  FROM scott.emp s
 WHERE s.ename = 'KING';

为列取有意义的名字

--为列取有意义的名字
SELECT s.empno, s.ename, s.job, s.mgr, s.hiredate, s.sal, s.comm, s.deptno
  FROM scott.emp s
 WHERE s.deptno = 10;
--通过直接字段+空格(或AS)+要取的名字(不能有特殊符号,例如花括号,小括号,表达式等)
SELECT s.empno    部门编号,
       s.ename    雇员姓名,
       s.job      雇员职位,
       s.mgr      雇员的经理,
       s.hiredate AS 雇员的雇佣日期,
       s.sal      AS 雇员基本工资,
       s.comm     AS 雇员的奖金,
       s.deptno   AS 雇员所在部门的编号
  FROM scott.emp s
 WHERE s.deptno = 10;
--通过直接字段+空格(或AS)+要取的名字(需要含有特殊符号,例如花括号,小括号,表达式等)
SELECT 
       --s.empno    AS "此"部门"编号", 双引号内不能包含双引号
       s.ename    AS 雇员姓名,
       s.job      AS 雇员职位,
       s.mgr      AS 雇员的经理,
       s.hiredate AS "雇员的雇佣日期(yyyy-mm-dd)",
       s.sal      AS 雇员基本工资,
       s.comm     AS "雇员的奖金{没有奖金就是空值!}",
       s.deptno   AS 雇员所在部门的编号
  FROM scott.emp s
 WHERE s.deptno = 10;
----注意:如果区别名为中文名,注意获取sql时对应的字符集,防止乱码,或尽量不使用中文和特殊符号

从一个表中向另一个表中复制行

--从一个表中向另一个表中复制行
SELECT * FROM scott.dept;
--假设以下为复制的数据
SELECT '50', s.dname, s.loc FROM scott.dept s WHERE s.deptno = '40';
INSERT INTO scott.dept
  SELECT '50', s.dname, s.loc FROM scott.dept s WHERE s.deptno = '40';
COMMIT;
SELECT s.deptno, s.dname, s.loc FROM scott.dept s WHERE s.deptno = '50';


--不影响scott用户脚本
DELETE FROM scott.dept s WHERE s.deptno = '50';
COMMIT;

从一个表中查找另一个表中没有的值

--从一个表中查找另一个表中没有的值
SELECT * FROM scott.emp s WHERE s.deptno = 10;
SELECT * FROM scott.emp o WHERE o.ename = 'KING';
--使用MINUS取差集
SELECT *
  FROM scott.emp s
 WHERE s.deptno = 10
MINUS
SELECT *
  FROM scott.emp o
 WHERE o.ename = 'KING';
--使用NOT EXISTS取另一个表中没有的值
SELECT *
  FROM scott.emp s
 WHERE s.deptno = 10
   AND NOT EXISTS (SELECT 1
          FROM scott.emp o
         WHERE o.ename = 'KING'
           AND s.empno = o.empno)

从字符串中删除不需要的字符

--从字符串中删除不需要的字符
SELECT * FROM scott.dept s WHERE s.deptno = 10;
SELECT s.dname FROM scott.dept s WHERE s.deptno = 10;
SELECT REPLACE(s.dname, 'CC', '') AS remove_dname
  FROM scott.dept s
 WHERE s.deptno = 10;

从表中删除所有记录

--从表中删除所有记录
select * from scott.emp;
delete scott.emp;
select * from scott.emp;
rollback;


--还原操作
rollback;

从表中检索部分列

--从表中检索部分列
select s.empno,s.ename,s.job from scott.emp s;
select s.empno,s.ename from scott.emp s;
select s.empno,s.ename,s.deptno from scott.emp s;

从表中检索部分行

--从表中检索部分行
--工资大于1600的员工
SELECT s.sal, s.* FROM scott.emp s WHERE s.sal > 1600;
--工资小于1600的员工
SELECT s.sal, s.* FROM scott.emp s WHERE s.sal < 1600;
--工资在800到1600之间的员工
SELECT s.sal, s.* FROM scott.emp s WHERE s.sal BETWEEN 800 AND 1600;
--工资等于1600的员工
SELECT s.sal, s.* FROM scott.emp s WHERE s.sal = 1600;
--工资不等于1600的员工
SELECT s.sal, s.* FROM scott.emp s WHERE s.sal <> 1600;
SELECT s.sal, s.* FROM scott.emp s WHERE s.sal ^= 1600;
SELECT s.sal, s.* FROM scott.emp s WHERE s.sal != 1600;

从表中选取所有的行和列

--从表中选取所有的行和列
select * from scott.emp;

以指定的次序返回查询结果

--以指定的次序返回查询结果
SELECT * FROM scott.emp s WHERE s.empno > 7499 ORDER BY s.empno ASC; --顺序
SELECT * FROM scott.emp s WHERE s.empno > 7499 ORDER BY s.empno DESC; --倒序
SELECT * FROM scott.emp s WHERE s.empno > 7499 ORDER BY s.ename ASC; --默认,如果是字符串,英文按照a到z,A-Z排序,同理中文按照拼音的字母排序,特殊符号按ACCII码排序

--汉字排序
--按照笔划排序
SELECT *
  FROM scott.emp s
 ORDER BY nlssort(s.ename, 'NLS_SORT=SCHINESE_STROKE_M');
--按照部首排序
SELECT *
  FROM scott.emp s
 ORDER BY nlssort(s.ename, 'NLS_SORT=SCHINESE_RADICAL_M');
--按照拼音排序，此为系统的默认排序方式
SELECT *
  FROM scott.emp s
 ORDER BY nlssort(s.ename, 'NLS_SORT=SCHINESE_PINYIN_M');

使用NULL代替默认值

--使用NULL代替默认值
--事实上有些字段如果需要默认值为NULL,其实可以直接插入进去
INSERT INTO scott.dept VALUES ('50', '行政部门', NULL);
SELECT * FROM scott.dept s WHERE s.loc IS NULL;
ROLLBACK;

使用SQL来生成SQL

--使用SQL来生成SQL
SELECT ('select count(1) from ' || s.table_name || ';') AS sql_table
  FROM user_tables s;


select count(1) from HYZ_DATABASE_DESIGN;
select count(1) from HYZ_DICTIONARY_USER;
select count(1) from HYZ_LOG;
select count(1) from HYZ_PATH;
select count(1) from HYZ_USER_LOGIN;
select count(1) from HYZ_COPY;

列出模式中的表

--列出模式中的表
SELECT * FROM all_tables s WHERE s.owner = 'SYSTEM'; --需要管理员权限
SELECT * FROM all_tables s WHERE s.owner = 'HUYINGZHAO'; --需要管理员权限
SELECT * FROM all_tables s WHERE s.owner = 'SCOTT'; --需要管理员权限

列出表中的列

--列出表中的列
SELECT * FROM all_tab_cols s WHERE s.owner = 'SYSTEM'; --需要管理员权限
SELECT * FROM all_tab_cols s WHERE s.owner = 'HUYINGZHAO'; --需要管理员权限
SELECT *
  FROM all_tab_cols s
 WHERE s.owner = 'HUYINGZHAO'
   AND s.table_name = 'HYZ_LOG'; --需要管理员权限 HYZ_LOG

列出表的索引列

--列出表的索引列
SELECT * FROM all_ind_columns s; --需要管理员权限
SELECT * FROM all_ind_columns s WHERE s.index_owner = 'HUYINGZHAO'; --需要管理员权限
SELECT * FROM all_ind_columns s WHERE s.index_owner = 'SCOTT'; --需要管理员权限

列出表约束

--列出表约束
SELECT * FROM all_constraints s; --需要管理员权限
SELECT * FROM all_constraints s WHERE s.index_owner = 'HUYINGZHAO'; --需要管理员权限
SELECT * FROM all_constraints s WHERE s.index_owner = 'SCOTT'; --需要管理员权限

删除单个记录

--删除单个记录
SELECT * FROM scott.emp s WHERE s.empno = 7782;
--通过条件为唯一约束或主键确定即可查询出单条数据,此时就可放心执行操作
DELETE scott.emp s WHERE s.deptno = 7782;
SELECT * FROM scott.emp s WHERE s.deptno = 7782;

--还原记录
rollback;

删除指定记录

--删除指定记录
SELECT * FROM scott.emp s WHERE s.deptno = 10;
DELETE scott.emp s WHERE s.deptno = 10;
SELECT * FROM scott.emp s WHERE s.deptno = 10;

--还原记录
rollback;

删除违反参照完整性的记录

--删除违反参照完整性的记录
SELECT *
  FROM scott.emp s
 WHERE NOT EXISTS (SELECT 1 FROM scott.dept o WHERE s.deptno = o.deptno);
DELETE scott.emp s
 WHERE NOT EXISTS (SELECT 1 FROM scott.dept o WHERE s.deptno = o.deptno);
DELETE scott.emp s
 WHERE s.deptno NOT IN
       (SELECT o.deptno FROM scott.dept o WHERE s.deptno = o.deptno);
       
       
--还原记录
rollback;

删除重复数据

--删除重复数据
CREATE TABLE TMP( ID NUMBER, V VARCHAR2(20));
insert into TMP(ID,V) values(1,'x');
insert into TMP(ID,V) values(2,'x');
insert into TMP(ID,V) values(3,'y');
insert into TMP(ID,V) values(4,'y');
insert into TMP(ID,V) values(5,'y');
insert into TMP(ID,V) values(6,'z');
insert into TMP(ID,V) values(7,'z');
insert into TMP(ID,V) values(8,'z');
insert into TMP(ID,V) values(9,'z');
insert into TMP(ID,V) values(9,'yz');
commit;
select * from TMP;
SELECT MIN(s.id),s.v FROM tmp s GROUP BY s.v;
delete tmp a where a.id in (SELECT MIN(s.id) FROM tmp s GROUP BY s.v);
commit;
select * from TMP;

--还原
drop table TMP;

合并记录

--合并记录
SELECT s.empno, s.ename, s.job, s.mgr, s.hiredate, s.sal, s.comm, s.deptno
  FROM scott.emp s
 ORDER BY s.deptno;
CREATE TABLE emp_commission AS
  SELECT s.deptno, s.empno, s.ename, s.comm
    FROM scott.emp s
   WHERE s.ename IN ('CLARK', 'KING', 'MILLER');
SELECT * FROM emp_commission;
MERGE INTO emp_commission ec
USING (SELECT s.empno,
              s.ename,
              s.job,
              s.mgr,
              s.hiredate,
              s.sal,
              s.comm,
              s.deptno
         FROM scott.emp s) emp
ON (ec.empno = emp.empno)
--MATCHED返回表emp_commission含有scott.emp的记录
WHEN MATCHED THEN
  UPDATE SET ec.comm = 1000 DELETE WHERE sal < 2000
--NOT MATCHED返回表emp_commission不含有scott.emp的记录
WHEN NOT MATCHED THEN
  INSERT
    (ec.empno, ec.ename, ec.deptno, ec.comm)
  VALUES
    (emp.empno, emp.ename, emp.deptno, emp.comm);
COMMIT;
SELECT * FROM emp_commission;

--还原操作
DROP TABLE emp_commission;

向查询中增加联接而不影响其他联接

--向查询中增加联接而不影响其他联接
SELECT * FROM scott.emp;
SELECT * FROM scott.dept;
SELECT * FROM scott.salgrade;
SELECT s.deptno,
       s.dname,
       s.loc,
       's <--> o',
       o.empno,
       o.ename,
       o.job,
       o.mgr,
       o.hiredate,
       o.sal,
       o.comm,
       o.deptno,
       'o <--> t',
       t.grade
  FROM scott.dept s
  JOIN scott.emp o
    ON s.deptno = o.deptno
  JOIN scott.salgrade t --只需要根据工资高低就可以定义等级了,不含奖金
    ON o.sal BETWEEN t.losal AND t.hisal

在ORACLE中描述数据字典视图

--在ORACLE中描述数据字典视图
--dictionary用于查询视图或表的说明
select * from dictionary;
select * from dictionary s where s.TABLE_NAME='USER_TABLES';

--dict_columns用于查询视图或表的对应列说明
select * from dict_columns;
select * from dict_columns s where s.TABLE_NAME='USER_TABLES';

在SLEECT语句中使用条件逻辑

--在SLEECT语句中使用条件逻辑
SELECT
--部门编号在7369到7499返回0,否则1
 (CASE
   WHEN s.empno BETWEEN 7369 AND 7499 THEN
    '0'
   ELSE
    '1'
 END) AS case_empno,
 --部门编号是7369返回0,否则1
 (CASE s.empno
   WHEN 7369 THEN
    '0'
   ELSE
    '1'
 END) AS case_empno2,
 --部门编号是7369返回0,否则1
 decode(s.empno, 7369, 0, 1) AS case_empno2,
 s.*
  FROM scott.emp s
 WHERE s.deptno = 20
    OR s.deptno = 30;

在where子句中引用取别名的列

--在where子句中引用取别名的列
SELECT *
  FROM (SELECT s.empno,
               s.ename,
               s.job,
               s.mgr,
               s.hiredate,
               s.sal,
               s.comm,
               s.deptno AS 雇员所在部门的编号
          FROM scott.emp s
         WHERE s.deptno >= 10)
 WHERE 雇员所在部门的编号 = 10;

在一个表中查找与其他表不匹配的记录

--在一个表中查找与其他表不匹配的记录
SELECT * FROM scott.emp;
SELECT * FROM scott.dept;
--查找scott.emp在scott.dept表中不存在的记录,明显通过上面的语句可以看出,雇员表没有部门编号为40的成员
SELECT s.deptno,
       s.dname,
       s.loc,
       's <--> o',
       o.empno,
       o.ename,
       o.job,
       o.mgr,
       o.hiredate,
       o.sal,
       o.comm,
       o.deptno
  FROM scott.dept s
  JOIN scott.emp o
    ON s.deptno = o.deptno(+)
--WHERE o.deptno IS NOT NULL;

在表中编辑记录

--在表中编辑记录
SELECT s.deptno, s.dname, s.loc
  FROM scott.dept s
 WHERE s.deptno IN (10, 20);
UPDATE scott.dept s SET s.dname = 'RESEARCH' WHERE s.deptno = 10;
UPDATE scott.dept s SET s.dname = 'ACCOUNTING' WHERE s.deptno = 20;
COMMIT;
SELECT s.deptno, s.dname, s.loc
  FROM scott.dept s
 WHERE s.deptno IN (10, 20);
 
--还原值
UPDATE scott.dept s SET s.dname = 'ACCOUNTING' WHERE s.deptno = 10;
UPDATE scott.dept s SET s.dname = 'RESEARCH' WHERE s.deptno = 20;
COMMIT;

在表中随机返回N条记录

--在表中随机返回N条记录
SELECT s.ename, s.job FROM scott.emp s ORDER BY dbms_random.value();
SELECT *
  FROM (SELECT s.ename, s.job FROM scott.emp s ORDER BY dbms_random.value())
 WHERE rownum <= 5;

在运算和比较时,使用NULL

--在运算和比较时,使用NULL
SELECT * FROM scott.emp s;
SELECT * FROM scott.emp s WHERE s.ename = 'WARD';
SELECT *
  FROM scott.emp s
 WHERE coalesce(s.comm, 0) <=
       (SELECT coalesce(o.comm, 0) FROM scott.emp o WHERE o.ename = 'WARD');
SELECT *
  FROM scott.emp s
 WHERE nvl(s.comm, 0) <=
       (SELECT nvl(o.comm, 0) FROM scott.emp o WHERE o.ename = 'WARD');

处理排序空值

--处理排序空值
SELECT * FROM scott.emp s ORDER BY s.comm;
SELECT * FROM scott.emp s ORDER BY s.comm DESC;
SELECT s.empno,
       s.ename,
       s.job,
       s.mgr,
       s.hiredate,
       s.sal,
       s.comm,
       s.deptno,
       --通过标志0,1或NULL,NOTNULL返回不同的结果,区别是否为NULL,既不影响效率也不影响查询结果顺序
       decode(s.comm, NULL, 'NULL', 'NOTNULL') AS comm_is_null
  FROM scott.emp s;

复制表定义

--复制表定义
SELECT * FROM user_tables s WHERE s.table_name = 'DEPT'; --查询当前用户表,以确定当前用户没有此表
SELECT * FROM scott.dept;
--如果没有同名表,可以直接创建了
CREATE TABLE dept AS
  SELECT s.deptno, s.dname, s.loc FROM scott.dept s;
SELECT * FROM user_tables s WHERE s.table_name = 'DEPT'; --查询当前用户表,以确定当前用户已创建此表
SELECT * FROM dept;
drop TABLE dept;
--如果只是创建另一个表相同的表结构,而不包含数据,可以用以下语句
CREATE TABLE dept AS
  SELECT s.deptno, s.dname, s.loc FROM scott.dept s WHERE 1 = 0;
SELECT * FROM dept;
drop TABLE dept;

字符串文字中包含引号

--字符串文字中包含引号
--dual是一张虚表,它在数据库中创建用户后就会存在,因此无需创建
--以下四个字段都是实现字符串中含有分号方法
SELECT q'{'123,321'}' AS test_1,q'['123,321']' AS test_2,q'('123,321')' AS test_3,'''123,321''' AS test_4 FROM dual;

对字母数字混合的数据排序

--对字母数字混合的数据排序
SELECT s.ename, s.deptno, s.ename || ' ' || s.deptno AS ename_deptno
  FROM scott.emp s;
--以下语句可以理解把上面的查询当成一张V表来使用
WITH v AS
 (SELECT s.ename, s.deptno, s.ename || ' ' || s.deptno AS ename_deptno
    FROM scott.emp s)
SELECT * FROM v;

--使用一般表达式对V表的ename_deptno的数字排序
WITH v AS
 (SELECT s.ename, s.deptno, s.ename || ' ' || s.deptno AS ename_deptno
    FROM scott.emp s)
SELECT s.ename, s.deptno, ename_deptno,REPLACE(ename_deptno,REPLACE(translate(ename_deptno, '0123456789', '########'),'#',''),'') AS deptno_num
  FROM v s
 ORDER BY deptno_num;
 
--使用一般表达式对V表的ename_deptno的字符串排序
WITH v AS
 (SELECT s.ename, s.deptno, s.ename || ' ' || s.deptno AS ename_deptno
    FROM scott.emp s)
SELECT s.ename, s.deptno, ename_deptno,REPLACE(translate(ename_deptno, '0123456789', '########'),'#','') AS ename_str
  FROM v s
 ORDER BY ename_str;
 
 --使用正则表达式对V表的ename_deptno字段的数字排序
WITH v AS
 (SELECT s.ename, s.deptno, s.ename || ' ' || s.deptno AS ename_deptno
    FROM scott.emp s)
SELECT s.ename, s.deptno, ename_deptno,regexp_substr(s.ename_deptno,'[0-9]+') AS deptno_num
  FROM v s
 ORDER BY deptno_num;
--使用正则表达式对V表的ename_deptno字段的字符串排序
WITH v AS
 (SELECT s.ename, s.deptno, s.ename || ' ' || s.deptno AS ename_deptno
    FROM scott.emp s)
SELECT s.ename, s.deptno, ename_deptno,regexp_substr(s.ename_deptno,'[a-z A-Z]+') AS ename_str
  FROM v s
 ORDER BY ename_str;

将空值转换为实际值

--将空值转换为实际值
SELECT s.empno,
       s.ename,
       s.job,
       s.mgr,
       s.hiredate,
       s.sal,
       s.deptno,
       s.comm,
       --使用coalesce函数
       coalesce(s.comm, 0) AS coalesce_comm,
       --使用nvl函数
       nvl(s.comm, 0) AS nvl_comm,
       --使用decode
       decode(s.comm, NULL, 0, s.comm) AS decode_comm,
             --使用case...when
             (CASE WHEN s.comm IS NULL THEN 0 ELSE s.comm END ) AS case_comm
  FROM scott.emp s;

按多个字段排序

--按多个字段排序
SELECT s.empno,s.ename,s.job FROM scott.emp s;
SELECT s.empno,s.ename,s.job FROM scott.emp s ORDER BY s.empno,s.ename,s.job; --越放前面优先级越高
SELECT s.empno,s.ename,s.job FROM scott.emp s ORDER BY s.ename,s.empno,s.job; --越放前面优先级越高
SELECT s.empno,s.ename,s.job FROM scott.emp s ORDER BY 1,2,3; --越放前面优先级越高,但此方法不推荐使用,因为会让人大惑不解
SELECT s.empno,s.ename,s.job FROM scott.emp s ORDER BY 1,2,3; --越放前面优先级越高,但此方法不推荐使用,因为会让人大惑不解

按子串排序

--按子串排序
SELECT s.ename,
       s.job,
       --取ename字符串中最后一个字符的前两个字符串
       substr(s.ename, length(s.ename) - 2, 2) AS substr_ename
  FROM scott.emp s; --不排序
SELECT s.ename,
       s.job,
       --取ename字符串中最后一个字符的前两个字符串
       substr(s.ename, length(s.ename) - 2, 2) AS substr_ename
  FROM scott.emp s
 ORDER BY substr(s.ename, length(s.ename) - 2, 2);  --按substr_ename排序
--order by时可以使用别名,效果完全一样
SELECT s.ename,
       s.job,
       --取ename字符串中最后一个字符的前两个字符串
       substr(s.ename, length(s.ename) - 2, 2) AS substr_ename
  FROM scott.emp s
 ORDER BY substr_ename; --按substr_ename排序

按模式搜索

--按模式搜索

SELECT * FROM scott.emp s WHERE s.ename LIKE '%S'; --搜索雇员名字末尾字符带S的,%占多位
SELECT * FROM scott.emp s WHERE s.ename LIKE 'S%'; --搜索雇员名字首字符带S的,%占多位
SELECT * FROM scott.emp s WHERE s.ename LIKE '%S%'; --搜索雇员名字内带S的,%占多位
SELECT * FROM scott.emp s WHERE s.ename LIKE '_L%'; --搜索雇员名字第二个字符带L的,"_"占一位
SELECT * FROM scott.emp s WHERE s.ename LIKE 'A_L%'; --搜索雇员名A开头的第二个字符带L的,"_"占一位

插入新记录

--插入新记录
SELECT * FROM scott.dept;
--插入新部门编号,部门名称,部门所在位置
INSERT INTO scott.dept VALUES ('50', '行政部门', '深圳');
COMMIT; --提交,执行此步同一事务下的操作将完成记录,无法还原操作
ROLLBACK; --回滚,执行此步同一事务下的操作将被还原(后悔药)

--因此rollback后数据不会插入到scott.dept中,只有commit后才会被插入到scott.dept中
--注意:执行操作时,涉及到更改数据,例如增删改等,必须执行commit或rollback以便完成事务的完整性,如果事务不完整,会发生锁表或资源占用等异常情况
--当发生资源暂用或者锁表,可以使用管理员身份执行强制关闭事务,也可以断开相应占用的数据库连接,当数据库连接断开后,事务将不再发生,也就不存在锁表了

--还原数据
DELETE FROM scott.dept s WHERE s.deptno = '50';
COMMIT;

插入默认值

--插入默认值
SELECT * FROM scott.dept;
ALTER TABLE scott.dept modify loc DEFAULT '深圳';
COMMIT;
INSERT INTO scott.dept VALUES ('50', '行政部门', DEFAULT);
COMMIT;
SELECT s.deptno, s.dname, s.loc FROM scott.dept s WHERE s.deptno = '50';


--后期视频为保障scott用户的表不受影响,在不影响每一视频功能实现情况下,使用一些脚本还原操作,所以视频不会执行
DELETE FROM scott.dept s WHERE s.deptno = '50';
COMMIT;
ALTER TABLE scott.dept modify loc DEFAULT null;
COMMIT;

查找满足多个条件的行

--查找满足多个条件的行
--例如:需要查询工作为'SALESMAN',入职日期为1982年2月20号之后的员工
SELECT s.empno, s.ename, s.job, s.mgr, s.hiredate, s.sal, s.comm, s.deptno
  FROM scott.emp s
 WHERE s.job = 'SALESMAN'
   AND s.hiredate > DATE '1981-02-20';
--例如:需要查询工作为'SALESMAN',入职日期为1982年2月20号之前的员工
SELECT s.empno, s.ename, s.job, s.mgr, s.hiredate, s.sal, s.comm, s.deptno
  FROM scott.emp s
 WHERE s.job = 'SALESMAN'
   AND s.hiredate <= DATE '1981-02-20';
--例如:需要查询工作为'SALESMAN'并且入职时间为1982年2月20号之前或部门编号为10或没有奖金的员工
SELECT s.empno, s.ename, s.job, s.mgr, s.hiredate, s.sal, s.comm, s.deptno
  FROM scott.emp s
 WHERE s.job = 'SALESMAN'
   AND (s.hiredate <= DATE
        '1981-02-20' OR s.deptno = '10' OR s.comm IS NOT NULL);
--例如:需要查询工作为'SALESMAN'或入职时间为1982年2月20号之前或部门编号为10或没有奖金的员工
SELECT s.empno, s.ename, s.job, s.mgr, s.hiredate, s.sal, s.comm, s.deptno
  FROM scott.emp s
 WHERE s.job = 'SALESMAN'
    OR (s.hiredate <= DATE
        '1981-02-20' OR s.deptno = '10' OR s.comm IS NOT NULL);

查找空值

--查找空值
SELECT * FROM scott.emp s WHERE s.mgr IS NULL;
SELECT * FROM scott.emp s WHERE s.comm IS NOT NULL;
--空格和NULL不是一回事,空格仍有占位,而NULL表示什么都没有,可以这样理解
SELECT * FROM scott.emp s WHERE (s.job) = ' ';
SELECT * FROM scott.emp s WHERE TRIM(s.job) IS NULL;
SELECT * FROM scott.emp s WHERE (s.job) IS NULL;

根据数据项的键排序

--根据数据项的键排序
SELECT s.empno,
       s.ename,
       s.job,
       s.mgr,
       s.hiredate,
       s.sal,
       s.comm,
       s.deptno,
       --当工作为SALESMAN时按comm排序,否则按sal排序
       decode(s.job, 'SALESMAN', s.comm, s.sal) AS jpx --此行可以放在order by上,可以不显示出来
  FROM scott.emp s
 ORDER BY jpx;
SELECT s.empno, s.ename, s.job, s.mgr, s.hiredate, s.sal, s.comm, s.deptno
  FROM scott.emp s
 ORDER BY decode(s.job, 'SALESMAN', s.comm, s.sal) --当工作为SALESMAN时按comm排序,否则按sal排序

组合相关的行

----组合相关的行
SELECT * FROM scott.emp s WHERE s.deptno = 10;
SELECT * FROM scott.dept o WHERE o.deptno = 10;
--可以使用内部条件连接两个表
SELECT s.empno,
       s.ename,
       s.job,
       s.mgr,
       s.hiredate,
       s.sal,
       s.comm,
       s.deptno,
       's <--> o',
       o.deptno,
       o.dname,
       o.loc
  FROM scott.emp s, scott.dept o
 WHERE s.deptno = o.deptno
   AND s.deptno = 10;
--可以使用JOIN连接两个表
SELECT s.empno,
       s.ename,
       s.job,
       s.mgr,
       s.hiredate,
       s.sal,
       s.comm,
       s.deptno,
       's <--> o',
       o.deptno,
       o.dname,
       o.loc
  FROM scott.emp s
  JOIN scott.dept o
    ON s.deptno = o.deptno --ON后面是两表或多表关联时的条件,也可以放在where后,但是不推荐,一般ON后面会更让人一看就懂
 WHERE s.deptno = 10;

聚集与联接

--聚集与联接
SELECT s.ename, s.job, s.mgr, s.hiredate, s.deptno, SUM(s.sal) AS sum_sal
  FROM scott.emp s
  JOIN scott.dept o
    ON s.deptno = o.deptno
   AND s.deptno = 20
 GROUP BY s.ename, s.job, s.mgr, s.hiredate, s.deptno
 ORDER BY s.job;
--如果GROUP BY使用不同,统计的合计含义也会改变,如下是统计雇员的经理,雇员所在部门的编号同一个工作的工资合计
SELECT s.job, s.mgr, s.deptno, SUM(s.sal) AS sum_sal
  FROM scott.emp s
  JOIN scott.dept o
    ON s.deptno = o.deptno
   AND s.deptno = 20
 GROUP BY s.job, s.mgr, s.deptno
 ORDER BY s.job;

记录集叠加

--记录集叠加
SELECT s.empno, s.ename, s.job, s.mgr, s.hiredate, s.sal, s.comm, s.deptno
  FROM scott.emp s
 WHERE s.deptno <= 10;
SELECT s.empno, s.ename, s.job, s.mgr, s.hiredate, s.sal, s.comm, s.deptno
  FROM scott.emp s
 WHERE s.deptno <= 20;
--UNION ALL上下两条或多条SQL必须元数据(字段名称,属性等)完全一致,使用UNION ALL取并集
SELECT s.empno, s.ename, s.job, s.mgr, s.hiredate, s.sal, s.comm, s.deptno
  FROM scott.emp s
 WHERE s.deptno <= 10
UNION ALL
SELECT s.empno, s.ename, s.job, s.mgr, s.hiredate, s.sal, s.comm, s.deptno
  FROM scott.emp s
 WHERE s.deptno <= 20;
--UNION和UNION ALL用法一致,但是UNION不仅去除重复,还会按字段顺序排序(以下empno优先级最高,按此排序)
SELECT s.empno, s.ename, s.job, s.mgr, s.hiredate, s.sal, s.comm, s.deptno
  FROM scott.emp s
 WHERE s.deptno = 10
UNION
SELECT s.empno, s.ename, s.job, s.mgr, s.hiredate, s.sal, s.comm, s.deptno
  FROM scott.emp s
 WHERE s.deptno = 20;

识别和消除笛卡尔积

--识别和消除笛卡尔积
SELECT * FROM scott.emp;
SELECT * FROM scott.dept;
--如果条件加的不合理或少条件,会导致垃圾数据的产生
SELECT s.empno,
       s.ename,
       s.job,
       s.mgr,
       s.hiredate,
       s.sal,
       s.comm,
       s.deptno,
       's <--> o',
       o.deptno,
       o.dname,
       o.loc
  FROM scott.emp s
  LEFT JOIN scott.dept o
    ON s.deptno = 10
   --where s.deptno = o.deptno
--所以为解决这个问题,两表的数据关联关系必须搞清楚,两边有共同的数据一定要关联起来,这样减少垃圾数据产生之后,合理根据需要筛选数据

连接列值

--连接列值
SELECT s.ename, s.ename FROM scott.emp s;
SELECT s.ename || '的工作是' || s.ename AS name_job FROM scott.emp s;
SELECT concat(concat(s.ename, '的工作是'), s.ename) AS name_job
  FROM scott.emp s;
--在ORACLE中"||"是concat(var1,var2)函数的简写

限制返回行数

--限制返回行数
SELECT * FROM scott.emp s;
SELECT * FROM scott.emp s WHERE rownum <= 10;
SELECT * FROM scott.emp s WHERE rownum <= 1;
--ORACLE约定,ROWNUM函数不能使用大于符号,否则不会有数据返回
SELECT * FROM scott.emp s WHERE rownum > 1;
--由于ROWNUM函数返回最小值为1,因此,1>0永远成立,所以如果使用>0条件查询时结果为所有数据
SELECT * FROM scott.emp s WHERE rownum > 0;
--把ROWNUM函数作为字段,可以使用分页查询,此时可以使用>符号,它将拥有和字段一样的属性
SELECT *
  FROM (SELECT s.empno, s.ename, rownum AS n
          FROM scott.emp s
         WHERE rownum <= 14)
 WHERE n >= 10;

2018-12-18

ORACLE视频学习之SQL篇

scott用户对应表字段备注

一次性向多个表插入记录

两个表中查找共同行

为列取有意义的名字

从一个表中向另一个表中复制行

从一个表中查找另一个表中没有的值

从字符串中删除不需要的字符

从表中删除所有记录

从表中检索部分列

从表中检索部分行

从表中选取所有的行和列

以指定的次序返回查询结果

使用NULL代替默认值

使用SQL来生成SQL

列出模式中的表

列出表中的列

列出表的索引列

列出表约束

删除单个记录

删除指定记录

删除违反参照完整性的记录

删除重复数据

合并记录

向查询中增加联接而不影响其他联接

在ORACLE中描述数据字典视图

在SLEECT语句中使用条件逻辑

在where子句中引用取别名的列

在一个表中查找与其他表不匹配的记录

在表中编辑记录

在表中随机返回N条记录

在运算和比较时,使用NULL

处理排序空值

复制表定义

字符串文字中包含引号

对字母数字混合的数据排序

将空值转换为实际值

按多个字段排序

按子串排序

按模式搜索

插入新记录

插入默认值

查找满足多个条件的行

查找空值

根据数据项的键排序

组合相关的行

聚集与联接

记录集叠加

识别和消除笛卡尔积

连接列值

限制返回行数

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读