【第22期】观点:IT 行业加班,到底有没有价值?

Oracle442个应用场景------------基础应用场景

原创 2015年07月09日 08:23:41

 

/////////////////基础知识//////////////////

 


应用场景178:最简单的select语句

SELECT * FROM Employees;

应用场景179:指定要查询的列

COL EMP_NAME FORMAT A20
COL SEX FORMAT A10
COL TITLE FORMAT A10
SELECT Emp_name, Sex, Title FROM HRMAN.Employees;


应用场景180:使用DISTINCT关键字

SELECT Title FROM HRMAN.Employees;

SELECT DISTINCT Title FROM HRMAN.Employees;

应用场景181:使用ROWNUM

COL EMP_NAME FORMAT A20
COL SEX FORMAT A10
COL TITLE FORMAT A10
SELECT Emp_name, Sex, Title FROM HRMAN.Employees
WHERE ROWNUM<=3;

应用场景182:改变显示的列标题

COL 姓名 FORMAT A20
COL 性别 FORMAT A4
COL 职务 FORMAT A10
COL 身份证 FORMAT A20
SELECT EMP_NAME AS 姓名, SEX AS 性别, TITLE AS 职务, WAGE AS 工资, IDCARD AS 身份证 FROM HRMAN.Employees;


应用场景183:设置查询条件


COL EMP_NAME FORMAT A20
SELECT Emp_Name, Wage FROM HRMAN.Employees WHERE Wage > 3000 AND Wage < 4000;


应用场景184:在查询条件中使用BITWEEN 关键字

COL EMP_NAME FORMAT A20
SELECT Emp_Name, Wage FROM HRMAN.Employees WHERE Wage BETWEEN 3000 AND 4000;


应用场景185:在查询条件中使用IN关键

COL EMP_NAME FORMAT A20
COL TITLE FORMAT A20
SELECT EMP_NAME, TITLE, WAGE FROM HRMAN.Employees WHERE Emp_name IN ('张三', '李四', '王五');

应用场景186:实现模糊查询

COL EMP_NAME FORMAT A20
COL TITLE FORMAT A20
COL IDCard FORMAT A20
SELECT EMP_NAME, TITLE, IDCard FROM HRMAN.Employees
WHERE IDCard LIKE '%ddd%';

COL EMP_NAME FORMAT A20
COL TITLE FORMAT A20
COL IDCard FORMAT A20
SELECT EMP_NAME, TITLE, IDCard FROM HRMAN.Employees
WHERE IDCard LIKE '110123_adx_';


应用场景187:排序结果集

COL EMP_NAME FORMAT A20
COL TITLE FORMAT A20
COL IDCard FORMAT A20
SELECT EMP_NAME, TITLE, IDCard FROM HRMAN.Employees
ORDER BY Emp_name;

COL EMP_NAME FORMAT A20
COL TITLE FORMAT A20
SELECT EMP_NAME, TITLE, Wage FROM HRMAN.Employees
ORDER BY Wage DESC;


应用场景188:对多列进行排序

COL EMP_NAME FORMAT A20
COL SEX FORMAT A20
SELECT EMP_NAME, Sex, Wage FROM HRMAN.Employees
ORDER BY Sex, Wage;


应用场景189:使用分组统计

COL 职务 FORMAT A10
SELECT Title AS 职务, AVG(Wage) AS 平均公资 FROM HRMAN.Employees GROUP BY Title;

COL Sex FORMAT A10
COL Title FORMAT A10
SELECT Sex, Title, AVG(Wage) FROM HRMAN.Employees GROUP BY Title;

SELECT Dep_Id, AVG(Wage) FROM HRMAN.Employees
GROUP BY Dep_Id HAVING AVG(Wage) > 4000;


应用场景190:连接查询

内连接
COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT  t1.Dep_name, t2.Emp_name FROM HRMAN.Departments t1, HRMAN.Employees t2
WHERE t1.Dep_id=t2.Dep_id;

外链接:
COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT  t1.Dep_name, t2.Emp_name FROM HRMAN.Departments t1 INNER JOIN HRMAN.Employees t2
ON t1.Dep_id=t2.Dep_id;


COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT  t1.Dep_name, t2.Emp_name FROM HRMAN.Departments t1 LEFT JOIN HRMAN.Employees t2
ON t1.Dep_id=t2.Dep_id;

COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT  t1.Dep_name, t2.Emp_name FROM HRMAN.Employees t2 RIGHT JOIN HRMAN.Departments t1
ON t1.Dep_id=t2.Dep_id;


COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT  t1.Dep_name, t2.Emp_name FROM HRMAN.Employees t2 FULL JOIN HRMAN.Departments t1
ON t1.Dep_id=t2.Dep_id;

交叉连接
COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT  t1.Dep_name, t2.Emp_name FROM HRMAN.Employees t2 CROSS JOIN HRMAN.Departments t1;


应用场景191:在连接查询中对空值中对空值的判断

COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT  t1.Dep_name, t2.Emp_name FROM HRMAN.Departments t1 LEFT JOIN HRMAN.Employees t2
ON t1.Dep_id=t2.Dep_id;

COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT  t1.Dep_name, t2.Emp_name FROM HRMAN.Departments t1 LEFT JOIN HRMAN.Employees t2
ON t1.Dep_id=t2.Dep_id
WHERE t2.Emp_id IS NULL;


应用场景192:一个简单地子查询

查询办公室的所有员工:
COL Emp_name FORMAT A20
COL Title FORMAT A20
SELECT Emp_Name, Title FROM HRMAN.Employees WHERE Dep_Id =
(SELECT Dep_Id FROM HRMAN.Departments WHERE Dep_name = '办公室');

返回两个部门的值:
COL Emp_name FORMAT A20
COL Title FORMAT A20
SELECT Emp_Name, Title FROM HRMAN.Employees WHERE Dep_Id =
(SELECT Dep_Id FROM HRMAN.Departments WHERE Dep_name = '办公室' OR Dep_name = '人事部');


应用场景193:在查询中使用具和函数返回值

统计表中所有工资低于品级工资的员工的信息

COL Emp_name FORMAT A20
COL Title FORMAT A20
SELECT Emp_name, Title, Wage FROM HRMAN.Employees WHERE Wage <
(SELECT AVG(Wage) FROM HRMAN.Employees);

应用场景194:IN关键字与返回值的子查询

查询办公室和人事部中的员工信息

COL Emp_name FORMAT A20
COL Title FORMAT A20
SELECT Emp_name, Title, Wage FROM HRMAN.Employees WHERE Dep_Id IN
(SELECT Dep_Id FROM HRMAN.Departments WHERE Dep_name = '办公室' OR Dep_name = '人事部');


应用场景195:EXISTS关键字与子查询

查询人事部中的员工信息
COL Emp_name FORMAT A20
COL Title FORMAT A20
SELECT Emp_name, Title, Wage FROM HRMAN.Employees e
WHERE EXISTS
(SELECT Dep_id FROM HRMAN.Departments d
WHERE e.Dep_id = d.Dep_id AND d.Dep_name='人事部');

使用IN关键字:
COL Emp_name FORMAT A20
COL Title FORMAT A20
SELECT Emp_name, Title, Wage FROM HRMAN.Employees e
WHERE e.Dep_id IN
(SELECT Dep_id FROM HRMAN.Departments d
WHERE e.Dep_id = d.Dep_id AND d.Dep_name='人事部');


应用场景196:使用UNION关键字的合并查询

从表中Employee中查询各个部门的部门经理

COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT Dep_Id, Dep_Name FROM HRMAN.Departments
UNION
SELECT Dep_Id, Emp_Name FROM HRMAN.Employees WHERE Title = '部门经理';


工资大于3000的员工记录:

COL Emp_name FORMAT A20
COL Title FORMAT A20
SELECT Emp_name, Title, Wage FROM HRMAN.Employees
UNION
SELECT Emp_name, Title, Wage FROM HRMAN.Employees WHERE Wage > 3000;

高效率:
COL Emp_name FORMAT A20
COL Title FORMAT A20
SELECT Emp_name, Title, Wage FROM HRMAN.Employees
UNION ALL
SELECT Emp_name, Title, Wage FROM HRMAN.Employees WHERE Wage > 3000;


应用场景197:使用select语句中的DECODE函数

SELECT Emp_name, DECODE(Sex, '男', '先生', '女', '女士', '未知') AS Sex
FROM HRMAN.Employees;


应用场景198:使用select与剧中CASE函数

SELECT Emp_name, CASE Sex WHEN '男' THEN '先生' WHEN '女' THEN '女士' ELSE '未知' END AS Sex
FROM HRMAN.Employees;


查询表Employees中的员工工资级别:

SELECT Emp_name, Wage, CASE WHEN Wage<=3000 THEN '低' WHEN Wage>3000 AND Wage<5000 THEN '中' WHEN Wage>=5000 THEN '高' END AS GRADE
FROM HRMAN.Employees;


