Oracle数据库基础教程：查询与排序-优快云博客

本文介绍了Oracle数据库的基础知识，包括数据库概念、SQL语句、排序和限制查询。讲解了select语句的使用，如查询、算术运算、别名、null值处理、distinct关键词以及sqlplus命令。还探讨了排序操作，如order by子句的用法和限制查询的where子句，展示了如何进行数据过滤和模糊查询。

一个认知

       认知什么是oracle？
           oracle：商业运用第一的关系型数据库
           实质：关系型数据库
       了解oracle数据库发展历史

二个概念

数据库：数据存储的仓库
关系型数据库：数据库中保存的对象之间可以存在一定的关联关系，并非完全独立。主要反映到以后学习的主外键.

三个名词

sql:结构化的查询语句，操作oracle数据库的语言
sqlplus:oracle软件自带的可以输入sql，且将sql执行结果显示的终端
pl/sql:程序化的sql语句，在sql语句的基础上加入一定的逻辑操作，如if for...,使之成为一个sql块，完成一定的功能

四种对象

table：表格，由行和列组成，列又称字段，每一行内容为表格的一条完整的数据。
       view: 视图，一张表或者多张表的部分或者完整的映射，好比表格照镜子，镜子里面的虚像就是view
       除去常见的table和view两种对象以外，oracle数据库还支持如下四种对象
       sequence：序列
       index：索引，提高数据的访问效率
       synonym：同义，方便对象的操作
       program unit：程序单元，pl/sql操作的对象

五种分类

sql的五大分类:
       ①DQL： Data retrieval：数据查询
           select
       ②DML：数据操纵语言（行级操作语言）：操   作的是表格当中一条一条的数据
           insert update delete
       ③DDL：数据定义语言（表级操作语言）：操   作的内容为表格（对象）
           create alter drop truncate rename
       ④TCL: 事务控制语言 transaction control：事务控制
           事务（Transaction）
           commit rollback savepoint
       ⑤DCL：数据控制语言
           grant授权 revoke撤回

安装oracle数据库

       1.1 最好默认按照到C盘
       1.2 安装过程中有一步让输入一个密码,建议使用oracle作为密码,方便记忆
       1.3 安装完成之后,打开系统服务,查看服务是否已经正常启动,具体情况参照文档:oracle系统服务.txt
       1.4 打开cmd,输入sqlplus,然后回车查看是否能执行该命令
       1.5 如果不能执行,则需要把安装目录里面的BIN里面配置到系统环境变了path中,然后重新打开一个cmd窗口即可

一、select语句，数据查询操作

1.使用select语句查询某张表的所有数据内容

语法：
   select [distinct] *{col_name1,col_name2,..}
   from tb_name;
   注意：语法中出现的中括号[]，表示该部分可有可无
   *：表示所有列，仅仅作为测试和学习使用，在企业用语中不出现，因为效率低下且可读性差
   col_name1：列名，将需要查阅的数据字段列举出来，可以查看多列值，列名之间用,进行分割即可
   s_emp ：员工信息表
   s_dept：员工部门表
需求：查看s_dept表中的所有记录
   select *
   from s_dept;

   select id,name,region_id
   from s_dept;
练习：查看s_dept表中的所有记录的id和name
   select id,name
   from s_dept;

练习：查看所有员工的id，名字(last_name)和薪资(salary)
select id,last_name,salary
from s_emp;

2.select语句可以对指定的列的所有值进行算术运算。

语法：
   select col_name 运算符数字
   from tb_name;
需求：查看每个员工的员工id，名字和年薪。
   select id,last_name,salary*12
   from s_emp;
注意：select语句永远不对原始数据进行修改。
练习：查看每个员工的员工id，名字和月薪涨100以后的年薪
   select id,last_name,(salary+100)*12
   from s_emp;

3.给查询的列起别名

语法：
   select old_column [as] new_column_name
   from tb_name;
需求：查看员工的员工id，名字和年薪,年薪列名为annual
   select id,last_name,salary*12 as annual
   from s_emp;

4.使用||可以使得多列的值或者列和特殊的字符串合并到一个列进行显示

语法：
   select col_name||'spe_char'||col_name
   from tb_name
   'spe_char':如果一个列的值要跟特殊的字符串连接显示，使用该语法。
需求：查看员工的员工id，全名
   select id,first_name||last_name
   from s_emp;
练习：查看员工的员工id，全名和职位名称，全名和职位名称合并成一列显示，且格式为：姓名，职位名称
   select id,first_name||' '||last_name||','||title as name
   from s_emp;

5.对null值得替换运算

