oracle 11g 007第2章 笔记
第一节、
1.retrieve all employees in department 90 -----------------在这样雇员表里查出 department=90的内容查出来。
SQL> select last_name,salary,department_id 2 from employees 3 where department_id=90; LAST_NAME SALARY DEPARTMENT_ID ------------------------- ---------- ------------- King 24000.00 90 Kochhar 17000.00 90 De Haan 17000.00 90
2.where condition ------------------介绍where子句表达式。 注意:在where子句后面不能用别名(alias).
在where子句使用字符串、和日期。
1)字符串表示: SQL> select last_name,job_id,department_id 2 from employees 3 where last_name='Whalen'; LAST_NAME JOB_ID DEPARTMENT_ID ------------------------- ---------- ------------- Whalen AD_ASST 10
注意:在字符串中是大小写敏感的。 2)日期表示:
SQL> select last_name,job_id,hire_date 2 from employees 3 where hire_date=to_date('1996/2/17','yyyy/mm/dd'); LAST_NAME JOB_ID HIRE_DATE ------------------------- ---------- ----------- Hartstein MK_MAN 1996/2/17
3)如果想字符串中选择全部大写或小写可以用(UPPER、LOWER函数)
UPPER使用:
SQL> select last_name,job_id,salary 2 from employees 3 where upper(last_name)='WHALEN'; LAST_NAME JOB_ID SALARY ------------------------- ---------- ---------- Whalen AD_ASST 4400.00
upper里的值全部替换成大写在跟WHALEN比较得出以上的值。
LOWER使用:
SQL> select last_name,job_id,salary 2 from employees 3 where lower(last_name)='whalen'; LAST_NAME JOB_ID SALARY ------------------------- ---------- ---------- Whalen AD_ASST 4400.00
LOWER里的值全部替换成小写在跟whalen比较得出以上的值。
第二节
comparison operators 算式优先级分为 (= 、> 、 >= 、< 、<= 、<>)还有(Between。。。and)、IN(set)、like、is null
举个例子使用<=运算符计算:
SQL> select last_name,job_id,department_id 2 from employees 3 where department_id <=20; LAST_NAME JOB_ID DEPARTMENT_ID ------------------------- ---------- ------------- Whalen AD_ASST 10 Hartstein MK_MAN 20 Fay MK_REP 20
以上是列出部门号小于等于20之间的数据。
Between。。。and用法:
SQL> select last_name,job_id,salary 2 from employees 3 where salary between 2500 and 3500; LAST_NAME JOB_ID SALARY ------------------------- ---------- ---------- OConnell SH_CLERK 2600.00 Grant SH_CLERK 2600.00 Khoo PU_CLERK 3100.00 Baida PU_CLERK 2900.00 Tobias PU_CLERK 2800.00 Himuro PU_CLERK 2600.00 Colmenares PU_CLERK 2500.00 Nayer ST_CLERK 3200.00 Mikkilineni ST_CLERK 2700.00 Bissot ST_CLERK 3300.00 Atkinson ST_CLERK 2800.00
以上的结果是取工资2500到3500之间的内容;
IN(set)用法·:
SQL> select last_name,job_id,salary 2 from employees 3 where salary in (2600,3100,2800); LAST_NAME JOB_ID SALARY ------------------------- ---------- ---------- OConnell SH_CLERK 2600.00 Grant SH_CLERK 2600.00 Khoo PU_CLERK 3100.00 Tobias PU_CLERK 2800.00 Himuro PU_CLERK 2600.00 Atkinson ST_CLERK 2800.00 Davies ST_CLERK 3100.00 Matos ST_CLERK 2600.00 Fleaur SH_CLERK 3100.00 Geoni SH_CLERK 2800.00 Jones SH_CLERK 2800.00 Walsh SH_CLERK 3100.00 以上是IN的用法,IN是里面的值是一个集合。 如果在IN取字符串的值如下:
SQL> select last_name,job_id,salary 2 from employees 3 where job_id in ('SH_CLERK','ST_CLERK'); LAST_NAME JOB_ID SALARY ------------------------- ---------- ---------- OConnell SH_CLERK 2600.00 Grant SH_CLERK 2600.00 Nayer ST_CLERK 3200.00 Mikkilineni ST_CLERK 2700.00 Landry ST_CLERK 2400.00 Markle ST_CLERK 2200.00 Bissot ST_CLERK 3300.00
like是一个模糊的查询(pattern模式),like中的字符串是区分大小写的。使用如下:
SQL> select first_name,hire_date 2 from employees 3 where hire_date like '%96%'; FIRST_NAME HIRE_DATE -------------------- ----------- Michael 1996/2/17 Matthew 1996/7/18 Jason 1996/6/14 John 1996/10/1 Janette 1996/1/30 Patrick 1996/3/4 Allan 1996/8/1 Ellen 1996/5/11 Nandita 1996/1/27 Sarah 1996/2/4
在like也可以是用‘-’这个代表是一个字符。使用如下:
SQL> select first_name,hire_date 2 from employees 3 where first_name like '_o%'; FIRST_NAME HIRE_DATE -------------------- ----------- Donald 1999/6/21 Douglas 2000/1/13 John 1997/9/28 Jose Manuel 1998/3/7 Mozhe 1997/10/30 John 1998/2/12 Joshua 1998/4/6 John 1996/10/1 Louise 1997/12/15 Jonathon 1998/3/24 以上是输出first_name第2个字符带有o的雇员名字。
is null 空值表达式的用法:
SQL> select last_name,manager_id 2 from employees 3 where manager_id is null; LAST_NAME MANAGER_ID ------------------------- ---------- King 举一反三取不属于Null的值如下:
SQL> select last_name,manager_id 2 from employees 3 where manager_id is not null; LAST_NAME MANAGER_ID ------------------------- ---------- OConnell 124 Grant 124 Whalen 101 Hartstein 100 Fay 201 Mavris 101 Baer 101 Higgins 101 Gietz 205 Kochhar 100 De Haan 100
第三节
逻辑运算符 and、or、not(not是最高级、其次and、or最低)
and 意思就是而且; or 意思就是或者; not 意思就是不属于。
我们来个and的例子:
SQL> select employee_id,last_name,job_id,salary 2 from employees 3 where salary >=10000 and job_id like '%MAN%'; EMPLOYEE_ID LAST_NAME JOB_ID SALARY ----------- ------------------------- ---------- ---------- 201 Hartstein MK_MAN 13000.00 114 Raphaely PU_MAN 11000.00 145 Russell SA_MAN 14000.00 146 Partners SA_MAN 13500.00 147 Errazuriz SA_MAN 12000.00 148 Cambrault SA_MAN 11000.00 149 Zlotkey SA_MAN 10500.00 7 rows selected
以上的是取employees雇员表中工资大于等于10000而且JOB_ID(头衔)带有MAN的字符。
or的用法如下:
SQL> select employee_id,last_name,job_id,salary 2 from employees 3 where salary >=10000 or job_id like '%MAN%'; EMPLOYEE_ID LAST_NAME JOB_ID SALARY ----------- ------------------------- ---------- ---------- 201 Hartstein MK_MAN 13000.00 204 Baer PR_REP 10000.00 205 Higgins AC_MGR 12000.00 100 King AD_PRES 24000.00 101 Kochhar AD_VP 17000.00 102 De Haan AD_VP 17000.00 108 Greenberg FI_MGR 12000.00 114 Raphaely PU_MAN 11000.00 120 Weiss ST_MAN 8000.00 121 Fripp ST_MAN 8200.00 122 Kaufling ST_MAN 7900.00 123 Vollman ST_MAN 6500.00 124 Mourgos ST_MAN 5800.00 145 Russell SA_MAN 14000.00 146 Partners SA_MAN 13500.00 147 Errazuriz SA_MAN 12000.00 148 Cambrault SA_MAN 11000.00 149 Zlotkey SA_MAN 10500.00 150 Tucker SA_REP 10000.00 156 King SA_REP 10000.00 EMPLOYEE_ID LAST_NAME JOB_ID SALARY ----------- ------------------------- ---------- ---------- 162 Vishney SA_REP 10500.00 168 Ozer SA_REP 11500.00 169 Bloom SA_REP 10000.00 174 Abel SA_REP 11000.00 24 rows selected
这里的or的意思是取employees(雇员表)中工资大于等于10000或JOB_ID(头衔)带有MAN的值。
not的用法:
SQL> select last_name,job_id,salary 2 from employees 3 where job_id not in('IT_PROG','ST_CLERK','SA_REP'); LAST_NAME JOB_ID SALARY ------------------------- ---------- ---------- OConnell SH_CLERK 2600.00 Grant SH_CLERK 2600.00 Whalen AD_ASST 4400.00 Hartstein MK_MAN 13000.00 Fay MK_REP 6000.00 Mavris HR_REP 6500.00 Baer PR_REP 10000.00 Higgins AC_MGR 12000.00 Gietz AC_ACCOUNT 8300.00 King AD_PRES 24000.00 Kochhar AD_VP 17000.00 De Haan AD_VP 17000.00 Greenberg FI_MGR 12000.00 Faviet FI_ACCOUNT 9000.00 Chen FI_ACCOUNT 8200.00 Sciarra FI_ACCOUNT 7700.00 Urman FI_ACCOUNT 7800.00 Popp FI_ACCOUNT 6900.00 Raphaely PU_MAN 11000.00 Khoo PU_CLERK 3100.00 LAST_NAME JOB_ID SALARY ------------------------- ---------- ---------- Baida PU_CLERK 2900.00 Tobias PU_CLERK 2800.00 Himuro PU_CLERK 2600.00 Colmenares PU_CLERK 2500.00 Weiss ST_MAN 8000.00 Fripp ST_MAN 8200.00 Kaufling ST_MAN 7900.00 Vollman ST_MAN 6500.00 Mourgos ST_MAN 5800.00 Russell SA_MAN 14000.00 Partners SA_MAN 13500.00 Errazuriz SA_MAN 12000.00 Cambrault SA_MAN 11000.00 Zlotkey SA_MAN 10500.00 Taylor SH_CLERK 3200.00 Fleaur SH_CLERK 3100.00 Sullivan SH_CLERK 2500.00 Geoni SH_CLERK 2800.00 Sarchand SH_CLERK 4200.00 Bull SH_CLERK 4100.00 Dellinger SH_CLERK 3400.00 LAST_NAME JOB_ID SALARY ------------------------- ---------- ---------- Cabrio SH_CLERK 3000.00 Chung SH_CLERK 3800.00 Dilly SH_CLERK 3600.00 Gates SH_CLERK 2900.00 Perkins SH_CLERK 2500.00 Bell SH_CLERK 4000.00 Everett SH_CLERK 3900.00 McCain SH_CLERK 3200.00 Jones SH_CLERK 2800.00 Walsh SH_CLERK 3100.00 Feeney SH_CLERK 3000.00 52 rows selected
以上就是not的用法,意思是取employees(雇员表)中不属于'IT_PROG','ST_CLERK','SA_REP'(JOB_ID的头衔)列出来。
我们已经知道怎么用第一节和第二节讲到的用法,但是这样都要考虑到优先的,如果你要让“or”比“and”优先的话就得用(),比如如下: SQL> select last_name,job_id,salary 2 from employees 3 where (job_id ='SA_REP' or job_id='AD_PRES') 4 and salary >=10000; LAST_NAME JOB_ID SALARY ------------------------- ---------- ---------- King AD_PRES 24000.00 Tucker SA_REP 10000.00 King SA_REP 10000.00 Vishney SA_REP 10500.00 Ozer SA_REP 11500.00 Bloom SA_REP 10000.00 Abel SA_REP 11000.00 7 rows selected
以上的意思是说:取雇员表(employees)中的JOB_ID为'SA_REP'或'AD_PRES'的值,且在'SA_REP'或'AD_PRES'的值中大于等于10000的值。
第三节介绍:order by子句
order by 按照次序执行,但它分为升序和降序排列,默认情况是按照升序排列;
1.在select的表中order by 是放在最后的。比如以下例子: SQL> select last_name,job_id,department_id 2 from employees 3 order by department_id desc; ------这里是按照(desc)降序排列 LAST_NAME JOB_ID DEPARTMENT_ID ------------------------- ---------- ------------- Grant SA_REP Higgins AC_MGR 110 Gietz AC_ACCOUNT 110 Urman FI_ACCOUNT 100 Faviet FI_ACCOUNT 100 Chen FI_ACCOUNT 100 Popp FI_ACCOUNT 100 Greenberg FI_MGR 100 Sciarra FI_ACCOUNT 100 De Haan AD_VP 90 Kochhar AD_VP 90 King AD_PRES 90 Tuvault SA_REP 80 Livingston SA_REP 80 Taylor SA_REP 80 Hutton SA_REP 80 Abel SA_REP 80 Kumar SA_REP 80 Russell SA_MAN 80 Partners SA_MAN 80
注意:order by 是可以引用别名的,但where子句中是不可以引用别名的。
2.order by 引用别名的例子如下:
SQL> select last_name,salary,salary+300 annsal 2 from employees 3 order by annsal asc; -------这里是按照(asc)升序排列 LAST_NAME SALARY ANNSAL ------------------------- ---------- ---------- Olson 2100.00 2400 Philtanker 2200.00 2500 Markle 2200.00 2500 Landry 2400.00 2700 Gee 2400.00 2700 Perkins 2500.00 2800 Colmenares 2500.00 2800 Patel 2500.00 2800 Vargas 2500.00 2800 Sullivan 2500.00 2800 Marlow 2500.00 2800 OConnell 2600.00 2900 Grant 2600.00 2900 Himuro 2600.00 2900 Matos 2600.00 2900 Mikkilineni 2700.00 3000 Seo 2700.00 3000 Tobias 2800.00 3100 Geoni 2800.00 3100 Atkinson 2800.00 3100
3.order by 后面跟 1、2、3、。。。。意思是以3为例是说按照第3列进行排序。如果大家不明白的话请看下面例子:
SQL> select last_name,job_id,department_id 2 from employees 3 order by 3; -----3代表第3列默认按照升序进行排序 LAST_NAME JOB_ID DEPARTMENT_ID ------------------------- ---------- ------------- Whalen AD_ASST 10 Hartstein MK_MAN 20 Fay MK_REP 20 Raphaely PU_MAN 30 Colmenares PU_CLERK 30 Khoo PU_CLERK 30 Baida PU_CLERK 30 Tobias PU_CLERK 30 Himuro PU_CLERK 30 Mavris HR_REP 40 OConnell SH_CLERK 50 Grant SH_CLERK 50 Weiss ST_MAN 50 Fripp ST_MAN 50 Kaufling ST_MAN 50 Vollman ST_MAN 50 Mourgos ST_MAN 50 Nayer ST_CLERK 50 Mikkilineni ST_CLERK 50 Landry ST_CLERK 50
第四节引用变量 substitution variable
1.引用变量是oracle 客户端做的事,oracle服务器只知道正确的值是多少。
2.变量符号式(&)和(&&)
3.变量符号可以在 where子句、order by子句、column(列)、table_name(表名)之中用到;来个例子如下:
SQL> select employee_id,salary,last_name,department_id 2 from employees 3 where employee_id = &employee_num; -----&employee_num是一个变量; 输入 employee_num 的值: 100 原值 3: where employee_id = &employee_num 新值 3: where employee_id = 100
EMPLOYEE_ID SALARY LAST_NAME DEPARTMENT_ID ----------- ---------- ------------------------- ------------- 100 24000 King 90
如果是字符串的话就要加 ''号 比如以下:
SQL> select last_name,salary,department_id 2 from employees 3 where last_name = &last_name; 输入 last_name 的值: 'Landry' 原值 3: where last_name = &last_name 新值 3: where last_name = 'Landry' ----注意要加''号;
LAST_NAME SALARY DEPARTMENT_ID ------------------------- ---------- ------------- Landry 2400 50
(&&)用法跟(&)有一定的区别看例子如下:
SQL> select &&last_name,salary,department_id 2 from employees 3 where last_name = && last_name; 原值 1: select &&last_name,salary,department_id 新值 1: select 'Landry',salary,department_id 原值 3: where last_name = && last_name 新值 3: where last_name = 'Landry'
'LANDR SALARY DEPARTMENT_ID ------ ---------- ------------- Landry 2400 50
两个(&&)就是会自动是替换不要手动输入两遍。
DEFINE 这个函数可以定义一个变量,如下例子:
SQL> define salary_num = 2600 ---定义salary_num=2600 SQL> select last_name,salary,department_id 2 from employees 3 where salary = &salary_num; &salary_num: 2600 ---这个是因为可以显示出2600的值因为使用了 set verify on|off开关 LAST_NAME SALARY DEPARTMENT_ID ------------------------- ---------- ------------- OConnell 2600.00 50 Grant 2600.00 50 Himuro 2600.00 30 Matos 2600.00 50
|