Oracle入门教程与实战
seo靠我 2023-09-25 15:14:55前言
Oracle 数据库系统是美国 Oracle 公司(甲骨文)提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器 (Client/Server) 或 B/S 体系结构的数据库之一，SEO靠我比如 SilverStream 就是基于数据库的一种中间件。 Oracle 数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系型数据库，它SEO靠我是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能，但它的所有知识，只要在一种机型上学习了 Oracle 知识，便能在各种类型的机器上使用它。
Oracle 的基本使用
连接命令
sqlplus SEO靠我/nolog进入 sqlplus 环境。其中 /nolog 是不登陆到数据库服务器的意思，如果没有 /nolog 参数， sqlplus 会提示你输入用户名和密码。
C:\Users\AdministrSEO靠我ator>sqlplusSQL*Plus: Release 11.1.0.7.0 - Production on 星期三 10月 16 10:30:42 2019Copyright (c) 1982,SEO靠我 2008, Oracle. All rights reserved.请输入用户名: conn[etc]用法: conn 用户名 / 密码 @网络服务名 [as sysdba/sysSEO靠我oper] 当用特权用户身份连接时，必须带上 as sysdba 或是 as sysoper
以系统管理员 (sysdba) 身份连接数据库
SQL> conn / as sysdba 已SEO靠我连接。创建用户
SQL> create user huang identified by 123456;用户已创建。用户授权
SQL> grant create session,connect,resouSEO靠我rce to huang;授权成功。连接到数据库
SQL> conn huang/123456 已连接。 show user显示当前用户名
SQL> show user SEO靠我 USER 为 "HUANG" passw[ord]用于修改用户的密码，如果要想修改其它用户的密码，需要用 sys/system 登录。
SQL> passw SEO靠我 更改 HUANG 的口令 旧口令: 新口令: 重新键入新口令: 口令已更改 disc[onnect]用于断开与当SEO靠我前数据库的连接 (不退出 sqlplus )
SQL> disc 从 Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 SEO靠我- Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 断SEO靠我开 SQL> exit用于断开与当前数据库的连接 (同时退出 sqlplus )
SQL> exit 从 Oracle Database 11g EntSEO靠我erprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP, Data Mining andSEO靠我 Real Application Testing options 断开C:\Users\Administrator>文件操作命令
start 和 @运行 SQL 脚本
SQL> @ d:\c.sql表已SEO靠我创建。或是
SQL> start d:\c.sql表已创建。 edit该命令可以编辑指定的 SQL 脚本
SQL> edit d:\c.sql这样会把 d:\c.sql 这个文件打开
spoSEO靠我ol该命令可以将 sqlplus 屏幕上的(查询)内容输出到指定文件中去
SQL> spool d:\d.sql SQL> select ASSETNUM 序号,DESCRIPTIONSEO靠我 描述 from ASSET; SQL> spool off交互式命令
&可以替代变量，而该变量在执行时，需要用户输入， Oracle 会提示用户输入值
SQL> select * frSEO靠我om ACCOUNTDEFAULTS where ORGID=&ORGID; 输入 orgid 的值:显示和设置环境变量
可以用来控制输出的各种格式，如果希望永久的保存相关设置，可以修改SEO靠我 glogin.sql 脚本
linesize设置显示行的宽度，默认是 80 个字符
SQL> show linesize linesize 80 SQL> set linSEO靠我esize 90 SQL> show linesize linesize 90 pagelize设置每页显示的行数目，默认是 14 ，用法和 lineSEO靠我size 一样
SQL> show pagesize pagesize 14 SQL> set pagesize 20 SQL> show pagesizSEO靠我e pagesize 20Oracle 用户管理
创建用户
在 Oracle 中要创建一个新的用户使用 create user 语句， 一般是具有 dba (数据库管理员)的权限才能使用。SEO靠我
create user 用户名 identified by 密码 ; SQL> create user zhangsan identified by 123456; SEO靠我create user zhangsan identified by 123456* 第 1 行出现错误: ORA-01031: 权限不足我们连接到 sysdba 创建SEO靠我用户
SQL> conn / as sysdba 已连接。 SQL> create user zhangsan identified by 123456;用户已创建。给用SEO靠我户修改密码
如果给自己修改密码可以直接使用
password 用户名 SQL> password huang 更改 huang 的口令 旧口令: SEO靠我 新口令: 重新键入新口令: 口令已更改如果给别人修改密码则需要具有 dba 的权限，或是拥有 alter user 的系统权限
alter user 用户名 idSEO靠我entified by 新密码 SQL> alter user zhangsan identified by 12345678;用户已更改。删除用户
一般以 dba 的身份去删除某个用SEO靠我户， 如果用其它用户去删除用户则需要具有 drop user 的权限。
drop user 用户名 [cascade] SQL> drop user zhangsan;用户已删除。如果SEO靠我要删除的用户，已经创建了表，那么就需要在删除的时候带一个参数 cascade ;
权限和角色
权限创建的新用户是没有任何权限的， 甚至连登陆的数据库的权限都没有， 需要为其指定相应的权限；要使用有能力授权SEO靠我的用户，如 sys 、 system 。
权限包含系统权限和对象权限
系统权限：用户对数据库的相关权限对象权限：用户对其他用户的数据对象操作的权限 角色角色是指由系统权限集合。通常给某个用SEO靠我户授予权限时如果没有角色存在的话，那么需要一条一条的操作，角色的存在就是使得授权变得很方便。通常一个角色由多个系统权限组成。常用的角色有三个 connect (7种权限)、 dba 、 resourcSEO靠我e (在任何表空间建表)。
connect 角色：是授予最终用户的典型权利，最基本的
alter session 修改会话create cluster 建立聚簇create database link 建SEO靠我立数据库链接create sequence 建立序列create session 建立会话create synonym 建立同义词create view 建立视图resource 角色： 是授予开发人SEO靠我员的
create cluster 建立聚簇create procedure 建立过程create sequence 建立序列create table 建表cteate trigger 建立触发器creSEO靠我ate type 建立类型dba 角色：拥有系统所有系统级权限
使用 grant 命令给用户分配权限：
grant 【权限名】 to 【用户名】
分配角色 grant 【角色名】 to 【用SEO靠我户名】 收回权限 revoke 【权限名】 from 【用户名】用户管理的综合案例
SQL> create user zhangsan identified by 12SEO靠我3456; /*创建用户*/用户已创建。SQL> conn zhangsan/123456; /*新建用户没有 session (登陆)权限*/ ERROR: ORA-SEO靠我01045: 用户 ZHANGSAN 没有 CREATE SESSION 权限; 登录被拒绝警告: 您不再连接到 ORACLE。 SQL> show user USERSEO靠我 为 "" SQL> conn / as sysdba; 已连接。 SQL> grant create session to zhangsan; /*使SEO靠我 zhangsan 能够被连接*/授权成功。SQL> conn zhangsan/123456; 已连接。 SQL> show user; USER 为SEO靠我 "ZHANGSAN" SQL> conn / as sysdba; 已连接。 SQL> grant resource to zhangsan; /*让SEO靠我zhangsan 能够在任何表空间下建表*/授权成功。SQL> create table users(name varchar(10),age number(3)); /*在 sys 角色下创建一个简SEO靠我单的表 users */表已创建。SQL> insert into users values(张三,22); /*插入数据*/已创建 1 行。SQL> insert into users valuesSEO靠我(李四,24);已创建 1 行。SQL> select * from users; /*查询*/NAME AGE -------------------- ---------- SEO靠我 张三 22 李四 24SQL> conn zhangsan/123456; 已连接。 SQL> select * from sys.usersSEO靠我; /*新建的用户没有查询 sys 表的权限*/ select * from sys.users* 第 1 行出现错误: ORA-00942: 表或视图SEO靠我不存在SQL> conn / as sysdba; 已连接。 SQL> grant select on users to zhangsan; /*登录到 sys 给 zSEO靠我hangsan 授权让 zhangsan 可以查看 sys 下的 users 表*/授权成功。SQL> conn zhangsan/123456; 已连接。 SQL> SEO靠我select * from sys.users; /* 登录到 zhangsan 下查看 users 表*/NAME AGE -------------------- --------SEO靠我-- 张三 22 王五 24SQL> update sys.users set name=张小三 where name=张三; /*这时如果想要更新 sys.usersSEO靠我 中的数据，会提示 "ORA-01031: 权限不足 " 。因为 sys 只给了 zhangsan 查看的权利，如果仍然想更新，要到 sys 下进行授权*/ update sys.usSEO靠我ers set name=张小三 where name=张三* 第 1 行出现错误: ORA-01031: 权限不足SQL> conn / as sysdba SEO靠我 已连接。 SQL> grant update on users to zhangsan; /*登录到 sys 给 zhangsan 授权让 zhangsan 可以更新 sys SEO靠我下的 users 表*/授权成功。SQL> conn zhangsan/123456; 已连接。 SQL> update sys.users set name=张小三 SEO靠我where name=张三;已更新 1 行。SQL> select * from sys.users;NAME AGE -------------------- ---------- SEO靠我 张小三 22 王五 24SQL> revoke resource from zhangsan; /*登陆到 sys 下回收 resource 权限*/撤销成功。SQL>SEO靠我 revoke select on users from zhangsan; /*登陆到 sys 下回收 select 权限*/撤销成功。SQL> conn zhangsan/123456; SEO靠我 已连接。 SQL> select * from sys.users; /* 这时 sys 就不能再查询 sys.users 的数据了*/ select * frSEO靠我om sys.users* 第 1 行出现错误: ORA-00942: 表或视图不存在使用 profile 管理用户口令
profile 是口令限制，资源限制的命令集合。SEO靠我当建立数据库时， Oracle 会自动建立名称为 default 的 profile。当建立用户没有制定 profile 选项，那 Oracle 就会将 default 分配给用户。
帐号锁定指定登录时SEO靠我最多可以输入密码的次数，也可以指定用户锁定的时间，以天为单位。一般用 dba 的身份去执行命令。
指定用户 huang 最多只能尝试三次登录，锁定时间为 2 天。
SQL> conn / as sysdbSEO靠我a; 已连接。 SQL> create profile lock_account limit failed_login_attempts 3 password_lockSEO靠我_time 2;配置文件已创建SQL> alter user huang profile lock_account;用户已更改。按 CTRL + C 退出来验证账号锁定
C:\Users\AdminisSEO靠我trator>sqlplusSQL*Plus: Release 11.1.0.7.0 - Production on 星期三 10月 30 11:13:48 2019Copyright (c) 198SEO靠我2, 2008, Oracle. All rights reserved.请输入用户名: huang 输入口令: ERROR: ORA-01017: 用SEO靠我户名/口令无效; 登录被拒绝请输入用户名: huang 输入口令: ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝请输入用户名: hSEO靠我uang 输入口令: ERROR: ORA-28000: 帐户已被锁定SP2-0157: 在 3 次尝试之后无法连接到 ORACLE, 退出 SQL*PSEO靠我lus 账户解锁 SQL> alter user huang account unlock;用户已更改。SQL> conn huang; 输入口令: SEO靠我 已连接。 终止口令为了让用户定期修改密码，可以使用终止口令的指令完成，同样这个命令也要 dba 身份来操作。
给 huang 创建一个 profile 文件，要求该用户每SEO靠我隔 10 天要修改登录密码，宽限期 2 天
SQL> create profile huang limit password_life_time 10 password_grace_time 2;配置文SEO靠我件已创建SQL> alter user huang profile huang;用户已更改。解锁方式同上
口令历史如果希望用户在修改密码时，不能使用以前用过的密码，可以使用口令历史，这样 Oracle SEO靠我就会将口令修改的信息存放在数据字典中，这样当用户修改密码时， Oracle 就会对新密码与就得进行对比，如果一样提示用户重新输入。
SQL> create profile password_historSEO靠我y limit password_life_time 10 password_grace_time 2 password_reuse_time 10;配置文件已创建SQL> alter user huSEO靠我ang profile password_history;用户已更改。SQL> alter user huang identified by 12345678;用户已更改。SQL> alter useSEO靠我r huang identified by 12345678; alter user huang identified by 12345678 * 第 SEO靠我1 行出现错误: ORA-28007: 无法重新使用口令password_reuse_time 10 表示 10 天后口令可重复使用。
删除 profile SQL> dSEO靠我rop profile password_history cascade;配置文件已删除。cascade 表示如果已经将 profile分 配给某个用户时，仍要删除 profile，就要加上 cascSEO靠我ade。
Oracle 表的管理
表名和列的命名规则
必须以字母开头长度不能超过 30 个字符不能使用 Oracle 的保留字只能使用如下字符 A-Z ， a-z ， 0-9 ， $ , # 等OracleSEO靠我 支持的数据类型
字符型char 定长，最长2000字符
例如： char(10) 存储内容为“小黄”时，前4个字符放‘小黄’，后六位由空格补齐
优点是：效率高，查询速率快。如身份证的字段可以设置成 chaSEO靠我r(18)
varchar2 变长最大 4000 字符( Oracle 推荐使用)
varchar2(10) 存储内容为“小黄”时， Oracle 分配 4 个字符
clob(character largeSEO靠我 object) 字符型大对象，最大 4G
数字类型number 范围 -10 的 38 次方到 10 的 38 次方，可以是整数，也可以是小数
number(5，2) 表示一个小数有5位有效数字， 2 SEO靠我位是小数
例如：定义一个范围在 -999.99-999.99 的数字可以用 number(5，2)，定义一个范围在 -99999-99999 可以用 number(5)
日期类型date 包含年月日和时分SEO靠我秒
timestamp Oracle 对 date 类型的扩展，可以精确到毫秒。
图片类型blob 二进制数据，可以存放图片，音频，视频最大 4G ，这个类型允许我们将大文件存储进数据库，但是一般在数据库SEO靠我里，存放的应该是这些文件的路径，如果对安全性有要求，可以将文件放入数据库(一般来讲，在真实项目中是不会把图片和声音真的往数据库里存放的)。
建表语句
建表 学生表 creaSEO靠我te table student( /*表名*/id number(4), /*学号*/name varchar2(20), /*姓名*/sex char(2), /*性别*/birthday datSEO靠我e, /*出生日期*/sal number(6,2) /*奖学金*/);SQL> desc student; /*查看表结构*/名称 是否为空? 类型-------------------------SEO靠我---------------- -------- ----------------------------ID NUMBER(4)NAME VARCHAR2(20 CHAR)SEX CHAR(2 CSEO靠我HAR)BIRTHDAY DATESAL NUMBER(6,2) 班级表 create table class(c_id number(2),c_name varchaSEO靠我r2(40));SQL> desc class;名称 是否为空? 类型----------------------------------------- -------- --------------SEO靠我--------------C_ID NUMBER(2)C_NAME VARCHAR2(40 CHAR) 修改表 SQL> alter table student adSEO靠我d (c_id number(2)); /*添加字段*/表已更改。SQL> alter table student modify (name varchar2(50)); /*修改字段的长度*/表已更SEO靠我改。SQL> alter table student modify (name char(20)); /*修改字段的类型(表中不能有数据)*/表已更改。SQL> alter table studentSEO靠我 rename column name to s_name; /*修改字段的名字(表中不能有数据)*/表已更改。SQL> alter table student drop column sex; /*SEO靠我删除一个字段(慎重使用)*/表已更改。SQL> rename student to stu; /*修改表的名字*/表已重命名。SQL> desc stu;名称 是否为空? 类型------------SEO靠我----------------------------- -------- ----------------------------ID NUMBER(4)S_NAME CHAR(20 CHAR)BSEO靠我IRTHDAY DATESAL NUMBER(6,2)C_ID NUMBER(2)SQL> drop table stu; /*删除表*/表已删除。SQL> desc stu; ERRSEO靠我OR: ORA-04043: 对象 stu 不存在 操作表使用 student 表
SQL> insert into student values(1,张三,男,01-1SEO靠我月-19,8888.88); /*添加数据，所有字段必须都插入， Oracle 中默认的日期格式‘DD-MON-YY’ (日-月-年)*/已创建 1 行。SQL> select * from studSEO靠我ent;ID NAME SEX BIRTHDAY ---------- ---------------------------------------- ---- ----------SEO靠我----SAL ----------1 张三 男 01-1月 -198888.88SQL> set linesize 300; /*设置行的宽度，这样就好看多了*/ SSEO靠我QL> select * from student;ID NAME SEX BIRTHDAY SAL ---------- ------------------------------SEO靠我---------- ---- -------------- ----------1 张三 男 01-1月 -19 8888.88SQL> alter session set nls_date_forSEO靠我mat=YYYY-MM-DD; /*修改日期的默认格式。注意，这种修改方法，只对当前会话有效。而不是当前的 sql*plus 窗口。即如果你这样修改之后，又使用 connect 命令以其他用户连接到数SEO靠我据库或者是连接到其他的数据库，则这个日期格式就失效了，又恢复到缺省的日期格式。要想永久改变日期输入格式是需要改注册表的，还有一个方法是使用函数。*/会话已更改。SQL> insert into stuSEO靠我dent values(2,李四,男,2019-01-02,8888.88);已创建 1 行。SQL> select * from student;ID NAME SEX BIRTHDAY SAL SEO靠我 ---------- ---------------------------------------- ---- ---------- ----------1 张三 男 2019-01-SEO靠我01 8888.882 李四 男 2019-01-02 8888.88SQL> insert into student(id,name) values(3,王五); /*插入部分字段,前提是未插入的字SEO靠我段允许为 null */已创建 1 行。SQL> select * from student;ID NAME SEX BIRTHDAY SAL ---------- ---------SEO靠我------------------------------- ---- ---------- ----------1 张三 男 2019-01-01 8888.882 李四 男 2019-01-02SEO靠我 8888.883 王五SQL> insert into student(id,name,sex) values(4,null,null); /*插入空值*/已创建 1 行。SQL> select *SEO靠我 from student;ID NAME SEX BIRTHDAY SAL ---------- ---------------------------------------- -SEO靠我--- ---------- ----------1 张三 男 2019-01-01 8888.882 李四 男 2019-01-02 8888.883 王五4SQL> select * from sSEO靠我tudent where name=null; /*查询 name 为空的一条记录，错误的做法*/未选定行SQL> select * from student where name is null; SEO靠我/*查询 name 为空的一条记录，正确的做法*/ID NAME SEX BIRTHDAY SAL ---------- -------------------------------SEO靠我--------- ---- ---------- ----------4SQL> select * from student where name is not null; /*查询 name 不为SEO靠我空的一条记录*/ID NAME SEX BIRTHDAY SAL ---------- ---------------------------------------- ---- --SEO靠我-------- ----------1 张三 男 2019-01-01 8888.882 李四 男 2019-01-02 8888.883 王五SQL> update student set sexSEO靠我=女 where id=1; /*修改一个字段*/已更新 1 行。SQL> select * from student;ID NAME SEX BIRTHDAY SAL -------SEO靠我--- ---------------------------------------- ---- ---------- ----------1 张三 女 2019-01-01 8888.882 李四SEO靠我 男 2019-01-02 8888.883 王五4SQL> update student set sex=女,name=李小四 where id=2; /*修改多个字段*/已更新 1 行。SQL> SEO靠我select * from student;ID NAME SEX BIRTHDAY SAL ---------- ----------------------------------SEO靠我------ ---- ---------- ----------1 张三 女 2019-01-01 8888.882 李小四 女 2019-01-02 8888.883 王五4SQL> updateSEO靠我 student set name=赵六 where name is null; /*修改含有 null 值的字段*/已更新 1 行。SQL> select * from student;ID NAMSEO靠我E SEX BIRTHDAY SAL ---------- ---------------------------------------- ---- ---------- -----SEO靠我-----1 张三 女 2019-01-01 8888.882 李小四 女 2019-01-02 8888.883 王五4 赵六SQL> insert into student values(1,张三SEO靠我,女,2019-01-01,8888.88); /*增加一条重复的记录*/已创建 1 行。SQL> select * from student;ID NAME SEX BIRTHDAY SAL SEO靠我 ---------- ---------------------------------------- ---- ---------- ----------1 张三 女 2019-01-01SEO靠我 8888.882 李小四 女 2019-01-02 8888.883 王五4 赵六1 张三 女 2019-01-01 8888.88SQL> select distinct id,name fromSEO靠我 student; /*取消重复行，查询时在 select 后面加上 distinct 即可将重复数据略去*/ID NAME ---------- ------------------SEO靠我----------------------3 王五2 李小四1 张三4 赵六SQL> delete from student where id=4; /*删除数据，删除一条记录*/已删除 1 行。SSEO靠我QL> select * from student;ID NAME SEX BIRTHDAY SAL ---------- ------------------------------SEO靠我---------- ---- ---------- ----------1 张三 女 2019-01-01 8888.882 李小四 女 2019-01-02 8888.883 王五1 张三 女 2SEO靠我019-01-01 8888.88SQL> delete from student; /*删除所有记录，表结构还在，会记录日志，这种删除是可以恢复的，速度会稍慢*/已删除4行。SQL> select SEO靠我* from student;未选定行SQL> truncate table student; /*删除所有记录，表结构还在，不记录日记，所有这种删除无法找回数据，但是速度很快*/表被截断。SQL> SEO靠我desc student;名称 是否为空? 类型----------------------------------------- -------- -------------------------SEO靠我---ID NUMBER(4)NAME VARCHAR2(20 CHAR)SEX CHAR(2 CHAR)BIRTHDAY DATESAL NUMBER(6,2)SQL> drop table stuSEO靠我dent; /*删除表的结构和数据*/表已删除。SQL> desc student; ERROR: ORA-04043: 对象 student 不存在SQL> creaSEO靠我te table student( /*表名*/ /*恢复数据，用 delete from student 时数据可恢，使用 student 表*/2 id number(4), /*学号*/3 naSEO靠我me varchar2(20), /*姓名*/4 sex char(2), /*性别*/5 birthday date, /*出生日期*/6 sal number(6,2) /*奖学金*/7 );表已SEO靠我创建。SQL> insert into student values(1,张小三,女,2019-01-01,8888.88); /*插入数据*/已创建 1 行。SQL> insert into stuSEO靠我dent values(2,李小四,女,2019-01-01,8888.88);已创建 1 行。SQL> select * from student;ID NAME SEX BIRTHDAY SAL SEO靠我 ---------- ---------------------------------------- ---- ---------- ----------1 张小三 女 2019-0SEO靠我1-01 8888.882 李小四 女 2019-01-01 8888.88SQL> savepoint sp; /*首先要设置一个保存点*/保存点已创建。SQL> delete from studeSEO靠我nt; /*删除数据*/已删除2行。SQL> select * from student; /*查询验证数据是否被删掉*/未选定行SQL> rollback to sp; /*回滚数据*/回退已完成。SEO靠我SQL> select * from student; /*查询验证数据回滚是否成功，可以设置多个保存点，但是如果不做处理，新的保存点会默认覆盖前一个保存点*/ID NAME SEX BIRTHDAYSEO靠我 SAL ---------- ---------------------------------------- ---- ---------- ----------1 张小三 女 2SEO靠我019-01-01 8888.882 李小四 女 2019-01-01 8888.88Oracle 的基本查询
PL/SQL 软件中两个命令
SQL> clear /*清屏命令*/ SQLSEO靠我> set timing on; /*打开显示操作时间*/ SQL> select * from student;ID NAME SEX BIRTHDAY SAL --SEO靠我-------- ---------------------------------------- ---- -------------- ----------1 张小三 女 01-1月 -19 88SEO靠我88.882 李小四 女 01-1月 -19 8888.88已用时间: 00: 00: 00.00 /*这里显示操作时间*/ SQL> set timing off; /*关闭操作时间SEO靠我*/ SQL> select count(*) from student; /*查询所有记录数*/COUNT(*) ----------2注意
Oracle 的字段不区分SEO靠我大小写，实体区分大小写。表基本查询
scott 用户存在的几张表 ( emp ， dept )，本次实例采用 emp 和 dept 表。
使用算术表达式显示每个雇员的月收入
SQL> select enamSEO靠我e "姓名",sal 月工资 from emp;姓名 月工资 -------------------- ---------- SMITH 800 ALLSEO靠我EN 1600 WARD 1250 JONES 2975 MARTIN 1250 BLAKE 2850 CLARK 24SEO靠我50 SCOTT 3000 KING 5000 TURNER 1500 ADAMS 1100姓名 月工资 -------SEO靠我------------- ---------- JAMES 950 FORD 3000 MILLER 1300已选择14行。显示每个雇员的年收入
SQLSEO靠我> select ename "姓名",sal * 12 as "年收入" from emp;姓名 年收入 -------------------- ---------- SEO靠我 SMITH 9600 ALLEN 19200 WARD 15000 JONES 35700 MARTIN 15000 SEO靠我 BLAKE 34200 CLARK 29400 SCOTT 36000 KING 60000 TURNER 18000 SEO靠我 ADAMS 13200姓名 年收入 -------------------- ---------- JAMES 11400 FORD 36000 SEO靠我 MILLER 15600这里的中文最好用引号引上,尽量不要用中文。
如果计算表达式中有一个 null 值，那么计算结果就为 null ，如何处理 null 值？
SQL> select salSEO靠我 * 13 + nvl(comm,0) * 13 "年薪",ename,comm from emp;年薪 ENAME COMM ---------- -----------------SEO靠我--- ----------10400 SMITH24700 ALLEN 30022750 WARD 50038675 JONES34450 MARTIN 140037050 BLAKE31850 CSEO靠我LARK39000 SCOTT65000 KING19500 TURNER 014300 ADAMS年薪 ENAME COMM ---------- -----------------SEO靠我--- ----------12350 JAMES39000 FORD16900 MILLER已选择14行。nvl(comm,0) 的意思是：如果 comm 为 null ，那么按 0 计算，不是 0SEO靠我 按本身计算。
用 “||” 来连接字符串
SQL> select ename || is a || job from emp; /*这句话表示:姓名为 XX 是做 XX 工作的*/ENAME||ISA|SEO靠我|JOB ---------------------------------------------------------------------------------------SEO靠我--------------- SMITH is a CLERK ALLEN is a SALESMAN WARD is a SALESMAN SEO靠我 JONES is a MANAGER MARTIN is a SALESMAN BLAKE is a MANAGER CLARK is a MASEO靠我NAGER SCOTT is a ANALYST KING is a PRESIDENT TURNER is a SALESMAN ADSEO靠我AMS is a CLERKENAME||ISA||JOB --------------------------------------------------------------SEO靠我---------------------------------------- JAMES is a CLERK FORD is a ANALYST SEO靠我MILLER is a CLERK已选择14行。 使用 where 字句显示工资高于 3000 的员工
SQL> select ename,sal from emp where sal SEO靠我> 3000;ENAME SAL -------------------- ---------- KING 5000查找 1982-1-1 后入职的员工
SQL> selSEO靠我ect ename from emp where hiredate > 1982-1-1;ENAME -------------------- SCOTT SEO靠我 ADAMS MILLER查找工资在 2000-2500 之间的员工，并且显示员工的工资
SQL> select ename,sal from emp where sal >=2000SEO靠我 and sal <=2500;ENAME SAL -------------------- ---------- CLARK 2450 使用 likeSEO靠我 操作符%：表示 0 到多个字符
_：表示任意单个字符显示首字母为 S 的员工
SQL> select ename from emp where ename like S%;ENAME -SEO靠我------------------- SMITH SCOTT显示第三个字母为大写 O 的所有员工的姓名和工资
SQL> select ename,sal from emSEO靠我p where ename like __O%; /*注意: 两个 _ 符号*/ENAME SAL -------------------- ---------- SCSEO靠我OTT 3000 在 where 条件中使用 in显示 empno 为 7844 , 7839 , 123 , 456 的雇员情况
SQL> select * from emp wherSEO靠我e empno in(7844,7839,123,456);EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ------SEO靠我-------------- ------------------ ---------- ---------- ---------- ---------- ----------7839 KING PRSEO靠我ESIDENT 1981-11-17 5000 107844 TURNER SALESMAN 7698 1981-09-08 1500 0 30这种查询效率很高
使用 is null 操作符显示没有上级SEO靠我的雇员情况
SQL> select * from emp where mgr is null;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO -SEO靠我--------- -------------------- ------------------ ---------- ---------- ---------- ---------- ------SEO靠我----7839 KING PRESIDENT 1981-11-17 5000 10 使用逻辑操作符号查询工资高于 500 或者是岗位为 MANAGER 的雇员，同时还要满足他们的姓名SEO靠我首字母为大写的 J
SQL> select * from emp where (sal >500 or job=MANAGER) and ename like J%;EMPNO ENAME JOB MGSEO靠我R HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- ---SEO靠我------- ---------- ---------- ----------7566 JONES MANAGER 7839 1981-04-02 2975 207900 JAMES CLERK 7SEO靠我698 1981-12-03 950 30这里的括号不可以忘记,否则条件就变了,因为 and 的优先级高于 or 。
使用 order by 子句按照工资从高到低的顺序显示雇员和工资
SQL> selecSEO靠我t ename,sal from emp order by sal desc;ENAME SAL -------------------- ---------- KINSEO靠我G 5000 FORD 3000 SCOTT 3000 JONES 2975 BLAKE 2850 CLARK 2450SEO靠我 ALLEN 1600 TURNER 1500 MILLER 1300 WARD 1250 MARTIN 1250ENASEO靠我ME SAL -------------------- ---------- ADAMS 1100 JAMES 950 SMITH 80SEO靠我0已选择14行。desc 为降序， asc 为升序(默认)
照部门号升序而雇员工资降序排列(知道某和部门最高工资的员工和最低工资的员工是谁)
SQL> select * from emp order bySEO靠我 deptno asc,sal desc;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------------SEO靠我----- ------------------ ---------- ---------- ---------- ---------- ----------7839 KING PRESIDENT 1SEO靠我981-11-17 5000 107782 CLARK MANAGER 7839 1981-06-09 2450 107934 MILLER CLERK 7782 1982-01-23 1300 10SEO靠我7788 SCOTT ANALYST 7566 1987-04-19 3000 207902 FORD ANALYST 7566 1981-12-03 3000 207566 JONES MANAGESEO靠我R 7839 1981-04-02 2975 207876 ADAMS CLERK 7788 1987-05-23 1100 207369 SMITH CLERK 7902 1980-12-17 80SEO靠我0 207698 BLAKE MANAGER 7839 1981-05-01 2850 307499 ALLEN SALESMAN 7698 1981-02-20 1600 300 307844 TUSEO靠我RNER SALESMAN 7698 1981-09-08 1500 0 30EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------SEO靠我-- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------765SEO靠我4 MARTIN SALESMAN 7698 1981-09-28 1250 1400 307521 WARD SALESMAN 7698 1981-02-22 1250 500 307900 JAMSEO靠我ES CLERK 7698 1981-12-03 950 30已选择14行。 使用列的别名排序 SQL> select ename,sal * 12 "年薪" fromSEO靠我 emp order by "年薪" asc;ENAME 年薪 -------------------- ---------- SMITH 9600 JSEO靠我AMES 11400 ADAMS 13200 WARD 15000 MARTIN 15000 MILLER 15600 SEO靠我TURNER 18000 ALLEN 19200 CLARK 29400 BLAKE 34200 JONES 35700ENAME 年薪SEO靠我 -------------------- ---------- SCOTT 36000 FORD 36000 KING 60000已选SEO靠我择14行。Oracle 的复杂查询
在实际应用中经常需要执行复杂的数据统计,经常需要现实多张表的数据，所以经常要用到数据分组函数如 max() , min() , avg() , sum() , couSEO靠我nt() 等。
分组函数
显示所有员工中最高工资和最低工资
SQL> select max(sal),min(sal) from emp;MAX(SAL) MIN(SAL) --------SEO靠我-- ----------5000 800注意
如果列里面有一个分组函数，其它的都必须是分组函数， 否则就出错；如本例中不能写成：SQL> select ename, sal from emp wherSEO靠我e sal = max(sal); select ename, sal from emp where sal = max(sal)* 第 1 行出现错误: SEO靠我 ORA-00934: 此处不允许使用分组函数因为： max 是分组函数，而 ename 不是分组函数。
查询最高，最低工资的员工(利用子查询)
SQL> select ename,sal from eSEO靠我mp where sal = (select max(sal) from emp);ENAME SAL -------------------- ---------- SEO靠我KING 5000SQL> select ename,sal from emp where sal = (select min(sal) from emp);ENAME SAL ---SEO靠我----------------- ---------- SMITH 800显示工资最高的员工的名字，工作岗位
SQL> select ename,job from emp where SEO靠我sal = (select max(sal) from emp);ENAME JOB -------------------- ------------------ KSEO靠我ING PRESIDENT显示工资高于平均工资的员工信息
首先我们可以查询所有员工的平均工资
SQL> select avg(sal) from emp;AVG(SAL) --------SEO靠我-- 2073.21429然后再查询高于平均工资的员工信息
SQL> select * from emp where sal > 2073;EMPNO ENAME JOB MGR HIRSEO靠我EDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- --------SEO靠我-- ---------- ---------- ----------7566 JONES MANAGER 7839 1981-04-02 2975 207698 BLAKE MANAGER 7839SEO靠我 1981-05-01 2850 307782 CLARK MANAGER 7839 1981-06-09 2450 107788 SCOTT ANALYST 7566 1987-04-19 3000SEO靠我 207839 KING PRESIDENT 1981-11-17 5000 107902 FORD ANALYST 7566 1981-12-03 3000 20已选择6行。当然也可以利用子查询
SQSEO靠我L> select * from emp where sal > (select avg(sal) from emp);EMPNO ENAME JOB MGR HIREDATE SAL COMM DESEO靠我PTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---SEO靠我------- ----------7566 JONES MANAGER 7839 1981-04-02 2975 207698 BLAKE MANAGER 7839 1981-05-01 2850 SEO靠我307782 CLARK MANAGER 7839 1981-06-09 2450 107788 SCOTT ANALYST 7566 1987-04-19 3000 207839 KING PRESSEO靠我IDENT 1981-11-17 5000 107902 FORD ANALYST 7566 1981-12-03 3000 20已选择6行。如果这里不是 * 号而是 ename,sal 等字段与分组SEO靠我函数那么在语句的最后要加上 group by ename,sal…(与 select 后的字段一致)
SQL> select ename,sal from emp where sal > (selectSEO靠我 avg(sal) from emp) group by ename,sal;ENAME SAL -------------------- ---------- JONSEO靠我ES 2975 SCOTT 3000 KING 5000 BLAKE 2850 CLARK 2450 FORD 3000SEO靠我已选择6行。 group by 和 having 子句group by 用于对查询结果分组统计
having 子句用于限制分组显示结果显示每个部门的平均工资和最高工资
SQL> selecSEO靠我t avg(sal),max(sal),deptno from emp group by deptno;AVG(SAL) MAX(SAL) DEPTNO ---------- ----SEO靠我------ ---------- 1566.66667 2850 302175 3000 20 2916.66667 5000 10分组字段依据必须出现在查询结果中，SEO靠我否则结果可读性太差。
显示每个部门的每种岗位的平均工资和最低工资
SQL> select avg(sal),min(sal),deptno,job from emp group by job,deptnoSEO靠我;AVG(SAL) MIN(SAL) DEPTNO JOB ---------- ---------- ---------- ------------------2975 2975 2SEO靠我0 MANAGER5000 5000 10 PRESIDENT1300 1300 10 CLERK1400 1250 30 SALESMAN3000 3000 20 ANALYST2850 2850 SEO靠我30 MANAGER2450 2450 10 MANAGER950 950 30 CLERK950 800 20 CLERK已选择9行。显示平均工资低于 2000 的部门号和它的平均工资与最高工资
SQSEO靠我L> select avg(sal),max(sal),deptno from emp group by deptno having avg(sal) < 2000;AVG(SAL) MAX(SAL)SEO靠我 DEPTNO ---------- ---------- ---------- 1566.66667 2850 30 对分组函数的总结 SEO靠我 分组函数只能出现在选择列表, having , group by , order by 子句中如果在select语句中同时包含有 group by , having , order by ,那么顺序SEO靠我为 group by , having , order by在选择列中如果有列,表达式,和分组函数,那么这些列表达式必须有一个出现在 group by 子句中,否则出错 如: seleSEO靠我ct deptno,avg(sal),max(sal) from emp group by deptno having avg(sal) > 2000; /*这里的deptno就一定要出现在groupSEO靠我 by中*/多表查询
基于两个或两个以上的表或是视图的查询，查单表满足不了要求，如部门和员工的关系。
SQL> select a.ename,a.sal,b.dname from emp a,dept bSEO靠我 where a.deptno = b.deptno;ENAME SAL DNAME -------------------- ---------- -----------------SEO靠我----------- CLARK 2450 ACCOUNTING KING 5000 ACCOUNTING MILLER 1300 ACCOUNTINSEO靠我G JONES 2975 RESEARCH FORD 3000 RESEARCH ADAMS 1100 RESEARCH SMITH 8SEO靠我00 RESEARCH SCOTT 3000 RESEARCH WARD 1250 SALES TURNER 1500 SALES ALSEO靠我LEN 1600 SALESENAME SAL DNAME -------------------- ---------- ---------------------------- SEO靠我 JAMES 950 SALES BLAKE 2850 SALES MARTIN 1250 SALES已选择14行。如果不加 where 子句,就会产生笛卡SEO靠我尔集,所谓笛卡尔集,就是不加筛选,将所有的都查询出来。
结论：
两张表关联,至少需要一个条件排除笛卡尔集
三张表关联,至少需要二个条件排除笛卡尔集
以此类推:多表查询中,判断条件至少是表的个数 -1。显示部门SEO靠我号为10的部门名,员工名和工资
SQL> select d.dname,e.ename,e.sal from emp e,dept d where e.deptno = d.deptno and d.dSEO靠我eptno = 10;DNAME ENAME SAL ---------------------------- -------------------- ---------- SEO靠我 ACCOUNTING CLARK 2450 ACCOUNTING KING 5000 ACCOUNTING MILLER 1300显示各个员工的姓名,工资及工资SEO靠我的级别
SQL> select * from salgrade; /*先查看 salgrade 的表结构和记录*/GRADE LOSAL HISAL ---------- -------SEO靠我--- ----------1 700 12002 1201 14003 1401 20004 2001 30005 3001 9999SQL> select e.ename,e.sal,s.gradSEO靠我e from emp e,salgrade s where e.sal between s.losal and s.hisal;ENAME SAL GRADE ------------SEO靠我-------- ---------- ---------- SMITH 800 1 JAMES 950 1 ADAMS 1100 1 SEO靠我WARD 1250 2 MARTIN 1250 2 MILLER 1300 2 TURNER 1500 3 ALLEN 1600 3 SEO靠我 CLARK 2450 4 BLAKE 2850 4 JONES 2975 4ENAME SAL GRADE ---------------SEO靠我----- ---------- ---------- SCOTT 3000 4 FORD 3000 4 KING 5000 5已选择14行。这里用到了SEO靠我 between … and 子句,表示在 losal 和 hisal 之间。
显示雇员名,雇员工资及所在部门的名字,并部门排序
SQL> select e.ename,e.sal,d.dname froSEO靠我m emp e,dept d where e.deptno = d.deptno order by e.deptno;ENAME SAL DNAME -----------------SEO靠我--- ---------- ---------------------------- CLARK 2450 ACCOUNTING KING 5000 ACCOUNTISEO靠我NG MILLER 1300 ACCOUNTING JONES 2975 RESEARCH FORD 3000 RESEARCH ADASEO靠我MS 1100 RESEARCH SMITH 800 RESEARCH SCOTT 3000 RESEARCH WARD 1250 SALES SEO靠我 TURNER 1500 SALES ALLEN 1600 SALESENAME SAL DNAME -------------------- ---------SEO靠我- ---------------------------- JAMES 950 SALES BLAKE 2850 SALES MARTIN 1250 SEO靠我SALES已选择14行。如果用 group by ,一定要把 e.deptno 放到查询列里面。
自连接
自连接是指在同一张表的连接查询。
显示某个员工的上级领导的姓名
SQL> select worker.SEO靠我ename,boss.ename from emp worker,emp boss where worker.mgr = boss.empno and worker.ename = FORD;ENAMSEO靠我E ENAME -------------------- -------------------- FORD JONES根据 FORD 的名字找到 FORD 的 mgrSEO靠我 编号再根据这个编号找到 boss 的 empno,最后显示出来。
子查询
子查询:指嵌入在其他 SQL 语句中的 select 语句,也叫做嵌套查询。
单行子查询指只返回一行数据的子查询语句。
显示与 SMSEO靠我ITH 同一部门的所有员工
分两步:
查出 SMITH 所在部门根据部门查出所有员工 SQL> select deptno from emp where ename = SMITH;DEPSEO靠我TNO ----------20SQL> select * from emp where deptno = (select deptno from emp where ename = SEO靠我SMITH);EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- --------SEO靠我---------- ---------- ---------- ---------- ---------- ----------7369 SMITH CLERK 7902 1980-12-17 80SEO靠我0 207566 JONES MANAGER 7839 1981-04-02 2975 207788 SCOTT ANALYST 7566 1987-04-19 3000 207876 ADAMS CSEO靠我LERK 7788 1987-05-23 1100 207902 FORD ANALYST 7566 1981-12-03 3000 20数据库在执行 SQL 是从左到右扫描的， 如果有括号的话，括号SEO靠我里面的先被优先执行。
多行子查询返回多行数据的子查询
查询和部门 10 的工作相同的雇员的名字,岗位,工资,部门号
1.首先查出部门 10 的工作种类
SQL> select job from emp wheSEO靠我re deptno = 10;JOB ------------------ MANAGER PRESIDENT CLERK发现有重复结果SEO靠我,所以在 job 前加上 distinct
SQL> select distinct job from emp where deptno = 10;JOB ---------------SEO靠我--- CLERK PRESIDENT MANAGER2.根据工作的种类查询
SQL> select ename,job,sal,deptno from SEO靠我emp where job in (select distinct job from emp where deptno = 10);ENAME JOB SAL DEPTNO -----SEO靠我--------------- ------------------ ---------- ---------- CLARK MANAGER 2450 10 BLAKESEO靠我 MANAGER 2850 30 JONES MANAGER 2975 20 KING PRESIDENT 5000 10 MILLER CLERK 1SEO靠我300 10 JAMES CLERK 950 30 ADAMS CLERK 1100 20 SMITH CLERK 800 20已选择8行。注意这里 jSEO靠我ob 之后用的是" in “而非” = ",因为等号 = 是一对一的。
all 操作符显示工资比部门 30 的所有员工的工资高的员工的姓名,工资和部门号
SQL> select ename,sal,depSEO靠我tno from emp where sal > all (select sal from emp where deptno = 30);ENAME SAL DEPTNO ------SEO靠我-------------- ---------- ---------- JONES 2975 20 SCOTT 3000 20 FORD 3000 2SEO靠我0 KING 5000 10也可以使用 max 方法
SQL> select ename,sal,deptno from emp where sal > (select max(sal)SEO靠我 from emp where deptno = 30);ENAME SAL DEPTNO -------------------- ---------- ---------- SEO靠我 JONES 2975 20 SCOTT 3000 20 KING 5000 10 FORD 3000 20max 方法的效率较高,原因是使用 SEO靠我all 操作符时,要和子查询所得结果逐一比较而使用 max 只需和 sal 中的最大值比较,减少了比较的次数,缩短了时间。数据量较大是会比较明显,数据量较小基本看不出来。
any 操作符显示工资比部门 SEO靠我30 的任意一个员工的工资高的员工的姓名,工资和部门号
SQL> select ename,sal,deptno from emp where sal > any (select sal from emSEO靠我p where deptno = 30);ENAME SAL DEPTNO -------------------- ---------- ---------- KINSEO靠我G 5000 10 FORD 3000 20 SCOTT 3000 20 JONES 2975 20 BLAKE 2850 30 SEO靠我 CLARK 2450 10 ALLEN 1600 30 TURNER 1500 30 MILLER 1300 10 WARD SEO靠我1250 30 MARTIN 1250 30ENAME SAL DEPTNO -------------------- ---------- ---------- SEO靠我 ADAMS 1100 20已选择12行。也可以使用 min 方法
SQL> select ename,sal,deptno from emp where sal > (select min(SEO靠我sal) from emp where deptno = 30);ENAME SAL DEPTNO -------------------- ---------- ----------SEO靠我 ALLEN 1600 30 WARD 1250 30 JONES 2975 20 MARTIN 1250 30 BLASEO靠我KE 2850 30 CLARK 2450 10 SCOTT 3000 20 KING 5000 10 TURNER 1500 30 SEO靠我 ADAMS 1100 20 FORD 3000 20ENAME SAL DEPTNO -------------------- ---------- --SEO靠我-------- MILLER 1300 10已选择12行。原理同上
多列子查询单行子查询是指子查询只返回单列、单行数据，多行子查询是指返回单列多行数据， 都是针对单列而言的，而多列子查SEO靠我询是指查询返回多个列数据的子查询语句。
查询与 SMITH 的部门和岗位完全相同的所有雇员
1.查询出 SMITH 的部门号,岗位
SQL> select deptno,job from emp whereSEO靠我 ename = SMITH;DEPTNO JOB ---------- ------------------20 CLERK2.显示结果
SQL> select * from emp SEO靠我where (deptno,job) = (select deptno,job from emp where ename = SMITH);EMPNO ENAME JOB MGR HIREDATE SSEO靠我AL COMM DEPTNO ---------- -------------------- ------------------ ---------- ---------- ----SEO靠我------ ---------- ----------7369 SMITH CLERK 7902 1980-12-17 800 207876 ADAMS CLERK 7788 1987-05-23 SEO靠我1100 20 在 from 子句中使用子查询显示高于自己部门的平均工资员工信息
1.查询各个部门的平均工资和部门号
SQL> select avg(sal),deptno from emSEO靠我p group by deptno;AVG(SAL) DEPTNO ---------- ---------- 1566.66667 302175 20 SEO靠我 2916.66667 102.把上面的查询看作是一张子表
SQL> select e.ename,e.deptno,e.sal,ds.mysal from emp e,(select deptno, SEO靠我avg(sal) mysal from emp group by deptno) ds where e.deptno = ds.deptno and e.sal > ds.mysal;ENAME DESEO靠我PTNO SAL MYSAL -------------------- ---------- ---------- ---------- ALLEN 30 1600 1SEO靠我566.66667 JONES 20 2975 2175 BLAKE 30 2850 1566.66667 SCOTT 20 3000 2175 SEO靠我 KING 10 5000 2916.66667 FORD 20 3000 2175已选择6行。将这两个表做关联查询,当在 from 子句中使用子查询时,该子查询会被作为一个视SEO靠我图来对待,因此叫做内嵌视图,当在 from 子句中使用子查询时,必须为子查询指定别名。
注意：别名不能用 as ,如:
SQL> select e.ename,e.deptno,e.sal,ds.mysaSEO靠我l from emp e,(select deptno, avg(sal) mysal from emp group by deptno) as ds where e.deptno = ds.deptSEO靠我no and e.sal > ds.mysal; select e.ename,e.deptno,e.sal,ds.mysal from emp e,(select deptno, aSEO靠我vg(sal) mysal from emp group by deptno) as ds where e.deptno = ds.deptno and e.sal > ds.mysal* SEO靠我 第 1 行出现错误: ORA-00933: SQL 命令未正确结束在 ds 前不能加 as ,否则会报错 (给表取别名的时候，不能加 as ;但是给列取别名，是可以加 as 的)SEO靠我。
Oracle 的分页
Oracle 的分页一共有三种方式:
根据 rowid 来分（效率最好）
按分析函数来分（效率次之）
按 rownum 来分（效率最差）
下面最主要介绍第三种：按 rownum 来分
SQSEO靠我L> select * from (select a.*,rownum rn from (select * from emp) a where rownum <= 15) where rn > 10;SEO靠我EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN ---------- -------------------- ------------SEO靠我------ ---------- ---------- ---------- ---------- ---------- ----------7876 ADAMS CLERK 7788 1987-0SEO靠我5-23 1100 20 117900 JAMES CLERK 7698 1981-12-03 950 30 127902 FORD ANALYST 7566 1981-12-03 3000 20 1SEO靠我37934 MILLER CLERK 7782 1982-01-23 1300 10 14已用时间: 00: 00: 00.02这条语句是用来将 11-15 条记录提取出来,拆分这条语句:
1.将所有想SEO靠我要的结果查询出来
SQL> select * from emp;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -----SEO靠我--------------- ------------------ ---------- ---------- ---------- ---------- ----------7369 SMITH SEO靠我CLERK 7902 1980-12-17 800 207499 ALLEN SALESMAN 7698 1981-02-20 1600 300 307521 WARD SALESMAN 7698 1SEO靠我981-02-22 1250 500 307566 JONES MANAGER 7839 1981-04-02 2975 207654 MARTIN SALESMAN 7698 1981-09-28 SEO靠我1250 1400 307698 BLAKE MANAGER 7839 1981-05-01 2850 307782 CLARK MANAGER 7839 1981-06-09 2450 107788SEO靠我 SCOTT ANALYST 7566 1987-04-19 3000 207839 KING PRESIDENT 1981-11-17 5000 107844 TURNER SALESMAN 769SEO靠我8 1981-09-08 1500 0 307876 ADAMS CLERK 7788 1987-05-23 1100 20EMPNO ENAME JOB MGR HIREDATE SAL COMM SEO靠我DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- -SEO靠我--------- ----------7900 JAMES CLERK 7698 1981-12-03 950 307902 FORD ANALYST 7566 1981-12-03 3000 20SEO靠我7934 MILLER CLERK 7782 1982-01-23 1300 10已选择14行。已用时间: 00: 00: 00.082.将上一步的结果作为一个视图,给每一条记录加上一个 rn 编号并SEO靠我将前 15 条记录查询出来;其中 rownum 为 Oracle 的关键字,且在第一次在查询字段中使用 rownum 时,如果有条件限制在 where 子句中也要用 rownum,不可以用 rn。
SQSEO靠我L> select a.*,rownum rn from (select * from emp) a where rownum <= 15;EMPNO ENAME JOB MGR HIREDATE SSEO靠我AL COMM DEPTNO RN ---------- -------------------- ------------------ ---------- ---------- -SEO靠我--------- ---------- ---------- ----------7369 SMITH CLERK 7902 1980-12-17 800 20 17499 ALLEN SALESMSEO靠我AN 7698 1981-02-20 1600 300 30 27521 WARD SALESMAN 7698 1981-02-22 1250 500 30 37566 JONES MANAGER 7SEO靠我839 1981-04-02 2975 20 47654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 57698 BLAKE MANAGER 7839 1SEO靠我981-05-01 2850 30 67782 CLARK MANAGER 7839 1981-06-09 2450 10 77788 SCOTT ANALYST 7566 1987-04-19 30SEO靠我00 20 87839 KING PRESIDENT 1981-11-17 5000 10 97844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 107876SEO靠我 ADAMS CLERK 7788 1987-05-23 1100 20 11EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN -----SEO靠我----- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------SEO靠我 ----------7900 JAMES CLERK 7698 1981-12-03 950 30 127902 FORD ANALYST 7566 1981-12-03 3000 20 13793SEO靠我4 MILLER CLERK 7782 1982-01-23 1300 10 14已选择14行。已用时间: 00: 00: 00.033.将前 15 条数据作为一个视图,提取出 11-15 条。
SQLSEO靠我> select * from (select a.*,rownum rn from (select * from emp) a where rownum <=15) where rn > 10;EMSEO靠我PNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN ---------- -------------------- --------------SEO靠我---- ---------- ---------- ---------- ---------- ---------- ----------7876 ADAMS CLERK 7788 1987-05-SEO靠我23 1100 20 117900 JAMES CLERK 7698 1981-12-03 950 30 127902 FORD ANALYST 7566 1981-12-03 3000 20 137SEO靠我934 MILLER CLERK 7782 1982-01-23 1300 10 14已用时间: 00: 00: 00.01在 Java 程序中只需要替换 15 和 10 这两个数字就可以实现分页了。SEO靠我
用查询结果创建新表
这个命令是一种快捷的建表方法
SQL> select * from (select a.*,rownum rn from (select * from emp) a where rowSEO靠我num <=15) where rn > 10;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN ---------- ---------SEO靠我----------- ------------------ ---------- ---------- ---------- ---------- ---------- ----------7876SEO靠我 ADAMS CLERK 7788 1987-05-23 1100 20 117900 JAMES CLERK 7698 1981-12-03 950 30 127902 FORD ANALYST 7SEO靠我566 1981-12-03 3000 20 137934 MILLER CLERK 7782 1982-01-23 1300 10 14已用时间: 00: 00: 00.01 SQLSEO靠我> create table mytable(id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;表已创建。已用SEO靠我时间: 00: 00: 00.64创建好之后,我们可以查看一下表结构
SQL> desc mytable;名称 是否为空? 类型-------------------------------------SEO靠我---------------- -------- ------------------------------------ID NUMBER(4)NAME VARCHAR2(10)SAL NUMBESEO靠我R(7,2)JOB VARCHAR2(9)DEPTNO NUMBER(2)SQL> desc emp;名称 是否为空? 类型--------------------------------------SEO靠我--------------- -------- ------------------------------------EMPNO NOT NULL NUMBER(4)ENAME VARCHAR2(SEO靠我10)JOB VARCHAR2(9)MGR NUMBER(4)HIREDATE DATESAL NUMBER(7,2)COMM NUMBER(7,2)DEPTNO NUMBER(2)可以看出，新建表的SEO靠我字段类型和查询表的一样。
合并查询
有时在实际应用中，为了合并多个 select 语句的结果，可以使用集合操作符号 union, union all, intersect (交集), minus (差集)SEO靠我
多用于数据量比较大的数据局库，运行速度快。
union该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。
SQL> select ename,sal,job from emp SEO靠我where sal > 2500 union select ename,sal,job from emp where job = MANAGER;ENAME SAL JOB -----SEO靠我--------------- ---------- ------------------ BLAKE 2850 MANAGER CLARK 2450 MANAGER SEO靠我 FORD 3000 ANALYST JONES 2975 MANAGER KING 5000 PRESIDENT SCOTT 3000 SEO靠我ANALYST已选择6行。已用时间: 00: 00: 00.00 union all该操作符与 union 相似，但是它不会取消重复行，而且不会排序。
SQL> select enamSEO靠我e,sal,job from emp where sal > 2500 union all select ename,sal,job from emp where job = MANAGER;ENAMSEO靠我E SAL JOB -------------------- ---------- ------------------ JONES 2975 MANAGER SEO靠我 BLAKE 2850 MANAGER SCOTT 3000 ANALYST KING 5000 PRESIDENT FORD 3000 ANALSEO靠我YST JONES 2975 MANAGER BLAKE 2850 MANAGER CLARK 2450 MANAGER已选择8行。已用时间: 00: SEO靠我00: 00.01 intersect使用该操作符用于取得两个结果集的交集。
SQL> select ename,sal,job from emp where sal > 2500 iSEO靠我ntersect select ename,sal,job from emp where job = MANAGER;ENAME SAL JOB -------------------SEO靠我- ---------- ------------------ BLAKE 2850 MANAGER JONES 2975 MANAGER已用时间: 00: 00: 0SEO靠我0.00 minus使用改操作符用于取得两个结果集的差集， 他只会显示存在第一个集合中， 而不存在第二个集合中的数据。
SQL> select ename,sal,job from eSEO靠我mp where sal > 2500 minus select ename,sal,job from emp where job = MANAGER;ENAME SAL JOB --SEO靠我------------------ ---------- ------------------ FORD 3000 ANALYST KING 5000 PRESIDESEO靠我NT SCOTT 3000 ANALYST已用时间: 00: 00: 00.00集合操作要比 and， or 效率高很多。
操作数据
使用特定的格式插入日期值。
使用to_date函数插入带SEO靠我有日期的表,并按照年月日格式插入
SQL> insert into emp values(9999,huang,PERSIDENT,null,to_date(1997-01-01,YYYY-MM-DD)SEO靠我,800,1000,10);已创建 1 行。已用时间: 00: 00: 00.06使用 to_date 函数可以插入任意形式的日期
使用子查询插入数据一条 insert 语句可以插入大量的数据，当处理行SEO靠我迁移或者装载外部表的数据到数据库时, 可以使用子查询插入数据。
SQL> insert into mytable(id,name,deptno) select empno,ename,deptno frSEO靠我om emp where deptno = 10;已创建4行。已用时间: 00: 00: 00.02 SQL> select * from mytable;ID NAME SAL JOSEO靠我B DEPTNO ---------- -------------------- ---------- ------------------ ----------7369 SMITH SEO靠我800 CLERK 207499 ALLEN 1600 SALESMAN 307521 WARD 1250 SALESMAN 307566 JONES 2975 MANAGER 207654 MARTSEO靠我IN 1250 SALESMAN 307698 BLAKE 2850 MANAGER 307782 CLARK 2450 MANAGER 107788 SCOTT 3000 ANALYST 20783SEO靠我9 KING 5000 PRESIDENT 107844 TURNER 1500 SALESMAN 307876 ADAMS 1100 CLERK 20ID NAME SAL JOB DEPTNO SEO靠我 ---------- -------------------- ---------- ------------------ ----------7900 JAMES 950 CLERK SEO靠我307902 FORD 3000 ANALYST 207934 MILLER 1300 CLERK 107782 CLARK 107839 KING 107934 MILLER 109999 huanSEO靠我g 10已选择18行。已用时间: 00: 00: 00.02后面 4 行为新插入的数据。
使用子查询更新数据希望员工 huang 的岗位、工资与 SCOOT 一样
SQL> update mytable SEO靠我set(job,sal) = (select job,sal from mytable where name=SCOTT) where name = huang;已更新 1 行。已用时间: 00: 0SEO靠我0: 00.00 SQL> select * from mytable;ID NAME SAL JOB DEPTNO ---------- --------------SEO靠我------ ---------- ------------------ ----------7369 SMITH 800 CLERK 207499 ALLEN 1600 SALESMAN 30752SEO靠我1 WARD 1250 SALESMAN 307566 JONES 2975 MANAGER 207654 MARTIN 1250 SALESMAN 307698 BLAKE 2850 MANAGERSEO靠我 307782 CLARK 2450 MANAGER 107788 SCOTT 3000 ANALYST 207839 KING 5000 PRESIDENT 107844 TURNER 1500 SSEO靠我ALESMAN 307876 ADAMS 1100 CLERK 20ID NAME SAL JOB DEPTNO ---------- -------------------- ---SEO靠我------- ------------------ ----------7900 JAMES 950 CLERK 307902 FORD 3000 ANALYST 207934 MILLER 130SEO靠我0 CLERK 107782 CLARK 107839 KING 107934 MILLER 109999 huang 3000 ANALYST 10已选择18行。已用时间: 00: 00: 00.0SEO靠我2name 的值要用大写, Oracle 对值的大小写是敏感的。
最新发布
-
火车站存包怎么收费
2023-09-12
-
养老保险个人缴纳比例是多少
2023-09-12
-
上海交大附中浦东实验高中
2023-09-12
-
保鲜膜能放微波炉加热吗
2023-09-12
-
如何制作usb加热片
2023-09-12
-
折800特卖商城怎么样
2023-09-12
-
詹天佑的著名作品
2023-09-12
-
钓鱼岛又叫什么名字
2023-09-12
-
清美公司简介(5个范本)
2023-09-12
-
钱波个人资料简历
2023-09-12