Oracle基础学习资料

Oracle基础学习

大型关系数据库

system

dhee

://IP:5560/isqlplus

导入数据库脚本：@脚本路径

所有的关系数据对表的操作使用的差不多上SQL语句

杳询表使用SQL语句中的SELECT语句

差不多SELECT语句：

SELECT列名洌名,……|*|算术表达式

FROM表名

-查询employees表中所有职员的last_name,salary

SELECTLAST_NAME,SALARY

FROMEMPLOYEES;

Selectlast_name,salary

Fromemployees;

一查询departments表中所有部门的信息

SELECT*

FROMDEPARTMENTS;

--算术运算符：+,*,!

+：只有加法运算的功能，没有连接作用

/：

SELECT5/2

FROMDUAL;

-查询employees表中所有职员的last_name,job_id,salary,年薪(salary*12)

SELECTLAST_NAMEJOB_ID,SALARY,SALARY*12

FROMEMPLOYEES;

列别名

1.列名列别名

2.列名AS列别名

注意：当列别名要区分大小写，或者列别名中包含了专门字符，或者列别名为关键时，需要

将别名放在一对双引号中

双引号在关系数据库中表示列别名

*/

SELECTLAST.NAME"USER"J0BJD.SALARY,SALARY*12AS"YEARSAL"

FROMEMPLOYEES;

一字符串：一对单引号

SELECT'DHEE'AS名字;大连，AS"city"

FROMDUAL;

-连接符：||

SELECTFIRST_NAME|r，'||LAST_NAMEAS姓名

FROMEMPLOYEES;

-屏蔽查询结果中重复记录：DISTINCT

SELECTDISTINCTJOBJD

PROMEMPLOYEES;

SELECTDISTINCTJOB」D,SALARY

FROMEMPLOYEES;

-过滤与排序

-过滤：WHERE子句

SELECT

FROM

[WHERE条件];

一兖询employees表中50号部门职员的last_name,salary,depar(menl_id

SELECTLAST_NAME,SALARY.DEPARTMENT_ID

FROMEMPLOYEES

WHEREDEPARTMENT_1D=5O;

--查询employees表中所有工资大于10000的职员的Iast_name,job_id,salary

SELECTLAST_NAME,JOB」D,SALARY

FROMEMPLOYEES

WHERESALARY>10000;

一查询employees表中king职员的employee_id,salary,department_id

SELECTEMPLOYEE_ID,SALARY,DEPARTMENT_ID

FROMEMPLOYEES

WHERELAST_NAME='King';

-查询条件为字符串时，查询的值必须放在一对单引号中，同时查询的内容区分大小写

—查询employees表中哪些职员的职位(job_id)为it_prog,显示这些职员的

last_namc,job_id,salary

SELECTLAST_NAME,JOBJD,SALARY

FROMEMPLOYEES

WHEREJOB_ID='IT_PROGr;

SELECTLAST_NAME,HIRE_DATE

FROMEMPLOYEES

WHEREHIRE_DATE=，07-6月-94：

-查询条件为口期类型时，查询的值必须放在一对单引号中，同时口期格式必须为Oracle默

认的日期格式。

-Oracle默认的日期格式为：DD-MON-RR

--查询employees表中97年以后入职的职员的last」iame.hire_date(包含97年)

SELECTLAST_NAME,HiRE_DATE

PROMEMPLOYEES

WHEREHIRE_DATE>='Oi-l月5;

--BETWEEN...AND…:使列大于等于一个值，同时小于等于另一个值1包含边界值)

一前旬employees表中工资在9000到12000的职员的lasl_name,salary

SELECTLAST_NAME,SALARY

FROMEMPLOYEES

WHERESALARYBETWEEN9000AND12000;

-查询employees表中95年到97年入职职员的Iasl_name,hire_dale(包含95年与97年)

SELECTLAST_NAME,HIRE_DATE

FROMEMPLOYEES

WHEREHIRE_DATEBETWEEN'01-1月-95,AND31-12月-97，；

-IN(值列表)：使列与列表中任意一个值进行匹配

一查询employees表中在20,50,90部门「作的职员的last_name,salary,department_id

SELECTLAST_NAME.SALARY,DEPARTMENT_ID

FROMEMPLOYEES

WHEREDEPARTMENT_1DIN(20,50,90);

-查询employees表中职位(job_id)为ic_prog或st_clerk或sa_rep的职员的

last_namejob_id,salary

SELECTLAST_NAME,JOBJD,SALARY

FROMEMPLOYEES

WHEREJOB」DIN('IT_PROG',ST_CLERK',SA_REP');

--LIKE:模糊查询

SELECTLAST_NAME,SALARY,DEPARTMENT_ID

FROMEMPLOYEES

WHEREDEPARTMENT_iD=20ORDEPARTMENT_ID=50ORDEPARTMENT」D=90;

一查询employees表中last_name中包含A(不区分大小写)的职员的last_name

SELECTLAST_NAME

FROMEMPLOYEES

WHERELAST_NAMELIKE'%A%'ORLAST_NAMELIKE'%a%';

一查询employees表中工资在5000-12000之间，同时在20或50号部门工作的职员的

last_name,salary,department_id

SELECTLAST_NAME,SALARY,DEPARTMENT_ID

FROMEMPLOYEES

WHERESALARYBETWEEN5000AND12000ANDDEPARTMENT」DIN(20,50);

SELECTLAST_NAME,SALARY,DEPARTMENT_ID

PROMEMPLOYEES

