SQL Server(SQL Server数据库管理系统)是由Microsoft开发的关系型数据库管理系统(RDBMS),用于管理和存储大规模的数据。它是一种强大的数据库平台,广泛用于企业和组织中,以支持数据存储、管理、查询和分析。
查询语句是用于与关系型数据库进行交互的命令或语句。它们允许您执行各种操作,包括检索数据、插入、更新和删除数据,以及创建、修改和删除数据库表和其他数据库对象。
生成测试数据
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;