1.oracle数据库启动的服务(OracleOraDb10g_home1TNSListener,OracleServiceORCL)
2.用客户端sqldeveloper.exe连接指定数据库的方式
cmd中输入sqlplus进行本机数据库的方式
3.数据库中表和字段的概念
4. sql语句的分类
a) DDL，数据定义语言 create table drop table等
b) *DML，数据操纵语言 insert update delete
c) *DQL，数据查询语言 select
d) DCL，数据控制语言 dba(数据库管理员)
5.ddl语句：建或者 删除表,索引等数据库对象
create table tablename (column1 type,column2 type,column3 type....)
drop table tablename;
6.DML，数据操纵语言 增删改(insert ,delete ,update)
insert into tablename(column1,column2....) values()
delete from tablename where 条件
update tablename set column1=value1,column2=value2 where 条件
DML语句要执行commit的操作才能生效
7.DQL 数据查询语句
select 要查询的字段列表 from tablename
要查询的字段列表：* 代表查询该表中所有字段或者是要查询的字段的列表格式为(column1,column2,column3....)
1).查询数据库当前的时间 oracle的是sysdate
oracle中的虚表dual:查询与具体表没有关系的字段 比如数据库当前时间 或者是一些表达式时使用
select sysdate from dual;
2).查询出所有员工的编号，姓名，职位，月薪，年薪(不包含奖金)，入职日期 并且给字段起别名
select empno 员工编号, ename 姓名, job 职位, sal 月薪,sal*12 年薪, hiredate from emp;
3).查询出所有员工的编号，姓名，职位，月薪，年薪(包含奖金)，入职日期 并且给字段起别名
含有任何null值的数学表达式最后的结果都为空值
数据中含有空值时往往会增加程序的处理难度,强烈建议在实际当中开发数据库应用系统的时候不要使用空值
4).查询出月薪大于1500的人的编号，姓名，职位，月薪，年薪(不包含奖金)，入职日期
5).查询出奖金为空的人的姓名，职位，月薪，奖金，年薪，入职日期
6).查询出工资大于等于1500小于等于3000的人的姓名，职位，月薪，奖金，年薪(包含奖金)，入职日期(两种语句写出)
7).查询出编号为7369，7844,7902三个人的信息(两种写法)
select * from emp where empno =some (7369,7844,7902);
select * from emp where empno in (7369,7844,7902);
8).查询出编号不为7369，7844,7902人的信息(两种写法)
select * from emp where empno <> all (7369,7844,7902);
select * from emp where empno not in (7369,7844,7902);
9).查询出名字中包含A的人的信息
select * FROM emp where ename like '%A%';
10).查询出名字中倒数第三个字符为A的人的信息.
select * FROM emp where ename like '%A__';
11).查询出薪水大于等于2000小于等于3000并且职位不等于ANALYST的人的编号，名字，职位，月薪，奖金，年薪
select empno, ename, job, sal, comm, sal*12 from emp
WHERE sal BETWEEN 2000 and 3000
and job<> 'ANALYST';
12).查询出入职日期28-9月 -81之后入职并且奖金不为空的人的编号，名字，职位，月薪，奖金，年薪
select empno, ename, job, sal, comm, sal*12 from emp
WHERE hiredate > '28-9月 -81'
and comm is not null;
13).按照员工月薪由高到低的顺序进行排列后的员工的信息
select * from emp
order by sal DESC;
14).查询出薪水大于1500的人的编号，姓名，薪水，年薪 并且按照薪水的降序进行显示
select empno,ename, sal, sal*12 from emp
where sal > 1500
order by sal DESC;
15).查询出薪水在1000到4000之间的人的信息 并且按照入职时间的先后进行显示
select * from emp
where sal BETWEEN 1000 and 4000
order by hiredate;
16).查询出薪水在1000到4000并且名字中包含A 并且奖金为空的人的信息 并且按照薪水的降序进行排列
select * from emp
where sal BETWEEN 1000 and 4000
and ename like '%A%'
and comm is null
order by sal desc;
17).求出名字中包含a(不区分大小写)的人的信息
注意：基本函数(lower,upper,substr,to_char,to_date等)可以用在select和from之间的查询字段的列表处 也可以用在where条件语句中
select * from emp
where lower(ename) like '%a%';
18).查询出薪水在1000到4000并且名字中包含A 并且奖金为空的人的信息 并且按照薪水的降序进行排列
19).查询出名字前两个字符为al的人的信息(两种写法实现)
select * from emp where lower(ename) like 'al%';
select * from emp where substr(lower(ename), 1, 2) = 'al';
19-5).查询出名字中后两位字符为er人的信息(两种写法实现)
select * from emp where lower(ename) like '%er';
select * from emp where lower(substr(ename, length(ename)-1,2)) = 'er';
20).查询出薪水大于1200并且入职日期在1981-04-02之后的人的编号，名字，月薪，年薪(不包含奖金),入职日期,格式化中国人能接受的日期形式
select empno,ename, sal, sal*12, to_char(hiredate, 'YYYY-MM-DD') riqi from emp
where sal>1200
and hiredate > to_date('1982-06-01', 'YYYY-MM-DD');
21).求出名字中包含a并且入职日期在1982-06-01之后入职的人编号，名字，月薪，年薪(不包含奖金),入职日期,格式化中国人能接受的日期形式
并且按照月薪的由高到低的顺序进行显示(两种形式 to_char 和to_date)
select empno,ename, sal, sal*12, to_char(hiredate, 'YYYY-MM-DD') riqi from emp
where lower(ename) like '%a%'
and hiredate > to_date('1982-06-01', 'YYYY-MM-DD')
order by sal DESC;
select empno,ename, sal, sal*12, to_char(hiredate, 'YYYY-MM-DD') riqi from emp
where lower(ename) like '%a%'
and to_char(hiredate, 'YYYY-MM-DD') > '1982-06-01'
order by sal DESC;
22).查询出月薪在公司的平均工资之上人的信息
组函数(avg,count,max,min,sum)只可以用在查询的列表 不可以用在where的条件语句中
如果在查询列表出现组函数 那么通常情况下不能单独出现其他字段 除非其他字段也放到相应的组函数中才可
23).求出部门编号为10的所有员工的平均工资
select deptno ,avg(sal) from emp
group by deptno
having deptno = 10;
24).求出公司每年总的支出，以及平均薪水，以及总人数
select SUM(sal+nvl(comm,0)) ,avg(sal), count(empno) from emp;
25).求出公司中每个部门的平均薪水
在使用group by 时，有一个规则需要遵守，即出现在select列表中的字段，如果没有在组函数中，那么必须出现在group by 子句中。
select avg(sal), deptno from emp
group by deptno;
26).求出每个部门的部门编号，每年总的支出，以及该部门的平均薪水，以及该部门的人数
select deptno,avg(sal), SUM((sal+nvl(comm,0))*12),count(empno) from emp
group by deptno;
27).查询出部门平均薪水大于1600的部门的平均薪水，人数以及该部门一年总的支出
select deptno,avg(sal), SUM((sal+nvl(comm,0))*12),count(empno) from emp
group by deptno
having AVG(sal)>1600;
28).求出每个部门的部门编号，每年总的支出，以及该部门的平均薪水，以及该部门的人数 并且按照平均薪水由高到低的顺序进行排列
select deptno,avg(sal) avg_sal, SUM((sal+nvl(comm,0))*12),count(empno) from emp
group by deptno
ORDER by avg_sal DESC;
小结：sql语句select的语法
select column1,column2,..... from tablename where 过滤条件(and or) group by column having 分组的限制条件
order by column(最后进行排序)
注意：where过滤条件中只允许使用普通函数 不可以使用组函数 但是having分组限制条件中可以使用组函数
29)*.查询出工资大于1200，并且入职日期在1981-09-09以后的部门里面的人的平均薪水大于2000的部门的平均工资及部门编号 并且将其结果按照平均工资进行降序的排列
30).查询出公司所有人里面工资在部门编号为30最高工资之上的人信息
select * from emp
WHERE sal>all(SELECT sal from emp where deptno=30);
31).查询出工资大于公司的平均工资并且入职日期大于1981-02-16，并且名字中包含a的人的编号，名称，月薪，年薪 并且按照年薪进行降序排列em
SELECT empno, ename, sal, (sal+nvl(comm,0))*12 year_sum from emp
where hiredate>to_date('1981-02-16','YYYY-MM-DD')
and sal>(SELECT avg(sal) from emp)
and lower(ename) like '%a%'
ORDER by year_sum DESC;
32).公司里面薪水最高的人的编号，名称，工作，入职日期，年薪
SELECT empno, ename, job, hiredate,(sal+nvl(comm,0))*12 year_sum from emp
where sal= (SELECT max(sal) from emp);
33)*.求平均薪水大于1600的部门里面工资大于1200的人所在的部门平均薪水，并且按照平均薪水的降序进行排序
34-1).查比部门号为10中最低的工资高的人的信息(不用组函数)。
SELECT ename,sal FROM emp WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 10);用some也可以
34-2).找到和30号部门员工的任何一个人工资相同的那些员工
SELECT ename,sal FROM emp WHERE sal = SOME(SELECT sal FROM emp WHERE deptno = 30) AND deptno<>30;
34-3).找到比部门号为20的员工的所有员工的工资都要高的员工，也就是比部门编号为20的那个工资最高的员工的还要高的员工(不用组函数)
SELECT ename,sal FROM emp WHERE sal > ALL(select sal from emp where deptno = 20);
35).求出每个人的编号，名称，工资，入职日期，部门编号，部门名称及部门所在地
select e.deptno, e.ename, e.hiredate, e.deptno, d.dname,d.loc
from emp e, dept d
where e.deptno= d.deptno;
36).查询工资大于公司平均工资，且名字中不包含a的，并且入职日期大于1982-07-07的员工的编号，名称，月薪，年薪，部门编号，部门名称，部门所在地 并且按照薪水降序进行排列
select e.empno, e.ename, e.sal,(sal+nvl(comm,0))*12 year_sum, e.deptno, d.dname,d.loc
from emp e, dept d
where e.deptno= d.deptno
and sal>(SELECT avg(sal) from emp)
and lower(ename) not like '%a%'
and e.hiredate>to_date('1982-07-07','YYYY-MM-DD')
ORDER by sal DESC;
37).求每个人的工资属于哪个级别
select e.sal, s.grade,s.losal, s.hisal
from emp e, salgrade s
where e.sal BETWEEN s.losal AND s.hisal
38).求出工资在公司的平均工资之上，并且入职日期在1981-06-04之后，并且名字中包含a的人的编号，姓名，工资，等级
select e.empno, e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal>(SELECT AVG(sal) from emp)
and e.hiredate>to_date('1981-06-04','YYYY-MM-DD')
and lower(e.ename) like '%a%'
and e.sal BETWEEN s.losal AND s.hisal
39).查出名字里面第二个字母不是A的人的信息以及所在的部门情况以及工资的等级情况
select e.empno, e.ename, e.sal,d.deptno, d.dname, d.loc, s.grade
from emp e, dept d,salgrade s
where substr(lower(e.ename) , 2, 2) <>'a'
and e.deptno= d.deptno
and e.sal BETWEEN s.losal AND s.hisal;
select empno,ename, sal,e.deptno,dname,loc,grade
from emp e inner join dept d
on d.deptno= e.deptno
inner join salgrade s
on sal BETWEEN s.losal and s.hisal
where ename not like '_A%';
40).每个人和对应的经理人的信息
select e1.empno,e1.ename,e1.sal,e1.mgr,e2.empno,e2.ename,e2.sal
from emp e1 LEFT join emp e2
on e1.mgr=e2.empno
41).求出每个部门以及对应的部门的员工信息
select empno,ename,sal,d.deptno,dname,loc
from emp e right join dept d
on e.deptno = d.deptno
42).求出每个部门平均薪水等级
select DISTINCT grade,avg_sal,a.deptno from
(select deptno,avg(sal) avg_sal from emp
group BY deptno) a, salgrade s
where avg_sal BETWEEN s.losal and s.hisal;
43)*******.求平均薪水最高的部门编号
---最高的平均薪水---求出每个部门的平均薪水
--1求出每个部门的平均薪水
select avg(sal),deptno from emp
group by deptno;
--2最高的平均薪水
select max(avg_sal) from (
select avg(sal) avg_sal,deptno from emp
group by deptno);
--3求平均薪水最高的部门编号
SELECT deptno from (
select avg(sal) avg_sal,deptno from emp
group by deptno)
where avg_sal = (select max(avg_sal) from (
select avg(sal) avg_sal,deptno from emp
group by deptno
));
44).求平均薪水最低的部门名称
--1每个部门对应的平均薪水
select avg(sal) avg_sal, deptno from emp
group by deptno;
--2平均薪水最低值
select min(avg_sal) from(
select avg(sal) avg_sal, deptno from emp
group by deptno);
--3求平均薪水最低的部门编号
select deptno from(
select avg(sal) avg_sal, deptno from emp
group by deptno)
where avg_sal=(
select min(avg_sal) min_avg from(
select avg(sal) avg_sal, deptno from emp
group by deptno))；
--4求平均薪水最低的部门名称
select dname from dept
where deptno=(
select deptno from(
select avg(sal) avg_sal, deptno from emp
group by deptno)
where avg_sal=(
select min(avg_sal) min_avg from(
select avg(sal) avg_sal, deptno from emp
group by deptno)));
45).求平均薪水等级最低的部门部门名称
------------------
--1每个部门对应的平均薪水
select avg(sal) avg_sal, deptno from emp
group by deptno
--2每个部门对应的平均薪水等级
select grade,avg_sal,deptno from(
select avg(sal) avg_sal, deptno from emp
group by deptno), salgrade s
where avg_sal BETWEEN s.losal and s.hisal;
--3平均薪水等级最低值
SELECT min(grade) from(
select grade,avg_sal,deptno from(
select avg(sal) avg_sal, deptno from emp
group by deptno), salgrade s
where avg_sal BETWEEN s.losal and s.hisal);
--4求出平均薪水最低的部门的编号
select deptno from (
select grade,avg_sal,deptno from(
select avg(sal) avg_sal, deptno from emp
group by deptno), salgrade s
where avg_sal BETWEEN s.losal and s.hisal)
where grade = (SELECT min(grade) from(
select grade,avg_sal,deptno from(
select avg(sal) avg_sal, deptno from emp
group by deptno), salgrade s
where avg_sal BETWEEN s.losal and s.hisal));
--5求平均薪水最低的部门名称
select dname from dept
where deptno =
(select DISTINCT deptno from
(select grade,avg_sal,deptno from (
select avg(sal) avg_sal, deptno from emp
group by deptno
) a, salgrade s
where avg_sal BETWEEN s.losal and s.hisal)
where grade = (select min(grade) from
(select grade,avg_sal,deptno from (
select avg(sal) avg_sal, deptno from emp
group by deptno
) a, salgrade s
where avg_sal BETWEEN s.losal and s.hisal)));
46).薪水最高的前5个人
select * from(
select rownum r,a.* from(
select empno,ename,sal from emp
ORDER by sal) a)
where r BETWEEN 1 and 5;
47).按薪水从高到低排列的第6个到第10个人的信息
select * from(
select rownum r,a.* from(
select empno,ename,sal from emp
ORDER by sal) a)
where r BETWEEN 6 and 10;
48).查询出名字中包含a的，并且薪水大于1200，并且入职日期大于1979-01-03的人里面薪水由高到低顺序排名的3到7人的信息
select * from(
select rownum r,a.* from(
select empno,ename,sal from emp
where lower(ename) like '%a%'
and sal > 1200
and hiredate > to_date('1979-01-03','YYYY-MM-DD')
ORDER by sal) a)
where r BETWEEN 3 and 7;
49).查询出名字中包含a的，并且薪水大于1200，并且入职日期大于1979-01-03的人里面薪水由高到低顺序排名的3到7人的编号，姓名，职位，月薪，年薪，部门名称，所在地以及工资的等级情况
select empno,ename,job,sal,year_sal,dname,loc,grade from(
select * from(
select rownum r,a.* from(
select empno,ename,job,sal,deptno,(sal+nvl(comm,0))*12 year_sal from emp
where lower(ename) like '%a%'
and sal > 1200
and hiredate > to_date('1979-01-03','YYYY-MM-DD')
ORDER by sal) a)
where r BETWEEN 3 and 7) b, dept d, salgrade s
where d.deptno=b.deptno
and b.sal BETWEEN s.losal and s.hisal;
数据库的分页：
select * from (select rownum r,a.* from (?) a) where r between ? and ?
第一个问号：指的是要查询的sql语句(包含排序)
第二个问号：指的是分页查询的起始行号
第三个问号：指的是分页查询的结束行号
oracle分页利用的rownum伪字段
mysql的分页利用的limit关键字
sqlserver的分页利用的top
视图：
以数据库管理员身份(dba)登录数据库
在cmd中输入sqlplus sys/密码 as sysdba
创建视图 需要赋权给scott用户grant create table,create view to scott;
create view v$_dept_avg_sal_info as select deptno,grade,avg_sal from (select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on
(t.avg_sal between s.losal and s.hisal);
使用视图的目的：
1。降低操作复杂度；视图是预编译的查询操作，一次定义，之后可快速调用；
2。提高系统安全。视图作为数据库对象，可以将其权限独立出来赋给用户，可避免用户对基表的盲目危险操作，同时也可屏蔽一部分私密的属性列。
1.查询出每门课都大于80分的学生姓名
select name from
(select min(score) min_score ,s.* from score s
group by name)
where min_score>80;
select name from score
group by name
having min(score) >80;
select DISTINCT name from score
where name not in (select name from score
where score<80);
2.查询出username和passwd重复的数据
select * from student
where (username,passwd) in
(select username,passwd from student
group by username, passwd
having count(*)>1);
3.删除掉student表中重复的记录
思路：(1)先查询出表中重复的记录
(2)将查询的重复记录的条件作为删除数据的条件之一(删除的就是这些重复的记录 但是要保留一个)
(3)如何去区分出这些重复的记录中保留的唯一的一条记录(利用rowid[取出rowid的最小值或者最大值])
(4)删除的另外一个条件是取出的rowid最小值的那条重复的记录不能删除
delete from student
where (username,passwd) in(
select username,passwd from student
group by username,passwd
having count(*)>1)
and rowid not in(
select min(rowid) from student
group by username,passwd
having count(*)>1)