数据库系统概论

数据库系统概论

一、DBMS（Database Management System）数据库管理系统

oracle（甲骨文） oracle 关系型数据库 RMDBMS

​ mysql 关系型数据库

Microsoft（微软） sqlserver 关系型数据库

​ access

Mongodb 大数据 文档存储型数据库

IBM DB2 关系型数据库

sybase

infomix

foxbase

达梦数据库

redis

• 数据库：顾名思义就是数据的仓库，它是长期存储在计算机内、有组织的、可共享的数据的集合。
• 数据库管理系统（DBMS）：用来对数据进行存储、管理等操作的系统级软件。

二、数据库系统分类

• 文件系统

  -数据库系统的萌芽阶段，通过文件来存取数据

• 层次型数据库

  -数据库系统真正开始阶段，数据的存储形式类似树形结构，所以也叫树形数据库

• 网状数据库

  -数据的存储形式类似网状结构

• 关系型数据库

• 面向对象数据库

NO-SQL

三、SQL（structure query language结构化查询语句）

-SQL是关系型数据库的标准语言，用于存储数据以及查询、更新和管理数据库系统

-SQL是高级的非过程化编程语言，允许用户在高层数据结构上工作

-它不要求用户指定对数据的存放，也不需要用户了解具体的数据存放方式，所以针对不同底层结构的不同数据库系统，可以使用相同的结构化查询语句作为数据输入与管理的接口

四、SQL的分类组成

-数据定义语言（Data Definition Language,DDL）用于定义SQL模式、表、视图、索引等数据库对象结构

-数据操作语言（Data Manipulation Language,DML）用于插入、删除和更改数据

-数据查询语言（Data Query Language,DQL）用于查询数据，通常将数据操作语言和数据查询语言统称为数据操作语言

-数据控制语言（Data Contral Language,DCL）用于对表、视图等的授权、完整性规则的描述和事务控制等

oracle数据的发展：8i–9i–10g–11g–12c

​ internet grid cloud

五、Oracle启动关闭

六、常用的客户端

1.sqlplus(重点了解)

（1）进入方式

a.通过安装目录

b.运行----sqlplus进入

c.运行----cmd----sqlplus

oracle开通的4个账号

系统管理员SYS SYSTEM AS SYSDBA

普通用户 SCOTT HR

(2).sqlplus怎么登陆

a.无用户名和密码登陆（使用os校验 ，必须保证sqlplus和oracle在同一服务器上）

​ sqlplus / as sysdba;

b. 使用用户名和密码登陆

​ sqlplus sys/123 as sysdba;

c.

2.sql developer(安装oracle自带的)

3.plsql developer客户端工具（常用工具）

4.Oracle企业管理器 （Oracle Enterprise Manager，OEM）

5.navicat for oracle客户端工具

七、sqlplus常用命令

1.登录

​ （1）sqlplus / as sysdba

​ 默认使用系统管理员权限，前提必须是oracle所在服务器本身才可以

​ （2）sqlplus /nolog;

​ conn / as sysdba;

​ （3）sqlplus user/password as sysdba;

2.解锁

​ 必须使用系统管理员用户

​ alter user scott account unlock;

​ //锁定

​ alter user scott account lock;

3.重设密码

​ alter user scott identified by 123;

4.查询所有表

​ select * from tab;

5.设置列宽

​ set linesize 150;

​ set pagesize 0;

6.显示当前数据库

​ 需要在管理员下运行

​ select name from v&database;

​ show parameter db_name;

7.显示账户信息

​ select username,account_status from

8.显示当前用户

show user；

八、查询与排序

一、数据库基本管理

1.解锁用户（必须以系统管理员SYSDBA身份才可以修改）

alter user hr account unlock; //解锁用户

alter user hr account lock;//锁定用户

2.修改密码

alter user hr identified by 123；//修改hr用户密码为123

3.查询表（当前表空间hr所有的表） scott账号 scheme方案

select * from tab；

select table_name from tabs;

4.显示账户信息（以dba身份进行查询）

select username，account_status from dba_users;

5.显示当前用户

show user；

6.显示当前数据库的名字（以dba身份进行查询）

select name from v$database;

show parameter db_name;

二、常用表结构（scott）

• scott用户常用表有三个

  –dept 部门表

  –emp 员工表

  –salgrade 薪水等级表

• 查看表结构命令

  desc 表名，例如可以使用desc dept命令来显示dept表的结构

emp员工表

dept部门表

salgrade薪水等级表

三、查询表中的数据（DQL）“重点”

sqlplus常用：

编辑 ed aa（文件名：aa.sql）

执行@aa

1.查询表中所有数据

​ select * from 表名;

(1)查询员工表所有数据

​ select * from emp;

2.查询指定字段信息

​ select 字段1名字,字段2名字… from 表名

(1)查询员工编号，员工姓名，员工薪资

​ select empno,ename,sal from emp;

3.计算列

（1）查询每个员工的年薪

​ select ename,sal*12 from emp;

4.虚表dual（作用：为了满足SQL语句有一个标准 select … from …）

（1）计算128*256

select 128*256 from dual;

(2)获取系统时间

select sysdate from dual;

5.字段别名

（1）查询每个员工的年薪

​ select ename,sal*12 as 年薪 from emp;

(2)简写

select ename,sal*12 年薪 from emp;

(3)给表起别名

select e.ename,e.sal from emp e;

注意：在oracle的sql中，表名、字段名不区分大小写

​ 但表中的数据是区分大小写的

oracle中双引号，表名、字段名区分大小写

​ 或者使用关键字可以通过加双引号变成普通的表名或者字段名

单引号 表示字符串

四、NVL函数

查看员工每月获得的总的报酬

当comm字段里值为null的，任意值与之计算结果都为null

nvl（comm，0）当comm字段的值为null时，让其函数的值为0

select ename,nvl(comm,0) from emp;

五、字符串连接

java +

oracle || ‘姓名’||’:’

六、去重distinct

1.查看该公司的员工分布在哪几个部门

select ename,distinct deptno from emp;//ename无法确定值

