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

Oracle数据库相关(学校实训上课内容)(整理完)

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

本文主要介绍学校实训期间总结的Oracle数据库的相关知识。

数据库的安装（2020-07-20上午）

1
2
3
4
5
6
7
8
下载11g版本的oracle数据库安装包
在d盘下面创建量空文件夹data1和data2
点击setup运行
全局数据库名：orcl也是实例名,服务名。他的库名也是orcl
数据库的口令，管理员的密码，自己设置自己能够记住的
数据库的版本：11g
Oracle数据库是什么数据库：关系型数据库
Oracle的端口号是1521

dos窗口sql指令相关（2020-07-20上午）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
win+R-->cmd  #进入dos窗口
sqlplus/nolog  #进入oracle控制台
conn/as sysdba  #以DBA角色进入，即数据库超管
select username from dba_users;  #查看用户列表
alter user 用户名 identified by 新密码;  #修改某一个用户的密码
alter user 用户名 account unlock;  #解锁用户账号
alter user 用户名 account lock;  #用户账号加锁
grant dba to 用户名;  #给用户授予dba权限
show user;  #查看当前用户
select table_name from tabs; #查看当前用户下的表
conn 用户名/密码  #切换用户

#通过dos窗口查询的结果，可以根据自己的屏幕情况进行调节,设置如下:
1.设置页面显示总行数
show pagesize;  #首先查看目前的pagesize,默认是14
set pagesize 100;  #将pagesize设置好100,则可以一次显示够多行记录了
2.设置行的宽度
show linesize;  #查看目前的linesize,默认是80
set linesize 200;  #设置成200或者更宽都可以

粗略创建用户及表的操作（2020-07-20上午）

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
#创建用户（比如说haha，密码设置为123）：
create user haha identified by 123;
#数据库的结构：
表空间-->用户-->数据库对象（表）-->操作数据
#注意：创建用户haha时没有指定表空间，那么会使用默认表空间。（users）
#连接haha用户,报错没有登录（create session）权限，通过system给haha用户授予登录权限内。
#注意：确保system有相关权限，才可以给其他用户授权。
#连接system，给haha授予登录权限：
grant create session to haha;
#登录haha用户：
conn haha/123;
#在haha用户下面创建表：
create table student(id number(4),name varchar2(10));
#报错，没有建表权限，重新登录system,通过system给haha授予建表权限
conn system/123;  grant create table to haha;
#连接haha用户，创建表
create table student(id number(4),name varchar2(10));
#连接system
前面的这些授权可以用grant dba to haha; 一步搞定
#通过表空间users给用户haha分配内存：
alter user haha quota unlimited on users;
#给表添加数据：
insert into student(id,name)  values(1,’admin’);
#查询数据：
select * from student;

数据库基本知识（2020-07-20下午）

1
2
3
4
5
6
7
8
9
10
11
1.什么是关系型数据库：
	基于关系模型提出的数据库叫关系型数据库。
2.什么是关系模型:
	由行和列组成的二维数据表
		一行叫一条记录。
		一列叫一个字段
		单元格叫值
3.有哪些关系型数据库：
        Oracle    mysql   sqlServer  db2
4.常见的oracle版本：
       10g   11g   12c

用户权限角色（2020-07-20下午）

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
#1.解锁scott用户：
     alter user scott account unlock;
#2.给它dba权限：
     grant dba to scott; 
     
#3.创建表空间：
        create  tablespace test1
        datafile 'd:\test1.dbf'
        size 100m   #表空间大小 
        autoextend on next 200m
        maxsize 1000m;
        
#4.创建临时表空间：
        create temporary tablespace test2
        tempfile 'd:\test2.dbf'
        size 20m
        autoextend on next 200m
        maxsize 1000m;
        
        
#5.创建用户并指定表空间：
           #注意：
               #1.用户一般是由dba创建，创建好的用户没有任何权限，必须要授予相关权限
               #2.用户自己不能删除自己
            
       create user haha
       identified by 123
       default tablespace test1
       temporary tablespace test2 quota 20m on test1;    
       
#6.直接创建用户，那么使用users默认表空间
       create user haha identified by ok;
             #1.授予dba 
                 grant dba to haha1; 
             #2.分配内存: 
                 alter user haha1 quota 10m on users; 
                 
#7.关于用户的三个最基本权限 
      #1. create session（登录） 2.create table(建表) 3.dba（最高权限）
      
#8.修改密码：
       alter user 用户名(haha) identified by 新密码(123);
       
#9.解锁和锁住账号
         #锁住scott
           alter user scott account lock;              
         #解锁scott
           alter user scott account unlock;    
           
#10.删除用户
          drop user 用户名 或者drop user 用户名 cascade;
          #cascade:先删除概念用户下的所有的对象在删除用户
          drop user haha cascade; 
          
#11.给用户授权
         grant 权限列表  to 用户。
         grant create session,create table to haha; #同时授予多个权限，每个权限之间逗号隔开
         grant dba to haha; 
         
#12.角色:
       #1.角色作用：接收权限的载体。通过角色给用户授权
       #2.创建角色
            create role tr;
            drop role tr;
       #3.将权限授予角色
           grant create session to tr;
       #4.通过角色给用户授权
           grant tr to haha;
       #5.从角色中收回权限
           revoke create session from tr; 
       #6.从用户haha中将角色tr收回
           revoke tr from haha; 
       #7.删除角色
           drop role tr;  
           
  #总结:
        #1.创建表空间
              #1.默认表空间
              #2.临时表空间
        #2.创建用户：
              #1.创建用户并且指定表空间
              #2.直接创建用户。表空间是(users)
        #3.权限：
              #登录   建表  dba
              #给用户授权
               
        #4.角色：
              #1.什么是角色.包括作用
              #2.创建角色：create role tr
              #3.如何给角色授权：grant 权限 to 角色
              #4.从角色中收回权限：revoke 权限 from 角色

基本的查询语句（2020-07-20下午）

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
#编写简单的查询语句

#1.5个结构化查询语言：（会背）

 	#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，用于定义、销毁、修改数据库对象。
       
#2.查询语句：
      #1：通过*来查询：*代表的是所有的字段
         select * from emp
      #2.根据具体的字段查询  
         select empno,ename 
         from emp;
#3.sql的书写规范：
      #1.不区分大小写
      #2.每个子句占用一行  
      
#练习1：
       select * from emp;
       select empno,ename,job
       from emp;  
       
#4.算数运算符：
       #注意：
            #算数运算符改变的是输出结果，不能改变基表中的数据
            #括号可以改变优先级
       select sal,sal*10
       from emp;
       
       select empno,ename,sal,sal*10+10
       from emp; 
       
       #括号改变优先级
       select empno,ename,sal,sal*(10+10)
       from emp;
  
#练习2：
      #1.员工转正后，月薪上调20%，请查询出所有员工转正后的月薪 
         select ename,sal*1.2
         from emp;    
      #2.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的年薪所得
      #（不考虑奖金部分,年薪的试用期6个月的月薪+转正后6个月的月薪)
         select ename, (sal*6+sal*6*1.2)
         from emp;
         
#null：无效的，未赋值的未只的不可用的值。
      #注意：不可以参与运算。参与运算结果还是null
        select empno, ename,sal,comm,comm+100
        from emp; 
        
      #通过函数nvl()函数处理空值,将空值指定具体的数据（不能处理非空的）
        select empno, ename,sal,comm,nvl(comm,3)+100
        from emp; 
        
#列别名：
       #1.作用：给字段或者表达式重新定义一个名字
       #2.定义的方式
           #1.字段 别名
           #2.字段  as 别名
         select ename 姓名, (sal*6+sal*6*1.2) as 年薪
         from emp;
       #3.三种特殊情况别名要加上双引号
           #1.大小写区分              
              select ename "Name"
              from emp; 
           #2.别名中有空格
              select ename "Na me"
              from emp; 
           #3.别名中有特殊符号
              select ename "Na%me"
              from emp; 
              
# ||连接操作符：它可以连接多个字段
      select ename||' hello '||job "employee"
      from emp;  
      
#distinct：消除重复的记录数
      select distinct deptno
      from emp; 
      
