SQL Server数据库使用教程

数据库学习笔记，学习视频是B站SQL SERVER数据库，大部分都是SQL语言部分。

创建数据库

create database st
on --数据文件
(
	name = 'st',--逻辑名称
	filename = 'D:\record\st.mdf', --储存地址
	size = 5mb, --初始大小
	filegrowth = 2mb --增长方式
)
log on --日志文件
(
	name = 'st_log',--逻辑名称
	filename = 'D:\record\st_log.ldf', --储存地址
	size = 5mb, --初始大小
	filegrowth = 2mb --增长方式
)

或者简写

create database st

直接默认建库

删除数据库

drop database st

改为

USE master
go
drop database st

不能删除当前正在使用的数据库。所以 USE master 是使用master数据库，这样就能解除要删除的当前数据库的正在使用的状态了。

*go表示一批sql语句结束，go之后的sql语句属于另一个批处理的范围，在sql所有语句的最后都默认有一个go。但是，请注意go不是sql语句，而只是一个能被sql server实用工具识别的命令。

分号和GO

这一段只是我学习的时候的疑惑，可以跳过不看

在 SQL 中，分号（;）和 GO 是两种不同的语句分隔符，它们在不同的上下文中使用。

分号用作大多数 SQL 提交语句的结束符号。它表示一个 SQL 语句的结束，用于告诉数据库管理系统将其视为一个完整的语句。在包含多个语句的 SQL 脚本中，分号用于分隔每个独立的语句。它在从应用程序或命令行交互式输入 SQL 语句时使用。

示例使用分号分隔的 SQL 语句：

SELECT * FROM Customers;
UPDATE Orders SET Status = 'Complete' WHERE OrderID = 12345;

GO 通常用作一种批处理控制指令，代表一组 SQL 语句的结束。它主要在一些特定的数据库管理系统（例如 Microsoft SQL Server）的脚本中使用。GO 并不是 SQL 的标准语法，它是在 SQL Server 管理工具（如 SQL Server Management Studio）或其他支持它的工具中解释的。

使用 GO 控制指令的示例：

SELECT * FROM Customers;
UPDATE Orders SET Status = 'Complete' WHERE OrderID = 12345;
GO
SELECT * FROM Products;

在上述示例中，第一个 GO 语句表示前两个 SELECT 和 UPDATE 语句的结束，数据库管理系统会执行它们。然后，下一个 SELECT 语句会在 GO 后执行。

需要注意的是，使用分号还是 GO 取决于你所使用的数据库管理系统和工具。不是所有的数据库管理系统都支持 GO 控制指令，同时也不是所有的 SQL 工具都支持分号作为语句的结束符号。因此，应查阅相关的文档和手册，了解所使用数据库管理系统和工具的具体语法规则和分隔符要求。

建立数据表

语法

CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)

如：

create table stu
(
	id int primary key identity(1,1),
	name varchar(20)
)

*其中identity可以自动生成id，(1,1)代表从1开始，步长为1

如果表名是函数名等保留字，可以加[]继续使用，比如

create table [Rank]

约束关键字

主键

primary key

1. 含义：非空且唯一
2. 一张表只能有一个字段为主键
3. 主键就是表中记录的唯一标识

create table stu
(
	id int primary key,-- 给id添加主键约束
	name varchar(20)
)

或者后面指定主键

create table stu
(
	id int,
	name varchar(20),
    primary key(id)
)

或者选两列作为主键

create table stu
(
	id int,
	name varchar(20),
    primary key(id,name)
)

非空

not null

create table stu
(
	id int,
	name varchar(20) not null -- name为非空
)

唯一

unique

一般同时添加非空约束时，唯一约束写在前面

create table stu
(
	id int,
	phone_number varchar(20) unique -- 添加了唯一约束
		
)

外键

foreign key

1. 外键是一种索引，是通过一张表中的一列指向另一张表的主键，使得这两张表产生关联
2. 是某个表中的一列，它包含在另一个表的主键中
3. 一张表中可以有一个外键也可以有多个外键

创建表时加入

create table 表名
(
    列1 数据类型 foreign key (外键列名称) references 关联表名(关联列名称),
    列2 数据类型,
    ...
)

或者放后面

create table 表名
(
    列1 数据类型,
    列2 数据类型,
    ...
    foreign key (外键列名称) references 关联表名(关联列名称)
)