WHERE(SALARY>=50(i0ANDSALARY<=12000)AND(DEPARTMENT_ID=20OR

DEPARTMENT_ID=50);

一行询employees表中工资不在9000-12000之间的职员的lasl_name,salary

SELECTLAST_NAME,SALARY

FROMEMPLOYEES

WHERENOTSALARYBETWEEN9000AND12000;

一排序：ORDERBY子句

SELECT3

FROMI

[WHERE]2

(ORDERBY排序列]4

升序:ASC,默认为升序

降序:DESC

*/

SELECTLAST_NAME,SALARYAS"sal”

FROMEMPLOYEES

WHERESALARY〉10000

ORDERBY"sal"DESC;

SELECTLAST_NAME,HIRE_DATE

FROMEMPLOYEES

ORDERBYHIRE_DATEDESC;

SELECTLAST_NAME

FROMEMPLOYEES

ORDERBYLAST_NAMEDESC;

1.ORDERBY后面能够放列名

2.ORDERBY后面能够放列别名(注意大小写)

3.ORDERBY后面能够放查询结果中列的序号

*/

一查询employees表中所有职员的lasl_name,job_id,depar【mem_id,salary,结果按部门升序，工

资降序排序

SELECTLAST_NAME,JOBJD,DEPARTMENT_ID,SALARY

FROMEMPLOYEES

ORDERBYDEPARTMENT"ASCNULLSFIRST,SALARYDESC;

SELECTLAST.NAME,JOBJD,DEPARTMENT_ID,SALARY

FROMEMPLOYEES

ORDERBYDEPARTMENT_IDDESCNULLSLAST,SALARYDESC;

-NULLSFIRST/NULLSLAST

--单行函数：

SELECTLAST_NAME,UPPER(LAST_NAME)--将参数的值变为大写

FROMEMPLOYEES；

SELECTLAST_NAME,LOWER(LAST_NAME)—将参数的值变为小写

FROMEMPLOYEES;

--查询employees表中last_name中包含A(不区分大小写)的职员的last_name

SELECTLAST_NAME

FROMEMPLOYEES

WHEREUPPER(LAST_NAME)LIKE'%A%';

SELECTLAST_NAME

FROMEMPLOYEES

WHERELOWER(LAST_NAME)LIKE'%a%';

SELECTINITCAPCabcabc!ABC#ABC$ABC%ABC・・・AABC)--将单词的单字母变为大写

FROMDUAL;

SELECTLAST_NAME,LENGTH(LAST_NAME)

FROMEMPLOYEES;

SELECTLENGTHC中国)-求字符数

FROMDUAL:

SELECTLENGTHBf中国')一求字节数

FROMDUAL;

SELECTLAST_NAME,SUBSTR(LAST_NAME,3)--截取字符串：从第N位开始截取一宜截

取到最后

FROMEMPLOYEES;

SELECTLAST_NAME,SUBSTR(LAST_NAME,3,2)-截取字符串：从第N位开始截取，械取

指定长度的字符串

FROMEMPLOYEES;

SELECTLAST_NAME,SUBSTR(LAST_NAME,-3)-截取字符串：从倒数第N位开始截取一

直截取到最后

FROMEMPLOYEES;

SELECTLAST_NAME,SUBSTR(LAST_NAME,-3,2)--截取字符串：从倒数第N位开始截取,

械取指定长度的字符串

FROMEMPLOYEES;