#总结：
      #1.5个查询语言（会背）
      #2.查询方式：
           #1.*查询，查询所有字段
           #2.查询具体的字段
      #3.运算符:+ - *  /
           #1.算数的优先级
           #2.通过括号，改变优先级
           #3.改变的输出结果，不是基表数据
      #4.列别名：
           #1.定义：
           #2.别名的三种特殊形式要加上双引号
      #5.||拼接符
           #1.可以连接多个字段或者字符串
      #6.distinct:过滤重复的记录数

创建和维护表（2020-07-21上午）

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
#1.表：存储用户数据

#2.数据库对象的定义
    #1.必须以字母开始
    #2.命名要有意义
    #3.对象不能重复
    #4.不能用保留字（关键字）
    #5.不区分大小写
    
    #Oracle中数据库对象命名原则 
    #必须由字母开始，长度在 1–30个字符之间。 
    #名字中只能包含 A–Z, a–z, 0–9, _ (下划线)，$ 和 #。 
    #同一个Oracle服务器用户所拥有的对象名字不能重复。 
    #名字不能为Oracle的保留字。
    #名字是大小写不敏感。

#3.建表：
    create table student(
        sid number(4) primary key,
        sname varchar2(10),
        birth date,
        height number(4,2)
    )
    #注意：最后一个字段不能有逗号
    
    select * from student;
    insert into student(sid,sname,birth,height) 
    values(1,'李四','01-1月-20',12.12);
    
    
#4.默认值：
    create table student(
        sid number(4) primary key,
        sname varchar2(10) default '张三',
        birth date,
        height number(4,2)
    )
    
    select * from student;
    insert into student(sid,birth,height) #不写字段，自动加上默认值
    values(1,'01-1月-20',12.12);
    
#5.数据类型
    #1.字符类型
           #1.char：固定长度。  
                #默认和最小长度为 1，最大长度为 2000，char(6) 张三(4) 不会将多余长度释放
                
           #2.varchar2：可变长度。 
                #最小长度是 1，最大长度是4000，char(6) 张三(4)  会将多余两个长度释放
           
           #3.clob：可变长度，最大可存储4G数据
           
    #2.数值类型
           #1.number：既可以表示小数，也可以表示整数
           #2.number(n)：表示的是整数  比如说number(3)：表示的是-999~999
           #3.number(p,s)：表示小数，  p：代表的是数字的总长度，s是小数的位数，(p-s)表示的是整数位数
              #number(4,2)：总长度：4，小数位是2位，整数位是(4-2)位：88.88，88.8，总长度不能超过4位
              
    #3.日期类型
           #1.date：精确到秒（常用） ，没有长度，有固定格式
           #2.timestamp：精确到毫秒

#6.添加日期类型的数据：

           #1.yy格式的类型：yyyy-mm-dd hh:mi:ss 是一个字符串
           
                 #to_date：日期转换函数，将字符串转换成日期类型
                 
                 insert into student(sid,sname,birth,height)
                 values(3,'王五',to_date('2020-10-10 10:10:10','yyyy-mm-dd hh:mi:ss'),88.88);
               
                 insert into student(sid,sname,birth,height)
                 values(4,'赵六',to_date('2020-10-10','yyyy-mm-dd'),88.88);
                 
           #2.RR格式：DD_MON_RR：oracle默认的日期类型：01-1月-20
           
                 insert into student(sid,sname,birth,height)
                 values(5,'smith','21-7月-20',88.88);

#7.修改和添加处理日期也是一样的
            
           #1.RR格式的修改
           
           update student set birth = '22-7月-20'
           where sid = 5;
           
           #2.yyyy格式的修改
           
           update student set birth = to_date('2020-07-22','yyyy-mm-dd')
           where sid = 5;
           
#8.通过子查询创建表
           #1.只创建表结构：当where后条件不成立时候，只创建表结构
           create table student1
           as
           select * from student where 1 = 0;
           
           select * from student1;
           drop table student1;
           
           #2.当条件表达式成立时，创建表的同时，复制表中的数据
           create table student1
           as
           select * from student where 1 = 1;
           
           select * from student1;

#9.表结构：主要是对表字段的添加、修改或者删除
           #1.添加一个字段
           alter table student 
           add(sex varchar2(2) default '男');
            
           select * from student;
            
           #2.修改字段：
               #1.可以修改字段的数据类型，必须清空该字段的所有的值
               #2.可以修改长度，不能比实际长度小，否则存不下该字段下的值
               #3.可以修改默认值。 可以修改之后的默认值，之前的不能修改。
           alter table student
           modify(sex varchar2(4) default '女');
            
           insert into student(sid,sname)
           values(6,'乌兹');
            
           #3.删除字段
           alter table student
           drop(sex);
            
#10.截断表（delete和truncate的区别，要求会背）
           #1.drop：数据库中的对象通过drop删除，可删除表，用户
           #2.delete：删除表中某几条数据，可以根据条件删除，不释放存储空间，可以回滚（背）
           delete from student where sid = 6;
           #3.truncate：截断表,删除表中所有数据，释放存储空间，不可以回滚（背）
           truncate table student;
           select * from student;
           
#课后作业
#1.请分析按照以下要求都需要建立什么类型的字段?
#(1)最大2000个字节定长字符串
char(2000)
#(2)如果输入‘张三’ 后添空格6个
char(10)
#(3)性别输入'男'或'女'
char(2)
#(4)最大4000个字节变长字符串
varchar2(4000)
#(5)如果在数据库中输入'张三'则显示数据'张三'
varchar(4)
#(6)表示数字范围为- 10的125次方到10的126次方, 可以表示小数 也可以表示整数
number
#(7)最大表示4位整数  -9999 到 9999
number(4)
#(8)表示5位有效数字 2位小数的 一个小数  -999.99 到 999.99
number(5,2)
#(9)包含年月日和时分秒
date
#(10)包含年月日和时分秒毫秒
timestamp
#(11)二进制大对象图像/声音
blob

#2.创建表date_test,包含列d，类型为date型。试向date_test表中插入两条记录，一条当前系统日期记录，一条记录为“1998-08-18”。
create table date_test(
	d date
)
insert into date_test
values(sysdate);

insert into date_test
values(to_date('1998-08-18','yyyy-mm-dd'));
#3.创建与dept表相同表结构的表dtest，将dept表中部门编号在40之前的信息插入该表。
create table dtest
as
select *
from dept
where deptno < 40;
#3.创建与emp表结构相同的表empl，并将其部门编号为前30号的员工信息复制到empl表。
create table emp1
as
select *
from emp
where deptno < 30;
#4.试为学生表student增加一列学生性别gender 默认值 “女”。
alter table student
add(gender char(2) default '女');
#5.试修改学生姓名列数据类型为定长字符型10位。
alter table student
modify(sname char(10));

限制编辑和对数据排序（2020-07-21下午）

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
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
#1.限制条件查询：通过where 根据条件查询
#2.比较操作符：= >  >=  <  <=  <>（不等于）
#3.适合的数据类型
    #1.字符类型的比较
    select ename,sal,empno
    from emp
    where ename='SMITH'; 
    
    #2.数值类型
    select ename,sal,empno
    from emp
    where sal>2000; 
       
    #3.日期类型的比较
       #1.比较yyyy格式
       select ename,sal,empno,hiredate
       from emp
       where hiredate>to_date('1981-2-22','yyyy-mm-dd')
       
       #2.比较RR格式:
       select ename,sal,empno,hiredate
       from emp
       where hiredate>'22-2月-81';
       
#练习1
    #1.查询职位为SALESMAN的员工编号、职位、入职日期。
         select empno,job,hiredate
         from emp
         where job='SALESMAN';

    #2.查询1985年12月31日之前入职的员工姓名及入职日期。
         select empno,hiredate
         from emp
         where hiredate<'31-12月-85';

    #3.查询部门标号不在10部门的员工姓名、部门编号。
         select ename,deptno
         from emp
         where deptno<>10;


