1.Select's basic syntax
SELECT COL1, COL2 ..... coln
[INTO NEWTABLE] 'Insert the data inserted into the new table
[From Tab1, Tab2 .... Tab3] 'Target Table
[Where @condition] 'Query Conditions
[Group By col1, col2 .... coln] 'Press the specified column group
[Having @Condition] 'Use of use when using functions (AVG, Count)
[Order By Col1, Col2 ..... coln] 'Sort by the specified column
* Column (COL) can be all columns in the table, or a derived column (columns generated after several table column)
* The new table is in the table of the database selected in the select command.
* Conditional data selection screen conditions, you can set greater than, less than or equal to basic judgment conditions.
* ORDER by Group By When the column and condition to be selected, you can sort and packet selected data.
* Having usually with Group By, is used as conditional selection, just like WHERE conditions.
2 basic query
2-1 Select all data Select * from table
2-2 Select the specified column Select Colom Table
2-3 Select the SELECT that does not repeat
Distinct Colom Table
2-4 Specify a list of display formats in a column Select Emp_name, Convert (8), Birthday, 11) --- Display the Birthday field as a CHAR form
2-5 Data Sort SELECT EMP_NAME FROM EMPLOYEE ORDER BY EMP_NAME DESC | ASC ----- DESC is descending, ASC is ascending, default is ascending
2-6 Use the function select count (*) from sales where tot_amt> 5000 --- The number of orders greater than 5000
2-7 Select the first few lines of data SELECT TOP 10 Orderid from ODERS
2-8 data packet
You can use the Group By clause to packet the data selected by the Select command, and the data after the group can be displayed, or
Sum (), AVG () and other functions calculate the sum of each group of data for a particular column.
SELECT COL1, COL2
Form tab
GROUP BY Col1
The field in the Group By clause must be in Select, for example
SELECT Pro_ID, SUM (Qty * Unit_Price) as Tot_AMT
Form Sailes - Show the sum of the subscription amount of each product in the product sales schedule,
Group by pro_id ----- and sort from big to small
Order by Tot_AMT DESC
SELECT Pro_ID, Count (*) from stock
Group by prod_id ---- group by clause is often mixed with HAVING, used to find out
Having count (*)> 1 --- Data that satisfies the specified condition in each group. This command displays two of the same model products.
Order by prod_id ------ Contact supplier 3 plus selection criteria When you just want to select some of the data, you can add the selection condition, that is, plus the WHERE clause
The logical operator is equal to =, not equal to <> or! =, Greater than>, less than <, greater than or equal to> =, less than or equal to <=, in Between,
Whether the IS NULL column value is NULL in the list of NOTWEEN, IN (Not In) columns
4 use uniform
% Zero or more characters
_ Single character
/ Special characters
[] The characters in a range, such as [0-9] or [ACFK]
[^] Not in a range of characters, such as [^ 0-9] or [^ acfk]
Note: When using a stroke harness to compare data, since SQL Server cannot use the primary key value and index to speed up the data in this case
It is a query.
5 tables
When queries between multiple tables, you will have a multi-table.
Such as: Select * from stock
Join Product
On stock.prod_id = product.prod_id
Join, more than two tables, in Stock, Product we show the Chinese name of the product, and I hope to display the name of the supplier.
Then you must pull the supplier table (SUPPLIER) to do Join
SELECT Product.Prod_name, Supplier.Sup_name, stock.stk_qty
From stock
Join Product on stock.prod_id = product.prod_id
Join Supplier on stock.sup_id = support_sup_id
Self Join
In the Join in the table, you can also do Join in the same table, this kind of Join, this is called Self-join.
Since Self-Join involves two identical tables, you can use the alias to make the same table as a different table.
The following command identifies the employees entering the company on the same day.
Select a.emp_no, a.emp_name, b.emp_no, b.emp_name, a.date_hired
From Employee A
Join Employee B
ON (a.emp_no! = B.emp_no and a.emp_name> B.emp_name and a.date_hired> B.DATE_HIRED)
ORDER BY A.DATE_HIRED
Out Join
The result of Join is a data from two or more tables that picked out Join condition. But data from different tables cannot meet Join
If the conditions are discarded, usually we call this called inner Join.
Another type named Out Join, this method is a master from the master. His handling method is to use the primary data of the primary table to the column of the Match from the table,
It is all the data we get, but the data that is not critical will be filled in NULL.
