Oracle Database is a powerful and complex RDBMS, offering a wide range of features for efficient data management. This cheat sheet provides a quick reference to essential commands and operations.
Connecting to Oracle Database
sql sqlplus username/password@hostname:port/SID
- Connect to an Oracle database using SQL*Plus.
bashconn username/password@hostname:port/SID
- Alternative connection command.
Basic SQL Commands
sql SELECT * FROM table_name;
- Retrieve all rows and columns from a table.
sql SELECT column1, column2 FROM table_name;
- Retrieve specific columns from a table.
sql INSERT INTO table_name (column1, column2) VALUES (value1, value2);
- Insert a new record into a table.
sql UPDATE table_name SET column1 = value1 WHERE condition;
- Update existing records in a table.
sql DELETE FROM table_name WHERE condition;
- Delete records from a table.
Database Operations
Create Database
sql CREATE DATABASE database_name;
- Create a new database.
Drop Database
sql DROP DATABASE database_name;
- Delete a database.
Create Table
sql CREATE TABLE table_name (
column1 datatype CONSTRAINT,
column2 datatype CONSTRAINT,
...
);
- Create a new table.
Drop Table
sql DROP TABLE table_name;
- Delete a table.
Constraints
Primary Key
sql ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY (column);
- Add a primary key constraint.
Foreign Key
sql ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column) REFERENCES other_table (other_column);
- Add a foreign key constraint.
Unique Key
sql ALTER TABLE table_name ADD CONSTRAINT unique_name UNIQUE (column);
- Add a unique key constraint.
Indexes
Create Index
sql CREATE INDEX index_name ON table_name (column);
- Create an index on a table.
Drop Index
sql DROP INDEX index_name;
- Delete an index.
Views
Create View
sql CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;
- Create a view based on a SELECT query.
Drop View
sql DROP VIEW view_name;
- Delete a view.
Transactions
Begin Transaction
sql BEGIN;
- Start a new transaction.
Commit
sql COMMIT;
- Save changes made in the current transaction.
Rollback
sql ROLLBACK;
- Undo changes made in the current transaction.
PL/SQL Basics
Anonymous Block
sql DECLARE
variable_name datatype;
BEGIN
-- PL/SQL statements
EXCEPTION
WHEN exception_name THEN
-- exception handling
END;
- Create an anonymous PL/SQL block.
Stored Procedure
sql CREATE OR REPLACE PROCEDURE procedure_name IS
BEGIN
-- PL/SQL statements
END procedure_name;
- Create a stored procedure.
Stored Function
sql CREATE OR REPLACE FUNCTION function_name RETURN datatype IS
BEGIN
-- PL/SQL statements
RETURN value;
END function_name;
- Create a stored function.
Trigger
sql CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT OR UPDATE OR DELETE ON table_name
FOR EACH ROW
BEGIN
-- PL/SQL statements
END trigger_name;
- Create a trigger.
User and Role Management
Create User
sql CREATE USER username IDENTIFIED BY password;
- Create a new user.
Grant Privileges
sql GRANT privilege TO username;
- Grant specific privileges to a user.
Create Role
sql CREATE ROLE role_name;
- Create a new role.
Grant Role
sql GRANT role_name TO username;
- Assign a role to a user.
Common Commands
Show Tables
sql SELECT table_name FROM all_tables;
- List all tables in the current schema.
Describe Table
sql DESC table_name;
- Show the structure of a table.
Show Users
sql SELECT username FROM all_users;
- List all users in the database.
Show Current User
sql SELECT user FROM dual;
- Display the current user.
Useful Functions
String Functions
sql UPPER(string);
LOWER(string);
SUBSTR(string, start_position, length);
- Convert to upper/lowercase, substring extraction.
Numeric Functions
sqlROUND(number, decimal_places); TRUNC(number, decimal_places);
- Round and truncate numbers.
Date Functions
sql SYSDATE;
TO_DATE('YYYY-MM-DD', 'format');
- Get current date, convert string to date.
This cheat sheet provides a quick reference to commonly used commands and operations in Oracle Database, helping you manage and manipulate your data efficiently.