#4.特殊比较运算符；
     #1.between and：是包含区间值
         #1.字符类型：
         select ename,empno,sal
         from  emp
         where ename between 'ALLEN' and 'SMITH';
             
         #2.数值类型
         select ename,empno,sal
         from emp
         where sal between 800 and 1600;
             
         #3.日期类型
         select ename,empno,sal,hiredate
         from emp
         where hiredate between '17-12月-80' and '1-5月-81';
         
     #2.in:判断要比较的值是否和集合中的值相等
         #1.字符类型
         select ename,sal
         from emp
         where ename in('SMITH','ALLEN','WARD','HELLO');
         
         #2.数值类型:
         select ename,sal
         from emp
         where sal in(800,1600,1250,20);
         
         #3.日期类型          
     
     #3.like:模糊查询
          #1._：任意一个字符
                #查询第二个字母是M的员工姓名
                select ename,sal,hiredate
                from emp
                where ename like '_M%';
                
                #倒数第二个字母是E
                select ename,sal,hiredate
                from emp
                where ename like '%E_';  
                
          #2.%：代表任意更多个字符
                #查询姓名中包含A的员工
                select ename,sal,hiredate
                from emp
                where ename like '%A%';
                
     #4.is null：判断是否为null
          #查询奖金为null的值
          select ename,comm
          from emp
          where comm is null; 
          
          #查询奖金不为null的
          select ename,comm
          from emp
          where comm is not null;

#练习3
  #1.查询员工姓名以W开头的员工姓名。
    select ename
    from emp
    where ename like 'W%';

  #2.查询员工姓名倒数第2个字符为T的员工姓名。
    select ename
    from emp
    where ename like '%T_';


  #3.查询奖金为空的员工姓名，奖金。
    select ename,comm
    from emp
    where comm is null;

#5.逻辑运算符
    #1.and：并且的关系，连接的两个表达式都为true，那么整个表达式为true
       select ename,sal,hiredate
       from emp
       where ename = 'ALLEN' and sal < 1000;
    #2.or：或者，只要数据表中有满足的条件，那么就有数据返回
       select ename,sal,hiredate
       from emp
       where ename = 'ALLEN' or sal < 1000;
    #3.not 取反
       #1.between and 取反
       select ename，sal
       from emp
       where sal not between 800 and 2000;
       
       #2.in 取反
       select ename,sal
       from emp
       where sal not in(800,1600);
       
       #3.like 取反
       select ename,sal
       from emp
       where ename not like '%A%';  
              
       #4.is null 取反
       select ename,comm
       from emp
       where comm is not null;

#6.逻辑运算的优先级：not  and  or
              select ename,sal,job
              from emp
              where job='SALESMAN'
              or
                    job='PRESIDENT'
              and 
                    sal>1500;       
 
#7.括号可以改变优先级
       
              select ename,sal,job
              from emp
              where (job='SALESMAN'
              or
                     job='PRESIDENT')
              and 
                     sal>1500;
                      
#8.排序:order by 
     #注意：执行顺序：order by在所有子句中最后一个执行
     #书写顺序；order by 在所有子句最后一个
     #1.asc:按照升序排序（默认排序）
         #1字符类型
             select ename,sal
             from emp
             order by ename asc;
             
         #2.数值类型；
               #null:在排序过程中，是最大的
               select ename,sal,comm
               from emp
               order by comm asc 
               
         #3.日期排序：
               select ename,sal,comm，hiredate
               from emp
               order by hiredate asc 
                 
     
     #2.desc：按照降序排序                      
             select ename,sal
             from emp
             order by ename desc; 
             
     #3.列别名排序；(有表达式的情况)
            select ename,sal,sal*10 newsal
            from emp
            order by newsal desc  
            
     #4.多列排序：将要排序的字段写在order by后面,用逗号隔开。 在部门的基础上对工资进行排序
            select ename,deptno,sal
            from emp
            order by deptno asc,sal desc 
            
     #5.根据结果集的字段序号排序（deptno在查询的第2位，sal在查询的第3位）
           select ename,deptno,sal
            from emp
            order by  2,3 desc  
            
#练习5：
       #1.查询部门在20或30的员工姓名，部门编号，并按照工资升序排序
       select ename,deptno
       from emp
       where deptno in(20,30)
       order by sal asc;
       
       #2.查询工资在2000-3000之间，部门不在10号的员工姓名，部门编号，工资，并按照部门升序，工资降序排序
       select ename,deptno,sal
       from emp
       where (sal between 2000 and 3000) and deptno<>10
       order by deptno asc,sal desc;
       
       #3.查询入职日期在81年至83年之间，职位以SALES或者MAN开头的员工姓名，入职日期，职位，并按照入职日期降序排序
       select ename,hiredate,job
       from emp
       where (hiredate between '01-1月-82' and '31-12月-83') and (job like 'SALES%' or job like 'MAN%')
       order by hiredate desc;
       
#9.总结：
       #1.where ：通过where可以限制条件查询
       #2.比较运算符：=  >  >=  <  <=  <>
       #3.比较运算符可以在字符、数值、日期等数据类型上使用
       #4.特殊比较运算符:
             #1.between   and ：三种数据类型都可以使用
             #2.in：跟集合中的数据进行匹配
             #3.like
                  #通配符：_代表任意一个字符
                  #通配符：%代表的是任意更多个
             #4：is null：判断是null
       #5.逻辑运算符：
             #1.and：并且的关系
             #2.or：只要满足条件都可以返回数据
             #3.not：取反
             #4.优先级：not and or
             #5.括号可以改变优先级
       #6.排序:order by
             #1.升序：asc
             #2.降序：desc
             #3.根据字段排序
             #4.根据别名排序
             #5.多列排序
             
#课后作业
#1.查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名、入职时间、职位。
    select ename,hiredate,job
    from emp
    where hiredate > '9-7月-82' and job <> 'SALESMAN';
    
#2.查询员工姓名的第三个字母是a的员工姓名。
    select ename
    from emp
    where ename like '__a%';
    
#3.查询除了10、20号部门以外的员工姓名、部门编号。
    select ename,deptno
    from emp
    where deptno not in(10,20);
    
#4.查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序。 
    select *
    from emp
    where deptno = 30
    order by sal desc,ename asc; #asc可以省略，默认升序
    
#5.查询没有上级的员工(经理号为空)的员工姓名。
    select ename
    from emp
    where mgr is null;
    
#6.查询工资大于等于4500并且部门为10或者20的员工的姓名、工资、部门编号。
    select ename,sal,deptno
    from emp
    where sal >= 4500 and deptno in (10,20);

单行函数（2020-07-21下午）

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
#1.函数：
#2.函数的分类
#单行函数
     #1.什么是单行函数:
          #输入一条数据返回一个结果
              #to_date('2020-10-10','yyyy-mm-dd')
     #2.单行函数的特征:
          #1.单行行数处理一条数据
          #2.返回一个结果
          #3.函数处理之后，数据类型可能发生改变to_date
          #4.单行函数可以写在select   where order by等子句的后面
          #5.单行函数中可以没有参数也可以有多个参数
          #6.函数之间可以嵌套
     #3.单行函数的分类
           #1.字符函数
                 #1.大小写转换函数
                      #1.lower:转小写
                         select lower('HELLO')
                         from dual; #dual是虚表，这个表没有任何数据，也不能保存任何数据
                         
                      #2.UPPER：转大写
                         select upper('hello')
                         from dual; 
                         
                      #3.initcap:首字母大写；
                         select initcap('hello')
                         from dual;     
                 
                 #2.字符处理函数
                       #1.concat： #连接两个值
                         select concat(ename,sal)
                         from emp;
                       #2.substr:截取指定长度的字符串
                           #1.从指定位置截取
                             select substr('hello',2,2)
                             from dual; 
                           #2.如果指定截取长度省略那么从指定位置截取到最后
                             select substr('hello',3)
                             from dual;
                           #3.如果指定位置为负数，那么从右往左数到指定位置，截取指定长度
                              select substr('hello',-4,3) #结果为ell
                              from dual; 
                       #3.length:返回字符串的长度
                              select length('hello')
                              from dual; 
                       #4.instr:返回子串在字符串中出现的位置
                       		 #instr（s1，s2，n1，n2）：返回s1中子串s2从n1开始，n2次出现的位置
                             #1.指定从哪一位置开始数并且指定子串第几次出现
                              select instr('hello','l',2,1)
                              from dual; 
                             #2.不指定位置，那么默认值都是1
                              select instr('hello','l')
                              from dual; 
                             #3.应用场景:如果有这个子串那么返回的结果是一个大于0的数值
                               select ename,sal,hiredate
                               from emp
                               where instr(ename,'A')>0;
                               
                        #5.lpad:左填充 
                                select lpad('hello',10,'*')
                                from dual;
                                      
                        #6.rpad:右填充:
                                select rpad('hello',10,'*')
                                from dual; 
                                
                        #7.trim:取出头部或者尾部的字符
                                 select trim('h' from 'hello')
                                 from dual; 
                                 
                        #8.replace:替换字符串中的某个字符
                                 select replace('hello','l','y')
                                 from dual;

