🍻 正在加载今日诗词....

Oracle数据库相关16届（已整理完）

阅读数： 次
原创 字数统计: 8.1k字   |   预计阅读时长≈ 34分钟

本文主要介绍Oracle数据库的相关知识。

cmd中的基本语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
sqlplus/nolog #连接数据库
conn/as sysdba
create user ctq identified by ok; #identified by 后面为登录密码
conn ctq/ok; #报错，缺失登录权限CREATE SESSION
conn/as sysdba
grant create session to ctq;
conn ctq/ok;
create table student(id number(4),name varchar2(8)); #权限不足，没有表空间
conn/as sysdba
grant create table to ctq;
conn ctq/ok;
create table student(id number(4),name varchar2(8)); #对表空间‘USERS’无权限
conn/as sysdba
alter user ctq quota（分配表空间大小） unlimited on users; #用户名已更改
conn ctq/ok;
create table student(id number(4),name varchar2(8)); #表已创建
drop user ctq cascade; #级联删除
#若一个用户没有指定表空间，那么用户会创建在user的默认表空间
#已创建一行（一次性只能添加一条数据，后面在子插入时可以插入多条数据）
insert into student(id,name) values(1,'独孤');
select * form student;
drop user ctq (cascade); #删除用户

数据库基本理论

1
2
3
4
5
6
7
8
#数据库类型：
	#关系型数据库：（Oracle、DB2、SQL Server、MySQL、Sybase）
		#基于关系模型提出的数据库叫关系型数据库
		#什么是关系模型：
			#有行和列组成的二维数据表
		#表的组成：
			#行：一条记录
			#列：字段

权限&用户&角色

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
#删除用户
	drop user ctq [cascade]
	#注意：cascade:先删除用户下的所有对象然后再删除用户
	#当前登录的用户不能够被删除
#权限
	#登录：create session
	#建表权限：create table
	#最高权限：dba
        #1.创建用户
	create urser ctq identified by ok;
        #2.授予登录权限
	grant create session to ctq;
        #3.收回权限
	revoke create session from ctq;
	
#角色
	 #作用：可以通过角色给用户授权（相当于用户与权限之间的桥梁）
	 #前提：先给角色授予相关权限，再通过角色给用户授权
         #1.创建角色
	create role r1;	
     	 #2.给角色授权（grant 权限列表 to 角色列表）
	grant create session,create table to r1;
         #3.通过角色给用户授权（grant 角色列表 to 用户列表）
	grant r1 to ctq;
         #4.通过角色收回权限
	revoke r1 from ctq;
         #5.从角色中收回权限
	revoke create session,create table from r1;
         #6.删除角色
	drop role r1;
	
	#with admin option:用户可以将自己的权限授予其他用户
         #1.给用户登录权限
	grant create session to ctq with admin option; 
         #2.创建另一个用户
	create user ctq2 identified by ok;
         #3.登录ctq给ctq2授权
	grant create session to ctq2; 
         #4.收回ctq用户中的登录权限
	revoke create session from ctq;
		 #但是ctq2中的登录权限不会被收回

创建和维护表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
#（重要）数据定义语言：（Data Definition Language，DDL）
#是SQL语言几种负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法组成

#字符类型：
	#char：固定长度字符型数据，长度的大小以字节为单位，默认最小长度为1，最大长度为2000（以固定长度占用内存空间）
	#varchar2：可变长度，最小长度为1，最大长度为4000（不占用多余的空间，以实际长度占用内存空间）
	#clob：可变长度，最大可存储4G数据
#数值类型：（number）
	#注意：根据参数区分存储怎样的值
	       #1.number：既可以表示整数，也可以表示小数，范围-10的125次方到10的126次方
	       #2.number(n)：只能存储整型。例如number(3)，-999到999
	       #3.number(p,s)：存储小数，p代表总长度，包括小数位数，s代表小数位数。例如number(5,2),-999.99到999.99
		   #p的范围1到38，s的范围-84到127
		#-s>0：只能表示小数，四舍五入到s位，有效位<=p
		#-s<0：只能表示整数，并且精确到小数点左边s位，有效位数<=p+|s|。
			#例如number(5,-2),1234567->1234600,12345.345->12300
		#-s=0：只能表示整数
		#-p<s：只能表示绝对值小于1，小数点后s-p为必须为0，保留s位小数。例如number(2,3)：0.012
#日期类型：
	#date：精确到年月日时分秒
	#timestamp：9i后新制的，精确到毫秒
#图片类型：
	#blob：最大可存储4G二进制的数据，可存储图片、声音、文件


#创建表：
	create table table1(
	        id number(4),
	        name varchar2(8)，
	        birthday date,
	        weight number(5,2),
	        country_code char(2) default '01'
	);

