526互联

SqlServer基本操作

发布时间 2023-07-17 10:20:58作者: 风筝遇上风

一、创建数据库和表

1、工具建库建表

2、脚本建库建表

USE [master]
GO
CREATE DATABASE [ZhaoXiEdu]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'ScoreInfo', FILENAME = N'D:\ScoreInfo.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH
= 65536KB )
LOG ON
( NAME = N'ScoreInfo_log', FILENAME = N'D:\ScoreInfo_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB ,
FILEGROWTH = 65536KB )
WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO

GO
CREATE TABLE [dbo].[ScoreInfo](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](255) NULL,
[course] [varchar](255) NULL,
[score] [int] NULL
) ON [PRIMARY]
GO

3、工具删除数据库删除表

4、脚本删除数据库删除表

USE [master]
GO
if db_id('ZhaoXiEdu') is not null
Drop database [ZhaoXiEdu]
USE [master]
GO
if EXISTS (SELECT * from sysobjects WHERE name='ScoreInfo')
Drop table [ScoreInfo]

二、数据基本CRUD

对于表中中数据的操作。

1、查询

use ZhaoxiEdu
SELECT 字段名称
,字段名称
,字段名称
,字段名称
FROM 表名称
SELECT *
FROM 表名称

2、新增

use ZhaoxiEdu
GO
insert into ScoreInfo(name, course, score) values ('张三','高级班', 80)
insert into ScoreInfo(name, course, score) values ('张三','架构班', 52)
insert into ScoreInfo(name, course, score) values ('张三','上位机班', 150)
insert into ScoreInfo(name, course, score) values ('李四','高级班', 44)
insert into ScoreInfo(name, course, score) values ('李四','数学', 111)
insert into ScoreInfo(name, course, score) values ('李四','英语', 110)
insert into ScoreInfo(name, course, score) values ('王五','高级班', 140)
insert into ScoreInfo(name, course, score) values ('王五','数学', 80)
insert into ScoreInfo(name, course, score) values ('王五','英语', 92)
insert into ScoreInfo(name, course, score) values ('王五','物理', 77)
insert into ScoreInfo(name, course, score) values ('王五','化学', 65)

3、修改

use ZhaoxiEdu
GO
UPDATE [dbo].[ScoreInfo]
SET [name] = <name, varchar(255),>
,[course] = <course, varchar(255),>
,[score] = <score, int,>
WHERE <搜索条件,,>

4、删除

USE [ZhaoXiEdu]
GO
DELETE FROM [dbo].[ScoreInfo]
WHERE <搜索条件,,>
GO

三、进阶查询

1、别名，查询结果拼接

SELECT TOP (1000) [Id] 主键
,[name]
,[course]
,[score]
FROM [ZhaoxiEdu].[dbo].[ScoreInfo]

2、条件查询

SELECT TOP (1000) [Id] 主键
,[name]
,[course]
,[score]
FROM [ZhaoxiEdu].[dbo].[ScoreInfo] where id=4

3、范围查询

SELECT TOP (1000) [Id] 主键
,[name]
,[course]
,[score]
FROM [ZhaoxiEdu].[dbo].[ScoreInfo] where id>5 and id<10
SELECT TOP (1000) [Id] 主键
,[name]
,[course]
,[score]
FROM [ZhaoxiEdu].[dbo].[ScoreInfo] where id between 4 and 10

4、null判断

SELECT TOP (1000) [Id] 主键
,[name]
,[course]
,[score]
FROM [ZhaoxiEdu].[dbo].[ScoreInfo] where course is null

5、查询前多少行/按比例查询结果

select top 3 * from ScoreInfo
select top(20) percent * from ScoreInfo

6、case when 判断