SELECTLAST_NAME,INSTR(LAST_NAME,'a>-在第一个参数中查找第二个参数首次显现

的位置，没找到返回0

FROMEMPLOYEES；

--显示第一个参数，并用第二个参数指定第一个参数的长度，假如第一个参数的长度不够用

第三个参数在左/右边补齐长度

SELECTLAST_NAME,LPAD(LAST_NAME,20,'$'),RPAD(LAST_NAME,20,'$')

FROMEMPLOYEES;

SELECTLAST_NAME.REPLACE(LAST_NAME：a','「)一将第一个参数中显现的第二个参数

用第三个参数替换掉

FROMEMPLOYEES;

SELECTTRIM('ABCABC')AS”A"—删除字符串左右显现的空格

FROMDUAL;

SELECTTRIM('A'FROM，AAABACAAA>-删除字符串左右显现的指定的字符

FROMDUAL;

--查询employees表中所有职员的last_namc,要求显示的内容中首字母为小写，其它字母均为

大写

SELECTLOWER(SUBSTR(LAST_NAME.1,1))||UPPER(SUBSTR(LAST_NAME,2))

FROMEMPLOYEES;

-查询employees表中last_name中包含a(小写)的职员的last_name(不能使用LIKE)

SELECTLAST_NAME

FROMEMPLOYEES

WHEREiNSTR(LAST_NAME；a')>0;

-查询employees表中last_name中为4个字符的职员的last_name

SELECTLAST_NAME

FROMEMPLOYEES

WHERELAST.NAMELIKE'

SELECTLAST.NAME

FROMEMPLOYEES

WHERELENGTH(LAST_NAME)=4;

一查询employees表中las5ame中包含a或e的职员的:asl」iame

SELECTLAST_NAME

FROMEMPLOYEES

WHERELAST_NAMELIKE'%a%'ORLAST_NAMELIKE'%e%';

SELECTLAST_NAME

FROMEMPLOYEES

WHEREINSTR(LAST_NAME；a')>0ORINSTR(LAST_NAME,'e')>0;

--四舍五入：

SELECTROUND(256.987),ROUND(256.987,2),ROUND(256,987.0),ROUND(256.987,-1)

FROMDUAL;

--截断数字：

SELECTTRUNC(256.987),TRUNC(256.987,2),TRUNC(256.987,0),TRUNC(256.987,-l)

FROMDUAL;

-MOD()：取余数

SELECTMOD(15,2)

FROMDUAL;

SELECTFLOOR(12.I)

FROMDUAL;

SELECTCEIU12.1)

FROMDUAL;

--获得数据库服务器的时刻：SYSDATE,日期+时刻

SELECTSYSDATE

FROMDUAL;

SQLServer:

getDate。:获得系统时刻

MySQL:

currdateO：获得系统口期，没有时刻

now()：获得系统日期+时刻

*/

日期+天数=日期

日期-天数=日期

口期-口期二天数

日期不能加日期

*/

SELECTSYSDATE-100

FROMDUAL;

-查询employees表中所有职员的last_name,hire_date,入职年数

SELECTLAST_NAME,HIRE_DATE,TRUNC((SYSDATE-HIRE_DATE)/365)

FROMEMPLOYEES;

-MONTHS_BETWEEN①1,D2):求两个参数相差的月数

SELECT

LAST_NAME,HIRE_DATE.TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)AS

年数

FROMEMPLOYEES;

—ADD_MONTHS(D,N):在一个日期上加上指定的月数

SELECTLAST_NAME,HIRE_DATE,ADD_MONTHS(HIRE_DATE,35)

FROMEMPLOYEES;

-NEXT_DAY():

SELECTNEXT_DAY(SYSDATE,7)

FROMDUAL;

SELECTNEXT_DAY(SYSDATE,'星期四')

FROMDUAL;

—LAST_DAY():求日期所在月份的最后一天

SELECTLAST_DAY(SYSDATE)

FROMDUAL;

-类型转换：隐式转换，显式转换

-隐式转换：Oracle自动将一个类型转换为另一个类型。

SELECTLAST_NAME,SALARY,DEPARTMENT_ID

FROMEMPLOYEES

WHEREDEPARTMENT_1D='5O';

--显式转换：

/*

数字与字符进行类型转换

日期与字符进行类型转换

数字与日期不能进行类型转换

刊

-TO_CHAR(D,M):将口期类型D按模板M转换为字符类型的值。

SELECTLAST_NAME,HIRE_DATE,TO_CHAR(HIRE_DATE,YYYY-MM-DDDY')

FROMEMPLOYEES;

SELECTLAST_NAME,TO_CHAR(HIRE_DATE：YYYY)

FROMEMPLOYEES;

SELECTTO_CHAR(SYSDATE：DDDDDD')

FROMDUAL；

SELECTTO_CHAR(S¥SDATE,'YYYY-MM-DDHH24:MI:SS')

FROMDUAL;

SELECTLAST_NAME,H1RE_DATE,TO_CHAR(HIRE_DATE：FMYYYY"年”MM"月”DD"日“

DY')

FROMEMPLOYEES;

一查询employees表中所有周三入职的职员的last_name,hire_date(格式为：YYYY-MM-DD

DY)

SELECTLAST_NAME,TO_CHAR(HIRE_DATE,,YYYY-MM-DDDY')

FROMEMPLOYEES

WHERETO_CHAR(HIRE_DATE：DY尸星期三，；

SELECTLAST_NAME,TO_CHAR(HIRE_DATE；YYYY-MM-DDDY')

FROMEMPLOYEES

WHERETO_CHAR(HIRE_DATE.'D')='4';

-TO_CHAR(N,M):将数字类型N按模板M转换为字符类型的值

SELECTTO_CHAR(256.987),

TO_CHAR(256.897.,FM99999.99'),

TO_CHAR(256.987：99,),

TO_CHAR(256.987：9999999999999,),

TO_CHAR(256.897,,FM()(X)()().()0,),

TO_CHAR(256.897,(FM99999.00,),

TO_CHAR(256456.897；FM9,999,999.(X)X

TO_CHAR(256456.897：FM$9,999,999.00,),

TO_CHAR(256456.897；FML9,999,999.00')

FROMDUAL;

一查询employees表中所有5月入职职员的last_name,hiie_date(格式为:1999年1月1日星

期五)，结果按hire_date升序排序。

SELECTLAST_NAME,TO_CHAR(HIRE_DATE,FMYYYY"年"MM"月”DD"曰"DY')AS

IIIRE_DATE

FROMEMPLOYEES

WHERETO_CHAR(HIRE_DATE1MONA5月’

ORDERBY2ASC;

--查询employees表中所有职员的last_name与职员3倍的工资(格式为：$50,000.00)，结果

按工资的降序排序。

SELECTLAST_NAME,TO_CHAR(SALARY*3,'FM$999,999.00)

FROMEMPLOYEES

ORDERBYSALARYDESC；

-TO_DATE(C,M)：依照模板M将字符类型C值转换为日期类型的值

SELECTTO_DATE('l999-10-10'/YYYY-MM-DD*)

FROMDUAL;

SELECTTO_DATE('2021-10-1VYYYY-MM-DD')-SYSDATE

FROMDUAL;

—TO_NUMBER(C,M):依照模板M将字符类型C转换为数字类型的值

SELECT

TO_NUMBERC256.23'),TO_NUMBER('2,256.23','9,999.00'),TO_NUMBER('$2,256.23','$9999.

00')

FROMDUAL;

一当NULL参与到算术运算时结果一定为NULL

—NVL():当第一个参数不为NULL时，返回第一个参数的值，当第一个参数为NULL时，返

回第二个参数的值，两个参数的类型必须一致。

SELECTLAST_NAMESALARY,NVL(TO_CHAR(COMMISSION_PCT),'没有佣金')AS佣

金’(SALARY*12)+(SALARY*12*NVL(COMMISSION_PCT.O))AS年收入

FROMEMPLOYEES;

-NVL2():当第一个参数不为NULL,返回第二个参数，当第一个参数为NULL,返回第三个

参数

SELECTLAST_NAME,NVL2(COMMISSION_PCT,'有佣金没有佣金')

FROMEMPLOYEES;

-查询employees表中所有职员的lasl_name,job_id,salary,以及增加后的工资，假如职员的

job_id为IT_PROG时，增加后的工资为工资为1.2倍，假如job_id为ST_CLERK时，增加

后的工资为工资的1.5倍，假如job_id为SA_REP时，增加后的工资为工资的2倍,其它jub_id

的职员的工资不变。

SELECTLAST_NAME,JOB_ID,SALARY,

CASE

WHENJOB」D=TT_PROG'THENSALARY*1.20

WHENJOB」D二ST_CLERKTHENSALARY*1.50

WHENJOB」D=5A_REPTHENSALARY*2

ELSESALARY

ENDAS增加后工资

FROMEMPLOYEES;

SELECTLAST_NAME,JOB」D,SALARY,

CASEJOB」D

WHEN'IT.PROG'THENSALARY*1.20

WHEN'ST-CLERK'THENSALARY*1.50

WHENSA_REP'THENSALARY*2

ELSESALARY

ENDAS增加后工资

FROMEMPLOYEES;

SELECTLAST_NAMEJOB_ID,SALARY,

DECODE(

JOBJD,

'IT.PROG',

SALARY*1.2,

'ST_CLERK',

SALARY*1.5,

'SA_REP',

SALARY*2,

SALARY

)AS增加后工资

FROMEMPLOYEES;

--多表连接

/*

笛卡尔集：

行数：表行数的乘积

产生笛卡尔集的缘故：没有连接条件或连接条件无效

*/

--等值连接［内连接，简单连接）

--查询所有部门的department_name,city

SELECTDEPARTMENT_NAME,CITY

FROMDEPARTMENTS,LOCATIONS

WHEREDEPARTMENTS.LOCATION_ID=LOCATIONS.LOCATION_ID;

一查询所有职员的last_name,department_name

SELECTLAST_NAME,DEPARTMENT_NAME

FROMEMPLOYEES,DEPARTMENTS

WHEREEMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID;

-表别名:表名表别名

SELECTLAST_NAME,DEPARTMENT_NAME

FROMEMPLOYEESEMRDEPARTMENTSDEPT

WHEREEMP.DEPARTMENT_ID=DEPT.DEPARTMENT_ID;

--杳询职员的last_name,department_id.department_name

SELECTEMRLAST_NAME,DEPT.DEPARTMENT_ID,DEPT.DEPARTMENT_NAME

FROMEMPLOYEESEMP,DEPARTMENTSDEPT

WHEREEMRDEPARTMENT_ID=DEPT.DEPARTMENT_ID;

一兖询职员的lasl_name,job_id,job_lille（jobs表中）

SELECTEMP.LAST_NAME,EMP.JOB_ID,JOB.JOB_TITLE

FROMEMPLOYEESEMPJOBSJOB

WHEREEMP.JOBJD=JOB.JOB_ID;

查询employees表中所有职员的last_nanie,hire_date(格式为:1999-10-10),工龄(只保留整数部

分)，以及奖金，假如职员的工龄在15(不包含15年)年以上，奖金为8倍的工资，假如职

员的工龄大于18(不包含18年)年以上，奖金为10倍的工资，假如职员的工龄在201不

包含20年)年以上，奖金为15倍的工资，假如职员的工龄小于或等于15年，那么没有奖

