Each of the following needs to be answered for Oracle, MySQL, SQLServer and attached to an example: Comment data type The complete database statement completes the structure of the table's structure to establish an index.
Mysql note untrue #
Multi-line / * * / mysql has three annotation sentences # 注 掉 注 注 内容 内容 内容 内容 # 部分 常 常 常 常 常 常 常 常 常 常 常 常 常 常 常 常 常 常 常 的Comment method. - Remember that there is a space behind you to get an annotation. / * ... * / We generally use the previous / * is enough, because we can't do it later, right? Such as: # this is a single line comment * / / * this, however, is a multiple line comment * / Since MySQL3.23, you can "hide" mysql in C style annotations Unique keywords, comments start with "/ *!" Instead of "/ *". MySQL Views the inside of this special type annotation and uses these keywords, but other database servers ignore these keywords as part of the comments. This helps to write code that utilizes the MySQL unique functionality when executed by MySQL, and the code can also be used for other database servers without modifying. The following two statements are equivalent to non-mysql database servers, but if it is a MySQL server, execute an insert delayed operation in the second statement: INSERT INTO ABSENCE INSERT / *! Delayed * / INTO ABSENCE SQL Server Comment Standard annotation "-" for single-line comments; the same program comment symbols as the C language, ie "/ ** /". "/ *" Is used to note the beginning of the text, "* /" is used to comment the end of the text, and you can identify multi-line text in the program. MySQL Build Form: MySQL> Create Table Data (Name Char (8), SEX Char (2)); the previous example is a table named DATA, and there are two fields, named, character, length 8 ; SEX (gender), character type, length 2. Table name DATA partition. SQL Server Build Form:
Create a table faster with the CREATE TABLE command. The syntax is as follows: CREATE TABLE [database_name [owner] | owner...] Table_name ({
Database_name Specifies which database that is newly created. If you do not specify a database name, the created table is stored in the current database. Owner Specifies the username of the database owner. Table_name Specifies the name of the new table, which is no more than 128 characters. For the database, Database_name.OWNER_NAME.Object_name should be the only one. Column_name Specifies the name of the new table and does not exceed 128 characters. For the database, Database_name.OWNER_NAME.Object_name should be the only one. Computed_column_expression Specifies the expression of the column value of the compute column. The expression can be a column name, a constant, a variable, a function, or a combination thereof. The so-called computing column is a virtual column, and its value is not actually stored in the table, but is the result of some calculation in the same table in the same table. For example, the employee information table stores the employee's employment date, then employee can be calculated by expressing the "Employment Date of Employment Date", the working age can be used as a column. ON {fileGroup | default} Specifies the file group name of the storage table. If you use the default option or omit the ON clause, the new table is stored in the default file group. TextImage_on Specifies the file group of data stored in Text, NText, and Image columns. If there is no such clause, these types of data are stored in the same file group along with the table. Data_type Specifies the data type default specify the default value of the column. When entering data, if the user does not specify a column value, the system will use the set default value as the column value. If the column does not specify a default value but allows a null value, the null value is used as the default. The default values can be constant, null values, SQL Server internal functions (such as getDate () functions), NILADIC functions, and more. The constant_expression column is a constant expression of the value, which can be a constant or system function or NULL. Identity specifies a list of Identity columns. There can be only one Identity column in a table. SEED specifies the initial value of the Identity column. INCREMENT specifies the increment of the Identity column. NOT for Replication Specify columns The Identity property of the column does not take effect when inserting data replicated from other tables into the table, that is, the lack of generating column values, so that the copied data row maintains the original column value. ROWGUIDCOL specifies the unique identification line number listed in the world (RowGuidcol is the abbreviation of the Row Global Unique Identifier Column). The data type of this column must be a UNIQUEIDENTIFIER type. There is only one column that is defined as the ROWGUIDCOL column in the column of the data type in the UNIQUEIDENTIFIER. The ROWGUIDCOL attribute does not make the column value unique, nor does it automatically generate a new value to the inserted row. The default value of the NEWID () function or the specified column needs to be used in the INSERT statement. Collate specifies the verification method used by the table. Column_constraint and table_constraint specify column constraints and table constraints, we will introduce specific definitions in the next section. The remaining parameters will gradually describe in the following chapters.
Note: There is at least one column of a table, but up to 1024 columns. You can create up to 2 million tables in each database. The metering unit used when stored is an extent. A disk is divided into 8 data pages, 8kb bytes per page. When you create a new table, it is assigned to a storage space initially only for a panel. When the storage space is added, the panel is increased in units.
Database_name Specifies which database that is newly created. If you do not specify a database name, the created table is stored in the current database. Owner Specifies the username of the database owner. Table_name Specifies the name of the new table, which is no more than 128 characters. For the database, Database_name.OWNER_NAME.Object_name should be the only one. Column_name Specifies the name of the new table and does not exceed 128 characters. For the database, Database_name.OWNER_NAME.Object_name should be the only one. Computed_column_expression Specifies the expression of the column value of the compute column. The expression can be a column name, a constant, a variable, a function, or a combination thereof. The so-called computing column is a virtual column, and its value is not actually stored in the table, but is the result of some calculation in the same table in the same table. For example, the employee information table stores the employee's employment date, then employee can be calculated by expressing the "Employment Date of Employment Date", the working age can be used as a column. ON {fileGroup | default} Specifies the file group name of the storage table. If you use the default option or omit the ON clause, the new table is stored in the default file group. TextImage_on Specifies the file group of data stored in Text, NText, and Image columns. If there is no such clause, these types of data are stored in the same file group along with the table. Data_type Specifies the data type default specify the default value of the column. When entering data, if the user does not specify a column value, the system will use the set default value as the column value. If the column does not specify a default value but allows a null value, the null value is used as the default. The default values can be constant, null values, SQL Server internal functions (such as getDate () functions), NILADIC functions, and more. The constant_expression column is a constant expression of the value, which can be a constant or system function or NULL. Identity specifies a list of Identity columns. There can be only one Identity column in a table. SEED specifies the initial value of the Identity column. INCREMENT specifies the increment of the Identity column. NOT for Replication Specify columns The Identity property of the column does not take effect when inserting data replicated from other tables into the table, that is, the lack of generating column values, so that the copied data row maintains the original column value. ROWGUIDCOL specifies the unique identification line number listed in the world (RowGuidcol is the abbreviation of the Row Global Unique Identifier Column). The data type of this column must be a UNIQUEIDENTIFIER type. There is only one column that is defined as the ROWGUIDCOL column in the column of the data type in the UNIQUEIDENTIFIER. The ROWGUIDCOL attribute does not make the column value unique, nor does it automatically generate a new value to the inserted row. The default value of the NEWID () function or the specified column needs to be used in the INSERT statement. Collate specifies the verification method used by the table. Column_constraint and table_constraint specify column constraints and table constraints, we will introduce specific definitions in the next section. The remaining parameters will gradually describe in the following chapters.
Note: There is at least one column of a table, but up to 1024 columns. You can create up to 2 million tables in each database. The metering unit used when stored is an extent. A disk is divided into 8 data pages, 8kb bytes per page. When you create a new table, it is assigned to a storage space initially only for a panel. When the storage space is added, the panel is increased in units. Create a table faster with the CREATE TABLE command. The syntax is as follows: CREATE TABLE [database_name [owner] | owner...] Table_name ({
Database_name Specifies which database that is newly created. If you do not specify a database name, the created table is stored in the current database. Owner Specifies the username of the database owner. Table_name Specifies the name of the new table, which is no more than 128 characters. For the database, Database_name.OWNER_NAME.Object_name should be the only one. Column_name Specifies the name of the new table and does not exceed 128 characters. For the database, Database_name.OWNER_NAME.Object_name should be the only one. Computed_column_expression Specifies the expression of the column value of the compute column. The expression can be a column name, a constant, a variable, a function, or a combination thereof. The so-called computing column is a virtual column, and its value is not actually stored in the table, but is the result of some calculation in the same table in the same table. For example, the employee information table stores the employee's employment date, then employee can be calculated by expressing the "Employment Date of Employment Date", the working age can be used as a column. ON {fileGroup | default} Specifies the file group name of the storage table. If you use the default option or omit the ON clause, the new table is stored in the default file group. TextImage_on Specifies the file group of data stored in Text, NText, and Image columns. If there is no such clause, these types of data are stored in the same file group along with the table. Data_type Specifies the data type default specify the default value of the column. When entering data, if the user does not specify a column value, the system will use the set default value as the column value. If the column does not specify a default value but allows a null value, the null value is used as the default. The default values can be constant, null values, SQL Server internal functions (such as getDate () functions), NILADIC functions, and more. The constant_expression column is a constant expression of the value, which can be a constant or system function or NULL. Identity specifies a list of Identity columns. There can be only one Identity column in a table. SEED specifies the initial value of the Identity column. INCREMENT specifies the increment of the Identity column. NOT for Replication Specify columns The Identity property of the column does not take effect when inserting data replicated from other tables into the table, that is, the lack of generating column values, so that the copied data row maintains the original column value. ROWGUIDCOL specifies the unique identification line number listed in the world (RowGuidcol is the abbreviation of the Row Global Unique Identifier Column). The data type of this column must be a UNIQUEIDENTIFIER type. There is only one column that is defined as the ROWGUIDCOL column in the column of the data type in the UNIQUEIDENTIFIER. The ROWGUIDCOL attribute does not make the column value unique, nor does it automatically generate a new value to the inserted row. The default value of the NEWID () function or the specified column needs to be used in the INSERT statement. Collate specifies the verification method used by the table. Column_constraint and table_constraint specify column constraints and table constraints, we will introduce specific definitions in the next section. The remaining parameters will gradually describe in the following chapters.
Note: There is at least one column of a table, but up to 1024 columns. You can create up to 2 million tables in each database. The metering unit used when stored is an extent. A disk is divided into 8 data pages, 8kb bytes per page. When you create a new table, it is assigned to a storage space initially only for a panel. When the storage space is added, the panel is increased in units.
Database_name Specifies which database that is newly created. If you do not specify a database name, the created table is stored in the current database. Owner Specifies the username of the database owner. Table_name Specifies the name of the new table, which is no more than 128 characters. For the database, Database_name.OWNER_NAME.Object_name should be the only one. Column_name Specifies the name of the new table and does not exceed 128 characters. For the database, Database_name.OWNER_NAME.Object_name should be the only one. Computed_column_expression Specifies the expression of the column value of the compute column. The expression can be a column name, a constant, a variable, a function, or a combination thereof. The so-called computing column is a virtual column, and its value is not actually stored in the table, but is the result of some calculation in the same table in the same table. For example, the employee information table stores the employee's employment date, then employee can be calculated by expressing the "Employment Date of Employment Date", the working age can be used as a column. ON {fileGroup | default} Specifies the file group name of the storage table. If you use the default option or omit the ON clause, the new table is stored in the default file group. TextImage_on Specifies the file group of data stored in Text, NText, and Image columns. If there is no such clause, these types of data are stored in the same file group along with the table. Data_type Specifies the data type default specify the default value of the column. When entering data, if the user does not specify a column value, the system will use the set default value as the column value. If the column does not specify a default value but allows a null value, the null value is used as the default. The default values can be constant, null values, SQL Server internal functions (such as getDate () functions), NILADIC functions, and more. The constant_expression column is a constant expression of the value, which can be a constant or system function or NULL. Identity specifies a list of Identity columns. There can be only one Identity column in a table. SEED specifies the initial value of the Identity column. INCREMENT specifies the increment of the Identity column. NOT for Replication Specify columns The Identity property of the column does not take effect when inserting data replicated from other tables into the table, that is, the lack of generating column values, so that the copied data row maintains the original column value. ROWGUIDCOL specifies the unique identification line number listed in the world (RowGuidcol is the abbreviation of the Row Global Unique Identifier Column). The data type of this column must be a UNIQUEIDENTIFIER type. There is only one column that is defined as the ROWGUIDCOL column in the column of the data type in the UNIQUEIDENTIFIER. The ROWGUIDCOL attribute does not make the column value unique, nor does it automatically generate a new value to the inserted row. The default value of the NEWID () function or the specified column needs to be used in the INSERT statement. Collate specifies the verification method used by the table. Column_constraint and table_constraint specify column constraints and table constraints, we will introduce specific definitions in the next section. The remaining parameters will gradually describe in the following chapters.
Note: There is at least one column of a table, but up to 1024 columns. You can create up to 2 million tables in each database. The metering unit used when stored is an extent. A disk is divided into 8 data pages, 8kb bytes per page. When you create a new table, it is assigned to a storage space initially only for a panel. When the storage space is added, the panel is increased in units. Complete construction statement SQLServer: 1. Expand the server group and expand the server instance 2. Right click on "Database" and click New Database command to open the Database Properties window. 3. Type the name of the new database. 4. By default, the system automatically uses the specified database name as a prefix to create a primary database and transaction log files. 5. To change the default value of the new master database file, click the Data File tab. 6. To change the default value of the newly created transaction log file, click the Transaction Log tab. 7. To specify how the file grows, select: * When you need more data space, you want to allow the currently selected file growth, select the "File Auto Growth" command * To specify the file, press the fixed step, Select the "Mire byte" command and specify a value * To specify the file in a percentage of the current size, select the "Press Percent" command and specify a value of 8. It is recommended that the maximum allowable growth of the specified file # Do this prevent files from increasing when adding data, so that the entire disk space is used. Complete construction statement SQLServer: 1. Expand the server group and expand the server instance 2. Right click on "Database" and click New Database command to open the Database Properties window. 3. Type the name of the new database. 4. By default, the system automatically uses the specified database name as a prefix to create a primary database and transaction log files. 5. To change the default value of the new master database file, click the Data File tab. 6. To change the default value of the newly created transaction log file, click the Transaction Log tab. 7. To specify how the file grows, select: * When you need more data space, you want to allow the currently selected file growth, select the "File Auto Growth" command * To specify the file, press the fixed step, Select the "Mire byte" command and specify a value * To specify the file in a percentage of the current size, select the "Press Percent" command and specify a value of 8. It is recommended that the maximum allowable growth of the specified file # Do this prevent files from increasing when adding data, so that the entire disk space is used.
Complete construction statement SQLServer: 1. Expand the server group and expand the server instance 2. Right click on "Database" and click New Database command to open the Database Properties window. 3. Type the name of the new database. 4. By default, the system automatically uses the specified database name as a prefix to create a primary database and transaction log files. 5. To change the default value of the new master database file, click the Data File tab. 6. To change the default value of the newly created transaction log file, click the Transaction Log tab. 7. To specify how the file grows, select: * When you need more data space, you want to allow the currently selected file growth, select the "File Auto Growth" command * To specify the file, press the fixed step, Select the "Mire byte" command and specify a value * To specify the file in a percentage of the current size, select the "Press Percent" command and specify a value of 8. It is recommended that the maximum allowable growth of the specified file # Do this prevent files from increasing when adding data, so that the entire disk space is used. MySQL:
/ * CREATE DATABASE ACCOUNTS; #accounts for database name use accounts; * /
Oracle: 1. Create a typical database 2 with Database Assistant. Create a database 3 with Database Assistant customization. Create a database create database mydb logfile file1 with PL / SQL statements, file2 size nm # file1, file2 file name size is the size of maxlogfiles n maxlogmembers n maxloghistory n Datafile file1 size nm # file1 file name maxdatafiles n maxinstances n archivelog | noarchivelog
Example: Create Database Mydb DataFile '/Disk03/Mydb/system1.dbf' size 20m logfile '/disk04/mydb/log1.log', 'disk05 / mydb / log2.log' size 512k;
Establish an index
1 SQL / ORACLE / Establish Index Create [Unique] [Clustered | Nonclustered] index index_name on {table | view} (Column [ASC | DESC])
Unique parameter creates a unique index; Clustered | Nonclustered aggregation / non-aggregation index; ASC Ascending DESC Descending EG: Create a WAGE Column-based index Au_wage; Create Index Au_wage on Employee (WAGE)
2 mysql 1). Create index: create index indexname on tablename (TableColumns (length)); if it is a char, varchar type, Length can be less than the field actual length; if it is a BLOB and Text type, you must specify Length. 2) a unique index to create an index: CREATE UNIQUE INDEX indexName ON tableName (tableColumns (length)) 3) primary key index CREATE TABLE testIndex (i_testID INT NOT NULL AUTO_INCREMENT, vc_Name VARCHAR (16) NOT NULL, PRIMARY KEY (i_testID)); three Database Data Type Oracle Data Type Category: Char Type Data for Description Length, Length <= 2000 byte varchar2 is used to describe the length of character type data, length <= 4000 byte nchar is used to store Unicode character set The fixed length character data, the length <= 1000 byte nvarchar2 is used to store the deterioration of the Unicode character set, the length <= 1000 bytes Number is used to store integer or floating-point value Date to store date data The long-length character data used to store the maximum length of 2GB is used to store the variable length character data of the non-structured data, the length <= 2000 byte long RAW is used to store the variable length character data of the non-structured data, and the length is < = 2GB ROWID used to store binary data of the physical address listed in the table, occupying a fixed 10 byte blob to store up to 4GB non-structural binary data CLOB to store up to 4GB of character data NClob is used Storage up to 4GB Unicode Character Data BFile is used to store unstructured binary data in an operating system file other than the database, and use the binary data FLOAT indicating any type of column address to store floating point numbers.
SQL-Sever Data Type Category:
Integer data type INT or INTEGER SMALLINT TINYINT BIGINT floating-point data type REAL FLOAT DECIMAL NUMERIC binary data types BINARY VARBINARY logical data type BIT character data type CHAR NCHAR VARCHAR NVARCHAR text and graphic data types TEXT NTEXT IMAGE date and time data types DATETIME SMALLDATETIME monetary data Type Money SmallMoney Specific Data Type TimeStamp UNIQUEIDENTIFIER User Custom Data Type Sysname New Data Type SQL_VARIANT TABLE
Detailed explanation:
4.3.1 Integer Data Type Integer Data Type is one of the most commonly used data types. 1. INT (Integer) INT (or Integer) data type stores between 31 (-2, 147, 483, 648) to 2, 31 (2, 147, 483, 647) All positive negative integers. The data of each int type is stored in 4 byte, where 1 represents the positive and negative number of the integer value, the other 31 bits represent the length and size of the integer value. 2, Smallint Smallint data type stores all positive negative integers between 15 squares (-32, 768) to 2 from -2 (-32, 768). Each Smallint type data occupies 2 bytes of storage, where 1 represents the positive and negative numbers of the integer value, and other 15 bits represent the length and size of the integer value. 3, Tinyint Tinyint data type stores all positive integers between 0 and 255. Each Tinyint type data takes up a 1 byte of storage space. 4, Bigint Bigint data type storage from -2 ^ 63 (-9, 223, 372, 036, 854, 775, 807) to 2 ^ 63-1 (9, 223, 372, 036, 854, 775, 807) All positive negative integers between. The data of each Bigint type occupies 8 bytes of storage.
4.3.2 Floating point data type floating point data type is used to store decimal decimal. The floating point value is stored in SQL Server (Round Up or a resection) mode. The above-called rounding refers to when (and only) the number of rounding is a non-zero number, adding the numerical value on the minimum effective bit of the digital portion, and performs the necessary carry. If a number is on the top count, its absolute value will not decrease. Such as: 2 bits and 12 rounds were carried out separately from 3.14159265358979, and the results were 3.15 and 3.141592653590. 1. The REAL data type REAL data type can be accurate to the 7th decimal, and its range is from -3.40e -38 to 3.40e 38. Data of each REAL type occupies 4 bytes of storage. 2, the Float Float data type can be accurate to the 15th decimal, which ranges from -1.79E -308 to 1.79E 308. Data of each float type occupies 8 bytes of storage. The Float data type is writable as Float [n]. n Specifies the accuracy of the FLOAT data. n is an integer value between 1 and 15. When N takes 1 to 7, it is actually a REAL type of data, and the system stores it with 4 bytes; when N is taken 8 to 15, the system considers it a float type, stores it with 8 bytes. . 3, the Decimal Decimal data type can provide the actual storage space required for the decimal, but there are some restrictions, you can use 2 to 17 bytes to store 38 times from -10 38 -1 to 10 The value is between. It can be written as the form of Decimal [p [s]], and P and S have determined an accurate scale and digit. Where P represents the total number of values available (excluding the decimal point), the default value is 18; s represents the number of digits after the decimal point, the default is 0. For example: Decimal (15 5), indicates a total of 15 digits, including 10 digits, decimal 5. Table 4-3 lists the relationship between the number of bytes required for each accuracy. 4, Numeric Numeric data type is identical to the Decimal data type. Note: SQL Server combines with the front-end development tool, the data accuracy supported by the data accuracy is 28 bits. However, you can perform the SQL Server to start the SQL Server by using the command to activate the default accuracy. The command syntax is as follows: SQLSERVR [/ D Master_Device_path] [/ p precisim_leve1] 4.3.3 Binary Data Type 1, binary binary data type is used to store binary data. Its definition is binary (n), n to represent the length of the data, with a value of 1 to 8000. You must specify the size of the binary type data when used, at least 1 byte. Binary Type Data occupies the storage space of N 4 bytes. When entering the data, you must add characters "0x" as a binary identifier before the data, such as: Enter "ABC", should enter "0xAbc". If the input data is too long, it will take off the portion. If the number of input data bits is odd, an 0 will be added after the start symbol "0x", as described above "0xAbc" will be automatically changed to "0x0ABC". 2, the definition of Varbinary Varbinary data type is Varbinary (N). It is similar to the binary type, and the value of N is also 1 to 8000. If the input data is too long, it will cut out the portion.
Different is the characteristics of the varbinary data type with variable length, because the storage length of the Varbinary data type is the actual value length 4 byte. When the Binary data type allows the NULL value, it will be considered a Varbinary data type. In general, due to the length of the Binary data type, it is fast than the processing speed of the Varbinary type. 4.3.4 Logic Data Type Bit: Bit Data Types Type 1 byte of storage, its value is 0 or 1. If the value other than 0 or 1 will be regarded as 1. The bit type cannot be defined as a null value (so-called NULL value refers to a null value or meaningless value).
4.3.5 Character Data Type Character Data Types are the most data types. It can be used to store various letters, numbers symbols, special symbols. In general, when using character type data, it must be plus single quotes' or double-quotes. The storage space of one byte. N indicates that the storage space occupied by all characters is 1 to 8000, which can accommodate 8,000 ansi characters. If the n value is not specified, the system default value is 1. If entering data The number of characters is less than n, the system automatically adds a space to fill the set space. If the input data is too long, it will cut off the portion. 2, nchar nchar data type definition form is nchar [ (N)] It is similar to the CHAR type. Different is the value of NCHAR data type N is 1 to 4000. Because nchar type uses the Unicode standard character set (CharacterSet). Unicode standard specify two bytes of each character Storage space, it is more than double storage space than non-Unicode standards. The benefits of using Unicode standards are due to their storage units using two bytes, and their storage units have greatly increased. Including the language characters in the world, in a data column, there will be Chinese, English, French, German, etc., without coding conflicts. 3. VARCHAR VARCHAR data type is defined as VARCHAR [( n)] It is similar to the CHAR type, and the value of n is from 1 to 8000. If the input data is too long, it will cut off the portion. Different, the varchar data type has the characteristics of the fluctuation because VARCHAR The storage length of the data type is the actual value length. If the number of characters of the input data is less than n, the system does not add spaces to fill the set space. In general, due to the length of the char data type, it is fixed Faster processing speed than VARCHAR type. 4, NVARCHAR NVARCHAR data type is nvarchar [(n)]. It is similar to the varchar type. Different, NVARCHAR data type uses Unicode standard character set (Character Set), N The value is 1 to 4000.
4.3.6 Text and Graphical Data Types This type of data type is used to store a large number of characters or binary data. 1, the TEXT TEXT data type is used to store large amounts of text data, and its capacity is the 31-1 (2, 147, 483, 647) by 1 to 2 (2, 147, 483, 647) by 1 to 2, and the storage space of the hard disk is required when practical applications. Fixed. In the previous version of SQL Server 2000, a Text object stored in the database is actually a pointer, which points to a data page (Data Page) in 8kb (8192 bytes). These data pages are dynamically increased and logical links. In SQL Server 2000, the Text and Image Types of data are stored directly into the table's data line instead of being stored in different data pages. This reduces space for storing Text and IMA-GE types, and reduces the number of I / Os for disk processing such data. 2 NTEXT NTEXT data types and text. Types Similar, the NText type uses the Unicode standard character set, so its theoretical capacity is 230-1 (1, 073, 741, 823) bytes. 3 Image Image Data Type The Binary Data is stored in a large amount of binary data. Its theoretical capacity is 2 (2,147,483,647) bytes of 2. The mode of its storage data is the same as the TEXT data type. Usually used to store Ole Object Linking and Embedding, object connections, and embedded objects such as graphics. Like the Binary data type, the data type "0X" must be added as a binary identifier as a binary identifier 4.3.7 and the time data type 1 DateTime DateTime data type is used to store the date and time of the combination of the date and time. It can store all the date and time from January 1, 1999, from January 1, 1999, 59 minutes, 59 seconds, 59 seconds, 999, 5.5%, 3.33 millisecond. The storage space occupied by the DateTime data type is 8 bytes. The first four bytes are used to store the number of days before January 1, 1900, and the numerical division is positive and negative. The positive number indicates the date after this date, and the negative number indicates the date before this date. The latter four bytes are used to store the number of milliseconds that have been specified from this time. If the time portion is omitted when entering the data, the system will be 12: 00: 00: 000AM as the time default value: If the date part is omitted, the system will be the default value from 1 January 199. 2 SmallDateTime SmallDateTime data type is similar to the DateTime data type, but its date is smaller, from January 1 to 2079, 1900 to June 6: The low precision is low, it can only be accurate to minutes, in minutes In order to be a value of four in seconds, it is round to 30 seconds. Such as: DateTime is 14: 38: 30.283, SmallDateTime is considered to be 14:39:00 SmallDateTime data types to store data using 4 bytes. The first 2 bytes store the number of days since January 1, 1900 from the basic date, and the last two bytes store the number of minutes passing by the time. The input format of the input format date and time is generally divided into three categories:
English Digital Format This format can be used in English full name or abbreviation, and it is not case sensitive; the year and months can not be comma; the year can be 4 or 2; when it is two times, if it is value Less than 50 is considered 20xX year. If it is greater than or equal to 50, it is considered to be 19xx year; if the date is omitted, it is considered to be the first month. The following format is the correct date format: JUNE 21 2000 OCT 1 1999 January 2000 2000 February 2000 May 1 2000 1 Sep 99 June July 00 Digital Derivative format Allows the slash (/), connectors (-) and decimal points ( .) As a separator between the year, the month, and day representation. Such as: YMD: 2000/6/22 2000-6-22 2000.6.22 MDY: 3/5/2000 3-5-2000 3.5.2000 DMY: 31/12/1999 31-12-1999 31.12.2000 Pure Digital Format The pure digital format is represented by a continuous 4-bit 6, bit or 8 digit. If you enter 6 or 8 digits, the system will be identified by year, month, day, ymd format, and the month and day are used in two digits: If the input number is 4 digits, the system is considered These 4 digits represent the year, the month and the day and the date of the year, January 1. Such as: 20000601 - June 1, 2000 991212 --- December 1998 - 1998? The order is entered. In the case of the colon, ":" is separated. However, the millisecond portion can be used in a decimal point. "The first digit represents a second, and the second digit represents one% of the second, the third digit represents millive seconds. Use AM when using 12 hours. AM and PM (PM) specify the time at noon or in the afternoon, and if not specified, the system defaults to AM. AM and PM are not case sensitive. Such as: 3: 5: 7.2pm --- 36:23 in the afternoon 200 milliseconds 10: 23: 5.123am --- 10:23 mM 5 seconds 123 milliseconds You can use the set dateFormat command to set the system default date -Time format. 4.3.8 Currency Data Type Currency Data Type Used to Store Money Values. When using the currency data type, the currency symbol should be added before the data, the system can identify the currency which country, if the currency symbol is not added, the default is "¥". The monetary symbols are shown in Figure 4-2. 1 Money Money data type data is a 4-digit Decimal value, which is worth 63 times from -2 (-922, 337, 203, 685, 477.5808 to 2) 63 ( 922, 337, 203, 685, 477.5807), the data accuracy is a million currency unit. The Money data type uses 8 byte storage.
2 SmallMoney SmallMoney data type is similar to the Money type, but its stored currency value is smaller than the Money data type, which is worth 4 bytes from -214, 748.3648 to 214, 748.3647. The storage space is 4 bytes.
4.3.9 Specific Data Types The SQL Server contains some special data types for data storage. 1 TimeStamp TimeSTAMP Data Type provides a unique value within the database range This type is equivalent to binary8 or varbinary (8), but the value of this column will be automatically updated when it defined in the update or inserted data lines, one count value This timestamp data column will be added automatically. There can be only one TimeStamp data column in each database table. If a column named "TimeStamp" is created, the type of the column will be automatically set to the TimeStamp data type. 2 UNIQUEIDENTIFIER UNIQUEIDENTIFIER Data Type Stores a 16-bit binary number. This number is called (Guidglobal Unique Identifier, "the world's only authentication number). This number is the only unique code generated by SQLServer's NewID function, and the number of computers around the world will not be the same via this function.
4.3.10 User Custom Data Type SysName SysName Data Type is the system to provide users to facilitate user-defined data types. It is defined as NVARCHAR (128), which can store 128 UNICODE characters or 256 general characters. See Chapter 7, "Custom Data Type" section in Chapter 7, "Managing Database Table" in Chapter 7.
4.3.11 New Data Type SQL Server 2000 Add 3 data types: Bigint, SQL_Variant, and Table. The Bigint data type has been described in an integer type. The rest is described below: 1 SQL_VARIANT SQL_VARIANT data type can store any other legal SQL Server data outside the text, graphics data (nText, Image), and TimeStamp type data. This data type greatly facilitates the development of SQL Server. 2 Table Table Data Type The result set after storing the table or view processing. This new type allows variables to store a table, making functions or procedures back to query results more convenient and fast, Ji, please refer to Chapter 13, "Cursor, View, and Customize
MySQL data type:
1. Numerical value Tinyint (very small integer) Smallint (smaller integer) Mediumint (medium-size integer) INT (standard integer) Bigint (large integer) FLOAT (single precision floating point) Double (double precision floating point) DECIMAL A string floating point number)
2. (character) string value char (fixed long string) VARCHAR (variable long string) Tinyblob (very small blob)) BLOB (small blob) Mediumblob (medium blob) longblob (big blob) TINYTEXT (very small text string) Text (small text string) MediumText (medium text string) longtext (big text string) ENUM (enumeration) set (collection)
3. Date and time value Date ("YYYY-MM-DD" format represented Date ("hh: mm: ss" formatted time type) DateTime ("YYYY-MM-DD HH: MM: SS" The time date type of format indicated) TimeStamp ("YYYYMMDDHHMMSS" format expressed time date type) Year ("YYYY" format year)
4. NULL Value NULL ORACLE Modify Data Sheet Structure (1) As shown in Figure 7.19 (2) The [General Information] tab of the edit table shown in Figure 7.20 appears. (3) Other tabs are relatively simple. MySQL Modify Data Sheet Structure ALTER TABLE Statement ALTER TABLE statement is a universal statement in MySQL, which can be used to do a lot. We have seen several features (create and delete indexes, and convert the table from a storage format to another). In this section, we will introduce some other features. The full syntax of the ALTER TABLE is introduced in Appendix D. When the structure of a table is discovered, the Alter Table is very useful when the structure is not reflected. It may be desirable to record other information with this table, or it contains excess values. Or some columns are too small, or their definitions are too large, and they need to be changed to save storage space. Or the table name given when the CREATE TABLE statement is released. Waiting, such as this problem, you can use the ALTER TABLE statement to be solved. Here are some examples: You are manipulating a Web-based questionnaire, a record of each submitted questionnaire as a table. Later decided to modify this questionnaire and increase some questions. At this time, you must add some columns to the new problem. You are managing a research project. Use the Auto_Increment column to allocate the case number to study the record. You don't want the funding extension to produce more than 50,000 records, so the type of this column is unsigned smallint, which can store the maximum value of 65 535. However, the project's funding is extended, and it seems that 50 000 records may be produced. At this time, the type of the column needs to be made more than to store more parts. The size of the size may also be reverse direction. A Char (255) column may now be created, but it is now found that there is no string than 100 characters in the table. This is reduced to save storage space. The alter table is as follows: ALTER TABLE TBL_NAME Action, ... Each action represents a modification of the table. MySQL expands the ALTER TABLE statement, allowing a plurality of actions to be specified, and each action is separated by commas. This is useful for reducing the keyboard input, but this more important reason is that unless all VARCHAR columns can be changed to a Char column at the same time, it is impossible to change the table from the row-growing table. . The following example shows some of the features of the ALTER TABLE. Rename the table. This is simple; just give the old table name and new table name: ALTER TABLE TBL_NAME RENAME AS New_TBL_NAME
There is a temporary table in MySQL3.23, rename a temporary table as the name already existing in the database, will hide the original table, as long as the temporary table is hidden. This is similar to the method of hiding a table by creating a temporary table with the same name. Change column type. In order to change the type of column, you can use the Change or Modify clause. If the list in the table MY _ T B L is hoped to change it to the column of Mediumint Unsigned. This work can be done with any of the following command: alter table my_tbl modify i mediumint unsigned alter table my_tbl modify i i mediumint unsigned
Why gave a column name twice in the change command? Because Changes can do, Modify can't do it is that the column name can be changed in addition to the change type. If you want to rename J on the change type, you can do it as follows: ALTER TABLE MY_TBL CHANGE I J Mediumint Unsigned is important to name the column you want to change and indicate a complete definition of columns including column names. Even if you do not change the column name, you need to include the corresponding column name in the definition. One important reason for changing the column type is to improve the efficiency of comparing the connection queries of the two tables. At the same type of two columns, faster. If you do the following query: SELECT ... from T1, T2 Where t1.name = t2.name
If t1.name is char (10), t2.name is Char (15), and the running speed of this query does not have both of them when char (15) is fast. Then you can change T1.NAME with any of the following commands to make them the same type: ALTER TABLE T1 Modify Name Char (15) Alter Table T1 Change Name Name Char (15)
For 3. 2 3 previous mysql versions, the connected columns must be important to the same type, otherwise the index cannot be used for comparison. For versions of version 3.23 or above, the index can be used for different types, but if the type is the same, the query is still faster. Convert the table from the variable long line to a fixed length line. If there is a table chartbl has a varchar column, you want to convert it to a Char column to see what performance improvement can be obtained. (The table of fixed-length rows is generally faster than the changing table.) This table is created as follows: Create Table Chartb1 (Name Varchar (40), Modify Address Char (80)
The problem here is to change all columns at once in the same ALTER TABLE statement. It is impossible to finish a column, or this attempt will not work. If DESCRIBE Chartbl is executed, it will find that two columns remain VARCHAR columns! The reason is that if each change is changed, MySQL noticed that the table still contains a growing column, then re-converts the column that has been changed to VARCHAR to save space. In order to deal with this problem, you should change all VARCHAR columns at the same time: Alter Table Chartb1 Modify Name Char (40), Modify Address Char (80)
Now Describe will display the table containing the CHAR column. Indeed, this type of operation is important because it allows the ALTER TABLE to support multiple actions in the same statement. It should be noted here that when it is desired to convert such a table: if there is a BLOB or Text column in the table, it will fail to make the conversion table set. Even if there is only one variable column in the table, it will make the table have a long row, because these variable column types do not have a constant equivalent. Convert the table from the fixed length line to a growing row. Although ChartBL is faster, it is necessary to take more space, so determine that it is converted back to the original form to save space. This conversion is easier. Simply convert a CHAR column to a VARCHAR column, MySQL automatically converts other Char columns. To convert a ChartBL table, you can use the following statements: ALTER TABLE CHARTB1 Modify Name Varchar (40) ALTER TABLE CHARTB1 Modify Address Varchar (80) The type of conversion table. If you upgrade from MySQL3.23 version to version 3.23 or higher, there may be some old tables originally created as an ISAM table. If you want them to be Myisam format, the following: ALTER TABLE TB1_NAME TYPE = MyISAM
SQL Sever Modify Data Sheet Structure
Modifications to Enterprise Manager Select the table you want to perform in Enterprise Manager, click Right-click, from the shortcut menu, select the "Designtable" option, the modification table structure dialog shown in Figure 7-4 will appear. You can modify the data type, name, etc. of the column, name, etc., or add, delete columns, or you can specify the primary keyword constraints of the table in the dialog box shown in Figure 7-4. Click the icon in the toolbar, the dialog box that appears as the edit table and constraints shown in Figure 7-5. Various constraints and some tables can be edited.
Modify the alter table command with the alter table command to add or delete the columns, constraints, or disable or enable existing constraints or triggers. The syntax is as follows: ALTER TABLE table {[ALTER COLUMN column_name {new_data_type [(precision [, scale])] [COLLATE
Table Specifies the name of the table to be modified. If the table is not in the current database or the table does not belong to the current user, it must indicate the database name and owner name it belongs. ALTER column new_data_type Specifies the new data type name, whose use standard is as follows: The original data type of the column should be converted to a new data type; the new data type cannot be TimeStamp; the new data type allows listed as null values; if original Columns are Identity columns, then new data types should support identity features; the current SET Arithabort settings will be considered in ON. Precision specifies the number of bits of the new data type. Scale Specifies the number of fewer numbers of new data types. NULL | NOT NULL The column does not allow NULL values. If you add a column to the table, specify it to NOT NULL, you must specify the default value of this column. After selecting this, the new_data_type [(Precision [, Scale]) option must be specified, even if the Precision and Scale options are constant, the current data type needs to be indicated. WITH CHECK | WITH NOCHECK Specifies whether the data already exists in the table needs to be verified using the newly added or just enabled Foreign Key constraint or check constraint. If not specified, with check is the default option for the new add-Added constraint, with Nocheck as the default option to enable old constraints. {Add | Drop} ROWGUIDCOL adds or deletes the ROWGUIDCOL attribute of the column. The ROWGUIDCOL attribute can only be assigned to a UniqueIdentifier column. Add to add one or more columns, calculate the definition of columns or table constraints. Computed_column_expression calculates the calculation expression. Drop {[constraint] constraint_name | Column column_name} Specifies the name of the constraint or column to be deleted. Columns in the following cases cannot be deleted; columns for copying; columns for indexing; columns for Check Foreign KEY UNIQUE or PRIMARY Key constraints; define default constraints or binding a default object; Bind the column of the rule. {CHECK | NOCHECK} constraint Enables or disables Foreign Key or Check constraints. All ALL uses the Nocheck option to disable all constraints or enable all constraints using the Check option. {Enable | disable} Trigger Enables or disables triggers. All of the ALL enables or disables options for all triggers. TRIGGER_NAME Specifies the trigger name. Other parameters are the same as those in the creation table and constraints. Example 7-13: Create a monotette information table, then modify the data type of the introduction column. create table order_firm (order_firm_id char (8) primary key, firm_name varchar (50) not null firm_introduce char (50) null) on [primary] (250) null Example alter table order_firm alter column firm_introduce varchar 7-14: Create a table ordering Insert a monotor number column.