#案例1
#分数有个范围：<90 不及格
90--120--及格
>120 良好
SELECT TOP (1000) [Id]
,[name]
,[course]
,[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 '鸭蛋'
end as '等级'
FROM [ZhaoxiEdu].[dbo].[ScoreInfo] order by [score] asc
#案例2
SELECT TOP (1000) [Id]
,[name]
,[course]
,[score]
,case course when '高级班' then '走高高级开发'
when '架构班' then '走向架构师'
when '全栈班' then '走向全栈开发'
end as '开发方向'
FROM [ZhaoxiEdu].[dbo].[ScoreInfo] order by [score] asc

7、in查询

SELECT TOP (1000) [Id]
,[name]
,[course]
,[score]
FROM [ZhaoxiEdu].[dbo].[ScoreInfo] where id in (4,5,6,7,8)

8、like查询

SELECT TOP (1000) [Id]
,[name]
,[course]
,[score]
FROM [ZhaoxiEdu].[dbo].[ScoreInfo] where [name] like '张%'

9、with关键字查询

with score as (SELECT * FROM [ZhaoxiEdu].[dbo].[ScoreInfo] )
select * from score where [name] ='张三'

10、子查询/exists关键字查询

#子查询
SELECT * FROM [ZhaoxiEdu].[dbo].[ScoreInfo] where id in (SELECT Id FROM [ZhaoxiEdu].[dbo].[ScoreInfo]
where [name]='张三')
#exists关键字
SELECT * FROM [ZhaoxiEdu].[dbo].[ScoreInfo] t1 where
exists(SELECT * FROM [ZhaoxiEdu].[dbo].[ScoreInfo] t2 where t1.Id=t2.Id and t2.[name]='张三')

11、复制新表/表数据复制

#复制新表
select * into ScoreInfo2 from ScoreInfo
#把另外一个结构相同的表数据复制到指定表中
insert [ZhaoxiEdu].[dbo].[ScoreInfo2] select [name]
,[course]
,[score] from [ZhaoxiEdu].[dbo].[ScoreInfo2]

12、distinct 同一列去掉重复

#普通查询某一列
SELECT score FROM [ZhaoxiEdu].[dbo].[ScoreInfo2] order by score desc
#查询某一列去掉重复
SELECT distinct(score) FROM [ZhaoxiEdu].[dbo].[ScoreInfo2] order by score desc

13、排序

#升序asc 降序desc 可以多列排序 从左往右优先级
with ttt as ( SELECT max(id) as id,[name],course,score FROM [ZhaoxiEdu].[dbo].[ScoreInfo2] group
by [name],course,score)
select * from ttt order by [course], score desc

14、聚合查询分组

#聚合---一般在汇总的时候需要用到
#汇总--每个人的总分
SELECT
[name]
,sum([score]) score
FROM [ZhaoxiEdu].[dbo].[ScoreInfo] group by [name]

15、分页查询

#必须带有主键Id，且主键Id是标识列，必须是自赠的
原理：需要拿出数据库的第5页，就是40-50条记录。首先拿出数据库中的前40条记录的id值，然后再拿出剩余部分的
前10条元素
declare @pagesize int;
select @pagesize=5;
declare @pageindex int;
select @pageindex=3;
select top (@pagesize) *
from ScoreInfoNew2
where id not in
(
--(10*(2-1)) 为页大小 * （当前第几页-1）
select top (@pagesize*(@pageindex-1)) id from ScoreInfoNew2 order by id
)
order by id
#分页查询二
原理：先查询前40条记录，然后获得其最id值，如果id值为null的，那么就返回0
然后查询id值大于前40条记录的最大id值的记录。
这个查询有一个条件，就是id必须是int类型的。
declare @pagesize int;
select @pagesize=2;
declare @pageindex int;
select @pageindex=1;
select top (@pagesize) *
from ScoreInfo
where id >
(
select isnull(max(id),0)
from
(
--(10*(2-1)) 为页大小 * （当前第几页-1）
select top ((@pagesize)*((@pageindex)-1)) id from ScoreInfo order by id
) A
)
order by id
#分页查询三
原理：先把表中的所有数据都按照一个rowNumber进行排序，然后查询rownuber大于40的前十条记录
这种方法和oracle中的一种分页方式类似，不过只支持2005版本以上的
declare @pagesize int;
select @pagesize=10;
declare @pageindex int;
select @pageindex=1;
select top (@pagesize) *
from
(
select row_number() over(order by id) as rownumber,* from ScoreInfo
) A
--(10*(2-1)) 为页大小 * （当前第几页-1）
where rownumber > ((@pagesize)*((@pageindex)-1))
#分页查询四
要求必须在SqlServer2012版本之后方可支持
declare @pagesize int;
select @pagesize=2;
declare @pageindex int;
select @pageindex=2;
SELECT *
FROM [ZhaoxiEdu].[dbo].[ScoreInfo] order by Id offset (@pagesize*(@pageindex-1)) --间隔多条条开始
rows fetch next (@pagesize) --获取多少条
rows only

16、union/union all操作

#可以把查询到的多个数据结构完全相同个表，合并起来
#union：自动去重 union all 不会去掉重复
SELECT TOP (1000) [Id]
,[name]
,[course]
,[score]
FROM [ZhaoxiEdu].[dbo].[ScoreInfo] where [name]='张三' union all
SELECT TOP (1000) [Id]
,[name]
,[course]
,[score]
FROM [ZhaoxiEdu].[dbo].[ScoreInfo] where [name]='张三'

17、行转列/列转行

#行转列--------------------------------------------------------------------------------
SELECT [name],
isnull(sum(case course when '高级班' then score end),0) as '高级班',
isnull(sum(case course when '架构班' then score end),0) as '架构班',
isnull(sum(case course when '上位机班' then score end),0) as '上位机班',
isnull(sum(case course when 'Web前端' then score end),0) as 'Web前端',
isnull(sum(case course when '全栈班' then score end),0) as '全栈班'
FROM [ZhaoxiEdu].[dbo].[ScoreInfo] group by [name]
#初始化数据表脚本--------------------------------------------------------------------------
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RowColumnConversion](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Advanced] [int] NULL,
[Framework] [int] NULL,
[UpperComputer] [int] NULL,
[Web] [int] NULL,
[FullStack] [nchar](10) NULL,
CONSTRAINT [PK_RowColumnConversion] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
#列转行--------------------------------------------------------------------------------
select row_number() over(order by id desc) as id,[name],t.course,t.score from(
select id,[name],course='高级班',score=[Advanced] from [ZhaoxiEdu].[dbo].[RowColumnConversion]
union all
select id,[name],course='架构班',score=Framework from [ZhaoxiEdu].[dbo].[RowColumnConversion]
union all
select id,[name],course='上位机班',score=UpperComputer from [ZhaoxiEdu].[dbo].[RowColumnConversion]
union all
select id,[name],course='Web前端',score=Web from [ZhaoxiEdu].[dbo].[RowColumnConversion]
union all
select id,[name],course='全栈班',score=FullStack from [ZhaoxiEdu].[dbo].[RowColumnConversion]
) t where score is not null order by id asc
,case t.course when '语文' then 1 when '数学' then 2 when '英语' then 3 when '物理' then 4 when '化学'
then 5 end
with tt as(
select id, [Name],course='高级班',score=[Advanced] FROM [ZhaoxiEdu].[dbo].[RowColumnConversion]
Union all
select id,[Name],course='架构班',score=[Framework] FROM [ZhaoxiEdu].[dbo].[RowColumnConversion]
Union all
select id,[Name],course='上位机',score=[UpperComputer] FROM [ZhaoxiEdu].[dbo].[RowColumnConversion]
Union all
select id,[Name],course='Web前端',score=[Web] FROM [ZhaoxiEdu].[dbo].[RowColumnConversion]
Union all
select id,[Name],course='全栈班',score=[FullStack] FROM [ZhaoxiEdu].[dbo].[RowColumnConversion])
select * from tt where score<>0 order by [Name] desc