应用场景199:保存查询结果

将办公室的所有员工的姓名和职务信息保存到表中OFFICE:

COL Emp_name FORMAT A20
COL Title FORMAT A20
CREATE TABLE HRMAN.Office AS
SELECT e.Emp_Name, e.Title
FROM HRMAN.Employees e, HRMAN.Departments d
WHERE e.Dep_id = d.Dep_Id AND d.Dep_Name = '办公室';
SELECT * FROM HRMAN.Office;


应用场景200:插入数据语句

INSERT INTO HRMAN.Departments VALUES(100, '公关部');
SELECT * FROM HRMAN.Departments;

INSERT INTO HRMAN.Employees (Emp_Name, Sex,Title,  IdCard, Dep_Id)
VALUES ('小明', '男', '职员', '110123456789', 2);


应用场景201:在插入数据时利用默认值

ALTER TABLE HRMAN.Employees ADD InputDate date DEFAULT(sysdate);

INSERT INTO HRMAN.Employees (Emp_Name, Sex,Title,  IdCard, Wage, Dep_Id)
VALUES ('小李', '男', '职员', '210123456789', 2500, 2);


应用场景202:修改数据语句

将表中的所有记录工资增加10%:

UPDATE HRMAN.Employees SET Wage=Wage*1.1;

将表中所有部门为"办公室"的员工工资增加10%

UPDATE HRMAN.Employees SET Wage=Wage*1.1
WHERE Dep_id = (SELECT Dep_id FROM HRMAN.Departments WHERE Dep_name = '办公室');


应用场景203:修改数据时不允许在唯一性约束列中使用相同的值

ALTER TABLE HRMAN.Employees
ADD CONSTRAINT UK_EMPNAME
UNIQUE(Emp_name);

UPDATE HRMAN.Employees SET Emp_name='张三' WHERE Emp_name='李四';

应用场景204:修改数据是不能违反检查约束

ALTER TABLE HRMAN.Employees
ADD CONSTRAINT CK_EMPWAGE CHECK(WAGE>0);

UPDATE HRMAN.Employees SET Wage=-1 WHERE Emp_Name='张三';

应用场景205:修改数据时不能违反外键约束

为表HRMAN。Departments的DEP_id列和表HRMAN.Employees的DEP_ID列中创建外键约束
ALTER TABLE HRMAN.Employees
ADD CONSTRAINT FK_EMP_DEPID
FOREIGN KEY(Dep_id) REFERENCES HRMAN.Departments(Dep_id);

UPDATE HRMAN.Employees SET Dep_id=200 WHERE Emp_Name='张三';

应用场景206:删除数据语句

DELETE FROM HRMAN.Employees WHERE Emp_Name = '小明';

TRUNCATE TABLE HRMAN.Employees;

版权声明:本人博客,供大家分享学习,有需要的话,可以转载! 举报

相关文章推荐

Oracle442个应用场景----------数据库实例

0L7Oc5琳越绰舷吕装 http://www.tonglou.com.cn/space.php?uid=9545158&do=blog&id=2412870 eZ6S73贤牡肇惹仕囟 髦Ю狭骘...

oracle触发器的一个应用场景

  需求背景描述: 在真实的项目中,有这么一个需求:现在有两张表,一张产品表productinfo,一张产品推荐表recommendproduct,产品表存储着所有的产品信息,产品推荐表存储着某些比较热卖的推荐产品。当我们将产品表中某个产品的价格修改了之后,在产品推荐表中,如果也存在该...
  • hnylj
  • hnylj
  • 2010-04-14 21:26
  • 1549

程序员升职加薪指南!还缺一个“证”!

CSDN出品,立即查看!

Oracle442个应用场景-----------数据库逻辑对象管理 应用场景

应用场景128:创建临时表 创建一个保存临时选择商品信息的临时表temp_goods CREATE GLOBAL TEMPORARY TABLE temp_goods (GoodsId   NU...

zookeeper的应用场景

zk的作用 及应用场景 1. 树形存储 . 分布式环境下协同工作,记录日志。 2. 配置管理 。 多client 环境下,依赖同样的配置。 各client通过watcher的方式 来监控zk中的配置变化来做处理。 3. 集群管理 Zookeeper 能够很容易的实现集群管理的功能...

Oracle442个应用场景---------PL/SQL基础

----------------------------------------------------------------------------------- 备份和恢复数据库略过,在后面讲...
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)