oracle sql language in the difference between the type of data

2011-05-09  来源:本站原创  分类:Database  人气:108 

Today, the oracle in learning the basic data types, Oracle data types supported can be divided into three basic categories: character data types, numeric data types and data types, said other data, its data types were compared and analyzed.

1.char

char length is fixed, for example, you define the char (20), even if you insert your abc, less than twenty bytes, the database will automatically add the abc 17 spaces back to complement the twenty bytes;

char is a distinction in English, Chinese accounted for two bytes in the char, while English accounted for one, so char (20) you can only save 20 characters or 10 characters.

char length of relatively fixed for the general case of non-Chinese

2.varchar/varchar2

varchar is not fixed length, for example, you define the varchar (20), when you insert abc, then in the database only 3 bytes.

varchar same distinction in English, this is the same char.

varchar2 is basically equivalent to varchar, it is the oracle himself a non-industry standard definition of varchar, difference is, varchar2 varchar with null instead of empty string

varchar/varchar2 for a fixed length, the general situation of non-Chinese

3.nvarchar/nvarchar2

nvarchar and nvarchar2 is a fixed length

nvarchar does not distinguish between Chinese and English, for example: you define nvarchar (20), you can deposit 20 letters / characters or combination of Chinese and English, this definition is 20 characters, not bytes

nvarchar2 basically equivalent to nvarchar, difference is in memory of the letters nvarchar2 also accounted for two-byte

nvarchar/nvarchar2 for storage of Chinese

char [(n)]

Fixed length, non-Unicode character data, length n bytes. n ranges from 1 to 8,000, the storage size is n bytes.

varchar [(n | max)]

Variable-length, non-Unicode character data. n ranges from 1 to 8,000. max indicates the maximum storage size is 2 ^ 31-1 bytes. Storage size is the actual length of the input data plus 2 bytes used to reflect the length of the stored data. The length of the input data can be 0 characters.

* If the size of the column the same data item, use char.
* If the column data entries vary considerably in size, use varchar.
* If the size of the column data entries vary considerably, and may exceed the size of 8,000 bytes, use varchar (max).

If you are not in the data definition or variable declaration statement, char or varchar data type specified n, the default length is 1. If you use the CAST and CONVERT functions char or varchar data type is not specified n, the default length is 30.
When the implementation of CREATE TABLE or ALTER TABLE, if the SET ANSI_PADDING is OFF, the char is defined as NULL columns as varchar treatment.
In addition to help understand, for reference only: Transfer from http://www.51testing.com/?uid-258885-action-viewspace-itemid-141197
Can also refer to learning http://ce.sysu.edu.cn/garden/dispbbs.asp?boardid=26&ID=8774&replyID=18180&skin=1
1.NULL value (empty value).

a. char NULL column takes the value of storage space.

b. varcahr NULL column values ​​do not take up storage space.

c. Insert the same number of NULL values, varchar columns into char columns efficiency was significantly higher.
2. Insert Data

Insert columns of data regardless of whether the involved index, char efficiency were significantly lower than varchar.

3. Update Data

If the update is not indexed on the columns, the lower the efficiency of char varchar, little difference; indexed words, efficiency is higher.

4. Modify the structure

a. either add or delete columns of type char or varchar, operation can be completed faster and there is no difference in efficiency.

b. For the purposes of increasing the width of the column, char and varchar are very obvious differences in the efficiency of, modify varcahr basically do not take time out, and modify the char column takes a long time.

5. Data Retrieval

Whether through the index, varchar data type char to retrieve a little better than the scan.

Choose the recommendations of char or varchar selected

1. Appropriate to the situation in the char:

a. column lines are basically the same data length, length variation does not exceed 50 bytes;

b. data changes frequently, less demand for data retrieval.

c. The length of the column will not change, modify the width of char type column the cost is relatively large.

d. column does not appear a large number of NULL values.

e. out too much on the need to create the index, the index too much data changes on the char column greater impact.

2. Suitable for the case of varchar;

a. column lines are great differences in the length of the data.

b. update the data in the column is very small, but check frequently.
c. often does not list data for the NULL value or empty value

