数据库学习总结-Marlon
目录
一、 ORACLE_简介........................................................................................................................................... 1
二、 ORACLE_简单查询.................................................................................................................................... 2
三、 ORACLE标量函数和算数运算................................................................................................................... 5
四、 ORACLE_多表查询.................................................................................................................................... 9
五、 ORACLE_列函数和分组........................................................................................................................... 10
六、 ORACLE_子查询..................................................................................................................................... 12
七、 ORACLE_表的更新操作........................................................................................................................... 13
八、 ORACLE_表与视图的管理....................................................................................................................... 14
u Oralce数据库发展
Oracle 8
Oracle 8i：i表示internet，标识着Oracle公司正式进军互联网。
Oracle9i
Oracle10g：g表示grid，即网络技术。
Oracle11g
u Oracle体系结构一
物理结构：
文件系统
控制文件
数据文件
日志文件
参数文件（不是数据库的组成成分）
u Oracel体系结构三
逻辑结构：
block 快
extent 盘区
segment 段
tablespace 表空间
datafile 数据文件
Ø SQL * Plus下的常用命令
u 连接到SQL*PLUS
sqlplus user/password[as sysdaba|sysoper]
sqlplus/nolog
u 启动数据库
startup mount 启动实例，打开控制文件，但不打开数据文件
startup nomount 只启动实例
u 关闭数据库
shutdown immediate
u 迫使每个用户执行为当前的SQL语句，立即断开连接
shutdown transactional
u 迫使用户执行完当前事务时，断开连接
shutdown abort
强制关闭数据库
u 常用SQL命令
show user：查看当前连接的用户
connect scott/tiger： 采用scott的用户名/tiger的密码连接数据库
desc table_name：查看tableName表结构
quit|exit：退出
disconnect：断开连接
clear screen：清屏，相当于Windows下的cls命令
select * from tab：列出当前用户下的所有表
@pata 执行pata制定的脚本文件
u Oracle常用基本数据类型
varchar2/varchar：变成字符串
char：定长字符串
Integer：整型
number(m,n)：数字型
smallint：短整型
float：浮点数
decimal：十进制数字（小数）
date：日期型
Ø SQL结构化查询语言（Structured Query Language）
u SQL分类
1. 数据定义语言（Data Definition Language，DDL）：create、alter、drop。
create table 表名; alter table add 新列名 数据类型; drop table 表名;
2. 数据操纵语言（Data Manipulation Language，DML）：insert、update、delete、select。
Insert into 表名(字段1,字段2…) values(值1,值2…);
update student set sage=22 where sno='200215'；(将学生200215的年龄改为22岁)
select distinct job from emp；去除重复行
3. 数据控制语言（Data Control Language，DCL）：commit work、rollback work。
u 查询雇员的所有信息
select * from emp；
*表示所有列
u 查询语句的格式
select *|列名 from 表名
u 查询雇员的编号，姓名，工资
select * from empno,ename,sal from emp;
u 查询所有职位
select job from emp;
select distinct job from emp;
distinct： 有区别的（去除重复行）
u 查询工资大于1500的雇员信息，列出编号，用户名，工资
select empno,ename,sal from emp where sal>1500;
u 带有where条件查询语句的基本格式
select *|列名 from 表名 where 条件;
u 比较运算符
大于：>
小于：<
等于：=
大于等于：>=
小于等于：<=
不等于：!=或者<>
u 限定查询 is null 和 is not null 的使用
n 查询每月可以得到奖金的雇员
select empno,ename,comm from emp where comm is not null;
select empno,ename,comm from emp where comm is not null and comm>0;
n 查询谁没有奖金
select empno,ename from emp where comm is null;
u 限定查询 and 的使用
n 查询工资大于1500，并且可以领取奖金的雇员
SQL> select empno,ename,sal,comm from emp where comm is not null and sal>1500;
u 限定查询 or 的使用
n 查询工资大于1500和可以领取奖金的雇员
select empno,ename,sal,comm from emp where sal>1500 or comm is not null;
n 查询没有奖金的雇员
select empno,ename,comm from emp where comm=0 or comm is null;
u 限定查询 使用not对条件整体取反
n 查询工资不大于1500并且不能领取奖金的雇员
select empno,ename,sal,comm from emp where sal<=1500 and (comm is null or comm=0);
select empno,ename,sal,comm from emp where not(sal>1500 or comm is not null);
u 限定查询 between...and...的使用
n 查询基本工资大于等于1500并且小于等于3000的雇员
select empno,ename,sal from emp where sal>=1500 and sal<=3000;
select empno,ename,sal from emp where sal between 1500 and 3000;
n 查询1981年雇佣的所有员工
select empno,ename,hiredate from emp where hiredate between'1-1月 1981' and '31-12月 1981';
注：日期格式 日-月 年，要匹配上
u 限定查询 字符串的比较
n 查询姓名是'SMITH'员工的所有信息
select * from emp where ename='SMITH';
select * from emp where ename='smith';
注：列值区分大小写
u 限定查询 in的使用
n 查询出编号7369,7499,7521的雇员的具体信息
select * from emp where empno=7369 or empno=7499 or empno=7521;
select * from emp where empno in(7369,7499,7521);
u 限定查询 not in的使用
n 查询出雇员编号不是369,7499,7521的雇员的具体信息
select * from emp where empno not in(7369,7499,7521);
u 限定查询 like的使用
n 查询雇员的名字第二个字符是M的雇员信息
select * from emp where ename like'_M%';
注：_匹配一个字符，%匹配0个多个字母（前缀或后缀的代表）
n 查询1982年入职的所有雇员的信息
select * from emp where hiredate like'%82';
n 查询工资中包含5的雇员信息
select * from emp sal where like'%5%';
u 对结果排序-Oracle By
n 查询员工工资大于1500员工的信息，按工资排序
select * from emp where sal>1500 order by sal;
n 查询工资大于1500员工的信息，按工资降序，按雇佣日期升序排序
select * from emp where sal>1500 order by sal desc,hiredate asc;
注：ASC升序，DESC降序，默认ASC。
ascending [?'sendi?]上升的，降序排列（descend [di'send] 的缩写）；
字符函数的使用
u 转换为大写字母
select upper('smith') from dual;
注：dual是公共表。upper ['?p?] 上面的，上部的
u 转换为小写字母
select lower('SMITH') from dual;
注：lower 放下
u 每个单词的字母变成大写，其余字母小写
select initcap('hello world') from dual
u 串连接(concat):可以使用"||"进行串连接
select concat('HELLO','WORLD') from dual;
select 'HELLO'||'WORLD' from dual;
u 求子串(SUBSTR)
select substr(ename,1,3) from emp;
注：含义为截取ename字段从第一个字符开始，总共三个字符
u 求长度(LENGTH)
select length(ename) from emp;
u 串替换(REPLACE)
select replace('HELLO WORLD','WOR','WEL') from dual;
结果：
REPLACE('HE
-----------
HELLO WELLD
注：第一个逗号是原字符，第二个逗号是原字符的一部分，第三部分是替换原字符那个部分。
u 要求显示所有雇员的姓名和姓名的后三个字符
select ename,substr(ename,length(ename)-2) from emp;
select ename,substr(ename,-3) from emp;
u 四舍五入(Round)
select round(789.536) from dual;
ROUND(789.536)
--------------
790
n 四舍五入(Round)：指定保留小数位数
select round(789.536,2) from dual;
----------------
789.54
n 四舍五入(Round)：对整数四舍五入
select round(789.536,-2) from dual;
ROUND(789.536,-2)
-----------------
800
u 截断小数位(TRUNC)
select trunc(789.536) from dual;
TRUNC(789.536)
--------------
789
n 截断小数位(TRUNC):指定保留小数位
select trunc(789.536,2) from dual;
TRUNC(789.536,2)
----------------
789.53
n 截断小数位(TRUNC):对整数截断
select trunc(789.536,-2) from dual;
TRUNC(789.536,-2)
-----------------
700
u 除空格（TRIM）
SQL> SELECT TRIM ( 55) FROM DUAL;
TRIM(55)
--------
55
u 取余数(MOD)
select mod(10,3) from dual;
u 日期函数
运算规律：
日期+数字=日期
日期-数字=日期
日期-日期=数字（天数）
u 查询当前日期
select sysdate from dual;
SYSDATE
-------------
10-3月 -12
u 显示10部门员工进入公司的星期数
select empno,ename,round((sysdate-hiredate)/7) from emp where deptno=10;
u 在指定日期上加入指定的月数之后的日期(ADD_MONTHS)
select add_months(sysdate,4) from dual;
ADD_MONTHS(SYS
--------------
10-7月 -12
u 求出给定日期范围的月数(months_between)
select empno,ename,months_between(sysdate,hiredate) from emp;
u 下一个给定的星期是那个日期(next_day)
select next_day(sysdate,'星期一') from dual;
注：表示在当前时间的前提下，下个礼拜一的具体日期
u 求出给定日期所在月份的最后一天日期(last_day)
select last_day(sysdate) from dual;
u 转换成字符串(to_char)
查询雇员号，姓名，以及入职的年份
select empno,ename,to_char(hiredate,'yyyy') from emp;
注：yyyy匹配年份，mm匹配月份，dd匹配日
u 使用to_char设置日期的显示格式
select empno,ename,to_char(hiredate,'yyyy-mm-dd') from emp;
u 使用fm去掉前导0
select empno,ename,to_char(hiredate,'fmyyyy-mm-dd') from emp;
EMPNO ENAME TO_CHAR(HI
------ ---------- ----------
7369 SMITH 1980-12-17
7499 ALLEN 1981-2-20
注：正常的值是1981-02-20，02前面的0去掉，变成了1981-2-20.
u 通过to_char设置数值的格式
select empno,ename,to_char(sal,'99,999') from emp;
说明：9代表1位数字
EMPNO ENAME TO_CHAR
---------- ---------- -------
7369 SMITH 800
7499 ALLEN 1,600
7521 WARD 1,250
注：把sal的值分成两个部分，后三位为一个部分，前两位为一个部分，用逗号隔开。
u 显示余额
select empno,ename,to_char(sal,'$99,999') from emp;
说明：$代表美元
u 转换成数字(to_number)
select to_number('123')+to_number('123') from dual;
TO_NUMBER('123')+TO_NUMBER('123')
---------------------------------
246
u 转换成日期(to_date)
select to_date('20##-07-31','yyyy-mm-dd') from dual;
TO_DATE('20##-
--------------
31-7月 -09
u 算数运算
n 查询每个员工的年收入
select empno,ename,(sal+comm)*12 from emp;
年收入=工资+奖金
n 查询每个员工的年收入
select empno,ename,(sal+comm)*12 income from emp;
n 查询每个员工的年收入
select empno,ename,(sal+NVL(comm,0))*12 income from emp;
注：NVL(comm,0)，当comm的值为null时，用0替换
u 格式：
select 列名1 别名1,......
from 表名1,表名2,......
where 条件
order by 列名
u 查询员工的编号，姓名，部门编号，部门名称
select empno,ename,emp.deptno,dname from emp,dept where emp.deptno=dept.deptno;
关联查询-为表命别名
u 查询员工的编号，姓名，部门编号，部门名称
select empno,ename,e.deptno,dname from emp e,dept d where d.deptno=e.deptno;
注：这里的e是emp的别名，d是dept的别名
关联查询-自连接
u 查询出每个雇员的姓名、工作、雇员的直接上级领导的姓名
select e.empno,e.ename,e.job,m.ename mname from emp e,emp m where e.mgr=m.empno;
注：mname是m.ename的别名
关联查询-多表关联
u 查询出每个雇员的姓名，工作，雇员的直接上级领导，以及部门名称
select e.ename,e.job,m.ename,d.dname from emp e,dept d,emp m
where e.mgr=m.empno and e.deptno=d.deptno;
注：当查询的字段间的关系涉及到n个表时，则n个表之间关联。
左、右外连接
u 查询员工编号，姓名，所在部门号，部门名称，将没有员工的部门也显示出来
select e.ename,d.deptno,d.dname from emp e,dept d
where e.deptno(+)=d.deptno;
注：(+)在左边，表示右连接，会列出所有右表中出现但是没有在左表中出现的行。
u 查询雇员的编号，姓名及其领导的编号，将没有领导的员工也列出来
select e.empno,e.ename,m.empno,m.ename from emp e,emp m
where e.mgr=m.empno(+);
注：(+)在右边表示左连接，会列出左表中出现但没有在右表中出现的行。
u 交叉连接(cross join)：用来长生笛卡尔积
select * from emp cross join dept;
u 自然连接(nutural join)：自动进行关联字段的匹配
select * from emp natural join dept;
u using子句：直接指定操作关联列
select * from emp join dept using(deptno)
u on子句：用户自己编写连接条件
select * from emp join dept on emp.deptno=dept.deptno;
u left join：左外连接
u right join：右外连接
Ø 常用的列函数
sum(expression) 求和
max(expression) 求最大值
min(expression) 求最小值
avg(expression) 求平均数
count(expression) 统计记录数
count(distinct colname) 统计去除重复行记录数
nvl(comm,0) 当comm为null时，用0替换
列函数的使用
u 查询员工的记录数，员工工资的总和，平均工资，最高工资，最低工资
select count(*) count_emp,sum(sal) sum_sal, max(sal) max_sal,min(sal) min_sal,
avg(sal) avg_sal from emp;
列函数的使用-对null的处理
u 查询所有员工的年收入
select sum(sal+comm) from emp;
SUM(SAL+COMM)
-------------
7800
注：当comm为null时，sal+comm是没有结果值的
select sum(sal)+sum(comm) from emp;
SUM(SAL)+SUM(COMM)
------------------
31225
select sum(sal+nvl(comm,0)) from emp;
SUM(SAL+NVL(COMM,0))
--------------------
31225
Ø 分组查询
语法格式
select 列名...,列函数
from 表名...
where 条件...
group by 列名...
order by 列名...
u 查询每个部门的人数
select deptno,count(*) from emp group by deptno;
规则：如果在select字句中，有不在列函数中的列，则该列一定要出现在group by之后。
分组查询having字句的使用
u 显示出平均工资大于2000的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno
having avg(sal)>2000;
SQL语句的书写顺序
select 列名,列函数
from 表名
where 条件
group by列名
having 条件
order by 列名
SQL语句的执行顺序
from
where
group by
having
select
order by
u 查询20,30部门的平均工资，并将平均工资大于2000的输出，输出结果按平均工资排序
select deptno,avg(sal) from emp
where deptno in(20,30)
group by deptno
having avg(sal)>2000
order by avg(sal);
在where子句中使用子查询
u 查询工资比7654雇员工资高的全部雇员信息
select * from emp where sal>(select sal from emp where empno=7654);
子查询-in的使用
u 查询和smith或jones在同一部门，同一职位工作的员工
select ename from emp where (deptno,job) in (select deptno,job from emp where ename in('SMITH','JONES'));
子查询-any的使用
u =any：与in操作符的效果一致
查询和smith或jones在同一部门，同一职位工作的员工
select ename from emp where (deptno,job)=any(select deptno,job from emp where ename in('SMITH','JONES'));
u >any：只要大于子查询中的任何一个值即可
select * from emp where sal>any
(select min(sal) from emp group by deptno);
u <any：只要小于子查询中的任何一个值即可
select empno from emp where sal<any (select min(sal) from emp group by deptno);
子查询all的使用
u >all：比最大的值大
select * from emp where sal>all
(select min(sal) from emp group by deptno);
注：select min(sal) from emp group by deptno结果为
MIN(SAL)
----------
950
800
1300
是每个部门当中的最少工资。
u <all：比最小的值小
u 创建表副本
create table myemp as select * from emp;
u 插入
u 到表中
insert的语法
insert into 表名(字段名1,字段名2,......)
values(值1,值2......)
u 为myemp中增加一条记录
insert into
myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7899,'张三','清洁工','7369','14-2月-2005',9000,300,40);
注：给定的值要和指定的字段数一致。
1.如果全部字段都插入值的话，那字段名可以省略。即：
insert into 表名 values(值1,值2,.......);
2.如果只是部分字段插入值的话，字段名又想省略的话，那么，其余没有数据插入的字段用null填补。
u to_date：字符型转换成日期型
u 批量插入记录
格式：
insert into 表名
select 字段列表 from 表名
where 条件
注：insert表和select表列数等都一致
例如：
create table test as select * from emp;
insert into test select * from emp;
u 修改记录
update表名set要修改的字段=新值，要修改的字段=新值，...
where条件
n 将雇员号为7896的雇员的奖金修改为100
update myemp set comm=100 where empno=7896;
注：执行修改操作时，一定要使用where来指定修改的条件，否则会改变表中的所有记录。
u 删除记录
delete from 表名 where 条件
n 删除雇员编号为7896的雇员信息
delete from myemp where empno=7896;
注：执行delete操作时，一定要使用where来指定修改的条件，否则会删除表中所有的记录。
u Oralce的事务处理
commit：提交事务
rollback：回滚操作
注：操作一旦执行了commit操作，就再也不能回滚操作了。
u oracle中的常见数据类型
varchar、varchar2：表示一个字符串，有长度限制，为255
number：
number(n)：表示一个整数，数字的长度是n，可以使用int
number(n,m)：表示一个小数，数字的长度为n，整数长度m-n，可以使用float
date：表示日期类型，日期要按照标准格式日期存放。
clob：大对象，表示大文本数据类型，可存放4G。
blob：大对象，表示二进制数据，最大可以存放4G，如电影，图片、歌曲
u 创建表
语法：
create table 表名(
字段名称1 字段类型[default 默认值],
字段名称1 字段类型[default 默认值],
............
字段名称1 字段类型[default 默认值],
);
u 复制表
create table 表名 as(子查询);
注：当子查询不成立时，如果1=2，则只复制表的结果，不复制表的数据。
u 删除表
语法：
drop table 表名;
u 修改表结构
n 增加一列
alter table 表名 add(列名称 列数据类型 default 默认值)
n 删除列
alter table 表名 drop column 列名称;
n 修改列的数据类型
alter table 表名称 modify(列的名称 列的类型 default 默认值);
注：1.如果是更改数据的长度，则要求更改时，长度不能小于当前表中数据所具有的最大长度。
2.如果是更改数据类型，则要求更改时，该列的所有记录值都为空。
u 修改表结构
n 为表重命名
rename 旧表名 to 新表名;
注：这是oracle特有的操作
n 将myemp表改名为iemp
SQL> rename myemp to iemp;
表已重命名。
n 截断表
truncate table 表名;
意义：清空表中的所有数据，并且立即释放资源，该操作是不可回滚。
u 约束-约束的分类
主键约束：表示一个唯一的标识，本身不能为空
唯一约束：列值不允许重复
检查约束：检查一个列的内容是否合法
非空约束：不能为空值，如用户不能为空(no null)
外键约束：在两张表中进行约束的操作
n 主键约束(primary key)
主键约束一般在id上使用，而且本身已经默认了不能为空，主键约束可以在建表的时候指定
create table person(
pid varchar2(18) primary key,
name varchar(30),
age number(3),
sex varchar2(2) default'男'
);
使用constraint指定
constraint [k?n'streint] 约束；强制
create table person(
pid varchar2(18),
name varchar(30),
age number(3),
sex varchar2(2) default'男'
constraint person_pid_pk primary key(pid);
);
n 非空约束(not null)
create table person(
pid varchar2(18) primary key,
name varchar(30) not null,
age number(3),
sex varchar2(2) default'男'
);
n 唯一约束(unique)
create table person(
pid varchar2(18) primary key,
name varchar(30) unique not null,
age number(3),
sex varchar2(2) default'男'
);
u 视图
概念：一个视图实质是封装了一条复杂的SQL语句
n 创建视图
语法：
create view 视图名称 as 子查询
n 创建部门20员工的雇员信息
create view empv20 as select * from emp where deptno=20;
注：当创建视图以后，可以像操作表一样操作视图。
注：视图的操作会影响到表的操作。
with check option 不能更新创建视图的条件
with read only 创建只读视图
n 删除视图
语法：
drop view 视图名称
注：当删除视图所在的表时，则视图也不能被使用
u 序列
创建序列语法：sequence ['si:kw?ns]
create sequence 序列名称;
create sequence myseq;
序列的操作：
nextval：取得序列的下一个内容
currval：取得当前序列的内容
n 创建表验证序列的操作
create table testseq(next number,curr number);
insert into testseq values(myseq.currval,myseq.nextval);
n 创建序列指定每次增长的增量
create sequence myseq increment by 2;
注：每次增长2
n 创建序列指定开始的序列，默认的序列从1开始。
create sequence myseq increment by 2 start with 10;
注：序列从10开始，每次增长2
n 创建一个序列1，3，5，7，9.
create sequence myseq maxvalue 9 increment by 2
start with 1 cache 2 cycle;
注：序列从1开始，每次增长2，最大值为9，循环两次。
数据库学习总结报告SQLServer是一个关系数据库管理系统，它最初是由MicrosoftSybase和Ashton-Tate三家…
电子商务数据库技术----SQLServer篇学习总结------60951P南信院这学期我们学习了数据库的SQLServer篇，…
数据库学习总结XX班姓名学号摘要：在当今信息化的社会里，数据库可以说已经融入到我们生活的方方面面中，如交通运输、银行金融、工商企业…
这学期我们学习了数据库应用教程这门课，以前并不知道这门课是干什么的，也不懂得什么是数据库，通过这一学期的学习，虽然了解的不是非常多…
数据库原理及应用学习体会通过一学期的学习，对《数据库原理及应用》基本有利一个整体性的了解和掌握，本书按顺序可以分为4个部分：第一部…
软件070107301024杨彦妍在学习数据库之前一直觉得数据库没有什么深入研究的价值，觉得无非是将数据存起来进行增删改查的操作就…
db2数据库1、打开数据库db2connectto数据库名;2、查看数据库中有哪些表db2listtables;3、查看数据库中的…
数据库原理及应用学习体会通过一学期的学习，对《数据库原理及应用》基本有利一个整体性的了解和掌握，本书按顺序可以分为4个部分：第一部…
这学期我们学习了数据库应用教程这门课，以前并不知道这门课是干什么的，也不懂得什么是数据库，通过这一学期的学习，虽然了解的不是非常多…
数据库学习总结XX班姓名学号摘要：在当今信息化的社会里，数据库可以说已经融入到我们生活的方方面面中，如交通运输、银行金融、工商企业…