18、左连接

#多个表通过字段之间的关系进行关联，在关联后，通过查询得到我们想要的数#据。
#数据准备--创建Company表--初始化数据
Use ZhaoxiEdu
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Company](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Name] [varchar](50) NULL,
[CreateTime] [datetime] NULL,
[CreatorId] [int] NOT NULL,
[LastModifierId] [int] NULL,
[LastModifyTime] [datetime] NULL,
CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
GO
SET IDENTITY_INSERT [dbo].[Company] ON
GO
INSERT [dbo].[Company] ([Id], [Name], [CreateTime], [CreatorId], [LastModifierId], [LastModifyTime])
VALUES (1, N'朝夕教育', CAST(N'2021-10-09T16:37:14.350' AS DateTime), 1, 1, CAST(N'2021-10-
09T16:37:14.350' AS DateTime))
GO
INSERT [dbo].[Company] ([Id], [Name], [CreateTime], [CreatorId], [LastModifierId], [LastModifyTime])
VALUES (2, N'阿里巴巴', CAST(N'2021-10-09T16:37:14.717' AS DateTime), 1, 1, CAST(N'2021-10-
09T16:37:14.717' AS DateTime))
GO
INSERT [dbo].[Company] ([Id], [Name], [CreateTime], [CreatorId], [LastModifierId], [LastModifyTime])
VALUES (3, N'百度', CAST(N'2021-10-09T16:37:14.717' AS DateTime), 1, 1, CAST(N'2021-10-09T16:37:14.717'
AS DateTime))
GO
INSERT [dbo].[Company] ([Id], [Name], [CreateTime], [CreatorId], [LastModifierId], [LastModifyTime])
VALUES (4, N'腾讯', CAST(N'2021-10-09T16:37:14.717' AS DateTime), 1, 1, CAST(N'2021-10-09T16:37:14.717'
AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[Company] OFF
#数据准备--创建SysUser表-初始化数据
Use ZhaoxiEdu
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SysUser](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Name] [varchar](50) NULL,
[CompanyId] [int] NULL,
[Password] [varchar](50) NULL,
[Status] [int] NOT NULL,
[Phone] [varchar](12) NULL,
[Mobile] [varchar](12) NULL,
[Address] [varchar](500) NULL,
[Email] [varchar](50) NULL,
[QQ] [bigint] NULL,
[WeChat] [varchar](50) NULL,
[Sex] [int] NULL,
[LastLoginTime] [datetime] NULL,
[CreateTime] [datetime] NULL,
[CreateId] [int] NULL,
[LastModifyTime] [datetime] NULL,
[LastModifyId] [int] NULL
CONSTRAINT [PK_SysUser] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
GO
SET IDENTITY_INSERT [dbo].[SysUser] ON
GO
INSERT [dbo].[SysUser] ([Id], [Name], [Password], [Status], [Phone], [Mobile], [Address], [Email],
[QQ], [WeChat], [Sex], [LastLoginTime], [CreateTime], [CreateId], [LastModifyTime], [LastModifyId],
[CompanyId]) VALUES (1, N'张三', N'123456', 1, N'18672713698', N'18672713698', N'武汉市',
N'18672713698@163.com', 123456789, NULL, 0, CAST(N'2020-12-15T00:00:00.000' AS DateTime), CAST(N'2020-
12-15T00:00:00.000' AS DateTime), 1, CAST(N'2020-12-15T00:00:00.000' AS DateTime), 1, 1)
GO
INSERT [dbo].[SysUser] ([Id], [Name], [Password], [Status], [Phone], [Mobile], [Address], [Email],
[QQ], [WeChat], [Sex], [LastLoginTime], [CreateTime], [CreateId], [LastModifyTime], [LastModifyId],
[CompanyId]) VALUES (3, N'李四', N'123456', 1, N'18672713698', N'18672713698', N'武汉市',
N'18672713698@163.com', 123456789, NULL, 0, CAST(N'2020-12-15T00:00:00.000' AS DateTime), CAST(N'2020-
12-15T00:00:00.000' AS DateTime), 1, CAST(N'2020-12-15T00:00:00.000' AS DateTime), 1, 1)
GO
INSERT [dbo].[SysUser] ([Id], [Name], [Password], [Status], [Phone], [Mobile], [Address], [Email],
[QQ], [WeChat], [Sex], [LastLoginTime], [CreateTime], [CreateId], [LastModifyTime], [LastModifyId],
[CompanyId]) VALUES (5, N'王五', N'Pass', 1, N'PHone', N'18672713698', N'abcc', N'', 123456798,
N'Wechat', 1, CAST(N'2021-10-09T15:03:43.673' AS DateTime), CAST(N'2021-10-09T15:03:43.673' AS
DateTime), 1, CAST(N'2021-10-09T15:03:43.677' AS DateTime), 1, 1)
GO
INSERT [dbo].[SysUser] ([Id], [Name], [Password], [Status], [Phone], [Mobile], [Address], [Email],
[QQ], [WeChat], [Sex], [LastLoginTime], [CreateTime], [CreateId], [LastModifyTime], [LastModifyId],
[CompanyId]) VALUES (6, N'赵六', N'Pass', 1, N'PHone', N'18672713698', N'abcc', N'', 123456798,
N'Wechat', 1, CAST(N'2021-10-09T15:11:06.473' AS DateTime), CAST(N'2021-10-09T15:11:06.473' AS
DateTime), 1, CAST(N'2021-10-09T15:11:06.473' AS DateTime), 1, 1)
GO
INSERT [dbo].[SysUser] ([Id], [Name], [Password], [Status], [Phone], [Mobile], [Address], [Email],
[QQ], [WeChat], [Sex], [LastLoginTime], [CreateTime], [CreateId], [LastModifyTime], [LastModifyId],
[CompanyId]) VALUES (7, N'添加', N'Pass', 1, N'PHone', N'18672713698', N'abcc', N'', 123456798,
N'Wechat', 1, CAST(N'2021-10-09T16:01:45.010' AS DateTime), CAST(N'2021-10-09T16:01:45.010' AS
DateTime), 1, CAST(N'2021-10-09T16:01:45.010' AS DateTime), 1, 1)
GO
SET IDENTITY_INSERT [dbo].[SysUser] OFF
#左连接
select * from Company c left join SysUser u on c.Id =u.CompanyId

19、内连接查询

select * from Company c inner join SysUser u on c.Id =u.CompanyId

 20、右连接查询

select * from Company c right join SysUser u on c.Id =u.CompanyId

21、全连接

select * from Company c full join SysUser u on c.Id =u.CompanyId

21、递归查询



  #可以通过一个参数，可以查询到无限层级下的数据，带有无限的层级查询。


　#初始化数据


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MenueInfo](
[Id] [int] IDENTITY(1,1) NOT NULL,
[MenuName] [varchar](40) NULL,
[ParentId] [int] NULL,
CONSTRAINT [PK_MenueInfo] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[RowColumnConversion] Script Date: 2022/8/5 10:38:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET IDENTITY_INSERT [dbo].[MenueInfo] ON
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (1, N'一级菜单', NULL)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (2, N'一级-二级菜单-1', 1)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (3, N'一级-二级菜单-2', 1)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (4, N'一级-二级菜单-3', 1)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (5, N'一级-二级菜单-1-三级菜单-1', 2)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (6, N'一级-二级菜单-1-三级菜单-2', 2)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (7, N'一级-二级菜单-1-三级菜单-3', 2)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (8, N'一级-二级菜单-2-三级菜单-1', 3)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (9, N'一级-二级菜单-2-三级菜单-2', 3)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (10, N'一级-二级菜单-2-三级菜单-3', 3)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (11, N'一级-二级菜单-3-三级菜单-1', 4)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (12, N'一级-二级菜单-3-三级菜单-2', 4)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (13, N'一级-二级菜单-3-三级菜单-3', 4)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (14, N'一级-二级菜单-3-三级菜单-1-四级菜
单-1', 11)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (15, N'一级-二级菜单-3-三级菜单-1-四级菜
单-2', 11)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (16, N'一级-二级菜单-3-三级菜单-1-四级菜
单-3', 11)
GO
SET IDENTITY_INSERT [dbo].[MenueInfo] OFF




希望能够根据一个父菜单，查询出当前菜单下的所有的子菜单；


递归语句


就是去查询层级结构的数据存储，查询数据树形结构数据。






with Con(Id,MenuName,ParentId,le) as
(
select Id,MenuName,ParentId,le=1 from [ZhaoxiEdu].[dbo].[MenueInfo] WHERE Id=4
union all
select a.Id,a.MenuName,a.ParentId,le=le+1 from [ZhaoxiEdu].[dbo].[MenueInfo] a join Con on
a.ParentId=con.Id
)
select Id,MenuName,ParentId,le from Con