金显示”此职员不在此次活动范畴内”.

*/

SELECTLAST_NAME,TO_CHAR(HIRE_DATE,'YYYY-MM-DD')AS

HIRE_DATE,TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)AS"工龄"，

CASE

WHENTRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)>20THEN

TO_CHAR(SALARY*15)

WHENTRUNC(M0NTHS_BETWEEN(SYSDATE.HIRE_DATE)/12)>18THEN

TO_CHAR(SALARY*10)

WHENTRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)>15THEN

TO_CHAR(SALARY*8)

ELSE，此职员不在此次活动范畴内，

ENDAS奖金

FROMEMPLOYEES;

SELECTLAST_NAME,TO_CHAR(HIRE_DATE,'YYYY-MM-DD')AS

HIRE_DATE.TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)AS“工龄”，

CASE

WHENTRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)>I5AND

TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)<=18THENSALARY*8

WHENTRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)>18AND

TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)<=20THENSALARY*10

WHENTRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)>20THENSALARY*15

ELSEO

ENDAS奖金

FROMEMPLOYEES;

-节询职员的last_name,job_ti(le,deparlmenl_name,cily

SELECTEMP.LAST_NAME,JOB.JOB_TITLE,DEPT.DEPARTMENT_NAME,LOC.CITY

FROMEMPLOYEESEMRDEPARTMENTSDEPT,LOCATIONSLOCJOBSJOB

WHEREEMP.DEPARTMENT_ID=DEPT.DEPARTMENT」D

ANDDEPT.LOCATION」D=LOC.LOCATION」D

ANDEMP.JOB_ID=JOB.JOB_ID；

SQL:1999

一查询职员的last_name,department_name

SELECTLAST_NAME,DEPARTMENT_NAME

FROMEMPLOYEESEMP,DEPARTMENTSDEPT

WHEREEMP.DEPARTMENT」D二DEPT.DEPARTMENT」D;

SELECTEMP.LAST_NAME,DEPT.DEPARTMENT_NAME

FROMEMPLOYEESEMPINNERJOINDEPARTMENTSDEPT

ONEMP.DEPARTMENT」D二DEPT.DEPARTMENT」D

WHEREEMP.LAST_NAMELIKE'%a%';

一查询职员Wlast_name,job_title,department_name,city

SELECTEMP.LAST_NAME,JOB.JOB_TITLE.DEPT.DEPARTMENT_NAME,LOC.CITY

FROMEMPLOYEESEMRDEPARTMENTSDEPTXOCATIONSLOC,JOBSJOB

WHEREEMP.DEPARTMENT」D=DEPT.DEPARTMENT」D

ANDDEPT.LOCATION_ID=LOC.LOCATION_ID

ANDEMRJOB_ID=JOB.JOBJD;

SELECTEMRLAST_NAME,JOBJOB_TITLE,DEPT.DEPARTMENT_NAME.LOC.CITY

FROMEMPLOYEESEMPJOINDEPARTMENTSDEPTON

EMP.DEPARTMENT_ID=DEPT.DEPARTMENT_ID

JOINJOBSJOBONJOB.JOB_ID=EMP.JOB_ID

JOINLOCATIONSLOCONLOC.LOCATION」D=DEPT.LOCATION」D;

--内连接：查询出满足连接条件的数据

--外连接：查询出满足连接条件与不满足连接条件的数，左外连接、右外连接

--查询所有职员的last_name,department_name

SELECTEMP.LAST_NAME,DEPT.DEPARTMENT_NAME

FROMEMPLOYEESEMPLEFTJOINDEPARTMENTSDEPT

ONEMP.DEPARTMENT」D二DEPT.DEPARTMENT」D;

SELECTEMP.LAST_NAME,DEPT.DEPARTMENT_NAME

FROMEMPLOYEESEMPRIGHTJOINDEPARTMENTSDEPT

ONEMP.DEPARTMENTJD=DEPT.DEPARTMENT_ID;

SELECTEMP.LAST_NAME,DEPT.DEPARTMENT_NAME

FROMEMPLOYEESEMPFULLOUTERJOINDEPARTMENTSDEPT

ONEMPDEPARTMENT」D=DEPT.DEPARTMENT」D;

-ORACLE中独有的外连接:(+)

-左外连接

SELECTLAST_NAME,DEPARTMENT_NAME

FROMEMPLOYEESEMP.DEPARTMENTSDEPT

WHEREEMP.DEPARTMENT_ID=DEPT.DEPARTMENT_ID(+)；

-右外连接

SELECTLAST_NAME,DEPARTMENT_NAME

FROMEMPLOYEESEMP,DEPARTMENTSDEPT

WHEREEMP.DEPARTMENT_ID(+)=DEPT.DEPARTMENT_ID;

-SQLServer:*

-左外连接

SELECTLAST_NAME,DEPARTMENT_NAME

FROMEMPLOYEESEMP.DEPARTMENTSDEPT

WHEREEMP.DEPARTMENT_ID*=DEPT.DEPARTMENT_ID;

-右外连接

SELECTLAST_NAME,DEPARTMENT_NAME

FROMEMPLOYEESEMP.DEPARTMENTSDEPT

WHEREEMP.DEPARTMENT_ID=*DEPT.DEPARTMENT_ID;

-组函数（聚合函数）

—SUM()：求总和

SELECTSUM(SALARY)

FROMEMPLOYEES;

-AVG():求平均值

SELECTAVG(SALARY)

FROMEMPLOYEES;

--MAX()/MIN():求最大值限小值

SELECTMAX(SALARY),MIN(SALARY)

FROMEMPLOYEES;

SELECTMAX(HIRE_DATE),MIN(HIRE_DATE)

FROMEMPLOYEES;

SELECTMAX(LAST_NAME),MIN(LAST_NAME)

FROMEMPLOYEES;

-求50号部门的平均工资，总工资，最高工资与最低工资

SELECTAVG(SALARY),SUM(SALARY),MAX(SALARY),MIN(SALARY)

FROMEMPLOYEES

WHEREDEPARTMENT_ID=50;

-COUNTO

SELECTCOUNT(*)-返回结果的行数

FROMEMPLOYEES

WHEREDEPARTMENT」D=50；

SELECTCOUNT(COMMISSION_PCT)-返I可指定列中不为NULL的值的个数

FROMEMPLOYEES;

--查询employees表中last_name中包含A(不区分大小写)的职员的人数

SELECTCOUNT(*)

FROMEMPLOYEES

WHEREUPPER(LAST_NAME)LIKE'%A%';

SELECT

EMP.LAST_NAME,EMP.JOB_iD,EMP.DEPARTMENT_ID,DEPT.DEPARTMENT_NAME

FROMEMPLOYEESEMPJOINDEPARTMENTSDEPTON

EMP.DEPARTMENT_ID=DEPT.DEPARTMENT_ID

JOINLOCATIONSLOCONLOC.LOCATIONJD=DEPT.LOCATION_ID

WHERELOC.CITY=Toronto';

SELECT

EMP.LAST_NAME,EMRJOB_ID,EMP.DEPARTMENT_ID,DEPT.DEPARTMENT_NAME

FROMEMPLOYEESEMRDEPARTMENTSDEPT.LOCATIONSLOC

WHEREEMP.DEPARTMENT_ID;DEPT.DEPARTMENT」DAND

LOC.LOCATION_ID=DEPT.LOCATION_IDANDLOC.CITY='Toronto';

-创建一个查询显示雇员的last_name并带星号显示他们的月薪，每个星号表示1000美圆。

按薪水降序排序数据。列标签为EMPLOYEES_AND_THEIR_SALARIESo

--查询所有90号部门职员的last_name,salary(格式为：Y50,000.00),city

SELECTEMP.LAST_NAME,TO_CHAR(EMP.SALARY,'FML99.999.00'),LOC.CITY

FROMEMPLOYEESEMPJOINDEPARTMENTSDEPTONEMP.DEPARTMENTJD=

DEPT.DEPARTMENT」D

JOINLOCATIONSLOCONLOC.LOCATION」D=DEPT.LOCATIONJD

WHEREDEPT.DEPARTMENT」D二90;

-查询97年以后入职职员的人数。

SELECTCOUNT(*)

PROMEMPLOYEES

WHEREHIRE_DATE>TO_DATE('1997-12-31','YYYY-MM-DD');

SELECTCOUNT(*)

FROMEMPLOYEES

WHEREHIRE_DATE>'31-12月-97，；

SELECTCOUNT(*)

FROMEMPLOYEES

WHERETO_CHAR(HIRE_DATE,'YYYY')>'1997'；

—GROUPBY子句:分组。统计，报表。

SELECT4

FROM1

(WHERE]2

[GROUPBY]3

[ORDERBY]5

--查询每个部门的最高的工资

SELECTDEPARTMENT」D,MAX(SALARY)

FROMEMPLOYEES

GROUPBYDEPARTMENT」D

ORDERBY1ASC;

SELECTDEPARTMENT」D,MAX(SALARY)

FROMEMPLOYEES

GROUPBYDEPARTMENTJD;

--查询每个职位的平均工资，显示job_id与平均工资

SELECTJOB」D,AVG(SALARY)

FROMEMPLOYEES

GROUPBYJOBJD;

-查询每个部门最高的工资

SELECTMAX(SALARY)

FROMEMPLOYEES

GROUPBYDEPARTMENT」D;

一查询每个部门的人数，显示dcpartmcnt_namc与人数

SELECTDEPT.DEPARTMENT_NAME,COUNT(*)

FROMEMPLOYEESEMPJOINDEPARTMENTSDEPTON

EMP.DEPARTMENT_ID=DEPT.DEPARTMENT_ID

GROUPBYDEPT.DEPARTMENT_NAME;

--查询每个部门的人数，显示dcpartmcnt_id,dcpartmcnt_namc与人数

SELECTEMP.DEPARTMENT」D,DEPT.DEPARTMENT_NAME,COUNT(*)

FROMEMPLOYEESEMPJOINDEPARTMENTSDEPTON

EMP.DEPARTMENTJD=DEPT.DEPARTMENT_ID

GROUPBYEMP.DEPARTMENT_ID,DEPT.DEPARTMENT_NAME

ORDERBY1ASC;

--查询所个部门中每个职位的人数，显示depa「tment_id,jub_id,人数

SELECTDEPARTMENT」DJOB」D,COUNT(*)

FROMEMPLOYEES

GROUPBYDEPARTMENT—ID,JOB」D

ORDERBYIASC,2ASC;

-组函数与NULL值:所有组函数在进行运算时会自动忽略NULL值

SELECTAVG(NVL(COMMISSION_PCT.O))

FROMEMPLOYEES;

-HAVING子句：过滤分组结果，使用HAVING时必须使用GROUPBY.

SELECT5

FROM1

IWHEREJ2

(GROUPBY]3

[HAVING条件]4

IORDERBY]6

