数据库知识

ORACLE SQL
SQL(structured query language)


data 持久化(保存)


文件
数据库(database)
table
SQL操作table
DDL(data definition language 数据定义语言)
create table 创建表:colmn,structure
colmn(列):colmn name(列名),data type(数据类型),length(长度),constraint(约束)


alter table修改表结构
修改列(add colmn增加列,modify colmn修改列,drop colmn删除列)
drop table 从数据库中删除表




DML(data manipulation language 数据操作语言):row,data
row(行)
insert into tabame 插入一条记录
update tabname 修改一条记录的某列的值
delete from tabname 删除一条记录


TCL(transaction 事务 control language 事务控制语言):ATM(交易)
commit(提交):让dml操作产生的数据入库
rollback(回滚):让dml操作失效


DQL(data query language数据查询语言)
select


DCL(data control langauge 数据控制语言)
usera,userb
usera
grant select on tabname to userb;
revoke
revoke select on tabname from userb;


使用频度高的语句
DQL,DML,TCL


oracle oracle10g mysql
IBM db2
MS  SQL server
.....


DBA(database administrator 数据库管理员)
install RDBMS(relationship database management system 关系型(二维表:列,行)数据库管理系统)--> create Database(open)-->create user-->(跟数据库建连接(登录,身份认证)-->SQL(DDL DML...),程序员的操作)


solaris
oracle server oracle数据库服务器
oracle for solaris
sqlplus
192.168.0.26  
192.168.0.20
192.168.0.23


远程登录 telnet
Linux终端: telnet 192.168.0.26(登录到26服务器上)
patch补丁 bug
login:openlab
password:open123
sunv210% echo $ORACLE_SID
tarena
sunv210%
%sqlplus 
SQL>
提示符
sunv210% shell里(cmd),os命令
SQL> :where am i
SQL> show user(Who am I)


JDBC TCP/IP
(ip address port(端口 1521) 数据库服务器名字 用户名 密码)


emp employee(员工)
dept department(部门)


create table dept_hiloo
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


DQL select
emp_jsd1301


newtab不存在
create table newtab(自己起的)
as
select * from oldtab(已经存在的);


newtab表存在,没有数据
insert into newtab
select * from oldtab;


emp,dept select
SQL 4GL 描述语言
select语句
源表 结果集
结果集是表里的部分列:投影操作
结果集是表里的部分行:选择操作
结果集来在多张表的记录的组合:连接操作 


单表操作
多表操作


select语句基本语法:
select colname1,... from tabname;
投影操作用select子句实现


sql语句经sqlplus送给server process(连接建好后标志创建server process进程),对语句进行语法分析,语义分析,产生执行计划并执行,生成结果集, 把结果集传回sqlplus.


select子句
后面跟列名,函数,表达式(数值,字符),运算功能,投影操作
对记录(可能是单列,可能是多列)去重


null的概念
null值,insert into (null)
bonus列不存占空间,未知的
null被看成无穷大
1,(5000+null)*12 = null表达式中有null值,结果为null
空值转换函数
nvl(p1,p2) p1,p2参数
if p1 is null then 
   return p2;
else
   return p1;
end if;


2 如果有多个null,经distinct处理后,保留一个.




字符串的拼接 ||


dual 单列单行用于常量的调用


where子句
where 条件表达式
列名 比较运算符 值
后面有多个条件,用and,or连接


课堂练习
1列出每个员工的名字和他的工资?
select ename,salary from emp_hiloo;
2列出部门表的所有信息?
select * from dept_hiloo;
*表示所有列
3列出每个员工的名字和他的年薪?
select ename,salary*12 
from emp_hiloo;
4列出每个员工的名字和他一年的总收入
select ename,(salary+bonus)*12 ann_sal
from emp_hiloo
ann_sal 列别名


select ename,(salary+bonus)*12 "Ann Sal"
from emp_hiloo
如果列别名中包含空格或希望大小写敏感,用双引号括起来.
(5000+null)*12 = null
(15000+null)*12 = null
(4000+null)*12 = null
select ename,
(salary+nvl(bonus,0))*12 ann_sal
from emp_hiloo;


5
zhangwuji's job is Manager.
liucangsong          Analyst
liyi                 Analyst
guofurong            Programmer
zhangsanfeng         President
yanxiaoliu           Manager
luwushuang           clerk
huangrong            Manager
weixiaobao           salesman
guojing              salesman


' 二义性 表示字符串的定界符,单引号本身
''''表示单引号本身.


select ename||'''s job is '||job
            || '.'  emp
from emp_hiloo


6公司里有哪些职位?
distinct:对记录做去重的,结果集的记录是唯一的.
select distinct job from emp_hiloo


7公司里有哪几类奖金?
select distinct bonus from emp_hiloo;


8各个部门有哪些不同的职位?
select distinct deptno,job 
from emp_hiloo
distinct后面跟多列,多列联合去重.


9哪些员工的工资比5000高?
select ename,salary
from emp_hiloo
where salary > 5000


10哪些员工的工资比5000高,列出名字,年薪
select ename,salary*12 ann_sal
from emp_hiloo
where salary > 5000


11哪些员工的年薪大于60000?列出名字,年薪
select ename,salary*12 ann_sal
from emp_hiloo
where salary*12 > 60000;


select ename,salary*12 
from emp_hiloo
where salary > 5000


select ename,salary*12 ann_sal
from emp_hiloo
where ann_sal > 60000;


