当前位置：移动技术网 > IT编程>数据库>Mysql > mysql与oracle的一些区别

mysql与oracle的一些区别

2020年07月14日 | 移动技术网IT编程 | 我要评论

数据库题

user

user_id, user_name

order

user_id ,price, create_time

SQL：查询姓名为xxx在2020年4月份一共消费的总金额

user_id, user_name, amount

oracle

create table sys_user_info
(
user_id varchar2(64) not null,
user_name varchar2(100),
password varchar2(100),
belong_org_id varchar2(10),
valid_status varchar2(2)
);

--创建主键
alter table sys_user_info add primary key (user_id) using index;

create table shopping_order
(
user_id varchar2(64) not null,
price number (12,2),
create_time DATE
);

insert into sys_user_info (USER_ID, USER_NAME, PASSWORD, BELONG_ORG_ID, VALID_STATUS)
values ('WANGJUAN001', '王娟', '123456', '1', 'A');

insert into sys_user_info (USER_ID, USER_NAME, PASSWORD, BELONG_ORG_ID, VALID_STATUS)
values ('WANGJUAN002', '王娟', '123456', '1', 'A');

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)
values ('WANGJUAN001', 10.00, to_date('01-04-2020', 'dd-mm-yyyy'));

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)
values ('WANGJUAN001', 15.00, to_date('08-04-2020', 'dd-mm-yyyy'));

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)
values ('WANGJUAN001', 25.00, to_date('30-04-2020 23:59:59', 'dd-mm-yyyy hh24:mi:ss'));

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)
values ('WANGJUAN001', 40.00, to_date('01-05-2020', 'dd-mm-yyyy'));

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)
values ('WANGJUAN002', 10.00, to_date('01-04-2020', 'dd-mm-yyyy'));

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)
values ('WANGJUAN002', 15.00, to_date('08-04-2020', 'dd-mm-yyyy'));

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)
values ('WANGJUAN002', 25.00, to_date('30-04-2020 23:59:59', 'dd-mm-yyyy hh24:mi:ss'));

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)
values ('WANGJUAN002', 40.00, to_date('01-05-2020', 'dd-mm-yyyy'));

oracle查询语句

select u.user_id,u.user_name,sum(price) as amount

from shopping_order o

left join sys_user_info u

on o.user_id = u.user_id

where u.user_name='王娟'

and create_time<to_date('2020/05/01','YYYY/MM/DD')--特别注意<2020/05/01

and create_time >= to_date('2020/04/01','YYYY/MM/DD')

group by u.user_id,u.user_name;

mysql

mysql创建索引

https://www.cnblogs.com/kenwong/p/4645337.html

mysql日期格式化

https://www.cnblogs.com/diandianquanquan/p/10852620.html

mysql datetime类型的数据表现形式为2020-06-01 00:00:00

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)

values ('WANGJUAN001', 10.00, str_to_date('2020/06/01', '%Y/%m/%d %H:%i:%s'));

str_to_date('2020/06/01', '%Y/%m/%d %H:%i:%s')存储在数据库的形式也为2020-06-01 00:00:00

date_format(date,'%Y-%m-%d') -------------->oracle中的to_char();

str_to_date(str,'%Y-%m-%d') -------------->oracle中的to_date();

create table sys_user_info

(

user_id varchar(64) not null,

user_name varchar(100),

password varchar(100),

belong_org_id varchar(10),

valid_status varchar(2)

);

#创建主键索引

alter table sys_user_info add primary key (user_id);

insert into sys_user_info (USER_ID, USER_NAME, PASSWORD, BELONG_ORG_ID, VALID_STATUS)

values (WANGJUAN001, '王娟', '123456', '1', 'A');

insert into sys_user_info (USER_ID, USER_NAME, PASSWORD, BELONG_ORG_ID, VALID_STATUS)

values (WANGJUAN002, '王娟', '123456', '1', 'A');

create table shopping_order

(

user_id varchar(64) not null,

price decimal,

create_time datetime

);

第一种

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)

values ('WANGJUAN001', 10.00, date_format(20200401, '%Y-%m-%d %H:%i:%s'));

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)

values ('WANGJUAN001', 15.00, date_format(20200408, '%Y-%m-%d %H:%i:%s'));

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)

values ('WANGJUAN001', 25.00, date_format(20200430235959, '%Y-%m-%d %H:%i:%s'));

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)

values ('WANGJUAN001', 40.00, date_format(20200501, '%Y-%m-%d %H:%i:%s'));

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)

values ('WANGJUAN002', 10.00, date_format(20200401, '%Y-%m-%d %H:%i:%s'));

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)

values ('WANGJUAN002', 15.00, date_format(20200408, '%Y-%m-%d %H:%i:%s'));

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)

values ('WANGJUAN002', 25.00, date_format(20200430235959, '%Y-%m-%d %H:%i:%s'));

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)

values ('WANGJUAN002', 40.00, date_format(20200501, '%Y-%m-%d %H:%i:%s'));

第二种

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)

