Collection of the most common Oracle commands for new students

We have sorted out the most common Oracle commands for Oracle beginners to learn! 1. Create a tablespace: createtablespace tablespace name datafile 'file address and file name' [size] [autoextend [on | off]. Note:

We have sorted out the most common Oracle commands for Oracle beginners to learn!

1,Create a tablespace:

Create tablespace name datafile' file address and file name '[size] [autoextend [on | off]; Note: [size] indicates the size of the tablespace to be created. The unit can be K or M. Autoextend indicates whether the created tablespace is automatically extended. 2, Create a new user:Create user Username identified by password [default tablespace name] [temporary tablespace name]; Note: The [default] statement indicates that a default tablespace is allocated to the newly created user; [tempporary] provides a temporary tablespace for the newly created user. 3, Grant permissions to new users:Grant connect to user name; Grant the user access to the database, create tables, and other structures. Grant resource to user name; Grant the user the permission to use the database space. Grant select | update on emp to user name; Note: select grants the user the permission to query the emp table, and update grants the user the permission to modify the emp table. 4, Change Password:Alter user Username identified by new password; 5, Delete A User:Drop user Username cascade; Note: cascade must be used when the user has a mode object; 6, Create a table:Create table Name (Field 1, Field 2 ,........ Field N type ); 7, Modify Table Structure:Alter table name madofy (column name type ,.........); Modifies the type and field size of a table. Alter table name add (field type ,........); To add a new column to the table. Alter table Name drop column name, indicating to delete existing columns in the table. 8, Clear all data in the table:The truncate table name. The truncate command is similar to the delete command, and can be used to delete all records in the table. The difference is that the truncate command deletes all records in the table and immediately releases space, the delete operation can release space only after the transaction is committed. It is more efficient to use truncate when data is determined to be unavailable. 9, View the table structure:Desc table name; 10, Delete tableDrop table name; 11, Query non-duplicate columns:Select distinct column name from table name; 12, Create a new table based on the existing table:Create table: the new table name is as select * from the old table name. Note: The statement is flexible. The fields and data in the new table are determined by the query statement. For example, you can change * to an existing column in the old table. You can add the where condition after the query statement to control the insertion of records into the new table. 13, Insert records from other tables:Insert into table name A select * from table name B [where condition]. Note: to modify the statement, Table A must have the same table structure as table B. 14, Grant the table operation permission to other users:Grant select | delete | update on table name to user name [whit grant option]; Note: [with grant option] indicates that the authorized user can grant this permission to other users. In addition, the statement can only grant users the permission to modify certain columns of a table. The syntax is as follows: grant update (column name 1, column name 2 ~~~) On Table name to user name; 15, Revoke the permissions of an authorized user:Revoke select | update | insert on table name from user name; 16, Set OPERATOR: A)The union operator is used to return all rows that are not repeated in two queries. For example, select ID from table name 1 union select ID from table name 2; B)The union all operator is used to return all rows selected by two queries. For example, select ID from table name 1 union all select ID from table name 2; C)The intersect operator is used to return only the rows selected for both queries. For example, select ID from table name 1 intersect select ID from table name 2; D)The minus operator is used to return the rows in the first table but not in table 2. For example, select ID from table name 1 minus select ID from table name 2; 17, Function: A)Date functions: For details, see 56 ~ 59 Page. B)Character functions: For details, see 59 ~ 60 Page. C)Numeric functions: For details, see 60 ~ 62 Page. D)Other functions: For details, see 63 ~ 71 Page.