2.查看每个部门有哪些岗位

七、条件过滤行where

注：在sql中 字段名是不区分大小写的 ENAME与ename都可以使用

​ 字段的值是区分大小写的 smith/SMITH/Smith都是代表不同的值

（1）等值过滤 =

​ 查询工资每月1600的员工姓名和工作岗位

select ename,job from emp where sal=1600

​

​ 查询姓名为SMITH的所有信息

select * from emp where ename=‘SMITH’ (要用大写的SMITH)

（2）非等值过滤 > < >= <= <>或!=

查询每月工资大于1500的员工姓名及薪资

select ename,sal from emp where sal>1500

(3) between 值1 and 值2 表示在值1和值2之前的值

查询工资在2000到3000之间的职员信息

select * from emp where sal between 2000 and 3000

（4）NULL值 （NOT NULL）

查询没有奖金的员工的信息 IS NULL

select * from emp where comm=0（错误的，0不代表null）

select * from emp where comm is null;

select * from emp where comm!=null;//不可以使用!=null作为过滤条件

查询有奖金的员工的信息 IS NOT NULL

select * from emp where comm is not null

(5)多个条件查询 and or in not in

查询部门是30,工资超过1300的员工信息

select * from emp where deptno=30 and sal>1300

查询工资为1300，1500，3000的员工的信息(使用or)

select * from emp where sal=1300 or sal=1500 or sal=3000

查询工资为1300，1500，3000的员工的信息(使用in)

select * from emp where sal in(1300,1500,3000)

(6)模糊查询 like

查询名字是A打头的员工信息

select * from emp where ename like ‘A%’；

查询名字里边包含AR的员工的信息

select * from emp where ename like ‘%AR%’;

查询名字以D结尾的员工的信息

select * from emp where ename like ‘%D’;

查询名字以D结尾（前边只有2个字符的名字）的员工的信息,

select * from emp where ename like ‘__D’;

八、排序 order by

select * from 表名 order by 排序的字段 asc（升序） desc（降序）

(1)按照薪资从低到高的顺序查询所有员工信息

select * from emp order by sal asc;

select * from emp order by sal;//默认排序方式是asc升序

(2)查看30部门的员工姓名及薪资，按薪资从高到低排序

select ENAME,SAL from emp where deptno=30 order by sal desc;

(3)查看员工信息按照部门编号从低到高，再按照薪资从高到低

select * from emp order by deptno asc,sal desc

注意：order by a,b 首先先按a字段排序，排好后再根据同一个a字段下继续排b

九、Oracle常用单行函数

一、函数的分类

函数：oracle提供能实现某些特定功能的。

分类：

–字符处理类函数

–数字、日期类函数

–转换函数

–通用函数

二、字符串处理函数

1.把所有员工的名字以小写方式输出 lower（字段名）

select lower（ename） from emp；

可以使用java中的String类提供的equalsIgnoreCase()可以实现大小写不同的值比较

2.大写 upper（字段名）

select upper（ename）from emp；

常用函数

三、数字、日期类型函数

（1）数字函数

–Round(参数1,[参数2])

功能:四舍五入，参数2可选，参数2表示保留几位小数

​

​

-Trunc(参数1,[参数2])

功能:截断数字(直接忽略),参数2表示截断到的小数位，如果参数2忽略则默认为0

​

​

查询每个员工的入职年数 date日期 time

​

（2）日期函数

–需求3:下个周三是几号?

使用函数 NEXT_DAY(date,char)

date指定日期,char周几，计算指定日期的下一个周几是几号

注意:1表示周日2表示周1以此类推

​

​

select round(sysdate,‘day’) from dual;–以周为单位

ROUND和TRUNC函数用于日期时，这些函数按指定的格式化模板四舍五入或截断，默认到日，也可以四舍五入或截断到月或年,如上图所示

注意：

–在英文系统中，月用三个字母作为缩写

–1到12月分别是：JAN、FEB、MAR、APR、MAY、JUN、JUL、AUG、SEP、OCT、NOV、DEC，周用具体的英文字母表示

–周1到周日分别是：SUNDAY、MONDAY、TUESDAY、WEDNESDAY、THURSDAY、FRIDAY、SATURDAY

–在中文系统（字符集）下，月和周的表示方式更简单，直接用1月、2月…12月，星期一、星期二…星期日表示

修改oracle默认的显示日期格式

系统级修改：alter system set nls_date_format=‘yyyy-mm-dd hh24:mi:ss’ scope=spfile

​ 配置环境变量NLS_DATE_FORMAT = ‘yyyy-mm-dd hh24:mi:ss’

​ 重启oracle数据库的服务才能生效，建议慎用，否则spfile文件损坏或者出错，oracle不 能正常启动

会话级修改：alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss’;

六、转换函数

1.to_char(参数)

–功能:将指定参数转换成字符

需求1:转换货币格式

9表示一位数字，如果该位没有数字则不显示,但小数点后的数字必须显示

to_char() 是oracle 的字符转换函数。to_char(value，‘format’) value是待转化的值，‘format’ 是转化后的 pattern。

这里样式 fm9900.0099的包括如下含义：

• 输出的字符串总共占了9位，小数点前4位，小数点后4位。
• 9代表含义，如果这个位置是数字就显示数字，否则就用空格替代占位，总之要占住这个位置。
• 0代表含义，如果这个位置是数字就显示数字，否则就用0替代占位，总之要占住这个位置。

需求2:格式化日期格式

select to_char(sysdate) from dual;

select to_char(sysdate,‘yyyy-mm-dd hh24:mi’) from dual;

2.to_date(参数)

功能:将指定参数转换成date类型

需求：查询在1981年5月1日前入职的员工的信息

select * from emp where hiredate

3.to_number(参数)

功能：把参数转换成number类型

字符3和字符9的和

select to_number(‘3’)+to_number(‘9’) from dual;

七.通用函数

1.nvl()函数 nvl(comm,0)

2.NVL2函数

–NVL2(参数1，参数2，参数3)

功能:参数1不是null 返回参数2

参数1是null 返回参数3