ORA-00904: "ANN_SAL": invalid identifier
无效的标识符(没有这个列名)


select语句中各个子句的执行顺序
from --> where --> select


12 zhangwuji的年薪是多少?
select ename,salary*12 ann_sal
from emp_hiloo
where ename = 'zhangwuji'
怎么存怎么写,数据有规则
忽略大小写,upper(p1),返回值大写,lower(p1)小写,initcap(p1)首字符大写


select ename,salary*12 ann_sal
from emp_hiloo
where ename = 'Zhangwuji'
区分大小写,它的编码(ASCII)的值不一样
no rows selected




课外练习
1不知道ename存储的大小写,找出zhangwuji的年薪?
2哪些员工的工资在[5000,10000]之间.
3 哪些员工的奖金是400,500,1000
4 哪些员工的工资不在[5000,10000]之间.
5 哪些员工的奖金不是400,500,1000.

第一天课外练习
1不知道ename存储的大小写,找出zhangwuji的年薪?
select ename,salary*12 ann_sal
from emp_hiloo
where upper(ename) = 'ZHANGWUJI'


zhangwuji-> upper('zhangwuji')->'ZHANGWUJI' = 'ZHANGWUJI'


用函数是不得已而为之
函数目的规范化数据存储,简化select语句的写法.


2哪些员工的工资在[5000,10000]之间.
select ename,salary
from emp_hiloo
where salary >= 5000
and salary <= 10000


3 哪些员工的奖金是400,500,1000
select ename,bonus
from emp_hiloo
where bonus = 400
or    bonus = 500
or    bonus = 1000


4 哪些员工的工资不在[5000,10000]之间.
select ename,salary
from emp_hiloo
where salary < 5000
or salary > 10000


select ename,salary
from emp_hiloo
where salary not between 5000 and 10000


5 哪些员工的奖金不是400,500,1000.
select ename,bonus
from emp_hiloo
where nvl(bonus,0) <> 400
and nvl(bonus,0) <> 500
and nvl(bonus,0) <> 1000


select ename,bonus
from emp_hiloo
where nvl(bonus,0) not in (400,500,1000)


哪些员工的奖金不是400,500,1000,也不能为null?
select ename,bonus
from emp_hiloo
where bonus not in (400,500,1000)


select ename,bonus
from emp_hiloo
where bonus not in (400,500,1000,null)


where子句后面可否跟列别名? 不能


哪些人的奖金是400,500,1000和没有奖金.
select ename, bonus
from emp_hiloo
where bonus in (400,500,1000,null);(错)
select ename, bonus
from emp_hiloo
where bonus in (400,500,1000)
select ename, bonus
from emp_hiloo
where nvl(bonus,0) in (400,500,1000,0)
select ename, bonus
from emp_hiloo
where bonus in (400,500,1000)
or bonus is null


第二天内容
SQL比较运算符
1 >= and <= between ... and,范围,闭区间
where salary between 5000 and 10000


2= or = or in  =any  集合 离散
where bonus in (400,500,1000)
= 单值运算符  in 多值运算符
= -> =any 等于任意一个值


3 like 像...一样 模糊 字符型
通配符  % 表示0过任意多个字符
_ 表示任意一个字符
'S'  like 'S%'1-任意  like 'S_' 条件长度2


DML(记录中包含null值的两种insert方式)
insert into tabname values ('ab',null,...)
insert into tabname(c1,c5) values
('ab',1);


4 判断某列的取值是否为空,用is null,不能用=null


否定形式
= <>
is null is not null
between and not between and
in =any = or = /<> and <> /not in/<>all
用not in比较时,集合中不能包含null值,若包含,no rows selected.
用in比较时,集合中包含null值,是否有null,对结果集没影响.
like not like


函数
nvl upper lower initcap
单行函数:对于每条记录来说,函数都返回一个值
多行函数(组函数):一组记录,函数返回一个值


单行函数
字符函数
upper lower initcap length substr
数值函数
round 四舍五入
        round(12.345,2)  12.35
round(12.5) 13
round(15.5,-1)  20
trunc 截取,取整
trunc(12.545,2)  12
round(12.5) 12
round(15.5,-1)  10


日期函数
        add_months(sysdate,6)
add_months(sysdate,-6)
        months_between(sysdate,hiredate)
        last_day(sysdate)


一般函数
        nvl()
        coalesce
        decode
转换函数
to_date to_char date varchar2
        to_number to_char number varchar2


select ename,empno,nvl(mgr,'Boss')
from emp_hiloo;                    
ERROR at line 1:
ORA-01722: invalid number


select ename,empno,
       nvl(to_char(mgr),'Boss')
from emp_hiloo;  




字符类型:char,varchar2
dname char(20),可以不跟长度,缺省值1char
ename varchar2(20),必须跟长度, 不跟,报错




select length(dname),dname||'D' 
from dept_hiloo;
select length(ename) ,ename||'D'
from emp_hiloo;


char 按定义长度存储,不足补空格,本身定长
varchar2,按实际长度存,本身变长
char比较,空格不敏感
varchar2比较,空格敏感


select ename from emp_hiloo
where ename = 'zhangwuji';
select ename from emp_hiloo
where ename = 'zhangwuji ';
select dname from dept_hiloo
where dname = 'developer';




