flower flower - 6 months ago 11
SQL Question

What's wrong with my sql in oracle?

What's wrong with my sql in oracle? There are some data in my table,I select all of them and I can get them.but I can not search them if I add a condition.When did I add single or double quotation marks in my sql?Now I find that when I write some search statement,I must add single quotation marks.And when I write some insert statement,I must add double quotation marks.Or my sql will run bad.How to judge when should I use the different quotation in my sql?

select * from T_STUDENT


and the result is:

sex(varchar2) phone(varchar2) birthtime(timestamp)
1 13553812147 2016-06-03 16:02:00.799 **


When I add a search condition,but the result is null.

//error:ORA-000904
select * from T_STUDENT where phone='13553812147'
//error:ORA-000904
select * from T_STUDENT where PHONE='13553812147'
//run well but result is null
select * from T_STUDENT where 'phone'='13553812147'


And the same question I meet in the insert statement.

//error:ORA-000904
insert into T_STUDENT (sex,phone,birthtime) values('1','12345645454','2016-06-04 16:02:00.799')
//error:ORA-000928 missing select keyword
insert into T_STUDENT ('sex','phone','birthtime') values('1','12345645454','2016-06-04 16:02:00.799')
//run well but must add double quotation marks
insert into T_STUDENT ("sex","phone","birthtime") values('1','12345645454','2016-06-04 16:02:00.799')

Answer

This is because your table was defined using double quotes around the column names:

create table t_student
 ( "sex" varchar2(1)
 , "phone" varchar2(30)
 , "birthtime" timestamp
 );

Using double quotes makes the names case-sensitive and so for ever after they must be referenced in double quotes, since by default Oracle is nicely case-insensitive. For this reason you should never use double quotes when creating tables, views etc.

Comments