Oracle data types, DDL, DML, DCL, TCL language, SQL operators, SQL functions

Source: Internet
Author: User
Tags arithmetic arithmetic operators logical operators rtrim

SQL queries and SQL functions

  This chapter aims

    Understanding Oracle Data Types

Understanding Data Definition Languages and data manipulation languages

Understanding Transaction Control Languages and Data Control languages

Mastering SQL Operators and SQL functions

 SQL (Structured Query Language)-- Structured Query Language

SQL enables communication with Oracle servers

SQL is a database language and Oracle uses that language to store and retrieve information

Tables are the primary database objects used to store data


   User--------------------Send SQL statement query-------------------------> server

User <-------------Send command output to client--------------------server 

  SQL supports the following categories of commands:

--Data definition language (DDL)

Create, ALTER, drop

  --Data Manipulation language (DML)

Select, insert, UPDATE, delete

--Data Control Language (DCL)

Grant, Revork

   --Transaction Control Language (TCL)

Commit, Rollback


  Oracle data types

    When you create a table, you must specify the data type for each column

    The type of data types for Oracle databases are:

      Character type

Numeric type

Date Time Type

Raw/long Raw Type

LOB type


  Oracle data Type--character data type:

    Char, VARCHAR2, long


--Use the char data type when a fixed-length string is required.

The--char data type stores alphanumeric values.

The column length of the--char data type can be between 1 and 2000 bytes.


--VARCHAR2 data types Support variable-length strings

--VARCHAR2 data type stores alphanumeric values

The size of the--VARCHAR2 data type is within the range of 1 to 4,000 bytes


--long data Types store variable-length character data

--long data types can store up to 2GB


  Oracle data Type--numeric type


--can store integers, floating-point numbers, and real numbers

-Maximum accuracy of 38 bits;

Declaration syntax for numeric data types:

Number (P,s)--p means the precision is the total number of Bits, s represents the digits of the decimal point


  Oracle data Type--date Time type

Date and time data types store date and time values, including year, month, day, hour, minute, second

The main date-time types are:

      Date-stores the day and time portion, accurate to the entire second

     Timestamp-Stores the date, time, and time zone information, and the seconds value is exactly 6 digits after the decimal point.


  Oracle data type--raw/long RAW type

Raw data types are used to store binary data

Raw data type can store up to 2000 bytes

LONG raw data type for storing variable-length binary data

LONG RAW data type can store up to 2GB



  Oracle data type--lob type

LOB types are called ' large object ' data types and can store up to 4GB of unstructured information, such as sound and video files.

    LOB data types allow efficient, random, segmented access to data


CLOB is character lob (character lob), which can store large numbers of character data

Blob is binary log (binary LOB), which can store large binary objects, shapes, video clips, and sound files

bfile is a binary file (binary) that stores binary data in operating system files outside the database.

  Oracle data Type-pseudo-column

A pseudo-column in Oracle is like a table column, but it is not stored in a table

Pseudo-columns can be queried from a table, but their values cannot be inserted, updated, and deleted

Common pseudo-columns are rowid and rownum

ROWID is the storage address of a row in a table that uniquely identifies a row in a database, and you can use the ROWID pseudo-column to quickly locate a row in a table.

RowNum is the ordinal of the row in the result set returned by the query, which you can use to limit the number of rows returned by the query


  Data Definition Language:

Data defintion language is used to alter the database structure, including creating, changing, and deleting database objects.

The data definition language commands used to manipulate the table structure are:

--Create table

--alter table

--truncate table

--Drop table

  Data Manipulation Language:

Data manipulation language (manipulation language) for retrieving, inserting, and modifying data

Data manipulation language is the most common SQL command

Data manipulation language commands include:






    Create a copy of a table using an existing table

CREATE table new table name as select column name from old table name


CREATE TABLE STU1 as SELECT * from Stu where stuno=stuno1;

    Select rows with no duplicates, use distinct in select


SELECT DISTINCT * from table name;

    Using column Aliases

--Provide a different name for the column expression

--The alias specifies the column header



    Insert a value for a date type

--The default format for date data type is ' DD-MON--RR '

--default format for using dates

--Using the To_date function to convert

INSERT into order_master values (' 0001 ', ' December-May -13 ', ' C ', ' 2 May-May-13 ');

Insert into my_table (date_col) VALUES (to_date) (' 2013-10-18 ', ' yyyy-mm-dd ');


    syntax for inserting records from other tables:

Insert into Table name (field list) Select field name from other table names


INSERT INTO Masterinfo select * from MasterInfo1

Insert into Masterinfo (stuno,stuname) Select stuno1,stuname1 from MasterInfo1


  Transaction Control Language

A transaction is the smallest unit of work that works as a whole;

Ensure the overall success or failure of a transaction, called transaction control

The statements used for transaction control are:

      Commit--commit and end transaction processing

      Rollback--undo the work already done in the transaction

      savepoint--marks a point in a transaction that can be rolled back


Update order_master set del_date= ' 30月-August -05 ' where ordemo<= ' 0002 ';

  Data Control Language

Data Control Language provides permission control commands for users

The commands for permission control are:
      Grant--granting permissions

      Revoke--Revoke granted permissions


Grant SELECT on Vendor_master to accounts with GRANT option;

Revoke seclect,update on Order_master from Martin;


SQL operator

The SQL operators supported by Oracle are categorized as:

Arithmetic operators, comparison operators, logical operators, set operators, join operators


  Arithmetic operators

Arithmetic operators are used to perform numeric calculations, and arithmetic expressions can be used in SQL statements, and arithmetic expressions consist of column names, numeric constants, and arithmetic operators that concatenate them into numeric data types.

Arithmetic operators include +-*/


