SQL Server从头学到尾—–期末总复习m046315852的博客-

时隔多月终于有时间写博客了

因为快要期末了，今天抽时间来更新一下SQL Server，方便一下大家，顺顺利利期末不挂科
1.数据库设计步骤
（1）需求分析
（2）概念结构设计
（3）逻辑结构设计
（4）数据库实施
（5）数据库运行和维护

2.基本概念
　数据库系统：由数据库，硬件，数据库管理系统组成
　数据库：表，关系，操作对象
　数据：数字，文字，图形

３.数据库文件有三类（主要记住后缀）

主数据文件（.mdf）：一个数据库只有一个 辅助数据文件（.ndf）：可以有多个 日志文件（.ldf）：用于恢复数据库所需的事务日志信息 

４．数据库标识符

规则： （1）首字母必须是下列字符之一 a~z，A~Z，“_”，“#” （2）后面字符 字母、数字、下划线  “$”,"@" 

注意的是 1.以“@”符号开始的标识符表示局部变量或参数　 2.以一个数字符号“#”开始的是表示临时表或过程， 3.以双数字符号“##”开始的标识符全局临时对象 如“##gzb”则是全局临时表4.全局变量：系统提供，以@@开头，只读的，也就是说由系统赋值，我们取值，全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值

５．ＥＲ图（考试大题可能会让你画图）

６．三大范式

1、第一范式（1NF）：元组的分量不可再分（每一列都不能再分） 2、第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖） 3、第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖） 

例子
（1）第一范式

调整后就满足第一范式
（2）第二范式

（3）第三范式

７．Ｔ－ＳＱＬ语句分类

ＤＤＬ（定义语言）create，alter，drop ＤＱＬ（查询语言）insert，update ＤＭＬ（操作语言）select ＤＣＬ（控制语言）revoke，deny，grant 

8.注释

 --这里是单行注释 /＊这里是多行注释*/ / ＊sqlserver中不区分大小写 ＊ / 

9.创建删除语句

（1）create database test --创建数据库 drop database test --删除数据库 （2）create table 表名（） drop database 表名 （3）create view 视图名--创建视图 drop view 视图名 （4）create unique 索引名--创建索引 drop unique 索引名 （5）create trigger 触发器名 drop trigger 触发器名 

10.SQL语法
(1)0select A from web;
　　　select ＊ from web;
(2)select distinct country from A;
distinct 查询不重复的
(3)where
查询中满足条件，文本用‘’，数值不用

where中的运算符 =，<> !=,>,<,>=,<= between 120 and 200包含边界值 not between  120 and  300  不包含边界值 like 搜索某种模式       %表示多个数值       _表示一个字符       M%以M开头       %M%包含M       %M_倒数第二个为M  in 针对某个列的多个可能值      where A in（200，300）      A中的列等于200，300的值 

（4）order by排序
desc 降序 默认升序
（5）insert into 向表中插入新纪录

