hc学习平台

 找回密码
 立即注册
搜索
热搜: 活动 交友 javakc
 › 学习交流 › MySql › 关于日期的sql
查看: 787|回复: 1
打印 上一主题 下一主题

关于日期的sql

[复制链接]

2249

主题

0

帖子

0

博客

admin

Rank: 9 Rank: 9 Rank: 9

积分

IP 编辑 禁止 帖子 清理

跳转到指定楼层
楼主
发表于2014-12-13 10:17:13 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式

有以下表结构Grade:

Name

inTime

Val

Tom

03-4月-08

A

Tom

02-4月-08

A

Tom

01-4月-08

B

Tom

31-3月-08

C

Jone

31-3月-08

A

Jone

01-4月-08

B

Jone

02-4月-08

B

Jone

03-4月-08

C


  1. 将intime这一列显示为“2008-04-03 16:44:11”

  2. 得到下列表数据:

Name A B C

Tom 2 1 1

Jone 1 2 1

  1. 08年4月份得A的次数超过1次的人员姓名

  2. 在grade上建立两个外键

外键名称

指向的表

指向表的列

On delete cascade

Stu_id_fk

Stu_id

Student

Id

yes

Cour_id_fk

Cour_id

Course

Id

yes


  1. 在表Grade的Stu_id和Cour_id上建立一个索引,如果现有数据量不多的情况下,如果使用以下sql查询,请问oracle的优化器会使用索引吗?

select * from grade where stu_id=1 and cour_id=1;


0

主题

1104

帖子

2

博客

桃子++

Rank: 9 Rank: 9 Rank: 9

积分

IP 编辑 禁止 帖子 清理

沙发
发表于2016-08-27 17:33:14 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式

1、将intime这一列显示为“2008-04-03 16:44:11”

    select to_char(intime,'yyyy-mm-dd hh24:mi:ss') from Grade;

2、得到下列表数据:

    Name A B C

    Tom 2 1 1

    Jone 1 2 1

    select Name,Val,count(1) from Grade group by Val;

3. 08年4月份得A的次数超过1次的人员姓名

    select Name from Grade where Val='A' and group by Val having count(Val)>1;

4.

create table Grade(

    Name varchar(10) not null primary key,

    inTime date,

    Val varchar(10),

    Stu_id number(10),

    Cour_id number(10)

);

create table Student(

    Id number(10) not null primary key,

    Stu_id_fk number(10),

    foreign key(Stu_id_fk)  references Grade(Stu_id)

);

create table Course(

    Id number(10) not null primary key,

    Cour_id_fk number(10),

    foreign key(Cour_id_fk) references Grade (Cour_id)

);

5. 会使用索引


沙发
发表于2016-09-08 14:42:37 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
1,将intime这一列显示为“2008-04-03 16:44:11”
select name,to_char(intime,'yyyy-mm-dd hh24:mi:ss') 录入时间,val from grade1

2,得到下列表数据:
Name A B C
Tom 2 1 1
Jone 1 2 1

 select name,
        count(val) A,
        (select count(val)
           from grade1
          where val = 'B'
            and name = 'tom') B,
        (select count(val)
           from grade1
          where val = 'C'
            and name = 'tom') C
   from grade1
  where val = 'A'
    and name = 'tom'
  group by name
 union
 select name,
        count(val) A,
        (select count(val)
           from grade1
          where val = 'B'
            and name = 'jone') B,
        (select count(val)
           from grade1
          where val = 'C'
            and name = 'jone') C
   from grade1
  where val = 'A'
    and name = 'jone'
  group by name
 

3,08年4月份得A的次数超过1次的人员姓名
select name
  from grade,
       (select val
          from grade1
         where intime like '2008/4/%'
           and val = 'A'
         group by val
        having count(1) > 1) t
 where grade.val = t.val
4,在grade上建立两个外键
create table Grade(
    Name varchar(10),
    inTime date,
    Val varchar(10),
    foreign key(sid)  references Student(sid),
    foreign key(cid) references Course(cid)
);
create table Student(
    sid number(10) not null primary key,
    name varchar2(10)
);
create table Course(
    cid number(10) not null primary key,
    name varchar2(10)
);

