首页 > 数据库 >SqlServer基本操作

SqlServer基本操作

时间：2023-07-17 10:22:15浏览次数：45  
标签：dbo ScoreInfo SqlServer course score GO 基本操作 Id

一、创建数据库和表

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'[email protected]', 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'[email protected]', 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










 




















标签：dbo,ScoreInfo,SqlServer,course,score,GO,基本操作,Id	

From： https://www.cnblogs.com/duyao/p/17559283.html
      

      




      

      

        
相关文章

        

          
    
 
  • SqlServer用户权限
    一、设置权限用户的意义数据库是一个应用程序，运行起来对应一个进程，这个进程中，有很多很强大的功能支持，对于开发者或者是数据库管理者，都是充分的来应用这些功能，应用这些功能，需要客户端/或者是应用程序链接进来，通过客户端/应用程序来操作数据库。操作客户端来使用数据库功能的最终......
     
  • 自建sqlserver迁移到aliyun的rds for sqlserver实战
    一、背景有些客户有上云的需求，需要把线下自建的sqlserver迁移至aliyun的rdsforsqlserver。大家第一时间想到的是用dts工具，根据工作经验，DTS迁移mysql类的数据库比较成熟，但是迁移sqlserver之类的会有问题。首先面临的一个问题就是源库日志膨胀的问题，也就是说是迁移过程中，源库的日......
     
  • MySQL学习-基本操作
    1.数据库操作创建数据库createdatabasetest；查看数据库showdatabases;进入数据库usetest;删除数据库dropdatabasetest;2.表格操作创建表格 createtabletable1(namevarchar(20),date1date)删除表格droptabletest1修改表格表项altertabletable1modif......
     
  • SqlServer 分页 写法
    一、SELECT*FROM   (SELECTROW_NUMBER()OVER(ORDERBYid)ASRowId,*FROMArticle wherecidin(3,4,5,6,7,8,9,10,11,12))ASrWHERE RowId BETWEEN1and12 二、select*from(   select ROW_NUMBER()OVER(ORDERBYid)asrowid,* from  ......
     
  • SQLServer 查询语句指定排序规则（查询时区分大小写）
    SQLServer查询语句指定排序规则（查询时区分大小写）介绍可以使用COLLATE子句将字符表达式应用于某个排序规则。为字符文本和变量分配当前数据库的默认排序规则。为列引用分配列的定义排序规则。COLLATE定义数据库或表列的排序规则，或应用于字符串表达式时的排序规则强制转换......
     
  • DBGridEh 基本操作
    导出到Excel等文件类型1.导入导出引用单元useDBGridEhImpExp;类型说明类型名称说明TDbGridEhExportAsText导出到文本文件TDbGridExportAsUnicodeText导出到Unicode文本TDbGridEhExportAsCSV导出到CSVTDbGridEhAsHtml导出到HTMLTDBGridEhAsRTF......
     
  • day03 链表基本操作
    前置知识，链表数据结构1.移除链表元素移除链表元素不难，只需要把前一个结点的下一节点指向下一个节点的下一节点如果当前遍历的节点与所给值相等，则需要移除此元素，移除元素是将上一节点的next域设置为当前节点的next，当前节点后移一位如果当前遍历的节点值不等于所给值，则前驱......
     
  • SQLServer批量修改和插入
    SQLServer批量修改或插入场景今天在工作中遇到这么一个场景，我需要根据条件对表A做批量更新或插入。因为条件比较复杂，所以我使用了临时表B，先把需要更新或插入的数据查询出来放入临时表。然后更新表A的某字段，更新条件是A.id=B.id，更新效果是若记录存在表A中，则更新表A的字段，若不......
     
  • Oracle EBS:注册Form表单和function功能基本操作
    OracleEBS:注册Form基本操作。这里将注册Form的基本步骤记录下来。1.首先，查看当前用户是否具备应用开发员责任。如果没有需要为当前登录用户配置应用开发员责任权限。2.配置完应用开发员角色后，打开oracleApplication表单界面，切换角色到应用开发员，打开应用产品，即可看到表单注......
     
  • SQLServer中创建用户角色及授权
    在SQLServer中创建用户角色及授权假设，我们准备建立一个dba数据库帐户，用来管理数据库mydb。1.首先在SQLServer服务器级别，创建登陆帐户（createlogin）--创建登陆帐户（createlogin）createlogindbawithpassword='abcd1234@',default_database=mydb登陆帐户名为：“dba”，......
     
    •            
          

        

      

    

    

      
赞助商

      




      

      
阅读排行

      

        
      

    

  





  
 
    
网站主页关于我们联系我们网站地图 

    
本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。 

    
Copyright © 2020-2023  IPS99  版权所有 IPS99