Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to manage the Oracle data sheet, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article.

1. Introduction of SQLDeveloper tools Oracle SQLDeveloper is a free graphical development tool officially produced by Oracle. Compared with SQL * Plus, the graphical interface is easy to operate, there is no need to remember a large number of commands, and the output result is beautiful. Its basic functions are formatting output of results, editor auto-prompt, code optimization, displaying SQL execution plan, monitoring session, writing and debugging stored procedures, and so on.

Installation and use of SQLDeveloper tools:

The SQLDeveloper tool can be downloaded from the official website of oracle: (I downloaded win64, download it myself according to the situation, decompress it to the hard drive and use it.)

Run the sqldeveloper.exe executable file after decompression, create a new connection, and connect after adding scott users:

It should be noted that SID refers to the unique identifier of the database, an initial ID,SID automatically given by the system when establishing a database is mainly used for some DBA operations and interaction with the operating system, from the operating system point of view to access the instance name, must be through the ORACLE_SID, and it also exists in the registry. How to view SID, on the WINDOWS platform, you can go to the registry, check HKEY_LOCAL_MACHINE\ SOFTWARE\ ORACLE, there is an ORACLE_SID. )

2. SQL of SQL and Oracle

SQL structured query language (Structured Query Language), abbreviated as SQL. Is a special purpose programming language, is a database query and programming language, used to access data and query, update and manage relational database systems.

Oracle's SQL:DDL (Data Definition language) data definition language, DML (Data Manipulation language) data operation language, DCL (Data Control Language) data control language, TCL (Transaction Control Language) transaction control language. DDL statement states that create table creates database tables create index creates indexes of database tables drop table deletes database tables drop deletes indexes of database tables truncate deletes all rows in the table alter table changes table structure, adds, modifies Delete column alter table add constraint add constraints DML statement to existing table description INSERT add data to database UPDATE modify data in database DELETE delete data in database SELECT select (query) data DCL statement indicates that grant grants permissions or roles to users or other roles (such as granting access rights) revoke withdraws permissions (such as revoking access rights) LOCK pairs from a user or database role Lock TCL statement according to a specific part of the library indicates that COMMIT commit transaction ROLLBACK transaction fallback SAVEPOINT setting SavePoint 3, The operation of database table: create table a, create the syntax structure of table

Through CREATE TABLE statements, such as: CREATE TABLE table_name (column_name datatype...)

B, data type (datatype)

When creating a data table, it involves the structure of the data table, that is, determining the data type of each column in the data table, whether it is numeric, character, date, image and other types. Because only when the structure of the data table is designed, the system will open up the corresponding space in the disk, and the user will be able to fill in the data into the table. )

The commonly used data types in Oracle are: character type, numeric type, date-time type, LOB type. The character type indicates CHAR1, which represents a fixed-length string; 2. The column length can be 1 to 2000 bytes. VARCHAR21, a string representing a variable length; 2. The maximum length is 4000 bytes. Numeric type description NUMBER declaration syntax is: NUMBER [(p [, s])] (p represents precision, s represents the number of decimal places, can store integer, floating point and other numeric types, with a maximum precision of 38 digits). For example: number (5prime0) can store up to five digits of integers; number (5prime2) can store up to 999.99 floating-point numbers. Date type description date type date time type stores date and time values, including year, month, day, hour, minute, second. The main date and time types are: DATE. LOB type description CLOBCLOB is Character LOB (character LOB): it can store a large amount of character data BLOBBLOB, namely Binary LOB (binary LOB): it can store larger binary objects, such as push, video clips and sound files, c, create student information table structure, student information table structure analysis: student information table

Table name Student Information Table (student) field name-student number (SID) number (8) field name-name (name) varchar2 (20) field name-gender (sex) char (2) field name-date of birth (birthday) date field name-home address (address) varchar2 (50)

Create a SQL statement for the student information table:

SQL > conn scott/02000059 is connected. SQL > create table student (sid number (8) 2), name varchar2 (20), sex char (2), birthday date, addresss varchar2 (50); the table has been created. SQL > 4, table operations of Oracle: constraints a, what are constraints? And constraints in Oracle

Constraint is a method provided by Oracle to automatically maintain the integrity of the database, which ensures the integrity of the data by restricting the data in the field, the data in the record and the data between the table.

The basic syntax format for integrity constraints is: [CONSTRAINT constraint_name (constraint name)] (note: when the constraint does not specify a name, the system will be given a name. )

