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