nvl()函数
   语法：
   select nvl(col_name,change_value)
   from tb_name;
需求：查看所有员工的员工id，名字和提成，如果提成为空，显示成0
   select id,last_name,nvl(commission_pct,0) commission_pct
   from s_emp;

6.使用distinct关键词，可以将显示中重复的记录只显示一条

语法：
select distinct col_name,col_name...
from tb_name;

   注意1：distinct关键词只能放在select关键词后面
   如：select id,distinct title
   from s_emp;
   该语句语法错!!!!!
   注意2：如果distinct关键词后面如果出现多列，表示多列联合去重，即多列的值都相同的时候才会认为是重复的记录。
   test表：
   id   id2
   1   2
   1   3
   2   4
   3   4
   3   4
   select distinct id,id2
   from test;
   显示结果为：
   id   id2
   1   2
   1   3
   2   4
   3   4
需求：查看所有员工的职位名称和部门id，同职位同部门的只显示一次
   select distinct title,dept_id
   from s_emp;

7.sqlplus命令

sqlplus 登录之后,可以使用buff(缓存)来存储/执行/修改要执行的sql语句
这里的buff的特点:
1.buff中只能存储一条sql语句(但是这条sql语句可能有很多行)
2.每次放入新的sql语句,会把之前的覆盖掉
3.每次执行sql语句,都会把这个sql语句放到buff里面

   l 查看缓存中的sql语句
   a 在[定位]的那一行后面追加新的内容
   i 在[定位]的那一行下面插入新的一行
   c 替换[定位]的那一行中的某些字符串
       c/老的字符串/新的字符串
   del 删除[定位]的那一行内容
   n 后面加内容可以重写这一行
   ! 后面接终端命令

!clear:清屏 windows中使用$符号例如:$cls
/ 执行缓存sql命令

clear buffer:清空当前缓存的命令

   save test.sql buff中的sql语句保存在test.sql文件中
   get test.sql 把test.sql中的内容在加载到buff中,但是没有运行
   start test.sql 把test.sql中的内容在加载到buff中并且执行
   @test.sql 把test.sql中的内容在加载到buff中并且执行
   edit file_name 使用系统默认编辑器去编辑文件

   spool file_name 将接下来的sql语句以及sql的运行结果保存到文件中
       sql1
       result1
       sql2
       result2
       ...
   spool off 关闭spool功能
   exit：退出

8.伪列 rownum的使用

select id,last_name,first_name, salary, dept_id

from s_emp
Where rownum <=10;

结果不好看，通过column使我们的显示界面好看。

   COLUMN last_name FORMAT a15;
   可以简写为:
   col last_name for a15;
   COLUMN first_name FORMAT a15;

二、排序和限制查询

1.排序:所谓排序，就是根据某个字段的值按照升序或者降序的情况将记录查询出来

   语法：
   select col_name,...
   from tb_name
   order by col_name [asc|desc],...
   注意：1.排序使用order by字句，该子句只对查询记录显示调整，并不改变查询结果，所以执行权最低，即最后执行。
   2.排序关键词：
       asc：升序（默认，默认的意思是不加关键词的时候默认为生序排序）
       desc：降序
   3.如果有多个列排序，后面的列排序的前提是前面的列排好序以后有重复（相同）的值。

   例子：
   id   id2
   1   2
   2   3
   3   4
   4   1
   4   2

   语句：
   select id,id2
   from test
   order by id,id2 desc;

   结果：
   id   id2
   1   2
   2   3
   3   4
   4   2
   4   1
   注意：先排第一列，如果第一列有重复的值再排第二列，以此类推
需求：查看员工的id，名字和薪资，按照薪资的降序排序显示。

2.限制查询，即指定查询条件进行查询

   语法：
   select col_name,...
   from tb_name
   where col_name 比较操作表达式
   逻辑操作符
   col_name 比较操作表达式
   ...
   注意：
   1.限制查询条件，使用where子句
   2.条件可以多个，使用逻辑操作符和()进行条件的逻辑整合
   3.where子句的优先级别最高
   4.比较操作表达式由操作符和值组成
       常见的操作：
       1》逻辑比较操作符
       = > < >= <= !=
       2》不等于:三个都表示不等于的意思(经常用的是!=)
       != <> ^=

需求：查看员工工资小于1000的员工id和名字
   select id,last_name,salary
   from s_emp
   where salary < 1000;

       2》sql比较操作符
       between and：在什么范围之内
需求：查看员工工资在700 到 1500之间的员工id，和名字
   select id,last_name,salary
   from s_emp
   where salary between 700 and 1500;
       in(list):在一个列表中