>select Itemdesc,max_level-qty_hand avble_limit from itemfile where p_category= ' spares ';

>select itemdestc,itemrate * (Max_level-qty_hand) from Itemfile where p_category= ' spares ';


  Comparison operators

Comparison operators are used to compare the values of two expressions

Comparison operators include =,! =, <, >, <=, >=, Between...and, in, like, is null.


>select itemdesc,re_level from Itemfile where qty_hand<max_level/2;

>select OrderNo from Order_master where del_date in (' June-February-05 ', ' May-February-05 ');

>select vencode,venname,tel_no from Vendor_master where venname like ' I s ';


  logical operators

Logical operators are used to combine the results of multiple comparison operations to produce one or both true or false results.

Logical operators include with (and), or (or), non-(not)


>select * from Order_master where odate> ' October-May -13 ' and del_date< ' 2 June-May-13 ';


  Set operator

The set operator combines the results of two queries into one result

Union, UNION ALL, intersect, minus

The Intersect operator returns only the public rows of two queries.

The minus operator returns the rows that appear in the second query from the first query result.

>select OrderNo from Order_master minus select OrderNo from Order_Detail;


  Join operator

The join operator is used to combine multiple strings or data values into a single string

>select (venname| | ' The address is ' | | venadd1| | ' ' | | venadd2| | ' ' | | VENADD3) address from Vendor_master where vencode= ' v001 ';

--by using the Join operator, you can combine multiple columns in a table into a logical row;


  Precedence of Operators

The order of precedence of SQL operators from highest to lowest is:

Arithmetic operators

Join operator

Comparison operators

Not logical operator

and logical operators

or logical operator

SQL functions

Oracle provides a range of functions for performing specific operations

The SQL function takes one or more parameters and returns a value

Classification of SQL functions:

Single-line functions, grouping functions, analytic functions


   Single-line function classification:

Single-line function returns only one value for each row queried from a table

Can appear in the SELECT clause and in the WHERE clause

A single-line function can be divided into:

Date function

numeric functions

Character functions

Conversion functions

Other functions

      Date function

Date functions operate on date values and produce results of date data types or numeric types

Date functions include:








      Character functions

Character functions accept character input and return characters or values

function input and output

Initcap (char) Select Initcap (' Hello ') from dual; Hello

Lower (char) select lower (' fun ') from dual; Fun

Upper (char) Select upper (' Sun ') from dual; SUN

LTrim (char,set) Select LTrim (' Xyzadams ', ' XYZ ') from dual; Adams

RTrim (char,set) Select RTrim (' Xyzadams ', ' AMS ') from dual; Xyzad

Translate (char,from,to) Select Tran Slate (' Jack ', ' J ', ' B ') from dual; Back

Replace (Char,search string,[rep string]) Select replace (' Jack and Jue ', ' j ', ' BL ') from dual; Black and Blue

InStr (Char,m,n) Select InStr (' Worldwide ', ' d ') from dual; 5

substr (char,m,n) Select substr (' ABCDEFG ', 3,2) from dual; Cd

Concat (EXPR1,EXPR2) Select Concat (' Hello ', ' world ') from dual; Hello World


     Conversion functions

Conversion functions convert from one data type to another data type

The commonly used conversion functions are:





Select To_date (' 2013-12-16 ', ' YYYY-MM-DD ') from dual;

Select To_number (' + ') from dual;

      Other functions

Functions to convert null values:

NVL Nvl2 Nullif


Select ITEMDESC,NVL (re_level,0) from Itemfile;

Select ITEMDESC,NVL2 (re_level,re_level,max_level) from Itemfile;

Select Itemdesc,nullif (re_level,max_level) from Itemfile;

      Grouping functions

Grouping functions to return results based on a set of rows

Returns a value for each set of rows

Avg min Max sum count


Select COUNT (*) from Itemfile;

Select AVG (*) from itemfile where p_category= ' accessories ';

Select COUNT (itemrate) from Itemfile;

Select Max (max_level) from Itemfile;

Select COUNT (Distinct qty_hand) from Itemfile;

Select SUM (itemrate * max_leve) from Itemfile;

  GROUP BY clause

The GROUP BY clause is used to divide the information into smaller groups

Each group of rows returns a single result for that group


Select P_category,max (itemrate) from Itemfile Group by P_category;


  HAVING clause

HAVING clause to specify criteria for a GROUP BY clause to retrieve rows

Select P_category,max (itemrate) from Itemfile GROUP by P_category have p_category not in (' Accessories ');


   Analytic functions

Analytic functions calculate aggregated values based on a set of rows

Used to calculate cumulative rankings, moving averages, etc. for complete aggregation

Parse function returns multiple rows for each set of records

Row_number Rank Dense_rank

The following three analysis functions are used to calculate the row position in a set of ordered rows, starting with 1

--row_number-Returns successive rows, regardless of the value equality

--rank-Rows with equal values have the same rank, and the ordinal then jumps

--dense_rank rows with equal values have the same row rank, the sequence number is continuous

Select D.dname,e.ename,e.sal,dense_rank () over (partition by E.deptno ORDER BY e.sal Desc) as Denrank from EMP e,dept D W Here E.deptno=d.deptno;


SQL is a common database language

SQL commands can be divided into data definition language, data manipulation language, transaction control language, data Control language

Data types supported by Oracle include characters, values, DateTime, raw and LOB, and more

SQL-supported operators include arithmetic, comparison, logic, collection, and join operators

SQL functions are broadly divided into single-line functions, aggregate functions, and analytic functions


Describes the similarities and differences between the TRUNCATE TABLE command and the Delete command.


Oracle data types, DDL, DML, DCL, TCL language, SQL operators, SQL functions

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.