数据库 --->N实例
实例-->表--->字段
数据库中数据都存储在表中
表的创建:
create table 表名(
字段名 数据类型,
字段2 数据类型
)
注意：
1.sql语法不区分大小写，数据区分大小写
2.表名的命名规则 字母，数字，下划线，-,$,#
3.字段命名也一样oo
oo
数据类型
--字符串类型
char(长度) 最大2000字节,如果数据没有达到声明字节的长度，剩余
的空间以空格来替补.长度指的是字节大小，例如一个中文在GBK编码
中占据两个字节，UTF-8编码中占据3个字节
varchar(长度) sql标准的字符串类型,最大4000字节，和char相比
效率更低，但是varchar类型如果数据达不到最大长度会自动节约空间
varchar2(长度) 同上，oracle自定义的类型
CLOB , BLOB 最大4G，用来放图片，音频视频等大文件的字节序列
--数值类型
number 数值类型,整数和浮点都可以存储 10的38次幂 最小-10 38
number(5) 只能存放整型，并且最大位数5位，99999
number(7,2) 最大7位，5位整数，最大两位精度 99999.99
--时间
date 年月日时分秒
timestamp 相比date多了毫秒
--先勾，再锁，再提交
--创建表的语法，然后选中这一段点击上面那个绿色的箭头
--删除表结构，删除表会把数据一并删除
drop table student;--后面是表名
create table student(
studentid number,--id
username varchar2(100),--姓名
sex char(3), --性别
age number(3), --年龄
birthday date --生日
)
---修改表结构
---添加字段
alter table 表名 add 新的字段名 类型
alter table student add phone number(11)
--修改列类型
alter table 表名 modify 列名 类型
alter table student modify username varchar2(20)
--删除列
alter table student drop column phone
--修改表名
rename students to student
---DML
--增删改查
--添加数据 insert
--按照指定的列插入
insert into 表名(列名,列名2) values (值1，值2)
不允许为null的列一定要插入
insert into student(studentid,username,sex,age) values
(1000,'爱新觉罗.迪迦','男',18);
insert into student(studentid,username,sex,age) values
(1001,'舞法天女.球女','女',22);
commit;
--直接插入所有的列，顺序按照建表的列的顺序来
insert into 表名 values(值，值)
--插入时间第一种方式 sysdate
insert into student values(1002,'多尔衮布隆','男',44,sysdate)
--插入时间的第二种方式，手动输入
insert into student values
(1003,'泰罗奥特曼','男',44,'1-8月-2018')
--插入时间第三种方式，使用oracle转换函数to_date()
insert into student values
(1007,'赛文奥特曼','男',23,
to_date('2008-8-8 18:59:45','yyyy-mm-dd hh24:mi:ss'))
使用to_Date()把字符串转换为指定的时间格式的date对象返回
---删除 delete
delete from 表名
delete from student
根据条件筛选只删除某一部分的数据,使用where 关键字
where后面写上筛选的条件表达式，满足该表达式的数据才会被删除
where不仅仅作用在删除，还包括改，查
比较运算符: > < >= <= = != <>
逻辑运算符: ! and or
--删除年龄小于20岁的学生
delete from student where age<20
删除可以回滚数据，如果已经提交不可回滚，drop是直接删除表结构
不存在回滚
--删除没有生日的学生，生日为空 is null 和is not null
delete from student where birthday is not null
---update 修改
update 表名 set 要修改的列=新的值,要修改的第二列=值,....
这样会修改整张表的某几列，一般结合where
update student set sex='女',birthday='2-2月-2012'
where username='赛文奥特曼'
----查询 select
select * from 表名
--查询*会把所有的列和数据显示
select * from student
--不建议查询所有列,只查询某几列
select 列名,列名 from student
select studentid,username from student
drop table emp;
drop table dept;
-- Create table
create table EMP
(
empno NUMBER(4) not null,
ename VARCHAR2(10),
job VARCHAR2(9),
mgp NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
create table DEPT(
DEPTNO number not null,
DNAME VARCHAR2(50),
LOC VARCHAR2(50)
);
--把查询结果直接插入到一张表中，要求查询的列数和表的
列数一一对应，类型也要对应
insert into emp select * from scott.emp;
---查询的时候使用条件，where
---查询出薪资大于2000的员工的信息
select * from emp where sal>2000
---查询出奖金不为空的员工的信息
select * from emp where comm is not null
---查询部门编号为20的员工信息
select * from emp where deptno = 20
---查询员工职位MANAGER并且工资大于3000的员工信息
select* from emp where job='MANAGER' and sal>3000
---nvl(值,替换值)在查询的select后面使用 把为null的列
查询显示的时候以替换值显示
select empno,ename,sal,nvl(comm,0) from emp
---as 取别名
select empno AS 员工编号,ename AS 员工姓名 from emp
--算数
select ename 姓名,sal*12 年薪 from emp
---把多列的数据拼接显示 ||
select '员工编号:'||empno||' 姓名:'||ename as 员工信息
from emp
---去重 distinct,必须写在select后第一个单词
select distinct empno,ename from emp
--in和not in
--in用于匹配多个条件,类似于多重or
select * from emp where empno =7566 or empno=7521
or empno=7654
--相当于把表中的每一条数据拿到in后面的括号里去逐一匹配
select * from emp where empno in(7566,7521,7654)
--not in相反
--模糊查询
like 替代等号
%表示的是任意位数的任意字符
_ 下划线表示的是一位任意字符
--查询名字中有s的
select * from emp where ename like 'S%'
--名字长度为6个的
select * from emp where ename like '______'
--查询名字4个字符，并且结尾RD
select * from emp where ename like '__RD'
--查询名字为4个字符或者6个字符的员工
select * from emp where ename like '____' or ename like '______'
--查询有奖金并且名字S开头的员工
select * from emp where comm is not null and
ename like 'S%'
--排序 order by 默认升序 asc升序，desc降序
select * from emp order by empno desc
---分组函数，聚合函数
sum() max() min() count() avg()
--最大的薪资
select max(sal) from emp
--最小的薪资
select min(sal) from emp
--总工资
select sum(sal) from emp
--count用于统计某一列的数据个数,null的不会统计
select count(*) from emp
--avg平均
select avg(sal) from emp
分组 group by
如果有where的情况下，group by必须出现在where语句后
groupby作用是分组查询，后面需要写指定的列名。表示按照
某一列或者多列进行分组。分组后select语句后只能出现
分组的列以及分组函数,分组就是把整张表的数据划分成多个
小组，然后查询的时候使用分组函数统计每一个组内的数据
---查询每个职位的员工人数
select job,count(*) from emp group by job
--查询每个部门的 最大和最小工资
select deptno,max(sal) 最大工资,min(sal) 最小工资
from emp group by deptno
--求每个部门的每个职位的平均工资
select deptno,job,avg(sal)
from emp group by deptno,job order by deptno
--求员工总工资大于10000的部门
--使用having 写在group by后面，用来对分组的数据筛选
---having后可以使用分组函数
select deptno,sum(sal)
from emp group by deptno having sum(sal)>10000
查询语句执行顺序
1.from 表 提取整张表的数据
2.有where的情况就进行where筛选
3.有group by就进行分组
4.有having就执行having在分组的数据上进行筛选
5.select 后面的分组函数
6.order by
--求部门人数在在4到6个之间的部门编号以及人数
select deptno,count(*)
from emp group by deptno
having count(*) between 4 and 6
--求部门最大工资大于3000并且最小工资小于1000的部门
编号以及最大最小工资
select deptno, max(sal),min(sal)
from emp group by deptno having max(sal)>3000 and
min(sal)<1000
--求部门平均工资在1000到2000之间的部门
select deptno
from emp group by deptno having avg(sal) between
1000 and 2000
---查询每个员工的名字以及所在的部门名字
--连表查询,多张表连接查询
--内连接，等值连接。通过查询多张表，两表中等值的数据
显示在同一行中，如果没有匹配的等值数据就不显示
select * from emp,dept
where emp.deptno = dept.deptno
---查询30号部分的所有员工的信息和所在部门名字
select e.ename,d.dname
from emp e,dept d where d.deptno = 30
and e.deptno = d.deptno
---查出工资大于10000的员工编号，工资,部门名称
select e.empno,e.sal,d.dname
from emp e,dept d where e.sal>3000 and
e.deptno = d.deptno
---查询每个员工的姓名以及上司的编号和姓名
select e.ename,boss.empno,boss.ename
from emp e,emp boss
where e.mgp = boss.empno
---查询和SMITH同一个部门的其他员工的信息
---嵌套查询，子查询.在一个查询语句内包含另一条查询
优先执行括号内的子查询语句
--单行单列子查询
select * from emp where deptno =
(select deptno from emp where ename = 'SMITH')
--多行单列的子查询,使用in匹配。不能用等号
--查询总工资大于10000的部门的员工信息
select * from emp where deptno in(
select deptno from emp group by deptno
having sum(sal)>10000)
--查询和WARD同一个部门同一种职位的其他员工信息
select * from emp where (deptno,job) =
(select deptno,job
from emp where ename = 'WARD')
--查询大于自己部门平均工资的员工信息
--把查询的结果当成一个临时的表，去连查
select e.*,e2.deptno,e2.avg
from emp e,
(select deptno,avg(sal) as avg from emp group by deptno ) e2
where e.deptno = e2.deptno and e.sal>e2.avg
drop table t_student;
drop table t_course;
drop table t_score;
drop table t_teacher;
create table t_student
(
sno varchar2(20) not null, --学号
sname varchar2(20) not null,--学生姓名
ssex varchar2(10) not null, --性别
sbirthday date, --生日
class varchar2(20) --所在班级编号
);
---------课程表
create table t_course
(
cno varchar2(20) not null,--课程编号
cname varchar2(20) not null, --课程名
tno varchar2(20) not null --授课老师编号
);
---------------分数
create table t_score
(
sno varchar2(20) not null, ---学号
cno varchar2(20) not null, ---课程号
degree number(10, 1) not null --分数
);
---老师表
create table t_teacher
(
tno varchar2(10) not null, --老师编号
tname varchar2(20) not null, --老师姓名
tsex varchar2(20) not null, --老师性别
tbirthday date not null, --生日
prof varchar2(20), --职称
depart varchar2(20) not null --部门
);
create table t_grade(
low number(3,0),
upp number(3),
rank char(1)
);
insert into t_student (sno,sname,ssex,sbirthday,class) values (108 ,'曾华'
,'男' ,to_date('1977-09-01','yyyy-mm-dd'),95033);
insert into t_student (sno,sname,ssex,sbirthday,class) values (105 ,'匡明'
,'男' ,to_date('1975-10-02','yyyy-mm-dd'),95031);
insert into t_student (sno,sname,ssex,sbirthday,class) values (107 ,'王丽'
,'女' ,to_date('1976-01-23','yyyy-mm-dd'),95033);
insert into t_student (sno,sname,ssex,sbirthday,class) values (101 ,'李军'
,'男' ,to_date('1976-02-20','yyyy-mm-dd'),95033);
insert into t_student (sno,sname,ssex,sbirthday,class) values (109 ,'王芳'
,'女' ,to_date('1975-02-10','yyyy-mm-dd'),95031);
insert into t_student (sno,sname,ssex,sbirthday,class) values (103 ,'陆君'
,'男' ,to_date('1974-06-03','yyyy-mm-dd'),95031);
insert into t_course(cno,cname,tno)values ('3-105' ,'计算机导论',825);
insert into t_course(cno,cname,tno)values ('3-245' ,'操作系统' ,804);
insert into t_course(cno,cname,tno)values ('6-166' ,'数据电路' ,856);
insert into t_course(cno,cname,tno)values ('9-888' ,'高等数学' ,100);
insert into t_score(sno,cno,degree)values (103,'3-245',86);
insert into t_score(sno,cno,degree)values (105,'3-245',75);
insert into t_score(sno,cno,degree)values (109,'3-245',68);
insert into t_score(sno,cno,degree)values (103,'3-105',92);
insert into t_score(sno,cno,degree)values (105,'3-105',88);
insert into t_score(sno,cno,degree)values (109,'3-105',76);
insert into t_score(sno,cno,degree)values (101,'3-105',64);
insert into t_score(sno,cno,degree)values (107,'3-105',91);
insert into t_score(sno,cno,degree)values (108,'3-105',78);
insert into t_score(sno,cno,degree)values (101,'6-166',85);
insert into t_score(sno,cno,degree)values (107,'6-106',79);
insert into t_score(sno,cno,degree)values (108,'6-166',81);
insert into t_teacher(tno,tname,tsex,tbirthday,prof,depart)
values (804,'李诚','男',to_date('1958-12-02','yyyy-mm-dd'),'副教授','计算机系');
insert into t_teacher(tno,tname,tsex,tbirthday,prof,depart)
values (856,'张旭','男',to_date('1969-03-12','yyyy-mm-dd'),'讲师','电子工程系');
insert into t_teacher(tno,tname,tsex,tbirthday,prof,depart)
values (825,'王萍','女',to_date('1972-05-05','yyyy-mm-dd'),'助教','计算机系');
insert into t_teacher(tno,tname,tsex,tbirthday,prof,depart)
values (831,'刘冰','女',to_date('1977-08-14','yyyy-mm-dd'),'助教','电子工程系');
insert into t_grade values(90,100,'a');
insert into t_grade values(80,89,'b');
insert into t_grade values(70,79,'c');
insert into t_grade values(60,69,'d');
insert into t_grade values(0,59,'e');
commit;
题目：
1、 查询student表中的所有记录的sname、ssex和class列。
select sname ,ssex,class from t_student
2、 查询教师所有的单位即不重复的depart列。
select distinct depart from t_teacher
3、 查询student表的所有记录。
select * from t_student
4、 查询score表中成绩在60到80之间的所有记录。
select * from t_score where degree between 60 and 80
5、 查询score表中成绩为85，86或88的记录。
select * from t_score where degree in(85,86,88)
6、 查询student表中“95031”班或性别为“女”的同学记录。
select * from t_student where class='95031' or ssex='女'
7、 以class降序查询student表的所有记录。
select * from t_student order by class desc
8、 以cno升序、degree降序查询score表的所有记录。
select * from t_score order by cno , degree desc
9、 查询“95031”班的学生人数。
select count(*) from t_student where class ='95031'
10、查询score表中的最高分的学生学号和课程号。
select sno,cno from t_score where degree =
(select max(degree) from t_score )
11、查询‘3-105’号课程的平均分。
select avg(degree) from t_score where cno = '3-105'
12、查询score表中至少有5名学生选修的并以3开头的课程的平均分数。
select cno,avg(degree)
from t_score where cno like '3%' group by cno having
count(*)>=5
13、查询最低分大于70，最高分小于90的sno列。
select sno from t_score group by sno having max(degree)<90
and min(degree)>70
14、查询所有学生的sname、cno和degree列。
select s.sname, sc.cno, sc.degree
from t_score sc,t_student s where s.sno = sc.sno
15、查询所有学生的sno、cname和degree列。
select c.cname, sc.sno, sc.degree
from t_score sc,t_course c where c.cno = sc.cno
16、查询所有学生的sname、cname和degree列。
select s.sname,c.cname,sc.degree
from t_score sc,t_student s,t_course c
where sc.sno = s.sno and sc.cno = c.cno
17、查询“95033”班所选课程的平均分。
select avg(degree) from t_score where sno in(
select sno from t_student where class='95033')
18、查询所有同学的sno、cno和rank列。
select sc.*,g.rank
from t_score sc,t_grade g
where sc.degree between g.low and g.upp
19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select * from t_score sc,t_student s where cno ='3-105' and
degree>(select max(degree) from t_score where sno = '109')
20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
--1.先查询所有选修两门及以上课程的学生学号，以及最大分
select sc.* from
(select sno,max(degree) max
from t_score group by sno having count(*)>=2) s1,
t_Score sc
where s1.sno = sc.sno and sc.degree<s1.max
21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select * from t_score where degree>(
select degree from t_score where sno ='109' and cno=
'3-105')
22、查询和学号为108的同学同年出生的所有学生的sno、sname和sbirthday列。
select * from t_student where to_char(sbirthday,'yyyy')=
(select to_char(sbirthday,'yyyy') from t_student
where sno = '108')
23、查询“张旭“教师任课的学生成绩。
select * from t_score where cno =(
select cno from t_course where tno =(
select tno from t_teacher where tname = '张旭'
)
)
select s.*
from t_score s ,t_teacher t,t_course c
where c.cno = t.cno and s.cno = c.cno and t.tname = '张旭'
24、查询选修某课程的同学人数多于5人的教师姓名。
select tname from t_teacher where tno in(
select tno from t_course where cno in(
select cno from t_score group by cno having count(*)>5))
25、查询95033班和95031班全体学生的记录。
select * from t_student where class in(95033,95031)
26、查询存在有85分以上成绩的课程cno.
select cno
from t_score group by cno having max(degree)>85
select distinct cno from t_score where degree>85
27、查询出“计算机系“教师所教课程的成绩表。
select * from t_Score where cno in(
select cno from t_course where tno in(
select tno from t_teacher where depart = '计算机系'))
28、查询“计算机系”与“电子工程系“不同职称的教师的tname和prof。
select distinct tname,prof from t_teacher
---查询“计算机系”与“电子工程系“不同的职称
select * from t_teacher where prof not in(
select t1.prof
from t_teacher t1,t_teacher t2 where t1.depart='电子工程系'
and t2.depart='计算机系' and t1.prof=t2.prof
and t1.tno!=t2.tno
)
---不同系的不同职称，显示系名和职称名
select depart,prof from t_teacher where prof in(
select s.prof from
(select distinct depart,prof from t_teacher ) s
group by prof having count(*)<2)
29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的cno、sno和degree,并按degree从高到低次序排序。
30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的cno、sno和degree.
31、查询所有教师和同学的name、sex和birthday.
select sname,ssex,sbirthday from t_student
union all
select tname,tsex,tbirthday from t_teacher
32、查询所有“女”教师和“女”同学的name、sex和birthday.
select sname,ssex,sbirthday from t_student where ssex='女'
union all
select tname,tsex,tbirthday from t_teacher where tsex='女'
33、查询成绩比该课程平均成绩低的同学的成绩表。
select s1.*
from t_score s1,
(select cno ,avg(degree) avg from t_score group by cno) s2
where s1.cno = s2.cno and s1.degree<s2.avg
34、查询所有任课教师的tname和depart.
select tname ,depart from t_teacher where tno in(
select distinct tno from t_course )
35 查询所有未讲课的教师的tname和depart.
select tname ,depart from t_teacher where tno not in(
select distinct tno from t_course )
36、查询至少有2名男生的班号。
select class
from t_student where ssex='男' group by class having
count(*)>=2
37、查询student表中不姓“王”的同学记录。
select *
from t_student where sname not like '王%'
38、查询student表中每个学生的姓名和年龄。
select sname,
to_char(sysdate,'yyyy')-to_char(sbirthday,'yyyy')
from t_Student
39、查询student表中最大和最小的sbirthday日期值。
select max(sbirthday),min(sbirthday) from t_Student
40、以班号和年龄从大到小的顺序查询student表中的全部记录。
select* from t_student order by class,sbirthday desc
41、查询“男”教师及其所上的课程。
select * from t_course where tno in(
select tno from t_teacher where tsex = '男')
42、查询最高分同学的sno、cno和degree列。
select * from t_score where degree = (
select max(degree) from t_score)
43、查询和“李军”同性别的所有同学的sname.
select sname from t_student where ssex = (
select ssex from t_student where sname = '李军')
44、查询和“李军”同性别并同班的同学sname.
select sname from t_student where (ssex,class) = (
select ssex,class from t_student where sname = '李军')
45、查询所有选修“计算机导论”课程的“男”同学的成绩表
select sc.*
from t_course c ,t_score sc,t_student s
where c.cname='计算机导论' and c.cno = sc.cno and sc.sno =
s.sno and s.ssex='男'
create table student_score(
sname varchar2(50),
coursename varchar2(50),
score number
)
名字 语文 数学 英语
狗蛋 70 77 0
李明 90 80 100
--列转行
select sname 名字,
sum(decode(coursename,'语文',score,0)) 语文,
sum(decode(coursename,'数学',score,0)) 数学,
sum(decode(coursename,'英语',score,0)) 英语
from student_Score group by sname
select year,
sum(decode(month,1,amount,0)) m1,
sum(decode(month,2,amount,0)) m2,
sum(decode(month,3,amount,0)) m3,
sum(decode(month,4,amount,0)) m4,
from table group by year
名字 第一名 第二名 第三名
A 2 1 0
B 0 2 1
C 1 1 1
---
create table RacingResults(
win_name char(30) not null,
place_name char(30) not null,
show_name char(30) not null
)
select name,
sum(decode(mingzi,1,count,0)) 第一名,
sum(decode(mingzi,2,count,0)) 第二名,
sum(decode(mingzi,3,count,0)) 第三名
from
(
select win_Name as name,1 as mingzi ,count(*) as count
from RacingResults group by win_name
union all
select place_name as name,2 as mingzi ,count(*) as count
from RacingResults group by place_name
union all
select show_Name as name,3 as mingzi ,count(*) as count
from RacingResults group by show_name
) s group by s.name
oracle函数
分组函数 min max sum avg count
转换函数
to_char 把时间转换成字符串
to_Date 把字符串转换成指定的时间格式
to_number
常用函数
nvl(值,'替换值') 在查询的时候把空的列以替换值显示
nvl2(值,'不为null的时候','为null的时候')
decode(值,if,then,else[可省略]) 类似多重if else if
详情请看PPT
（1） 查询20部门的所有员工信息。
select * from emp where deptno = 20
（2） 查询所有工种为CLERK的员工的员工号、员工名和部门号。
select empno,deptno,ename from emp where job = 'CLERK'
（3） 查询奖金（COMM）高于工资（SAL）的员工信息。
select * from emp where comm>sal
（4） 查询奖金高于工资的20%的员工信息。
select * from emp where comm>(sal*0.2)
（5） 查询10号部门中工种为MANAGER和20部门中工种为CLERK的员工的信息。
select * from emp where deptno = 10 and job ='MANAGER' or
deptno = 20 and job ='CLERK'
（6） 查询所有工种不是MANAGER和CLERK，
select* from emp where job not in ('MANAGER','CLERK')
--且工资大于或等于2000的员工的详细信息。
select* from emp where job not in ('MANAGER','CLERK') and
sal>=2000
（7） 查询有奖金的员工的不同工种。
select distinct job from emp where comm is not null
and comm!=0
（8） 查询所有员工工资与奖金
select sum(sal)+sum(comm) from emp
的和。
（9） 查询没有奖金或奖金低于100的员工信息。
select distinct job from emp where comm is not null or
comm<1000
--（10） 查询各月倒数第3天(倒数第2天)入职的员工信息。
select* from emp where hiredate=last_day(hiredate)-2
（11） 查询工龄大于或等于25年的员工信息。
select* from emp
where months_between(sysdate,hiredate)>=12*25
--（12） 查询员工信息，要求以首字母大写的方式显示所有员工的姓名。
select initcap(ename) from emp
（13） 查询员工名正好为6个字符的员工的信息。
select* from emp where ename like '______'
（14） 查询员工名字中不包含字母“Ｓ”的员工。
select* from emp where ename not like '%S%'
（15） 查询员工姓名的第二字母为“M”的员工信息。
select* from emp where ename not like '_M%'
--（16） 查询所有员工姓名的前三个字符。
select substr(ename,1,3) from emp
--（17） 查询所有员工的姓名，如果包含字母“S”，则用“s”替换。
select replace(ename,'S','s') from emp
--返回被替换了指定子串的字符串。
（18） 查询员工的姓名和入职日期，并按入职日期从先到后进行排序。
select ename,hiredate from emp order by hiredate
（19） 显示所有员工的姓名、工种、工资和奖金，按工种降序排序，
--若工种相同则按工资升序排序。
select ename,job,sal,comm from emp order by job desc,sal asc
（20） 显示所有员工的姓名、入职的年份和月份，
--按入职日期所在的月份排序，若月份相同则按入职的年份排序。
select to_Char(hiredate,'YYYY') year,to_Char(hiredate,'mm') month from emp
order by month,year
（21） 查询在2月份入职的所有员工信息。
select* from emp where to_Char(hiredate,'mm')=2
（22） 查询所有员工入职以来的工作期限，
用“XX年XX月XX日”的形式表示。
1.先取得两个时间的月份差，用月差去除12向下取整得到整年份
2.再用两个时间的月份差%12得到多出来的月份，向上取整
3.把两个时间的月份整的差添加在原本的日期上得到新的时间
再用当前系统时间去减去那天的时间得到一个日，向上取整
select
empno,
floor(months_between(sysdate,hiredate)/12) 年,
ceil(mod(months_between(sysdate,hiredate),12)) 月,
ceil(sysdate-add_months(hiredate,floor(months_between(sysdate,hiredate)))) 天
from emp
（23.1） 查询至少有一个员工的部门信息。
select * from dept where deptno in(
select deptno
from emp group by deptno having count(*)>=1)
（23.2） 查询至少有两个员工的部门信息。
select * from dept where deptno in(
select deptno
from emp group by deptno having count(*)>=2)
（24） 查询工资比
SMITH员工工资
高的所有员工信息。
select * from emp where sal >(
select sal from emp where ename ='SMITH')
（25） 查询所有员工的姓名及其直接上级的姓名。
select e.ename,boss.ename from emp e,emp boss
where e.mgp =boss.empno
（26） 查询入职日期早于其直接上级领导的所有员工信息。
select e.ename,boss.ename from emp e,emp boss
where e.mgp =boss.empno and e.hiredate<boss.hiredate
（27） 查询所有部门及其员工信息，包括那些没有员工的部门。
（28） 查询所有员工及其部门信息，包括那些还不属于任何部门的员工。
（29） 查询所有工种为CLERK的员工的姓名及其部门名称。
select * from emp e,dept d where e.job ='CLERK' and e.deptno
=d.deptno
（30） 查询最低工资大于2500的各种工作。
-----------------------------------------------------------------
select job from emp group by job having min(sal)>2500
（31） 查询平均工资低于2000的部门及其员工信息。
select * from emp where deptno in(
select deptno from emp group by deptno having avg(sal)<2000)
（32） 查询在SALES部门工作的员工的姓名信息。
select* from emp where deptno = (
select deptno from dept where dname = 'SALES')
（33） 查询工资高于公司平均工资的所有员工信息。
select * from emp where sal >(
select avg(sal) from emp)
（34） 查询出与SMITH员工从事相同工作的所有员工信息。
select * from emp where job =(
select job from emp where ename = 'SMITH')
（35） 列出工资等于30部门中某个员工的工资的所有员工的姓名和工资。
select* from emp where sal in(
select sal from emp where deptno = 30)
and deptno !=30
（36） 查询工资高于30部门工作的所有员工的工资的员工姓名和工资。
select* from emp where sal >(
select max(sal) from emp where deptno = 30)
（37） 查询每个部门中的员工数量、平均工资和平均工作年限。
select deptno,count(*),avg(sal),
avg(floor(months_between(sysdate,hiredate)/12))
from emp group by deptno
（38） 查询从事同一种工作但不属于同一部门的员工
信息。
（39） 查询各个部门的详细信息以及部门人数、部门平均工资。
select * from
(select deptno,count(*),avg(sal) from emp group by deptno) s,
dept d where d.deptno = s.deptno
（40） 查询各种工作的最低工资。
select job,min(sal) from emp group by job
（41） 查询各个部门中不同工种的最高工资。
select deptno,job,max(sal) from
emp group by deptno,job
（42） 查询10号部门员工及其领导的信息。
select*
from emp e ,emp boss where e.mgp = boss.empno and
e.deptno = 10
（43） 查询各个部门的人数及平均工资。
select deptno,count(*),avg(sal) from emp group by deptno
（44） 查询工资为某个部门平均工资的员工的信息。
select * from emp where sal in(
select avg(sal) avg from emp group by deptno)
（45） 查询工资高于本部门平均工资的员工的信息。
select e.*,s.avg from
(select deptno,avg(sal) avg from emp group by deptno) s,
emp e where s.deptno = e.deptno and e.sal>s.avg
（46） 查询工资高于本部门平均工资的员工的信息及其部门的平均工资。
（47） 查询工资高于20号部门某个员工工资的员工的信息。
select * from emp where sal >(
select min(sal) from emp where deptno = 20)
（48）统计各个工种的员工人数与平均工资。
select job,count(*),avg(sal)
from emp group by job
（49） 统计每个部门中各工种的人数与平均工资。
select deptno,job,count(*),avg(sal)
from emp group by deptno, job
（50） 查询其他部门中工资、奖金与30号部门某员工工资、
（51） 查询部门人数大于5的部门的员工信息。
select deptno
from emp group by deptno having count(*)>5
（52） 查询所有员工工资都大于1000的部门的信息。
select deptno from emp
group by deptno having min(sal)>1000
（53） 查询所有员工工资都大于1000的部门的
信息及其员工信息。
select * from emp e,dept d
where e.deptno in
(select deptno from emp
group by deptno having min(sal)>1000)
and e.deptno = d.deptno
（54） 查询所有员工工资都在900～3000之间的部门的信息。
select deptno
from emp group by deptno
having min(sal)>=900 and max(sal)<=3000
（55） 查询有工资在900～3000之间的员工所在部门的员工信息。
select* from emp where deptno in(
select distinct deptno from emp where sal between 900 and 3000
)
（56） 查询每个员工的领导所在部门的信息。
（57） 查询人数最多的部门信息。
rownum 给查询的结果行号
rownum 不能使用大于号
如果使用rownum查询区间，需要把rownum先查询出来做为伪列，然后
取列的值的区间
select s.*,rownum from
(select deptno,count(*) count from emp group by deptno
order by count desc) s
where rownum=1
（58） 查询30号部门中工资排序前3名的员工信息。
select s.*,rownum from
(select emp.* from emp where deptno = 30 order by sal desc) s
where rownum<=3
（59） 查询所有员工中工资排序在5到10名之间的员工信息。
select * from (
select s.*,rownum rn from (
select * from emp order by sal desc) s
) e where e.rn between 5 and 10
（60） 查询指定年份之间入职的员工信息。(1980-1985)
exists 关键字
select * from emp e where exists
(select * from dept where
dept.deptno=e.deptno and dept.deptno=30)
使用exists和in的区别
in用于外表数据比较大的情况
exist用于子查询表较大的情况
exists会判断数据是否满足条件，如果满足不管内层查询返回什么结果
只判断返回true还是false来决定是否显示外表的这条数据
如果一个查询直接查询两个表，称为笛卡尔集,乘集
select * from emp,dept
--合并查询
把多个表的查询结果集合并也称为(union)并集,会去掉重复数据，会排序
select ssex,sname from t_student
union
select tsex,tname from t_teacher
union all 相比union，all不会去重，不会排序
查询工资大于3000的员工或者30号部门的员工信息
select * from emp where sal>3000
union all
select * from emp where deptno =30
minus差集,把多条查询的交集从第一条查询中剔除
select * from emp
minus
select * from emp where deptno =30
intersect交集,得到多条查询的完全相同的数据
工资大于3000并且部门为30号
select * from emp where sal>3000
intersect
select * from emp where deptno =30
--内连接 inner join
select * from emp inner join dept on emp.deptno=dept.deptno
--左外left join
以左表为主表，连接表为辅，先查询主表的所有数据，然后再以等值
的方式查询辅表的数据
查询所有部门及其员工信息，包括那些没有员工的部门。
select *
from dept left join emp on dept.deptno = emp.deptno
另一种语法,(+)号加在条件的哪一端那一段就为辅
select * from emp e,dept d where e.deptno(+) = d.deptno(+)
--右外right join 左外反过来
--查询所有员工及其部门信息，包括那些还不属于任何部门的员工。
select *
from dept right join emp on dept.deptno = emp.deptno
--全外full join 先以内联的方式查询多表的数据，然后逐一显示哪些
不匹配的数据
查询所有的员工以及所在的部门信息，包括那些没有部门的员工和
没有员工的部门
select *
from dept full join emp on dept.deptno = emp.deptno
设计范式
1.字段原子性
省 市 区 详细地址
2.唯一字段
订单表
订单号 时间 金额 用户编号
用户表
编号 名字 地址 电话 性别
3.外键
约束
默认值约束 default 插入数据的时候如果添加了该约束的列没有插入
值就会插入默认值
检查约束 限制设置了检查约束的列只能插入某一种或者多种值
create table t_user(
userid number,
sex varchar2(50) check (sex in('男','女'))
)
唯一约束
主键约束
一个表中只能有一个主键，由一列 或者多列组合（联合主键）
主键确保唯一，取值不能为空。主键会占据物理内存，所以访问效率
更高
又分为匿名主键和命名主键
drop table t_yonghu;
create table t_yonghu(
id number,--追加primary key 匿名的方式
username varchar2(50)
)
--可以查看约束名称
select * from user_cons_columns
--添加命名主键
alter table t_yonghu add constraint pk_yonghu_id primary key
(id)
alter table 表名 add constraint 主键名 primary key (做为主键的列)
--删除约束
alter table 表名 drop constraint 约束名
alter table t_yonghu drop constraint pk_yonghu_id
外键约束
外键是另一张表的主键，用来让多表之间产生关联关系
外键取值以另一张表的主键的值参考,或者为null
--添加外键约束
alter table 表名 add constraint 外键名 foreign key(本表的列名)
references 另一张表名(另一张表的列)
alter table dept add constraint pk_dept_deptno primary key(deptno)
alter table emp add constraint fk_emp_deptno foreign key(deptno)
references dept(deptno)
--添加了外键后，主键的那个表不能随意删除记录
delete from dept where deptno = 30
--学生表tblStudent（编号StuId、姓名StuName、年龄StuAge、性别StuSex）
--课程表tblCourse（课程编号CourseId、课程名称CourseName、教师编号TeaId）
--成绩表tblScore（学生编号StuId、课程编号CourseId、成绩Score）
--教师表tblTeacher（教师编号TeaId、姓名TeaName）
SELECT * FROM tblStudent
SELECT * FROM tblCourse
SELECT * FROM tblScore
SELECT * FROM tblTeacher
--1、查询“001”课程比“002”课程成绩高的所有学生的学号；
select s1.* from
(select * from tblscore where courseid = '001')s1,
(select * from tblscore where courseid = '002')s2
where s1.stuid = s2.stuid and s1.score>s2.score
--2、查询平均成绩大于60分的同学的学号和平均成绩；
select stuid,avg(score)
from tblscore group by stuid having avg(score)>60
--3、查询所有同学的学号、姓名、选课数、总成绩；
select s2.stuname,s1.* from
(select stuid,count(*) count, sum(score) sum
from tblscore s group by stuid) s1,
tblstudent s2 where s1.stuid(+) = s2.stuid
--4、查询姓“李”的老师的个数；
select count(*)
from tblteacher where teaname like '李%'
--5、查询没学过“叶平”老师课的同学的学号、姓名；
select * from tblstudent where stuid not in(
select stuid from tblscore where courseid in(
select courseid from tblcourse where teaid =(
select teaid from tblteacher where teaname ='叶平')))
---差集
select stuid from tblstudent
minus
select sc.stuid from
tblscore sc,tblcourse c,tblteacher t
where t.teaname ='叶平' and c.teaid = t.teaid
and sc.courseid = c.courseid
--6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select s1.* from
(select * from tblscore where courseid = '001')s1,
(select * from tblscore where courseid = '002')s2
where s1.stuid = s2.stuid
--7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
--8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
--9、查询所有课程成绩小于60分的同学的学号、姓名；
select stuid
from tblscore group by stuid having max(score)<60
--11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
select * from tblscore where courseid in(
select courseid from tblscore where stuid = '1001')
--12、查询至少学过学号为“1001”同学所有课程的其他同学学号和姓名；
select stuid,count(*) count from tblscore where courseid in
(select courseid from tblscore where stuid = '1001')
group by stuid having count(*) =
(select count(*) from tblscore where stuid = '1001')
--13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；
update 表名 set 列=新的值 where
update tblscore sc set score=
--把id代入到内部子查询返回这门课程的平均分
(select avg(score) from tblscore where courseid = sc.courseid)
where courseid in
(
select c.courseid from tblteacher t,tblcourse c where t.teaname='叶平'
and t.teaid = c.teaid
)
--14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；
select s2.* from
(select stuid,count(*) count from tblscore where courseid in
(select courseid from tblscore where stuid = '1002')
group by stuid having count(*) =
(select count(*) from tblscore where stuid = '1002')) s,
(select stuid,count(*) count from tblscore group by stuid ) s2
where s.stuid = s2.stuid and s.count = s2.count
--15、删除学习“叶平”老师课的SC表记录；
delete from tblscore where courseid in
(
select c.courseid from tblteacher t,tblcourse c where t.teaname='叶平'
and t.teaid = c.teaid
)
--16、向SC表中插入一些记录，这些记录要求符合以下条件：
没有上过编号“003”课程的同学学号、'002'号课的平均成绩；
insert into tblscore
select s1.*,'003',s2.* from
(select stuid from tblstudent
minus
select stuid from tblscore sc where courseid ='003') s1,
(select avg(score) from tblscore where courseid='002') s2
--17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、
“英语”三门的课程成绩，按如下形式显示：
学生ID,数据库,企业管理,英语,有效课程数,有效平均分
--18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select courseid,max(score),min(score)
from tblscore group by courseid
--19、按各科平均成绩从低到高和及格率的百分数从高到低顺序 (百分数后如何格式化为两位小数??)
select
courseid ,
avg(score) avg,
(select count(*) from tblscore where courseid = sc.courseid
and score>=60)/count(*)*100||'%' jgl
from tblscore sc group by courseid
order by avg ,jgl desc
--20、查询如下课程平均成绩和及格率的百分数(用"1行"显示):
企业管理（001），马克思
select * from
(select
(select coursename from tblcourse where courseid = sc.courseid) 课程名
,avg(score),
(select count(*) from tblscore where courseid = sc.courseid
and score>=60)/count(*)*100||'%' jgl
from tblcourse c,tblscore sc where coursename ='企业管理'
and sc.courseid = c.courseid
group by sc.courseid) s1,
(select
(select coursename from tblcourse where courseid = sc.courseid) 课程名
,avg(score),
(select count(*) from tblscore where courseid = sc.courseid
and score>=60)/count(*)*100||'%' jgl
from tblcourse c,tblscore sc where coursename ='马克思'
and sc.courseid = c.courseid
group by sc.courseid) s2
--21、查询不同老师所教不同课程平均分从高到低显示
select teaname,coursename,avg(score) pjf from
(
select t.teaname,c.coursename,sc.score
from tblcourse c,tblteacher t,tblscore sc
where c.teaid = t.teaid and sc.courseid = c.courseid
) s group by teaname,coursename order by pjf desc
--22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：
企业管理（001），马克思（002），UML （003），数据库（004）
格式：[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩
select
stuid 学生ID,
(select stuname from tblstudent where stuid = s.stuid) 学生姓名,
sum(decode(courseid,'001',score,0)) 企业管理,
sum(decode(courseid,'002',score,0)) 马克思,
sum(decode(courseid,'003',score,0)) UML,
sum(decode(courseid,'004',score,0)) 数据库,
avg(score) 平均成绩
from
(
select stuid,courseid,score,
row_number()over(partition by courseid order by score desc ) rn
from tblscore where courseid in('001','002','003','004')
) s where rn between 3 and 6 group by stuid
--23、统计列印各科成绩,各分数段人数:
课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
select
courseid 课程ID,
(select coursename from tblcourse where courseid=s.courseid) 课程名字,
sum(decode(scopes,'85-100',count,0)) "[100-85]"
from
(
select courseid,count(*) count,'85-100' scopes
from tblscore where score between 85 and 100
group by courseid
union all
select courseid,count(*) count,'70-85' scopes
from tblscore where score between 70 and 84
group by courseid
union all
select courseid,count(*) count,'60-70' scopes
from tblscore where score between 60 and 69
group by courseid
union all
select courseid,count(*) count,'<60' scopes
from tblscore where score <60
group by courseid)
s group by courseid
--24、查询学生平均成绩及其名次
select s.*,rownum rn from(
select stuid,avg(score) avg from tblscore
group by stuid order by
avg desc) s
--25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
-- 加了一个函数
select* from(
select courseid,
score,
row_number()over(partition by courseid order by score desc) rn
from tblscore) where rn between 1 and 3
--26、查询每门课程被选修的学生数
select courseid,count(*) from tblscore group by courseid
--27、查询出只选修了一门课程的全部学生的学号和姓名
select stuid,
(select stuname from tblstudent where stuid = sc.stuid)
from tblscore sc group by stuid
having count(*)=1
--28、查询男生、女生人数
select stusex,count(*)
from tblstudent group by stusex
--30、查询同名同性学生名单，并统计同名人数
select stuname,stusex,
(select count(*) from tblstudent where stuname=s.stuname)
from tblstudent s group by stuname,stusex having count(*)>1
--31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)
select* from tblstudent
where to_char(sysdate,'yyyy')-1981=stuage
--32、查询每门课程的平均成绩，结果按平均成绩升序排列，
平均成绩相同时，按课程号降序排列
select courseid,avg(score) avg
from tblscore group by courseid
order by avg,courseid desc
--33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select stuid,avg(score)
from tblscore group by stuid having avg(score)>85
--34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数
--35、查询所有学生的选课情况；
--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
--37、查询不及格的课程，并按课程号从大到小排列
--38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
--39、求选了课程的学生人数
--40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩
--41、查询各个课程及相应的选修人数
--42、查询不同课程成绩相同的学生的学号、课程号、学生成绩
--43、查询每门功成绩最好的前两名
--44、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
--45、检索至少选修两门课程的学生学号
--有重复课程时用此方法(如补考)
--46、查询全部学生都选修的课程的课程号和课程名
--47、查询没学过“叶平”老师讲授的任一门课程的学生
--48、查询两门以上不及格课程的同学的学号及其平均成绩
--49、检索“004”课程分数小于60，按分数降序排列的同学学号 (ok)
--50、删除“002”同学的“001”课程的成绩
--序列
oracle用于产生一个唯一数字的对象
通常用于产生的值做为主键的值递增
序列的创建做为数据库的对象，并不依赖任何一张表，只不
过是我们需要把他作用在某张表上
---创建序列的语法
create sequence 表名_SEQ
increment by 步长
start with 起始值
maxvalue 最大值
minvalue 最小值
cache 缓存空间
cycle|| nocycle 是否循环
--创建序列
create sequence T_USER_SEQ
increment by 1 --每次自增1
start with 0--初始值
maxvalue 9999
minvalue 0
cache 5
cycle
--使用序列
序列.nextval 让序列自增一次并且得到当前的值
序列.currval 不会让序列自增，得到当前的序列值
select t_user_seq.nextval from dual
select t_user_seq.currval from dual
---
insert into t_user values(t_user_seq.nextval,'男');
commit;