Oracle数据库实现查询.docx
《Oracle数据库实现查询.docx》由会员分享，可在线阅读，更多相关《Oracle数据库实现查询.docx（38页珍藏版）》请在冰点文库上搜索。
Oracle数据库实现查询
Oracle数据库
http:
//localhost:
5560/isqlplus/
sqlplus回车
按照提示输入口令、密码
altersessionsetnls_date_language="american"更改会话支持英文日期
@E:
\summit2.sql执行SQL脚本
select*froms_emp;
OracleSQL
数据如何保存？
从文件转变为数据库（二维表）
DDL：
数据定义语言，画表头的整个过程（tablestructure）
createtable创建表、altertable修改表、droptable删除表
column列、datatype数据类型、width宽度、constraints约束
DML：
数据操作语言，表结构定义完成后，往表中插入/更新/删除数据（tabledata——row行/记录）
insert插入、delete删除、update更新
TCL：
事务控制语言，数据操作完成之后，需要确认/取消
commit提交、rollback回滚、savepoint保留点
DQL：
数据查询语言
select查询
DCL：
数据控制语言（多用户才能用到）
grant授权、revoke回收
DBMS数据库管理系统、DBA数据库管理员
descs_emp
ID
NUMBER
NOTNULL
LAST_NAME
VARCHAR2
NOTNULL
FIRST_NAME
VARCHAR2
USERID
VARCHAR2
START_DATE
DATE
COMMENTS
VARCHAR2
MANAGER_ID
NUMBER
TITLE
VARCHAR2
DEPT_ID
NUMBER
SALARY
NUMBER
COMMISSION_PCT
NUMBER
descs_region地区表
IDNOTNULLNUMBER（7）
NAMENOTNULLVARCHAR2（50）
descs_dept部门表
ID
NUMBER
NOTNULL
NAME
VARCHAR2
NOTNULL
REGION_ID
NUMBER
desc不是SQL命名，只是Oracle支持的命令
Select语句
功能：
选择selection、投影projection、连接join
select表达式from表名
selectfirst_name,salary*12froms_emp
selectfirst_name,salary*12ann_salfroms_emp
srlary*12改名ann_sal
ann_sal列的别名，如果有空格，特殊字符，大小写敏感加双引号连接
select first_name,salary*12 "ann sal"
from s_emp
select first_name,commission_pct,salary*12*（1+nvl（commission_pct/100,0）） "ann sal"from s_emp
null空置不等于0，不等于空格，算术运算中包含null导致结果为null（当做无穷大）
如何当作0处理？
nvl函数——Oracle独有的（通用函数coalesce）
nvl（p1,p2）
ifp1isnullthenreturnp2
elsereturnp1
endif;
如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值。
该函数的目的是把一个空值（null）转换成一个实际的值。
其表达式的值可以是数字型、字符型和日期型。
但是表达式1和表达式2的数据类型必须为同一个类型
对数字型：
NVL（comm,0）;
对字符型NVL（TO_CHAR（comm）,'NoCommission'）
对日期型NVL（hiredate,'31-DEC-99'）
select last_name ||''|| first_name "full name"from s_emp
||连接，’’表达字符\字符串
注意’’与””区别
Carmenisindeptin50
Markisindeptin10
select first_name || ' is in dept ' || dept_id || '.'from s_emp
distinct去重复，只能跟在select之后，前面没有任何列，即所有列联合起来是唯一的
各个部门有哪些不同的职位
selectdistincttitle,dept_idfroms_emp;
——投影
select后面跟列名
select *from s_item
ORD_ID
ITEM_ID
PRODUCT_ID
PRICE
QUANTITY
QUANTITY_SHIPPED
100
1
10011
135
500
500
100
2
10013
380
400
400
100
3
10021
14
500
500
100
5
30326
582
600
600
100
7
41010
8
250
250
select *from S_CUSTOMER
ID
NAME
PHONE
ADDRESS
CITY
STATE
COUNTRY
ZIP_CODE
CREDIT_RATING
SALES_REP_ID
REGION_ID
COMMENTS
201
Unisports
55-2066101
72ViaBahia
SaoPaolo
Brazil
EXCELLENT
12
2
202
OJAtheletics
81-20101
6741TakashiBlvd.
Osaka
Japan
POOR
14
4
203
DelhiSports
91-10351
11368Chanakya
NewDelhi
India
GOOD
14
4
select *from s_image
ID
FORMAT
USE_FILENAME
FILENAME
IMAGE
1001
JTIFF
Y
bunboot.tif
1002
JTIFF
Y
aceboot.tif
1003
JTIFF
Y
proboot.tif
选择操作：
Where放在from后面，跟条件表达式（结构：
列名/表达式/常量+比较运算符+文字值）不能跟别名
先执行where后执行select即先执行选择，后执行投影
SQL>selectfirst_name,salaryfroms_empwheresalary>1000;
先执行where后执行select即先执行选择
SQL>selectfirst_name,salary*12ann_salfroms_empwhereann_sal>12000;
selectfirst_name,salary*12ann_salfroms_empwhereann_sal>12000
*
第1行出现错误:
ORA-00904:
"ANN_SAL":
标识符无效
字符类型需要使用单引号，大小写敏感
SQL>selectsalary*12froms_empwherefirst_name='Carmen';
SALARY*12
----------
30000
员工工资到1400到2000之间
select first_name, salary*12from s_empwhere salary>=1400 and salary<=2000
LaDoris
17400
Midori
16800
Mark
17400
Audry
18600
Colin
16800
select first_name, salaryfrom s_empwhere salary between 1400 and 2000
FIRST_NAME
SALARY
LaDoris
1450
Midori
1400
Mark
1450
查询dept_id为324243的员工信息
select first_name, salary,dept_id
from s_emp
where dept_id in（32,42,43）or
in表示一个集合，其中任选其一，等同于=any（32，42,43）
FIRST_NAME
SALARY
DEPT_ID
Roberta
1250
42
Ben
1100
43
Henry
1490
32
Akira
1200
42
字符串比较（模糊查询）可以通过like加通配符
%表示0-n个字符，_单个字符
数据库两类表：
用户表，系统表（存储数据库信息）
找出以S_开头的表名
select table_name
from user_tables
where table_name like 'S\_%' escape '\'
S_CUSTOMERS_DEPTS_EMPS_IMAGES_INVENTORYS_ITEMS_LONGTEXT
S_ORDS_PRODUCTS_REGIONS_TITLES_WAREHOUSE
已选择12行。
S需要大写，escape‘\’定义\为转义字符
哪些员工没有提成（commission_pct）
select first_name,commission_pct
from s_emp
where commission_pct is null
null特点：
1、null=null不是true，null<>null也不为true，null无法比较，使用isnull或者isnotnull
2、算术表达式一旦出现null即为空
3、selectdistinctcommission_pctfroms_emp
dept_idnotin（34,22,43）;等价于dept_id<>34anddept_id<>22anddept_id<>43
或者dept_id<>all（34,22,43）
SQL>selectfirst_name,dept_idfroms_empwheredept_idin（32,42,43,null）;
FIRST_NAMEDEPT_ID
-----------------------------------
Roberta42
Ben43
Henry32
Akira42
Vikram42
Chad43
Alexander43
已选择7行。
SQL>selectfirst_name,dept_idfroms_empwheredept_idin（32,42,43）;
FIRST_NAMEDEPT_ID
-----------------------------------
Roberta42
Ben43
Henry32
Akira42
Vikram42
Chad43
Alexander43
已选择7行。
SQL>selectfirst_name,dept_idfroms_empwheredept_idnotin（32,42,43）;
FIRST_NAMEDEPT_ID
-----------------------------------
Carmen50
LaDoris41
Midori31
Mark10
Audry50
Molly41
Antoinette44
Marta45
Colin31
Yasmin33
Mai34
FIRST_NAMEDEPT_ID
-----------------------------------
Andre35
Elena41
George41
Eddie44
Radha34
Bela45
Sylvie45
已选择18行。
SQL>selectfirst_name,dept_idfroms_empwheredept_idnotin（32,42,43,null）;
未选定行
集合中有null肯定无结果
notbetweenand
32部门，42部门员工工资大于1000的信息
SQL>selectfirst_name,dept_id,salaryfroms_empwheresalary>1000anddept_id
in（32,42）;
FIRST_NAMEDEPT_IDSALARY
---------------------------------------------
Roberta421250
Henry321490
Akira421200
SQL>selectfirst_name,dept_id,salaryfroms_empwheresalary>1000anddept_id
=32ordept_id=42;
FIRST_NAMEDEPT_IDSALARY
---------------------------------------------
Roberta421250
Henry321490
Akira421200
Vikram42795
SQL>selectfirst_name,dept_id,salaryfroms_empwheresalary>1000and（dept_i
d=32ordept_id=42）;
FIRST_NAMEDEPT_IDSALARY
---------------------------------------------
Roberta421250
Henry321490
Akira421200
注意不加（）的结果
orderby:
对查询出来的结果进行排序，select字句的最后一个字句
先执行from，然后执行where，执行select，执行orderby
不使用默认原来表的存储顺序
SQL>selectfirst_name,salaryfroms_emporderbysalarydesc;
Carmen2500
Audry1550
Mai1525
Yasmin1515
SQL>selectfirst_name,salaryfroms_emporderbysalaryasc;
Chad750
Vikram795
Radha795
Eddie800
Alexander850
null视为无穷大，可以使用别名，可以使用列索引（位置），使用索引要确定字段非空
SQL>selectfirst_name,salary*12sal_annfroms_empwheresalary*12>18000order
bysal_anndesc;（使用别名）
Carmen30000
Audry18600
Mai18300
Yasmin18180
SQL>selectfirst_name,salary*12sal_annfroms_empwheresalary*12>18000orderby1desc;（使用索引列）
Yasmin18180
Mai18300
Carmen30000
Audry18600
可以对多列进行排序
单行函数：
每条记录都有返回结果
字符类型、数值类型、日期类型、转换函数、nvl一般函数（支持字符，数值，日期）
Lower大写字符转换小写
SQL>selectlower（'SQLcourse'）fromdual;
LOWER（'SQL
----------
sqlcourse
SQL>selectsalary*12froms_empwherelower（first_name）='carmen';
SALARY*12
----------
30000
substr截取子串，
SQL>selectfirst_name,substr（first_name,2,3）froms_emp;
FIRST_NAMESUBSTR
-------------------------------
Carmenarm
LaDorisaDo
Midoriido
Markark
length获取字符串长度
SQL>selectfirst_name,length（first_name）froms_emp;
FIRST_NAMELENGTH（FIRST_NAME）
-------------------------------------------
Carmen6
LaDoris7
Midori6
Mark4
左补空格实现右对齐
SQL>selectlpad（first_name,10,''）froms_emp;
LPAD（FIRST_NAME,10,'
--------------------
Carmen
LaDoris
Midori
Mark
Round四舍五入，trunc不支持四舍五入
SQL>selectsalary,round（salary,-1）,trunc（salary,-1）froms_emp;
SALARYROUND（SALARY,-1）TRUNC（SALARY,-1）
130713101300
140014001400
151515201510
152515301520
145014501450
140014001400
to_number如（'1500'）
SQL>selectfirst_name,salaryfroms_empwheresalary>'1500';
FIRST_NAMESALARY
-----------------------------------
Carmen2500
Audry1550
Yasmin1515
Mai1525
隐式数据类型转换
to_char、to_number,to_date实现数据类型转换
SQL>selectsalary,to_char（salary,'$99,999.99'）froms_emp;
SALARYTO_CHAR（SAL
---------------------
2500$2,500.00
1450$1,450.00
1400$1,400.00
9代表数字，0强制显示0
SQL>selectsalary,to_char（salary,'$00,000.00'）froms_emp;
SALARYTO_CHAR（SAL
---------------------
2500$02,500.00
1450$01,450.00
1400$01,400.00
不加to_char会出现类型转换错误
SQL>selectfirst_name,nvl（to_char（manager_id）,'BOSS'）froms_emp;
FIRST_NAMENVL（TO_CHAR（MANAGER_ID）,'BOSS'）
-----------------------------------------------------------------
CarmenBOSS
LaDoris1
Midori1
Mark1
Audry1
Molly2
Roberta2
Ben2
——表连接
selectfirst_name,namefroms_empcrossjoins_dept;
SQL>selecte.first_name,e.dept_id,d.name,d.idfroms_empejoins_deptdone.d
ept_id=d.id;
FIRST_NAMEDEPT_IDNAMEID
----------------------------------------------------------------------
Carmen50Administration50
LaDoris41Operations41
Midori31Sales31
Mark10Finance10
Audry50Administration50
Molly41Operations41
Roberta42Operations42
Ben43Operations43
驱动表s_emp、匹配表s_dept
在匹配表中找到驱动表的dept_id对应的id（比如32），找到之后不在往下找（id唯一）
内连接的核心匹配
SQL>selecte.first_name,d.namefroms_empejoins_deptdone.dept_id=d.idande.first_name='Carmen';
FIRST_NAMENAME
--------------------------------------------------
CarmenAdministration
区别？
先过滤在连接（越早过滤效率越高），先过滤在连接在选择
SQL>selecte.first_name,d.namefroms_empejoins_deptdone.dept_id=d.idwheree.first_name='Carmen';
FIRST_NAMENAME
--------------------------------------------------
CarmenAdministration
亚洲地区有哪些部门？
s_dep和s_region;
SQL>selecte.namefroms_deptdjoins_regionronr.name='Asia'andd.region_i
d=r.id;
NAME
-------------------------
Sales
Operations
哪些地区有销售部门？
SQL>selectd.namefroms_deptejoins_regiondone.name='Sales'ande.region_
id=d.id;
升级会员 