SqlServer与SqlServer Manager studio学习

附加.1数据库文件 

数据库文件包括主要数据文件、次要数据文件、事物日志文件;

主要数据文件:存放数据和数据库的初始化信息，每个数据库有且只能有一个主要数据库文件，以.mdf为扩展名;

次要数据文件:存放除了主要数据文件以外的所有数据的文件，次要数据文件并不是必须的，可以没有，也可以是一个或者多个，以.ndf为扩展名；

事物日志文件：存放用于恢复数据库的所有日志信息，每一个数据库至少要有一个日志文件，可以有多个，以.ldf为扩展名;

1.分离（备份）、附加（还原）

适用于拷贝数据库，添加数据库;

2.新建用户

新建用户，强制密码过期不勾选，在 安全性->登录名 中设置创建用户的属性，在状态一栏中有“登录”一项，默认是禁用，改为启用。

用Windows身份登录Management Studio，右击服务器选择属性，将安全性选项 更改为:SQL Server 和 Windows 身份验证模式；

在计算机-管理-服务中重启sqlServer

3.主键与外键

主键:能够唯一标识一个事物的一个或多个字段的组合；

主键通常是整数，不建议使用字符串当整数，因为字符串当主键检索效率不高，如果是用于集群式服务，可以考虑用字符串当主键；

主键的值通常不允许修改，除非本记录被删除；

主键建议不要写id，写成表名Id或者表名_id;

用代理主键，不要用业务主键，任何一个表，强烈建议不要用与业务有关联的字段当主键，因为设计到字段数据的修改，影响到外键的表，我们通常用一个整型编号充当主键；

一张表的一个字段的数据源自另外一张表或本表的主键或唯一键，那么这个字段就叫做外键，一个表中可以有多个外键；

含有外键字段的表称为外键表，外键所对应的主键所在的表称为主键表，一般先有主键表才有外键表；例如先建立部门，才有员工，部门是主键表，员工表是外键表；

create table province (
	id int constraint pk_pro_id primary key,
	pro_name nvarchar(20) not null,
	code nvarchar(20)
);
create table company (
	--这里是注释
	id int primary key,
	company_name nvarchar(100) not null,
	addr nvarchar(200) not null,
	registerMoney int not null,
	legalPerson nvarchar(50),
	province_id int constraint fk_province_id foreign key references province(id)
);

4.表的相关属性

字段(列):表示一个事物的某个属性/特征；

记录(元组):字段的组合，表示一个具体的事物；

表:记录的组合，表示同一类事物的集合；

字段是事物的属性，记录是事物本身，表示事物的集合；

5.无法修改表字段---提示不允许保存修改

工具-〉选项-〉左侧有个 设计器-〉表设计器和数据库设计器 -> 阻止保存要求重新创建表的更改(右侧) 把钩去掉即可

6.nvarchar

nvarchar n-国际的，可支持汉字，var-variable变量，char-字符，nvarchar表示支持汉字符号的长度可变化的字符

7.约束

对一个表中的属性操作的限制叫做约束；

分类:

①主键约束(实体完整性):不允许重复元素，避免了数据冗余;

②外键约束(引用完整性)：通过外键约束从语法上保证了本事物所关联的其他事物一定是存在的；

③check约束:保证事物属性的取值在合法的范围之内；

④default约束:保证事物的属性一定会有一个值;

⑤唯一约束(unique):保证了事物属性的取值不会重复，唯一键可以是空值，但是只能有一行是空值；

⑥非空约束(not null):要求用户必须为改属性赋值;

唯一约束与主键的区别:

        1. 同一张表只能有一个主键约束，但能有多个唯一约束；
        2. 主键约束字段值不能为NULL，唯一约束字段值可以为NULL；
        3. 主键约束字段可以做为其他表的外键，唯一约束字段不可以做为其他表的外键；
        4. SQLServer默认为主键约束字段创建聚集索引，为唯一约束字段创建非聚集索引；

8.单引号与双引号

数据库中，单引号代表字符串，双引号代表字段别名;

9.一对一关系

既可以把A表的主键当做B表的外键，也可以把B表的主键当做A表的外键;

这种关系并不常见，因为一般来说，按照这种方式相关的信息都在一个表中。可以利用一对一关系来分割具有多列的表；

10.一对多

就是 A 表的一条记录，对应 B 表的多条记录，且 A 的主键作为 B 表的外键;

11.多对多

可以理解为是一对多和多对一的组合。要实现多对多，一般都需要有一张中间表（也叫关联表），将两张表进行关联，形成多对多的形式;

 第三张关联表以两个外键的整体为主键;

12.计算列

select ename,sal "月薪",sal*12 as yearSal from emp;

13.distinct

查询非重复的数据
 

