SQL Server table Data simple operation (table data query)

--Table data query--
-- basic query of data --

-- simple query of data --
SELECT * | field name [, field Name 2, ...] from data table name [WHERE condition expression]

Cases:
Use Commodity Management database
Go
SELECT * From commodity information table
Select product number, product name, origin from product information Form
SELELCT * FROM product information sheet where origin = ' Shenyang, Liaoning '

-- keyword-assisted query --
--1)DISTINCT keyword (used to eliminate duplicate rows in query results, immediately after the Select command)--
SELECT DISTINCT * | field name [, field Name 2, ...] from data table name [WHERE condition expression]

Cases:
Use Commodity Management database
Go
Select distinct origin from product information Form

--2)top keyword (used to find top N or previous n% records in results, usage: top n | n percent, immediately after the Select command)--
Select Top N | N percent * | field name [, field Name 2, ...] from data table name [WHERE condition expression]

Cases:
Use Commodity Management database
Go
Select Top 3 * from commodity information table-first 3 records of query results
Select top percent * from product information table-30% Records of query results

--3)Between...and ... Keywords (used to find records in a range of results, used in the Where, as filter criteria)--
field name [not] between low value and high value--plus "not" means to meet between...and ... Search results for keywords inverse value

Cases:
Use Commodity Management database
Go
SELELCT * from inventory information table where in stock quantity between and 200--Query the "inventory quantity" record in the inventory information table from 100 to 200

--4)inkeyword (used to find a record with a specified value, put it behind where, as a filter)--
field name [NOT] int (value 1, value 2, value 3,...) --Add "not" to negate the lookup result that satisfies the in keyword

Cases:
Use Commodity Management database
Go
SELELCT * from inventory information table where in stock quantity in (100,200)--Check the "inventory quantity" in the Inventory information table for a record of 100 or 200

--5)likekeyword (used to implement a certain range of fuzzy queries, mainly for the character field, when used in the back, as a filter)--
field name [not] like ' < character expression > ' --plus ' not ' means to negate the lookup result that satisfies the LIKE keyword

Cases:
Use Commodity Management database
Go
SELECT * FROM Customer Information table where customer name like ' _ Small% '--check the "Customer name" in the "Customer information sheet" to meet the filter criteria ' _ Small% ' record

---6)innull keyword (used to query whether a field contains null values, which is used as a condition filter)--
field name is null --is cannot be replaced with "=", null cannot be replaced with "0" or a space

Cases:
Use Commodity Management database
Go
SELELCT * from Customer Information table where mailbox is null

--statistical query of data--
--aggregate function Query--
Count (* | field name)--Total number of data in the statistics table
sum (expression | field name)--The calculation of the data in an expression or field nameand the, the data type requirement for an expression or field name isNumeric Type
avg (expression | field name)--The calculation of the data in an expression or field nameAverage, the data type requirement for an expression or field name isNumeric Type
max (expression | field name)--To find the data in an expression or field name.Maximum Value, the data type of an expression or field name can beNumeric Type、Character TypeOrDate-Time type
min (expression | field name)--To find the data in an expression or field name.Minimum Value, the data type of an expression or field name can beNumeric Type、Character TypeOrDate-Time type
--3 ways to rename a query result:--
original field name ' New field name '
original field name as ' new field name '
' new field name ' = Original field name

Cases:
Use Commodity Management database
Go
Select COUNT (*), SUM (quantity purchased), AVG (purchase amount), MAX (incoming amount), MIN (purchase amount) from Arrival information table
Select COUNT (*) as ' total Record ', SUM (quantity of incoming goods) as ' stock quantity and ', AVG (purchase amount) ' Average of purchase amount ', max (purchase amount) ' Maximum value of purchase amount ', ' minimum value of incoming amount ' =min (purchase amount) from inbound information table

-- query result ordering (the ORDER BY statement is used to implement a sort operation, which can appear after the from or where statement)--
Order BY field name 1 [, field Name 2, ...] [ASC | desc] --plus ASC for ascending, plus desc for descending, default ascending, keyword ASC can omit

Cases:
Use Commodity Management database
Go
Select*from Purchase Information table order by incoming Quantity desc--Query all fields in the incoming information table and sort the incoming quantity in descending order

--Subtotal query result grouping --
--1)GROUP By ... Statement --can appear after a from statement or a where statement when used
Group By field Name list [having conditional expression] --"Field Name list" means grouping by that field. Typically, an aggregate function is followed by a SELECT statement to represent the evaluation subtotal, and the field name after the SELECT statement is either placed in an aggregate function or placed in a group by statement, otherwise an error occurs. Having to make further conditional filtering on the results, add aggregation functions

Cases:
Use Commodity Management database
Go
Select Arrival date, sum (quantity of goods) as ' stock quantity and ', Sum (purchase amount) as ' incoming amount and ' From Inbound information table group by date of purchase
Select product number, sum (quantity of goods) as ' quantity of goods and ' from incoming Information table group by product number having SUM (quantity of goods) >30

--2)compute...by ... Statement --must be placed in order...by when used ... Statements are used together, which means sorting (grouping) first, then subtotal
Compute aggregation Function list by Group field name --compute...by ... The Group field after the by in the statement must be associated with the order...by ... The sort field after by in the statement is the same

Cases:
Use Commodity Management database
Go
SELECT * From incoming information table order by arrival date compute sum (quantity purchased), SUM (purchase amount) by arrival date

-- advanced query of Data --

