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

The most important and core part of SQL language is its query function. The query statement is used to retrieve the data that already exists in the database according to a specific combination, conditional expression or order, using the select statement.

Use SELECT to query data

1. SELECT grammatical structure

The basic query format in T-SQL is a query block composed of SELECT clause, FROM clause and WHERE clause:

Select column name from table name where query qualification

Among them

* select specifies which columns of data you want to view

* from specifies which tables the data comes from

* where specifies which lines you want to view

The syntax of the SELECT statement is as follows:

Select column name / / SELECT clause: specifies the query list field. The column name format is Table. Field 1, table. Field 2, if you look up a single table, it can be abbreviated as "field 1, field 2" [into new table name] / / INTO clause: optional, store the query results in a new table from table name / / FROM clause: specify the table name of the query data [where query qualification] / / WHERE clause: optional, query condition Conditional expression or logical expression consisting of fields [group by grouping condition] / / GROUP BY clause: optional, specify the grouping condition for query results, usually a column name, but not a column alias [having grouping query qualification] / / HAVING clause: specify grouping search criteria [order by collation esc | desc] / / ORDER BY clause is usually used with the GROUP BY clause to specify how the query results are sorted. The default is ascending ESC. DESC represents

two。 Expression.

An expression is a combination of symbols and operators, and it can be evaluated to get a single data value

(1) conditional expression

Constant: a single symbol (letter, number, symbol) that specifies a data value.

Column name: the name of the column in the table

Unary operator: an operator with only one Operand (+ positive,-negative)

Binary operator: an operator that combines two operands to perform an operation, which can be an arithmetic operator, an assignment operator, a bit operator, a comparison operator, a logical operator, a string concatenation operator, and a unary operator

The SQL statement that queries the LIKE operator requires the following wildcard operators:

For example, if you are required to find the first two digits of the phone number in the employee Information Table as "13", you can write the following constraint expression

(2) logical expression

3. Query examples

(1) query all the information of students with scores of 90-100 in the student table

Select * from student where score between 90 and 100

(2) query the information of students with scores below 90 or above 95 in student table

Select * from student where score 95

(3) query the information of students with scores of 89, 90 and 91 in student table

Select * from student where score in (89 ~ 90 ~ 91)

(4) query the information of all students surnamed Liu in student table

Select * from student where name like 'Liu%'

(5) query the data of the first five rows in the student table

Select top 5 * from student

(6) query all the student information in the student table and display the query results according to the scores from high to low.

Select * from student order by score desc

4. Grouping query

Grouping query is to classify and combine the data in the table according to certain conditions, and get the statistical information according to the need, which can be realized through the group by clause.

In a group by clause query, the column name specified by select is either the column specified in the group by clause or an aggregate function. Aggregate functions are used to calculate a set of values and return a single value, such as summation, maximum, minimum, average, and so on. The common aggregate functions in T-SQL are SUM (), AVG (), MAX (), MIN (), COUNT () and so on.

(1) query the total scores of all students in the student table, and the list name is displayed as "Total scores".

Select SUM (grade) as total score from student

(2) query the average scores of all students in the student table

Select AVG (grade) as grade point average from student

(3) query the highest and lowest scores of all students in the student table

Select MAX (grade) as highest score MIN (grade) as lowest score from student

(4) query the number of rows in the student table

Total rows of select COUNT (*) as from student

(5) query the total scores of each class in the student table

Select SUM (grade) as total score from student group by class

(6) query the classes with a total score of more than 200 in the student table

Select SUM (grade) as total score from student group by class having SUM (grade) > 200

(7) Save the student information of Class 2 in student table to table student_new.

Select * into student_new from student where Class = 2

5. Subquery

A subquery is a query that is nested in a query.

Case 1: query which student has the highest or lowest score in the student table

Select name, grade

From student

Where score = (select MAX (grade) from student) or score = (select MIN (grade) from student)

Case 2: query the quantity and percentage of production per quarter

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

Using T-SQL to realize multi-table query

The previous queries are single-table queries. If a query needs to operate on multiple tables, it is called a join query, which queries the data through the association of common columns between the tables.

1. Internal connection (INNER JOIN)

Inner join (INNER JOIN) is the most commonly used connection method, showing only the collection portion of the data in the two tables

Case: use inner joins in tables An and B to query students' names, schools and occupations

* method 1: specify the connection condition in the where clause, as follows

Select A.name name A Magi A. school A Magi B.name name B Magi B. job occupation B

From A,B

Where A.name=B.name

* implementation method 2: use inner join in the from clause. To implement the on clause, write as follows

Select A.name name A Magi A. school A Magi B.name name B Magi B. job occupation B

From An inner join B on A.name=B.name

two。 External connection

The outer join is the extension of the inner join. In addition to joining the duplicate parts of the data in the two tables, you can also require that all the data on the left or right side be displayed.

(1) left external connection (LEFT JOIN)

The result set of the left outer join includes all rows of the left table

Case: use the left outer connection to query the student's name, school and occupation in tables An and B.

Select A.name name A Magi A. school A Magi B.name name B Magi B. job occupation B

From A left join B on A.name=B.name

(2) right external connection (RIGHT JOIN)

The right outer join is the reverse connection of the left outer connection, and its result set includes all rows on the right.

Case: use the right outer connection in tables An and B to query students' names, schools and occupations.

Select A.name name A Magi A. school A Magi B.name name B Magi B. job occupation B

From A right join B on A.name=B.name

3) fully connected (FULL JOIN)

Full join right into a complete outer join, including all rows in the left and right table

Case study: use full external connections in tables An and B to query students' names, schools and occupations

Select A.name name A Magi A. school A Magi B.name name B Magi B. job occupation B

From A full join B on A.name=B.name

3. Merge two result sets

Case: merge the results of two tables into one result display

SELECT name, student number, class, nationality

FROM Class two

UNION

Select surname +''+ first name, student number, class, nationality

Class one of from

Order by class desc, student number asc

4. Self-connection

Case: find the name of each employee's boss

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

5. Cross connection

Cross-join means that there is no connection between the tables, and each row of the left table and the right table is combined one by one, which is equivalent to multiplying the two tables.

SELECT T. Name of teacher, C. Course name

From faculty basic information table AS T cross join course schedule AS C

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.