SQL server projection query and selection Query

-- Simple query

-- Projection Query

/*

Simple query keyword description:

All: Specify to display all records, including duplicate rows. All is the default setting.

Distinct: displays all records, but does not include duplicate rows.

Top n [percent]: Specifies to return the first n rows or the first n % of data records from the result.

*: Indicates all records.

*/

-- Select the specified column in a table

-- Query "name" and "Age" in the student table"

Select Sname, Sage from Student

-- Query all records in the student table

Select * from Student -- use * to represent all columns

-- Query the names of all students in the student table and remove duplicate rows.

Select distinct Sname from Student

/*

You can use the distinct keyword to filter out duplicate rows in the query results.

*/

-- Query the records of the first three rows in the student table

Select top 3 * from Student

/*

You can use top n to specify the first n rows of records in the query table.

*/

-- Query the first 50% records in the student table

Select top 50 percent * from Student

/*

You can use top n percent to specify the top n % records in the query table.

*/

-- Modify the column title in the query result

/*

Common Methods for modifying column names

Method 1: directly give the column name after the column expression

Method 2: Use the as keyword to connect the column expression with the specified column name

*/

-- Change the Sname in the query result to "name", Sno to "student ID", and Sage to "age"

-- Method 1:

Select Sno Student ID, Sname name, Sage age from Student

-- Method 2:

Select Sno as Student ID, Sname as name, Sage as age from Student

-- Calculate the column Value

Select 100-Sage as life from Student

-- SELECT query

/*

SELECT query syntax:

Select select_list from table_list

Where search_condition

// Multiple statements can be used as conditional expressions.

They are relational expressions, logical expressions, between statements, in statements, like statements, is [not] null statements, and compound statements"

*/

-- Use "relational expression" as the query condition ......

Select * from SC

-- Query all records with scores greater than or equal to 90

Select * from SC Student where Grade> = 90

-- Use "logical expression" as the query condition ......

/*

Logical expressions in SQL:

Not: non

And: corresponds

Or: or

*/

Select * from Student

-- Query the student table for a 19-year-old male student.

Select * from Student where Sage = 19 and Ssex = 'male'

-- Query the student whose age is 19 or 20 in the student table.

Select * from Student where Sage = 19 or Sage = 20

-- Query the student whose age is not 19 in the student table

Select * from Student where not Sage = 19

-- Use the key between as the query condition .......

/*

Between Syntax:

Expression [not] between Expression 1 and expression 2

You can use the between keyword to conveniently control the range of query result data.

Note that using between to form a search range is a "closed interval"

*/

-- Query all students aged 18 and 20

Select * from Student where Sage between 18 and 20

-- Query all students aged between 18 and 19

Select * from Student where Sage not between 18 and 19

-- Use the keyword "in" as the condition expression ...........

/*

Like the between keyword, The in keyword is introduced to more easily limit the scope of data retrieval.

*/

/*

The syntax of the in keyword is as follows:

Expression [not] in (expression 1, expression 2 ,...)

*/

Select * from Student

-- Query all students aged 18 and 19

Select * from Student where Sage in (18, 19)

-- Use the like keyword statement as a condition statement ......

/*

Like keyword searches for strings matching the specified pattern

*/

/*

Wildcard introduction:

%: Any string containing zero or multiple characters

_: Any single character

[]: Represents a single character in the specified range. [] can be a single character (for example, [acef]) or a character range (for example, [a-f]).

[^]: Indicates a single character that is not within the specified range. [^] can be a single character (for example, [^ abef]) or a [^ a-f] character range.

*/

/*

Wildcard example

Like 'AB %' returns any string starting with AB

Like 'AB %' returns any string starting with AB.

Like '% abc' returns any string ending with abc

Like '% abc %' returns any string containing abc

Like '_ AB' returns a string of any three characters ending with AB.

Like '[ACK] %' returns any string starting with A, C, or K

Like '[A-T] in' returns A four-character string ending with ing, whose first letter ranges from A to T

Like 'M [^ c] %' returns a string of any length starting with M and the second character is not c.

*/

Select * from Student

-- Query all students surnamed Wang

Select * from Student where Sname like 'wang %'

Insert into Student (Sno, Sname, Sage, Ssex, Sdept)

Values ('008 ', 'zhangsi', 20, 'mal', 'scs ')

-- Query all students whose names contain four characters

Select * from Student where Sname like '% 4%'

//

-- Use isnull (whether or not <NO> is null) to query ......

/*

Note: Comparison operators cannot be used in where statements to control and determine whether an expression is null.

Syntax:

Expression is null

Or

Expression is not null

*/

-- Query all students whose names are empty .........

Select * from Student where Sname is not null

-- Query Using Compound conditions ..............

/*

When using compound statements, you must use logical operators to merge multiple condition statements.

And

Or

Not

Each separate condition statement can be enclosed in parentheses ().

*/

// Aggregate function (calculate the processing result of record data)

-- Aggregate function (calculate the processing result of record data)

/*

Aggregate functions are some column functions that have been defined in SQL Server.

Note: These functions process a data set instead of a single row of records.

*/

/*

Sum () returns the sum of a numeric column or computed column.

Avg () returns a box of numeric columns or the average value of the calculated column.

Min () returns the minimum value.

Max () returns the maximum value.

Count () returns the number of data items in a data column.

Count (*) returns the number of rows found.

*/

Select * from SC

-- Calculate the average value of the result in the SC table.

Select AVG (Grade) as average score from SC

-- Calculate the sum of the result in the SC table.

Select sum (Grade) as average score from SC

-- Calculate the number of items in the SC table

Select count (Grade) as number of records from SC

Select COUNT (*) as number of records from SC