5,在表Grade的Stu_id和Cour_id上建立一个索引,如果现有数据量不多的情况下,如果使用以下sql查询,请问oracle的优化器会使用索引吗?
select * from grade where stu_id=1 and cour_id=1;
oracle的优化器不会使用索引

0

主题

1700

帖子

0

博客

song

Rank: 9 Rank: 9 Rank: 9

积分

IP 编辑 禁止 帖子 清理

沙发
发表于2016-09-08 10:51:00 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式

//创建表

create table grade(

 name varchar2(5),
 intime date,
 val varchar2(4)

)


// 插入数据

insert into grade values('tom','03-4月-08','a');
insert into grade values('tom','02-4月-08','a');
insert into grade values('tom','01-4月-08','b');
insert into grade values('tom','31-3月-08','c');
insert into grade values('jone','31-3月-08','a');
insert into grade values('jone','01-4月-08','b');

insert into grade values('jone','02-4月-08','b');

insert into grade values('jone','03-4月-08','c');

1.

  select to_char(intime,'yyyy-mm-dd hh24:mi:ss') from Grade;


2.得到下列表数据:
    Name A B C
    Tom 2 1 1
    Jone 1 2 1

    
    select name ,count(val) A ,(select count(val) from grade  where val='b' and name='tom') B ,(select count(val) from grade  where val='c' and name='tom') C
    from grade
    where val='a' and  name='tom'
    group by name
   union
   select name ,count(val) A ,(select count(val) from grade  where val='b' and name='jone') B ,(select count(val) from grade  where val='c' and name='jone') C
    from grade
    where val='a' and  name='jone'
    group by name

3. 08年4月份得A的次数超过1次的人员姓名


    select name
    from grade
    where intime like '2008/4/%' and val='a'
    group by name
    having count(val)>1

4.

create table Grade(

    Name varchar(10) not null primary key,

    inTime date,

    Val varchar(10),

    Stu_id number(10),

    Cour_id number(10)

);

create table Student(

    Id number(10) not null primary key,

    Stu_id_fk number(10),

    foreign key(Stu_id_fk)  references Grade(Stu_id)

);

create table Course(

    Id number(10) not null primary key,

    Cour_id_fk number(10),

    foreign key(Cour_id_fk) references Grade (Cour_id)

);

5. 会使用索引

0

主题

1518

帖子

0

博客

Cracia

Rank: 9 Rank: 9 Rank: 9

积分

IP 编辑 禁止 帖子 清理

沙发
发表于2016-09-07 12:12:37 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式

1.将intime这一列显示为“2008-04-03 16:44:11”

select name intime('2008-04-03 16:44:11','yyyy-mm-dd hh24:mi:ss') val  from grade

2.得到下列表数据:

select count(1),count(2),count(3) from grade group by name

3.08年4月份得A的次数超过1次的人员姓名

select name from grade where intime in('01-4月-08','02-4月-08','03-4月-08') gruop by name having count(1)>1

4.在grade上建立两个外键:

alter table student

    add constraint Stu_id_fk foreign key(id)

    reference grade Stu_id

alter table course

    add constraint cour_id_fk foreign key(id)

    reference grade cour_id

5.不会

0

主题

1721

帖子

0

博客

XIAOC

Rank: 9 Rank: 9 Rank: 9

积分

IP 编辑 禁止 帖子 清理

沙发
发表于2016-09-09 17:39:53 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式

update Grade set "inTime"=to_char('2008-04-03 16:44:11','yyyy-mm-dd hh24:mi');

select name from s where val='A' and substr(ind,4)='4月 -08' group by name having count(1)>1;

select g.name,(select count(1) from grade gra where gra.name=g.name and gra.va='A') A,(select count(1) from grade gra where gra.name=g.name and gra.va='B') B,(select count(1) from grade gra where gra.name=g.name and gra.va='C') C from (select name from grade group by name) g

constraint Stu_id_fk foreign key(Stu_id) references  student(id)  On delete cascade

constraint  Cour_id_fk foreign key(cour_id) references  course(id)  On delete cascade

会使用




0

主题

1510

帖子

0

博客

zhangjialong

Rank: 9 Rank: 9 Rank: 9

积分

IP 编辑 禁止 帖子 清理

