Oracle basic statement

1. Create a table

Create table IT_EMPLOYEES

(

ENPLOYEES_ID NUMBER (6) NOT NULL UNIQUE

FIRST_NAME VARCHAR2 (20)

LAST_NAME VARCHAR2 (25) NOT NULL

EMAIL VARCHAR2 (25)

PHONE_NUMBER VARCHAR2 (20)

JOB_ID VARCHAR2 (10)

SALARY NUMBER (8 dint 2)

MANAGER_ID NUMBER (6)

);

2.-- create an index. After creation, it is stored in the ascending order of the value of LAST_NAME, with it_lastname as the index name.

Create [unique] [cluster] index index name on table name (field name)

Unique: the index value cannot be repeated.

Cluster: the index created is a clustered index.

Create index it_lastname on it_employees (last_name)

3. Delete the table

Delete statements when a base table is no longer needed

Drop table table name

Delete View

Drop view View name

Delete index

Drop index index name

3. Insert data into the table

Insert into IT_EMPLOYEES values (1meme Liuzhong pageantry beijinghejia qq.composer pr 110meme 001m 100000jue 1)

4. Insert data into some fields in the table

5. Insert into IT_EMPLOYEES (ENPLOYEES_ID,LAST_NAME) VALUES (3recoveryanan)

6. Alter table it_employees add age int;-add age field with type int

7. Alter table it_employees drop column age;-- delete the field age

8. Order by classification

Select * from it_employees where salary > = 100000 order by salary;-the default ascending order by salary, from low to high. The descending order is to add desc after it, that is:

Select * from it_employees where salary > = 100000 order by salary desc

9. Group by groups records

Select job_id, avg (salary), sum (salary), max (salary), count (job_id) from it_employees group by job_id

Attachment: http://down.51cto.com/data/2366584