SQL Server programming is a must--(1-20-point summary)

--1. Common stored Procedures
Sp_databases
--Returns a list of available databases

Sp_tables
--Returns a list of available tables in the currently selected database, including not only your own tables, but also system tables and other tables

Sp_tables null, dbo, scratch, "' Table '"
--returns only the available tables for the currently selected database, excluding views and system tables

Sp_columns ARPU
--Displays the columns of the table ARPU

--2. Identity: Indicates that some table columns require unique values, such as the order number, when a row is added to the table, SQL Server can automatically assign the next available number;

--3. To end an SQL statement, multiple SQL statements must be separated by semicolons; can also not add, but add to the readability of the more strong;

--4. SQL statements are case-insensitive, are used to capitalize on SQL keywords, use lowercase for all columns and table names, and are easy to read and debug;

--5. blank, when processing SQL statements, all whitespace is ignored, the SQL statement is divided into multiple lines easier to read and debug;

--6. Query multiple columns, separated by commas;

--to compare a value to a column of a string data type, you need to limit the quotation marks. The value used to compare with the value column is not quoted;

-----------------------SELECT to retrieve data-----------------------
--7. Return only a different row
--SELECT DISTINCT vend_id
-From the products;

--8. The distinct is used for all rows, and all the different rows are output
--SELECT DISTINCT Vend_id,prod_price
-From the products;

--9. Limit results: Only the first 5 rows of data are displayed;
--select TOP (5) Prod_name
--from Products;

--10. Limit results: Only the first 25% rows of the table are displayed;
--select TOP (+) PERCENT Prod_name
--from Products;

--11. Use a fully restricted table name query;
SELECT Products.prod_name
--table name. Column Name
From Crashcourse.dbo.products
--database name. Table owner name. Table Name

Retrieve Data---------------------------Sort--------------------------
--12. Sort the Prod_name column alphabetically, using order BY, the default ascending order (ASC)
SELECT Prod_name
From Products
ORDER by Prod_name;

--Sort by prod_name, descending order
SELECT Prod_name
From Products
ORDER by Prod_name DESC;

--13. Sort by multiple columns, retrieve 3 columns, and sort the results by two columns-first by price, then by name
SELECT Prod_id,prod_price,prod_name
From Products
ORDER by Prod_price,prod_name;

--14. Specify sort direction: Sort by multiple columns, retrieve 3 columns, and sort the results by two columns-first by price descending, then by name
SELECT Prod_id,prod_price,prod_name
From Products
ORDER by Prod_price Desc,prod_name;

--15. Find the maximum value in a column, the price of the most expensive product
SELECT TOP (1) prod_price
From Products
ORDER by Prod_price DESC;

--16. Find the minimum value in a column
SELECT TOP (1) prod_price
From Products
ORDER by Prod_price ASC;

---------------------------Filtering Data--------------------------
----------Search criteria (filter condition)

--17. Check Single value: Use a WHERE clause to return a row with a Prod_price value of 2.50
SELECT Prod_name, Prod_price
From Products
WHERE Prod_price = 2.50;

--The ORDER by statement needs to be behind the WHERE clause, otherwise an error occurs
SELECT Prod_name, Prod_price
From Products
WHERE Prod_price = 2.50
ORDER by Prod_name DESC

--18. Mismatch check: List all products not manufactured by Vendor 1003
SELECT vend_id, Prod_name
From Products
WHERE vend_id <> 1003;
--where vend_id! = 1003;

--19. Scope Check: Retrieve all products priced between $5 and $10
SELECT Prod_name, Prod_price
From Products
WHERE Prod_price between 5 and 10;
--where Prod_price >= 5 and Prod_price <= 10;

--20. NULL check: Returns the value of the user's e-mail null
SELECT cust_id
From customers
where Cust_email is NULL

--Returns the product name with the product price NULL in the product table, because there is no match, the output is empty
SELECT Prod_name
From Products
WHERE Prod_price is NULL

This article comes from the "Ricky's blog" blog, please be sure to keep this source http://57388.blog.51cto.com/47388/1701578

SQL Server programming is a must--(1-20-point summary)