Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.