单行函数（2020-07-23上午）

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
           #2.数值函数
                    #1.round:四舍五入
                       #第二参数如果是正数，那么从小数点右边数保留几位，
                       #第二个参数如果是负数，那么从小数点左边数保留几位。
                       select round(45.926,2),round(45.926,1),round(45.926,0),round(45.926,-1) #结果为45.93   45.9  46  50
                       from dual;
                    #2.trunc:截取指定位数的数据
                       select trunc(45.926,2),trunc(45.926,1),trunc(45.926,0),trunc(45.926,-1) #结果为45.92  45.9  45  40
                       from dual;
                       
                    #3.mod:取余数
                       select mod(45,2)
                       from dual;   
                       
           #3.日期函数
                    #1.RR格式:DD-MON-RR:oracle数据库默认的时间格式
                    #2.sysdate:返回的是当前的系统时间
                        select sysdate
                        from dual;
                        select * from student;
                    #3.系统时间的应用    
                        insert into student(sid,sname,birth,height)
                        values(1,'张三',sysdate,12.12);
                            
                    #4.时间的加减
                        #保证都是日期类型
                        select (sysdate-hiredate)/365 年,(sysdate-hiredate)/30 月
                        from emp;
                    #5.RR格式的世纪判断：
                          #1.指定年份是小，当前年份是小。取当前世纪。同小取当前世纪
                          #2.指定年份是大 ，当前年份是大。取当前世纪。同大取当前世纪
                          #3.指定年份是小，当前年份是大。取上个世纪。小，大取上世纪 
                          #4.指定年份是大，当前年份是小。取下一个世纪。大，小取下个世纪。
           
           #3.日期函数
                   #1.months_between:返回两个日期之间的月数
                     select months_between(sysdate,hiredate)
                     from emp; 
                   #2,add_months:在日期上加上指定月数
                     select hiredate, add_months(hiredate,2) newhiredate
                     from emp;
                   #3.next_day:返回日期的下一个星期几
                     select sysdate,next_day(sysdate,'星期五')
                     from  dual;
                   #4.last_day:返回是月份的最后一天
                     select last_day(sysdate)
                     from dual;  
                   #5.month:四舍五入到具体日期
                       #1.精确到月份
                       select hiredate ,round(hiredate,'month')
                       from emp;  
                       #2.精确到年份
                       select hiredate ,round(hiredate,'year')
                       from emp;
                   #6.EXTRACT:返回指定的日期
                       #1.返回月
                       select EXTRACT(month from hiredate)
                       from emp;
                       #2.返回年
                       select EXTRACT(year from hiredate)
                       from emp;
                       #3.通过to_char来转换 
                       select to_char(hiredate,'yyyy-mm')
                       from emp;    
                       
                      
			#4.转换函数：是指和日期之间是不能转换的。
                    #1.to_char:转换成字符串
                        #1.日期转换成字符串
                        select to_char(hiredate,'Day') 年
                        from emp;
                        #2.将数值转换成字符串
                            #注意：9的占位要大于转换的位数
                            select to_char(sal,'$9,999')
                            from emp;   
                                   
                    #2.to_date:转换成日期
                        select to_date('2020-7-23','yyyy-mm-dd')
                        rom dual;
                          
                    #3.to_number:转换成数值 
                        #转换的数据必须是有数值组成
                        select to_number('123')
                        from dual; 	                     
                                
            #5.通用函数 
           		 #1.nvl:处理null.
                     #注意：非null是不能处理
                     select nvl(comm,0)
                     from emp;
                              
                 #2.case:跟字段进行比较
                     select ename,deptno,
                     (
                       case deptno
                           when 10 then '销售部'
                           when 20 then '研发部'
                     	   else '无'
                           end
                      ) deptname
                      from emp;
                 #3.函数嵌套：
                     select nvl(to_char(mgr),'nomanager') 
                     from emp;  


		#总结：
               #1.字符函数：
                     #1.大小写转换函数
                     #2.字符处理函数
               #2.数值函数；
                     #1.round:
                     #2.trunc:截取指定长度
                     #3.mod:取余
               #3.日期函数：
                     #1.sysdate:当前系统时间
                     #2.日期的加减：都是日期类型
                     #3.日期函数:
                           #1.months_between:返回两个日期之间间隔约束
                           #2.add_months:加上指定约束
                           #3.next_day:返回下一个星期几
                           #4.last_day:当前日期的最后一天 
                           #5.EXTRACT:返回指定日期
                     #4.转换函数：
                           #1.to_char:转换成字符
                           #2.to_date:转换成日期
                           #3.to_number:转换成数值
                           #to_date和to_number都是跟字符之间的转换。数值和日期之间不能发生转换
                     #5.通用函数；
                            #1.nvl:处理null
                            #2.case:和字段匹配重新定义名字
                            #3.decode: 和字段匹配重新定义名字
                            #4.函数之间是可以嵌套的    
#课后作业
    #1.计算2000年1月1日到现在有多少月，多少周（四舍五入）
    select round(months_between(sysdate,'01-1月-00')),
           round(round(sysdate - to_date('2000-01-01','yyyy-mm-dd'))/7)
    from dual;
    
    #2.查询员工ENAME的第三个字母是A的员工的信息(使用2个函数)。
    select *
    from emp
    where substr(ename,3,1) = 'A';
    
    select *
    from emp
    where instr(ename,'A',3) = 3;
    
    #3.使用trim函数将字符串‘hello’，‘ Hello ’，‘bllb’，‘hello  ’分别处理得以下字符串ello、Hello、ll、hello。
    select trim('h' from 'hello'),
           trim(' ' from ' Hello '),
           trim('b' from 'bllb'),
           trim(' ' from 'hello ')
    from dual;
    
    #4.将员工工资按如下格式显示：123,234.00 RMB 
    select empno,to_char(sal,'999,999.99') || 'RMB'
    from emp;
    
    #5.查询员工的姓名及其经理编号，要求对于没有经理的显示“No Manager”字符串。
    select ename,nvl(to_char(mgr),'No Manager')
    from emp;
    
    #6.将员工的参加工作日期按如下格式显示：月份/年份。
    select empno,to_char(hiredate,'mm/yyyy')
    from emp;
    
    #7.在员工表中查询出员工的工资，并计算应交税款：
    #如果工资小于1000,税率为0，
    #如果工资大于等于1000并小于2000，税率为10％，
    #如果工资大于等于2000并小于3000，税率为15％，
    #如果工资大于等于3000，税率为20％。
    select empno,sal,
      	   (case  
            when sal>=0 and sal<1000 then sal*0
            when sal>=1000 and sal<2000 then sal*0.1
            when sal>=2000 and sal<3000 then sal*0.15
            when sal>=3000 then sal*0.2
            else 0 
            end
            ) 税率
  	from emp;
    
    #8.创建一个查询显示所有雇员的 ename和 sal。
    #格式化sal为 15 个字符长度，用 $ 左填充，列标签 SALARY。
    select ename,lpad(sal,15,'$') SALARY
    from emp;

分组函数（2020-07-23下午）

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
#1.分组函数：
     #1.处理多条数据，返回一个结果。
     #2.5个聚合函数：
        #1.min:返回最小值
             select min(sal) 最小工资
             from emp;
        
        #2.max:返回最大值
             select max(sal) 最大值
             from emp;
        
        #3.sum:求和
              select sum(sal)
              from emp;
        
        #4.avg:计算平均值
              select avg(comm)
              from emp;
        #5.count:计算总记录数
              select count(*)
              from emp;
              #除了count(*)其他聚合函数都是过滤null
              
        #6.count(distinct):取出重复之后的总记录数
               select count(distinct deptno)
               from emp; 
        #7.avg计算带有null的数据
              select avg(nvl(comm,0))
              from emp; 
#练习1：
         #1.查询部门20的员工，每个月的工资总和及平均工资。
           select sum(sal),avg(sal)
           from emp
           where deptno=20;
           
