经典案例oracle和mysql分别比较

oracle和mysql对比（案例）

oracle：

# 建表

CREATE TABLE match (
  rq date DEFAULT NULL,
  sh varchar(255) DEFAULT NULL
)


# 插入数据
INSERT INTO match VALUES (to_date('2005-05-09','yyyy-mm-dd'), '胜');
INSERT INTO match VALUES (to_date('2005-05-09','yyyy-mm-dd'), '负');
INSERT INTO match VALUES (to_date('2005-05-09','yyyy-mm-dd'), '胜');
INSERT INTO match VALUES (to_date('2005-05-09','yyyy-mm-dd'), '负');
INSERT INTO match VALUES (to_date('2005-05-10','yyyy-mm-dd'), '负');
INSERT INTO match VALUES (to_date('2005-05-10','yyyy-mm-dd'), '胜');
INSERT INTO match VALUES (to_date('2005-05-10','yyyy-mm-dd'), '负');

# 第一种查法
select rq,
sum(case when SH='胜' then 1 else 0 end) "胜",
sum(case when SH='负' then 1 else 0 end) "负" 
from match group by rq;

# 第二种查法
select rq,
sum(decode(sh,'胜',1,0)) "胜",
sum(decode(sh,'负',1,0)) "负" 
from match group by rq;

mysql：

