SQL-Common statements

--Conditional query
SELECT * FROM car where oil<8--view the results of oil less than 8 from the car table
Select name as model name, oil as fuel consumption from car where oil<8--just look at name and oil Word to add an as to change name to model name oil to fuel consumption as can be omitted
SELECT * FROM car where oil=7.4--view oil equals 7.4 results from car table = = and <> is not equal to
SELECT * from car where oil=7.4 and price<50--View the result of oil equals 7.4 and price less than 50 from the car table and and or is or
SELECT * from car where oil=7.4 or price is null--is can also be replaced by =
--Fuzzy query
SELECT * from car where name '% Mercedes% '--name contains Mercedes Benz: name in the end with Mercedes Benz%:name in the beginning with Mercedes
--Sort query by price query
SELECT * FROM Car ORDER BY price desc--prices sorted by default Ascending desc: Flashbacks
--Go to re-query (not commonly used in the General group query)
Select distinct brand from car--brand non-repeating
--Group Query
Select Brand,max from Car GROUP by brand-the highest price per brand
--Joint query
Select Brand from Car
Union
Select Brand_Code from Brand
--The aggregation function takes the group query as an example
Select Brand,min from Car GROUP by brand-the lowest price in each brand
Select Brand,sum from Car GROUP by brand-average price in each brand
Select Brand,count from Car GROUP by brand--several models in each brand
Select Brand,count (*) There are several data from the car--car table
--Mathematical functions
Select CEILING (5.1)
Select Floor (5.9)
Select ROUND (5.91,1)
Select ABS (-10)--Absolute value
Select PI ()--3.14159265358979323846
Select LOWER (' ABCDEF ')--the contents in parentheses become lowercase
Select UPPER (' ABCD ')--the contents in parentheses become uppercase
Select RTRIM (LTRIM (' Dfdsfas '))--Remove both spaces
--Conversion function (content is the concatenation of the characters is the number added)
Select *from Car
Select Name+brand from Car--string concatenation
Select Oil+price from Car--add
Select Oil+convert (Decimal (10,2), powers) from car--converts the powers type to a decimal type
--Time function
Select year (GETDATE ())--Get the year before
Select Month (GETDATE ())--Get the current month
Select DATEADD (Hour,8,getdate ())--Add 8 hours to the current time
--Sub-query
SELECT * from Teacher where prof= ' teaching assistants ' and depart= ' computer system '--normal wording
SELECT * FROM score where Cno in (select Cno from Course where cname= ' Introduction to Computers ')
--Merge Query
SELECT * FROM KCB joins CJB on KCB. Cno = CJB. cno--merges course's Cno and score's Cno
SELECT * from Course ieft join score on course.cno = Score.cno--One left table (Course) whichever

SQL-Common statements