SQL Server Learning notes <> basics, some basic commands, single table queries (null top usage, with ties attached properties, over window functions), ranking function

SQL Server Basics

(1) Create a database

There are two ways to create a database, manually create and write SQL script creation, where I use a script to create a database called TSQLFundamentals2008. The script is as follows:

View Code

While inserting some data into the database table, the user follows the SQL practice of the database. In this case, you can download the appropriate script to initialize the database. I put on the Baidu cloud above, please poke

Me: http://yun.baidu.com/share/link?shareid=3635107613&uk=2971209779, provides the electronic version and script of the book "Sqlserver2008 Technology Insider".

(2) The table of TSQLFundamentals2008 data is explained here:

The database table interface is as follows:

HR. Employees	Employee table, which stores some basic information about the employee.
Production.Products	Product Information Sheet
Production.suppliers	Supplier table
Production.customers	Customer Information Form
Production.categories	Product Category Table
Sales.orderdetails	Order Details Form
Sales.orders	Order Form
Sales.shippers	Freight company Table

SQL Server Some basic commands:

Query Database exists:

If db_id ("TestDB") is not null;

Check if the table exists:

If object_id ("TextDB", "U") is not null, where U represents the user table

To create a database:

Create database+ Data name

To delete a database:

Drop DATABASE Database name--Deletes the

DROP table name--Deletes the

Delete from table name where condition--delete data

Query statement:

Use database name--Modified Database

Select*from + table name-the table to query

Select a, xxx, xxx from table name where condition--data with conditional query

Insert data:

Insert into table name (condition) values (corresponding value)

single-table query

(1) grouping-for a group query, the select sentence will have a limit, the query field needs to appear in the GROUP BY clause, and after grouping, the group can be counted.

Query the employee table and count the number of people in each group according to the country in which the employee is located:

1 Select Country,count (*) as N ' number ' 2 from HR. EMPLOYEES3 GROUP BY country

When the field to be queried is not included in the GROUP BY clause, the corresponding error is reported, so it is important to note that after the query fields that appear after the select are grouped, you also need to appear behind group by.

(2) Here is a hint: query conditions do not use computed columns, the following are the specific reasons:

For example, you can write SQL statements by querying all employee information that an employee is born for 1973 years in an employee table:

1 Select year (birthdate), firstname,lastname from HR. Employees2 where year (birthdate) = ' 1973 '

Can see the results of the query will be 1973 of employee information to find out, but you can think about it, the above SQL statement in the query, the first is to speak birthdate to take out the annual calculation,

Year (birthdate), which is a built-in function of SQL, that can be used to calculate the date of the string that was taken out. We can also use the following SQL statement to query:

 

As you can see from the SQL Execution plan, the query condition with computed columns is the index scan, and when the WHERE clause is followed by a look-up limit, the walk is a lasso lookup. Compare two queries obviously most of the cases

The query performance of the index lookup is higher than the index scan, especially if the queried database is not very large, and the index lookup consumes much less time than the index scan. So in the query condition, do

To avoid calculation conditions.

(3) say that null,null in SQL Server does not exist in the database, unlike NULL in C #, does not represent a null reference, no object, and anull operation rule: any operation with NULL is NULL.

is [NOT] null: can only be used as a conditional-judgment expression, is it null? is a condition of true, not a condition of false.

IsNull (): function, if the first argument is null, replace the value of the first parameter with the value of the second parameter as the return value of the function. Remember that the type of the second parameter must be compatible with the first one.

Nullif (): function, if two parameter values are equal, one argument is null, or two parameter is NULL, the function return value is NULL, otherwise the value of the first parameter is returned.

(4) Top usage: It is intended to remove the number of pre-conditions in the table. Top---Top 10

When it comes to top, I suddenly think of the first 30-40 records in a table that often appear in the interview question, noting that the ID may not be contiguous. Use top to write this:

1 Select top * from a where ID2 not in (select top with ID from a  order by ID ASC) 3 ORDER by ID ASC