不指定列名 insert into employee(empName, empAge, empAddress) values('李四',21,'北门')  指定列名 insert into employee(empName, empAge, empAddress) values('李四',21,'北门'）  旧表插入新表 select * into 新表 from 旧表 (相当于把表备份了,只备份表结构与数据，约束消失) select * into student2 from student  --select 列名 into 新表 from 旧表 (相当于把表备份了，只需要其中几列) select stuName,stuSex,stuAddress into student3 from student  --select 列名 as 别名 into 新表 from 旧表 (相当于把表备份了，只需要其中几列,可用as加别名) select stuName as '姓名',stuSex as 性别,stuAddress 地址 into student4 from student 

（6）更新表中的记录（是已经存在的数据）

update 表名 set country='USA'--更新了国家为USA where    --固定规定哪些记录被更新如不写则全更新 

（7）delect删除表中的行

 delect  from  web  where name=‘张三’  where规定哪些行要被删除，不写全被删除 

（8）top 规定返回行的数目

select top 5  percent *  from --返回前5行 select top 5  percent * from  A  order by  ID decs  返回后5行 

（9）别名AS

 可以使用as关键字起别名  也可以用=来起别名 

（10）join 用来把两个表或者多个表的行连接起来

left join （左连接）右边没有用null right join（右连接）左边没有用null inner join（全连接）都得右 实例 １．左连接 SELECT ＊FROM  student AS stu --左表 LEFT JOIN exam exa --右表 可以省略as关键字 ON (stu.stuId = exa.stuId)--on是条件 

（11）合并两个或者多个select 结果

union （两个select 内部必须有相同列） 选取不同值，不允许重复 union all  允许重复 

（12）数据库的完整性（约束）
数据完整性=数据的有效性和正确性(完整性=有效,正确)
完整性约束这里是考试重点要很熟悉

1.唯一约束(允许一行数据为空) unique 2.主键约束(唯一约束 + 非空约束)  primary key 3.默认值约束 default   4.check约束 限制列中值得范围 5.主外键约束  foreign key 6.非空约束  not null 一个表在不同的关系中既可以是主表又可以是子表 主外键是一对多关系 

create table student(－－创建表
–字段名 数据类型 约束 (一般在此只加非空约束)
　stuId int not null,
　stuName varchar(20) not null,
　stuAge int not null,
　stuSex bit not null,
　stuTel varchar(13) not null,
　stuAddress varchar(30)
)
go
–添加主键约束

alter table student　　－－alter是修改已经存在的 add constraint PK_stuId primary key (stuId) 

–添加唯一约束

alter table student add constraint UQ_stuName unique (stuName) 

–添加check约束

alter table student add constraint CK_stuAge check (stuAge >=18 and stuAge <= 35) 

–添加默认约束

alter table student  add constraint DF_stuAddress default ('地址不详') for stuAddress 

go

create table exam(
examId int not null,
stuId int not null,
labExam int not null,
writtenAxam int not null
)
go
–添加主键约束

alter table exam add constraint PK_examId primary key (examId) 

–添加check约束

alter table exam add constraint CK_labExam check (labExam between 0 and 100)-- 

between Aand 来确定范围表示在AB之间
–添加非空约束

alter table exam add constraint NN_labExam check (labExam is not null) 

–设置非空

(alter table 表名 alter column 列名 该列的类型 NOT NULL) alter table exam  alter column writtenAxam int not null 

在设置空与非空时用is、not is

–添加check约束

alter table exam add constraint CK_writtenAxam check (writtenAxam >= 0 and writtenAxam <= 100 ) 

–添加外键约束

alter table exam --修改外键表 add constraint FK_stuId foreign key(stuId) references student(stuId)     --外键表字段   主键表字段 

go

--删除约束(alter table 表名 drop constraint 约束名) alter table exam drop constraint FK_stuId 

哪个是外键表，就给它添加外键约束
给表添加主外键约束的时候遵循3个一致
1.外键表外键与主键表主键数据类型一致(语法规范)
2.长度一致(语法规范)
3.名称一致(一般是项目规范，名称最好是一致)
也就是说要与主键表一一对应

11.索引
对索引不了解的可以看一下这个索引介绍
类似于目录
（1）类型

1.唯一索引(UNIQUE)：不允许两行具有相同的索引值（创建了唯一约束，系统将自动创建唯一索引） 2.主键索引：主键索引要求主键中的每个值是唯一的，（创建主键自动创建主键索引） 3.聚集索引(CLUSTERED)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，表中只能包含一个聚集索引，主键列默认为聚集索引 4.非聚集索引(NONCLUSTERED)：表中各行的物理顺序与键值的逻辑（索引）顺序不匹配，表中可以有249个非聚集索引 

（2）创建

USE xmgl GO   --创建索引 IF EXISTS(SELECT* FROM sysindexes WHERE name='emp_id')--创建索引之前要判断是否存在索引 DROP INDEX UserInfo.emp_id--表名.索引名 GO--先判断索引是否存在存在则删除 GO 批处理 CREATE NONCLUSTERED INDEX emp_id-- NONCLUSTERED 非聚集索引 INDEX代表创建的是索引 ON 员工表(员工号)--ON代表在哪个表哪个列（索引关键字） WITH  FILLFACTOR =80  --代表填充因子为80 GO 

（3）指定按照某个索引进行查询如下

SELECT 字段1,字段2 FROM 表名 WITH (INDEX = 索引名) WHERE 查询条件 SELECT 字段1,字段2 FROM 表名 WITH (INDEX(索引名)) WHERE 查询条件 指定按'IX_stuName'索引查询 --指定按索引IX_stuName进行查询 SELECT * FROM student WITH(INDEX(IX_stuName)) WHERE stuName = '令狐冲' -指定按索引IX_stuName进行查询 SELECT * FROM student WITH(INDEX = IX_stuName) WHERE stuName = '令狐冲' 

12.group by
定义：具体学习在group by我觉得讲的比较好

 对其进行分组GROUP BY必须得配合聚合函数来用，分组之后你可以计数（COUNT），求和（SUM），求平均数（AVG）等。 

聚合函数

count() 计数 sum() 求和 avg() 平均数 max() 最大值 min() 最小值 

需要注意的
NUll不计算在内，如下示例

SELECT COUNT(schoolName),COUNT(hobby), COUNT(＊) FROM student 

13.having

where 和 having 的区别 where 对表中的原始数据进行的筛选 having 对分组后使用聚合函数计算后的数据进行的筛选，与GROUP BY结合使用 

我们举个例子：

每个部门人数都有了，那如果我们想要进一步知道员工人数大于30000的部门是哪些，这个时候就得用到HAVING了。  SELECT dept_no as 部门, count( emp_no) as 人数 FROM dept_emp  WHERE to_date = '9999-01-01'  GROUP BY dept_no 

13.in or 关键字
in可以用or代替

--使用or关键字 SELECT * FROM student WHERE stuAge = 18 OR stuAge = 27 OR stuAge = 26 --使用in关键字 SELECT * FROM student WHERE stuAge IN (18, 27, 26) --使用BETWEEN AND 关键字 SELECT * FROM student WHERE stuAge BETWEEN 18 AND 27 SELECT * FROM student WHERE stuAge >= 18 AND stuAge <= 27 

14.自动增加
在定义创建表的时候可以用

ID int identity （1，1） primary key 开始值为1，后面自动加1 

15.字符串函数
CHARINDEX函数,下标从1开始，如果没找到的话返回0，而在java中下标从0开始，没找到的话返回-1

SELECT CHARINDEX('on', 'my name is jiongjiong', 15) --LEN函数 SELECT LEN('jack') --LEN函数是求长度 SELECT stuName, stuAddress, LEN(stuAddress) AS '长度' FROM student  --RTRIM函数和LTRIM函数是去掉左右两边的空格 SELECT ＊ FROM student WHERE RTRIM(LTRIM(stuAddress)) = '江西省赣州市' SELECT ＊FROM student WHERE LTRIM(stuAddress) = '江西省赣州市' UPDATE student SET stuAddress = ' 江西省 赣州市 ' WHERE stuId =19 

16.日期函数
详细讲解点击日期函数讲解
（1）GETDATE() 返回当前系统日期
（2）DATEADD(日期部分,常数,日期) 返回将日期的指定日期部分加常数后的结果返回
（3）DATEDIFF( 日期部分,日期1,日期2) 返回两个日期之间的差值,日期部分(日期1-日期2)
（4）DATENAME() 返回指定日期的日期部分的字符串(返回字符串)
（5）DATEPART() 返回指定日期的日期部分的整数(返回整数)
（6）DAY() 返回指定日期的日部分的整数
（7）MONTH() 返回指定日期的月部分的整数
（8）YEAR() 返回指定日期的年部分的整数

17.T-SQL定义变量
声明变量 ：DECLARE 变量名 数据类型
定义变量：使用@符号
给变量赋值使用：set关键字

DECLARE @age INT SET @age = 10 PRINT @age --SELECT @age PRINT '@age = ' + CONVERT(VARCHAR(5), @age) SELECT @age = stuAge FROM student WHERE stuId = 10 --必须确保只有一条记录 PRINT @age PRINT '@age = ' + CONVERT(VARCHAR(5), @age) DECLARE @stuAge INT SELECT @stuAge = stuAge FROM student WHERE stuName = '李四' SELECT * FROM student WHERE stuAge > @stuAge 

18.子查询（非常重要！！！自己去找练习题多做题）
子查询(一个查询的结果作为另外一个查询的条件一般子查询要保证返回结果只有一个(即一行,一列)in子查询返回一列多行子查询从内往外读(写)当要显示多个表数据时使用联结，只显示一个表数据时使用子查询
)

SELECT * FROM student WHERE  stuAge > (SELECT stuAge FROM student WHERE stuName = '李四') 

牛客上有许多练习题区多练习！！！！

19.流程控制

SELECT  * FROM exam SELECT  *, '等级' =   CASE   WHEN labExam < 60 THEN 'E'  WHEN labExam > 60  AND labExam <= 69 THEN 'D'  WHEN labExam BETWEEN 70 AND 79 THEN 'C'  WHEN labExam BETWEEN 80 AND 89 THEN 'B'  ELSE 'A' END FROM exam 

回顾下以前的知识
流程控制，分支判断
多重判断：嵌套if,阶梯式if(可以是区间范围，可以是定值),switch(只能是某个定值)

20.WHILE循环

DECLARE @testIndex int SET @testIndex = 0 WHILE(@testIndex < 10)  BEGIN  PRINT '第' + CAST((@testIndex + 1 ) AS VARCHAR(3))+ '次打印hello'  SET @testIndex = @testIndex + 1 END 

21.视图
视图的优点或用途：

1.筛选表中的行 2.防止未经许可的用户访问敏感数据 3.降低数据库的复杂程度(或者说降低sql的复杂度) 4.将多个物理数据库抽象为一个逻辑数据库 

视图特点：

视图是为查询服务的，不能通过视图新增、修改、删除数据，同一张 

表可以根据需求创建不同的视图

将多个物理数据库抽象为一个逻辑数据库,以下示例

CREATE VIEW 视图名  AS  SELECT 表A.列1,表A.列2,表B.列1,表B.列2 FROM 数据库名1.角色名.表名1 AS 表A INNER JOIN 数据库名2.角色名.表名2 AS 表B ON (表A.主键 = 表B.外键)  CREATE VIEW test1  AS  SELECT * FROM student WHERE stuSex = 0  GO SELECT * FROM test1 go alter VIEW test1 AS SELECT *,stuId AS 'id', stuName AS 'myName' FROM student WHERE stuSex = 0 GO 

更新视图中的数据，对视图中的数据的增加，删除，修改时也会影响原表

UPDATE test1 SET stuName = '风清扬老前辈' WHERE id = 5 IF('a' = 'a') PRINT '相等' ELSE PRINT '不相等'  IF('a' = 'A') PRINT '相等' ELSE PRINT '不相等' 

22.事务
（1）事务什么时候用：

同时执行多个insert,update,delete,sql语句时使用事务是作为单个逻辑工作单元执行的一系列操作，这些操作作为一个整体一起向系统提交，要么都执行，要么都不执行，事务是一个不可分割的工作逻辑单元 

（2）事务必须具备一下4个属性，简称ACID

1.原子性A 2.一致性C 3.隔离性I 4.永久性D 

（3）语句

BEGIN TRANSACTION 开始事务 COMMIT TRANSACTION 提交事务 ROLLBACK TRANSACTION 回滚事务 

（4）事务分类

1.显示事务：用BEGIN TRANSACTION 关键字明确指定事务的开始和明确指定提交事务(COMMIT TRANSACTION )和明确指定回滚(ROLLBACK TRANSACTION) 2.隐式事务：不需要指定事务的开始即不需要使用BEGIN TRANSACTION关键字开始，但是需要手工提交事务和手工回滚事务，如果要用隐式事务的话，需要设置一个开关(即SET IMPLICIT_TRANSACTIONS ON)。你也可以理解为显示事务和隐式事务没什么区别，不同的地方就是，显示需要用BEGIN TRANSACTION 关键字，隐式不需要用BEGIN TRANSACTION 关键字。因为它已经是隐式事务了，就没有必要每次都加上BEGIN TRANSACTION 了，但是我们必须在后面加上提交事务(COMMIT TRANSACTION )和回滚事务(ROLLBACK TRANSACTION)，必须手工去提交事务或手工回滚事务了 3.自动提交事务：这是sqlserver的默认模式，它将每条单独的T-SQL语句视为一个事务 

开启隐式事务：SET IMPLICIT_TRANSACTIONS ON
关闭隐式事务：SET IMPLICIT_TRANSACTIONS OFF

我们暂时只能在一条conn上做事务，如果要在多条conn上做事务，一般借助框架或容器，使用JTA(java TRANSACTION API)即java事务接口

23.存储过程
看这篇文章吧小编又变懒了存储过程

24.触发器
（1）定义

触发器（TRIGGER）是由事件来触发某个操作。这些事件包括INSERT语句、UPDATE语句和DELETE语句。当数据库系统执行这些事件时，会激活促发其执行相应的操作。 

（2）触发器语法

CREATE TRIGGER trigger_name ON table_name [WITH encryption] --是否加密 FOR [DELETE,INSERT,UPDATE] AS T_SQL语句 GO 

（3）查看触发器

SHOW TRIGGERS 语句查看触发器信息 SHOW TRIGGERS; 在 triggers 表中查看触发器信息 DROP TRIGGER trig_book2 ; 

这里就不进行具体讲解详细学习点击这个查看触发器

+,有错误评论区欢迎指出