日期类型
create table test
(c1 date);
insert into test values ('01-JAN-13');
用7个字节存,世纪,年,月,日,时,分,秒
sysdate系统时间
 alter session set nls_date_format = 'yyyy mm dd hh24:mi:ss';
session,会话
建连接 connect,client端(sqlplus jdbc程序)将sql语句送给server process运行,server process创建一个session,是sql语句的运行环境.


'01-JAN-13' 缺省格式:DD-MON-RR 
格式敏感


隐式数据类型转换(系统转 字符转date)
insert into test values ('01-JAN-13');


insert into test values 
(to_date('01-JAN-13','DD-MON-RR'));


insert into test values ('2013-01-01');


显式数据类型转换(用户调)
insert into test values (
to_date('2013-01-01','yyyy-mm-dd'));
ORA-01861: literal(文字) does not match format string(不匹配格式串 DD-MON-RR)


select c1 from test
select to_char(c1,'yyyy-mm-dd')
from test;


日期表达式
date 加减 number = date
date 减 date = number


组函数
avg 平均值 number
sum 求和 number
count 计数 number 字符 date
max 最大值 number 字符 date
min 最小值 number 字符 date
结论:组函数处理所有的非空值
当所有的值都是空值,count返回0,其他函数返回NULL.
所有的null值分为一组
group by 子句
将记录分成若干个组,
group by 列名


select deptno,round(avg(salary))
from emp_hiloo
group by deptno


课堂练习
1哪些员工名字的第二个字符是h?
select ename from emp_hiloo
where ename like '_h%';


2哪些员工的职位是以J_开始?
SQL> insert into emp_hiloo(empno,ename,job)
  2  values (1111,'abc','J_DBA');
SQL> insert into emp_hiloo(empno,ename,job)
  2  values (2222,'def', 'JOB');
select job from emp_hiloo
where job like 'J_%';
rollback;


通配符带着二义性,缺省当通配符解释.
select job from emp_hiloo
where job like 'J\_%' escape '\' 
'\_'表示下划线本身.


3哪些员工没有奖金?
select ename,bonus from emp_hiloo
where bonus is null;
select ename,bonus from emp_hiloo
where bonus = null;


null = null 不成立


4哪些员工有奖金?
select ename,bonus from emp_hiloo
where bonus is not null;
select ename,bonus from emp_hiloo
where bonus <> NULL


400 <> NULL 不成立
NULL <> NULL 不成立
对于NULL来说,任何值做=和<>的比较,都是不成立的.


to_date,to_char
5 emp(empno,ename,hiredate)
2008 08 08 08:08:08


insert into emp_hiloo(empno,ename,hiredate)
values(1111,'abc',
to_date('2008 08 08 08:08:08',
        'yyyy mm dd hh24:mi:ss'))


select to_char(hiredate,
             'yyyy mm dd hh24:mi:ss')
from emp_hiloo
date:to_date to_char 第二个参数都是格式说明


6找出3月份入职的员工?
select ename,hiredate
from emp_hiloo
where to_char(hiredate,'mm') = '03'


select ename,hiredate
from emp_hiloo
where to_char(hiredate,'mm yyyy dd') 
like '03%'


select ename,hiredate
from emp_hiloo
where hiredate like '%MAR%'


select ename,hiredate from emp_hiloo
where to_char(hiredate,'fmMONTH') = 'MARCH'


select ename,hiredate from emp_hiloo
where rtrim(to_char(hiredate,'MONTH')) = 'MARCH'


'MARCH    'varchar2 'MARCH'


select ename,hiredate from emp_hiloo
where to_char(hiredate,'MONTH') like '%MARCH%'




select ename,hiredate
from emp_hiloo
where to_char(hiredate,'fmmm') = '3'
'03'->fm  = '3' 
fm去掉前导0或空格




select ename,hiredate
from emp_hiloo
where to_char(hiredate,'mm') = 3
'03' = 3 隐式数据类型转换
字符   number  字符向数值转
to_number('03') = 3


SQL> select to_number('a9') from dual;
select to_number('a9') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number


7 十分钟之前,十分之之后
select sysdate-1/144,sysdate+1/144
from dual;


8select ename,salary,bonus,
       coalesce(bonus,salary*0.1,100) new_bonus
from emp_hiloo


if p1 is not null then
   return p1;
elsif p2 is not null then
   return p2;
else
     return p3;
end if;


8 case when
  select ename,deptno,
         case when deptno = 10
              then salary*1.1
              when deptno = 20
              then salary*1.2
         else
              salary
         end new_sal
  from emp_hiloo;


  select ename,deptno,
         case when deptno = 10
              then salary*1.1
              when deptno = 20
              then salary*1.2
         end new_sal
  from emp_hiloo;


9 select ename,deptno,
         decode(deptno,10,salary*1.1,
                       20,salary*1.2,
                       salary) new_sal
  from emp_hiloo;


10 有奖金的平均值,和,个数,最大值,最小值
select avg(bonus),sum(bonus),
       count(bonus),
       max(bonus), min(bonus)
from emp_hiloo


count(*):返回记录数


11所有人的奖金的平均值,和,个数,最大值,最小值
select avg(nvl(bonus,0)),
       sum(bonus)/count(*),
       count(bonus),
       max(bonus), min(nvl(bonus,0))
from emp_hiloo


12 select avg(bonus),sum(bonus),
          count(bonus),
          max(bonus),min(bonus)
   from emp_hiloo
   where bonus is null;


