Oracle笔记 Oracle笔记

进入DOS按: win+R 输入sqlplus 回车
username: sys as sysdba 回车
password : 密码为空不写直接回车
检查当前登录的用户: show user
连接命令 (1)conn[ect]
用法: conn 用户名/密码@网络服务名 [as sysdba/sysoper]
当用特权用户身份连接时，必须带上 as sysdba 或是 as sysoper
案例: conn scott/123456
(2)disc[onnect]
说明:该命令用来断开与当前数据库的连接
(3)passw[ord]此命令只适用于SYSTEM
说明:该命令用于修改用户的密码.如果要想修改其它用户的密码，需要用sys/system登陆.
(4)show user
说明：显示当前用户名
(5)exit
说明：该命令会断开与数据库的连接，同时会退出sql*plus
修改权限设置一般情况下使用dba用户
修改用户密码: 语法:alter user 用户名 identified by 新密码;
案例:alter user system identified by 123456;
锁定用户
语法:alter user 用户名 account lock;
案例:alter user scott account lock;
解除锁定
语法:alter user 用户名 account unlock;
案例:alter user scott account unlock;
显示和设置环境变量
概述：可以用来控制输出的各种格式。
(1)linesize
说明:设置显示行的宽度,默认是80个字符
sql>show linesize 显示宽度
sql>set linesize 90 设置宽度
(2)pagesize
说明:设置每页显示的行数目,默认是14,用法和linesize一样。
至于其它环境参数的使用也是大同小异
Spool c:/笔记.txt
Spool off （结束）
“/”执行上一条语句
Edit（修改错误的语句）修改完成输入“/”执行
“--”单行注释
/**/多行注释
查看表结构：desc emp(常用)
清屏 host cls
set timing on/off(on表示开启Oracle中语句执行时间的计算)
管理员用户使用普通用户的表
语法: select * from 用户名.表名
创建用户:(管理员) 语法：create user 用户名 identified by 密码
案例: create user scxh identified by 123456;
备注: 新创建的用户,没有任何的权限,包括登录都没有
给用户修改密码
概述:如果给自己修改密码可以直接使用
sql>password 用户名
如果给别人修改密码则需要具有dba的权限,或是拥有alter user的系统权限
sql>alter user 用户名 identified by 新密码
创建用户案例例子: sql> create user dongshao identified by m123
default tablespace users
temporary tablespace temp
quota 3m on users;
identified by 表明该用户dongshao 将用数据库方式验证
default tablespace users //用户的表空间在users上
temporary tablespace temp //用户dongshao的临时表健在temp 空间
quota 3m on users //表明用户dongshao 建立的数据对象(表,索引,视图,pl/sql块..)最大只能是3m
刚刚创建的用户是没有任何权限的,因此,需要dba给该用户授权.
sql>grant connect to dongshao
如果你希望该用户建表没有空间的限制
sql>grant resource to dongshao
如果你希望该用户成为dba
sql>grant dba to dongshao
删除用户
概述：一般以dba的身份去删除某个用户,
如果用其它用户去删除用户则需要具有 drop user的权限。
比如 drop user 用户名【cascade】
案例: drop user scxh cascade;
cascade: 如果这个用户下面创建了表,则使用这个
System给东少权限时
Grant connect to dongshao with admin option
create session:登录
create table :创建表
create view:试图
1.创建2个用户
Create user dongshao1 identified by 123456;
Create user dongshao2 identified by 123456;
2.给dongshao1这个用户添加权限,并且让其可以继续授予其他用户的权限
grant create session,create table to dongshao1 with admin option;
grant create view to dongshao1;
3.使用dongshao1用户给dongshao2用户授权.
grant create session,create table to dongshao2 with admin option;
4.使用dongshao1用户给dongshao2授权视图权限的时候,会提示权限不足
grant create view to dongshao2;
5.回收权限
revoke create session from dongshao1; --这个是冬哥哥
1.创建两个用户 qq , tt.初始阶段他们没有任何权限，如果登陆就会给出错误的信息
1.1创建两个用户，并指定密码.
2.给用户qq授权:
2.1:授予create session 和create table权限时带with admin option
2.2 授予create view 时不带with admin option
对象权限; 给用户设置对象权限
grant 权限 on 对象 to 用户名 with grant option
grant select on emp to monkey
设置用户访问权限更加精细控制.(授予列权限)
语法: grant update on 表名(列名称) to 用户名
案例: grant update on emp(sal) to monkey;
建立角色㈠建立角色(不验证)
如果角色是公用的角色，可以采用不验证的方式建立角色.
语法:create role 角色名 not identified;
案例:create role mydongshao1 not identified; 没有任何权限
㈡建立角色(数据库验证)
语法:create role 角色名 identified by 口令
案例:create role 角色名 identified by dongshao
profile 指定scott这个用户最多只能尝试3次登陆,锁定时间为2天，让我们看看怎么实现
创建profile文件
语法:create profile pro名称 limit failed_login_attempts 3次 password_lock_time 2天;
sql> create profile lock_account limit failed_login_attempts 3 password_lock_time 2;
给用户设置profile
语法: alter user 用户 profile pro名称;
sql> alter user scott profile lock_account;
创建表: 语法:
create table 表名(
字段名称1 字段类型,
字段名称2 字段类型
...
)
【Oracle笔记】创建一个表,对这个表进行CRUD
案例: 创建一个wwdz,然后对这个进行表增删改查
create table wwdz(
id number, --编号
name varchar2(20), --姓名
tel char(11), --电话
datetime date default sysdate --时间
);
查询表中的所有数据语法: select * from 表名
案例: select * from wwdz
对wwdz这个表添加一条数据
语法: insert into 表名(字段名称1,字段名称2,字段名称3,...) values(值1,值2,值3,...);
案例: insert into wwdz(id,name,tel) values(2,'还厉害哦',12312312312);
案例: insert into wwdz(id,name,tel) values(2,'还厉害哦','12312312312');
案例: insert into wwdz(id,name,tel) values('三','你真的很厉害哦','12312312312'); 错误的
修改wwdz表中的某一行数据
语法: update 表名 set 字段名称1=值1,字段名称2=值2 备注: 如果执行这个语句,整个表都将被修改(慎用)
语法: update 表名 set 字段名称1=值1,字段名称2=值2 where 条件
案例: 修改编号2这个用户的电话号码为:13888888888,姓名: 方志华
案例: update wwdz set name='方志华',tel=138888888888 where id=2;
删除wwdz表中某一条数据
语法: delete from 表名如果执行这个语句,整个表的数据都没有了(慎用)
语法: delete from 表名 where 条件
案例: delete from wwdz where id=3;
删除表:
语法:drop table 表名
案例:drop table wwdz
表：
语法：
create table 表名(
字段1 类型1，
字段2 类型2，
)
添加
insert into 表名(字段1，字段2) values（值1，值2，）
修改
update 表名 set 字段1=值，字段2=值 where 条件
删除：
delete from 表名 where 条件
查询：
查询表中的所有数据：select * from 表名
带条件查询： select * from 表名 where 条件
单独查询指定字段： select 字段1，字段2，... from 表名 where 条件
varchar2(20) 变长
char(20) 定长
字符类型,并且长度为20字符(10个中文)
面试题: 请问varchar2和char之间有什么区别? 谁的查询速度更快
答: varchar2是变长,保存的值实际的长度,但是char是定长,如果保存的值没有达到
指定的长度,则使用空格填充,反之超出则会报错(自动截取到指定长度然后保存)
char的查询速度比varchar2更快,因为在进行查询的时候char不需要计算长度直接
进行对比,而varchar2首先进行长度的计算,然后在进行值的查询.
number : 数值
语法: number(p,s)
案例: number(5,2);
解释: 代表数值类型,有五个有效位,两个小数位(最大位:999.99)
子查询创建表
根据一个表来创建另外的一个表：（表结构和表内容一起被复制）
语法: create table 新表名 as select * from 旧表名
案例: create table dongshao as select * from emp;
查看结果: select * from dongshao;
只复制表的结构
语法:create table 新表名 as select * from 旧表名 where 1=2
案例:create table dongge as select * from emp where 1=2
查看结果:select * from dongge;
查看结构: desc dongge;
复制表内容(将一个原有表中的数据,复制到另一个表中,不指定字段,全部复制)
语法:insert into 目标表 select * from 原表
案例:insert into dongge select * from emp
查看结果: select * from dongge;
复制表内容(将一个原有表中的数据,复制到另一个表中,并且指定字段)
语法:insert into 目标表(字段1,字段2) select 字段1,字段2 from 原表
案例:insert into tongzhai(id,name) select empno,ename from emp
创建一个tongzhai表
create table tongzhai(
id int,
name varchar(30)
)
查看结果
select * from tongzhai;
mysql命令
查看所有的数据库
show databases;
查看所有的表
show tables;
创建数据库
create database 库名
在mysql中创建emp表
create table emp(
empno int,
ename varchar(20),
job varchar(20),
mgr int,
hiredate Date,
sal double(7,2),
comm double(7,2),
deptno int
)
desc emp;
INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);
select * from tongzhai;
insert into tongzhai(id) values(1);
desc tongzhai
修改表结构追加新列
语法:alter table 表名 add 字段名类型;
案例:alter table tongzhai add age tinyint;
tinyint: 范围: -128到127
修改列
语法:alter table 表名 modify 字段名类型;
案例:alter table tongzhai modify age int;
修改默认值的设置：
语法:alter table 表名 modify 字段名类型 default 默认值;
案例:alter table tongzhai modify age int default 11;
删除列(不要轻易删除一个字段)
语法:alter table 表名 drop column 字段名
案例:alter table dongshao drop column address
重命名列
语法:alter table 表名 rename column 旧名称 to 新名称
案例:alter table dongshao rename column name to a_name
修改表的名称(表的名称也不要修改)
语法:rename 旧表名 to 新表名;
案例:rename student2 to do;
约束创建约束：
语法:
create table 表名(
字段名称字段类型 constraint 约束名称约束
字段名称字段类型 constraint 约束名称约束约束
......
)
案例:
create table myperson(
id varchar2(18) constraint myperson_PK primary key, --primary key主键约束
name varchar2(40) constraint myperson_Name_notnull not null, --not null不能为空
gender varchar2(4) constraint myperson_Gender check (gender in ('男','女')), --check查验
email varchar2(40) constraint myperson_Email_unique unique --unique唯一
constraint myperson_Email_notnull not null, --not null不能为空
deptno number constraint myperson_FK references dept(deptno) ON DELETE CASCADE
--references外键约束
);
删除约束：
语法:alter table 表名 drop constraint 约束名称;
案例:alter table myperson drop constraint myperson_PK;
给列添加约束
语法:alter table 表名 add(constraint 约束名称约束(字段))
案例:alter table dongge add(constraint pk_id primary key(id))
创建一个表
create table bb(
id number primary key,
name varchar2(20) default '成都新华高级技工学校',
age number(2) not null,
zhc varchar2(20) default '' not null,
status number, --1: 正常,0:非正常
create_time number --时间戳:1232654553 时间:'2022-12-12 12:12:12'
)
课堂抽查