需求:有年终奖comm的员工过节费是月薪的30%

没有年终奖的员工过节费是月薪的50%

select ename,nvl2(comm,sal0.3,sal0.5) as 过节费 from emp

十、组函数和多表查询

一、组函数

•什么是组函数

–也叫聚合函数，多行输入一行输出。普通函数是一行输入一行输出

max() 求最大值

min() 求最小值

sum() 求和

avg() 求平均值

count() 求记录数（record）

select count(*) from emp;//emp表总共有多少条记录

需求：求最大工资值

select max（sal） from emp;

需求：谁工资最高

select ename from emp where sal=(select max（sal） from emp);

二、分组

•使用group by对查询后的记录进行分组

–需求1:求每个部门的平均薪水

select deptno,avg(sal) as avg_sal from emp group by deptno

•group by分组规则

–如果分组了,select后面的字段要么是分组的条件，要么是五个组函数之一

–小技巧:一说每个就要分组了哦，而且每后面的就是分组的条件

•需求2：每个部门同一个职位的最高工资

select deptno ,job,max(sal) from emp group by deptno,job

三、使用having过滤

•使用having对分组以后的数据进行再过滤

–where 语句是对单条记录进行过滤的，不能过滤分组之后的记录

执行语句的时候首先执行where 语句 然后把查到的记录进行分组

要想过滤分组后的数据，用having

–需求1:按照部门编号进行分组，分组之后求每一个部门的平均薪水，要求显示平均薪水大于2000的部门的部门编号和平均薪水

select deptno,avg(sal) from emp

group by deptno

having avg(sal)>2000;

•需求2:薪水大于1200的雇员，按照部门编号进行分组，分组之后平均薪水必须大于1500,求分组内的平均工资，平均工资按倒序排列

select avg(sal) from emp where sal>1200

group by deptno having avg(sal)>1500 order by avg(sal) desc

•Sql语句总结(共五行)

select *

from emp

where 条件(avg(sal)>2000不允许，分组后才平均）

group by 分组字段

having 对分组后的数据进行过滤

order by 分组后的数据进行排序

四、子查询（嵌套查询）

•查询的分类

–简单查询:是查一次 一个select

–复杂查询分为子查询和连接查询

•子查询(也叫嵌套查询)

–查多次，多个select嵌套出现,第一次的查询结果可以作为第二次的查询条件 或 表名

–需求:求工资最高那个人的姓名和薪水

第一步:求最大工资 select max(sal) from emp

第二步:求最大工资那个人叫什么

select ename,sal from emp where sal=5000

第三步 合并 select ename,sal from emp where sal=(select max(sal) from emp);

•需求2:哪些人得工资位于 所有人得平均工资之上

–1.求平均工资

select avg(sal) from emp

–2. 工资大于平均工资的 人的姓名、工资

select ename,sal from emp where sal>2073.21428571429

–3.合并

select ename,sal from emp where sal>(select avg(sal) from emp)

•需求:查SMITH的上司叫什么

–1.查SMITH的上司的编号

select mgr from emp where ename=‘SMITH’;–7902

–2.查上司叫什么

select ename from emp where empno = 7902;–FORD

–3.合并

select ename from emp where empno = (select mgr from emp where ename=‘SMITH’)

练习：

1.SMITH所在部门的名字

SELECT DEPTNO FROM EMP WHERE ENAME=‘SMITH’;

SELECT DNAME FROM DEPT WHERE DEPTNO=’’;

SELECT DNAME FROM DEPT WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME=‘SMITH’)

2.SALES部门工资最高的人 ER模型

SELECT DEPTNO FROM DEPT WHERE DNAME=‘SALES’;

SELECT MAX(SAL) FROM EMP WHERE DEPTNO=’’

SELECT * FROM EMP WHERE SAL=’’

SELECT * FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME=‘SALES’))

3.SALES部门的工资等级为3的员工信息

select losal from salgrade where grade=3

select hisal from salgrade where grade=3

select deptno from dept where dname=‘SALES’

select * from emp where deptno=(select deptno from dept where dname=‘SALES’’) and sal between (select losal from salgrade where grade=3) and (select hisal from salgrade where grade=3)

注：返回多行记录不能使用=，使用in

select * from emp where deptno = (select deptno from dept where dname=‘SALES’ OR DNAME=‘ACCOUNTING’)

五、连接查询（重点，难点）

•什么是连接查询

–查询多张表的数据,把多张表连接起来查询叫连接查询

•为什么用或什么时候用?

–当数据来自多张表的时候，考虑使用连接查询

•连接查询分类

–内连接 inner join

–外连接 outer join

–交叉连接（笛卡尔积）cross join

–自然连接 natural join

查询员工姓名、员工薪水、员工所在部门名称

1.使用交叉连接（笛卡尔积）

select ename,sal,dname from emp,dept where dept.deptno=emp.deptno

select * from emp cross jion dept （较少用的写法）

select * from emp,dept （等价上边的写法，常用写法）

2.使用内连接（特点：把所有不满足on连接条件的全部过滤掉）

select e.ename,e.sal,d.dname from emp e [inner] join dept d on e.deptno=d.deptno

select e.name,e.sal,d.dname from emp join dept d using(deptno)

使用using子句注意：（1）using只能用在等值连接

​ （2）using内字段不能加限定别名

3.左外连接（特点：左表是基表，先把满足on连接条件全部查出，然后把左表中不满足连接条件也全部显示出来，匹配表右表中没有与基表匹配的记录以null进行填充）

​

​

4.右外连接

​

外连接符号“+”

select * from emp e,dept d where e.deptno=d.deptno(+)

上边sql不是交叉连接，而是外连接，没有加号的是基表，有加号是匹配表

注意：（1）不能用join

​ （2）+只能用在列上，不能用在查询结果上

​ （3）不能与in 和 or用在一起

​ （4）+只能写在一边，不能两边都写，只能是左外连接或右外连接，不能是全外连接

5.全外连接

6.自然连接natural join

与内连接相似，可以省略on条件（会自动查找2张表存在关联的字段）

