SQL sever 笔记

SQL sever 笔记

2023年12月2日 星期六
今天学习了SQL sever

# 数据库概述

## 1、 数据库的好处

1. 将数据持久化到本地
2. 提供结构化查询功能

## 2、 数据库的常见概念

1. DB：数据库，存储数据的仓库
2. DBMS：数据库管理系统，又称为数据库软件或者数据库产品，用于创建和管理数据库，常见的有MySQL、Oracle、SQL Server
3. DBS：数据库系统，数据库系统是一个通称，包括数据库、数据库管理系统、数据库管理员等，是最大的范畴。
4. SQL：结构化查询语言，用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据库软件通用的语言。

## 3、 数据库的存储特点

1. 数据存放到表中，然后表再放到库中
2. 一个库中可以有多张表，每张表具有唯一的表名用来标识自己
3. 表中有一个或多个列，列又称为“字段”，相当于Java中“属性”
4. 表中的每一个行数据，相当于Java中“对象”

## 4、 数据库的常见分类

1. 关系型数据库：MySQL、Oracle、DB2、SQL Server

2. 非关系型数据库：

   * 键值存储数据库：Redis、Memcached、MemcacheDB

   * 列存储数据库：HBase、Cassandra

   * 面向文档的数据库：MongDB、CouchDB

   * 图形数据库：Neo4J

## 5、 SQL语言分类

1. DQL：数据查询语言：select、from、where
2. DCL：数据控制语言：grant、revoke
3. DDL：数据定义语言：create、alter、drop、truncate
4. DML：数据操作语言：insert、update、delete
5. TCL：事务控制语言：commit、rollback

2023年12月4日 星期一
今天做完了上周剩下的题目,学习了sql sever 的增删改
1.修改表名
exec sp_rename '旧表名','新表名'
2.修改列名
exec sp_rename '表名.列名','新列名'
3.修改列属性
alter table  表名 alter column 列名 属性
4.修改/删除数据 同 MySQL
drop/delete
5.插入列 同MySQL
add
6.删除一列
alter table 表名 drop column 列名

2023年12月7日 星期四
今天学习了SQL sever 的约束
约束
not null:非空
unique:唯一
default:默认
check:检查约束
priemary key:主键
foreign key:外键
identity(1,1):自增

create table 表名(
    字段名 字段类型 列级约束，
    字段名 字段类型,
    表级约束    
)

2023年12月8日 星期五

SQL sever数据库管理
创建数据库
create database 数据库名

删除数据库
drop database 数据库名

图像创建删除:略

数据类型

1.整数型
bigint:大整数
int:整数
smallint:小整数
tinyint:微短整数

2.精确数值型
decimal
numeric
- 精度为1～9时，存储字节长度为5。
- 精度为10～19时，存储字节长度为9。
- 精度为20～28时，存储字节长度为13。
- 精度为29～38时，存储字节长度为17。

3.浮点型
real
float
浮点型包括real和float[(n)] 。两者通常都使用科学计数法表示数据，即形为：尾数E阶数，如5.6432E20、-2.98E10、1.287659E-9等。

当n在1～24之间时，实际上是定义了一个real型数据，存储长度为4字节，精度为7位有效数字。当n在25～53之间时，存储长度为8字节，精度为15位有效数字。当缺省n时，代表n在25～53之间。

4.货币型
money:数据的数范围为-2的63次方（-922337203685477.5808）～2的63次方-1（922337203685477.5807），精度为19，小数位数为4，长度为8字节。

smallmoney:数据的数范围为-2的31次方（-214748.3648）～2的31次方-1（214748.3647），精度为10，小数位数为4，长度为4字节。

5.字符型
char:定长字符数据类型，其中n在1到8000之间，缺省为1。当实际存储的串长度不足n时，则在串的尾部添加空格。
varchar[(n)]：变长字符数据类型，这里n表示的是字符串可达到的最大长度

6.unicode字符型
Unicode是“统一字符编码标准”，用于支持国际上非英语语种的字符数据的存储和处理，包括nchar[(n)]和nvarchar[(n)] 。

Unicode字符型包括两类：nchar是固定长度的数据类型，nvarchar是可变长度的数据类型，二者均使用UNICODE UCS-2字符集。

- nchar[(n)]：n的值在1与4000之间，缺省为1，长度2n字节。若输入的字符串长度不足n，将以空白字符补足。
- nvarchar[(n)]：nvarchar[(n)]为最多包含n个字符的可变长度Unicode字符型数据，n的值在1与4000之间，缺省为1。长度是所输入字符个数的两倍。

实际上，nchar、nvarchar与char、varchar的使用非常相似，只是字符集不同（前者使用Unicode字符集，后者使用ASCII字符集）。

