SQL class Notes

Source: Internet
Author: User

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

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.