SQL server操作要点

SQL基础操作：说明：【】是可省略的项 A是可视化操作 B是SQL操作
概念模型：用e-r图表示，逻辑模型：把e-r图转换为关系模式（类似：A（a,b,c））
物理模型是mdf文件
索引
索引对应的是内模式部分，基本表对应的是模式部分，而视图对应的是外模式部分。
创建：
A：点击对应的数据库☛右击对应的表的索引结点☛新建索引然后命名添加对应的列
B：
create 【unique】【cluster/noncluster】index 索引名 on 表（列名）
修改：
A：
B: alter index 索引名 on 表（列名）rebulid/重新生成/reorganize/重新组织索引/disable/禁用索引/
删除：
A:点击索引下面的对应的索引然后点击删除
B: drop index 索引名 on 表【列名】

视图
创建：
A:右击视图☛新建视图☛选中相应的表☛关闭添加表☛选中对应的列名
B: create view 视图名 as select语句
使用：
Select * from 视图名
修改：
B: alter view 视图名 as 新的select语句
更新：（视图更新的数据也会存入原表）
B: update 视图名 set 列名=‘新值’
插入：
B: insert 视图名 values （新值）
删除：
B: drop view 视图名
存储过程
创建：
（不带参数的）
B: create procedure 存储名 on 表 as【 】 sql 语句
Excel 存储名
创建带输入参数：
create procedure 存储名 declare @变量 数据类型 set 变量=。。 as select 语句
Exec 存储名 ‘变量值 或者是 exec 储存名 @变量=新值
创建带输入输出参数：
Create procedure 存储名 @变量1 数据类型，@变量2 数据类型 output（只能在as前面定义变量）
As SQL语句
执行：
Declare @变量1 数据类型，@变量2 数据类型 output
Set @变量1=新值
Exec 存储名 ‘@变量1’，@变量2 output

成批插入：
Create table 新表名
（表结构）
Insert into 新表名 exec 存储名

触发器
创建：
Create trigger on 表
For/after insert/update/delete
As sql 语句
其中inserted表是触发器所在的表的副本，deleted表与触发器所在的表没有相同的行。
触发语句：insert，delete，update会先执行自身的功能再执行触发器，如果需要取消该dml操作需要在加上rollback

Create trigger on 表
Instead of insert，delete，update
As SQL语句

先执行触发器语句没有执行dml语句，即如果触发器语句没有增删改查语句则dml语句不起作用
Ddl触发器
Create trigger on database
For alter，delete，insert，update_table
As SQL
游标
定义：
declare 游标名 cursor【static，dynamic】for
Sql 语句
打开游标：
Open 游标名
检索：
Fetch next（下一行），first/last（（只能在动态游标））,prior（前一行）from 游标名
关闭：
Close 游标名
释放：
Deallocate 游标名
利用游标定位修改数据：
Insert，delete，update 。。。。。Where current of 游标名
事务：
Begin transaction
Rollback 在begin 事务中的取消其到上一个还原点（事务名）之间的所有操作
若使用rollback+还原点则所在还原点及其后面的事务均取消操作
Save transaction 事务名（保存还原点）
例：
Begin transaction 1
Delete from 表名 where 加条件
commit
Begin transaction 2
Delete from 表名 where 加条件
Begin transaction 3
Delete from 表名 where 加条件
Rollback transaction 2（事务2,3操作均取消）

自定义函数：
标量函数（返回一个值）
Create function 函数名【（变量）】
【@变量1 数据类型】
Returns 数据类型
As
Begin
SQL语句
Return 数值
End

