达永编程网

01多表查询

1. 定义：通过连接运算符可以实现多个表连接数据查询；
2. 分类：
1. 内连接(等值连接 inner join)： 只返回两个表中联结字段相等的行；
2. select 表1.*,表2.* from 表1 inner join 表2 on a.值 {= | != } b.值 [条件1] [where 条件] [group by 条件] [having 条件] [order by 条件];
3. 左联接(left join):返回包括左表中的所有记录和右表中联结字段相等的记录；
4. 
   
5. 
   
6. select 表1.*,表2.* from 表1 left join 表2 on a.值 {= | != } b.值 [条件1] [where 条件] [group by 条件] [having 条件] [order by 条件];
7. 右联接(right join):返回包括右表中的所有记录和左表中联结字段相等的记录；
8. select 表1.*,表2.* from 表1 right [outer] join 表2 on a.值 {= | != } b.值 [条件1] [where 条件] [group by 条件] [having 条件] [order by 条件];
9. 全外连接(full outer join):返回两个连接中所有的记录数据；
10. select 表1.*,表2.* from 表1 full [outer] join 表2 on a.值 { = | != } b.值 [条件1] [where 条件] [group by 条件] [having 条件] [order by 条件];
11. 注：
12. {}：大括号内的值必选一个，例如：选择=号。
13. []：索引符号的值表示是可添加也可不添加的，例如：选择不添加[条件1]。
14. [条件1]：[条件1]=[and | or ] [a.值=b.值 | a.值!=b.值 | a.值=值 | a.值!=值 | b.值=值 | b.值!=值]，例如：and a.值=b值，或者or a.值等于b.值。
15. [where 条件]：where后跟表中数据的筛选条件，例如：where a.值=1。
16. [group by 条件]：group by后面跟分组条件，可以跟一个或者多个分组条件，例如：group by a.值,b.值。
17. [having 条件]：having后边主要是筛选group by条件分组后的结果，后面可以跟一个或者多个条件，例如：select max(a.id),min(b.name) from A as a full outer join B as b on a.id=b.id group by a.id,b.name having a,id>1 and b.name=1。
18. [order by 条件]：order by后面跟排序条件，可以根据一个值或者多个值排序，例如：根据多个值排序，select a.* from A as a full outer join B as b on a.id=b.id order by a.值 asc,b.值 desc。
19. 扩展：
20. --并集union：去掉重复数据，把两个表数据叠加在一起
21. select COUNT(*) from PCInfo where PCScore2=100
22. union
23. select COUNT(*) from cardInfo where sex='女'
24. --并集union all：不会去掉重复
25. select COUNT(*) from PCInfo where PCScore2=100
26. union all
27. select COUNT(*) from cardInfo where sex='女'
28. --交集intersect：两个表相同的数据显示出来
29. select PCInfo.PCScore from PCInfo
30. intersect
31. select cardInfo.id from cardInfo
32. --差集except：自动删除两表重复行。并且只显示表1，不显示表2，表1在表2有的数据也不显示
33. select PCScore from PCInfo
34. except
35. select cardInfo.id from cardInfo
3. 示例：
4. 参数：Users.Id=Orders.UserId

• 内连接：select * from Users a inner join Orders b on a.id=b.userid;
• 
  

• 右联接：select * from Users a right join Orders b on a.id=b.userid;
• 
  

• 左联接：select * from Users a left join Orders b on a.id=b.userid;

• 全外连接：select * from Users a full join Orders b on a.id=b.userid;

02存储过程

存储过程是一种存储在数据库中的可重用程序，其中包含一组 SQL 语句和控制结构，可以被其他应用程序或工具调用；

