数据库-Oracle复习

一、数据库表结构的操作：

1.创建表Scores

 

1 create table Scores --表名

2 (Id int identity(1,1) primary key,--设置主键,并且行号自增，

identity(1,1)表示Id列从1开始自增，每次增加1

3 Date datetime not null, --设置类型为datetime，不能为空

4 Name nvarchar(50) not null,

5 Score nvarchar(2) --默认状态下，类型为空

6 )

 

2.删除表Scores

 1 drop table Scores --删除表（表的结构、属性以及索引也会被删除） 

3.清空表数据

 1 truncate table Scores --仅仅删除表格中的数据 

5.修改列Score为 not null

1 alter table Scores

2 alter column Score nvarchar(2) not null

6.添加列

1 alter table Scores

2 add new nvarchar(20) not null

7.修改列名

1 exec sp_rename 'Scores.Name','NewName','column'

8.删除列

1 alter table Scores

2 drop column new

9.修改identity列

 如果说创建表时没有设置自增列。因为自增列不能直接修改，必须降原有Id列删除，然后重新添加一列具有identity属性的Id字段。

1 exec sp_pkeys @table_name='Scores' --查询主键名

2 alter table Scores drop constraint PK__Scores__3214EC074E3D66D2 --将主键约束先删去

3 alter table Scores drop column Id --将Id列删去

4 alter table Scores add Id int identity(1,1) --添加自增列(此时Id列不是主键)

5 alter table Scores add Id int identity(1,1) constraint pk primary key --添加自增列,设置Id为主键名字为pk

 10.修改列类型

 

alter table TableName alter column ColumnName varchar(50) --将一个列的类型改成varchar（50）

alter table student change name name varchar(20) --另一种修改方法

 

 

数据的基本操作

DML与DQL查看图

 

 

 

 

 

 

三大范式

概念：

第一范式：数据库表中的字段都是单一属性的，不可再分的，

第二范式：在满足第一范式的前提下，数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖。单一主键即可满足第二范式

第三范式：在满足第二范式的前提下要求表中不存在非关键字段对任一候选关键字段的传递函数依赖

 

例题：

 

序列

序列

序列是一种用来生成唯一数字值的数据库对象，是一种高效率获得唯一键值的途径。

序列的值由Oracle程序按递增或递减顺序自动生成，通常用来自动生成表的主键值。

序列是独立的数据库对象，并不依附于表，一个序列可以为一个表作主键值，也可以为多个表提供主键值。

创建序列

CREATE  SEQUENCE  sequence_name

[START  WITH  i]      

----从i开始生成序列，不写默认为从1开始

[INCREMENT  BY  j]   

----步进值为j，不写默认为1，j可为正数也可为负数

[MAXVALUE  m |  NOMAXVALUE] 

---设置序列最大值为m或没有最大值，默认无上限

[MINVALUE  n |  NOMINVALUE]  

---设置序列最小值为n或没有最小值，默认无下限

[CYCLE  |  NOCYCLE]

---设置递增到最大值或递减到最小值时，重头循环生成序列，或不循环，默认为不循环

[CACHE  p |  NOCACHE]

---预先取出p个数据到缓存中，以提高生成效率，p默认为20

序列的使用：

序列中有两个伪劣，可通过下面两个方法获取：

NEXTVAL：获取序列的下一个值，初始位置在第一行数据之上。每执行一次浪费一个序列值，可能不连续。

CURRVAL：获取当前序列的值，不会导致序列值递增。至少要执行一次NEXTVAL才能使用CURRVAL。

查询序列的第一个值：

SELECT  sequence_name.NEXTVAL  FROM  DUAL

查询序列的当前值：

SELECT sequence_name.CURRVAL  FROM  DUAL

向表中插入序列值：

INSERT  INTO  table_name(column)

VALUES (sequence_name.NEXTVAL)

删除序列：

DROP  SEQUENCE  sequence_name

索引

索引是一种允许直接访问数据表中某一数据行的树形结构。是独立于表的数据库对象。可以存放在与表不同的表空间中，是为了提高查询效率而引入的。

索引记录中存有索引关键字和指向表中数据的指针（地址）。

索引一旦建立就被Oracle系统自动维护，查询语句中不用指定使用哪个索引，其多IO的操作比表进行IO操作要少很多。

索引包含：Index entry header、Key column length 、Key column value 、ROWID

ROWID：伪列，唯一标识一条数据记录，可理解为行地址

创建索引：

CREATE  [UNIQUE]  INDEX  index_name

ON  table_name(column,column,...)

其中UNIQUE表示唯一索引

重建索引：

ALTER  INDEX  index_name  REBUILD

删除索引：

DROP  INDEX  index_name

注意：

（1）不要在经常做DML操作的表上建立索引（2）不要在小表上建立索引（3）删除很少被使用的，不合理的索引（4）限制表上索引数目，索引并不是越多越好。

 

分页

分页 (rowid,rownum)伪列

一般查不到，需要指定查询才能查询到伪列：

Select rownum from student

 rownum常用于分页

教案上的例子：

<!---分页-->

select rownum as rn,a.*  from a_user a;

select  * from (select rownum as rn,a.*  from a_user a) b where rn<3

select * from (select  * from (select rownum as rn,a.*  from a_user a) b where rn<3) c where rn>0

Notes:
1.因为rownum是动态的所以使>=需要注意一下

1. 分页的时候需要其别名，不要再rownum上加基表的前缀，因为再内外查询的时候，外查询需要用到内查询的rownum而不是重新分配的rownum
2. Oracle语法规范：再其中输出其他列时，使用*是不能通过必须指定表名称
3. 通过子查询查询在某区间的数据

 

 