#通过子查询创建表：（可以用来复制表）
	create table emp1
	as
	select id,ename from emp;
	
	create table emp1
	as
	select * from emp
	where 1=0; #（条件不成立，只会创建表结构）

	create table emp1
	as
	select * from emp
	where 1=1; #（条件成立，数据全部复制，默认情况为条件成立）
       
     	#含有表达式需要取别名
	create table emp1
	as
	select empno,ename,sal,sal+100 newsalary
	from emp;

#引用另一个用户中的表（用户名.表名）
	select * from scott.empl;

#修改表
	#1.添加列：
	       alter table dossier
	       add(gender varchar2(2) default '男');  #新添加的列在表的最后       
	#2.修改列：
           alter table dossier 
	       modify (gender varchar2(4)); #要比之前的大
	#3.删除列
	       alter table dossier
	       drop(gender);

#删除表
	drop table dossier;

#查表
	select * from dossier;

#drop   delete   truncate 区别
	#drop：数据库中的对象通过drop删除，可删除表，用户
	#truncate：删除表中数据，所有的，释放存储空间，没有事务管理
	#delete：删除表中数据，可以根据条件值删除某几条数据，不释放存储空间，有事务管理

#重命名：重命名表名
	rename dossier to dossier1;

简单的查询语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
#结构化查询语言
	#1.数据查询语言（DQL:Data Query Language）：语句主要包括SELECT，用于从表中检索数据。

	#2.数据操作语言（DML：Data Manipulation Language）：语句主要包括INSERT，UPDATE和DELETE，用于添加，修改和删除表中的行数据。

	#3.事务处理语言（TPL:Transaction Process Language）： 语句主要包括COMMIT和ROLLBACK，用于提交和回滚。

	#4.数据控制语言（DCL:Data Control Language）：语句主要包括GRANT和REVOKE，用于进行授权和收回权限。

	#5.数据定义语言（DDL:Data Definition Language）：语句主要包括CREATE、DROP、ALTER，用于定义、销毁、修改数据库对象。

#查询语言
	#1.*代表所有的列
	#2.select后面可以指明具体的列
	#3.from：具体的表
	#4.子句：
		select id，name
		from ctq；
	#每个子句占据一行


#算数运算符
	#使用场景：在select子句后面对相关列的操作
	#注意点：算数运算符改变的是输出结果而不是基表中的数据

#改变优先级
	select empno，sal，sal*12+100
	from emp；
	#先乘除，再加减，改变优先加括号

#空值null：
	#注意：空值不能参与任何运算，运算结果都是null
	#通过函数nvl()处理函数，只能处理空值，指定为一个数字
		select  empno，sal，nvl（sal，0）+100
		from emp；

#列别名
	#重新给列起一个名字：只是一个输出列，通常在表达式上
		#1.直接起别名
			select ename 姓名
			from emp；
		#2.通过as起别名
			select ename as 姓名
			from emp；
		#3.别名的3种特殊情况
			#3.1 列别名中有空格（用双引号）
				select ename “ne wname”，job newjob
				from emp；
			#3.2 列别名中包含大小写（用双引号）
				select ename “newName”，job “newJob”
				from emp	；	
			#3.3 列别名中有特殊字符
				select ename “name%”
				from emp；

#连接操作符
	#||，列和列之间的连接，列和字符之间的连接
		select ename，sal，ename||sal
		from emp；

#原义字符串
	#可以把字符作为表中的输出结果
		select ename ||'is a'||job
		from emp；

#消除重复行：distinct
	select distinct deptno 
	from emp

限制数据和对数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
#限制数据和对数据的排序
	#限制条件通过where子句来实现
	#where子句的写法：where 列名 比较操作符 比较的值：sal>3000

		#特殊比较操作符：<>不等于
	#1.字符类型比较
		select ename，sal，deptno
		from emp
		where ename=‘tom’；
		#注意：1.字符比较用单引号   2.和表中的值一样，区分大小写
	#2.数值比较
		select ename，sal，comm
		from emp
		where sal>3000；
	#3.日期类型的比较
		select ename，sal，hiredate
		from emp
		where hiredate>to_date（‘1981-12-01’，‘yyyy-mm-dd’）；
	#4.<>的比较
		select ename，hiredate，sal
		from emp
		where hiredate<>to_date（‘1981-12-03’，‘yyyy-mm-dd’）

		#特殊比较运算符：
	#1.between and：
		#注意：包含上下限
	        #1.1 数值类型：
		select ename，sal，job
		from emp
		where sal between 3100 and 4800；
	        #1.2 字符比较
		select ename，sal，job
		from emp	
		where ename between ‘AROMANO’ and ‘MILLER’；#按照首字母字典的顺序
	        #1.3 日期比较
		select ename,sal,job
		from emp
		where hiredate between to_date('1981-01-01'，'yyyy-mm-dd')and to_date('1991-01-01'，'yyyy-mm-dd')；
	#2.in的比较
	      	#比较和in集合列表中的任何一个值相等
        	#2.1 数值类型
		select ename,sal
		from emp
		where sal in(3100,4800,1);
	        #2.2 字符比较
		select ename，sal
		from emp
		where ename in('tom','tom_ab','dd');
	        #2.3 日期类型的比较：DD-MON-RR（Oracle默认支持该格式）
		select ename,sal
		from emp
		where hiredate in('03-2月-97','02-4月-81');
	#3.like：模糊查询满足部分匹配
	      	#两个通配符：
			#1._：代表任意一个字符
			#2.%：代表任意多个字符
		select ename，sal
		from emp
		where ename like ‘%A%’；#只要包含A的就返回数据，没有具体的位置
			            ‘_A%’；#第二个字母必须是A
			            ‘%A_’；#倒数第二个必须是A
		#转义符：escape
		select ename，job
		from emp	
		where job like‘MAN\_%’ escape‘\’；
		#转义符号前后保持一致，例如@也可以，&也可以，但它本身是一个变量，会让输入一个值
	#4.is null检索数据为空
		select sal，comm	
		from emp	
		where comm is null；#is not null（非空）
		
	#逻辑运算符
	#1.and：逻辑与：
		#两个标答是都为true，整个表达式为true
		#如果有一个为false，则整个表达式为false
			select ename，job
			from emp
			where sal>1000
			and job=‘clerk’
	#2.or：逻辑或：
		#只要有一个为true，则整个表达式为true
		#两个为false，整个表达式为false
	#3.not：逻辑非，取反
		#应用场景：
			#not between ... and
			#not like
			#is not null
			#not in

