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;
网友评论