SQL Server 笔记

关注

原创 2023-08-16 09:20:17 3.9k 阅读 AI 写同款· GEO 优化 ›

SQL-Server笔记触发器是SQL server提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行insert、delete、和update时就会激活它执行。自动提交事务：是SQL server默认的事务模式，将每个T-SQL语句都视为一个事务，如果成功执行，则自动提交，如果出现错误，则自动回滚事务。一、子查询是一个嵌套在select、insert、update和delete语句或其他子查询中的查询，任何允许使用表达式的地方均可使用子查询，但子查询通常位于where子句中。阅读详情

SQL Server笔记

DDL语句

创建表：

Create Table 表(
		列 类型  [identity] primary key,
		列 类型(长度) not null
)

删除表格：

Drop Table 表

更改表名：

Exec sp_rename '表名','新表名'

添加列：

alter table 表 add 列名 类型

删除列：

alter table 表 drop column 列名

修改列名：

Exec sp_rename '表.列名','新名','COLUMN'

修改列的数据类型：

alter table 表 Alter column 列名 类型   
（数据类型---备注型-->text [非Unicode] ）

在修改列的数据类型时，有些主键列不能直接修改，需要三步：
删除主键-->再改类型(注意新的主键需非空)-->最后增加主键
1.alter table 表名字 drop constraint 约束名;
2.alter table 表 Alter column 列名 类型(长度) not null;
3.alter table 表名字 add constraint pk_表 primary key(列);

用一位二进制存储数据用bit类型
如果用binary(n)，插入数据时要在数据前面加上0x

备注类型text或Ntext

图片类型image

存储汉字 Nvarchar(Unicode字符)

DML语句

新增语句：

insert  into  表名 (列名,列名,列名) values(值,值,值);
insert into 表名字 values(值,值,值),(值,值,值),(值,值,值);

删除语句：

Delete 表名 Where 列 Like '%州%'
delete 表名 [where 列名 = 值]
Delete 表 where 列 >/</= (Select…From 表  Where…)

修改语句：

update 表名 set 列名 = 值 [where 列名 = 值]
update 表 set 列 = Floor(列*0.1) Where...  //向下取整
update 表 set 列 = (select…from 表名 Where…)
update 表 set 列 = case when…then…

查询语句：
select 列名，列名 from 表名；

日期函数：
getdate():获取当前系统日期、时间
dateadd(day/year/month,天数，指定日期):在向指定日期加上一段时间的基础上，返回新的时间
datediff(day/year..,日期1，日期2):返回两个日期之间的差值，日期1>日期2

DQL语句：

select 列名，列名 from 表名；
select * from 表名；

剔除重复行：

select distinct 列名 form stu;

查询结果排序：

select * from 表名 order by 列名 desc / asc ;

显示查询结果的前几行：

select top 行数 * from 表名 order by 列名 desc / asc ;

若排序查询结果有并列，想要一同显示：

select top 行数 with ties * from 表名 order by 列名 desc / asc ;

显示查询结果的前百分之几，且并列的一同显示：

select top 百分比的数字 percent with ties * from 表名 order by 列名 desc / asc ;

查询结果的列名另起别名：

select 原列名 as 新列名 from 表名；

也可以省略as：

select 原列名  新列名 from 表名；

还可以写成“=”，原列名可以是表达式：

select 新列名 = 原列名 from 表名；

起别名时要注意语法，若新名字开头为数字则要给新名字用[ ]括起来，
或者给新名字加上双引号

把查询结果的列名改名，并将查询结果存入新表：

select [新列名 = 原列名] into 新表 from 原表 ;

把查询结果的列名改名，并将查询结果存入已存在的表：

insert into 已存在的表(列名，列名..) select 列名，列名.. from 原表；

分组查询
select 列,分组函数(列) from 表名 group by 列；

分组函数：
count(): 计数器
avg(): 求平均值
sum(): 求总和
max():求最大值
min():求最小值

查询结果保留几位小数：
进行类型转换或使用函数

使用 Round() 函数，如 Round(@num,2) 参数 2 表示保留两位有效数字。
更好的方法是使用 Convert(decimal(18,2),@num) 实现转换，decimal(18,2) 指定要保留的有效数字。

