MySql && Oracle

了解MySql & Oracle

---

mysql

• 研发公司 ： MySql --- 开源
• 现公司 ： Oracle --- 收费(部分功能)

oracle

• 研发公司 ： oracle

mysql --- oracle

• 基础SQL一样 ， 具体一些功能有所不同

MySql 普通操作

---

命名规则

• 关键词大小写不敏感

注释

单行注释

--

-- 单行注释

多行注释

/**/

/*
多
行
注
释
*/

算术运算符

• 加、减、乘、除、取余

-- 加
Select 2.5+8;

-- 减
Select 8-2.5;

-- 乘
Select 2.5*8;

-- 除
Select 8/2.5;  -- 保留小数位
Select 8 div 2.5;    -- 取整

-- 取余
Select 8%2.5;
Select 8 mod 2.5;

比较运算符

• 大于、小于、等于、不等、多对判断

-- 大于
Select 8>1; -- 符合返回1

-- 小于
Select 8<1; -- 不符合返回0

-- 等于
Select 8=1;
Select 'a'='a';
Select null = null; -- 结果为空 ，判断空不能用等号
Select null is null; -- 判断结果是否为空
Select null <=> null; -- 安全等于，与普通等于区别于 判断null返回1或0

-- 不等
Select 8 != 1;
Select 2 <> 6;

-- 与 null 判断时 无论是否相同都返回 null
Select 8 != null; -- 结果为 null
Select 2 <> null; -- 结果为 null

Select 2 is not null; -- 判断结果是否不为空

-- 多对判断
Select 2 in (1,7,32,8,23,2); -- 一对多判断
Select 10 between 0 and 50;  -- 判断区间
Select 10 not between 0 and 50; -- 判断区间（否定）

逻辑运算符

• and (与)
• or (或)
• not (非)

MySql 数据库操作

---

定义语言 （DDL）

• create
• drop
• alter

创建表（Create）

数据类型

• int
• double
• char（必须给长度） --- 按照定义长度存储
• varchar（必须给长度）--- 按照实际长度存储
• text（文本）
• datetime（时间）
• timestamp（时间戳类型）--- 默认值 CURRENT_TIMESTAMP

create table xxx -- (表名)
(
  -- 字段名称  类型（字符类型需要给初始长度）  约束条件  （int类型可以制定自增，一张表只有一个自增）,
  -- 字段名 ... ,
  -- ...
);

主键

• primary key
• 唯一并且不为 null，一般情况 一表只有一主键

外键

• foreign key
• 连接其他表的主键

唯一

• unique
• 不能为 null

约束

• CONSTRAINT

创建表格

• 当存在外键时，删除表时需要先删除外键表，再删除该表
• 创建时，需要县创建主表，再创建外键表
• 外键名称为自定义名称，为了方便阅读信息 可写为 fk_xx_YY_yy fk为foreign key xx为本表中的需设为外键的字段 YY另一表格，yy连接外键的字段

Create table stu -- 创建 名为 stu 的表格
(
  -- 字段名称 id 数据类型 int 设置主键 自增
  id int primary key auto_increment,
  
  -- 字段名称 name 数据类型 varchar 数据长度 10 不为空
  name varchar(10) not null,
  
  -- 字段名称 sex 数据类型 char 数据长度 2 默认值为 男
  sex char(2) default '男',
  
  -- 字段名称 addtime 数据类型 datetime 默认值为 当前时间
  addtime datetime default NOW()
);

Create table child
(
  -- 字段名称 id 数据类型 int 设置主键 自增
  id int primary key auto_increment,
   
  -- 字段名称 cid 数据类型 int
  cid int,
  
  -- 字段名称 cname 数据类型 varchar 数据长度 10
  cname varchar(10),
  
  -- 约束条件 设置外键 本表中 cid 字段与外表 stu 中 id 字段 外键名称 child_cid_stu_id
  CONSTRAINT child_cid_stu_id foreign KEY(cid) REFERENCES stu(id) 
);

create table 表名
(select ... from 已有表) -- 根据结果集创建表

• 根据结果集创建表

删除表（Drop）

DROP table child; -- 先删除外键表
DROP table stu; -- 再删除主表

区分 delete drop truncate

删除数据

• delete truncate
• delete 删除可以回滚（取消删除），处理速度慢
• truncate 删除是没有回滚，处理速度快

删除表结构

• drop

修改表（Alter）

• modify --- 修改字段的 数据类型
• change --- 修改字段的 名称 和 数据类型
• rename --- 修改表的名字
• add column --- 添加字段
• add CONSTRAINT --- 添加约束

alter table stu
modify name int; -- 将字段 name 的类型修改为 int

alter table stu
change name sname varchar(5); -- 将字段 name 名称变成 sname 并将类型修改为 varchar

alter table stu
rename student;  -- 将表 stu 名称更改为 student

alter table student
add column score double(5,2); -- 添加字段 score 字段，长度为 5位 其中小数占 2位 整数占 3位

