创
ORACLE| ORACLE基础语法汇总
1.建表的语法
create table 表名(
字段名(列名) 字段类型 [约束 默认值],
.....
字段名(列名) 字段类型 [约束 默认值]
);
注意:1.表名,字段名,关键字大小写忽略
2.表名,字段名不能使用关键字
3.表名,字段名不可以使用数字开头,中间不能出现特殊符号
4.表名,字段名长度不能超过30个字符
5.表名,字段名定义需要有含义
2.创建一个用户表 t_user
|-姓名name,字符串
|-性别gender,字符串
|-年龄age,数字型
create table t_user(
name varchar2(30),
gender varchar2(3),
age number
);
--注意: desc命令必須在命令窗口中執行.在sql window中不能執行
desc t_user;
3.Oracle中常见的数据类型
字符串
|-varchar2(length):可变长的字符串,length定义最长字符串的字节数.
length最大值位4000,节省空间,查询效率低
|-char(length):定长的字符串,length定义最长的字符串的字节数,最大值2000
浪费空间,查询效率高
|-varchar(length):等价于varchar2(length),varchar2是Oracle独有的.
数字类型
|-number(p,s):p表示定义数字的长度(不包含小数点),s表示小数点后面的尾数
|-定义商品的价格 number(5,2),表示小数点前面有3个数字,后面有两个数字
123.23 12.23(合法,在精度里面) 1234.10(不合法)
|-number:表示描述整数
日期类型
|-date:七个字节,如果是英文环境 DD-MOR-RR "11-JUN-15" 如果是中文环境 15-7月-15
4.删除表 drop table 表名
drop table test2;
create table test2(
str varchar(200)
);
5.创建表员工表 t_emp
|-员工id 长度最长为11的整数
|-员工姓名 name
|-员工工资 salary 12345.12
|-生日 birth 日期
|-职位 job 字符串
|-员工性别 gender
create table t_emp(
id number(11),
name varchar2(30),
salary number(7,2),
birth date,
job varchar2(30),
gender char(3)
);
--使用default设置字段的默认值
drop table t_emp;--将性别默认值为M
create table t_emp(
gender char(3) default 'm' /*单引号表示字符串*/
);
--使用not null设置字段值不能为空
create table t_emp(
name varchar2(30) not null
);
6.使用ALTER修改表结构
--添加新的字段
语法:alter table 表名 add (新的字段 字段类型,...);
--想t_emp表中追加deptno的字段
alter table t_emp add(deptno number(11));
--删除字段
语法:alter table 表名 drop column 字段名;
--删除t_emp表中的gender字段
alter table t_emp drop column gender;
--修改列名
语法:alter table 表名 rename column 旧列名 to 新列名;
--将t_emp表中的deptno修改为dept_no
alter table t_emp rename column deptno to dept_no;
--修改字段类型
语法:alter table 表名 modify (列名 新类型,...);
--将t_emp表的salary的类型修改为number(5,2)
alter table t_emp modify (salary number(5,2));
7.truncate:删除表中的数据
语法:truncate table 表名;
truncate只是清空表中的数据,但是保留表的结构
drop:将表结构删除
8.DML语句:操作表数据
|-8.1 插入数据 insert into
|-语法 INSERT INTO 表名 [(字段名,...)] VALUES(值,...)
--注意:如果是向表中的所有字段添加数据时,可以省略字段名
--向t_emp表中插入数据
--开发中推荐使用明确字段名
insert into t_emp(id,name,salary,birth,job,dept_no)
values(1001,'yves',123.23,sysdate,'开发',10);
--不便于维护
insert into t_emp values(1003,'yvesHe',123.45,sysdate,'测试',20);
commit;--提交数据
查询表中的所有数据:select * from 表名;-- *通配符,表示所有的字段
|-8.2 删除数据 delete
|-语法:delete from 表名 [where 过滤条件];
delete from t_emp; --注意：将表中的数据全部删除
/*删除数据通常使用where条件
*/
//删除id=1001的用户
delete from t_emp where id=1001;/*where是过滤条件*/
delete和truncate的区别？
|-1.delete属于DML语句，需要事务的支持
|-2.truncate属于DDL语句，无需事务的支持
|-3.delete需要回滚内存空间，truncate无需回滚内存空间
|-4.delete性能要低于truncate
|-5.delete保留高水位线，truncate删除高水位线
/*
DML语句需要事务的管理：
commit提交数据
rollback回滚数据
*/
|-8.3 修改记录 update
|-语法：update 表名 set 字段名=值[,....] [where 过滤条件];
update t_emp set name='jerry';
//将id=1001的用户名改为 jerry,工资改为 888
update t_emp set name='jerry',salary=888 where id=1001;
9.事务控制语句(配合DML语句一起使用)
commit:提交事务
rollback:事务回滚
savepoint 事务保存点
create table temp( id number);
insert into temp values(1);
insert into temp values(2);
savepoint A;--设置事务的保存点
insert into temp values(3);
insert into temp values(4);
insert into temp values(5);
savepoint B;
insert into temp values(6);
insert into temp values(7);
savepoint C;
insert into temp values(8);
rollback to B;--回滚到保存点B,数据到5
rollback to A;--可以成功,数据到2
/*rollback to C 报错,事务已经到了B,C不存在了*/
select * from temp;
❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤
1.补充的函数
coalesce(参数列表):返回参数列表中第一个非空参数,最后一个参数通常为常量
--案例:
年终提成:
|-1.如果员工的comm不为空,发comm
|-2.如果员工的comm为空,发工资的一半
|-3.如果sal和comm为空,发100安慰
select ename,sal,comm,coalesce(comm,sal*0.5,100) comms
from emp;
case语句:类似于java中的switch语句
case 表达式
when 值 then 执行的语句 /*没有逗号*/
...
when 值 then 执行的语句
else 执行语句 /*类似于switch语句中的default*/
end;
--加薪
|-ANALYST职位,提高10%
|-SALESMAN,提高5%
|-CLERK,提供2%
select ename,sal,job,
case job
when 'ANALYST' then sal*1.1
when 'SALESMAN' then sal*1.05
when 'CLERK' then sal*1.02
else sal
end new_sal
from emp;
decode(判断条件,匹配1,值1,匹配2,值2,...,默认值):函数,是case语句的简写
--加薪
|-ANALYST职位,提高10%
|-SALESMAN,提高5%
|-CLERK,提供2%
select ename,sal,job,
decode(job,'ANALYST',sal*1.1,'SALESMAN',sal*1.05,'CLERK',sal*1.02,sal) new_sal
from emp;
2.单行函数:一条数据经过函数处理获得一条结果.
组函数:多条记录经过组函数的处理只会得到一条结果
常见的组函数
|-count(表达式):计数
--统计员工表中有多少个员工
select count(*) from emp;
select count(ename) from emp;
--员工表中有多少个员工有提成
select count(comm) from emp;--可以进行空值处理
--员工表中有多少个职位
select count(distinct job) from emp;
|-max():最大值,min()最小值,avg()平均值,sum()求和
--员工表中最高的工资
select max(sal),min(sal),sum(sal),count(ename),avg(sal) from emp;
--求平均值是需要注意空值处理
select sum(comm),avg(nvl(comm,0)),count(ename) from emp;
--获得每个职位的平均工资,最大值,最小值
group by:分组的关键子
select job,max(sal),min(sal),avg(sal)
from emp
group by job;--表示根据职位进行分组
--获得每个部门的平均工资,最大值,最小值
select deptno ,max(sal),min(sal),round(avg(sal)) avg_sal
from emp
group by deptno
order by avg_sal;
注意:如果select后面出现了组函数,那么其他内容必须出现在 group by 子句中.
--获得平均工资大于2000的职位
select job,round(avg(sal)) avg_sal
from emp
group by job
having round(avg(sal))>2000;/*having是对分组后的结果进行过滤,不能使用别名*/
having和where的区别
|-where是对表中的数据进行过滤,先执行
|-having是对分组后的结果进行过滤,如果有where先执行where然后分组
--获得管理者下面的员工的平均工资, 最低工资不能少于800,没有管理者的不参与统计
select mgr,round(avg(sal)),min(sal)
from emp
where mgr is not null /*1.where先执行*/
group by mgr /*2.分组*/
having min(sal)>800; /*3.对分组的结果进行过滤*/
--获得各个部门中工资大于1500的员工人数(先过滤再分组)
select deptno,count(*)
from emp
where sal>1500 /*先过滤*/
group by deptno;
--平均工资最高的部门
select max(avg(sal))
from emp
group by deptno;
--那些职位的人数超过2个人,对人数进行排序
select job,count(ename) nums
from emp
group by job
having count(ename)>2
order by nums ; /*注意order by需要保存到最后面*/
--薪水最高的员工
select max(sal) from emp;--获得最高薪水
select * from emp where sal=5000;
子查询:在主查询执行之前执行,将执行的结果作为主查询的一个判断依据
select ename,sal,deptno
from emp
where sal=(select max(sal) from emp);
注意:1.子查询需要定义在括号当中
2.子查询通常定义在条件判断的右边
3.在子查询中不建议使用 order by
--查询谁的薪水比 BLAKE 高
select ename,sal
from emp
where sal>(select sal from emp where ename='BLAKE') ;/*避免值过多*/
/*如果子查询的结果为null 那么结果为null*/
select ename,sal
from emp
where sal>(select sal from emp where ename='yves') ;
--子查询中多行比较符
in :等于列表中的任何一个
any:和子查询结果中的任意一个值进行比较
all:和子查询结果中的所有值进行比较
--获得各个部门中工资最高的员工
select ename,sal,deptno
from emp
where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);
--谁的工资比20号部门员工的工资都高
select ename,sal,deptno
from emp
where sal>all(select sal from emp where deptno=20) ;/*all大于最大值*/
select ename,sal,deptno
from emp
where sal>any(select sal from emp where deptno=20) ;/*any 大于最小值*/
--获得SIMTH 的同部门的员工,除了SIMTN以外
select ename,deptno
from emp
where deptno in (select deptno from emp where ename='SMITH') /*in 可能其他部门有同名的人*/
and ename!='SMITH';/*ename <> 'SMITH'*/
关联子查询:子查询不再是一个独立的SQL语句,依赖外部的主查询传入的参数.
--那些员工的薪水比公司的平均薪水低(非关联子查询实现)
select ename,sal
from emp
where sal<(select avg(sal) from emp);
--那些员工的薪水比本部分的平均薪水要低
select ename,sal,deptno
from emp e /*为当前的emp表取别名*/
where sal<(select avg(sal) from emp where deptno=e.deptno);
--有下属的员工
select empno,ename,deptno
from emp e
where exists (select 1 from emp where mgr=e.empno);
注意: exists:如果子查询的有结果返回,true,如果没有结果返回为false,所以exists不关心
子查询的结果,通常返回一个常量 1,获得定义一个任何字段
select * from emp;
select distinct mgr from emp where mgr is not null;
哪些人不是下属
select empno,ename,deptno
from emp e
where not exists (select 1 from emp where mgr=e.empno);
select empno,ename
from emp
where empno not in (select distinct mgr from emp where mgr is not null);
注意:not in 当中如果有null那么结果为null
集合的操作
union 去掉重复记录
create table ta( str number);
insert into ta values(4);
insert into ta values(3);
insert into ta values(1);
insert into ta values(2);
create table tb(stb number);
insert into tb values(5);
insert into tb values(3);
ta={4,3,1,2};
tb={5,3};
select * from ta
union /*合集*/
select * from tb;
ta union tb={1,2,3,4,5};
select * from ta
union all
select * from tb;
ta union all tb={4,3,1,2,5,3};
union和union all的区别
|-union:去重复,排序
|-union all:不重复也不排序.(推荐)
select * from ta
intersect /*交集*/
select * from tb;
ta intersect tb={3};
select * from ta
minus /*差集*/
select * from tb;
ta minus tb ={1,2,4};
tb minus ta ={5};
连接查询:查询多个视图或者数据表时成为连接查询
|-内连接:返回所有符合连接条件的记录
|-外连接:返回符合连接条件的记录,同时返回不符合连接条件的记录(左外连接,右外连接)
|-全外连接:左外连接和右外连接的结果总和
|-自连接:数据的来源是一个表表,关联条件来自同一个数据表或者视图中
--查询员工的姓名和所在部门的名称和地区
--采用内连接: 表1 [inner] join 表2 on 关联的条件
select e.ename,d.dname,d.loc,d.deptno
from emp e join dept d on e.deptno=d.deptno;
--inner通常被省略,建议在写关联查询时提供表的别名
select e.ename,d.dname,d.loc,d.deptno
from emp e inner join dept d on e.deptno=d.deptno;
select e.ename,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno;
--使用外外连接:
|-左外连接: 表1 left outer join 表2 on 关联条件
|-结果集的组成:匹配的数据+表1中没有匹配上的数据(结果集中保留表1的完整性)
|-右外连接: 表1 right outer join 表2 on 关联条件
|-结果集的组成:内连接的结果集+表2中没有匹配上的数据(保留表2的完整性)
|-左左左全,右右右全
--获得没有员工的部门
select e.ename,d.dname,d.loc,d.deptno
from dept d left outer join emp e on e.deptno=d.deptno
where e.ename is null;
select e.ename,d.dname,d.loc,d.deptno
from dept d ,emp e
where e.deptno(+)=d.deptno;/*保留 d 表的完整性早期的外连接*/
查询并显示SALES部门的职位
select distinct e.job
from emp e join dept d
on e.deptno=d.deptno
where d.dname='SALES';
查询部门的名称,所在地,员工数量,平均工资
select d.dname,d.loc,e.avg_sal,e.count_e
from dept d
join
( select deptno,avg(sal) avg_sal,count(ename) count_e
from emp
group by deptno
) e
on d.deptno=e.deptno;
--自连接
查询员工以及上司的名称
select e1.ename employee,e2.ename lidao
from emp e1,emp e2
where e1.mgr=e2.empno;
--全外连接表1 full outer join 表2
|-结果集:内连接结果集+左外连接+右外连接
select e.id,e.name,d.name
from temp_emp e full outer join temp_dept d
on e.d_id=d.id;
create table temp_emp(id number ,name varchar2(30),d_id number);
insert into temp_emp values (1001,'yves',1);
insert into temp_emp values(1002,'yvesHe',2);
insert into temp_emp values(1003,'lucy',3);
insert into temp_emp values(1004,'outMan',null);
create table temp_dept(id number,name varchar2(30));
insert into temp_dept values(1,'开发');
insert into temp_dept values (2,'财务');
insert into temp_dept values (3,'测试');
insert into temp_dept values (4,'前台');
3.表的复制:如果需要对表中的数据进行删除和修改,建议通过复制表中的数据来对数据进行操作
create table 表名 as 查询语句;
--将emp表中的数据复制到t_emp表中
create table t_emp
as
select * from emp;
--只需要表的结构
--将emp表的结构复制到t_emp表中
create table t_emp
as
select * from emp
where 1=0;/*提供一个否定条件*/
--只复制一部分数据
--将emp表中部门10的员工的数据复制到t_emp表中
create table t_emp
as
select * from emp
where deptno=10;
--将emp表中的员工姓名,工资,年薪保存到t_emp表中
create table t_emp
as
select ename,sal,sal*12 year_sal /*如果字段中出现函数或者计算需要提供别名*/
from emp;
--统计emp表中部门的人数,将部门编码和人数保存到t_emp表中
create table t_emp(did,ecount)
as
select deptno,count(ename)
from emp
group by deptno;
注意:表的复制只会复制表中的数据,不会复制表中的约束
4.伪列
select rowid ,e.* from emp e;
rowid:是一个伪列,Oracle独有的.每一条记录的rowid的记录是唯一的
--删除表中的重复记录
select e.*,d.*
from emp e,dept d
where e.deptno=e.deptno;/*连接条件不合理:产生笛卡尔积*/
笛卡尔积产生的原因
|-1.没有连接条件
|-2.连接条件无效
避免笛卡尔积的方法:提供合理的连接条件
迪卡集的作用:临时获得大量的测试数据.
create table t_emp
as
select e.*
from emp e,dept d
where e.deptno=e.deptno;
select rowid,e.* from t_emp e;
--删除重复的记录
delete from t_emp where rowid not in(
select max(rowid) from t_emp group by empno,ename,job,mgr );
select * from t_emp;
rownum:伪列,表示行号
select rownum ,e.* from t_emp e;
--显示t_emp表中前五条记录
select rownum ,e.* from t_emp e where rownum<=5; /*rownum最小值从1开始*/
--显示t_emp表中第3条到第9条记录
select rownum ,e.* from t_emp e
where rownum>=3 and rownum<=9; /*查不出任何结果*/
--在Oracle中,使用rownum进行分页查询
select ee.*
from
(select rownum r,e.* from t_emp e ) ee
where ee.r>=3 and ee.r<=9;
--将t_emp表中按照工资进行排序,显示出第 5条到第10条
select ee.*
from
(select rownum r,e.* from (select * from t_emp order by sal) e ) ee
where ee.r>=5 and ee.r<=10;
5.约束条件
|-主键约束(PK):primary key
|-一张表中只能有一个主键约束
|-主键约束的特点:不能为空,具有唯一性
|-关系型数据库中使用主键约束唯一标识一行数据
|-主键约束分为两类
|-行级约束
|-表级约束
--建表时定义主键约束
create table 表名(
字段名 类型 primary key , --列级约束,只能作用一个字段
...
);
--建表时定义表级约束
create table 表名(
字段1 类型,
字段2 类型,
...,
constraint 表名_字段名_PK primary key(字段1,字段2) /*字段 1和字段 2 联合作为主键 */
);
非空约束(NN):not null
--语法
create table 表名(
字段名 类型 not null
....
);
--定义非空约束名称
create table 表名(
字段名 类型 constraint 表名_字段名_NN not null,
...
);
注意:非空约束只有列级约束,没有表级约束
唯一性约束(uk):unique:值必须唯一,可以为null
--列级约束
create table 表名(
字段名 类型 unique,
....
);
--表级约束
create table 表名(
字段1 类型,
字段2 类型,
...,
constraint 表名_字段名_uk unique(字段1,字段2)
);
检测约束(CK):check
--列级约束
create table 表名(
字段名 类型 check(字段名 in(值1,...)),
....
);
--表级约束
create table 表名(
字段1 类型,
字段2 类型,
...,
constraint 表名_字段名_ck check(字段1 in(值1,...))
);
--提供一个用户表 t_user
create table t_user(
id number constraint USER_ID_PK primary key,/*表示用户编号,主键*/
name varchar2(30) constraint USER_NAME_NN not null,
gender char(3) default 'F',
constraint USER_NAME_UK unique(name),
constraint USER_GENDER_CK check(gender in('F','M'))
);
user_constraints:数据库提供的查看表中约束条件的
--查询t_user表中的约束条件
select CONSTRAINT_TYPE,CONSTRAINT_NAME from user_constraints where table_name ='T_USER';
insert into t_user values(1001,'yves','F');
insert into t_user values(1001,'yvesHe','M');--报错,违法主键约束
预习:外键约束
❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤
1.关于tab3表内容：
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要生成下列结果, 该如何写sql语句?
/*建表*/
create table tab3(
playday date,
result varchar2(3)
);
insert into tab3 values(to_date('2005-05-09','yyyy-MM-dd'),'胜');
insert into tab3 values(to_date('2005-05-09','yyyy-MM-dd'),'胜');
insert into tab3 values(to_date('2005-05-09','yyyy-MM-dd'),'负');
insert into tab3 values(to_date('2005-05-09','yyyy-MM-dd'),'负');
insert into tab3 values(to_date('2005-05-10','yyyy-MM-dd'),'胜');
insert into tab3 values(to_date('2005-05-10','yyyy-MM-dd'),'负');
insert into tab3 values(to_date('2005-05-10','yyyy-MM-dd'),'负');
要求:
胜 负
2005-05-09 2 2
2005-05-10 1 2
方法1:
select playday,
sum(
case
when result='胜' then 1
else 0
end
) 胜,
sum(
case
when result='负' then 1
else 0
end
) 负
from tab3
group by playday;
方法2:
select playday,
sum(
decode(result,'胜',1,'负',0)
) 胜,
sum(
decode(result,'负',1,'胜',0)
) 负
from tab3
group by playday;
2.表中有A B C三列,用SQL语句实现：
当A列大于B列时选择A列否则选择B列，
当B列大于C列时选择B列否则选择C列。
/*建表*/
建立一个表:tab4里面有三个字段:A,B,C
方法1:
select
case
when A>B then A
else B
end resultAB,
case
when B>C then B
else C
end resultBC
from tab4;
sign比较大小的函数
select sign(-22),sign(100),sign(0) from dual;
/*结果为:-1,1,0*/
方法2:
select decode(sign(A-B),1,A,B) resultAB,decode(sign(B-C),1,B,C) from tab4;
3.一个日期判断的sql语句
请取出tab5表中日期(SendTime字段)为当天的所有记录?
(SendTime字段为datetime型，包含日期与时间)
/*建表:tab5*/
select * from tab5 t
where to_char(t.datetime,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd');
4.有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示
语文70分，数学80分，英语58分，
请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：
大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。
显示格式：
语文 数学 英语
及格 优秀 不及格
/*建表:tab6*/
create table tab6(
语文 number,
数学 number,
英语 number
);
insert into tab6 values(70,80,58);
方法1:case语句
select
(case when 语文>=80 then '优秀'
when 语文>=60 then '及格'
else '不及格' end) 语文,
(case when 数学>=80 then '优秀'
when 数学>=60 then '及格'
else '不及格' end) 数学,
(case when 英语>=80 then '优秀'
when 英语>=60 then '及格'
else '不及格' end) 英语
from tab6;
方法2:decode
select
decode(sign(语文-80),1,'优秀',0,'优秀',-1,
decode(sign(语文-60),1,'及格',0,'及格',-1,'不及格')) 语文,
decode(sign(数学-80),1,'优秀',0,'优秀',-1,
decode(sign(数学-60),1,'及格',0,'及格',-1,'不及格')) 数学,
decode(sign(英语-80),1,'优秀',0,'优秀',-1,
decode(sign(英语-60),1,'及格',0,'及格',-1,'不及格')) 英语
from tab6;
5用一条SQL语句 查询出每门课都大于80分的学生姓名
name kecheng fenshu
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90
/*建表:*/
create table tab7(
name varchar2(30),
course varchar2(39),
score number
);
insert into tab7(name,course,score) values('张三','语文',81);
insert into tab7(name,course,score) values('张三','数学',75);
insert into tab7(name,course,score) values('李四','语文',76);
insert into tab7(name,course,score) values('李四','数学',90);
insert into tab7(name,course,score) values('王五','语文',81);
insert into tab7(name,course,score) values('王五','数学',100);
insert into tab7(name,course,score) values('王五','英语',90);
select distinct name from tab7 where name not in(
select distinct name from tab7 where score<80);
1.标间约束
|-主键约束(PK):primary key
|-作用:唯一标识一行数据
|-特点:不能为空,唯一
|-唯一性约束(UK):unique
|-作用:字段值不能重复
|-特点:可以为空
|-非空约束(NN):not null
|-作用:字段值不能为空
|-注意:非空约束不能定义表级约束
|-检测约束(CK):check
|-作用:字段值只能在指定的内容中选择
2.外键(foreign key):在数据库内部,使用外键来描述表与表之间的关系
有外键列的表称为从表(子表),提供参考值的表称为主表(父表)
外键字段可以参考主表的主键约束或者唯一性约束的字段
用户表和订单表
|-一个用户肯能有多个订单,为了在数据库中描述关系,在订单表中提供外键
--用户表
create table t_user(
id number primary key,
name varchar2(30) not null
);
--订单表
create table t_order(
oid number primary key,/*订单编号*/
account number(7,2) not null,/*总金额*/
pronums number not null,/*货品总数*/
orderday date,/*下单日期*/
u_id number references t_user(id) /*定义表级的外键约束,参考用户表的中id值*/
);
/*注意*/
|-1.建表时:先建主表,在建立从表
|-2.插入数据时,先操作主表,再操作从表
|-3.删除数据时,如没有级联操作,必须先删除子表中的数据,在删除主表中的数据
|-4.删除表结构,要先删除子表
/*插入数据*/
insert into t_user values(1001,'yves');
insert into t_user values(1002,'yvesHe');
insert into t_order
values(231001,250.25,4,to_date('2015-05-29','yyyy-MM-dd'),1001);
insert into t_order
values(231002,1250.25,4,to_date('2015-05-30','yyyy-MM-dd'),1001);
--插入下列数据出
--ORA-02291: 违反完整约束条件 (SCOTT.SYS_C004141) - 未找到父项关键字
--原因:子表中的外键列必须参考父表中的主键值,必须存在的值
insert into t_order
values(231002,1250.25,4,to_date('2015-05-30','yyyy-MM-dd'),1003);
--删除数据
--执行下条SQL语句出现
--ORA-02292: 违反完整约束条件 (SCOTT.SYS_C004141) - 已找到子记录
--原因:主表的当前数据被从表参考.要先删除从表中的数据
delete from t_user where id=1001;
delete from t_order where u_id=1001;
--删除表结构
--执行下条SQL语句出现
--ORA-02449: 表中的唯一/主键被外键引用
--原因:当前表的主键被作为其他表的外键
drop table t_user;
--表级约束定义外键
--用户表
create table t_user(
id number primary key,
name varchar2(30) not null
);
--订单表
create table t_order(
oid number primary key,/*订单编号*/
account number(7,2) not null,/*总金额*/
pronums number not null,/*货品总数*/
orderday date,/*下单日期*/
u_id number, /*定义表级的外键约束,参考用户表的中id值*/
constraint order_user_id_fk foreign key(u_id) references t_user(id)
);
级联删除:
1.默认情况下,必须先删除从表的数据在删除主表的数据
--如何删除表中的约束
alter table t_order drop constraint order_user_id_fk;
on delete set null:当主表的数据被删除时,子表参考的值设为null
--重新为表t_order添加外键约束
alter table t_order add constraint order_user_id_fk
foreign key(u_id) references t_user(id) on delete set null;
先删除父表
delete from t_user where id=1001;
子表t_order中对应的u_id字段的值被置空
on delete cascade :当主表的数据被删除时,子表中对应的值也被删除
--重新为表t_order添加外键约束
alter table t_order add constraint order_user_id_fk
foreign key(u_id) references t_user(id) on delete cascade;
先删除父表
delete from t_user where id=1001;
子表t_order中对应的u_id的整条记录被删除
drop table t_order;
drop table t_user;
--用户表
create table t_user(
id number primary key,
name varchar2(30) unique /*唯一性约束*/
);
--订单表
create table t_order(
oid number primary key,/*订单编号*/
account number(7,2) not null,/*总金额*/
pronums number not null,/*货品总数*/
orderday date,/*下单日期*/
u_name varchar2(30), /*定义表级的外键约束,参考的主表的唯一性约束的字段*/
constraint order_user_id_fk foreign key(u_name) references t_user(name)
);
用户表和身份证
用户表 t_person
身份证 t_idcard
一个用户只能有一张身份证,一张身份证对应一个用户
1对1的描述的策略:唯一外键
create table t_person(
id number primary key,
name varchar2(30)
);
create table t_idcard(
id number primary key,
serial number, /*有效期 */
p_id number ,
constraint idcard_person_id_fk foreign key(p_id) references t_person(id),/*外键*/
constraint idcard_p_id_uk unique(p_id)/*唯一性约束*/
);
drop table t_idcard;
drop table t_person;
1对1的描述的策略:共享主键
create table t_person(
id number primary key,
name varchar2(30)
);
create table t_idcard(
id number primary key references t_person(id),/*id即为外键又为主键*/
serial number /*有效期 */
);
多对多的关系:必须通过第三张表进行描述
--学生表
create table t_stu(
id number primary key,
name varchar2(30)
);
--课程表
create table t_course(
id number primary key,
name varchar2(30),
score number /*总的学分*/
);
学生表和课程表示多对多,提供第三张关系表
create table t_s_c(
id number primary key,
s_id number references t_stu(id),
c_id number references t_course(id),
score number /*某个学生实际的成绩*/
);
约束的建立时机:为了更好的对约束条件进行管理,建议对约束进行命名.在建表之后在提供约束
--添加约束
alter table 表名 add constraint 自定义约束名 约束条件;
注意:非空约束,不能定义表级约束
alter table 表名 modify (字段名 字段列席 not null );
--删除约束
alter table 表名 drop constraint 指定的约束名;
3.数据库设计的三范式
|-在数据设计时需要遵守的规则,关系型数据库中对这类规则称为范式.
第一范式:每个字段要确保原子性.每个字段不能再进行分解
第二范式:在第一范式的基础上,确保表中的每一列和主键相关.
第三范式:在第二范式的基础上,却表表中的字段和主键是直接相关而不是间接相关
4.作业
购物系统中的关系
用户表 t_user
商品表 t_product
订单表 t_order
地址表 t_address
订单详细表 t_orderitem
❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤
1.数据库脚步文件的定义
1.删除约束条件
2.删除表结构(先删除主表再删除父表)
3.建表
4.定义约束条件
5.插入测试数据
2.数据字典
2.1 数据字典的格式
user_xxx:当前用户自己的对象
all_xxx:当前用户能够访问的对象(自己的对象+其他用户授权访问的对象)
dba_xxx:当前数据库所有的对象
user_tables:当前用户中可以操作的表,查询当前用户下的所有的表名
select table_name from user_tables;
user_constraints:当前用户所有的约束条件
user_objects:当前用户包含的对象
3.数据库的对象
3.1 table 表 :是关系型数据库中基本的存储单元,是一个二维结构由行和列组成.
3.2 view 视图:是一个虚表,视图会对应一个查询语句.将查询语句的结果赋予一个名字,
就是试图的名称,可以像操作表一样操作视图.视图的目的可以保证表的
安全和简化查询操作.
3.3 index 索引:在数据库中用来加速查询的数据库对象.减少磁盘的IO操作,提高访问性能
3.4 sequence 序列:用来唯一生成数值的数据库对象.通常使用序列来控制主键值.由数据库内
部提供具有安全性和高效性
3.5 procedure 存储过程:在数据库中完成特定的任何和操作.实现小部分的业务逻辑
3.6 function 函数:用来进行复杂的计算,返回结果.
3.7 package 包:将函数和存储过程组织起来,形成程序组.由包头和包体组成
3.8 trigger 触发器:在事件发生时隐式的执行,类似于java中的事件监听器
4.序列 sequence:连续产生不同的数值作为表的主键值
|-oracle,db2中有序列,mysql,sqlserver没有序列
|-序列是数据库中独立的对象
--创建序列 :默认从1开始,步进为 1
create sequence mytest_seq;
序列的属性
|-nextval:获得序列进行步进操作的值,会引起序列值得变化
|-currval:获得当前序列最大的值,不会引起序列值得变化
访问序列属性:自定义的序列名.nextval / 自定义的序列名.currval
--创建指定的起始值和步进
create sequence 自定义名称 start with 指定的值 increment by 步进;
create sequence test_seq start with 10000 increment by 2;
create table t_test(
id number primary key ,
name varchar2(30)
);
insert into t_test(id,name) values(test_seq.nextval,'yves');
insert into t_test(id,name) values(test_seq.nextval,'yves');
insert into t_test(id,name) values(test_seq.nextval,'yves');
insert into t_test(id,name) values(test_seq.nextval,'yves');
insert into t_test(id,name) values(test_seq.nextval,'yves');
insert into t_test(id,name) values(test_seq.nextval,'yves');
--删除序列
drop sequence 序列名;
drop sequence test_seq;--表中的数据不会受影响
5.视图 view
视图的特点
|-1.简化复制查询的操作
|-2.隐藏表中字段
|-3.视图是虚表,基表数据的影射
简单视图:视图中的数据就是基表中的子集.
drop table t_emp;
--复制emp表
create table t_emp
as
select * from emp where deptno in (10,20);
--使用t_emp表作为基表创建视图
create view v_emp
as
select ename,empno,deptno from t_emp where deptno=20;
--查询视图
select * from v_emp;
--查询视图的结构
desc v_emp;
--更新基表中的数据,那么视图中对应的记录也会变化
update t_emp set ename='yves' where empno=7369;
--更新视图中的数据:简单视图在默认情况下可以通过修改视图影响基表中的数据
update v_emp set ename='yves' where empno=7369;
简单视图防止修改视图影响基表在创建视图时使用 with read only
--删除视图
drop view v_emp;
--创建只读的简单视图
create view v_emp
as
select ename,empno,deptno from t_emp where deptno=20 with read only;
复杂视图:数据是通过基表中的数据计算获得.特点只读.
|-关联视图:视图中的数据通过多张表联合查询获得
--创建一个试图,显示每个部门有多少员工
create view v_emp_count
as
select deptno,count(ename) emp_num from t_emp group by deptno;
--向基表中插入数据:视图中的数据会变化
insert into t_emp(ename,empno,deptno) values('yves',1001,10);
--修改视图
delete from v_emp_count where deptno=10;
--创建视图可以使用create or replace 进行创建:如果没有创建,有就覆盖
create or replace view v_emp_count
as
select max(sal) max_sal,avg(nvl(sal,0)) avg_sal from t_emp;
--通过数据字典查寻视图
select text from user_views where view_name='V_EMP_COUNT';--视图对应的sql语句
--为scott授予创建视图的权限
使用sys以管理员身份登录
grant create view to scott;
6.索引 index:目的提高查询效率
|-结构:内容+地址
|-注意:如果字段有频繁的DML操作那么不适合创建索引(可能造成频繁的创建索引)
建表时对于主键约束和唯一性约束自动创建索引(user_indexes)
create table t_test(
id number constraint t_test_id_pk primary key,
name varchar2(20) constraint t_test_name_uk unique,
age number constraint t_test_age_nn not null,
gender varchar2(3) constraint t_test_gender_ck check(gender in('F','M'))
);
select index_name,index_type from user_indexes where table_name='T_TEST';
--对于主键字段和唯一性字段如果有where条件会使用到索引
select * from t_test where id=1000;
--如果对索引字段进行的计算,那么不会使用索引
select * from t_test where id/100=10; --不会使用索引
优化=> select * from t_test where id=1000; --可以使用索引
--条件查询中没有使用索引字段
select * from t_test where age=10;--全表扫描
手动创建索引
create index 自定义索引名称 on 表名(字段名);
create index t_test_age on t_test(age);
删除索引
drop index 自定义名称;
6.如何进行SQL语句的优化
|-** 在select语句中避免使用 *
|-** 减少数据库的访问次数
|-** 删除重复记录
|-尽量多使用commit
|-** 使用where替换having
|-多使用内部函数提高sql语句效率
|-多使用表的别名
|-使用exists替换in,使用 not exists替换not in
|-尽量使用索引类进行查询
|-sql语句尽量大写.oracle会自动转换成大写
|-** 避免在索引列上进行计算
|-** 避免在索引类上使用not,oracle遇到not就使用全表扫描
|-可以使用>=替换>
|-使用in替换or
|-尽量使用where替换group by
|-** 避免使用消耗资源的操作.如 union
7.数据库的物理组件(物理结构)
Oracle主要的物理文件有三类
|- 数据文件:存储数据,如数据表中的数据,索引数据
|- 控制文件:记录数据库结构的二进制文件
|-在线日志文件:记录数据库的日常操作. 用于故障恢复.
费主要的物理文件
|-密码文件, 参数文件,备份文件,警告和跟踪文件
8.逻辑结构
Oracle的逻辑组件:数据库->表空间-->段-->区->数据块->模式
表空间是oracle中最大的逻辑组件.一个Oracle至少包含一个表空间,系统默认的就是名为
SYSTEM的系统表空间
表空间是由一个或者多个数据文件组成,一个数据文件只能和一个表空间进行关联.
段:是构成表空间的逻辑结构,段由一组区组成。
区：为段分配空间，区是由数据块组成
数据块：是数据库能够读取，操作，分配的最小逻辑单元
模式：等同于用户，用户所创建数据库对象的总称
9.jdbc