Oracle笔记-T

苦力怕qyby
于 2023-03-15 18:06:25 发布
阅读量218
点赞数
文章标签：数据结构 Powered by 金山文档
本文链接：https://blog.csdn.net/FYH003/article/details/129562759
版权
/*Linux上编写SQL语句：
sqlplus
输入数据库登录用户：system（管理员）   密码:123456     退出返回命令行：exit
*/

--注释：对SQL语句作用的说明，在运行过程不起作用
--  以--开头为单行注释
/* 
多行注释
多行注释
多行注释 
*/ 

--数据库作用：存储软件运行过程中产生的数据（数字、符号、文字、声音、图像....）
--数据库相关术语
数据库           database  DB
数据库管理系统   Database Management System  DBMS  
数据库管理员     Database Administrator   DBA

主流数据库：
分类：关系型数据库和非关系型数据库
关系型数据库：Oracle、Mysql、SQLserver  （用关系模型来组织数据的）
非关系型数据库： Mongolia DB 、Redis  （分布式的）

SQL（Structured  Query Language  结构化查询语言）
(1)数据定义语言(DDL)：create、alter、drop 
(2)数据操纵语言（DML）：insert、update、delete、select
(3)数据控制语言（DCL）:grant、revoke
(4)事务控制语言（TCL）:commit、rollback、savepoint

oracle用户与权限管理
（1）创建用户   
语法结构：create user 用户名 identified by 密码 account unlock|lock
create user test  identified by 123456 account unlock;--创建一个test用户并且不锁定

(2)用户授权
connect权限：建立与数据库之间的连接，不能创建表，只能对原有表的增、删、改、查
resource权限：可以创建表以对原有表的增、删、改、查
语法结构：grant connect,resource to 用户名
grant connect to test; --给test授权连接权限
grant resource to test;--给test授权访问资源权限

(3)回收权限
语法结构：revoke 权限 from 用户名
revoke connect,resource from test;--回收test用户连接与访问资源权限

（4）修改用户密码
语法：alter user 用户名 identified by 新密码
alter user test identified by 666666;

(5)锁定或解锁用户
语法：alter user 用户名 account lock|unlock
alter user test account lock;  --锁定用户
alter user test account unlock;  --解锁用户

（6）删除用户
语法：drop user 用户名 cascade;
drop user test cascade;--删除test用户

--练习
1.新建一个用户tom，密码设置为love, 解锁状态
create user tom identified by love account unlock;

2.给用户tom授权连接、访问资源的权限
grant connect,resource to tom;

3.锁定tom用户的账户，使其不能登录
alter user tom account lock;

4.给tom用户解锁
alter user tom account unlock;

5.收回tom用户的登录、访问资源的权限
revoke connect,resource from tom;

6.修改tom用户密码为lw
alter user tom identified by lw;

7.删除用户tom
drop user tom cascade;

Oracle数据类型
（1）字符串类型
char(长度)     固定长度的字符串类型     name  char(10)       最长输入不能超过10个字符，不使用的用空格填充，提高查询速度。
varchar2(长度) 可变长度的字符串类型     name  varchar2(10)   最长输入不能超过10个字符，不使用的会回收，节省磁盘空间。

(2)数字类型
number    可以输入整数或小数                  age number
number(3) 表示最大只能输入三位整数  <=999
number(5,2) 5代表整数与小数的有效位数，2代表小数的有效位数   123   12.23  1234（x） 23.123>23.12 

(3)日期时间类型
 date       birth date 
 
(4)LOB 数据类型主要存储一些非结构化的数据，例如：图形，声音（最大4G） 

--创建表
语法：create table 表名（列名1 类型 【约束】,列名2 类型 【约束】,列名3 类型 【约束】......）
create table student(id number,name char(20),age number,birth date); --创建一个student表
select * from student; --查询表中的所有数据

--插入表数据
语法：insert into 表名（列名1，列名2，列名3...）values(列名1的值,列名2的值，列名3的值.....)
insert into student(id,name,age,birth) values(1000,'张三',20,'1998-8-5');
insert into student values(200,'李四',21,'2000-2-6')  --如果后面的数据是一一对应的，前面的列名可以省略
insert into student(name,id,birth,age) values('王五',3000,'1999-5-9',22)

商品表 goods
商品编号 gid     商品名称 gname    单价 pic   生产日期 pd   
create table goods(gid number(4),gname char(10),pic number,pd date);
insert into goods values(1000,'花生',5.98,'20230301')
select * from goods;

--约束
（1）主键约束    primary key  在一个表中只有一列可以设置为主键约束（不能为空，不能重复）
（2）非空约束    not null     不能为空
（3）唯一约束    unique       内容不能重复，可以为空
（4）条件约束    check        只能输入满足条件的数据
（5）默认约束    default      如果不填值，就给一个默认值
（6）外键约束    references   主表与从表之间的数据完整性约束

create table stu (id number primary key,name char(20) not null,
sex char(10) check(sex='男'or sex='女'),addr varchar2(100) default '宝安区',tel number unique);

insert into stu values(1000,'王小五','男','龙华区',13888888888);

select * from stu;

insert into stu(id,name,sex,tel) values(1006,'李小明','男',13888888883);

create table score(
id number references stu(id),  --外键约束  stu表id字段
course char(10),
score number(3)
)

insert into score values(1006,'数学',98)

select * from score;