#运算符的优先级（可以通过括号来改变优先级）
	#1.算术运算符：*，\，+，-
	#2.连接运算符：||
	#3.比较运算符：=，<>，<，>，<=，>=
	#4.特殊比较运算符：between .. and...，in，like，is null
	#5.逻辑非：not
	#6.逻辑与：and
	#7.逻辑或：or

#排序
	order by：
		#asc：升序（默认）
		#desc：降序
		#书写顺序：在所有子句的最后
		#注意：升序空值排在最后，空值最大
		#表达式不能进行排序，只有别名可以
	#1.数值类型排序：
		select ename，sal
		from emp
		where sal>1000
		order by sal；#默认按照升序排序
	#2.字符排序：
		select ename，job，sal
		from emp	
		order by ename desc；#降序排列
	#3.日期排序
		select hiredate
		from emp	
		where hiredate>‘01-1月-82’
		order by hiredate desc；
	#4.空值排序（空值最大）
		select comm
		from emp
		order by comm asc；

#多列排序
	#排序基础：从左往右排序

单行函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
#单行函数和多行函数的区别：
	#区别：单行函数输入一条记录多行函数输入多条记录
	#共同点：都是输出一条记录

#单行函数：	
	#字符函数：
		#大小写转换函数：
			#lower：转小写
				select lower（‘HELLO’）
				from DUAL； #（虚表，这个表没有任何数据，也不能保存任何数据）
				select lower（ename）
				from emp；
			#upper：转大写
				select upper（‘hello’）
				from dual；
			#initcap：首字母大写
				select initcap（‘hello’）
				from dual；
		#字符处理函数
			#concat：只能连接两个值
				select concat（‘hello’，‘o’）
				from dual；
				
				select concat（ename，sal）
				from emp；
			#substr：截取字符串长度
				#第一个参数：要处理的值，第二个参数，指定截取的位置，第三个参数是截取长度
					select substr（‘hello’，2,2）
					from dual；#如果没有第三个参数则默认截取到最后
				
				#如果第二个参数是负数，表示字符串从右往左数到指定位置，截取对应长度
					select substr（‘hello’，-4,2）#结果为el
					from dual；
			#length：返回字符串的长度
				select length（ename）
				from emp；
			#instr（s1，s2，n1，n2）：返回s1中子串s2从n1开始，n2次出现的位置
				select instr（‘hello’，‘l’，1，1）
				from dual；
			#lpad：左填充
				select lpad（‘hello’，10，‘*’）#10表示未到10个长度的话，就在左边用*填充
				from dual；
			#rpad：右填充
				select rpad（‘hello’，10，‘*’）#10表示未到10个长度的话，就在右边用*填充
				from dual；
			#trim：去除字符串的头部或尾部的字符，中间不可去
				select trim（‘h’ from ‘hello’）
				from dual；
				select trim（‘o’ from ‘hello’）
				from dual；


	#数值函数：
		#round：四舍五入（第二个参数为保留小数位数）
			select round（45.926，2），round（45.926，0），round（45.926，1），round（45.926，-1）
			from dual；
		#trunc：截取指定长度的数值（直接截取，不进行四舍五入）
			select trunc（45.926，2），trunc（45.926，1），trunc（45.926，-1）
			from dual；
		#mod：取余
			select mod（3，2）
			from dual；
		#replace：替换
			select replace（‘hello’，‘l’，‘p’）
			from dual；
	#日期函数：
		#rr格式
			DD-MON-RR：01-1月-18
		#系统日期：sysdate
			select sysdate
			from dual；
		#日期的运算：
			select （sysdate-hiredate）/30 入职月，（sysdate-hiredate）入职天，（sysdate-hiredate）/365 入职年
			from emp；
		#RR格式的判断：
			#0-49：小
			#50-99：大
				#大大取当前
				#大小取下
				#小小取当前
				#小大取上
		#YY格式直接取
		#months_between：返回两个日期的自然月数
			select  ename，months_between（sysdate，hiredate）
			from emp；
		#add_months：在指定日期上加上相应的月数
			select ename，hiredate，add_months（hiredate，3）
			from emp；
		#next_day：返回指定日期的下一个星期几
			select next_day（hiredate，‘星期一’）
			from emp；
			select next_day（‘05-12月-18’，‘星期四’）
			from dual；#会返回2018/12/06
		#last_day：返回当月的最后一天
			select last_day（‘05-12月-18’）
			from dual；#会返回2018/12/31
		#round：日期的四舍五入
			select hiredate，round（hiredate，‘MONTH’）#从16号开始进1，精确到月；年7月开始进1
			from emp；
		#rr格式截取年份
			substr（‘hiredate’，-2，2）=‘81’ #oracle默认日期为rr格式
		#extract：返回具体日期
			select ename，hiredate，extract（MONTH from hiredate）
			from emp；
			select ename，hiredate，extract（YEAR from hiredate）
			from emp；
			select ename，hiredate，extract（DAY from hiredate）
			from emp；
	#转换函数：
		#to_char：转换成字符
			#将日期转换成字符
				select to_char（hiredate，‘yyyy’）
				from emp；
				
				select to_char（hiredate，‘mm’）
				from emp；
			
				select to_char（hiredate，‘year’）#输出英文的年份
				from emp；
	
				select to_char（hiredate，‘yy’）
				from emp；

				select to_char（hiredate，‘mi’）#到分
				from emp；
			#9表示一位数，0表示前导0，$代表美元符号，L表示本地货币符号，.表示小数点，，表示千位符
				select to_char（sal，‘L9,999’）
				from emp；# 如果超出范围#表示
		#to_number：字符转换成数值
			#注意：字符是由数值组成
				select to_number（‘123’）+100
				from dual；
		#to_date：转换成日期
			select to_date（‘2018-12-06 14:06:20’，‘yyyy-mm-dd hh24：mi：ss’）
			from dual；
				select to_date（‘1997-01-01 12:12:12’,‘yyyy-mm-dd hh24：mi：ss’）
				from dual；#一般用于insert
	#通用函数：
		#nvl：处理空值
			select comm，nvl（comm，0）+sal
			from emp；
		#nvl2：
		#nullif：
		#coalesce：
		#case：
			select ename, deptno,
       
				(case deptno     
	          
					 when 10  then '销售部'
                       
					 when 20  then '技术部'
                        
					 when 30  then '管理部'
                          
					 else '无'   end)   
                            
				deptname  
			 
			from emp;

		#decode：
			select ename, deptno,
  
				decode(deptno,
    
					10,'销售部',
    
					20,'技术部',
    
					30,'管理部',
    
					'无')   
				deptname
			
			from emp;

		#嵌套函数：最好从里往外写
			select ename,nvl(to_char(mgr),'no manager')
			from emp;