• 外键列与关联列必须具有相同的数据类型和长度。
• 外键列的值必须存在于关联表的关联列中，否则将违反外键约束。

检查

check

限制数据

例如性别里限制只能填入男/女

create table stu
(
	id int primary key,
	name varchar(20),
	sex nvarchar(1) check (sex = '男' or sex = '女')
)

检查后面还可以填 (a > 1000 and a < 10000) 这种表达式

默认

default

为表中的列提供默认值，如果插入或更新操作未提供具体值，则使用默认值。

例如学生表中，学校里大部分是男生，性别默认设置为“男”。

create table stu
(
	id int primary key,
	name varchar(20),
	sex nvarchar(1) default ('男') check (sex = '男' or sex = '女')
)

在 SQL 中，对于默认值的具体设置，是否需要使用括号取决于所使用的数据库管理系统以及默认值的类型。

一般来说，如果默认值是一个常量或字符串，通常不需要使用括号。例如：

sqlCREATE TABLE Customers (
    ID INT,
    Name VARCHAR(50),
    City VARCHAR(50) DEFAULT 'Unknown'
);

在上述示例中，City 列的默认值是一个字符串常量 ‘Unknown’，而在定义中没有使用括号。

然而，如果默认值是一个复杂的表达式、函数调用或必须进行计算的值，则可能需要使用括号。这是因为括号可以帮助明确表达式的计算顺序和优先级。

例如，假设有一个表格需要将当前日期作为默认值：

sqlCREATE TABLE Orders (
    ID INT,
    OrderDate DATE DEFAULT (GETDATE())
);

在上述示例中，OrderDate 列的默认值是一个函数调用 GETDATE()，而在定义中使用了括号以指示函数的调用

修改表和约束

修改表结构

(1)添加列

alter table 表名 add 新列名 数据类型

给员工表添加一列邮箱

alter table People add PeopleMail varchar(200)

(2)删除列

alter table 表名 drop column 列名

删除邮箱这一列

alter table People drop column PeopleMail

(3)修改列

alter table 表名 alter column 列名 数据类型

修改地址varchar(300)为varchar(200)

alter table People alter column PeopleAddress varchar(200)

维护约束(删除，添加)

删除约束

alter table 表名 drop constraint 约束名

删除月薪的约束

alter table People drop constraint CK_People_PeoPleSa_34C8D9D1

添加约束

添加约束(check约束)

alter table 表名 add constraint 约束名 check(表达式)

添加约束(主键)

alter table 表名 add constraint 约束名 primary key(列名)

添加约束(唯一)

alter table 表名 add constraint 约束名 unique(列名)

添加约束(默认值)

alter table 表名 add constraint 约束名 default 默认值 for 列名

添加约束(外键)

alter table 表名 add constraint 约束名 foreign key(列名) references 关联表名(列名)

插入数据

语法（一次插入多行）

insert into 表名
values(值1,值2,......),
(值1,值2,......),
(值1,值2,......),
(值1,值2,......),

或者指定插入的列

insert into 表名(列1,列2,......)
values(值1,值2,......),

还可以插入子查询的结果，如：

INSERT INTO TAB2 (C1, avg_C2)
SELECT C1, AVG(C2) FROM TAB1 GROUP BY C1;

子查询相关内容在下面。

修改和删除数据

修改数据

语法

update 表明 set 字段1=值1,字段2=值2 where 条件

例子：

工资调整，每个人加薪1000元（批量修改）

update People set PeopleSalary = PeopleSalary + 1000

将员工编号为7的人加薪500元（根据条件修改）

update People set PeopleSalary = PeopleSalary + 500
where PeopleId = 7

将软件部（部门编号为1）的员工工资低于15000元的调整为15000元（根据多条件修改）

update People set PeopleSalary = 15000
where DepartmentId = 1 and PeopleSalary < 15000

修改刘备的工资是以前的两倍，并且把刘备的地址修改为北京（修改多个字段）

update People set PeopleSalary = PeopleSalary * 2,
PeopleAddress = '北京'
where PeopleName = '刘备'

删除数据

语法

delete from 表明 where 条件

例子：

删除员工表所有记录

delete from People

删除市场部（部门编号为3）中工资大于10000的人

delete from People where DepartmentId = 3 and PeopleSalary > 10000