-- multi-table connection query --
-- Cartesian product query
SELECT * from a B--a,b to two sheets
-- Conditional connection (multi-table connection)
The list of select field names from Table 1, table 2, table 3,... where table 1. public fields = Table 2. Public fields and table 2. Public fields = Table 3. --select followed by the queried field name, there is a public field to indicate is the data table. Be sure to have a where condition, the public field of the table and table is the connection medium, usually the connection query of n tables has at least n-1 connection condition support

Cases:
Use Commodity Management database
Go
Select customer Information table. Customer number, customer name, product number, sales date from customer information sheet, Sales Information table where customer information table. Customer Number = Sales Information table. Customer number

Cases:
Use Commodity Management database
Go
Select customer Information table. Customer number, customer name, commodity information table. Product number, product name, sales amount, Commodity type name
From customer information Form, commodity information table, sales information Form, Commodity type table
The Where Customer Information table. Customer Number = Sales Information table. Customer number and commodity information table. Product Number = Sales Information table. Product ID and commodity information table. Commodity type number = Commodity type number------Check customer purchase information

--1) Inner Connection (one of the conditional connections)
SELECT * | field list from data table 1 [inner] Join data table 2 on Join condition expression [inner] jion data table 3 on join condition expression ... [Where Condition expression] --When two or more tables are connected, connect the data table 2 in table 1, data table 2, connect the datasheet 3 sequentially

Cases:
Use Commodity Management database
Go
Select customer Information table. Customer number, customer name, commodity information table. Product number, product name, sales amount, Commodity type name
From Customer information table
Join Sales Information Sheet on the Sales Information table. Customer Number = Customer Information table. Customer number
Join Product Information table on the Product information table. Product Number = Sales Information sheet. Product Code
The Join Commodity Type table on the Commodity type table. Product Type number = commodity information table. Product Type number--Check customer purchase information

-- External connection
--1) left outer join (that is, the left table is the main table, with each record in the primary table and each record in the right table to join the combination, the record that satisfies the condition and does not satisfy the condition will appear in the result, the record with unsatisfied condition fills with empty value)
Select field Name list from table 1 left JOIN table 2 on table 1. public field = Table 2. Public field --table 1 is the primary table and the ON keyword indicates the join condition

Cases:
Select*from A. School number, name, course name
From A left join B
On A. Study number =b.

Cases:
Use Commodity Management database
Go
Select customer Information table. Customer ID, customer name, product number, date of sale
From Customer information table left JOIN Sales Information table
On Customer Information table. Customer Number = Sales Information table. Customer number

--2) right outer join (that is, the right table is the main table, with each record in the main table and each record in the left table to join the combination, the record that satisfies the condition and does not satisfy the condition will appear in the result, the record with unsatisfied condition fills with empty value)
Select field Name list from table 1 right Join table 2 on table 1. public field = Table 2. Public field --table 2 is the primary table and the ON keyword indicates the join condition

Cases:
Use Commodity Management database
Go
Select customer Information table. Customer ID, customer name, product number, date of sale
From Sales Information table right join customer Information Form
On Customer Information table. Customer Number = Sales Information table. Customer number

--3) full outer join (All records in the left table are connected to each record in the right table, and the records that meet the conditions and those that do not meet the conditions in the table will appear in the results, and the records with unsatisfied conditions are filled with empty values)
Select field Name list from table 1 full join table 2 on table 1. public field = Table 2. Public fields --the left and right tables are not primary and secondary, connected to each other, table 1 table 2 position can be interchanged, the ON keyword indicates the connection condition

Cases:
Use Commodity Management database
Go
Select A. School number, name, course name
From A full join C
On A. Study number =c.

-- self-linking (refers to the data table and its own implementation of the connection query operation, is also a form of a multi-table connection)
From data table name table alias [, ...] or from data table name as table alias [, ...] --table aliases are used when querying a data table using the FROM statement, using the AS keyword to specify table aliases, and spaces can be used. You can specify aliases for multiple data tables, you can specify multiple aliases for a single data table, that is, make multiple copies of a single data table

Cases:
Use Commodity Management database
Go
Select DISTINCT A. Product number, A. Customer number from Sales Information table A, Sales Information table as B where a. Customer number =b. Customer number and a. Product Code < > B. Item number

-- subquery (subquery exists in nested query statement)--
-Inarithmetic subquery
SELECT * | field name [, field Name 2, ...] from data table name where conditional expression in (SELECT * | field name [, field Name 2, ...] from data table name [where condition expression])

Cases:
Use Commodity Management database
Go
Select customer name, contact phone from Customer information table where customer number in (select customer number from Sales Information table where sales date = ' 2012-12-11 ')--check the name and contact number of the customer who purchased the item on December 11, 2012

--- compare subqueries ( where conditions in the main query use comparison operators and subqueries to form a query condition , and the program executes a subquery first, unlike the in operator, the result of a subquery after the comparison operator is just a value, not a collection)
(Common comparison operators:=,>,>=,<,<=,<>,!=, !>,!<)

Cases:
Use Commodity Management database
Go
Select product Type number, product name from commodity information table where item number in (select product number from Sales Information table where sales quantity < (select AVG (sales quantity) from Sales Information table))

--Theexists operation subquery (exists means "exists", the subquery behind it does not produce a query result, but instead produces a logical value of TRUE or false, when the word query has a record that is found to be true, Conversely, false to display results only if the subquery result is true)

Cases:
Use Commodity Management database
Go
Select customer name, contact phone from Customer information table where exists (SELECT * From Sales Information table where customer number = Customer Information table. Customer number)

Note: "--" for comment text or description

SQL Server table Data simple operation (table data query)