生成測試數(shù)據(jù)

if exists(select * from sysobjects where name='db_user') drop table db_user gocreate table db_user (員工編號 int primary key not null, --設(shè)置主鍵員工姓名 varchar(50) not null, 員工性別 char(2) not null,員工年齡 int not null,注冊日期 datetime not null,最后登錄 datetime not null,員工工資 float not null,員工標(biāo)識 varchar(50) not null, ) goDECLARE @fName TABLE(Id INT IDENTITY(1,1) PRIMARY KEY, NAME NVARCHAR(20)) -- 姓氏 DECLARE @lName TABLE(Id INT IDENTITY(1,1) PRIMARY KEY, NAME NVARCHAR(20)) -- 名字INSERT @fName VALUES ('趙'),('錢'),('孫'),('李'),('周'),('吳'),('鄭'),('王'),('馮'),('陳'),('楮'),('衛(wèi)'),('蔣'),('沈'),('韓'),('楊'), ('朱'),('秦'),('尤'),('許'),('何'),('呂'),('施'),('張'),('孔'),('曹'),('嚴(yán)'),('華'),('金'),('魏'),('陶'),('姜'), ('戚'),('謝'),('鄒'),('喻'),('柏'),('水'),('竇'),('章'),('云'),('蘇'),('潘'),('葛'),('奚'),('范'),('彭'),('郎'), ('魯'),('韋'),('昌'),('馬'),('苗'),('鳳'),('花'),('方'),('俞'),('任'),('袁'),('柳'),('酆'),('鮑'),('史'),('唐'), ('費'),('廉'),('岑'),('薛'),('雷'),('賀'),('倪'),('湯'),('滕'),('殷'),('羅'),('畢'),('郝'),('鄔'),('安'),('常'), ('樂'),('于'),('時'),('傅'),('皮'),('卞'),('齊'),('康'),('伍'),('余'),('元'),('卜'),('顧'),('孟'),('平'),('黃'), ('和'),('穆'),('蕭'),('尹')INSERT @lName VALUES ('愛'),('安'),('百'),('邦'),('寶'),('保'),('抱'),('貝'),('倍'),('蓓'),('本'), ('必'),('碧'),('璧'),('斌'),('冰'),('兵'),('炳'),('步'),('彩'),('曹'),('昌'),('長'),('常'),('超'), ('朝'),('陳'),('晨'),('成'),('呈'),('承'),('誠'),('崇'),('楚'),('傳'),('春'),('純'),('翠'),('村'), ('殿'),('丁'),('定'),('東'),('冬'),('二'),('凡'),('方'),('芳'),('昉'),('飛'),('菲'),('紛'),('芬'), ('奮'),('風(fēng)'),('峰'),('鋒'),('鳳'),('芙'),('福'),('付'),('復(fù)'),('富'),('改'),('剛'),('高'),('閣'), ('鉻'),('根'),('庚'),('耕'),('公'),('功'),('冠'),('光'),('廣'),('歸'),('桂'),('國'),('海'),('寒'), ('翰'),('昊'),('浩'),('荷'),('紅'),('宏'),('洪'),('鴻'),('厚'),('華'),('存'),('大'),('丹'),('道'), ('德'),('登'),('砥'),('典'),('佃')declare @index int set @index = 1while(@index <= 30000) begin-- 隨機(jī)生成姓名declare @tempName varchar(10)set @tempName= (SELECT RTRIM((SELECT NAME FROM @fName WHERE Id = Round(Rand()*(100-1)+1,0)))+RTRIM(LTRIM((SELECT NAME FROM @lName WHERE Id = Round(Rand()*(100-1)+1,0))))+RTRIM(LTRIM((SELECT NAME FROM @lName WHERE Id = Round(Rand()*(100-1)+1,0)))))-- 隨機(jī)生成年齡declare @tempAge intset @tempAge = Round(Rand()*(100-1)+1,1)-- 隨機(jī)生成性別declare @in intdeclare @tempSex char(2)set @in = Round(Rand()*(2-1)+1,0) -- 生成隨機(jī)數(shù)1-2if(@in = 1)beginset @tempSex = '男'endelse beginset @tempSex = '女'end-- 隨機(jī)生成浮點數(shù)的工資declare @float_main float,@float_from int,@tempWages floatset @float_main = Round(Rand(),2)set @float_from = Round(Rand()*(10000-1)+1,1)set @tempWages = @float_main+@float_frominsert into dbo.db_user(員工編號,員工姓名,員工性別,員工年齡,注冊日期,最后登錄,員工工資,員工標(biāo)識)values(@index,@tempName,@tempSex,@tempAge,GETDATE()-Round(Rand()*(1000-1)+1,1),GETDATE()+Round(Rand()*(1000-1)+1,1),@tempWages,replace(newid(), '-', ''))set @index = @index+1 end-- 隨機(jī)布爾值 --SELECT CAST(ROUND(RAND(),0) AS BIT) --SELECT ROUND(RAND(),0) -- 生成2位隨機(jī)數(shù)方法1 --select cast(floor(rand()*100) as int) --select cast(ceiling(rand()*100) as int)

