sqlpassion Performance Tuning Training Planindividual study translation, if there is falsehood, please do not hesitate to point out, thank you. Week 1:sql Server How to execute a query Before we go into the intricacies of SQL Server performance tuning, I'd like to start by enumerating how SQL Server executes a
Basic format:SELECT [All | distinct] select_listFrom Table_list/view_list[WHERE conditions][GROUP BY Group_list][Having conditions][ORDER by Order_list]Cases:Select employee number, name, salary as month salary, (salary *12+5000)/6 as year bonus from staffSelect *from staff where warehouse number in (' Wh1 ', ' wh2 ') and sex! = ' women ' and wages >=1300 and wages 3.1 several special SQL Enquiry3.1.1 Fuzzy Query
Label:--Paging data----row_number () is called window-opening function, can be paged operation Select Row_number () over (order by ID) as Num,*from gb_data----adds a sequential num value to each column to facilitate the use of the first few data. Select row_number () over (order by ID) as num,* from Gb_data where Num>5and num 10--This line of code is wrong, because the system does not recognize how much num is, why? -Because of the order of execution, the
Today, when writing a program, you need to generate an SQL statement from the start ID to the end ID. It turns out that you don't need this feature now.
How do you query SQL Server for data SQL statements that are queried between the first few to several in the database?For example, in
Article 1: how to optimize slow query SQL statements in MySQL56-Introduction to slow logs: in actual log analysis, there are usually a large number of slow logs, at the same time, there will be a large number of records for the same query. here we need to find the most problematic and optimized log in the actual log analysis, generally, the number of slow logs is
SQL query begins with a specified character SQL query statementLike a lot of time we need to be in the MySQL tutorial, MSSQL to query eligible data, today I came across a query with "Deng" all the conditions of the beginning of th
First of all, let's look at one of the steps here that translates into banner, which requires pivot, step-by-step Then look at the overall query results for the two query methods after the main table join So take a look at the latter query method is also through the industry after the conversion to do the join execution plan, you can see only the Word table
Original address: http://www.cnblogs.com/lyhabc/p/3367274.htmlBefore reading this article, you can read the following article firstSQL Server's unique task scheduling algorithm "Sqlos"Task Scheduler for SQL Server Sqlos [go]Translated from:http://rusanu.com/2013/08/01/understanding-how-sql-server-executes-a-query/Http://www.codeproject.com/Articles/630346/Underst
Recursive queries using CTE (common table expressions) ---- SQL Server 2005 and later versions
A common table expression (CTE) has an important advantage: It can reference itself to create a recursive CTE. Recursive CTE is a public table expression that repeats the initial CTE to return a subset of data until the complete result set is obtained.
When a query references recursive CTE, it is called recursive
Hibernate native SQL query Multiple table Association, SQL statement to pay attention to the problem@for ever 2009-9-4System environment:MySQL5.1Hibernate3.3The assumption is as follows:The entity classes Question and answer correspond to the data table Question and answer respectively.and the fields of table question and answer are mostly the same, and the numbe
Query a stored procedure sharing of SQL Server database deadlocks. SQL Server Stored Procedure
When SQL Server is used as the database application system, it cannot avoid deadlocks. When deadlocks occur, maintenance personnel or developers only use sp_who to find deadlocks, then use sp_kill to kill. Using sp_who_lock,
S (Sno,sname,sdd,sage)Sno,sname,sdd,sageEach representative of the school number, learningMember's name, affiliation, student's ageC (Cno,cname)Cno,cnameRepresent the course number, the course name, respectivelySC (Sno,cno,score)Sno,cno,scoreRepresenting the school number,of the ElectiveCourse number, learning scoreThe data for the three tables are as follows:Problem:Use standard SQL nested statements to query
(*), sex from student group by sex;Grouping statistics by age and gender combination and sortingSelect COUNT (*), sex from student group by sex, age order by age;Grouped by gender and are records with IDs greater than 2 finally sorted by sexSelect COUNT (*), sex from student where ID > 2 GROUP by sex Order by sex;Querying data with IDs greater than 2, and grouping and sorting results after completion of operationsSelect COUNT (*), (Sex * ID) New from student where ID > 2 GROUP BY sex * ID of OR
Partii:sql BasicsCHAPTER 4Creating a simple Querydescribes a way to create SQL the technology of the statement -- "Request/translation/clean up/sql "The SELECT operationin SQL can broken down into three smaller operations,Which we'll referto as the select statement,the Select expression,and the SelectQuery.The first layer contains one layer , nesting with each o
Grouping query of SQL is very useful, but sometimes its features are also annoying. Take a good look at this SQL:
select employer_name, department, max(salary) from employer_salary group by department;
Query the name and salary number of the highest-paying person in each department. Is there a problem with this
, generating VT6.
has: has a having filter applied to VT6. Only groups that make
Select: processes the select list, producing VT8.
DISTINCT: removes duplicate rows from VT8, resulting in VT9.
ORDER BY: generates a cursor (VC10) by sorting the rows in VT9 by the list of columns in the ORDER by clause.
TOP: selects the specified number or scale of rows from the beginning of the VC10, generates the table VT11, and returns the caller.
Note: Step 10, this
--select select * from student; --all query All the Select all sex from student; --DISTINCT filtering repeats select distinct sex from student; --count Statistics SELECT COUNT (*) from student; Select count (Sex) from student; Select count (Distinct sex) from student; --top fetch top N Records select Top 3 * from student; --alias Column name rename named Select ID as number, name ' names ', sex gender from student; --alias Table Name table rename Sele
In SQL ServerEach database has a table generated by the system.The table sysobjects records all the table names in the database.We can use the following SQL syntax for query operations.
Copy codeThe Code is as follows:Select Name, id from sysobjects where xtype = 'U'
Xtype = 'U' indicates the table used. If xtype = 's' is used'The default table
There is also a
Check this diary record
SELECT * FROM Messages WHERE convert (Nvarchar, createdate, MR) = CONVERT (Nvarchar, GETDATE (),) Order by CreateD Ate DESC
SELECT * from table WHERE DateDiff (day, column name , GETDATE ()) =0
Check this year
SELECT Count (*) from Messages WHERE DateDiff (year,createdate,getdate ()) =0
Query this month
SELECT Count (*) from Messages WHERE DateDiff (month,createdate,getdate ()) =0
This week 's record
SELECT * from
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.