nchar [(n)]

n-character fixed-length Unicode character data. n value must be between 1 and 4,000 (inclusive). Twice the storage size is n bytes.

nvarchar [(n | max)]

Variable-length Unicode character data. n values ​​between 1 and 4,000 (inclusive). max indicates the maximum storage size is 2 ^ 31-1 bytes. Storage size is twice the number of characters entered + 2 bytes. The length of the input data can be 0 characters.

Notes

If there is no definition or variable declaration in the data specified in the statement n, the default length is 1. If you do not use the CAST function to specify n, the default length is 30.

If the size of the column may be the same data item, use the nchar.

If the column data entries vary widely in size, use nvarchar.

sysname is a system of user-defined data types, in addition to non-null values, the functional and nvarchar (128) the same. sysname used to reference the database object names.

Nchar or nvarchar for the use of the object is assigned the default collation of the database, but can use the COLLATE clause to assign a specific collation.

SET ANSI_PADDING ON for nchar and never nvarchar. SET ANSI_PADDING OFF does not apply to nchar or nvarchar data types.

In Oracle of CHAR, NCHAR, VARCHAR, VARCHAR2, NVARCHAR2 the difference between the five types

1.CHAR (size) and VARCHAR (size) of the difference between
CHAR is fixed-length field, the maximum length of 2K bytes;
VARCHAR is variable length field, the maximum length of 4K bytes;

2.CHAR (size) and NCHAR (size) of the difference between
CHAR account if stored 1 byte alphanumeric, character encoding GBK store store 2 bytes, stored UTF-8 encoded character takes 3 bytes;
NCHAR character set to define the selected stored number of bytes occupied by the characters, usually stored as a 2-byte characters (regardless of character or characters)

3.VARCHAR (size) and VARCHAR2 (size) of the difference in the present version, there is no difference between the two; maximum length of 4K bytes; recommended VARCHAR2;

4.VARCHAR2 (size) and NVARCHAR2 (size) of the difference between the maximum length of 4K bytes, the difference with the difference between CHAR and NCHAR; (if the database character set length is 2, then NVARCHAR2 up to 2K)

5. The common characteristics of the time when performing insert, insert a value of'', then into a null, the insert ... values ​​('') <=> insert ... values ​​(null)
Search criteria to be used where xx is null

6. Examples of such a gender field, which kept "men and women," one of the values, two common options
CHAR (2) and NCHAR (1)

