telnet 192.168.0.26
openlab (OS用户名）
open123
sqlplus openlab/open123 (DB用户名）
SQL>
------------------------------------
SQL :
DML:数据操纵语言
SELECT INSERT UPDATE DELETE
DDL：数据定义语言
CREATE ALTER DROP TRUNCATE
DCL： 数据控制语言
COMMIT ROLLBACK SET
GRANT REVOKE
---------------------------------------------------
SQL*PLUS （ORACLE独有）
有缩写 ，回车就运行，不需要分号结束
1、查看数据库表的结构: DESC
2、编辑SQL命令 ： I C A /
3、对文件进行处理：
SPOOL
SPOOL OFF
@ start
desc s_dept
select * from s_dept ;
-------------------------
set pause on
select * from s_emp;
回车
set pause off
------------------------
host clear
desc s_emp
select LAST_NAME,SALARY,TITLE
from s_emp;
select last_name , salary * 1.1
from s_emp ;
------------------
l1
a sal1
l
/
------------------
l1
c/sal1/
/
------------------
l 显示最后一条SQL
l1
c/1.1/1.3 替换字符串
c/1.1/ 删除字符串
del 删除当前行
i 当前行后，新加一行
a 当前行后，直接添加 字符
/ 运行
------------------------
select first_name||last_name name
from s_emp;
select first_name|| ' ' || last_name name
from s_emp;
select sysdate from dual;
select 'TODAY IS :'||sysdate from dual;
-----------------------------------
salary* null = null
salary * nvl(null, 0) = 0
每个员工的月收入：工资+奖金
select last_name ,
salary + salary * nvl(commission_pct,0)/ 100
totalsal
from s_emp;
last_name || first_name
----------------------------------------
NVL: 两个参数的数据类型要一致
对列值去重
select distinct dept_id from s_emp;
select distinct c1,c2 from t1 ;
显示各个部门有哪些工种
select distinct dept_id , title
from s_emp
order by dept_id ;
---------------------------------
SQL*PLUS语言，定义列的输出格式
column salary format '$999,999.00'
select salary from s_emp;
column last_name format a4
select last_name from s_emp;
column salary clear
column last_name clear
------------------------------------
select * from t1 ;
select c1 ,c2 ,c3 , 列运算 列别名
from t1;
列运算 : + - * / 注意空（NVL）
字符运算： ||
列别名 ： "my sal" (大小写，有空格）
desc t1
----------------------------------------
第二章 WHERE和ORDER BY
显示员工名字和工资（升序）
select last_name , salary
from s_emp
order by 2 ;
显示员工名字和工资（降序）
select last_name , salary
from s_emp
order by 2 desc ;
select last_name , salary
from s_emp
order by 2 desc , 1 ;
---------------------------
1991.8.6入职员工
(要先查询日期的默认格式）
05-SEP-08
select last_name ,salary
from s_emp
where start_date='06-AUG-91' ;
JAN FEB MAR APR MAY JUN
JUL AUG SEP OCT NOV DEC
--------------------------------------
1991年入职的员工，工资大于 1000 的人
列出名字和工资（降序）
select last_name ,salary
from s_emp
where start_date >= '01-JAN-91'
and start_date <= '31-DEC-91'
and salary > 1000
order by salary desc;
--------------------
2000-----3000
salary >= 2000 and salary <= 3000
salary between 2000 and 3000
工资介于2000 和3000 的员工名字
select last_name
from s_emp
where salary between 2000 and 3000;
----------------------
10，13，18，34 号部门的员工
名字和工资（降序）
select last_name ,salary
from s_emp
where dept_id in ( 10,13,18,34)
order by 2 desc;
dept_id = null
null = null
null <> null (false)
----------------------------------
like :
% 0 1 n
_ 1
last_name 第二个字符是a
last_name 中含有字母 a和e 的员工名字
select last_name
from s_emp
where last_name like '_a%';
last_name 中含有字母 a和e 的员工名字
select last_name
from s_emp
where last_name like '%a%e%'
or last_name like '%e%a%' ;
6月份入职员工名字和工资
select last_name
from s_emp
where start_date like '%JUN%';
转义
select * from t1
where a like 's/_%' escape '/';
select * from t1
where a like 's*_%' escape '*';
哪些员工没有部门
select last_name
from s_emp
where dept_id is null;
select last_name
from s_emp
where dept_id is not null;
-------------------------------
AND 0 1 null (不确定)
0 0 0 0
1 0 1 null
null 0 null null
OR 0 1 null
0 0 1 null
1 1 1 1
null null 1 null
NOT
0 1
1 0
null null
in (1,3,5,null) NULL 的出现不影响结果
=1 or =3 or =5 or = null
not in (1,3,5,null) 没有返回行
<>1 and <>3 and <>5 and <> null
--------------------------------
select
from
where 条件1 AND | OR 条件2
order by 列名字，列别名，列位置 DESC
；
条件1 ：比较运算符
= > >=
<> != ^=
between ...（小） and ... （大）
in （ ， ， ，）
like : % _ escape 日期
is null
is not null
---------------------------------
第三章 单行函数
作用
分类
select lower('af WER'),UPPER('adsgf'),
initcap('asd asd')
from dual;
substr('asdfasdf' , 4 ) 'fasdf'
substr('asdfasdf' , -1 , 1 ) 'f'
instr ('dasfs' ,'a') 2
instr ('dasfs' ,'av') 0
last_name 有且只有一个 字母a
select last_name
from s_emp
where last_name like '%a%'
and last_name not like '%a%a%' ;
select last_name
from s_emp
where instr(last_name , 'a') > 0
and
instr(substr(last_name,instr(last_name , 'a')+1),'a')
= 0 ;
员工的工资 （<10000） 千位数字
select salary , trunc(salary/1000)
from s_emp;
--------------------------------
员工的工资 千位数字
select salary ,substr(trunc(salary/1000),-1,1)
from s_emp ;
select mod (trunc(12000/1000),10)
from dual; 伪表
------------------------------------
select sysdate ,user ,234*234,trunc(120/10)
from dual;
------------------------------------
char:
lower upper initcap
substr instr length
lpad rpad
number :
round trunc mod
date:
months_bwteen
add_months
next_day last_day
round trunc
----------------------------------
修改当前会话中的日期显示格式
alter session set
nls_date_format='yyyy.mm.dd:hh24:mi:ss';
conn openlab/open123
日期显示格式回到以前
员工的工龄 （四舍五入到个位）
select last_name ,
round( (sysdate-start_date)/365 ) years
from s_emp;
select last_name ,
round(months_between (sysdate,start_date)/12 ) years
from s_emp;
下周的今天
select sysdate+7 from dual;
每个员工 工作30年后的第一个星期一 是哪天
select last_name,
next_day(add_months(start_date,360),
'MONDAY' ) day
from s_emp;
2124 年 2月最后一天
alter session set
nls_date_format='yyyy.mm.dd';
select last_day('2124.02.01') from dual;
--------------------------------
alter session set
nls_date_format='yyyy.mm.dd:hh24:mi:ss';
select trunc(sysdate,'hh')+1/24 from dual;
-----------------------------
select to_char(sysdate,'yyyy.month.dd:hh24:mi:ss day am')
from dual;
星期一 入职员工
select last_name
from s_emp
where to_char(start_date,'DY') = 'MON' ;
select last_name
from s_emp
where to_char(start_date,'DAY') = 'MONDAY' ; 没有返回行 ，不对
----------------------------
上半月 入职员工 （1---15）
select last_name
from s_emp
where to_char(start_date,'dd' ) < '16' ;
select last_name,
to_char(salary ,'$99,999.00')
from s_emp;
16 ---------> '16' (隐含转换）
16 ---------> '$16.00' （显式转换）
16 <--------- '16' (隐含转换）
16 <--------- '$16.00' （显式转换）
---------------------------
第四章 连接查询
相等连接 ：
select last_name , name
from s_emp e , s_dept d
where dept_id = d.id ;
工资高于2000 的员工 名字和所在部门名字
select last_name , name
from s_emp e , s_dept d
where dept_id = d.id
and salary > 2000;
不等连接
select last_name , grade
from s_emp , salgrade
where salary between losal and hisal ;
-----------------------
外连接 ：
select a , b
from t2,t3
where a= b(+) ;
select a , b
from t2,t3
where a(+)= b ;
员工名字，部门名字 （部门显示完整）
select last_name ,name
from s_emp e , s_dept d
where dept_id(+) = d.id ;
哪些部门没有员工
select name
from s_emp e , s_dept d
where dept_id(+) = d.id
and last_name is null ;
1 A 4
2 B 4
3 C 5
4 D 5
5 E
员工名字和上司名字 (完整)
select wkr.last_name , mgr.last_name
from s_emp wkr , s_emp mgr
where wkr.manager_id = mgr.id ;
最低层员工名字
select mgr.last_name
from s_emp wkr , s_emp mgr
where wkr.manager_id(+) = mgr.id
and wkr.last_name is null ;
所有 领导名字
select distinct mgr.last_name
from s_emp wkr , s_emp mgr
where wkr.manager_id = mgr.id ;
--------------------------
select a,b
from t2,t3 ;
------------------------------------
第五章 组函数
a : 10 10 20 30
avg (a) = (10+10+20+30)/4
avg (distinct a)= (10+20+30)/3
comm : 10 10 20 null
avg(comm) = (10+10+20)/3
avg(nvl(comm,0))= (10+10+20)/4
-----------------------------------------------
comm : 10 10 20 null
max(comm) : 20
min(comm) : 10
sum(comm) : 40
avg(comm) : (10+10+20)/3 注意空值
count (*) : 4 所有记录的行数
count(comm) : 3 列不为空的行数
尤其注意：avg , count 注意空值
------------------------------------------------
10----40号部门中，最高、最低、平均工资
select last_name from s_emp
where salary is null;
select max(salary),min(salary) , avg(salary)
from s_emp
where dept_id between 10 and 40;
整个公司的平均奖金, 单位：元
（四舍五入到个位）
select round(avg (salary * nvl(commission_pct,0))) avgcomm
from s_emp;
公司人数
奖金不为空的人数
有奖金的人数
select count(*) from s_emp;
select count(commission_pct) from s_emp;
select count(*) from s_emp
where commission_pct > 0 ;
－－－－－－－－－－－－－
各个部门的平均工资（降序）
select dept_id ,avg(salary)
from s_emp
group by dept_id
order by 2 desc ;
----------------------------------------
各个部门，工资高于1000 的人数
select dept_id ,count(*)
from s_emp
where salary > 1000
group by dept_id ;
哪些部门 工资高于1000 的人数 超过2个
select dept_id ,count(*)
from s_emp
where salary > 1000
group by dept_id
having count(*) > 2;
--------------------------------------
是否有两个人，同部门，同工资 ？？
如果有，列出 部门号和工资
select dept_id ,salary ,count(*)
from s_emp
group by dept_id ,salary
having count(*) >1 ;
--------------------------------------
哪些部门的平均工资高于 2000
select dept_id ,avg(salary)
from s_emp
group by dept_id
having avg(salary) > 2000 ;
------------------------------------
哪些部门工资高于 600 的人数 超过 3 个
列出部门的名字、人数(降序)
select name , count(*)
from s_emp , s_dept d
where salary > 600
and dept_id = d.id
group by name
having count(*) > 3
order by 2 desc ;
如果SELECT中有组函数，
那么SELECT中的单个列，
必须是GROUP BY 中出现的列
N个表，连接条件最少是 N-1
----------------------------------------
各个部门的名字、最小、最大、平均工资
select name,min(salary),max(salary),
avg(salary)
from s_emp , s_dept d
where dept_id = d.id
group by name ;
----------------------------------------
第六章 子查询
哪些员工和Smith 同部门
select last_name ,salary
from s_emp
where dept_id in
(select dept_id from s_emp
where last_name = 'Smith');
哪些员工是Smith 的同事
select last_name ,salary
from s_emp
where dept_id in
(select dept_id from s_emp
where last_name = 'Smith')
and last_name <> 'Smith' ;
哪些员工的工资，高于公司的平均工资？
select last_name , salary
from s_emp
where salary >
(select avg(salary) from s_emp) ;
哪个员工 工资最高？
select last_name , salary
from s_emp
where salary =
(select max(salary) from s_emp) ;
各个部门中，工资最高的员工名字、工资和
部门号
select dept_id ,last_name , salary
from s_emp
where (dept_id ,salary) in
(select dept_id ,max(salary)
from s_emp
group by dept_id ) ;
哪些部门人数，超过 30号部门的人数
select dept_id ,count(*)
from s_emp
group by dept_id
having count(*) >
(select count(*) from s_emp
where dept_id = 30);
人数超过2个的部门 ID 及其该部门的平均工资
select dept_id ,avg(salary)
from s_emp
group by dept_id
having count(*) > 2;
----------
子查询：
1、WHERE
2、HAVING
3、FROM (IN-LINE VIEW)
哪些员工，工资高于所在部门的平均工资
select last_name ,salary
from s_emp e,
(select dept_id ,avg(salary) avgsal
from s_emp
group by dept_id) f
where e.dept_id = f.dept_id
and salary > avgsal ;
工资最高的前5个员工
select * from
(select last_name,salary from s_emp
order by salary desc )
where rownum < 6;
------------------------------------
第七章 数据库设计及其E-R图
an employee may be is a manager of
some employees
an employee may be managed by
an employee
----------------------------
student :
id name age
addr monitor grade
course :
id name
classroom teacher
允许学生休学
课程必须都是有学生在学习的（不保留无人选修的课程）
-------------------------
1NF: 消除列中嵌套列
解决： 将这些嵌套列，做成独立的列
2NF：消除部分依赖
解决：将表切割，完全依赖的列单独做个表
部分依赖的列，再做成新表
3NF：消除传递依赖
解决：将表切割，直接依赖的列单独做个表
传递依赖的列，再做成新表
范式目的：
规范表的结构设计，消除数据的冗余
做成满足3NF，表会切割得越来越小
代码表 多
问题： 降低数据库查询性能 （表的连接）
ORACLE8 ： ORDBMS
抽象数据类型
数据的冗余（一致性）
-------------------------------------
PK (a,b)
a is not null+b is not null + (a,b) unique
UK(a,b)
(a,b) unique
-----------------------------------
t1 : a pk
t2 : a references t1 (a)
t1 : a
t2 : a references t1 (a) 不能引用
（ T1 上的a 列，没有UK或者PK）
s_dept : id (pk)
s_emp : dept_id (fk) ---- s_dept(id)
dept_id : 可以为空，如果不为空，必须是
现有部门中的一个
----------------------------------------
E-R：
实体，属性，
关系 (1：1，1：n, n:m) , may be ,must be
数据库设计表格：
E-R 实体名 ----》 表名
属性----》 列
(# --->pk , * ---> nn , UID----> uk
(#)----> nn + uk )
关系 (1：1，1：n ) ----》 列 （FK）
关系 (n：m)-----》表
------------------------------------------
第八章 创建表
数据库对象：
1、表：存储业务数据，有结构，有数据
2、视图：虚表，只有结构，没有数据
简化复杂查询，屏蔽敏感数据
对于提高查询性能，作用不明显
3、序列：自动产生一组连续的整数
步长是固定的
可以共享
1 3 5 7 9 。。。
4、索引：对一个或者多个列，进行排序
相当于目录，加快查询的性能
加快定位记录
单独有自己的存储空间，和表分离
表名字：
a12
-----------------------
创建表的前提条件
1、CREATE TABLE权限
2、需要有存储空间的许可
（unlimited tablespace）
创建表前考虑：
1、表名字、列名字要符合命名规范
2、使用合适的数据类型和长度
3、数据要满足的业务条件-----约束
4、表的数据存储在哪里
命名规范 ：
数据类型：
约束：限定表中的数据，需要满足一定的业务要求
1、NN ：只能定义在列级 （desc tab1）
2、PK：可以定义在列级或者表级
(ORACLE 自动创建索引）
3、UK：可以定义在列级或者表级
(ORACLE 自动创建索引）
4、FK：可以定义在列级或者表级
5、CK：可以定义在列级或者表级
1、列级
2、表级 ：约束条件和多列有关系
ORACLE对约束起名字：SYS_C43566
-------------------------------------
create table zr1 (
id number(2) not null,
name char(4),
[constraint zr1_uk1] unique (id ,name) );
select constraint_name
from user_constraints
where table_name='ZR1' ;
-------------------------------------
FK：
dept_id number(4) references s_dept(id),
不能删除有员工的部门
dept_id number(4) references s_dept(id)
on delete cascade,
删除有员工的部门时，同时删除这个部门的员工
dept_id number(4) references s_dept(id)
on delete set null ,
删除有员工的部门时，同时这个部门的员工
的部门号，设定为空
salary number(4)
check(salary between 2000 and 4000) ,
name char(4) check (name like 'T%') ,
start_date date check (start_date < sysdate),
create table zr3(
id number(7) primary key ,
name char(25) not null,
region_id number(7) references s_region(id),
unique (name,region_id) );
create table zr4(
id number(2) primary key ,
name char(8) not null ,
sal number(6),
comm number(6),
mgr number(2) references zr4(id)
on delete set null ,
unique (name , sal ) ,
check (sal > comm ));
create table zr5(
name char(8) not null ,
sal number(6),
foreign key (name,sal) references
zr4(name,sal)) ;
创建新表，各个部门的平均工资
create table zr6
as select dept_id ,avg(salary) avgsal
from s_emp
group by dept_id ;
创建空表，结构同s_emp一样
create table zr7
as select * from s_emp
where 1=2;
--------------------------------
第九章 修改数据
insert into t1
values( v1,v2,v3) ;
insert into t1(c1,c2)
values( v1,v2 );
insert into t1(c1,c2)
values( v1,sysdate );
insert into t1(c1,c2)
values( v1,'01-JAN-80' );
insert into t1
select ..... ;
create table zr7
as select * from s_emp
where 1=2;
将工资高于公司平均工资的人，写入到zr7
insert into zr7
select * from s_emp
where salary >
(select avg(salary) from s_emp) ;
commit;
---------------------------------
10号部门员工，工资上涨10%
update s_emp
set salary = salary * 1.1
where dept_id = 10;
行排它锁 ，其他用户不能修改这些记录行
commit ;
--------------------------
update s_emp
set salary =
(select avg(salary) from
s_emp where dept_id = 10);
where dept_id = 10;
rollback ;
----------------------------------
事务：
一组SQL，一起提交，或者一起回退
事务开始：
上个事务的结束，就是下个事务的开始
事务的结束：
COMMIT
ROLLBACK
DDL（CREATE ，ALTER ，DROP）
DCL（GRANT ，REVOKE）
EXIT
-----------------------
alter table zr6
add (avgcomm number(6) );
alter table zr6
drop (avgcomm );
----------------------------------
alter table zr6
modify (avgsal not null );
alter table zr6
modify (avgsal null );
-----------------------------------
alter table zr6
modify (avgsal default 2000 );
alter table zr6
modify (avgsal default null );
----------------------------------
create table zr8
as select * from s_emp
where 1=2 ;
alter table zr8
add primary key (id) ;
alter table zr8
add foreign key (dept_id)
references s_dept(id) ;
alter table zr8
add foreign key (manager_id)
references zr8(id) ;
alter table zr8
add check (salary < 5000 ) ;
user_constraints
alter table zr8
drop constraint ** ;
alter table zr8
drop primary key cascade ;
(manager_id fk 没有了)
drop table ** ;
drop table ** cascade constraints;
rename zr1 to zr;
原来的表名，无效
---------------------------------
1,3,5,7,9......99
create sequence s_zr1
start with 1
increment by 2
maxvalue 99
cache 10 ;
select s_zr1.nextval from dual;
select s_zr1.currval from dual;
create table zr3(a int);
insert into zr3 values(s_zr1.nextval);
/
/
commit ;
select * from zr3;
---------------------------------
2 4 6 8 10 2 4 6 ....
create sequence s_zr3
start with 2
increment by 2
maxvalue 10
minvalue 2
nocache
cycle ;
select s_zr3.nextval from dual;
--------------------------------
user_sequences
8477
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