-WHERE子句中不能使用组函数作为过滤条件。

WHERE与HAVING的区别:

1.WHERE过滤的是表，HAVING过滤的是分组的结果

2.WHERE中不能使用组函数，HAVING能够使用组函数

*/

--查询平均工资大于10000的部门编号与平均工资，结果按工资降序排序

SELECTDEPARTMENT_ID,AVG(SALARY)

FROMEMPLOYEES

GROUPBYDEPARTMENT—ID

HAVINGAVG(SALARY)>l(X)()0

ORDERBYAVG(SALARY)DESC;

--当使用GROUPBY与DISTINCT时ORDERBY中的列必须在SELECT子句中显现。

--查询最高工资大于6000的职位与最高工资，同时JOB」D中要包含REP

SELECTJOB」D,MAX(SALARY)

FROMEMPLOYEES

WHEREJOBJDLIKE'%REP%'

GROUPBYJOBJD

HAVINGMAX(SALARY)>=6000;

--查询每年入职的人数，显示年份与人数，结果按年份升序排序

SELECTTO_CHAR(HIRE_DATE,'YYYY'),COUNT(*)

FROMEMPLOYEES

GROUPBYTO_CHAR(HIRE_DATE.'YYYY')

ORDERBYTO.CHARCHIRE.DATE/YYYY')ASC;