查询

常见的SQL函数

1. 聚合函数：

   • COUNT()：计算行数或非空值数量
   • SUM()：计算总和
   • AVG()：计算平均值
   • MIN()：找出最小值
   • MAX()：找出最大值

2. 字符串函数：

   • CONCAT()：连接字符串
   • SUBSTRING()：截取子字符串
   • UPPER()：将字符串转换为大写
   • LOWER()：将字符串转换为小写
   • LEN()：计算字符串长度

3. 数值函数：

   • ABS()：返回绝对值
   • ROUND()：四舍五入（round(25.5555,2)保留两位小数，结果为25.5600）
   • FLOOR()：向下取整
   • CEILING()：向上取整
   • RAND()：生成随机数

4. 日期和时间函数：

   • GETDATE()：返回当前日期和时间
   • CURDATE()：返回当前日期
   • YEAR()：提取年份
   • MONTH()：提取月份
   • DAY()：提取天数
   • DATEDIFF()：两日期相减

   -- 间隔年
   select datediff(year, 开始日期,结束日期) FROM T_表名;;
   -- 间隔季
   select datediff(quarter, 开始日期,结束日期) FROM T表名;
   -- 间隔月
   select datediff(month, 开始日期,结束日期) from 表名;
   -- 间隔天
   select datediff(day, 开始日期,结束日期) FROM T_表名; 
   -- 间隔周
   select datediff(week, 开始日期,结束日期) FROM T_表名;
   -- 间隔小时
   select datediff(hour, 开始日期,结束日期) FROM T_表名;
   -- 间隔分
   select datediff(minute, 开始日期,结束日期) FROM T_表名;
   -- 间隔秒
   select datediff(second, 开始日期,结束日期) FROM T_表名; 
   

5. 条件函数：

   • IF()：根据条件返回不同的值
   • CASE... END：根据条件执行不同的操作

基础查询

1.查询所有行所有列

-- 查询所有的部门
select * from Department
-- 查询所有员工信息
select * from People

是一个通配符，表示“所有列”。当你在 SELECT 语句中使用 SELECT *，它将检索给定表的所有列的数据。

2.指定列查询（姓名，性别，月薪，电话）

select PeopleName,PeopleSex,PeopleSalary,PeoplePhone from People

3.指定列查询，并自定义中文列名（姓名，性别，月薪，电话）

直接在字段后面打空格+自定义列名

select PeopleName 姓名,PeopleSex 性别,PeopleSalary 月薪,PeoplePhone 电话 from People

4.查询公司员工所在城市（不需要重复数据）（distinct）

select distinct PeopleAddress from People

distinct 关键字用于返回唯一、不重复的结果集。它通常用于 SELECT 语句中，以消除查询结果中的重复行。

5.假设工资普调10%，查询原始工资和调整后的工资，显示（姓名，性别，月薪，加薪后的月薪）（添加列查询）

select PeopleName 姓名,PeopleSex 性别,PeopleSalary 月薪,PeopleSalary*1.1 加薪后月薪 from People

条件查询

SQL中常用运算符：

• =：相等
• <> 或 !=：不相等
• <：小于
• >：大于
• <=：小于等于
• >=：大于等于
• AND 或 &&：逻辑与
• OR 或 ||：逻辑或
• NOT 或 !：逻辑非
• LIKE：模糊匹配（使用通配符 % 和 _）
• IN：判断值是否在给定列表中
• BETWEEN...AND...：判断值是否在给定范围内
• IS NULL：判断是否为空值
• IS NOT NULL：判断是否不为空值

1.查询性别为女的员工信息

select * from People where PeopleSex = '女'

2.查询性别为女且工资大于等于10000元的（多条件查询）

select * from People where PeopleSex = '女' and PeopleSalary >= 10000

3.查询月薪大于等于10000，或者月薪大于8000且为女员工的员工信息

select * from People where PeopleSalary >= 10000 or (PeopleSex = '女' and PeopleSalary >= 8000)

4.查询月薪在10000到20000之间的员工（between）

select * from People where PeopleSalary >= 10000 and PeopleSalary <= 20000;

select * from People where PeopleSalary between 10000 and 20000;

5.查询出地址在武汉或者北京的员工信息（in）

select * from People where PeopleAddress = '武汉' or PeopleAddress = '北京' ;