#2.group by:分组的子句
         #按照什么字段分组，那么久将这个字段放在group by后面
         #group by deptno:按照部门来分组   
        #1.group by 的书写位置：
              #如果没有where那么就写在from后面。否则写在where后面
              
        #2.查询每个部门的编号，平均工资
                  #按照部门分组，没有where过滤
              select deptno,avg(sal)
              from emp
              group by deptno;
              
        #3.注意：如果select中出现聚合函数，那么除了聚合函数其他的字段必须写在group by后面
              select deptno,job,avg(sal)
              from emp
              group by deptno,job;  
          
        #4.注意：group by后面出现的字段不一定要出现在select后面
              select avg(sal)
              from emp
              group by deptno,job;
              
#3.按照多列分组: 
              #将要分组的多个字段写在group by的后面。
              select deptno,job, avg(sal)
              from emp
              group by deptno,job;  
              
#4.分组函数的过滤:having
               #1.where和having都是对数据过滤的。
               #2.where是在分组之前过滤。where要写在group by的前面。过滤字段
                 select deptno,sal,avg(sal)
                 from emp
                 where deptno>10
                 group by deptno,sal;
               #3.having是在分组之后过滤的。要写在group by的后面。一般是过滤聚合函数
                 select deptno,sal,avg(sal) avgsal
                 from emp
                 where deptno>10
                 group by deptno,sal
                 having avg(sal)>2000
                 order by avgsal; 
                 
                 
#5.sql的书写顺序:
             select   from   where  group by  having  order by
#6.sql的执行顺序；
              from   where  group by  having  select  order by      
              
#课后作业
    #1.查询部门平均工资在2500元以上的部门名称及平均工资。
    select dname,avg(sal)
    from emp e,dept d
    where e.deptno = d.deptno
    group by dname
    having avg(sal) > 2500;
    
    #2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。
    select job,avg(sal)
    from emp
    where job not like 'SA'
    group by job
    having avg(sal) > 2500
    order by avg(sal) desc;
    
    #3.查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。
    select dname,round(min(sal),-1) 最低工资,
                 round(max(sal),-1) 最高工资
    from emp e,dept d
    where e.deptno = d.deptno
    group by dname
    having count(empno) > 2;
    
    #4.查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。 
    select job,sum(sal)
    from emp
    where job <> 'SALESMAN'
    group by job
    having sum(sal) > 2500;
    
    #5.显示经理号码和经理姓名，这个经理所管理员工的最低工资，没
    #有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。
    select em.empno,em.ename,min(e.sal)
    from emp e,emp em
    where e.mgr = em.empno(+)
    group by em.empno,em.ename
    having min(e.sal) >= 3000
    order by min(e.sal) desc;
    
    #6.写一个查询，显示每个部门最高工资和最低工资的差额。
    select deptno,max(sal) - min(sal)
    from emp
    group by deptno;

多表连接（2020-07-23下午）

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
#1.多表连接:
       #通过关联关系建立多个表的连接，获取多个表中的数据
       
#2.笛卡尔积:
       #为什么会产生笛卡尔积；
            #没有连接条件。会产生无效数据。
            #表中的每一条记录都会跟另一张表中的每一条记录产生关联
            #产生的记录数是两张表的乘积
                select e.deptno,d.deptno
                from emp e,dept d
            #关联查询时要避免产生笛卡尔积    
              
       
#3.多表连接的分类:
        #1.等值连接：通过=连接的条件
               select  e.ename,e.sal,e.deptno,d.deptno,d.dname
               from emp e,dept d
               where e.deptno=d.deptno; #关联关系 
               
               select * from emp;
               select * from dept;
            #1.通过and连接其他条件,其他限制条件通过and连接写在连接条件的后面
               select  e.ename,e.sal,e.deptno,d.deptno,d.dname
               from emp e,dept d
               where e.deptno=d.deptno and e.sal>2000;
               
            #2.如果两个表中有相同的字段名，可以用表别名作为前缀   
               select  e.ename,e.sal,e.deptno,d.deptno,d.dname
               from emp e,dept d
               where e.deptno=d.deptno and e.sal>2000;
               
            #3.多余两张表的连接
                select  e.ename,e.sal,e.deptno,d.deptno,d.dname
               from emp e,dept d,order o
               where e.deptno=d.deptno and o.id=e.oid and e.sal>2000; 
                 #如果有n张表那么要有n-1个关联条件
                 
                 
#练习1：
                 #1.写一个查询，显示所有员工姓名，部门编号，部门名称。
                       select e.ename,e.deptno,d.dname
                       from emp e,dept d
                       where e.deptno=d.deptno;
                       
                 #2.写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金
                       select e.ename,d.loc,e.comm
                       from emp e,dept d
                       where e.deptno=d.deptno and d.loc='CHICAGO' and comm is not null;     
                       
                 #3.写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。
                       select e.ename,d.loc
                       from emp e,dept d
                       where e.deptno=d.deptno and ename like '%A%'

多表连接（2020-07-24上午）

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
 #2.非等值连接：between and 
                      select e.ename,e.sal,s.grade
                      from emp e,salgrade s
                      where e.sal between s.losal and s.hisal;
#练习2：
       #1.查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。
                      select e.empno,e.ename,e.sal,s.grade,d.loc
                      from emp e,salgrade s,dept d
                      where e.deptno=d.deptno 
                      and e.sal between s.losal and s.hisal
                      order by s.grade asc; 
                          
        
        #3.外连接(+)
              #什么是基准表：多表连接时，基准表中的数据会全部显示。即使没有匹配上的。
              #(+):加号的对边是基准表，基准表中的数据会全部显示，加号边跟基准表匹配
              #如果匹配不上，那么加号会产生一个万能行，由万能行跟匹配不上的数据进行匹配。这个万能行由空值组成。  
        
              #查询所有雇员姓名，部门编号，部门名称，包括没有员工的部门也要显示出来
                 select e.ename,e.empno,d.dname,d.deptno
                 from emp e,dept d
                 where e.deptno(+)=d.deptno
                 
        #4.自身连接；
              #同一张表不同字段的连接（确保两个字段的数据类型是一致的）
              #关联条件：员工的经理编号=经理的员工编号
                 select * from emp;
                 select  worker.ename||' leader is '||manager.ename
                 from emp worker,emp manager
                 where  worker.mgr=manager.empno;
                  
#SQL99写法:
          #所有的关系型数据库都支持的写法。
       #1.cross join:交叉连接
            #会产生笛卡尔积，所以不使用
            
       #2.natural join:自然连接
            #关联的两个表中必须要有相同的字段，并且字段的数据类型要一样
            #关联条件：是两个表中共有的字段
            select  e.ename,d.dname
            from emp e
            natural join dept d
            
       #3.using:
            #通过指定两个表中共有的字段并且数据类型一致
            select e.ename,d.dname
            from emp e join dept d using(deptno) 
            
      #on:书写关联条件
            #将关联条件从where子句中分开。提高代码的可读性
            
            select e.ename,d.dname,s.grade
            from emp e
            join dept d
            on e.deptno=d.deptno
            join salgrade s
            on e.sal between s.losal and s.hisal;  
            
      #左连接；（重点）
            #以左表为基准，左表中的数据全部显示，右表跟左表匹配，匹配不上用空值填充
            select e.ename,e.deptno,d.deptno,d.dname
            from emp e left outer join dept d
            on (e.deptno=d.deptno)
                  
             
            select e.ename,e.deptno,d.deptno,d.dname
            from emp e left  join dept d
            on (e.deptno=d.deptno)
            
       #右连接：
           #以右边表为基准，左表跟右表匹配，匹配不上用空值填充
            select e.ename,e.deptno,d.deptno,d.dname
            from emp e right  join dept d
            on (e.deptno=d.deptno)
            
        #全外连接：full outer join
            #能匹配上的数据进行匹配。两个表中不能匹配的数据也显示
             select  e.ename,e.deptno,d.deptno,d.dname
             from emp e full outer join dept d 
             on(e.deptno=d.deptno)   
             
             
#课后作业
#1.显示员工SMITH的姓名，部门名称，直接上级名称
    select e.ename,d.dname, em.ename 直接上级名称
    from emp e,dept d,emp em
    where e.ename = 'SMITH'
    and e.deptno = d.deptno
    and e.mgr = em.empno;
    
#2.显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级。
    select e.ename,d.dname,e.sal,s.grade
    from emp e,dept d,salgrade s
    where e.deptno = d.deptno
    and (sal between s.losal and s.hisal)
    and s.grade > 4;
    