values ('WANGJUAN001', 10.00, str_to_date('2020-04-01', '%Y-%m-%d %H:%i:%s'));

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)

values ('WANGJUAN001', 15.00, str_to_date('2020-04-08', '%Y-%m-%d %H:%i:%s'));

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)

values ('WANGJUAN001', 25.00, str_to_date('2020-04-30 23:59:59', '%Y-%m-%d %H:%i:%s'));

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)

values ('WANGJUAN001', 40.00, str_to_date('2020-05-01', '%Y-%m-%d %H:%i:%s'));

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)

values ('WANGJUAN002', 10.00, str_to_date('2020-04-01', '%Y-%m-%d %H:%i:%s'));

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)

values ('WANGJUAN002', 15.00, str_to_date('2020-04-08', '%Y-%m-%d %H:%i:%s'));

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)

values ('WANGJUAN002', 25.00, str_to_date('2020-04-30 23:59:59', '%Y-%m-%d %H:%i:%s'));

insert into shopping_order (USER_ID, PRICE, CREATE_TIME)

values ('WANGJUAN002', 40.00, str_to_date('2020-05-01', '%Y-%m-%d %H:%i:%s'));

mysql查询语句

select u.user_id,u.user_name,sum(price) as amount

from shopping_order o

left join sys_user_info u

on o.user_id = u.user_id

where u.user_name='王娟'

and create_time<str_to_date('2020/05/01','%Y/%m/%d') #特别注意<2020/05/01

and create_time >= str_to_date('2020/04/01','%Y/%m/%d')

group by u.user_id,u.user_name;

mysql having

having字句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录

select u.user_id,u.user_name,sum(price) as amount

from shopping_order o

left join sys_user_info u

on o.user_id = u.user_id

where u.user_name='王娟'

and create_time<str_to_date('2020/07/01','%Y/%m/%d') #特别注意<2020/05/01

and create_time >= str_to_date('2020/04/01','%Y/%m/%d')

group by u.user_id,u.user_name

having amount>90;

distinct 在mysql与oracle之区别

distinct在mysql中与sum函数一起使用，不会像group by一样进行分组，而是把所有amount加起来。

select DISTINCT u.user_id,u.user_name,sum(price) as amount

from shopping_order o

left join sys_user_info u

on o.user_id = u.user_id

where u.user_name='王娟'

and create_time<str_to_date('2020/05/01','%Y/%m/%d') #特别注意<2020/05/01

and create_time >= str_to_date('2020/04/01','%Y/%m/%d')

distinct在oracle中与sum函数一起使用会报错，提示不是单组分组函数。

select DISTINCT u.user_id,sum(price) as amount

from shopping_order o

left join sys_user_info u

on o.user_id = u.user_id

where u.user_name='王娟'

and create_time<to_date('2020/07/01','YYYY/MM/DD')

and create_time >= to_date('2020/04/01','YYYY/MM/DD');

MyBatis处理MySQL字段类型date与datetime

https://blog.csdn.net/ywb201314/article/details/83795265

本文地址：https://blog.csdn.net/bladeandmaster88/article/details/107327807

您可能感兴趣的文章:

如对本文有疑问，点击进行留言回复！！

linux 安装mysql8.0

1.在 /use/local下创建mysql文件夹 mkdir mysql2.切换到mysql文件夹下 cd ... [阅读全文]
Centos8 环境下安装mysql-5.7

安装Mysql1、在执行wget命令的目录下或你的上传目录下找到Mysql安装包：mysql-5.7.24-li... [阅读全文]
分布式事务

分布式事务文章目录分布式事务一、分布式场景下的数据一致性问题形成原因剖析1. 分布式事务背景背景知识什么情况下会... [阅读全文]
MySQL主从架构部署及同步原理

一般MySQL的主从架构的目的都是用来做读写分离，分担主库的负载压力，主库用来写，从库用来读。MySQL主从架构... [阅读全文]
Mysql_InnoDB_文件

Mysql_InnoDB_文件Mysql数据库和InnoDB存储引擎存储的文件如下：参数文件日志文件Socket... [阅读全文]
鲲鹏解决方案 1.0vs1.5

鲲鹏解决方案在1.0版本中是将鲲鹏解放方案分为了通用解决方案以及行业解决方案两种。基于华为鲲鹏计算平台（云平台或... [阅读全文]
MySQL技术内幕：InnoDB存储引擎读书笔记

引言本书介绍InnoDB的体系结构和工作原理，并结合InnoDB的源代码讲解了它的内部实现机制。why比what... [阅读全文]
普通Hash与一致性Hash

Hash算法应⽤场景Hash算法在分布式集群架构中的应⽤场景Hash算法在很多分布式集群产品中都有应⽤，⽐如分布... [阅读全文]
一步步教你用Prometheus搭建实时监控系统系列(一)——上帝之火，普罗米修斯的崛起

上帝之火本系列讲述的是开源实时监控告警解决方案Prometheus，这个单词很牛逼。每次我都能联想到带来上帝之火... [阅读全文]
行级锁与表级锁

一、简介数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种... [阅读全文]