Getting Started with Oracle database SQL

1.oracle database download, installation, uninstallation

Oracle Downloads:

Oracle Download link Address: http://www.oracle.com/technetwork/cn/database/enterprise-edition/downloads/index.html

such as: Choose to accept the agreement, choose their own corresponding system, the number of files can be;

The file download good path is as follows:

Installation: Click Setup.exe file to prepare for installation:

A few steps: The first configuration of the security installation, just to learn, you can remove the previous tick, the rest of the direct click Next, note that: Select the installation path,

Due to the large Oracle database, it is best not to place on the C drive, placed in a larger disk space, to avoid slowing down the system at startup, and finally when the password is set: the password is

Your login password, the rest of the direct next.

Verify that the installation is successful:

After installation can be found in the bottom left corner of the desktop, I am using a Windows 10 system, different system display differs: Inside can find an icon as shown (the current version of Oracle installed):

Click on the SQL Plus tool

Enter User name: System

Enter the password that you entered at the time of installation, which appears:

A link to the text that corresponds to the version of the Oracle database that you downloaded the installation appears, stating that you have successfully installed

Uninstallation of Oracle:

First locate the Deinstall folder under install Oracle Files directory:

Find the file under the Deinstall.bat this is a batch deletion of the execution file, click into, follow the prompts to operate, will generally

Uninstall the software, the registry and so on to remove the clean, only to completely uninstall the software

2. User and table space

2.1 Login with System user

The system user sorts from the permission size: sys, system, Sysman, Scott; the default password for Scott is Tiger

Use the System user logon statement:

Connect [Username/password] [@server] [as Sysdba|sysoper]

Example: Connect System/root @orcl as Sysdba

The ARCL above is to create the database default database name when you install the Oracle database, SYSDBA is logged in as an administrator, Sysoper is logged in as a normal operator province

Such as:

For SYS must be followed by the as what, meaning is in what identity to log in, SYS must be SYSDBA Administrator identity

You can also: view the current logged-on user through the Show Users statement

You can see that the current logged-on user is sys, and theOracle statement is case-insensitive, only if the specified table name must be uppercase

2.2.Oracle User Good table space View login user

Dba-users: User dictionary, to view the structure of the user dictionary through Desc;

By select Username,acount_status from Dba_users, you can view the status of the application username and account in the corresponding dba_users;

Can see Sys/system, is an open state, most of the others are locked locked state

If you want to use SQL statements for these locked accounts: Alter user username account unlock; So you can use it to log in;

2.3: Table space creation, setting, deletion, adding or subtracting a file statement

Alter user username default|temporary tablespace tablespace_name;

Temporary tablespace: It is not persisted, only used in stored procedures or other processes, and is recycled when the process is finished.

Creating tablespace: Create [temporary] tablespace tablespace_name tempfile|datafile ' xx.dbf ' size xx;

View data dictionary: desc dba_data_files;

View the file path of the specific tablespace in the data dictionary:

Modify the state of a tablespace: Online status, offline status

SQL statement: Alter TABLESPACE Tablespace_name Online|offline

To modify the state of a tablespace: set read-only or read-write status

Alter tablespace Tablespace_name read Only|read write;

To add a data file to a tablespace:

Alter tablespace tablespace_name add datafile ' xx.dbf ' size xx;

This is the resulting file after the original table space is created with the SQL statement:

To delete a table space:

SQL statement: Drop tablespace tablespace_name [including contents];

The path to where the table space is located, as can be seen below, this just deletes its data structure, but the inside

File is not deleted.

3. Managing tables

Data type of 3.1Oracle

(1), character type

char (n), nchar (n), where the N maximum value in char is the maximum value of 2000,nchar is 1000, they are the character type of the immutable length

For example: Set the name of char (10), if the actual only 2 characters, then the eight characters in the back, memory will be filled, so that in

It's a waste of memory to some extent, so there's a variable-length character type.

VARCHAR2 (n), nvarchar (n)

(2), numerical type

Number (M,n), m indicates that several digits can be expressed, n means that it can be followed by several decimals;

Example: Number (5,2): 100.00, if written in 10000.00, use it to represent the words will appear ######, can not display the results.

(3), date type

