Oracle prerequisite statement
SQL statements commonly used in Oracle_11g (a must for senior engineer)
SQL statements commonly used in Oracle_11g (a must for senior engineer)
-- exit SQLPLUS
Exit
-- modify the password of system (sys) account
SQLPLUS / NOLOG
CONN / AS SYSDBA
ALTER USER SYSTEM IDENTIFIED BY tarring
-- clear SQLPLUS screen
CLEAR SCREEN
CL SCR
-- View the data file location
SELECT NAME FROM v$datafile
-View the location of the control file
SELECT NAME FROM v$controlfile
-- View the log file location
SELECT MEMBER FROM v$logfile
-- create a tablespace
CREATE TABLESPACE ts01
DATAFILE'D:\ DataBase\ Oracle11g\ oradata\ orcl\ test_db01.dbf'SIZE 100m
AUTOEXTEND ON NEXT 100M MAXSIZE 1024M
DEFAULT STORAGE (INITIAL 10m NEXT 1m)
PERMANENT
ONLINE
LOGGING
-- modify tablespace
ALTER TABLESPACE ts01
NOLOGGING
-- add data files to the tablespace
ALTER TABLESPACE ts01
ADD DATAFILE'D:\ DataBase\ Oracle11g\ oradata\ orcl\ test_db02.dbf'SIZE 100m REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
-- Delete tablespace
DROP TABLESPACE ts01
-- Delete tablespaces while deleting data files
DROP TABLESPACE ts01 INCLUDING CONTENTS AND DATAFILES
-- create tables in tablespaces
CREATE TABLE student (
Student_id VARCHAR2 (10)
Student_name VARCHAR2 (20)
) TABLESPACE ts01
-- View the tablespace to which the table belongs
SELECT TABLE_NAME, TABLESPACE_NAME FROM tabs WHERE TABLE_NAME = 'STUDENT'
-- View the table structure
DESCRIBE student
DESC student
-- add table notes
COMMENT ON TABLE student IS 'Student Information Table'
-- View table comments
SELECT * FROM USER_TAB_COMMENTS WHERE TABLE_NAME = 'STUDENT'
SELECT * FROM ALL_TAB_COMMENTS WHERE TABLE_NAME = 'STUDENT'
-- add comments to the table field
COMMENT ON COLUMN STUDENT.STUDENT_ID IS 'Student number'
-- View table field comments
SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME = 'STUDENT'
SELECT * FROM ALL_COL_COMMENTS WHERE TABLE_NAME = 'STUDENT'
-- View all user tables
SELECT * FROM User_Tables
-- View all objects owned by the user
SELECT * FROM User_Objects
-- View the sequence of table attempts owned by the user
SELECT * FROM User_Catalog
-- Table field modification
ALTER TABLE student MODIFY (student_id CHAR (15))
-- Table field added
ALTER TABLE STUDENT ADD (AGE NUMBER (2))
-Delete table field
ALTER TABLE STUDENT DROP COLUMN student_name
-- modify the table name
RENAME STUDENT TO STU
-- delete the table
DROP TABLE STUDENT
CREATE TABLE student (
S_id Varchar2 (10)
S_name varchar2 (20)
S_age Number (3)
S_birthday DATE
) TABLESPACE ts01
-- add a record
INSERT INTO
Student (s_id, s_name, s_age, s_birthday)
VALUES ('S000000001mm,' Tarring01', 10, to_date ('1982-10-06mm maidd`))
INSERT INTO
Student (s_id, s_name, s_age, s_birthday)
VALUES ('S000000002,' Tarring02', 10, Sysdate)
When using alternative variables, enter string fields in quotation marks as well
INSERT INTO
Student (s_id, s_name, s_age, s_birthday)
VALUES (& s_id, & s_name, 10, Sysdate)
-- modify the record
UPDATE student SET s_name = 'Tao Chuan', sages = 20 WHERE s_id = 'S000000002'
-- Delete records
DELETE FROM student WHERE s_id = 'S000000002'
-- truncation table
TRUNCATE TABLE student
-- transaction processing
COMMIT;-commit transaction
INSERT INTO student (s_id, s_name) VALUES ('S001,' tarring1')
ROLLBACK;-rollback, rollback to the point after the last commit
-transactions with recovery points
COMMIT
INSERT INTO student (s_id, s_name) VALUES ('S001,' tarring1')
SAVEPOINT firstdate
INSERT INTO student (s_id, s_name) VALUES ('S002,' tarring2')
SAVEPOINT seconddate
DELETE FROM student
ROLLBACK TO firstdate
SELECT * FROM student
-- constraint description
-- UNIQUE specifies the value of the field, which must be unique
-- PRIMARY KEY primary key, which indexes the specified field and is the only value
-- NOTNULL cannot be a null value [''NULL] or 0 (zero)
-- CHECK check, which must meet the specified conditions
-- FOREIGN KEY foreign key, used to create a relationship between reference tables
-- create tables and establish unique constraints
CREATE TABLE student (
S_id Varchar2 (10)
S_name varchar2 (20)
S_age Number (3)
S_birthday DATE
CONSTRAINT s_name_uk UNIQUE (s_name)
) TABLESPACE ts01
-- View unique constraints
SELECT table_name, constraint_name, constraint_type FROM User_Constraints WHEREtable_name = 'STUDENT'
-- Job: data Dictionary [Common Classification]
-- create tables and primary keys at the same time
CREATE TABLE student (
S_id Varchar2 (10)
S_name varchar2 (20)
S_age Number (3)
S_birthday DATE
CONSTRAINT s_id_pk PRIMARY KEY (s_id)
) TABLESPACE ts01
-- View primary key constraints
SELECT table_name, constraint_name, constraint_type FROM User_Constraints WHEREtable_name = 'STUDENT'
-- create tables and non-empty fields at the same time
CREATE TABLE student (
S_id Varchar2 (10)
S_name varchar2 (20) NOT NULL
S_age Number (3)
S_birthday DATE
CONSTRAINT s_id_pk PRIMARY KEY (s_id)
) TABLESPACE ts01
INSERT INTO student (s_id, s_name) VALUES ('S001, NULL);-- insert a null
-- View non-empty constraints
SELECT table_name, constraint_name, constraint_type, search_condition FROMUser_Constraints WHERE table_name = 'STUDENT'
-- establishing tables and establishing inspections at the same time
CREATE TABLE student (
S_id Varchar2 (10)
S_name varchar2 (20)
S_age Number (3)
S_birthday DATE
CONSTRAINT s_age_ck CHECK (s_age BETWEEN 1 AND 100)-Endpoint values can be used
) TABLESPACE ts01
-- View check constraints
SELECT table_name, constraint_name, constraint_type, search_condition FROMUser_Constraints WHERE table_name = 'STUDENT'
-- use of foreign keys
CREATE TABLE team (
T_id Varchar2 (10)
T_name Varchar2 (20)
CONSTRAINT t_id_pk PRIMARY KEY (t_id)
) TABLESPACE ts01
CREATE TABLE student (
S_id Varchar2 (10)
Team_id VARCHAR2 (10)
S_name varchar2 (20)
CONSTRAINT s_id_pk PRIMARY KEY (s_id)
CONSTRAINT s_team_id_fk FOREIGN KEY (team_id) REFERENCES team (t_id)
) TABLESPACE ts01
-- View the foreign key constraints of the table
SELECT table_name, constraint_name, constraint_type FROM User_Constraints WHEREtable_name = 'STUDENT'
Drop table team;-referenced tables cannot be deleted
Insert into team (t_id, t_name) values ('t001,' lansene')
Insert into student (s_id, s_name, team_id) values ('s 001', 't001')
Delete from team;-referenced records cannot be deleted
-- close a constraint
ALTER TABLE student DISABLE CONSTRAINT s_team_id_fk
-- enable a constraint
ALTER TABLE student ENABLE CONSTRAINT s_team_id_fk
-- Delete a constraint
ALTER TABLE student DROP CONSTRAINT s_team_id_fk
-- add a constraint to the created table
ALTER TABLE student ADD CONSTRAINT s_team_id_fk FOREIGN KEY (team_id) REFERENCES team (t_id)
/ * |
| | 5 types of SQL statement | Command | description |
| | * |
| | Data Retrieval data retrieval | select | query record |
| | * |
| | Date Manipulation Language [DML] data manipulation language | insert | add record |
| | update | modify record |
| | delete | Delete record |
| | * |
| | Data Definition Language [DDL] data definition language | create | create |
| | alter | modify |
| | drop | discard [delete] |
| | rename | rename |
| | truncate | truncation |
| | * |
| | Transaction Control transaction control | commit | confirm command |
| | rollback | fall back to the last confirmed command or SavePoint |
| | savepoint | set SavePoint |
| | * |
| | Data Control Language [DCL] data Control language | grant | Grant permissions |
| | revoke | revoke permissions |
| * /
/ * * |
| | system permissions | description |
| | * | |
| | create session | Connect to the database |
| | * | |
| | create table | create table |
| | * | |
| | create sequence | creation sequence |
| | * | |
| | create view | create view |
| | * | |
| | create proceduer | create program |
| | * /
/ * |
| |\ object | Table [table] | View [view] | Program [procedure] |
| | permissions\ |
| | * |
| | insert | Y | Y |
| | * |
| | alter | Y | |
| | * |
| | update | Y | Y |
| | * |
| | delete | Y | Y |
| | * |
| | select | Y | Y |
| | * |
| | index | Y | |
| | * |
| | execute | Y | |
| * /
-- create users
CREATE USER u01 IDENTIFIED BY p01
-- create users and develop default tablespaces
CREATE USER U01 IDENTIFIED BY P01 DEFAULT TABLESPACE QUOTA 2m ON ts01;-quotas available in the quota tablespace
-- change the user's password
ALTER USER u01 IDENTIFIED BY p001
-- modify user tablespace quotas
ALTER USER u01 QUOTA 20M ON ts01
ALTER USER U01 QUOTA UNLIMITED ON ts01;-users have no quota restrictions on tablespaces
-- reclaim unlimited tablespace permissions
REVOKE UNLIMITED TABLESPACE FROM ts01
-- Delete user
DROP USER u01
-- switch users who connect to the database
CONNECT u01/p01
Conn u01/p01
-- authorize users to connect to the database
GRANT CREATE SESSION TO u01
-- authorize the user to create a sequence (sequence)
GRANT CREATE sequence TO u01
-- authorize users to create tables
GRANT CREATE TABLE TO u01
-- authorize the user to look up the table
Grant select on ts01.user1 to wangkai
-- authorize the user to modify the table
Grant alter on user1 to wangkai
-- authorize the user to delete the table (without this permission)
Grant drop on user1 to wangkai; (error)
-- authorize users to have all permissions on a table
Grant all on user1 to wangkai
-- authorize all users to have all permissions on a table
Grant all on user1 to public
-- revoke the user's right to create tables
REVOKE CREATE TABLE FROM u01
-- revoke the user's right to look up the table
REVOKE select on user1 FROM u01
-- revoke the permission of the user to modify
REVOKE select on user1 FROM u01
-- take back all user permissions on a table
Revoke all on user1 from wangkai
-- revoke all permissions of all users on a table
Revoke all on user1 to public
-- create a role
CREATE ROLE r01
-- role authorization
GRANT CREATE SESSION, CREATE TABLE TO r01
-- revoke role permissions
REVOKE CREATE TABLE FROM r01
-- View role permissions
SELECT ROLE,PRIVILEGE FROM role_sys_privs WHERE ROLE='R01'
-- roles are assigned to users
GRANT r01 TO u01
-- View the current user role
SELECT * FROM user_role_privs
-- Delete roles
DROP ROLE r01