一查询人数大于或等于3人的部门的dcpartment_id与人数

SELECTDEPARTMENTJD,COUNT(*)

FROMEMPLOYEES

GROUPBYDEPARTMENT_ID

HAVINGCOUNT(*)>=3;

一组函数嵌套：

--查询所有部门的最高的平均工资

SELECTMAX(AVG(SALARY))

FROMEMPLOYEES

GROUPBYDEPARTMENT_ID;

1.组函数嵌套时必须使用GROUPBY子句

2.组函数嵌套时不要在SELECT子句中显现非组函数的列

3.组函数嵌套只能显现在SELECT子句中

*/

--子查询（嵌套查询）:

-查询与144号职员在同一个部门工作的职员的last_name,deparlmenl_id

SELECTDEPARTMENTJD

FROMEMPLOYEES

WHEREEMPLOYEE」D=I44;

-一般子查询：先执行子查询，再执行主查询

SELECTLAST_NAME,DEPARTMENT_ID

FROMEMPLOYEES

WHEREDEPARTMENT_ID=(SELECTDEPARTMENT_ID

FROMEMPLOYEES

WHEREEMPLOYEE」D=144);

-杳询哪些职员的工资大于104号职员的工资，显示这些职员的last_name,salary

SELECTLAST_NAME,SALARY

FROMEMPLOYEES

WHERESALARY>(SELECTSALARY

PROMEMPLOYEES

WHEREEMPLOYEE」D=104);

/*

1.从ORACLE8I以后，除了GROUPBY子句外其它子句都能够使用子查询

2.子查询一定要显现在一对小括号中

3.尽量将子查询放在运算符的右边

4.除非取前N条数据，否则不要在子查询中使用ORDERBY子句。

5.子查询中返回列的个数与类型，必须与主查询中条件的列的个数与类型一致

*/