13 select count(distinct bonus) 
   from emp_hiloo;
 select count(distinct nvl(bonus,0)) from emp_hiloo;


14各个部门的平均工资?(emp)


15每种奖金有多少人?
select bonus,count(empno)
from emp_hiloo
group by bonus
防止表中有null记录(所有的列都是null),一列必须满足非空且唯一的约束


























课外练习
1substr(p1,p2,p3) 求子串,p1要处理的串,p2是起始位置(1开始,从左往右数),p3子串的长度 
 显示每个员工名字的最后两个字符?


2工资小于5000的涨10%,
      在[5000,10000]涨5%,
其他人不变


3 每种奖金有多少人?(null,'NO Bonus')


(内容已经讲完,现在留课外练习)


4 10部门的平均工资?(只显示平均工资)
  10部门的平均工资?(显示部门号,平均工资)


5 10部门,30部门的平均工资,显示部门号,平均工资


6估算语句的结果集
select max(deptno),avg(salary)
 from emp_hiloo
 where deptno in (10,30);


select avg(deptno),avg(salary)
 from emp_hiloo
 where deptno in (10,30);


select min(deptno),avg(salary)
 from emp_hiloo
 where deptno in (10,30);




第二天课外练习
1substr(p1,p2,p3) 求子串,p1要处理的串,p2是起始位置(1开始,从左往右数,-1从右往左数),p3子串的长度 
 显示每个员工名字的最后两个字符?
select ename,substr(ename,-2,2)
from emp_hiloo


select ename,
       substr(ename,length(ename)-1,2)
from emp_hiloo;


2工资小于5000的涨10%,
      在[5000,10000]涨5%,
其他人不变
select ename,salary,
       case when salary < 5000
            then salary*1.1
            when salary between 5000
                        and 10000
            then salary*1.05
       else 
            salary
       end new_sal
from emp_hiloo;




3 每种奖金有多少人?(null,'NO Bonus')
select nvl(to_char(bonus,'No Bonus')),
       count(empno)
from emp_hiloo
group by bonus


4 10部门的平均工资?(只显示平均工资)
  select round(avg(salary))
  from emp_hiloo
  where deptno = 10


  10部门的平均工资?(显示部门号,平均工资)
  select deptno,round(avg(salary))
  from emp_hiloo
  where deptno = 10
  group by deptno


 
  select deptno,round(avg(salary))
  from emp_hiloo
  where deptno = 10
ORA-00937: not a single-group group function


select max(deptno),round(avg(salary))
from emp_hiloo
where deptno = 10;
max的替代物:min avg


结论:在没有group by子句的情况下,select语句后面如果有一个组函数,其他的都得是组函数




select deptno,ename,round(avg(salary))
  from emp_hiloo
  where deptno = 10
  group by deptno
ORA-00979: not a GROUP BY expression
结论:在有group by子句的情况下,select语句后面可以跟组函数,组标识


5 10部门,30部门的平均工资,显示部门号,平均工资
select deptno,round(avg(salary))
from emp_hiloo
where deptno in (10,30)
group by deptno


6估算语句的结果集
select max(deptno),avg(salary)
 from emp_hiloo
 where deptno in (10,30);


select avg(deptno),avg(salary)
 from emp_hiloo
 where deptno in (10,30);


select min(deptno),avg(salary)
 from emp_hiloo
 where deptno in (10,30);


第三天内容
select语句的语法顺序
select from where group by


select语句的执行顺序
from where group by select(组信息)


平均工资比5000高的那些部门的人数?
having子句
select deptno,count(empno)
from emp_hiloo
group by deptno
having round(avg(salary)) > 5000


select语句的语法顺序
select from where group by having


select语句的执行顺序
from where group by having select(组信息)


where和having比较
共同点:过滤,都执行在select之前,都不可以跟列别名
不同点:
where过滤的是记录,后面可以跟任意列名,单行函数,不能跟组函数,执行在group by之前
having过滤的是组,后面可以跟组函数,组标识,不能跟任意的列名(除组标识),单行函数,执行在group by之后


order by子句
select语句的语法顺序
select from where group by having 
order by


select语句的执行顺序
from where group by having select(组信息)
order by
order by 列名,列别名,函数,表达式,位置
select ename,salary
from emp_hiloo
order by 2;
列位置指salary,在select子句后面的位置


select 2 from dual;
2是常量


排序:升序(缺省 asc NULL在最后) 降序(desc在最前面)


子查询
在SQL语句中嵌入select语句
create table newtab
as
select * from oldtab;


insert into newtab
select * from oldtab;


select语句中嵌入select语句.




课堂练习
1哪种奖金的人数多于2人?
select bonus,count(empno)
from emp_hiloo
group by bonus
having count(empno) > 2


2 各个部门不同职位的平均工资?
select deptno,job,round(avg(salary))
from emp_hiloo
group by deptno,job


2列出员工的名字,部门号,工资,部门号升序,同一部门按工资降序.
select ename,deptno,salary
from emp_hiloo
order by deptno,salary desc


3 列出员工的名字和年薪,按年薪降序排列
select ename,salary*12 ann_sal
from emp_hiloo
order by salary desc


单列子查询
4哪些员工的工资是最低的.
select ename,salary
from emp_hiloo
where salary = (select min(salary)
                from emp_hiloo)
