数据库

最新推荐文章于 2023-03-31 21:17:02 发布

小小小太阳001

最新推荐文章于 2023-03-31 21:17:02 发布

阅读量334

点赞数

本文链接：https://blog.csdn.net/yudi1523/article/details/101469423

版权

SQL

三范式:
1NF:是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值;
2NF:要求数据库表中的每个实例或行必须可以被唯一地区分;
3NF:要求一个数据库表中不包含已在其它表中已包
含的非主关键字信息
目的:
避免数据重复冗余，1NF 列不可再分,最小原子（避
免重复） ;2NF 主键依赖 (确定唯一)；3N 消除传递依赖(建立主外键关联拆分表)

#1.select查询

1查询列:
select distinct *|字段|表达式 as 别名 from 表表别名 SELECT * FROM 表名;–查询某个表中所有的记录的所有字段信息
SELECT 列名 FROM 表名; --查询某个表中所有的记录的指定字段信息 SELECT列名1,列名2 FROM 表名; --查询某个表中所有的记录的字段1 字段2 SELECT distinct 列名 FROM 表名;–去除重复记录
SELECT 表达式 FROM 表名; --查询表达式
SELECT xxx as 别名 FROM 表名表别名 --使用别名

字符串使用"表示字符串(注意区分"") ，拼接使用 ||

	select 'my' from emp;  
    select ename||'a'||'-->' info from emp;

伪列不存在的列，构建虚拟的列

	select empno, 1*2 as count,'cmj' as name,deptno from emp;

虚表用于计算表达式，显示单条记录的值

	select 1+1 from dual;

null null 遇到数字参与运算的结果为 null,遇到字符串为空串

nvl nvl内置函数，判断是否为null,如果为空，取默认值0，否则取字段实际值

	select ename,sal*12+nvl(comm,0) from emp;

2.查询行(记录)

where 过滤行记录条件 ,条件有
a)、= 、 >、 <、 >=、 <=、 !=、 <>、 between and
b)、and 、or、 not、 union、 union all、 intersect 、minus
c)、null :is null、 is not null、 not is null
d)、like :模糊查询 % _ escape(‘单个字符’)
f)、in 、 exists(难点) 及子查询

	select * from emp where deptno !=20;
	select * from emp where deptno <>20;
	select * from emp where sal between 800 and 950; --between and是成对
	出现的
	--查询 员工的年薪大于20000的 员工名称、岗位 年薪
	--1)、nvl 
	select ename,job,12*(nvl(comm,0)+sal) income from emp; 
	--2)、年薪大于20000
	--错误,不能使用别名: select ename,job,12*(nvl(comm,0)+sal) income from 
	emp where income>2000;
	--a)、嵌套一个: 查询在前 过滤在后
	select ename,job,income from
	(select ename,job,12*(nvl(comm,0)+sal) income from emp) where
	income>2000;
	--b)、不使用别名 (推荐) :过滤在前，查询在后
	select ename,job,12*(nvl(comm,0)+sal) income from emp where
	12*(nvl(comm,0)+sal) >2000 ;
	--了解 any some all
	-- >=any(值列表) 大于最小值 <=any(值列表)小于最大值
	select * from emp where sal >=any(900,2000);
	select * from emp where sal <=any(900,2000);
	-- some与any 一样的效果
	-- all 大于最大值 小于最小值
	select * from emp where sal >=all(900,2000);
	select * from emp where sal <=all(900,2000);
	
	select * from emp where sal>=900 and sal<=950;

null不能使用条件判断，只能使用is ：
–存在佣金的员工名称

	select * from emp where comm is null;

–不存在佣金的员工名称

	select * from emp where comm is not null;
	select * from emp where not comm is null;

集合操作
Union、Union All、Intersect、Minus
Union，并集(去重) 对两个结果集进行并集操作，不包括重复行同时进行默认规则的排序；
Union All，全集(不去重) 对两个结果集进行并集操作，包括重复行，不进行排序；
Intersect，交集(找出重复) 对两个结果集进行交集操作，不包括重复行，同时进行默认规
则的排序；
Minus，差集(减去重复) 对两个结果集进行差操作，不包括重复行，同时进行默认规则的排
序

	--查询工资大于1500 或 含有佣金的人员姓名
	--union 去除重复行
	select ename from emp where sal>1500
	union
	select ename from emp where comm is not null;
	-- union all 不去除重复行
	select ename from emp where sal>1500
	union all
	select ename from emp where comm is not null;
	--查询显示不存在雇员的所有部门号。
	select deptno from dept 
	minus
	select distinct deptno from emp 
	--查询工资大于1500 且 含有佣金的人员姓名
	select ename,sal,comm from emp where sal>1500
	intersect
	select ename,sal,comm from emp where comm is not null;

like :模糊查询
模糊查询，使用通配符:
%:零个及以上(任意个数的)的字符
_:一个字符
遇到内容中包含 % _ 使用escape(‘单个字符’)指定转义符

	--查询员工姓名中包含字符A的员工信息
	select * from emp where ename like '%A%';
	--查询员工姓名中包含第二个A的员工名称信息
	select * from emp where ename like '_A%';
	--数据中 员工姓名中 存在 _ % ,如何查找：
	--1)、编写测试数据
	insert into emp(empno,ename,sal) values(1000,'t_%test',8989);
	insert into emp(empno,ename,sal) values(1200,'t_tes%t',8000);
	--2)、查找
	--查询员工姓名中包含字符%的员工名称 岗位 工资 部门编号
	select ename,job,sal,deptno from emp where ename like '%a%%'
	escape('a');
	--查询员工姓名中包含第二个_的员工名称 岗位 工资 部门编号

in 与 exists
in相当于使用or的多个等值，定值集合 ,如果存在子查询，确保类型相同、字段数为1，如果记录多，效率不高，用于一些少量定值判断上：

	select * from emp where sal in(900,800)
	--子查询(查询中再有查询) in 只能存在一个字段
	select * from emp where sal in (select sal from emp e where
	deptno=10)
	--10或30部门的雇员信息
	select * from emp where deptno in(10,30);
	--部门名称为 SALES 或 ACCOUNTING 的雇员信息
	select deptno from dept where dname in('SALES','ACCOUNTING');
	select *
	from emp
	where deptno in
	(select deptno from dept where dname in ('SALES', 
	'ACCOUNTING'));

	/*
	便于理解 使用java思维
	while(外层结果集){
	while(内层结果集){
	if(emp.deptno==10){
	syso("....");
	}
	if(emp.deptno==30){
	syso("....");
	} } }
	==>in :如果记录多，效率不高，用于 一些 少量定值判断上
	*/