7、自连接（单表操作，用多表的方式）

查询员工的姓名及其上级的姓名

(1)交叉连接

select e1.ename 员工姓名,e2.ename 经理姓名 from emp e1,emp e2 where e1.mgr = e2.empno;

​

(2)内连接

select e1.ename 员工姓名,e2.ename 经理姓名 from emp e1 inner join emp e2 on e1.mgr = e2.empno;

（3）左外连接

select e1.ename 员工姓名,e2.ename 经理姓名 from emp e1 left join emp e2 on e1.mgr = e2.empno;

（4）右外连接（不适合）

（5）全外连接（不适合）

​

（6）自然连接（不适合）

练习：

1.DEPT表按照部门跟EMP表左关联

\2. 列出所有“CLERK”（办事员）的姓名及其部门名称。

\3. 列出所有雇员的雇员名称、部门名称和薪金(使用自然连接，using字句，on字句)

\4. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

\5. 列出所有员工的姓名及其直接上级领导的姓名

\6. 显示所有员工的姓名ename，部门号deptno和部门名称dname。(使用自然连接，using字句，on字句)

\7. 查询20号部门员工的job和20号部门的loc 20;

\8. 选择所有有奖金comm的员工的ename , dname , loc

9．选择在DALLAS工作的员工的ename , job , deptno, dname

\10. 显示雇员名、雇员工资及所在部门的名字，并按部门排序。(使用自然连接，using字句，on字句)

\11. 列出至少有一个员工的所有部门的部门名称（DNAME）和该部门的员工人数使用自然连接，using字句，on字句)

\12. 查询受雇日期早于其直接上级的所有员工的编号，姓名，部门名称

\13. 查询至少有4个员工的部门的部门名称。(使用自然连接，using字句，on字句)

\14. 查询部门名称中带’S’字符的部门的员工的工资总和部门人数，显示结果为部门名称，部门员工的工资总和，部门人数

\15. 查询出没有下属的员工的姓名及他的职位

复习：

复杂查询：

子查询（嵌套查询）

​ 最后要查询的结果一定是在同一个表中

​ 有多个select语句或关键字构成一个查询语句

​ select子查询可以充当select后边（作为一个查询字段）、from后边（作为一个表）、（条件）where后边

​ select (select max(sal) from emp)max_sal,ename from emp

​ select ename from (select ename,sal from (select * from emp where hiredate<‘1980-05-05’) where sal>1500) e1

​ select e1.ename from emp e1 where e1.sal >(select avg(e2.sal) from emp e2)

连接查询

1.交叉查询（笛卡尔积）cross join 使用where

​ select ename,dname from emp,dept where emp.deptno = dept.deptno

2.内连接 [inner] join 。。。on

​ A inner join B on A.aid=B.bid

​ inner join C on B.no=C.no

​ inner join D on A.aa = D.aa

​ 只有在满足A和B都不为全空的情况下

​ 只有满足on的连接条件的记录才显示出来

​ select ename,dname from emp inner join dept on emp.deptno = dept.deptno

​

​ 假如关联字段名相同的话，可以使用using子句

​ select ename,dname from emp [inner] join dept using(deptno)

3.外连接 outer join

​ 左外 left join 保证左表（基表）的数据要完整显示，假如右表没有匹配的记录则用null填充

​ 右外 right join 保证右表（基表）的数据要完整显示，假如左表没有匹配的记录则用null填充

​ 全外 full join 保证左右表（基表）的数据要完整显示，假如右左表没有匹配的记录则用null填充

​ oracle有全外连接，而mysql不支持全外连接

on 连接条件

where 过滤条件

select ename,dname from emp right join dept on emp.deptno = dept.deptno

select ename,dname,sal from emp e left join dept d on e.deptno=d.deptno and e.sal>1500;

select ename,dname,sal from emp e left join dept d on e.deptno=d.deptno where e.sal>1500;

4.自然连接 natural join，可以省略on（要求2张表中字段名及类型相同才可匹配）

​ select ename,dname from emp natural join dept

5.union 联合 要求2个结果集必须列的个数及类型都匹配

​ or

​ select ename,sal from emp where deptno=20

​ union

​ select ename,sal from emp where deptno=30

​ union与union all

​ union带去重功能

​ union all不带去重功能

​ 分库分表、分布式、负载均衡

SQL

DCL

DQL

RDBMS 关系型数据库管理系统

查询分为简单查询、复杂查询

简单查询一个查询，一个select

复杂查询分为子查询和连接查询

子查询（嵌套查询）

select * from emp where deptno in (select deptno from dept where loc=‘DALLAS’)

子查询语句可以使用在where子句，from，select,不能用在group by后

select * from (select * from emp where deptno=30)d30 where d30.ename=’’

select ename,(select max(comm) from emp)commax from emp

注：查询子句返回单行记录和多行记录

单行记录 = > < >= <= != <>

多行记录 IN NOT IN 集合，满足集合任意一个值，查询的是in中子集 sal in （select sal from emp where deptno=30）

​ ANY sal < any（select sal from emp where deptno=30 ） //any指的30部门的最大值

​ ALL sal < all（select sal from emp where deptno=30） //all指的30部门的最小值

判断 EXISTS() 存在就满足返回true 不存在返回false

子句的排序没有任何意义，会被忽略

连接查询

1.交叉连接查询 cross join（省略）笛卡尔积连接（使用where避免笛卡尔积）

select * from emp,dept <===> select * from emp cross join dept

2.内连接 inner join或join

select * from emp e inner join dept d on e.deptno=d.deptno

select * from emp e join dept d using(deptno)

3.自然连接 natural join ，不使用on子句,会自动去掉重复的列

select * from emp e natural join dept d;

4.外连接 不会去掉不匹配的记录，显示的条件是不匹配的记录在主表中

左外 左主右从 left outer join 或left join

右外 左从右主 right outer join 或right join

全外 左右都可以当做主表 full outer join或full join

5.自连接 把同一张表通过别名的方式变成多表

等值连接 连接条件使用=

非等值连接 连接条件使用> < != between and