创建一个表,要求有(编号,姓名,性别,时间,年龄,电话,描述)
create table db(
id number,
name varchar2(20),
sex varchar(2),
time date,
nianling varchar(2),
tel char(11),
miaosu varchar(40));
给表中的编号设置主键约束
alter table db add(primary key(id));
给表中的姓名设置不能为空
alter table db modify name not null;
给表中的性别设置只能选择男或者女
alter table db add(constraint xb check(sex='男' or sex='女'));
给表中的时间设置默认为系统时间
alter table db modify time default sysdate;
给表中的年龄设置一个范围18--22之间
alter table db add(constraint nianling_check check(nianling>=18 and nianling<=22));
alter table db drop constraint nianling_check;
alter table db add(constraint nianling_check check(nianling between 18 and 22));
给表中的电话设置唯一
alter table db modify tel unique;

添加表注释
语法:comment on table 表名 is '注解名称';
案例:comment on table student is '学生表';
添加每个字段的注释
语法:comment on column 表名.字段名称 is '注解名称';
案例:comment on column db.tel is '电话号码';
添加语句:
语法1: insert into 表名(字段1,字段2,..) values(值1,值2,..) 字段和值一一对应
语法2: insert into 表名 values(值1,值2,..) 一一对应
插入之前改变日期表达式的方法：(日-月-年) '15-3月-2022' '2022-03-15'
alter session set nls_date_format='yyyy-mm-dd';
插入数据时修改时间表达式
insert into emp(empno,hiredate) values(9527,to_date('1990-02-03','yyyy-mm-dd'));
修改数据
语法: update 表名 set 字段=值,字段=值 where 条件
删除
语法: delete from 表名 where 条件
查询查询表中所有数据
select * from 表名
查询表中指定列
select 列1,列2,.. from 表名
根据条件查询指定的数据
select * from 表名 where 条件
where: 条件查询,根据指定的条件进行查询
emp表中的字段的解释
EMPNO : 编号
ENAME : 姓名
JOB : 职位
MGR : 领导的编号
HIREDATE : 入职日期
SAL : 工资
COMM : 奖金
DEPTNO : 部门编号