沙发
发表于2016-09-09 14:54:52 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
--创建grade表
create table grade(
    name varchar2(5),
    intime date,
    val varchar2(5)
)
--录入数据
insert into grade(name,intime,val)
values ('Tom','03-4月-08','A')
insert into grade(name,intime,val)
values ('Tom','02-4月-08','A')
insert into grade(name,intime,val)
values ('Tom','01-4月-08','B')
insert into grade(name,intime,val)
values ('Tom','31-3月-08','C')
insert into grade(name,intime,val)
values ('Jone','31-3月-08','A')
insert into grade(name,intime,val)
values ('Jone','01-4月-08','B')
insert into grade(name,intime,val)
values ('Jone','02-4月-08','B')
insert into grade(name,intime,val)
values ('Jone','03-4月-08','C')

--1.将intime这一列显示为“2008-04-03 16:44:11”
update grade
set intime=to_date('2008-04-03 16:44:11','yyyy-mm-dd hh24:mi:ss')

select to_char(intime,'yyyy-mm-dd hh24:mi:ss')
from grade

--2. 得到下列表数据:
--Name A B C
--Tom 2 1 1
--Jone 1 2 1
select name ,count(val) A ,(select count(val) from grade  where val='B' and name='Tom') B ,(select count(val) from grade  where val='C' and name='Tom') C
    from grade
    where val='A' and  name='Tom'
    group by name
   union
   select name ,count(val) A ,(select count(val) from grade  where val='B' and name='Jone') B ,(select count(val) from grade  where val='C' and name='Jone') C
    from grade
    where val='A' and  name='Jone'
    group by name

--3.08年4月份得A的次数超过1次的人员姓名
select name
  from grade
 where intime between to_date('2008-04-01', 'yyyy-mm-dd') and
       to_date('2008-04-30', 'yyyy-mm-dd')
   and val = 'A'
 group by name
having count(val) > 1

--4.在grade上建立两个外键
create table Grade(
    Name varchar(10),
    inTime date,
    Val varchar(10),
    foreign key(sid)  references Student(sid),
    foreign key(cid) references Course(cid)
);
create table Student(
    sid number(10) not null primary key,
    name varchar2(10)
);
create table Course(
    cid number(10) not null primary key,
    name varchar2(10)
);

--5.
在表Grade的Stu_id和Cour_id上建立一个索引,如果现有数据量不多的情况下,如果使用以下sql查询,请问oracle的优化器会使用索引吗?
select * from grade where stu_id=1 and cour_id=1;

oracle的优化器不会使用索引

0

主题

1402

帖子

0

博客

马鹏磊

Rank: 9 Rank: 9 Rank: 9

积分

IP 编辑 禁止 帖子 清理

沙发
发表于2016-09-08 19:33:14 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式

drop table grade;
create table grade(
       name varchar2(10),
       inTime date,
       val varchar2(10)
)
insert into grade(name,inTime,val)
values('Tom','03-4月-08','A');
insert into grade(name,inTime,val)
values('Tom','02-4月-08','A');
insert into grade(name,inTime,val)
values('Tom','01-4月-08','B');
insert into grade(name,inTime,val)
values('Tom','31-3月-08','C');
insert into grade(name,inTime,val)
values('Jone','31-3月-08','A');
insert into grade(name,inTime,val)
values('Jone','01-4月-08','B');
insert into grade(name,inTime,val)
values('Jone','02-4月-08','B');
insert into grade(name,inTime,val)
values('Jone','03-4月-08','C');
--将intime这一列显示为“2008-04-03 16:44:11”
select to_char(inTime,'yyyy-mm-dd hh24:mi:ss')from grade;
--查询数据
select name,
       count(val) A,
       (select count(val)
          from grade
         where val = 'B'
           and name = 'Tom') B,
       (select count(val)
          from grade
         where val = 'C'
           and name = 'Tom') C
  from grade
 where val = 'A'
   and name = 'Tom'
 group by name
union
select name,
       count(val) A,
       (select count(val)
          from grade
         where val = 'B'
           and name = 'Jone') B,
       (select count(val)
          from grade
         where val = 'C'
           and name = 'Jone') C

  from grade
 where val = 'A'
   and name = 'Jone'
 group by name
 --08年4月份得A的次数超过1次的人员姓名
 --查询时间格式
 select name
   from grade
  where intime between to_date('2008/4/1', 'yyyy-mm-dd') and
        to_date('2008/4/30', 'yyyy-mm-dd')
    and val = 'A'
  group by name
 having count(val) >= 1