最常見的查詢語句:

-- 查版本 select @@VERSION-- 查數(shù)據(jù)庫啟動時間 select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1-- 查看數(shù)據(jù)庫服務(wù)器名和實例名 print '服務(wù)名稱:' + convert(varchar(30),@@SERVERNAME) print '實例名稱:' + convert(varchar(30),@@SERVICENAME) select * from INFORMATION_SCHEMA.TABLES -- 查詢表 select * from INFORMATION_SCHEMA.COLUMNS -- 查詢字段 select SUSER_NAME() -- 返回當(dāng)前登錄用戶 select USER_NAME() -- 返回數(shù)據(jù)庫用戶名標(biāo)識 select DB_NAME() -- 返回當(dāng)前所在數(shù)據(jù)庫名稱 select IS_MEMBER('db_owner') -- 是否為db_owner角色exec sys.sp_configure -- 查看數(shù)據(jù)庫啟動參數(shù) exec sys.sp_databases -- 查詢所有數(shù)據(jù)庫 exec sp_helpdb -- 查看所有數(shù)據(jù)庫名稱及大小 exec xp_msver -- 查系統(tǒng)詳細(xì)信息 exec sp_helplogins -- 查看所有數(shù)據(jù)庫用戶登錄信息 exec sp_helpsrvrolemember -- 查看所有數(shù)據(jù)庫用戶所屬的角色信息 exec sp_helplinkedsrvlogin -- 查看鏈接服務(wù)器 exec sp_who 'active' -- 查看數(shù)據(jù)庫里用戶和進(jìn)程的信息-- 查詢數(shù)據(jù)庫所有記錄 select * from dbo.db_user;-- 查詢員工姓名并自動去重，關(guān)鍵詞 DISTINCT 用于返回唯一不同的值 select DISTINCT 員工姓名 from dbo.db_user;-- 查詢數(shù)據(jù)庫中前10條記錄 select TOP 10 * from dbo.db_user; select TOP 10 員工姓名,員工年齡 from dbo.db_user;-- where 語句增加過濾條件 select * from dbo.db_user where (員工編號 >=100) and (員工編號 <= 200) select * from dbo.db_user where 員工編號 BETWEEN 100 and 200-- in 查詢指定的多條記錄: 查詢員工編號是100,200,300的記錄 select * from dbo.db_user where 員工編號 in(100,200,300)-- 常用排序 select * from dbo.db_user order by 員工年齡 -- 以員工年齡升序排列 select * from dbo.db_user order by 員工年齡 desc -- 以員工年齡降序排列-- 常用統(tǒng)計 select COUNT(*) from dbo.db_user where 員工性別='女' select MAX(員工年齡) from dbo.db_user select AVG(員工工資) from dbo.db_user where 員工編號 >=100 and 員工編號 <= 300 select COUNT(DISTINCT 員工姓名) from dbo.db_user where 員工姓名 like '王%' -- 關(guān)鍵字匹配查找 select * from dbo.db_user where 員工姓名 like '王%' -- 匹配開頭是王的所有人 select * from dbo.db_user where 員工姓名 like '%廣翠' -- 匹配結(jié)尾是廣翠的所有人 select * from dbo.db_user where 員工姓名 like '%廣%' -- 匹配包含所有廣字的記錄 select * from dbo.db_user where 員工姓名 not like '%廣%' -- 匹配不包含廣字的記錄 select * from dbo.db_user where 員工姓名 like '王_邦' -- 匹配王中間任意字符結(jié)尾是邦 select * from dbo.db_user where 員工姓名 like '__邦' select * from dbo.db_user where 員工姓名 like '[王任金]%' -- 匹配開頭是[王 任 金]的任意字符-- 找員工工資最高和最小的員工,并把他的姓名工資輸出 select 員工姓名,員工工資 from dbo.db_user where 員工工資=(select MAX(員工工資) from dbo.db_user) select 員工姓名,員工工資 from dbo.db_user where 員工工資=(select MIN(員工工資) from dbo.db_user)-- 統(tǒng)計員工姓名重復(fù)出現(xiàn)的次數(shù) select 員工姓名,COUNT(*) AS 姓名的出現(xiàn)次數(shù) from dbo.db_user group by 員工姓名;-- 取別名 select 員工姓名 AS 姓名,員工性別 AS 性別 from dbo.db_user;

