Common Oracle Statement-------------------------------------------"(note)

Orale Common Statements

1: Query the field for the specified table name

SELECT * from sys.user_tab_columns where table_name= table name//query field specifying table name

2: Querying Database parameters

Show parameter db;

3: Query The instance name of the database

Select instance_name from V$instance;

3: After the database installation is complete, if you want to know if the running amount database has a domain name and the database domain names can be used

Select value from V$parameter where name= ' Db_domain '

Show parameter domain

4: After the database installation is complete, if you want to know if the running amount database has a domain name and the database domain names can be used

Select value from V$parameter where name= ' Db_domain '

Show parameter domain

5: After the database installation is complete, if you want to know if the running amount database has a domain name and the database domain names can be used

Select value from V$parameter where name= ' Db_domain '

Show parameter domain

6:oracle Basic Operation statements

Oracle Operation statements:

1. Create a table

CREATE TABLE Table name (

Column Name 1 type,

Column Name 2 type

);

2. Modifying class Properties

ALTER TABLE name modify (column name type);

3. Adding columns

ALTER TABLE name Add (column name type);

4. Adding primary KEY constraints and non-null constraints

ALTER TABLE name add constraint pk_ table name primary key (column name);

ALTER TABLE name modify (column name not NULL);

5. Delete a PRIMARY KEY constraint

ALTER TABLE name drop PRIMARY key;

ALTER TABLE name drop constraint pk_ table name;

6. Failure constraints

ALTER TABLE name disable primary key;

The ALTER TABLE name disable constraint pk_ table name;

7. Effective constraints

ALTER TABLE name enable primary key;

ALTER TABLE name enable constraint Pk_ table name;

8. Delete Columns

ALTER TABLE name drop column name;

9. Set a column that is not available, and then delete

ALTER TABLE name set unused (column name);

ALTER TABLE name drop unused columns;

10. Modify the table name

Rename table name 1 to table name 2

ALTER TABLE name 1 rename to table name 2;

11. Truncate the table

TRUNCATE TABLE name;

12. truncate table to preserve row space

TRUNCATE table name Resue storage;

13. View Table Structure

DESC table name;

14. Delete a table

drop table name;

15. Inserting records

Example: INSERT into table name values (content 1, content 2, content 3, content 4);

16. Inserting rows with parameter dialogs

Example: INSERT into table name VALUES (& column name 1,& column name 2);

Insert into table name values (content 1, content 2);

17. Insert a few columns of records

Insert into table name (column name 1, column name 2) values (content 1, content 2);

18. Insert a null value for the column (its column cannot be not NULL)

Insert into table name values (content 1,null,null);

19. Create table (including primary key and foreign key setting) method one

CREATE TABLE Table name

(

Column Name 1 type

Constraint pk_ table name primary key,

Column Name 2 type NOT NULL,

Column Name 3 type

Constraint fk_ table name reference table name (column name),

Column Name 3 type

Constraint ck_ table name check (column name 3 in ("Content 1", "Content 2", "Content 3"))

);

20. Querying All Rows

SELECT * from table name;

21. Querying a few columns

Select Column Name 1, column name 2 from table name;

22. Duplicate row elimination

Select distict column name from table name;

23.where Statement Query

SELECT * FROM table name where condition order by column name;

(Note: If the number type is detected automatically in ascending order, if you want to sort in descending order, select * from table name where condition order by column name Desc;)

24. Create a table, method two

CREATE TABLE table name (         column name 1 type primary key,         column name 2 type NOT NULL,         column name 3 type check (column name 3 in ("'", "'", ' "),         column name 4 type Refernce table name (column name));

　　

25. Modify column = '? ' The data

Update table name set (column =? ) Where column = '? ’;

　　

26. Delete Rows

Delete from table name where condition;

　　

27. Transaction Processing

--Transaction processing

Update table name

Set column name (date) = ' 30月-May-98 '

Where condition;

SavePoint Mark1;

Delete from table name where condition;

SavePoint Mark2;

Rollback to SavePoint Mark1;

Rollback

28. Set up user user1, password is password

Permissions granted to user Connect,resource

Connect Role for Login

Resource roles are used to build tables and so on.

Connect System/manager

Create user user1 identified by password;

Grant Connect,resource to password;

29. Data Control Language

Connect Scott/tiger

30. Grant User1 permission to query and modify the table 1

Grant Select,update on table 1 to User1;

31. Grant User1 the rights to modify the columns 1 and 2 in table 1

Grant Update (column 1, column 2) on table 1 to User1;

32. Grant users access to the table 1 query user1

And User1 users can also grant this permission to other users (with GRANT option)

Grant Select on table 1 to User1 with GRANT option;

33. Revoke Permissions for table 1 queries and modifications from user User1

Revoke select,update on table 1 from User1

About Dates

1. Date conversion: To_char (date, "Yyyy-mm-dd")

2, Oracle Direct query out of the year, here is ten years

Select to_char (add_months (sysdate, (1-rownum) *12), ' yyyy ') date from dual Connect by rownum<11 order by month Desc

(1- rownum) *12: A minus line number multiplied by 12 months

Results

Oracle Connection Query

SELECT  A. Farming recorder,  a. Cooperatives, a.   Township,   a. Village name,  B. Land number,  B. Planting number, season,  year,  name,  planting area,  seed dosage, (select count (id) from yjzx_ farming record _ whole land where planting number =b. Planting number) as the ground,  (select count (id) from yjzx_ farming record _ seeding where planting number =b. Planting number) as seeding,   (select count (id) from yjzx_ farming record _ Nursery where planting number =b. Planting number) As Nursery,    (select count (id) from yjzx_ farming record _ fertilizer where planting number =b. Planting number) as fertilizer from   (select         farming recorder,         land number,        Yjzx_ Cooperatives and village information. Village name,        Yjzx_ Cooperative and village information. Cooperatives,        yjzx_ cooperatives and village information. Township        from   yjzx_ plots information,               yjzx_ cooperatives and village information        WHERE  Yjzx_ parcel information. Village = Yjzx_ cooperatives and village information. Village name) A,       yjzx_ cropping Records bwhere A. Land number = B. Land number and a. Farming recorder = ' Shang '

Query results

Select     ID, warehouse    number,    warehouse address,    company,    Warehouse building,    internal warehouse number, capacity    "Capacity (ton)",   //change the display name of the column to the storage capacity (ton)    Administrator ID ,    administrator name,    (select NVL (sum (current inventory)/1000,0) from Yjzx_ inventory information where yjzx_ inventory information. Warehouse Number =YJZX_ warehouse basic information. Warehouse number) "Current stock (ton)", Storage Capacity    -(select NVL (sum (current inventory)/1000,0) from Yjzx_ inventory information where yjzx_ inventory information. Warehouse Number =YJZX_ warehouse basic information. Warehouse number) "Remaining capacity (ton)" from Yjzx_ Warehouse basic Information WHERE Administrator id=20316

NVL (sum(current stock)/1000,0): unit conversion, converted Ton

Common Oracle Statement-------------------------------------------"(note)