BiuBiu

# SQL Server

# 基础知识

# 数据库组成

  1. 创建数据库
  2. 数据库文件
    • 主要数据库文件 .mdf 存放数据和数据库初始化信息。每个数据库只能有一个主要数据库文件
    • 次要数据库文件 .ndf 可以没有或有多个
    • 事务日志文件 存放用于恢复数据库得所有日志信息。每个数据库至少有一个日志文件，可以有多个
  3. 文件组 Primary

# 数据库对象

项目 描述
表 TABLE 行列组成，存储数据
字段 COLUMN 一个表可以有多个列，有多种数据类型
视图 VIEW 虚拟表
索引 INDEX 可以加快数据查询
存储过程 一组为了完成特定功能的SQL语句集合
触发器 用户定义的SQL事务命令集合，当执行增删改时，命令会自动触发执行
约束 对列进行一种限制
缺省值 对列指定一个默认值

# 数据类型

  1. 数值型

    • 整形(字节) bigint-8、int-4、 smallint-2、 tinyint-1
    • 浮点型 float、 real=float(24)、decimal(18,2)

  2. 字符型

    • char、 varchar(n)、 text、 nvarchar(n)

  3. 日期型

    • datetime、 datetime2、 date、time

  4. 其他类型

    • uniqueidentifier 全球唯一

# SQL语句

# 1、创建数据库

use master -- 选择要操作的数据库，master: 系统数据库
go -- 批处理命令

-- 创建数据库
create database ufsdb -- 数据库名称
on primary -- 主文件组
( 
	name='ufsdb', -- 数据库主要数据文件逻辑名 /var/opt/mssql/data
	filename='/var/opt/mssql/data/ufsdb.mdf', -- 主要数据库文件的路径
	size=5MB, -- 数据库主要文件的初始大小
	filegrowth=1MB -- 数据库主要文件的增量
)
log on -- 日志文件
(
	name='ufsdb_log', -- 数据库日志文件逻辑名 /var/opt/mssql/data
	filename='/var/opt/mssql/data/ufsdb_log.ldf', -- 日志文件的路径
	size=1MB, -- 日志文件的初始大小
	filegrowth=10% -- 日志文件的增量
)
go 

-- 删除数据库
drop database ufsdb
go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# 2、创建表

use ufs
go

-- 创建表
create table ProductInfo (
	Id int identity(1001,1) primary key not null, -- 自增ID,从1001开始每次自增1
	ProNo varchar(50) not null,
	ProName nvarchar(50) not null,
	TypeId int not null,
	Price decimal(18,2) null,
	ProCount int default 0 not null
)
go
-- 创建表
create table ProductType (
	TypeId int identity(1,1) primary key not null, -- 自增ID,从1001开始每次自增1
	TypeName nvarchar(50) not null
)
go

-- 删除表
drop table ProductInfo
go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# 3、修改表

-- 新增列
alter table ProductInfo add ProRemark nvarchar(max) null 
-- 删除列
alter table ProductInfo drop column ProRemark
-- 修改列
alter table ProductInfo alter column ProRemark nvarchar(50) not null

-- 修改字段名称 一般慎用,后果很严重要想清楚
exec sp_rename 'ProductInfo.ProRemark', 'Remark', 'column'
1
2
3
4
5
6
7
8
9

# 4、创建约束

-- 创建表的时候创建外键约束
create table ProductInfo (
	Id int identity(1001,1) primary key not null, -- 自增ID,从1001开始每次自增1
	-- 唯一约束
	ProNo varchar(50) unique not null,
	ProName nvarchar(50) not null,
	-- 外键约束
	TypeId int not null foreign key references ProductType(TypeId),
	-- 检查约束
	Price decimal(18,2) check(Price<10000) null,
	ProCount int default 0 not null
)
go

-- 创建主键约束
alter table ProductInfo add constraint PK_ProductInfo primary key(Id)
-- 创建外键约束
alter table ProductInfo add constraint FK_ProductInfo foreign key(TypeId) references ProductType(TypeId)
-- 创建唯一约束
alter table ProductInfo add constraint UQ_ProductInfo_ProNo unique(ProNo)
-- 创建唯一约束 ProNo+ProName
alter table ProductInfo add constraint UQ_ProductInfo_ProNo unique(ProNo,ProName)
-- 创建检查约束
alter table ProductInfo add constraint CK_ProductInfo_Price check(Price<10000)
-- 创建DEFAULT约束
alter table ProductInfo add constraint DF_ProductInfo_ProCount default (0) for ProCount
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

# 5、插入数据

insert into ProductType(TypeName)
values('服装类')

insert into ProductType(TypeName)
select '电子类'

insert into ProductType(TypeName)
values('A类'),('B类'),('C类')

insert into ProductType(TypeName)
select 'I类'     union
select 'II类'    union
select 'III类'   union
select 'IV类'

-- 克隆数据
INSERT INTO ProductType(TypeName) -- 目标表
SELECT name AS 'TypeName' FROM UserInfo -- 源表
-- 备份数据
SELECT TypeName INTO ProductType_Bak FROM ProductType
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# 6、修改删除数据