执行顺序
先执行子查询(只执行一遍),若返回多条记录,去重,拿到该结果执行主查询.主查询的比较运算符的选择由子查询返回的记录数决定.(单值运算符,多值运算符)


5哪些员工的工资比zhangwuji的工资高?
select ename,salary
from emp_hiloo
where salary > 
     ( select salary from emp_hiloo
       where ename = 'zhangwuji');
insert into emp_hiloo(empno,ename,salary)
values (1111,'zhangwuji',6000);
RA-01427: single-row subquery returns more than one row


比所有的zhangwuji的工资都高
select ename,salary
from emp_hiloo
where salary >all 
     ( select salary from emp_hiloo
       where ename = 'zhangwuji');


select ename,salary
from emp_hiloo
where salary >
     ( select max(salary) from emp_hiloo
       where ename = 'zhangwuji');


select ename,salary
from emp_hiloo
where salary >any 
     ( select salary from emp_hiloo
       where ename = 'zhangwuji');


select ename,salary
from emp_hiloo
where salary >
     ( select min(salary) from emp_hiloo
       where ename = 'zhangwuji');


6哪些部门的平均工资比30部门的平均工资高?
select deptno,round(avg(salary))
from emp_hiloo
group by deptno
having round(avg(salary)) >
         (select round(avg(salary))
          from emp_hiloo
          where deptno = 30)


7哪些人是领导?
select ename from emp_hiloo
where empno in 
          (select mgr from emp_hiloo)
select ename from emp_hiloo
where empno in (400,500,1000,2000,null)


8哪些人是员工? 哪些人不是领导?
select ename from emp_hiloo
where empno not in 
          (400,500,1000,2000,null)


select ename from emp_hiloo
where empno not in 
          (select mgr from emp_hiloo)


select ename from emp_hiloo
where empno not in 
          (select nvl(mgr,empno) 
           from emp_hiloo)


select ename from emp_hiloo
where empno not in 
          (select mgr from emp_hiloo
           where mgr is not null)




雇主employor    雇员employee
boss zhangsf树根    除了zhangsf(树干,叶)
where mgr is null   where mgr is not null


领导manager 员工 staff
树根 树干 树叶
mgr                     not mgr


9哪些员工的工资等于本部门的平均工资
多列子查询
select ename,deptno,salary
from emp_hiloo
where (deptno,salary) in
       (select deptno,round(avg(salary))
        from emp_hiloo
        group by deptno);




10 找出每个部门工资做高的员工?
select ename,deptno,salary
from emp_hiloo
where (deptno,salary) in
       (select deptno,max(salary))
        from emp_hiloo
        group by deptno);
非关联子查询:子查询不会引用主表的列
单列
多列
-------------------------


11哪些员工的工资大于本部门的平均工资
关联子查询:
在子查询中引用主表的列
select ename,deptno,salary
from emp_hiloo o
where o.salary > 
         (select round(avg(salary))
          from emp_hiloo i
          where o.deptno = i.deptno)


exists 存在
select ename from emp_hiloo o
where exists
            (select 1 from emp_hiloo i
             where o.empno = i.mgr);


update emp set ename = 'abc'
where ename = 'zhangwuji'


not exists 不存在


exists和not exists
如果找到匹配,立即返回,exists(记录放入结果集),not exists(过滤)
如果找不到匹配(跟所有的都不匹配),exists(过滤),not exists (记录放入结果集)
select deptno,dname,location
from dept_hiloo o
where not exists 
          (select 1 from emp_hiloo i
           where i.deptno = o.deptno)


哪些部门有员工?部门名称,部门位置,部门号
select deptno,dname,location
from dept_hiloo o
where exists 
          (select 1 from emp_hiloo i
           where i.deptno = o.deptno)


select deptno,dname,location
from dept_hiloo 
where deptno in (
          select deptno from emp_hiloo)


哪些人是员工?那些人不是领导?
select ename from emp_hiloo o
where not exists
          (select 1 from emp_hiloo i
           where i.mgr = o.empno)


exists和in解决匹配问题,肯定问题


not exists(跟所有都不匹配)和not in(<>all)解决否定问题.


子查询
非关联子查询(单列和多列)
关联子查询(exists not exists)
多表查询
连接join
cross join(交叉连接)
inner join(内连接)
outer join(外连接)


cross join


select e.ename,e.deptno,
d.deptno,d.location
from emp_hiloo e cross join dept_hiloo d
where e.deptno = d.deptno
结果集=4*10 笛卡尔积


内连接




课外练习
1 zhangwuji的领导是谁?
2 zhangwuji领导谁?
3 哪些人是领导?
4 哪些人是员工?哪些人不是领导?
5 哪些部门有员工?
6 哪些部门没有员工?
7 结果集是什么?为什么?
select ename from emp_hiloo o
where not exists
          (select 1 from emp_hiloo i
           where o.mgr = i.empno)


select ename from emp_hiloo o
where exists
          (select 1 from emp_hiloo i
           where o.mgr = i.empno)



第三天课外练习
1 zhangwuji的领导是谁?
select ename from emp_hiloo
where empno in 
           (select mgr from emp_hiloo
            where ename = 'zhangwuji')


select ename from emp_hiloo o
where exists 
           (select mgr from emp_hiloo i
            where o.empno = i.mgr
            and i.ename = 'zhangwuji')


2 zhangwuji领导谁?
select ename from emp_hiloo
where mgr in 
          (select empno from emp_hiloo
           where ename = 'zhangwuji');
