SQL Server学习笔记
时间：2015-9-11 17:19
学无止境
——什么是数据库
可以对数据进行存储和管理的软件以及数据本身，统称为数据库。
数据库是由表、关系、操作组成。
——为什么需要数据库
几乎所有的应用软件的后台都需要数据库
数据库存储数据占用空间小，容易持久保存
存储比较安全
容易维护和升级
数据库移植比较容易
简化对数据的操作
B/S架构里面包含数据库
——预备知识
学习数据库必须得学习数据库原理吗
应该具备一些数据库原理的知识
学习Sql Server必须得先学一门编程语言吗
不需要，但是懂一门编程语言的话会有助于学习SqlServer的TL_SQL
数据结构和数据库的区别
数据库是在应用软件（外存）级别研究数据的存储和操作
数据结构是在系统软件（内存）的级别研究数据的存储和操作
什么是连接
数据库和客户端是分离的，如果想通过客户端操作数据库，必须先用客户端连接数据库，数据库可以是本地的，也可以是远程的。
有了编程语言为什么还需要数据库
对内存数据操作是编程语言的强项，但是对硬盘数据操作却是编程语言的弱项，对硬盘数据操作却是数据库的强项，是数据库研究
的核心问题。
从三个方面学习数据库
数据库是如何存储数据的
字段 记录 表 约束（主键、外键、唯一键、非空、check、default、触发器）
数据库是如何操作数据的
insert、update、delete、T-Sql、存储过程、函数、触发器
数据库是如何显示数据的
select（重点的重点）
——必备的一些操作知识
如何建立数据库
如何删除数据库
如何附加和分离数据库（备份和删除数据库）
——数据库是如何解决数据存储问题的
1、表的相关数据
字段（列）
一个事物的某一个静态特征（属性）。
记录（行）
字段的组合，表示的是一个具体的事物
表
记录的组合，表示的是同一类型事物的集合
表和字段、记录的关系
字段表示的是事物的属性
记录表示的是事物本身
表是事物的集合
列
字段的另一称谓
属性（列）
字段的另一种称谓
元组（行）
行、记录的另一种称谓
2、create table 命令
通过图形化界面建表。
通过命令建表：
create table Course
create table 最后一个字段的后面建议不要写逗号。
references的作用是外键表创建外键时引用主键表的主键。
3、什么是约束
定义：
对一个表中的属性操作的限制叫做约束。
分类：
主键约束（实体完整性）
不允许重复元素，避免了数据的冗余。
外键约束（引用完整性）
通过外键约束从语法上保证了本事物所关联的其他事物一定是存在的。
事物和事物之间的关系是通过外键来体现的。
constraint约束
check约束
限制某一个值在一定范围内。
例：工资设置必须高于5000，小于500000
create table Employee
(
Emp_id int not null primary key,
Emp_salary float not null check (Emp_salary>5000 and Emp_salary_<500000)
)
default约束
保证实物的属性一定会有一个值，用户如果不给该属性设置值时，系统会给一个默认值。
create table Student
该约束表示默认Stu_sex值为男，（）可以省略，在数据库中字符串用 ‘ ’ 括起来，双引号用来标识一个对象的名字，表的
名字，列的名字，事物的名字。
insert into Student (id,name) values (1,'王五')
在Student表中插入id和name的值，并没有设置sex的值，此时默认值为‘男’。
insert into Student values (2,'李四')
则会报错，因为插入数据与列数不匹配，需要指定插入列。
语句后面不能写逗号，只能写分号。
unique约束
保证了事物属性的取值不允许重复，但允许为null，null不能重复。（Oracle中允许null 重复）
一个表中可以有多个unique列。
create table Student2
insert into Student2 (id,name,sex,age) values (1,'张三','男',1);
为了便于数据库的操作，最好使用一个没有实际意义的字段当做主键（一般是整型数字），该主键最好不是记录的属性。
把本来是主键的键当做唯一键，比如账号和用户名，都不可重复，但是操作用户名时不会导致主键更改。
一般设置主键时加一个identity，使主键自增。
not null 约束
要求用户必须为该属性赋一个值，否则语法出错。
如果一个字段不写null也不写not null，则默认是null，用户可以不给该字段赋值。
如果用户没有为该字段赋值，则该字段的默认值为null。
null和default的区别：
相同点
都允许用户不赋值。
不同点
null修饰的字段如果用户不赋值的默认为null。
default修饰的字段如果用户不赋值则默认是default指定的值。
4、表和约束的区别
数据库是通过表来解决事物的存储问题的。
数据库是通过约束来解决事物取值的有效性和合法性的问题。
建表的过程就是指定事物属性及指定事物各种约束的过程。
5、什么是关系
定义：
表和表之间的联系。
实现方式：
通过设置不同形式的外键来体现表和表的不同关系。
分类：
一对一
既可以把A表的主键充当B表的外键。
也可以把B表的主键充当A表的外键。
一对多
把“一”表的主键充当“多”表的外键。
要在多的一方添加外键，不能在“一”的一方添加外键，否则关系就是多对一了。
多对多
不能在其中一张表插入外键，多对多的关系必须通过单独的一张表来表示。
在数据库关系图中可以看到，banji_jiaoshi_mapping和jiaoshi是多对一的关系，并且banji_jiaoshi_mapping和banji也是多对
一的关系，所以banji和jiaoshi通过第三张表可以体现出多对多的关系。
如果表和表之间的关系被删除，则可以通过“关系”来恢复表和表之间的关系。
至少有两列外键，并且整体是一个主键。
create table banji
--将(banji_id,jiaoshi_id.kecheng)这三个字段的值的整体设置为主键
6、主键
定义：
能够唯一标识一个事物的一个字段或者多个字段的组合，被称为主键。
注意：
含有主键的表叫做主键表。
主键通常都是整数，不建议使用字符串当主键（如果主键是用于集群式服务，可以考虑用字符串当主键）。
主键的值通常不允许修改，除非本记录被删除。
主键不要定义成ID，而要定义成“表明ID”或者“表明_ID”。
要用代理主键，不要用业务主键。
任何一张表，强烈建议不要使用有业务含义的字段充当主键，通常会在表中单独添加一个整型的编号充当主键字段。
7、外键
定义：
如果一个表中的若干个字段是来自另外若干个表中的主键或唯一键，则这若干个字段就是外键。
注意：
外键通常是来自另外表的主键而不是唯一键，因为唯一键可能为null。
外键不一定是来自另外的表，也可能是来自本表的主键。
外键就是连接两个表的字段。
当存在多对一的关系时，一般把外键写在多的一方。
可以表现两个表之间的关系，也只能通过外键来表示。
一个含有外键字段的表称为外键表，外键为主键的表称为主键表。A表中的主键i在B表中作为外键存在，B表就是外键表，A表就
是主键表。
问题：
先删主键表还是先删外键表？
答案：先删外键表
如果先删主键表，会报错，因为这会导致外键表中的数据引用失败。
如果外键表存在，则主键表无法删除。
0.
——查询
1、计算列
--输出一年的工资（12个月）
select sal*12 from emp
select ename as "员工姓名" from emp
从员工表查询员工姓名，并将ename字段改为中文的“员工姓名”，其中as可以省略，”员工姓名“可以使用单引号，也可以不使用
引号，建议使用双引号 “ ” ，这样方便数据库移植。
select 123 from emp
如果select一个无意义的值，则会输出这个值，该值并不代表什么，只是一个值。
注意：
在Oracle中字段的别名不允许用单引号括起来，但是SQL允许使用单引号，为了保证兼容性，最好使用双引号。
2、distinct【不允许重复的值】
关键四distinct用于返回唯一不同的值。
1、select distinct deptno from emp
--distinct deptno会过滤掉重复的deptno值
2、select distinct comm from emp（comm中包含多个null）
--distinct可以过滤掉重复的null，或者说 当有多个null值，只输出一个null。
3、select distinct deptno,comm from emp
--distinct把deptno和comm的组合进行过滤，输出行数按照多的一方输出。
4、select departno,distinct comm from emp
--会出错，因为逻辑上有错误，如果显示14行deptno，则无法显示过滤后的comm。
5、select ename,* from emp
--SQL Server不会报错，而Oracle中会出错，所以为了方便数据库移植，最好不要这样写。
3、between【两者之间】
操作符 between...and会选区介于两个值之间的数据范围，这些值可以是数值、文本或者日期。
1、--查询工资大于1500并且小于3000的人
select * from emp
where sal>=1500 and sal <= 3000
--也可以写成
select * from emp
where sal between 1500 and 3000
如果需要显示范围之外的值，需要使用not操作符。
2、--查询工资小于1500并且大于3000的人
select * from emp
where sal < 1500 or sal > 3000
--也可以写成
select * from emp
where sal not between 1500 and 3000
4、in
in操作符允许我们在where子句中规定多个值。
1、--输出工资为1500、3000、5000的人的信息
select * from emp
where sal in (1500,3000,5000)
--等价于
select * from emp
where sal = 1500 or sal = 3000 or sal = 5000
2、--输出工资不为1500、3000、5000的人的信息
select * from emp
where sal not in (1500,3000,5000)
--等价于
select * from emp
where sal <> 1500 and sal <> 3000 and sal <> 5000
数据库中不等于有两种表示方法：!= 和 <>，推荐使用<>。
not and是or。
not or是and。
5、top
top子句用于规定要返回的记录数目。
--返回emp表前五行的记录。
select top 5 * from emp
--返回emp表前15%的记录
select top 15 percent * from emp
注意：一共14行记录，14*15%=2.1，虽然是小数，但是无论小数位是几，都向前进一，所以显示的是前3行。
6、null
0和null是不一样的，null表示空，没有值，0表示一个确定的值，任何类型的数据都允许为null。
--输出奖金非空的员工信息
select * from emp
where comm <> null
select * from emp
where comm != null
select * from emp
总结：
null不能参与<> !=运算。
任何数字与null运算，结果都为null。
--null可以参与is、is not运算。
--输出奖金为空的员工的信息
select * from emp
where comm is null
--输出奖金不为空的员工的信息
select * from emp
where comm is not null
--输出员工姓名和年薪+奖金
select ename "员工姓名",sal*12+isnull(comm,0) "年薪" from emp
因为奖金comm中含有null值，而null不能参与数学运算，所以使用isnull，如果comm是null，就返回0，否则返回comm的值。
7、order by
order by 语句默认按照升序(asc)对记录进行排序，如果希望按照降序对记录进行排序，可以使用desc关键字进行降序排序。
--默认按照升序进行排序。
select * from emp
order by sal
--先对deptno进行升序排序，当deptno相等时，再对sal进行升序排序。
select * from emp
order by deptno,sal
--先对deptno进行降序排序，当deptno相等时，再对sal进行升序排序。
select * from emp
order by deptno desc,sal
如果在sal后面加上desc，则sal也会按照降序进行排序。
8、模糊查询
格式：
select 字段的集合 from 表名
where 字段的名字 like 匹配的字符
--匹配的条件通常含有通配符
通配符：（大小写通用）
not like
--查询ename字段中不包含A字符的员工信息
select * from emp
where ename not like '%A%'
%
表示任意0个或多个字符。
--查询ename字段中包含A字符的员工信息。
select * from emp
where ename like '%A%'
_
仅替代一个字符。
--查询ename字段中第二个字符为A的员工信息。
select * from emp
where ename like '_A%'
[a-f]
可以是字符列[a-f]中的任意一个字符。
--查询ename字段中第二个字符是A到F中任意一个单一字符的员工信息。
select * from emp
where ename like '_[A-F]%'
[a,f]
可以是a或f的单一字符。
--查询ename字段中第二个字符是A或F的员工信息。
select * from emp
where ename like '_[A,F]%'
[^a-c]
不在字符列中的任意一个字符。
--查询ename字段中第二个字符不在A到F的范围内的员工信息。
select * from emp
where ename like '_[A-F]%'
注意：匹配的条件必须用单引号括起来。（单引号表示字符串，双引号表示一个对象的名字）
--如果要查询% _ 通配符等特殊字符，可以使用：
select * from emp
where ename like '%\%%' escape '\'
--其中escape关键字将'\'转换成“转义字符”，使 '\' 之后的字符不再是本来含义。
9、聚合函数
where中不能包含聚合函数。
函数的分类
单行函数
每一行（记录）都返回一个值
例：
select lower(ename) from emp
--该函数的功能是将ename转换成小写之后再显示输出，每一条记录都会返回一个值并输出，所以lower是单行函数。
多行函数
多行（记录）返回一个值。
聚合函数是多行函数。
例：
select max(sal) from emp
--该函数的功能是将sal列中的最大值输出。
注意的问题：
判断如下SQL语句是否正确：
select max(sal) "最高工资",min(sal) "最低工资", count(ename) "员工人数" from emp --ok
select max(sal), lower(ename) from emp --error 单行函数和多行函数不能一起使用
聚合函数的分类
max()
min()
avg()
count()
--返回表中所有的记录的个数
select count(*) from emp
--deptno重复的记录也被当做有效记录，输出14
select count(deptno) from emp
--返回字段值非空的记录的个数，重复的记录也会被当做有效记录。
--如果使用distinct则会去掉重复的记录，输出3
select count(distinct depeno) from emp
--返回字段不重复并且非空的记录的个数。
--如果有多个null，则会自动去除null记录，输出4
select count(comm) from emp
10、group by
--分组查询只能显示分组之后的整体信息，不能显示组内部某一字段的信息。
select deptno "部门编号",avg(sal) "平均工资",ename"员工姓名" from emp
11、having
having可以对group by之后的数据进行筛选。
having语句可以按照select字段名进行过滤，可以用聚合函数进行过滤，不可以使用别名和模糊查询进行查询。
having中只能查询组的整体信息，但不能查询组的详细信息。
格式：
group by 字段的集合
功能：
把表中的记录按照字段分成不同的组。
例子：
--查询不同部门的平均工资。
select deptno,avg(sal) "平均工资" from emp
group by deptno
--输出部门平均工资大于1600的部门编号和部门的平均工资。
结论：where是对原始记录进行过滤，having是对分组之后的记录过滤。
参数顺序不能变化。
注意：
理解：group by a,b,c,d的用法，先按a分组，如果A相同，再按b分组其次c，最终统计的是最小分组的信息。
一定要明白下列语句为什么是错误的
--select deptno,avg(sal) "部门平均工资" ename from emp
group by deptno
--select deptno,ename from emp
group by emp
--select deptno,job,sal from emp
group by deptno,job
having子句语法注意事项：
1、having子句是用来对分组之后的数据进行过滤，因此使用having时通常都会先使用group by。
2、如果没有使用group by但使用了having，则意味着having把所有的记录当做一组来进行过滤。
select count(*)
from emp
having avg(sal) > 1000
3、having子句出现的字段必须是分组之后的组的整体信息，having子句不允许出现组内的详细信息。
4、select中可以出现字段别名，但是having子句中不能出现字段的别名，只能使用字段名。
5、having和where的异同
相同点：
都是对数据进行过滤，只保留有效数据。
都不能出现字段别名。
不同点：
where是对原始的记录过滤，having是对分组之后的记录进行过滤。
where必须写在having前面，顺序不可颠倒，否则运行出错。
12、连接查询
定义：
将两个表或者两个以上的表以一定的连接条件连接起来，从中检索出满足条件的数据。
分类：
**内连接：
1、select ... from A,B 的用法
select ... from A,B得到的是一个笛卡尔积。
行数是A和B的乘积，列数是A和B之和。
（该临时表前面的是A表的总表，后面的是B表的每一列）
例：
--输出70行，11列
select * from A,B
2、select ... from A,B where ... 的用法
对select ... from A.B产生的笛卡尔积用where中的条件进行过滤。
例：
--输出5行
select * from emp.dept
where empno = 7369 --where是过滤条件，不是连接条件，区别于join中的on
3、select ... from A join B on ... 的用法
--输出14行，2列
select "E".ename "员工姓名","D".dname "部门名称"
--用emp表和dept表连接，on指定连接表的条件：部门表的编号=员工表的部门编号
4、SQL92标准和SQL99标准的区别
select ,,, from A,B where...
是SQL92标准
select ,,, from A join B on
是SQL99标准
输出结果是一样的，推荐使用SQL99标准：
1、SQL99标准更容易理解。
2、在SQL99标准中，on和where可以做不同的分工
on指定连接条件
where对连接之后的临时表（笛卡尔积）的数据进行筛选。
例：
输出工资大于2000的员工姓名、部门名称和工资等级。
--SQL99标准
select "E".ename,"D".dname,"G".grade,"E".sal
5、select、from、where、join、on、group by、order by、top、having的混合使用。
6、习题
（1）求出每个员工的姓名、部门编号、薪水和薪水的等级。
select "T".deptno,"D".dname,"T".avg_sal,"S".grade
外连接：
定义：
不但返回满足连接条件的所有记录（内连接），而且会返回部分不满足条件的记录。
分类：
左外连接：
不但返回满足条件的所有记录，而且会返回左表不满足连接条件的记录。
select * from emp "E"
left join dept "D" --如果不写left，则默认inner内连接。
on "E".deptno = "D".deptno
右外连接：
不但返回满足连接条件的所有记录，而且会返回右表不满足连接条件的记录。
（左外连接+右外连接）
完全连接输出的结果集中包含三部分：
1、两个表中匹配的所有的行的记录。
2、左表中那些在右表中找不到匹配的行的记录，这些记录全部为null。
3、右表中那些在左表中找不到匹配的行的记录，这些记录全部为null。
交叉连接：
select *
from emp
cross join dept
on 1 = 1
等价于
select *
from emp,dept
自连接：
定义：
一张表自己和自己连接起来查询数据。
例子：
不准用聚合函数求薪水最高的员工。
select * from emp
where empno not in (
select distinct "E1".empno
from emp "E1"
join emp "E2"
on "E1".sal < "E2".sal
)
联合：
定义：
两个select生成的临时表的数据以纵向的方式连接在一起。
注意：以前所学将的连接是以横向的方式连接在一起。
例子：
--输出每个员工的姓名，工资和上司的姓名
select "E1".ename,"E1".sal,"E2".ename "上司的姓名"
from emp "E1"
join emp "E2"
on "E1".mgr = "E2".empno
union
select ename,sal,'BOSS' from emp where mgr is null
注意：
若干个select子句联合成功的条件：
1、这若干个select子句输出的列数必须是相等的。
2、这若干个select子句输出列的数据类型至少是兼容的。
13、分页查询
假设每页显示n条记录，当前要显示的是第m页，表名是A，主键是A_id
公式为：
select top n *
from A
where A_id not in(select top (m-1)*n A_id from A)
例子：
select top 3 *
命令：
SQL Server关键字：
add
添加约束。
alter table Department
add constraint PK_Department primary key (Department_No)
create table A
创建A表。
constraint
建表时指定了约束名
create table test
将主键的约束名定义为abc，如果不加constraint定义约束名，则默认分配一个随机名称。
删除表。
删除A表。
drop table A
drop constraint
删除约束
alter table Department
add constraint PK_Department primary key (Department_No)
default
默认约束。
sex nchar(2) default '男' not null。
如果不设置sex的值，则默认为’男‘。
identity
设置主键自动增长，用户不需要为identity修饰的主键赋值。
create table Student
(
Student_id int primary key identity, --如果不设置identity的值，则默认从1开始，每次增长值为1。
Student_name nvarchar(20) not null
)
insert into Student values (1,'张三')
---------------------------------------------
create table Student2
(
Student_id int primary key identity(2015,10), --设置identity的初始值为2015，每次增长值为10。
Student_name nvarchar(200) not null
)
insert into Student2 values ('张三') --如果主键设置为identity，则插入数据时可以不用指定需要插入的数据并且不需要插入主键
--------------------------------------------
delete Student2 where Student_name = '张三'
此时张三的主键为105，则删除之后再插入数据，主键递增为110，默认略过105.
select A from B
从B表查询A列数据，如果A是*，则查询B表全部数据。
foreign key
设置外键。
id int foreign key references Course(Cou_id) not null。
设置id为该表的外键，并且id中的约束和数据引自Course表中的Cou_id。
primary key
设置主键。
id int primary key not null,
设置id为该表的主键。
references
references的作用是外键表创建外键时引用主键表的主键的值和约束。
create table test
select into
select into语句从一个表中选取数据，然后把数据插入另一个表中。
select into语句常用于创建表的备份附件或者用于对记录进行存档。
select column_name
into new_table
from old_table
update
unique
设置唯一约束，用法和主键相似，同一表中数据不可以重复，可以为空，null不可以重复。
name nvarchar(50) unique,
设置name为该表的唯一约束，name中不能出现重复的值，可以为空，null不能重复。
where
SQL Server函数：
avg()
avg函数返回数值列的平均值，null不包括在计算中。
count(字段名)
count函数返回指定列的值的数目，null不计入数目。
first()
first函数返回指定的字段中第一个记录的值。
format()
format函数用于对字段的显示进行格式化。
group by()
group by语句用于结合合计函数，根据一个或多个列对结果集进行分组。
having()
在SQL中增加having子句的原因是，where关键字无法与合计函数一起使用。
lower(字段名)
将该字段转换为小写输出。
select lower(ename) from emp
last()
last函数返回指定的字段中最后一个记录的值。
max()
max函数返回一列中的最大值，null值不包括在计算中。
upper(字段名)
将该字段转换为大写输出。
select upper(ename) from emp
命令：
修改名字是‘张三的记录中的Emp_id，把Emp_id修改为1001’
update Employee set Emp_id=1001 where Emp_name='张三'
