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

Source: Internet
Author: User

--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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.