SQLserver数据库基础

1.数据库的概念

数据存储：将数据存储起来，以便于后续的查找更新和删除
数据组织：以结构化的形式来存储数据，如 表，行列等形式
数据操作：允许对数据进行增删改查。
数据安全：权限管理、数据加密。
数据库是一个结构化的数据集合，用于存储和组织数据，并提供对数据的高效访问、管理和操作。数据库可以用于各种场景，包括网站应用程序、企业信息系统、移动应用程序等。

2.数据库的分类

数据库可以根据不同的标准进行分类，下面是一些常见的分类方式：

关系数据库：
Oracle、DB2、MySQL、SQL Server、SyBase
Oracle适合大型的企业，运行稳定，可移植性强，功能齐全
MySQL和SqlServer适合中小型企业，效率高操作容易
非关系数据库：
使用非结构化或半结构化数据模型，如文档型数据库、键值对数据库、列存储数据库等，如MongoDB、Redis、Cassandra等。
键值存储数据库：Oracle BDB、Redis、BeansDB
列式储数数据库：HBase、Cassandra、Riak
文档型数据库：MongoDB、CouchDB
图形数据库：Neo4J、InfoGrid、Infinite Graph

3.结构化查询

结构化查询语言（Structured Query Language，简称SQL）是一种用于管理关系型数据库系统的标准化语言。它提供了一套用于定义、操作和管理关系型数据库的语法和功能。SQL具有以下特点和功能：
1.数据定义语言（DDL）： 用于定义数据库的结构，包括创建表、修改表结构、删除表等操作。
** 数据操作语言（DML）：** 用于对数据库中的数据进行增删改查操作，包括插入数据、更新数据、删除数据等。
数据查询语言（DQL）： 用于从数据库中检索数据，最常用的语句是SELECT语句，可以根据条件查询指定的数据。
数据控制语言（DCL）： 用于控制数据库的访问权限和安全性，包括授权用户访问数据库、撤销用户权限等。
数据事务控制（Transaction Control）： 用于管理数据库中的事务，包括事务的开始、提交和回滚操作，确保数据库的完整性和一致性。

4.数据库设计的几个规范

1.列不可以再分， 表中的列必须是不能再划分的属性值，保证原子性，当两列的数据相似或者相同时尽量合并成一列，避免冗余数据的产生
2.属性完全依赖于主键，在满足第一个条件的前提下满足此条件，数据表中的每一条数据必须可以唯一区分，为了保证数据的区别，需要设置一个列中数据具有唯一性的主键。
3.属性不依赖于其它非主属性。 属性直接依赖于主键数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。
比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）应该拆解成两张表（学号，姓名，年龄，性别，所在院校）+（所在院校，院校地址，院校电话）

5.数据库中表和表的关系

一对一关系（One-to-One Relationship）：两个表之间的每个记录在另一个表中只有一个对应记录。这种关系通常用于将某些可选的或可拆分的数据放在单独的表中，以免造成冗余。例如，一个 “人员” 表与一个 “身份证” 表之间可能具有一对一关系。

假设我们有一个 “人员” 表，其中包含 “姓名”、“电话”、“邮件” 等信息。由于 “身份证号码” 是唯一的，我们可以将 “身份证号码” 单独放在一个表中，以避免在 “人员” 表中出现冗余数据。这时可以创建一个 “身份证” 表，然后在 “人员” 表中添加一个指向 “身份证” 表的外键。

一对多关系（One-to-Many Relationship）：一个表中的每条记录可以对应另一个表中的多条记录。这种关系通常通过在多的一方表中添加外键来实现。例如，一个 “订单” 表与一个 “订单详情” 表之间可能存在一对多关系，即一个订单可以对应多个订单详情。

举例：

假设我们有一个 “订单” 表，其中包含 “订单号”、“下单时间”、“总金额” 等信息。由于一个订单可以包含多个商品，我们可以将每个订单的商品单独放在一个 “订单详情” 表中。这时可以在 “订单详情” 表中添加一个指向 “订单” 表的外键来表示订单和订单详情之间的关系。

假设我们有一个 “订单” 表，用于存储订单的信息，包括订单号、下单时间和总金额等。而 “订单详情” 表则用于存储每个订单中的商品详情，包括商品名称、数量和单价等。

在这种情况下，我们可以通过在 “订单详情” 表中添加一个指向 “订单” 表的外键来建立一对多关系。具体步骤如下：

创建 “订单” 表：

创建 “订单详情” 表：

在上述示例中，我们可以看到 “订单详情” 表中的每一行都与 “订单” 表中的某个订单号相关联。这样，通过外键关系，我们可以轻松地找到每个订单对应的多个订单详情。

