Oracle Database Connection + addition, deletion, modification, and query operations tutorial, oracle addition and Deletion

Oracle Database Connection + addition, deletion, modification, and query operations tutorial, oracle addition and Deletion

Connect to the oracle database and use

$ Conn = oci_connect ('account', 'Password', "(DEscriptION = (ADDRESS = (PROTOCOL = TCP) (HOST = ip address connecting to oracle) (PORT = PORT )) (CONNECT_DATA = (SID = HCMPRD2) "," utf8 "); $ query = oci_parse ($ conn," select * from TEST. ABC "); // TEST Database ABC table oci_execute ($ query); oci_fetch_all ($ query, $ list, null, null, OCI_FETCHSTATEMENT_BY_ROW); var_dump ($ list );

I. Add

View table structure desc table name

View table structure desc table name

Add a field alter table name add (column type );

Modify Field Type altertable table name modify (column type );

Delete the alter table Name drop column name;

Rename student to stu

Drop table droptable table name

Insert data into Table Name (column name 1, column name 2) values (column value 1, column value 2)

How to quickly copy data 1 insert into test select * from test;

How to quickly copy data 2 create table name select * from test

Import data: @ file name

How to check how long a SQL statement has been executed set timing on

Example 1: create table test (

Id number,

Name varchar2 (20 ),

Age number (2)

);

 

Example 2,

Insert into table name values (values of all columns );

Insert into test values (1, 'hangsan ', 20 );

Example 3,

Insert into Table Name (column) values (corresponding value );

Insert into test (id, name) values (2, 'lisi ');

Ii. Delete

Delete from test where id = 1;

Delete all records from test

Delete all records truncate table test (deleting all data does not affect the table structure)

Drop table test (delete all data, including the table structure)

Submit data commit

Rollback data rollback

3. Change

Update Record update table set column = new value [where condition]

Update all data update table set column = New Value

Example 1: updatetest set name = 'hangsan2' where name = 'hangsan'

Example 2: update test set age = 20;

Iv. Query

Select * from test where rownum <= 10

Remove duplicate show selectdistinct column from Table Name

Date type to_date (string 1, string 2) string 1 is the date string, string 2 is the format

SQL fuzzy query like

The query results must be sorted by the order by field, asc ascending desc descending, default ascending

The specified field is in ascending order.

Filter the grouped structure and output having

Example 1: select * from emp order by sal --> after order by, the default value is ascending.