6-1 SQL Introduction

1.sql Language Introduction

Structured Query Language (structured query Language) is the standard language for relational databases.

SQL works in conjunction with database programs, such as Ms Access, DB2, Informix, MS SQL Server, Oracle, Sybase, and other database systems.

2. History of SQL

October 1986, The American National Association (ANSI) issued the ANSI file x5.135-1986 "database language sql", June 1987 International Standardization Association (ISO) adopted as international standards. These two standards are now called "SQL86".

October 1989 ANSI also promulgated the enhanced integrity characteristics of the SQL89 standard. Subsequently, the ISO to the standard of a large number of modifications and expansion.

August 1992 ISO issued a standardized document iso/iec9075:1992 "database language SQL", people used to call the standard SQL92-----"SQL2".

1999 ISO issued a standardized document: iso/iec9075:1999 database language SQL, people used to call SQL----"SQL3".

Currently, most SQL database programs have their own private extensions in addition to the SQL standard. This means that there are many different versions of the SQL language, but in order to be compatible with the ANSI standard, they must support some key keywords in a similar way (such as SELECT, UPDATE, DELETE, INSERT, WHERE, and so on).

3. SQL Language Function Division

SQL language is divided into four types of functions: Data Query Language DQL, Data manipulation language DML, data definition language DDL, Data Control Language DCL.

(1) Data Query Language DQL

The DQL basic structure of a data query language is a query block consisting of a SELECT clause, a FROM clause, a WHERE clause:

SELECT < field Name table >

From < table or view name >

WHERE < query conditions >

(2) Data manipulation language

Data manipulation language DML mainly has three kinds of forms:

1) Inserting: Insert

2) Updated: Update

3) Deletion: Delete

(3) Data definition language DDL

The data definition language DDL is used to create various objects in the database-----tables, views, indexes, synonyms, clusters, and so on:

CREATE Table/view/index/syn/cluster

(4) Data Control Language DCL

The Data Control Language DCL is used to grant or reclaim certain privileges to access the database and to control

The database manipulates the time and effect that the transaction takes place, monitors the database and so on. Such as:

1 Grant: Authorization.

2) ROLLBACK [WORK] to [savepoint]: Back to a certain point.

3 Commit [WORK]: Submit

Wait

4.SQL Basic Grammar Rules

The syntax rules of SQL language are simple and popular, and easy to operate.

(1). Keywords are case-insensitive: SELECT, select (2).        Object names are case-insensitive select * from Tab_stu; SELECT * from Tab_stu (3). Character and date are case-sensitive Select ename, deptno from emp where ename= ' SCOTT '; Select ename, deptno from emp where ename = ' Scott '; (4), the Oracle database in the SQL plus tools such as the end of a semicolon, multiline statements can be wrapped (5) table and column naming criteria table or column name of the maximum length of 30 characters. A table or column name can contain letters, numbers, and underscore characters (_). The table or column name must begin with a letter. Names can contain numbers or underscores, but must begin with a letter. Table and column names are case-insensitive.
5. Oracle Data Types

Orale Data type:

Type	Meaning
CHAR (length)	A fixed-length string is stored. Parameter length specifies a length, padding with a space if the stored string is less than length. The default length is 1, and the maximum is no more than 2000 bytes.
VARCHAR2 (length)	Stores a variable-length string. length specifies the maximum length of the string. The default length is 1, and the maximum is no more than 4000 characters.
Number (P,s)	You can store either floating-point numbers or integers, and p represents the maximum number of digits (if the decimal number includes the integer part and the decimal part and the decimal point, p defaults to 38), and S is the decimal digit. Can be stored in negative numbers
DATE	Store date and time, storage era, 4-bit year, month, day, time, minute, second, storage time from January 1, 4712 to A.D. December 31, 4712.
TIMESTAMP	Not only stores the date of the month, time, minutes, and seconds after 6 digits, including time zone.
Clob	Store large text, such as storing unstructured XML documents
Blob	Store binary objects, such as graphics, video, sounds, and so on.

Oracle-supported data types can be grouped into three basic categories: Character data types, numeric data types, and data types that represent other data.

character data type

The char char data type stores a fixed-length subkey value. A char data type can consist of 1 to 2000 characters. If the length is not specified for char, its default length is set to 1. If you assign a value to a char type variable whose length is less than the specified length, Oracle automatically fills it with a space.

VARCHAR2 stores variable-length strings. Although you must also specify the length of a VARCHAR2 data variable, this length refers to the maximum length assigned to the variable rather than the actual assignment length. You do not need to fill with spaces. Can be set to a maximum of 4,000 characters.

Because the VARCHAR2 data type is stored only as characters assigned to the column (without spaces), VARCHAR2 requires less storage than the char data type.

Oracle recommends using VARCHAR2