（2）创建表后添加约束
语法:alter table 表名 add constraint 约束名  约束内容
alter table score add constraint ckscore check(score>=0 and score<=100);

insert into score values(1006,'语文',50)

（3）删除约束
语法：alter table 表名 drop constraint 约束名
alter table score drop constraint ckscore;
insert into score values(1006,'英语',150)

（4）修改表名
语法：rename 原表名 to 新表名
rename stu to stu1

(5)添加列
语法：alter table 表名 add 列名 类型 【约束】
alter table score add ksdate date
select * from score;

(6)删除列
语法：alter table 表名 drop column 列名
alter table score drop column ksdate;


--练习
create table class_info (
c_id char(20) primary key,
c_type char(6) check(c_type='UI' or c_type='测试' or c_type='开发'),
c_position  char(20) not null,
start_time  date ,
c_status char(6) check(c_status='在读' or c_status='毕业') 
);

select * from class_info;

alter table class_info modify start_time date not null;
alter table class_info modify start_time date null;
alter table class_info add sex char(3) check(sex='男' or sex='女')
insert into class_info values(1,'UI',45,'2002-1-1','毕业','男')
rename class_info to class_new

--删除表（把整个表删掉）
语法：drop table 表名
drop table class_new

---------------------------------第二天-------------------------------------------

--查询：用select命令从数据库的表中提取满足条件的信息
查询语法：
select *|列名|表达式
from 表名【别名】
where 条件
group by 列名
having 条件
order by 列名 【asc|desc】

* 表示返回表中所有数据
列名可以选择部分，列名之间用逗号隔开
表达式可以函数，常数等组成的表达式
from 要查询的数据用源于哪个表

SQL语句规则
1.不区分大小写
2.语句可以写一行，也可以写多行

--查询所有列的内容
select * from emp;

--查询指定列的内容
select ename,sal from emp;

--列名起别名
select ename 姓名,sal 工资 from emp;

--表名起别名
select e.ename,e.hiredate  from emp e;

--order by 排序  asc升序  desc 降序
--员工的工资从小到大排序
select * from emp order by sal asc;
select * from emp order by sal;  --默认为升序

--从大到小排序
select * from emp order by sal desc;

--对多列进行排序
select * from emp order by sal desc,comm desc  --先排序第一列，如果相同时才到排序第二个

--操作符
（1）算数运算操作符：  +   -  *   / 
（2）关系运算符:      >  <   >=   <=  !=   <>
（3）逻辑运算符:   and  or  not
（4）字符串连接符:  ||

--求每个员工的年薪
select ename,sal*12 年薪 from emp

--工资大于1500员工信息
select * from emp where sal>1500;

--查询工资不是1250的员工的信息
select * from emp where sal<>1250;
select * from emp where sal !=1250;

--and  并且（同时满足条件）    or 或者（满足其中一个条件即可）    not 取反
--查询出部门编号是20，并且工资大于2000的员工信息
select * from emp where deptno=20 and sal>2000;

--查询出20，30号部门的员工信息
select * from emp where deptno=20 or deptno=30;

--工资不是3000的员工信息
select * from emp where not sal = 3000

字符串连接符 ||
select '我的姓名:'||ename||','||'我的工资:'||sal  from emp   

课堂练习：
--查询出2000年以后入职时间的员工信息
select * from emp e where e.hiredate>='2000-1-1'

--查询出30号部门工资大于2000的员工信息
select * from emp where deptno=30 and sal>2000

--显示KING和SCOTT员工信息，并按工资从高到低显示
select * from emp where ename ='KING' OR ename='SCOTT' order by sal desc;

--查询出20号和30号部门工资大于2000的员工信息
select * from emp where( deptno=20 or deptno=30 ) and sal>2000

--查询出工资在2000-3000之间的员工信息
select * from emp where sal>=2000 and sal<=3000;

--查询出不是30号部门并且工资大于3000的员
select * from emp where sal>3000 and deptno!=30;

消除重复行（内容）
select distinct deptno from emp;

null操作
空值不等于0或空格，空值没有赋值，未知，不可用值
在查询条件中null值用is null作为条件，非null值用is not null做条件

--没有奖金的员工信息
select * from emp where comm is null;
--有奖金的员工信息
select * from emp where comm is not null;

null的特点
（1）null与任何数字算数运算，返回的永远是null    null+1000=null
select ename,sal+comm from emp
(2)null与任意值进行关系运算，返回的永远是false(不成立)
select * from emp where null<3;

in操作
where子句中可以使用in操作符来查询其列值在指定的列表中的行
not in 与in相反

--查询SCOTT和KING员工信息
select * from emp where ename in('SCOTT','KING');
--查询不是SCOTT和KING员工信息
select * from emp where ename not in('SCOTT','KING');

between.....and...
查询列值包含在指定的区间范围内（包含边界）
--查询工资在2000到3000之间的员工信息
select * from emp where sal>=2000 and sal<=3000
select * from emp where sal between 2000 and 3000

like模糊查询
% 表示0个或多个任意的字符
_ 表示1个任意的字符

--查询出姓名以S开头的员工信息
select * from emp where ename like 'S%';  

--查询出姓名中包含有S的员工信息
select * from emp where ename like '%S%'; 

--查询出姓名中第二个字符为D的员工信息
select * from emp where ename like '_D%';

--查询出姓名中只有四个字符的员工信息
select * from emp where ename like '____';

