mysql与oracle的一些区别

程序员文章站 2023-03-08 08:17:47

数据库题useruser_id, user_nameorderuser_id ,price, create_timeSQL：查询姓名为xxx在2020年4月份一共消费的总金额user_id, user_name, amountoraclecreate table sys_user_info(user_id varchar2(64) not null,user_name varchar2(100),password varchar2(100),belong_org_id va...

数据库题

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'));

mysql与oracle的一些区别

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')

mysql与oracle的一些区别

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

mysql与oracle的一些区别

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

上一篇： vlookup函数简介

下一篇：保护乳房女人乳头痒当心两种病

mysql与oracle的一些区别

数据库题

oracle

oracle查询语句

mysql

第一种

mysql查询语句

mysql having

distinct 在mysql与oracle之区别

MyBatis处理MySQL字段类型date与datetime

红豆与赤小豆的区别有哪些，功效有什么不一样！

i3和i5性能差多少？Intel i3与i5处理器的区别对比介绍

MySQL数据库存储过程和事务的区别讲解

Linux中进程和线程的对比与区别

重新学习MySQL数据库6：浅谈MySQL的中事务与锁

Mysql5.7.18的安装与主从复制图文详解

MySQL中Decimal类型和Float Double的区别(详解)

MySQL 5.7中的关键字与保留字详解

PHP抽象类与接口的区别实例详解

Jquery中children与find之间的区别详细解析