本文共 5305 字，大约阅读时间需要 17 分钟。

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;