需求：查看员工号1,3,5,7,9员工的工资
   select id,last_name,salary
   from s_emp
   where id in (1,3,5,7,9);
   【   like:模糊查询，即值不是精确的值的时候使用】
       通配符，即可以代替任何内容的符号
       【% ：通配0到多个字符】
       【_ : 当且仅当通配一个字符】

       转义字符：
       默认为\,可以指定【指定的时候用escape 符号指明即可,转义字符只能转义后面的一个字符】字符可以 a | 空格等
需求：查看员工名字以C字母开头的员工的id，工资。
   select id,last_name,salary
   from s_emp
   where last_name like 'C%';
练习：查看员工名字长度不小于5，且第四个字母为n字母的员工id和工资
    select id,last_name,salary
   from s_emp
   where last_name like '___n_%';
需求：查看员工名字中包换一个_的员工id和工资
   select id,last_name,salary
   from s_emp
   where last_name like '%\_%' escape '\';

       is null：对null值操作特定义的操作符，不能使用=
需求：查看员工提成为为空的员工的id和名字
   select id,last_name,commission_pct
   from s_emp
   where commission_pct is null;
   3.逻辑操作符
   当条件有多个的时候使用
       and：且逻辑
       or：或逻辑
       注意：and逻辑比or逻辑要高
   not：非逻辑
需求：查看员工部门id为41且职位名称为Stock Clerk（存库管理员）的员工id和名字
   select id,last_name,dept_id,title
   from s_emp
   where dept_id = 41
   and
   title = 'Stock Clerk';

附件：（oracle测试表）

alter session set nls_language=english;

CREATE TABLE s_dept(
id   NUMBER(7)    CONSTRAINT s_dept_id_nn NOT NULL,
name   VARCHAR2(25)   CONSTRAINT s_dept_name_nn NOT NULL,
region_id   NUMBER(7),
CONSTRAINT s_dept_id_pk PRIMARY KEY (id),
CONSTRAINT s_dept_name_region_id_uk UNIQUE (name, region_id)
);

INSERT INTO s_dept VALUES (10, 'Finance', 1);
INSERT INTO s_dept VALUES (31, 'Sales', 1);
INSERT INTO s_dept VALUES (32, 'Sales', 2);
INSERT INTO s_dept VALUES (33, 'Sales', 3);
INSERT INTO s_dept VALUES (34, 'Sales', 4);
INSERT INTO s_dept VALUES (35, 'Sales', 5);
INSERT INTO s_dept VALUES (41, 'Operations', 1);
INSERT INTO s_dept VALUES (42, 'Operations', 2);
INSERT INTO s_dept VALUES (43, 'Operations', 3);
INSERT INTO s_dept VALUES (44, 'Operations', 4);
INSERT INTO s_dept VALUES (45, 'Operations', 5);
INSERT INTO s_dept VALUES (50, 'Administration', 1);
COMMIT;

------------------------------------------

CREATE TABLE s_emp(
   id   NUMBER(7)   CONSTRAINT s_emp_id_nn NOT NULL,
   last_name   VARCHAR2(25)   CONSTRAINT s_emp_last_name_nn NOT NULL,
   first_name   VARCHAR2(25),
   userid       VARCHAR2(8),
   start_date   DATE,
   comments   VARCHAR2(255),
   manager_id   NUMBER(7),
   title       VARCHAR2(25),
   dept_id       NUMBER(7),
   salary       NUMBER(11, 2),
   commission_pct   NUMBER(4, 2),
   CONSTRAINT s_emp_id_pk PRIMARY KEY (id),
   CONSTRAINT s_emp_userid_uk UNIQUE (userid),
   CONSTRAINT s_emp_commission_pct_ck
   CHECK (commission_pct IN (10, 12.5, 15, 17.5, 20)));