exists条件为true,存在记录则返回结果，后续不再继续比较查询，与查询的字
段无关，与记录有关:

	--exists :条件为true,存在记录，则返回结果，后续不再继续 ,与字段无关，与记录有关
	--exists 难点: 外层结果集 内层结果集 关系列(没有关系列 true) 
	/*
	while(外层结果集){
	while(内层结果集){
	if(emp.deptno==dept.deptno||true){
	syso("....");
	break; //跳出本层
	} 
	} }
	*/
	--无关系列
	/**
	while(emp 的14条记录){
	while(dept的2条记录){
	if(true){ 
	syso();
	break;
	}
	}}
	*/
	select *
	from emp
	where exists
	(select deptno,dname from dept where dname in ('SALES', 
	'ACCOUNTING'));
	--加入关系列
	/**
	while(emp 的14条记录){
	while(dept的2条记录){
	if(e.deptno=d.deptno){ 
	syso();
	break;
	} 
	}}
	*/
	select *
	from emp e
	where exists
	(select deptno,dname from dept d where dname in ('SALES', 
	'ACCOUNTING') and e.deptno=d.deptno);
	/**
	while(emp 的14条记录){
	while(dept的2条记录){
	if(e.deptno!=d.deptno){ 
	syso();
	break;
	} 
	}}
	*/
	select *
	from emp e
	where exists
	(select deptno,dname from dept d where dname in ('SALES', 
	'ACCOUNTING') and e.deptno!=d.deptno);
	--分析以下结果
	select *
	from emp e
	where not exists
	(select deptno,dname from dept d where dname in ('SALES', 
	'ACCOUNTING') and e.deptno=d.deptno); 
	select ename, sal
	from emp
	where sal in (select sal from emp e2 where e2.sal >= 2000);
	select ename, sal
	from emp
	where exists (select ename, sal from emp e2 where e2.sal >= 2000);
	--exists
	select empno, ename, sal
	from emp e1
	where exists (select empno, ename, sal, comm
	from emp e2
	where comm is not null
	and e1.empno = e2.empno); 
	select empno, ename, sal
	from emp e1
	where exists (select empno, ename, sal, comm
	from emp e2
	where comm is not null
	and e1.deptno = e2.deptno );

获取所有行的记录

select * from emp;
select * from emp where 1=1 ;
select * from emp where ename like '%';

排序
使用 ORDER BY 排序，排序不是真实改变存储结构的顺序，而是获取的集合的
顺序。
顺序 :asc（默认） desc
多字段: 在前面字段相等时，使用后面的字段排序
空排序: 降序为 desc，注意 null 为最后

	--按工资降序
	select * from emp order by sal desc;
	--null问题
	select * from emp order by nvl(comm,0),comm desc;
	select * from emp order by comm nulls first;
	--查询雇员姓名，年薪 按佣金排序 默认为升序(asc)，降序为desc，注意null为最后
	select ename,(sal+nvl(comm,0))*12,comm total from emp order by comm 
	desc;
	--查询雇员姓名，年薪 按佣金排序 默认为升序(asc)，降序为desc，注意null为最后
	select ename,(sal+nvl(comm,0))*12,comm total from emp order by comm 
	desc;
	--对部门编号为 20 或30的雇员，工资+佣金 进行升序排序，如果相同，则按姓名降序。
	--1、查询20、30 雇员
	select * from emp where deptno in(20,30);
	--2、工资+佣金排序
	select ename,sal,comm,sal+nvl(comm,0) c from emp where deptno 
	in(20,30) order by c;
	--3、多个字段排序使用, 排序的字段可以使构建出来的虚拟的字段
	select ename,sal,comm from emp where deptno in(20,30) order by
	sal+nvl(comm,0),ename desc;

3.数据库函数

函数分为系统内置函数自定义函数如 to_date 、 to_char （字符和日期的转换）
根据函数的返回结果，我们将函数分为单行函数和多行函数
1、单行函数:一条记录返回一个结果
2、多行函数组函数聚合函数 (重点) :多条记录返回一个结果 (重点)

单行函数

日期函数: 注意区分 db数据库时间，java应用服务器的时间。以一方为准
oracle以内部数字格式存储日期:年,月,日,小时,分钟,秒
sysdate/current_date 以date类型返回当前的日期
add_months(d,x) 返回加上x月后的日期d的值
LAST_DAY(d) 返回的所在月份的最后一天
months_between(date1,date2) 返回date1和date2之间月的数目
next_day(sysdate,‘星期一’) 下周星期一

1)、当前时间

	select current_date from dual where 1=1;
	select sysdate from dual where 1=1;

2)、修改日期（天数±）

	--两天后的时刻
	select sysdate+2 from dual;

3)、修改月份

	--当前5个月后的时间
	select add_months(sysdate,5) from dual;
	--雇佣日期 2个月的时间
	select ename,hiredate, add_months(hiredate,2) after from emp;

4)、月份之差

	--雇佣日期 距离现在的 月份数
	select ename, months_between(sysdate , hiredate) from emp;

5)、最后一天

	--返回雇佣日期 当月最后一天的时间
	select ename, last_day(hiredate) d from emp;

6)、下一个星期的时间

	--下一个星期二
	select next_day(sysdate, '星期二') from dual;

转换函数（*）
to_date(c,m)  字符串以指定格式转换为日期
to_char(d,m)  日期以指定格式转换为字符串

	select to_date('2017-3-21 18:12:12','yyyy-mm-dd hh24:mi:ss') time
	from dual;
	select to_char(sysdate,'yyyy-mm-dd') from dual;
	select to_char(sysdate,'yyyy/mm/dd') from dual;
	select to_char(sysdate,'yyyy\mm\dd') from dual;
	注意中文的问题
	--select to_char(sysdate,'yyyy年mm月dd日') from dual;select
	to_char(sysdate,'yyyy"年"mm"月"dd"日"') from dual;
	--查询 82的员工信息
	select * from emp where hiredate like '%82%' ; 或
	select * from emp where to_char(hiredate,'yyyy')='1982'; 或
	select *
	from emp
	where hiredate between to_date('1982-01-01', 'yyyy-mm-dd') and
	to_date('1982-12-31', 'yyyy-mm-dd');

其他类型函数(保证类型兼容)

	--1)、nvl nvl(string1,string2)  如果string1为null,则结果为string2的值
	select ename, nvl(null,0) from emp;
	select ename, nvl(to_char(comm),'hello') from emp;
	--2)、decode decode(condition,case1,express1,case2 , express2,….casen , expressn, expressionm)
	select ename,decode(deptno, 10,'十',20,'二十') from emp;
	--3)、case when then else end
	
	--给所有的员工 涨薪,10-->10% 20-->8% 30 -->15% 其他-->20%
	--decode
	select ename,
	sal,
	deptno,
	decode(deptno,
	10,
	sal * 1.1,
	20,
	sal * 1.08,
	30,
	sal * 1.15,
	sal * 1.2) raisesal
	from emp;
	--case when then else end
	select ename,
	sal,
	deptno,
	(case deptno
	when 10 then
	sal * 1.1
	when 20 then
	sal * 1.08
	when 30 then
	sal * 1.15
	else
	sal * 1.2
	end) raisesal
	from emp;

分析函数(了解)

	select ename, sum(1) over(order by sal desc) aa from emp;
	select deptno,
	ename,
	sal,
	sum(sal) over(order by ename) 连续求和,
	sum(sal) over() 总和, -- 此处sum(sal) over () 等同于sum(sal)
	100 * round(sal / sum(sal) over(), 4) "份额(%)"
	from emp;
	select deptno,
	ename,
	sal,
	sum(sal) over(partition by deptno order by ename) 部门连续求和, --各部门的薪水"连 续"求和
	sum(sal) over(partition by deptno) 部门总和, -- 部门统计的总和，同一部门总和不变
	100 * round(sal / sum(sal) over(partition by deptno), 4) "部门份额(%)",
	sum(sal) over(order by deptno, ename) 连续求和, --所有部门的薪水"连续"求和
	sum(sal) over() 总和, -- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和
	100 * round(sal / sum(sal) over(), 4) "总份额(%)"
	from emp;

组函数

组函数|多行函数|聚合函数即多条记录返回一个结果。如下几个组函数：
avg 、sum、 min、 max、 count
1)、count :统计记录数 count() -->* 或一个列名
2)、max min: 最大值最小值
3)、sum：求和
4)、avg:平均值
注意: 1、组函数仅在选择列表和Having子句中有效
2、出现组函数，select 只能有组函数或分组字段
说明：
组信息与单条记录不能同时查询
组函数不能用在 where中，能使用的地方 select having
null 不参与运算

