sql server 基本操作二

標籤：

基本查詢

create database ZK_Testgouse zk_testgocreate table Student(    ID int identity(1,1) primary key,    Name varchar(20) not null,    Sex char(2) not null,    Birthday datetime,     Height int)select * from studentinsert into student (Name, Sex, Birthday, Height)values(    ‘張三‘,‘男‘,‘1988-08-12‘, 172),(    ‘張三1‘,‘女‘,‘1986-08-12‘, 168),(    ‘我就‘,‘男‘,‘1988-08-12‘, 122),(    ‘kk‘,‘男‘,‘1988-12-19‘, 132),(    ‘嗎看‘,‘男‘,‘1958-9-12‘, 152),(    ‘咯跑‘,‘男‘,‘1976-8-12‘, 102),(    ‘用額‘,‘男‘,‘1988-8-12‘, 192),(    ‘離開‘,‘男‘,‘1988-8-1‘, 152)select distinct * from studentselect top 3 * from studentselect top 30 percent * from studentdeclare @count int = 5select top (@count) * from student select Name as 姓名 from studentselect Name + ‘zk‘ as 姓名 from studentselect Name + ‘zk‘ + convert(varchar(20), birthday, 112) as Name from studentselect datediff(year, birthday, getdate()) age from student--group by操作用by的欄位產生了暫存資料表，在其後進行的操作--只能用暫存資料表有的欄位，否則需要使用max、min等對原表進行操作組合成新表select name,max(sex) as sex,max(height) as heightfrom student where height >= 151group by namehaving max(sex) = ‘男‘order by name descselect * from student where height = 172select * from student where sex = ‘男‘select * from student where height >= 150 and height <= 175select * from student where height between 150 and 178select * from student where height in(176, 172, 180)select * from student where height not in(176, 172, 180)select * from student where height like ‘1_2‘select * from student where height like ‘17%‘select * from student where height like ‘1[67][012345]‘select * from student where height is nullupdate student set height = null where id = 8select * from student where height is not null--將null值轉換為0select * from student where isnull(height, 0) < 180 select * from student where exists(    select 1)--判斷某個資料是否有if exists(    select * from student where id=12)print ‘exist‘elseprint ‘not exists‘select * from student where exists(    select * from student where id=9)select * from student as t1 where exists(    select * from student where id = t1.id and height > 173)select * from student where charindex(‘張‘, name, 1) >= 1select * from student where patindex(‘張_‘, name) >= 1select * from student order by name descalter table student add class int default(null)--迴圈賦值declare @no intset @no =1while @no < 30    begin        update student set class [email protected] where id=@no        set @no = @no + 1    endselect * from student order by sex,classselect class,year(birthday),count(id),avg(height),max(birthday) from student group by class,birthdayselect class,year(birthday),count(id),avg(height),max(birthday) from student group by class,birthday having class=1select class,year(birthday),count(id),avg(height),max(birthday) from student group by class,birthday having avg(height) > 165select class,sum(isnull(height,0)) from student group by classselect class,avg(height) from student group by classselect class,count(height) from student group by classselect class,max(height) from student group by classselect class,min(height) from student group by classselect class,count(distinct(height)) from student group by class

 

sql server 基本操作二