7.文本类型
* text类型可以表示最大长度为 2的31次方-1（2147483647）个字符，其数据的存储长度为实际字符数个字节。
* ntext类型可表示最大长度为 2的30次方-1（1073741823）个Unicode字符，其数据的存储长度是实际字符个数的两倍（以字节为单位）。

8.日期类型
datetime：可表示的日期范围从1753 年1月1日到9999年12月31日的日期和时间数据
smalldatetime：可表示从1900年1月1日到2079年6月6日的日期和时间，数据精确到分钟。
date

9.位型
bit型只存储0和1，长度为一个字节。但要注意，SQL Server对表中bit类型列的存储做了优化：

如果一个表中有不多于8个的bit列，这些列将作为一个字节存储；如果表中有9到16个bit列，这些列将作为两个字节存储；更多列的情况依次类推。

字符串值TRUE和FALSE可以转换为以下bit 值：TRUE转换为1，FALSE转换为0。

查询

1.简单查询 (同MySQL)
2.别名查询
【格式一】：
select 字段名 as 别名 from 【表名/查询结果集】;

【格式二】：
select 字段名 别名 from 【表名/查询结果集】;

【格式三】：
select 别名=字段名 from 【表名/查询结果集】;

3.去重查询(同MySQL)

4.条件查询
条件运算符：>、>=、<、<=、=、<>（不等于）、!=（不等于）、{!<（不小于）、!>（不大于）SQL sever特有}
* 逻辑运算符：and、or、not   (用法同MySQL)
* 模糊运算符：
  * like：%任意多个字符、_任意单个字符，如果有特殊字符，需要使用escape转义
  * between and
  * not between and
  * in
  * not in
  * is null
  * is not null

5.分组查询   group by  (同MySQL)

6.过滤查询  having  (同MySQL)

7.排序查询 order by   desc/asc  (同MySQL)

8.多表查询  (同MySQL)

9.子查询  (同MySQL)
 bug:如果from后的表是我们上一步求出来的结果,其中包括了order by ,查询结果就一定报错

想出结果就必须把order by 删除才有结果

10.限制查询 (分页)
select top 获取前几条 【*/字段名,...】 from 【表名/查询结果集】
【where 查询条件】
【group by 分组条件】
【having 过滤条件】
【order by 排序字段 asc|desc】;

案例演示1：查询XSB表中前10条记录，输出所有列

select top 10 * from XSB;

案例演示2：查询XSB表中第11-20条记录，输出所有列

select top 10 * from XSB
where 学号 not in (select top 10 学号 from XSB);

查询学生信息按照年龄升序排序，在结果中从第11条数据开始取，取两个出来
select top 2 * from student where id not in(select top 10 id from student order by age asc
) order by age asc
 

2023年12月9日 星期六

sql sever 高级语法
1.索引
### 索引概念

索引是帮助SQL Server高效获取数据的一种有序的数据结构。它有以下两种分类：

* **聚簇索引**：聚簇索引的顺序就是数据在物理存储顺序。每个表只有一个聚簇索引，SQL Server 2005 是按B树（BTREE）方式组织聚簇索引的，聚簇索引的叶节点就是数据节点，由于数据记录按聚簇索引键的次序存储，因此查找效率高。除非在创建主键时特别指定，否则创建主键时会自动创建聚簇索引。
* **非聚簇索引**：非聚簇索引的索引顺序与数据物理顺序无关。非聚簇索引也是按B树方式组织的，但非聚簇索引B数的叶接地那不存放数据页信息，而是存放非聚集索引的键值，并且每个键值项都有指针指向包含该键值的数据行。一个表中可有多个非聚集索引，创建索引时，可指定是按升序还是降序。

语法

创建索引
create [unique] index 索引名称 on 表名(要创建的索引字段1,要创建的索引字段2.....)

重建索引
alter index 索引名称 on 表名 rebuild

删除索引
drop index 表名.索引名

2.视图
### 视图概念

视图时一个虚拟表，其内容由查询定义。但视图并不存储数据，视图数据来自由定义视图的查询所引用的表。

使用视图有下列优点：

* 简单性：如可屏蔽表连接等复杂操作。
* 安全性：简化用户权限的管理，将用户限制在数据的不同子集上。
* 逻辑数据独立性：基本表的改变只需改变视图，而无需修改应用程序。

创建视图
create view 视图名称 as 查询语句

调用视图
select * from 视图名称

删除视图
drop view 视图名称

修改视图
先删除,再创建,创建时修改

3.T-SQL语言

### 概念