select * from People where PeopleAddress in('北京','武汉')；

查询出地址不在武汉或者北京的员工信息

select * from People where PeopleAddress not in('北京','武汉')；

6.查询所有员工，工资降序（排序order by）

select * from People order by PeopleSalary desc

asc为升序，desc为降序，什么都不写默认为升序

7.查询所有员工，根据名字长度排序（降序）

select * from People order by len(PeopleName) desc

8.查询工资最高的五个人/前10%的信息

select top 5 * from People order by PeopleSalary desc；
select top 10 percent * from People order by PeopleSalary desc；

9.查询地址没有填的员工（null）

select * from People where PeopleAddress is null

判断是否为空的时候，不能用等于号，要用 is

查询地址不为空的员工

select * from People where PeopleAddress is not null

假如字符串为’'，那么不是空值null，而是一个空字符串

10.查询出工资比赵云高的人的信息（子查询）

select * from People where PeopleSalary >
(select PeopleSalary from People where PeopleName = '赵云')

11.查询所有员工信息，添加一列生肖（case...end）

语法：

CASE 
   WHEN condition1 THEN result1
   WHEN condition2 THEN result2
   ...
   ELSE result
END

在上述语法中，CASE表达式首先评估条件（condition1、condition2等），并返回与第一个满足条件的结果（result1、result2等）相关联的值。如果没有条件满足，那么会返回ELSE子句中指定的默认结果（result）。

已知1985年出生的属鼠，1985%12=4，所以年份除以12余数为4的人属鼠

--查询属鼠的员工
select * from People where year(PeopleBirth) % 12 = 4

查询所有员工信息，添加一列生肖：

select * ,
case
	when year(PeopleBirth) % 12 = 4 then '鼠'
	when year(PeopleBirth) % 12 = 5 then '牛'
    ...
    when year(PeopleBirth) % 12 = 3 then '猪'
    else ''
end 生肖
from People

其中when后的判断条件year(PeopleBirth) % 12 =是一致的，条件判断语句可以放到case后

select * ,
case year(PeopleBirth) % 12
	when 4 then '鼠'
	when 5 then '牛'
    ...
    when 3 then '猪'
    else ''
end 生肖
from People

模糊查询

在 SQL 中，可以使用模糊查询来查找与指定模式匹配的数据。

LIKE 操作符之后常用的特殊字符和操作符：

1. %：百分号通配符，表示零个或多个字符的任意序列。
   • 例：WHERE column_name LIKE '%abc%' 可以匹配任意位置包含 “abc” 的值。
2. _：下划线通配符，表示单个字符的任意值。
   • 例：WHERE column_name LIKE 'a_c' 可以匹配以 “a” 开头、以 “c” 结尾，并且中间有且只有一个字符的值。
3. [...]：字符范围，指定一个字符匹配范围。
   • 例：WHERE column_name LIKE '[A-C]%' 可以匹配以 A、B 或 C 开头的值。
4. [^...]：反向字符范围，指定一个字符不匹配的范围。
   • 例：WHERE column_name LIKE '[^0-9]%' 可以匹配不以数字开头的值。

下面是一些示例，演示如何在 SQL 查询中使用模糊查询：

-- 以指定模式开头的数据
SELECT * FROM table_name WHERE column_name LIKE 'pattern%';

-- 以指定模式结尾的数据
SELECT * FROM table_name WHERE column_name LIKE '%pattern';

-- 包含指定模式的数据
SELECT * FROM table_name WHERE column_name LIKE '%pattern%';

-- 匹配特定长度的数据
SELECT * FROM table_name WHERE column_name LIKE '____';  -- 这里的四个下划线代表四个字符

-- 使用多个模式进行匹配
SELECT * FROM table_name WHERE column_name LIKE 'pattern1' OR column_name LIKE 'pattern2';

-- 使用通配符的转义字符
SELECT * FROM table_name WHERE column_name LIKE 'pattern\%';  -- 匹配含有"pattern%"的值，而不是模糊匹配

-- 匹配以 A、B 或 C 开头的值
SELECT * FROM table_name WHERE column_name LIKE '[A-C]%' ;
SELECT * FROM table_name WHERE column_name LIKE '[A,B,C]%' ;