1、count

	--1、count统计所有的员工数
	--1)、*
	--2)、主键
	--3)、推荐
	select ename,1 from emp;
	select count(1) from emp where 1=1;
	--2、null不参与运算
	--存在佣金的员工数
	--不推荐/不需要
	select count(comm) from emp where comm is not null;
	--推荐
	select count(comm) from emp;
	--统计 部门编号30的员工数
	select count(1) from emp where deptno=30;
	--统计数量过程中 ，可能处理重复
	--统计 存在员工的 部门数量
	select count(distinct(deptno)) 有人的部门 from emp;
	--统计10和20部门一共有多少人
	select distinct(count(1)) from emp where deptno in(10,20);

2、max min: 最大值最小值

	--查询所有员工的 最高薪水 ，最低薪水，员工总数 -->组信息
	select max(sal) maxSal , min(sal) minSal , count(1) from emp;
	--查询 最高薪水的员工名称 及薪水
	--组信息 与单条记录不能同时查询
	select max(sal), ename, sal from emp;  错误
	select ename, sal from emp where sal=(select max(sal) from emp );

3、sum：求和

	-- 查询10部门的所有员工的工资总和
	select sum(sal) from emp where deptno=10; 4、

avg:平均

	-- 查询工资低于平均工资的员工编号，姓名及工资
	select empno, ename,sal from emp where sal<(select avg(sal)from
	emp);
	--查看 高于本部门平均薪水员工姓名
	select * from emp e1 where sal>(select avg(sal) from emp e2 where
	e1.deptno=e2.deptno );

分组

分组: group by , 将符合条件的记录进一步的分组
过滤组:having , 过滤组信息，表达式同 where 一致

	select distinct * | 字段 | 表达式 | 函数 as 别名
	from 表 表别名
	where 过滤行记录条件
	group by 分组字段列表
	having 过滤组
	order by 字段列表 asc | desc

解析步骤
1)、from 2)、where 3)、group 4)、having 5)、select 6)、order by
group by :分组
1)、select 出现分组函数,就不能使用非分组信息，可以使用 group by 字段
2)、group by字段可以不出现 select 中，反之select 除组函数外的，其他字段必
须出现在group by 中
过滤组 having :
where :过滤行记录，不能使用组函数， having:过滤组可以使用组函数

	--按 部门 查询 平均工资
	select avg(sal) from emp group by deptno;
	--按 部门岗位 查询 平均工资
	select avg(sal) from emp group by deptno,job;
	--按 部门 查询 平均工资，且平均工资大于2000的部门编号
	--1、先分组 后过滤 (不推荐)
	select *
	from (select deptno, avg(sal) avsal from emp where 1 = 1 group by
	deptno)
	where avsal > 2000;
	--2、过滤组 ,分组同时 过滤
	select avg(sal), deptno from emp group by deptno having
	avg(sal)>2000;
	--查询 最低平均工资的部门编号
	--1)、按部门求出平均薪水
	select avg(sal) from emp group by deptno;
	--2)、找出最低的平均薪水
	select min(avg(sal)) from emp group by deptno;
	--3)、过滤组
	select deptno
	from emp
	where 1 = 1
	group by deptno
	having avg(sal) = (select min(avg(sal)) from emp where 1 = 1 group
	by deptno);
	--查看 高于本部门平均薪水员工姓名
	--1、按部门求出平均薪水
	--2、关联子查询
	select *
	from emp e
	where exists
	(select deptno
	from (select deptno, avg(sal) avgsal from emp group by
	deptno) e2
	where e.deptno = e2.deptno
	and e.sal > avgsal);
	--另外一种 (推荐)
	select *
	from emp e1
	where sal > (select avg(sal) from emp e2 where e2.deptno = 
	e1.deptno);

