Oracle Database 11g : SQL 基础
第一章：使用SQL SELECT 语句检索数据
使用的表
1、空值
2：列别名
1：定义列别名
2：使用列别名
select FIRST_NAME AS "Name",SALARY*12 AS "Alnry ada" from employees
select FIRST_NAME AS name,COMMISSION_PCT AS comm from employees
3、连接运算符、文字字符串、其它引号运算符和 DISTINCT 关键字的用法
连接运算符
文字字符串
使用文字字符串
select FIRST_NAME || ' is a '|| JOB_ID AS "employees Details" from employees
其它引号(q) 运算符
select DEPARTMENT_NAME || q'[ Departments's Manage id is:]'|| MANAGER_ID AS "DEPARTMENT-DEPARTMENT" from departments;
q'[]'
重复行
select DEPARTMENT_ID from employees;
select distinct DEPARTMENT_ID from employees;
3、显示表结构
1、按住键盘Ctrl键不放，然后使用鼠标的左键来点击想要查看表结构的那一张表的名称，点击之后就会显示表的结构情况了。
或者打开命令窗口，点击上方的文件选项选择新建之后点击命令窗口，如下图中所示。
第二章：对数据进行限制和排序
1、使用以下项对行进行限制： – WHERE 子句 – 使用=、<=、BETWEEN、IN、LIKE 和NULL 条件的 比较条件 – 使用AND、OR 和NOT 运算符的逻辑条件
1：有选择地对行进行限制
select *from employees where DEPARTMENT_ID=90；
符串和日期
select LAST_NAME,HIRE_DATE from employees where HIRE_DATE='17-FEB-04'
使用比较运算符
使用BETWEEN 运算符的范围条件
使用IN 运算符的成员条件
2、使用LIKE 运算符执行模式匹配
3、组合通配符字符
4、使用NULL 条件
5、使用逻辑运算符定义条件
6、使用AND 运算符
select EMPLOYEE_ID,LAST_NAME,JOB_ID, SALARY
from employees where SALARY>=1000 and JOB_ID like '_H%CL%'
select EMPLOYEE_ID,LAST_NAME,JOB_ID, SALARY
from employees where SALARY>=1000 or JOB_ID like '_H%CL%'
7、表达式中运算符的优先级规则
select EMPLOYEE_ID,LAST_NAME,JOB_ID, SALARY
from employees
where JOB_ID ='SA_REP' OR JOB_ID ='AD_PRES' and SALARY>=15000;
select EMPLOYEE_ID,LAST_NAME,JOB_ID, SALARY
from employees
where (JOB_ID ='SA_REP' OR JOB_ID ='AD_PRES') and SALARY>=15000;`
8、使用ORDER BY 子句对行进行排序
select EMPLOYEE_ID,LAST_NAME,JOB_ID, SALARY
from employees
order by EMPLOYEE_ID desc,SALARY desc;
9、替代变量
第三章：使用单行函数定制输出
1：SQL 函数
2：两种类型的SQL 函数
3：单行函数
2：字符函数
1：字符函数
2：大小写转换函数
3：使用大小写转换函数
select EMPLOYEE_ID,FIRST_NAME, DEPARTMENT_ID from employees where LAST_NAME='grant';
select EMPLOYEE_ID,FIRST_NAME, DEPARTMENT_ID from employees where LOWER(LAST_NAME)='grant';
4：字符处理函数
5：使用字符处理函数
Oracle中的instr()函数 详解及应用
1、instr()函数的格式 （俗称：字符查找函数）
格式一：instr( string1, string2 ) // instr(源字符串, 目标字符串)
格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] ) // instr(源字符串, 目标字符串, 起始位置, 匹配序号)
解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。
注：在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。只检索一次，也就是说从字符的开始到字符的结尾就结束。
2、实例
格式一
1 select instr('helloworld','l') from dual; --返回结果：3 默认第一次出现“l”的位置
2 select instr('helloworld','lo') from dual; --返回结果：4 即“lo”同时(连续)出现，“l”的位置
3 select instr('helloworld','wo') from dual; --返回结果：6 即“w”开始出现的位置
格式二
1 select instr('helloworld','l',2,2) from dual; --返回结果：4 也就是说：在"helloworld"的第2(e)号位置开始，查找第二次出现的“l”的位置
2 select instr('helloworld','l',3,2) from dual; --返回结果：4 也就是说：在"helloworld"的第3(l)号位置开始，查找第二次出现的“l”的位置
3 select instr('helloworld','l',4,2) from dual; --返回结果：9 也就是说：在"helloworld"的第4(l)号位置开始，查找第二次出现的“l”的位置
4 select instr('helloworld','l',-1,1) from dual; --返回结果：9 也就是说：在"helloworld"的倒数第1(d)号位置开始，往回查找第一次出现的“l”的位置
5 select instr('helloworld','l',-2,2) from dual; --返回结果：4 也就是说：在"helloworld"的倒数第2(l)号位置开始，往回查找第二次出现的“l”的位置
6 select instr('helloworld','l',2,3) from dual; --返回结果：9 也就是说：在"helloworld"的第2(e)号位置开始，查找第三次出现的“l”的位置
7 select instr('helloworld','l',-2,3) from dual; --返回结果：3 也就是说：在"helloworld"的倒数第2(l)号位置开始，往回查找第三次出现的“l”的位置
select EMPLOYEE_ID,concat(FIRST_NAME,LAST_NAME) NAME,JOB_ID, length(LAST_NAME),instr(LAST_NAME,'a') "adeweizhi" from employees where substr(JOB_ID,4)= 'REP'
第四章：使用转换函数和条件表达式
1：转换函数
2：隐式数据类型转换
3：显式数据类型转换
4：TO_CHAR、TO_DATE、TO_NUMBER 函数
常工作中与date操作关系最大的就是两个转换函数：to_date(),to_char()
多种日期格式：
YYYY：四位表示的年份
YYY，YY，Y：年份的最后三位、两位或一位，缺省为当前世纪
MM：01~12的月份编号
MONTH：九个字符表示的月份，右边用空格填补
MON：三位字符的月份缩写
WW：一年中的星期
D：星期中的第几天
DD：月份中的第几天
DDD：年所中的第几天
DAY：九个字符表示的天的全称，右边用空格补齐
HH，HH12：一天中的第几个小时，12进制表示法
HH24：一天中的第几个小时，取值为00~23
MI：一小时中的分钟
SS：一分钟中的秒
SSSS：从午夜开始过去的秒数
to_char():将日期转按一定格式换成字符类型
5.to_date()：作用将字符类型按一定格式转化为日期类型
具体用法:to_date(’‘2004-11-27’’,’‘yyyy-mm-dd’’),前者为字符串，后者为转换日期格
注意，前后两者要以一对应。
比如：to_date(’‘2004-11-27 13:34:43’’, ‘‘yyyy-mm-dd hh24:mi:ss’’) 将得到具体的时间
select to_date('2005-01-01 ','yyyy-MM-dd') from dual;
select to_date('2005-01-01 08:40:50','yyyy-MM-dd HH24:mi:ss') from dual;
2.to_char()：将日期转按一定格式换成字符类型
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; //日期转化为字符串
select to_char(sysdate,'yyyy') as nowYear from dual; //获取时间的年
select to_char(sysdate,'mm') as nowMonth from dual; //获取时间的月
select to_char(sysdate,'dd') as nowDay from dual; //获取时间的日
select to_char(sysdate,'hh24') as nowHour from dual; //获取时间的时
select to_char(sysdate,'mi') as nowMinute from dual; //获取时间的分
select to_char(sysdate,'ss') as nowSecond from dual; //获取时间的秒
注只能用单引号
使用TO_NUMBER
6：嵌套函数
单行函数可以嵌套到任意深度。嵌套函数的计算顺序是从最内层到最外层。下面的一些示例显示这类函数的灵活性。
SELECT last_name, UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 3), '_US')) FROM employees
7、常规函数： – NVL – NVL2 – NULLIF – COALESCE
7.1 NVL 函数
Oracle中函数以前介绍的字符串处理，日期函数，数学函数,以及转换函数等等，还有一类函数是通用函数。主要有：NVL,NVL2,NULLIF,COALESCE，这几个函数用在各个类型上都可以。
下面简单介绍一下几个函数的用法。
在介绍这个之前你必须明白什么是oracle中的空值null
NVL函数
NVL函数的格式如下：NVL(expr1,expr2)
含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。
其中显示-1的本来的值全部都是空值的
select LAST_NAME,SALARY,NVL(SALARY,-1) from employees
7.2 NVL2函数
NVL2函数的格式如下：NVL2(expr1,expr2, expr3)
含义是：如果该函数的第一个参数为空那么显示第三个参数的值，如果第一个参数的值不为空，则显示第二个参数的值。
select LAST_NAME,SALARY,NVL2(SALARY,1,-1) from employees
上面的例子中。凡是结果是1的原来都不为空，而结果是-1的原来的值就是空。
7.3. NULLIF函数
NULLIF(exp1,expr2)函数的作用是如果exp1和exp2相等则返回空(NULL)，否则返回第一个值。
下面是一个例子。使用的是oracle中HR schema，如果HR处于锁定，请启用
这里的作用是显示出那些换过工作的人员原工作，现工作。
第五章：使用组函数报告聚集数据
5.1： 组函数： – 类型和语法 – 使用AVG、SUM、MIN、MAX、COUNT – 在组函数中使用DISTINCT 关键字 – 组函数中的NULL 值
select avg(salary),max(salary),min(salary),sum(salary) from employees
5.2：使用COUNT 函数
3：使用DISTINCT 关键字
select count(distinct department_id) from employees
5.1：对行进行分组： – GROUP BY 子句 – HAVING 子句
创建数据组：GROUP BY 子句的语法
select department_id,avg(salary) from employees group by department_id;
按多个列进行分组
select department_id,job_id,sum(salary) from employees where department_id >10 group by department_id,job_id order by department_id;
使用组函数的非法查询
第六章：使用联接显示多个表中的数据
6.1联接类型
6.2使用SQL:1999 语法将表联接起来
6.3限定不确定的列名
6.4自然联接： – USING 子句 – ON 子句
select DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID,CITY from departments
natural join locations;
没有相同的列报错。
6.5使用USING 子句创建联接
使用USING指定等值链接的列
select LAST_NAME,PHONE_NUMBER,DEPARTMENT_ID,DEPARTMENT_NAME from employees join departments
using (DEPARTMENT_ID);
6.5表别名用法
select d.DEPARTMENT_NAME,l.city from departments d join locations l
using (LOCATION_ID);
如果在SQL语句中的另一个位置使用了同一列，则不要对其设置其别名
select d.DEPARTMENT_NAME,l.city from departments d join locations l
using (LOCATION_ID)
where location_id>1000
不要对locatio_Id这列以列别名如 l.location方式放在select后面。下面是错误语法
select d.DEPARTMENT_NAME,l.city ,l.location_id from departments d join locations l
using (LOCATION_ID)
where location_id>1000
6.5使用ON 子句创建联接
使用ON 子句创建三向联接
select FIRST_NAME,SALARY,DEPARTMENT_NAME,CITY from employees e
join departments d
on e.DEPARTMENT_ID=d.DEPARTMENT_ID
join locations l
on l.location_id=d.location_id
对联接应用附加条件
6.6内连接和左外连接、右外连接
左外连接：左表不加限制，保留左表的数据，匹配右表，右表没有匹配到的行中的列显示为null。
右外连接：右表不加限制，保留右表的数据。匹配左表，左表没有匹配到的行中列显示为null。
第七章：使用子查询来解决查询
select LAST_NAME,JOB_ID,SALARY
from employees
where salary =(select min(SALARY) from employees)
7.1带有子查询的HAVING 子句
:7.2多行子查询
select LAST_NAME, SALARY,JOB_ID,SALARY from employees
where salary < any (select salary from employees where job_id ='IT_PROG')
and JOB_ID<> 'IT_PROG'
第八章：使用集合运算符
• UNION和UNION ALL运算符
• INTERSECT运算符
• MINUS运算符
• 匹配SELECT语句
• 在集合运算中使用ORDER BY子句
集合运算符用于将两个或更多查询的结果合并成一个结果。包含集合运算符的查询被称为 复合查询。
所有集合运算符都具有相同的优先级。如果SQL 语句包含多个集合运算符，在没有使用 括号明确地指定其它顺序时，Oracle Server 会从左（上）到右（下）对这些运算符进行 计算。在将INTERSECT运算符与其它集合运算符配合使用的查询中，应使用括号明确 地指定计算的顺序。
集合运算符准则
• SELECT列表中的表达式在数量上必须匹配。
• 第二个查询中每一列的数据类型必须与第一个查询中对应列的数据类型相匹配。
• 可以使用括号更改执行顺序。
• ORDER BY子句只能出现在语句的末尾。
8.1使用UNION运算符
显示所有雇员的当前职务和先前职务的详细信息。每位雇员 仅显示一次。
UNION运算符会删除所有重复记录。如果出现在EMPLOYEES表和JOB_HISTORY表中 的记录是相同的，则这些记录只显示一次。在示例显示的输出中，会看到由于每行中 的JOB_ID不同，EMPLOYEE_ID为200 的雇员的记录出现了两次。
select EMPLOYEE_ID,JOB_ID,DEPARTMENT_ID
from employees
union
select EMPLOYEE_ID,JOB_ID,DEPARTMENT_ID from job_history
在前面的输出中，雇员200 出现了三次。为什么？请注意雇员200 的DEPARTMENT_ID 值。一行的DEPARTMENT_ID为90，另一行为10，第三行为90。由于职务ID 和部门ID 的组合是唯一的，所以雇员200 的每行都是唯一的，因此没有被认为是重复行。请注意， 输出按SELECT子句的第一列（在此例中，为EMPLOYEE_ID）的升序进行排序。
8.1UNION ALL运算符
显示所有雇员的当前部门和先前部门。
select EMPLOYEE_ID,JOB_ID,DEPARTMENT_ID from employees
union all
select EMPLOYEE_ID,JOB_ID,DEPARTMENT_ID from job_history
order by DEPARTMENT_ID
使用UNION ALL运算符
在此示例中，选择了117 行。在两个表中共选择了117 行。UNION ALL运算符不会删除 重复行。UNION将返回由任一查询选定的所有不同行。而UNION ALL将返回由任一 查询选定的所有行，包括所有重复行。现在使用UNION子句编写 该查询：
上面的查询会返回116 行。这是因为此查询删除了后面一行（因为该行是重复行）：
8.2INTERSECT运算符
INTERSECT不会忽略NULL值。
显示符合以下条件的雇员的雇员ID 和职务ID：这些雇员的 当前职务与以前的职务相同，也就是说这些雇员曾担任过别 的职务，但现在又重新担任了以前的同一职务。
在示例中，查询只返回在两个表的选定列中具有相同值的那些记录。
如果将DEPARTMENT_ID列添加到EMPLOYEES表的SELECT语句，将 DEPARTMENT_ID列添加到JOB_HISTORY表的SELECT语句，然后运行此查询，会
产生什么结果？由于引入的其它列的值可能重复，也可能不重复，因此结果可能不同。
由于EMPLOYEES.DEPARTMENT_ID值与JOB_HISTORY.DEPARTMENT_ID值不相同， 所以结果中不再包含雇员200。
8.2MINUS运算符
显示从未更换过职务的雇员的雇员ID。
select EMPLOYEE_ID from employees
intersect
select EMPLOYEE_ID from job_history
在示例中，用EMPLOYEES表中的雇员ID 减去JOB_HISTORY表中的雇员ID。
结果集中显示运行减法运算后剩下的雇员，这些雇员由存在于EMPLOYEES表中但并不存 在于JOB_HISTORY表中的那些行代表。这些行记录了从未更换过职务的雇员。
8.3匹配SELECT 语句
• 使用UNION运算符可显示位置ID、部门名称和所在的 省/市/自治区。
• 如果某些列不存在于一个表或另一个表中，则必须使用 TO_CHAR函数或任何其它转换函数匹配数据类型。
Department不在locations表中，state_province不在departments表中
SELECT location_id, department_name "Department", TO_CHAR(NULL) "Warehouse location" FROM departments
UNION
SELECT location_id, TO_CHAR(NULL) "Department", state_province FROM locations;
由于查询的SELECT列表中的表达式必须在数量上相匹配，因此可以使用伪列和数据类 型转换函数来遵循此规则 。在示例中，已将名称Warehouse location指定为伪列
标题。在第一个查询中使用了TO_CHAR函数，以匹配由第二个查询检索的 state_province列的VARCHAR2数据类型。同样，第二个查询中的TO_CHAR函数
用于匹配由第一个查询检索的department_name列的VARCHAR2数据类型。
使用UNION运算符可显示所有雇员的雇员ID、职务ID 和 薪金。
匹配SELECT语句：示例
EMPLOYEES和JOB_HISTORY表具有多个共同的列（例如，EMPLOYEE_ID、JOB_ID 和DEPARTMENT_ID）。但是，如果知道薪金仅存在于EMPLOYEES表中，而且要在 查询中使用UNION运算符来显示雇员ID、职务ID 和薪金，这时又该如何操作？
示例中将EMPLOYEES表和JOB_HISTORY表中的EMPLOYEE_ID列和 JOB_ID列相匹配。将文字值0添加到JOB_HISTORY SELECT语句中，可匹配 EMPLOYEES SELECT语句中SALARY列的数值。
在显示结果中，对应于JOB_HISTORY表中的一条记录的每个输出行在SALARY 列中都包含一个0。
SELECT employee_id, job_id,salary FROM employees
UNION
SELECT employee_id, job_id,0 FROM job_history ORDER BY 2;
在集合运算中使用ORDER BY子句
• ORDER BY子句只能在复合查询的末尾出现一次。
• 这些查询中不能包含多个ORDER BY子句。
• ORDER BY子句仅识别第一个SELECT查询中的列。
• 默认情况下，使用第一个SELECT查询中的第一列按升序对输出进行排序。
ORDER BY子句只能在复合查询中使用一次。如果使用ORDER BY子句，则必须将其放 在查询的末尾。ORDER BY子句接受列名或别名。默认情况下，输出按第一个SELECT 查询的第一列的升序进行排序。
注：ORDER BY子句不能识别第二个SELECT查询中的列名。为避免列名混淆，通常的 做法是对列位置执行ORDER BY。
例如，在下面的语句中，输出将按job_id的升序显示。
如果忽略ORDER BY，则默认情况下输出将按employee_id的升序进行排序。不能 使用第二个查询中的列对输出进行排序。
第九章：处理数据
9.1：在表中添加新行 – INSERT 语句
INSERT 语句语法
插入新行
插入带有空值的行
insert into departments (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)
values(75,'Publicadadad',null,null);
commit
插入特殊值
插入特定日期和时间值
insert into employees (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
values(10,'daj','aijun','qq.com','5669',to_date('FEB 3,1999','MON DD,YYYY'),'AC_ACCOUNT',7800,NULL,205,110);
commit
从其它表中复制行
9.2更改表中的数据 – UPDATE 语句
update sales set LAST_NAME='daj' where EMPLOYEE_ID=198;
commit
select * from sales where LAST_NAME='daj
9.3使用子查询更新两列
update employees
set JOB_ID=(select JOB_ID from employees where employee_id=205),
SALARY=(select SALARY from employees where employee_id=205)
where employee_id=113
commit
9.4根据另一个表更新行
update sales
set DEPARTMENT_ID=(select DEPARTMENT_ID from employees where employee_id=100)
where JOB_ID=(select JOB_ID from employees where employee_id=200)
9.5从表中删除行： – DELETE 语句 – TRUNCATE 语句
9.5:使用COMMIT、ROLLBACK 和SAVEPOINT 执行数据库 事务处理控制
第十章：使用DDL 语句创建和管理表
10.1：数据库对象
10.2：命名规则
10.3:CREATE TABLE 语句： – 访问另一个用户的表 – DEFAULT 选项
10.4:数据类型
10.4日期时间数据类型
10.5约束条件概览：NOT NULL、UNIQUE、PRIMARY KEY、 FOREIGN KEY、CHECK 约束条件
第十一章：创建其它方案对象
1、视图
create or replace view empvu80
(id_number,name,sal,department_id)
as select EMPLOYEE_ID,FIRST_NAME||LAST_NAME,SALARY,DEPARTMENT_ID
from employees
where DEPARTMENT_ID='50'
select * from empvu80
create or replace view dept_sum_vu
(name,minsal,maxsal,avgsal)
as select d.department_name,min(e.salary),max(e.salary),avg(e.salary)
from employees e join departments d
on e.department_id=d.department_id
group by d.department_name
select * from dept_sum_vu
2：序列概览： – 创建、使用和修改序列 – 高速缓存序列值 – NEXTVAL 和CURRVAL 伪列
2. 1：CREATE SEQUENCE 语句：语法
2：创建序列
create sequence dept_deptid_seq
increment by 10
start with 120
maxvalue 9999
nocache
nocycle
create sequence dept_deptid_seq
increment by 10
start with 120
maxvalue 9999
nocache
nocycle
select dept_deptid_seq.nextval from dual ;
select dept_deptid_seq.currval from dual
create sequence dept_deptid_seq
increment by 10
start with 190
maxvalue 9999
nocache
nocycle
select dept_deptid_seq.nextval from dual ;
select dept_deptid_seq.currval from dual
insert into departments (DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID)
values (dept_deptid_seq.nextval,'ADC','2500')
commit
3:修改序列
4:索引概览 – 创建、删除索引
5、创建索引
4:同义词概览 – 创建、删除同义词
第十二章：控制授权
第十三章：管理方案对象
1：使用ALTER TABLE 语句添加、修改或删除列
1：ALTER TABLE 语句
2:添加列
3:修改列
4:删除列
2:管理约束条件： – 添加和删除约束条件 – 延迟约束条件 – 启用和禁用约束条件
添加约束条件
11：重命名表列和约束条件
3：创建索引： – 使用CREATE TABLE 语句 – 创建基于函数的索引 – 删除索引
2：CREATE INDEX 与CREATE TABLE 语句 配合使用
create table new_emp
(employee_id NUMBER(6)
Primary key using index
(create index emp_id_idx on new_emp(employee_id)),
first_name varchar2(20),
last_name varchar2(25));
commit
select index_name,table_name
from user_indexes
where table_name='NEW_EMP'
4：执行闪回操作
2：使用FLASHBACK TABLE 语句
create table employees123 as select * from employees123
commit
drop table employees123
select * from recyclebin
flashback table employees123 to before drop
第十四章：使用数据字典视图管理对象
1：数据字典
2：数据字典结构
3：如何使用字典视图
4：USER_OBJECTS 和ALL_OBJECTS 视图
5：USER_OBJECTS 视图
2：在字典视图中查询以下信息： – 表信息 – 列信息 – 约束条件信息
3：在字典视图中查询以下信息： – 视图信息 – 序列信息 – 同义词信息 – 索引信息
1：视图信息
select * from departments
select * from locations
select * from countries
select * from regions
select DEPARTMENT_NAME,city from departments natural join (select l.LOCATION_ID,l.CITY,l.COUNTRY_ID from locations l join countries c on l.COUNTRY_ID=c.COUNTRY_ID
join regions using(REGION_ID) where REGION_NAME='Europe');
select DEPARTMENT_NAME,city from departments natural join (select l.LOCATION_ID,l.CITY,l.COUNTRY_ID from locations l join countries c on l.COUNTRY_ID=c.COUNTRY_ID
join regions r on c.REGION_ID=r.REGION_ID where REGION_NAME='Europe')
204
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