#3.显示员工KING和FORD管理的员工姓名及其经理姓名。
    select e.ename 员工姓名,em.ename 经理姓名
    from emp e,emp em
    where em.ename in ('KING','FORD')
    and e.mgr = em.empno;


#4.显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早。
    select e.ename,e.hiredate,em.ename,em.hiredate
    from emp e,emp em
    where e.mgr = em.empno
    and e.hiredate >em.hiredate;

子查询（2020-07-24下午）

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
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
#1.子查询
#2.什么是子查询:
     #在一个sql中包含多个select语句
#3.子查询的执行顺序：
     #子查询分为主查询和子查询，执行时子查询作为一个结果，要先于主查询执行
     
#4.为什么使用子查询：
     #1.当需要的结果不知道的情况，要通过子查询将这个结果查询出来
       #eg:查询工资比Jones工资高的员工信息？
          #Jones的工资在这里是不知道的。查出jones的工资是多少。 
          #只有先知道Jones的工资是多少，才能拿这个结果作为条件查询
            #1.先查出jones的工资是多少
                select sal
                from emp
                where ename='JONES';
            #2.编写主查询    
           select ename,sal
           from emp
           where sal>(select sal
                      from emp
                      where ename='JONES')
    #2.子查询的书写规则:
           #1.子查询要用括号括起来
           #2. 子查询写在比较运算符的右边
           #3.如果子查询返回的是一条记录那么使用单行运算符（=  >  >=  <  <= <>）
                    
    #3.运算符：
          #1.单行运算符;
              #如果子查询返回的是一条记录那么使用单行运算符（=  >  >=  <  <= <>）
          #2.多行运算符：
              #如果子查询返回的是多条记录那么使用多行运算符（in any all） 
              
    #4.子查询的书写场景
          #where 
              #显示和雇员7369从事相同工作并且工资大于雇员7876的雇员的姓名和工作。
                 #1.先查询7369的工作
                   select job
                   from emp
                   where empno=7369;
                 #2.查询7876的员工工资
                   select sal
                   from emp
                   where sal=7876
                   
                 #3.编写主查询:
                   select empno, ename,job
                   from emp
                   where job=(
                             select job
                             from emp
                             where empno=7369)
                   and sal>(
                            select sal
                            from emp
                            where empno=7876); 
                            
              #查询工资最低的员工姓名，岗位及工资
                  #1.查询最低工资：
                     select min(sal)
                     from emp; 
                  #2.编写主查询
                     select  ename,job,sal
                     from emp
                     where sal=(
                      select min(sal)
                       from emp
                     );                   

          #from
          
          #having  
                  #查询部门最低工资比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 
                       );
                         
                 #查询哪个部门的员工人数 高于各部门平均人数。
                     #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
                        ) ;       

    #5.子查询的分类
          #单行子查询
                    
          #多行子查询
               #in： 
                  #为什么使用多行运算符
                    #子查询中的结果有多个。
                      #1.每个部门的最低工资
                        select deptno, min(sal)
                        from emp
                        group by deptno
                        
                      #2.编写主查询
                        select empno,ename
                        from emp
                        where sal = (
                               select  min(sal)
                               from emp
                               group by deptno
                        ); 
                      #只要子查询中返回是多条记录那么必须要用多行子查询  
                      
                   #查询是经理的员工姓名，工资 
                        #1.查询经理编号
                             select mgr
                             from emp;
                             
                        #2.编写主查询
                            select ename,sal
                            from emp
                            where empno in(
                                select mgr
                                 from emp
                              )
                              
          #any：
                 # =any:等于子查询中的任意一个相当于in
                      select ename,sal
                            from emp
                            where empno =any(
                                select mgr
                                 from emp
                              )
                 # >any:大于子查询中的任意一个
                     #查询部门编号不为10，且工资比10部门任意一名员工工资高的员工编号，
                     #姓名，职位，工资。
                        #1.查询10号部门的工资是多少
                            select sal
                            from emp
                            where empno=10
                        #2.编写主查询：
                            select ename,job,sal
                            from emp
                            where sal>any(
                                select sal
                                 from emp
                                where deptno=10
                            ) and deptno<>10;   
                     
  
                 
                 # <any:小于子查询中的任意一个  
                     #查询部门编号不为10，且工资比10部门任意一名工资低的员工编号，
                     #姓名，职位，工资。
                        #1.查询10号部门的工资是多少
                            select sal
                            from emp
                            where empno=10
                        #2.编写主查询：
                            select ename,job,sal
                            from emp
                            where sal<any(
                                select sal
                                 from emp
                                where deptno=10
                            ) and deptno<>10;                      
                #all:所有:
                  #>all:大于所有
                        #查询部门编号不为10，且工资比10部门所有员工工资高的员工编号，
                        #姓名，职位，工资。
                        
                             #1.查询10号部门的工资
                               select sal
                               from emp
                               where deptno=10
                             #2.主查询
                               select ename,job,sal
                               from emp
                               where sal >all(
                               
                                     select sal
                                     from emp
                                     where deptno=10
                               )  and 
                               deptno<>10;

                   
                   # <all:小于所有
                        #查询部门编号不为10，且工资比10部门所有员工工资低的员工编号，
                        #姓名，职位，工资。
                        
                           
                             #1.查询10号部门的工资
                               select sal
                               from emp
                               where deptno=10
                             #2.主查询
                               select ename,job,sal
                               from emp
                               where sal <all(
                               
                                     select sal
                                     from emp
                                     where deptno=10
                               )  and 
                               deptno<>10;

                   
                   
                   # =all:等于所有
                
                        #查询部门编号不为10，且工资和10部门所有员工工资相等的员工编号，
                        #姓名，职位，工资。

                
                             #1.查询10号部门的工资
                               select sal
                               from emp
                               where deptno=10
                             #2.主查询
                               select ename,job,sal
                               from emp
                               where sal =all(
                               
                                     select sal
                                     from emp
                                     where deptno=10
                               )  and 
                               deptno<>10;
                
                
                         
          #多列子查询                 
                 #1.多个列进行比较
                 #2.通常使用in比较运算符  
                 #3.多列子查询的列要用括号括起来 
                 #4.（注意：） 多列之间是并且的关系    
            #查询出和1981年入职的任意一个员工的部门和职位完全相同员工姓名、
            #部门、职位、入职日期,不包括1981年入职员工。
            
               #1.查询1981年入职员工的部门和职位
                  select deptno,job
                  from emp
                  where to_char(hiredate,'yyyy')='1981';
               #2.主查询
                  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';   

       #6.子查询中有null:
             #注意:如果子查询中有空值那么不能使用not in
             
             #每个部门的平均工资
             SELECT   deptno, avg(sal) salavg
                    FROM     emp
                    GROUP BY deptno
       #from中的子查询             
            #自己部门的员工和自己部门的平均工资比较        
                    10 24.5      ww 15
                                 se   20   
                                 qw 30
                                 
                    
                    20 30.6       yu  30
                                  ty  50
                                  hu  20 
                             
                    30 45.5
             #查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资
                 #1.查询每个部门的平均工资
                    select deptno,avg(sal)
                    from emp
                    group by deptno
                    
                 #2.主查询
                    select e.ename,e.sal,e.deptno,d.avgsal
                    from emp e,(
                                select deptno,avg(sal) avgsal
                                from emp
                                group by deptno
                                ) d
                    where e.deptno=d.deptno
                      and e.sal> d.avgsal.
                      
#rownum:这是一个伪列：
      #特点：
          #1.从1开始产生
          #2.主要用作分页
                                   
   #分页：
          #1.两层分页：
              select d.* from
              (select ename,sal,deptno,rownum r
              from emp) d
              where r>(2-1)*5 and r<=2*5;
              #index:当前页：变量  1  2  3   4
              #size:每页固定条数  5
              #index-1)*size:上页的最后一条数据
              #index*size:当前页的最后一条数据
              
          #2.三层分页;
               #在什么情况下使用三层：
                   
              select d.* from
              (select ename,sal,deptno,rownum r
              from emp order by sal) d
              where r>(2-1)*5 and r<=2*5;  
              
                #为什么rownum顺序会发生改变：
                   order by输出数据的顺序重新排列
                #解决的方式：将rownum放在主查询中，让order by先于rownum执行
                #就是排序之后在产生rownum .可以将rownum放在主查询中 
                
              select d.*,rownum r from
              (select ename,sal,deptno
              from emp order by sal) d
              where r>(2-1)*5 and r<=2*5; 
              #注意：rownum的别名必须放在子查询中，才能被识别
              select * from
                 （select d.*,rownum r from
                  (select ename,sal,deptno
              from emp order by sal) d）
              where r>(2-1)*5 and r<=2*5; 
              
              