//筛选非重复的deptno数据
select distinct deptno from emp;

//筛选非重复的comm+deptno数据
select distinct comm,deptno from emp;

14.between

select * from emp where sal >= 1600 and sal<=3000;
等价于
select * from emp where sal between 1600 and 3000;

select * from emp where sal < 1600 or sal> 3000;
等价于
select * from emp where sal not between 1600 and 3000;

15.top

//输出前50条数据
select top 50 * from emp;

//输出前50%条数据
select top 50 percent * from emp;

16.null

null不能参与<>,!=,=运算,可以参与is,is not运算;

select  * from emp where comm  is not null;

//判断非空
select ename,sal,comm,sal*12+isnull(comm,0)  "年薪" from emp;

0和null是不一样的，null表示空值，0表示一个确定的值；

任何类型的数据都允许为空；

null不能参与任何数据的运算，否则结果永远为空；

17.创建视图

视图可以简化查询，避免查询语句的重复编写，类似于编程语言的方法；

视图的格式

create view 视图名字 as
--select前面不能添加begin
select ...
--select后面不能添加end

create view v_scott_avg_info as
select AVG(sal) vag_sal,deptno from emp group by deptno;

//应用视图
use scott
select * from v_scott_avg_info where vag_sal=(select max(vag_sal) from v_scott_avg_info)

 视图的优点:简化查询，可以增加数据的保密性；

视图的缺点:增加了数据库的维护成本；

创建视图的select语句必须为所有计算列指定别名;

视图不是物理表，是虚拟表，所以不建议通过视图更新视图所依附的原始表的数据或结果；

18.存储过程

go 
	if exists(select * from sys.objects where name='pro_emp_dept_info')
	drop procedure pro_emp_dept_info
go
create procedure pro_emp_dept_info as 
select e.ename,e.sal,d.dname from emp e,dept d
where e.sal>800 and e.deptno=d.deptno

//执行存储过程
go 
execute pro_emp_dept_info
go

//带参数存储过程
go 
	if exists(select * from sys.objects where name='pro_emp_dept_info_param')
	drop procedure pro_emp_dept_info
go
create procedure pro_emp_dept_info_param
 ( @salNum int )	--输入参数
as 
select e.ename,e.sal,d.dname from emp e,dept d
where e.sal>@salNum and e.deptno=d.deptno

//执行
go 
execute pro_emp_dept_info_param 1500
go

//带输入参数，输出参数的存储过程
go 
	if exists(select * from sys.objects where name='pro_emp_dept_info_param2')
	drop procedure pro_emp_dept_info_param2
go

create procedure pro_emp_dept_info_param2
 ( @salNum int,		--输入参数
   @salMax int output,	--输出参数
   @deptName nvarchar(50) output
 )
as 
select @salMax=max(e.sal) from emp e
where e.sal>@salNum 

select @deptName=(select d.dname from dept d left join emp e on e.deptno=d.deptno where e.sal=@salMax) from dept

//执行
go 
declare @salNum int
declare @salMax int
declare @deptName nvarchar(50)
set @salNum=1500
execute pro_emp_dept_info_param2 @salNum,@salMax output,@deptName output
print '最高薪资'+convert(nvarchar(20),@salMax)+',部门'+@deptName
go

19.模糊查询like

查询修饰符:

        %表示若干个任意个字符

        _表示一个任意字符        

select * from  emp where ename like '_A%';

        [a-zA-Z]表示包含a-zA-Z的中的任意一个字符

select * from  emp where ename like '_[a-zA-Z]A%';

        [A,Z]表示包含A或者Z中的任意一个字符

select * from  emp where ename like '_[A,Z]A%';

        [^A-Z]表示不包含A-Z的任意一个字符

select * from  emp where ename like '_[^A-F]A%';

escape

在sql中可以把任意字符当做转义字符的标志，具体是哪个字符当作转义字符，则是由escape制定

select * from  emp where ename like '%+%%' escape '+';
select * from  emp where ename like '%+%+_%' escape '+';

20.函数

sql的函数分为单行函数和多行函数；

单行函数:一行记录返回一个值的称为单行函数；

//每一行记录返回一个值
select UPPER(ename) from emp;

多行函数：多行记录返回一个值的称为多行函数;

//所有记录返回一个值
select max(sal) from emp;

 单行函数和多行函数不能同时出现在同一条语句中，否则会出逻辑冲突；

21.聚合函数

聚合函数属于多行函数

聚合函数包括:min(),max(),avg(),sum(),  count()等

  count()：统计某个字段的记录条数，不会统计字段为Null的记录；

select count(distinct deptno) from emp;

22.group by

group by把表中的记录按照计划分成不同的组;

group by之后select中只能出现分组后的整体信息，不能出现组内的详细字段