exists
3 哪些人是领导?
4 哪些人是员工?哪些人不是领导?
5 哪些部门有员工?
6 哪些部门没有员工?


7 结果集是什么?为什么?
employor
select ename from emp_hiloo o
where not exists
          (select 1 from emp_hiloo i
           where o.mgr = i.empno)


employee 9
select ename from emp_hiloo o
where exists
          (select 1 from emp_hiloo i
           where o.mgr = i.empno)


inner join(内连接)
列出员工的名字和他的工作地点
select e.ename,d.location
from emp_hiloo e join dept_hiloo d
ORA-00905: missing keyword 缺少关键字


select e.ename,d.location
from emp_hiloo e join dept_hiloo d
on e.deptno = d.deptno
and e.ename = 'zhangwuji'


from (e d) -->on的and 条件(过滤) -->on join(连接)
驱动表 
匹配表
内连接:匹配记录


zhangwuji在哪个地区?


课堂练习
1列出部门名称和职位?
select disitnct dname,job
from emp_hiloo e join dept_hiloo d
on e.deptno = d.deptno


2哪些员工在beijing上班?
select e.ename,d.location
from emp_hiloo e join dept_hiloo d
on e.deptno = d.deptno
and d.location = 'beijing'


3各个部门的平均工资,列出部门名称,地区名称,平均工资?
select d.dname,d.location,
       round(avg(salary)) a_sal
from emp_hiloo e join dept_hiloo d
on e.deptno = d.deptno
group by d.dname,d.location
from(e d) --> join on -->group by -->select
       min    min
select max(d.dname),max(d.location),
       round(avg(salary)) a_sal
from emp_hiloo e join dept_hiloo d
on e.deptno = d.deptno
group by d.deptno