1. 优点:
1. 可重用性：存储过程创建后可以在程序中被多次调用执行，而不必重新编写该存储过程的 SQL 语句。
2. 提高性能：存储过程可以减少与数据库服务器的通信次数，并且还可以缓存查询计划以提高效率，从而提高应用程序的性能。
3. 安全性：使用存储过程可以有效地防止 SQL 注入攻击，并且可以使数据库的权限管理更加简单和灵活。
4. 可移植性：数据库专业人员可以随时对存储过程进行修改，但对应用程序源代码却毫无影响，从而极大地提高了程序的可移植性。
2. 缺点：
1. 存储过程的开发和维护需要专业的数据库技术人员，对于非专业的开发人员来说可能会难以理解
2. 存储过程在开发过程中可能会带来额外的开销，因为需要对存储过程进行单独的测试和调试
3. 常用参数：
1. 输入参数（IN）：指定一个或多个输入参数，将输入值传递到存储过程中。
2. 输出参数（OUT）：从存储过程中返回一项或多项结果值。
3. 输入输出参数（INOUT）：同时支持输入和输出的参数。
4. 默认参数（DEFAULT）：为存储过程参数指定默认值。
5. 返回值（RETURN）：从存储过程中返回一个整数值
4. 系统存储参数：

• sp_help：返回有关数据库对象的信息，如表、视图、存储过程等。
• sp_rename：重命名数据库对象。
• sp_spaceused：返回指定数据库对象（表、视图等）占用空间的信息。
• sp_who：返回当前 SQL Server 实例上的活动用户和进程的信息。
• sp_lock：返回当前 SQL Server 实例上的锁定信息。
• sp_helpindex：返回指定表的索引信息。
• sp_columns：返回指定表的列信息。
• sp_adduser：为 SQL Server 创建一个新的用户登录。
• sp_password：更改 SQL Server 用户的密码。[
• sp_audit_write：将指定的文本写入 SQL Server 的安全审核日志中。
• DECLARE 语句：

1. 变量名称：可以是任何合法的标识符，使用 @ 作为前缀；
2. 数据类型：表示变量要存储的数据类型，可以是各种简单或复杂的数据类型，包括整数型、小数型、字符型、日期型等；
3. 初始值（可选）：用于设置变量的初始值，如果不指定初始值，则变量将会被初始化为 NULL。需要注意的是，初始值的数据类型必须与变量的数据类型相匹配；
4. 游标名称：可以是任何合法的标识符，使用 @ 作为前缀；
5. 游标查询：指定了游标要遍历的 SELECT 语句，可以包含 ORDER BY、DISTINCT 等子句；
6. 注：

• 在同一作用域内，不能有同名的变量或游标；
• 变量的名称和游标的名称不能是保留关键字；
• 声明游标时，必须先定义游标的查询语句，然后再执行 OPEN 语句；
• 声明游标时，必须使用 CLOSE 和 DEALLOCATE 语句显式地关闭和释放游标；
• DECLARE 语句仅用于声明变量、游标或表变量，在使用这些参数之前，我们需要为其赋值或者执行打开游标等操作；

代码的详解：

• 第一行：创建存储过程，名为 "TestProcedure"；
• 第二行：指定两个输入参数，一个整数类型的 "Parameter1" 和一个最大长度为 50 的字符串类型的 "Parameter2"；
• 第四行：开始存储过程主体；
• 第五行：设置 NOCOUNT 选项，以便在存储过程完成时不返回任何计数信息；
• 第七行：使用 "DECLARE" 语句声明两个变量。"Counter" 变量用于跟踪循环次数，"Message" 变量用于存储要打印的消息；
• 第九行：使用条件判断 "IF" 语句，检查 "Parameter1" 是否大于 0；
• 第十行：如果 "Parameter1" 大于 0，则使用循环 "WHILE" 语句执行多次，直到 "Counter" 变量等于 "Parameter1" 值为止；
• 第十一行：在循环中使用 "SET" 语句将消息内容存储在 "Message" 变量中，使用 "CONCAT" 函数将字符串拼接起来，将每个循环次数加 1，然后将结果存储在 "Message" 变量中；
• 第十二行：在循环中使用 "PRINT" 语句打印 "Message" 变量中存储的消息；
• 第十三行：在循环中使用 "SET" 语句递增 "Counter" 变量；
• 第十五行：如果 "Parameter1" 小于等于 0，则打印错误消息；
• 第六行和第十七行：结束存储过程主体和存储过程创建语句；