SQL Server projection query, select query

--Simple query

--Projection Query

/*

Simple Query Keyword Description:

All: Specifies that all records are displayed, including duplicate rows. All is the default setting.

Distinct: Specifies that all records are displayed, but does not include duplicate rows.

Top n [percent]: Specifies the first n rows to be returned from the result, or the previous n% data record

*: Indicates all records

*/

--Select the specified column in a table

--Check the student's table "name", "Age"

Select Sname, Sage from Student

--Query all records in the student table

SELECT * FROM Student-represents all columns with *

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

Select distinct sname from Student

/*

Use the DISTINCT keyword to filter out duplicate rows in query results

*/

--Query the first three rows in the student table

Select Top 3 * from Student

/*

Top n allows you to specify the first n rows of records in a query table

*/

--Check the top 50% records in the student table

Select top percent * from Student

/*

Top N percent can be used to specify records for the previous n% in the query table

*/

--Modify the column headings in the query results

/*

Common ways to modify query column names

Method One: Give the column name directly after the expression

Method Two: Join the column expression and the specified column name with the AS keyword

*/

--The query results in the sname to "name", Sno changed to "School Number", Sage changed to "age"

--Method One:

Select Sno number, sname name, Sage age from Student

--Method Two:

Select Sno as number, sname as name, Sage as age from Student

--Computed column values

Select 100-sage as Life from Student

--Select query

/*

Select query Syntax:

Select select_list from Table_list

where search_condition

There are a number of statements that can be used as conditional expressions

are relational expressions, logical expressions, between statements, in statements, like statements, are [NOT] NULL statements, compound statements, respectively.

*/

--use "relational expression" as a query condition ...

SELECT * FROM SC

--check all records with a score greater than or equal to 90 points

SELECT * from SC Student where grade>=90

--use "logical expression" as a query condition ...

/*

Logical Expressions in SQL:

Not: Non-

And: With

Or: OR

*/

SELECT * FROM Student

--Check the student's table for male students aged 19 years

SELECT * from Student where Sage = Ssex = ' Male '

--Check the student's table for students ages 19 or 20 years old

SELECT * from Student where sage = ' or Sage = 20

--Check the student's table for students who are not 19 years of age

SELECT * from Student where not Sage = 19

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

/*

Between syntax:

expression [NOT] between expression 1 and expression 2

Use the between keyword to easily control the range of query result data

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

*/

--Query for all ages "greater than equals" 18 years old and "less than equal" 20 year old students

SELECT * from Student where Sage between 20

--Check all students who are not 18-19 years of age

SELECT * from Student where Sage is not between 19

--use in (belonging to) keywords as conditional expressions ....

/*

As with the between keyword, the in keyword is introduced to make it easier to limit the range of data retrieved

*/

/*

The syntax for the IN keyword is as follows:

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

*/

SELECT * FROM Student

--Check all students aged 18 and 19 years of age

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

--Use the LIKE keyword statement as a conditional statement ...

/*

The LIKE keyword searches for a string that matches a specified pattern

*/

/*

Introduction to wildcard characters:

%: Any string consisting of 0 or more characters

_: Any single character

[]: A single character in the specified range, [] can be a single character (such as [ACEF]), or a range of characters (such as [a-f])

[^]: a single character that is not in the specified range, [^] can be a single character (such as [^ABEF]), or it can be a character range [^a-f]

*/

/*

Examples of wildcard characters

Like ' ab% ' returns any string starting with AB

Like ' ab% ' returns any string at the beginning of Ab

Like '%abc ' returns an arbitrary string ending with ABC

Like '%abc% ' returns any string containing ABC

Like ' _ab ' returns any three-character string at the end of AB

Like ' [ack]% ' returns any string starting with a, C, or K

Like ' [A-t]ing ' returns a string of four characters, ending with ING, whose first letter ranges from a to T

Like ' m[^c]% ' returns a string that starts with M and the second character is not a C

*/

SELECT * FROM Student

--Check all the students surnamed Wang

SELECT * from Student where sname like ' King% '

Insert into Student (sno,sname,sage,ssex,sdept)

VALUES (' 008 ', ' Zhang Si ', 20, ' Male ', ' SC ')

--Search all names with four words of students

SELECT * from Student where sname like '% four '

//

--Use ISNULL (whether < not > empty) query ...

/*

Note: You cannot use comparison operators to control judgments in a where statement, and you can use NULL expressions only to determine whether an expression is a null value

The syntax is as follows:

Expression is null

Or

expression is not null

*/

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

SELECT * from Student where sname isn't null

--use compound criteria to query ......

/*

When using compound statements, you need to combine multiple conditional statements using logical operators

and

Or

Not

Each individual conditional statement can be enclosed with () parentheses

*/

Aggregate functions (for processing results of record data)

--aggregate function (for processing results of record data)

/*

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

Note: These functions handle a collection of data, not a single row of records

*/

/*

SUM () returns the sum of a numeric column or computed column

AVG () returns the average of a box of numeric columns or computed columns

MIN () returns the minimum value

Max () returns the maximum value

COUNT () returns the number of items in a data column

COUNT (*) returns the number of rows found

*/

SELECT * FROM SC

--to find the average of the results in SC table

Select AVG (Grade) as score average from SC

--To find the sum of the achievements in SC table

Select SUM (Grade) as score average from SC

--Find the number of items in SC table

Select COUNT (Grade) as record bar from SC

Select COUNT (*) as record bar from SC