-- 修改 注意主键不允许修改
update table ProductType
set TypeName = '手机类'
where Id = 1001
-- 删除
delete from ProductType where Id > 100
-- 清空表数据
truncate table ProductType
1
2
3
4
5
6
7
8

truncate是即时操作，不会触发触发器，不记录日志，不参与事务，删除无法恢复

# 7、查询语句（略）

# 8、索引操作

聚集索引：主键是聚集索引，最多只能有1个
非聚集索引：唯一索引就是，可以有多个

-- 加上clustered就是聚集索引或者主键
create clustered index PK_user_id 
on user(id) 
with(
   drop_existing=on -- 先删除原来的，然后创建索引
)


-- 唯一非聚集索引
create unique nonclustered index UQ_user_name 
on user(name) 
with(
   drop_existing=on, -- 先删除原来的，然后创建索引
   fillfactor=100,   -- 填充因子：索引页数据占索引页大小的百分比
                     -- 读写比：100:1 => 100
                     -- 读<写：50~70
                     -- 读写各一半：80~90
   ignore_dup_key=on
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 9、视图

虚拟表：就是一组查询语句，简化查询用的
分类：标准视图、索引视图、分区视图

-- 1.标准视图
create view view_name 
as
select xxx
go

-- 查询视图就和查询表一样
select * from view_name




-- 2.索引视图
create view view_name with schemabinding 
as
select xxx
go

-- 索引视图创建唯一聚集索引
create unique clustered index uq_view_name_index
on view_name(uid)

-- 分区视图
create view view_name 
as
select xxx
union all
selext 111
go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

# 10、存储过程

一组为了完成特定功能的SQL语句集合

-- 1. 创建
CREATE PROC sp_searchUser
-- 参数列表
@userId int,
@username varchar(50)
AS
BEGIN 
   
   //SQL
   DECLARE @time datetime
   SET @time=getdate()
   //SQL
   
END
GO

-- 2. 调用
EXEC sp_searchUser

-- 3. 删除
DROP PROC sp_searchUser
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

# 系统表的使用

  • 获取所有数据库名 SELECT Name FROM Master..SysDatabases ORDER BY Name

  • 获取DatabaseName库里所有表名 SELECT Name FROM DatabaseName..SysObjects Where XType='U' ORDER BY Name

--XType='U':表示所有用户表; XType='S':表示所有系统表;

  • 获取字段名 SELECT Name FROM SysColumns WHERE id=Object_Id('TableName')

  • 查询库里所有表名称 创建时间 修改时间

select name as [表名称],
    create_date as [表创建时间],
    modify_date as [表修改时间]
from sys.objects
where type='u' order by modify_date desc
1
2
3
4
5
  • 查询表的名称 记录数 创建时间
SELECT a.name as '表名', 
			 b.rows as '记录数',
			 a.crdate as '创建日期'
FROM sysobjects AS a
	INNER JOIN sysindexes AS b ON a.id = b.id
WHERE (a.type = 'u') 
AND (b.indid IN (0, 1))
ORDER BY b.rows DESC;
1
2
3
4
5
6
7
8

  • 查询某一个表的字段和数据类型
    select column_name,data_type from information_schema.columns where table_name = 'tableName'

  • 查询数据库中无数据的数据表

SELECT
	sys.objects.name 
FROM
	sys.objects
	JOIN sys.sysindexes ON sys.objects.object_id = sys.sysindexes.id 
WHERE
	indid <= 2 
	AND type = 'U' 
GROUP BY
	sys.objects.name 
HAVING
	SUM ( ROWS ) =0
1
2
3
4
5
6
7
8
9
10
11
12
  • 判断数据库中是否已经存在某个表，有的话就删除该表 if(Exists(Select * From SysObjects Where xtype='U' And Name='表名')) drop table [dbo].[表名]

# 其他sql

  1. 查询UUID
select newid();
select replace(newid(),'-','');
1
2
  1. 查询统计数据
WITH  NewTable AS (
	SELECT DISTINCT L.UserID
	FROM UserLoginHistory L WITH(nolock)
	WHERE L.AppName = 'WeChat_Card' 
	AND L.CityCode = '0755'
	AND L.LoginDateTime BETWEEN '2021-10-01' AND '2021-12-31'
)
SELECT COUNT(*) Num FROM NewTable;
1
2
3
4
5
6
7
8
  1. mybatis
    <insert id="insertUserSpeechArt" parameterType="com.biubiu.model.param.user.Param">
    <![CDATA[
        IF NOT EXISTS(SELECT TOP 1 1 FROM [dbo].[UserSpeechArt] WITH(NOLOCK) WHERE [UserId]=#{userId} AND [SpeechArt]=#{speechArt} AND [CityCode]=#{cityCode})
        BEGIN
            INSERT INTO [dbo].[UserSpeechArt]([UserId], [SpeechArt],[CityCode])
            VALUES (#{userId},#{speechArt},#{cityCode})
        END
            ELSE
        BEGIN
            UPDATE [dbo].[UserSpeechArt] SET [UpdateTime]=GETDATE(),[IsDel]=0 WHERE [UserId]=#{userId} AND [SpeechArt]=#{speechArt} AND [CityCode]=#{cityCode}
        END
    ]]>
    </insert>
1
2
3
4
5
6
7
8
9
10
11
12
13

← Oracle →