Operation of database and table

first, the data type1, binary type: (with length limit, can put 800 0,1) Format: binary[(n)]--fixed-length n-byte binary data varbinary[(n)]--N-byte variable-length binary data2, character data format:Char[(N)]--fixed-length n character data varchar[(n)]--variable long character data with a maximum length of n is the same point: a maximum of 8kb, equivalent to 8,000 English letters or numbers, also equivalent to 4,000 Chinese characters, more than 4000 words are generally stored in the text type to distinguish char and varchar:Char（ -) and varchar ( -) Two defines a quantity of 10 length,Char（ -) will automatically fill the empty 10 positions with null, and the unused 10 spaces will not be released. and varchar ( -) is now equivalent to char (Ten), the remaining 10 spaces are automatically released, saving space. Therefore, the general use of varchar3Date Time Data format: DateTime range is 1753.1.1-1999.12. tothe smalldatetime range is 1900.1.1-2079.6.6Note: (1), date time has scope, do not exceed its scope when defined (2), define time with datetime data, and try not to use varchar () (3), date and time type length fixed4, real-data (1), fixed-point data format:decimal[(P[,s])--where P is the total, S is the number of decimal places numeric[(P[,s])] If s default, then no fractional part note: p contains the number of decimal places, the decimal point is one example: if the decimal point 2 is, after the decimal point of 4, then P=7, s=4(2), floating-point data format:float(real) [(n)]--n the number of digits used to record the mantissa of float5, currency type6, the special data type bit contains only 0 or 1, which can be used to represent true,false7, text, and image data type text: Used to handle variable-length large text data, which can store 2GB images: Used to process image data, store 2GB, database creation and management there are 3 types of SQL languages: DDL (data definition language), DML (Data Manipulation language), DCL (Data Control Language)1, Database creation: Format: Create database file name--database named Create is created meaning on [PRIMARY]--specifies that the data master file name, if not primary, is the secondary file name (name=logical_file_name, FILENAME='os_file_name', SIZE=size,--Initialize file size MAXSIZE=maxsize,--Specifies the maximum value of the file FileGrowth=growth_increment)--specify incremental log on for file growth--log file (NAME=logical_file_name, FILENAME='os_file_name', SIZE=size, MAXSIZE=maxsize, FileGrowth=growth_increment)2, modify the database alter3, delete the database. Note: The deleted database must not be in use State third, table creation and management1, creating tables create table [[Database name.] Table owner.] Table name ({column name data type} [NULL|Not NULL] {column name as column expression}--columns that can be computed from other tables are represented by column expressions [,........] ) [on {file group name|DEFAULT}]2, specifying default value format: [default constant expression] Example: CREATE TABLE STUDENT (NAME CHAR (8), Collece CHAR ( -) DEFAULT'Heilongjiang Army Day land Reclamation University'--if not complete the default is Heilongjiang Army Day Land Reclamation University)3, identity column (self-growing column) format create TABLE [[Database name.] Table owner.] Table name ({column name data type} [IDENTITY (seed value, growth value) not NULL]--The seed value (meaning is increased from a few starts) the growth value is (a few increases at a time) [...,]) Note: (1), a table can have only one column defined as the IDENTITY property. The column must be indecimal、int, Numeric, smallint, bigint, tinyint data definitions. (2), if the seed value and growth value are not defined, the default is 1 (3), the identifier column is not allowed to be null, and cannot contain the default*(4), insert into input, if there is an identity column, you can automatically omit. Example: Table student has two ID column, Name column two column, where ID is listed as identifier column, insert into student values when input ('Liu Ming'） --You only need to enter a number to automatically generate the identifier column in the table*(5), identity column with a scrap one, once used can not be used for example: If a three-row student table, id column, Name column, delete the second row name, and then add one, the ID column automatically becomes 4. If ID4 's name is found to be wrong, the ID will automatically change to 5 (6If you want to fill in the identifier column from the beginning, truncate the table name--truncate is the meaning of truncation and emptying.4, check checking constraint purpose: Mainly to prevent the appearance of identical values, can be used as a distinction. Action: Find the table you want to manipulate--> Right-click Design-Find column name to set CHECK constraint-right---Check Constraint5, defining the characteristics of the primary key primary key: (1), uniqueness. A table has only one primary key (2), not empty. You cannot enter a null value in the primary key column (3), each table should have a primary key (4), the primary key is automatically sorted by the type of the primary key: (1), typically set a primary key (2), sometimes 2 primary keys are also set, which is the combination of primary keys. The combination of the primary key means that it is the only non-repeating combination, one of which can be repeated, but it is not possible to duplicate it together. Action: Find the table you want to manipulate--> Right--Design the column you want to set--right--and set the primary key//or simply click on the column you want to set, then click the key marker at the top left6, define an outside keyword (1), foreign keys generally involve two tables: Main table and Main table from table: Table to constrain from table: Constrained table--foreign keys are generally built from tables (2), find the table you want to manipulate--right-click on the top left corner with the primary key next to the relationship key--and then select the item you want to set in the right column--First Data Relationship diagram--> Right--New diagram--Select a relationship--the key point to the main table (you can directly set the primary key, a relationship directly connected to it) Note: The INSERT and update specifications have update rules and delete rules: (1), delete rule: No action is taken--The Main table of items that have been used from a table cannot be deleted, modified, cascaded--The main table is deleted, followed by the deletion from the table. Set NULL--The primary table is deleted and becomes null from the table. Update rule: Do not take any action--The Main table of items that have been used from a table cannot update the Cascade--The main table is updated, and updates are followed from the table. Set NULL--The primary table is updated, and the table becomes null. 7, candidate key (unique non-repeating key other than the primary key) Unique constraint feature: (1), do not repeat (2), can have multiple--a UNIQUE constraint can have multiple (3), can be empty--the UNIQUE constraint can be null (4), does not have the automatic sorting function operation: Find the table to manipulate--> Right--design--Find columns to constrain--add-type Unique key8, add column ALTER TABLE name--Modify Table9, DROP table name

Operation of database and table