SQL Server additions and deletions change

--use used to set which database is currently in use
Use studentdb
--go Batch processing
Go

Case-insensitive in--t-sql, data in database tables is case-sensitive
--for example: INSERT and insert are not case-sensitive, data in database tables Lisi and Lisi are case-sensitive

---------------------insert Operation-------------

-a case with an identity column (the identity column cannot be manipulated because the identity column is System-managed)
--1) How to write field names
INSERT INTO Deptinfo (Deptname,remark) VALUES (' Teaching department ', ' ABC ')
INSERT INTO Deptinfo (Deptname,remark) VALUES (' Business unit ', ' handling business work ')
--2) How to omit field names
INSERT into deptinfo values (' academic department ', ' teaching ')
INSERT into deptinfo values (' teaching ')-error (The data value does not match the field defined by the table)

--Insert columns with default values
--1) using the default keyword
INSERT into Deptinfo values (default, ' aaabbb ')--default represent defaults
--2) does not handle default values
INSERT INTO Deptinfo (Remark) VALUES (' teaching ')

--Insert null value (SQL database table data using NULL means NULL)
--1) directly with null
INSERT into Deptinfo values (' Foreign Office ', null)
--2) do not operate on fields that can be empty
INSERT INTO Deptinfo (deptname) VALUES (' Sports department ')

--Insert unique values
INSERT into deptinfo values (' academic department ', ' AAAA ')--error (because the department Name column already exists ' academic Department ')

--Add action to note:
--1, add the data must match with the field (number, order, data type ...)
--2, note the corresponding features in the table (primary key, non-null, check constraint, default value, foreign key ...)

--Add multiple rows of data at once (cannot use default)
INSERT INTO Deptinfo (Deptname,remark)
Select ' Department 4 ', ' AAA ' union All
Select ' Department 5 ', ' BBB ' union All
Select ' Department 5 ', ' BBB '
--union does not add duplicate rows, union All can add duplicate rows

-------------Update Operation----------------
--Modify the data in the entire table
Update deptinfo set remark= ' AAA '
--Modify the qualifying data in the table
Update deptinfo set remark= ' where deptid=6 ' teaching
Update deptinfo set remark= ' where deptid>8 ' teaching
--Modify data for multiple fields in a table (multiple fields separated by commas)
Update deptinfo Set deptname= ' academic department ', remark= ' teaching ' where deptid=1
--Use update to be aware of:
--Be sure to consider carefully if you want to add a where condition, and modify the data for all rows in the table without conditions

----------------The delete operation----------
--delete data based on criteria
Delete from Deptinfo where deptid=6
--delete data from the entire table
Delete from Deptinfo

----------------Delete and truncate-------
--common denominator: You can delete data from a table
TRUNCATE TABLE Deptinfo
--Different points:
--1, truncate efficiency is higher than delete
--2, TRUNCATE operations are not logged to the log file, but the delete operation is logged to the log file
--3, truncate after deleting data in the table, the identity column starts at 1, and the value of the identity column continues to increment on the previous basis after delete deletes the data in the table.
--4, truncate can only delete all the data in the table, delete can remove all data or you can delete the eligible data according to the criteria

INSERT INTO Scoreinfo (Stuname,subject,score) VALUES (' Zhang Wei ', ' JAVA ', +)
Update scoreinfo set score=score+5 where socre<60

SQL Server additions and deletions change