Constraints in Oracle: primary key constraint (primary key constraint), uniqueness constraint (unique constraint), default constraint (default constraint), non-null constraint (not null constraint), check constraint (check constraint), external key constraint (foreign key constraint).

B, table operation of Oracle: primary key constraint

The PRIMARY KEY constraint is used to define the primary key of the base table. It is the unique identifier that determines each record in the table. Its value cannot be NULL and cannot be repeated to ensure the integrity of the entity. There can be only one primary key in a table, but it can be made up of multiple columns. Such as: primary key (student number, subject number)

Add a primary key constraint when creating a table:

SQL > conn scott/02000059 is connected. SQL > create table student (sid number (8pm 2), name varchar2 (20), sex char (2), birthday date, address varchar2 (50), constraint sid_pk primary key (sid)); the table has been created.

Add a primary key constraint when modifying the table:

SQL > create table student (sid number (8) 2), name varchar2 (20), sex char (2), birthday date, address varchar2 (50); the table has been created. SQL > alter table student add constraint sid_pk primary key (sid); the table has changed. C, table operation of Oracle: non-null constraint

Non-null constraint (not null): used to ensure that the column cannot be NULL, if a NOT NULL constraint is defined on the column, then data must be provided for the column when inserting data; when updating column data, its value cannot be set to NULL. (non-empty (not null) constraints are column level constraints)

Additional note: what is the difference in definition between column-level constraints and table-level constraints?

Column-level constraint syntax format: column [CONSTRAINT constraint_name] constraint_type, indicating that column-level constraints must follow the definition of the column.

Table-level constraints: column,... [CONSTRAINT constraint_name] constraint_type (column,...), note: table-level constraints are not defined with columns, but are defined separately.

Add non-empty (not null) constraints when creating tables: (custom constraint names for later maintenance)