显示员工姓名，工资及工资等级

select ename,sal,grade from emp e left join salgrade s on sal between losal and hisal

显示员工姓名，工资及工资等级，所在部门名称

select ename,sal,grade,dname

from emp e join dept d

​ on e.deptno=d.deptno

​ join salgrade s

​ on sal between losal and hisal

DML

DDL

十一、操作数据与管理表

一、记录的增删改DML

1.添加记录insert

（1）全值插入

​ insert into 表名 values(表字段1值，表字段2值.。。。。。)

​ insert into student values(20140910015,‘黄晓晨’,21,‘女’);

​ insert into student values(20140910015,‘林树森’,21,‘男’,‘11-11月-1995’)

​ insert into student values(20140910015,‘褚梦成’,21,‘男’,to_date(‘1995-11-20’,‘yyyy-mm-dd’))

​ insert into student values(20140910015,‘王凯’,21,‘男’,to_date(‘1996-11-20’,‘yyyy-mm-dd’))

（2）指定列进行插入

​ insert into student(sname,sno) values(‘何玉鑫’,20140910016);

​ (3)查询结果插入，批量插入

​ •INSERT INTO table1[(column1, column2…)]

​ SELECT col1, col2… FROM table2…

例子：insert into student select * from student2

​ insert into student_history（sno，sname，sage） select sno1，sname1，sage1 from student1 where gsdate<=‘1-9月-2013’

2.删除记录 delete

（1）清空表

​ delete [from] student

（2）删除指定的条件

​ delete from emp where sal>3000

3.修改记录update

所有人年龄增加一岁

update student set sage=sage+1，ssex=‘男’

修改王凯的出生日期

update student set sbirth=‘20-11月-1995’ where sname=‘王凯’

二、事务管理 Transcation

1. 什么是事务

在数据库中事务是工作的逻辑单元，一个事务是由一个或多个完成一组的相关行为的SQL语句组成，通过事务机制确保这一组SQL语句所作的操作要么都成功执行，完成整个工作单元操作，要么一个也不执行。

如：网上转帐就是典型的要用事务来处理，用以保证数据的一致性。

2. 事务特性

SQL92标准定义了数据库事务的四个特点ACID：

• 原子性(Atomicity)：一个事务里面所有包含的SQL语句是一个执行整体，不可分割，要么都做，要么都不做。
• 一致性(Consistency)：事务开始时，数据库中的数据是一致的，事务结束时，数据库的数据也应该是一致的。
• 隔离性(Isolation)：是指数据库允许多个并发事务同时对其中的数据进行读写和修改的能力，隔离性可以防止事务的并发执行时，由于他们的操作命令交叉执行而导致的数据不一致状态。
• 持久性 (Durability) : 是指当事务结束后，它对数据库中的影响是永久的，即便系统遇到故障的情况下，数据也不会丢失。

一组SQL语句操作要成为事务，数据库管理系统必须保证这组操作的原子性（Atomicity）、一致性（consistency）、隔离性（Isolation）和持久性（Durability），这就是ACID特性。

3. 数据异常

因为Oracle中支持多个事务并发执行，所以会出现下面的数据异常。

（1） 脏读

​ 当一个事务修改数据时，另一事务读取了该数据，但是第一个事务由于某种原因取消对数据修改，使数据返回了原状态，这是第二个事务读取的数据与数据库中数据不一致，这就叫脏读。

如：事务T1修改了一条数据，但是还未提交，事务T2恰好读取到了这条修改后了的数据，此时T1将事务回滚，这个时候T2读取到的数据就是脏数据。

读到事务未提交的数据，解决方法当修改数据的时候锁定该数据暂时不允许读取

​

（2） 不可重复读

​ 是指一个事务读取数据库中的数据后，另一个事务则更新了数据，当第一个事务再次读取其中的数据时，就会发现数据已经发生了改变，这就是不可重复读取。不可重复读取所导致的结果就是一个事务前后两次读取的数据不相同。

如：事务T1读取一行记录，紧接着事务T2修改了T1刚刚读取的记录，然后T1再次查询，发现与第一次读取的记录不同。

事务update和delete操作时，解决方法锁定该条记录即可

​

（3） 幻读

​ 如果一个事务基于某个条件读取数据后，另一个事务则更新了同一个表中的数据，这时第一个事务再次读取数据时，根据搜索的条件返回了不同的行，这就是幻读。

如：事务T1读取一条指定where条件的语句，返回结果集。此时事务T2插入一行新记录，恰好满足T1的where条件。然后T1使用相同的条件再次查询，结果集中可以看到T2插入的记录，这条新纪录就是幻读。

事务有insert操作的时候，解决方法锁定表

注：不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了，不可重复读主要是针对update和delete来说，而幻读则是针对insert来说

事务中遇到的这些异常与事务的隔离性设置有关，事务的隔离性设置越多，异常就出现的越少，就越低但并发效果，事务的隔离性设置越少，异常出现的越多，并发效果越高。

4. 事务隔离级别

针对读取数据时可能产生的不一致现象，在SQL92标准中定义了4个事务的隔离级别：

隔离级别	脏读	不可重复读	幻读
Read uncommitted(读未提交)	是	是	是
Read committed（读已提交）	否	是	是
Repeatable read（可重复读）	否	否	是
Serializable（串行读）	否	否	否

Oracle默认的隔离级别是read committed。

Oracle支持上述四种隔离级别中的两种:read committed 和serializable。除此之外，Oralce中还定义Read only和Read write隔离级别。

Read only：事务中不能有任何修改数据库中数据的操作语句，是Serializable的一个子集。

Read write：它是默认设置，该选项表示在事务中可以有访问语句、修改语句，但不经常使用。

设置隔离级别

设置一个事务的隔离级别：

• SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
• SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
• SET TRANSACTION READ ONLY;
• SET TRANSACTION READ WRITE;

注意：这些语句是互斥的，不能同时设置两个或两个以上的选项。

设置单个会话的隔离级别：

• ALTER SESSION SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
• ALTER SESSION SET TRANSACTION ISOLATION SERIALIZABLE;