例如，订单号为 1001 的订单有两个订单详情（商品A和商品B），订单号为 1002 的订单有一个订单详情（商品C），订单号为 1003 的订单有两个订单详情（商品D和商品E）。
这就是一对多关系的具体举例说明，其中一个表（订单）对应多个订单详情。

多对多关系（Many-to-Many Relationship）：两个表之间的多条记录可以相互对应。为了实现多对多关系，通常需要创建一个连接表（关联表、中间表），该表包含两个表的主键作为外键。例如，一个 “学生” 表和一个 “课程” 表之间可能存在多对多关系，可以通过创建一个 “选课” 表来表示学生和课程之间的关系。
举例：
我们有两个实体，分别是学生和课程，它们之间存在多对多的关系。为了维护这种关系，我们可以创建一个中间表选课记录，该表包含三列，如下所示：

在上面的表格中，我们可以看到学生和课程之间的多对多关系。例如，学生001同时选择了课程101和102，课程102被学生001和002选择，等等。选课记录表中的每一行记录都表示一个学生选择了一门课程，并且记录了选课时间。通过查询选课记录表，我们可以得到所有学生选择的所有课程，也可以得到某个学生所选的所有课程。需要注意的是，在选课记录表中，学生ID和课程ID组成了联合主键，确保了每个学生只能选择一次同一门课程，也保证了每门课程只能被同一个学生选择一次。

自引用关系（Self-Referencing Relationship）：一个表中的记录可以与同一个表中的其他记录建立关联。这种关系通常用于表示层次结构或树形结构数据。例如，一个 “员工” 表中的每个员工可能有一个上级，可以通过在同一表中添加一个指向上级员工的外键来表示自引用关系。

Alice是公司中的顶级领导，Bob和Charlie是直接下属，David和Emma是Bob的下属，Frank是Charlie的下属。
通过使用自引用关系，我们可以轻松地查询员工的上下级关系。例如，要找到Bob的直接下属，可以执行以下SQL查询：
SELECT * FROM employees WHERE supervisor_id = 2;
同样地，如果要找到某个员工的直接上级，可以根据supervisor_id列进行查询。例如，要找到Frank的直接上级，可以执行以下SQL查询：
SELECT * FROM employees WHERE id = (SELECT supervisor_id FROM employees WHERE name = ‘Frank’);

6.常用的公共字段

• ID:物理id
• CreateBy:行记录的创建人
• CreateTime：行记录创建时间
• UpdateBy:行记录的更新人
• UpdateTime：行记录更新时间
• UserIP：操作人IP
• TS：时间戳，版本号，乐观锁更新用
• ISDEL:假删除标记

7.数据库的数据类型

1.整型数据
(1)int:4字节 2^-31 ~2^31-1
(2)bigint: 8字节 63次方
(3)smallint:2字节
(4)tinyint:1字节
2.浮点型数据
（1）real： 4字节
（2）float：float(n) ，n为1-24 用4字节， 25-53用8字节
（3）decimal:用于在数据库中存储精确的小数值。其特点是可以精确表示小数值而不会出现浮点数运算带来的舍入误差。 DECIMAL(10, 2) 表示总共10位数字，其中包括小数点，其中有2位是小数部分。
3.字符串类型
前缀

• var：表示是实际存储空间是变长的，不带var，存储长度不足时，空格补足。带var节省空间，但效率低，不带var，浪费空间，但效率高。
• n：表示为Unicode字符， 它所有的字符都用两个字节表示 （因为汉字2字节）
  字符
• char （8000个英文，4000个汉字，n取值1-8000）
• varchar(n) （比char多存储两个字节的内容-关于数据长度）
• nchar(n) （4000个英文或者汉字，统一unicode标准）
• nvarchar（n）可变长度存储Unicode标准
  4.日期和时间类型
• date：数据格式为“YYYY-MM-DD” 存储占用3字节
• time：数据格式为“hh:mm:ss[.nnnnnnn]，存储占用5字节
• datetime:1900-01-01 00:00:00
• datetime2:默认格式为：YYYY-MM-DD hh:mm:ss[.fractional seconds]

5. 文本和图像类型

• text:用于存储大量文本数据的类型，可以容纳非常大的字符串。与 CHAR 和 VARCHAR 不同，TEXT 类型没有声明固定的最大长度限制。（不建议在开发中使用）
• ntext：用于存储 Unicode 格式的大型文本数据。NTEXT 类型同样可以存储最多 2^30 - 1 个 Unicode 字符。与TEXT 类似，NTEXT 也已被标记为过时的数据类型。
• image 用于存储二进制图像数据或其他大型二进制数据。IMAGE 类型可以存储最多 2^31 - 1 个字节的二进制数据。同样地，IMAGE 类型也已经被标记为过时的数据类型，不建议在新的应用中使用。
  6.货币类型
