Oracle Basic query Statements and examples

1. Query all Columns

SELECT * from table name;

2. Query table structure

DESC table name;

3. Query the specified column

Select Ename,sal,job from table name;

4. View all tables and fields in Racle

Get table:

Select table_name from User_tables; The current user's table

Select table_name from All_tables; All users ' tables

Select table_name from Dba_tables; including system tables

Select table_name from dba_tables where owner= ' user name '

User_tables:

Table_name,tablespace_name,last_analyzed, etc.

Dba_tables:

Ower,table_name,tablespace_name,last_analyzed, etc.

All_tables:

Ower,table_name,tablespace_name,last_analyzed, etc.

All_objects:

Ower,object_name,subobject_name,object_id,created,last_ddl_time,timestamp,status, etc.

Get table field:

SELECT * from user_tab_columns where table_name= ' user table ';

SELECT * from all_tab_columns where table_name= ' user table ';

SELECT * from dba_tab_columns where table_name= ' user table ';

User_tab_columns:

TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,COLUMN_ID, etc.

All_tab_columns:

OWER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,COLUMN_ID, etc.

Dba_tab_columns:

OWER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,COLUMN_ID, etc.

Get Table Comments:

SELECT * FROM User_tab_comments

User_tab_comments:table_name,table_type,comments

And then there's the dba_tab_comments,all_tab_comments,

These two are more than user_tab_comments ower columns.

Get field Comments:

SELECT * FROM User_col_comments

User_col_comments:table_name,column_name,comments

And then there's the dba_col_comments,all_col_comments,

These two are more than user_col_comments ower columns.

5. Lock Oracle User and Unlock

Alter user Scott account lock

Alter user Scott account unlock

6. Where sentence

SELECT * from table name where field > value;

SELECT * from indicates where to_char (field, ' Yyyy-mm-dd ') > ' 1982-1-1 '; To_char conversion function

SELECT * from indicates where to_char (field, ' yyyy ') = ' 1980 ';

SELECT * from indicates where to_char (field, ' mm ') = ' 4 ';

Show wages at 2000 to 2500 wages

SELECT * from table name where field >=2000 and field <=2500;

SELECT * from indicates where field between and 2500;

7, fuzzy query like

%: Represents any 0 to more characters; _: represents any single character

How to display the employee's name and salary for the first letter S

Select Eaname, sal from table name where Eaname like ' s% ';

How to display all employee names and wages for the third letter O

Select Eaname, sal from table name where Eaname like ' __o% ';

8. Where statement uses in

How to display an employee with a empno of 123,345,678

1. Select * from indicates where empno=123 or empno=345 or empno=678;

SELECT * from indicates where empno in (123,345,678);

2, is null empty value query

SELECT * from indicates that the where field name is null;

3. Oracle Logical operators

Check your salary above 500 or post for MSN employees, and also meet their initials University J

SELECT * from indicates where (sal>500 or job= ' MSN ') and (Enname like ' j% ');

drop table Student;drop table Course;drop table Score;drop table teacher;
CREATE TABLE STUDENT (SNO varchar (3) NOT NULL, SNAME varchar (4) Not null,ssex varchar (2) NOT NULL, Sbirthday Date,class NUM BER not NULL);
CREATE TABLE COURSE (CNO varchar (5) NOT NULL, CNAME varchar (TEN) NOT NULL, TNO varchar (TEN) not NULL);
CREATE TABLE Score (SNO varchar (3) NOT NULL, CNO varchar (5) is not NULL, degree number is not null);
CREATE TABLE TEACHER (TNO varchar (3) NOT NULL, Tname varchar (4) is not NULL, Tsex varchar (2) is not NULL, Tbirthday DATE not NUL L, PROF varchar (6), Depart varchar (TEN) not NULL);
INSERT into STUDENT (sno,sname,ssex,sbirthday,class) VALUES (108, ' Zeng Hua ', ' Male ', to_date (' 1977-09-01 ', ' yyyy-mm-dd '), 95033) INSERT into STUDENT (Sno,sname,ssex,sbirthday,class) VALUES (105, ' Kuanming ', ' Male ', to_date (' 1975-10-02 ', ' yyyy-mm-dd '), 95031 INSERT into STUDENT (sno,sname,ssex,sbirthday,class) VALUES (107, ' Wang Li ', ' Female ', to_date (' 1976-01-23 ', ' yyyy-mm-dd '), 95033); INSERT into STUDENT (Sno,sname,ssex,sbirthday,class) VALUES (101, ' Li June ', ' Male ', to_date (' 1976-02-20 ', ' yyyy-mm-dd ') , 95033); INSERT into STUDENT (Sno,sname,ssex,sbirthday,class) VALUES (109, ' Wang Fang ', ' Female ', to_date (' 1975-02-10 ', ' yyyy-mm-dd ' ), 95031); INSERT into STUDENT (Sno,sname,ssex,sbirthday,class) VALUES (103, ' contacts ', ' Male ', to_date (' 1974-06-03 ', ' yyyy-mm-dd '), 95031);
Insert into COURSE (CNO,CNAME,TNO) VALUES (' 3-105 ', ' Introduction to Computer ', 825); insert into COURSE (CNO,CNAME,TNO) VALUES (' 3-245 ', ' OS ', 804); insert into COURSE (CNO,CNAME,TNO) VALUES (' 6-166 ', ' data circuit ', 856); insert into COURSE (CNO,CNAME,TNO) VALUES (' 9-888 ' , ' advanced mathematics ', 100);
Insert INTO score (Sno,cno,degree) VALUES (103, ' 3-245 ', +), insert into score (Sno,cno,degree) VALUES ("3-245 ',"); Nsert into score (Sno,cno,degree) VALUES (109, ' 3-245 ', ""), INSERT into score (Sno,cno,degree) VALUES (103, ' 3-105 '; Nsert into score (sno,cno,degree) VALUES ("3-105", "n"), INSERT into score (Sno,cno,degree) VALUES (109, ' 3-105 ', ()); Nsert into score (Sno,cno,degree) VALUES (101, ' 3-105 ', +); INSERT into score (sno,cno,degree) VALUES (107, ' 3-105 '; Nsert into score (sno,cno,degree) VALUES (108, ' 3-105 ', +); INSERT into score (Sno,cno,degree) VALUES (101, ' 6-166 ', ()); I Nsert into score (sno,cno,degree) VALUES (107, ' 6-106 ', +); INSERT into score (sno,cno,degree) VALUES (108, ' 6-166 ', 81);
INSERT into TEACHER (Tno,tname,tsex,tbirthday,prof,depart) VALUES (804, ' sung ', ' Male ', to_date (' 1958-12-02 ', ' yyyy-mm-dd '), ' Associate Professor ', ' computer Department '); INSERT into TEACHER (Tno,tname,tsex,tbirthday,prof,depart) VALUES (856, ' Zhang Xu ', ' Male ', to_date (' 1969-03-12 '), ' Yyyy-mm-dd '), ' lecturer ', ' electronic Engineering '); INSERT into TEACHER (Tno,tname,tsex,tbirthday,prof,depart) VALUES (825, ' Wang Ping ', ' female ', to_date (' 1972-05-05 ', ' yyyy-mm-dd '), ' ta ', ' computer Department '); INSERT into TEACHER (Tno,tname,tsex,tbirthday,prof,depart) VALUES (831, ' Liu Bing ', ' Female ', to_date (' 1977-08-14 ', ' yyyy-mm-dd '), ' teaching assistant ', ' Electronic Engineering Department ');

Oracle Basic query Statements and examples