相关文章
  • oracle sql language in the difference between the type of data 2011-05-09

    Today, the oracle in learning the basic data types, Oracle data types supported can be divided into three basic categories: character data types, numeric data types and data types, said other data, its data types were compared and analyzed. 1.char ch

  • (R) ORACLE / SQL SERVER in the difference between functions and stored procedures 2011-07-20

    Differences in Oracle In SQL SERVER: Essentially no difference. Just a function like: Returns a variable limit. The stored procedure can return more than one. The function is embedded in the sql can be used, you can select the call, but not stored pr

  • Reproduced: DB2.ORACLE SQL written, the main difference 2011-05-10

    To be honest, ORACLE spoiled the domestic programmers, code ORACLE SQL flooded with properties, few people know what the ANSI standard SQL is like, cause the program to not run out of ORACLE, but fortunately with the hibernate popular, the situation

  • SQL language presented in a way the type of transaction 2010-12-16

    SQL language foundation SQL (structured query language, Structured Query Language) is a standard database language, the following features: 1 All data are stored in relational tables. Table consists of rows and columns, each row contains a set of dat

  • Oracle SQL Language Fundamentals 2010-11-11

    1.desc tablename --- description of a table (field, type, length) 2.select empno, sal * 12 "All money" from emp; --- To the result item from the alias. Or contain special characters, such as to maintain the original format, with double quotes. 3

  • [Transfer] mysql command-line commands and SQL statements, MySQL modify delete add field type, field data, etc. .. 2011-08-23

    I. Common mysql command-line command 1 Start service net start mysql MYSQL Stop service net stop mysql MYSQL 2. Netstat-na | findstr 3306 view was listening port, findstr to find the back of the port for the existence of 3. MYSQL landing on the comma

  • ORACLE SQL statement Categories 2010-03-11

    Oracle SQL statements can be divided into the following categories: Data manipulation language statements [Data manipulation language, DML] From one or more tables or views in the query data (SELECT); for operation [fetch] is scrollable [scrollable]

  • How to use an Oracle SQL to quickly generate test data 100 000 (rpm) 2011-01-20

    Doing database development or management of a large number of people often want to create test data, whenever they need to over ten thousand, if a one input, it will waste a lot of time, this article describes how the Oracle SQL to quickly generate a

  • How to use an Oracle SQL to quickly generate test data 100,000 2011-04-02

    Doing database development or management of a large number of people often want to create test data, whenever they need to over ten thousand, if a one input, it will waste a lot of time, this article describes how the Oracle SQL to quickly generate a

  • Oracle sql collation used 2011-04-12

    Tidy up the Oracle base commonly used in the project statement, be a small summary overview. 1, to obtain the current time select sysdate from dual 2, time to get a char select to_char (sysdate, 'yyyy-MM-dd') as mydate from dual 3, accurate to second

  • ORACLE database structured query language SQL language (a) 2010-12-30

    ORACLE database structured query language SQL language (a) First, the concept description: Database DATABASE, table TABLE, column COLUMN, line ROW, keyword PRIMARY KEY, Index INDEX Second, the column type: CHAR character, and VARCHAR2, value NUMBER,

  • ORACLE database query language SQL language structure (a) 2010-12-30

    ORACLE database query language SQL language structure (a) First, the concept description: Database DATABASE, table TABLE, column COLUMN, line ROW, keyword PRIMARY KEY, index INDEX Second, the type of the column: the character CHAR and VARCHAR2, value

  • Oracle SQL in the difference between IN and EXSITS Summary 2010-08-28

    IN Determine whether a given value or subquery match the value in the list. EXISTS Specify a subquery to detect the presence of line. Compare EXISTS and IN query This example compares the two semantics similar to the query. The first query uses EXIST

  • [Accumulation of knowledge] MySQL and Oracle sql statement difference between the basic (A) 2011-01-20

    mysql and oracle sql in some basic differences exist Table structure is as follows create table test( id int, name varchar(10), subject varchar(10), score varchar(10), primary key( id ) ); 1: Get the value of the difference. You can use the following

  • SQL statement. PL / SQL blocks and SQL * Plus commands the difference between 2010-09-15

    In SQL * Plus can handle three types of commands: SQL statement, PL / SQL blocks and SQL * Plus commands. However, there is a difference between the three. 1) SQL statement is a database object for the operation of the language, including data defini

  • oracle sql query used classic 2010-10-15

    oracle sql query used classic 1, delete duplicate records delete from wr_recordformtable a where rowid! = (Select max (rowid) from wr_recordformtable t where t.pid = a.pid and t.tablename = a.tablename ) 2, access to view execution time, accurate acc

  • SQL language summary and Optimization (finishing) 2011-06-07

    The above discussion is that several aspects of transactions and database locking in some of the content, in fact, the most commonly used in practice is the basic sql statement, and in fact I personally feel sql difficult to remember (ha ha, poor syn

  • Oracle: SQL * Loader and external tables (External Table) Usage 2011-08-29

    exp: Binary -> Database sqlloader: text -> Database oracle_loader: text -> Database oracle_datapump: Binary -> Database ================================================== ================================= Usage of SQL * Loader: ORACLE SQL * LO

  • oracle PL / SQL basic composition, block structure and basic syntax requirements, data types, variable definitions, operators and functions 2011-10-08

    oracle PL / SQL basic composition, block structure and basic syntax requirements, data types, variable definitions, operators and functions Feature PL / SQL language is the SQL language extensions, designed for application developers with features su

  • Oracle SQL Optimization 2010-02-15

    SQL optimization should be adjusted from five aspects: 1. Remove unnecessary large table full table scans 2. Cached small table full table scans 3. Inspection optimize the use of the index 4. Test the connection optimization technology 5. Cost to min