|
|
[size=10.5000pt][size=10.5000pt][size=10.5000pt][size=10.5000pt][size=10.5000pt][size=10.5000pt][size=10.5000pt][size=10.5000pt][size=10.5000pt][size=10.5000pt]一、创建与管理表
创建表的命名规则表名和列名:
必须以字母开头
必须在 1–30 个字符之间
必须只能包含 A–Z, a–z,0–9, _, $, 和 #
必须不能和用户定义的其他对象重名
必须不能是Oracle 的保留字
数据类型
VARCHAR2(size) 可变长字符数据
CHAR(size) 定长字符数据
NUMBER(p,s) 可变长数值数据
DATE 日期型数据
LONG 可变长字符数据，最大可达到2G
CLOB 字符数据，最大可达到4G
RAW (LONG RAW) 原始的二进制数据
BLOB 二进制数据，最大可达到4G
BFILE 存储外部文件的二进制数据，最大可达到4G
ROWID 行地址
(1) 创建CREATE TABLE 语句
必须具备:
CREATE TABLE权限
存储空间必须指定
表名（Table_name）
列名（Column_names）
数据类型（data_type）
尺寸 CREATE TABLE Table_name（Column_names data_type，Column_namesdata_type，Column_names data_type）
如：create table EMP(snoChar(3),sname varchar2(8),Ssex varchar2(2),Sbirthday date,Class Char(5))
EMP 表
+--------+----------+------------+---------+-----------+
| sno | sname | Ssex |Sbirthday| Class |
+--------+----------+------------+---------+-----------+
| | | | | |
+--------+----------+------------+---------+-----------+
复制现有表：create tableemp1 as select * from emp;/*新建emp1表复制emp表数据*/Emp表
Emp1表
+---+----------+----------+-----+
|sno|sname|Ssex|Sbirthday|Class|
+---+----------+----------+-----+
|001| 张三| 26 |1998-01-01|1701 |
+---+-----+----+----------+-----+
Emp1表
+---+-----+----+----------+-----+
|sno|sname|Ssex|Sbirthday |Class|
+---+-----+----+----------+-----+
|001| 张三| 26 |1998-01-01|1701 |
+---+-----+----+----------+-----+
复制现有表并且表为空：createtable emp2 as select * from emp where 1=2;/*emp2为空表*/
Emp表
+---+----------+----------+-----+
|sno|sname|Ssex|Sbirthday |Class|
+---+----------+----------+-----+
|001| 张三| 26 |1998-01-01|1701 |
+---+-----+----+----------+-----+
Emp2表
+---+-----+----+----------+-----+
|sno|sname|Ssex|Sbirthday |Class|
+---+-----+----+----------+-----+
| | | | | |
+---+-----+----+----------+-----+
(2) 列ALTER TABLE 语句
ADD-追加新的列
为新追加的列定义默认值
MODIFY-修改现有的列
DROP COLUMN-删除一个列
RENAME COLUMN···TO···重命名表的一个列名
如：追加新列
ALTER TABLE EMPADD(cno_id VARCHAR2(6))
+--------+----------+------------+---------+-----------+-----------+
| sno | sname | Ssex |Sbirthday| Class | sno_id |
+--------+----------+------------+---------+-----------+-----------+
| | | | | | |
+--------+----------+------------+---------+-----------+-----------+
修改列类型
ALTER TABLE EMP
MODIFY(sno_idVARCHAR2(10))
将sno_id VARCHAR2(6)的类型改成了VARCHAR2(10)
重命名列
ALTER TABLE EMP
RENAME COLUMN sno_id TO id;
+--------+----------+------------+---------+-----------+-----------+
| sno | sname | Ssex |Sbirthday| Class | id |
+--------+----------+------------+---------+-----------+-----------+
| | | | | | |
+--------+----------+------------+---------+-----------+-----------+
删除列ALTER TABLE EMPDROP COLUMN id;
+--------+----------+------------+---------+-----------+
| sno | sname | Ssex |Sbirthday| Class |
+--------+----------+------------+---------+-----------+
| | | | | |
+--------+----------+------------+---------+-----------+
(3) 删除、清空表（DROP TABLE 语句、TRUNCATE TABLE语句、DELETE 语句）
DROP TABLE 语句(删除表)
数据和结构都被删除
所有正在运行的相关事务被提交
所有相关索引被删除
DROP TABLE 语句不能回滚如：DROP TABLE EMP2;(无法回滚该表)
TRUNCATE TABLE语句(清空表)
删除表中所有的数据
释放表的存储空间
TRUNCATE语句不能回滚如:TRUNCATE TABLE EMP1;(无法回滚数据)
DELETE 语句(删除数据)
可以回滚数据
如：delete from emp2;(可添加WHERE条件删除指定数据)
COMMIT：提交事务处理
ROLLBACK：事务处理回退
(4) 修改表名（RENAME···TO···语句）
RENAME···TO···语句
执行RENAME语句改变表, 视图, 序列, 或同义词的名称
必须是对象的拥有者
RENAME EMP1 TOEMP_dept;
二、数据处理
(1) 插入数据(INSERT 语句)
使用这种语法一次只能向表中插入一条数据
为每一列添加一个新值。
按列的默认顺序列出各个列的值。
在 INSERT子句中随意列出列名和他们的值。
字符和日期型数据应包含在单引号中。
INSERT INTOTABLE(column,column,column,column)
VALUES(value,value,value,value)
如：
INSERT INTOEMP(sno,sname,Ssex)
VALUES (70,’YCR’,19)
插入空值
隐式方式:
在列名表中省略该列的值。
INSERT INTO EMP(department_id, sname)
VALUES (30, 'Pur');
+---+----------+----------+file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml2276\wps1.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml2276\wps2.png
|sno|sname|Ssex|Sbirthday|
+---+-----+----+----------+
|30 | Pur | | |
显示方式: 在VALUES 子句中指定空值。
NSERT INTO EMP
VALUES (100, 'Finance', NULL, NULL);
+---+-----+----+----------+
|sno|sname|Ssex|Sbirthday|
+---+----------+----------+
|30 | Pur | | |
+---+-----+----+----------+
(2) 使用脚本插入值 (&)
在SQL 语句中使用 & 变量指定列值。
& 变量放在VALUES子句中。
如：
INSERT INTOdepartments (department_id, department_name,location_id)
VALUES(&department_id, '&department_name',&location);
(3) 从其他表拷贝数据
在 INSERT语句中加入子查询。
不必书写 VALUES子句。
子查询中的值列表应与 INSERT子句中的列名对应
如：
INSERT INTOemp2
SELECT *
FROM emp
WHEREdepartment_id = 90;
(4) 更新数据——又可以成为替换(UPDATE 语句)
可以一次更新多条数据。
UPDATE table
SET column = value
如：
UPDATE employees
SETdepartment_id = 70
WHERE employee_id = 113;
(5) 删除数据(DELETE 语句)
使用WHERE 子句删除指定的记录。
如果省略WHERE 子句，则表中的全部数据将被删除
DELETE FROM table
[WHERE ;
(6) 回滚到存储点(SAVEPOINT)
使用SAVEPOINT 语句在当前事务中创建保存点。
使用ROLLBACK TO SAVEPOINT 语句回滚到创建的保存点。
可以设置多个存储点
如：
SAVEPOINTupdate_done;--设置存储点
ROLLBACK TOupdate_done--回滚到存储点
(7) 提交数据(COMMIT)
数据的改变已经被保存到数据库中。
改变前的数据已经丢失。
所有用户可以看到结果。
锁被释放,其他用户可以操作涉及到的数据。
所有保存点被释放。
(8) 回滚数据(ROLLBACK)
数据改变被取消。
修改前的数据状态被恢复。
锁被释放。
三、约束条件
约束是表级的强制规定
(1) 添加约束
NOT NULL-非空约束注：
添加 NOT NULL 约束
要使用 MODIFY 语句
如：Alter table emp modify(sno Char(3)not null);
UNIQUE -唯一约束，允许出现空值，可以出现多个空值
PRIMARY KEY-不允许为空，且该值不存在
FOREIGN KEY-不允许不存在值
CHECK-定义每一行必须满足的条件
如：
ALTER TABLEemp
ADD CONSTRAINT FOREIGN KEY(manager_id)；
(2) 删除约束
如：
ALTER TABLEemployees
DROP CONSTRAINTemp_manager_fk;
(3) 无效化约束
如：
ALTER TABLEemployees
DISABLECONSTRAINTemp_emp_id_pk;
(4) 激活约束
如：
ALTER TABLEemployees
ENABLECONSTRAINTemp_emp_id_pk;
(5) 查询约束(USER_CONSTRAINTS)
如:
SELECTconstraint_name,constraint_type,
search_condition
FROMuser_constraints
WHEREtable_name ='EMPLOYEES';
(6) 查询定义约束的列(USER_CONS_COLUMNS)
如：
SELECTconstraint_name,column_name
FROMuser_cons_columns
WHEREtable_name ='EMPLOYEES';
以上是对表的设定，
下面是对表数据的查询
四、查询语句(SELECT 语句)
SELECT - 标识列名
FROM - 标识表名
* - 所选表的选择全部列用于日期和数字的算术运算符：
加（+）、减（-）、乘（*）、除（/）
列名别命名：别命名的方法有两种
在列后面紧跟着的地方空格后输入别名
在列后方紧着的地方加入AS关键字然后输入别名
PS：别名使用如果含有空格或者特殊字符并且区分大小写。
1、 连接符
连接符的作用就是使用“||”把列与列连接起来
如：
SELECT‘Hello’|| ‘World’
FROM DUAL
得出结果会是“HelloWorld”
2、 删除重复行（DISTINCT）
在默认情况下查询出的结果会带有重复数据，有时候为了方便统计需要去除重复数据这个时候我们就要用到‘DISTINCT’关键字 ，在select 后面加入 DISTINCT
3、 过滤语句（WHERE）
使用WHERE可以将不符合条件的数据行过滤掉注：WHERE子句紧跟随FROM语句。日期和字符要包含在单引号内，字符的关键字大小敏感，日期格式敏感。
4、 比较运算
通常运用在where中作为查询条件中进行比较
等于（=）、大于（>）、小于（<）、大于等于（>=）、小于等于（<=）、不等于（<>或者!=）、赋值（:=）
5、 两个之间的值（BETWEEN···AND···）
如：
SELECT *
FROM EMPWHERE AGE BETWEEN 15 AND 20
6、 等于值中的任意一个（in(set,set,set)）
如：
SELECT *
FROM EMPWHERE AGE in（15,18,20）
7、 模糊查询（LIKE）
注：在LIKE中模糊查询‘_’表示一个字符，‘%’表示零个或者多个字符。当遇到特殊字符的时候使用‘ESCAPE’关键字
如：模糊查询拥有‘IT\’的信息
SELECT EMP_ID
FROM EMP
WHERE EMP_ID LIKE ‘%IT\%’ESCAPE ‘\’;
8、 空值（IS NULL）、否（NOT）、并且（AND）、或者（OR）
如：查询 EMP中不为空的信息
SELECT *
FROM EMP
WHERE SEX IS (NOT) NULL --（不）为空
例如：查询已经达到结婚年龄的男生
select *
from students
where SAGE >= 23 ANDSSEX = '男'
查询已经达到结婚年龄的男生或女生
select *
from students
where SAGE >=23AND SSEX = '男' OR SAGE >=21 AND SSEX = '女'
在比较运算中每个运算符号都有一个优先顺序的等级说法，等级越在前优先级越高（使用括号可以改变优先顺序）
a. 算术运算符
b. 连接符
c. 比较符
d. IS [NOT] NULL, LIKE, [NOT] IN
e. [NOT] BETWEEN
f. NOT
g. AND
h. OR
9、 排序（ORDER BY）
ORDER BY子句用于SELECT语句的结尾
排序又分为升序（ASC）与降序（DESC）这两种
如：select *
from students
ORDER BY ID ASC
10、 多表查询
使用连接多个表中对应的数据进行连接
SELECT a.column, b.column
FROM table1 a, table2 b
WHERE a.column1 = b.column2;
连接N个表就至少需要N-1个连接条件。
在多表查询中有种连接方式
（1）等值连接：等值数据连接
如：查询当前员工所在的部门
SELECTe.Employee_Id,e.First_Name,d.DEPARTMENT_NAME
FROM employees e,departments d
where e.department_id = d.department_id
（2）非等值连接:
如：查询工资所在等级
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal ANDj.highest_sal;
（3）外连接
在外连接中又分为左连接和右连接以及满外连接
外连接可以查询不满足连接条件的数据
左外连接意思是查询左侧数据不满足条件的行
右外连接意思是查询右侧数据不满足条件的行
满外连接意思是返回两个表中不满足条件的行
如：左外连接
SELECT table1.column, table2.column
FROM table1,table2
WHERE table1.column= table2.column(+);
+------------+----------+-----+
| ID | NAME | AGE |
+------------+----------+-----+
| 1 | | |
+------------+----------+-----+
右外连接
SELECT table1.column,table2.column
WHERE table1.column(+)= table2.column;
FROM table1,table2
+------------+----------+-----+
| ID | NAME | AGE |
+------------+----------+-----+
| | 张三 | 18 |
+------------+----------+-----+
满外连接
SELECT table1.column,table2.column
FROM table1 a
FULL OUTERJOIN table2 b
ON （a.ID =b.ID）;
+------------+----------+-----+
| ID | NAME | AGE |
+------------+----------+-----+
| 1 | | |
| | 张三 | 18 |
+------------+----------+-----+
（4）自连接
自连接就是连接自身的表数据。
例如：查询员工的上级经理是谁
SELECT a.LAST_NAME ||’的上级领导是’b.LAST_NAME
FROM employees a,employees b
Where a.manager_id = b.manager_id
（5）自然连接
使用NATURAL JOIN子句来使两个表中具有相同名字的列作为条件创建等值连接，但是如果只是列名相同数据类型不同的话，会产生错误。
例如：查询学生的各科成绩并且其老师的姓名
select SNO,SNAME AS "学生",SSEX,DEGREE,CNAME,TNAME,PROF,DEPARY
from Student
NATURAL JOIN Score --成绩表
NATURAL JOIN Course --课程表
NATURAL JOIN Teacher --教师表
（6）USING子句
自然匹配可能会会匹配到多个列，所以USING子句是在自然连接的等值连接中作为指定等值连接所需要用的列。
例如：查询学生的各科成绩并且其老师的姓名
select SNO,SNAME AS "学生",SSEX,DEGREE,CNAME,TNAME,PROF,DEPARY
from Student JOIN Score USING(SNO)
JOIN Course USING(CNO)
JOIN Teacher USING(TNO)
（7）ON子句
ON子句也是自然连接中是以具有相同名字的列为连接的条件，ON子句也是语句更具有易读性
例如：查询学生的各科成绩并且其老师的姓名
select a.SNO,a.SNAME AS "学生",a.SSEX,b.DEGREE,c.CNAME,d.TNAME,d.PROF,d.DEPARY
from Student a JOIN Score b ON (a.SNO = b.SNO)
JOIN Course c ON(b.CNO = c.CNO)
JOIN Teacher d ON(c.TNO = d.TNO)
五、函数
(1) 单行函数
1) 字符函数
[size=10.5000pt]1．大小写控制函数
大小写控制函数顾名思义就是控制大小写的作用
转换小写-------LOWER("SQL Course")
转换大写-------UPPER("SQL Course")
转换首字母大写-INITCAP("SQL Course")
如：select LOWER(FIRST_NAME),
UPPER(LAST_NAME),
INITCAP(EMAIL)
from employees
+------------------+----------------+--------------+
|LOWER(FIRST_NAME) |UPPER(LAST_NAME)|INITCAP(EMAIL)|
+------------------+----------------+--------------+
| steven | KING | Sking |
+------------------+----------------+--------------+
[size=10.5000pt]2．字符控制函数
a. 拼接：CONCAT('Hello', 'World')------------HelloWorld
b. 间值：SUBSTR('HelloWorld',1,5)------------Hello
c. 总数：LENGTH('HelloWorld')----------------10
d. 位置：INSTR('HelloWorld', 'W')------------6
e. 左补齐：LPAD(salary,10,'*')---------------*****24000
f. 右补齐：RPAD(salary,10,'*')---------------24000*****
g. 去除：TRIM('H' FROM 'HelloWorld')---------------elloWorld
h. 替换：REPLACE('abcd','b','m')-------amcd
2) 数值
ROUND-----四舍五入
TRUNC-----截断
MOD-------求余
如：select ROUND(3.6415926),
TRUNC(3.1415926),
MOD(9,2)
FROM DUAL;
+-----------------+----------------+--------------+
|ROUND(3.6415926) |TRUNC(3.1415926)| MOD(9,2) |
+-----------------+----------------+--------------+
| 4 | 3 | 1 |
+-----------------+----------------+--------------+
3) 日期
a. 日期的数学运算
在日期上加上或减去一个数字结果仍为日期。
两个日期相减返回日期之间相差的天数。
如：计算现在距离2019年1月1日过了多少个周
SELECT (SYSDATE-TO_DATE('2019-01-01','YYYY-MM-DD'))/7
FRPM DUAL;
b. 日期函数
MONTHS_BETWEEN---两个日期相差的月数
ADD_MONTHS-------向指定日期中加上若干月数
NEXT_DAY---------指定日期的下一个星期 * 对应的日期
LAST_DAY---------本月的最后一天
ROUND------------日期四舍五入
TRUNC------------日期截断
例如：2019年1月1日至今多少天
SELECTMONTHS_BETWEEN(SYSDATE-TO_DATE('2019-01-01','YYYY-MM-DD'))
FRPM DUAL;
2019年1月1日6个月后是什么时候
SELECT ADD_MONTHS(TO_DATE('2019-01-01','YYYY-MM-DD'),6)
FRPM DUAL;
得到的结果是2019-7-1 2019年1月当月的最后一天
SELECT LAST_DAY(TO_DATE('2019-01-01','YYYY-MM-DD'))
FRPM DUAL;
得到的结果是2019-1-31 2019年1月1日至今多少天（四舍五入）
SELECT ROUND(SYSDATE-TO_DATE('2019-01-01','YYYY-MM-DD'))
FROM DUAL;
得到的结果是85 2019年1月1日至今多少天（截取）
SELECT TRUNC(SYSDATE-TO_DATE('2019-01-01','YYYY-MM-DD'))
FROM DUAL;
得到的结果是85虽然上面四舍五入的结果和截取的结果虽然一样但是他们的原理不一样，四舍五入的结果是85.4...，截取的结果就是85
4) 转换函数
TO_CHAR函数对日期的转换
必须包含在单引号中而且大小写敏感。
可以包含任意的有效的日期格式。
日期之间用逗号隔开。
SELECT TO_CHAR(sysdate,‘yyyy-mm-ddhh:mi:ss’)
FROM dual;
TO_DATE 函数对字符的转换
TO_DATE(‘2012年10月29日 08:10:21’,’yyyy“年”mm”月”dd“日”hh:mi:ss’)From dual
5) 通用
适用于任何数据类型，同时也适用于空值a. NVL函数NVL函数是当一个是空值的时候转换为一个已知的值
例如：输出LAST_NAME，department_id，当department_id为null时，显示‘没有部门’。
SELECT LAST_NAME,NVL(department_id,’没有部门’);
FROM EMP; 查询出来的结果如下：
+--------------+--------------------+
| LAST_NAME | department_id |
+--------------+--------------------+
| 张三 | 18 |
| 李四 | 没有部门 |
| 张三 | 20 |
+--------------+--------------------+
b. NVL2 函数
NVL2(expr1, expr2, expr3) ：expr1不为NULL，返回expr2；为NULL，返回expr3。
例如：当查询员工的奖金率，若为空，返回0.01,若不为空，返回实际奖金率+0.015
Select last_name,commission_pct,NVL2(commission_pct,commission_pct+0.015,0.01)AS income
FROM EMP
查询出来的结果如下：
+--------------+--------------------+--------------+
| LAST_NAME | commission_pct | income |
+--------------+--------------------+--------------+
| 张三 | .2 | .215 |
| 李四 | .3 | .315 |
| 刘五 | | 0.01 |
+--------------+--------------------+--------------+
c. NULLIF 函数
NULLIF (expr1, expr2) : 相等返回NULL，不等返回expr1
例如：核对数据
SELECT first_name, LENGTH(first_name) "expr1",last_name,
LENGTH(last_name) "expr2",
NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM emp;
结果如下：
+--------------+----------------+--------------+---------------+------------------+
| first_name | expr1 | last_name | expr2 | result |
+--------------+----------------+--------------+---------------+------------------+
| Zhang | 5 | San | 3 | 5 |
| Li | 2 | Si | 2 | 2 |
| Li | 2 | Wu | 2 | 2 |
+--------------+----------------+--------------+---------------+------------------+
d. COALESCE 函数
COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。而且如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE 。
例如：SELECT last_name,COALESCE(commission_pct, salary, 10) comm
FROM employees
结果如下
+--------------+----------+
| LAST_NAME | comm |
+--------------+----------+
| 张三 | .2 |
| 李四 | 4300 |
| 刘五 | 10 |
+--------------+----------+
6) 条件表达式
使用IF-THEN-ELSE 逻辑
使用方法:CASE 表达式、DECODE 函数
CASE 表达式在需要使用 IF-THEN-ELSE 逻辑时:CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
END
例如：查询部门号为 10,20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍,20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数
select employee_ID,department_id , (case department_id when10 Then 1.1*SALARY
when 20 then 1.2*SALARY
when 30 then 1.3*SALARY
end) AS SALARY
from employees
where department_id in(10,20,30)
查询结果如下：
+--------------+----------------+--------------+
| employee_ID | department_id | SALARY |
+--------------+----------------+--------------+
| 114 | 30 | 14300 |
| 115 | 30 | 4030 |
| 116 | 20 | 3700 |
| 117 | 10 | 3380 |
+--------------+----------------+--------------+
7) 嵌套函数
单行函数可以进行多次嵌套来生成嵌套函数，嵌套函数由内到外的执行顺序
3(2(1(col,arg1),arg2),arg3)
执行顺序由1到3的顺序执行
(2) 分组函数
分组函数作用于一组数据，并对一组数据返回一个值。
[size=10.5000pt]a. 常用分组函数分类：AVG（平均值）、COUNT（总数）、MAX（最大值）、MIN（最小值）、SUM（和）
MAX和MIN、COUNT函数可以对任意数据类型使用
COUNT、AVG不计算空值，DISTINCT 你计算空值不为空
[size=10.5000pt]b. 常用的分组字句：GROUPBY（分组）、HAVING（过滤组函数）
GROUP BY通常用于分组函数作为一个组来进行分类的作用。
因为WHERE不能使用子函数来进行过滤，HAVING可以使用组函数过滤，所以通常分组函数常用HAVING来过滤条件查询
例如：查询公司部门的最大值，最小值，平均值，总和、总数，而且平均工资大于3000
selectMANAGER_ID,MAX(SALARY) "最大值",MIN(SALARY)"最小值"，AVG(SALARY)"平均值",SUM(SALARY) "总和",COUNT(*) "总数"
fromemployees
Having AVG(SALARY)>3000
GroupBy MANAGER_ID
结果如下
+--------------+---------+---------+---------+---------+------+
| employee_ID | 最大值 | 最小值 | 平均值 | 总和 | 总数 |
+--------------+---------+---------+---------+---------+------+
| | 24000 | 24000 | 24000 | 24000 | 1 |
| 100| 17000 | 5800 | 11100 | 155400 | 14 |
| 123| 4000 | 2500 | 3238 | 25900 | 8 |
+--------------+---------+---------+---------+---------+------+
六、子查询
允许把一个查询嵌套在另一个查询当中
子查询会返回一个标量(就一个值)、一个行、一个列或一个表
子查询常出现在FROM语句、WHERE语句以及HAVING语句中
SELECT select_list
FROM table a,(SELECT select_list
FROM table) b
WHERE select_list = (SELECT select_list
FROM table)
HAVING MIN(select_list) < (SELECT MIN(select_list)
FROM employees);
多行子查询
多行比较操作符IN
等于列表中的任意一个
ANY和操作符返回的某个值比较
ALL和子查询返回的所有值比较
ANY和ALL之间的区别是任意一值与所有值得差别
七、SET运算符
SET操作符用于两个或者多个表的对比查询
并集：UNION/UNION ALL
交集：INTERSECT
差距：MINUS
a. UNION 操作符返回两个查询的结果集的并集
例如:SELECT 1,2,3,4,5,7
FROM DUAL
UNION
SELECT 1,2,3,4,6
FROM DUAL
得到的结果是1,2,3,4,5,6,7
b. UNION ALL 操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。
例如:SELECT 1,2,3,4,5,7
FROM DUAL
UNION ALL
SELECT 1,2,3,4,6
FROM DUAL
得到的结果是1,1,2,2,3,3,4,4,5,6,7
c. INTERSECT 操作符返回两个结果集的交集
例如:SELECT 1,2,3,4,5,7
FROM DUAL
INTERSECT
SELECT 1,2,3,4,6
FROM DUAL
得到的结果是5,6,7
d. MINUS操作符：返回两个结果集的差集
例如:SELECT 1,2,3,4,5,7
FROM DUAL
MINUS
SELECT 1,2,3,4,6
FROM DUAL
得到的结果是5,7
在列中可以加入字符，以及空值。
例如：查询老师和学生都为女的信息
select SNO,SNAME,SSEX,'学生'
from Student
where SSEX = '女'
UNION
select TNO,TNAME,TSEX,null
from Teacher
where TSEX = '女'
+---------+----------+---------+----------+
| SNO | SNAME | SSEX | '学生' |
+---------+----------+---------+----------+
| 107 | 王丽 | 女 | 学生 |
| 109 | 王芳 | 女 | 学生 |
| 825 | 王萍 | 女 | |
| 831 | 刘冰 | 女 | |
+---------+----------+---------+----------+
八、视图
视图是一种建立在已有表的基础上的虚表，以便于简化查询，避免重复访问相同数据的作用
[size=10.5000pt]
[size=10.5000pt]a. 创建视图
创建视图使用 CREATE VIEW ···AS···语句插入子查询
子查询可以使复杂的SELECT语句
创建视图时可以在子查询中给列定义别名或者视图名后面定义别名
如：CREATE VIEW viwe_name
AS SELECT column ，column，column
FROM table
或者
CREATE VIEW viwe_name(name,sex,age)
AS SELECT column ，column，column
FROM table
或者
CREATE VIEW viwe_name
AS SELECT column name,column sex，column age
[size=10.5000pt]b. 修改视图
使用CREATE OR REPLACE VIEW ··· AS···子句修改视图
如：CREATE OR REPLACE VIEW 即可创建又可修改
CREATE OR REPLACE VIEW viwe_name
AS SELECT column ，column，column
FROM table
[size=10.5000pt]c. 屏蔽操作视图
使用 WITH READ ONLY 选项屏蔽对视图的DML 操作，这样避免了用户误改视图导致视图出错的情况，但是，使用了WITH READ ONLY 也会到时视图无法更改
如：CREATE OR REPLACE VIEW 即可创建又可修改
CREATE OR REPLACE VIEW viwe_name
AS SELECT column ，column，column
FROM tableWITH READ ONLY
[size=10.5000pt]d. 删除视图
删除视图只是删除视图的定义，并不会删除基表的数据
DROP VIEW view_name;
[size=10.5000pt]e. TOP-N分析
Top-N 分析查询一个列中最大或最小的 n 个值
这个时候我们要用到伪类：ROWNUM
在ROWNUM的条件查询中，我们只能使用<(小于)或者<=(小于等于)，不能使用=(等于)，>(大于)，>=(大于等于)
SELECT [column_list], ROWNUM
FROM (SELECT [column_list]
FROM table
ORDER BY Top-N_column)
WHERE ROWNUM <= N;
如：查询员工表中，工资排名在10-20之间的员工信息。
select *
from(select rownum rn,employee_id,salary
from(selectemployee_id,salary
from employeesorder by salary desc))
where rn <=20 and rn >10
|
|
发表于 2019-10-18 21:29:36