查詢練習(xí): 最后的查詢練習(xí).

-- 創(chuàng)建學(xué)生表 create table Student(SID varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10)); insert into Student values('01' , '趙雷' , '1990-01-01' , '男'),('02' , '錢電' , '1990-12-21' , '男'),('03' , '孫風(fēng)' , '1990-05-20' , '男'),('04' , '李云' , '1990-08-06' , '男'),('05' , '周梅' , '1991-12-01' , '女'),('06' , '吳蘭' , '1992-03-01' , '女'),('07' , '鄭竹' , '1989-07-01' , '女'),('08' , '王菊' , '1990-01-20' , '女'),('09' , '王吳宏' , '1997-12-20' , '女');-- 創(chuàng)建課程表 create table Course(CID varchar(10),Cname nvarchar(10),TID varchar(10)); insert into Course values('01' , '語文' , '02'),('02' , '數(shù)學(xué)' , '01'),('03' , '英語' , '03');-- 創(chuàng)建教師表 create table Teacher(TID varchar(10),Tname nvarchar(10)); insert into Teacher values('01' , '張老師'),('2','李老師'),('3','王老師');-- 創(chuàng)建成績表 create table StudentScore(SID varchar(10),CID varchar(10),score decimal(18,1)); insert into StudentScore values('01' , '01' , 80),('01' , '02' , 90),('01' , '03' , 99),('02' , '01' , 70),('02' , '02' , 60) ,('02' , '03' , 80),('03' , '01' , 75),('03' , '02' , 55),('03' , '03' , 80),('04' , '01' , 50),('04' , '02' , 30),('04' , '03' , 20) ,('05' , '01' , 76),('05' , '02' , 87),('06' , '01' , 31),('06' , '03' , 34),('07' , '02' , 89),('07' , '03' , 98);

練習(xí)記錄：

