sqlserver

生成测试数据

if exists(select * from sysobjects where name='db_user')
drop table db_user
go

create table db_user
(
	员工编号 int primary key not null,  --设置主键
	员工姓名 varchar(50) not null, 
	员工性别 char(2) not null,
	员工年龄 int not null,
	注册日期 datetime not null,
	最后登录 datetime not null,
	员工工资 float not null,
	员工标识 varchar(50) not null,
)
go

DECLARE @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
('赵'),('钱'),('孙'),('李'),('周'),('吴'),('郑'),('王'),('冯'),('陈'),('楮'),('卫'),('蒋'),('沈'),('韩'),('杨'),
('朱'),('秦'),('尤'),('许'),('何'),('吕'),('施'),('张'),('孔'),('曹'),('严'),('华'),('金'),('魏'),('陶'),('姜'),
('戚'),('谢'),('邹'),('喻'),('柏'),('水'),('窦'),('章'),('云'),('苏'),('潘'),('葛'),('奚'),('范'),('彭'),('郎'),
('鲁'),('韦'),('昌'),('马'),('苗'),('凤'),('花'),('方'),('俞'),('任'),('袁'),('柳'),('酆'),('鲍'),('史'),('唐'),
('费'),('廉'),('岑'),('薛'),('雷'),('贺'),('倪'),('汤'),('滕'),('殷'),('罗'),('毕'),('郝'),('邬'),('安'),('常'),
('乐'),('于'),('时'),('傅'),('皮'),('卞'),('齐'),('康'),('伍'),('余'),('元'),('卜'),('顾'),('孟'),('平'),('黄'),
('和'),('穆'),('萧'),('尹')

INSERT @lName VALUES ('爱'),('安'),('百'),('邦'),('宝'),('保'),('抱'),('贝'),('倍'),('蓓'),('本'),
('必'),('碧'),('璧'),('斌'),('冰'),('兵'),('炳'),('步'),('彩'),('曹'),('昌'),('长'),('常'),('超'),
('朝'),('陈'),('晨'),('成'),('呈'),('承'),('诚'),('崇'),('楚'),('传'),('春'),('纯'),('翠'),('村'),
('殿'),('丁'),('定'),('东'),('冬'),('二'),('凡'),('方'),('芳'),('昉'),('飞'),('菲'),('纷'),('芬'),
('奋'),('风'),('峰'),('锋'),('凤'),('芙'),('福'),('付'),('复'),('富'),('改'),('刚'),('高'),('阁'),
('铬'),('根'),('庚'),('耕'),('公'),('功'),('冠'),('光'),('广'),('归'),('桂'),('国'),('海'),('寒'),
('翰'),('昊'),('浩'),('荷'),('红'),('宏'),('洪'),('鸿'),('厚'),('华'),('存'),('大'),('丹'),('道'),
('德'),('登'),('砥'),('典'),('佃')

declare @index int
set @index = 1

while(@index <= 30000)
begin
	-- 随机生成姓名
	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)))))

	-- 随机生成年龄
	declare @tempAge int
	set @tempAge = Round(Rand()*(100-1)+1,1)

	-- 随机生成性别
	declare @in int
	declare @tempSex char(2)
	set @in = Round(Rand()*(2-1)+1,0) -- 生成随机数1-2
	if(@in = 1)
	begin
		set @tempSex = '男'
	end
	else begin
		set @tempSex = '女'
	end

	-- 随机生成浮点数的工资
	declare @float_main float,@float_from int,@tempWages float
	set @float_main = Round(Rand(),2)
	set @float_from = Round(Rand()*(10000-1)+1,1)
	set @tempWages = @float_main+@float_from

	insert into dbo.db_user(员工编号,员工姓名,员工性别,员工年龄,注册日期,最后登录,员工工资,员工标识)
	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

-- 随机布尔值
--SELECT CAST(ROUND(RAND(),0) AS BIT)
--SELECT ROUND(RAND(),0) 

-- 生成2位随机数方法1
--select cast(floor(rand()*100) as int)
--select cast(ceiling(rand()*100) as int)

最常见的查询语句:

-- 查版本
select @@VERSION

-- 查数据库启动时间
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1