alter table student
add CONSTRAINT fk_id_child_cid foreign key(id) REFERENCES child(cid); -- 添加约束 外键

alter table student
drop score; -- 删除字段

alter table student
drop foreign key fk_id_child_cid; -- 删除外键

查询语言 （DQL）

• Select

单表查询（Select）

• ' * ' --- 通配符，表示所有
• where --- 过滤 --- 其身后不能接聚合函数
• like --- 模糊查询 , % --- 占位符 , _ --- 占位符
• group by --- 分组 --- 其身后不能接聚合函数
• max、min、avg、count --- 最大、最小、平均、求个数 （聚合函数） --- 聚合函数不能互相嵌套
• having --- 与where作用相似 --- 身后可跟聚合函数
• order by --- 排序（默认从小到大）倒序 desc
• limite --- 截取数目 --- limit a,b 从 a开始 去不包括a , 取b条
• current --- 当前页数
• page --- 每页显示数据 --- page page*(current - 1),page
• year(字段名) --- 截取到年
• month(字段名) --- 截取到月
• day(字段名) --- 截取到日
• between a and b --- a-b之间
• distinct --- 去重
• concat(aaa,bbb) --- 字符串拼接
• as --- 使用别名
• union --- 去重（主键）
• union all --- 不去重连接，显示所有内容
• 执行顺序 from --- where --- group by --- having --- order by --- limit

-- 查询整表
Select * from job;
Select id,recDate,source,position,name,age,tel,sex from job;

-- 查询年龄大于18的女生
Select * from job where age > 18 and sex = '女';

-- 模糊查询
Select * from job where name like '张%';

-- 以 8 结尾的电话号
Select * from job where tel like '%8';

-- 包含 8 的电话号
Select * from job where tel like '%8%';

-- 查询两个字名字
Select * from job where name like '张_';

-- 查询三个字，中间为 海
Select * from job where name like '_海_';

-- 查询男女各多少人
Select count(sex) from job where sex = '男';
Select count(sex) from job where sex = '女';
-- 分组函数 
Select count(*),sex from job group by sex;

-- 查询最大、小、平均年龄
Select MAX(age) from job;
Select MIN(age) from job;
Select AVG(age) from job;

-- 查询岗位申请人数，排序
Select count(*),position from job group by position order by count(*) desc;

-- 最受欢迎岗位
Select count(*),position from job group by position order by count(*) desc limit 1;

-- 查询出都有哪些岗位提供给求职者
select position from job group by position;

-- 去重
select distinct position from job;

-- 每一年的求职者人数有多少，现实要求 xxxx 年， n 人
select concat(year(recDate),'年') as '年份', concat(count(*), '人')
from job group by year(recDate);

-- 字段别名
select year(recDate) as '年份' , count(*) as '人数' 
from job group by year(recDate);

select year(recDate) '年份' , count(*) '人数' 
from job group by year(recDate);

多表查询

自连接 自查询

• 嵌套

-- 本表中年龄最大的求职者
select name,age,from job where age =
select max(age),name from job;

外连接

左外连接

• left join
• 显示左表格全部信息

select d.com_id,d.com_name,e.com_id from dept d left join emp e on d.com_id = e.comid

右外连接

• right join
• 显示右表格全部信息

select d.com_id,d.com_name,e.com_id from dept d right join emp e on d.com_id = e.comid

左 --- 右 外连接

• 左连接是以左表格为基础向右表进行查询，显示左表全部信息
• 右连接是以右表格为基础向左表进行查询，显示右表全部信息
• 左外连接 与 右外连接可以穿插使用

全外连接

内连接

• inner join
• 显示两张表公共信息

select d.com_id,d.com_name,e.com_id from dept d inner join emp e on d.com_id = e.com_id;

环绕连接

• cross join
• 两张没有关联的表的信息，信息条数是两张表数量的乘积 --- 笛卡尔积
• 用于求有多少条数据

select d.com_id,d.com_name,e.com_id, from dept d cross join emp e;

不使用关键词

• 内连接

select * from emp e,dept d where e.com_id = d.com_id

操作语言 （DML）

---

• insert
• update
• delete
• merge

添加操作（Insert）

用法

• 字段赋值必须和数据中类型、数量、位置一一对应

insert into 表名 values();

根据查询结果添加数据

• 结果集字段类型、数量、位置需要保证一致

insert into 表1
(select ... from 表2 where 条件); -- 使用结果集添加

修改更新（Update）

• 注意 ：修改数据不给条件会将所有信息进行修改

用法

update 表名 set 修改内容; -- 全部修改

update 表名 set 修改内容 where 修改条件; -- 精确修改

删除操作（Delete）

• 注意 ：删除数据不给条件会将所有信息进行修改

用法

delete from 表名; -- 全部删除

delete from 表名 where 删除条件; -- 精确删除

视图 （view）

• 不直接存入内存（不占内存）
• 面向用户，将表以视图形式展示给用户，避免用户看到表结构

