Oracle 11G basic learning --- (2) SQL language basics

 

Where can I start learning about Oracle? Here, I will take you to the door of Oracle. One chapter a day, seven days of entry is not a dream.

1. SQL language Overview

1.1 SQL language classification

Data Definition Language (DDL)

Used to CREATE, modify, and delete database objects, such as create table, alter table, and drop table. DDL statements automatically commit transactions.

Data Manipulation Language (DML)

Used to manipulate databases, including INSERT, UPDATE, DELETE, and SELECT

Data Control Language (DCL)

These commands are used to GRANT and REVOKE permissions, including GRANT and REVOKE commands. The DCL statement automatically submits the transaction.

Transaction Control Language (TCL)

It is used to maintain data consistency, including three statements: COMMIT (COMMIT transaction), ROLLBACK (ROLLBACK transaction), and SAVEPOINT (set save point ).

1.2 SQL statement writing rules

SQL keywords are case-insensitive. They can be either case-insensitive or case-insensitive.

The Object Name and column name are case insensitive.

The character value and date value are case sensitive.

In SQL * Plus, each SQL statement must end with a semicolon. If the statement is long, You can distribute the statement text to multiple rows and end with a semicolon.

 

<P> 2. Use DDL statements

2.1 common Oracle 11g Data Types

Character Type

CHAR type

CHAR indicates a fixed-length string. If the length is insufficient, spaces are used to supplement the string. Up to 2000 bytes can be stored.

The CHAR type is distinguished between Chinese and English. Chinese occupies two bytes of CHAR, while English occupies only one byte.

VARCHAR2 type

VARCHAR2 indicates a variable-length string, which can store up to 4000 bytes.

Specify the data size when defining the data type. Compared with CHAR, VARCHAR2 can save disk space.

Value Type

The NUMBER type can store positive, negative, zero, fixed point, and 38-bit floating point numbers.

NUMBER (M, N ). M indicates the precision and the total number of digits. N indicates the number of digits on the right of the decimal point.

Date type

DATE type

The DATE data type is used to store the DATE and time data in the table. The value range is from January 1, January 1, 4712 BC to January 1, December 31, 9999 AD.

The length of the DATE type is 7 to 7 bytes, indicating century, year, month, day, hour, minute, and second, respectively.

TIMESTAMP type

The TIMESTAMP data type is used to store the hour, minute, and second values of the year, month, day, and time of a date.

The second value is accurate to the last six digits of the decimal point. The data type also contains time zone information.

2.2 create table command

Create table is used to CREATE a TABLE. When creating a table, the primary key, foreign key, unique constraint, and Check constraint of the table are often created.

2.3 alter table command

Add a new column to the created table

Alter table tableName add columnName dataType

Modify the data type and length of a specified column in a table

Alter table tableName modify column columnName dataType

Delete the specified column in the table

Alter table tableName drop columnName </p> <p> 2.4 truncate table command

The runcate table command is used to delete all contents of a TABLE and release the storage space used by the TABLE, but does not delete the TABLE structure.

Syntax

Truncate table tableName

The truncate table command can quickly delete TABLE records and free up space. It is fast and efficient without transaction processing, but cannot roll back transactions.

The DELETE command can be used to cancel deletion after deletion.

2.5 drop table command

The drop table command is used to delete tables and all data from the database.

3. Use DML statements

3.2 UPDATE command

3.3 DELETE command

The DELETE command is used to DELETE table content.

4. Use the SELECT statement

4.1 overview of the Sample Database

During the installation of Oracle 11g database creation process, if the "Create Database with sample solution" check box is selected on the "sample solution" tab in the "specify database configuration details" window, the new user sample database will be installed.

Oracle's example scheme is based on a virtual company that sells material through various channels

The HR solution is a basic relational database solution for introducing the simplest and most basic topics.

The solution contains seven tables: employees, departments, locations, countries, regions, and jobs) job_history)

4.2 overview of the HR sample solution

By default, the accounts of all the sample schemes are locked, and their passwords are expired and cannot be connected to the database.

Unlock HR users

4.3 table structure in the HR Sample Scheme

4.4 subquery

4.5

Use subqueries in DDL and DML statements