-- 匹配不以数字开头的值
SELECT * FROM table_name WHERE column_name LIKE '[^0-9]%' ;
SELECT * FROM table_name WHERE column_name LIKE '[^0,1,2,3,4,5,6,7,8,9]%' ;

-- 查询出电话号码开头为138的,第四位是7或者8，最后一个号码是5
select *from People where PeoplePhone like'138[7,8]%5';

分组查询

以下是使用 GROUP BY 子句进行分组查询的一般语法：

SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
WHERE condition
GROUP BY column1, column2, ...

在以上语法中：

• SELECT 子句指定要包含在结果中的列，可以是被分组的列或聚合函数的结果。
• FROM 子句指定要查询的表。
• WHERE 子句（可选）用于筛选特定的行。
• GROUP BY 子句指定要进行分组的列或表达式。

注意事项：

• SELECT 后面只能跟聚合函数查询结果和GROUP BY后的列名
• 如果在 GROUP BY 子句中使用了表达式（而不是列名），则必须在 SELECT 子句中重复该表达式。
• 可以使用多个列来组合成一个组，只需在 GROUP BY 子句中列出这些列即可。
• 分组后的结果集中的每个组都会产生一个汇总结果。
• WHERE 子句不能出现聚合函数表达式

1.根据员工所在地区分组统计员工人数，员工工资总和，平均工资，最高工资和最低工资

select PeopleAddress 地区,count(*) 员工人数,
sum(PeopleSalary) 工资总和,avg(PeopleSalary) 平均工资,
max(PeopleSalary) 最高工资,avg(PeopleSalary) 最低工资
from People
group by PeopleAddress

2.根据员工所在地区分组统计员工人数，员工工资总和，平均工资，最高工资和最低工资，出生年月在1985年后的员工不参与统计

select PeopleAddress 地区,count(*) 员工人数,
sum(PeopleSalary) 工资总和,avg(PeopleSalary) 平均工资,
max(PeopleSalary) 最高工资,avg(PeopleSalary) 最低工资
from People 
where PeopleBirth < '1985-1-1'
group by PeopleAddress

3.根据员工所在地区分组统计员工人数，员工工资总和，平均工资，最高工资和最低工资，出生年月在1985年后的员工不参与统计，筛选后的每个地区的员工人数至少有2个(having)

select PeopleAddress 地区,count(*) 员工人数,
sum(PeopleSalary) 工资总和,avg(PeopleSalary) 平均工资,
max(PeopleSalary) 最高工资,avg(PeopleSalary) 最低工资
from People 
where PeopleBirth < '1985-1-1'
group by PeopleAddress
having count(*) >= 2

having关键字写到group by的后面，作为每个组的条件

多表查询

笛卡尔乘积

select xxx from xxx,xxx,....

简单多条查询（隐式内连接）

查询员工信息，显示部门名称（People表和Department表）

select * from People P,Department D
where P.DepartmentId = D.DepartmentId

重命名People和Department表，提高打字速度

内连接查询

基本语法：

SELECT columns
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column;

这里，table1 和 table2 是你要连接的两个表，matching_column 是两个表中共有的列，用于作为连接条件。

1.查询员工信息，显示部门名称

select * from People inner join Department
on People.DepartmentId = Department.DepartmentId

2.查询员工信息，显示职级名称

select * from People inner join [Rank]
on People.RankId = [Rank].RankId

3.查询员工信息，显示部门名称，显示职级名称

select * from People 
inner join Department on People.DepartmentId = Department.DepartmentId
inner join [Rank] on People.RankId = [Rank].RankId

外连接

外连接（Outer Join）是SQL中一种多表查询方式，它扩展了内连接的功能，不仅返回两个表中匹配的行，还包含不匹配的行。外连接主要有三种类型：左外连接（Left Outer Join）、右外连接（Right Outer Join）和全外连接（Full Outer Join）。

左外连接

左外连接以左表（即LEFT JOIN关键字左侧的表）为基础，返回左表中的所有行，即使在右表中没有匹配的行。对于左表中没有在右表中找到匹配的行，右表中的列值将被填充为NULL。

语法示例：

SELECT *
FROM 表1
LEFT JOIN 表2
ON 表1.CommonColumn = 表2.CommonColumn;

例子：

假设我们有两个表，Employees（员工表）和Departments（部门表）。Employees表中可能有些员工尚未分配到部门。我们要查询所有员工及其所在部门名称。

SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

在这个查询中，即使某些员工的DepartmentID在Departments表中找不到匹配，这些员工的信息也会被列出，只是对应的DepartmentName为NULL。

右外连接

右外连接与左外连接相反，它以右表（RIGHT JOIN关键字右侧的表）为基础，返回右表中的所有行，即使在左表中没有匹配的行。对于右表中没有在左表中找到匹配的行，左表中的列值将被填充为NULL。

语法示例：

SELECT *
FROM 表1
RIGHT JOIN 表2
ON 表1.CommonColumn = 表2.CommonColumn;

例子：

继续上面的例子，如果我们想找出所有部门及其员工，即使某些部门还没有员工，可以使用右外连接：

SELECT Departments.DepartmentName, Employees.EmployeeName
FROM Departments
RIGHT JOIN Employees
ON Departments.DepartmentID = Employees.DepartmentID;

这里，即使某个部门还没有分配员工，该部门的信息也会被列出，对应的EmployeeName为NULL。

外连接多张表

外连接并不局限于只连接两张表。实际上，你可以链式使用外连接来连接三个或更多的表。当你需要从多个表中获取数据，并且这些表之间存在某种关联时，多表的外连接就显得非常有用。下面是连接三张表的示例：

SELECT *
FROM TableA
LEFT JOIN TableB
ON TableA.CommonColumn = TableB.CommonColumn
LEFT JOIN TableC
ON TableA.AnotherCommonColumn = TableC.CommonColumn OR TableB.YetAnotherCommonColumn = TableC.CommonColumn;

在这个例子中，首先TableA和TableB通过它们共有的列进行了左外连接，然后这个结果集再与TableC进行左外连接，第二次连接可以基于TableA或TableB与TableC之间的不同匹配条件。

全外连接（仅作了解）

全外连接结合了左外连接和右外连接的功能，它返回左表和右表中所有行的组合。如果某行在其中一个表中没有匹配，则另一个表中的相应列值将被填充为NULL。

语法示例：

SELECT *
FROM 表1
FULL OUTER JOIN 表2
ON 表1.CommonColumn = 表2.CommonColumn;

例子：

查询所有员工及其部门，同时展示没有员工的部门和没有分配部门的员工：

SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
FULL OUTER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

在这个查询中，所有员工和部门都会被列出，无论是否匹配，不匹配的列将填充NULL。

子查询

WHERE子查询

这种类型的子查询通常返回单个值，用来作为外部查询的条件。

例子：查找所有工资高于公司平均工资的员工。

SELECT * 
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

FROM子查询

这种子查询将一个查询的结果作为临时表（派生表），供外部查询进一步操作。

例子：找出每个部门最高薪资的员工。

SELECT Department, MAX(Salary) AS MaxSalary
FROM (
    SELECT Department, EmployeeID, Salary
    FROM Employees
) AS SubQuery
GROUP BY Department;

AS SubQuery 是一个别名（alias）的定义，用于给子查询结果表命名。

例子：查询所有选修了1号课程的学生姓名（基于派生表的查询）

SELECT Student.Sname
FROM Student, (SELECT Sno FROM SC WHERE Cno = '1') AS SC1
WHERE Student.Sno = SC1.Sno;

EXISTS子查询

EXISTS子查询用于检查是否存在满足特定条件的记录，返回布尔值TRUE或FALSE。

例子：找出至少有一个员工的部门。

SELECT DepartmentID 
FROM Departments 
WHERE EXISTS (
    SELECT 1 
    FROM Employees 
    WHERE Employees.DepartmentID = Departments.DepartmentID
);

IN子查询

IN子查询用来测试一个值是否在子查询返回的结果集合中。

例子：查找所有购买了特定商品的客户。

SELECT CustomerName 
FROM Customers 
WHERE CustomerID IN (
    SELECT CustomerID 
    FROM Orders 
    WHERE ProductID = 123
);

ANY子查询

ANY是多值比较运算符，与IN类似，但用于比较操作符（如>、<、=等）。

例子：找出工资比任何部门平均工资高的员工。

SELECT EmployeeName 
FROM Employees 
WHERE Salary > ANY (
    SELECT AVG(Salary) 
    FROM Employees 
    GROUP BY DepartmentID
);

