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

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

　　　　Char

--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

--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

--long data Types store variable-length character data

--long data types can store up to 2GB

　　

　　Oracle data Type--numeric type

Number

--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:

--Select

--Insert

--Update

--Delete

　　

　　　　Create a copy of a table using an existing table

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

Cases:

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

　　　　Select rows with no duplicates, use distinct in select

Cases:

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

Cases:

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

Cases:

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

Cases:

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 +-*/

Cases:

>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.

Cases:

>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)

Cases:

>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:

Add_months

Months_between

Last_day

Round

Next_day

Trunc

Extract

　　　　　　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:

To_char

To_date

To_number

Cases:

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

Cases:

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

Cases:

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

Cases:

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;

Summarize:

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