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;