• money 该类型用于存储具有固定精度和小数位数的货币值。MONEY 数据类型占用 8 个字节的存储空间
• smallmoney 该类型也用于存储货币值，但是其具有较小的精度和范围。SMALLMONEY 数据类型占用 4 个字节的存储空间
  7.其他类型
• bit 位数据 通常存储0或1 用来表示布尔类型的true 或者false
• binary(n) 长度为n个字节的固定长度二进制数据
• rowversion 它用于存储数据库表中的版本号或时间戳信息，以跟踪记录的更新历史。是自动生成的数据类型，每当表中的行发生更改时，自动递增
• xml ：存储xml数据

8.数据库的相关操作

表的操作

1.创建表
create table Products(
Name varchar(30) not null,
Id int primary key not null,
price money null,
)
go
2.删除表
drop table 表名
3.修改字段名
EXEC sp_rename ‘Products.NewColumnName’, ‘Name’ ;
4.修改字段类型
alter table 表名 alter column 字段名 类型 是否空
5.修改字段默认值
–根据约束名称删除约束
alter table 表名 drop constraint 约束名
–根据表名向字段中增加新的默认值
alter table 表名 add default (0) for 字段名 with values
6.新增字段
alter table 表明 add 字段名 类型 not null default（1）；
//注意只能添加可以为空值的列或者有默认值的列
7.删除字段
alter table 表明 drop column 字段名

表的增删改查（CURD）

1.单表查询

查询全部字段：select * from 表名
查询部分字段：select 列1,列2,列3 from 表名 where 条件 desc/asc
查询去重字段：select distinct 字段1 from 表名
字段别名： select Name as 名字 from 表名
过滤查询

• 简单条件 select * from 表名 where 字段=值
• –in、not in : select * from 表名 where 字段 in()/not in（）
• between and :select * from Products where id between 1 and BETWEEN 运算符用于检查一个值是否在某个范围之内（包括边界值）。A 和 B 是可以进行比较的值，可以是数字、日期、字符串等类型的数据。
• 条件合并 select * from Products where id>2 and（or）price<4;
• 模糊查询 %: 表示零或多个字符 _ : 表示一个字符
  select * from Products where Name like ‘%锰’
  分页查询
  进行分页查询可以通过使用 OFFSET 和 FETCH 子句来实现。这两个子句通常与 ORDER BY 结合使用，以便按照指定的顺序对结果进行分页。

SELECT column1, column2, ...
FROM your_table
ORDER BY some_column
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
1
2
3
4
5

OFFSET 10 ROWS 指定了要跳过的行数，这里表示跳过前面的 10 行。FETCH NEXT 10 ROWS ONLY 指定了要返回的行数，这里表示返回接下来的 10 行。
分组聚集函数
select count(*) from 表名
select sum/max/min from 表名

2.多表查询 (JOIN ON)

多表查询通常涉及使用 JOIN 子句来将多个表结合在一起，以便根据它们之间的关联关系检索数据。
内连接（INNER JOIN）： 返回两个表中满足连接条件的匹配行。
–隐世内连接
select * from A，B where A.列=B.列
–显示内连接 inner 可省略

SELECT columns
FROM table1
[INNER] JOIN table2 ON table1.column_name = table2.column_name;
1
2
3

左连接（LEFT JOIN）：返回左表中所有行，并与右表中满足连接条件的行进行匹配。

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;
1
2
3

右连接（RIGHT JOIN）
返回右表中所有行，并与左表中满足连接条件的行进行匹配。

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
1
2
3

全连接 FULL OUTER JOIN ：返回左右两个表中的所有行，对于不匹配的行填充 NULL 值。

SELECT columns
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
1
2
3

3.插入数据

• 全字段插入可省略字段名称
  insert into Products (Name,Id,price,DatetimeNewAdd) values(‘忘完’,6,3,null);
• 插入多条
  insert into Products values(‘你好’,7,6,‘2023-12-11 12:12:12’),(‘你好2’,8,6,‘2023-12-11 12:12:12’);

4.删除数据

• DELETE 语句用于删除满足指定条件的表中的行。使用 DELETE 语句时，你需要指定要删除的表名以及一个条件来确定要删除哪些行。例如：

DELETE FROM tableName WHERE condition;
1

在这个示例中，tableName 是要删除行的表名，condition 是指定要删除哪些行的条件。

• TRUNCATE 语句用于删除表中的所有行，并且不能通过条件来筛选。使用 TRUNCATE 语句时，你只需要指定要删除的表名。例如：

TRUNCATE TABLE tableName;
1

