SQL Server's T-SQL Add, delete, change, check

Increment-INSERT statement:
Insert into table name (column name, column name, column name)
Values (data, data, data)
For example, add Class 3 to the Zhang San score of 90 to the score table:
INSERT INTO score table (name, Grade, Class)
Values (Zhang San, 90, 3)
• Column names can be added without the column name data will follow the table's field order write data
Delete-Remove statement:
1. Delete from table name [where condition]
The WHERE clause can be added without adding a where meaning is to delete the entire table directly
For example, delete student information with grades less than 60 in the score table:
Delete from score table where score <60
2. TRUNCATE TABLE name
• Meaning is to delete the entire table directly
3. The difference between truancate table and delete:
truncate TABLE is not followed by WHERE clause, while delete can where, conditionally delete data
TRUNCATE TABLE does not log transaction logs, so data cannot be recovered after deletion, and delete is reversed
The identifier column is reset after the truncate table is deleted, and the DELETE statement does not
TRUNCATE TABLE cannot be used for tables that have a FOREIGN key constraint reference.
Change-UPDATE statement:
Update table name set column name = update value [WHERE condition]
WHERE clause does not update all data
For example, add two points to Zhang San's score in the score table:
Update score Table Set score = Score +2 where name = ' Zhang San '
Check select-query statement:
1. Select Column name from table name where condition
2. SELECT statement Parameters:
into: Save query results to a new table
from: Follow the table you want to query
Where: Query criteria
GROUP BY: Group Query
having: Used with group BY, query criteria
ORDER BY: Sorts the results of the query; DESC is descending, ASC is ascending, no default ascending br/>3, expression
⑴ conditional expression
An expression in SQL Server can contain one or more of the following parameters
• Constants: Symbols that represent a single specified data value. A constant consists of one or more letters, numeric characters (Letters A-Z, a-Z, numeric 0~9), or symbols (!, @, #等). Letters, date and time data type constants need to be enclosed in single quotes, and binary strings and numeric constants do not require
• Unary operator: an operator with only one operand, where "+" represents a positive number, "-" represents a negative number, and a "~" Complement operator
• Two-tuple operator: an operator that performs an operation by combining two operands. A binary operator can be an arithmetic operator, an assignment operator (=), a bitwise operator, a comparison operator, a logical operator, a string concatenation (or concatenation) operator (+), or a unary operator. is the comparison operator.

like statement wildcard: '-' denotes any single character; '% ' character of any length; [] denotes the character specified in parentheses; [^] denotes a character that is not in parentheses
⑵-Logical expression
Not: No, it is reversed with other operators
And: And, two conditions simultaneously satisfy
Or: Or, two conditions satisfy a

Operation section
1. Query all columns in the Products table: SelectFrom Products

2, query the Products table of the specific column (name, cost): Select name, cost from product

3, the query cost between 1 to 5 yuan of fruit: selectFrom products where cost between 1 and 5

4, query the first 5 lines: select Top 5From Products

5. Change the column name of the query result set (query the name and cost information of the watermelon and display it as name and costs):
Select name as name, cost as price from product where name = ' Watermelon '

6. Sort the results of the query (find all the fruits and sort by the descending cost): SelectFrom products where kind = ' fruit ' ORDER by cost Desc

7, packet query (query the average cost of various classes and sort by average cost cost): Select Type, AVG (cost) as average cost from the products group by kind having avg (cost) >20 ORDER by AVG (COST)

8. Save the query results in a new table (query all fruits and save the results to the NewProducts table): Selectinto NewProducts from Products

Refresh the table

You can see the new newproducts table

9. Like clause (query out all managers): SelectFrom [Employee information sheet -2] where position like '% manager% '

10, multi-table query within the connection (from the student's basic information table and the Student score table to find the student's name and CNT scores, telephone): select S. name, S. Phone, c.cnt from Student basic information table as s inner JOIN student score table as C on S. Name =c. Name

11. Left/Right outer connection: select S. name, S. Telephone, c.cnt from Student basic information table as S left/right outer join Student score table as C on S. Name =c. Name (take left outer join as an example)

12. Self-connect (Find the boss name of each employee in Employee Information table 2): Select Y. Employee Name as boss name from employee information table as Y INNER JOIN employee Information table as S on Y. Boss Id=s. Employee ID

13. Subquery (Find the production quantity, percent of each quarter in the Production table): Select Quarter, sum (production quantity) as production quantity per quarter, str ((SUM (production quantity)/(select sum (production quantity) from production table) *100) + '% ' as Percent from Production table group by quarter order by quarter

14. Subquery (use SELECT in the WHERE clause to find courses in the score table and student tables Id=1 and 〉80 students): Select Student table. Name from student table where 80< (select score from score table where student table. Student Id= score table. Student ID and score table. Course id=1)

SQL Server's T-SQL Add, delete, change, check