Use subquery In the create table statement

Use subquery in INSERT statements

Use subquery In the DELETE statement

Use the subquery in the UPDATE statement

5. Use DCL Data Control Language

5.1 GRANT

If you create a table in your own mode, the user is the owner of the table and has all operation permissions on the table.

Unless you want to share the table with other users, you do not need to grant any operation permissions to the table to other users.

You can use the GRANT command to GRANT some operation permissions to the table to other users. Such permissions are called object permissions.

Syntax

GRANT privileges ON object-name TO userName

5.2 REVOKE

The REVOKE command is used to REVOKE the granted permissions on user objects.

Syntax

REVOKE privileges ON object-name FROM userName

6. Use the TCL transaction control language

Start and end of a transaction

Start transaction

Connect to the database and start executing a DML (UPDATE, INSERT, DELETE) Statement

The previous transaction ends or executes a statement that will automatically commit the transaction.

Transaction ends

Execute a COMMIT (transaction COMMIT) or ROLBACK (transaction rollback) Statement

Execute a statement that will automatically submit the transaction

An error occurred while executing a DML statement.

Automatically submit transactions

Execute a DDL Statement (such as create table and alter table)

Execute a DCL Statement (such as GRANT and REVOKE)

Disconnect from the database. For example, execute a DISCONNECT command in SQL * PLUS or execute a CONNECT command in the same SQL * PLUS to start a session, or execute the EXIT command to exit SQL * PLUS and so on </p> <p> 6.2 use the COMMIT command to submit the transaction

Session A inserts A record into the emp table. Before submission, another session B cannot view the new record.

Only after session A commits A transaction can session B view the newly added records. This is actually the isolation of the transaction </p> <p> 6.3 use ROLLBACK to roll back the transaction

6.4 roll back some transactions

7. Use basic functions

Dual table

A dual table is a virtual table in the Oracle database. It has one row and one column, and the owner is a SYS user. However, it can be used by all users in the database.

You cannot insert data into the table, but you can use the table to select system variables or evaluate the value of an expression.

Single Row Function

Date Functions

ADD_MONTHS function: returns the date value after the specified date plus the specified number of months.

MONTHS_BETWEEN function: Used to return the number of months between two dates

The LAST_DAY function is used to return the last day of the month corresponding to the specified date.

NEXT_DAY function: returns the date of the specified day of the next week.

EXTRACT function: used to EXTRACT specific parts of a date </p> <p> character Function

Function Name function example output result

LOWER (c) lowercase conversion select lower ('scce ')

From dual; scce

UPPER (c) capital conversion select upper ('scce ')

From dual; SCCE

LTRIM (c1 [, c2]) removes any characters in c2 on the left of c1. It ends when it encounters a character that is not in c2 and returns the remaining string. If c2 is omitted, the default space is select ltrim ('adminscce ', 'ad') from dual; minscce.

RTRIM (c1 [, c2]) removes any character from c2 on the Right of c1. It ends when it encounters a character that is not in c2 and returns the remaining string. If c2 is omitted, the default space is select rtrim ('adminscce ', 'scce') from dual; admin </p> <p> function name example output result

REPLACE (c1, c2 [, c3]) replaces c2 in c1 with c3 and returns the remaining string. C3 is null by default. If c3 is null, all characters where c2 appears are deleted. If c2 is null, c1 is returned. If c1 is null, returns null select replace ('a * B * ',' * ') from dual; AB

INSTR (c1, c2) searches for the first occurrence location of c2 in c1. If no result is found, return zero select instr ('abcbc ', 'bc') from dual; 2

SUBSTR (c, m [, n]) returns the substring of c, where m is the starting position of the substring and n is the length of the substring. If n is omitted, select Aubstr ('scce ', 2, 2) from dual; cc

CONCAT (c1, c2) connection string select concat ('scc ', 'E') from dual; scce

LENGTH (c) returns the string length select LENGTH ('scce ') from dual; 4 </p> <p> mathematical function

Function Name function example result

ABS returns the absolute value of select abs (-1) from dual; 1

CEIL rounded up select ceil (1.33) from dual; 2

FLOOR rounded down select floor (1.33) from dual; 1