select Student.SID,Student.Sname from Student join (select avg(score) as avg_score,SID from StudentScore group by SID having avg_score >= 80 ) StudentScore on Student.SID = StudentScore.SID;select Student.SID,Student.Sname from Student join ( select SID,avg(score) as avg_score from StudentScore group by SID having avg_score >=80 )StudentScore on Student.SID = StudentScore.SID;select Student.SID,Student.Sname from Student join ( select SID,avg(score) as avg_score from StudentScore group by SID having avg_score >=80 )StudentScore on Student.SID = StudentScore.SID;select Course.CID,Course.Cname from Course join( select CID from lyshark.StudentScore where SID = (select SID from lyshark.Student where Sname='周梅') )StudentScore on Course.CID = StudentScore.CID;select Course.CID,Course.Cname from Course join( select CID from lyshark.StudentScore where SID = (select SID from lyshark.Student where Sname='周梅') )StudentScore on Course.CID = StudentScore.CID;select Course.CID,Course.Cname from Course join( select CID from lyshark.StudentScore where SID= (select SID from lyshark.Student where Sname='孫風(fēng)') )StudentScore on StudentScore.CID = Course.CID;select Teacher.Tname from Teacher join( select Course.TID from Course join( select CID from StudentScore where SID = (select SID from Student where Sname='孫風(fēng)') ) as StudentScore on StudentScore.CID = Course.CID; ) as Course on Teacher.TID = Course.TID;select * from Student where SID not in (Select SID from StudentScore where CID ='01' or CID = '02');select Student.*,Course.Cname,Teacher.Tname from Teacher,Course,Student,StudentScore where Teacher.Tname='王老師' and Teacher.TID = Course.TID and Course.CID = StudentScore.CID and StudentScore.SID = Student.SID;select Student.* from (select SID,count(CID) from StudentScore GROUP BY StudentScore.SID);select distinct a.SID,a.Sname from Student as a join StudentScore b on a.SID = b.SID join Course c on c.CID = b.CID join Teacher d on d.TID = c.TID where d.Tname = '張老師';select m.* from Student m where SID in(select SID from(select distinct SID from StudentScore where CID = '01'union allselect distinct SID from StudentScore where CID = '02')as t GROUP BY SID having count(*)=2 )order by m.SID;select Student.* from Student join StudentScore on Student.SID = StudentScore.SID where CID = '02' and exists (select 1 from StudentScore sc_2 where sc_2.SID = StudentScore.SID and sc_2.CID='01');select Student.* from Student join StudentScore on Student.SID = StudentScore.SID where CID = '01' and exists (select 1 from StudentScore sc_2 where sc_2.SID = StudentScore.SID and sc_2.CID='02');select Student.* from Student join StudentScore on Student.SID = StudentScore.SID where CID = '01' and not exists (select 1 from StudentScore sc where sc.SID = StudentScore.SID and sc.CID='02');select Student.SID,Student.Sname from Student join ( select SID,avg(score) as avg_score from StudentScore group by SID having avg_score >=80 )StudentScore on Student.SID = StudentScore.SID;select Student.Sname from Student join( select SID from StudentScore WHERE score<60 group by SID having count(*)>=2 )StudentScore on Student.SID = StudentScore.SID;-- 查詢性李的老師的個數(shù) select count(Tname) as 老師數(shù)量 from lyshark.Teacher where Tname like '李%'; select count(Tname) as 老師數(shù)量 from lyshark.Teacher where left(Tname,1)='李';-- 查詢男生女生人數(shù) select count(Ssex) as 男生人數(shù) from lyshark.Student where Ssex='男'; select sum(case when Ssex='男' then 1 else 0 end) as 男生人數(shù) from lyshark.Student;-- 統(tǒng)計男女人數(shù)情況 select case when Ssex='男' then '男生人數(shù)' else '女生人數(shù)' end as 男女情況,count(*) as 總?cè)藬?shù) from lyshark.Student group by case when Ssex='男' then '男生人數(shù)' else '女生人數(shù)' end;-- 查詢同名同性學(xué)生名單，并統(tǒng)計人數(shù) select Sname as 姓名,count(*) as 人數(shù) from lyshark.Student group by Sname having count(*) >1;-- 查詢1990年出生的學(xué)生名單 select * from Student where year(sage) = 1990; select * from Student where Sage = '1990-08-06 00:00:00';-- 查詢兩門及其以上不及格課程的同學(xué)的學(xué)號與姓名 select SID from StudentScore WHERE score<60 group by SID having count(*)>=2;select Student.Sname from Student join(select SID from StudentScore WHERE score<60 group by SID having count(*)>=2 )StudentScore on Student.SID = StudentScore.SID;-- 查詢兩門及其以上不及格課程的同學(xué)的學(xué)號、姓名及其平均成績 select Student.SID,Student.sname,avg(StudentScore.score) as avg_score from Student join StudentScore on Student.SID = StudentScore.SID where Student.SID in(select SID from StudentScore where score < 60 group by SID having count(*) >= 2) group by Student.SID,Student.sname;-- 查詢Student中不存在CID編號 01-02的行 select * from Student where SID not in (Select SID from StudentScore where CID ='01' or CID = '02');-- 查詢學(xué)習(xí)過張老師課程的同學(xué)信息 select Student.*,Course.Cname,Teacher.Tname from Teacher,Course,Student,StudentScore where Teacher.Tname='張老師' and Teacher.TID = Course.TID and Course.CID = StudentScore.CID and StudentScore.SID = Student.SID;select distinct a.SID,a.Sname from Student as a join StudentScore b on a.SID = b.SID join Course c on c.CID = b.CID join Teacher d on d.TID = c.TID where d.Tname = '張老師';-- 查詢學(xué)過編號為"01"并且也學(xué)過編號為"02"的課程的同學(xué)的信息 select m.* from Student m where SID in(select SID from(select distinct SID from StudentScore where CID = '01'union allselect distinct SID from StudentScore where CID = '02')as t GROUP BY SID having count(*)=2 )order by m.SID;select Student.* from Student join StudentScore on Student.SID = StudentScore.SID where CID = '02' and exists (select 1 from StudentScore sc_2 where sc_2.SID = StudentScore.SID and sc_2.CID='01');select Student.* from Student join StudentScore on Student.SID = StudentScore.SID where CID = '01' and exists (select 1 from StudentScore sc_2 where sc_2.SID = StudentScore.SID and sc_2.CID='02');-- 查詢學(xué)過編號為"01"但是沒有學(xué)過編號為"02"的課程的同學(xué)的信息 select Student.* from Student join StudentScore on Student.SID = StudentScore.SID where CID = '01' and not exists (select 1 from StudentScore sc where sc.SID = StudentScore.SID and sc.CID='02');-- 查詢平均成績及大于80分的學(xué)生 select SID,avg(score) as avg_score from StudentScore group by SID having avg_score >= 70;select Student.SID,Student.Sname,StudentScore.avg_score from Student join (select avg(score) as avg_score,SID from StudentScore group by SID having avg_score >= 80 ) StudentScore on Student.SID = StudentScore.SID;-- 查詢周梅同學(xué)的平均成績,和他學(xué)過的專業(yè)課 select avg(score) from lyshark.StudentScore where SID = (select SID from lyshark.Student where Sname='周梅');select Course.CID,Course.Cname from Course join( select CID from lyshark.StudentScore where SID = (select SID from lyshark.Student where Sname='周梅') )as StudentScore on Course.CID = StudentScore.CID;

總結(jié)

以上是生活随笔為你收集整理的SQL Server 常用查询练习的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。