行转列
id name course score
1 张三语文 81
2 张三数学 75
3 李四语文 81
4 李四数学 90
5 王五语文 81
6 王五数学 100
7 王五英语 90
转成如下
方便: 解耦（与记录之间）+扩展(与结构之间)
姓名语文数学英语
张三 81 75
李四 81 90
王五 81 100 90
行转列

	--找出课程名(表头)
	select distinct course from tb_student;
	--数据(行记录) 分组（学生+行转列 decode）
	select * from tb_student;
	--1、行转列 decode
	select name,decode(course,'语文',score) 语文,decode(course,'数学
	',score) 数学,
	decode(course,'英语',score) 英语 from tb_student;
	--2、分组
	select name,
	min(decode(course, '语文', score)) 语文,
	min(decode(course, '数学', score)) 数学,
	min(decode(course, '英语', score)) 英语
	from tb_student
	group by name;

连表查询

rowid 和 rownum
ROWID 是 ORACLE 中的一个重要的概念。用于定位数据库中一条记录的一个
相对唯一地址值。通常情况下，该值在该行数据插入到数据库表时即被确定且唯一。
ROWID 它是一个伪列，它并不实际存在于表中。它是 ORACLE 在读取表中数据行时，根据每一行数据的物理地址信息编码而成的一个伪列。所以根据一行数据的 ROWID 能找到一行数据的物理地址信息。从而快速地定位到数据行。数据库的大多数操作都是通过 ROWID 来完成的，而且使用 ROWID 来进行单记录定位速度是最快的。我们可以将其用于删除重复数据。
ROWNUM 是一种伪列，它会根据返回记录生成一个序列化的数字。排序后的
结果集的顺序号，每一个结果集都有自己顺序号，不能直接查询大于 1 的数。利用
ROWNUM，我们可以生产一些原先难以实现的结果输出。例如实现分页操作。
ps: oracle 中索引从 1 开始，java 程序从 0 开始

rowid
实现重复记录的删除
学生成绩表
要求：删除重复记录，一条记录只保留一次
思路 将所有记录按照某种特定规律分组（相同的记录为一组），保留下每组中的一
条记录即可，其他记录删除
1、找出重复数据 :哪个学生哪门课重复了

	select name,course,count(1) from tb_student group by name,course;
	select name,course,count(1) from tb_student group by name,course 
	having count(1)>1;

2、删除重复数据 :删除重复记录

	-- 每条记录的唯一标识
	select s.* , rowid from tb_student s;
	--找出 保留的rowid
	select min(rowid) from tb_student group by name,course;
	--删除
	delete from tb_student where rowid not in (select min(rowid) from
	tb_student group by name,course);

rownum
rownum :1、必须排序 2、不能直接取大于 1 的数

	--最底层 rownum 数据库默认顺序号 -->没有用的
	select emp.*, rownum from emp；
	select emp.*, rownum from emp order by sal ;
	--自己 排序后结果集的顺序号
	select e.*, rownum from (select * from emp order by sal desc) e;
	--取出工资前5名
	select e.*, rownum
	from (select * from emp order by sal desc) e
	where rownum <= 5;
	--取出 工资 3-5 名
	select e.*, rownum
	from (select * from emp order by sal desc) e
	where rownum <= 5
	and rownum >= 3;
	--三层模板 (分页)
	select e.*
	from (select e.*, rownum rn
	from (select * from emp order by sal desc) e
	where rownum <= 5) e
	where rn >= 3;
	/* 
	select 字段列表 from (select e.*,rownum rn from (select from 表 order by 字段) e where rownum<=
	最大值) 
	where rn>=最小值
	*/
	select e.*
	from (select e.*, rownum rn
	from (select * from emp order by sal desc) e
	where rownum <= 10) e
	where rn >= 6;

表连接（92）
当我们获取的数据不是来自于同一张表而是来自于多张表时就需要使用到表连接
select * from emp;
select * from dept;
注意:同名列非* 必须区分
数据源、关系列、过滤条件、字段
1、笛卡尔积
–非* 必须区分使用表名或别名.区分

	select * from emp , dept;
	select ename , dname from emp , dept;
	select ename, dname, e.deptno from emp e, dept d;

2、等值连接(在笛卡尔积基础上取条件列相同的值)

	--员工名称及部门名称
	select ename, dname, e.deptno from emp e, dept d where
	e.deptno=d.deptno;
	--找出30部门的员工名称及部门名称:先关联后过滤
	select ename, dname, e.deptno from emp e, dept d where
	e.deptno=d.deptno and e.deptno=30;
	--记录很多时 :先过滤后关联
	-- 数据来源： emp (select * from emp where deptno=30) e , 
	dept(select * from dept where deptno=30) d
	select * from emp where deptno=30;
	select * from dept where deptno=30;
	-- 查询的字段：ename, dname, e.deptno
	-- 条件：e.deptno=d.deptno , deptno=30
	select ename, dname, e.deptno from
	(select * from emp where deptno=30) e ,(select *
	from dept where deptno=30) d
	where e.deptno=d.deptno;

3、非等值连接 > < != <>between and

	--查询员工姓名，工资及等级
	--900 属于哪个等级
	select grade from salgrade where 900 >losal and 900<hisal;
	select grade from salgrade where 900 between losal and
	hisal;
	--查询员工姓名，工资及等级
	-- 数据源： emp e, salgrade s
	-- 字段： ename, grade, sal
	-- sal between losal and hisal
	select ename, grade, sal from salgrade s, emp e where sal 
	between losal and hisal;

4、自连接: 特殊的等值连接 (来自于同一张表)

	--找出 存在上级的员工姓名 及上级名称
	-- 数据来源： emp e, emp m
	-- 字段： e.ename, m.ename
	-- 条件： e.mgr=m.empno
	select e.ename, m.ename from emp e, emp m where
	e.mgr=m.empno;

5、外连接
看+和, 主表在,的左边就叫左外连接主表在,的右边叫右连接

	--找出 所有的员工姓名 及上级名称
	--找出 所有部门的员工数 及部门名称
	select dname, nu from dept d, (select count(1) nu, deptno from emp 
	group by deptno) e 
	where d.deptno(+)=e.deptno;

99 连接
交叉连接 cross join —>笛卡尔积
自然连接(主外键、同名列) natural join -->等值连接
join using连接(同名列) -->等值连接
[inner]join on 连接 -->等值连接非等值自连接 (解决一切) 关系列必须区分
left|right [outer] join on|using -->外连接
full join on|using -->全连接满足直接匹配，不满足相互补充null ，确保所有表的记录都至少出现一次

1、交叉连接

	select * from emp cross join dept;

2、自然连接

	select * from emp natural join dept;
	--在指定列过程中同名列归共同所有(*除外)
	select deptno,e.ename,d.dname from emp e natural join dept d;

3、using 连接

	select deptno,e.ename,d.dname from emp e join dept d 
	using(deptno);

4、on 连接

	-- natrual 等值
	select ename, dname
	from emp
	natural join dept 
	where deptno = 30;
	--using
	select ename, dname from emp join dept using (deptno) where deptno = 
	30;
	--on
	select ename, dname
	from emp
	join dept
	on emp.deptno = dept.deptno
	where emp.deptno = 30;
	on 非等值连接 、自连接
	--部门编号为30的员工名称 工资等级
	select ename, grade
	from emp e
	join salgrade s
	on e.sal between s.losal and s.hisal 
	where deptno=30;
	--部门编号为30的员工名称 上级名称
	select e.ename,m.ename mname from emp e join emp m
	on e.mgr =m.empno where e.deptno =30;
	--部门编号为30的员工 员工名称 部门名称 工资等级 上级名称
	select e.ename, dname, grade, m.ename mname
	from emp e
	join dept d
	on e.deptno = d.deptno
	join salgrade s
	on e.sal between s.losal and s.hisal
	join emp m
	on e.mgr = m.empno
	where e.deptno = 30;

5、外连接

	--所有部门的 部门名称，员工数
	--左外
	select dname, n
	from dept d
	left outer join (select deptno, count(1) n from emp group by
	deptno) i
	on d.deptno = i.deptno;
	select dname, n
	from dept d
	left outer join (select deptno, count(1) n from emp group by
	deptno) i
	using (deptno);
	--右外
	select dname, n
	from (select deptno, count(1) n from emp group by deptno) i
	right outer join dept d
	on d.deptno = i.deptno;

6、全连接

	select *
	from (select 1 no, 'a' "name"
	from dual
	union
	select 2 no, 'b' "name" from dual) a
	full join (select 1 no, 'c' "name"
	from dual
	union
	select 3 no, 'd' "name" from dual) b
	on a.no=b.no;
	select *
	from (select 1 no, 'a' "name"
	from dual
	union
	select 2 no, 'b' "name" from dual) a
	full join (select 1 no, 'c' "name"
	from dual
	union
	select 3 no, 'd' "name" from dual) b
	using(no);

视图

视图:建立在表|结果集|视图上的虚拟表，有以下作用
1、简化:select 查询语句
2、重用:封装select语句命名
3、隐藏:内部细节
4、区分:相同数据不同查询
不是所有的用户都有创建视图的权限
1、前提： create view -->组 connect resource dba
2、授权: -->sqlplus /nolog
a)、sys登录 conn sys/123456@orcl as sysdba
b)、授权: grant dba to scott;
回收: revoke dba from scott;
c)、重新登录
create or replace view 视图名 as select语句 [with read only];
要求:所有列必须存在名称。
对视图的删除不会删除原有表的数据
drop view 视图名;

求部门经理人中平均薪水最低的部门名称

	--1)、部门经理人 -->mgr
	create or replace view vw_emp_mgr as select distinct mgr from emp 
	where mgr is not null with read only;
	--2)、部门经理人的薪水
	create or replace view vw_emp_mgr_sal as
	select *
	from emp
	where empno in (select mgr from vw_emp_mgr) with read only;
	--3)、 按部门平均
	create or replace view vw_emp_sal_group as select deptno,avg(sal) 
	avgsal from vw_emp_mgr_sal group by deptno with read only;
	--4)、过滤组
	--找出最低的 平均薪水
	1)、 使用第三个视图
	select min(avgsal) from vw_emp_sal_group
	2)、使用第二个视图
	select min(avg(sal)) minsal from vw_emp_mgr_sal group by deptno;
	--获取部门编号及平均薪水
	select *
	from vw_emp_sal_group
	where avgsal =
	(select min(avg(sal)) minsal from vw_emp_mgr_sal group by
	deptno);
	--部门名称
	select dname
	from vw_emp_sal_group vw, dept d
	where vw.deptno = d.deptno
	and avgsal =
	(select min(avg(sal)) minsal from vw_emp_mgr_sal group by
	deptno);

索引（了解）

索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。
索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率
索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表；
索引一旦建立，在表上进行 DML 操作时（例如在执行插入、修改或者删除相关操作时），oracle 会自动管理索引，索引删除，不会对表产生影响
索引对用户是透明的，无论表上是否有索引，sql 语句的用法不变
oracle 创建主键时会自动在该列上创建索引