#课后作业
#1.查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。
    #查询编号为7782的员工工资
    select sal 
    from emp 
    where empno = 7782;
    
    #查询编号为7369的员工从事的工作
    select job 
    from emp 
    where empno = 7369
    
    #主查询
    select e.empno,e.ename,e.sal
    from emp e
    where e.sal > (
                select sal 
                from emp 
                where empno = 7782)
    and e.job = (
                select job 
                from emp 
                where empno = 7369); 

#2.查询工资最高的员工姓名和工资。 
    #查询最高工资
    select max(sal)
    from emp;
    
    #主查询
    select ename,sal
    from emp
    where sal = (
                 select max(sal)
                 from emp);
                 
#3.查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。
    #查询10号部门最低工资
    select min(sal)
    from emp
    where deptno = 10;
    
    #主查询
    select d.deptno,d.dname,min(sal)
    from emp e,dept d
    where e.deptno = d.deptno
    group by d.deptno,d.dname
    having min(sal) > (
           select min(sal)
           from emp
           where deptno = 10
           );    
    
#4.查询员工工资为其部门最低工资的员工的编号和姓名及工资。
    #查询部门最低工资
    select min(sal) minsal
    from emp
    group by deptno;
    
    #主查询
    select empno,ename,sal
    from emp e,( select deptno,min(sal) minsal from emp group by deptno) ms
    where e.deptno = ms.deptno
    and e.sal = ms.minsal;
    
#5.显示经理是KING的员工姓名，工资。
    #查询KING的编号
    select empno
    from emp
    where ename = 'KING';
    
    #主查询
    select ename ,sal
    from emp
    where mgr = (
                select empno
                from emp
                where ename = 'KING');
                
#6.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。
    #查询SMITH参加工作的时间
    select hiredate
    from emp
    where ename = 'SMITH';
    
    #主查询
    select ename,sal,hiredate
    from emp
    where hiredate > (
                     select hiredate
                     from emp
                     where ename = 'SMITH'
                     );
                     
#7.使用子查询的方式查询哪些职员在NEW YORK工作。
    #查询在NEW YORK工作的部门
    select deptno
    from dept
    where loc = 'NEW YORK';
    
    #主查询
    select *
    from emp
    where deptno = (
                 select deptno
                 from dept
                 where loc = 'NEW YORK'
                   );
                   
#8.写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期，查询结果中排除SMITH。
    #查询SMITH所在的部门
    select deptno
    from emp
    where ename = 'SMITH';
    
    #主查询
    select ename,hiredate
    from emp
    where deptno = (
                   select deptno
                   from emp
                   where ename = 'SMITH'
                   )
    and ename <> 'SMITH';
    
#9.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。
    #全体职员平均工资
    select avg(sal)
    from emp;
    
    #主查询
    select empno,ename
    from emp
    where sal > (
                select avg(sal)
                from emp
                );
                
#10.写一个查询显示其上级领导是King的员工姓名、工资。
    #查询KING的编号
    select empno
    from emp
    where ename = 'KING';
    
    #主查询
    select ename,sal
    from emp
    where mgr = (
                select empno
                from emp
                where ename = 'KING'
                );
#11.显示所有工作在RESEARCH部门的员工姓名，职位。
    #查询RESEARCH部门的部门编号
    select deptno
    from dept
    where dname = 'RESEARCH';
    
    #主查询
    select ename,job
    from emp
    where deptno = (
                   select deptno
                   from dept
                   where dname = 'RESEARCH'
                   );
#12.查询每个部门的部门编号、平均工资，要求部门的平均工资高于部门20的平均工资。
    #查询部门20的平均工资
    select avg(sal)
    from emp
    where deptno = 20;
    
    #主查询
    select deptno,avg(sal)
    from emp
    group by deptno
    having avg(sal) > (
                      select avg(sal)
                      from emp
                      where deptno = 20
                      );
#13.查询大于自己部门平均工资的员工姓名，工资，所在部门平均工资，高于部门平均工资的额度。
    #查询每个部门的平均工资
    select deptno,avg(sal) avgsal 
    from emp 
    group by deptno;
    
    #主查询
    select ename,sal,avgsal,sal-avgsal
    from emp e,(select deptno,avg(sal) avgsal 
               from emp 
               group by deptno) d 
    where e.deptno = d.deptno and sal > avgsal;

约束（2020-07-24下午）

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
#约束:constraint:
#约束的命名规则：表名_字段名_约束名
   #1.什么约束：
         #给字段加上一些规则，保证数据的完整性
   #2.约束的分类：
        #1.not null:非空约束
              #指定的字段不能有空值
                create table student(
                   sid number(4),
                   sname varchar2(20) not null
                )
                insert into student(sid,sname) values(1,'张三')
                select * from student
        #2.unique:唯一性约束
              #特点:添加的数据不能重复
              #列级别：在字段上加上约束
               drop table student; 
                create table student(
                   sid number(4),
                   sname varchar2(20) constraint student_sname_uq unique
                )
                
               #表级别   b
                  create table student(
                   sid number(4),
                   sname varchar2(20),
                   constraint student_sname_uq unique(sname)
                );
                 insert into student(sid,sname) values(2,'张三')
             
        #3.primary key:主键约束
              #特点：
                  #1.唯一性
                  #2.非空性
               drop table student;   
              #列级别
                  create table student(
                   sid number(4) constraint student_sid_pk primary key,
                   sname varchar2(20)
                  
                );
                
              #表级别
                   create table student(
                   sid number(4),
                   sname varchar2(20),
                   constraint student_sid_pk primary key(sid)
                  
                );  
                
                 insert into student(sname) values('李四')
        #4.foreign key:外建约束
                 #特点：
                 #1.被从表引用的字段必须是主键
                 #2.创建表时要先创建主表在创建从表
                 #3.添加数据时要先添加主表的数据在添加从表中的数据
                 #4.外建只能添加主表中有的数据或者空值
                 #5.删除数据：如果主表中的数据被从表引用那么要先删除子表中的数据在删除主表中的数据
                 #6.删除表：要先删除子表在删除主表
                  create table class(
                     cid number(4) primary key,
                     cname varchar2(10)
                  ) 
                  
                  create table student(
                     sid number(4) primary key,
                     sname varchar2(10),
                     cid number(4) constraint student_cid_fk REFERENCES class(cid)  
                  ) 
                  #添加class的数据
                   insert into class(cid,cname) values(1,'java');
                   insert into class(cid,cname) values(2,'php');
                  #添加学生表数据
                   insert into student(sid,sname) values(3,'张三');
                  #删除主表中的数据
                    delete from class where cid =1;#不能删除
                    #先删除子表中引用的数据
                    delete from student where cid=1;
                    
                    #删除class:
                     drop table class;
                    #先删除子表
                     drop table student 
                     
               #表级别的外建约束：
                    create table student(
                     sid number(4) primary key,
                     sname varchar2(10),
                     cid number(4),
                     constraint student_cid_fk foreign key(cid)
                     REFERENCES class(cid) on delete cascade
                  )        
        
        #5.check:检查性约束 
               #特点:可以指定值在某个范围内  
                 drop table student;
                 create table student(
                   sid number(4) constraint student_sid_pk primary key,
                   sname varchar2(20),
                   sal number(4) constraint student_sal_ck check(sal between 2000 and 3000),
                   sex varchar2(4) constraint student_sex_ck check(sex in ('男','女')) 
                  
                );  
        
                insert into student(sid,sname,sal,sex) values(1,'李四',2100,'男')
                select * from student


#课后作业
#1.简述5种约束的含义。
   not null: 非空约束，指定某列的所有行数据不能包含空值
   unique: 唯一性约束，指定列或者列的组合的所有行数据必须唯一
   primary key: 主键约束，表的每行的唯一性标识，指定列或者列的组合的所有行数据必须唯一
   foreign key: 外键约束，在列及引用列上建立的一种强制依赖关系
   check: 检查性约束，在列上指定一个必须满足的条件