# 建表
CREATE TABLE `match` (
  `rq` date DEFAULT NULL,
  `sh` varchar(255) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

# 插入数据
INSERT INTO `match` VALUES ('2005-05-09', '胜');
INSERT INTO `match` VALUES ('2005-05-09', '负');
INSERT INTO `match` VALUES ('2005-05-09', '胜');
INSERT INTO `match` VALUES ('2005-05-09', '负');
INSERT INTO `match` VALUES ('2005-05-10', '负');
INSERT INTO `match` VALUES ('2005-05-10', '胜');
INSERT INTO `match` VALUES ('2005-05-10', '负');

# 第一种查法
select rq,
sum(case when sh="胜" then 1 else 0 end) "胜",
sum(case when sh="负" then 1 else 0 end) "负"  
from match group by rq;

# 第二种查法
select rq,
sum(if(sh="胜",1,0)) "胜",
sum(if(sh="负",1,0)) "负" 
from match group by rq;

按照每个人的工资划分为低中高三个层次：

oracle：

# 建表
CREATE TABLE emp (
  id number(11) NOT NULL,
  name varchar(50) DEFAULT NULL,
  job varchar(50) DEFAULT NULL,
  topid number(11) DEFAULT NULL,
  hdate date DEFAULT NULL,
  sal number(11) DEFAULT NULL,
  bonus number(11) DEFAULT NULL,
  dept_id number(11) DEFAULT NULL
)


# 插入数据
INSERT INTO emp VALUES ('1001', '王克晶', '办事员', '1007', to_date('1980-12-17','yyyy-mm-dd'), '800', '500', '20');
INSERT INTO emp VALUES ('1003', '齐雷', '分析员', '1011', to_date('1981-02-20','yyyy-mm-dd'), '1900', '300', '10');
INSERT INTO emp VALUES ('1005', '王海涛', '推销员', '1011', to_date('1981-02-22','yyyy-mm-dd'), '2450', '600', '10');
INSERT INTO emp VALUES ('1007', '刘苍松', '经理', '1017', to_date('1981-04-02','yyyy-mm-dd'), '3675', '700', '20');
INSERT INTO emp VALUES ('1009', '张慎政', '推销员', '1011', to_date('1981-09-28','yyyy-mm-dd'), '1250', '1400', '10');
INSERT INTO emp VALUES ('1011', '陈子枢', '经理', '1017', to_date('1981-05-01','yyyy-mm-dd'), '3450', '400', '10');
INSERT INTO emp VALUES ('1013', '张久军', '办事员', '1011', to_date('1981-06-09','yyyy-mm-dd'), '1250', '800', '10');
INSERT INTO emp VALUES ('1015', '程祖红', '分析员', '1007', to_date('1987-04-19','yyyy-mm-dd'), '4000', '1000', '20');
INSERT INTO emp VALUES ('1017', '韩少云', '董事长', null, to_date('1981-11-17','yyyy-mm-dd'), '5000', null, null);
INSERT INTO emp VALUES ('1019', '刘沛霞', '推销员', '1011', to_date('1981-09-08','yyyy-mm-dd'), '1500', '500', '10');
INSERT INTO emp VALUES ('1021', '范传奇', '办事员', '1007', to_date('1987-05-23','yyyy-mm-dd'), '1100', '1000', '20');
INSERT INTO emp VALUES ('1023', '赵栋', '经理', '1017', to_date('1981-12-03','yyyy-mm-dd'), '950', null, '30');
INSERT INTO emp VALUES ('1025', '朴乾', '分析员', '1023', to_date('1981-12-03','yyyy-mm-dd'), '2000', '600', '30');
INSERT INTO emp VALUES ('1027', '叶尚青', '办事员', '1023', to_date('1982-01-23','yyyy-mm-dd'), '1300', '400', '30');

select * from emp

select name,sal,
(case when sal>=3000 then '高工资' when sal>1000 then '工资一般' else '低工资' end) "判断工资" 
from emp order by sal;

mysql：

# 建表
CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `job` varchar(50) DEFAULT NULL,
  `topid` int(11) DEFAULT NULL,
  `hdate` date DEFAULT NULL,
  `sal` int(11) DEFAULT NULL,
  `bonus` int(11) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `dept_id` (`dept_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


# 插入数据
INSERT INTO `emp` VALUES ('1001', '王克晶', '办事员', '1007', '1980-12-17', '800', '500', '20');
INSERT INTO `emp` VALUES ('1003', '齐雷', '分析员', '1011', '1981-02-20', '1900', '300', '10');
INSERT INTO `emp` VALUES ('1005', '王海涛', '推销员', '1011', '1981-02-22', '2450', '600', '10');
INSERT INTO `emp` VALUES ('1007', '刘苍松', '经理', '1017', '1981-04-02', '3675', '700', '20');
INSERT INTO `emp` VALUES ('1009', '张慎政', '推销员', '1011', '1981-09-28', '1250', '1400', '10');
INSERT INTO `emp` VALUES ('1011', '陈子枢', '经理', '1017', '1981-05-01', '3450', '400', '10');
INSERT INTO `emp` VALUES ('1013', '张久军', '办事员', '1011', '1981-06-09', '1250', '800', '10');
INSERT INTO `emp` VALUES ('1015', '程祖红', '分析员', '1007', '1987-04-19', '4000', '1000', '20');
INSERT INTO `emp` VALUES ('1017', '韩少云', '董事长', null, '1981-11-17', '5000', null, null);
INSERT INTO `emp` VALUES ('1019', '刘沛霞', '推销员', '1011', '1981-09-08', '1500', '500', '10');
INSERT INTO `emp` VALUES ('1021', '范传奇', '办事员', '1007', '1987-05-23', '1100', '1000', '20');
INSERT INTO `emp` VALUES ('1023', '赵栋', '经理', '1017', '1981-12-03', '950', null, '30');
INSERT INTO `emp` VALUES ('1025', '朴乾', '分析员', '1023', '1981-12-03', '2000', '600', '30');
INSERT INTO `emp` VALUES ('1027', '叶尚青', '办事员', '1023', '1982-01-23', '1300', '400', '30');

# 第一种查法
select name,sal,if(sal>=3000,"高工资",if(sal>=1000,"工资一般","低工资")) as '工资判断' 
from emp;

# 第二种查法
select name,sal,
(case when sal>=3000 then '高工资' when sal>1000 then '工资一般' else '低工资' end) '判断工资' 
from emp;