索引: 提高查询速度的一种手段 -->目录
1、唯一性较好字段适合建立索引
2、大数据量才有效果
3、主键|唯一: 唯一索引

	create index 索引名 on表名 (字段列表...)
	drop index 索引名
	create index idx_emp on emp(sal,ename);
	drop index idx_emp;
	select * from emp order by sal,ename;

设计表

设计表首先应该按需遵循三范式
1、确定表名 2、确定字段名类型 +约束(主键外键非空默检查认唯一)
主键：唯一标识一条记录（唯一并且非空）
唯一：唯一
非空：不能为空
默认：当没给值时使用给定一个默认值
外键：参考其他表（自己）的某个（某些）字段
检查：自定义的规则
用户表
表名 tb_user
主键 userid
字段名中文类型为空默认值其他说明
userid 流水号 number(5) 否主键
username 用户名 varchar2(30) 否长度在4-20
userpwd 密码 varchar2(20) 否长度在4-18
age 年龄 number(3) 18 大于>=18
gender 性别 char(2) 男男or 女
email 邮箱 varchar2(30) 唯一
regtime 注册日期 date sysdate
备注
文章表
表名 tb_txt
主键 txtid
字段名中文类型为空默认值其他说明
txtid 流水号 number(10) 否主键
title 标题 varchar2(32) 否长度在4-30
txt 正文 varchar2(1024)
pubtime 发布时间 date sysdate
userid 发布人 number(5) 外键，参考tb_user的userid列
备注

创建表
表名必须唯一，如果存在，必须删除

创建表（不加约束）
1)、创建新表

	create table 表名(
	字段名 类型(长度) 约束,
	...其他字段....
	..约束........
	);
	表名 tb_user
	主键 userid
	字段名 中文 类型 为空 默认值 其他说明
	userid 流水号 number(5) 否 主键
	username 用户名 varchar2(30) 否 长度在4-20
	userpwd 密码 varchar2(20) 否 长度在4-18
	age 年龄 number(3) 18 大于>=18
	gender 性别 char(2) 男 男or 女
	email 邮箱 varchar2(30) 唯一
	regtime 注册日期 date sysdate
	create table tb_user(
	userid number(5),
	username varchar2(30),
	userpwd varchar2(20),
	age number(3) ,
	gender char(2) ,
	email varchar2(30),
	regtime date
	);
	--加入注释
	comment on table tb_user is '用户表';
	comment on column tb_user.userid is '流水号,主键';
	comment on column tb_user.username is '用户名';
	comment on column tb_user.userpwd is '密码';
	comment on column tb_user.age is '年龄';
	comment on column tb_user.gender is '性别';
	comment on column tb_user.email is '邮箱';
	comment on column tb_user.regtime is '注册日期';
	表名 tb_txt
	主键 txtid
	字段名 中文 类型 为空 默认值 其他说明
	txtid 流水号 number(10) 否 主键
	title 标题 varchar2(32) 否 长度在4-30
	txt 正文 varchar2(1024)
	pubtime 发布时间 date sysdate
	userid 发布人 number(5) 外键，参考tb_user的userid列
	create table tb_txt(
	txtid number(10),
	title varchar2(32),
	txt varchar2(1024),
	pubtime date,
	userid number(5)
	);
	--注释
	comment on table tb_txt is '文章表';
	comment on column tb_txt.txtid is '流水号,主键';
	comment on column tb_txt.title is '标题';
	comment on column tb_txt.txt is '正文';
	comment on column tb_txt.pubtime is '发布时间';
	comment on column tb_txt.userid is '发布人,外键，参考 tb_user 的 userid 列';
	--删除 （先删除从表 再删除主表 ；同时删除约束）
	drop table tb_txt cascade constraints;
	drop table tb_user cascade constraints;

2)、已有表中拷贝结构

	create table 表名 as select 字段列表 from 已有表 where 1!=1;
	--拷贝结构 emp 
	create table emp_his as select ename,sal from emp where 1!=1;
	--拷贝结构 emp +数据
	create table emp_his2 as select ename,sal from emp where sal>2000;

3)、删除表

	drop table 表名 cascade constraints
	--删除表
	drop table emp_his2 cascade constraints;
	drop table emp_his cascade constraints;

2、创建表（同时创建约束+默认名称）
这种在创建表的同时创建约束并使用默认约束名称的方式，后期不方便排错，所以不
推荐使用。其主要的优点是简单。

	--删除 （先删除从表 再删除主表 ；同时删除约束）
	drop table tb_txt cascade constraints;
	drop table tb_user cascade constraints;
	表名 tb_user
	主键 userid
	字段名 中文 类型 为空 默认值 其他说明
	userid 流水号 number(5) 否 主键
	username 用户名 varchar2(30) 否 长度在4-20
	userpwd 密码 varchar2(20) 否 长度在4-18
	age 年龄 number(3) 18 大于>=18
	gender 性别 char(2) 男 男or 女
	email 邮箱 varchar2(30) 唯一
	regtime 注册日期 date sysdate 
	create table tb_user(
	userid number(5) primary key,
	username varchar2(30) check(length(username) between 4 and 20) not null ,
	userpwd varchar2(20) not null check(length(userpwd) between 4 and 18),
	age number(3) default(18) check(age>=18),
	gender char(2) default('男') check(gender in('男','女')),
	email varchar2(30) unique,
	regtime date default(sysdate)
	);
	--加入注释
	comment on table tb_user is '用户表';
	comment on column tb_user.userid is '流水号,主键';
	comment on column tb_user.username is '用户名';
	comment on column tb_user.userpwd is '密码';
	comment on column tb_user.age is '年龄';
	comment on column tb_user.gender is '性别';
	comment on column tb_user.email is '邮箱
	comment on column tb_user.regtime is '注册日期';
	表名 tb_txt
	主键 txtid
	字段名 中文 类型 为空 默认值 其他说明
	txtid 流水号 number(10) 否 主键
	title 标题 varchar2(32) 否 长度在4-30
	txt 正文 varchar2(1024) 
	pubtime 发布时间 date sysdate 
	userid 发布人 number(5) 外键，参考tb_user的userid列
	create table tb_txt(
	txtid number(10) primary key,
	title varchar2(32) not null check(length(title)>=4 and length(title)<=30),
	txt varchar2(1024),
	pubtime date default(sysdate),
	userid number(5) references tb_user(userid) on delete set null
	);
	--注释
	comment on table tb_txt is '文章表';
	comment on column tb_txt.txtid is '流水号,主键';
	comment on column tb_txt.title is '标题';
	comment on column tb_txt.txt is '正文';
	comment on column tb_txt.pubtime is '发布时间';
	comment on column tb_txt.userid is '发布人,外键，参考tb_user的userid列';
	--删除 （先删除从表 再删除主表 ；同时删除约束）
	drop table tb_txt cascade constraints;
	drop table tb_user cascade constraints;