POWER power POWER (2, 3) from dual; 8

MOD returns the remainder select mod (10, 3) from dual; 1

ROUND rounded to select round (12.126, 2) from dual; 12.13

TRUNC truncation select trunc (12.126, 2) from dual; </p> <p> Conversion Function

TO_CHAR: used to convert a date or number to a value of the VARCHAR2 data type in the specified format.

Syntax

TO_CHAR (d | n [, fmt])

D indicates the date, n indicates the number, and fmt indicates the date or number format.

Conversion functions

TO_NUMBER: converts a string containing numbers to the NUMBER data type to perform arithmetic operations on the data type.

Syntax

TO_NUMBER (char)

Generally, conversion is not required because Oracle can implicitly convert numeric strings.

Other functions

NVL: Used to replace a null value with a specified value.

Syntax

NVL (expr1, expr2)

If expr1 is not NULL, expr1 is returned; otherwise, expr2 is returned.

Add a bonus field bonus to the employees table in the HR solution. This field may be blank. Pay-as-you-go is required to calculate the actual salary of an employee. Pay-as-you-go is equal to basic salary + bonus.

Other functions

NVL2: similar to NVL, but the parameters are different.

Syntax

NVL2 (expr1, expr2, expr3)

If expr1 is not NULL, the value of expr2 is returned; otherwise, the value of expr3 is returned.

Other functions

DECODE: the DECODE function is equivalent to a Condition Statement (IF). It compares the input value with the parameter list in the function and returns a corresponding value based on the input value.

Syntax

DECODE (input_value, value, result [, value, result…] [, Default_result]);

Input_value indicates the value to be processed. The DECODE function compares the value with a series of sequence dolls to determine the final returned result.

Value indicates the value of an ordinal pair. If the input value matches the value, the corresponding result is returned.

Result indicates the result value of an ordinal pair.

Default_result indicates that the default value returned by the function fails to match any sequence even.

Increase the salary for the employees in the employees table based on the table requirements, and confirm the UPDATE results. </p> <p> conclusion

VARCHAR2 is a common variable-length character type in Oracle.

The CLOB data type is used to store variable-length character data, and the BLOB data type is used to store large binary objects.

Subqueries in Oracle can be divided into single-row subqueries, multi-row subqueries, multi-column subqueries, and related subqueries.

The GRANT command is used to GRANT object permissions, and the REVOKE command is used to REVOKE permissions.

The COMMIT command is used to COMMIT transactions, and the ROLLBACK command is used to roll back transactions.

The TO_CHAR function is used to convert a date or number to a value of the VARCHAR2 data type in the specified format.

The DECODE function is equivalent to a Condition Statement (IF). It compares the input value with the parameter list in the function and returns a corresponding value based on the input value </p> <p>

A tablespace is the largest logical unit in a database. An Oracle Database contains at least one tablespace, that is, the SYSTEM tablespace named SYSTEM. </P> <p> Each tablespace is composed of one or more data files. A data file can only be associated with one tablespace. </P> <p> the tablespace size is equal to the total size of all data files that constitute the tablespace. </P> <p> the syntax for creating a tablespace is:

Create tablespace tablespacename

DATAFILE 'filename' [SIZE integer [K | M]

[AUTOEXTEND [OFF | ON]; </p> <p> to connect to the Oracle database, you need to create a user account.

Each user has a default tablespace and a temporary tablespace.

The create user command is used to CREATE a new USER.

The syntax of the create user command is:

CREATE USER MARTIN

Identified by martinpwd

DEFAULT TABLESPACE USERS

Temporary tablespace temp;

Create a user named martin whose password is

The martinpwd </p> <p> GRANT command can be used to assign permissions or roles to users.

Grant connect to martin; the CONNECT role allows users to connect to the database and create database objects.

Grant resource to martin; the RESOURCE role allows users TO use the storage space in the database </p> <p> If the Scott user's password is changed when the user is on the machine, what should I do? </P> <p> Solution

1) log on to the Oracle server anonymously.

Sqlplus/nolog

Conn/as sysdba

2) Change scott's password

Alter user scott identified by tiger;

</P>

From happy pig's column