创建视图

create view 视图名 as
(select * from 表名);

事务（autocommit）

• 保存点 （savepoint）
• 提交（commit）
• 回滚（rollback）

用法

set autocommit 0; -- 非自动提交（可回滚）
delete from stu where name = '李四';
rollback; -- true

set autocommit 1; -- 自动提交（回滚无效）
delete from stu where name = '李四';
rollback; -- false

-- -----------------------------------------------

set autocommit 0; -- 非自动提交（可回滚）
delete from stu where name = '李四';
commit; -- 先保存后回滚
rollback; -- false

set autocommit 1; -- 自动提交（回滚无效）
delete from stu where name = '李四';
rollback; -- true
commit; -- 先回滚后保存（无提交内容）

-- -----------------------------------------------

set autocommit 0; -- 非自动提交（可回滚）
delete from stu where name = '李四';
savepoint a; -- 保存点
delete from stu where name = '张三';
rollback to a;
commit -- 张三未删除，李四删除

只适用于有回滚的删除（delete）

Orical 操作

创建表

• mysql 为 char，varchar
• Oracle为 varchar ，varchar2
• 添加当前时间方式与 mysql 不同
• 无自动递增

create table username
(
    id int primary key,
    name varchar2(10) default'',  -- varchar varchar2 相当于Mysql char varchar
    sex varchar2(5),
    tel varchar2(11) unique,
    addtime date default sysdate  --  sysdate 默认系统时间，年月日，时分秒
);

添加数据

• 每次只能添加一组数据
• 年月日插入需要通过 to_date 方法
• 24H / 12H 方法也不是用大小写区别
• 分钟也变成了 mi
• 表后接括号，就可以只添加需要输入的字段（其他字段有默认值情况下），Mysql 和Oracle 都适用
• 多了个根据变量添加数据

-- 添加数据 一次只能插入一个数据
insert into username values
(1,'张三','男','123456',to_date('1997-10-1','yyyy-mm-dd'));  --  年月日的插入方法

insert into username values
(2,'李四','男','654321',to_date('1997-10-1 14:12:56','yyyy-mm-dd,hh24:mi:ss'));  --  24H时间插入方法

insert into username values
(3,'王五','男','142536',to_date('1997-10-1 3:12:56','yyyy-mm-dd,hh:mi:ss')); -- 12H时间插入方法

insert into username(id,tel) values  -- 括号内添加需要输入的字段(两个数据库都适用)
(4,'635241');

-- 根据变量添加数据
insert into username(id,tel) values
(&id,&tel);

insert into username(id,tel,addtime) values
(&id,&tel,to_date('&addtime','yyyy-mm-dd'));

insert into username values
(&id,'&name','&sex',&tel,to_date('&addtime','yyyy-mm-dd'));

• 其余操作和Mysql没有太大区别

检查（check）

• 限制写入信息

create table username1
(
    id int primary key,
    name varchar2(10) default'',  -- varchar varchar2 相当于Mysql char varchar
    sex varchar2(5) check (sex in ('男','女')), -- 限制写入男女
    tel varchar2(11) unique,
    addtime date default sysdate  --  sysdate 默认系统时间，年月日，时分秒
    constraint check_sex check (sex in ('男','女'))
);

create table username2
(
    id int primary key,
    name varchar2(10) default'',  -- varchar varchar2 相当于Mysql char varchar
    sex varchar2(5),
    tel varchar2(11) unique,
    addtime date default sysdate,  --  sysdate 默认系统时间，年月日，时分秒
    constraint check_sex check (sex in ('男','女'))  -- 通过约束限制写入男女
);

伪列（rownum）

• 相当于 mysql 的 Limite
• 查询非第一条数据的范围、精确条数数据需要嵌套查询

select * from username where rownum = 1; -- 取第一条

select * from username where rownum >= 1; -- 取所有

select * from username where rownum <= 4; -- 取前五条

select * from username where rownum >= 4; -- false

-- 查询非第一条数据需要嵌套查询 不能直接查*，否则报 表达式缺省 错
select * from (select rownum r ,id,name from username) where r = 3; -- 可以读取每一条信息
select * from (select rownum r ,id,name from username where rownum <= 5) where r >=2; -- 第二条到第四条

序列（sequence）

• 用于sql 优化： 给表添加序列，检索速度更快

-- 序列
create sequence seq_a
increment by 1 -- 每次递增数量
start with 1   -- 开始数
maxvalue 999   -- 当前序列最大值 nomaxvalue 没有最大值
minvalue 1     -- 当前序列最小值 nominvalue 没有最小值
cycle -- 是否循环 是 cycle 否 nocycle
cache 10-- 是否有缓存 是 cache + 缓存数 否 nocache

drop sequence seq_a; -- 删除序列

alter system flush shared_pool-- 清除缓存

insert into T_USER values(seq_a.nextval,'张三','男','123312',sysdate);