用户、权限：

https://www.cnblogs.com/zlbx/p/4817910.html

 

函数

常用：

<!--  字符串上面的去空格操作-->

select trim('     zzzzz') from dual;

 

<!-- 数字上面的操作 四舍五入-->

select  round(20.1)  from dual;

 

<!--判断2个时间上面的日期差值 -->

select  months_between(birthday,tday) from  a_user;

<!--构建seq -->

create sequence  userseq;

 

<!--使用seq进行主键上面的操作-->

insert  into a_user(aid,aname) values(userseq.nextval,'xxx');

 

<!--使用seq进行值的操作 -->

insert into a_user(aid,aname) values (userseq.nextval,'xxx');

 

日期函数：

1.sysdate

用途：获取当前系统时间。

2.to_date('字符类型','日期类型') 

 　　用途：将字符类型转化成日期类型的函数，主要用于插入、查询和修改。

3.to_char('日期类型','字符类型')

　　　　用途：将日期类型转换为字符类型的函数，主要用于查询，也可以进行删除使用。

1.sysdate

没啥好说的，使用非常简单。

2.to_date('字符类型','日期类型') 

我们使用scott用户自带的表给大家介绍该函数的使用。先给大家介绍第一种使用方法。to_date用于插入数据：

--插入数据

insert into emp(empno,hiredate) values(8888,'1991-09-22');

commit; --错误写法

这是一个错误写法，因为插入的数据类型是字符类型，不是我们要的日期类型，需要将字符类型转换为日期类型。看下面的改进姿势：

insert into emp(empno,hiredate) values(8888,to_date('1991-09-22','yyyy-mm-dd'));

commit; --正确写法

查看运行结果：

 

如果，觉得插入的日期不够爽，可以增加时分秒比如：好好hh24:mi:ss

insert into emp(empno,hiredate) values(8889,to_date('1991-09-22 22:18:23','yyyy-mm-dd hh24:mi:ss'));

commit; --正确写法

插入成功后，我们看下插入后的结果：

 

第二种，使用to_date进行查询数据

--查找数据

select * from emp where hiredate=to_date('1981-2-22','yyyy-mm-dd')

查询结果如下：

 

第三种，使用to_date进行修改数据

--修改数据

update emp set hiredate=to_date('1981-12-20','yyyy-mm-dd') where empno=7499;

查看修改后的结果：

 

3.to_char('日期类型','字符类型')

第一种：使用to_char进行查询，hiredate的数据被修改。

--查询数据

select * from emp where to_char(hiredate,'yyyy')=1991;

查看结果：

 

第二种：使用to_char进行删除操作，删除dd=3的日期，

--删除数据

select * from emp where to_char(hiredate,'dd')=3;

温馨提示：to_char不可以用来做修改。用的比较多的就是查询和删除数据。

 

好吧，关于to_date  to_char日期函数使用，我们就先介绍到这里了。

 

ER图绘制

某医院病房的计算机管理系统中需要下述信息

科室：科室名，科室地址，科室电话

   病房：病房号，床位号，科室名

   医生：工作证号，姓名，职称，科室名，年龄

  病人：病例号，姓名，性别，诊治，主治医生，病房号

  其中，一个科室有多个病房、多名医生，一个病房只属于一个科室，一个医生只属于一个科室，但是可以负责多名病人的诊治，一命病人的主治医生只能有一名。

   完成以下设计：

1） 设计该计算机管理系统的E-R图；

2） 将该E-R图转换为关系模型结构，指出转换结果中每个关系模式的候选码。

E-R图如下所示。

 

2）对应的关系模型结构如下：

科室（科室名，科室地址，科室电话）

   病房（病房号，床位号，科室名）

   医生（工作证号，姓名，职称，科室名，年龄）

   病人（病例号，姓名，性别，诊治，主治医生，病房号）

 

科室的候选码是科室名；

   病房的候选码是（病房号，床位号）；

医生的候选码是工作证号；

病人的候选码是病历号。

 

 

 

 

 

数据库基本知识；

种类

关系型数据库（关系模型指的就是二维表格模型）和非关系型数据库（键值对存储）

 

历史以及结构模型

参考数据库概念设计与分析，复习一下数据库管理历史、数据库的模型（层次结构模型、网状结构模型，关系结构模型）

 

三级模式以及两级映射

外模式(用户级)-》模式（概念级）-》内模式（物理级）

外模式/模式 映像使数据具有较高的逻辑独立性

模型/内模式 映像使数据具有较高的物理独立性

五个约束三个完整性

三大范式：

第二范式：在数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（一般单一主键就可以解决这个问题）

第三范式：在第二范式的基础上要求数据表中不存在非关键对任一候选关键字段的传递函数依赖（传递函数依赖问题）

 

 

认识sql

结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；

 

Sql语言按照功能划分为四大类：

DDL  数据定义语言，创建，修改，删除数据库对象操作(DROP、create、alter、grant)

DML  数据操纵语言，增删改数据操纵（CRUD）

DQL  数据查询语言，查询数据的操作

DCL  数据控制语言，用户权限管理操作以及事务操作

 

 

1. varchar2把所有字符都占两字节处理(一般情况下)，varchar只对汉字和全角等字符占两字节，数字，英文字符等都是一个字节；
   2.VARCHAR2把空串等同于null处理，而varchar仍按照空串处理；
   3.VARCHAR2字符要用几个字节存储，要看数据库使用的字符集，
   大部分情况下建议使用varchar2类型，可以保证更好的兼容性。

 

事务的四大特征：ACID

原子性：事务操作时一个整体，要么都执行，要么都不执行

一致性：

隔离性：

持久性：