- 浏览: 358125 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (511)
- AgileMethodology (4)
- RDBMS (32)
- NoSQL (16)
- Java (27)
- Python (28)
- Maven (15)
- Linux (27)
- CommonUtils (13)
- IDE (8)
- JavaScript (17)
- jQuery (2)
- OSGi (2)
- JavaWeb (5)
- Spring (37)
- Struts2 (3)
- ORM (13)
- Ant (1)
- apache-tiles (1)
- FreeMarker (2)
- JSON (8)
- XML (1)
- JUnit (3)
- Lucene (1)
- Web Service (9)
- Design Pattern (13)
- Algorithm (21)
- JVM (14)
- Hadoop (16)
- Spark (5)
- Scala (31)
- Git (4)
- Server (3)
- Node.js (18)
- Concurrent (42)
- Lock (9)
- Collections (3)
- Network (11)
- MicroService (7)
- Docker (13)
- FP (20)
- spring.io (2)
- ELK (1)
- Kafka (5)
最新评论
对Oracle 11g scott用户下的4张表进行数据查询
SET linesize 500; SET pagesize 100; DESC dept; DESC emp; DESC bonus; DESC salgrade; --基本查询 SELECT * FROM dept; SELECT * FROM emp; SELECT * FROM bonus; SELECT * FROM salgrade; SELECT deptno, dname FROM dept; SELECT DISTINCT deptno, job FROM emp; SELECT ename, hiredate FROM emp WHERE hiredate > '01-JAN-82'; SELECT ename, hiredate FROM emp WHERE hiredate > TO_DATE('1982-01-01', 'YYYY-MM-DD'); SELECT deptno, sal, job, ename FROM emp WHERE deptno = 20 AND (sal > 2500 OR job = 'CLERK'); SELECT deptno, sal, job, ename FROM emp WHERE deptno IN(20, 30) AND job NOT IN('CLERK', 'SALESMAN'); SELECT sal, hiredate, comm, ename FROM emp WHERE sal BETWEEN 2500 AND 3500 AND hiredate BETWEEN TO_DATE('1981-01-01', 'YYYY-MM-DD') AND TO_DATE('1981-12-31', 'YYYY-MM-DD') AND comm IS NULL; SELECT ename, sal, deptno FROM emp WHERE ename LIKE 'A%' OR ename LIKE '_A%'; SELECT ename, job, TO_CHAR(hiredate, 'YYYY-MM-DD'), sal * 1.2 FROM emp WHERE hiredate > TO_DATE('1982-01-01', 'YYYY-MM-DD'); SELECT rowid, rownum, ename, sal, deptno FROM emp WHERE ename LIKE 'A%' OR ename LIKE '_A%'; SELECT deptno, ename, sal, comm FROM emp WHERE sal BETWEEN 1500 AND 3000 ORDER BY deptno DESC, ename; SELECT DISTINCT deptno , job FROM emp WHERE deptno = 20 ORDER BY job; --分组查询 SELECT empno, sal , comm FROM emp WHERE deptno = 30; SELECT avg(sal), avg(distinct sal), max(sal), min(sal), sum(sal), count(*), count(sal), count(distinct sal), count(comm) FROM emp WHERE deptno = 30; SELECT deptno, avg(sal), max(sal) FROM emp GROUP BY deptno ORDER BY avg(sal); SELECT deptno, job, avg(sal), max(sal) FROM emp GROUP BY deptno, job; SELECT deptno, job, avg(sal), max(sal) FROM emp GROUP BY rollup(deptno, job); SELECT deptno, job, avg(sal), max(sal) FROM emp GROUP BY cube(deptno, job); SELECT deptno, avg(sal), max(sal) FROM emp WHERE deptno <= 50 GROUP BY deptno HAVING avg(sal) > 2000; --连接查询 SELECT deptno FROM dept WHERE deptno < 30; SELECT deptno, ename FROM emp WHERE job = 'CLERK'; SELECT d.deptno, e.deptno, e.ename FROM dept d, emp e WHERE d.deptno < 30 AND e.job = 'CLERK'; SELECT d.deptno, d.dname, e.ename, e.sal FROM dept d, emp e WHERE d.deptno = e.deptno AND d.deptno = 20; SELECT e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal AND e.deptno = 30; SELECT empno, ename, mgr FROM emp WHERE deptno = 30; SELECT e.ename, m.ename FROM emp e, emp m WHERE e.mgr = m.empno AND e.deptno = 30; --集合查询 SELECT empno, ename, mgr FROM emp WHERE deptno = 30 UNION ALL SELECT empno, ename, mgr FROM emp WHERE job = 'MANAGER'; SELECT empno, ename, mgr FROM emp WHERE deptno = 30 UNION SELECT empno, ename, mgr FROM emp WHERE job = 'MANAGER'; SELECT empno, ename, mgr FROM emp WHERE deptno = 30 MINUS SELECT empno, ename, mgr FROM emp WHERE job = 'MANAGER'; SELECT empno, ename, mgr FROM emp WHERE deptno = 30 INTERSECT SELECT empno, ename, mgr FROM emp WHERE job = 'MANAGER'; --子查询 SELECT ename, deptno, sal FROM emp WHERE sal = (SELECT max(sal) FROM emp); SELECT ename, deptno, sal, job FROM emp WHERE job IN(SELECT distinct job FROM emp WHERE deptno = 20); SELECT ename, deptno, sal, job FROM emp WHERE job NOT IN(SELECT distinct job FROM emp WHERE deptno = 20); SELECT ename, deptno, sal, job FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 20); SELECT ename, deptno, sal, job FROM emp WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 20) ORDER BY deptno; SELECT ename, deptno, sal, job FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'SMITH'); SELECT ename, deptno, sal, job, mgr FROM emp WHERE job IN(SELECT job FROM emp WHERE deptno = 20) AND mgr IN(SELECT mgr FROM emp WHERE deptno = 20) ORDER BY deptno; SELECT deptno, ( SELECT max(sal) FROM emp b WHERE b.deptno = a.deptno) maxsal FROM emp a ORDER BY deptno; SELECT ename, deptno, sal, job FROM emp WHERE EXISTS( SELECT 'x' FROM dept WHERE dept.deptno = emp.deptno AND dept.loc = 'NEW YORK'); SELECT distinct deptno, ( SELECT max(sal) FROM emp b WHERE b.deptno = a.deptno) maxsal FROM emp a ORDER BY deptno;
将以上语句放入一个sql文件中,如sqldemo.sql,在sqlplus中使用@ ${filepath}\sqldemo.sql命令执行查看结果。
发表评论
-
MySQL UNSIGNED
2019-06-26 13:31 370原创转载请注明出处:https://agilestyle. ... -
MySQL索引最左匹配原则
2018-11-14 12:44 2616原创转载请注明出处:http://agilestyle.i ... -
MySQL SQL Create demo
2018-10-22 15:10 375ddl demo CREATE TABLE IF NOT ... -
(转)MySQL Explain详解
2018-09-30 13:16 374作者:陆炫志 出处:xuanzhi的博客 http:// ... -
Mac上安装MySQL后zsh: command not found: mysql
2018-08-19 21:20 4063原创转载请注明出处:http://agilestyle.i ... -
MySQL key值的含义
2017-09-25 21:05 656PRI — 主键约束; UNI — 唯一约束; MU ... -
SQL常见笔试题
2017-09-06 23:00 375原创转载请注明出处:http://agilestyle.i ... -
MySQL扩展
2017-08-23 21:50 474原创转载请注明出处 ... -
表级锁、行级锁、页面锁
2017-04-17 21:30 485原创转载请注明出处 ... -
Oracle优化常见术语
2017-04-02 23:03 435原创转载请注明出处:http://agilestyle.i ... -
Oracle分区
2017-04-02 22:03 403原创转载请注明出处:http://agilestyle.i ... -
MySQL分区
2017-02-24 14:11 371原创转载请注明出处:http://agilestyle.i ... -
Oracle常见术语、操作、区别
2017-02-22 20:55 836原创转载请注明出处:http://agilestyle.i ... -
ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
2017-02-15 17:03 463原创转载请注明出处:http://agilestyle.i ... -
SQL UNION 操作符
2016-11-24 16:22 673原创转载请注明出处 ... -
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
2016-09-02 10:16 366use query below to check activ ... -
MySQL压缩版安装配置
2016-05-18 23:29 608原创装载请注明出处:http://agilestyle.i ... -
综合数据和分组函数
2016-01-30 21:41 569desc emp; COUNT函数 selec ... -
1NF、2NF、3NF
2016-01-28 02:53 406第一范式: 所有的键属性(列)都已定义 没有任何重复组 ... -
ORACLE11gR2完全卸载(转)
2015-12-19 16:45 15181. 停止“服务”中所有的ORCLE服务。 ...
相关推荐
.appName("Python Spark SQL basic example") \ .config("spark.some.config.option", "some-value") \ .getOrCreate() ``` SparkSession是Spark 2.0引入的新接口,它整合了HiveContext、SQLContext和...
│ BasicExample1.java │ BasicExample2.java │ BasicExample3.java │ ├─sample_files │ binaryContent.doc │ textContent.txt │ └─SQL collection_schema.sql collection_schema_10g.sql ...
.appName("Python Spark SQL basic example") \ .getOrCreate() # 读取 CSV 文件 data = spark.read.format("csv").option("header", "false").load("path/to/file.csv") ``` ### 3. 数据处理 题目要求完成多项...
在移动应用中,数据库通常用于存储和管理数据,SQLExample是B4A中的一个典型数据库操作示例,它涵盖了如何在Android应用中创建、查询、更新和删除数据的基本步骤。下面,我们将深入探讨这些关键知识点。 首先,我们...
6. **example32**:可能涉及到数据库访问,例如使用ADO.NET连接SQL Server,执行SQL查询,或者更新数据。这将涵盖`SqlConnection`,`SqlCommand`,以及数据绑定技术。 7. **EXAMPLE7**:可能是一个关于定时器控件...
Transact-SQL, or T-SQL, is Microsoft Corporation’s powerful implementation of the ANSI standard SQL ...An example using the AdventureWorks2008DW database for SQL Server 2008 is also used in Chapter 15.
例如,如果搜索关键字是“example”,则查询可能是`SELECT * FROM TableName WHERE ColumnName LIKE '%example%'`。 4. 执行查询:使用SqlCommand对象的ExecuteReader方法执行每个查询,并将结果存储在一个...
It's easy to find basic SQL syntax and keyword information online. What's hard to find is challenging, well-designed, real-world problems—the type of problems that come up all the time when you're ...
在ACCESS中,你可以使用VBA(Visual Basic for Applications)进行更高级的数据操作,但基础的更新操作可以通过SQL语句完成。下面我们将详细讨论如何执行这些操作,并探讨一些相关的知识点。 1. **SQL UPDATE语句**...
Note: This article assumes a basic knowledge of SQL Data Manipulation Language (DML) and Data Definition Language (DDL) and the ability to read simple code examples written in Xbasic.
这个压缩包 "instantclient-basic-win-x86-64-11.1.0.7.0.zip" 包含了Windows 64位平台上的基本组件,用于在没有完整Oracle客户端安装的情况下连接到Oracle 11g数据库。它由Oracle公司提供,适用于那些需要快速、...
在VB(Visual Basic)编程环境中,获取数据库表的索引信息是一项重要的任务,尤其是在进行数据操作和优化查询性能时。本示例"Example060-获得表的索引信息"将向我们展示如何实现这一功能。索引是数据库管理系统中的...
The example tables used throughout the guide are designed to demonstrate various aspects of using PROC SQL. These tables often include a mix of numeric and character fields and represent real-world ...
command.Parameters.AddWithValue("@email", "test@example.com") command.ExecuteNonQuery() ``` 这里,我们创建了一个`SqlCommand`,设置SQL语句,并添加了参数化输入。`ExecuteNonQuery()`方法执行非查询操作,...
C ++ SQL解析器 这是C ++SQL解析器。 它将给定SQL查询解析为C ++对象。 它是为与集成而开发的,但也可以在其他环境中完美使用。 在2015年3月,我们还撰写了一篇简短的论文,概述了... // Basic Usage Example con
在VB(Visual Basic)编程环境中,数据显示与修改是常见的任务,尤其在开发用户界面或数据库应用时。"Example053-数据显示与修改.rar"这个压缩包文件很可能包含了一个示例项目,演示了如何在VB中实现这些功能。让...
Using reader As New StreamReader("C:\example.txt") Dim line As String = reader.ReadLine() Console.WriteLine(line) End Using ``` 这段代码将打开并读取指定路径的文件,然后打印出第一行内容。 2. **...
4. example75:可能涉及数据库连接,例如使用ADO.NET访问SQL Server或其他数据库,学习如何执行查询、更新数据等操作。 5. example99:可能是一个综合性的案例,例如游戏或模拟器,展示VB的高级功能和性能优化技巧...
Oracle是世界上最广泛使用的数据库管理系统之一,它以其强大的功能和稳定性在企业级应用中...为了更深入地学习Oracle,你可以进一步探索这些主题,同时实践`oracle_basic_example.sql`中的示例代码,以巩固你的理解。
In our example, if one transaction (T1) holds an exclusive lock at the table level, and another transaction (T2) holds an exclusive lock at the row level, each of the transactions believe they have ...