INSERT INTO s_emp VALUES (1, 'Velasquez', 'Carmen', 'cvelasqu',to_date('03-MAR-90 8:30', 'dd-mon-yy hh24:mi'), NULL, NULL, 'President',50, 2500, NULL);
INSERT INTO s_emp VALUES (2, 'Ngao', 'LaDoris', 'lngao','08-MAR-90', NULL, 1, 'VP, Operations',41, 1450, NULL);
INSERT INTO s_emp VALUES (3, 'Nagayama', 'Midori', 'mnagayam','17-JUN-91', NULL, 1, 'VP, Sales',31, 1400, NULL);
INSERT INTO s_emp VALUES (4, 'Quick-To-See', 'Mark', 'mquickto','07-APR-90', NULL, 1, 'VP, Finance',10, 1450, NULL);
INSERT INTO s_emp VALUES (5, 'Ropeburn', 'Audry', 'aropebur','04-MAR-90', NULL, 1, 'VP, Administration',50, 1550, NULL);
INSERT INTO s_emp VALUES (6, 'Urguhart', 'Molly', 'murguhar','18-JAN-91', NULL, 2, 'Warehouse Manager',41, 1200, NULL);
INSERT INTO s_emp VALUES (7, 'Menchu', 'Roberta', 'rmenchu','14-MAY-90', NULL, 2, 'Warehouse Manager',42, 1250, NULL);
INSERT INTO s_emp VALUES (8, 'Biri', 'Ben', 'bbiri','07-APR-90', NULL, 2, 'Warehouse Manager',43, 1100, NULL);
INSERT INTO s_emp VALUES (9, 'Catchpole', 'Antoinette', 'acatchpo','09-FEB-92', NULL, 2, 'Warehouse Manager',44, 1300, NULL);
INSERT INTO s_emp VALUES (10, 'Havel', 'Marta', 'mhavel','27-FEB-91', NULL, 2, 'Warehouse Manager',45, 1307, NULL);
INSERT INTO s_emp VALUES (11, 'Magee', 'Colin', 'cmagee','14-MAY-90', NULL, 3, 'Sales Representative',31, 1400, 10);
INSERT INTO s_emp VALUES (12, 'Giljum', 'Henry', 'hgiljum','18-JAN-92', NULL, 3, 'Sales Representative',32, 1490, 12.5);
INSERT INTO s_emp VALUES (13, 'Sedeghi', 'Yasmin', 'ysedeghi','18-FEB-91', NULL, 3, 'Sales Representative',33, 1515, 10);
INSERT INTO s_emp VALUES (14, 'Nguyen', 'Mai', 'mnguyen','22-JAN-92', NULL, 3, 'Sales Representative',34, 1525, 15);
INSERT INTO s_emp VALUES (15, 'Dumas', 'Andre', 'adumas','09-OCT-91', NULL, 3, 'Sales Representative',35, 1450, 17.5);
INSERT INTO s_emp VALUES (16, 'Maduro', 'Elena', 'emaduro','07-FEB-92', NULL, 6, 'Stock Clerk',41, 1400, NULL);
INSERT INTO s_emp VALUES (17, 'Smith', 'George', 'gsmith','08-MAR-90', NULL, 6, 'Stock Clerk',41, 940, NULL);
INSERT INTO s_emp VALUES (18, 'Nozaki', 'Akira', 'anozaki','09-FEB-91', NULL, 7, 'Stock Clerk',42, 1200, NULL);
INSERT INTO s_emp VALUES (19, 'Patel', 'Vikram', 'vpatel','06-AUG-91', NULL, 7, 'Stock Clerk',42, 795, NULL);
INSERT INTO s_emp VALUES (20, 'Newman', 'Chad', 'cnewman','21-JUL-91', NULL, 8, 'Stock Clerk',43, 750, NULL);
INSERT INTO s_emp VALUES (21, 'Markarian', 'Alexander', 'amarkari','26-MAY-91', NULL, 8, 'Stock Clerk',43, 850, NULL);
INSERT INTO s_emp VALUES (22, 'Chang', 'Eddie', 'echang','30-NOV-90', NULL, 9, 'Stock Clerk',44, 800, NULL);
INSERT INTO s_emp VALUES (23, 'Patel', 'Radha', 'rpatel','17-OCT-90', NULL, 9, 'Stock Clerk',34, 795, NULL);
INSERT INTO s_emp VALUES (24, 'Dancs', 'Bela', 'bdancs','17-MAR-91', NULL, 10, 'Stock Clerk',45, 860, NULL);
INSERT INTO s_emp VALUES (25, 'Schwartz', 'Sylvie', 'sschwart','09-MAY-91', NULL, 10, 'Stock Clerk',45, 1100, NULL);
COMMIT;

---------------------------------------------------

CREATE TABLE s_region(
   id   NUMBER(7)   CONSTRAINT s_region_id_nn NOT NULL,
   name   VARCHAR2(50)   CONSTRAINT s_region_name_nn NOT NULL,
   CONSTRAINT s_region_id_pk PRIMARY KEY (id),
   CONSTRAINT s_region_name_uk UNIQUE (name)
);

INSERT INTO s_region VALUES (1, 'North America');
INSERT INTO s_region VALUES (2, 'South America');
INSERT INTO s_region VALUES (3, 'Africa / Middle East');
INSERT INTO s_region VALUES (4, 'Asia');
INSERT INTO s_region VALUES (5, 'Europe');
COMMIT;

loading.........