数据库 day60,61 Oracle入门,单行函数,多表查询,子查询,事物处理,约束,rownum分页,视图,序列,索引
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了数据库 day60,61 Oracle入门,单行函数,多表查询,子查询,事物处理,约束,rownum分页,视图,序列,索引，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含34816字，纯文字阅读大概需要50分钟。
内容图文
，.
varchar2 把所有字符都占两字节处理(一般情况下)，
varchar只对汉字和全角等字符占两字节，数字，英文字符等都是一个字节，
VARCHAR2把空串等同于null处理，而varchar仍按照空串处理；建议在oracle中使用varchar2
Number(7,2) 数值类型整数位占5位，小数位占2位，一共7位。
Date 时间类型
7.Scott用户下的表结构
8. sql
1.sql简介
结构化查询语言(Structured Query Language)简称SQL(发音：/??s kju? ??l/ "S-Q-L")，结构化查询语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统, 可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套，这使它具有极大的灵活性和强大的功能。
? DML(数据库操作语言): 其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。
? DDL(数据库定义语言): 其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。
DCL(数据库控制语言):它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户 组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。
2. 简单查询
1.查询语法
Select * |列名 from 表名
2.别名用法
在查询的结果列中可以使用别名
Select 列名 别名，列名别名，... from emp;
3. 除重复的数据
Select distinct *|列名, ... from emp;
使用distinct可以消除重复的行，如果查询多列的必须保证多列都重复才能去掉重复
4. 字符串连接查询
查询雇员编号，姓名，工作
编号是：7369的雇员， 姓名是：smith，工作是：clerk
字符串的连接使用‘||’
5. 查询中四则运算
查询每个雇员的年薪
Select ename, sal*12 from emp;
Select ename, sal*12 income from emp;
Sql中支持四则运算“+，-，*，/”
3. 限定查询
在查询绝大多数都会有条件的限制
语法：select *|列名 from 表名 where 条件
例如：查询工资大于1500的所有雇员
1. 非空和空的限制
? 示例：查询每月能得到奖金的雇员
分析：只要字段中存在内容表示不为空，如果不存在内容就是null，
语法：列名 IS NOTNULL
为空 列名 IS NULL
select * from emp where comm is not null;
select * from emp where comm is null;
? 范例：查询工资大于1500并且有奖金领取的雇员
分析：多个查询条件同时满足之间使用‘AND’
select * from emp where sal > 1500 and comm is not null;
? 范例:查询工资大于1500或者有奖金的雇员
分析：多个查询条件或满足，条件之间使用“OR”
select * from emp where sal > 1500 or comm is not null;
? 范例：查询工资不大于1500和没有奖金的人
语法：NOT(查询条件)
select * from emp where sal <= 1500 and comm is null;
select * from emp where not (sal > 1500 or comm is not null);
范例：基本工资大于1500但是小于3000的全部雇员
分析：sal>1500,sal<3000
select * from emp where sal > 1500 and sal < 3000;
Between and等于 sal >=1500 and sal <= 3000
select * from emp where sal between 1500 and 3000;
范例：查询1981-1-1到1981-12-31号入职的雇员
分析：between and不仅可以使用在数值之间，也可以用在日期的区间
select * from emp where hiredate between to_date(‘1981-1-1‘,‘yyyy-mm-dd‘) and to_date(‘1981-12-31‘,‘yyyy-mm-dd‘);
范例：查询雇员名字叫smith的雇员
在oracle中的查询条件中查询条件的值是区分大小写的
select * from emp where ename = ‘SMITH;
范例:查询雇员编号是7369,7499,7521的雇员编号的具体信息
如果使用之前的做法可以使用OR关键字
select * from emp where empno = 7369 or empno = 7499 or empno = 7521;
实际上，此时指定了查询范围，那么sql可以使用IN关键字
语法: 列名 IN (值1，值2，....)
列名 NOT IN (值1， 值2，...)
其中的值不仅可以是数值类型也可以是字符串
select * from emp where empno in (7369, 7499, 7521);
select * from emp where empno not in (7369, 7499, 7521);
select * from emp where ename in (‘SMITH‘, ‘SMITH2‘, ‘SMITH3‘);
在常用的站点中经常会有模糊查询，即：输入一个关键字，把符合的内容全部的查询出来，在sql中使用LIKE语句完成。
在LIKE中主要使用以下两种通配符
“%”：可以匹配任意长度的内容
“_”:可以匹配一个长度的内容
范例：查询出所有雇员姓名中第二个字符包含“M”的雇员
select * from emp where ename like ‘_M%‘;
在LIKE中如果没有关键字表示查询全部
select * from emp where ename like ‘%%‘;
查询名字中带有“M”的雇员
select * from emp where ename like ‘%M%‘;
在oracle中不等号的用法可以有两种形式“<>”和“!=”
范例：查询雇员编号不是7369的雇员信息
select * from emp where empno != 7369;
select * from emp where empno <> 7369;
4.对结果集排序
在sql中可以使用ORDER BY对查询结果进行排序
语法：SELECT * |列名 FROM 表名 {WEHRE 查询条件} ORDER BY 列名1 ASC|DESC，列名2...ASC|DESC
范例：查询雇员的工资从低到高
分析：ORDER BY 列名 默认的排序规则是升序排列，可以不指定ASC，如果按着降序排列必须指定DESC
select * from emp order by sal desc;
如果存在多个排序字段可以用逗号分隔
select * from emp order by sal desc, hiredate asc;
注意ORDER BY语句要放在sql的最后执行。
5.单行函数
1.字符函数
接收字符输入返回字符或者数值，dual是伪表
1. 把小写的字符转换成大小的字符
upper(‘smith‘)
select upper(‘smith‘) from dual;
2. 把大写字符变成小写字符
lower(‘SMITH‘)
select lower(‘SMITH‘) from dual;
3. 把首字符大写
initcap(‘smith‘)
select initcap(‘smith‘) from dual;
4. 字符串的连接可以使用concat可以使用“||”建议使用“||”
concat(‘hello‘, ‘world‘)
select concat(‘jr‘, ‘smith‘) from dual;
5. 字符串的截取，使用substr，第一个参数是源字符串，第二个参数是开始索引，第三个参数结束的索引，开始的索引使用1和0效果相同
substr(‘hello‘, 1,3)
select substr(‘hello‘, 1, 3) from dual;
select substr(‘hello‘, 0, 9) from dual;
6. 获取字符串的长度
length(‘hello‘)
select length(‘hello‘) from dual;
7. 字符串替换，第一个参数是源字符串，第二个参数被替换的字符串，第三个是替换字符串
replace(‘hello‘, ‘l‘,‘x‘)
select replace(‘hello‘, ‘l‘, ‘x‘) from dual;
2. 数值函数
1. 四舍五入函数：ROUND()
默认情况下ROUND四舍五入取整，可以自己指定保留的位数。
select round(12.634) from dual;
select round(12.6358, 3) from dual;
2. 取整：TRUNC()，默认全部去掉小数，也可以指定保留的位数
select trunc(12.634) from dual;
select trunc(12.637, 2) from dual;
3. 取余数MOD()
select mod(10, 3) from dual;
3.日期函数
Oracle中提供了很多和日期相关的函数，包括日期的加减，在日期加减时有一些规律
日期 –数字 = 日期
日期 + 数字 = 日期
日期 –日期 = 数字
1. 范例：查询雇员的进入公司的周数。
分析：查询雇员进入公司的天数(sysdate– 入职日期)/7就是周数
select ename, round((sysdate - hiredate)/7) weeks from emp;
2. 获得两个时间段中的月数：MONTHS_BETWEEN()
范例：查询所有雇员进入公司的月数
select ename, round(months_between(sysdate, hiredate)) months from emp;
3. 获得几个月后的日期：ADD_MONTH()
范例：求出三个月后的日期
select add_months(sysdate, 3) from dual;
4. 指定给出下次某日期数NEXT_DATE()
范例：求出下一个星期一是什么日期
select next_day(sysdate, ‘星期一‘) from dual;
5. 求出一个日期的最后一天
范例：求出本月的最后一天是几号
select last_day(sysdate) from dual;
select last_day(to_date(‘1997-1-23‘, ‘yyyy-mm-dd‘)) from dual;
4.转换函数
1. TO_CHAR:字符串转换函数
范例：查询所有的雇员将将年月日分开，此时可以使用TO_CHAR函数来拆分
拆分时需要使用通配符
年：y, 年是四位使用yyyy
月：m, 月是两位使用mm
日：d, 日是两位使用dd
时分秒:hh mi ss
select ename,
to_char(hiredate, ‘yyyy‘) 年,
to_char(hiredate, ‘mm‘) 月,
to_char(hiredate, ‘dd‘) 日
from emp;
select ename, to_char(hiredate, ‘yyyy-mm-dd‘) 雇佣日期 from emp;
在结果中10以下的月前面被被补了前导零，可以使用fm去掉前导零
select ename, to_char(hiredate, ‘fmyyyy-mm-dd‘) 雇佣日期 from emp;
TO_CHAR还可以给数字做格式化
范例：把雇员的工资按三位用“,”分隔，在oracle中“9”代表一位数字
select to_char(1000000, ‘999,999,999‘) from dual;
如果在钱的前面加上国家的符号可以使用“$”代表是美元，如果要使用本地的钱的单位使用“L”
select to_char(1000000, ‘$999,999,999‘) from dual;
select to_char(1000000, ‘l999,999,999‘) from dual;(本地结果人民币￥)
2. TO_NUMBER:数值转换函数
TO_NUMBER可以把字符串转换成数值
select to_number(‘20‘) + to_number(‘80‘) from dual;
3. TO_DATE:日期转换函数
TO_DATE可以把字符串的数据转换成日期类型
select to_date(‘1999-12-12‘, ‘yyyy-mm-dd‘) from dual;
5.通用函数
1．空值处理nvl
范例：查询所有的雇员的年薪
select ename, (sal*12 + comm) yearsal from emp;
我们发现很多员工的年薪是空的，原因是很多员工的奖金是null，null和任何数值计算都是null，这时我们可以使用nvl来处理。
空和任何数计算都是空
select ename, (sal*12 + nvl(comm, 0)) yearsal, nvl(comm, 0) comm from emp;
2.Decode函数
该函数类似if....elseif...esle
语法：DECODE(col/expression,[search1,result1],[search2, result2]....[default])
Col/expression:列名或表达式
Search1，search2...:用于比较的条件
Result1, result2...:返回值
如果col/expression和Searchi匹配就返回resulti,否则返回default的默认值
select decode(3, 1, ‘我是1‘, 2, ‘我是2‘, ‘无名氏‘) from dual;
范例：查询出所有雇员的职位的中文名
select ename, decode(job,
'CLERK',
'业务员',
'SALESMAN',
'销售',
'MANAGER',
'经理',
'ANALYST',
'分析员',
'PRESIDENT',
'总裁',
'无业')
from emp; 3.case when
CASE WHEN comparison_expr1 THENreturn_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
select ename,
case
when job = 'CLERK' then
'业务员'
when job = 'SALESMAN' then
'销售'
when job = 'MANAGER' then
'经理'
when job = 'ANALYST' then
'分析员'
when job = 'PRESIDENT' then
'总裁'
else
'无业'
end
from emp;
6. 多表查询
1.多表连接基本查询
使用一张以上的表做查询就是多表查询
语法： SELECT {DISTINCT} *|列名.. FROM 表名 别名，表名1 别名
{WHERE限制条件 ORDER BY 排序字段 ASC|DESC...}
范例：查询员工表和部门表
select * from emp,dept;
我们发现产生的记录数是56条，我们还会发现emp表是16条，dept表是4条，56正是emp表和dept表的记录数的乘积，我们称其为笛卡尔积。
如果多张表进行一起查询而且每张表的数据很大的话笛卡尔积就会变得非常大，对性能造成影响，想要去掉笛卡尔积我们需要关联查询。
在两张表中我们发现有一个共同的字段是depno，depno就是两张表的关联的字段，我们可以使用这个字段来做限制条件，两张表的关联查询字段一般是其中一张表的主键，另一张表的外键。
关联之后我们发现数据条数是14条，不在是56条。
多表查询我们可以为每一张表起一个别名
select e.*, d.dname, d.loc from emp e, dept d where e.deptno = d.deptno;
范例：查询出雇员的编号，姓名，部门的编号和名称，地址
select e.empno, e.ename, e.deptno, d.dname, d.loc from emp e, dept d where e.deptno = d.deptno;
范例：查询出每个员工的上级领导
分析：emp表中的mgr字段是当前雇员的上级领导的编号，所以该字段对emp表产生了自身关联，可以使用mgr字段和empno来关联
select e.empno, e.ename, e1.empno, e1.ename from emp e, emp e1 where e.mgr = e1.empno;
范例:在上一个例子的基础上查询该员工的部门名称
分析：只要在上一个例子基础上再加一张表的关联，使用deptno来做关联字段即可
select e.empno, e.ename, e1.empno, e1.ename, d.deptno, d.dname
from emp e, emp e1, dept d
where e.mgr = e1.empno
and e.deptno = d.deptno; 范例：查询出每个员工编号，姓名，部门名称，工资等级和他的上级领导的姓名，工资等级
select e.empno,
e.ename,
d.dname,
decode(s.grade,
1,
'一级',
2,
'二级',
3,
'三级',
4,
'四级',
5,
'五级',
'无级') grade,
e1.ename,
decode(s1.grade,
1,
'一级',
2,
'二级',
3,
'三级',
4,
'四级',
5,
'五级',
'无级') grade1
from emp e, dept d, salgrade s, emp e1, salgrade s1
where e.deptno = d.deptno
and e.mgr = e1.empno
and e.sal between s.losal and s.hisal
and e1.sal between s1.losal and s1.hisal
可以使用层次查询
select level 查询必须带伪列 level
connect by 条件为 prior 上一层的empnp值 等于 本层的mgr值
start with 从哪开始遍历
SQL> --自连接不适合操作大表
SQL> --层次查询
SQL> select level,empno,ename,mgr
2 from emp
3 connect by prior empno=mgr
4 start with mgr is null
5 order by 1;
LEVEL EMPNO ENAME MGR
---------- ---------- ---------- ----------
1 7839 KING
2 7566 JONES 7839
2 7698 BLAKE 7839
2 7782 CLARK 7839
3 7902 FORD 7566
3 7521 WARD 7698
3 7900 JAMES 7698
3 7934 MILLER 7782
3 7499 ALLEN 7698
3 7788 SCOTT 7566
3 7654 MARTIN 7698
LEVEL EMPNO ENAME MGR
---------- ---------- ---------- ----------
3 7844 TURNER 7698
4 7876 ADAMS 7788
4 7369 SMITH 7902
2. 外连接（左右连接）
1. 右连接
当我们在做基本连接查询的时候，查询出所有的部门下的员工，我们发现编号为40的部门下没有员工，但是要求把该部门也展示出来，我们发现上面的基本查询是办不到的
使用(+)表示左连接或者右连接，
当(+)在左边表的关联条件字段上时是左连接，
如果是在右边表的关联条件字段上就是右连接。
范例：查询出所有员工的上级领导
分析：我们发现使用我们以前的做法发现KING的上级领导没有被展示，我们需要使用左右连接把他查询出来
select e.empno 员工编号,
e.ename 员工姓名,
d.dname 部门名称,
s.grade 员工工资等级,
ee.ename 领导姓名,
s2.grade 领导工资等级
from emp e, emp ee, salgrade s, dept d, salgrade s2
where e.deptno = d.deptno
and e.mgr = ee.empno(+)
and e.sal between s.losal and s.hisal
and ee.sal between s2.losal(+) and s2.hisal(+);
3.sql1999对SQL的支持
1.交叉连接CROSS JOIN（了解）
交叉连接会产生笛卡尔积
select * from emp cross join dept;
1. 自然连接NATURAL JOIN（了解）
自然连接会自动的分析管理条件进行连接，去掉笛卡尔积。
select * from emp natural join dept;
2. USING子句，直接管理操作（了解）
select * from emp join dept using(deptno) where deptno = 20;
3. ON子句，自己编写连接条件（重要）
On相当于where
select * from emp e join dept d on e.deptno = d.deptno;
select * from emp e, dept d where e.deptno = d.deptno;
4. 左连接和右连接LEFT JOIN和RIGHT JOIN（重要）
select * from dept d left join emp e on d.deptno = e.deptno;
select * from dept d, emp e where d.deptno = e.deptno(+);
--在子查询的结果集中不能有相同的列名
select a.*, d.dname
from (select e.empno, e.ename, e.deptno, e1.empno empno1, e1.ename ename1
from emp e
left join emp e1
on e.mgr = e1.empno) a,
dept d
where a.deptno = d.deptno ; 左右连接转换
select * from dept d left join emp e on d.deptno = e.deptno;
select * from emp e right join dept d on e.deptno = d.deptno;
select * from emp e left join dept d on e.deptno = d.deptno
4.分组函数
1.统计记录数count()
范例：查询出所有员工的记录数
select count(empno) from emp;
不建议使用count(*)，可以使用一个具体的列以免影响性能。
2.最小值查询min()
范例：查询出来员工最低工资
select min(sal) from emp;
3.最大值查询max()
范例：查询出员工的最高工资
select max(sal) from emp;
4.查询平均值avg()
范例：查询出员工的平均工资
select avg(sal) from emp;
5.求和函数sum()
范例：查询出20部门的员工的工资总和
select sum(sal) from emp e where e.deptno = 20;
5.分组统计(group by)
分组统计需要使用GROUPBY来分组
语法：语法：SELECT *|列名 FROM 表名 {WEHRE 查询条件} {GROUP BY 分组字段} ORDER BY 列名1 ASC|DESC，列名2...ASC|DESC
范例：查询每个部门的人数
select deptno, count(empno) from emp group by deptno ;
范例：查询出每个部门的平均工资
select deptno, avg(sal) from emp group by deptno;
--查询公司中每种工作的人数
select job, count(*) from emp group by job;
--查询每个部门内每种工作的人数
select deptno, job, count(*) from emp group by deptno, job order by deptno ;
如果我们想查询出来部门编号，和部门下的人数
我们发现报了一个ORA-00937的错误
注意：
1. 如果使用分组函数，SQL只可以把GOURP BY分组条件字段和分组函数查询出来，不能有其他字段。
2. 如果使用分组函数，不使用GROUP BY 只可以查询出来分组函数的值
范例：按部门分组，查询出部门名称和部门的员工数量
select d.dname, count(e.empno) from emp e, dept d where e.deptno = d.deptno group by d.dname ;
--group by 后面有的字段，select后才可以有，group by后面没有的字段，select后面绝对不能有
select d.dname, d.loc, count(e.empno) from emp e, dept d where e.deptno = d.deptno group by d.dname, d.loc ;
范例：查询出部门人数大于5人的部门
分析：需要给count(ename)加条件，此时在本查询中不能使用where，可以使用HAVING
/*count(*),如果整条记录存在不管字段是否是空都当做记录来统计， 如果count(empno)那么如果字段是空则不做统计
*/
select d.deptno, d.dname, count(e.empno) from emp e, dept d where e.deptno(+) = d.deptno group by d.deptno, d.dname; select d.deptno, d.dname, count(e.empno) pcount from emp e, dept d where e.deptno(+) = d.deptno group by d.deptno, d.dname having count(e.empno) > 5;
范例：查询出部门平均工资大于2000的部门
select e.deptno, d.dname, avg(e.sal) from emp e, dept d where e.deptno = d.deptno group by e.deptno, d.dname having avg(e.sal) > 2000 ;
范例：显示非销售人员工作名称以及从事同一工作的员工的月工资的总和，并且要满足从事同一工作月工资总和大于5000，结果按月工资总和的升序排列。
分析：
1. 查询出非销售人员
2. 以步骤1为基础按着工作分组求工资的总和
3. 以步骤2为基础查询出月工资总和大于5000的工作
4. 按着月工资的总和的升序排列
select e.job, sum(e.sal) from emp e where e.job <> 'SALESMAN' group by e.job having sum(e.sal) > 5000 order by sum(e.sal) asc;
注意：只有分组条件在结果集中是重复的分组才有意义。
--查询每个部门工资最低的人的员工编号，名字，工资
select e.*, a.deptno, nvl(a.lowestsal, 0)
from emp e,
(select d.deptno, min(t.sal) lowestsal
from emp t, dept d
where t.deptno(+) = d.deptno
group by d.deptno) a
where e.sal(+) = a.lowestsal
and e.deptno(+) = a.deptno
select b.deptno 部门编号,nvl(a.ename,'无') 员工姓名, nvl(b.minsal,0) 部门员工最低工资 from (select e1.ename,e1.deptno,e1.sal from emp e1 ) a, (select d.deptno, min(e.sal) minsal from emp e ,dept d where d.deptno=e.deptno(+) group by e.deptno ,d.deptno) b where a.sal(+)=b.minsal and a.deptno(+)=b.deptno
7.子查询
子查询：在一个查询的内部还包括另一个查询，则此查询称为子查询。
Sql的任何位置都可以加入子查询。
范例：查询比7654工资高的雇员
分析：查询出7654员工的工资是多少，把它作为条件
select * from emp t where t.sal > (select e.sal from emp e where e.empno = 7654);
所有的子查询必须在“（）”中编写
子查询在操作中有三类：
单列子查询：返回的结果是一列的一个内容(条件=)
单行子查询：返回多个列，有可能是一个完整的记录
多行子查询：返回多条记录(in)
范例：查询出比雇员7654的工资高，同时从事和7788的工作一样的员工
select * from emp t where t.sal > (select e.sal from emp e where e.empno = 7654) and t.job = (select e1.job from emp e1 where e1.empno = 7788);
范例：要求查询每个部门的最低工资和最低工资的雇员和部门名称
select e.*, a.deptno, nvl(a.lowestsal, 0)
from emp e,
(select d.deptno, min(t.sal) lowestsal
from emp t, dept d
where t.deptno(+) = d.deptno
group by d.deptno) a
where e.sal(+) = a.lowestsal
and e.deptno(+) = a.deptno
在返回多条记录的子查询可以把它的结果集当做一张表，给起个别名，如图中的a。
范例：查询出来每个部门最低工资的员工
Exists和not exists关键字的用法：
exists （sql 返回结果集为真）
not exists (sql 不返回结果集为真）
比 in性能高 开发中代替in
范例：查询出有员工的部门有哪些？
select * from dept t where t.deptno in (select distinct t.deptno from emp t) select * from dept t where exists (select * from emp e where t.deptno = e.deptno) select * from dept t where not exists (select * from emp e where t.deptno = e.deptno)
Union和Union All的用法
Union：对两个结果集进行并集操作，不包括重复行。
--查询工资大于1000和工资大于2000的员工
select * from emp t where t.sal > 1000
union
select * from emp t where t.sal > 2000;
Union All：对两个结果集进行并集操作，包括重复行。
select * from emp t where t.sal > 1000
union all
select * from emp t where t.sal > 2000;
/* union必须要保证列数相同, 如果合并的列的类型不一致不能合并(如果类型一致不管列的名字是否一致都能合并但是没意义) ,
要求合并的时候列的数量和名字和类型都一致
*/
8数据库变更
为了保存原始emp的信息保存，我们复制一份emp表
Create table myemp as select * from emp;
1. Insert（增加）
语法：
INSERT INTO表名[(列名1，列名2，...)]VALUES(值1，值2，...)
标准写法
insert into myemp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
values(8000, ‘张三‘,‘开发‘, 7839, to_date(‘1981-9-11‘,‘yyyy-mm-dd‘), 1000, 200, 10);
简单写法（不建议）
INSERT INTO 表名VALUES(值1，值2，...)
insert into myemp values(7789, ‘张三‘, ‘开发‘, 7839, to_date(‘1992-10-22‘,‘yyyy-MM-dd‘), 2000, 200, 10);
注意：使用简单的写法必须按照表中的字段的顺序来插入值，而且如果有为空的字段使用null
insert into myemp values(7790, ‘张三‘, ‘开发‘, null, to_date(‘1992-10-22‘,‘yyyy-MM-dd‘), 2000, null, 10);
2. update（修改）
全部修改：UPDATE 表名 SET 列名1=值1，列名2=值2，....
局部修改：UPDATE 表名 SET 列名1=值1，列名2=值2，....WHERE 修改条件；
update myemp t set t.sal = 1000, t.job = '保洁', t.deptno = 20 where t.empno = 8000; update myemp t set t.comm = nvl(t.comm, 0) + 100 where t.empno in (8000, 8001, 8002);
3. delete（删除）
语法 : DELETEFROM 表名 WHERE 删除条件;
--在mysql中删除是不能有别名的
delete from myemp t where t.empno = 8000;
--在oracle中可以省略from 在sql不可省略
delete myemp t where t.empno = 8001;
在删除语句中如果不指定删除条件的话就会删除所有的数据
Mysql中删除不支持别名
Oracle 中删除语句的 from 可以省略
9.事物处理
范例：删除员工7934；
从结果上看似乎数据已经删除，但是我们再打开另一个窗口查看发现7934的员工还在
事务处理：所谓的事务管理就是要保证数据的完整性，要么同时成功，要么同时失败
当我们执行完delete语句时，我们去数据库中去查看数据，发现并没有我们删除数据，这是因为oracle的事务对数据库的变更的处理，我们必须做提交事务才能让数据真正的插入到数据库中，在同样在执行完数据库变更的操作后还可以把事务进行回滚，这样就不会插入到数据库。如果事务提交后则不可以再回滚。
提交：commit
回滚：rollback
在oracle中会数据库变更会发生锁的情况（此处应用可以解决项目多线程并发带来的数据安全问题）
当两个数据库的连接同时来修改同一数据时，一定会有一连接先修改，另一个连接就会等待直到第一个连接修改完毕再修改
模拟电商购物 并发问题解决思路 使用乐观锁
更新前并发查询导致更新后库存数量错误
模拟订单提交：需要对数据库做3件事，保存订单，保存订单明细，扣减库存
orders order_detail item
public void saveOrder(Orders order, List<OrderDetail> detailList) {
//保存订单并且返回主键
orderDao.save(order);
for(OrderDetail detail : detailList){
detail.setOrder(order.getOrder());
detailDao.saveDetail(detail);
Item item = itemDao.getItemById(detail.getItemId());//A和B同时执行,查询出相同的数据库存都是100
item.setStock(item.getStock - detail.getQuantity);
itemDao.update(item);//此时更新虽然有锁,但为时已晚,出现并发问题
}
}
A和B同时执行查询
item_id stock
1000 100
1000 100
A(2)买2个 1000 98
B(3)买3个 1000 97使用乐观锁解决此问题(悲观锁应用于互联网项目性能太低)
加入 version 字段 控制版本
item_id stock version
1000 100 1
A(2) 1000 98
B(3) 1000 97
A和B同时执行
1000 100 1
itemDao.updateWithVersion(item);
update item t set t.stock = #{stock}, t.version = t.version + 1 where t.item_id = #{itemId} and t.version = #{version} and t.stock > #{quantity}
假设A抢到了cpu先执行update，b被阻塞在外，A会把数据改成98，版本变成2
1000 98 2
当B得到执行的时候where t.item_id = #{itemId} and t.version = #{version} 条件导致update数据相应条数是0
public void saveOrder(Orders order, List<OrderDetail> detailList) {
//保存订单并且返回主键
orderDao.save(order);
for(OrderDetail detail : detailList){
detail.setOrder(order.getOrder());
detailDao.saveDetail(detail);
Item item = itemDao.getItemById(detail.getItemId());//A和B同时执行
item.setStock(item.getStock - detail.getQuantity);
int flag = itemDao.updateWithVersion(item);//返回数据的响应条数
if(flag == 0){
throw StockException(itemId);//抛出运行时异常让事务回滚
}
}
}
在Controller中
public String submitOrder(){
try{
orderService.void saveOrder(Orders order, List<OrderDetail> detailList) ;
}catch(Exception e){
if(e instance of StockException){
//判断是并发导致的回滚还是库存导致的
String itemId = e.getMessage();
//根据itemId查询一下库存够不够
if(库存够){
this.submitOrder();
}else{
model.addAttribute("对不起你买的货已经") ;
return "fail"
}
}
}
return "success"
}
10.表的管理
1.常用的数据类型
| No | 数据类型 | 描述 |
| 1 | Varchar， varchar2 | 表示一个字符串 |
| 2 | NUMBER | NUMBER(n)表示一个整数，长度是n / NUMBER(m,n):表示一个小数，总长度是m，小数是n，整数是m-n |
| 3 | DATA | 表示日期类型 |
| 4 | CLOB | 大对象，表示大文本数据类型，可存4G |
| 5 | BLOB | 大对象，表示二进制数据，可存4G |
2建表
语法：
Create table 表名（
字段1 数据类型 [default 默认值],
字段2 数据类型 [default 默认值],
...
字段n 数据类型 [default 默认值]
）;
范例：创建person表
create table person(
pid number(10),
name varchar2(10),
gender number(1) default 1,
birthday date
);
insert into person(pid, name, gender,birthday)
values(1, '张三', 1, to_date('1999-12-22','yyyy-MM-dd'));2.表删除
语法：DROP TABLE 表名
3.表的修改
在sql中使用alter可以修改表
添加语法：ALTERTABLE 表名称 ADD(列名1 类型 [DEFAULT 默认值]，列名1 类型 [DEFAULT 默认值]...)
修改语法：ALTERTABLE 表名称 MODIFY(列名1 类型 [DEFAULT 默认值]，列名1 类型 [DEFAULT 默认值]...)
注意修改时如果原有某列的数据长度为200，则不可以把该列修改成小于200的列
范例：在person表中增加列address
alter table person add(addressvarchar2(10));
范例：把person表的address列的长度修改成20长度
alter table person modify(address varchar2(20));
4. 截断表
在person表中使用delete语句删除数据，则可以使用rollback来回滚，如果想要清空一张表的数据，同时不想回滚可以立即释放资源需要使用截断表的语法
语法：TRUNCATE TABLE 表名
范例：截断person表
truncate table person;
11.约束
在数据库开发中，约束是必不可少，使用约束可以更好的保证数据的完整性。
1.主键约束
主键约束都是在id上使用，而且本身已经默认了内容不能为空，可以在建表的时候指定。
创建一张表，把pid作为主键
create table person(
pid number(10) primary key,
name varchar2(10),
gender number(1) default 1,
birthday date
);主键不可重复，SCOTT.SYS_C0017981是系统自动分配的约束的名字
主键不可为空
我们可以自己来指定主键约束的名字
create table person(
pid number(10),
name varchar2(10),
gender number(1) default 1,
birthday date,
constraint person_pk_pid primary key(pid)
);
2.非空约束
使用非空约束，可以使指定的字段不可以为空。
范例：建立一张pid和name不可以为空的表
create table person(
pid number(10) not null,
name varchar2(10) not null,
gender number(1) ,
birthday date,
);
3.唯一约束（unique）
表中的一个字段的内容是唯一的
范例：建表一个name是唯一的表
create table person(
pid number(10) ,
name varchar2(10) unique,
gender number(1) ,
birthday date
);
唯一约束的名字也可以自定义
create table person(
pid number(10) ,
name varchar2(10),
gender number(1) ,
birthday date,
constraint person_name_uk unique(name)
);
4.检查约束
使用检查约束可以来约束字段值的合法范围。
范例：创建一张表性别只能是1或2
create table person(
pid number(10) ,
name varchar2(10),
gender number(1) check(gender in (1, 2)),
birthday date
);
检查约束也可以自定义
create table person(
pid number(10) ,
name varchar2(10),
gender number(1),
birthday date,
constraint person_gender_ck check(gender in (1,2))
);
5.外键约束
之前所讲的都是单表的约束，外键是两张表的约束，可以保证关联数据的完整性。
范例：创建两张表，一张订单表，一张是订单明细表，订单和明细是一对多的关系
create table orders(
order_id number(10) ,
total_price number(10,2),
order_time date,
constraint orders_order_id_pk primary key(order_id)
);create table order_detail(
detail_id number(10) ,
order_id number(10),
item_name varchar2(10),
quantity number(10),
constraint order_detail_detail_id_pk primary key(detail_id)
);
insert into orders values(1, 200, to_date(‘2015-12-12‘,‘yyyy-MM-dd‘));
insert into order_detail values(1, 2,‘java‘,1);
我们在两张表中插入如上两条数据，我们发现在order_detail表中插入的order_id在order表中并不存在，这样在数据库中就产生了脏数据。此时需要外键来约束它。
我们再次建表
create table orders(
order_id number(10) ,
total_price number(10,2),
order_time date,
constraint orders_order_id_pk primary key(order_id)
);
create table order_detail(
detail_id number(10) ,
order_id number(10),
item_name varchar2(10),
quantity number(10),
constraint order_detail_detail_id_pk primary key(detail_id),
constraint order_detail_order_id_fk foreign key(order_id) referencesorders(order_id)
);
外键关联一定注意：
外键一定是主表的主键
删表时一定先删字表再删主表，如果直接删主表会出现由于约束存在无法删除的问题
但是可以强制删除droptable orders cascade constraint;(不建议)
删除主表的数据可以删除子表的关联数据，再删主表，也可以使用级联删除级联删除在外键约束上要加上on delete cascade 如
constraint order_detail_order_id_fk foreignkey(order_id)
references orders(order_id) on delete cascade
这样删除主表数据的时候会把字表的关联数据一同删除
12. rownum 分页
ROWNUM:表示行号，实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现。
范例：查询emp表带有rownum列
select rownum, t.* from emp t
我们可以根据rownum来取结果集的前几行，比如前5行
select rownum, t.* from emp t where rownum < 6;
但是我们不能取到中间几行，因为rownum不支持大于号，只支持小于号，如果想实现我们的需求怎么办呢？
答案是使用子查询，也正是oracle分页的做法。
select *
from (select rownum rm, a.* from (select * from emp) a where rownum <11) b where b.rm > 5
select rownum, t.* from emp t ;
--rownum不支持大于号
select rownum, t.* from emp t where rownum > 6;
/*
startNum = (pageNo - 1)*pageSize
endNum = pageNo * pageSize + 1
pageSize pageNo startNum endNum
5 1 0 6
5 2 5 11
5 3 10 16
*/
---建议使用
select *
from (select rownum rw, a.* from (
select * from emp
) a where rownum < 16) b
where b.rw > 10;
select *
from (select rownum rw, t.* from emp t where rownum < 16) a
where a.rw > 10;
select * from (select * from emp) where rownum > 1;
13. 视图
视图就是封装了一条复杂查询的语句。
语法1.：CREATE VIEW 视图名称 AS 子查询
范例：建立一个视图，此视图包括了20部门的全部员工信息
create view empvd20 as select * from emp twhere t.deptno = 20
视图创建完毕就可以使用视图来查询，查询出来的都是20部门的员工
select * from empvd20;
语法2：CREATE OR REPLACE VIEW 视图名称 AS 子查询
如果视图已经存在我们可以使用语法2来创建视图，这样已有的视图会被覆盖。
create or replace view empvd20 as select *from emp t where t.deptno = 20
那么视图可以修改吗？
我们尝试着修改视图但是发现是视图所查询的表的字段值被修改了。所以我们一般不会去修改视图。
我们可以设置视图为只读。
语法3：CREATE OR REPLACE VIEW 视图名称 AS 子查询 WITH READ ONLY
create or replace view empvd20 as select *from emp t where t.deptno = 20 with read only
14. 序列
在很多数据库中都存在一个自动增长的列,如果现在要想在oracle 中完成自动增长的功能, 则只能依靠序列完成,所有的自动增长操作,需要用户手工完成处理。
语法：CREATESEQUENCE 序列名
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE/ MINVALUE n|NOMAXVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n|NOCACHE}];
范例:创建一个seqpersonid的序列,验证自动增长的操作
CREATE SEQUENCE seqpersonid;
序列创建完成之后,所有的自动增长应该由用户自己处理,所以在序列中提供了以下的两种操作：
NextVal :取得序列的下一个内容
CurrVal :取得序列的当前内容
select seqpersonid.nextval from dual;
select seqpersonid.currval from dual;
在插入数据时需要自增的主键中可以这样使用
select seqpid.currval from dual; select seqpid.nextval from dual; insert into person values(seqpid.nextval, '张三', 0, sysdate); select * from person; insert into orders values(seqpid.nextval ,100, '软件园', sysdate);
在实际项目中每一张表会配一个序列，但是表和序列是没有必然的联系的，一个序列被哪一张表使用都可以，但是我们一般都是一张表用一个序列。
序列的管理一般使用工具来管理。
15索引
索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o 次数,
从而提高数据访问性能。索引有很多种我们主要介绍常用的几种:
为什么添加了索引之后，会加快查询速度呢？
图书馆：如果杂乱地放书的话检索起来就非常困难,所以将书分类，然后再建一个箱子，箱
子里面放卡片，卡片里面可以按类查询,按姓名查或者类别查,这样的话速度会快很多很多，
这个就有点像索引。索引的好处就是提高你找到书的速度，但是正是因为你建了索引，就应
该有人专门来维护索引，维护索引是要有时间精力的开销的，也就是说索引是不能乱建的，
所以建索引有个原则：如果有一个字段如果不经常查询，就不要去建索引。
现在把书变成我们的表，把卡片变成我们的索引，就知道为什么索引会快，为什么会有开销。
创建索引的语法：
创建索引：
1．单例索引
单例索引是基于单个列所建立的索引，比如:
CREATE index 索引名 on 表名(列名)
2．复合索引
复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引，但是
要求列的组合必须不同,比如：
Create index emp_idx1 on emp(ename,job);
Create index emp_idx1 on emp(job,ename);
范例：给person表的name建立索引
create index pname_index on person(name);
范例：给person表创建一个name和gender的索引
createindex pname_gender_index on person(name, gender);
索引的使用原则：
? 在大表上建立索引才有意义
? 在where子句后面或者是连接条件上建立索引
? 索引的层次不要超过4层
数据库 day60,61 Oracle入门,单行函数,多表查询,子查询,事物处理,约束,rownum分页,视图,序列,索引
标签：
本文系统来源：http://blog.csdn.net/opopopwqwqwq/article/details/51731006
内容总结
以上是互联网集市为您收集整理的数据库 day60,61 Oracle入门,单行函数,多表查询,子查询,事物处理,约束,rownum分页,视图,序列,索引全部内容，希望文章能够帮你解决数据库 day60,61 Oracle入门,单行函数,多表查询,子查询,事物处理,约束,rownum分页,视图,序列,索引所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。