SQL Server table and table data operations

I. Creating a table structure

 Usetest_427--Use the test_427 database to create a table operation for the databaseGOCREATE TABLEXSB--Create a XSB table(StudentnoChar(6) not NULL PRIMARY KEY,--Column name + type + can be empty, last set primary keyStudentnameChar(8) not NULL, Studentsexbit NULL DEFAULT 1,--Use default to set defaults, here set to 1Sbrithday DateNULL, StudentspecialtyChar( A)NULL, Sumcreditint NULL, Studentremarksvarchar( -)NULL --no commas at the end .)

If you do not add use test_427, the table created by default is the current database.

To create a table with computed columns

--to create a table with computed columns Usetest_427GOCREATE TABLEtest_1 (CourseIDChar(3)PRIMARY KEY,--because the primary key itself cannot be empty, this can be without null, because it is not emptySumscoreReal  not NULL,--Real is a floating-point type with an accuracy of 7 digitsSumnumberofstudentint  not NULL, Averagescore asSumscore/Sumnumberofstudent PERSISTED--using the As field, there is also a persisted, which represents the retention)

If you do not use the persisted keyword, you cannot add PRIMARY key, UNIQUE, default constraints on the computed column, because the values on the computed column are computed by the server, so you cannot assign values to computed columns when inserting or modifying data.

Second, modify the table structure

--Add columns

--add a new column to a table Usetest_427GOALTER TABLEXSB--Modify Table Xsb    ADDScholarshiptinyint NULL --increase the list of scholarshipsGO Usetest_427GOALTER TABLEtest_1ADDScholarship_1tinyint NULL,--add two columns, separated by commasScholarship_2tinyint NULLGO

--Delete columns

-- to remove a column from a table  Use test_427 GO ALTER TABLE XSB     DROP COLUMN -- Note Adding column means that the columns are deleted GO

In writing this code, it is easy to forget to add some keywords, such as column

So we can translate the literal meaning to deepen the memory and understanding, translated as follows:

using (use) database test_427 (name)

Execute now (the code above)

Modify (Alter) a table (table) whose name is Test_1

Deletes a column (column) in the table named Scholarship

Execute now (the code above)

tip: When you hit the SQL statement, you can recite this kind of Chinese in your heart, easy to remember, and not prone to mistakes and missing some keywords

--Modify Column Properties

--Modify the properties in the table, using the Alter--Change the column length of the name to 10 and change the date of birth from data to Datatime Usetest_427GOALTER TABLEXSBALTER COLUMNStudentnameChar(Ten)--use ALTER to indicate update modificationsGOALTER TABLEXSBALTER COLUMNSbirthdaydatetimeGO

--Delete Table

-- Delete a table  Use test_427 GO DROP TABLE -- Drop Delete Table

Third, Operation table-insert record

Inserting records using INSERT statements

--manipulating table data-inserting records/*Insert the following row of data into the table XSB of the database test_427:123456, daily, 1, 1994-11-21, software engineering, 60,null*/ Usetest_427GOINSERT  intoXSB--don't forget to add into    VALUES('123456','every day',1,'1994-11-21','Software Engineering', -,NULL)--The values Word is followed by S

If there is a default value in the table, you can insert the table without assigning a value to the default value.

In SQL Server, you can insert multiple records at one time, and each data is separated by commas:

INSERT  intoXSBVALUES    ('123457','Xiao Xiao',1,'1994-11-24','Software Engineering', -,NULL),--Note that this is separated by commas('123458','Ming',1,'1994-11-22','Software Engineering', -,NULL)

Generate computer professional student tables from table XSB, including number, name, and specialty, requiring the data in the new table to be set in the first 4 rows:

/*generate a Computer Professional Student table from table XSB, including number, name, and major, requiring the data in the new table to be centered on the first 4 rows. */--Create a table first Usetest_427GOCREATE TABLEXSB1 (StudentnoChar(Ten)PRIMARY KEY, StudentnameChar( A) not NULL, SpeialityChar(Ten)NULL)--Inserting DataINSERT TOP(4) intoXSB1--Top (4) here indicates that the inserted data is the first 4 data of the other table    SELECTStudentno,studentname,studentspecialty--These elements are inserted in the XSB table, as required by the topic.         fromXSB--use from to indicate which table to come from        WHEREStudentspecialty= 'Software Engineering' --constraint conditionsGO

The function of the INSERT statement above is to insert the values of the number, name, and professional names of each record in XSB that are professionally named "Software Engineering" into the rows of table XSB1.

Use the following query statement to verify the query:

SELECT *      from XSB1

Operation Result:

Additional learning notes about SQL Server

SQL Server tables and table data operations