多表连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
#什么是多表连接
	#通过连接条件把相关表关联起来
		select * from emp；
	#n个表相连会有n-1个关联关系，先写关联条件，再写限制条件

#笛卡尔积：
	#为什么会产生笛卡尔积：
		#没有连接条件会产生笛卡尔积
	select emp.ename,emp.empno,dept.deptno,dept.dname
	from emp,dept

#等值连接：通过=关联两个表中的列
	select emp.empno,emp.ename,emp.deptno,dept.deptno,dept.dname
	from emp,dept
	where emp.deptno=dept.deptno;

#有其他的过滤条件

#限制歧义列名：给表起一个别名
	select e.ename,e.deptno
	from emp e,dept d
	where e.deptno=d.deptno;

#非等值连接
	select e.ename,e.sal,s.grade
	from emp e,salgrade s
	where e.sal between s.losal and s.hisal

#外部连接（Oracle自带的连接）
	#(+)：+号的一边产生一个万能行，是由空值组成的
	#基准表：+的对立面是基准表
		select  e.ename,e.deptno,d.deptno,d.dname
		from emp e,dept d
		where e.deptno(+)=d.deptno; #（dept为基准表）
		#基准表中的数据全部显示，另一张表中的数据和基准表中的数据匹配，匹配不上则用空值填充

#自身连接（自连接）
	#连接条件
		#员工的经理编号=经理的员工编号
		select worker.ename,manager.ename
		from emp worker,emp manager
		where worker.mgr=manager.empno;