SQL > conn scott/02000059 is connected. SQL > create table student (sid number (8) not null, name varchar2 (20) not null, sex char (2) constraint2 nn_sex not null,birthday date, address date, constraint sid_pk primary key (sid); the table has been created. Add non-empty constraints when modifying tables: add non-empty (not null) constraints using the modify statement: ALTER TABLE table_name MODIFY (column datatype NOT NULL)

Syntax format for deleting non-null (not null) constraints: ALTER TABLE table_name MODIFY column_name datatpe NULL

Example on the command line:

SQL > alter table student modify (name varchar2 (20) NULL); the table has changed. SQL >

Additional note: there are two ways to delete a constraint: activate or invalidate the constraint, and delete the constraint completely

Invalidate or activate the constraint: ALTER TABLE table_name DISABLE | ENABLE CONSTRAINT constraint_name;; removes the constraint completely: ALTER TABLE table_name DROP CONSTRAINT constraint_name;.

Format for deleting primary key constraints: ALTER TABLE table_name DROP PRIMARY KEY

Example on the command line: (invalidate and delete primary key constraints)

The SQL > alter table student enable constraint sid_pk; table has changed. The SQL > alter table student drop constraint sid_pk; table has changed. Table operations of SQL > d and Oracle: unique constraints

Unique constraint: the uniqueness constraint is used to specify that the combined values of one or more columns are unique to prevent duplicate values from being entered in the column.

Considerations for uniqueness constraints: columns that use uniqueness constraints are allowed to be null; multiple uniqueness constraints can be allowed in a table; uniqueness constraints can be defined on multiple columns. (when a uniqueness constraint is defined in one column, it can be a table-level constraint or a column-level constraint; when a uniqueness constraint is defined in multiple columns, it must be a table-level constraint. )

An example of adding unique constraints when creating a table:

C:\ Users\ Administrator > sqlplus / nologSQL*Plus: Release 11.2.0.1.0 Production on Tuesday November 28 21:40:44 2017Copyright (c) 1982, 2010, Oracle. All rights reserved.SQL > conn scott/02000059 is connected. SQL > create table student (sid number (8) 0), name varchar2 (20), sex char (2), birthday date, address varchar2 (50), email varchar2 (50) unique, cardid varchar2 (18), constraint uk_cardid unique (cardid); the table has been created. SQL >

Add a uniqueness constraint when modifying the table:

SQL > create table student (sid number (8) 0), name varchar2 (20), sex char (2), birthday date, address varchar2 (50), email varchar2 (50) unique, cardid varchar2 (18); the table has been created. SQL > alter table student add constraint uk_cardid unique (cardid); the table has changed.

Delete a uniqueness constraint: there are two ways to disable the constraint or delete it completely

The SQL > alter table student disable constraint uk_cardid; table has changed. The SQL > alter table student drop constraint uk_cardid; table has changed. Table operations of SQL > e and Oracle: check constraints check constraints set check conditions on the values in the input column or the whole table to limit the input values and ensure the integrity of database data.

An example of adding a check constraint when creating a table: (you can use table-level constraints or column-level constraints)

SQL > conn scott/02000059 is connected. SQL > create table student (sid number (810), name varchar2 (20), sex char (2) check (sex=' male'or sex=' female'), birthday date, address varchar2 (50); the table has been created. SQL > SQL > conn scott/02000059 is connected. SQL > create table student (sid number (8) 0), name varchar2 (20), sex char (2), birthday date, address varchar2 (50), constraint ck_sex check (sex=' male'or sex=' female'); the table has been created. SQL >

Add a check constraint when you modify the table:

SQL > alter table student add constraint ck_sex check (sex=' male'or sex=' female'); the table has been changed.

Delete check constraint: there are two ways to disable the constraint or delete it completely

The SQL > alter table student disable constraint ck_sex; table has changed. The SQL > alter table student drop constraint ck_sex; table has changed. Table operations of f and Oracle: foreign key constraints

A foreign key (FOREIGN KEY) is one or more columns used to establish and strengthen a link between two table data. A foreign key constraint is the only constraint that involves the relationship between two tables.

It should be noted that the main table is created first and then after the table. (delete slave table first, then delete master table)

Set the syntax format for foreign key constraints:

Column-level constraints: CRAETE TABLE slave table (column_name datatype REFERENCES master table (column_name) [ON DELETE CASCADE],....); (ON DELETE CASCADE represents cascading deletion)

Table-level constraints: CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES master table (column_name) [ON DELETE CASCADE]

Example of setting column-level foreign key constraints when creating a table:

SQL > create table department (depid varchar2 (10) primary key, depname varchar2 (30)); table created. SQL > create table student (sid number (8) 0), name varchar2 (20), sex char (2), birthday date, address varchar2 (50), depid varchar2 (10) references department (depid); the table has been created.

Example of setting table-level foreign key constraints when creating a table:

C:\ Users\ Administrator > sqlplus / nologSQL*Plus: Release 11.2.0.1.0 Production on Wednesday November 29 16:53:36 2017Copyright (c) 1982, 2010, Oracle. All rights reserved.SQL > conn scott/02000059 is connected. SQL > create table department (depid varchar2 (10) primary key, depname varchar2 (30)); table created. SQL > create table student (sid number (810), name varchar2 (20), sex char (2), birthday date, address varchar2 (50), depid varchar2 (10), constraint fk_depid foreign key (depid) references department (depid) on delete cascade); the table has been created. SQL > Note for foreign key constraints: when setting foreign key constraints, the fields in the master table must be primary key columns (or unique columns); the corresponding fields in the master and slave tables must be of the same data type; and the values of the foreign key fields in the slave table must come from the values of the corresponding fields in the master table, or null values.

Add a foreign key constraint when modifying the table: alter table student add constraint fk_depid foreign key (depid) references department (depid) on delete cascade

SQL > alter table student add constraint fk_depid foreign key (depid) references department (depid) on delete cascade; table has changed. SQL >

Delete foreign key constraints: there are two ways to disable constraints and delete them completely

The SQL > alter table student disable constraint fk_dep table has changed. The SQL > alter table student drop constraint fk_depid; table has changed. SQL > 5. Table operations of Oracle: modify and delete table a, modify table

Add column syntax structure: ALTER TABLE table name ADD added column name data type

Example: ALTER TABLE student ADD tel VARCHAR2 (11)

SQL > ALTER TABLE student ADD tel VARCHAR2 (11); the table has changed. SQL >

Modify column syntax structure: ALTER TABLE table name MODIFY column name new data type

For example: ALTER TABLE student MODIFY tel NUMBER (11. 0)

SQL > ALTER TABLE student MODIFY tel NUMBER (11. 0); the table has changed. SQL >

Delete column syntax structure: ALTER TABLE table name DROP COLUMN column name

For example: ALTER TABLE student DROP COLUMN tel

The SQL > ALTER TABLE student DROP COLUMN tel; table has changed. SQL >

Modify the syntax structure of the column name: ALTER TABLE table name RENAME COLUMN column name TO new column name

For example: ALTER TABLE student RENAME COLUMN sex TO gender

The SQL > ALTER TABLE student RENAME COLUMN sex TO gender; table has changed. SQL >

Modify the syntax structure of the table name: RENAME table name TO new table name

For example: RENAME student TO studnetifo

The SQL > RENAME student TO studnetifo; table has been renamed. SQL > b, delete table

There are two ways to delete a table: TRUNCATE TABLE table name: used to delete all data in the table, not delete the table, this deletion method is faster than the DELETE method to delete data, also known as truncated table; DROP TABLE table name: delete table structure.

Example:

The SQL > truncate table emp_bak; table is truncated. The SQL > drop table emp_bak; table has been deleted. SQL > 6. Table operations of Oracel: add, delete, modify and query

Syntax structure for adding information: INSERT INTO table name [(column 1, column 2,...,)] VALUES (value 1, value 2,..., value N)

Syntax format of query information: SELECT * | column [,...] From table name

Modify the syntax format of the information: UPDATE table SET column = value [, column = value,...] [WHERE condition]

Syntax format of delete message: DELETE FROM table [WHERE condition]

7. Oracle table operation: transaction

What is a transaction: a transaction can be thought of as a unit of several operations on the database that are either completed or cancelled to ensure that the data meets the consistency requirements.

The composition of a transaction: one or more DML, a DLL, or a DCL statement. (the DML statement requires either COMMIT to commit the transaction or ROLLBACK to roll back the transaction, while DDL and DCL commit the transaction automatically. )

Why transactions are used: the reason for using transactions is to ensure the security and validity of the data. (for example, when performing a transaction operation (DML statement), Oracle adds a table lock on the active table to prevent other users from changing the table structure, and a row lock on the affected row to prevent other transactions from performing DML operations on the corresponding row. )

Control commands for the transaction:

Commit transaction (COMMIT): you can commit a transaction through the COMMIT statement. When the COMMIT statement is executed, the transaction changes are confirmed, the transaction ends, the SavePoint is deleted, and the lock is released. After using the commit statement to end the transaction, other sessions can see the new data after the transaction has changed.

Rollback transaction (ROLLBACK): ROLLBACK can only undo uncommitted data, but cannot undo data that has already been commit, because commit has been persisted to the database since then.

Save point (SAVEPOINT): a point in a transaction that cancels part of the transaction. When the transaction ends, all save points defined by the transaction are automatically deleted. When ROLLBACK is executed, you can fall back to the specified point by specifying a SavePoint. (set SavePoint: SAVEPOINT Aten, roll back some transactions: ROLLBACK TO Aten, rollback all transactions: ROLLBACK;)

8. Oracle data Dictionary

What is a data dictionary: a place where Oracle stores information about a database and its purpose is to describe data. A database data dictionary is a set of table and view structures. The tables in the data dictionary cannot be accessed directly, but the views in the data dictionary can be accessed.

The function of the data dictionary: through the data dictionary, we can understand the internal information of the database. When users encounter difficulties in operating the data in the database, they can access the data dictionary to view detailed information.

Data dictionaries commonly used in Oracle: data dictionaries commonly used in Oracle are divided into three categories, starting with three prefixes: user*, all, and dba_.

User_*: this view stores information about the objects owned by the current user. (that is, all objects in this user mode)

All*: this view stores information about objects that the current user can access. (compared to user, all_ does not need to own the object, just needs to have access to the object. )

Dba_*: this view stores information about all objects in the database. (provided that the current user has access to these databases and generally must have administrator privileges. )

A. The name of the data dictionary commonly used in Oracle describes user_users 's information about the user user_tablespaces about the table space user_tables about the database table user_views about the view user_sequences about the user sequence information user_constraints about the user table constraint information user_triggers about the user trigger information user_source about the user stored procedure information b, Apply the data dictionary to view the corresponding database information (example)

View the user information under the current user: SELECT * FROM user_users

View the user information of the current user under scott:

View basic information about all users that the user has access to: SELECT * FROM all_users

View basic information for all users under scott:

View the user information of all users in the database: SELECT * FROM dba_users

Users cannot view user information for all users using scott:

Error occurred in line 1 of SQL > SELECT * FROM dba_users;SELECT * FROM dba_ users: ORA-00942: table or view does not exist SQL >

Use sys users to view user information for all users:

Thank you for reading this article carefully. I hope the article "how to manage Oracle datasheets" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.