04_Oracle_常见的命令
--------------输入一下指令，按下快捷键 F8
select * from emp;
--------------创建表格
create table 表名(
字段名1 数据类型1,
字段名2 数据类型2,
字段名3 数据类型3,
................ ,
字段名n 数据类型n
);
--------------数据类型
1）、整数 -> 整型 - number 或 number(3)
1 2 3 666
2）、小数 -> 浮点型 - number(长度,小数的位数)
price number(7,2)
2975.00 1.23
3）、字符串 -> 字符串的特点：用单引号引住
char(长度) -> 固定长度
varchar2(长度) -> 可变长度
4）、日期 date
-------------- 关键字：表名，字段名不能和关键字冲突
create table stu(
stuId number,
stuName varchar2(20),
age number(3),
sex char(3),
birthday date
);
select * from stu;
drop table stu;
--------------创建具有约束条件的表格
create table 表名(
字段名1 数据类型1 约束条件,
字段名2 数据类型2 约束条件,
字段名3 数据类型3 约束条件,
................ 约束条件,
字段名n 数据类型n 约束条件
);
--------------约束条件
1、唯一：unique
2、非空：not null / null
3、默认：default 值
4、检查：check(检查的条件)
5、主键：primary key ：包含唯一和非空两个约束
6、外键：references 主键所在的表名(主键名) ：外键的值要和主键对应
（外键值，必须包含在主键内）
-> 主外键的作用：保证数据的一致性。
create table t_class(
cId number primary key,
cName varchar2(20) unique
);
create table student(
stId number primary key,
stName varchar2(20) not null,
sex char(3) default '男' check(sex = '男' or sex = '女'),
birthday date default sysdate,
classId number references t_class(cId)
);
select sysdate from dual;
drop table t_class;
drop table student;
------------------------------------------
/*
商品售货系统设计案例
现有一个商品的数据、记录客户及其购物情况，由下三张表组成
商品goods（商品号goodsid,商品名goodsname,单价untiprice,商品类别category，供应商provider）
客户customer（客户号customerid，姓名name,住址address，电邮email,性别sex，身份证cardid）
购买purchase（客户号customerid,商品号goodsid，购买数量nums）
1、每个表格要建立主键或外键，客户的姓名不能为空值；单价必须大于0，购买数量必须在1到30之间；
电邮不能重复；客户的性别必须是男或女，默认是男。
*/
----------------------------------------------------------
create table goods(
goodsid number primary key,
goodsname varchar2(30),
untiprice number(7,2) check(untiprice > 0),
ccategory varchar2(20),
provider varchar2(30)
);
create table customer(
customerid number primary key,
cname varchar2(20) not null,
address varchar2(50),
email varchar2(30) unique,
sex char(3) default '男' check(sex='男' or sex='女'),
cardid varchar2(18)
);
create table purchase(
customerid number references customer(customerid),
goodsid number references goods(goodsid),
-- nums number check(nums >= 1 and nums <= 30)
nums number check(nums between 1 and 30)
);
select * from stu;
--------------查看表结构
步骤：
1、新建一个command window
2、输入命令：desc 表名;
--------------增加表的字段
alter table 表名 add(
字段1 数据类型1 约束条件,
....
);
alter table stu add(shengao number(4,2) not null,tizhong number(5,2));
--------------修改表的字段
alter table 表名 modify(
被修改的字段1 数据类型1 约束条件,
....
);
alter table stu modify(tizhong number(7,2));
--------------删除表的字段
alter table 表名 drop column 字段名;
alter table stu drop column tizhong;
--------------删除整张表
drop table 表名;
drop table stu;
-------------------------------------------
create table 宿舍表(
宿舍号 char(6) primary key,
宿舍电话 number(7) check(宿舍电话 between 6330000 and 6339999)
);
create table 同学表(
学号 char(6) primary key,
姓名 varchar2(30) not null,
性别 char(3) check(性别 = '男' or 性别 = '女'),
年龄 number not null,
民族 varchar2(10) default '彝族' not null,
身份证号 varchar2(18) unique,
宿舍号 char(6) references 宿舍表(宿舍号)
)
-------------------------------------------
select * from emp;
select * from dept;
--------------1、全表查询
语法：
select * / 字段名1,字段名2,...,字段名n
from 表名;
* -> 表示一个表的所有字段
-------------------------------------------
select *
from emp;
---------
select empno,ename,sal
from emp;
--------------2、条件查询
-- 比较运算符： > >= < <= !=或者<> = ，比较运算符两边的数据类型一样
语法：
select * / 字段名1,字段名2,...,字段名n
from 表名
where 查询条件;
-- 例：查询20号部门中全体员工的姓名、工资、和工种的有关信息。
select ename,sal,job
from emp
where deptno = 20;
-- 练习：查找出奖金超过其工资的雇员的姓名、工资、奖金和工种的信息。
select ename,sal,comm,job
from emp
where comm > sal;
--------------3、指定字段的重复值，只返回一行 -- 去重
语法：distinct 字段
select distinct deptno from emp;
-- 例：查找出20号部门中有多少种不同的工种。
select distinct job
from emp
where deptno = 20;
--------------4、多逻辑运算符 and or --- 用来连接多个条件
-- 例：查找出全部经理 和 第10号部门秘书的有关信息
select *
from emp
where job = 'MANAGER' or job = 'CLERK' and deptno = 10;
-- 查找出不是30号部门中的所有经理的所有信息。
select *
from emp
where job = 'MANAGER' and deptno != 30;
--------------5、多值比较运算符
-- 字段 between 值1 and 值2
-- 字段 in(指定的值)
-- 例:查找出工资在2000到3000之间的职工姓名、工种和工资
select ename,job,sal
from emp
where sal between 2000 and 3000;
-- 例：查询出全部秘书、分析员或推销员的姓名、工种、工资和所在部门号
select ename,job,sal,deptno
from emp
where job = 'CLERK' or job = 'ANALYST' or job = 'SALESMAN';
----------
select ename,job,sal,deptno
from emp
where job in('CLERK','ANALYST','SALESMAN');
-- 练习：查找出工资在2500到3500之外的职工姓名、工种和工资
select ename,job,sal
from emp
where sal not between 2500 and 3500;
-- 练习：查询出工资分别是1500，2500，3000的分析员或推销员的姓名、工种、工资和所在部门号
select ename,job,sal,deptno
from emp
where sal in(1500,2500,3000) and job in('ANALYST','SALESMAN');
--------------6、模糊查询
关键字: like
匹配符：
% -> 匹配任意长度的字符串
'ABC%' -> 表示匹配以ABC开头的任意字符串
'%ABC%' -> 表示匹配包含ABC的任意字符串
'%ABC' -> 表示匹配以ABC结尾的任意字符串
_ -> 匹配任意单个字符
A_C ->表示配以A开头，C结尾的任意三个字符串
A__C ->表示配以A开头，C结尾的任意四个字符串
AC_ ->表示配以AC开头的任意三个字符串
-- 例：查询出名字以"MA"开头的全部职工的姓名、工种、工资和部门号
select ename,job,sal,deptno
from emp
where ename like 'MA%';
-- 例：查询出名字以"SCO"开头的，长度为5位的全部职工的姓名、工种、工资和部门号
select ename,job,sal,deptno
from emp
where ename like 'SCO__';
练习：
1、查找出所有工种以'MANAG'开头的职工姓名、工种和工资
select ename,job,sal
from emp
where job like 'MANAG%';
2、查找出所有姓名以'ALLE'开头，且长度为5的职工姓名、工种和工资
select ename,job,sal
from emp
where ename like 'ALLE_';
--------------7、查找空值 is null 非空 is not null
select *
from emp
where comm is null;
select *
from emp
where comm is not null;
--------------8、排序 order by 字段 -->根据指定字段进行排序
语法：
select * / 字段 / 表达式
from 表
where 单条数据的过滤条件
order by 字段1/表达式 asc,字段2/表达式 desc;
-- asc 表示升序，默认为升序
-- desc 表示降序
-- order by 字段1 asc,字段2 desc -- 先根据字段1进行升序；
-- 如果字段1存在重复的值，则根据字段2降序
-- 例：计算每个销售人员的年度总报酬，并按总报酬由高到低顺序显示
select (sal+comm)*12 年度总报酬
from emp
where job = 'SALESMAN'
order by (sal+comm)*12 desc;
练习：
1、查找出工资高于1000元的职工的姓名、工种、工资和部门号，并按部门号由小到大排序显示
select e.ename,e.job,e.sal,e.deptno
from emp e
where e.sal > 1000
order by e.deptno;
2、查找出奖金超过本人基本工资3%的职工的姓名，工资，奖金，奖金与工资的比例，
并按其比例由高到低显示
select e.ename,e.sal,e.comm,e.comm/e.sal 奖金与工资的比例
from emp e
where e.comm > e.sal * 0.03
order by e.comm/e.sal desc;
3、按工种升序，而同工种按工资降序排列显示全部职工的姓名，工种，工资。
select e.ename,e.job,e.sal
from emp e
order by e.job,e.sal desc;
----------------------------------------------------------------------------------------------
select * from t_class;
select * from student;
--------------9、向表中添加数据
语法：
insert into 表名(字段1,字段2,...,字段n) values(值1,值2,...,值n);
insert into t_class(cid,cname) values(1,'软件测试班');
insert into t_class(cid,cname) values(2,'JAVA开发班');
insert into student(stid,stname,sex,birthday,classid)
values(1,'小王','男',sysdate,2);
insert into student(stid,stname,sex,classid)
values(2,'小吴','男',2);
insert into student(stid,stname,birthday,classid)
values(3,'小花',sysdate,1);
insert into student
values(4,'小米','男',sysdate,2);
rollback; -- 撤销/回滚
commit; -- 将数据永久保存到数据库
-- 将具有日期格式的字符串转换为日期类型
语法：to_date('被转换的字符串','日期格式')
to_date('1997-07-01','YYYY-MM-DD')
--- （5,'小明','男','1997-07-01',1）
insert into student(stid,stname,sex,birthday,classid)
values(5,'小明','男',to_date('1997-07-01','YYYY-MM-DD'),1);
-- 练习：在emp表中，查找1981-05-01之前入职的员工信息
select *
from emp e
where e.hiredate < to_date('1981-05-01','YYYY-MM-DD');
--------------10、修改表中的数据
语法：
update 表名
set 字段1 = 值1,字段2 = 值2,...,字段n = 值n
where 单条数据的过滤条件;
update student
set stname = '老王'
where stname = '小王';
-- 将小花的性别插入对应的单元格
update student
set sex = '女'
where stname = '小花';
--------------11、删除表中的数据
语法：
delete 表名
where 单条数据的过滤条件;
delete student
where stname = '小王';
select * from t_class;
select * from student order by classid;
-- 练习：把cid为1的所有数据都删掉
/*
如果删除的数据涉及两张表，要先删除外键所在的表的数据，再删主键所在的表的数据
*/
delete student where classid = 1;
delete t_class where cid = 1;
-------------
truncate table 表名;
truncate table student;
truncate table t_class;
删除数据
delete：可以加where条件，数据可以回滚，删除速度慢
truncate：不可以加where条件，不数据可以回滚，删除速度快
drop：删除整个表
drop table student;
drop table t_class;
---------------------------------------------
select * from emp;
-------------- 12、聚合函数/统计函数/分组函数
count(*) -> 统计当前表格的行数
select count(*) from emp;
count(字段) -> 统计指定字段的非空行数
select count(comm) from emp;
-- 练习：统计emp表中，comm字段为空的行数
select count(*) from emp where comm is null;
select count(*)-count(comm) from emp;
avg(字段) -> 统计指定字段的平均值
select avg(sal) from emp;
---round(m,n) 精确到小数点的指定位数
select round(avg(sal),2) 平均值 from emp;
select round(avg(sal+nvl(comm,0)),2) 平均值 from emp;
sum(字段) -> 统计指定字段的和
select sum(sal) from emp;
-- nvl(m,n) 空值处理函数
select sum(sal+nvl(comm,0)) from emp;
min(字段) -> 统计指定字段的最小值
select min(sal) from emp;
max(字段) -> 统计指定字段的最大值
select max(sal) from emp;
----------
select count(*),count(comm),avg(sal),sum(sal),min(sal),max(sal)
from emp;
-- 例：计算emp表中公司职工的总人数及工种数
select count(*) 总人数,count(distinct job) 工种数
from emp;
-- 练习：计算全部销售员的年平均报酬。
select avg(sal)*12 年平均报酬
from emp
where job = 'SALESMAN';
--------------13、分组统计 group by 字段
select 字段 / 统计函数
from 表
where 单条数据的过滤条件
group by 字段1,字段2; -- 先按字段1分组，再在字段1内按字段2分组
-- 例：计算出公司支付给每个工种的总工资
select job,sum(sal)
from emp
group by job;
-- 例：计算每个部门中每种工种各有多少职工数。
select deptno,job,count(*)
from emp
group by deptno,job
order by deptno;
--- 求每个部门有多少职工
select deptno,count(*)
from emp
group by deptno;
-- 练习：统计各部门的人数。
select deptno,count(*)
from emp
group by deptno;
--- 对分组后的数据做过滤 having 分组后的过滤条件
select 字段 / 统计函数
from 表
where 单条数据的过滤条件
group by 字段
having 分组函数的比较表达式
order by 字段;
-- 例：查询各工种组的年平均工资，要求每个工种组至少在2人以上
select job,avg(sal)*12 年平均工资,count(*)
from emp
group by job
having count(*) > 2;
练习1：查询出至少有两名秘书的所有部门的部门号，并按人数降序排序。
select deptno,count(*)
from emp
where job = 'CLERK'
group by deptno
having count(*) >= 2
order by count(*) desc;
练习2：查询出所有经理和销售人员的年平均工资，并按年平均工资降序排
select job,avg(sal)*12 年平均工资
from emp
where job in('MANAGER','SALESMAN')
group by job
order by avg(sal)*12 desc;
select job,avg(sal)*12 年平均工资
from emp
group by job
having job in('MANAGER','SALESMAN')
order by avg(sal)*12 desc;
----------------------------------------------------------------------------------------------
--- 1、多表查询
-- 例：查找名字为"ALLEN"的职工所在的部门号、部门名和部门所在地
select d.deptno,d.dname,d.loc
from emp e,dept d
where e.deptno = d.deptno and e.ename = 'ALLEN';
-- 查询部门号是20,30,40的职工的员工编号，姓名，工资，部门所在位置。
select e.empno,e.ename,e.sal,d.loc
from emp e,dept d
where e.deptno = d.deptno and d.deptno in(20,30,40);
group by
having
order by
--- 查询的思路 ---
1、分析题目需求，确认查询的数据涉及哪些表；
2、如果查询的数据需要来源于多张，就要将表的相同字段在where后面用等于号连接起来；
3、如果需要对单条数据做过滤，就要将过滤条件追加到where字句后面，并用and连接；
4、如果需要对多组数据分别做统计，就要用到group by字句；
5、如果需要对分组后的数据做过滤，就要用到having字句；
6、如果需要对数据进行排序，就要用到order by字句。
-- 显示部门号为10的部门名、员工号和工资
select d.dname,e.empno,e.sal
from emp e,dept d
where e.deptno = d.deptno and d.deptno = 10;
-- 显示雇员名，雇员工资及所在的部门的名字，并按部门名排序
select e.ename,e.sal,d.dname
from emp e,dept d
where e.deptno = d.deptno
order by d.dname;
-- 显示平均工资高于2000的部门号和它的平均工资
select e.deptno,avg(e.sal)
from emp e
group by e.deptno
having avg(e.sal) > 2000;
-----------------------------------------------
select * from emp order by deptno;
select * from dept;
select e.*,d.*
from emp e,dept d
where e.deptno = d.deptno order by d.deptno;
--- 1、外连接：左连接，右连接 连接符：(+)
左连接：
select * / 字段 / 表达式 / 分组函数
from 表名1 表名1的别名, 表名2 表名2的别名
where 表名1的别名.字段 = 表名2的别名.字段(+);
--- 左连接
select e.*,d.*
from emp e,dept d
where e.deptno = d.deptno(+) order by d.deptno;
------------------------------------------------------------------------
右连接：
select * / 字段 / 表达式 / 分组函数
from 表名1 表名1的别名, 表名1 表名2的别名
where 表名1的别名.字段(+) = 表名2的别名.字段;
--- 右连接
select e.*,d.*
from emp e,dept d
where e.deptno(+) = d.deptno order by d.deptno;
-- 练习：显示出所有部门的编号、名称和其职工的姓名与工种。
select d.deptno,d.dname,e.ename,e.job
from dept d,emp e
where d.deptno = e.deptno(+);
-------------------------------------------------------------------------
select d.deptno,d.dname,e.ename,e.job
from dept d left join emp e on d.deptno = e.deptno;
select d.deptno,d.dname,e.ename,e.job
from dept d right join emp e on d.deptno = e.deptno;
-- 练习：列出无雇员的部门的情况。
select d.*
from dept d,emp e
where e.deptno(+) = d.deptno and e.empno is null;
---2、子查询/嵌套查询 子查询，需要用小括号括住
-- 例：查找出与“SMITH”在同一个部门工作的所有职工姓名及工资
select e.ename,e.sal
from emp e
where e.deptno = ('SMITH'所在的部门号);
--'SMITH'所在的部门号
select deptno
from emp
where ename = 'SMITH'
--
select e.ename, e.sal,e.deptno
from emp e
where e.deptno = (select deptno from emp where ename = 'SMITH');
----------
(1)、查找出工资比"SCOTT"工资高的职工的名字，工种，工资和所在的部门号，并按工资升序排序。
select e.ename,e.job,e.sal,e.deptno
from emp e
where e.sal > ("SCOTT"的工资)
order by e.sal;
-- "SCOTT"的工资
select sal from emp where ename = 'SCOTT'
----
select e.ename, e.job, e.sal, e.deptno
from emp e
where e.sal > (select sal from emp where ename = 'SCOTT')
order by e.sal;
(2)、查找出工资比"SCOTT"高，并且在"NEW YORK"工作的职工的有关情况。
select *
from emp e,dept d
where e.deptno(+) = d.deptno
and e.sal > ("SCOTT"的工资)
and d.loc = 'NEW YORK';
-- "SCOTT"的工资
select sal from emp where ename = 'SCOTT'
----
select *
from emp e, dept d
where e.deptno(+) = d.deptno
and e.sal > (select sal from emp where ename = 'SCOTT')
and d.loc = 'NEW YORK';
(3)、查找出具有最高月工资的雇员的姓名、工种和工资。
select e.ename,e.job,e.sal
from emp e
where e.sal = (最高月工资);
-- 最高月工资
select max(sal) from emp
----
select e.ename, e.job, e.sal
from emp e
where e.sal = (select max(sal) from emp);
(4)、查找出在"CHICAGO"工作的职工的姓名、工种和工资。
select e.ename,e.job,e.sal
from dept d,emp e
where e.deptno(+) = d.deptno and d.loc = 'CHICAGO';
---
select e.ename,e.job,e.sal
from emp e
where e.deptno = (在CHICAGO的部门号);
--在CHICAGO的部门号
select deptno from dept where loc = 'CHIGAGO'
---
select e.ename,e.job,e.sal
from emp e
where e.deptno = (select deptno from dept where loc = 'CHIGAGO');
--- 例：查找出部门10与部门30中工种相同的职工的姓名和工种。
select e.ename,e.job
from emp e
where e.deptno = 10 and e.job in(30号部门的工种);
-- 30号部门的工种
select distinct job from emp where deptno = 30
--
select e.ename, e.job
from emp e
where e.deptno = 10
and e.job in (select distinct job from emp where deptno = 30);
-----------------------------------
-- 查找出工资在1000到3500元之间的职工所在部门的所有人员的有关信息。
select e.*
from emp e
where e.deptno in (工资在1000到3500元之间的部门号);
-- 工资在1000到3500元之间的部门号
select distinct deptno from emp where sal between 1000 and 3500
-----
select e.*
from emp e
where e.deptno in
(select distinct deptno from emp where sal between 1000 and 3500);
-- 查找出工种在部门10中没有的其他部门职工的姓名、工种和工资信息。
select e.ename,e.job,e.sal
from emp e
where e.job not in(10部门的工种);
-- 10部门的工种
select distinct job from emp where deptno = 10
---
select e.ename, e.job, e.sal
from emp e
where e.job not in (select distinct job from emp where deptno = 10);
-- 查找出部门20中的工种相同的职工的姓名与工种。
select e.ename,e.job
from emp e
where e.deptno = 20 and e.job in(20号部门的相同工种);
-- 20号部门的相同工种
select job
from emp
where deptno = 20
group by job
having count(*) >= 2
-----
select e.ename, e.job
from emp e
where e.deptno = 20
and e.job in (select job
from emp
where deptno = 20
group by job
having count(*) >= 2);
-- 查找出10号部门中的工种与销售部门中任何职工工种相同的职工的信息。
select *
from emp e,dept d
where e.deptno(+) = d.deptno
and e.deptno = 10
and e.job in(销售部门的工种);
-- 销售部门的工种
select distinct job
from dept de,emp em
where de.deptno = em.deptno
and de.dname = 'SALES'
--------
select *
from emp e, dept d
where e.deptno(+) = d.deptno
and e.deptno = 10
and e.job in (select distinct job
from dept de, emp em
where de.deptno = em.deptno
and de.dname = 'SALES');
-- from 子查询
select * from emp;
select * from (select * from emp);
select * from (select empno,ename from emp);
---- 伪列：rownum -----
select rownum,e.* from emp e
--- 查询表的前10行
select rownum,e.* from emp e where rownum <= 10;
--- rownum不属于任何一个表
select e.rownum,e.* from emp e
--- 查询表的第5到第10行 -- rownum不能跨过1而存在
select * from (select rownum rn,e.* from emp e)
--
select * from (select rownum rn,e.* from emp e) t
where t.rn < 5;
--
select * from (select rownum rn,e.* from emp e) t
where t.rn between 5 and 10;
-- 练习：查询一个表的后5行
select * from (select rownum rn,e.* from emp e) t
where t.rn < 5;
select *
from (select rownum rn, e.* from emp e) t
where t.rn > (select count(*) - 5 from emp);