SQL语言的全名是结构化查询语言（Structured Query Language），1986年10月，美国ANSI对 SQL进行规范后，以此作为关系数据库管理系统的标准语言。不同的数据库管理系统对SQL规范做了某些编改和扩充。例如，微软公司的SQL Server支持的是T-SQL，而甲骨文公司的Oracle数据库所使用的SQL语言则是PL-SQL。T-SQL是SQL语言的一种版本，只能用于微软SQL Server以及Sybase Adaptive Server系列数据库。T-SQL除了提供标准的SQL命令之外，还提供了变量说明、流程控制、功能函数等。在SQL Server数据库中，T-SQL语言由DQL、DCL、DDL、 DML 及流控制语句组成。

### 常量

按类型分为：字符串常量、整型常量、实型常量、日期时间常量、货币常量、唯一标识常量等。

SQL Server中变量可分为两类：全局变量，局部变量。

* **全局变量**：全局变量由系统提供且预先声明，以“@@”开头。T-SQL全局变量作为函数引用。例如，

  @@ERROR返回执行的上一个T-SQL语句的错误号；@@CONNECTIONS返回自上次启动SQL Server以

  来连接或试图连接的次数。

* **局部变量**：局部变量以@开头。例如，保存运算的中间结果，作为循环变量等。

局部变量的定义:
declare {@变量名 数据类型}[,...n]

局部变量的赋值:
set 语法格式 :一个set语句只能给一个变量赋值
set @变量名=表达式

select语法格式：一个select语句可以给多个变量赋值
select { @变量名＝表达式 }[,...n]

### 数据类型

数据类型包括 **系统数据类型** 和 **用户自定义数据类型**

* **系统数据类型**：系统数据类型又称为基本数据类型，在第四章已介绍。
* **用户自定义数据类型**：基于基本数据类型，可由其他表字段引用，在这里不重点介绍，了解即可。

#### 运算符

1. **算术运算符**：+（加）、-（减）、*（乘）、/（除）、%（求模）。
2. **位运算符**：执行位操作，两个表达式的类型可为整型或与整型兼容的数据类型（例如字符型等，但不能为image类型）。
3. **比较运算符**：比较运算符又称关系运算符，其运算结果为逻辑值，可以为三种之一：TRUE、FALSE 及 UNKNOWN。
4. **逻辑运算符**：逻辑运算符在SELECT语句的WHERE子句部分讲过，在此了解即可。
5.  **字符串联接运算符**：通过运算符“+”实现两个字符串的联接运算。
6. **一元运算符** ：+（正）、-（负）和～（按位取反）。
7. **赋值运算符** ：指SET和SELECT语句中使用的“=”。

### 流程控制语句

流程控制语句可以改变计算机的执行顺序。

SQL Server流程控制语句如下图：

begin ...end     语句块                  cont inue    用于重新开始下一次循环

if...else        条件语句               break        用于退出最内层的循环

case             分支语句               return       无条件返回

goto             无条件转移语句     waitfor      为语句的执行设置延迟

while            循环语句

#### 语句块

语法格式：BEGIN…END可以嵌套使用。

案例演示：查询XSB和KCB的信息。
begin
    select * from XSB;
    select * from KCB;
end

#### 条件语句

语法格式：用于条件判断。

案例演示：如果1=1，则查询学生表，否则查询KCB。
if 1=1
    select * from XSB;
else
    select * from KCB;

#### 分支语句

语法格式：用于分支选择。

案例演示：判断变量的数值来选择输出“男”还是“女”。

#格式一：
declare @性别 bit =1;
select 性别=
case @性别
   when 1 then '男'
   when 0 then '女'
end;

#格式二：
declare @性别 bit =0;
select 性别=
case 
   when @性别=1 then '男'
   when @性别=0 then '女'
end;

#### 无条件转移语句

语法格式：label是指向的语句标号，标号必须符合标识符规则。

案例演示：将学号为"081102"的学生的总学分使用循环修改到大于等于60，每次只加2并判断循环了多少次。
declare @num int =0;
while (select 总学分 from XSB where 学号 = '081102')<60
begin
    update XSB set 总学分=总学分+2 where 学号= '081102'
    set @num=@num+1
end;
select @num as 循环次数;

#### continue语句

语法格式：一般用在循环语句中，用于结束本次循环，重新转到下一次循环条件的判断。

continue;

#### break语句

语法格式：一般用在循环语句中，用于退出本层循环。当程序中有多层循环嵌套时，使用break语句只能退出其所在的这一层循环。

break;

#### return语句

语法格式：用于返回使用，可以返回一个表达式的值。
return [表达式];

系统内置函数

数学函数 (同Oracle)

sqrt()：开平方根。
select sqrt(4);