--在grade 上建立两个外键
--外键在有主键的表中建立
create table grade(
       name varchar2(10),
       intinme date,
       val varchar2(10),
       Stu_id varchar2(10),
       Cour_id varchar2(10),
         foreign key(Stu_id_fk) reference Student(id),
         foreign key(Cour_id_fk) reference Course(id)
       
)
create table Student(
       id varchar2(10) not null primary key,
       Stu_id_fk varvhar2(10)
    
      
)
create table course(
       id varchar2(10) not null paimary key,
       Cour_id_fk varchar(10)
      
)

--5

不会

 

0

主题

1932

帖子

0

博客

740317431

Rank: 9 Rank: 9 Rank: 9

积分

IP 编辑 禁止 帖子 清理

沙发
发表于2017-08-19 17:59:59 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
--1
select t.name,to_char(intime,'yyyy-mm-dd hh24:mi:ss'),t.val from grade t
--2
--行转列
select *
  from (select name, val from grade) pivot(count(val) for val in('A',
                                                                 'B',
                                                                 'C'))
--列转行
select * from grade2 unpivot (次数 for 等级 in (A,B,C))

--3
select name,count(1)
  from grade
 where to_char(intime, 'yyyy-mm') = '2008-04'
   and val = 'A'
 group by name
 having count(1)>1
--4
alter table grade
add constraint stu_id_fk foreign key (stu_id)
  references student(id) on delete cascade

0

主题

1902

帖子

0

博客

王之神判

Rank: 9 Rank: 9 Rank: 9

积分

IP 编辑 禁止 帖子 清理

沙发
发表于2017-08-20 17:50:12 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
select g.name,to_char(g.intime,'yyyy-mm-dd hh24:mi:ss'),g.val 
from grade g;


select *
from (select name, val from grade) 
pivot(count(val) for val in('A', 'B', 'C'));


select name,count(1)
from grade
where to_char(intime, 'yyyy-mm') = '2008-04' and val = 'A'
group by name
having count(1)>1;


alter table grade
add constraint stu_id_fk 
foreign key (stu_id) references student(id) on delete cascade
add constraint cour_id_fk 
foreign key (cour_id) references course(id) on delete cascade


不会

0

主题

997

帖子

0

博客

lijiaxing

Rank: 9 Rank: 9 Rank: 9

积分

IP 编辑 禁止 帖子 清理

沙发
发表于2017-10-24 18:08:01 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式

1.select to_char(intime,'yyyy-mm-dd hh24:mi:ss') from grade;

2.select * from (select name, val from grade) pivot(count(val) for val in('A','B','C'))

3.select * from grade2 unpivot (次数 for 等级 in (A,B,C))

5.

select name,count(1)
  from grade
 where to_char(intime, 'yyyy-mm') = '2008-04'
   and val = 'A'
 group by name
 having count(1)>1

alter table grade
add constraint stu_id_fk foreign key (stu_id)
references student(id) on delete cascade

0

主题

1213

帖子

0

博客

yanghao

Rank: 9 Rank: 9 Rank: 9

积分

IP 编辑 禁止 帖子 清理

沙发
发表于2018-09-06 20:58:45 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式

UPDATE GRADE SET intime=TO_DATE(“2008-04-03 16:44:11”);

SELECT NAME,(SELECT COUNT(1) FROM GRADE D WHERE SAL=A AND G.NAME=D.NAME) A FROM GRADE G GROUP BY NAME


select name from

(SELECT NAME,sal,count(sal) cs FROM GRADE WHERE INTIME BETWEEN "01-4月-08" AND "30-4月-08" group by name)x where x.sal='a' and x.cs>5;


alter table grade add foreign key (stu_id_flk) references student(sty_id);

alter table grade add foreign key (Cour_id_fk) references course(cour_id)




站点统计|举报|Archiver|手机版|小黑屋|Comsenz Inc.

GMT+8, , Processed in 0.195171 second(s), 9 queries .

Powered by javakc! X1.0

© 2004-2014 javakc

f1208.com 备案号:京ICP备14030918号-1

返回顶部