The nchar and NVARCHAR2 nchar and NVARCHAR2 data types store character data of fixed length and variable length, but they use a different character set than the other types of the database. When you create a database, you specify the character set that is used to encode the data in the database. You can also specify an auxiliary character set [that is, the local language set (national Language set, or NLS)]. NCHAR and NVARCHAR2 type columns use the auxiliary character set.

In Oracle9i, you can represent the length of nchar and NVARCHAR2 columns in characters instead of bytes.

A Long data type can hold 2GB of character data, which is inherited from earlier versions. Now, if you want to store large volumes of data, Oracle recommends using CLOB and NCLOB data types. There are many limitations to using the long type in tables and SQL statements.

CLOB and Nclob CLOB and NCLOB data types can store up to 4GB of character data. The NCLOB data type can store NLS data.

Numeric data Types

Oracle uses standard, variable-length internal formats to store numbers. This internal format precision can be as high as 38 bits.

The number data type can have two qualifiers, such as:

Column number (precision, scale)

Precision represents a valid bit in a number. If precision is not specified, Oracle will use 38 as the precision.

Scale represents the number of digits to the right of the decimal point, scale the default setting is 0. If you set the scale to a negative number, Oracle will take the digit to the specified number of digits to the left of the decimal point.

Date Data Type

Oracle Standard date format is: Dd-mon-yy HH:MI:SS

By modifying the parameter Nls_date_format of an instance, you can change the format of the date inserted in the instance. During a session, you can modify the date through the ALTER SESSIONS SQL command or update a specific value by using parameters in the to_date expression of the SQL statement.

other types of data

Raw and long raw raw and long raw data types are primarily used to interpret the database. When these two types are specified, Oracle stores the data in a bit form. The raw data type is typically used to store objects in a specific format, such as bitmaps. The raw data type can occupy 2KB of space, while a long raw data type can occupy a 2GB size.

ROWID ROWID is a special type of column called Pseudo columns (Pseudocolumn). The ROWID pseudo column can be accessed as a normal column in an SQL SELECT statement. Each row in an Oracle database has a pseudo column. The ROWID represents the address of the row, and the ROWID pseudo column is defined with the ROWID data type.

ROWID is associated with a specific disk-driven location, so rowid is the quickest way to get a row. However, the rowid of the rows changes as you unload and overload the database, so it is recommended that you do not use the values of ROWID in the transaction. For example, once the current application has finished using the record, there is no reason to save the rowid of the row. You cannot set the value of a standard ROWID pseudo column through any SQL statement.

A column or variable can be defined as a ROWID data type, but Oracle cannot guarantee that the value of the column or variable is a valid ROWID.

Lob

LOB (large object) data type that can hold 4GB of information. LOB has the following 3 types:

。 CLOB, only character data can be stored

。 NCLOB, saving native language character set data

。 BLOB, saving data with binary information

You can specify whether to save one LOB data in an Oracle database or point to an external file that contains the secondary data.

The LOB can participate in the transaction. The data in the management LOB must be Dbms_lob pl/sql built-in packages or OCI interfaces.

To facilitate the conversion of a Long data type to lob,oracle9i contains many functions that support both LOB and long, as well as a new selection of an ALTER TABLE statement that allows the Long data type to be automatically converted to lob.

BFILE

The bfile data type is used as a pointer to a file that is stored outside of an Oracle database.

XmlType

As part of the support for XML, Oracle9i contains a new data type XmlType. The column defined as XmlType stores an XML document in the character LOB column. There are many built-in features that allow you to extract a single node from the text, and you can create indexes on any node in the XmlType document.

User-defined data

After Oracle8, users can define their own complex data types, which are composed of Oracle base data types.

AnyType, Anydata and Anydataset

Oracle includes 3 new data types that define data structures outside of existing data types. Each of these data types must be defined with a program unit to let Oracle9i know how to handle the specific implementations of these types.

Type Conversions

Oracle automatically converts certain data types to other data types, and the conversion depends on the SQL statement that includes the value.

Data conversions can also be performed through Oracle's type conversion functions.

Connection and comparison

On most platforms, the connection operators in Oracle SQL are two vertical bars (| | Said The connection is a two character value connection. Oracle's automatic type conversion feature allows two numeric values to be connected.

Null

Null values are one of the important features of relational databases. In fact, NULL does not represent any value, it represents no value. If you want to create a column of a table, and the column must have a value, you should specify it as NOT NULL, which means that the column cannot contain null values.

Any data type can be assigned a null value. The null value introduces the three-state logic of the SQL operation. If one side of the comparison is a null value, then there are 3 states: Ture, False, and neither.

Because a null value is not equal to 0 or any other value, testing whether a data is a null value can only be done through the relational operator is null.

A null value is especially appropriate when a column is not assigned a value. If you choose not to use null values, you must assign values to all columns of the row. This actually cancels out the possibility that a column does not need a value, and the value assigned to it can easily be misunderstood. This situation can mislead the end user and cause the cumulative operation error results.

6. Supplementary note on data types

Fixed-length character types such as char (10) and indefinite long type of an operator, such as varchar (20), can have time differences when looking.