--查询出姓名中以S结尾的员工信息
select * from emp where ename like '%S';

--查询出姓名以_开头的员工信息
select * from emp where ename like '\_%' escape '\'; --escape \  代表\后面的这个字符不转义

--------------------------第二天作业讲解---------------------------------------------------------
--练习：
--查询职员表中，没有领导的员工姓名及职位，并按职位升序排序。
select ename,job from emp where mgr is null order by job asc;

--查询职员表中，有奖金的员工姓名、薪资和绩效，并按工资降序排列。
select ename,sal,comm from emp where comm is not null order by sal desc;

--查询职员表中，员工姓名的第三个字母是A的员工姓名。
select ename from emp where ename like '__A%';

--查询职员表中员工号、姓名、工资，以及工资提高百分之20%后的结果。
select empno,ename,sal,sal*1.2 from emp

--查询员工的姓名和工资，条件限定为：工资必须大于1200，并对查询结果按入职时间进行排列，早入职排在前面，晚入职排在后面
select ename,sal from emp e where sal>1200 order by e.hiredate ;

--查询除了经理（MANAGER）之外的所有员工信息
select * from emp where job != 'MANAGER'
select * from emp where job <> 'MANAGER'
select * from emp where job not in( 'MANAGER')

--查询员工信息,要求姓名中第二个字符是字母A 或者第3个字符是字母A
select * from emp where ename like '_A%' or ename like '__A%';

1.    显示薪水大于2000，且工作类别是MANAGER的雇员信息
select * from emp where sal>2000 and job='MANAGER'

2.    显示年薪大于30000，工作类别不是MANAGER的雇员信息
select * from emp where sal*12>30000 and job != 'MANAGER'

3.    显示薪水在1500到3000之间，工作类别以“M”开头的雇员信息
select * from emp where sal between 1500 and 3000 and  job like 'M%'

4.    显示佣金为空并且部门号为20或30的雇员信息
select * from emp where comm is null and deptno in(20,30)

5.    显示佣金不为空或者部门号为20的雇员信息，要求按照薪水降序排列
select * from emp where comm is not null or deptno=20 order by sal desc

6.    显示年薪大于30000工作类别不是MANAGER，且部门号不是10和40的雇员信息，要求按照雇员姓名进行排列
select * from emp where sal*12>30000 and job!='MANAGER' and deptno not in (10,40) order by ename 

7.    选择在部门 30 中员工的所有信息
select * from emp where deptno=30

8.    列出职位为（MANAGER）的员工的编号，姓名
select empno,ename from emp where job='MANAGER'

