SQL Server Basic Operations II

Basic Query

CREATE database Zk_testgouse zk_testgocreate table Student (IDintIdentity1,1) primary key, Name varchar ( -) notNULL, SexChar(2) notNULL, Birthday datetime, Heightint)Select* fromStudentinsert into student (Name, Sex, Birthday, Height) VALUES ('Zhang San','male','1988-08-12',172),(    'Zhang 31','female','1986-08-12',168),(    'I'll','male','1988-08-12',122),(    'KK','male','1988-12-19', the),(    '? Look','male','1958-9-12', the),(    'Run','male','1976-8-12',102),(    'Use amount','male','1988-8-12',192),(    'Leave','male','1988-8-1', the)SelectDistinct * fromStudentSelectTop3* fromStudentSelectTop -Percent * fromStudentdeclare @countint=5SelectTop (@count) * fromStudentSelectName asName fromStudentSelectName +'ZK'  asName fromStudentSelectName +'ZK'+ CONVERT (varchar ( -), Birthday, the) asName fromStudentSelectDateDiff (year, Birthday, GETDATE ()) Age fromStudent--The group by operation uses the word segmentsgenerating as a temporary table, followed by the Operation--You can use only the fields of a temporary table, otherwise you need to use Max, MIN, etc. to manipulate the original table to synthesize a new tableSelectName,max (Sex) asSex,max (height) asHeight fromStudentwhereHeight >=151GROUP by namehaving Max (Sex)='male'ORDER BY name DescSelect* fromStudentwhereHeight =172Select* fromStudentwhereSex ='male'Select* fromStudentwhereHeight >= Maxand height <=175Select* fromStudentwhereHeight between Maxand178Select* fromStudentwhereHeightinch(176,172, the)Select* fromStudentwhereHeight notinch(176,172, the)Select* fromStudentwhereHeight like'1_2'Select* fromStudentwhereHeight like'17%'Select* fromStudentwhereHeight like'1[67][012345]'Select* fromStudentwhereHeight is NULLUpdate StudentSetHeight =NULL whereID =8Select* fromStudentwhereHeight isNotNULL--converts a null value to 0Select* fromStudentwhereIsNull (height,0) < the Select* fromStudentwhereexists (Select 1)--determine if a data hasifexists (Select* fromStudentwhereId= A) Print'exist'ElsePrint'Not exists'Select* fromStudentwhereexists (Select* fromStudentwhereId=9)Select* fromStudent asT1whereexists (Select* fromStudentwhereid = t1.id and height >173)Select* fromStudentwhereCharindex'Zhang', Name,1) >=1Select* fromStudentwherePatindex'Zhang _', name) >=1Select* fromStudent ORDER by name Descalter table Student Addclass int default(NULL)--cyclic assignment declare @nointSet@no =1 while@no < -Begin update StudentSet class[Email protected]whereId=@noSet@no = @no +1EndSelect* fromStudent ORDER by sex,classSelect class, year (birthday), count (ID), AVG (height), max (birthday) fromstudent group byclass, BirthdaySelect class, year (birthday), count (ID), AVG (height), max (birthday) fromstudent group byclass, Birthday hasclass=1Select class, year (birthday), count (ID), AVG (height), max (birthday) fromstudent group byclass, Birthday has avg (height) >165Select class, Sum (isnull (height,0)) fromStudent GROUP BYclassSelect class, avg (height) fromStudent GROUP BYclassSelect class, Count (height) fromStudent GROUP BYclassSelect class, max (height) fromStudent GROUP BYclassSelect class, min (height) fromStudent GROUP BYclassSelect class, COUNT (distinct (height)) fromStudent GROUP BYclass

SQL Server Basic Operations II