-查询工资大于公司平均工资的职员的last_name,d叩arlmenl_id,salary

SELECTLAST_NAME,DEPARTMENT」D,SALARY

FROMEMPLOYEES

WHERESALARY>(SELECTAVG(SALARY)

FROMEMPLOYEES);

一查询出入职最早的职员的last_name,hire_date(格式为：1999・1・1)

SELECTLAST_NAME,TO_CHAR(HIRE_DATE,,FMYYYY-MM-DD,)

FROMEMPLOYEES

WHEREHIRE.DATE=(SELECTMIN(HIRE_DATE)

FROMEMPLOYEES);

-查询平均工资大于60号部门最高工资的部门的departmenjid,平均工资

SELECTDEPARTMENTJD,AVG(SALARY)

FROMEMPLOYEES

GROUPBYDEPARTMENT_ID

HAVINGAVG(SALARY)>(SELECTMAX(SALARY)

FROMEMPLOYEES

WHEREDEPARTMENT_ID=60);

--查询人数最多的部门的加partment_id与人数

SELECTDEPARTMENT」D,COUNT(*)

FROMEMPLOYEES

GROUPBYDEPARTMENT—ID

HAVINGCOUNT(*)=(SELECTMAX(COUNT(*))

FROMEMPLOYEES

GROUPBYDEPARTMENT");

--多行子查询：IN,ANY,ALL

-查询工资至少大于60号部门一名职员的工资的职员的last_name,salary

SELECTLAST.NAME,SALARY

FROMEMPLOYEES

WHERESALARYIN(SELECTSALARY

FROMEMPLOYEES

WHEREDEPARTMENT_ID=60);

SELECTLAST_NAME.SALARY

FROMEMPLOYEES

WHERESALARY>ANY(SELECTSALARY

FROMEMPLOYEES

WHEREDEPARTMENT_ID=60)；

SELECTLAST_NAME,SALARY

FROMEMPLOYEES

WHERESALARY>ALL(SELECTSALARY

FROMEMPLOYEES

WHEREDEPARTMENTJD=60);

-在SELECT子句中使用子查询

SELECTLAST_NAME,(SELECTDEPARTMENT_NAMEFROMDEPARTMENTSWHERE

DEPARTMENT_ID=90)

FROMEMPLOYEES;

-查询每个职位的人数以及那个职位人数占公司总人数的百分比，显示job_id,人数,百分比

SELECTJOB_ID,COUNT(*),TRUNC((COUNT(*)/(SELECTCOUNT(*)FROM

EMPLOYEES))*100)11'%'AS百分比

FROMEMPLOYEES

GROUPBYJOB」D;

--伪列:在建表时ORACLE为表自动添加的列。

--ROWID:数据在硬盘或内存中的地址

-ROWNUM:行号,只能小于或小于等于或等于1,否则返回0行数据

SELECTROWNUM.LASTNAME.SALARY

FROMEMPLOYEES

WHEREROWNUM=IO

ORDERBY2DESC;

-查询employees收入最低的前5名

SELECTLAST_NAME,SALARY

FROM(SELECTLAST_NAME,SALARY

FROMEMPLOYEES

ORDERBYSALARYASC)

WHERER0WNUM<=5;

-SQLServer

SELECTTOP5LAST_NAME,SALARY

FROMEMPLOYEES

ORDERBYSALARYDESC;

-MySQL

SELECTLAST_NAME.SALARY

FROMEMPLOYEES

ORDERBYSALARYDESC

LIMIT5;

--查询入职最晚的5名职员的last_name,hire_date(格式为:1999-10-10),salary(格式为：

50,000.00)

SELECTLAST_NAME.HIRE_DATE,SALARY

FROM(SELECTLAST_NAME,TO_CHAR(HIRE_DATE,'YYYY-MM-DD')AS

HIRE_DATE,TO_CHAR(SALARY,'FM99,999.00)ASSALARY

FROMEMPLOYEES

ORDERBYHIRE_DATEDESC)

WHEREROWNUM<=5;

--查询与124号职员在同一年入职的职员的employee_id,las5ame,hire_date(格式为:

1999-10-10)

SELECTEMPLOYEE」D,LAST_NAME,TO_CHAR(HIRE_DATE；YYYY-MM-DD')

FROMEMPLOYEES

WHERETO_CHAR(HIRE_DATE,'YYYY)=(SELECTTO_CHAR(HIRE_DATE；YYYY')

FROMEMPLOYEES

WHEREEMPLOYEE_ID=&INPUT);

-高级子查询：成对子查询，相关子查询

--查询工资与所在部门平均工资相等的职员的last_namedepartment_id,sala「y

SELECTLAST_NAME,DEPARTMENT」D,SALARY

FROMEMPLOYEES

WHERE(DEPARTMENT」D.SALARY)IN(SELECTDEPARTMENT」D,AVG(SALARYj

FROMEMPLOYEES

GROUPBYDEPARTMENT_ID);

一查询各个部门入职最早的职员的last_name,hire_date(格式为：1999-10-10).department_id

SELECTLAST_NAME,TO_CHAR(HIRE_DATE,'YYYY-MM-DD').DEPARTMENT_ID

FROMEMPLOYEES

WHERE(NVL(DEPARTMENTJD.O),HIRE_DATE)IN(SELECT

NVL(DEPARTMENT_ID,O),M1N(HIRE_DATE)

FROMEMPLOYEES

GROUPBYDEPARTMENT」D);

-相关子查询：先执行主查询，再执行子查询

一查询工资大于所在部门的平均工资的职员的lasl_name,department_id,salary

SELECTLAST_NAME,DEPARTMENT」D,SALARY

FROMEMPLOYEESEMP

WHERESALARY>(SELECTAVG(SALARY)

FROMEMPLOYEES

WHEREDEPARTMENT_ID=EMP.DEPARTMENT_ID);

SELECTEMP.LAST_NAME,EMP.DEPARTMENT_ID,EMP.SALARY

FROMEMPLOYEESEMP,

(SELECTDEPARTMENT_ID,AVG(SALARY)ASSALARY

FROMEMPLOYEES

GROUPBYDEPARTMENTSA

WHEREEMP.DEPARTMENT_ID=A.DEPARTMENTJDANDEMP.SALARY>A.SALARY;

--集合运算：井集，交集，补集

-并集：集合+集合

-兖询收入最高与收入最低的职员的last_name,salary

SELECTLAST_NAME,SALARY

FROMEMPLOYEES

WHERESALARY=(SELECTMAX(SALARY)

FROMEMPLOYEES)

UNION

SELECTLAST.NAME,SALARY

FROMEMPLOYEES

WHERESALARY=(SELECTMIN(SALARY)

FROMEMPLOYEES)

ORDERBY2ASC;

SELECTEMPLOYEE」D,LAST_NAME

FROMEMPLOYEES

UNION

SELECTDEPARTMENT」D,DEPARTMENT_NAME

FROMDEPARTMENTS;

SELECTEMPLOYEEJD.LAST_NAME

FROMEMPLOYEES

UNION-去掉重灾值

SELECTEMPLOYEE_ID,LAST_NAME

FROMEMPLOYEES;

SELECTEMPLOYEE_ID,LAST_NAME

FROMEMPLOYEES

UNIONALL-不去重复值

SELECTEMPLOYEE_ID,LAST_NAME

FROMEMPLOYEES;

一查询50号部门同时工资大于3000的职员的lasl_name,salary,department_id

SELECTLAST_NAME,SALARY.DEPARTMENT_ID

FROMEMPLOYEES

WHEREDEPARTMENTJD=50

INTERSECT

SELECTLAST_NAME,SALARY,DEPARTMENT_ID

FROMEMPLOYEES

WHERESALARY>30(X);

SELECTLAST_NAME

FROMEMPLOYEES

WHERELAST_NAMELIKE'%a%'

INTERSECT

SELECTLAST.NAME

FROMEMPLOYEES

WHERELAST.NAMELIKE'%e%';

-补集:集合•集合

/*

A:12345

B:35

A-B=l24

B-A=NULL

*/

--查询收入最高的6/0名职员的las5ame,salary

SELECTLAST_NAME,SALARY

FROM(SELECTLAST.NAME,SALARY

FROMEMPLOYEES

ORDERBYSALARYDESC)

WHEREROWNUM<=10

MINUS

SELECTLAST_NAME,SALARY

FROM（SELECTLAST.NAME,SALARY

FROMEMPLOYEES

ORDERBYSALARYDESC）

WHERER0WNUM<=5

ORDERBYSALARYDESC;

--创建表

/*

CREATETABLE表名（

列名数据类型［（长度）］［约束］,

列名数据类型［（长度）］［约束］,

列名数据类型［（长度）］［约束］,

列名数据类型［(长度)］［约束］

)

*/

一数字类型：NUMBER,长度能够省略

/*

整型：NUMBER(6):整数，同时长度为6位的整数。NUMBER:省略长度，默认为38位的整

数

浮点型：NUMBERS.2):整个数字最大的长度为8位，其中有两位小数

*/

―字符类型：长度不能省略

/*

CHAR(10):AB，储存固定长度的字符串。

VARCHAR2(10):AB,储存可变长度的字符串。

VARCHAR与VARCHAR2的区别？

*/

--口期类型：不能写长度

/*

DATE:口期+时刻

*/

DROPTABLET1;

CREATETABLET1(

T」DNUMBER(5),

T_NAMEVARCHAR2(20),

T_SEXCHAR(ICHAR),

TDATEDATE

);

--复制表:

/*

CREATETABLE表名

AS

SELECT语句

*/

DROPTABLETl;

CREATETABLET1

AS

SELECTLAST_NAME,SALARY,DEPARTMENT」D

FROMEMPLOYEES

WHEREDEPARTMENT」D=50;

一添加数据：INSERTINTO语句

1.向表中添加一行新的数据，并表新行中所有的列赋值

INSERTINTO表名VALUES(值，值，值,……);

INSERTINTOT1VALUES(1,'AA','男',TO_DATE('1999-10-10','YYYY-MM-DD'));

2.向表中添加一行新的数据，并向指定的列中赋值

INSERTINTO表名(列名，列名，列名,……)VALUES(值，值，值,……);

INSERTINTOT1(T_ID.T_NAME)VALUES(2：BB');

INSERTINTOT1(T_NAME,T_ID)VALUES('CC',3);

-修改数据：UPDATE语句

UPDATE表名SET列名=值,列名=值,...(WHERE条件］;

UPDATET1SETT_NAME='ABCWHERET」D=1;

UPDATET1SETT_SEX;女,T_DATE=SYSDATEWHERET_DATEISNULL;

-将employees收入最高的职员的工资改为公司的最低工资

UPDATEEMPLOYEESSETSALARY=(SELECTMIN(SALARY)FROMEMPLOYEES)

WHERESALARY=(SELECTMAX(SALARY)FROMEMPLOYEES);

-删除数据：DELETE语句

DELETE|FROM］表名［WHERE条件］;

DELETET1WHERET_SEX二男；

-删除T1表中T_NAME列中所有的数据

UPDATET1SETT_NAME=NULL;

一约束：

L.主键约束：PRIMARYKEY:不能为