Common oracle SQL statements

Common ORACLE commands spoolhost none -- use sqlplus to connect to the database -- standard usage sqlplususernamepassword @ hostname: portSERVICENAME -- Sample sqlpluspaymentpassword@192.168.117.1pay -- hostname: portSERVICENAME can be configured as an alias to the tnsnames. ora file sqlpluspayment

Common ORACLE commands spool host none -- use sqlplus to connect to the database -- standard usage sqlplus username/password @ hostname: port/SERVICENAME -- Example sqlplus payment/password@192.168.117.1/pay -- hostname: port/SERVICENAME can be configured as an alias to tnsnames. ora file sqlplus payment

Common ORACLE commands
Spool
Host
<无>

-- Use sqlplus to connect to the database -- standard usage sqlplus username/password @ hostname: port/SERVICENAME -- Example sqlplus payment/password@192.168.117.1/pay -- hostname: port/SERVICENAME can be configured as an alias to tnsnames. ora file sqlplus payment/password @ tnsname -- first enter sqlplus, then use the conn method to connect sqlplus/nologconn payment/password@192.168.117.1/pay -- copy several -- operating system authentication, the listener process sqlplus/as sysdba is not required. Only the local database can be connected, and the listener process sqlplus username/password is not required. Listener process is available. The most common connection is through a network. Sqlplus username/password @ tnsname

-- Log on to the server and switch to the oracle user su-oracle -- enter the folder, all subsequent operations on the file are based on the directory cd/oracle/database/-- sqlplus to connect to the specific database sqlplus payment/password -- determine whether the user is correctly show user; -- execute payment. SQL -- output all content to payment. log, the file is located in the/oracle/database/directory spool payment.log@payment_indexs_execute.sqlspool off -- use the host command can be converted to the linux Command to execute host more payment. log -- you can also use the conn command to connect to the database conn payment/passwordshow user;

-- View the database table index SELECT * FROM USER_INDEXS WHERE TABLE_NAME = 'tablename'; -- view the column information created by the index SELECT * FROM USER_IND_COLUMNS WHERE INDEX_NAME = UPPER ('indexname '); -- CREATE primary key -- 1) when creating a TABLE, specify the primary key create table Persons (P_ID int not null, LastName varchar (255) not null, FirstName varchar (255 ), address varchar (255), City varchar (255), CONSTRAINT PK_Persons primary key (P_ID, LastName) -- 2) ALTER Table ADD two methods alter table TableName ADD Primary key (Column1); -- does this method support multiple columns? In this way, no primary key name is specified. What is the default primary key name? alter table TableName add constraint PK_TabeName primary key (Column1, Column2); -- create a UNIQUE index alter table TableName add constraint U_INDEX_NAME UNIQUE (Column1, column2); create unique index U_INDEX_NAME ON TableName (Column1, Column2); -- is there an ALTER method for creating a common INDEX? Create index I _INDEX_NAME ON TableName (Column1, Column2); -- when an INDEX column already exists, "locate duplicate keyword"/* 1 is reported when a unique INDEX is created. modify duplicate records. 2. create an enable novalidate Index */alter table TableName add constraint U_IDX_NAME enable novalidate; -- modify the index type: enable novlidate-> enable validatealter table TableName enable validate constraint U_IDX_NAME; -- delete index alter table TableName drop constraint U_IDX_NAME; -- set the TABLE to READ-only table alter table t read only; -- Cancel READ-ONLY (set to READ/WRITE) alter table t read write;

-- Update a field/* The conditions in the brackets are the correlated conditions outside the brackets of Table1 and Table2. The conditional Conditions are the range updated by Table1 */UPDAE Table1 SET Column1 = (SELECT Column FROM Table2 WHERE conditions) WHERE Conditions; -- UPDATE multiple fields. Use commas (,) between multiple fields to split UPDATE Table1 SET Column1, Column2 = (SELECT Column1, Column2 FROM Table2 WHERE Conditions) WHERE Conditions; -- associate the MCC and MCCDescription fields of institution 001 and 002 with the MCCDetail table for UPDATE Merchant t1 set mcc, MCCDescription = (select mcc, MCCDescription FROM MCCDetail t2WHERE T1.MCC = T2.MCC) WHERE T1.MerchantNo IN ('001', '002 ');