5. 事务控制

事务组成：

一条或者多条DML，[一条DDL]和一条DCL。

事务的分类：

1、显式事务：

（1）要去显式的调用DCL。

（2）只有用到COMMIT以后才会真正写入数据库，也持久化了。

2、隐式事务：

（1）如果人工要使用隐式事务，SET AUTOCOMMIT ON (只针对一个连接)

（2）以下情况为自动提交：

1）正常执行完成的DDL语句：create、alter、drop

2）正常执行完场的DCL语句GRANT、REVOKE

3）正常退出的SQLPlus或者SQL Developer等客户端

回滚：

RollBack只能对未提交的数据撤销，已经Commit的数据是无法撤销的，因为commit之后已经持久化到数据库中。

（1） 提交事务

在执行使用COMMIT语句可以提交事务，当执行了COMMIT语句后，会确认事务的变化，结束事务，删除保存点，释放锁。当使用COMMIT语句结束事务之后，其他会话将可以查看到事务变化后的新数据。

（2） 回滚事务

保存点（savepoint）：是事务中的一点，用于取消部分事务，当结束事务时，会自动的删除该事务所定义的所有保存点。当执行ROLLBACK时，通过指定保存点可以回退到指定的点。

设置保存点：

sql> Savepoint a;

删除保存点：

sql> Release Savepoint a;

回滚部分事务：

sql> Rollback To a;

回滚全部事务：

sql> Rollback;

6.数据库锁：

锁的级别（粒度）： 表级锁，行级锁

排它锁：（X锁，eXclusive LockS）

当有DML语句执行的时候，设计的行都会加上排它锁，其他事物不能进行读取修改。

共享锁：（S锁，Shared Locks）

加了共享锁的数据，可以被其他事务读取，但不能修改。如select语句。

为了保证性能：乐观锁，悲观锁

悲观锁：每次都是假设数据肯定会冲突，数据开始读取时就把数据给锁住。

乐观锁：每次都是假设一般情况下不会发生数据冲突，只有数据更新提交的时候，才会对数据的冲突与否进行检测，如果发生冲突，返回错误信息让用户处理。

​ CAS compare and swap

7.JDBC的事务

三、oracle常用基本数据类型

1.字符类型

字符串数据类型还可以依据存储空间分为固定长度类型（CHAR/NCHAR) 和可变长度类型（VARCHAR2/NVARCHAR2)两种.

固定长度：是指虽然输入的字段值小于该字段的限制长度，但是实际存储数据时，会先自动向右补足空格后，才将字段值的内容存储到数据块中。

​ info char(4) ‘ab’ —>'ab ’ String trim() name="john "; name.trim();//去掉空格

可变长度：是指当输入的字段值小于该字段的限制长度时，直接将字段值的内容存储到数据块中，而不会补上空白，这样可以节省数据块空间。

char(size [BYTE | CHAR]):默认1字节，n值最大为2000.定长字符串，会用空格填充来达到其最大长度。如非null的char(n)总是包含n个字节信息。可以指定它存储字节或字符，例如 CHAR(12 BYTYE) CHAR(12 CHAR)

nchar(n):默认1字符，最大存储内容2000字节.这是一个包含Unicode格式数据的定长字符串。它的最大长度取决于国家字符集。

varchar2(n BYTE/CHAR):最大长度必须指定，至少为1字节或者1字符，n值最大为4000.长度为n个字节的可变长度且非Unicode的字符数据，oracle自定义的非标准，用null代表varchar的空字符串。区分中英文。 varchar2(255) 最大可以放255个字符，‘ab中国’ 英文一个字符，中文是2个字符

varchar(n):VARCHAR数据类型目前是VARCHAR2的同义词 空字符串""

nvarchar(n)：最大长度必须指定，最大存储内容4000字节.包含n个字符的可变长度Unicode字符数据.不区分中英文，n表示字符数，不是字节数 nvarchar(10) 存10个字符（可以中文也可英文），英文是1个字节，中文是2个字节

nvarchar2(n)：最大长度必须指定，最大存储内容4000字节.包含n个字符的可变长度Unicode字符数据.不区分中英文 ，中英文都是2个字节

例：我和coffee

char字段占n个字节的存储空间

varchar字段占2*2+6=10个字节的存储空间

nvarchar字段占8*2=16个字节的存储空间

字段值只是英文可选择varchar2，而字段值存在较多的双字节（中文等）字符时使用nvarchar2

2.数字类型

NUMBER(p[,s]):1-22字节。P(Precison)取值范围1到38,S(Scale)取值范围-84到127.可以存放数据范围为10^130~10126（不包含此值).正值s为小数位数，负值s表示四舍五入到小数点左部多少位。

例：123.89

NUMBER(6,1) 123.9

NUMBER(6,-1) 120

INTEGER类型：INTEGER是NUMBER的子类型，它等同于NUMBER（38,0），用来存储整数。若插入、更新的数值有小数，则会被四舍五入。

3.日期类型

DATE：其中总包含7个属性，包括：世纪、年、月、日期、小时、分钟和秒。一般占用7个字节的存储空间

TIMESTAMP [(fractional_seconds_precision)]（时间戳）：一个7字节或12字节的定宽日期/时间数据类型。fractional_seconds_precision为Oracle存储秒值小数部分位数，默认为6，可选值为0到9。没有时间区

4.LOB大对象类型

CLOB：最大为(4GB-1)*数据库块大小，存储单字节或者多字节字符数据，支持事务处理。主要用于存储大型英文字符

NCLOB：最大为(4GB-1)*数据库块大小，存储Unicode数据。支持事务处理。主要用于存储大型非英文字符

BLOB：最大为(4GB-1)*数据库块大小，存储非结构化二进制数据，支持事务处理。可以被认为是没有字符集语义的比特流，主要存储图像、声音、视频等文件。

BFILE：LOB地址指向文件系统上的一个二进制文件，维护目录和文件名。不参与事务处理。只支持只读操作。

注意：

