T-SQL query statements

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

Querying data with Select

1. SELECT syntax structure

The query base format in T-SQL is a query block consisting of a SELECT clause, a FROM clause, a WHERE clause:

Select Column name from table name where query qualification

which

* Select specifies which columns of data you want to view

* from specifies which tables the data comes from

* where specifies which rows you want to see

The syntax for the SELECT statement is as follows:

select   Column Name                         //select clause: Specifies the query list field, with the column name formatted as "table." Field 1, table. Field 2 ", if you look up a single table, you can shorthand for" field 1, Field 2 "[ into   New Table name]                   //into clause: Optional, store query results in a new table from   table name                        //FROM clause: Specifies the table name of the query data [where    Query Qualification]            //where clause: Optional, query condition, A conditional expression or logical expression consisting of a field [group by   a grouping condition]                //group by clause: Optional, specifies the grouping criteria for the query results, usually a column name, but not the alias of the column [having    Group Query Qualification]          //having clause: Specifies grouping search conditions, usually with group  The BY clause uses the [ORDER BY   Sort Rule esc|desc]                 //order by clause: Specifies how the query results are sorted by default to ascending Esc;desc representation

2. Expressions

Expressions are a combination of symbols and operators, and can be evaluated to a single data value

(1) Conditional expression

Constants: Symbols for a single specified data value (letters, numbers, symbols)

Column name: The names of the columns in the table

Unary operators: Operators with only one operand (+ positive, negative)

Binary operator: An operator that performs an operation by combining two operands, which can be an arithmetic operator, an assignment operator, a bitwise operator, a comparison operator, a logical operator, a string concatenation operator, a unary operator

The SQL statement that queries the LIKE operator needs to use the following wildcard operator:

For example, if you want 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 information of students with scores of 90-100 in the student table

SELECT * from student where score between and 100

(2) Query the student table for grades less than 90 or higher than 95 student information

SELECT * FROM student where score <90 or score >95

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

SELECT * from student where score in (89,90,91)

(4) Check the information of all students surnamed Liu in the student form

SELECT * FROM student where name like ' Liu% '

(5) Querying the first 5 rows of data in the student table

Select Top 5 * from student

(6) Query the student table for all student information, according to the score from high to low display query results

SELECT * FROM student ORDER BY score Desc

4. Group queries

The group query is the combination of the data in the table according to certain conditions, and the statistic information can be achieved by 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. An aggregate function is used to evaluate a set of values and return a single value, such as SUM, Maximum, minimum, average, and so on. Common aggregation functions in T-SQL are sum (), AVG (), MAX (), MIN (), COUNT (), and so on

(1) The total scores of all students in the student table are queried, and the list name is "Total"

Select SUM (Score) as total from student

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

Select AVG (score) as average score from student

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

Select MAX (Score) as highest min (score) as lowest score from student

(4) Querying all rows in the student table

Select COUNT (*) as total rows from student

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

Select SUM (Score) as total from student group by class

(6) Query the student table for a total of more than 200 of the class

Select SUM (scores) as total score from student group by class having SUM (score) >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. Sub-query

Subqueries are queries that are nested in queries

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

Select Name, Score

From student

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

Case 2: Query the production quantity per quarter, percent

Select Quarter , sum ( production quantity ) as the number of production 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

6. Merging two result sets

Implementing multi-table queries using T-SQL

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

1. Internal connection (INNER join)

An inner join (INNER join) is the most common way to connect, showing only the collection portion of the data in two tables

Case Study: use inner joins in table A and table B to find student names, schools, occupations

* Implementation Method One: Specify the connection condition in the WHERE clause, as follows

Select A.name Name A,a.school School a,b.name name B,b.job occupation B

From a A B

where A.name=b.name

* Implementation method Two: In the FROM clause using the INNER JOIN ... on clause to implement, the wording is as follows

Select A.name Name A,a.school School a,b.name name B,b.job occupation B

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

2. External connection

An outer join is an extension of an internal connection, which, in addition to connecting the data repeats in two tables, can also require that all data on the left or right be displayed

(1) LEFT OUTER JOIN

The result set for left outer joins includes all rows of the left table

case: using left outer joins in table A and Table B query student name, school, occupation

Select A.name Name A,a.school School a,b.name name B,b.job occupation B

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

(2) Right outer join

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

Case: use right outer joins in table A and table B to query student names, schools, occupations

Select A.name Name A,a.school School a,b.name name B,b.job occupation B

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

(3) fully connected (full join)

Full connection right into full outer joins, including left table and all rows in the right table

Case: use full outer joins in tables A and B to query student names, schools, occupations

Select A.name Name A,a.school School a,b.name name B,b.job occupation B

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

3. Merging two result sets

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

SELECT name , School Number , class , nationality

From Class II

UNION

Select Surname + "+ name , School Number , class , nationality

From First Class

Order by class desc, study number ASC

4. Self-connect

Case: Find the boss name of each employee

SELECT Y. employee name, 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-connect

Cross join is that there is no connection between the table, the left table and the right table each row one by one combination, the equivalent of two tables multiplied

SELECT T. name of teacher , C. Course Name

From Instructor Basic information table as T Cross join curriculum as C

T-SQL query statements