这两个方法有一点不同：使用 Round() 函数，如果 @num 是常数，如 Round(2.3344,2) 则会在把有效数字后面的变为0 ，成 2.3300。但 Convert() 函数就不会

convert(类型(长度),表达式,[,样式])

数据转换函数有两个转换函数：Convert和Cast。
Cast函数允许把一种数据强制转换成另一种数据类型，语法如下：

Cast(表达式 AS 数据类型)

CONVERT允许用户把表达式从一种类型转换成另一种数据类型，还允许把日期转换成不同的样式(样式值如下表左二列)，语法如下。
Convert(数据类型[(长度)],表达式[,样式])

Convert(…)函数样式参数取值与相应样式

数字	格式
101	mm/dd/yyyy
102	yy.mm.dd
103	dd/mm/yy
104	dd.mm.yy
105	dd-mm-yy
106	dd mon yy
107	mon dd, yy
108	hh:mm:ss
109	mon dd yyyy
110	mm-dd-yy
111	yy/mm/dd
112	yymmdd
113	dd mon yyyy hh:mm:ss:mmm(24h)
120	yyyy-mm-dd hh:mm:ss(24h)
121	yyyy-mm-dd hh:mm:ss.fff(24h)

类型转换

select 列,cast(分组函数(列) as decimal(6,4)) from 表名；

参数6表示有效数字位数，参数4表示保留4位小数，结果就是两位整数和4位小数

having关键字
having关键字的使用与where类似

优先级别：	where  >  group-by  >  having  >  order-by

更新值—多分支语句(类似于switch)