这里使用了ANY关键字，它意味着接下来的子查询必须至少有一个结果值，使得比较条件为真。换句话说，如果子查询返回多个值，只要主查询中的员工工资大于这些值中的任意一个，该员工就会被包括在结果集中。

ALL子查询

ALL用于比较操作符，表示与子查询返回的所有值进行比较。

例子：找出工资高于所有部门平均工资的员工。

SELECT EmployeeName 
FROM Employees 
WHERE Salary > ALL (
    SELECT AVG(Salary) 
    FROM Employees 
    GROUP BY DepartmentID
);

这里使用了ALL关键字，意味着接下来的子查询中的所有值都必须满足比较条件，即员工的工资必须大于子查询返回的所有部门平均工资。

集合查询

UNION

取并集 用于合并两个或多个SELECT语句的结果集，并自动去除重复行。结果集中的列名和列的顺序需要保持一致。

例子：查询所有男性和女性员工的名字。

SELECT Name FROM Employees WHERE Gender = 'M'
UNION
SELECT Name FROM Employees WHERE Gender = 'F';

INTERSECT

取交集 返回两个SELECT语句结果集中相同的行。

例子：找出既是销售部又是市场部的员工ID。

SELECT EmployeeID FROM Departments WHERE DepartmentName = 'Sales'
INTERSECT
SELECT EmployeeID FROM Departments WHERE DepartmentName = 'Marketing';

EXCEPT

取差集 从第一个查询结果中移除第二个查询结果中存在的行。

例子：找出所有选修了课程A但没选修课程B的学生。

SELECT StudentID FROM Enrollments WHERE CourseID = 'A'
EXCEPT
SELECT StudentID FROM Enrollments WHERE CourseID = 'B';

使用注意事项

• 执行集合查询时，参与集合操作的每个SELECT语句返回的列数必须相同，且对应列的数据类型需兼容。
• 使用UNION，虽然列名不必完全相同，但为了清晰和可读性，建议保持列名一致。
• 集合查询中，通常不会包含ORDER BY或LIMIT等影响结果排序或数量的子句，如果需要排序或限制结果数量，应该在外层查询中应用这些子句。

索引

索引通过创建一种数据结构（通常是B树或哈希表），来加速对表中数据的访问。当创建索引时，数据库引擎会在后台构建这个数据结构，它包括了索引列的值及其对应的行指针或记录位置。这意味着，当执行查询时，数据库可以通过索引快速定位到数据所在的行，而不是逐行扫描整个表。

聚集索引

• 特点：决定了表中数据行的物理排序顺序，每个表只能有一个聚集索引。Clustered

  当你在表上创建一个聚集索引时，SQL Server会根据索引键的值对表中的实际行数据进行物理排序。这意味着表中的数据行会按照聚集索引指定的列的顺序重新排列在磁盘上。例如，如果你在Employees表上按EmployeeID创建了一个聚集索引，那么EmployeeID小的记录会存储在磁盘上的较前位置，而EmployeeID大的记录则存储在较后的位置。

• 适用场景：适用于频繁按索引列排序或分组的查询，以及主键。

• 示例

  CREATE CLUSTERED INDEX idx_EmployeeID
  ON Employees(EmployeeID);
  

非聚集索引

• 特点：不改变表中数据的实际物理顺序，而是包含指向数据行的指针。Non-Clustered

• 适用场景：适用于查询中经常使用的列，尤其是非主键列。

• 示例

  CREATE NONCLUSTERED INDEX idx_LastName
  ON Employees(LastName);
  

覆盖索引

• 特点：索引包含查询所需的所有列，避免了回表查询。

• 优点：提高查询效率，减少I/O操作。

• 示例

  如果查询只需要LastName和FirstName，创建覆盖索引如下：

  CREATE NONCLUSTERED INDEX idx_Covered_LastName_FirstName
  ON Employees(LastName, FirstName);
  

唯一索引

• 特点：确保索引列的值是唯一的。

• 适用场景：用于防止重复数据，也可以作为非聚集索引。

• 示例

  CREATE UNIQUE NONCLUSTERED INDEX idx_Unique_Email
  ON Employees(Email);
  

全文索引

• 特点：用于全文搜索，支持复杂的文本匹配。

• 适用场景：文本字段，如文章内容、评论。