3、创建表（同时创建约束+指定名称）
创建表的同时创建约束并指定约束的名称，后期方便排错，推荐使用

	--删除 （先删除从表 再删除主表 ；同时删除约束）
	drop table tb_txt cascade constraints;
	drop table tb_user cascade constraints;
	表名 tb_user
	主键 userid
	字段名 中文 类型 为空 默认值 其他说明
	userid 流水号 number(5) 否 主键
	username 用户名 varchar2(30) 否 长度在4-20
	userpwd 密码 varchar2(20) 否 长度在4-18
	age 年龄 number(3) 18 大于>=18
	gender 性别 char(2) 男 男or 女
	email 邮箱 varchar2(30) 唯一
	regtime 注册日期 date sysdate 
	create table tb_user(
	userid number(5),
	username varchar2(30) constraint nn_user_name not null ,
	userpwd varchar2(20) constraint nn_user_pwd not null ,
	age number(3) default(18) ,
	gender char(2) default('男'),
	email varchar2(30),
	regtime date default(sysdate),
	constraint pk_user_id primary key (userid),
	constraint ck_user_name check(length(username)between 4 and 20) ,
	constraint ck_user_pwd check(length(userpwd) between 4 and 18),
	constraint ck_user_age check(age>=18),
	constraint ck_user_gender check(gender in('男','女')),
	constraint uq_user_email unique(email) 
	);
	--加入注释
	comment on table tb_user is '用户表';
	comment on column tb_user.userid is '流水号,主键';
	comment on column tb_user.username is '用户名';
	comment on column tb_user.userpwd is '密码';
	comment on column tb_user.age is '年龄';
	comment on column tb_user.gender is '性别';
	comment on column tb_user.email is '邮箱';
	comment on column tb_user.regtime is '注册日期';
	表名 tb_txt
	主键 txtid
	字段名 中文 类型 为空 默认值 其他说明
	txtid 流水号 number(10) 否 主键
	title 标题 varchar2(32) 否 长度在4-30
	txt 正文 varchar2(1024) 
	pubtime 发布时间 date sysdate 
	userid 发布人 number(5) 外键，参考tb_user的userid列
	create table tb_txt(
	txtid number(10),
	title varchar2(32) constraint nn_txt_title not null,
	txt varchar2(1024),
	pubtime date default(sysdate),
	userid number(5) ,
	constraint pk_txt_id primary key(txtid),
	constraint ck_txt_id check(length(title)>=4 and length(title)<=30),
	constraint fk_txt_ref_user_id foreign key(userid) references tb_user(userid) on delete cascade 
	);
	--注释
	comment on table tb_txt is '文章表';
	comment on column tb_txt.txtid is '流水号,主键';
	comment on column tb_txt.title is '标题';
	comment on column tb_txt.txt is '正文';
	comment on column tb_txt.pubtime is '发布时间';
	comment on column tb_txt.userid is '发布人,外键，参考tb_user的userid列';
	--删除 （先删除从表 再删除主表 ；同时删除约束）
	drop table tb_txt cascade constraints;
	drop table tb_user cascade constraints;

4、创建表（追加创建约束+指定名称）
推荐，便于后期排错

	--删除 （先删除从表 再删除主表 ；同时删除约束）
	drop table tb_txt cascade constraints;
	drop table tb_user cascade constraints;
	表名 tb_user
	主键 userid
	字段名 中文 类型 为空 默认值 其他说明
	userid 流水号 number(5) 否 主键
	username 用户名 varchar2(30) 否 长度在4-20
	userpwd 密码 varchar2(20) 否 长度在4-18
	age 年龄 number(3) 18 大于>=18
	gender 性别 char(2) 男 男or 女
	email 邮箱 varchar2(30) 唯一
	regtime 注册日期 date sysdate 
	create table tb_user(
	userid number(5),
	username varchar2(30) ,
	userpwd varchar2(20) ,
	age number(3) ,
	gender char(2) ,
	email varchar2(30),
	regtime date default(sysdate) 
	);
	--追加约束
	alter table tb_user add constraint pk_user_id primary key (userid); 
	alter table tb_user add constraint ck_user_name check(length(username)between 4 and 20) ;
	alter table tb_user add constraint ck_user_pwd check(length(userpwd) between 4 and 18);
	alter table tb_user add constraint ck_user_age check(age>=18);
	alter table tb_user add constraint ck_user_gender check(gender in('男','女'));
	alter table tb_user add constraint uq_user_email unique(email); 
	--非空与默认
	alter table tb_user modify (username constraint nn_user_name not null);
	alter table tb_user modify (userpwd constraint nn_user_pwd not null);
	alter table tb_user modify (age default(18));
	alter table tb_user modify (gender default('男'));
	--加入注释
	comment on table tb_user is '用户表';
	comment on column tb_user.userid is '流水号,主键';
	comment on column tb_user.username is '用户名';
	comment on column tb_user.userpwd is '密码';
	comment on column tb_user.age is '年龄';
	comment on column tb_user.gender is '性别';
	comment on column tb_user.email is '邮箱';
	comment on column tb_user.regtime is '注册日期';
	表名 tb_txt
	主键 txtid
	字段名 中文 类型 为空 默认值 其他说明
	txtid 流水号 number(10) 否 主键
	title 标题 varchar2(32) 否 长度在4-30
	txt 正文 varchar2(1024) 
	pubtime 发布时间 date sysdate 
	userid 发布人 number(5) 外键，参考tb_user的userid列
	create table tb_txt(
	txtid number(10),
	title varchar2(32),
	txt varchar2(1024),
	pubtime date,
	userid number(5) 
	);
	--追加约束
	alter table tb_txt add constraint pk_txt_id primary key(txtid);
	alter table tb_txt add constraint ck_txt_id check(length(title)>=4 and length(title)<=30);
	--三种级联删除规则
	alter table tb_txt add constraint fk_txt_ref_user_id foreign key(userid) references tb_user(userid);
	alter table tb_txt add constraint fk_txt_ref_user_id foreign key(userid) references tb_user(userid) on 
	delete cascade ;
	alter table tb_txt add constraint fk_txt_ref_user_id foreign key(userid) references tb_user(userid) on 
	delete set null;
	--注意非空 默认
	alter table tb_txt modify (title constraint nn_txt_title not null) ;
	alter table tb_txt modify (pubtime default(sysdate));
	--注释
	comment on table tb_txt is '文章表';
	comment on column tb_txt.txtid is '流水号,主键';
	comment on column tb_txt.title is '标题';
	comment on column tb_txt.txt is '正文';
	comment on column tb_txt.pubtime is '发布时间';
	comment on column tb_txt.userid is '发布人,外键，参考tb_user的userid列';
	--删除 （先删除从表 再删除主表 ；同时删除约束）
	drop table tb_txt cascade constraints;
	drop table tb_user cascade constraints;

二、约束（了解）
在 oracle 中所有的一切都是对象，约束也是一个个的对象，除了能创建约束我们还能
对约束进行一些其他的操作
1、查看某个用户的约束

	select constraint_name, constraint_type
	from user_constraints
	where owner = upper('scott');

2、查看表的约束

	select constraint_name,constraint_type
	from user_constraints
	where table_name=upper('tb_user');

3、查看字段名+约束

	select constraint_name, column_name
	from user_cons_columns
	where table_name = upper('tb_user');

4、约束的禁用与启用

	ALTER TABLE tb_user disable constraint nn_user_name; 
	ALTER TABLE tb_user enable constraint nn_user_name;

5、删除约束

	alter table tb_user drop constraint uq_user_email cascade;

6、修改约束

	--非空
	alter table tb_user modify (username varchar2(20));
	--默认
	alter table tb_user modify (age default null);

三、表的其他操作
1、修改表结构
1、修改表名 :rename to
2、修改列名: alter table 表名 rename column to
3、修改类型: alter table 表名 modify(字段类型) 4、修改约束: 先删除后添加
5、添加列: alter table 表名 add 字段类型
6、删除列:alter table 表名 drop column 字段

	--修改表名
	rename tb_txt to tb_txt_new;
	--修改列名
	alter table tb_txt_new rename column txtid to tid;
	--修改类型
	alter table tb_txt_new modify(tid varchar2(20));
	--添加列
	alter table tb_txt_new add col varchar2(30);
	--删除列
	alter table tb_txt_new drop column col;
	select * from tb_txt_new;