#SQL99的写法：
	select  e.empno,e.deptno,d.deptno,d.dname
	from emp e
	cross join dept d;
	#产生笛卡尔积，不可取
#自然连接：
	#两个表中要有相同列
	#列的数据类型保持一致
	#是等值连接
	select deptno,e.ename
	from emp e
	natural join dept d;
	#注意：共有的列不能用限定词

#using连接：
	select  e.ename,e.sal,d.dname
	from emp e join dept d using(deptno)
	#using指定的列是两个表中具有相同名称和数据类型的列

#ON子句：指定连接条件
	select e.eanme,e.deptno,d.deptno,d.dname
	from emp e
	join dept d
	on(e.deptno=d.deptno);

#左外连接：
	#以左表为基准，右表和左表匹配，匹配不上用空值填充
		select e.empno,e.ename,e.deptno,d.deptno,d.dname
		from emp e
		left outer join dept d
		on(e.deptno=d.deptno); #emp为左表，没有部门的员工显示出来
#左连接：
		select e.empno,e.ename,e.deptno,d.deptno,d.dname
		from emp e
		left  join dept d
		on(e.deptno=d.deptno); #效果无区别，书法上有区别

#右外连接：
	#以右表为基准，左表和右表匹配，匹配不上用空值填充
		select e.empno,e.ename,e.deptno,d.deptno,d.dname
		from emp e
		right outer join dept d
		on(e.deptno=d.deptno); #dept为右表，没有员工的部门显示出来
#右连接：
		select e.empno,e.ename,e.deptno,d.deptno,d.dname
		from emp e
		right join dept d
		on(e.deptno=d.deptno);

#全外连接
	#在等值连接的基础上，两个表中的数据都显示
		select e.empno,e.ename,e.deptno,d.deptno,d.dname
		from emp e
		full outer join dept d
		on(e.deptno=d.deptno);

分组函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
#聚合函数要用HAVING来过滤
#聚合函数：
	#min：最小值
	#max：最大值
		#字符类型
			select max(ename)
			from emp;
		#数值类型：
			select min(sal)
			from emp;
		#日期类型
			select min(hiredate)
			from emp;
	#sum：求和
	#avg：求平均值
		select sum(sal) 和,avg(sal) #平均值
		from emp;
	#count：返回总记录数
		select count(*)
		from emp;
		select count(comm)
		from emp;
	#过滤空值：
		#只有count(*)不过滤空值
#group by：用来分组，比如：group by deptno
	#可以把表中的数据分成指定小组
	#查询每个部门的编号，平均工资
		select deptno,avg(sal),ename
		from emp
		group by deptno,ename;
	#注意：在select子句后面除了聚合函数，其他的列都要出现在group by子句后面
	#注意：在group by后面出现的列不一定要出现在select后面

#having：写在group by 的后面，where写在group by的前面
	#应用场景：用来过滤分组函数
	#除了聚合函数，其他的条件过滤都要写在where子句中

#书写顺序：
	select
	from
	where
	group by
	having
	order by
#执行顺序：
	#1.通过FROM子句中找到需要查询的表；

	#2.通过WHERE子句进行非分组函数筛选判断；

	#3.通过GROUP BY子句完成分组操作；

	#4.通过HAVING子句完成组函数筛选判断；

	#5.通过SELECT子句选择显示的列或表达式及组函数；
	
	#6.通过ORDER BY子句进行排序操作。

子查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
#查询工资比Jones工资高的员工信息
	#1.查询jones的工资信息
		select sal 
		from emp
		where ename='JONES';
	#2.sal>sal（jons）
		select ename,sal,hiredate
		from emp
		where sal>(
			select sal 
			from emp
			where ename='JONES';
		); #(子查询需要用括号括起来，子查询先于主查询执行)
		#子查询可以嵌套在where,having,from后

#单行运算符：>,<,=,<>,>=,<=
	#子查询的返回结果为一条数据
		#1.查询出比jones为雇员工资高的其他雇员
			#1.1 书写步骤：
				#1.1.1 先写子查询
				#1.1.2 编写主查询
		#2.显示和雇员7369从事相同工作并且工资大于雇员7876的雇员的姓名和工作
			#1.查询7369的工作岗位
				select job
				from emp
				where empno=7369;
			#2.查询7369的工资
				select sal
				from emp
				where empno=7369;
			#3.编写主查询
				select ename,job,sal
				from emp
				where job=(
				       select job
				       from emp
				       where empno=7369
				)
				and sal>(
				       select job
				       from emp
				       where empno=7369
				);
		#3.查询工资最低的员工姓名，岗位及工资
			#1.查询最低工资
				select min(sal)
				from emp
			#2.写主查询
				select ename,job,sal
				where sal=(
				        select min(sal)
				        from emp	
				);
		#4.查询部门最低工资比20部门最低工资高的部门编号及最低工资

			#1.查询20号部门的最低工资
				select min(sal)
				from emp
				where deptno=20;
			#2.编写主查询
				select  deptno,min(sal)
				from emp
				group by deptno
				having min(sal)>(
				         select min(sal)
				         from emp
				         where deptno=20
				);
		#5.查询哪个部门的员工人数 高于各部门平均人数
			#1.查询部门平均人数
				select avg(count(empno))
				from emp
				group by deptno;
			#2.编写主查询
				select deptno,count(empno)
				from emp
				group by deptno
				having count(empno)>(
				        select avg(count(empno))
				        from emp
				        group by deptno
				);