• 示例

  CREATE FULLTEXT INDEX ON Employees(Comments)
  KEY INDEX PK_Employees_EmployeeID; -- 假设PK_Employees_EmployeeID是表的主键索引
  

视图

视图（View）是一种虚拟表，它不包含实际存储的数据，而是根据定义视图的查询语句动态生成的结果集。

创建视图示例

假设我们有两个表，一个是Employees（员工表），另一个是Departments（部门表），并且我们想创建一个视图来显示每个员工及其所属部门的名称。

CREATE VIEW EmployeeDepartment AS
SELECT E.EmployeeID, E.FirstName, E.LastName, D.DepartmentName
FROM Employees AS E
INNER JOIN Departments AS D ON E.DepartmentID = D.DepartmentID;

这个视图EmployeeDepartment结合了Employees和Departments表的信息，展示了每个员工的ID、名字、姓氏以及他们所在的部门名称。

使用视图

一旦视图创建完成，就可以像查询普通表那样来查询视图：

-- 查询所有员工及其部门名称
SELECT * FROM EmployeeDepartment;

-- 查询特定部门的所有员工
SELECT * FROM EmployeeDepartment WHERE DepartmentName = '销售部';

修改和删除视图

• 修改视图：如果需要修改视图的定义，可以使用ALTER VIEW语句。

  ALTER VIEW EmployeeDepartment AS
  SELECT ...; -- 新的查询定义
  

• 删除视图：使用DROP VIEW语句可以删除不再需要的视图。

  DROP VIEW EmployeeDepartment;
  

三范式

数据库结构设计三范式

第一范式 要求数据库表的每一列（字段）都是不可分割的原子值。

假设有一个“联系人信息”表，最初设计时可能有一个字段叫做“联系方式”，里面同时包含电话号码和电子邮件地址。这就不满足1NF，因为“联系方式”不是原子性的。正确的做法是将这个字段拆分成“电话号码”和“电子邮件”两个独立的列，每个列只存储一种类型的信息。

第二范式 在满足第一范式的基础上，要求表中的所有非主键字段完全依赖于主键，不能存在部分依赖。换句话说，表中的每一列都必须与整个主键相关联，而不是主键的一部分。

假设有一个订单详情表，包含订单号、产品编号、产品名称和产品价格。如果订单号和产品编号一起作为复合主键，但产品名称和产品价格只依赖于产品编号，而不直接依赖于订单号，这就违反了2NF。解决办法是将产品信息分离到另一个只包含产品编号、产品名称和产品价格的产品表，订单详情表只需保留订单号和产品编号，产品名称和价格通过产品编号与产品表关联查询获得。

第三范式 在满足第二范式的基础上，要求表中不存在传递依赖。即每一个非主键字段都不依赖于其他非主键字段，只依赖于主键。

继续使用上述的订单详情表和产品表的例子，假设产品表中还包含了一个类别ID，而产品名称是根据类别ID决定的（例如，类别ID为1代表电子产品，产品名必须叫xxx电子产品）。这时，产品名称间接依赖于类别ID（依赖传递：产品名称—>类别ID—>产品ID），而不是直接依赖于主键（产品编号），这违反了3NF。解决方法是创建一个新的“类别描述”表，包含类别ID和类别名称，产品表中的产品名称直接依赖于产品编号，而不再依赖类别ID，通过类别ID与类别描述表关联来获取类别相关的描述信息。

表关系

一对一

一个表中的每一行记录与另一个表中的恰好一行记录相对应。

考虑一个系统中，每个用户有一个唯一的个人资料。在这种情况下，可以有两个表：Users 和 Profiles。每个用户（Users 表）只对应一个个人资料（Profiles 表），反之亦然。

一对多

一个表中的每一行记录可以与另一个表中的多行记录相对应。

一个典型的例子是班级和学生的模型。一个班级（Classes 表）可以有多个学生（Students 表），但一个学生只属于一个班级。

多对多

两个表中的行可以相互对应多行。

学生和课程的关系。一个学生可以注册多个课程，同时一门课程也可以被多个学生选修。为处理这种关系，通常需要一个关联表（或称为桥接表、交叉表）来存储两个表之间的关系。

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(50)
);

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(50)
);

-- 学生选课表
CREATE TABLE StudentCourses (
    StudentID INT,               
    CourseID INT,
    PRIMARY KEY (StudentID, CourseID), 
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);