#2.创建学生关系sc，包括属性名：
#选课流水号 数值型 主键；
#学生编号 非空 外键
#课程编号 非空 外键；
#成绩     0-100之间；

    create table sc(
        selectno number primary key,
        sno varchar2(10) not null constraint sc_sno_fk references student(sno),
        cno varchar2(10) not null constraint sc_cno_fk references course(cno),
        grade number constraint sc_grade_ck check(grade between 0 and 100)
    );

#3.创建copy_emp，要求格式同emp表完全一样，不包含数据。
    create table copy_emp
    as
    select *
    from emp
    where 1 = 0;

#4.创建copy_dept，要求格式同dept表完全一样，不包含数据。
    create table copy_dept
    as
    select *
    from dept
    where 1 = 0;

#5.设置copy_emp 表中外键deptno，参照copy_dept中deptno,语句能否成功,为什么？
    不能，因为已经关联了dept中的外键。

#6.追加copy_dept表中主键deptno
    alter table copy_dept
    add constraint copy_dept_deptno_pk primary key(deptno)

序列、索引（2020-07-27上午）

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
#1.序列
   #通过一定的规则，使主键自动增加
   
#2.创建序列
      create sequence test_seq
         start with 1   #默认值是1  
         increment by 2 #默认值是1
         maxvalue 200
         minvalue 1
         cache 10;
      #创建一个简单的序列 
      create sequence test_seq1;   
#3.序列的两个属性         
       #currval:返回序列的当前值
           #注意：在使用currval之前必须先试用nextval。产生一序列值
           select test_seq.currval
           from dual;
       
       #nextval:产生下一个序列值 
           select test_seq.nextval
           from dual;
           
#4.序列的应用场景：
          insert into student(sid,sname,sage)
          values(test_seq.nextval,'王五',23);
          
          select * from student;
          
#5.删除数据库对象：
         drop sequence test_seq;
         
#6.index:索引    
       #好处：提高查询效率
       
       #1.自动创建：
            #当创建主键和unique的时候会自动创建索引
            
       #2.手动创建；
            #1.创建单列索引
             create index  idx_student_sname
             on student(sname);
            #2.创建多列索引
              create index idx_student_sname_sage
              on student(sname,sage); 
              
#7.索引的缺点:
         #1.索引占用空间
         #2.降低dml的操作速度
         
#8.删除索引:
          drop index  idx_student_sname_sage;

视图（2020-07-27上午）

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
#1.视图的概念:
  #1.是一个命名查询，可以改变基表中的数据。
  #2.视图是逻辑上来自一个或者多个表中的数据
#2.视图优点：
    #可以简化查询，访问方式和表一样
    
#3.创建视图：
      #创建一个视图v_emp10，通过该视图只能查看10号部门的员工编号，员工姓名，职位。
      create view e_emp10
      as
      select empno,ename,job
      from emp
      where deptno=10;
      #查询视图:
       select * from e_emp10;
       
#练习;
    #创建一个视图，通过该视图可以查询到工资在2000-5000内并且姓名中包含有A的员工编号，姓名，工资。
      create view v_emp1
      as
      select empno,ename,sal
      from emp
      where sal between 2000 and 5000
      and ename like '%A%';
      
#4.通过列别名创建视图
      #:表头是以别名显示
      create view v_emp2
      as
      select empno no,ename name,sal newsal
      from emp
      where deptno=30; 
      
      select * from v_emp2;  
      
#5.通过多张表创建视图
      create view v_emp3
           (deptname,minsal,maxsal,avgsal)
      as
      select d.dname,min(sal),max(sal),avg(sal)
      from emp e,dept d
      where e.deptno=d.deptno
      group by d.dname; 
      
      select * from v_emp3; 
  
#练习2：    
      
#1.创建一个视图，通过该视图可以查询到工作在NEW YORK和CHICAGO的员工编号，姓名，部门编号，入职日期。
      create view v_emp4
      as
      select e.empno,e.ename,e.deptno,e.hiredate
      from emp e,dept d
      where e.deptno=d.deptno and d.loc in('NEW YORK','CHICAGO'); 

#2.创建一个视图，通过该视图可以查询到每个部门的部门名称及最低工资。              
       create view v_emp5
       as
       select d.dname,min(e.sal) minsal
       from emp e,dept d
       where e.deptno=d.deptno
       group by d.dname
       ;
       select * from v_emp5;
       #注意：常见视图时，聚合函数要定义列别名
       
#6.修改视图 create or replace view xx:
       create or replace view v_emp5
         (deptname,newminsal)
       as
       select d.dname,min(e.sal) minsal
       from emp e,dept d
       where e.deptno=d.deptno
       group by d.dname
       ; 
       
#7.从视图中删除基表中的数据：
      #如果视图中包含 group by或者distinct是不能删除的。
      create view s_stu1
      as
      select * from student;
      select * from student;
      #通过视图删除
      delete from s_stu1 where sid=7;             
       
      
#8.通过修改和添加视图中的数据也可以改变基表中的数据
      #如果视图中包含 group by或者distinct是不能删除的。
      
#9.通过 with check option可以追加约束。
         #指定数据的范围。
         
         
#10.with reade only:只读 .只能查询

              
 #复习: 
     #1.数据操作语言
           #insert
               #1.指定字段添加
               #2.不指定字段（添加所有字段）
           #update
                #1.修改单个字段
                #2.修改多个字段（每个字段用逗号隔开）
           #delete
                #删除表中的记录
                
           #事务:
               #碰到insert  update  delete必要提交事务或者回滚事务
               #commt:事务的提交
               #rollback:事务的回滚 
               #事务的4个特性(会背) 
               
       #序列和索引：
               #1.创建序列
                      #create sequence xx
                      #默认创建：start with和increment by都说1.并且不循环
                      #通过指定值创建
               #2.序列的两个属性:
                      #currval:返回当前序列值
                      #nextval：产生下一个序列值
                      #注意：只有先使用nextval之后产生一个序列值，才能在使用currval.查询当前序列值
               #3.序列的应用场景:
                       #主要用来添加表中的主键值
                       
               #4.索引:
                    #1.索引的创建:
                        #create index xx
                    #2.索引可以创建在单个字段上
                         #create index xx on 表名(字段名)
                    #3.索引可以创建在多个字段上                                        
                          #create index xx on 表名(字段名,字段名)
                    #4.索引的应用场景：
                          #建立在经常被查询的数据上
                          #经常用作增删改查的数据不能建立索引
                          #数据量比较大的情况
                          
                     #5.索引的缺点：
                          #1.占用内存 
                          #2.降低dml操作  
                          
         #视图；
              #1.视图是逻辑上来自一个或者多个表中的数据  
              #2.创建视图;
                  # create view xx as 子查询
              #3.修改视图                             
                   # create or replace view xx as 子查询
              #4. 视图中表头的设置:
                   #1.在子查询中直接给字段起别名
                   #2.视图名后面加上括号。别名要跟字段名对应 
                   
              #5.修改视图中的数据会改变基表中的数
              
              #6.通过with check option constraint可以添加约束，指定添加数据的范围
              
              #7.with read only:只读，不能修改

数据操作语言（2020-07-27上午）

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
#数据操作语言
    #1.添加数据
          create table student(
             sid number(4) primary key,
             sname varchar2(10),
             sage number(4)
          )
          #1.不指定列,那么表中所有的字段都要添加
          insert into student
          values(2,'张三1',21)
          
          #2.指定字段添加
          insert into student(sid,sname)
          values(3,'李四')
          select * from student;
    #2.修改数据
          #1.单列修改
         update student set sname='王五'
         where sid=1; 
          #2.多列修改,每个字段之间要用逗号隔开
         update student set sname='王五',sage=18
         where sid=1; 
    
    #3.删除数据
         delete from student
         where sid =1;
         
#事务:
    #1.提交事务：
         #commit; 
         commit; 
         
    #2.rollback;            
         rollback;
         
    #3.在增删改查的时候磁能开启事务
        #一定要结束事务 
        
    #4.事务的4个特性:
         #1.原子性
         #2.一致性
         #3.隔离性
         #4.持久性
本文作者： LiJing
发布时间： 2020-07-20
最后更新： 2023-01-27
本文标题： Oracle数据库相关(学校实训上课内容)(整理完)
本文链接： https://blog-yilia.xiaojingge.com/posts/c4d86e7d.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

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