from后面可以跟子查询
select d.dname,d.location,a.a_sal
from dept_hiloo d join
  (select deptno,round(avg(salary) a_sal
   from emp_hiloo 
   group by deptno) a
on d.deptno = a.deptno




哪些员工的工资比本部门的平均工资高?
select e.ename,e.salary,a.a_sal
from emp_hiloo e join
  (select deptno,round(avg(salary)) a_sal
   from emp_hiloo 
   group by deptno) a
on e.deptno = a.deptno
and e.salary > a.a_sal


列出员工名字和他领导的名字


select t1.ename employee,
       t2.ename manager
from emp_hiloo t1 join emp_hiloo t2
on t1.mgr = t2.empno
t1 10条记录 匹配9条记录 匹配不上 1
t2 10条记录 匹配4条记录 匹配不上 10-4




select t1.ename employee,
       decode(t2.ename,t1.ename,'Boss',
                   t2.ename) manager
from emp_hiloo t1 join emp_hiloo t2
on nvl(t1.mgr,t1.empno) = t2.empno


select distinct t1.ename
from emp_hiloo t1 join emp_hiloo t2
on t1.empno = t2.mgr
   
t1.mgr = t2.empno  t2.ename


select ename from emp_hiloo  o
where exists
         (select 1 from emp_hiloo i
          where o.empno = i.mgr)


outer join(外连接)
from t1 left join t2
on t1.c1 = t2.c2
外连接结果集=内连接的结果集+(t1表中匹配不上的记录和t2表的null记录的组合)


from t1 right join t2
on t1.c1 = t2.c2




from t1 full join t2
on t1.c1 = t2.c2


两种方法,内连接nvl(mgr,empno),外连接
列出员工名字和领导名字(包含zhangsanfeng)
select t1.ename employee,
       nvl(t2.ename,'Boss')
from emp_hiloo t1 left join emp_hiloo t2
on t1.mgr = t2.empno
10 = 9 + 1


各个部门的平均工资,显示部门名称,地区,平均工资(包含40部门)


select d.dname,d.location,a.a_sal
from dept_hiloo d left join
  (select deptno,round(avg(salary)) a_sal
   from emp_hiloo
   group by deptno) a
on d.deptno = a.deptno
4 = 3 + 1


from dept_hiloo d left join emp_hiloo e
on d.deptno = a.deptno
11 = 10 + 1


from dept_hiloo d right join emp_hiloo e
on d.deptno = a.deptno
10 = 10 + 0


哪个部门没有员工?
select d.deptno,d.dname,
       e.empno,e.ename,e.bonus
from dept_hiloo d left join
     emp_hiloo e
on d.deptno = e.deptno
where e.empno is null


内连接 匹配
外连接 (匹配+不匹配 某种表的所有记录要在结果集中),不匹配
对匹配表的过滤发生在连接之前用on过滤(on and) 发生在连接之后用where过滤


对驱动表的过滤一定要用where子句


哪个部门没有叫zhangwuji的?


from dept_hiloo d left join
     emp_hiloo e
on d.deptno = e.deptno
and e.ename = 'zhangwuji'
where e.empno is null
from (e d) -->on过滤e-->left join -->where


否定 外连接 + where 匹配表.非空列 is null


哪些人是员工?(哪些人不是领导)


select t1.ename
from emp_hiloo left t1 join 
   emp_hiloo t2
on t1.empno = t2.mgr t1.mgr = t2.empno
where t2.empno is null
15 = 9 + (10 - 4)


内连接
     等值连接
     自连接
     非等值连接
外连接
     等值连接
     自连接
     非等值连接
SQL> create table salgrade_hiloo
  2  (grade number(2),
  3   lowsal number(8,2),
  4   hisal number(8,2))
  5
SQL> select * from salgrade_hiloo;


     GRADE     LOWSAL      HISAL
---------- ---------- ----------
         5          1       4000
         4       4001       6000
         3       6001       8000
         2       8001      10000
         1      10001      99999
         0     100000     200000


insert into salgrade_hiloo values
(5,1,4000);


select e.ename,e.salary,s.grade
from emp_hiloo e join salgrade_hiloo s
on e.salary between s.lowsal and s.hisal
and s.grade in (3,5)


1在Linux上准备好你们的.sql文件
2在solaris下vi test.sql
按aio中的任意一个键,进入编辑模式,鼠标右键paste,按ESC键,敲:wq!按回车
3%sqlplus jsd1301/jsd1301 @test.sql
4出错
4.1 rm test.sql
5回到1步重新开始


课外练习
1 zhangwuji领导谁?
select ename from emp_hiloo
where mgr in 
          (select empno from emp_hiloo
           where ename = 'zhangwuji');
用exists实现 inner join


2 zhangwuji的领导是谁? inner join


3各个部门的平均工资,显示部门名称,地区,平均工资(包含40部门)三种写法


4 各个级别的人数(两种都要)

第四天课外练习
1 zhangwuji领导谁?
select ename from emp_hiloo
where mgr in 
          (select empno from emp_hiloo
           where ename = 'zhangwuji');
用exists实现 inner join
select ename
from emp_hiloo o
where exists
         (select 1 from emp_hiloo i
          where o.mgr = i.empno
          and i.ename = 'zhangwuji')


select t1.ename,t2.ename
from emp_hiloo t1 join emp_hiloo t2
on t1.empno = t2.mgr
and t1.ename = 'zhangwuji'


2 zhangwuji的领导是谁? inner join
select t1.ename,t2.ename
from emp_hiloo t1 join emp_hiloo t2
on t1.empno = t2.mgr
and t2.ename = 'zhangwuji'


3各个部门的平均工资,显示部门名称,地区,平均工资(包含40部门)三种写法
select d.dname,d.location,
       round(avg(salary)) a_sal
from dept_hiloo d left join emp_hiloo e
on d.deptno = e.deptno
group by d.dname,d.location


select max(d.dname),max(d.location),
       round(avg(salary)) a_sal
from dept_hiloo d left join emp_hiloo e
on d.deptno = e.deptno
group by d.deptno


4 各个级别的人数(两种都要)
select s.grade,count(e.empno)
from emp_hiloo e join salgrade_hiloo s
on e.salary between s.lowsal and s.hisal
group by s.grade


select s.grade,count(e.empno)
from emp_hiloo e right join      salgrade_hiloo s
on e.salary between s.lowsal and s.hisal
group by s.grade


第五天内容
分页问题,排序问题
oracle
rownum 伪列
select ename,rownum from emp_hiloo
特性:记录编号必须从1开始


找出第一条记录
找出第二条记录
找出前二条记录
找出[2,5]条记录


分页问题
第一页:
select ename,salary,rownum
from emp_hiloo
where rownum <= 3;


非一页
select rn,ename,salary
from (
select rownum rn,ename,salary
from emp_hiloo
where rownum <= 6
      )
where rn >= 4


排名问题
select rownum,ename,salary
from emp_hiloo
where rownum <= 3
order by salary desc
 ROWNUM ENAME                    SALARY
------- -------------------- ----------
      1 zhangwuji                 10000
      3 liyi                       9000
      2 liucangsong                8000


select rownum,ename,salary
from(
select ename,salary
from emp_hiloo
order by salary desc
     )
where rownum <= 3


子查询
表连接
集合操作


集合操作
union/union all (并集)
intersect(交集)
minus(差)
连接两条select语句,必须是同构的,列的个数以及数据类型完全一致
union,intersect,minus的结果集都是去重的
union all不去重


select ename,deptno,salary*1.1
from emp_hiloo
where deptno = 10
union all
select ename,deptno,salary*1.2
from emp_hiloo
where deptno = 20
union all
select ename,deptno,salary
from emp_hiloo
where deptno not in (10,20)
改写为case when


列出员工名字和领导名字的对应关系(包含1005) 3种写法
select t1.ename,t2.ename
from emp_hiloo t1 join emp_hiloo t2
on t1.mgr = t2.empno
union all
select ename,'Boss'
from emp_hiloo
where mgr is null


20部门和30部门的共同职位是?
select job from emp_hiloo
where deptno = 20
intersect
select job from emp_hiloo
where deptno = 30


哪个部门没有员工?
select deptno from dept_hiloo
minus
select deptno from emp_hiloo


匹配+不匹配 (外连接+union/union all)
匹配(内连接 in exists intersect)
不匹配(外连接+where 匹配表.非空列 is null not in not exists minus)


DDL(数据类型 约束 constraint)
约束
primary key 主键 pk
foreign key (reference) 外键 fk
not null(非空) nn
unique key  唯一键 uk
check  检查约束  ck


pk目的
不允许表中有null记录和重复记录
pk=nn + uk
列级约束
create table  test
(c1 number(2) constraint test_c1_pk primary key,
 c2 number);


表级约束
create table  test
(c1 number(2),
 c2 number,
 constraint test_c1_pk primary key(c1));




1 m
dept(1) emp(m)
1个部门有多个员工,一个员工属于一个部门
emp(empno,deptno)


m m
student  course  stu_cour
一名学生选多门课程,一门课程被多个学生选


学生选课表 stu_cour
stu_id 
cour_id
pk (stu_id,cour_id)
score
create table stu_cour
(stu_id number(4),
 cour_id number(2),
 score number(3),
 constraint stu_cour_sid_cid_pk
 primary key(stu_id,cour_id)
);
1 1(夫妻)


定义了fk的表称之为子表(child table),被fk列引用的表称之为父表,被fk列引用的列要求是唯一性
1先create parent table,有uk(被引用列),再create child table,定义fk列
2先insert into parent table,再insert into child table
3先delete from child table,再delete from parent table
4先drop child table,再drop parent table


外键约束
保证表的一对多关系
create table child
(c1 number(4) constraint child_c1_pk
    primary key,
 c2 number(2) constraint child_c2_fk
    references parent(c1))
ERROR at line 5:
ORA-00942: table or view does not exist


create table parent
(c1 number(2));
create table child
(c1 number(4) constraint child_c1_pk
    primary key,
 c2 number(2) constraint child_c2_fk
    references parent(c1))
ERROR at line 5:
ORA-02270: no matching unique or primary key for this column-list


alter table parent
add constraint parent_c1_pk 
primary key(c1);
create table child
(c1 number(4) constraint child_c1_pk
    primary key,
 c2 number(2) constraint child_c2_fk
    references parent(c1))
table created


SQL> insert into child values (1,1);
insert into child values (1,1)
ERROR at line 1:
ORA-02291: integrity constraint(违反fk) (HILOO.CHILD_C2_FK) violated - parent key not found


insert into parent values (1);
insert into child values (1,1);


SQL> delete from parent where c1 = 1;
delete from parent where c1 = 1
*
ERROR at line 1:
ORA-02292: integrity constraint (HILOO.CHILD_C2_FK) violated - child record  found


SQL> delete from child where c2 = 1;
SQL> delete from parent where c1 = 1;


drop table parent purge;
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys


drop table parent cascade constraints purge;
alter table child drop constraint child_c2_fk;
drop table parent purge;


cascade constraints 级联约束


级联删除
create table child1
(c1 number(4) constraint child1_c1_pk
    primary key,
 c2 number(2) constraint child1_c2_fk
    references parent(c1)
    on delete cascade)


delete from parent where c1 = 1;(包含了以下两条命令)
delete from child1 where c2 = 1;
delete from parent where c1 = 1;


create table child2
(c1 number(4) constraint child2_c1_pk
    primary key,
 c2 number(2) constraint child2_c2_fk
    references parent(c1)
    on delete set null)
constraint cons_name primary key(c1,c2),


delete from parent where c1 = 1;(包含了以下两条命令)
update child2 set c2 = null
where c2 = 1;
delete from parent where c1 = 1;


fk的表级约束形式
create table child(
c1 nubmer(3) constraint child_c1_pk
             primary key,
c2 number(2),
constraint child_c2_fk foreign key(c2)
           references parent(c1));
student
id pk
name 


course
id pk
name


stu_cour
stu_id fk -> student(id)
cour_id -> course (id)
pk(stu_id,cour_id)
score


not null
强制属性
create table test
(c1 number not null)


unique
保证唯一性
pk,uk比较
共同点:唯一的,被fk所引用
不同点:
pk = uk + nn
uk允许为null,多个null值
create table test
(c1 number constraint test_c1_pk
           primary key,
 c2 number constraint test_c2_uk
           unique,
 c3 number constraint test_c3_uk
           unique)


create table test
(c1 number constraint test_c1_pk
           primary key,
 c2 number ,
 c3 number ,
 constraint test_c3_uk unique(c2,c3))


create table test
(c1 number constraint test_c1_pk
           primary key,
 c2 number not null constraint test_c2_uk
           unique,
 c3 number not null constraint test_c3_uk
           unique)


check约束
create table test(
c1 number constraint test_c1_ck 
          check (c1 > 100)
);


create table test(
c1 number ,
constraint test_c1_ck 
          check (c1 > 100)
);


transaction 
id
balance 余额
4000 a
100  b


create table account
(id  number
 balance number);
insert into account values (1,4000);
insert into account values (2,100);
commit;
update account 
set balance = balance - 3200
where id = 1;
update account
set balance = balance + 3200
where id = 2;
commit;


alter table test drop primary key;


transaction
oltp on-line transaction processing
联机事务处理系统
事务的隔离级别(缺省)
read committed
已经提交了的数据和本session正在修改的数据
如果不commit,操作不生效,其他人看不见.
如果不commit,其他人不能操作同一条记录,会阻塞
如果不commit,ROLLBACK SEGMENT空间不释放
用户 session
jsd1301 102 
student 1 ddl
student 2 表存在 ddl结果,自动commit


1 dml没commit
2 不能看见


DML锁
表级共享锁
行级排他锁
表级共享锁 行级排他锁
s1 ok ok
s2 ok wait
s3 ok ok


DDL DDL排他锁
drop table
ORA-00054: resource busy and acquire with NOWAIT specified


rollback segment(公共空间 dml)
rollback;


课外练习
1 按工资排名的第4到第6名员工找出来.


列出学生名字,课程名字,成绩
每个学生的平均成绩
哪些学生选了所有的课程
哪门课程没人选?


DDL,transaction