2、删除表
1、先删除从表再删除主表
2、删除表的同时删除约束

	drop table tb_txt_new cascade constraints;
	drop table tb_user cascade constraints;

3、截断数据
truncate: 截断所有的数据 ,如果截断的是主表，结构不能存在外键关联
截断数据同时从结构上检查

	create table emp_his as select * from emp where 1=1;
	select * from emp_his;
	--截断所有的数据
	truncate table emp_his;
	--不能截断: truncate table dept;

四、序列
使用工具|程序管理流水号，序列在创建时没有与表关联 ,在操作数据时与表关联
1、创建

	create sequence 序列名 start with 起始值 increment by 步进;

2、使用
在操作数据添加更新 -->主键
1)、currval :当前值
2)、nextval:下个值

	create sequence seq_tb_user start with 2 increment by 2;
	drop sequence seq_tb_user;
	select seq_tb_user.nextval from dual;
	select seq_tb_user.currval from dual

3、删除

drop sequence 序列名

事务

事务是指作为单个逻辑工作单元执行的一组相关操作。这些操作要求全部完成
或者全部不完成。使用事务是为了保证数据的安全有效。
事务有一下四个特点：（ACID） 1、原子性（Atomic）：事务中所有数据的修改，要么全部执行，要么全部不执行。
2、一致性（Consistence）：事务完成时，要使所有所有的数据都保持一致的状态，
换言之：通过事务进行的所有数据修改，必须在所有相关的表中得到反映。
3、隔离性（Isolation）：事务应该在另一个事务对数据的修改前或者修改后进行访问。
4、持久性（Durability）：保证事务对数据库的修改是持久有效的，即使发生系统故障，
也不应该丢失。
当事务之间发生并发时有几个隔离级别
Oracle 默认的隔离级别是 read committed。
Oracle 支持上述四种隔离级别中的两种:read committed 和 serializable。除此之外，
Oralce 中还定义 Read only 和 Read write 隔离级别。
Read only：事务中不能有任何修改数据库中数据的操作语句，是 Serializable 的一个子集。
Read write：它是默认设置，该选项表示在事务中可以有访问语句、修改语句，但不经常使用。
丢失更新：两个事务同时存储，一个存储 100 ，一个存储 200，最终可能至存储了
200 或者 100，那另一个的更新就没成功，即结果不为预想的 300
脏读：事务 T1 更新了一行数据，还没有提交所做的修改，T2 读取更新后的数据，T1
回滚，T2 读取的数据无效，这种数据称为脏读数据。
不可重复读：事务 T1 读取一行数据，T2 修改了 T1 刚刚读取的记录，T1 再次查询，
发现与第一次读取的记录不相同，称为不可重复读。
幻读：事务 T1 读取一条带 WHERE 条件的语句，返回结果集，T2 插入一条新纪录，
恰好也是 T1 的 WHERE 条件，T1 再次查询，结果集中又看到 T2 的记录，新纪录就叫
做幻读。
1、事务的开启
自动开启于 DML 之 insert delete update
2、事务的结束

成功
正常执行完成的 DDL 语句：create、alter、drop
正常执行完 DCL 语句 GRANT、REVOKE
正常退出的 SQLPlus 或者 SQL Developer 等客户端
如果人工要使用隐式事务，SET AUTOCOMMIT ON (只针对一个连接)  手动提交：使用 commit
失败
rollback ，手动回滚
非法退出意外的断电
rollback 只能对未提交的数据撤销，已经 Commit 的数据是无法撤销的，因为 commit
之后已经持久化到数据库中。
二、DML
DDL（Data Definition Language 数据定义语言）用于操作对象和对象
的属性,这种对象包括数据库本身，以及数据库对象，像：表、视图等等，DDL 对这
些对象和属性的管理和定义具体表现在 create、drop 和 alter 上。特别注意：DDL 操作
的“对象”的概念，”对象“包括对象及对象的属性，而且对象最小也比记录大个层
次。以表举例：create 创建数据表，alter 可以更改该表的字段，drop 可以删除这个表，
从这里我们可以看到，DDL 所站的高度，他不会对具体的数据进行操作。

DDL 的主要语句（操作）语句作用

	create 可以创建数据库和数据库的一些对象
	drop 可以删除数据表、索引、条件约束等
	alter 修改数据表定义及属性

DML（Data Manipulation Language 数据操控语言）用于操作数据库对象中包
含的数据，也就是说操作的单位是记录。
DML 的主要语句（操作）语句作用

	Insert 向数据表张插入一条记录
	Delete 删除数据表中的一条或多条记录，也可以删除数据表中的所有记录，但是，它的操作对象仍是记录
	Update 用于修改已存在表中的记录的内容

使用场景：

	insert 注册
	update 修改密码
	delete 退出、删除、剔除会员
	select 登录|查看会员

1、insert
insert into 表名 [(字段列表)] values(值列表); 添加记录
添加记录时需要满足一下条件
类型长度兼容: 字段兼容值
值满足约束 :主键 (唯一+非空) 非空(必填) 唯一(不重复 ) 默认(没有填写使用默认值) 检查(满足条
件) 外键(参考主表主键列的值)  个数必须相同: 指定列，个数顺序与列相同；
没有指定，个数与表结构的列个数和顺序相同 (null也得占位，没有默认值)

添加数据

insert into 表名 values(和表结构顺序和个数和类型一致的数据可以手写也可以从别的表中获取的);
insert into 表名(指定列名) values(和指定的列个数、顺序、类型一致的列数据)

eg:

insert into 表(指定列) select 查询列 from 源表 where 过滤数据;
insert into 表(指定列) values(值列表);
insert into 表名 select 查询列 from 源表 where 过滤数据;
insert into 表名 values(值列表 );

操作实例
创建序列：一般为一张表准备一个序列

create sequence seq_user_id increment by 1 start with 1 ;
create sequence seq_txt_id increment by 1 start with 1 ;

准备表：见 005 表设计
eg: 添加所有列

--没有列|所有列 所有字段,值个数必须为7个，没有默认值，非空可以使用null占位
insert into tb_user values (seq_user_id.nextval,'test','test123',null,'女',null,sysdate);

eg: 添加指定列(推荐: 方便阅读操作、更改顺序、非空列和默认值列可以选填)

--指定所有列
insert into tb_user(username,userid,userpwd,gender,age,email,regtime) values 
('shsxt',seq_user_id.nextval,'verygood','男',27,'bjsxt@qq.com.cn',sysdate);
--指定部分列(非空列和默认值列 可以选填, 必填项|主键列 必须指定 ,没有指定列 如果存在默认值，使用
默认值填充，否则null填充)
insert into tb_user(username,userid,userpwd) values 
('shsh',seq_user_id.nextval,'shanghai');
commit;
insert into tb_user(username,userid,userpwd,age) values 
('穷屌丝男',seq_user_id.nextval,'极品女士',20);
commit;

eg: 添加外键

--添加同时查询 :使用一条sql 查询外键 ,直接添加 (少用，外键只有一个值，多个值运行错误)一条sql搞 定
insert into tb_txt(txtid,title,userid) values(seq_txt_id.nextval,'iphone6来了',(select userid from tb_user 
where username='穷屌丝男'));
--先查询后添加 :
--查询值
select userid from tb_user where username='穷屌丝男';
--添加
insert into tb_txt(txtid,title,userid) values(seq_txt_id.nextval,'您的肾值钱吗?',10);
commit;