Date

more precise timestamp.

(4), other types

BLOBs and CLOB represent types of large numbers

1, blobs are all called binary Large objects (binary Large object). It is used to store large binary objects in the database. The maximum size that can be stored is 4G bytes
2. Clob are all called character large objects (Character Large object). It is similar to the Long data type except that CLOB is used to store large single-byte character blocks in the database and does not support character sets of varying widths.
The maximum size that can be stored is 4G bytes, and CLOB can store single-byte character data, and blobs can store unstructured binary data

Operating table structure in 3.2Oracle

Add Field

ALTER TABLE table_name ADD COLUMN_NAME datatype;

modifying field data types

ALTER TABLE table_name MODIFY COLUMN_NAME Datetype;

Delete a field

ALTER TABLE table_name DROP COLUMN column_name;

Rename the existing field name

ALTER TABLE table_name Rename column column_name to New_column_name;

Modify Table Name

Rename table_name to New_table_name;

Delete a table

TRUNCATE TABLE table_name

DROP TABLE table_name

Truncate is the meaning of truncation, the deletion of the contents of the table, relative efficiency is very high

Drop is to remove the table structure and table contents together

4. Data in the action table

Add data

INSERT INTO table_name (column_name,...) VALUES (value1,....);

Delete a table

Delete from table_name where conditions;

Modify Table

UPDATE table_name SET column_name = ' xx ',...

Copying tables

Copy at creation time

Premise: The structure and data types in the table need to be consistent

CREATE TABLE table_name

As

Select Column1,... | * FROM Table_old;

Copy on Add

INSERT INTO table_name (Column1,...)

Select Column1,... | * FROM Table_old;

5. Constraints

V constraints are included in Oracle altogether

(1), non-null constraints

(2), UNIQUE constraint

(3), PRIMARY KEY constraint

(4), FOREIGN KEY constraint

(5), CHECK constraints

The SQL statement is mainly done in three ways:

One, adding constraints when creating a table

Non-empty constraint: NOT NULL

PRIMARY KEY constraint: primary key

FOREIGN KEY constraint: References table (column_name), the data type must be consistent between the primary table and the table

Unique key: Unique

Check constraint: check (expression), expression: constraint for a field

Ii. Adding a constraint when modifying a table

Add non-null: ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME datatype NOT null;

Add PRIMARY KEY constraint: ALTER TABLE TABLE_NAME ADD CONSTRAINT constraint_name primary key (column_name);

Add FOREIGN KEY constraint: ALTER TABLE table_name ADD constraint constraint_name FORENGN key (column name) references table (column_name);

Add unique KEY constraint: ALTER TABLE TABLE_NAME ADD constraint constraint_name unique (column_name);

Add CHECK constraint: ALTER TABLE table_name ADD constraint constraint_name check (expression);

Iii. removing constraints from a table

Delete non-null: ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME datatype null;

To remove additional constraints: Disable|enable constraint constraint_name

Drop constraint constraint_name;

Append: To query which constraints are in a table

Select Constraint_name,constraint_type,status from user_constraints where table_name= ';

6. Query statements

6.1 Basic Query statements

SELECT DISTINCT Column_name1,... | * FROM table_name where conditions;

Distinct is to remove duplicates

Formatting in 1.sql*plus:

Column column_name HEADING new_name; Column can also be abbreviated as Col

First write this sentence in writing query statements, the last query is the definition of their own new_name; the feeling is the as alias in MySQL

2.column column_name format DataFormat;

The format is directly defined, and the number format represents a bit with 9.

3. Clearly set the format

Column column_name clear;

6.2 Fuzzy Query

The fuzzy query in Oracle and MySQL are also consistent with the like wildcard character (_,%); _ can represent only one character, and% can represent 0 to more arbitrary characters

6.3 Sort

Order by DESC/ASC and MySQL usage consistent

6.4 Case ... when to use

Case COLUMN_NAME If value1 then result,...

else result end;

This is the same as the switch case in Java, and returns different results depending on the value.

The use of the 6.5 decode function

Decode (COLUMN_NAME,VALUE1,RESULT1,...., defaultvalue);

This is case ... when it's almost

Getting Started with Oracle database SQL