​ 遇到大对象列时，插入空白构造函数。 字符型：empty_clob()，empty_nclob() 二进制型：empty_blob() 二进制文件类型：BFileName函数指向外部文件。 BFileName函数： BFileName(‘逻辑目录名’，‘文件名’); 逻辑目录名只能大写，因为数据词典是以大写方式存储。Oracle是区分大小写的。 在创建时，无需将BFileName函数逻辑目录指向物理路径，使用时才做检查二者是否关联。 例子： Insert Into tLob Values(1,‘Gene’,empty_clob(),empty_blob(),bfilename(‘MYDIR’,‘IMG_0210.JPG’));

​ 可以在INSERT语句中使用这些初始化函数，初始化完成后，可以使用UPDATE语句

​ 向大对象列中加入数据。

例：

1. Create table poem
2. (
3. ​ poem_writer varchar2(30),
4. ​ poem_title varchar2(30),
5. ​ poem_content nclob
6. );
7. Insert into poem(poem_writer,poem_title,poem_content) values(‘李白’,‘静夜思’,EMPTY_CLOB());
8. Update poem set poem_content=’
9. ​ 床前明月光，疑是地上霜。
10. ​ 举头望明月，低头思故乡。
11. ​ ’
12. where poem_title=‘静夜思’;

5.其他类型

LONG：最大为2GB，变长类型，存储字符串。与VARCHAR2 或CHAR 类型一样，存储在LONG 类型中的文本要进行字符集转换.ORACLE建议开发中使用CLOB替代LONG类型。支持LONG 列只是为了保证向后兼容性。CLOB类型比LONG类型的限制要少得多。

ROWID:10字节.代表记录的地址。显示为18位的字符串。用于定位数据库中一条记录的一个相对唯一地址值。通常情况下，该值在该行数据插入到数据库表时即被确定且唯一。ROWID它是一个伪列，它并不实际存在于表中

四、表的创建和管理

•表是数据库中最重要的对象，是数据库的基础，是基本存储单元，用来存储数据，由行（记录record）和列（字段field）组成

•在被授权的情况下，数据库用户可以创建表，并且创建表的用户对该表具有增删改查的权限。一个数据库表，可以有多达1000个字段，表名和字段名必须符合命名规则。表在创建时没有数据，只是一个表结构，之后可以通过INSERT语句单行或多行添加数据

•如同Java语言变量有命名规则一样，Oracle数据库的表和字段的命名有如下规则：

–必须以字母开始

–必须是1到30 个字符长度

–只能包含字母、数字、下划线“_”、美元符“$”和井号“#”

–不能使用Oracle的关键字

–同一个用户所拥有的对象之间不能重名

1.plsql添加表

（1）第一步、新建

（2）第二步，填写表信息

（3）第三步、创建表字段

2.ddl添加表（sql）

（1）创建完整表

create table 表名（字段名1 字段1类型 约束1，字段名2 字段2类型 约束2，，，，，，，）

创建一个课程表，有课程编号，课程名称、课程简介3个字段，要求课程简介默认为‘无’，编号和名称不能为空

create table course(

​ cno number not null,

​ cname varchar2(20) not null,

​ cintro varchar2(500) default ‘无’);

​

（2）使用子查询创建表（不光可以复制表的结构，还可以复制表的数据）

不指定列名

create table course1 as select * from course;

指定列名

create table course2(cno,cname) as select cno,cname from course;

create table course3(no,name) as select cno,cname from course;

3.修改表结构

•用ALTER TABLE语句添加字段、修改字段和删除字段的语法形式如下，其中，ADD、MODIFY和DROP是修改的类型

–ALTER TABLE tablename

​ ADD(col1 type1 [DEFAULT1], col2 type2 [DEFAULT2]…)

–ALTER TABLE tablename

​ MODIFY(col1 type1 [DEFAULT1], col2 type2 [DEFAULT2]…)

–ALTER TABLE tablename

​ DROP(col)

4.删除表

drop table course

本章任务：

​

十二、约束和视图

一、约束的概念

•Oracle数据库使用约束（constraints）来防止无效的数据进入到表中，保护数据的实体完整性

•通过约束，强制用户在插入、更新或删除数据时必须遵循一定的规则

•约束规则定义在表级，如果表和表之间有从属关系，约束也可以防止表的删除

•约束可以在创建表的时候定义，也可以在表创建之后定义

•给约束起一个有意义的名字（例如DEPT_DNAME_NN代表部门名不能为空的约束），易于约束的引用

•不显式命名约束，Oracle数据库将用格式SYS_Cn产生一个名字，这里n 是一个唯一的整数，所以约束名是唯一的

二、约束的使用

1.定义格式

–CREATE TABLE [schema.]tablename（

​ col1 type1 [DEFAULT1] [col1_constraint],

​ col2 type1 [DEFAULT2] [col2_constraint],

…

[table_constraint])

列级约束：col1_constraint

表级约束：table_constraint

2.NOT NULL非空约束

CREATE TABLE depts_temp3(
       dept_id      NUMBER(4)     NOT NULL,  //隐式声明约束，会自动生成一个约束的名字
       dept_name     VARCHAR2(20)  CONSTRAINT   DEPT3_NAME_NN NOT NULL,//显式声明，给约束起了自定义名字
       manager_fname VARCHAR2(14),
       dept_loc    VARCHAR2(50)
       CONSTRAINT  dept3_id_pk  PRIMARY KEY(dept_name,manager_fname)
 );

​

3.unique唯一约束

–要求字段或者字段的组合（键）的每个值是唯一的，定义UNIQUE约束的字段（或字段组合） 被称为唯一键（unique key）

（a）表创建后进行修改

ALTER TABLE depts_temp3
        ADD(dept_desc VARCHAR(100) CONSTRAINT   dept3_desc_uk UNIQUE)

​