You can also use the following wording, but the readability is poor:

1 Select top * fron A where id>2 (select Max (ID) from (select top with ID from A ORDER by ID) as T) 3 ORDER by ID ASC

Of course, since there is scope in existence, it can be implemented with exist:

1 Select Top * from a A1 2 where not EXISTS 3 (SELECT * FROM 4 (select Top, from a ORDER by ID ASC) A25 where a2.id =a1.id 6)

However, there is a need to consider---- related subqueries: When the main query iterates through a single record, it performs a subquery against the value of the main query, so it is less efficient.

The following describes the use of top and percent, percent represents the percentage: for example, query the employee table, the previous 20% of the employee's information, can write SQL, query results for two people.

1 Select TOP (Percent) * from Hr.employees  

We are inquiring about hr.employees (employee table), and check the total number of people in the employee table, the results show that there are 9 people.

1 Select COUNT (*) as N ' total number ' from Hr.employees

As can be seen, 9 people by 20% to take the whole number, so find out the show has two people.

(5) With ties additional properties:

When we query the order form, query sql:

1 Select Orderid,orderdate2 from Sales.orders ORDER by OrderDate  DESC

Join our Top 5 when we query the top five order information

1 Select Top 5 orderid,orderdate2 from Sales.orders ORDER by OrderDate  DESC

Query results

The comparison does not add top 5, the query results intercept the first five order information, but sometimes we need to be the same as the last order date to be taken together, we need to take the additional property with ties.

(6) Over open window function:

The above is about using the Count aggregate function, in which you need to group the sum. But using over can also be achieved based on what sums. Omit GROUP BY.

1 Select Firstname,lastname, COUNT (*) over () as  N ' total number of people ' 2 from Hr.employees

where over (), parentheses can attach conditions, based on what to summarize. Does not add, it means that all records are summarized. For example, for each customer to spend the total amount of orders, you can write:

1 Select Orderid,custid,sum (val) over (partition by CustID) as n ' Total customer consumption ', 2 sum (val) over () as n ' Order total ' from Sales.ordervalu Es

Five. Ranking function

(1) Row_number, line number, general and over joint use. Ranking based on what.

1 Select Row_number () over (order by LastName) as N ' line number ', lastname,firstname2 from Hr.employees

(2) rank, rank, ranking in real sense, for example:

1 Select Country,row_number () over (order by country) as N ' rank ranking ', lastname,firstname2 from Hr.employees

It can be seen that, according to the country rankings, is indeed out, but found that the first four are the same as the UK, supposedly to make part of the order, so you can use rank to operate.

1 Select Country,rank () over (order by country) as N ' rank ranking ', lastname,firstname2 from Hr.employees

As can be seen, after using rank, country with the UK tie, similar to the student test scores ranked tie situation.

(3) Dense_rank, dense rankings

After rank ranked by the above, there is tie situation, but country for the USA should be second, so there is the use of dense ranking Dense_rank ranking.

1 Select Country,dense_rank () over (order by country) as N ' Dense_rank rank ', lastname,firstname2 from Hr.employees

It can be seen that after adopting Dense_rank, it satisfies the demand of a rank under a certain condition.

(4) Grouping ntile. Group by a certain condition.

1 Select Country,ntile (3) over (order by country) as n ' ntile grouping ', Dense_rank () over (order by country) as n ' dense_rank rank ', LA Stname,firstname2 from Hr.employees3 ORDER by country

Sometimes in order to sort within a certain range, for example:

1 Select Lastname,firstname,country,row_number () over (order by country) as N ' rank ' 2 from hr.employees

In order to achieve the sort according to the country scope, that is, country for the UK for a group to be sorted, country for a group of USA to sort. It can be written like this:

1 Select Lastname,firstname,country,row_number () over (partition by country order by country) as N ' rank ' 2 from hr.employees

SQL Server learning note <> basics, some basic commands, single table query (null top usage, with ties attached property, over window function), ranking function