请查询出emp表中员工的姓名,工资和奖金
select ename,sal,comm from emp;
请查询出emp表中部门编号为20的所有员工
select * from emp where deptno=20;

?显示每个雇员的年工资
select sal12 from emp;
使用列的别名: as 设置别名
语法: select 字段 as 别名 from 表名;
语法: select 字段别名 from 表名;
select sal12 as "年薪" from emp;
select sal12 "年薪" from emp;
select sal12 year_money from emp;
如何处理null值
select ename,(sal12)+comm from emp; 这里我们使用年薪+null = null
使用nvl函数来处理
语法: nvl(字段,默认): 如果字段不为null,则使用字段的值,否则使用默认值(默认值为自定义)
语法案例:select nvl(字段名称,默认值) from emp;
sql案例:select ename,(sal12)+nvl(comm,0) from emp;
如何连接字符串(||)(输出: XXX姓名的工资的多少)
语法: 字段名称 || 字段名称
select ename||'的薪资为:'||sal from emp;
select ename||sal from emp;
？如何显示某人是什么岗位
select ename,job from emp where ename='黄木城';
使用where子句
?如何显示工资高于3000的员工
select * from emp where sal >= 3000;
?如何查找1982.1.1后入职的员工
select * from emp where hiredate >= '1982';
?如何显示工资在2000到3000的员工情况
select * from emp where sal >= 2000 and sal <= 3000;
between——在某个范围:
语法: 字段名称 between 起始值 and 结束值
select * from emp where sal between 2000 and 3000;
?如何显示工资不在2000到3000的员工情况
select * from emp where sal not between 2000 and 3000;
?查询SMITH 的薪水,工作,所在部门
select ename,sal,job,depeno from emp where ename='SMITH';
1、distinct——过滤掉多余的重复记录只保留一条
select distinct deptno from emp; 这里，distinct deptno会过滤掉重复的deptno
select distinct comm from emp; distinct也可以过滤掉重复的null，或者说如果有多个null，只输出一个
select distinct comm, deptno from emp; 把comm和deptnor的组合进行过滤
select deptno, distinct comm from emp; 逻辑上有冲突
select ename, * from emp; 这个在SqlServer里正确，在Oracle里不正确
in和null in（属于若干个孤立的值)
语法: 字段 in(值1,值2,值3); : 如果没有查询到,则返回空,不显示
语法: 字段 not in(值1,值2,值3); : 不在这个范围内的所有数据
案例: empno in(6666,7777,8888,9999)
表案例: select * from emp where ename in('大哥','黄木城');
数据库中的 and和&&一个意思 or和||一个意思
表案例: select * from emp where ename='大哥' or ename='黄木城';
表案例: select * from emp where empno in(6666,7777,8888,9999);
不在这个范围内的所有数据 not in
表案例: select * from emp where empno not in(6666,7777,8888,9999);
null 空 ,没有值
零和null是不一样的，null表示空值，没有值；零表示一个确定的值
null不能参与如下运算：<> != =
null可以参与如下运算：is is not
语法: select * from 表名 where 字段 is null
语法: select * from 表名 where 字段 is not null
select * from emp where comm <> null; --错误
select * from emp where comm != null; --错误
select * from emp where comm is not null; --正确
select * from emp where comm = null; --错误
select * from emp where comm is null; -- 正确
select * from emp where comm is null; --输出奖金为空的员工的信息
select * from emp where comm is not null; --输出奖金不为空的员工的信息
思考：如何显示没有上级的雇员的情况
select * from emp where mgr is null;
order by: 排序如果不指定排序的标准，则默认是升序，升序用asc表示，默认可以省略不写，降序用desc表示
为一个字段指定的排序标准并不会对另一个字段产生影响
强烈建议为第一个字段都指定排序的标准
语法: select * from 表名 order by 字段 asc; 默认为升序asc(可以不写)
语法: select * from 表名 order by 字段1,字段2; 都是升序
语法: select * from 表名 order by 字段1,字段2 desc; 先升后降
语法: select * from 表名 order by 字段1 desc, 字段2; 先降后升
语法: select * from 表名 order by 字段1 desc, 字段2 desc; 都降
备注: 在进行排序时,如果设置了多个,当第一个有相同排序时,则继续执行第二排序规则
模糊查询 like 语法:
select * from 表名 where 字段 like '%值%'; 匹配带有该值的所有字段的值
select * from emp where ename like '%S%'; 匹配ename字段所有值中带有S的名字
select * from 表名 where 字段 like '值%'; 匹配以该值开头的所有字段的值
select * from emp where ename like 'S%'; 匹配ename字段以S开头的所有字段值
select * from 表名 where 字段 like '%值'; 匹配以该值结尾的所有字段的值
select * from emp where ename like '%S'; 匹配ename字段以S结尾的所有字段值
何波提问: 万一别人的名称里面就有'_'怎么办??
select * from 表名 where 字段 like '值%'; 第一个''占位符,查询字段值的第二个值相同的字段值
select * from 表名 where 字段 like '_S%'; 匹配ename第二个值为s的所有字段值
select * from 表名 where 字段 like '__值%'; 前两个'_'占位符,查询字段值的第三个值相同的字段值
select * from emp where ename like '__S%'; 匹配ename第三个值为s的所有字段值
思考：在 emp 表中查询工资高于500或是岗位为MANAGER的雇员，
同时还要满足他们的姓名首字母为大写的 J。
select * from emp where sal>500 or job='MANAGER' and ename like 'J%';
1.找出佣金高于薪金的雇员
select * from emp where comm>sal;
2.找出佣金高于薪金60%的雇员
select * from emp where comm>sal*0.6;
3.找出部门10中所有经理和部门20中的所有办事员的详细资料
select * from emp where (job='MANAGER' and deptno='10') or (job='CLERK' and deptno='20');
4.显示不带有'R'的雇员姓名
select * from emp where ename not like '%R%';
5.显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
select ename,hiredate from emp order by hiredate;
6.显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序
select ename,job,sal from emp order by job desc,sal;
数据分组-max,min,avg,sum,count AVG(字段) 数字表达式中所有值的平均值
SUM(字段) 表达式中所有值的和
COUNT(字段) 表达式中值的个数
COUNT(*) 统计总条数
MAX(字段) 表达式中的最高值
MIN(字段) 表达式中的最低值
?如何显示所有员工中最高工资和最低工资
select max(sal),min(sal) from emp;
?显示所有员工的平均工资和工资总和
select avg(sal),sum(sal) from emp;
?计算共有多少员工
select count(*) from emp;
扩展要求：
?请显示工资最高的员工的名字，工作岗位
???想想应该怎么写
?请显示工资高于平均工资的员工信息
这个我也不会?????
分组: 面试题哦!
group by 和 having子句
group by用于对查询的结果分组统计,
having子句用于限制分组后显示结果.
查询出emp表中有哪些职位
select * from emp order by job;
select job from emp group by job;
select distinct job from emp;
select comm from emp group by comm;
?如何显示每个部门的平均工资和最高工资
select deptno,max(sal) ,avg(sal),sum(sal) from emp group by deptno;
?显示每个部门的每种岗位的平均工资和最低工资
select job,max(sal) ,avg(sal),min(sal) from emp group by job;
?显示平均工资低于2000的部门号和它的平均工资
select avg(sal),deptno from emp group by deptno having avg(sal) < 2000 ;
扩展要求：
显示工资低于2000，部门平均工资低于1000的部门号和部门平均工资
select deptno,avg(sal) from emp where sal<2000 group by deptno having avg(sal)<1000;
笛卡尔集: 语法: select * from 表名1,表名2 where 条件(条件是两个表都满足的情况下)
显示所有员工的姓名,工资,部门号和部门名称

根据两个表中的deptno条件进行比对查询出所有的数据
select * from emp,dept where emp.deptno=dept.deptno;
select emp.ename,emp.sal,emp.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno;
select a.ename,a.sal,b.deptno,b.dname,b.LOC from emp a,dept b where a.deptno=b.deptno;
?显示SCOTT用户,工资及所在部门的名字【笛卡尔集】
结果:
eanme sal dname
SCOTT 3000.00 RESEARCH
select a.ename,a.sal,b.deptno,b.dname,b.LOC from emp a,dept b
where a.deptno=b.deptno and a.ename='SCOTT';

?查询雇员姓名，工资以及工资等级
select e.ename, e.sal, s.grade from emp e,salgrade s
where e.sal between s.losal and s.hisal;
?如何显示部门号为10的部门名、员工名和工资
select d.dname,e.ename,e.sal from emp e,dept d where e.deptno=d.deptno and e.deptno=10;
扩展要求:
?显示雇员名,雇员工资，工资等级及所在部门的名字,并按部门排序.
select e.ename,e.sal,s.grade from emp e,dept d,salgrade s where
(e.sal between s.losal and s.hisal)and e.deptno=d.deptno order by d.dname;