字符串转换函数 (同MySQL)

str()：把数值型数据转换为字符型数据。
select STR(100);

char()：将ASCII码转换为字符。输入0 ~ 255之间的ASCII 码值，否则返回NULL 。
select CHAR(97);

ascii()：返回字符表达式最左端字符的ASCII码值。
select ASCII('a');

字符串处理函数 (同MySQL)

#### 数据类型转换函数

常用的类型转换有：日期型→字符型、字符型→日期型、数值型→字符型、字符型→数值型等。

CAST (表达式 AS 新类型)
CONVERT (新类型 ,表达式)

日期型→字符型：

select CAST('1997-12-05' as varchar(30));

select CONVERT(varchar(30),'1997-12-05');

字符型→日期型：
select CAST('1997-12-05' as datetime);

select CONVERT(datetime,'1997-12-05');

数值型→字符型：

select CAST(1997 as varchar(30));

select CONVERT(varchar(30),1997);

字符型→数值型：

select CAST('1997' as int);

select CONVERT(int,'1997');

#### 日期时间函数

getdate()：返回当前系统日期和时间，返回值类型为datetime。

select GETDATE();

year()：返回指定日期的年部分，返回值为整数。

month()：返回指定日期的月部分，返回值为整数。

day()：返回指定日期的天部分，返回值为整数。

datediff()：返回两个指定日期在datepart（年、月、日）方面date2超过date1的差距值，其结果值是一个带有正负号的整数值。
select DATEDIFF(DAY,'2020-01-01','2020-01-10');
select DATEDIFF(MONTH,'2019-01-01','2020-01-01');
select DATEDIFF(YEAR,'2019-01-01','2020-01-01');

select DATEDIFF(YEAR,-1,'2020-01-01'); 给某个日期减上一年

元数据函数

db_id()：根据数据库名，返回数据库标识（ID）号。
select DB_ID('学生信息数据库');

db_name()：根据数据库ID，返回数据库名。
select DB_NAME(DB_ID('学生信息数据库'));

object_id()：返回数据库中对象的ID号 ，返回值类型为smallint。

select OBJECT_ID('XSB');

### 用户定义函数

#### 函数语法

创建函数：

create function 函数名称 ( [@参数名 参数类型 [=默认值]],... )
returns 返回值类型
as
begin
    函数体
    return 表达式;
end;

调用函数：

select dbo.函数名(实参1,实参2,...,实参n);

删除函数：

drop function 函数名称;

修改函数：

先删除、在创建

4.触发器
### 触发器概念

SQL Server触发器分为DML触发器和DDL触发器这两种。

* DML触发器，执行INSERT、 DELETE 、UPDATE语句时触发。DML触发器可以实现数据的完整性、多个表间数据的一致性等。比如，实现外键的功能，当向CJB中插入记录时，保证学号是XSB表中已存在的；再如，可通过对XSB表定义DELETE触发器，实现在XSB表中删除一个学生时，同时删除CJB表中所有该学生的记录。
* DDL触发器，只由T-SQL语句触发。是SQL Server 2005新增的功能，也是由相应的事件触发，但DDL触发器在执行CREATE、ALTER、DROP等语句时触发。

在触发器中可以使用两个特殊的虚拟表inserted和deleted：

- inserted表存放新增的记录
- deleted表存放被删除的记录

Update操作时：新的记录存入inserted表，旧的记录存入deleted表。

创建触发器:(for/instead of 的效果一样选一个用就行)

create trigger 触发器名称  on 表名/视图名
{for/after/instead of}{insert[,]/updte[,]/delete}
as 
begin
   触发器需要执行的逻辑
end;

删除DML触发器:
drop trigger 触发器名称

删除DDL触发器:
drop trigger 触发器名称 on 数据库作用域/服务器作用域;

修改触发器：
先删除、在创建

SQL sever备份与恢复

1.数据备份
USE master
GO
EXEC sp_addumpdevice 'disk',                                --磁盘
     '逻辑名称',                                --逻辑名
     '备份地址'                                        --物理名
backup database 数据库名称 to 逻辑名称                                  --备份
 

例如:
USE master
GO
EXEC sp_addumpdevice 'disk',                          --磁盘
     'mybackupfile',                          --逻辑名
     'D:\mybackupfile.bak'                      --物理名
backup database 学生信息数据库 to mybackupfile                      --备份

2.数据恢复:

restore database 数据库名称 from 逻辑名称 with file=1, REPLACE

案例演示：

注意：只有数据库被删除后并且在删除前进行了完全备份，才能使用下边这条命令恢复。

restore database 学生信息数据库 from mybackupfile with file=1, REPLACE