#多行运算符：IN,ANY,ALL
	#子查询的返回结果是多条记录

 	       < ANY：表示小于子查询结果集中的任意一个，即小于最大值就可以。


	       > ANY：表示大于子查询结果集中的任意一个，即大于最小值就可以。


	       = ANY：表示等于子查询结果中的任意一个，即等于谁都可以，相当于IN

	       < ALL：表示小于子查询结果集中的所有行，即小于最小值

	       > ALL：表示大于子查询结果集中的所有行，即大于最大值
	      

#  = ALL :表示等于子查询结果集中的所有行,即等于所有值，通常无意义


		#1.每个部门的最低工资
		      select ename
		      from emp
		      where sal in(
			select min(sal)
			from emp
			group by deptno
		      );
		#2.查询是经理的员工姓名，工资
		     select ename,sal,hiredate
	      	     from emp
		     where empno in(
			select mgr
			from emp
		     )
		#3.查询部门编号不为10，且工资比10部门任意一名员工工资高的员工编号，姓名，职位，工资
		     select empno,ename,job,sal
		     from emp
		     where deptno<>10
	 	     and sal>any(
		    	select sal
			from emp
			where deptno=10
		     )
		#4.查询部门编号不为10，且工资比10部门所有员工工资高的员工编号，姓名，职位，工资
		     select empno,ename,job,sal
		     from emp
		     where deptno<>10
	 	     and sal>all(
		    	select sal
			from emp
			where deptno=10
		     )

#多列子查询
	#通过子查询后面的列判断子查询是单列还是多列
	#如果子查询select后面是单列就是多列子查询，反之是多列子查询
	#注意：比较运算符左边的列和子查询select中的列要匹配、多列的逻辑关系是并且
	
	#1.查询出和1981年入职的任意一个员工的部门和职位完全相同员工姓名、部门、职位、入职日期,不包括1981年入职员工
		select ename,deptno,job,hiredate
		from emp
		where (deptno,job) in(
			select deptno,job
			from emp
			where to_char(hiredate,'yyyy')=1981
		)and to_char(hiredate,'yyyy')<>1981;
	#2.查询出和1981年入职的任意一个员工的部门或职位相同员工姓名、部门、职位、入职日期,不包括1981年入职员工
		select ename,deptno,job,hiredate
		from emp
		where (deptno in(

			select deptno
			from emp
			where to_char(hiredate,'yyyy')='1981')
			or job in(
			select job
			from emp
			where to_char(hiredate,'yyyy')='1981')
		)and to_char(hiredate,'yyyy')<>'1981';

#子查询中的空值
	#注意：not in不能与子查询中的空值一起用，如果用了，查询无果

#在from中使用子查询
	#查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资
	
		select e.ename,e.sal,e.deptno,a.salavg
		from emp e,(select deptno,avg(sal) salavg
			     from emp
			     group by deptno)a
		where e.deptno=a.deptno
		and e.sal>a.salavg;
	
#rownum：伪列
	select enae,sal,rownum
	from emp;
	#rownum的应用场景：分页
	#结论：rownum始终从1开始产生
	#注意：当rownum与order by一起使用时，rownum实际是已经被排了序的rownum

#top_n：取前几条数据，可用于最大或最小的几个值
	select d.*,rownum
	from (select sal,ename,hiredate
	          from emp
	          order by sal) d
	where rownum<5; #工资最少的4位员工
	select d.*,rownum
	from (select ename,sal,hiredate
	          from emp
	          order by hiredate desc)d
	where rownum<5; #入职最晚的4位员工
	#目的：让order by先于rownum执行

#分页：
		#index：当前页
		#size：每页显示条数
		#index-1）*size：每页第一条记录
		#index*size：每页最后一条记录
	#两层：如果sql中没有order by子句，那么使用两层
		         select d.*
		         from(select ename,sal,hiredate,rownum r
			from emp)d
		         where r>(index-1)*size
	 	         and r<=index*size;
	#三层：如果sql中有order by子句，那么使用三层
		         select *
		         from(select d.*,rownum r
			from(select ename,sal,hiredate
			        from emp
			        order by sal)d)
		         where r>(index-1)*size
		         and r<=index*size;
	#注意：rownum的别名在主查询中不能被识别

