In the system, the table is divided into four kinds: 1. system table 2. Partition table 3. Temporary table 4. User-defined data table
1.Sql CREATE TABLE default column allows null
CREATE table table name (column name 1 data type, column name 2 data type, column name 3 data type,....)
Create Table intnvarchar (255 Datetime image notnull)
2. Create a temporary table
Not saved in the DB instance, just saved in the temporary buffer
-- The local temporary table is only visible to the current user create table #test (id Span style= "COLOR: #0000ff" >int not null varchar (10
-- Global Temp Table Create Table # #test ( intnotnull, varchar( ), nvarchar())
3. Set the column identity uniqueness is not duplicated
Select Table Right-click Design Select column Identity specification, insert data without inserting column ID field value
4. Default value when inserting column values this column defaults to a value
5. Create constraints such as column values are not allowed beyond a certain range ~ can only be applied to columns
The name and condition of the column is entered in the new constraint expression
6. Modify table Information
exec ' Test ' ' Test2 ' -- using Stored procedures to modify table names -- parameter 1 Name of the original table -- parameter 2 new table name
-- Increase column modification (modify column data type) Delete column and so on use ALTER TABLE alter table Test2 add seniority int
7. Custom data types
Customizing data types based on system data types to use
8. When a rule inserts or updates data into a column of a table, it is used to limit the range of new values entered in the column ~
The difference between it and the constraint
Syntax for creating rules:
Create rule rule name expression of as rule ~ ~ ~
Ps:
Create Rule score as @value between 0 and - -- define a rule definition variable @value used to standardize the range of values
To bind to a column in a specific data table after creating a rule
Using system stored procedures to implement
-- system stored procedure sp_bindrule
-- Parameter 1 score Create a good rule name
-- parameter 2 is bound to a specific table. Column Name
sp_bindrule score,' student grades, score '
9. Dismiss and delete rules
Unbind the rule to remove!!!
-- Unbind -- parameter-specific bound columns ' student performance. Score ' -- Delete Rule Drop Rule Score
10. Data graphs better display the relationships between tables ~ ~
1. Right-click the new diagram
2. Add all the tables
3. Drag the column of the selected table to the table corresponding to the Add Relation column will pop up the Set Relationship dialog box set primary key and foreign key click OK
4.SQL Server CREATE TABLE modification table