请注意以下区别：
DELETE 是逐行删除，可以根据条件选择要删除的行，而 TRUNCATE 则是一次性删除整个表的数据。DELETE 可以回滚操作，可以使用事务进行控制。而 TRUNCATE 操作无法回滚。对于自增主键列，执行 DELETE 后，下一个插入的值将继续自增。而执行 TRUNCATE 后，自增计数器将重新从初始值开始。

5.更新数据

• 一般单表的更新
  UPDATE tableName
  SET column1 = value1, column2 = value2, …
  WHERE condition;
• 关联表更新
  update table1
  set table1.x1=‘abc’
  from table1
  join table2 on table1.id=table2.uId where condition
• 关联更新多张表
  update table1,table2
  set table1.字段1 =‘XXX’,table2.字段2=‘YYY’
  where table1.字段3=table2.字段3

9.数据库的常用函数

1.字符串函数

• SELECT SUBSTRING(‘Hello World’, 1, 5);从源字符串中截取一部分子字符串。
• 获取字符串的左侧或右侧的字符。
  SELECT LEFT(‘Hello World’, 5); – 返回 ‘Hello’
  SELECT RIGHT(‘Hello World’, 5); – 返回 ‘World’
• SELECT LEN(‘hello word’);返回长度
• REPLACE：替换字符串中的字符或子字符串 SELECT REPLACE(‘hello word’,‘l’,‘m’)
• SELECT CONCAT(‘Hello’, ’ ', ‘World’); – 返回 'Hello World’连接两个或多个字符串
• SELECT CHARINDEX(‘World’, ‘Hello World’); – 返回 7 检索一个字符串的位置
• 去除左右侧的空格：
  SELECT LTRIM(’ Hello’); – 返回 ‘Hello’
  SELECT RTRIM('Hello '); – 返回 ‘Hello’

2.数学函数

3.类型转换函数

4.日期函数

• GETDATE()，GETUTCDATE() ：获取当前的日期和时间
• DAY(),MONTH(),YEAR()返回指定日期的d是一个月中的第几天、月份、年数
• DATEDIFF 计算两个日期之间的天数差
• SELECT DATEDIFF(day, ‘2023-12-11’, ‘2016-11-21’) AS 日期差;计算日期差

10.数据库的常用对象

1）.存储过程

存储过程是一种预编译的程序，它被存储在数据库中并可被反复调用。存储过程通常由 SQL 语句、控制流语句和变量组成，并且可以接收参数和返回结果。使用存储过程可以将复杂的数据库操作封装为一个单独的单元，并且可以在需要时方便地调用。
存储过程的优点包括：

• 提高性能：存储过程只需要编译一次，之后每次执行都不需要重复编译。这种预编译的过程可以提高查询的速度和响应时间。
• 增加安全性：存储过程可以控制对数据库的访问权限，只允许授权用户或角色访问存储过程中包含的数据。
• 简化复杂操作：存储过程可以将多个 SQL 语句和控制流语句组合在一起，从而简化复杂的查询操作。
• 方便维护：存储过程可以在数据库中进行版本控制，并且可以通过修改存储过程来改变其行为，而无需修改应用程序代码。

创建存储过程

CREATE PROCEDURE sp_get_customer @customer_id INT
AS
BEGIN
    SELECT * FROM customers WHERE customer_id = @customer_id
END
1
2
3
4
5

调用存储过程
exec sp_get_customer 123;

2）.索引

数据库中的索引是一种数据结构，用于加快对数据库表中数据的访问速度。它类似于书籍的目录，可以帮助数据库系统快速定位到所需数据的位置。通过创建适当的索引，可以大大减少查询所需的时间，提高数据库的性能。索引通常基于表中一个或多个列的数值进行构建，这些列称为索引键。当执行查询时，数据库系统可以使用索引键来快速定位到符合条件的记录，而不必扫描整个表。

• 单列索引：基于单个列的索引。例如，对于一个用户表，可以在 user_id 列上创建单列索引，以加快按用户 ID 进行检索的速度。 CREATE INDEX idx_user_id ON users (user_id);
• 复合索引：基于多个列的组合索引。例如，对于一个订单表，可以在 customer_id 和 order_date 列上创建复合索引，以加快按客户ID和订单日期的检索速度。CREATE INDEX idx_customer_orderdate ON orders (customer_id, order_date);
• 唯一索引：确保索引列中的值唯一。例如，在员工表中，可以为员工的身份证号码创建唯一索引，以确保每个员工的身份证号码都是唯一的。CREATE UNIQUE INDEX idx_employee_id ON employees (employee_id);
• 全文索引：用于对文本数据进行全文搜索。全文索引允许在文本字段中进行关键字搜索，比如在一个博客文章表的内容字段上创建全文索引，以支持全文搜索功能。CREATE FULLTEXT INDEX idx_content ON blog_posts (post_content);