欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  IT编程

mysql与oracle的一些区别

程序员文章站 2022-05-07 19:00:02
数据库题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的一些区别

 

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

上一篇： 与你共同见证，2019 MOZI教育智能机器人正式全球发布

下一篇： 数据库实习详解：基于数据库的C/S程序开发

推荐阅读