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