（b）表定义的创建约束

            ```sql
            CREATE TABLE depts_temp3(
                   dept_id      NUMBER(4)     NOT NULL,  //隐式声明约束，会自动生成一个约束的名字
                   dept_name     VARCHAR2(20)  CONSTRAINT   DEPT3_NAME_NN NOT NULL,//显式声                      明，给约束起了自定义名字
                   manager_fname VARCHAR2(14),
                   dept_loc    VARCHAR2(50)，
                   dept_desc      varchar2(100) unique,
                CONSTRAINT  dept3_id_pk  PRIMARY KEY(dept_id)
             );
            ```

​

©唯一是个组合

​ 成绩表

​ sno cno score

            ```sql
               create table student_score(
                      sno  number,
                      cno  number,
                      score   number,
                      constraint student_score_uq unique(sno,cno)//sno和cno组合起来唯一
                 )
            ```

​

4.主外键约束

主键 primary key 主键等于唯一约束和非空约束

外键的值一定是主键值的子集

​ 成绩表

​ sno cno score

  create table student_score(
          sno  number,
          cno  number,
          score   number,
          constraint student_score_uq primary key(sno,cno)//sno和cno组合起来唯一
     )

​

外键 foreign key

CREATE TABLE emps(
       emp_id NUMBER(6) NOT NULL,
       last_name VARCHAR2(25) NOT NULL,
       salary NUMBER(8,2),
       dept_id NUMBER(4),
       CONSTRAINT emps_dept_fk FOREIGN KEY(dept_id) REFERENCES depts_temp3(dept_id),
       CONSTRAINT emps_id_pk PRIMARY KEY(emp_id)
       );

​

（1）外键必须是主键的子集

（2）删除先删外键再删主键

（3）插入先插入主键再插入外键

create table score(
          sno  number,
          cno  number,
          score   number,
          constraint student_score_uq primary key(sno,cno)//sno和cno组合起来唯一
          foreign key(sno) references student(sno);
          foreign key(cno) references course(cno);
     )

​

5.check约束

create table student（
     sno number primary key，
     sname   varchar2（20） not null，
     sage    number  check（sage>19 and sage<25）
     ssex    varchar2(2) check(ssex='1' or ssex='0')
）

​

练习：

三、视图view

1.视图作用

view和table类似，一般view是建立在table或者其他view基础上的

•表现数据的逻辑子集或数据组合

•视图是基于表或另一个视图的逻辑表，一个视图并不包含真实的数据，它提供了另一个视角查看或改 变表中的数据

•视图本质上就是一个SELECT语句

视图的作用：

–视图可以起到隐藏数据的功能。因为视图可以选择性的显示表中的字段（或行）。

–视图可以将复杂的多表连接查询的结果构成一个简单的表结构提供给用户。

2.视图分类

3.视图创建

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW viewName[(aliaslist)]

AS subquery

[WITH CHECK OPTION [CONSTRAINT cname]

[WITH READ ONLY]

•OR REPLACE表示如果视图已经存在则重新创建

•FORCE表示创建视图而不管基表是否存在，NOFORCE表示只有基表存在的情况下才创建视图（默认情况）

•view表示视图名

•aliaslist表示别名列表（别名之间用逗号隔开，别名的个数需要和子查询中选择的字段或表达式的个数一致）

•subquery表示子查询

•WITH CHECK OPTION表示只有可访问的行在视图中才能被插入或更新

•cname表示为约束指定的名称

•WITH READ ONLY表示在该视图中不可执行DML操作

注：视图的核心是子查询，在视图中，子查询可以包含复杂的SELECT语句，但不可以包含ORDER BY子句（如想排序，可以从视图中取数据时进行排序）

–创建员工的视图

create view emp_view
as
  select empno,ename,job from emp
with read only;

create or replace view group_view(gvid,gvname)
as
  select gid,gname from sgroup where gid<5
  with check option
--with read only;

–查询视图

select * from group_view;

–向基表sgroup插入一条数据

insert into sgroup values(7,'七组','');

–向简单视图插入一条数据，会影响基表数据

insert into group_view values(8,'八组');

t student_score_uq primary key(sno,cno)//sno和cno组合起来唯一
foreign key(sno) references student(sno);
foreign key(cno) references course(cno);
)

​          

#### **5.check约束**

```sql
create table student（
     sno number primary key，
     sname   varchar2（20） not null，
     sage    number  check（sage>19 and sage<25）
     ssex    varchar2(2) check(ssex='1' or ssex='0')
）

​

练习：

三、视图view

1.视图作用

view和table类似，一般view是建立在table或者其他view基础上的

•表现数据的逻辑子集或数据组合

•视图是基于表或另一个视图的逻辑表，一个视图并不包含真实的数据，它提供了另一个视角查看或改 变表中的数据

•视图本质上就是一个SELECT语句

视图的作用：

–视图可以起到隐藏数据的功能。因为视图可以选择性的显示表中的字段（或行）。

–视图可以将复杂的多表连接查询的结果构成一个简单的表结构提供给用户。

2.视图分类

3.视图创建

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW viewName[(aliaslist)]

AS subquery

[WITH CHECK OPTION [CONSTRAINT cname]

[WITH READ ONLY]

•OR REPLACE表示如果视图已经存在则重新创建

•FORCE表示创建视图而不管基表是否存在，NOFORCE表示只有基表存在的情况下才创建视图（默认情况）

•view表示视图名

•aliaslist表示别名列表（别名之间用逗号隔开，别名的个数需要和子查询中选择的字段或表达式的个数一致）

•subquery表示子查询

•WITH CHECK OPTION表示只有可访问的行在视图中才能被插入或更新

•cname表示为约束指定的名称

•WITH READ ONLY表示在该视图中不可执行DML操作

注：视图的核心是子查询，在视图中，子查询可以包含复杂的SELECT语句，但不可以包含ORDER BY子句（如想排序，可以从视图中取数据时进行排序）

–创建员工的视图

create view emp_view
as
  select empno,ename,job from emp
with read only;

create or replace view group_view(gvid,gvname)
as
  select gid,gname from sgroup where gid<5
  with check option
--with read only;

–查询视图

select * from group_view;

–向基表sgroup插入一条数据

insert into sgroup values(7,'七组','');

–向简单视图插入一条数据，会影响基表数据

insert into group_view values(8,'八组');