一.数据库相关概念介绍
1、DB（Database）：数据库
DBA（DataBase Administrator）：数据库管理员
DBMS（DataBase Management System）：数据库管理系统
2、数据库的种类:
oracle / sybase / db2(IBM)
sql server / mysql /
access
3、数据表结构：由行和列组成.
行: row / record
列: column / field
4、SQL分类：
DQL（数据库查询语言）: select
DML（数据库管理语言）: insert / update / delete
DDL（数据库定义语言）: create / drop / alter / truncate
DCL（数据库控制语言）: grant / revoke
TCL（事务控制语言）: commit / rollback / savepoint
注：
5、远程登录到Oracle数据库服务器的方式:
登录到远程主机：
C:>telnet 192.168.0.26
用户名/密码: openlab/open123
这组是远程机器的用户名和密码
远程登录后连接数据库:sqlplus
sqlplus是Oracle数据库提供的命令行客户端工具
sunv210% sqlplus openlab/open123
这组是数据库的用户名和密码
6、了解练习常用到的数据表:
emp: 职员表
dept: 部门表
salgrade: 薪水等级表
desc命令:查看一个表的结构.
如：desc dept
- Oracle 数据库中常用的数据类型
varchar2(长度) 可变长字符串，它按照字符串的实际长度存储。
如：varchar2（10）表示最大长度为10的可变长字符串
char(长度) 定长字符串，它存储不足的会用空格补齐,默认长度 1 个字节 ，如：char（10），表示固定长度为10的字符串
Number() 表示整数或者浮点数精度为 38位，取值范围 1~38之间
number(7,2)表示数字类型，一共7位，小数点后两位，最大 99999.99
number(5) 表示5位整数， 最大99999
number 随便
date 日期类型，
blob 大对象类型，存二进制对象(声音、图片）
二、数据库查询：select
格式：select [列1，列2 别名，.......] from [表名] where [过滤条件]；
说明：
1、-- *号表示查询全部列
select * from dept; //查询dept表中的所有列
select ename, sal, comm from emp; //查询emp表中的ename、sal、comm列
2、-- 列别名：当对某一列取了别名后
3、列的算术表达式：
如：select ename，sal+comm total_sal from emp;
4、WHERE子句用来选择符合条件的记录。
、= 表示结果符合等于指定条件
如：//查询emp表中所有的职位为经理的员工名字和职位
elect ename, job from emp where job = 'MANAGER';
、between ... and ... 表示结果在这之间，between and 是一个闭区间
如：//查询emp表中薪水在1000到2000之间的员工名字和薪水
Select ename ，sal from emp where sal between 1000 and 2000；
、!=，<>，^= 这三个都可以表示不等于；
如：//查询emp表中不在部门10工作的员工姓名和工作部门
Select ename ，deptno from emp where deptno ！=10；
④、in (va1,val2,...) 判断结果是否在这个集合中存在
如：//查询emp表中在部门10和20 工作的员工姓名和部门
Select ename ，deptno from emp where deptno in （10,20）；
⑤、like '...' 表示字符串通配查询，'%'表示 0 或多个字符， '_' 表示一个字符；
如：//查询emp表中 员工姓名中包含字母"M"的员工，列出其姓名
Select ename from emp where ename like '%M%';
注： escape转义的用法：like 'S\_%' escape '\' ，表示查询时匹 配字符"_"，而不是表示一个字符
⑥... and ... 表示只有两个条件同时满足；
如：// 查询emp表中在部门10中工作且薪水大于2000的员工，列出姓名
薪水，部门号
Select ename，sal,deptno from emp where sal>2000 and deptno=10;
⑦、... or ... 表示条件只要满足其中之一就可以；
⑧、all ... 是要求都满足条件；
⑨、not ..... 可以与以上的条件产生相反的效果； 注：空值与任何值比较结果都为空
⑩、... is null 用来判断值是否为空。
- ORDER BY子句
格式：order by 目标列名（别名） 排序顺序（不写则默认为升序）
说明：ORDER BY子句使得 SQL在显示查询结果时按指定行的指定顺序排列
ASC（默认，升序） ，不怎么时默认
DESC（降序）
例如： //查询emp表中ename列，并按ename升序排序
select ename from emp order by ename;
//查询emp表中ename列，并按ename降序排序
select ename from emp order by ename desc;
- oracle 数据库函数
注意:dual 表(哑表)是专门用于函数测试和运算的.
单行函数 ：一个值输入，一个值输出
组函数：多个值输入，一个值输出
1、字符函数 (字符是大小写敏感 的 )
转小写 lower(字段名)
转大写 upper(字段名)
首字母大写 initcap(字段名)
字符串拼接 concat(字段 1, 字段 2)
截取子串 substr(字段名, 起始位置，取字符个数)
字符串长度 length(…)
空值函数 nvl(…) ，常用
例: select first_name,substr(first_name,2,2) sub from s_emp;（从名字的第二个字符
开始取两个字符）
select first_name,substr(first_name,-2,2) sub from s_emp; （从名字的倒数第二个
字符开始取两个字符）
2、数值函数
、 四舍五入函数 round(数据,保留到小数点后几位)
1 表示保留到小数点后一位，-1 表示保留到小数点前一位。
例：select round(15.36,1) from dual; //结果为15.4
、 截取数值函数 trunc(数据，保留到小数点后几位)
例：select trunc(123.456,1) from dual; //结果为123.4
截取到小数点后一位,注意:与 round 函数不同,不会四舍五入。
、取余函数 mod（被除数，除数）
列：select mod（16,5）from dual； //结果：1
- 日期函数
、取系统时间: sysdate(Oracle独有的函数)
select sysdate from dual;
、日期的加减
select sysdate-1,sysdate,sysdate+1 from dual; 取系统时间的前一天，当天和后一天
select sysdate,sysdate+1/144 from dual; 延迟 10 分钟
④、1、months_between(sysdate,hiredate):月份在当前时间和入职时间之间。
2、add_months(sysdate,6):在当前时间上加6个月。
3、sysdate+6:当前时间加6天。
4、next_day(sysdate,'FRI'):下一个周五（中文环境下要写"星期五"）。
5、next_day(sysdate,5):下一个周四（数字中英文通用）。
4、不同数据类型间的转换
、将日期转换为指定格式字符串 to_char（date,'日期格式')
//将当前系统时间转换为 年月日 时（24小时制）分秒的形式
select to_char(sysdate,'yyyy mm dd hh24:mi:ss')from dual;
、将指定格式字符串转换为日期 to_date（'字符串'，'日期格式'）
//如：将字符串"2011-03-20"转换为日期
Select to_date（'2011-03-20'，'yyyy-mm-dd'） from dual；
、to_number('$12345.67','$99999.99')
:将字符串"$12345.67"转换成数字12345.67（后面$99999.99表示前面数字的显示格式，）
④、to_char('1234','9999.00'):结果为：1234.00，后面的0表示不足位用0补。
- group by 分组函数
group by 分组子句 对分组后的子句进行过滤还可以用having不能用where
如：//查询平均薪水大于2000的部门，并且按部门分组，列出部门号和平均薪水
select deptno ，avg（sal） from emp group by deptno having avg（sal） >2000；
--获得每个部门每个职位的平均薪水
select deptno, job, avg(sal) from emp group by deptno, job order by deptno, job;
- 其他常用组函数
Avg（） 求平均值函数
Count（）统计数量的函数
Max（）求最大值函数
Min（）求最小值函数
Sum（）求和值函数
注：所有组函数会忽略空值（可用空值处理函数解决）, avg sum只能作用于数字类型
//求emp表中所有人的奖金的平均值
select avg(nvl(comm,0 ) ) from s_emp;
- 表连接
概念区分：等值连接、非等值连接；内连接、外连接、自连接；
1、等值连接、非等值连接：当连接运算符为=时，称为等值连接。使用其它运算符称为非等值 连接。
如：//查询员工名字和所在的部门名字
select e.ename,d.dname from emp e ,dept d where e.deptno=d.deptno;
- 内连接、外连接、自连接
- 、自连接:连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，这种连接称为表的自身连接
- 、内连接：相对于外连接而言，进行连接的两个表对应的相匹配的字段完全相同的连接。
默认空值不匹配
c） 、外连接：相对于内连接而言，匹配空值，分为左外连接、右外连接和全外连接
三者分别表示：匹配左边为空的情况、匹配右边为空的情况、匹配左右任意一边为空的情况
作用：
例如：列出所有没有员工的部门
select e.ename,d.dname from emp e,dept d where e.deptno(+)=d.deptno
and e.deptno is null;
列出所有没有部门的员工
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno（+）
and d.deptno is null;
列出所有员工及所有的部门，包括没有员工的部门和没有部门的员工
Select e.ename d.dname from emp e full outer join dept d on(e.deptno=d.deptno);
列出不是领导的员工
select manager.ename from emp worker right outer join emp manager
on worker.mgr = manager.empno where worker.empno is null;
- 子查询
1、概念：可以嵌在 sql 语句中的 select 语句。
Sql语句执行时会先执行嵌套的子查询语句，再将查询结果带到主查询中执行。
- 应用
如：找出员工中薪水最低的员工，列出姓名及其薪水：
Select ename ，sal from emp where sal =(select min(sal) from emp);
查询谁的薪水比本部门的平均薪水高
select e.ename e.sal a.avgsal from emp e ,(select deptno, avg(sal) from emp group by deptno ) a where e.deptno=a.deptno and e.sal>a.avgsal;
六、对表本身的操作
- 创建表
- 、create table mytable （列1 类型 约束，列2 类型 .....）;
- 复制表(包括结构和数据)
- 、create table emp_copy as select * from emp;
- 、creare table emp_copy1 as select empno,ename,sal,deptno from emp;
- 、create table emp_copy as select * from emp where 1=0;
// 后面where给的条件永远为false ，结果为只复制结构，没有数据。
七、表中数据的操作
- insert 操作
格式：insert into 表名（列名1，列名2，...） values （列1值，列2值，....）；
注：当插入的记录包括所有列时，不用再表名后注明列名
如：假设有表student 只包含两列为stu_id，stu_name时，插入记录
insert into student values （1001，'张三'）；
- 、insert 一条记录
Insert into emp（empno，ename，sal） values （1001，'张三'，1500）；
- 、insert 多条记录：后面接上select的结果（要求结果和要插入的表结构一致）
Insert into student select empno，ename from emp；
- update 更新数据
格式：update 表名 set 列名=此列新的值 where 过滤条件
- 、将7788号员工的薪水加800元
Update emp set sal =sal+800 where empno=7788；
b） 、改7788号员工的薪水为1800同时改job 为salsman
Update emp set sal = 1800,job = 'salsman' where empno=7788;
// 多个字段用"，"隔开
- delete 删除表中数据
格式：delete 表名 where 过滤条件；
- 、删除emp表中部门号为10的数据
delete emp where deptno=10；
注意：当表中数据存在被子表应用时，是不能直接删除的 ，如：
delete * from dept where deptno =10;//子表 emp中存在deptno=10的引用
但如果子表中有on delete 约束时 就可以 如：
create table student_wan(id number(10) primary key ,name varchar2(30) ,mid number(2), constraint stunwan_mid_fk foreign key (mid) references major_wan(mid) on delete set null);
// 删除部门10 后 子表中相关数据将设置为空。
- 事务的概念：
当开始执行DML操作时，就开始了一个事务。当事务开始后正在操作的数据是加锁的，别人不能同时操作（挂起）。
事务的结束动作就是 commit； DDL, DCL语句执行会自动提交 commit。
sqlplus 正常退出是会自动做提交动作的 commit;，当系统异常退出时，会执行回滚（撤销改动）操作 rollback;。
一个没有结束的事务，叫做活动的事务 (active transaction),活动的事务中修改的数据时，只有本会话(session)才能看见。
用处：操作过程中可以用savepoint保存状态，再用rollback to 返回设置的保存点。（前提是没有提交commit）
如：delete from emp；//删除emp中数据
Savepoint A；
Rollback to A；//数据恢复了
八、数据库的主要对象
- 视图：（很向软连接）
- 、创建视图：create [or replace] view_name as ......;
Create or replace view v_emp_copy as select ename ，sal ， from emp_copy；
// 取emp_copy表的ename和sal字段创建视图 v_emp_copy；
// 视图是虚的，如果没有就创建，如有就replace
注：Create or replace view v_emp_copy as select deptno，avg（sal）from emp_copy group by deptno；// 错;
// 视图的来源中的数据不是基表原始数据，而是计算后的数据，这样的视图叫复杂视图，复杂视图中avg（sal）不能作为列名，需要给出别名
如下：
Create or replace view v_emp_copy as select deptno，avg（sal） avg_sal from emp_copy group by deptno；
b） 、和查询表一样查询视图
Select * from v_emp_copy ；
c） 、删除视图:
Drop view view_name;
注：此时往表base table 基表）中插入数据时：表中没有变化，示图
中的数据发生改变，从示图中插数据时相对应的表会发生改变:往示图中
插数据时，会直接插进基表中，查看示图中的数据时，相当于就是执行创
建时的select语句。
简单示图：能进行DML操作。
复杂示图：来源于多张表，不能执行DML操作。
- 数据字典
数据字典的种类：(**表示table、view等对象
user_*****; 当前用户下的表、视图等对象
all_*****; 当前用户的和可访问的其他用户下的表、视图等对象
dba_*****; 数据库下所有用户下的表、视图等对象
注：dba_***包含all_***,all_****包含user_*****;
- 、用户名下的数据表：user_tables（只读的，系统自动管理）
Select count(*) from user_tables;//结果 n个表
Select table_name from user_tables where rownum<20;
//查询前19个表的表名
b） 、视图表：user_views（只读的，系统自动管理）
Select text from user_views where view_name='view_name' ;
// 查询指定视图对应的查询语句（视图本身就相当于一条查询语句）
- 索引
索引对系统的性能影响非常大，创建索引的目的就是为了加快查询的速度，让表中的数据在做insert时就进行排序，索引是DML语句触发的，系统自动运行，也可以强制使用索引。
- 、概念：FTS（full table scan）全表扫描，效率低
- 序列（sequence）
创建序列：create sequence 序列名；
(不带参数时默认为从 1 开始每次递增 1，oracle 中为了提高产生序列的
般一次性产生 20 个序列放入当前会话的序列池中备用以加快效率)
sequence 的参数：
increment by n 递增量
start with n 起始值
maxvalue n 最大值
minvalue n 最小值
cycle|no cycle 循环
cache n 缓存(第一次取时会一次取多少个 id 存起来)
如：create sequence mysequence start with 10 increment by 10；
对序列的操作：
Desc mysequence；查看序列类型
//查看当前用户下的所有序列，列出序列名，缓存大小，最大值
select sequence_name , cache_size , last_number from user_sequences ；
currval 当前的序列数
nextval 下一个序列数，它会自动给当前的序列加一个步长值。
drop sequence 序列名; //删除序列 sequence
- Oracle中约束
针对表中的字段进行定义的。
1、primary key（主键约束 PK） 保证实体的完整性，保证记录的唯一。
主键约束，唯一且非空，并且每一个表中只能有一个主键，有两个字段联合作为主键时，将两个字段组合在一起唯一标识记录，叫做联合主键。
主键约束的定义：
第一种定义形式：
create table test(c number primary key ); 列级约束
第二种定义形式：
create table test(c number , primary key(c) ) ; 表级约束
create table test(c1 number constraints pk_c1 primary key ); 对约束指定名字为
pk_c1
create table test(c number , c1 number , primary key (c ,c1) ) ; 用表级约束可以实现
联合主键
- foreign key（外键约束 FK）保证引用的完整性，外键约束，
外键的取值是受另外一张表中的主键或唯一值的约束，不能够取其他值，只能够引用 主键或唯一键的值，
被引用的表，叫做 parent table（父表），引用方的表叫做 child table（子表），
要想创建子表，就要先创建父表；记录的插入也是如此，先父表后子表；
删除记录，要先删除子表记录，后删除父表记录；
要修改记录，如果要修改父表的记录要保证没有被子表引用。
要删表时，要先删子表，后删除父表。(可以通过使用 cascade constraints 选项来 删除父表)
carete table parent(c1 number primary key ); 列级外键约束定义
create table child (c number primary key , c2 number references parent(c1));
表级外键约束定义
create table child( c number primary key , c2 number , foreign key(c2) references
parent(c1));
3、not null（非空约束NN）这是一个列级约束,在建表时,在数据类型的后面加上
not null ，也就是在插入时不允许插入空值。
例：create table student(id number primary key,name varchar2(32) not null,address
varchar2(32));
4、unique（唯一约束UK）
唯一约束，允许为空，要求插入的记录中的值是唯一的。
例：create table student(id number，name varchar2(32),address varchar2(32),primary
key (id),unique (address));
5、check 约束
检查约束，可以按照指定条件，检查记录的插入。check 中不能使用伪列，不能
使用函数，不能引用其他字段。
例：create table sal (a1 number , check(a1>1000));.
十、Oracle 中的伪列
1、伪列就像 Oracle 中的一个表列，但实际上它并未存储在表中。伪列可以从表中
查询，但是不能插入、更新或删除它们的值。
2、常用的伪列：rowid 和 rownum。
rowid：数据库中的每一行都有一个行地址，rowid 伪列返回该行地址。可以使用rowid 值来定位表中的一行。通常情况下，rowid 值可以唯一地标识数据库中的一行。
rowid 伪列有以下重要用途：
1）能以最快的方式访问表中的一行；
2）能显示表的行是如何存储的。
3）可以作为表中行的唯一标识。
如：SQL> select rowid, name from s_dept;
rownum：对于一个查询返回的每一行，rownum 伪列返回一个数值代表的次序。
rownum 伪列特点：
1）有个特点：要么等于 1要么小于某个值或小于等于某个值，不能直接等于某个
值, 不能大于某个值。
2）常用于分页显示。
返回的第一行的 rownum 值为 1，第二行的 rownum 值为 2，依此类推。通过使
用 rownum 伪列，用户可以限制查询返回的行数。
如：SQL>select * from s_emp where rownum<11; 从 s_emp 表中提取 10 条记录。
如：select rn,c1 from(select rownum rn, c1 from test where rownum <= 5)
where rn between 3 and 5; //提取第 3 条到第5 条的记录
注：between and 不能直接对rownum使用，但可以对象此句中那样将rownum作 为子 查询结果的一列（取别名），再在主查询中使用
如：select rownum ,table_name from user_tables where rownum between 3 and 5;
//此句永远显示返回数据为空
select rn,table_name from (select rownum rn,table_name from user_tables) where rn between 3 and 5;
//这句就行
如：找出第二名
select * from (select rownum rn , a.* from (select max(s.name),sum(grade) sg
from stu s,grade g
where s.id=g.sid
group by s.id
order by sg desc ) a)
where rn=2
posted on
