MySql和Oracle

2019-07-18 本文已影响0人北街九条狗

安装区别

win7下安装程序需要调兼容性

右键管理员身份运行安装程序

注意：

MySQL是直接提供用户，然后用户可以执行建库，建表等操作

Oracle需要先行建库，库提供用户

Oracle提供了2个重要用户

管理员： sys 密码 : 初始密码 as sysdba

普通用户： scott（锁定状态）密码： tiger

在Oracle中，数据库对象是属于用户的

切换用户conn scott/tiger;

提示the password has expired密码过期

采用IDE去访问数据

plSqldeveloper ：解压路径中不能存在括号
navicat for oracle

mysql中autocommit默认是true,oralce中autocommit默认是false

DQL,DML,DCL,DDL,TPL

DQL数据查询语言 : SELECT

DML数据操作语言： insert ,update, delete

DCL数据控制语言 : grant ，revoke

DDL数据定义语言 : create ,alter ,drop

TPL 事务控制语言: commit,rollback

建表差异

-- MySQL 5.6以上版本
-- 主键自增长
create table userinfo(
    `id` bigint auto_increment comment '主键',
    `username` varchar(50) not null default '' comment '用户名',
    `password` varchar(20) not null default '' comment '密码',
    `birthday` date not null default '' comment '生日',
    `regtime` datetime not null default now() comment '注册时间',
    `height` decimal(3,2) not null default 0 comment '身高',
    `sign` longtext,
    primary key(`id`)
)
-- 插入记录，不需要管主键，因为自增长
insert into userinfo(username,password,birthday,regtime,height,sign)
values (...)

-- oracle
-- 数据类型
-- 数值型 NUMBER 类似 mysql 的 int
-- 字符型 varchar2 类似 mysql 的 varchar
-- 日期型 date 类似 mysql 的 datetime
-- 浮点型 NUMBER(p,s)
-- clob 类似 longtext
-- sysdate 相当于 now()
create table userinfo(
    id NUMBER,
    username varchar2(50) default '' not null ,
    password varchar2(20) default '' not null ,
    birthday date default '' not null ,
    regtime date default sysdate not null,
    height NUMBER(3,2) default 0 not null ,
    sign clob,
    primary key(id)
)
-- 没有主键自增长，主键的自增靠的是“序列”
-- 先创建序列，序列就是oracle中生成主键的对象
-- 这个序列默认从1开始,start with可以指定初始值
create sequence seq_userinfo
[start with 10]
-- 插入数据时，需要指定使用的序列
insert into student(id,stuname,age,birthday,height)
values (seq_student.nextval,'关羽',61,to_date('2012-11-7','YYYY-MM-dd'),1.98);

-- 在oracle中多一种约束类型，检查性约束
create table userinfo(
    id NUMBER,
    username varchar2(50) default '' not null ,
    password varchar2(20) default '' not null ,
    birthday date default '' not null ,
    regtime date default sysdate not null,
    height NUMBER(3,2) default 0 not null ,
    gender varchar2(2) default '男' not null,
    sign clob,
    primary key(id),
    check (gender in ('男','女')) -- 在mysql中不生效，mysql中采取枚举类型即可
)

create table userinfo1(
    `id` bigint auto_increment comment '主键',
    `username` varchar(50) not null default '' comment '用户名',
    `password` varchar(20) not null default '' comment '密码',
    `birthday` date comment '生日',
    `height` decimal(3,2) not null default 0 comment '身高',
    `gender` enum('男','女') not null default '男' comment '性别', -- 枚举
    `sign` longtext,
    primary key(`id`)
)

日期格式化

-- mysql
select DATE_FORMAT(releasetime,'%Y-%m月-%d %H:%i:%s')
from article

-- oracle
select to_char(sysdate,'yyyy-mm"月"-dd HH24:mi:ss')
from userinfo

分页

-- Mysql中分页 limit
select *
from student
limit 0,5

-- Oracle 没有limit需要查询嵌套才能完成
-- 查询student中前5条数据
-- rownum是一个伪列
select *
from student
where rownum <= 5

-- 生日降序排序，查询student中前5条数据
select ROWNUM,a.*
from (select ROWNUM rn1,s.* 
     from student s
     order by BIRTHDAY desc) a
where ROWNUM <= 5

--  生日降序排序,查询student中2到7条数据
select b.*
from (select rownum rn,a.*
     from (select *
          from student
          order by name) a
     where rownum <= 7) b
where rn > 2

select b.*
from (select rownum rn,a.*
     from (select *
          from student
          order by birthday desc) a) b
where rn > 2 and rn <= 7

外部连接和自连接

左[外]连接 left [outer] join

select name,cname
from student a
left join ban b
on a.cno = b.cid

-- oracle 还可以用+去指定出空行的表
select name,cname
from student a
join ban b
on a.cno = b.cid(+)

右[外]连接 right [outer] join

全[外]连接 full [outer] join

自连接 inner join 自己与自己连接

视图View

命名的查询，并不保存任何数据

oracle中需要对scott用户授权才能创建视图

grant create view to scott

索引Index

加快查询速度，但会降低增删改速度

-- mysql创建索引
create table student1(
`id` BIGINT auto_increment, 
`stuname` varchar(20) default '' not null,
`age` int(2) default 0 not null,
`birthday` date,
`height` decimal(3,2) default 0 not null,
PRIMARY KEY(`id`),
index(`age`)
)

-- oracle创建索引
CREATE INDEX index_student_age
ON student(age)

数据库三范式

什么是三大范式：

第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。

第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。

第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.

理解三大范式

第一范式

1、每一列属性都是不可再分的属性值，确保每一列的原子性

2、两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。

如果需求知道那个省那个市并按其分类，那么显然第一个表格是不容易满足需求的，也不符合第一范式。

显然第一个表结构不但不能满足足够多物品的要求，还会在物品少时产生冗余。也是不符合第一范式的。

第二范式

每一行的数据只能与其中一列相关，即一行数据只做一件事。只要数据列中出现数据重复，就要把表拆分开来。

一个人同时订几个房间，就会出来一个订单号多条数据，这样子联系人都是重复的，就会造成数据冗余。我们应该把他拆开来。

这样便实现啦一条数据做一件事，不掺杂复杂的关系逻辑。同时对表数据的更新维护也更易操作。

第三范式

数据不能存在传递关系，即没个属性都跟主键有直接关系而不是间接关系。像：a-->b-->c 属性之间含有这样的关系，是不符合第三范式的。

比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）

这样一个表结构，就存在上述关系。学号--> 所在院校 --> (院校地址，院校电话)

这样的表结构，我们应该拆开来，如下。

（学号，姓名，年龄，性别，所在院校）--（所在院校，院校地址，院校电话）