约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
#(重要)5个约束--约束：constraint
	#列级别约束
		#not null：必须在列级别上，不需要声明constraint关键字
		#约束命名：表名_列名_约束类型
		#创建时间：在创建表的时候创建约束
			#unique：唯一性
		name varchar2(10) constraint student1_name_uq unique
			#primary key：唯一性与非空性
		id number(2) constraint student1_id_pk primary key
			#check：检查性约束：数据必须要满足一定的条件
		age number(2) constraint student1_age_ck check(age between 10 and 20)
		sex varchar(2) constraint student1_sex_ck check(sex in('男','女'))
			#既有default又有check时把default放在前面
	#表级别约束：在表的最后一行加约束
			#unique：
		constraint student1_name_uq unique(name)
			#primary key
		constraint student1_id_pk primary key(id)

#外键约束：foreign key
	#主表
	create table class(
	       cid number(2) constraint class_cid_pk primary key,
	       cname varchar2(10)
	)
	insert into class(cid,cname)
	values(1,'java')
	
	#从表
	create table student(
	       sid number(2) ,
	       name varchar2(10),
	       sex varchar2(2),
	       weight number(6),
	       cid number(2) constraint student_cid_fk references class(cid)
	)
		#表级别：constraint student_cid_fk foreign key(cid) references class(cid) 
	constraint student_cid_fk foreign key(cid) references class(cid) on delete cascade #联级删除
	insert into student(id,name,age,sex,weight,cid)
	values(4,'hh',21,'男',70,1);
	#注意：外键添加的值必须是主键有的值，或者是空值
	
   	#锁表：只有select语句可以触发
	select * from student for update;
    #追加外键约束：
	alter table student add foreign key(cid) references class(cid);
   	#删除约束：
	alter table student drop constraint student_sex_ck

数据操作与实务控制

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
#添加一个日期类型
	insert into emp(empno,ename,sal,hiredate,deptno)
	values(2,'李四',3000,'01-1月-18',10);
	
	insert into emp(empno,ename,sal,hiredate,deptno)
	values(3,'王五',4000,to_date('2018-12-10','yyyy-mm-dd'),10);

#通过子查询创建表
	create table emp10
	as
	select * from emp
	where 1=0;	#当表达式不成立时，创建表结构没有数据；成立时，表中的数据也创建出来
			 
#通过子查询创建的表，表中的列通过子查询中select后面的列来指定

#通过子查询向表中添加数据
	insert into emp10
	select empno,ename,hiredate
	from emp
	where deptno=10;

#修改数据
	#单列修改
		update emp10
		set ename='张三'
		where empno=7935;
	#多列修改
		update emp10
		set ename='王五',hiredate=to_date('2018-01-01','yyyy-mm-dd')
		where empno=7936;
	#sal计算
		update emp
		set sal=sal+300
		where empno=7935;

#删除数据
	delete from emp
	where empno=7936;

#事务组成：
	在数据库中，事务由一组相关的DML或SELECT语句，加上一个TPL语句（COMMIT、ROLLBACK）
	或一个DDL语句(CREATE、ALTER、DROP、TRUNCATE等)或一个DCL（GRANT、REVOKE）语句。
	
	例：如下语句组成两个事务。
			
INSERT….
			
UPDATE….
			
DELETE….

			SELECT….
			
INSERT…
			
COMMIT; #前6条语句，组成第1个事务
		
			UPDATE…
			
DELETE….

			CREATE… ; #后3条语句，组成第2个事务
#事务特性
	#事务特征可用四个字母的缩写表示：即ACID
	
	原子性（Atomicity）
事务就像“原子”一样，不可被分割，组成事务的DML操作语句要么全成功，要么全失败，不可能出现部分成功部分失败的情况。
	             	
	一致性（Consistency）
一旦事务完成，不管是成功的，还是失败的，整个系统处于数据一致的状态。

	隔离性（Isolation）
一个事务的执行不会被另一个事务所干扰。比如两个人同时从一个账户从取钱，通过事务的隔离性确保账户余额的正确性。

	持久性（Durability）
也称为永久性，指事务一旦提交，对数据的改变就是永久的，不可以再被回滚。

