SQL class notes--Manage tables

2017.11.09

1. Using T-SQL statements to display information about a table, call the system's stored procedures
SP_HELP (student) Table name
2. Modify the structure of the table
Add Columns:
ALTER TABLE table name
Description of the Add column list name
To delete a column:
ALTER TABLE column name
Drop Column Name
Rename Table name:
Use database name
Go
sp_rename ' old_table name ', ' new_table name ', (' object ') can be written without writing

Comments:
Single-line Comment--
Multiline Comment/* */

3.SQL server constraint mechanism
You can add constraints to a table by using the CREATE TABLE statement, or you can use the ALTER TABLE statement to add a constraint to a table that already exists
The main constraints in SQL server2008 are:
Primary Key Primary
FOREIGN key foreign key
Default Value Deflaut
Checking check

Primary key:

To add a primary key using ALTER TABLE:
ALTER TABLE table name
Add constraint constraint name
Primary key (column name [,...])

Constraint name: The name specified for the constraint
Column Name: Represents the column on which the primary key constraint is created
eg
Use Kecheng
Go
ALTER TABLE Course
Add constraint pk_ number
Primary key (School number)

FOREIGN key:
1. CREATE TABLE Grade
(StudentID char (foreign) key references student (Syudentid)
CourseID Char (8) NOT NULL,
Term nvarchar (20),
Grade tinyint check (grade between 0 and 100)
Go
2.
Primary KEY (StudentID, CNO),
Foreign KEY (StudentID) references student (StudentID)
Grammar:
ALTER TABLE name 1
Add constraint constraint name
Foreign key (column name 1)
References Column name (field name) of the associated table

eg
Use ticket reservation information Management system
Go
ALTER TABLE booking Form
Add constraint Fk_ Employee number
Foreign key (employee number) References Employee table (employee number)

Set the current table as a foreign key table, set as the foreign key is the primary key of such a table Cpno first class

Set Default values:
When you create, add the constraint default
ALTER TABLE student
Add constraint sex default ' man ' for sex

WHERE clause:
Between ...
Not between ... and ....
Between and 100 including 60 also includes 100

Check constraint:
Added at creation time
ALTER TABLE table name
Add constraint constraint name
Check (expression)

To delete a constraint:
ALTER TABLE table name
DROP CONSTRAINT constraint name

How to set a cascade delete after setting the foreign key:
Select Cascade in inset and update in foreign key

SQL class notes--Manage tables