9.    找出部门 10 中的经理(MANAGER)和部门 20 中的普通员工(CLERK)
select  * from emp where ( deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK')

10.    找出部门 10 中既不是经理也不是普通员工，而且工资大于等于 2000 的员工
select * from emp where deptno=10 and job not in ('MANAGER','CLERK') and sal>=2000

11.    找出没有奖金或者奖金低于 500 的员工
select * from emp where comm is null or comm<500

12.    显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
select ename from emp e order by e.hiredate asc

13.    找出有奖金的员工的不同工作岗位
select distinct job from emp where comm is not null

14.    找出姓名中不带 R 这个字母的员工
select * from emp where ename not like '%R%'

15.    显示所有员工，按入职年限降序排列，若相同，则按工资升序排序
select * from emp order by  emp.hiredate desc,sal asc;

16.    查找出不属于任何部门的员工
select * from emp where deptno is null

------------------------------------------------第三天------------------------------------------------
集合运算：就是把两个或者多个结果集组合成为1个结果集

(1)intersect（交集） :返回两个表共同的记录
select * from emp;
select * from dept;
select deptno from emp intersect select deptno from dept

(2)union(并集)：返回各个查询的所有记录，不包括重复记录
select deptno from emp union select deptno from dept   --emp 10 20 30  null  /  dept  10 20 30 40

(3)union all(并集) ：返回各个查询的所有记录，包括重复记录，不去重
select deptno from emp union all select deptno from dept
 
(4)minus(补集)：返回第一个表比第二个表多的记录
select deptno from emp minus select deptno from dept;  --null
select deptno from dept minus select deptno from emp;   --40

多表查询：交叉连接、内连接、外连接
--交叉连接：交叉连接返回左表中的所有行，左表的每一行与右表的所有行组合，交叉连接也称为笛卡尔积（集）

 select * from emp; 
 select * from dept;
 select * from emp,dept;  --返回左表行数与右表行数相乘的组合行数  17*4=68
 
--内连接
（1）等值连接：在连接条件中使用等于号（=）运算符比较被连接列的列值，查询结果中列出被连接表中的所有列，包括重复列
select * from emp e,dept d where e.deptno=d.deptno  

（2）非等值连接：在连接条件中不使用等于号（=）运算符（>=、<= 、< 、> 、！=）

--求员工的工资等级
select e.ename,e.sal,s.grade,s.losal,s.hisal   from emp e,salgrade s where e.sal between s.losal and s.hisal

（3）自连接：连接的两个表都是同一个表
--查询出每一个员工的领导姓名，员工的领导编号在emp中，领导的姓名在emp中(e1 员工表   e2领导表)
select e1.ename,e1.mgr, e2.empno,e2.ename  from emp e1,emp e2 where e1.mgr=e2.empno

--1.查询出工作地点在NEW YORK的员工信息，显示姓名，职位，工作地点
select a.ename,a.job,b.loc from emp a,dept b where a.deptno=b.deptno and b.loc='NEW YORK'

--2.查询出WARD员工的工作地点，显示姓名和工作地点
select a.ename,b.loc from emp a,dept b where a.deptno=b.deptno and a.ename='WARD'

--外连接（左外连接、右外连接、满连接）
（1）左外连接（left join ）:A表左连接B表，A表的内容全部显示，B表没有的用null代替
语法：select * from A表 left join B表 on 条件
--查询每一个部门都有哪些员工，显示部门编号，部门名称，员工编号，员工姓名
select * from dept d left join emp e on d.deptno=e.deptno

(2)右外连接（right join  等同于 right outer join）:A表右连接B表，返回B表所有的内容，A表没有的用null代替
--查询每一个部门都有哪些员工，显示部门编号，部门名称，员工编号，员工姓名
select * from emp e right join dept d on e.deptno=d.deptno

--查询出每一个员工的工作部门信息
select * from emp e left join dept d on e.deptno=d.deptno   --左连接
select * from dept d right join emp e on d.deptno=e.deptno  --右连接 

（3）满外连接（full join）:返回左表和右表的所有记录
select * from emp e full join dept d on e.deptno=d.deptno

create table stu (id number,name char(10));
create table score(id number,sno number,score number);
insert into stu values(1,'张三');
insert into stu values(2,'李四');
insert into stu values(3,'王五');
insert into stu values(5,'刘海');
insert into score values(1,100,60);
insert into score values(2,200,70);
insert into score values(3,300,80);
insert into score values(4,400,90);

select * from stu;
select * from score


（1）查询出每个学生的分数
select  * from stu a left join score b on a.id=b.id

（2）查询出每个科目编号参加的学生信息
select * from score c left join stu d on c.id=d.id

（3）查询出stu表与score表所有的内容

select * from stu s full join score f on s.id=f.id

子查询：在select语句中又包括有select语句
单行子查询：不向外部返回结果，或者只返1行结果
多行子查询：向外部返回0行或1行或多行结果

--查询出在芝加哥工作的员工的信息
(1)求出在芝加哥工作地点对应的部门编号
select deptno from dept where loc='CHICAGO'  --30
（2）求出对应部门编号的员工信息
select * from emp where deptno=30

select * from emp where deptno=(select deptno from dept where loc='CHICAGO') --子查询
select * from emp a,dept b where a.deptno=b.deptno and b.loc='CHICAGO' --多表查询

--查询出KING员工的工作部门名称 
（1）先在emp表中查询出KING是属于哪个部门编号 
select deptno from emp where ename='KING'   --10
（2）根据对应的部门编号在dept表中找出部门名称
select dname from dept where deptno=10

select dname from dept where deptno=(select deptno from emp where ename='KING')

注意事项
1.如果内部查询不返回任意记录，外面查询也不会返回任意的记录
2.在内部子查询中可以使用关系运算符（< 、>、<=、>=、<>）
3.如果内部查询返回多行，外部查询结果会报错

--any子查询  （100  120  150）
<any:小于最高的   小于150
>any:大于最小的   大于100

--查询出emp表中比销售员（SALESMAN）最高工资低的员工的信息
select sal from emp where job='SALESMAN'  --返回4行记录，最高工资1602
select * from emp where sal<1602

select * from emp where sal<any (1602,1250,1250,1500)

select * from emp where sal<any (select sal from emp where job='SALESMAN')

--all子查询 （100  120   150）
>all  大于最高的    大于150
<all  小于最低的    小于100
--查询出比所有销售员工资还要高的员工信息
（1）先找出销售员的最高工资是多少
select sal from emp where job='SALESMAN'
(2)找出工资sal>1602的员工信息
select * from emp where sal>1602

select * from emp where sal >all(select sal from emp where job='SALESMAN')

--练习
1.查询出在“SALES”部门工作的员工信息
(1)先查询出“SALES”部门编号
select deptno from dept where dname='SALES'   --30
(2)查询出30号部门的员工信息
select * from emp where deptno=30

select * from emp where deptno=(select deptno from dept where dname='SALES')  --子查询

2.查询出“WARD”员工的工作地点
（1）先求出该员工的部门编号
select deptno from emp where ename='WARD'   --30
（2）求出30号部门的工作地点
select loc from dept where deptno=30

select loc from dept where deptno=(select deptno from emp where ename='WARD') --子查询

3.查询出比在“CHICAGO”工作的员工最高工资还高的员工信息
（1）求出在“CHICAGO”工作的部门编号
select deptno from dept where loc='CHICAGO'   --30
（2）求30号部门最高工资 
select sal from emp where deptno=30   --2850
（3）求工资比2850高的员工的信息
select * from emp where sal>2850

select * from emp where sal>all
(select sal from emp where deptno=(select deptno from dept where loc='CHICAGO' )) --子查询


------------------------------------------第三天作业讲解-------------------------------------------
create table aaa(id number, name char(10));
create table ccc(id number,sno number,ccc number);

insert into aaa values(1,'a');
insert into aaa values(2,'b');
insert into aaa values(3,'c');
insert into aaa values(4,'d');
insert into aaa values(5,'e');

insert into ccc values(1,100,50);
insert into ccc values(2,100,60);
insert into ccc values(3,100,80);
insert into ccc values(4,200,75);
insert into ccc values(5,200,85);
insert into ccc values(1,300,62);
insert into ccc values(2,400,32);

select * from aaa;
select * from ccc;

--(1)查询出a的考试成绩
select * from aaa,ccc where aaa.id=ccc.id and aaa.name='a'  --多表查询
select * from ccc where id=(select id from aaa where name='a')  --子查询

--(2)查询出比a考试最高分还高的学生信息
select id from aaa where name='a'  --1
select ccc from ccc where id=1   --50  62
select id from ccc where ccc>62  --3   4   5
select * from aaa where id in (3,4,5)

select * from aaa where id in (
select id from ccc where ccc>all(select ccc from ccc where id=(select id from aaa where name='a'))
) --子查询

--(3)查询出比科目编号200最低分高的学生信息
select * from aaa a,ccc c where a.id=c.id and c.ccc >any(select ccc from ccc where sno=200)  --多表与子查询组合


--(4)查询出考试不合格的学生信息
select id from ccc where ccc<60   --1  2
select * from aaa where id in (1,2)
select * from aaa where id in (select id from ccc where ccc<60)  --子查询

select * from aaa a,ccc c where a.id=c.id and c.ccc<60  --多表查询


1.    查询工资在 1500 到 3000 之间，工作类别以B开头的雇员信息
select * from emp where sal between 1500 and 3000 and job like 'B%'

2.    查询出部门 10 中既不是经理（MANAGER）也不是普通员工（CLERK），而且工资大于等于 2000 的员工
select * from emp where deptno=10 and job not in ('MANAGER','CLERK') and sal>=2000

3.    查询与 BLAKE 在同一部门工作的雇员的姓名和受雇日期，但是 BLAKE 不包含在内。
select e.ename,e.hiredate from emp e where deptno=(select deptno from emp where ename='BLAKE') and ename!='BLAKE'

4.    查询位置在 DALLAS 的部门内的员工信息。
select deptno from dept where loc='DALLAS'  --20
select * from emp where deptno=20
select * from emp where deptno=(select deptno from dept where loc='DALLAS')  --子查询
select * from emp e,dept d where e.deptno=d.deptno and d.loc='DALLAS'   --多表查询

5.    查询被 KING 直接管理的雇员的姓名以及薪水。
select empno from emp where ename='KING'   --7839
select * from emp where mgr=7839
select * from emp where mgr=(select empno from emp where ename='KING' ) --子查询
select * from emp e1,emp e2 where e1.empno=e2.mgr and e1.ename='KING' -- 自连接    e1 领导表    e2 员工表

6.    查询出与SCOTT一样工资的其他雇员的姓名、受雇日期以及薪水。
select sal from emp where ename='SCOTT'   --3000
select * from emp where sal=3000 and ename!='SCOTT'
select ename,hiredate,sal from emp where sal=(select sal from emp where ename='SCOTT') and ename!='SCOTT'  --子查询

7.    查找出工资等级不为 4 级的员工信息，显示员工名字，部门名字，部门位置
select * from 
(select * from emp e,salgrade s where e.sal between s.losal and s.hisal and s.grade!=4) T 
full join dept d on T.deptno=d.deptno 

-------------------------------第四天--------------------------------------------
--增删改

（1）根据查询的结果创建表
语法：create table 表名 as select 语句

--创建一个工资大于2000，入职年限升序排序的表信息
create table emp_1 as
select * from emp where sal>2000 order by hiredate asc

select * from emp_1

--备份emp表
create table emp_bak as select * from emp;
select * from emp_bak;

--只复制表结构，不要表数据
create table emp_2 as 
select * from emp where 1=2   --条件不成立，不会复制数据
select * from emp_2

--通过多表查询生成一个新表（列名不能重复）
create table emp_4 as 
select a.ename,a.sal,b.deptno,b.dname from emp a,dept b where a.deptno =b.deptno
select * from emp_4

（2）插入数据
语法：insert into 表名（列名1，列名2，列名3...） values (值1，值2，值3....)
insert into emp_4 (ename,sal,deptno,dname) values ('AAA',8000,10,'SZ')  --按照完整结构插入数据
insert into emp_4 values ('CCC',9000,20,'SZ')  --如果后面的值是一一对应的，前面列名省略 
insert into emp_4(deptno,ename,dname) values(10,'DDD','GD')--如果后面的值不是一一对应的，或者有一些列为空时，必须前面要加上对应的列名

--把查询出来的结果数据插入到表中（查询出来的数据要与表结构一致）
语法：insert into 表名 select 语句
select * from emp_2
select * from emp where sal>3000

insert into emp_2 select * from emp where sal>3000 

(3)修改数据
语法：update 表名 set 列名1=新值，列名2=新值 where 条件
select * from emp_bak;

--工资低于2000的修改对应员工的工资（在原有工资上加200）
update emp_bak set sal=sal+200 where sal<2000  --根据条件修改1列数据

--把KING员工的员工编号、姓名、部门编号进行修改
update emp_bak set empno=1000,ename='张三',deptno=8 where ename='KING'  --同时修改多列内容
select * from emp_bak

（3）删除数据
语法：delete from 表名 where 条件
--删除工资小于2000的员工的信息
delete from emp_bak where sal<2000

--删除整个表的数据
delete from emp_bak


事务控制语言（TCL）:commit、rollback、savepoint
commit :提交更改（插入数据、修改数据、删除数据）
rollback:回滚更改
savepoint:创建还原点，可以还原到指定的位置上

--commit
select * from emp_4;
insert into emp_4 values('王五',20000,60,'SZ');
commit;  --提交并保存数据

--rollback(没有提交才可以回滚)
回滚到上一次保存的内容

--savepoint
create table abc (id number,name char(10));
select * from abc;
insert into abc values(1,'a');
savepoint a1  --1行数据
insert into abc values(2,'b');
savepoint a2  --2行数据
insert into abc values(3,'c');
savepoint a3  --3行数据

insert into abc values(4,'d');
insert into abc values(5,'e');

rollback to a1  --还原a2保存点

insert into abc values(6,'f');
savepoint a6;
commit;

insert into abc values(7,'h');

rollback to a6

select * from abc;

--a1   >  a2   >a3
rollback to a2

单行函数：对每一个函数应用在表的记录时，只能输入一行结果，返回一个结果
聚合函数：聚合函数同时可以对多行数据进行操作，返回一个结果

常用单行函数
字符函数：对字符串进行操作
数字函数：对数字进行计算，返回一个数字
转换函数：可以把一个数据类型转换为另一种数据类型
日期函数：对日期和时间进行处理

--在我们Oracle数据库有一个虚拟表dual
select 4+5 from dual;

--字符函数
(1)concat(x,y)  连接字符串x和y 
select concat('Hello','World') from dual;   --HelloWorld

(2)instr(x,str[,start][,n])  在x字符串查找str字符，返回第一次出现的位置，可以指定从start开始，也可以指定从第n个开始
select instr('Helloworld','o') from dual  --在指定的字符串中从第1个字符开始找对应字符第一次出现的位置   5
select instr('Helloworld','o',6) from dual--在指定的字符串中从第6个字符开始找对应字符第一次出现的位置   7
select instr('Helloworld','o',3,2) from dual--在指定的字符串中从第3个字符开始找对应字符第2次出现的位置  7

(3)length(x) 返回x字符串的长度
select length('abcd') from dual;
--显示每个员工姓名的长度
select ename,length(ename) from emp;
--查询出姓名长度为6个字符的员工信息
select * from emp where length(ename)=6

（4）lower(x) x转换为小写
select lower('AbC') from dual;  --abc
select ename,lower(ename) from emp;

（5）upper(x) x转换为大写
select upper('abc') from dual;   --ABC

（6）initcap(x)  把x的首字符转换为大写
select initcap('abc') from dual;  --Abc
select initcap(ename) from emp;

（7）rtrim(x[,y]) 把x右边截去y字符串，缺省截去空格
select rtrim('   Hell   ') from dual  --截去右边的空格
select rtrim('   Hell?????','?') from dual  --截去字符串右边的?

（8）ltrim(x[,y]) 把x左边截去y字符串，缺省截去空格
select ltrim('   Hell   ') from dual  --截去左边的空格
select ltrim('**** **Hell','*') from dual  --截去字符串左边的*，遇到空格停止      **Hell

（9）trim([a From]x) 把x两边截去a字符串，缺省截去空格
select trim('   Hell   ') from dual  --截去两边的空格
select trim('?' from '????Hell?????') from dual  --截去字符串右边的?    Hell

(10) replace(x,old,new)  在x字符串中把old字符替换成new字符
select replace('HelloWorld','o','8') from dual   --把字符串中的o 替换成8    Hell8W8rld

(11)substr(x,start[,length])  返回x的字符串，从start开始，截取length个字符，length不填时默认到结尾
select substr('Helloworld',5) from dual;  --从第5个开始截取到结尾   oworld 
select substr('Helloworld',6,3) from dual;  --从第6个开始截取3个字符   wor
--截取员工姓名的前3个字符
select ename,substr(ename,1,3) from emp;

--数字函数
（1）abs(x)  求x绝对值
select abs(-6) from dual;   --6

（2）ceil(x)  大于或等于x的最小整数
select ceil(3.25) from dual; --   4
select ceil(-3.56) from dual ; --   -3

（3）floor(x)  小于或等于x的最大整数
select  floor(5.8) from dual;   --   5
select floor(-4.6) from dual;   --  -5

（4）round(x[,y])   x在第y位四舍五入
select round(123.656) from dual;   --取整数，四舍五入        124
select round(123.456,2) from dual;  --取2位小数点，四舍五入  123.46 

（5）trunc(x[,y])  x在第y位截断，不进行四舍五入
select trunc(3.145) from dual;  --截取整数部分
select trunc(3.145,2) from dual; --截取二位小数，不四舍五入    3.14

--日期函数  date
日期函数对日期进行运算

（1）sysdate  显示系统当前日期时间
select sysdate from dual;

（2）last_day  显示本月最后一天
select last_day(sysdate) from dual;
select e.hiredate,last_day(e.hiredate)-2 from emp e
--查询出每个月最后三天入职的员工信息
select e.ename,e.hiredate from emp e where e.hiredate between  last_day(e.hiredate)-2 and last_day(e.hiredate)

(3)add_months(d,n)   当前日期d后推n个月
select add_months(sysdate,5) from dual;    --日期月份后推5个月       2023/8/14 16:34:01
select add_months('2008-02-05',5) from dual;   --2008/7/5
select add_months('2008/2/5',3) from dual;   --  2008/5/5

(4)增加或减少多少天
select sysdate+3 from dual;  --在当前日期中加3天
select sysdate-5 from dual;  --在当前日期中减5天

(5)months_between  计算两个时间之间有几个月
select round( months_between(sysdate,'2008-08-08'),1) from dual;  --求2个日期之间相差多少个月
--入职时间超20年员工信息
select * from emp where months_between(sysdate,hiredate) >20*12

--转换函数
(1)to_char()  转换成字符串
select to_char(5)||to_char(6) from dual; --把5这个数字转换成字符串后再与后面的字符串连接在一起

(2)to_date()  转换成日期
create table datetype (id date)
select * from datetype
insert into datetype values('2023-5-9')  --标准日期
insert into datetype values('2019/8/9')  --标准日期
insert into datetype values(to_date('2024年8月9日','yyyy"年"mm"月"dd"日"'))  
insert into datetype values(to_date('2025#5#9#','yyyy"#"mm"#"dd"#"'))   --yyyy年份   mm月份  dd 天
insert into datetype values(to_date('2024年8月9日 21:20:30','yyyy"年"mm"月"dd"日" hh24:mi:ss'));  --hh24 24小时制   mi分钟   ss秒  

(3)to_number() 转换成数字
select to_number('123')+100 from dual;

--获取日期当中的某一部分
select  to_char(sysdate,'yyyy') from dual;   --获取年份
select to_char(sysdate,'mm') from dual;    --获取月份
select to_char(sysdate,'dd') from dual;    --获取天

--查询出8月份入职的员
select * from emp where to_char(hiredate,'mm')=8 

-----------------------------------第五天--------------------------------------------------------
--练习
--显示出emp表中姓名为5个字符的员工信息
select * from emp where length(ename)=5   

--显示emp表中每个员工姓名的第3与第4个字符
select substr(ename,3,2) from emp;

--把5.6789这个数字保留2位小数点（四舍五入）
select round(5.6789,2) from dual;

--显示出2020-10-8与2008-11-8之间相差几年,保留2位小数
select  round(months_between('2020-10-8','2008-11-8')/12,2) from dual;

--把字符串“2008年8月8日 8时18分28秒”插入到表中的时间列
insert into datetype values (to_date('2008年8月8日 8时18分28秒','yyyy"年"mm"月"dd"日" hh24"时"mi"分"ss"秒"'))
select * from datetype

--把emp表中每个员工的入职时间以“XXX年XX月XX日 XX时XX分XX秒”格式显示
select hiredate,to_char(hiredate,'yyyy"年"mm"月"dd"日"') from emp;


(1)查询出每个月倒数第三天入职的员工信息（如：2009-5-29）
select * from emp e where e.hiredate=last_day(e.hiredate)-2     

(2）查询出2000年前入职的员工信息
select * from emp e where to_number(to_char(e.hiredate,'yyyy'))<2000

(3)查询出 25 年前入职的员工信息
select * from emp e where (sysdate-e.hiredate)/365>25  --第一种    按天
select * from emp e where months_between(sysdate,e.hiredate)/12>25;  --第二种  月份

(4)所有员工名字前加上 Dear ,并且名字首字母大写
select ename, 'Dear'|| initcap(ename) from emp

(5)查询出姓名为 5 个字符的员工信息
select * from emp where length(ename)=5

(6)只显示员工姓名的第一个字符,后面用***表示（例如：KING > K***）
select ename,concat(substr(ename,1,1),'***') from emp

(7)查询到 2 月份入职的员工信息
select * from emp e where to_number(to_char(e.hiredate,'mm'))=2 

(8)查询出3月至5月入职的员工信息
select * from emp e where to_number(to_char(e.hiredate,'mm')) between 3 and 5

其他常用单行函数
（1）nvl(x,value):如果x为空，返回value,否则返回x
--查询出每个员工每月的总工资（工资+奖金）  
select ename,sal,comm,sal+nvl(comm,0) from emp;

--对工资小于2000元的员工，如果没有奖金，每人发奖金100元
select ename,sal,comm,sal+nvl(comm,100) from emp where sal<2000

(2)nvl2(x,value1,value2):如果x非空，返回value1,否则返回value2
--工资为2000元以下，没有奖金，奖金加200元，有奖金加100元
select ename,sal,comm,sal+nvl2(comm,comm+100,200) from emp

聚合函数
avg（）  平均值
sum( )   求值
min（）  最小值    max（） 最大值
count()  统计数量
--求员工的平均工资，最高工资，最低工资，月总工资
select round(avg(sal),1) 平均工资,max(sal) 最高工资,min(sal) 最低工资, sum(sal)月总工资 from emp  

--求30部门员工的平均工资
select avg(sal) from emp where deptno=30

--求20部门有几个员工
select count(*) from emp where deptno=20   --取表中最多那一列的行数


group by  分组函数
（1）求每个部门的平均工资
先按照部门分组
分别对每一个部门进行求平均工资
select deptno,avg(sal) from emp group by deptno ;

（2）求平均工资大于2000的部门
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000 ;  --having  分组后过滤

（3）求除了20号部门之外，其它部门平均工资大于1500的部门
select deptno,avg(sal) from emp where deptno!=20 group by deptno having avg(sal)>1500

--练习
--（1）统计出20号部门人数
select  count(*) from emp where deptno=20

--（2）求30号部门有奖金员工的平均工资
select round(avg(sal),2) from emp where comm is not null and deptno=30

--（3）10号部门的平均工资比30号部门平均工资多多少
select (select avg(sal) from emp where deptno=10)-(select avg(sal) from emp where deptno=30)  from dual

--（4）统计出4月份入职的员工人数
select count(*) from emp where to_number(to_char(emp.hiredate,'mm'))=4

Oralce中伪列
rowid:显示数据保存在磁盘上的物理地址
rownum：伪列，主要用于翻页查询

select rowid, emp.* from emp  --显示数据的磁盘上的物理地址

select rownum,emp.* from emp; --rownum 在查询的时候会给每一行加一个行号，第一行为1，第二行为2.....

--查询出员工表中前5个员工的信息
select ename,sal,deptno from emp where rownum<=5

--查询出工资最高前3位员工信息
select rownum,T.* from 
(select emp.* from emp  order by sal desc) T  where rownum<=3

--查询表中第5到10条员工信息
select A.*   from 
(select rownum R,emp.* from emp) A where A.R between 5 and 10

--练习 
1.按部门统计员工数，查出员工数最多的前2名部门。
select rownum,T.* from
(select deptno, count(*) from emp group by deptno order by count(*)desc) T  where rownum<=2

2.查找出部门 10 和部门 20 中，工资最高第 3 名到第 5 名的员工的员工名字，部门名字，部门位置
select C.*  from
(select rownum A,T.*  from
(select e.ename,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno and d.deptno in (10,20)
 order by e.sal desc) T ) C where C.A between 3 and 5

case的使用
语法：
case
  when 条件1 then action1
  when 条件2 then action2
  when 条件3 then action3
  when 条件n then actionN
  else action
end

--创建一列，显示员工的奖金的情况，comm为显示‘无’，否则显示“有”
select e.ename,e.sal,comm,case
when comm is null or comm=0 then '无'
else '有'
end 奖金情况
from emp e

--根据分数显示对应的等级 （>90 A  >80 B   >70 C  >60 D  <60 E）
create table score(sno number,score number);
insert into score values(1,65);
insert into score values(2,76);
insert into score values(3,86);
insert into score values(4,94);
insert into score values(5,24);
select * from score;

select sno,score,case
when score>90 then 'A'
when score>80 then 'B'
when score>70 then 'C'
when score>60 then 'D'
else '不合格'
end 等级
from score;

--显示每个员工入职的月份（1981/05/10 》 5月）
select ename,e.hiredate,case to_char(e.hiredate,'mm')
when '01' then '1月'
when '02' then '2月'
when '03' then '3月'
when '04' then '4月'
when '05' then '5月'
when '06' then '6月'
when '07' then '7月'
when '08' then '8月'
when '09' then '9月'
when '10' then '10月'
when '11' then '11月'
when '12' then '12月'
else '其它'
end 入职月份
from emp e;

select ename,emp.hiredate,to_char(emp.hiredate,'mm')||'月' 入职月份 from emp;

视图：创建一个虚拟表（基表），提供给别人使用
视图作用：
（1）方便对方调用
（2）对自身的数据进行保护 

创建视图语法结构：
create [or replace] view 视图名
as
select 语句
[with read only]

--只查看员工的姓名和工资
--创建视图
create or replace view AABBCC
as
select ename from emp where sal>=3000;

--调用视图：select * from 视图名
select * from AABBCC


--删除视图：drop view 视图名
drop view AABBCC


索引：相当于对某些列创建索引，提高查询速度
--创建索引
语法：create index 索引名 on 表名 （列名1，列名2.....）
create index A_empno_ename_deptno on emp (empno,ename,deptno)
select * from emp where sal>3000;

--删除索引
语法：drop index 索引名
drop index A_empno_ename_deptno

--生成一个100W行数据的表
create table testtable as
select rownum as id,
sysdate+ rownum/24/3600as inc_datetime,
    trunc(dbms_random.value(0, 100)) as random_id, 
dbms_random.string('x', 20) random_string 
from dual
connect by level <=1000000;  

select count(*) from testtable   --100W行

没有创建索引时查询：
select * from testtable where id in (5,500,5000,50000,500000,900000)   --0.172

创建索引
create index idx_id on testtable(id);

创建索引时查询:
select * from testtable where id in (5,500,5000,50000,500000,900000)   --0.042

存储过程：把一些特定的SQL集合在一起执行
--创建存储过程
create or replace procedure 存储过程名 （参数 in|out  参数数据类型）
as 
begin
  sql语句集
end;

--调用存储过程
begin
  存储过程名（参数）
end;


----------------创建一个可以修改某个员工工资存储过程---------------------------
create or replace procedure sp_update_sal (name in char)
as 
begin
update emp set sal=sal+1 where ename=name;
commit;
end; 

begin
sp_update_sal('KING');
end;

select * from emp where ename='KING'

----------------------------删除数据---------------------------
select * from AAA
--创建存储过程
create or replace procedure sp_delete(no in number)
as 
begin
delete from AAA where id=no;
commit;
end;
--调用存储过程
begin
sp_delete(3);
end;

------------------插入数据-------------------------------
--创建存储过程
create or replace procedure sp_insert(no in number,ename in char)
as 
begin
insert into AAA values(no,ename);
commit;
end;

--调用
begin
  sp_insert(8,'张三');
end; 

select * from AAA