update 表名 set 列 = 
   case
   when 计算-比较表达式 then 操作表达式
   when 列 % 数字 = 数字 then 列 +/-/* 数字
   when ... then...
   else 操作表达式
   end

判断表格是否存在
第一种方式：

   if exists(select * from sys.objects where type = 'U' and name = '表格名字')...

//表格的type = U表示为用户表，type = S表示为系统表
第二种方式：

   if object_id('表名字') is not null ...

多表联合操作：
select 列名 from 表格1 表格1别名
where 表格1别名 . 列名 = / < / > 表格2 . 列名(或值)

//外面可以嵌套update操作和delete操作
//可以是同一张表格联合查询，
//如果不是同一张表格，可以不用起别名，直接表名.列就行

向上取整函数：floor()
向下取整函数：ceiling()

如果题目要求输出的结果不允许有null值的话，可以把null值转换成空格：

select 列 case 
when 列 is null then '  ' 
else  cast(列 as varchar(长度))
end as 列名
from 表格名

如果要把null值设置为0：

select 列 isnull(列,0) as 列名 from 表格名

是否区分大小写、全半角、重音、片假名

collate Chinase_PRC_CS_AS_KS_WS 区分大小写、区分重音、区分半角名、区分全半角

默认情况下，不区分大小写、区分重音、不区分片假名、不区分全半角
_CI_AS_KI_WI

是否区分大小写（CS / CI）
是否区分重音（AS / AI）
是否区分片假名（KS / KI）
是否区分全半角（WS / WI）

多表连接

1. 全连接

select 列 from 表1 cross join 表2 [where 连接条件]
也可以写成：select 列 from 表1,表2

2. 内连接

select 列 from 表1 inner join 表2 on 连接条件

（如果join关键字前没有明确的指定连接类型，则默认是内连接）

3.外连接
左外连接和右外连接：

select * from A left/right [outer] join B     on  连接条件

	1.两张表格A和B  取决于谁的数据在左边显示
			A表格先出现   A左边显示
			B表格后出现   B右边显示
	2.left和right来控制以哪一个表格的数据作为基准
			作为基准的表格数据必须全部显示出来
			非基准的表格按照on条件与之拼接
			若找到条件拼接  则正常显示   若找不到满足条件的则 null

全外连接：（左连接和右连接的并集）

select  列  from  表1 full [outer] join 表2 on  连接条件

union联合查询
规则：列数和裂的顺序必须相同；数据类型必须兼容

select * from 表1 union [all] select * from 表2

(不加all可以去重复，
把union改为intersect是求两个表格的差集，
把union改为except是求两个表格的交集)

约束

1.主键约束

创建表格时增加主键约束：

	create table 表名 (
		列名 类型(长度) [constraint 约束名] primary key
   )

修改表格时添加主键约束：

alter table 表名 add [constraint 约束名] primary key(列)

（！前提是此列得是非空的）

删除主键约束：

alter table 表名 drop constraint 约束名

有些低版本的数据库，如果要修改主键，就要先删除主键，然后才能修改这个列，修改完以后再把主键设置回去

2.唯一约束

创建表格时增加唯一约束：

	create table 表名 (
		列名 类型(长度) [constraint 约束名] unique
   )

修改表格时添加唯一约束：

alter table 表名 add [constraint 约束名] unique(列)

删除唯一约束：

alter table 表名 drop constraint 约束名

3.非空约束

创建表格时增加非空约束：

	create table 表名 (
		列名 类型(长度) [constraint 约束名] not null
   )

修改表格时添加非空约束：

alter table 表名 alter column 列 类型(长度) not null

删除非空约束：

alter table 表名 alter column 列 类型(长度) null

4.外键约束

创建表格时增加外键约束：

	create table 表名 (
		列名 类型(长度) [constraint 约束名] foreign key references 表名(列名)
   )

注意外键连接的关联表的键必须是主键！！！
修改表格时添加外键约束：

alter table 表名 add [constraint 约束名]  foreign key  (列) references 表名(列名)

删除外键约束：

alter table 表名 drop constraint 约束名

在这里涉及到一个级联删除的问题：
当外键已经存在时，当前表删除一条记录会连带着关联表的记录一起删除，所以不建议使用
alter table 表名 add [constraint 约束名]  foreign key  (列) references 表名(列名) on delete cascade
建议使用级联修改：
alter table 表名 add [constraint 约束名]  foreign key  (列) references 表名(列名) on update cascade

不常用约束：
5.检查约束
创建表格时增加检查约束：

	create table 表名 (
		列名 类型(长度) check (检查条件)
   )

修改表格时添加检查约束：

alter table 表名 add [constraint 约束名] check (检查条件)

删除唯一约束：

alter table 表名 drop constraint 约束名

存储过程

创建存储过程

create procedure 存储过程名 (@ 变量名 类型，@变量名 类型(长度) [形式参数]) 
as 
sql语句
[
if(@变量名 >/=/< @变量名) 
set @作为返回值的形参=@变量/值
GO
]
[return ...]  //只能返回整数状态值

//形式参数可以写output（输出参数），如果是作为返回值的变量一定要指定output，可以写readonly（只读），可以写encryption(加密) 。参数列表的括号可写可不写

执行存储过程

exec 存储过程名 参数列表

例：
declare @临时变量名类型，@临时变量名类型
exec 存储过程名 @存储过程的参数名=值/default, @存储过程的形式参数名=@临时变量名
select @临时变量名 //输出表格
[print @临时变量名] //输出文本
//执行存储过程时，存储过程的参数列表顺序不必和定义存储过程的顺序一样

自定义函数

根据返回值的不同，分为三个类别：

1.返回标量值的函数

只能返回一个值（计算结果），语法：

create function 函数名(@参数名 数据类型)
returns 返回值数据类型
 as
	begin
		函数体
	    return 返回值
	end

标量值函数使用时必须前缀dbo.XXX：
dbo.函数名(参数列表)

2.返回可以更新数据表的函数（返回表）

只包含单个sql语句，且返回的表后续再进行更新时，表格里的数据也会跟着改变
创建内联表值函数：

create function 函数名(@参数名 数据类型[=default])
as
returns table
return sql语句

3.返回不可更新表的函数

多语句表值函数包含多条sql语句，且后续无法对返回的表数据进行更新
创建多语句表值函数的语法：

create function 函数名(@函数名 数据类型)
returns @返回的表的表名 table(列 数据类型，列 数据类型(长度))
as
  begin
  	函数体
  	return
  end

视图

创建视图

create view 视图名 [(列名 类型(长度),列名 类型(长度)...)]
[with encryption] //对视图加密
as
select 语句
[with check option] //对视图进行修改时需要符合条件，带检查效果

触发器

创建DML触发器

DML触发器有三种：Insert触发器,Update触发器,Delete触发器。
SQL支持两种类型的DML触发器：前(Instead of)触发器和后(After)触发器。

语法如下：

create trigger 触发器名 on 表名 
after/instead of  [instert][,delete][,update]
as
[begin]
[sql语句]
[declare定义临时变量语句]
[select赋值语句]
[print输出语句]
[end]

其中，有两个概念表： Inserted 和 Deleted
这两个表的结构和创建触发器时关联的表结构一样

Inserted保存的是插入的记录值或修改后的记录值(新值)
Deleted保存的是被删除的记录值或修改前的记录值(旧值)

这两个表可以作为一般的表与创建触发器时关联的表做SQL操作，比如子查询等等

创建DDL触发器

create Trigger 触发器名 on {all Server / database}
[with encryption]
{for / after} {事件类型 / 事件组}
as
SQL语句

事件类型：能激发DDL触发器的事件名，比如
Create_Table 创建表，Alter_Table 修改表，Drop_Table删除表
Create_Function 创建函数，Drop_Function 删除函数
…

指定创建的触发器的级别：
all server : 表示创建的触发器为服务器级别，在当前服务器出现任何指定事件类型，都会激发改触发器

database ：表示创建的触发器为数据库级别，在当前数据库出现任何指定事件类型，都会激发改触发器

创建登录触发器

create trigger 触发器名 on all server [with [encryption] [execute as '登录帐号']]
{for|after} Logon
as
SQL语句

encryption : 例如 password = ‘123456’

数据库增删改

创建数据库

create databases 数据库名 on
(name='数据库逻辑名',fileName='数据存储的路径'[,size=数据文件初值,maxSize=最大值<不限的话填UNLIMITED>,fileGrowth=增量])
log on (name='日志逻辑名',fileName=日志存储的路径')

创建多文件组数据库

create databases 数据库名 on
primary (name='主文件组逻辑名',fileName='主文件组存储的路径'),
//若一个文件组里有多个数据文件，则每一组括号之间用逗号分隔
FileGroup 辅文件组名  (name='辅文件组逻辑名',fileName='辅文件组存储的路径'),
FileGroup 辅文件组名  (name='辅文件组逻辑名',fileName='辅文件组存储的路径')
//注意log on的前面是没有逗号的
log on (name='日志逻辑名',fileName=日志存储的路径')

创建分区架构

//首先创建多文件组数据库（参照上面）

go
use 数据库
go
create partition function 分区函数名(int)
as range left/right for values( 数字,数字 ,数字 ) //创建四个分区区间
go
create partition scheme 分区架构名 
as partition 分区函数名 to (文件组1,文件组2,文件组3,文件组4)//将四个分区区间和四个文件组对应

创建表

//创建表用分区架构，列1作为分区依据，列1的值落入四个分区区间，记录存入相应文件组

create table 表名 (列1 数据类型 [primary key],列2 数据类型(长度)) on 分区架构名(列1)

//创建表存于不同文件组

create table 表名 (列名 数据类型 [primary key],列名 数据类型(长度)) on 文件组名 //若文件组名带引号，则引号用[ ]代替

基础知识

PatIndex(‘要寻找的字符’,‘目标字符串’) //和CharIndex()函数一样，下标是从1开始数的
Stuff(‘原字符串’,从哪个下标开始,到哪个下标,‘替换成这个字符串’)
将1NF分解为2NF应消除（非主属性对关键字的部分依赖）
将2NF分解为3NF应消除（非主属性对关键字的传递依赖）
将3NF分解为BCNF应消除（主属性对关键字的部分与传递依赖）
设有关系W(工号，姓名，工种，定额)，将其规范化到第三范式正确的答案是（Wl(工号，姓名，工种) W2(工种，定额)）
在关系模式R(A,B,C,D)中，有函数依赖集F={A→B,B→C,C→D}，则R能达到（2NF）
数据库三级视图，反映了三种不同角度看待数据库的观点，用户眼中的数据库称为（外部视图）
数据库中，数据的物理独立性是指（用户的应用程序与存储在磁盘上的数据库中的数据是相互独立的）
关系模型中3NF是指（满足2NF且不存在传递依赖现象）
数据库系统的三级模式结构是指（外模式、模式、）内模式）
在一个E-R图中，有两个实体，这两个实体之间的联系是一对多的，转换为关系模式后，至少需要几个关系模式（ 2 ）
在一个E-R图中，有两个实体，这两个实体之间的联系是多对多的，转换为关系模式后，至少需要几个关系模式（3）
一门课有N个学生选修，一个学生可选修M门课的，则它们是（N:M联系）
E－R图中用( 矩形 )表示实体
E－R图中用( 菱形 )表示联系
E－R图中用( 椭圆形 )表示属性
反映数据库物理概念的是（内模式）
数据库三级组织结构中，（接口层和存储层只有一个，而用户层可能有多个）
主键一定是聚簇索引；聚簇索引使行的物理顺序与索引的顺序保持一致；一个表只能有一个Identity列；一个基本表有且仅有一个聚簇索引
数据库三级组织结构，其中数据库管理员所看到的是（模式）
体现了数据库操作的存储层的是（内模式）
体现了数据库操作的接口层的是( 模式 )。
数据库三个模式中，模式是内模式的逻辑表示；内模式是模式的物理实现；外模式则是模式的部分抽取；
一个公司有N个部门，一个部门有M个职工，部门与职工是（1:N联系）
一个公司中有1个总经理，领导着若干个部门经理，每个部门下又有若干员工，则体现他们隶属关系的是( 1:N联系 )
一个公司中有若干个项目，每个项目又有若干员工，若每个员工只能参予一个项目，则体现项目与员工关系的是( 1:N联系 )
一个公司中有若干个项目，每个项目又有若干员工，若每个员工可以参予多个项目，则体现项目与员工关系的是( N:M联系 )
表中数据(不含标题)不允许出现相同的两行；行和列的次序可以任意交换；表中数据(不含标题)允许出现相同的两列
( DBMS )提供应用程序与数据库的接口，并由它实现用户逻辑地访问数据库中的数据。
一个学生有唯一的一个学号，一个学号也唯一对应一个学生，所以学生与学号是（啥都不是，不是一对一，也不是一对多，也不是多对多）
smallint类型数据要存储( 2 )字节
学生表有学号、姓名、性别、课程号、成绩等五个字段，则该表所属范式为（ 1NF ）
学生表有学号、课程号、课程名、成绩等四个字段，则该表所属范式为（ 1NF ）
现将含有学号、姓名、系名、系主任名、课程号、成绩等字段的学生表，分解为学生表(学号、姓名、系名、系主任名)和选修表(学号、课程号、成绩)，则分解后所属范式为（2NF）
现将含有学号、姓名、系名、课程号、成绩等字段的学生表，分解为学生表(学号、姓名、系名)和选修表(学号、课程号、成绩)，则分解后所属范式为（3NF）
现将含有学号、姓名、系名、系主任名、课程号、成绩等字段的学生表，分解为学生表(学号、姓名、系名)和选修表(学号、课程号、成绩)及系表(系名、系主任名)，则分解后所属范式为（3NF）
（信息）是经过加工处理的数据，而末经过加工的数据只能是基本素材
数据库设计过程分为两方面:逻辑设计和（物理）设计
如果一个批处理中包含有多个存储过程，那么除第一个存储过程外，所有剩下的存储过程都必须在前面使用（ Exec ）关键字
可以在一条语句中为多个局部变量赋值的语句是（select）
SQL SERVER在数据库建立时自动定义了两个特殊的数据库用户:dbo和（guest）
关系数据库操作语言DML操作对象与结果都是（表）
关系之间的联系正是通过（外部关键字）实现的
关系模型数据结构中用（关系）表示实体间的联系
关系规范化的过程实质上是对关系（逐步分解）的过程
执行 commit 命令表示完成一个事务单元
在一条语句中只能为一个局部变量赋值的语句是（set）