-- 查看数据库服务器名和实例名
print '服务名称:' + convert(varchar(30),@@SERVERNAME)
print '实例名称:' + convert(varchar(30),@@SERVICENAME)
select * from INFORMATION_SCHEMA.TABLES  -- 查询表
select * from INFORMATION_SCHEMA.COLUMNS -- 查询字段
select SUSER_NAME()  -- 返回当前登录用户
select USER_NAME()   -- 返回数据库用户名标识
select DB_NAME()     -- 返回当前所在数据库名称
select IS_MEMBER('db_owner')    -- 是否为db_owner角色

exec sys.sp_configure   -- 查看数据库启动参数
exec sys.sp_databases   -- 查询所有数据库
exec sp_helpdb   -- 查看所有数据库名称及大小
exec xp_msver  -- 查系统详细信息
exec sp_helplogins -- 查看所有数据库用户登录信息
exec sp_helpsrvrolemember -- 查看所有数据库用户所属的角色信息
exec sp_helplinkedsrvlogin -- 查看链接服务器
exec sp_who 'active'  -- 查看数据库里用户和进程的信息


-- 查询数据库所有记录
select * from dbo.db_user;

-- 查询员工姓名并自动去重，关键词 DISTINCT 用于返回唯一不同的值
select DISTINCT 员工姓名 from dbo.db_user;

-- 查询数据库中前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  -- 以员工年龄降序排列

-- 常用统计
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 '王%' 

-- 关键字匹配查找
select * from dbo.db_user where 员工姓名 like '王%'        -- 匹配开头是王的所有人
select * from dbo.db_user where 员工姓名 like '%广翠'      -- 匹配结尾是广翠的所有人
select * from dbo.db_user where 员工姓名 like '%广%'       -- 匹配包含所有广字的记录
select * from dbo.db_user where 员工姓名 not like '%广%'   -- 匹配不包含广字的记录
select * from dbo.db_user where 员工姓名 like '王_邦'      -- 匹配王中间任意字符结尾是邦
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)

-- 统计员工姓名重复出现的次数
select 员工姓名,COUNT(*) AS 姓名的出现次数 from dbo.db_user group by 员工姓名;

-- 取别名
select 员工姓名 AS 姓名,员工性别 AS 性别 from dbo.db_user;

查询练习: 最后的查询练习.

-- 创建学生表
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' , '孙风' , '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' , '女');

-- 创建课程表
create table Course(CID varchar(10),Cname nvarchar(10),TID varchar(10));
insert into Course values('01' , '语文' , '02'),('02' , '数学' , '01'),('03' , '英语' , '03');

-- 创建教师表
create table Teacher(TID varchar(10),Tname nvarchar(10));
insert into Teacher values('01' , '张老师'),('2','李老师'),('3','王老师');

-- 创建成绩表
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);

练习记录：

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='孙风')
)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='孙风')
) 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 all
		select 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;

-- 查询性李的老师的个数
select count(Tname) as 老师数量 from lyshark.Teacher where Tname like '李%';
select count(Tname) as 老师数量 from lyshark.Teacher where left(Tname,1)='李';

-- 查询男生女生人数
select count(Ssex) as 男生人数 from lyshark.Student where Ssex='男';
select sum(case when Ssex='男' then 1 else 0 end) as 男生人数 from lyshark.Student;

-- 统计男女人数情况
select case when Ssex='男' then '男生人数' else '女生人数' end as 男女情况,count(*) as 总人数
from lyshark.Student group by case when Ssex='男' then '男生人数' else '女生人数' end;

-- 查询同名同性学生名单，并统计人数
select Sname as 姓名,count(*) as 人数 from lyshark.Student group by Sname having count(*) >1;

-- 查询1990年出生的学生名单
select * from Student where year(sage) = 1990;
select * from Student where Sage = '1990-08-06 00:00:00';

-- 查询两门及其以上不及格课程的同学的学号与姓名
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;

-- 查询两门及其以上不及格课程的同学的学号、姓名及其平均成绩
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');

-- 查询学习过张老师课程的同学信息
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 = '张老师';

-- 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select m.* from Student m where SID in(
    select SID from
    (
        select distinct SID from StudentScore where CID = '01'
        union all
        select 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');

-- 查询学过编号为"01"但是没有学过编号为"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');

-- 查询平均成绩及大于80分的学生
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;

-- 查询周梅同学的平均成绩,和他学过的专业课
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;