调用函数1.
Select * from 所有者.函数名【（变量）】
例：SELECT dbo.average(@course1)
调用函数2.
新建course1表
create table course1
(cno char(4) primary key,
cname nvarchar(20) ,
credit int ,
aver as (dbo.average(cno))
)
内嵌函数：
Create function 函数名【（变量）】
【@变量1 数据类型】
Returns table
As
Return
Sql 语句
例：
CREATE FUNCTION st_func(@major char(10)) RETURNS table
AS
return
SELECT a.sno,sname, cno,score
FROM student a,sc
WHERE specialty=@major
and a.sno=sc.sno
调用：
Select * from 函数名【（变量）】
多值函数：
Create function 函数名【（变量）】
Returns @新建表名 table （表结构）
As
begin
Insert @新建表名 select * from表
return
end
例：
CREATE FUNCTION st_score (@no char(7))
RETURNS @score table
( xs_no char(7) , xs_name char(6) ,
kc_name char(10) , cj int , xf int )
AS
BEGIN
INSERT @score SELECT s.sno,sname,cname,credit, score FROM student s,course c,sc
WHERE s.sno=sc.sno AND c.cno=sc.cno
AND s.sno=@no
RETURN
END
（Begin用于有多条语句的批处理，声明变量在as之前）
调用：
Select * from 函数名【（变量）】
约束：
主键：向表设置主键：
列名1 数据类型 not null
Constraint 列名1 primary key（列名1）
添加主键：
Add constraint 列名1 primary key （列名1）
外键：向表设置外键：
列名1 数据类型 not null
constraint 列名1， foreign key （列名1）references 主表（列名）
添加外键：
Alter table 表名
Add constraint 列名2 foreign key references （列名2 ）主表（列名1）
某一列约束：
Check约束：
可是视化操作：右击目标列，选择check约束，添加，表达式：（与ssms语法一样）
表的增删改查：
向表添加数据：
Insert into 表名 values（数值）
向有默认值的表添加数据：在已设置的列所对应的位置填defaul
删除表数据：delete from 表名 where条件
修改：alter table 表名
drop column 列名（删除一列）
Add 新列名 数据类型（新增一列）
Alter column 列名 新数据类型（修改某一列）
Exec sp_rename ‘旧名’‘新名’
更新：update 表名 set 列名=新值 where 条件
查询：
单表查询：select */列名 from 表 where 条件 group by 列名 having 条
order by 列名
多表查询：（1）
select */列名 from 表 1，表2 where 条件 and 表1.列名=表2.列名
group by 列名 having 条件
order by 列名
（2）
select */列名 from 表 1 left/right join表2 on 表1.列名=表2.列名
where 条件
group by 列名 having 条件
order by 列名
(3)
Select * from 表1
Union/union all (有重复值）
Select * from 表2（前提是筛选出来的列数要一致，结果是两张表垂直排列）

子查询：
select */列名 /A.列名，B.列名from （select 列名 from 表1 ）A ，（select 列名 from 表 where 条件）B where A.列名=B.列名
group by 列名 having 条件
order by 列名

可结合函数：sum（） avg（） max（） min（），top n，rank（）over（partition 要分组的列名order by 要排序的列名）其结果是新建一列存放跳跃排名
Dense_rank（）over（partition 要分组的列名 order by 要排序的列名）存放连续排名
Row_number() over （partition 要分组的列名order by要排序的列名）数值相同，序号不一样，（必须与order by连用）
sum（列名）over（partition by 列名1 ）在同类型的多列中显示sum总数
sum（列名）over（partition by 列名1 order by 列名2）在同类型的列每行累加
Sum（列名）over（order by 列名）每行显示sum总数
例：

–建立测试表和测试数据
CREATE TABLE Employee ( ID INT PRIMARY KEY, Name VARCHAR(20), GroupName VARCHAR(20),Salary INT)
INSERT INTO Employee
VALUES(1,‘小明’,‘开发部’,8000),
(4,‘小张’,‘开发部’,7600),
(5,‘小白’,‘开发部’,7000),
(8,‘小王’,‘财务部’,5000),
(9, null,‘财务部’,NULL),
(15,‘小刘’,‘财务部’,6000),
(16,‘小高’,‘行政部’,4500),
(18,‘小王’,‘行政部’,4000),
(23,‘小李’,‘行政部’,4500),
(29,‘小吴’,‘行政部’,4700);
SELECT *, SUM(Salary) OVER(PARTITION BY Groupname) 每个组的总工资, SUM(Salary) OVER(PARTITION BY groupname ORDER BY ID) 每个组的累计总工资, SUM(Salary) OVER(ORDER BY ID) 累计工资, SUM(Salary) OVER() 总工资from Employee

Ntile（n）：分为哪个部分，每部分分别用123代替
SELECT *,NTILE(3) OVER (ORDER BY SCORE DESC) AS 分区后排序 FROM Scores;
1
1
2
2
3
3

分组集：
（1）Grouping sets（）
Group by grouping sets（列名1，列名2）：
相当于select * from 表group by 列名1
union all select * from 表 group by 列名2
（2）group by grouping sets（列名1 ，列名2）with rollup
例：
部门 员工 工资
a Zhang 100
a li 200
a wang 300
a zhao 400
a duan 400
b duan 600
b duan 700

（3）group by grouping sets（部门 ，员工）with rollup
结果：
部门 员工 工资
a duan 500
a li 200
a wang 300
a zhao 400
a duan 600
a null 1500(a的合计)
b duan 1300
b null 1300（b的合计）
null null 2800（a和b的合计）

（4）group by grouping sets（部门，员工）with cube
相当于withrollup表union all select ‘null’，员工，sum（工资）from depart group by 员工
结果：
部门 员工 工资
a null 2800
a null 1500
a duan 500
a li 200
a wang 300
a zhang 100
a zhao 400
b null 1300
b duan 1300
null duan 1800
null li 200
null wang 300
null zhang 100
null zhao 400

Convert（要转换的数据类型，目标数值）
String（字符串，开始截取位置，结束位置）
日期函数：
Datename（week、year、month、dayofyear，getdate（））计算当前时间是第几周、年、月
Datediff（year，month，day，‘日期1’，‘日期2’）两个日期相隔多少
Datepart（year、month、day、quarter，getdate（））取年、月、日
Dateadd（year、month、day，n，getdate（））当前时间加上n

关键字：
In（等于多个值中的其中一个），between 小数值 and 大数值，like‘%A%’（查找有A的字符串），like‘A’（查找第二个字符是A的字符串），like‘%A%B%’（查找A和B不连续的字符串），like‘a%’（查找以a开头的字符串），like‘%a’（以a结尾的字符串），
Like‘a%_%’（查找以a开头的三个长度的字符串）

流程控制：
Begin…end

If …else

Case …when…then
Case 列名 when 值 then或者case when列名=‘值’then…

While 条件
SQL语句
Continue（从循环条件开始）；
Sql语句
Break；
Return 数据类型 （return后面语句不执行）
Goto：
Goto 自行命名
自行命名：SQL语句（自行跳到）
Waitfor time ‘具体时间’
SQL语句
Waitfor delay ‘具体时间点’

高级查询：
建立临时表：
Select * into #新表名 from 旧表名

全局变量：
@@error 检查SQL执行是否出错，返回1则为正常
@@rowcount 返回受SQL影响的行数
@@indentity 最后一次插入的标识值 需要在列设置标识并且要求列为int或float

集合：
Union、union all，
表1 except 表2（表1减去表2 的列）
表1 interset 表2（取表1与表2交集）

易错点：

1. print语句和rollback同时出现在语句块需要用begin end包围
2. Case语句需要用end
3. 先分类再排序需要用而且排序的列名没有用聚合函数：over（partition by分类列名 order by 排序列名）
4. Group by 与多字段：

第二次 按照 b列来分组 代码如下
select count(a),b from test group by b

第三次 按照 c列来分组 代码如下
select count(a),c from test group by c

第四次 按照 b c两个条件来分组
select count(a),b,c from test group by b,c

第五次 按照 c b 顺序分组
select count(a),b,c from test group by c,b

先对第一个条件b列的值 进行分组,分为 第一组:1-5, 第二组6-8,然后又对已经存在的两个分组用条件二 c列的值进行分组,发现第一组又可以分为两组 1-4,5
1 甲 a
1 甲 a
1 甲 a
1 甲 a
1 甲 a
1 乙 a

事务：
结构：begin transaction 事务名
Save transaction 事务名
Dml操作语句
Rollback 事务名
Commit transaction 事务名
例子：
Begin transaction
Save transaction sp1
Delete from 表 where 条件
Save transaction sp2
Delete from 表 where 条件
Save transaction sp3
Delete from 表 where 条件
Rollback sp2
Commit
只执行了第一删除
时间函数：
select GETDATE() as ‘当前日期时间’,
DateName(year,GetDate())+’-’+DateName(month,GetDate())+’-’+DateName(day,GetDate()) as ‘当前日期’,
DateName(quarter,GetDate()) as ‘第几季度’,
DateName(week,GetDate()) as ‘一年中的第几周’,
DateName(DAYOFYEAR,GetDate()) as ‘一年中的第几天’,
DateName(year,GetDate()) as ‘年’,
DateName(month,GetDate()) as ‘月’,
DateName(day,GetDate()) as ‘日’,
DateName(hour,GetDate()) as ‘时’,
DateName(minute,GetDate()) as ‘分’,
DateName(second,GetDate()) as ‘秒’,
DateName(MILLISECOND,GetDate()) as ‘豪秒’,
DateName(WEEKDAY,GetDate()) as ‘星期几’

Dateadd（单位，目标数值，当前操作对象）
例：dateadd（day，2，date1）
Date1=date1加上两天

允许表结构修改：
工具-----designer--------去掉阻止。。。。前面那个勾