Adding SQL Server data to modify deletes and queries

Data additions:

First set up a database, click New Query, and then create a table in code, the list of names and data types, constraints can be added without

Then use the INSERT statement to add data to the table

Insert [into] Table name (column name 1, column name 2, column name 3 ...). Column name N)

VALUES (' column value 1 ', ' column value 2 ', ' column value 3 ',...... ' column value n ')

Note: column values need to be enclosed in single quotes, [] into can be written in parentheses

Changes to the data:

modifying data by using the UPDATE statement

Format: Update table name set column name = ' expression ' WHERE condition expression

Example: 1:

If you do not write a where conditional sentence will be all numbers are b001 column of the author and publishing house all changed to Zhang San and Tsinghua University Press

Example 2: Reducing the orange stock 20

Example 3: Change the Apple inventory to 30

Example 4: Change the price of peach to 5.20, production to Feicheng, inventory to 50

Deletion of data:

Format: delete [from] table name [WHERE condition expression]

This will erase all data that is numbered b007.

Query for data:

Format: Select column name from table name where * (asterisk) represents all columns

Cases:

Eliminate duplicate rows: Distinct column name

Cases:

WHERE clause: This clause is used to determine the query criteria, and the matching operators are as follows:

%-----percent sign denotes any number of characters and can be used as a prefix or as a suffix

_------Underline a character that can be used as a prefix or suffix

Example 1: Find the car codenamed B003.

Example 2: Find a car with a price greater than 500,000

Example 3: Find a car with a price greater than 300,000 less than 500,000 and find a car with a price greater than 300,000 fuel consumption less than 5

You can also use between and to represent a car between two conditions, such as a car greater than 300,000 less than 500,000 can also be written like this

Example 4: Fuzzy query query out Audi opening car, "type" end of the car, car name contains the number 5 car, car name The third word is the number 5 car

Represents a statement that can display several messages at a time

Indicates that 10 messages can be displayed at a time

Example: Find out the displacement of the car between 400,000 to 500,000

ORDER BY clause: ORDER BY column name

NOTE: ASC arranges desc in descending order by ascending

Example 1: Sort by car price in ascending order

Example 2: According to the car's fuel consumption in descending order, the same as fuel consumption, by car price descending order

Note: If a table has the price of the fruit and the inventory of the two columns, but there is no cost this column, to calculate the cost of fruit in this table, use this table

Unit Price Times Inventory is the cost of fruit

If you want to change the name of a column in a table

Adding SQL Server data to modify deletes and queries