eg: 添加时间

--添加时间
insert into tb_user(username,userid,userpwd,age,regtime) values 
('java程序猿',seq_user_id.nextval,'bjsxt',20,to_date('2014-10-31','yyyy-mm-dd'));
commit;
查看数据
select * from tb_user;
select * from tb_txt;

eg: 从已有表中添加数据

select * from emp_his;
insert into emp_his(empno,ename,job) select empno,ename,job from emp where sal>2000;
rollback;
select * from emp_his;
insert into emp_his select empno, ename, job, mgr, hiredate, sal , comm, deptno from emp where 
deptno=20;
drop table emp_his cascade constraints;

2、update
update 为修改数据
update 表名 set 字段=值 [,…] where 过滤行记录;
要求：
记录存在
类型长度兼容: 字段兼容值
个数相同
更改数据
从已有表中查询数据更改字段值：

update 表名 set(字段列表)=(select 字段列表 from 源表 where 过滤源表记录 ) where 更新记录的条件
手动更改字段值：
update 表名 set 字段=值 [,....] where 过滤行记录;

操作实例
eg:手动更改字段值

select * from tb_user;
--重置所有人员的密码 8888
update tb_user set userpwd=8888 where 1=1;
rollback;
--修改 shsxt 的密码 和年龄
update tb_user set userpwd='good',age=29 where username='shsxt' and userpwd='verygood';
commit;

eg:从表中获取

--了解 将用户名与密码对换
update tb_user set(username,userpwd)=(select userpwd,username from tb_user where userid=6) 
where userid=6;
--select 只能返回一行数据: update tb_user set(username,userpwd)=(select userpwd,username from 
tb_user ) where userid=6;

3、delete
delete 删除数据
delete [from] 表名 where 过滤行记录
说明：
1、delete 可以删除指定部分记录，删除全部记录
2、记录上存在主外键关联时，删除存在关联的主表的记录时,注意参考外键约束，约束强制不让删除
先删除从表再删除主表

--删除全部数据
delete from tb_user where 1=1;
--删除指定数据
delete from tb_user where userid<10;
--主外键关联时，注意 参考约束， 约束强制不让删除
--先删除从表 再删除主表
delete from tb_txt where 1=1;
delete from tb_user where 1=1;
commit;

截断数据与删除数据区别 truncate 与delete 区别

1、truncate -->ddl ，不涉及事务，就不能回滚
delete -->dml ,涉及事务，可以回滚
2、truncate 截断所有的数据 delete 可以删除全部 或者部分记录
3、truncate从结构上检查是否存在主外键，如果存在，不让删除
delete 从记录上检查是否存在主外键，如果存在，按参考外键约束进行删除。1;

测试：

truncate table tb_user;
delete from tb_user;
ddl: 异常则失败,没有异常就是成功
dml: insert update delete -->异常则失败，记录数<=0则失败; 记录数>0就是成功
select -->异常则失败,返回结果集 存在结果集就是成功
create table emp_his as select * from emp;
delete from emp_his;

JDBC
jdbc: java database connectivity java 与数据库的连接
一、角色分类
服务器 (db)： a)、接收 sql b)、执行 sql c)、返回结果
客户端 (java)：a)、接收数据 b)、组装sql c)、发送SQL(与数据库建立联系) d)、分析结果
二、面向接口编程
1、java 制定标准，不同的数据库厂商实现接口即可。java 中提供的接口 java.sql.*
包下，常用接口如下
接口名称作用
java.sql.Connection 连接
java.sql.Statement 静态处理块
java.sql.PreparedStatement 预处理块
java.sql.ResultSet 结果集
java.sql.ResultSetMetaData 结果集元信息
2、oracle 厂商实现接口（jar）
F:\app\Administrator\product\11.2.0\dbhome_1\jdbc\lib\ojdbc6.jar 视安装路径而定
三、jdbc 步骤
1、选择快递公司加载驱动（完整路径）
2、与快递公司建立联系(电话号码唯一信息) 建立连接（url 用户名密码） 3、快递员收包裹创建处理块 Statement PreparedStatement
4、打包投递执行: execute(ddl) int executeUpdate(dml)
ResultSet executeQuery(select)
5、签收分析结果 :ddl -->没有异常 dml—>>0 select–>分析
结果集
6、打发走人释放资源
核心为拼接 SQL、分析结果、操作结果
四、连接
1、准备工作
引入驱动包
构建路径 build path
测试用户是否正确登录
2、建立连接
1）、连接字符串
驱动: oracle.jdbc.driver.OracleDriver
url: jdbc:oracle:thin:@db 服务器地址:端口:实例
连接 url => jdbc:oracle:thin:@localhost:1521:orcl
用户名与密码: scott tiger
2）、编写测试类
加载驱动
a)、硬编码: new oracle.jdbc.driver.OracleDriver();
b)、软编码: class.forName(“oracle.jdbc.driver.OracleDriver”)
建立连接
Connection con =DriverManager.getConnection(“jdbc:oracle:thin:@locahost:1521:orcl”,“scott”,“tiger”)
五、处理块
1、静态处理块 Statement
Statement 是 Java 执行数据库操作的一个重要接口，用于在已经建立数据库连
接的基础上，向数据库发送要执行的 SQL 语句。Statement 对象，用于执行不带参数
的简单 SQL 语句。
执行静态 SQL 语句并返回它所生成结果的对象。
1)、创建：连接.Connection.createStatement()
2)、执行：
ddl -->execute(dd语句) – 通常不会在代码中执行
dml -->executeUpdate(dml语句)
select -->executeQuery(select)
3)、特点：处理不变的静态的 sql 语句
优点: 直接查看sql ，方便处理错误
缺点:性能不高拼接 sql 麻烦可能存在 sql 注入
2、预处理块 PreparedStatement
PreparedStatement 接口继承了 Statement，并与之在两方面有所不同：有人
主张，在 JDBC 应用中，如果你已经是稍有水平开发者，你就应该始终以
PreparedStatement 代替 Statement.也就是说，在任何时候都不要使用 Statement。
由于 PreparedStatement 对象已预编译过，所以其执行速度要快于 Statement
对象。因此，多次执行的 SQL 语句经常创建为 PreparedStatement 对象，以提高效率。
1)、创建：创建:连接.prepareStatement(sql)
2)、执行：
存在? ,先填充参数再执行
ddl -->execute()
dml -->executeUpdate()
select -->executeQuery()
3)、特点：处理不变的静态的 sql 语句 |可变的 sql 语句带 ? 的 sql
优点:性能高，方便编写sql 不存在sql注入安全
缺点:不能直接打印sql语句不方便处理错误
六、分析
执行完 SQL 语句后可能成功也可能失败，如果成功，有数据则我们很大一部分情况是
需要获取查询的结果。数据就是一切
 ddl:没有异常就是成功
 dml: 结果>0就是成功
 select:分析结果集
对于 select 的结果集进行分析，类似于迭代器，先判断（移动）再获取
next()
getXxx(索引|列名|别名)
七、释放资源
释放资源的原则是先打开的后关闭，则我们的顺序一般为：结果集->处理块->连接

小小小太阳001

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
数据库

SQL1. 查询语句–select … from 数据来源;–select后是结果集数据的集合select * from emp;select * from dept;select deptno,dname from (select * from dept); --查询嵌套查询– 按条件查询过滤行记录– select … from … where 过滤行记录条件;–执行顺...
复制链接

扫一扫