Shulou(Shulou.com)06/01 Report--

Add-insert statement:

Insert into table name (column name, column name)

Values (data, data)

For example, add a score of 90 for Zhang San of Class 3 to the transcript:

Insert into score sheet (name, grade, class)

Values (Zhang San, 90pr 3)

The column name may not be added, and the data without the column name will be written in the order of the fields of the table.

Delete-delete statement:

1. Delete from table name [where condition]

The where clause may not be added. If it does not add where, it means to delete the entire table directly.

For example, delete the information of students whose scores are less than 60 in the transcript:

Delete from score sheet where score 3, expression

⑴ conditional expression

Expressions in SQLServer can contain one or more of the following parameters

Constant: a symbol that represents a single specified data value. A constant consists of one or more letters, numeric characters (letters Amurz, Amurz, the number 0,9) or symbols (!, @, #, etc.). Constants of letter, date and time data types need to be enclosed in single quotation marks, while binary strings and numeric constants do not

Unary operator: an operator with only one Operand, where "+" represents a positive number, "-" represents a negative number, and "~" complement operator

Binary operator: an operator that combines two operands to perform an operation. Binary operators can be arithmetic operators, assignment operators (=), bit operators, comparison operators, logical operators, string concatenation (or concatenation) operators, or unary operators. The following figure shows the comparison operator.

Like statement wildcards:'- 'for any single character;'% 'for characters of any length; [] for characters specified in parentheses; [^] for characters that are not in parentheses

⑵ logical expression

Not: no, anti-sense with other operators

And: and, two conditions are satisfied at the same time

Or: or, two conditions satisfy one

Operation part

1. Query all the columns in the products table: select from products

2. Query the specific column (name, cost) of the products table: select name, cost from products

3. Query fruits with a cost between 1 and 5 yuan: select from products where cost between 1 and 5

4. Query the first five lines: select top 5 from products

5. Change the column name of the query result set (query the name and cost information of watermelon and display it as name and cost):

Select name as name, cost as cost from products where name = 'watermelon'

6. Sort the query results (find all fruits and sort them in descending order of cost): select from products where category = 'fruit' order by cost desc

7. Group query (query the average cost of each category and sort it by average cost): select category, avg (cost) as average cost from products group by type having avg (cost) > 20 order by avg (cost)

8. Save the query results to a new table (query all the fruits and save the results to the newproducts table): select into newproducts from products

Refresh the table

You can see the new newproducts table

9. Like clause (find out all managers): select from [employee Information Table-2] where position like'% Manager%'

10. Link in the multi-table query (look up the names and CNT scores of the students who took part in the examination from the student basic information table and the student score table, telephone number): select S. Name, S. Telephone, C.CNT from student basic information form as S inner join student grade sheet as C on S. Name = C. Name

11. Left / right outer connection: select S. Name, S. Telephone, C.CNT from student basic information form as S left/right outer join student grade sheet as C on S. Name = C. Name (take the left outer link as an example)

12. Self-connect (look up the boss name of each employee in employee Information Table 2): select Y. The name of the employee, S. 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, percentage in 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 percentage from production Table group by quarter order by quarter

14. Subquery (use select in the where clause to find students with course ID=1 and scores > 80 in the grade table and student table): select student table. Name from student form where 80

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.