SQL学习笔记

入门

查询

聚合和排序

更新数据

高级查询

函数、谓词、case

集合运算

use master;
go

-- go 进入批处理
create database Morning;
go

use Morning
go
-- [dbo]表存储的架构，可以修改
create table [dbo].[ScoreInfo](
	[Id] [int] identity(1,1) not null,
	[name] [varchar](50) not null,
	[course] [varchar](50) null,
	[score] [int] null
) on [primary]
go

-- 创建表
create table [dbo].[ScoreInfo1](
	[Id] [int] identity(1,1) not null,
	[name] [varchar](50) not null,
	[course] [varchar](50) null,
	[score] [int] null
) on [primary]
go

--删除表
if exists(select * from sysobjects where name='ScoreInfo') drop table [dbo].[ScoreInfo1] 
go

--------------1. 新增数据
insert into ScoreInfo ([name],course,score) values ('cat4','精英班',53);
go
insert into ScoreInfo ([name],course,score) values ('cat4','菜鸟班',76);

--------------2. 查询
select * from ScoreInfo;
select [Id],[name],[course],[score] from [Morning].[dbo].[ScoreInfo];
-- 别名
select 
	[Id] as 标识,
	[name] as '名称',
	[course] '课程',
	[score]
	from [Morning].[dbo].[ScoreInfo];

-- 条件查询 where
select 
	[Id] as 标识,
	[name] as '名称',
	[course] '课程',
	[score]
	from [Morning].[dbo].[ScoreInfo] where score >= 60 and score <= 90;
-- between a and b  a和b之间
select 
	[Id] as 标识,
	[name] as '名称',
	[course] '课程',
	[score]
	from [Morning].[dbo].[ScoreInfo] where score between  60 and  90;
-- null查询
select 
	[Id] as 标识,
	[name] as '名称',
	[course] '课程',
	[score]
	from [Morning].[dbo].[ScoreInfo] where course is null;
-- 查询前多少行/按比例查询结果
select top 2 * from ScoreInfo;
select top(40) percent * from ScoreInfo;

-- 分支判断1（可用大于小于等于） case when then end
select top(100) Id,name,score,
	case when score < 90 then '不及格'
		 when score >= 90 and score < 120 then '及格'
		 when score >= 120 and score < 130 then '良好'
		 when score >= 130 then '优秀'
		 else '0'
		 end as '等级'
from Morning.dbo.ScoreInfo order by score asc;
-- 分支判断2（等于） case when then end
select top(1000) Id,name,score,
	case course when '小班' then '小班人才'
		        when '大班' then '大班人才'
		        when '菜鸟班'then '菜鸟'
		        else '0'
		        end as '班'
from Morning.dbo.ScoreInfo order by score asc;

-- in 查询 （包含）
select top(10) 
	Id,name,course,score
from Morning.dbo.ScoreInfo where id in (2,4,9)
--** like 查询 （模糊查询  和 % 通配符一起使用）
-- 嘎%：必须以嘎开头
-- %嘎：必须以嘎结尾
-- %嘎%：包含嘎就行
-- 如果要对搜索框进行模糊查询，关键字匹配：'%'+keyword+'%'
select 
	[Id],
	[name],
	[course],
	[score]
	from [Morning].[dbo].[ScoreInfo] where [name] like '嘎%';

-- With 关键字 表示一个片段
-- 把变量指向一个片段s  便于做连接查询，提高代码的阅读性
with s as(select 
	[Id],
	[name],
	[course],
	[score]
from [Morning].[dbo].[ScoreInfo])
select * from s
-- 子查询/exists关键字
-- 做子查询一般推荐使用exists 关键字（效率高）
select * from [Morning].[dbo].[ScoreInfo]
	where id in (select Id from [Morning].[dbo].[ScoreInfo] where [name]='cat')

select [Id],
	[name],
	[course],
	[score]
from [Morning].[dbo].[ScoreInfo] as t1
	where exists (select [Id],[name],[course],[score] 
	              from [Morning].[dbo].[ScoreInfo] as t2 
	                   where t1.Id = t2.Id and t2.name = 'cat')
-- 去重 distinct 同一列去掉重复
select distinct course from Morning.dbo.ScoreInfo order by course desc

-- 排序 order by （单列、多列：从左往右优先级） 升序asc 降序desc
-- 单列
select 
	[Id] as 标识,
	[name] as '名称',
	[course] '课程',
	[score]
	from [Morning].[dbo].[ScoreInfo] order by [name] asc;
-- 多列
select 
	[Id] as 标识,
	[name] as '名称',
	[course] '课程',
	[score]
	from [Morning].[dbo].[ScoreInfo] order by [name],[course] asc;

-- 聚合 group by 
-- 把多行汇总成一行 对于不能汇总的列，可以进行sum、count等操作
-- 查出每个人的总分
select name,sum(score) secoreSum,count(name) personCount 
	from Morning.dbo.ScoreInfo group by name
----------- 更新
update ScoreInfo set score = 86,course = '精英班1' where id = 1;

-- 分页查询1：必须带有主键Id，且主键Id是标识列，必须是自增的
declare @pagesize int; --每一页数据的数量
select @pagesize = 5;
declare @pageindex int; -- 那一页
select @pageindex = 1;

select * from ScoreInfo

--1
select top(@pagesize)  * 
from ScoreInfo
where id not in
(
	select top(@pagesize*(@pageindex-1)) id from ScoreInfo order by id
)
order by id
--2 如果没有自增id时，通过row_number添加
select top(@pagesize)  * 
from
(
	select ROW_NUMBER() over(order by id) as rownumber,* from ScoreInfo
) A
where rownumber > (@pagesize*(@pageindex-1))
--3 
select * from [Morning].[dbo].[ScoreInfo] order by Id
	offset((@pagesize*(@pageindex-1))) -- 间隔多少条才开始
	rows fetch next (@pagesize) -- 获取多少条
	rows only
-- 删除数据
delete ScoreInfo where id = 1;

---------- 表备份
select * into ScoreInfoNew from ScoreInfo
------- 表复制
insert into [dbo].[ScoreInfoNew] select [name],[course],[score] from ScoreInfo

---------union：将两个查询的结果连接到一个结果集中。 可控制结果集是否包含重复行：
--UNION ALL - 包括重复行。
--UNION - 排除重复行。自动去重
select 
	[Id] as 标识,
	[name] as '名称',
	[course] '课程',
	[score]
	from [Morning].[dbo].[ScoreInfo] where name='cat'
	union all
select 
	[Id] as 标识,
	[name] as '名称',
	[course] '课程',
	[score]
	from [Morning].[dbo].[ScoreInfo] where name='cat'