--note that the company generally uses a never-repeating GUID () key (Web project is not used) such as NULL to participate in the operation, the result is null to create an index in the database to improve query efficiency, only in the fields that are often retrieved to create the index) SQL query NULL data SELSCT * FROM Table where name is null query age between 20 and 30 SELSCT * from Table where ages between nd 30 query age is 20,22,32,46 data SELSCT * from Table where Ages in (20,22,32,46) queries older than 20 data. Sort by ascending desc: Descending asc: Ascending SELSCT Top 3 * from Table where age>20 //after the condition is ordered, order by must after the where statement to order by the age asc to group according to ages, and calculates the number of people of each age select ages, COUNT (*) from TableGroup by the data is grouped according to age and calculates the number of people per age, and calculates the average wage for the same age, showing only select Age, Avg (Salary), with a number greater than 1, and COUNT (*) from TableGroup by Agehaving Count (*) >1 //having filtering after grouping, only columns subqueries in SELECT Top 3 * from Tablewhere primary key not In (select Top 5 payroll from Table order BY payroll desc) Order By Salary desc Modify table structure add field ALTER TABLE table name add column name data type Go to duplicate query Selec T tistinct salary, name from table is not repeated for the entire row shown union merges two query results together, and the top and bottom two data types are compatible, eliminating duplicate data by default SelecT name, age from Table1union) here plus all do not repeat, show all) select Name, age from table2) can be multi-line merged) sql function abs (): absolute value ceiling)) Rounded to the maximum integer floor)) rounded to the smallest integer round) to modify the number of digits after the decimal point) round len) to calculate the length of the string) lower () upper () fill case LTrim)) string left space to remove the RTrim) string to remove the right space, Go on both sides nested two function substring () (string, from which intercept, intercept length is) Convert type function cast (' 123 ' as int) convert (DateTime. ') 2012-01-01 ') Select IsNull (field, ' name ') from table --the null in the field to the specified name date function getdate), whichever is the current date DateAdd) unit, quantity, Pending date) dateadd) month,-8,date) calculates the difference between two dates for the date DateDiff (unit, start date, end date) 8 months before the date is calculated (date) units, dates) Returns a specific part of a date statistics of the number of employees in a year (select datepart) (years), entry time) count) *) from table GROUP by datepart) The case function usage: select name) Case number when 1 then ' user ' when 2 Then ' member ' Else ' unknown ' end ' as Customer type from table name select o.no,c.name,c.ageform Table1 as O Jo In Table2 as C on O.id=c.idwhere c.age>=20; subquery --single row subquery select * FROM Tablewhere age> (select min Table) sql Page SELECT * FROM (select Row_number () over (order by fsalary Desc) as rownum,* form table) as E1wh EreE1.rownum>=3 and e1.rownum<=5 the "SQL Classic exercises" table has ABC three columns, implemented with SQL statements: When column A is greater than column B, select column A or column B, When column B is greater than column C, select column B otherwise select column C. Select) Case Time a>b a else B end), (case time b>c B else C end) from table is shown in the column "Customer Type", when the number equals 1, the ' user ' is displayed when the number equals 2 Show ' members ', other numbers show ' unknown ' select name ' case when salary <2000 Then ' low income ' when wages >2000 and wages <5000 then ' middle income ' else ' high income ' end ) from table --query call duration five records select Top 5 * from Tableorder by DateDiff (second,startdatetime,enddatetime) desc& nbsp;--Output all data (long distance number) 0) The total length of the Select SUM (DateDiff (second,startdatetime,enddatetime)) from Tablewhere tellnum like ' 0% ' --output the top three caller number of the total call of the month select top 3 sum (second,startdatetime,enddatetime) Callernumber from Tablewhere DateDiff (month,startdatetime,getdate ) =0;group by Callernumberorder by sum (DateDiff (second,startdatetime,enddatetime) desc --output the number of top three callers to call this month select Top 3 callernumber from Tablewhere DateDiff (month,startdatetime,getdate ) =0;group by Callernumberorder by Count (*) desc --output all data dial-up pipelining), caller number, phone number, length of call, and add total call duration on the last line, summarizing "Total City", "Long Total" select Callernumber,tellnum,datediff ( second,startdatetime,enddatetime) Form tableunion allselect N ' Total ', Convert (varchar (), SUM (Case when tellnum Like ' 0% ' then DateDiff (second,startdatetime,enddatetime) Else 0end)), sum (Case when tellnum don't like ' 0% ' then DateDiff (second,startdatetime,enddatetime) Else 0end)) from table