#事务结束
	#显式结束
		提交(COMMIT)：使用COMMIT命令实现，以成功的方式结束事务，组成事务的DML语句操作全部生效。

		回滚(ROLLBACK)：使用ROLLBACK命令实现，以失败的方式结束事务，组成事务的DML语句操作全部被取消。

	#隐式结束
		隐式提交：当下列任意一种情况发生时，会发生隐式提交

		执行一个DDL(CREATE、ALTER、DROP、TRUNCATE、RENAME）语句；
				
		执行一个DCL(GRANT、REVOKE)语句；
				
		从SQL*Plus正常退出（即使用EXIT或QUIT命令退出）；


		隐式回滚：当下列任意一种情况发生时，会发生隐式回滚

		从SQL*Plus中强行退出；
				
		客户端连接到服务器端异常中断；

		系统崩溃；

索引、序列、同义词

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
#序列
	#应用场景：主键值自动增长
	#创建序列
	      #创建序列test_seq，起始值为10，每次增长2，最大值100，最小值9，循环序列，每次缓存10 

		create sequence test_seq
		start with 10 #起始值
		increment by 2 #增长2
		maxvalue 100 #最大值100
		minvalue 9 #最小值9
		cycle
		cache 10;    
	#查询序列值
		#nextval：产生一个序列值
			select test_seq.nextval from dual;
			#注意：序列创建时是没有产生序列值的，只有通过nextval才能产生
			        
		#currval：查询当前的序列值
			select test_seq.currval from dual;
			#注意：第一次使用currval之前必须要先使用nextval产生一个序列值
			
	#序列的应用
		#一般在insert语句中    主键列：（序列.nextval）	
			insert into emp(empno,ename,hiredate,deptno)
			values(test_seq.nextval,'zz','01-2月-13',10);
	#修改序列
	    #注意：start with不能修改
		alter sequence test_seq
		increment by 1
		maxvalue 200
		minvalue 1
		nocycle
		cache 8;
	#删除序列
		drop sequence test_seq;

#索引
         #如果表中有primary key 或者 unique时，会自动创建索引
         #提高查询速度
	 #单列索引
		create index index_emp_ename
		on emp(ename);
	 #复合索引
		create index index_emp_enamejob
		on emp(ename,job);
	 #测试环境
		create table el
		as
		select * from emp;
		
		insert into el select * from emp;
		
		update el set empno=rownum;
	 #适合创建索引的情况（重要）
		表数据量很大；

		要查询的结果集在2%-4%左右；
		
		经常用来做WHERE条件中的列或者多表连接的列；

		查询列的数据范围分布很广；
		
		查询列中包含大量的NULL值,因为空值不包含在索引中；
		
 	 #不适合创建索引的情况
		数据量很小的表；
		
		在查询中不常用来作为查询条件的列；
		
		频繁更新的表；
		
		索引列作为表达式的一部分被使用时，比如常查询的条件是SALARY*12，此时在SALARY列上创建索引是没有效果的；
		
		查询条件中有单行函数时，用不上索引；
		
	 #索引缺点：  
	 			占用空间；
				降低DML的操作速度；
	 #删除索引
		drop index index_emp_ename;
	    #注意：删除索引后，索引中的数据及定义被删除，索引所占的数据空间被释放，但表中的数据仍然存在。

#同义词 （相当于别名）
    #注意：只有数据库管理员才拥有公有同义词的创建和删除权限
	create synonym emp1
	for emp;
	
	drop synonym emp1;

视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
#创建视图
	create view dept10
	as
	select empno,ename,job
	from emp
	where deptno=10;
#删除视图
	drop view dept10;
#通过列别名创建视图
	create view v_emp
	as
	select empno 编号,ename 姓名,sal 工资
	from emp
	where deptno=10;
#创建复杂视图
	#创建一个视图，通过该视图可以查看每个部门的名称，最低工资，最高工资，平均工资
	create view v_emp1
	(name,minsal,maxsal,avgsal) #给视图起别名
	as
	select d.dname,min(e.sal),max(e.sal),avg(e.sal)
	from emp e,dept d
	where e.deptno=d.deptno
	group by d.dname
#修改视图
	create or replace view v_emp1
	(employ_no,employ_name,employ_job) #给视图起别名
	as
	select empno,ename,job
	from emp
	where deptno=10
#在视图上添加数据
	insert into v_emp1(empno,ename,job)
	values(2,'周六','研发');  #在视图上添加的数据会在基表中添加，视图中不添加
	
#with read only：一般用于视图，在创建视图的最后一句添加，让视图只读

#不可删除的情况：
	GROUP函数、GROUP BY子句，DISTINCT关键字；
#不可修改的情况：
	GROUP函数、GROUP BY子句，DISTINCT关键字；
	使用表达式定义的列；
	ROWNUM 伪列；
#不可插入的情况：
	GROUP函数、GROUP BY子句，DISTINCT关键字；
	使用表达式定义的列；
	ROWNUM 伪列；
	基表中未在视图中选择的其它列定义为非	空并且没有默认值
本文作者： LiJing
发布时间： 2020-07-20
最后更新： 2023-01-27
本文标题： Oracle数据库相关16届（已整理完）
本文链接： https://blog-yilia.xiaojingge.com/posts/fa4a0f7c.html
版权声明： 本作品采用 CC BY-NC-SA 4.0 许可协议进行许可。转载请注明出处！
知识共享许可协议
赏

谢谢你请我吃糖果

支付宝
微信
本站总访问量次 | 本站访客数人

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

喜欢看书、打代码、吃吃吃！我从没被谁知道，所以也没被谁忘记。在别人的回忆中生活，并不是我的目的。我只不过是自己一个人在努力罢了。